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

limbo_sqlite3_parser/to_sql_string/stmt/
mod.rs
