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            // Normalize single-argument PRAGMAs to assignment syntax.
134            Expression::Pragma(mut p) => {
135                if p.value.is_some() || p.args.len() == 1 {
136                    p.use_assignment_syntax = true;
137                }
138                Ok(Expression::Pragma(p))
139            }
140
141            // Generic function transformations
142            Expression::Function(f) => self.transform_function(*f),
143
144            // Generic aggregate function transformations
145            Expression::AggregateFunction(f) => self.transform_aggregate_function(f),
146
147            // Cast transformations for type mapping
148            Expression::Cast(c) => self.transform_cast(*c),
149
150            // Div: SQLite has TYPED_DIVISION - wrap left operand in CAST(AS REAL)
151            Expression::Div(mut op) => {
152                // Don't add CAST AS REAL if either operand is already a float literal
153                let right_is_float = matches!(&op.right, Expression::Literal(lit) if matches!(lit.as_ref(), crate::expressions::Literal::Number(n) if n.contains('.')));
154                let right_is_float_cast = Self::is_float_cast(&op.right);
155                if !Self::is_float_cast(&op.left) && !right_is_float && !right_is_float_cast {
156                    op.left = Expression::Cast(Box::new(crate::expressions::Cast {
157                        this: op.left,
158                        to: crate::expressions::DataType::Float {
159                            precision: None,
160                            scale: None,
161                            real_spelling: true,
162                        },
163                        trailing_comments: Vec::new(),
164                        double_colon_syntax: false,
165                        format: None,
166                        default: None,
167                        inferred_type: None,
168                    }));
169                }
170                Ok(Expression::Div(op))
171            }
172
173            // Pass through everything else
174            _ => Ok(expr),
175        }
176    }
177}
178
179impl SQLiteDialect {
180    /// Check if an expression is already a CAST to a float type
181    fn is_float_cast(expr: &Expression) -> bool {
182        if let Expression::Cast(cast) = expr {
183            match &cast.to {
184                crate::expressions::DataType::Double { .. }
185                | crate::expressions::DataType::Float { .. } => true,
186                crate::expressions::DataType::Custom { name } => {
187                    name.eq_ignore_ascii_case("REAL") || name.eq_ignore_ascii_case("DOUBLE")
188                }
189                _ => false,
190            }
191        } else {
192            false
193        }
194    }
195
196    fn transform_function(&self, f: Function) -> Result<Expression> {
197        let name_upper = f.name.to_uppercase();
198        match name_upper.as_str() {
199            // LIKE(pattern, string) -> string LIKE pattern (SQLite function form)
200            "LIKE" if f.args.len() == 2 => {
201                let mut args = f.args;
202                let pattern = args.remove(0);
203                let string = args.remove(0);
204                // Swap: string LIKE pattern
205                Ok(Expression::Like(Box::new(LikeOp::new(string, pattern))))
206            }
207            // LIKE(pattern, string, escape) -> string LIKE pattern ESCAPE escape
208            "LIKE" if f.args.len() == 3 => {
209                let mut args = f.args;
210                let pattern = args.remove(0);
211                let string = args.remove(0);
212                let escape = args.remove(0);
213                Ok(Expression::Like(Box::new(LikeOp {
214                    left: string,
215                    right: pattern,
216                    escape: Some(escape),
217                    quantifier: None,
218                    inferred_type: None,
219                })))
220            }
221            // GLOB(pattern, string) -> string GLOB pattern (SQLite function form)
222            "GLOB" if f.args.len() == 2 => {
223                let mut args = f.args;
224                let pattern = args.remove(0);
225                let string = args.remove(0);
226                // Swap: string GLOB pattern
227                Ok(Expression::Glob(Box::new(BinaryOp::new(string, pattern))))
228            }
229            // NVL -> IFNULL
230            "NVL" if f.args.len() == 2 => {
231                let mut args = f.args;
232                let expr1 = args.remove(0);
233                let expr2 = args.remove(0);
234                Ok(Expression::IfNull(Box::new(BinaryFunc {
235                    original_name: None,
236                    this: expr1,
237                    expression: expr2,
238                    inferred_type: None,
239                })))
240            }
241
242            // COALESCE stays as COALESCE (native to SQLite)
243            "COALESCE" => Ok(Expression::Coalesce(Box::new(VarArgFunc {
244                original_name: None,
245                expressions: f.args,
246                inferred_type: None,
247            }))),
248
249            // RAND -> RANDOM in SQLite
250            "RAND" => Ok(Expression::Function(Box::new(Function::new(
251                "RANDOM".to_string(),
252                vec![],
253            )))),
254
255            // CHR -> CHAR in SQLite
256            "CHR" if f.args.len() == 1 => Ok(Expression::Function(Box::new(Function::new(
257                "CHAR".to_string(),
258                f.args,
259            )))),
260
261            // POSITION -> INSTR in SQLite (with swapped arguments)
262            "POSITION" if f.args.len() == 2 => {
263                let mut args = f.args;
264                let substring = args.remove(0);
265                let string = args.remove(0);
266                // INSTR(string, substring) - note: argument order is reversed from POSITION
267                Ok(Expression::Function(Box::new(Function::new(
268                    "INSTR".to_string(),
269                    vec![string, substring],
270                ))))
271            }
272
273            // STRPOS -> INSTR in SQLite (with swapped arguments)
274            "STRPOS" if f.args.len() == 2 => {
275                let mut args = f.args;
276                let string = args.remove(0);
277                let substring = args.remove(0);
278                // INSTR(string, substring)
279                Ok(Expression::Function(Box::new(Function::new(
280                    "INSTR".to_string(),
281                    vec![string, substring],
282                ))))
283            }
284
285            // CHARINDEX -> INSTR in SQLite
286            "CHARINDEX" if f.args.len() >= 2 => {
287                let mut args = f.args;
288                let substring = args.remove(0);
289                let string = args.remove(0);
290                // INSTR(string, substring)
291                Ok(Expression::Function(Box::new(Function::new(
292                    "INSTR".to_string(),
293                    vec![string, substring],
294                ))))
295            }
296
297            // LEVENSHTEIN -> EDITDIST3 in SQLite
298            "LEVENSHTEIN" if !f.args.is_empty() => Ok(Expression::Function(Box::new(
299                Function::new("EDITDIST3".to_string(), f.args),
300            ))),
301
302            // GETDATE -> CURRENT_TIMESTAMP
303            "GETDATE" => Ok(Expression::CurrentTimestamp(
304                crate::expressions::CurrentTimestamp {
305                    precision: None,
306                    sysdate: false,
307                },
308            )),
309
310            // NOW -> CURRENT_TIMESTAMP
311            "NOW" => Ok(Expression::CurrentTimestamp(
312                crate::expressions::CurrentTimestamp {
313                    precision: None,
314                    sysdate: false,
315                },
316            )),
317
318            // CEILING -> CEIL (not supported in SQLite, but we try)
319            "CEILING" if f.args.len() == 1 => Ok(Expression::Ceil(Box::new(CeilFunc {
320                this: f.args.into_iter().next().unwrap(),
321                decimals: None,
322                to: None,
323            }))),
324
325            // LEN -> LENGTH in SQLite
326            "LEN" if f.args.len() == 1 => Ok(Expression::Length(Box::new(UnaryFunc::new(
327                f.args.into_iter().next().unwrap(),
328            )))),
329
330            // SUBSTRING is native to SQLite (keep as-is)
331            "SUBSTRING" => Ok(Expression::Function(Box::new(Function::new(
332                "SUBSTRING".to_string(),
333                f.args,
334            )))),
335
336            // STRING_AGG -> GROUP_CONCAT in SQLite
337            "STRING_AGG" if !f.args.is_empty() => Ok(Expression::Function(Box::new(
338                Function::new("GROUP_CONCAT".to_string(), f.args),
339            ))),
340
341            // LISTAGG -> GROUP_CONCAT in SQLite
342            "LISTAGG" if !f.args.is_empty() => Ok(Expression::Function(Box::new(Function::new(
343                "GROUP_CONCAT".to_string(),
344                f.args,
345            )))),
346
347            // DATEDIFF(a, b, unit_string) -> JULIANDAY arithmetic for SQLite
348            "DATEDIFF" | "DATE_DIFF" if f.args.len() == 3 => {
349                let mut args = f.args;
350                let first = args.remove(0); // date1
351                let second = args.remove(0); // date2
352                let unit_expr = args.remove(0); // unit string like 'day'
353
354                // Extract unit string
355                let unit_str = match &unit_expr {
356                    Expression::Literal(lit)
357                        if matches!(lit.as_ref(), crate::expressions::Literal::String(_)) =>
358                    {
359                        let crate::expressions::Literal::String(s) = lit.as_ref() else {
360                            unreachable!()
361                        };
362                        s.to_lowercase()
363                    }
364                    Expression::Identifier(id) => id.name.to_lowercase(),
365                    Expression::Var(v) => v.this.to_lowercase(),
366                    Expression::Column(col) if col.table.is_none() => col.name.name.to_lowercase(),
367                    _ => "day".to_string(),
368                };
369
370                // JULIANDAY(first) - JULIANDAY(second)
371                let jd_first = Expression::Function(Box::new(Function::new(
372                    "JULIANDAY".to_string(),
373                    vec![first],
374                )));
375                let jd_second = Expression::Function(Box::new(Function::new(
376                    "JULIANDAY".to_string(),
377                    vec![second],
378                )));
379                let diff = Expression::Sub(Box::new(BinaryOp::new(jd_first, jd_second)));
380                let paren_diff = Expression::Paren(Box::new(crate::expressions::Paren {
381                    this: diff,
382                    trailing_comments: Vec::new(),
383                }));
384
385                // Apply multiplier based on unit
386                let adjusted = match unit_str.as_str() {
387                    "hour" => Expression::Mul(Box::new(BinaryOp::new(
388                        paren_diff,
389                        Expression::Literal(Box::new(crate::expressions::Literal::Number(
390                            "24.0".to_string(),
391                        ))),
392                    ))),
393                    "minute" => Expression::Mul(Box::new(BinaryOp::new(
394                        paren_diff,
395                        Expression::Literal(Box::new(crate::expressions::Literal::Number(
396                            "1440.0".to_string(),
397                        ))),
398                    ))),
399                    "second" => Expression::Mul(Box::new(BinaryOp::new(
400                        paren_diff,
401                        Expression::Literal(Box::new(crate::expressions::Literal::Number(
402                            "86400.0".to_string(),
403                        ))),
404                    ))),
405                    "month" => Expression::Div(Box::new(BinaryOp::new(
406                        paren_diff,
407                        Expression::Literal(Box::new(crate::expressions::Literal::Number(
408                            "30.0".to_string(),
409                        ))),
410                    ))),
411                    "year" => Expression::Div(Box::new(BinaryOp::new(
412                        paren_diff,
413                        Expression::Literal(Box::new(crate::expressions::Literal::Number(
414                            "365.0".to_string(),
415                        ))),
416                    ))),
417                    _ => paren_diff, // day is the default
418                };
419
420                // CAST(... AS INTEGER)
421                Ok(Expression::Cast(Box::new(Cast {
422                    this: adjusted,
423                    to: crate::expressions::DataType::Int {
424                        length: None,
425                        integer_spelling: true,
426                    },
427                    trailing_comments: Vec::new(),
428                    double_colon_syntax: false,
429                    format: None,
430                    default: None,
431                    inferred_type: None,
432                })))
433            }
434
435            // STRFTIME with single arg -> add CURRENT_TIMESTAMP as second arg
436            "STRFTIME" if f.args.len() == 1 => {
437                let mut args = f.args;
438                args.push(Expression::CurrentTimestamp(
439                    crate::expressions::CurrentTimestamp {
440                        precision: None,
441                        sysdate: false,
442                    },
443                ));
444                Ok(Expression::Function(Box::new(Function::new(
445                    "STRFTIME".to_string(),
446                    args,
447                ))))
448            }
449
450            // CONCAT(a, b, ...) -> a || b || ... for SQLite
451            "CONCAT" if f.args.len() >= 2 => {
452                let mut args = f.args;
453                let mut result = args.remove(0);
454                for arg in args {
455                    result = Expression::DPipe(Box::new(crate::expressions::DPipe {
456                        this: Box::new(result),
457                        expression: Box::new(arg),
458                        safe: None,
459                    }));
460                }
461                Ok(result)
462            }
463
464            // TRUNC: SQLite doesn't support decimals arg, strip second arg
465            "TRUNC" if f.args.len() > 1 => Ok(Expression::Function(Box::new(Function::new(
466                "TRUNC".to_string(),
467                vec![f.args[0].clone()],
468            )))),
469
470            // Pass through everything else
471            _ => Ok(Expression::Function(Box::new(f))),
472        }
473    }
474
475    fn transform_aggregate_function(
476        &self,
477        f: Box<crate::expressions::AggregateFunction>,
478    ) -> Result<Expression> {
479        let name_upper = f.name.to_uppercase();
480        match name_upper.as_str() {
481            // COUNT_IF -> SUM(CASE WHEN...)
482            "COUNT_IF" if !f.args.is_empty() => {
483                let condition = f.args.into_iter().next().unwrap();
484                let case_expr = Expression::Case(Box::new(Case {
485                    operand: None,
486                    whens: vec![(condition, Expression::number(1))],
487                    else_: Some(Expression::number(0)),
488                    comments: Vec::new(),
489                    inferred_type: None,
490                }));
491                Ok(Expression::Sum(Box::new(AggFunc {
492                    ignore_nulls: None,
493                    having_max: None,
494                    this: case_expr,
495                    distinct: f.distinct,
496                    filter: f.filter,
497                    order_by: Vec::new(),
498                    name: None,
499                    limit: None,
500                    inferred_type: None,
501                })))
502            }
503
504            // ANY_VALUE -> MAX in SQLite
505            "ANY_VALUE" if !f.args.is_empty() => {
506                let arg = f.args.into_iter().next().unwrap();
507                Ok(Expression::Max(Box::new(AggFunc {
508                    ignore_nulls: None,
509                    having_max: None,
510                    this: arg,
511                    distinct: f.distinct,
512                    filter: f.filter,
513                    order_by: Vec::new(),
514                    name: None,
515                    limit: None,
516                    inferred_type: None,
517                })))
518            }
519
520            // STRING_AGG -> GROUP_CONCAT
521            "STRING_AGG" if !f.args.is_empty() => Ok(Expression::Function(Box::new(
522                Function::new("GROUP_CONCAT".to_string(), f.args),
523            ))),
524
525            // LISTAGG -> GROUP_CONCAT
526            "LISTAGG" if !f.args.is_empty() => Ok(Expression::Function(Box::new(Function::new(
527                "GROUP_CONCAT".to_string(),
528                f.args,
529            )))),
530
531            // ARRAY_AGG -> GROUP_CONCAT (SQLite doesn't have arrays)
532            "ARRAY_AGG" if !f.args.is_empty() => Ok(Expression::Function(Box::new(Function::new(
533                "GROUP_CONCAT".to_string(),
534                f.args,
535            )))),
536
537            // Pass through everything else
538            _ => Ok(Expression::AggregateFunction(f)),
539        }
540    }
541
542    fn transform_cast(&self, c: Cast) -> Result<Expression> {
543        // SQLite has limited type support, map types appropriately
544        // The type mapping is handled in the generator via type_mapping
545        // For now, just pass through
546        Ok(Expression::Cast(Box::new(c)))
547    }
548}
polyglot_sql/dialects/sqlite.rs

polyglot_sql/dialects/
sqlite.rs
