polyglot_sql/dialects/
sqlite.rs

1//! SQLite Dialect
2//!
3//! SQLite-specific transformations based on sqlglot patterns.
4
5use super::{DialectImpl, DialectType};
6use crate::error::Result;
7use crate::expressions::{
8    AggFunc, BinaryFunc, BinaryOp, Case, Cast, CeilFunc, Expression, Function, LikeOp, UnaryFunc,
9    VarArgFunc,
10};
11use crate::generator::GeneratorConfig;
12use crate::tokens::TokenizerConfig;
13
14/// SQLite dialect
15pub struct SQLiteDialect;
16
17impl DialectImpl for SQLiteDialect {
18    fn dialect_type(&self) -> DialectType {
19        DialectType::SQLite
20    }
21
22    fn tokenizer_config(&self) -> TokenizerConfig {
23        let mut config = TokenizerConfig::default();
24        // SQLite supports multiple identifier quote styles
25        config.identifiers.insert('"', '"');
26        config.identifiers.insert('[', ']');
27        config.identifiers.insert('`', '`');
28        // SQLite does NOT support nested comments
29        config.nested_comments = false;
30        // SQLite supports 0x/0X hex number literals (e.g., 0XCC -> x'CC')
31        config.hex_number_strings = true;
32        config
33    }
34
35    fn generator_config(&self) -> GeneratorConfig {
36        use crate::generator::IdentifierQuoteStyle;
37        GeneratorConfig {
38            identifier_quote: '"',
39            identifier_quote_style: IdentifierQuoteStyle::DOUBLE_QUOTE,
40            dialect: Some(DialectType::SQLite),
41            // SQLite uses comma syntax for JSON_OBJECT: JSON_OBJECT('key', value)
42            json_key_value_pair_sep: ",",
43            // SQLite doesn't support table alias columns: t AS t(c1, c2)
44            supports_table_alias_columns: false,
45            ..Default::default()
46        }
47    }
48
49    fn transform_expr(&self, expr: Expression) -> Result<Expression> {
50        match expr {
51            // IFNULL is native to SQLite, but we also support COALESCE
52            Expression::Nvl(f) => Ok(Expression::IfNull(f)),
53
54            // TryCast -> CAST (SQLite doesn't support TRY_CAST)
55            Expression::TryCast(c) => Ok(Expression::Cast(c)),
56
57            // SafeCast -> CAST (SQLite doesn't support safe casts)
58            Expression::SafeCast(c) => Ok(Expression::Cast(c)),
59
60            // RAND -> RANDOM in SQLite
61            Expression::Rand(r) => {
62                // SQLite's RANDOM() doesn't take a seed argument
63                let _ = r.seed; // Ignore seed
64                Ok(Expression::Function(Box::new(Function::new(
65                    "RANDOM".to_string(),
66                    vec![],
67                ))))
68            }
69
70            // RANDOM expression -> RANDOM() function
71            Expression::Random(_) => Ok(Expression::Function(Box::new(Function::new(
72                "RANDOM".to_string(),
73                vec![],
74            )))),
75
76            // ILike -> LOWER() LIKE LOWER() (SQLite doesn't support ILIKE)
77            Expression::ILike(op) => {
78                let lower_left = Expression::Lower(Box::new(UnaryFunc::new(op.left.clone())));
79                let lower_right = Expression::Lower(Box::new(UnaryFunc::new(op.right.clone())));
80                Ok(Expression::Like(Box::new(LikeOp {
81                    left: lower_left,
82                    right: lower_right,
83                    escape: op.escape,
84                    quantifier: op.quantifier.clone(),
85                    inferred_type: None,
86                })))
87            }
88
89            // CountIf -> SUM(IIF(condition, 1, 0))
90            Expression::CountIf(f) => {
91                let iif_expr = Expression::Function(Box::new(Function::new(
92                    "IIF".to_string(),
93                    vec![f.this.clone(), Expression::number(1), Expression::number(0)],
94                )));
95                Ok(Expression::Sum(Box::new(AggFunc {
96                    ignore_nulls: None,
97                    having_max: None,
98                    this: iif_expr,
99                    distinct: f.distinct,
100                    filter: f.filter,
101                    order_by: Vec::new(),
102                    name: None,
103                    limit: None,
104                    inferred_type: None,
105                })))
106            }
107
108            // UNNEST -> not supported in SQLite, pass through
109            Expression::Unnest(_) => Ok(expr),
110
111            // EXPLODE -> not supported in SQLite, pass through
112            Expression::Explode(_) => Ok(expr),
113
114            // Concat expressions -> use || operator (handled in generator)
115            Expression::Concat(c) => {
116                // SQLite uses || for concatenation
117                // We'll keep the Concat expression and let the generator handle it
118                Ok(Expression::Concat(c))
119            }
120
121            // IfFunc -> IIF in SQLite
122            Expression::IfFunc(f) => {
123                let mut args = vec![f.condition, f.true_value];
124                if let Some(false_val) = f.false_value {
125                    args.push(false_val);
126                }
127                Ok(Expression::Function(Box::new(Function::new(
128                    "IIF".to_string(),
129                    args,
130                ))))
131            }
132
133            // Generic function transformations
134            Expression::Function(f) => self.transform_function(*f),
135
136            // Generic aggregate function transformations
137            Expression::AggregateFunction(f) => self.transform_aggregate_function(f),
138
139            // Cast transformations for type mapping
140            Expression::Cast(c) => self.transform_cast(*c),
141
142            // Div: SQLite has TYPED_DIVISION - wrap left operand in CAST(AS REAL)
143            Expression::Div(mut op) => {
144                // Don't add CAST AS REAL if either operand is already a float literal
145                let right_is_float = matches!(&op.right, Expression::Literal(lit) if matches!(lit.as_ref(), crate::expressions::Literal::Number(n) if n.contains('.')));
146                let right_is_float_cast = Self::is_float_cast(&op.right);
147                if !Self::is_float_cast(&op.left) && !right_is_float && !right_is_float_cast {
148                    op.left = Expression::Cast(Box::new(crate::expressions::Cast {
149                        this: op.left,
150                        to: crate::expressions::DataType::Float {
151                            precision: None,
152                            scale: None,
153                            real_spelling: true,
154                        },
155                        trailing_comments: Vec::new(),
156                        double_colon_syntax: false,
157                        format: None,
158                        default: None,
159                        inferred_type: None,
160                    }));
161                }
162                Ok(Expression::Div(op))
163            }
164
165            // Pass through everything else
166            _ => Ok(expr),
167        }
168    }
169}
170
171impl SQLiteDialect {
172    /// Check if an expression is already a CAST to a float type
173    fn is_float_cast(expr: &Expression) -> bool {
174        if let Expression::Cast(cast) = expr {
175            match &cast.to {
176                crate::expressions::DataType::Double { .. }
177                | crate::expressions::DataType::Float { .. } => true,
178                crate::expressions::DataType::Custom { name } => {
179                    name.eq_ignore_ascii_case("REAL") || name.eq_ignore_ascii_case("DOUBLE")
180                }
181                _ => false,
182            }
183        } else {
184            false
185        }
186    }
187
188    fn transform_function(&self, f: Function) -> Result<Expression> {
189        let name_upper = f.name.to_uppercase();
190        match name_upper.as_str() {
191            // LIKE(pattern, string) -> string LIKE pattern (SQLite function form)
192            "LIKE" if f.args.len() == 2 => {
193                let mut args = f.args;
194                let pattern = args.remove(0);
195                let string = args.remove(0);
196                // Swap: string LIKE pattern
197                Ok(Expression::Like(Box::new(LikeOp::new(string, pattern))))
198            }
199            // LIKE(pattern, string, escape) -> string LIKE pattern ESCAPE escape
200            "LIKE" if f.args.len() == 3 => {
201                let mut args = f.args;
202                let pattern = args.remove(0);
203                let string = args.remove(0);
204                let escape = args.remove(0);
205                Ok(Expression::Like(Box::new(LikeOp {
206                    left: string,
207                    right: pattern,
208                    escape: Some(escape),
209                    quantifier: None,
210                    inferred_type: None,
211                })))
212            }
213            // GLOB(pattern, string) -> string GLOB pattern (SQLite function form)
214            "GLOB" if f.args.len() == 2 => {
215                let mut args = f.args;
216                let pattern = args.remove(0);
217                let string = args.remove(0);
218                // Swap: string GLOB pattern
219                Ok(Expression::Glob(Box::new(BinaryOp::new(string, pattern))))
220            }
221            // NVL -> IFNULL
222            "NVL" if f.args.len() == 2 => {
223                let mut args = f.args;
224                let expr1 = args.remove(0);
225                let expr2 = args.remove(0);
226                Ok(Expression::IfNull(Box::new(BinaryFunc {
227                    original_name: None,
228                    this: expr1,
229                    expression: expr2,
230                    inferred_type: None,
231                })))
232            }
233
234            // COALESCE stays as COALESCE (native to SQLite)
235            "COALESCE" => Ok(Expression::Coalesce(Box::new(VarArgFunc {
236                original_name: None,
237                expressions: f.args,
238                inferred_type: None,
239            }))),
240
241            // RAND -> RANDOM in SQLite
242            "RAND" => Ok(Expression::Function(Box::new(Function::new(
243                "RANDOM".to_string(),
244                vec![],
245            )))),
246
247            // CHR -> CHAR in SQLite
248            "CHR" if f.args.len() == 1 => Ok(Expression::Function(Box::new(Function::new(
249                "CHAR".to_string(),
250                f.args,
251            )))),
252
253            // POSITION -> INSTR in SQLite (with swapped arguments)
254            "POSITION" if f.args.len() == 2 => {
255                let mut args = f.args;
256                let substring = args.remove(0);
257                let string = args.remove(0);
258                // INSTR(string, substring) - note: argument order is reversed from POSITION
259                Ok(Expression::Function(Box::new(Function::new(
260                    "INSTR".to_string(),
261                    vec![string, substring],
262                ))))
263            }
264
265            // STRPOS -> INSTR in SQLite (with swapped arguments)
266            "STRPOS" if f.args.len() == 2 => {
267                let mut args = f.args;
268                let string = args.remove(0);
269                let substring = args.remove(0);
270                // INSTR(string, substring)
271                Ok(Expression::Function(Box::new(Function::new(
272                    "INSTR".to_string(),
273                    vec![string, substring],
274                ))))
275            }
276
277            // CHARINDEX -> INSTR in SQLite
278            "CHARINDEX" if f.args.len() >= 2 => {
279                let mut args = f.args;
280                let substring = args.remove(0);
281                let string = args.remove(0);
282                // INSTR(string, substring)
283                Ok(Expression::Function(Box::new(Function::new(
284                    "INSTR".to_string(),
285                    vec![string, substring],
286                ))))
287            }
288
289            // LEVENSHTEIN -> EDITDIST3 in SQLite
290            "LEVENSHTEIN" if !f.args.is_empty() => Ok(Expression::Function(Box::new(
291                Function::new("EDITDIST3".to_string(), f.args),
292            ))),
293
294            // GETDATE -> CURRENT_TIMESTAMP
295            "GETDATE" => Ok(Expression::CurrentTimestamp(
296                crate::expressions::CurrentTimestamp {
297                    precision: None,
298                    sysdate: false,
299                },
300            )),
301
302            // NOW -> CURRENT_TIMESTAMP
303            "NOW" => Ok(Expression::CurrentTimestamp(
304                crate::expressions::CurrentTimestamp {
305                    precision: None,
306                    sysdate: false,
307                },
308            )),
309
310            // CEILING -> CEIL (not supported in SQLite, but we try)
311            "CEILING" if f.args.len() == 1 => Ok(Expression::Ceil(Box::new(CeilFunc {
312                this: f.args.into_iter().next().unwrap(),
313                decimals: None,
314                to: None,
315            }))),
316
317            // LEN -> LENGTH in SQLite
318            "LEN" if f.args.len() == 1 => Ok(Expression::Length(Box::new(UnaryFunc::new(
319                f.args.into_iter().next().unwrap(),
320            )))),
321
322            // SUBSTRING is native to SQLite (keep as-is)
323            "SUBSTRING" => Ok(Expression::Function(Box::new(Function::new(
324                "SUBSTRING".to_string(),
325                f.args,
326            )))),
327
328            // STRING_AGG -> GROUP_CONCAT in SQLite
329            "STRING_AGG" if !f.args.is_empty() => Ok(Expression::Function(Box::new(
330                Function::new("GROUP_CONCAT".to_string(), f.args),
331            ))),
332
333            // LISTAGG -> GROUP_CONCAT in SQLite
334            "LISTAGG" if !f.args.is_empty() => Ok(Expression::Function(Box::new(Function::new(
335                "GROUP_CONCAT".to_string(),
336                f.args,
337            )))),
338
339            // DATEDIFF(a, b, unit_string) -> JULIANDAY arithmetic for SQLite
340            "DATEDIFF" | "DATE_DIFF" if f.args.len() == 3 => {
341                let mut args = f.args;
342                let first = args.remove(0); // date1
343                let second = args.remove(0); // date2
344                let unit_expr = args.remove(0); // unit string like 'day'
345
346                // Extract unit string
347                let unit_str = match &unit_expr {
348                    Expression::Literal(lit)
349                        if matches!(lit.as_ref(), crate::expressions::Literal::String(_)) =>
350                    {
351                        let crate::expressions::Literal::String(s) = lit.as_ref() else {
352                            unreachable!()
353                        };
354                        s.to_lowercase()
355                    }
356                    Expression::Identifier(id) => id.name.to_lowercase(),
357                    _ => "day".to_string(),
358                };
359
360                // JULIANDAY(first) - JULIANDAY(second)
361                let jd_first = Expression::Function(Box::new(Function::new(
362                    "JULIANDAY".to_string(),
363                    vec![first],
364                )));
365                let jd_second = Expression::Function(Box::new(Function::new(
366                    "JULIANDAY".to_string(),
367                    vec![second],
368                )));
369                let diff = Expression::Sub(Box::new(BinaryOp::new(jd_first, jd_second)));
370                let paren_diff = Expression::Paren(Box::new(crate::expressions::Paren {
371                    this: diff,
372                    trailing_comments: Vec::new(),
373                }));
374
375                // Apply multiplier based on unit
376                let adjusted = match unit_str.as_str() {
377                    "hour" => Expression::Mul(Box::new(BinaryOp::new(
378                        paren_diff,
379                        Expression::Literal(Box::new(crate::expressions::Literal::Number(
380                            "24.0".to_string(),
381                        ))),
382                    ))),
383                    "minute" => Expression::Mul(Box::new(BinaryOp::new(
384                        paren_diff,
385                        Expression::Literal(Box::new(crate::expressions::Literal::Number(
386                            "1440.0".to_string(),
387                        ))),
388                    ))),
389                    "second" => Expression::Mul(Box::new(BinaryOp::new(
390                        paren_diff,
391                        Expression::Literal(Box::new(crate::expressions::Literal::Number(
392                            "86400.0".to_string(),
393                        ))),
394                    ))),
395                    "month" => Expression::Div(Box::new(BinaryOp::new(
396                        paren_diff,
397                        Expression::Literal(Box::new(crate::expressions::Literal::Number(
398                            "30.0".to_string(),
399                        ))),
400                    ))),
401                    "year" => Expression::Div(Box::new(BinaryOp::new(
402                        paren_diff,
403                        Expression::Literal(Box::new(crate::expressions::Literal::Number(
404                            "365.0".to_string(),
405                        ))),
406                    ))),
407                    _ => paren_diff, // day is the default
408                };
409
410                // CAST(... AS INTEGER)
411                Ok(Expression::Cast(Box::new(Cast {
412                    this: adjusted,
413                    to: crate::expressions::DataType::Int {
414                        length: None,
415                        integer_spelling: true,
416                    },
417                    trailing_comments: Vec::new(),
418                    double_colon_syntax: false,
419                    format: None,
420                    default: None,
421                    inferred_type: None,
422                })))
423            }
424
425            // STRFTIME with single arg -> add CURRENT_TIMESTAMP as second arg
426            "STRFTIME" if f.args.len() == 1 => {
427                let mut args = f.args;
428                args.push(Expression::CurrentTimestamp(
429                    crate::expressions::CurrentTimestamp {
430                        precision: None,
431                        sysdate: false,
432                    },
433                ));
434                Ok(Expression::Function(Box::new(Function::new(
435                    "STRFTIME".to_string(),
436                    args,
437                ))))
438            }
439
440            // CONCAT(a, b, ...) -> a || b || ... for SQLite
441            "CONCAT" if f.args.len() >= 2 => {
442                let mut args = f.args;
443                let mut result = args.remove(0);
444                for arg in args {
445                    result = Expression::DPipe(Box::new(crate::expressions::DPipe {
446                        this: Box::new(result),
447                        expression: Box::new(arg),
448                        safe: None,
449                    }));
450                }
451                Ok(result)
452            }
453
454            // TRUNC: SQLite doesn't support decimals arg, strip second arg
455            "TRUNC" if f.args.len() > 1 => Ok(Expression::Function(Box::new(Function::new(
456                "TRUNC".to_string(),
457                vec![f.args[0].clone()],
458            )))),
459
460            // Pass through everything else
461            _ => Ok(Expression::Function(Box::new(f))),
462        }
463    }
464
465    fn transform_aggregate_function(
466        &self,
467        f: Box<crate::expressions::AggregateFunction>,
468    ) -> Result<Expression> {
469        let name_upper = f.name.to_uppercase();
470        match name_upper.as_str() {
471            // COUNT_IF -> SUM(CASE WHEN...)
472            "COUNT_IF" if !f.args.is_empty() => {
473                let condition = f.args.into_iter().next().unwrap();
474                let case_expr = Expression::Case(Box::new(Case {
475                    operand: None,
476                    whens: vec![(condition, Expression::number(1))],
477                    else_: Some(Expression::number(0)),
478                    comments: Vec::new(),
479                    inferred_type: None,
480                }));
481                Ok(Expression::Sum(Box::new(AggFunc {
482                    ignore_nulls: None,
483                    having_max: None,
484                    this: case_expr,
485                    distinct: f.distinct,
486                    filter: f.filter,
487                    order_by: Vec::new(),
488                    name: None,
489                    limit: None,
490                    inferred_type: None,
491                })))
492            }
493
494            // ANY_VALUE -> MAX in SQLite
495            "ANY_VALUE" if !f.args.is_empty() => {
496                let arg = f.args.into_iter().next().unwrap();
497                Ok(Expression::Max(Box::new(AggFunc {
498                    ignore_nulls: None,
499                    having_max: None,
500                    this: arg,
501                    distinct: f.distinct,
502                    filter: f.filter,
503                    order_by: Vec::new(),
504                    name: None,
505                    limit: None,
506                    inferred_type: None,
507                })))
508            }
509
510            // STRING_AGG -> GROUP_CONCAT
511            "STRING_AGG" if !f.args.is_empty() => Ok(Expression::Function(Box::new(
512                Function::new("GROUP_CONCAT".to_string(), f.args),
513            ))),
514
515            // LISTAGG -> GROUP_CONCAT
516            "LISTAGG" if !f.args.is_empty() => Ok(Expression::Function(Box::new(Function::new(
517                "GROUP_CONCAT".to_string(),
518                f.args,
519            )))),
520
521            // ARRAY_AGG -> GROUP_CONCAT (SQLite doesn't have arrays)
522            "ARRAY_AGG" if !f.args.is_empty() => Ok(Expression::Function(Box::new(Function::new(
523                "GROUP_CONCAT".to_string(),
524                f.args,
525            )))),
526
527            // Pass through everything else
528            _ => Ok(Expression::AggregateFunction(f)),
529        }
530    }
531
532    fn transform_cast(&self, c: Cast) -> Result<Expression> {
533        // SQLite has limited type support, map types appropriately
534        // The type mapping is handled in the generator via type_mapping
535        // For now, just pass through
536        Ok(Expression::Cast(Box::new(c)))
537    }
538}
polyglot_sql/dialects/sqlite.rs

polyglot_sql/dialects/
sqlite.rs
