mik_sql/
dialect.rs

1//! SQL dialect implementations for Postgres and `SQLite`.
2//!
3//! Each dialect handles the specific syntax differences between databases.
4
5use crate::Value;
6
7/// SQL dialect trait for database-specific syntax.
8pub trait Dialect: Clone + Copy {
9    /// Format a parameter placeholder (e.g., `$1` for Postgres, `?1` for `SQLite`).
10    fn param(&self, idx: usize) -> String;
11
12    /// Format a boolean literal.
13    fn bool_lit(&self, val: bool) -> &'static str;
14
15    /// Format the regex operator and pattern.
16    /// Returns (operator, `should_transform_pattern`).
17    fn regex_op(&self) -> &'static str;
18
19    /// Format an IN clause with multiple values.
20    /// Returns the SQL fragment (e.g., `= ANY($1)` or `IN (?1, ?2)`).
21    fn in_clause(&self, field: &str, values: &[Value], start_idx: usize) -> (String, Vec<Value>);
22
23    /// Format a NOT IN clause.
24    fn not_in_clause(
25        &self,
26        field: &str,
27        values: &[Value],
28        start_idx: usize,
29    ) -> (String, Vec<Value>);
30
31    /// Whether ILIKE is supported natively.
32    fn supports_ilike(&self) -> bool;
33
34    /// Format a STARTS WITH clause (e.g., `LIKE $1 || '%'` or `LIKE ?1 || '%'`).
35    fn starts_with_clause(&self, field: &str, idx: usize) -> String;
36
37    /// Format an ENDS WITH clause (e.g., `LIKE '%' || $1` or `LIKE '%' || ?1`).
38    fn ends_with_clause(&self, field: &str, idx: usize) -> String;
39
40    /// Format a CONTAINS clause (e.g., `LIKE '%' || $1 || '%'` or `LIKE '%' || ?1 || '%'`).
41    fn contains_clause(&self, field: &str, idx: usize) -> String;
42}
43
44/// Postgres dialect.
45#[derive(Debug, Clone, Copy, Default)]
46#[non_exhaustive]
47pub struct Postgres;
48
49impl Dialect for Postgres {
50    #[inline]
51    fn param(&self, idx: usize) -> String {
52        format!("${idx}")
53    }
54
55    #[inline]
56    fn bool_lit(&self, val: bool) -> &'static str {
57        if val { "TRUE" } else { "FALSE" }
58    }
59
60    #[inline]
61    fn regex_op(&self) -> &'static str {
62        "~"
63    }
64
65    fn in_clause(&self, field: &str, values: &[Value], start_idx: usize) -> (String, Vec<Value>) {
66        // Postgres: field = ANY($1) with array parameter
67        let sql = format!("{field} = ANY(${start_idx})");
68        (sql, vec![Value::Array(values.to_vec())])
69    }
70
71    fn not_in_clause(
72        &self,
73        field: &str,
74        values: &[Value],
75        start_idx: usize,
76    ) -> (String, Vec<Value>) {
77        let sql = format!("{field} != ALL(${start_idx})");
78        (sql, vec![Value::Array(values.to_vec())])
79    }
80
81    #[inline]
82    fn supports_ilike(&self) -> bool {
83        true
84    }
85
86    #[inline]
87    fn starts_with_clause(&self, field: &str, idx: usize) -> String {
88        format!("{field} LIKE ${idx} || '%'")
89    }
90
91    #[inline]
92    fn ends_with_clause(&self, field: &str, idx: usize) -> String {
93        format!("{field} LIKE '%' || ${idx}")
94    }
95
96    #[inline]
97    fn contains_clause(&self, field: &str, idx: usize) -> String {
98        format!("{field} LIKE '%' || ${idx} || '%'")
99    }
100}
101
102/// `SQLite` dialect.
103#[derive(Debug, Clone, Copy, Default)]
104#[non_exhaustive]
105pub struct Sqlite;
106
107impl Dialect for Sqlite {
108    #[inline]
109    fn param(&self, idx: usize) -> String {
110        format!("?{idx}")
111    }
112
113    #[inline]
114    fn bool_lit(&self, val: bool) -> &'static str {
115        if val { "1" } else { "0" }
116    }
117
118    #[inline]
119    fn regex_op(&self) -> &'static str {
120        // SQLite doesn't have native regex, fall back to LIKE
121        "LIKE"
122    }
123
124    fn in_clause(&self, field: &str, values: &[Value], start_idx: usize) -> (String, Vec<Value>) {
125        // SQLite: field IN (?1, ?2, ?3) with expanded parameters
126        let placeholders: Vec<String> = (0..values.len())
127            .map(|i| format!("?{}", start_idx + i))
128            .collect();
129        let sql = format!("{} IN ({})", field, placeholders.join(", "));
130        (sql, values.to_vec())
131    }
132
133    fn not_in_clause(
134        &self,
135        field: &str,
136        values: &[Value],
137        start_idx: usize,
138    ) -> (String, Vec<Value>) {
139        let placeholders: Vec<String> = (0..values.len())
140            .map(|i| format!("?{}", start_idx + i))
141            .collect();
142        let sql = format!("{} NOT IN ({})", field, placeholders.join(", "));
143        (sql, values.to_vec())
144    }
145
146    #[inline]
147    fn supports_ilike(&self) -> bool {
148        // SQLite LIKE is case-insensitive for ASCII by default
149        false
150    }
151
152    #[inline]
153    fn starts_with_clause(&self, field: &str, idx: usize) -> String {
154        format!("{field} LIKE ?{idx} || '%'")
155    }
156
157    #[inline]
158    fn ends_with_clause(&self, field: &str, idx: usize) -> String {
159        format!("{field} LIKE '%' || ?{idx}")
160    }
161
162    #[inline]
163    fn contains_clause(&self, field: &str, idx: usize) -> String {
164        format!("{field} LIKE '%' || ?{idx} || '%'")
165    }
166}
167
168#[cfg(test)]
169mod tests {
170    use super::*;
171
172    #[test]
173    fn test_postgres_params() {
174        let pg = Postgres;
175        assert_eq!(pg.param(1), "$1");
176        assert_eq!(pg.param(10), "$10");
177    }
178
179    #[test]
180    fn test_sqlite_params() {
181        let sqlite = Sqlite;
182        assert_eq!(sqlite.param(1), "?1");
183        assert_eq!(sqlite.param(10), "?10");
184    }
185
186    #[test]
187    fn test_postgres_bool() {
188        let pg = Postgres;
189        assert_eq!(pg.bool_lit(true), "TRUE");
190        assert_eq!(pg.bool_lit(false), "FALSE");
191    }
192
193    #[test]
194    fn test_sqlite_bool() {
195        let sqlite = Sqlite;
196        assert_eq!(sqlite.bool_lit(true), "1");
197        assert_eq!(sqlite.bool_lit(false), "0");
198    }
199
200    #[test]
201    fn test_postgres_in_clause() {
202        let pg = Postgres;
203        let values = vec![Value::String("a".into()), Value::String("b".into())];
204        let (sql, params) = pg.in_clause("status", &values, 1);
205
206        assert_eq!(sql, "status = ANY($1)");
207        assert_eq!(params.len(), 1); // Single array param
208    }
209
210    #[test]
211    fn test_sqlite_in_clause() {
212        let sqlite = Sqlite;
213        let values = vec![Value::String("a".into()), Value::String("b".into())];
214        let (sql, params) = sqlite.in_clause("status", &values, 1);
215
216        assert_eq!(sql, "status IN (?1, ?2)");
217        assert_eq!(params.len(), 2); // Expanded params
218    }
219
220    // --- Additional tests for regex_op ---
221
222    #[test]
223    fn test_postgres_regex_op() {
224        let pg = Postgres;
225        assert_eq!(pg.regex_op(), "~");
226    }
227
228    #[test]
229    fn test_sqlite_regex_op() {
230        let sqlite = Sqlite;
231        assert_eq!(sqlite.regex_op(), "LIKE");
232    }
233
234    // --- Tests for supports_ilike ---
235
236    #[test]
237    fn test_postgres_supports_ilike() {
238        let pg = Postgres;
239        assert!(pg.supports_ilike());
240    }
241
242    #[test]
243    fn test_sqlite_supports_ilike() {
244        let sqlite = Sqlite;
245        assert!(!sqlite.supports_ilike());
246    }
247
248    // --- Tests for not_in_clause ---
249
250    #[test]
251    fn test_postgres_not_in_clause() {
252        let pg = Postgres;
253        let values = vec![Value::Int(1), Value::Int(2), Value::Int(3)];
254        let (sql, params) = pg.not_in_clause("id", &values, 1);
255
256        assert_eq!(sql, "id != ALL($1)");
257        assert_eq!(params.len(), 1);
258        let Value::Array(arr) = &params[0] else {
259            panic!("expected Value::Array, got {:?}", params[0])
260        };
261        assert_eq!(arr.len(), 3);
262    }
263
264    #[test]
265    fn test_sqlite_not_in_clause() {
266        let sqlite = Sqlite;
267        let values = vec![Value::Int(1), Value::Int(2), Value::Int(3)];
268        let (sql, params) = sqlite.not_in_clause("id", &values, 1);
269
270        assert_eq!(sql, "id NOT IN (?1, ?2, ?3)");
271        assert_eq!(params.len(), 3);
272    }
273
274    #[test]
275    fn test_sqlite_not_in_clause_with_offset() {
276        let sqlite = Sqlite;
277        let values = vec![Value::String("x".into()), Value::String("y".into())];
278        let (sql, params) = sqlite.not_in_clause("name", &values, 5);
279
280        assert_eq!(sql, "name NOT IN (?5, ?6)");
281        assert_eq!(params.len(), 2);
282    }
283
284    // --- Tests for starts_with_clause ---
285
286    #[test]
287    fn test_postgres_starts_with_clause() {
288        let pg = Postgres;
289        assert_eq!(pg.starts_with_clause("name", 1), "name LIKE $1 || '%'");
290        assert_eq!(pg.starts_with_clause("title", 5), "title LIKE $5 || '%'");
291    }
292
293    #[test]
294    fn test_sqlite_starts_with_clause() {
295        let sqlite = Sqlite;
296        assert_eq!(sqlite.starts_with_clause("name", 1), "name LIKE ?1 || '%'");
297        assert_eq!(
298            sqlite.starts_with_clause("title", 5),
299            "title LIKE ?5 || '%'"
300        );
301    }
302
303    // --- Tests for ends_with_clause ---
304
305    #[test]
306    fn test_postgres_ends_with_clause() {
307        let pg = Postgres;
308        assert_eq!(pg.ends_with_clause("name", 1), "name LIKE '%' || $1");
309        assert_eq!(pg.ends_with_clause("email", 3), "email LIKE '%' || $3");
310    }
311
312    #[test]
313    fn test_sqlite_ends_with_clause() {
314        let sqlite = Sqlite;
315        assert_eq!(sqlite.ends_with_clause("name", 1), "name LIKE '%' || ?1");
316        assert_eq!(sqlite.ends_with_clause("email", 3), "email LIKE '%' || ?3");
317    }
318
319    // --- Tests for contains_clause ---
320
321    #[test]
322    fn test_postgres_contains_clause() {
323        let pg = Postgres;
324        assert_eq!(pg.contains_clause("name", 1), "name LIKE '%' || $1 || '%'");
325        assert_eq!(
326            pg.contains_clause("description", 2),
327            "description LIKE '%' || $2 || '%'"
328        );
329    }
330
331    #[test]
332    fn test_sqlite_contains_clause() {
333        let sqlite = Sqlite;
334        assert_eq!(
335            sqlite.contains_clause("name", 1),
336            "name LIKE '%' || ?1 || '%'"
337        );
338        assert_eq!(
339            sqlite.contains_clause("description", 2),
340            "description LIKE '%' || ?2 || '%'"
341        );
342    }
343
344    // --- Tests for in_clause with different value types ---
345
346    #[test]
347    fn test_postgres_in_clause_with_ints() {
348        let pg = Postgres;
349        let values = vec![Value::Int(1), Value::Int(2), Value::Int(3)];
350        let (sql, params) = pg.in_clause("id", &values, 2);
351
352        assert_eq!(sql, "id = ANY($2)");
353        assert_eq!(params.len(), 1);
354    }
355
356    #[test]
357    fn test_sqlite_in_clause_with_ints() {
358        let sqlite = Sqlite;
359        let values = vec![Value::Int(1), Value::Int(2), Value::Int(3)];
360        let (sql, params) = sqlite.in_clause("id", &values, 2);
361
362        assert_eq!(sql, "id IN (?2, ?3, ?4)");
363        assert_eq!(params.len(), 3);
364    }
365
366    #[test]
367    fn test_sqlite_in_clause_single_value() {
368        let sqlite = Sqlite;
369        let values = vec![Value::Int(42)];
370        let (sql, params) = sqlite.in_clause("id", &values, 1);
371
372        assert_eq!(sql, "id IN (?1)");
373        assert_eq!(params.len(), 1);
374    }
375
376    #[test]
377    fn test_postgres_in_clause_single_value() {
378        let pg = Postgres;
379        let values = vec![Value::String("only".into())];
380        let (sql, params) = pg.in_clause("name", &values, 1);
381
382        assert_eq!(sql, "name = ANY($1)");
383        assert_eq!(params.len(), 1);
384    }
385
386    #[test]
387    fn test_sqlite_in_clause_empty() {
388        let sqlite = Sqlite;
389        let values: Vec<Value> = vec![];
390        let (sql, _params) = sqlite.in_clause("id", &values, 1);
391
392        assert_eq!(sql, "id IN ()");
393    }
394
395    #[test]
396    fn test_postgres_in_clause_empty() {
397        let pg = Postgres;
398        let values: Vec<Value> = vec![];
399        let (sql, params) = pg.in_clause("id", &values, 1);
400
401        assert_eq!(sql, "id = ANY($1)");
402        let Value::Array(arr) = &params[0] else {
403            panic!("expected Value::Array, got {:?}", params[0])
404        };
405        assert!(arr.is_empty());
406    }
407
408    // --- Tests for Default trait ---
409
410    #[test]
411    fn test_postgres_default() {
412        let pg = Postgres;
413        assert_eq!(pg.param(1), "$1");
414    }
415
416    #[test]
417    fn test_sqlite_default() {
418        let sqlite = Sqlite;
419        assert_eq!(sqlite.param(1), "?1");
420    }
421
422    // --- Tests for Clone and Copy ---
423
424    #[test]
425    fn test_postgres_clone_copy() {
426        let pg = Postgres;
427        let pg_clone = pg;
428        let pg_copy = pg;
429        assert_eq!(pg_clone.param(1), pg_copy.param(1));
430    }
431
432    #[test]
433    fn test_sqlite_clone_copy() {
434        let sqlite = Sqlite;
435        let sqlite_clone = sqlite;
436        let sqlite_copy = sqlite;
437        assert_eq!(sqlite_clone.param(1), sqlite_copy.param(1));
438    }
439
440    // --- Tests with various start indices ---
441
442    #[test]
443    fn test_postgres_in_clause_high_index() {
444        let pg = Postgres;
445        let values = vec![Value::Int(1)];
446        let (sql, _) = pg.in_clause("id", &values, 100);
447        assert_eq!(sql, "id = ANY($100)");
448    }
449
450    #[test]
451    fn test_sqlite_in_clause_high_index() {
452        let sqlite = Sqlite;
453        let values = vec![Value::Int(1), Value::Int(2)];
454        let (sql, _) = sqlite.in_clause("id", &values, 100);
455        assert_eq!(sql, "id IN (?100, ?101)");
456    }
457
458    #[test]
459    fn test_sqlite_not_in_clause_high_index() {
460        let sqlite = Sqlite;
461        let values = vec![Value::Int(1), Value::Int(2)];
462        let (sql, _) = sqlite.not_in_clause("id", &values, 50);
463        assert_eq!(sql, "id NOT IN (?50, ?51)");
464    }
465
466    #[test]
467    fn test_postgres_not_in_clause_high_index() {
468        let pg = Postgres;
469        let values = vec![Value::Int(1)];
470        let (sql, _) = pg.not_in_clause("id", &values, 99);
471        assert_eq!(sql, "id != ALL($99)");
472    }
473
474    // --- Tests for Debug trait ---
475
476    #[test]
477    fn test_postgres_debug() {
478        let pg = Postgres;
479        let debug_str = format!("{pg:?}");
480        assert_eq!(debug_str, "Postgres");
481    }
482
483    #[test]
484    fn test_sqlite_debug() {
485        let sqlite = Sqlite;
486        let debug_str = format!("{sqlite:?}");
487        assert_eq!(debug_str, "Sqlite");
488    }
489
490    // --- Tests for Default trait via Default::default() ---
491
492    #[test]
493    #[allow(clippy::default_trait_access)]
494    fn test_postgres_default_trait() {
495        let pg: Postgres = Default::default();
496        assert_eq!(pg.param(1), "$1");
497    }
498
499    #[test]
500    #[allow(clippy::default_trait_access)]
501    fn test_sqlite_default_trait() {
502        let sqlite: Sqlite = Default::default();
503        assert_eq!(sqlite.param(1), "?1");
504    }
505
506    // --- Tests for Clone trait via explicit .clone() ---
507
508    #[test]
509    #[allow(clippy::clone_on_copy)]
510    fn test_postgres_clone_explicit() {
511        let pg = Postgres;
512        let pg_cloned = pg.clone();
513        assert_eq!(pg_cloned.param(1), "$1");
514    }
515
516    #[test]
517    #[allow(clippy::clone_on_copy)]
518    fn test_sqlite_clone_explicit() {
519        let sqlite = Sqlite;
520        let sqlite_cloned = sqlite.clone();
521        assert_eq!(sqlite_cloned.param(1), "?1");
522    }
523
524    // --- Tests for empty not_in_clause ---
525
526    #[test]
527    fn test_sqlite_not_in_clause_empty() {
528        let sqlite = Sqlite;
529        let values: Vec<Value> = vec![];
530        let (sql, params) = sqlite.not_in_clause("id", &values, 1);
531
532        assert_eq!(sql, "id NOT IN ()");
533        assert!(params.is_empty());
534    }
535
536    #[test]
537    fn test_postgres_not_in_clause_empty() {
538        let pg = Postgres;
539        let values: Vec<Value> = vec![];
540        let (sql, params) = pg.not_in_clause("id", &values, 1);
541
542        assert_eq!(sql, "id != ALL($1)");
543        assert_eq!(params.len(), 1);
544        let Value::Array(arr) = &params[0] else {
545            panic!("expected Value::Array, got {:?}", params[0])
546        };
547        assert!(arr.is_empty());
548    }
549}
mik_sql/dialect.rs

mik_sql/
dialect.rs
