limbo_sqlite3_parser/to_sql_string/stmt/
mod.rs

1use crate::ast;
2
3use super::ToSqlString;
4
5mod alter_table;
6mod create_table;
7mod create_trigger;
8mod create_virtual_table;
9mod delete;
10mod insert;
11mod select;
12mod update;
13
14impl ToSqlString for ast::Stmt {
15    fn to_sql_string<C: super::ToSqlContext>(&self, context: &C) -> String {
16        match self {
17            Self::AlterTable(alter_table) => {
18                let (name, body) = alter_table.as_ref();
19                format!(
20                    "ALTER TABLE {} {};",
21                    name.to_sql_string(context),
22                    body.to_sql_string(context)
23                )
24            }
25            Self::Analyze(name) => {
26                if let Some(name) = name {
27                    format!("ANALYZE {};", name.to_sql_string(context))
28                } else {
29                    format!("ANALYZE;")
30                }
31            }
32            Self::Attach {
33                expr,
34                db_name,
35                key,
36                database_kw,
37            } => {
38                format!(
39                    "ATTACH{} {} AS {}{};",
40                    if *database_kw { " DATABASE" } else { "" },
41                    expr.to_sql_string(context),
42                    db_name.to_sql_string(context),
43                    key.as_ref().map_or("".to_string(), |key| format!(
44                        " KEY {}",
45                        key.to_sql_string(context)
46                    ))
47                )
48            }
49            Self::Begin(transaction_type, name) => {
50                let t_type = transaction_type.map_or("", |t_type| match t_type {
51                    ast::TransactionType::Deferred => " DEFERRED",
52                    ast::TransactionType::Exclusive => " EXCLUSIVE",
53                    ast::TransactionType::Immediate => " IMMEDIATE",
54                });
55                format!(
56                    "BEGIN{}{};",
57                    t_type,
58                    name.as_ref()
59                        .map_or("".to_string(), |name| format!(" TRANSACTION {}", name.0))
60                )
61            }
62            // END or COMMIT are equivalent here, so just defaulting to COMMIT
63            Self::Commit(name) => format!(
64                "COMMIT{};",
65                name.as_ref()
66                    .map_or("".to_string(), |name| format!(" TRANSACTION {}", name.0))
67            ),
68            Self::CreateIndex {
69                unique,
70                if_not_exists,
71                idx_name,
72                tbl_name,
73                columns,
74                where_clause,
75            } => format!(
76                "CREATE {}INDEX {}{} ON {} ({}){};",
77                unique.then_some("UNIQUE ").unwrap_or(""),
78                if_not_exists.then_some("IF NOT EXISTS ").unwrap_or(""),
79                idx_name.to_sql_string(context),
80                tbl_name.0,
81                columns
82                    .iter()
83                    .map(|col| col.to_sql_string(context))
84                    .collect::<Vec<_>>()
85                    .join(", "),
86                where_clause
87                    .as_ref()
88                    .map_or("".to_string(), |where_clause| format!(
89                        " WHERE {}",
90                        where_clause.to_sql_string(context)
91                    ))
92            ),
93            Self::CreateTable {
94                temporary,
95                if_not_exists,
96                tbl_name,
97                body,
98            } => format!(
99                "CREATE{} TABLE {}{} {};",
100                temporary.then_some(" TEMP").unwrap_or(""),
101                if_not_exists.then_some("IF NOT EXISTS ").unwrap_or(""),
102                tbl_name.to_sql_string(context),
103                body.to_sql_string(context)
104            ),
105            Self::CreateTrigger(trigger) => trigger.to_sql_string(context),
106            Self::CreateView {
107                temporary,
108                if_not_exists,
109                view_name,
110                columns,
111                select,
112            } => {
113                format!(
114                    "CREATE{} VIEW {}{}{} AS {};",
115                    temporary.then_some(" TEMP").unwrap_or(""),
116                    if_not_exists.then_some("IF NOT EXISTS ").unwrap_or(""),
117                    view_name.to_sql_string(context),
118                    columns.as_ref().map_or("".to_string(), |columns| format!(
119                        " ({})",
120                        columns
121                            .iter()
122                            .map(|col| col.to_string())
123                            .collect::<Vec<_>>()
124                            .join(", ")
125                    )),
126                    select.to_sql_string(context)
127                )
128            }
129            Self::CreateVirtualTable(create_virtual_table) => {
130                create_virtual_table.to_sql_string(context)
131            }
132            Self::Delete(delete) => delete.to_sql_string(context),
133            Self::Detach(name) => format!("DETACH {};", name.to_sql_string(context)),
134            Self::DropIndex {
135                if_exists,
136                idx_name,
137            } => format!(
138                "DROP INDEX{} {};",
139                if_exists.then_some("IF EXISTS ").unwrap_or(""),
140                idx_name.to_sql_string(context)
141            ),
142            Self::DropTable {
143                if_exists,
144                tbl_name,
145            } => format!(
146                "DROP TABLE{} {};",
147                if_exists.then_some("IF EXISTS ").unwrap_or(""),
148                tbl_name.to_sql_string(context)
149            ),
150            Self::DropTrigger {
151                if_exists,
152                trigger_name,
153            } => format!(
154                "DROP TRIGGER{} {};",
155                if_exists.then_some("IF EXISTS ").unwrap_or(""),
156                trigger_name.to_sql_string(context)
157            ),
158            Self::DropView {
159                if_exists,
160                view_name,
161            } => format!(
162                "DROP VIEW{} {};",
163                if_exists.then_some("IF EXISTS ").unwrap_or(""),
164                view_name.to_sql_string(context)
165            ),
166            Self::Insert(insert) => format!("{};", insert.to_sql_string(context)),
167            Self::Pragma(name, body) => format!(
168                "PRAGMA {}{};",
169                name.to_sql_string(context),
170                body.as_ref()
171                    .map_or("".to_string(), |body| match body.as_ref() {
172                        ast::PragmaBody::Equals(expr) =>
173                            format!(" = {}", expr.to_sql_string(context)),
174                        ast::PragmaBody::Call(expr) => format!("({})", expr.to_sql_string(context)),
175                    })
176            ),
177            // TODO: missing collation name
178            Self::Reindex { obj_name } => format!(
179                "REINDEX{};",
180                obj_name.as_ref().map_or("".to_string(), |name| format!(
181                    " {}",
182                    name.to_sql_string(context)
183                ))
184            ),
185            Self::Release(name) => format!("RELEASE {};", name.0),
186            Self::Rollback {
187                tx_name,
188                savepoint_name,
189            } => format!(
190                "ROLLBACK{}{};",
191                tx_name
192                    .as_ref()
193                    .map_or("".to_string(), |name| format!(" TRANSACTION {}", name.0)),
194                savepoint_name
195                    .as_ref()
196                    .map_or("".to_string(), |name| format!(" TO {}", name.0))
197            ),
198            Self::Savepoint(name) => format!("SAVEPOINT {};", name.0),
199            Self::Select(select) => format!("{};", select.to_sql_string(context)),
200            Self::Update(update) => format!("{};", update.to_sql_string(context)),
201            Self::Vacuum(name, expr) => {
202                format!(
203                    "VACUUM{}{};",
204                    name.as_ref()
205                        .map_or("".to_string(), |name| format!(" {}", name.0)),
206                    expr.as_ref().map_or("".to_string(), |expr| format!(
207                        " INTO {}",
208                        expr.to_sql_string(context)
209                    ))
210                )
211            }
212        }
213    }
214}
215
216#[cfg(test)]
217pub(crate) mod tests {
218    use crate::to_sql_string::ToSqlContext;
219
220    #[macro_export]
221    /// Create a test that first parses then input, the converts the parsed ast back to a string and compares with original input
222    macro_rules! to_sql_string_test {
223        ($test_name:ident, $input:expr) => {
224            #[test]
225            fn $test_name() {
226                let context = $crate::to_sql_string::stmt::tests::TestContext;
227                let input = $input.split_whitespace().collect::<Vec<&str>>().join(" ");
228                let mut parser = $crate::lexer::sql::Parser::new(input.as_bytes());
229                let cmd = fallible_iterator::FallibleIterator::next(&mut parser)
230                    .unwrap()
231                    .unwrap();
232                assert_eq!(
233                    input,
234                    $crate::to_sql_string::ToSqlString::to_sql_string(cmd.stmt(), &context)
235                );
236            }
237        };
238        ($test_name:ident, $input:expr, $($attribute:meta),*) => {
239            #[test]
240            $(#[$attribute])*
241            fn $test_name() {
242                let context = $crate::to_sql_string::stmt::tests::TestContext;
243                let input = $input.split_whitespace().collect::<Vec<&str>>().join(" ");
244                let mut parser = $crate::lexer::sql::Parser::new(input.as_bytes());
245                let cmd = fallible_iterator::FallibleIterator::next(&mut parser)
246                    .unwrap()
247                    .unwrap();
248                assert_eq!(
249                    input,
250                    $crate::to_sql_string::ToSqlString::to_sql_string(cmd.stmt(), &context)
251                );
252            }
253        }
254    }
255
256    pub(crate) struct TestContext;
257
258    // Placeholders for compilation
259    // Context only necessary parsing inside limbo_core or in the simulator
260    impl ToSqlContext for TestContext {
261        fn get_column_name(
262            &self,
263            _table_id: crate::ast::TableReferenceId,
264            _col_idx: usize,
265        ) -> &str {
266            todo!()
267        }
268
269        fn get_table_name(&self, _id: crate::ast::TableReferenceId) -> &str {
270            todo!()
271        }
272    }
273
274    to_sql_string_test!(test_analyze, "ANALYZE;");
275
276    to_sql_string_test!(
277        test_analyze_table,
278        "ANALYZE table;",
279        ignore = "parser can't parse table name"
280    );
281
282    to_sql_string_test!(
283        test_analyze_schema_table,
284        "ANALYZE schema.table;",
285        ignore = "parser can't parse schema.table name"
286    );
287
288    to_sql_string_test!(test_attach, "ATTACH './test.db' AS test_db;");
289
290    // ATTACH vs ATTACH DATABASE must round-trip to their own form.
291    to_sql_string_test!(test_attach_no_database_kw, "ATTACH './test.db' AS test_db;");
292
293    to_sql_string_test!(
294        test_attach_database_kw,
295        "ATTACH DATABASE './test.db' AS test_db;"
296    );
297
298    to_sql_string_test!(
299        test_attach_with_key,
300        "ATTACH './test.db' AS test_db KEY 'secret';"
301    );
302
303    to_sql_string_test!(
304        test_attach_database_kw_with_key,
305        "ATTACH DATABASE './test.db' AS test_db KEY 'secret';"
306    );
307
308    to_sql_string_test!(test_transaction, "BEGIN;");
309
310    to_sql_string_test!(test_transaction_with_name, "BEGIN TRANSACTION name;");
311
312    to_sql_string_test!(test_transaction_deferred, "BEGIN DEFERRED;");
313
314    to_sql_string_test!(test_transaction_immediate, "BEGIN IMMEDIATE;");
315
316    to_sql_string_test!(test_transaction_exclusive, "BEGIN EXCLUSIVE;");
317
318    to_sql_string_test!(test_commit, "COMMIT;");
319
320    to_sql_string_test!(test_commit_with_name, "COMMIT TRANSACTION name;");
321
322    // Test a simple index on a single column
323    to_sql_string_test!(
324        test_create_index_simple,
325        "CREATE INDEX idx_name ON employees (last_name);"
326    );
327
328    // Test a unique index to enforce uniqueness on a column
329    to_sql_string_test!(
330        test_create_unique_index,
331        "CREATE UNIQUE INDEX idx_unique_email ON users (email);"
332    );
333
334    // Test a multi-column index
335    to_sql_string_test!(
336        test_create_index_multi_column,
337        "CREATE INDEX idx_name_salary ON employees (last_name, salary);"
338    );
339
340    // Test a partial index with a WHERE clause
341    to_sql_string_test!(
342        test_create_partial_index,
343        "CREATE INDEX idx_active_users ON users (username) WHERE active = true;"
344    );
345
346    // Test an index on an expression
347    to_sql_string_test!(
348        test_create_index_on_expression,
349        "CREATE INDEX idx_upper_name ON employees (UPPER(last_name));"
350    );
351
352    // Test an index with descending order
353    to_sql_string_test!(
354        test_create_index_descending,
355        "CREATE INDEX idx_salary_desc ON employees (salary DESC);"
356    );
357
358    // Test an index with mixed ascending and descending orders on multiple columns
359    to_sql_string_test!(
360        test_create_index_mixed_order,
361        "CREATE INDEX idx_name_asc_salary_desc ON employees (last_name ASC, salary DESC);"
362    );
363
364    // Test 1: View with DISTINCT keyword
365    to_sql_string_test!(
366        test_create_view_distinct,
367        "CREATE VIEW view_distinct AS SELECT DISTINCT name FROM employees;"
368    );
369
370    // Test 2: View with LIMIT clause
371    to_sql_string_test!(
372        test_create_view_limit,
373        "CREATE VIEW view_limit AS SELECT id, name FROM employees LIMIT 10;"
374    );
375
376    // Test 3: View with CASE expression
377    to_sql_string_test!(
378        test_create_view_case,
379        "CREATE VIEW view_case AS SELECT name, CASE WHEN salary > 70000 THEN 'High' ELSE 'Low' END AS salary_level FROM employees;"
380    );
381
382    // Test 4: View with LEFT JOIN
383    to_sql_string_test!(
384        test_create_view_left_join,
385        "CREATE VIEW view_left_join AS SELECT e.name, d.name AS department FROM employees e LEFT JOIN departments d ON e.department_id = d.id;"
386    );
387
388    // Test 5: View with HAVING clause
389    to_sql_string_test!(
390        test_create_view_having,
391        "CREATE VIEW view_having AS SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id HAVING AVG(salary) > 55000;"
392    );
393
394    // Test 6: View with CTE (Common Table Expression)
395    to_sql_string_test!(
396        test_create_view_cte,
397        "CREATE VIEW view_cte AS WITH high_earners AS (SELECT * FROM employees WHERE salary > 80000) SELECT id, name FROM high_earners;"
398    );
399
400    // Test 7: View with multiple conditions in WHERE
401    to_sql_string_test!(
402        test_create_view_multi_where,
403        "CREATE VIEW view_multi_where AS SELECT id, name FROM employees WHERE salary > 50000 AND department_id = 3;"
404    );
405
406    // Test 8: View with NULL handling
407    to_sql_string_test!(
408        test_create_view_null,
409        "CREATE VIEW view_null AS SELECT name, COALESCE(salary, 0) AS salary FROM employees;"
410    );
411
412    // Test 9: View with subquery in WHERE clause
413    to_sql_string_test!(
414        test_create_view_subquery_where,
415        "CREATE VIEW view_subquery_where AS SELECT name FROM employees WHERE department_id IN (SELECT id FROM departments WHERE name = 'Sales');"
416    );
417
418    // Test 10: View with arithmetic expression
419    to_sql_string_test!(
420        test_create_view_arithmetic,
421        "CREATE VIEW view_arithmetic AS SELECT name, salary * 1.1 AS adjusted_salary FROM employees;"
422    );
423
424    to_sql_string_test!(test_detach, "DETACH 'x.db';");
425
426    to_sql_string_test!(test_drop_index, "DROP INDEX schema_name.test_index;");
427
428    to_sql_string_test!(test_drop_table, "DROP TABLE schema_name.test_table;");
429
430    to_sql_string_test!(test_drop_trigger, "DROP TRIGGER schema_name.test_trigger;");
431
432    to_sql_string_test!(test_drop_view, "DROP VIEW schema_name.test_view;");
433
434    to_sql_string_test!(test_pragma_equals, "PRAGMA schema_name.Pragma_name = 1;");
435
436    to_sql_string_test!(test_pragma_call, "PRAGMA schema_name.Pragma_name_2(1);");
437
438    to_sql_string_test!(test_reindex, "REINDEX schema_name.test_table;");
439
440    to_sql_string_test!(test_reindex_2, "REINDEX;");
441
442    to_sql_string_test!(test_release, "RELEASE savepoint_name;");
443
444    to_sql_string_test!(test_rollback, "ROLLBACK;");
445
446    to_sql_string_test!(test_rollback_2, "ROLLBACK TO savepoint_name;");
447
448    to_sql_string_test!(test_rollback_with_name, "ROLLBACK TRANSACTION name;");
449
450    to_sql_string_test!(
451        test_rollback_with_name_and_savepoint,
452        "ROLLBACK TRANSACTION name TO savepoint_name;"
453    );
454
455    to_sql_string_test!(test_savepoint, "SAVEPOINT savepoint_name;");
456
457    to_sql_string_test!(test_vacuum, "VACUUM;");
458
459    to_sql_string_test!(test_vacuum_2, "VACUUM schema_name;");
460
461    to_sql_string_test!(test_vacuum_3, "VACUUM schema_name INTO test.db;");
462}
limbo_sqlite3_parser/to_sql_string/stmt/mod.rs

limbo_sqlite3_parser/to_sql_string/stmt/
mod.rs
