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::Param(value) => {
80            if matches!(value, Value::Null) {
81                ctx.sql.push_str("NULL");
82            } else {
83                ctx.take_param(value);
84            }
85        }
86        Expr::Binary { left, op, right } => {
87            if matches!(*op, BinaryOp::In | BinaryOp::NotIn) {
88                ctx.sql.push('(');
89                render_expr_owned(ctx, left.as_mut());
90                ctx.sql.push(' ');
91                ctx.sql
92                    .push_str(if matches!(*op, BinaryOp::In) { "IN" } else { "NOT IN" });
93                ctx.sql.push_str(" (");
94                if let Expr::List(exprs) = right.as_mut() {
95                    for (i, e) in exprs.iter_mut().enumerate() {
96                        if i > 0 {
97                            ctx.sql.push_str(", ");
98                        }
99                        render_expr_owned(ctx, e);
100                    }
101                } else {
102                    render_expr_owned(ctx, right.as_mut());
103                }
104                ctx.sql.push(')');
105                ctx.sql.push(')');
106            } else if matches!(
107                *op,
108                BinaryOp::ArrayContains | BinaryOp::ArrayContainedBy | BinaryOp::ArrayOverlaps
109            ) {
110                match *op {
111                    BinaryOp::ArrayContains => {
112                        ctx.sql.push_str("NOT EXISTS (SELECT 1 FROM json_each(");
113                        render_expr_owned(ctx, right.as_mut());
114                        ctx.sql.push_str(") AS _rhs WHERE NOT EXISTS (SELECT 1 FROM json_each(");
115                        render_expr_owned(ctx, left.as_mut());
116                        ctx.sql.push_str(") AS _col WHERE _col.value IS _rhs.value))");
117                    }
118                    BinaryOp::ArrayContainedBy => {
119                        ctx.sql.push_str("NOT EXISTS (SELECT 1 FROM json_each(");
120                        render_expr_owned(ctx, left.as_mut());
121                        ctx.sql.push_str(") AS _col WHERE NOT EXISTS (SELECT 1 FROM json_each(");
122                        render_expr_owned(ctx, right.as_mut());
123                        ctx.sql.push_str(") AS _rhs WHERE _col.value IS _rhs.value))");
124                    }
125                    BinaryOp::ArrayOverlaps => {
126                        ctx.sql.push_str("EXISTS (SELECT 1 FROM json_each(");
127                        render_expr_owned(ctx, left.as_mut());
128                        ctx.sql.push_str(") AS _col WHERE EXISTS (SELECT 1 FROM json_each(");
129                        render_expr_owned(ctx, right.as_mut());
130                        ctx.sql.push_str(") AS _rhs WHERE _col.value IS _rhs.value))");
131                    }
132                    _ => unreachable!(),
133                }
134            } else {
135                ctx.sql.push('(');
136                render_expr_owned(ctx, left.as_mut());
137                ctx.sql.push(' ');
138                ctx.sql.push_str(crate::binary_op_sql(op));
139                ctx.sql.push(' ');
140                render_expr_owned(ctx, right.as_mut());
141                ctx.sql.push(')');
142            }
143        }
144        Expr::FunctionCall { name, args } => {
145            let sqlite_name = match name.as_str() {
146                "json_agg" => "json_group_array",
147                "json_build_object" => "json_object",
148                _ => name,
149            };
150            ctx.sql.push_str(sqlite_name);
151            ctx.sql.push('(');
152            for (i, arg) in args.iter_mut().enumerate() {
153                if i > 0 {
154                    ctx.sql.push_str(", ");
155                }
156                render_expr_owned(ctx, arg);
157            }
158            ctx.sql.push(')');
159        }
160        Expr::Filter { expr, predicate } => {
161            render_expr_owned(ctx, expr.as_mut());
162            ctx.sql.push_str(" FILTER (WHERE ");
163            render_expr_owned(ctx, predicate.as_mut());
164            ctx.sql.push(')');
165        }
166    });
167}
168
169#[cfg(test)]
170mod tests {
171    use super::*;
172
173    fn quote_identifier(name: &str) -> String {
174        let mut sql = String::new();
175        crate::push_quoted_identifier(&mut sql, name, '"');
176        sql
177    }
178
179    #[test]
180    fn test_quote_identifier() {
181        assert_eq!(quote_identifier("users"), "\"users\"");
182        assert_eq!(quote_identifier("email"), "\"email\"");
183        assert_eq!(quote_identifier("foo\"bar"), "\"foo\"\"bar\"");
184        assert_eq!(quote_identifier("a\"b\"c"), "\"a\"\"b\"\"c\"");
185    }
186
187    #[test]
188    fn test_array_contains_operator() {
189        let dialect = SqliteDialect;
190        let expr = Expr::Binary {
191            left: Box::new(Expr::column("posts__tags")),
192            op: BinaryOp::ArrayContains,
193            right: Box::new(Expr::param(Value::Array(vec![Value::String(
194                "rust".to_string(),
195            )]))),
196        };
197        let select = Select::from_table("posts").filter(expr).build().unwrap();
198        let sql = dialect.render_select(&select).unwrap();
199
200        assert_eq!(
201            sql.text,
202            "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))"
203        );
204        assert_eq!(sql.params.len(), 1);
205        match &sql.params[0] {
206            Value::Array(arr) => {
207                assert_eq!(arr.len(), 1);
208                assert_eq!(arr[0], Value::String("rust".to_string()));
209            }
210            _ => panic!("Expected Array value"),
211        }
212    }
213
214    #[test]
215    fn test_array_contained_by_operator() {
216        let dialect = SqliteDialect;
217        let expr = Expr::Binary {
218            left: Box::new(Expr::column("posts__tags")),
219            op: BinaryOp::ArrayContainedBy,
220            right: Box::new(Expr::param(Value::Array(vec![
221                Value::String("rust".to_string()),
222                Value::String("go".to_string()),
223            ]))),
224        };
225        let select = Select::from_table("posts").filter(expr).build().unwrap();
226        let sql = dialect.render_select(&select).unwrap();
227
228        assert_eq!(
229            sql.text,
230            "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))"
231        );
232        assert_eq!(sql.params.len(), 1);
233        match &sql.params[0] {
234            Value::Array(arr) => {
235                assert_eq!(arr.len(), 2);
236                assert_eq!(arr[0], Value::String("rust".to_string()));
237                assert_eq!(arr[1], Value::String("go".to_string()));
238            }
239            _ => panic!("Expected Array value"),
240        }
241    }
242
243    #[test]
244    fn test_array_overlaps_operator() {
245        let dialect = SqliteDialect;
246        let expr = Expr::Binary {
247            left: Box::new(Expr::column("posts__tags")),
248            op: BinaryOp::ArrayOverlaps,
249            right: Box::new(Expr::param(Value::Array(vec![
250                Value::String("rust".to_string()),
251                Value::String("python".to_string()),
252            ]))),
253        };
254        let select = Select::from_table("posts").filter(expr).build().unwrap();
255        let sql = dialect.render_select(&select).unwrap();
256
257        assert_eq!(
258            sql.text,
259            "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))"
260        );
261        assert_eq!(sql.params.len(), 1);
262        match &sql.params[0] {
263            Value::Array(arr) => {
264                assert_eq!(arr.len(), 2);
265                assert_eq!(arr[0], Value::String("rust".to_string()));
266                assert_eq!(arr[1], Value::String("python".to_string()));
267            }
268            _ => panic!("Expected Array value"),
269        }
270    }
271}
nautilus_dialect/sqlite.rs

nautilus_dialect/
sqlite.rs
