dbrest_sqlite/
dialect.rs

1//! SQLite SQL dialect implementation.
2
3use dbrest_core::backend::SqlDialect;
4use dbrest_core::plan::types::CoercibleField;
5use dbrest_core::query::sql_builder::{SqlBuilder, SqlParam};
6
7/// SQLite dialect — generates SQLite-specific SQL syntax.
8#[derive(Debug, Clone, Copy)]
9pub struct SqliteDialect;
10
11impl SqlDialect for SqliteDialect {
12    fn json_agg_with_columns(&self, b: &mut SqlBuilder, alias: &str, columns: &[&str]) {
13        if columns.is_empty() {
14            // Fallback: assume the alias refers to a single-column JSON text.
15            b.push("COALESCE(json_group_array(json(");
16            b.push_ident(alias);
17            b.push(")), '[]')");
18        } else {
19            // Build: COALESCE(json_group_array(json_object('col1', "alias"."col1", ...)), '[]')
20            b.push("COALESCE(json_group_array(json_object(");
21            for (i, col) in columns.iter().enumerate() {
22                if i > 0 {
23                    b.push(", ");
24                }
25                b.push("'");
26                b.push(col);
27                b.push("', ");
28                b.push_ident(alias);
29                b.push(".");
30                b.push_ident(col);
31            }
32            b.push(")), '[]')");
33        }
34    }
35
36    fn row_to_json_with_columns(&self, b: &mut SqlBuilder, alias: &str, columns: &[&str]) {
37        if columns.is_empty() {
38            b.push("json(");
39            b.push_ident(alias);
40            b.push(")");
41        } else {
42            // Build: json_object('col1', "alias"."col1", ...)
43            b.push("json_object(");
44            for (i, col) in columns.iter().enumerate() {
45                if i > 0 {
46                    b.push(", ");
47                }
48                b.push("'");
49                b.push(col);
50                b.push("', ");
51                b.push_ident(alias);
52                b.push(".");
53                b.push_ident(col);
54            }
55            b.push(")");
56        }
57    }
58
59    fn count_expr(&self, b: &mut SqlBuilder, expr: &str) {
60        b.push("COUNT(");
61        b.push_ident(expr);
62        b.push(")");
63    }
64
65    fn count_star(&self, b: &mut SqlBuilder) {
66        b.push("SELECT COUNT(*) AS ");
67        b.push_ident("dbrst_filtered_count");
68    }
69
70    fn set_session_var(&self, _b: &mut SqlBuilder, _key: &str, _value: &str) {
71        tracing::warn!(
72            "set_session_var called on SqliteDialect — this is a no-op; \
73             use build_tx_vars_statement instead"
74        );
75    }
76
77    fn session_vars_are_select_exprs(&self) -> bool {
78        false
79    }
80
81    fn build_tx_vars_statement(&self, b: &mut SqlBuilder, vars: &[(&str, &str)]) {
82        // Single INSERT OR REPLACE with multiple VALUES rows.
83        b.push("INSERT OR REPLACE INTO _dbrest_vars(key, val) VALUES ");
84        for (i, (key, value)) in vars.iter().enumerate() {
85            if i > 0 {
86                b.push(", ");
87            }
88            b.push("('");
89            for ch in key.chars() {
90                if ch == '\'' {
91                    b.push("'");
92                }
93                b.push_char(ch);
94            }
95            b.push("', '");
96            for ch in value.chars() {
97                if ch == '\'' {
98                    b.push("'");
99                }
100                b.push_char(ch);
101            }
102            b.push("')");
103        }
104    }
105
106    fn get_session_var(&self, b: &mut SqlBuilder, key: &str, column_alias: &str) {
107        // Read from the temp vars table, returning NULL if not set.
108        b.push("(SELECT val FROM _dbrest_vars WHERE key = '");
109        for ch in key.chars() {
110            if ch == '\'' {
111                b.push("'");
112            }
113            b.push_char(ch);
114        }
115        b.push("') AS ");
116        b.push(column_alias);
117    }
118
119    fn type_cast(&self, b: &mut SqlBuilder, expr: &str, ty: &str) {
120        b.push("CAST(");
121        b.push(expr);
122        b.push(" AS ");
123        b.push(&sqlite_type(ty));
124        b.push(")");
125    }
126
127    fn from_json_body(&self, b: &mut SqlBuilder, columns: &[CoercibleField], json_bytes: &[u8]) {
128        // SQLite: Use json_each() to iterate over array elements,
129        // then json_extract() to pull out each column.
130        //
131        // SELECT json_extract(value, '$.col1') AS "col1", ... FROM json_each($1)
132        let is_array = json_bytes.first().map(|&c| c == b'[').unwrap_or(false);
133
134        if is_array {
135            b.push("(SELECT ");
136            b.push_separated(", ", columns, |b, col| {
137                b.push("json_extract(value, '$.");
138                // Escape the column name for JSON path
139                b.push(&col.name.replace('\'', "''"));
140                b.push("') AS ");
141                b.push_ident(&col.name);
142            });
143            b.push(" FROM json_each(");
144            b.push_param(SqlParam::Text(
145                String::from_utf8_lossy(json_bytes).into_owned(),
146            ));
147            b.push("))");
148        } else {
149            // Single object: wrap in array
150            b.push("(SELECT ");
151            b.push_separated(", ", columns, |b, col| {
152                b.push("json_extract(");
153                b.push_param(SqlParam::Text(
154                    String::from_utf8_lossy(json_bytes).into_owned(),
155                ));
156                b.push(", '$.");
157                b.push(&col.name.replace('\'', "''"));
158                b.push("') AS ");
159                b.push_ident(&col.name);
160            });
161            b.push(")");
162        }
163    }
164
165    fn push_type_cast_suffix(&self, b: &mut SqlBuilder, ty: &str) {
166        // SQLite doesn't support :: syntax. We can't easily wrap in CAST
167        // after the fact, so for suffix-style casts we use a no-op for now.
168        // The type affinity system in SQLite handles most cases automatically.
169        let _ = (b, ty);
170    }
171
172    fn push_array_type_cast_suffix(&self, b: &mut SqlBuilder, _ty: &str) {
173        // SQLite has no array types — this is a no-op.
174        let _ = b;
175    }
176
177    fn quote_ident(&self, ident: &str) -> String {
178        format!("\"{}\"", ident.replace('"', "\"\""))
179    }
180
181    fn quote_literal(&self, lit: &str) -> String {
182        format!("'{}'", lit.replace('\'', "''"))
183    }
184
185    fn supports_fts(&self) -> bool {
186        false // FTS5 support can be added later
187    }
188
189    fn fts_predicate(
190        &self,
191        _b: &mut SqlBuilder,
192        _config: Option<&str>,
193        _column: &str,
194        _operator: &str,
195    ) {
196        // FTS5 support not yet implemented.
197        // Future: column MATCH $1
198    }
199
200    fn row_to_json_star(&self, b: &mut SqlBuilder, source: &str) {
201        // SQLite doesn't support source.* in function calls.
202        // For scalar RPCs, we wrap with json_object from all columns.
203        // Fallback: just select all columns as JSON.
204        b.push("json_group_array(json_object(*)) FROM ");
205        b.push(source);
206    }
207
208    fn count_star_from(&self, b: &mut SqlBuilder, source: &str) {
209        b.push("(SELECT COUNT(*) FROM ");
210        b.push(source);
211        b.push(")");
212    }
213
214    fn push_literal(&self, b: &mut SqlBuilder, s: &str) {
215        // SQLite uses standard SQL literal escaping (no E-string prefix).
216        b.push("'");
217        for ch in s.chars() {
218            if ch == '\'' {
219                b.push("'");
220            }
221            b.push_char(ch);
222        }
223        b.push("'");
224    }
225
226    fn supports_lateral_join(&self) -> bool {
227        false
228    }
229
230    fn named_param_assign(&self) -> &str {
231        // SQLite doesn't support named parameter assignment in function calls.
232        // This won't typically be used since SQLite doesn't have stored procedures.
233        " = "
234    }
235
236    fn supports_dml_cte(&self) -> bool {
237        false
238    }
239}
240
241/// Map PostgreSQL type names to SQLite type affinities.
242fn sqlite_type(pg_type: &str) -> String {
243    match pg_type.to_lowercase().as_str() {
244        "integer" | "int" | "int4" | "int8" | "bigint" | "smallint" | "int2" | "serial"
245        | "bigserial" => "INTEGER".to_string(),
246        "real" | "float4" | "float8" | "double precision" | "numeric" | "decimal" => {
247            "REAL".to_string()
248        }
249        "boolean" | "bool" => "INTEGER".to_string(), // SQLite uses 0/1
250        "blob" | "bytea" => "BLOB".to_string(),
251        "json" | "jsonb" => "TEXT".to_string(),
252        _ => "TEXT".to_string(),
253    }
254}
255
256#[cfg(test)]
257mod tests {
258    use super::*;
259
260    fn dialect() -> SqliteDialect {
261        SqliteDialect
262    }
263
264    #[test]
265    fn test_json_agg() {
266        let mut b = SqlBuilder::new();
267        dialect().json_agg(&mut b, "_dbrst_t");
268        assert_eq!(
269            b.sql(),
270            "COALESCE(json_group_array(json(\"_dbrst_t\")), '[]')"
271        );
272    }
273
274    #[test]
275    fn test_count_expr() {
276        let mut b = SqlBuilder::new();
277        dialect().count_expr(&mut b, "_dbrst_t");
278        assert_eq!(b.sql(), "COUNT(\"_dbrst_t\")");
279    }
280
281    #[test]
282    fn test_count_star_from() {
283        let mut b = SqlBuilder::new();
284        dialect().count_star_from(&mut b, "dbrst_source");
285        assert_eq!(b.sql(), "(SELECT COUNT(*) FROM dbrst_source)");
286    }
287
288    #[test]
289    fn test_type_cast() {
290        let mut b = SqlBuilder::new();
291        dialect().type_cast(&mut b, "col", "integer");
292        assert_eq!(b.sql(), "CAST(col AS INTEGER)");
293    }
294
295    #[test]
296    fn test_type_cast_text() {
297        let mut b = SqlBuilder::new();
298        dialect().type_cast(&mut b, "col", "varchar");
299        assert_eq!(b.sql(), "CAST(col AS TEXT)");
300    }
301
302    #[test]
303    fn test_push_literal_no_backslash() {
304        let mut b = SqlBuilder::new();
305        dialect().push_literal(&mut b, "hello");
306        assert_eq!(b.sql(), "'hello'");
307    }
308
309    #[test]
310    fn test_push_literal_with_quote() {
311        let mut b = SqlBuilder::new();
312        dialect().push_literal(&mut b, "it's");
313        assert_eq!(b.sql(), "'it''s'");
314    }
315
316    #[test]
317    fn test_push_literal_with_backslash() {
318        let mut b = SqlBuilder::new();
319        dialect().push_literal(&mut b, "back\\slash");
320        // SQLite does NOT use E-string prefix
321        assert_eq!(b.sql(), "'back\\slash'");
322    }
323
324    #[test]
325    fn test_build_tx_vars_statement() {
326        let mut b = SqlBuilder::new();
327        dialect().build_tx_vars_statement(
328            &mut b,
329            &[("request.method", "GET"), ("request.path", "/users")],
330        );
331        let sql = b.sql();
332        assert!(sql.contains("INSERT OR REPLACE INTO _dbrest_vars"));
333        assert!(sql.contains("request.method"));
334        assert!(sql.contains("GET"));
335        assert!(sql.contains("request.path"));
336        assert!(sql.contains("/users"));
337    }
338
339    #[test]
340    fn test_session_vars_are_not_select_exprs() {
341        assert!(!dialect().session_vars_are_select_exprs());
342    }
343
344    #[test]
345    fn test_get_session_var() {
346        let mut b = SqlBuilder::new();
347        dialect().get_session_var(&mut b, "response.headers", "response_headers");
348        assert!(b.sql().contains("_dbrest_vars"));
349        assert!(b.sql().contains("response.headers"));
350        assert!(b.sql().contains("AS response_headers"));
351    }
352
353    #[test]
354    fn test_quote_ident() {
355        assert_eq!(dialect().quote_ident("users"), "\"users\"");
356        assert_eq!(dialect().quote_ident("a\"b"), "\"a\"\"b\"");
357    }
358
359    #[test]
360    fn test_quote_literal() {
361        assert_eq!(dialect().quote_literal("hello"), "'hello'");
362        assert_eq!(dialect().quote_literal("it's"), "'it''s'");
363    }
364
365    #[test]
366    fn test_supports_fts() {
367        assert!(!dialect().supports_fts());
368    }
369
370    #[test]
371    fn test_supports_lateral_join() {
372        assert!(!dialect().supports_lateral_join());
373    }
374
375    #[test]
376    fn test_set_session_var_does_not_panic() {
377        let d = dialect();
378        let mut b = SqlBuilder::new();
379        // Should log a warning but not panic
380        d.set_session_var(&mut b, "key", "value");
381        assert_eq!(b.sql(), "");
382    }
383
384    #[test]
385    fn test_sqlite_type_mapping() {
386        assert_eq!(sqlite_type("integer"), "INTEGER");
387        assert_eq!(sqlite_type("bigint"), "INTEGER");
388        assert_eq!(sqlite_type("real"), "REAL");
389        assert_eq!(sqlite_type("float8"), "REAL");
390        assert_eq!(sqlite_type("boolean"), "INTEGER");
391        assert_eq!(sqlite_type("bytea"), "BLOB");
392        assert_eq!(sqlite_type("json"), "TEXT");
393        assert_eq!(sqlite_type("text"), "TEXT");
394        assert_eq!(sqlite_type("varchar"), "TEXT");
395    }
396}
dbrest_sqlite/dialect.rs

dbrest_sqlite/
dialect.rs
