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::new();
15        render_select_body_core!(
16            &mut ctx,
17            select,
18            quote_identifier,
19            render_expr,
20            false,
21            false
22        );
23        Ok(Sql {
24            text: ctx.sql,
25            params: ctx.params,
26        })
27    }
28
29    fn render_insert(&self, insert: &Insert) -> Result<Sql> {
30        let mut ctx = RenderContext::new();
31        render_insert_body!(&mut ctx, insert, quote_identifier, true, false);
32        Ok(Sql {
33            text: ctx.sql,
34            params: ctx.params,
35        })
36    }
37
38    fn render_update(&self, update: &Update) -> Result<Sql> {
39        let mut ctx = RenderContext::new();
40        render_update_body!(&mut ctx, update, quote_identifier, render_expr, true, false);
41        Ok(Sql {
42            text: ctx.sql,
43            params: ctx.params,
44        })
45    }
46
47    fn render_delete(&self, delete: &Delete) -> Result<Sql> {
48        let mut ctx = RenderContext::new();
49        render_delete_body!(&mut ctx, delete, quote_identifier, render_expr, true);
50        Ok(Sql {
51            text: ctx.sql,
52            params: ctx.params,
53        })
54    }
55}
56
57fn quote_identifier(name: &str) -> String {
58    crate::double_quote_identifier(name)
59}
60
61struct RenderContext {
62    sql: String,
63    params: Vec<Value>,
64}
65
66impl RenderContext {
67    fn new() -> Self {
68        Self {
69            sql: String::new(),
70            params: Vec::new(),
71        }
72    }
73
74    fn push_param(&mut self, value: Value) -> String {
75        self.params.push(value);
76        "?".to_string()
77    }
78}
79
80fn render_select_body(ctx: &mut RenderContext, select: &crate::Select) {
81    render_select_body_core!(ctx, select, quote_identifier, render_expr, false, false);
82}
83
84fn render_expr(ctx: &mut RenderContext, expr: &Expr) {
85    render_expr_common!(ctx, expr, quote_identifier, render_expr, render_select_body, {
86        Expr::Param(value) => {
87            if matches!(value, Value::Null) {
88                ctx.sql.push_str("NULL");
89            } else {
90                let placeholder = ctx.push_param(value.clone());
91                ctx.sql.push_str(&placeholder);
92            }
93        }
94        Expr::Binary { left, op, right } => {
95            if matches!(op, BinaryOp::In | BinaryOp::NotIn) {
96                ctx.sql.push('(');
97                render_expr(ctx, left);
98                ctx.sql.push(' ');
99                ctx.sql.push_str(if matches!(op, BinaryOp::In) { "IN" } else { "NOT IN" });
100                ctx.sql.push_str(" (");
101                if let Expr::List(exprs) = right.as_ref() {
102                    for (i, e) in exprs.iter().enumerate() {
103                        if i > 0 { ctx.sql.push_str(", "); }
104                        render_expr(ctx, e);
105                    }
106                } else {
107                    render_expr(ctx, right);
108                }
109                ctx.sql.push(')');
110                ctx.sql.push(')');
111            } else if matches!(op, BinaryOp::ArrayContains | BinaryOp::ArrayContainedBy | BinaryOp::ArrayOverlaps) {
112                // Array operators emulated via SQLite JSON functions.
113                // Arrays are bound as JSON strings by the connector layer; json_each unpacks them.
114                match op {
115                    BinaryOp::ArrayContains => {
116                        // col @> rhs: every element of rhs exists in col.
117                        ctx.sql.push_str("NOT EXISTS (SELECT 1 FROM json_each(");
118                        render_expr(ctx, right);
119                        ctx.sql.push_str(") AS _rhs WHERE NOT EXISTS (SELECT 1 FROM json_each(");
120                        render_expr(ctx, left);
121                        ctx.sql.push_str(") AS _col WHERE _col.value IS _rhs.value))");
122                    }
123                    BinaryOp::ArrayContainedBy => {
124                        // col <@ rhs: every element of col exists in rhs.
125                        ctx.sql.push_str("NOT EXISTS (SELECT 1 FROM json_each(");
126                        render_expr(ctx, left);
127                        ctx.sql.push_str(") AS _col WHERE NOT EXISTS (SELECT 1 FROM json_each(");
128                        render_expr(ctx, right);
129                        ctx.sql.push_str(") AS _rhs WHERE _col.value IS _rhs.value))");
130                    }
131                    BinaryOp::ArrayOverlaps => {
132                        // col && rhs: at least one element in common.
133                        ctx.sql.push_str("EXISTS (SELECT 1 FROM json_each(");
134                        render_expr(ctx, left);
135                        ctx.sql.push_str(") AS _col WHERE EXISTS (SELECT 1 FROM json_each(");
136                        render_expr(ctx, right);
137                        ctx.sql.push_str(") AS _rhs WHERE _col.value IS _rhs.value))");
138                    }
139                    _ => unreachable!(),
140                }
141            } else {
142                ctx.sql.push('(');
143                render_expr(ctx, left);
144                ctx.sql.push(' ');
145                ctx.sql.push_str(crate::binary_op_sql(op));
146                ctx.sql.push(' ');
147                render_expr(ctx, right);
148                ctx.sql.push(')');
149            }
150        }
151        Expr::FunctionCall { name, args } => {
152            let sqlite_name = match name.as_str() {
153                "json_agg" => "json_group_array",
154                "json_build_object" => "json_object",
155                _ => name,
156            };
157            ctx.sql.push_str(sqlite_name);
158            ctx.sql.push('(');
159            for (i, arg) in args.iter().enumerate() {
160                if i > 0 { ctx.sql.push_str(", "); }
161                render_expr(ctx, arg);
162            }
163            ctx.sql.push(')');
164        }
165        Expr::Filter { expr, predicate } => {
166            render_expr(ctx, expr);
167            ctx.sql.push_str(" FILTER (WHERE ");
168            render_expr(ctx, predicate);
169            ctx.sql.push(')');
170        }
171    });
172}
173
174#[cfg(test)]
175mod tests {
176    use super::*;
177
178    #[test]
179    fn test_quote_identifier() {
180        assert_eq!(quote_identifier("users"), "\"users\"");
181        assert_eq!(quote_identifier("email"), "\"email\"");
182        assert_eq!(quote_identifier("foo\"bar"), "\"foo\"\"bar\"");
183        assert_eq!(quote_identifier("a\"b\"c"), "\"a\"\"b\"\"c\"");
184    }
185
186    #[test]
187    fn test_array_contains_operator() {
188        let dialect = SqliteDialect;
189        let expr = Expr::Binary {
190            left: Box::new(Expr::column("posts__tags")),
191            op: BinaryOp::ArrayContains,
192            right: Box::new(Expr::param(Value::Array(vec![Value::String(
193                "rust".to_string(),
194            )]))),
195        };
196        let select = Select::from_table("posts").filter(expr).build().unwrap();
197        let sql = dialect.render_select(&select).unwrap();
198
199        assert_eq!(
200            sql.text,
201            "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))"
202        );
203        assert_eq!(sql.params.len(), 1);
204        match &sql.params[0] {
205            Value::Array(arr) => {
206                assert_eq!(arr.len(), 1);
207                assert_eq!(arr[0], Value::String("rust".to_string()));
208            }
209            _ => panic!("Expected Array value"),
210        }
211    }
212
213    #[test]
214    fn test_array_contained_by_operator() {
215        let dialect = SqliteDialect;
216        let expr = Expr::Binary {
217            left: Box::new(Expr::column("posts__tags")),
218            op: BinaryOp::ArrayContainedBy,
219            right: Box::new(Expr::param(Value::Array(vec![
220                Value::String("rust".to_string()),
221                Value::String("go".to_string()),
222            ]))),
223        };
224        let select = Select::from_table("posts").filter(expr).build().unwrap();
225        let sql = dialect.render_select(&select).unwrap();
226
227        assert_eq!(
228            sql.text,
229            "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))"
230        );
231        assert_eq!(sql.params.len(), 1);
232        match &sql.params[0] {
233            Value::Array(arr) => {
234                assert_eq!(arr.len(), 2);
235                assert_eq!(arr[0], Value::String("rust".to_string()));
236                assert_eq!(arr[1], Value::String("go".to_string()));
237            }
238            _ => panic!("Expected Array value"),
239        }
240    }
241
242    #[test]
243    fn test_array_overlaps_operator() {
244        let dialect = SqliteDialect;
245        let expr = Expr::Binary {
246            left: Box::new(Expr::column("posts__tags")),
247            op: BinaryOp::ArrayOverlaps,
248            right: Box::new(Expr::param(Value::Array(vec![
249                Value::String("rust".to_string()),
250                Value::String("python".to_string()),
251            ]))),
252        };
253        let select = Select::from_table("posts").filter(expr).build().unwrap();
254        let sql = dialect.render_select(&select).unwrap();
255
256        assert_eq!(
257            sql.text,
258            "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))"
259        );
260        assert_eq!(sql.params.len(), 1);
261        match &sql.params[0] {
262            Value::Array(arr) => {
263                assert_eq!(arr.len(), 2);
264                assert_eq!(arr[0], Value::String("rust".to_string()));
265                assert_eq!(arr[1], Value::String("python".to_string()));
266            }
267            _ => panic!("Expected Array value"),
268        }
269    }
270}
nautilus_dialect/sqlite.rs

nautilus_dialect/
sqlite.rs
