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_owned(&self, mut select: Select) -> Result<Sql> {
14        let mut ctx = RenderContext::with_estimate(crate::estimate_select_render(&select));
15        render_select_body_core_mut!(&mut ctx, &mut select, '"', render_expr_owned, false, false);
16        Ok(Sql {
17            text: ctx.sql,
18            params: ctx.params,
19        })
20    }
21
22    fn render_insert_owned(&self, mut insert: Insert) -> Result<Sql> {
23        let mut ctx = RenderContext::with_estimate(crate::estimate_insert_render(&insert));
24        render_insert_body_mut!(&mut ctx, &mut insert, '"', true, false);
25        Ok(Sql {
26            text: ctx.sql,
27            params: ctx.params,
28        })
29    }
30
31    fn render_update_owned(&self, mut update: Update) -> Result<Sql> {
32        let mut ctx = RenderContext::with_estimate(crate::estimate_update_render(&update));
33        render_update_body_mut!(&mut ctx, &mut update, '"', render_expr_owned, true, false);
34        Ok(Sql {
35            text: ctx.sql,
36            params: ctx.params,
37        })
38    }
39
40    fn render_delete_owned(&self, mut delete: Delete) -> Result<Sql> {
41        let mut ctx = RenderContext::with_estimate(crate::estimate_delete_render(&delete));
42        render_delete_body_mut!(&mut ctx, &mut delete, '"', render_expr_owned, true);
43        Ok(Sql {
44            text: ctx.sql,
45            params: ctx.params,
46        })
47    }
48}
49
50struct RenderContext {
51    sql: String,
52    params: Vec<Value>,
53}
54
55impl RenderContext {
56    fn with_estimate(estimate: crate::RenderEstimate) -> Self {
57        Self {
58            sql: String::with_capacity(estimate.sql_capacity),
59            params: Vec::with_capacity(estimate.params_capacity),
60        }
61    }
62
63    fn push_param(&mut self, value: Value) {
64        self.params.push(value);
65        self.sql.push('?');
66    }
67
68    fn take_param(&mut self, value: &mut Value) {
69        self.push_param(std::mem::replace(value, Value::Null));
70    }
71}
72
73fn render_select_body_owned(ctx: &mut RenderContext, select: &mut crate::Select) {
74    render_select_body_core_mut!(ctx, select, '"', render_expr_owned, false, false);
75}
76
77fn render_expr_owned(ctx: &mut RenderContext, expr: &mut Expr) {
78    render_expr_common_mut!(ctx, expr, '"', render_expr_owned, render_select_body_owned, {
79        Expr::CompositeField {
80            table,
81            column,
82            json_key,
83            ..
84        } => {
85            ctx.sql.push_str("json_extract(");
86            crate::push_qualified_identifier(&mut ctx.sql, table, column, '"');
87            ctx.sql.push_str(", ");
88            crate::push_json_object_path_literal(&mut ctx.sql, json_key);
89            ctx.sql.push(')');
90        }
91        Expr::Param(value) => {
92            if matches!(value, Value::Null) {
93                ctx.sql.push_str("NULL");
94            } else {
95                ctx.take_param(value);
96            }
97        }
98        Expr::Binary { left, op, right } => {
99            if matches!(*op, BinaryOp::In | BinaryOp::NotIn) {
100                ctx.sql.push('(');
101                render_expr_owned(ctx, left.as_mut());
102                ctx.sql.push(' ');
103                ctx.sql
104                    .push_str(if matches!(*op, BinaryOp::In) { "IN" } else { "NOT IN" });
105                ctx.sql.push_str(" (");
106                if let Expr::List(exprs) = right.as_mut() {
107                    for (i, e) in exprs.iter_mut().enumerate() {
108                        if i > 0 {
109                            ctx.sql.push_str(", ");
110                        }
111                        render_expr_owned(ctx, e);
112                    }
113                } else {
114                    render_expr_owned(ctx, right.as_mut());
115                }
116                ctx.sql.push(')');
117                ctx.sql.push(')');
118            } else if matches!(
119                *op,
120                BinaryOp::ArrayContains | BinaryOp::ArrayContainedBy | BinaryOp::ArrayOverlaps
121            ) {
122                match *op {
123                    BinaryOp::ArrayContains => {
124                        ctx.sql.push_str("NOT EXISTS (SELECT 1 FROM json_each(");
125                        render_expr_owned(ctx, right.as_mut());
126                        ctx.sql.push_str(") AS _rhs WHERE NOT EXISTS (SELECT 1 FROM json_each(");
127                        render_expr_owned(ctx, left.as_mut());
128                        ctx.sql.push_str(") AS _col WHERE _col.value IS _rhs.value))");
129                    }
130                    BinaryOp::ArrayContainedBy => {
131                        ctx.sql.push_str("NOT EXISTS (SELECT 1 FROM json_each(");
132                        render_expr_owned(ctx, left.as_mut());
133                        ctx.sql.push_str(") AS _col WHERE NOT EXISTS (SELECT 1 FROM json_each(");
134                        render_expr_owned(ctx, right.as_mut());
135                        ctx.sql.push_str(") AS _rhs WHERE _col.value IS _rhs.value))");
136                    }
137                    BinaryOp::ArrayOverlaps => {
138                        ctx.sql.push_str("EXISTS (SELECT 1 FROM json_each(");
139                        render_expr_owned(ctx, left.as_mut());
140                        ctx.sql.push_str(") AS _col WHERE EXISTS (SELECT 1 FROM json_each(");
141                        render_expr_owned(ctx, right.as_mut());
142                        ctx.sql.push_str(") AS _rhs WHERE _col.value IS _rhs.value))");
143                    }
144                    _ => unreachable!(),
145                }
146            } else {
147                ctx.sql.push('(');
148                render_expr_owned(ctx, left.as_mut());
149                ctx.sql.push(' ');
150                ctx.sql.push_str(crate::binary_op_sql(op));
151                ctx.sql.push(' ');
152                render_expr_owned(ctx, right.as_mut());
153                ctx.sql.push(')');
154            }
155        }
156        Expr::FunctionCall { name, args } => {
157            let sqlite_name = match name.as_str() {
158                "json_agg" => "json_group_array",
159                "json_build_object" => "json_object",
160                _ => name,
161            };
162            ctx.sql.push_str(sqlite_name);
163            ctx.sql.push('(');
164            for (i, arg) in args.iter_mut().enumerate() {
165                if i > 0 {
166                    ctx.sql.push_str(", ");
167                }
168                render_expr_owned(ctx, arg);
169            }
170            ctx.sql.push(')');
171        }
172        Expr::Filter { expr, predicate } => {
173            render_expr_owned(ctx, expr.as_mut());
174            ctx.sql.push_str(" FILTER (WHERE ");
175            render_expr_owned(ctx, predicate.as_mut());
176            ctx.sql.push(')');
177        }
178    });
179}
180
181#[cfg(test)]
182mod tests {
183    use super::*;
184
185    fn quote_identifier(name: &str) -> String {
186        let mut sql = String::new();
187        crate::push_quoted_identifier(&mut sql, name, '"');
188        sql
189    }
190
191    #[test]
192    fn test_quote_identifier() {
193        assert_eq!(quote_identifier("users"), "\"users\"");
194        assert_eq!(quote_identifier("email"), "\"email\"");
195        assert_eq!(quote_identifier("foo\"bar"), "\"foo\"\"bar\"");
196        assert_eq!(quote_identifier("a\"b\"c"), "\"a\"\"b\"\"c\"");
197    }
198
199    #[test]
200    fn test_array_contains_operator() {
201        let dialect = SqliteDialect;
202        let expr = Expr::Binary {
203            left: Box::new(Expr::column("posts__tags")),
204            op: BinaryOp::ArrayContains,
205            right: Box::new(Expr::param(Value::Array(vec![Value::String(
206                "rust".to_string(),
207            )]))),
208        };
209        let select = Select::from_table("posts").filter(expr).build().unwrap();
210        let sql = dialect.render_select(&select).unwrap();
211
212        assert_eq!(
213            sql.text,
214            "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))"
215        );
216        assert_eq!(sql.params.len(), 1);
217        match &sql.params[0] {
218            Value::Array(arr) => {
219                assert_eq!(arr.len(), 1);
220                assert_eq!(arr[0], Value::String("rust".to_string()));
221            }
222            _ => panic!("Expected Array value"),
223        }
224    }
225
226    #[test]
227    fn test_array_contained_by_operator() {
228        let dialect = SqliteDialect;
229        let expr = Expr::Binary {
230            left: Box::new(Expr::column("posts__tags")),
231            op: BinaryOp::ArrayContainedBy,
232            right: Box::new(Expr::param(Value::Array(vec![
233                Value::String("rust".to_string()),
234                Value::String("go".to_string()),
235            ]))),
236        };
237        let select = Select::from_table("posts").filter(expr).build().unwrap();
238        let sql = dialect.render_select(&select).unwrap();
239
240        assert_eq!(
241            sql.text,
242            "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))"
243        );
244        assert_eq!(sql.params.len(), 1);
245        match &sql.params[0] {
246            Value::Array(arr) => {
247                assert_eq!(arr.len(), 2);
248                assert_eq!(arr[0], Value::String("rust".to_string()));
249                assert_eq!(arr[1], Value::String("go".to_string()));
250            }
251            _ => panic!("Expected Array value"),
252        }
253    }
254
255    #[test]
256    fn test_array_overlaps_operator() {
257        let dialect = SqliteDialect;
258        let expr = Expr::Binary {
259            left: Box::new(Expr::column("posts__tags")),
260            op: BinaryOp::ArrayOverlaps,
261            right: Box::new(Expr::param(Value::Array(vec![
262                Value::String("rust".to_string()),
263                Value::String("python".to_string()),
264            ]))),
265        };
266        let select = Select::from_table("posts").filter(expr).build().unwrap();
267        let sql = dialect.render_select(&select).unwrap();
268
269        assert_eq!(
270            sql.text,
271            "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))"
272        );
273        assert_eq!(sql.params.len(), 1);
274        match &sql.params[0] {
275            Value::Array(arr) => {
276                assert_eq!(arr.len(), 2);
277                assert_eq!(arr[0], Value::String("rust".to_string()));
278                assert_eq!(arr[1], Value::String("python".to_string()));
279            }
280            _ => panic!("Expected Array value"),
281        }
282    }
283
284    #[test]
285    fn composite_field_ordering_uses_json_extract() {
286        let dialect = SqliteDialect;
287        let select = Select::from_table("shipments")
288            .order_by_expr(
289                Expr::composite_field(
290                    "shipments",
291                    "delivery_snapshot",
292                    "eta_minutes",
293                    "etaMinutes",
294                    nautilus_core::JsonPathCast::Signed,
295                ),
296                nautilus_core::OrderDir::Asc,
297            )
298            .build()
299            .unwrap();
300        let sql = dialect.render_select(&select).unwrap();
301
302        assert_eq!(
303            sql.text,
304            "SELECT * FROM \"shipments\" ORDER BY json_extract(\"shipments\".\"delivery_snapshot\", '$.\"etaMinutes\"') ASC"
305        );
306    }
307}
nautilus_dialect/sqlite.rs

nautilus_dialect/
sqlite.rs
