nautilus_dialect/
sqlite.rs

1//! SQLite SQL dialect renderer.
2
3use crate::{Dialect, Sql};
4use nautilus_core::{BinaryOp, Delete, Expr, Insert, Result, Select, Update, Value};
5
6/// SQLite SQL dialect renderer.
7#[derive(Debug, Clone, Copy)]
8pub struct SqliteDialect;
9
10/// Renders query ASTs into SQLite-compatible SQL with `?` placeholders
11/// and double-quoted identifiers.
12impl Dialect for SqliteDialect {
13    fn render_select(&self, select: &Select) -> Result<Sql> {
14        let mut ctx = RenderContext::with_estimate(crate::estimate_select_render(select));
15        render_select_body_core!(&mut ctx, select, '"', render_expr, false, false);
16        Ok(Sql {
17            text: ctx.sql,
18            params: ctx.params,
19        })
20    }
21
22    fn render_select_owned(&self, mut select: Select) -> Result<Sql> {
23        let mut ctx = RenderContext::with_estimate(crate::estimate_select_render(&select));
24        render_select_body_core_mut!(&mut ctx, &mut select, '"', render_expr_owned, false, false);
25        Ok(Sql {
26            text: ctx.sql,
27            params: ctx.params,
28        })
29    }
30
31    fn render_insert(&self, insert: &Insert) -> Result<Sql> {
32        let mut ctx = RenderContext::with_estimate(crate::estimate_insert_render(insert));
33        render_insert_body!(&mut ctx, insert, '"', true, false);
34        Ok(Sql {
35            text: ctx.sql,
36            params: ctx.params,
37        })
38    }
39
40    fn render_insert_owned(&self, mut insert: Insert) -> Result<Sql> {
41        let mut ctx = RenderContext::with_estimate(crate::estimate_insert_render(&insert));
42        render_insert_body_mut!(&mut ctx, &mut insert, '"', true, false);
43        Ok(Sql {
44            text: ctx.sql,
45            params: ctx.params,
46        })
47    }
48
49    fn render_update(&self, update: &Update) -> Result<Sql> {
50        let mut ctx = RenderContext::with_estimate(crate::estimate_update_render(update));
51        render_update_body!(&mut ctx, update, '"', render_expr, true, false);
52        Ok(Sql {
53            text: ctx.sql,
54            params: ctx.params,
55        })
56    }
57
58    fn render_update_owned(&self, mut update: Update) -> Result<Sql> {
59        let mut ctx = RenderContext::with_estimate(crate::estimate_update_render(&update));
60        render_update_body_mut!(&mut ctx, &mut update, '"', render_expr_owned, true, false);
61        Ok(Sql {
62            text: ctx.sql,
63            params: ctx.params,
64        })
65    }
66
67    fn render_delete(&self, delete: &Delete) -> Result<Sql> {
68        let mut ctx = RenderContext::with_estimate(crate::estimate_delete_render(delete));
69        render_delete_body!(&mut ctx, delete, '"', render_expr, true);
70        Ok(Sql {
71            text: ctx.sql,
72            params: ctx.params,
73        })
74    }
75
76    fn render_delete_owned(&self, mut delete: Delete) -> Result<Sql> {
77        let mut ctx = RenderContext::with_estimate(crate::estimate_delete_render(&delete));
78        render_delete_body_mut!(&mut ctx, &mut delete, '"', render_expr_owned, true);
79        Ok(Sql {
80            text: ctx.sql,
81            params: ctx.params,
82        })
83    }
84}
85
86struct RenderContext {
87    sql: String,
88    params: Vec<Value>,
89}
90
91impl RenderContext {
92    fn with_estimate(estimate: crate::RenderEstimate) -> Self {
93        Self {
94            sql: String::with_capacity(estimate.sql_capacity),
95            params: Vec::with_capacity(estimate.params_capacity),
96        }
97    }
98
99    fn push_param(&mut self, value: Value) {
100        self.params.push(value);
101        self.sql.push('?');
102    }
103
104    fn take_param(&mut self, value: &mut Value) {
105        self.push_param(std::mem::replace(value, Value::Null));
106    }
107}
108
109fn render_select_body(ctx: &mut RenderContext, select: &crate::Select) {
110    render_select_body_core!(ctx, select, '"', render_expr, false, false);
111}
112
113fn render_select_body_owned(ctx: &mut RenderContext, select: &mut crate::Select) {
114    render_select_body_core_mut!(ctx, select, '"', render_expr_owned, false, false);
115}
116
117fn render_expr(ctx: &mut RenderContext, expr: &Expr) {
118    render_expr_common!(ctx, expr, '"', render_expr, render_select_body, {
119        Expr::Param(value) => {
120            if matches!(value, Value::Null) {
121                ctx.sql.push_str("NULL");
122            } else {
123                ctx.push_param(value.clone());
124            }
125        }
126        Expr::Binary { left, op, right } => {
127            if matches!(op, BinaryOp::In | BinaryOp::NotIn) {
128                ctx.sql.push('(');
129                render_expr(ctx, left);
130                ctx.sql.push(' ');
131                ctx.sql.push_str(if matches!(op, BinaryOp::In) { "IN" } else { "NOT IN" });
132                ctx.sql.push_str(" (");
133                if let Expr::List(exprs) = right.as_ref() {
134                    for (i, e) in exprs.iter().enumerate() {
135                        if i > 0 { ctx.sql.push_str(", "); }
136                        render_expr(ctx, e);
137                    }
138                } else {
139                    render_expr(ctx, right);
140                }
141                ctx.sql.push(')');
142                ctx.sql.push(')');
143            } else if matches!(op, BinaryOp::ArrayContains | BinaryOp::ArrayContainedBy | BinaryOp::ArrayOverlaps) {
144                // Array operators emulated via SQLite JSON functions.
145                // Arrays are bound as JSON strings by the connector layer; json_each unpacks them.
146                match op {
147                    BinaryOp::ArrayContains => {
148                        // col @> rhs: every element of rhs exists in col.
149                        ctx.sql.push_str("NOT EXISTS (SELECT 1 FROM json_each(");
150                        render_expr(ctx, right);
151                        ctx.sql.push_str(") AS _rhs WHERE NOT EXISTS (SELECT 1 FROM json_each(");
152                        render_expr(ctx, left);
153                        ctx.sql.push_str(") AS _col WHERE _col.value IS _rhs.value))");
154                    }
155                    BinaryOp::ArrayContainedBy => {
156                        // col <@ rhs: every element of col exists in rhs.
157                        ctx.sql.push_str("NOT EXISTS (SELECT 1 FROM json_each(");
158                        render_expr(ctx, left);
159                        ctx.sql.push_str(") AS _col WHERE NOT EXISTS (SELECT 1 FROM json_each(");
160                        render_expr(ctx, right);
161                        ctx.sql.push_str(") AS _rhs WHERE _col.value IS _rhs.value))");
162                    }
163                    BinaryOp::ArrayOverlaps => {
164                        // col && rhs: at least one element in common.
165                        ctx.sql.push_str("EXISTS (SELECT 1 FROM json_each(");
166                        render_expr(ctx, left);
167                        ctx.sql.push_str(") AS _col WHERE EXISTS (SELECT 1 FROM json_each(");
168                        render_expr(ctx, right);
169                        ctx.sql.push_str(") AS _rhs WHERE _col.value IS _rhs.value))");
170                    }
171                    _ => unreachable!(),
172                }
173            } else {
174                ctx.sql.push('(');
175                render_expr(ctx, left);
176                ctx.sql.push(' ');
177                ctx.sql.push_str(crate::binary_op_sql(op));
178                ctx.sql.push(' ');
179                render_expr(ctx, right);
180                ctx.sql.push(')');
181            }
182        }
183        Expr::FunctionCall { name, args } => {
184            let sqlite_name = match name.as_str() {
185                "json_agg" => "json_group_array",
186                "json_build_object" => "json_object",
187                _ => name,
188            };
189            ctx.sql.push_str(sqlite_name);
190            ctx.sql.push('(');
191            for (i, arg) in args.iter().enumerate() {
192                if i > 0 { ctx.sql.push_str(", "); }
193                render_expr(ctx, arg);
194            }
195            ctx.sql.push(')');
196        }
197        Expr::Filter { expr, predicate } => {
198            render_expr(ctx, expr);
199            ctx.sql.push_str(" FILTER (WHERE ");
200            render_expr(ctx, predicate);
201            ctx.sql.push(')');
202        }
203    });
204}
205
206fn render_expr_owned(ctx: &mut RenderContext, expr: &mut Expr) {
207    render_expr_common_mut!(ctx, expr, '"', render_expr_owned, render_select_body_owned, {
208        Expr::Param(value) => {
209            if matches!(value, Value::Null) {
210                ctx.sql.push_str("NULL");
211            } else {
212                ctx.take_param(value);
213            }
214        }
215        Expr::Binary { left, op, right } => {
216            if matches!(*op, BinaryOp::In | BinaryOp::NotIn) {
217                ctx.sql.push('(');
218                render_expr_owned(ctx, left.as_mut());
219                ctx.sql.push(' ');
220                ctx.sql
221                    .push_str(if matches!(*op, BinaryOp::In) { "IN" } else { "NOT IN" });
222                ctx.sql.push_str(" (");
223                if let Expr::List(exprs) = right.as_mut() {
224                    for (i, e) in exprs.iter_mut().enumerate() {
225                        if i > 0 {
226                            ctx.sql.push_str(", ");
227                        }
228                        render_expr_owned(ctx, e);
229                    }
230                } else {
231                    render_expr_owned(ctx, right.as_mut());
232                }
233                ctx.sql.push(')');
234                ctx.sql.push(')');
235            } else if matches!(
236                *op,
237                BinaryOp::ArrayContains | BinaryOp::ArrayContainedBy | BinaryOp::ArrayOverlaps
238            ) {
239                match *op {
240                    BinaryOp::ArrayContains => {
241                        ctx.sql.push_str("NOT EXISTS (SELECT 1 FROM json_each(");
242                        render_expr_owned(ctx, right.as_mut());
243                        ctx.sql.push_str(") AS _rhs WHERE NOT EXISTS (SELECT 1 FROM json_each(");
244                        render_expr_owned(ctx, left.as_mut());
245                        ctx.sql.push_str(") AS _col WHERE _col.value IS _rhs.value))");
246                    }
247                    BinaryOp::ArrayContainedBy => {
248                        ctx.sql.push_str("NOT EXISTS (SELECT 1 FROM json_each(");
249                        render_expr_owned(ctx, left.as_mut());
250                        ctx.sql.push_str(") AS _col WHERE NOT EXISTS (SELECT 1 FROM json_each(");
251                        render_expr_owned(ctx, right.as_mut());
252                        ctx.sql.push_str(") AS _rhs WHERE _col.value IS _rhs.value))");
253                    }
254                    BinaryOp::ArrayOverlaps => {
255                        ctx.sql.push_str("EXISTS (SELECT 1 FROM json_each(");
256                        render_expr_owned(ctx, left.as_mut());
257                        ctx.sql.push_str(") AS _col WHERE EXISTS (SELECT 1 FROM json_each(");
258                        render_expr_owned(ctx, right.as_mut());
259                        ctx.sql.push_str(") AS _rhs WHERE _col.value IS _rhs.value))");
260                    }
261                    _ => unreachable!(),
262                }
263            } else {
264                ctx.sql.push('(');
265                render_expr_owned(ctx, left.as_mut());
266                ctx.sql.push(' ');
267                ctx.sql.push_str(crate::binary_op_sql(op));
268                ctx.sql.push(' ');
269                render_expr_owned(ctx, right.as_mut());
270                ctx.sql.push(')');
271            }
272        }
273        Expr::FunctionCall { name, args } => {
274            let sqlite_name = match name.as_str() {
275                "json_agg" => "json_group_array",
276                "json_build_object" => "json_object",
277                _ => name,
278            };
279            ctx.sql.push_str(sqlite_name);
280            ctx.sql.push('(');
281            for (i, arg) in args.iter_mut().enumerate() {
282                if i > 0 {
283                    ctx.sql.push_str(", ");
284                }
285                render_expr_owned(ctx, arg);
286            }
287            ctx.sql.push(')');
288        }
289        Expr::Filter { expr, predicate } => {
290            render_expr_owned(ctx, expr.as_mut());
291            ctx.sql.push_str(" FILTER (WHERE ");
292            render_expr_owned(ctx, predicate.as_mut());
293            ctx.sql.push(')');
294        }
295    });
296}
297
298#[cfg(test)]
299mod tests {
300    use super::*;
301
302    fn quote_identifier(name: &str) -> String {
303        let mut sql = String::new();
304        crate::push_quoted_identifier(&mut sql, name, '"');
305        sql
306    }
307
308    #[test]
309    fn test_quote_identifier() {
310        assert_eq!(quote_identifier("users"), "\"users\"");
311        assert_eq!(quote_identifier("email"), "\"email\"");
312        assert_eq!(quote_identifier("foo\"bar"), "\"foo\"\"bar\"");
313        assert_eq!(quote_identifier("a\"b\"c"), "\"a\"\"b\"\"c\"");
314    }
315
316    #[test]
317    fn test_array_contains_operator() {
318        let dialect = SqliteDialect;
319        let expr = Expr::Binary {
320            left: Box::new(Expr::column("posts__tags")),
321            op: BinaryOp::ArrayContains,
322            right: Box::new(Expr::param(Value::Array(vec![Value::String(
323                "rust".to_string(),
324            )]))),
325        };
326        let select = Select::from_table("posts").filter(expr).build().unwrap();
327        let sql = dialect.render_select(&select).unwrap();
328
329        assert_eq!(
330            sql.text,
331            "SELECT * FROM \"posts\" WHERE NOT EXISTS (SELECT 1 FROM json_each(?) AS _rhs WHERE NOT EXISTS (SELECT 1 FROM json_each(\"posts\".\"tags\") AS _col WHERE _col.value IS _rhs.value))"
332        );
333        assert_eq!(sql.params.len(), 1);
334        match &sql.params[0] {
335            Value::Array(arr) => {
336                assert_eq!(arr.len(), 1);
337                assert_eq!(arr[0], Value::String("rust".to_string()));
338            }
339            _ => panic!("Expected Array value"),
340        }
341    }
342
343    #[test]
344    fn test_array_contained_by_operator() {
345        let dialect = SqliteDialect;
346        let expr = Expr::Binary {
347            left: Box::new(Expr::column("posts__tags")),
348            op: BinaryOp::ArrayContainedBy,
349            right: Box::new(Expr::param(Value::Array(vec![
350                Value::String("rust".to_string()),
351                Value::String("go".to_string()),
352            ]))),
353        };
354        let select = Select::from_table("posts").filter(expr).build().unwrap();
355        let sql = dialect.render_select(&select).unwrap();
356
357        assert_eq!(
358            sql.text,
359            "SELECT * FROM \"posts\" WHERE NOT EXISTS (SELECT 1 FROM json_each(\"posts\".\"tags\") AS _col WHERE NOT EXISTS (SELECT 1 FROM json_each(?) AS _rhs WHERE _col.value IS _rhs.value))"
360        );
361        assert_eq!(sql.params.len(), 1);
362        match &sql.params[0] {
363            Value::Array(arr) => {
364                assert_eq!(arr.len(), 2);
365                assert_eq!(arr[0], Value::String("rust".to_string()));
366                assert_eq!(arr[1], Value::String("go".to_string()));
367            }
368            _ => panic!("Expected Array value"),
369        }
370    }
371
372    #[test]
373    fn test_array_overlaps_operator() {
374        let dialect = SqliteDialect;
375        let expr = Expr::Binary {
376            left: Box::new(Expr::column("posts__tags")),
377            op: BinaryOp::ArrayOverlaps,
378            right: Box::new(Expr::param(Value::Array(vec![
379                Value::String("rust".to_string()),
380                Value::String("python".to_string()),
381            ]))),
382        };
383        let select = Select::from_table("posts").filter(expr).build().unwrap();
384        let sql = dialect.render_select(&select).unwrap();
385
386        assert_eq!(
387            sql.text,
388            "SELECT * FROM \"posts\" WHERE EXISTS (SELECT 1 FROM json_each(\"posts\".\"tags\") AS _col WHERE EXISTS (SELECT 1 FROM json_each(?) AS _rhs WHERE _col.value IS _rhs.value))"
389        );
390        assert_eq!(sql.params.len(), 1);
391        match &sql.params[0] {
392            Value::Array(arr) => {
393                assert_eq!(arr.len(), 2);
394                assert_eq!(arr[0], Value::String("rust".to_string()));
395                assert_eq!(arr[1], Value::String("python".to_string()));
396            }
397            _ => panic!("Expected Array value"),
398        }
399    }
400}
nautilus_dialect/sqlite.rs

nautilus_dialect/
sqlite.rs
