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                    Expression::Var(v) => v.this.to_lowercase(),
358                    Expression::Column(col) if col.table.is_none() => col.name.name.to_lowercase(),
359                    _ => "day".to_string(),
360                };
361
362                // JULIANDAY(first) - JULIANDAY(second)
363                let jd_first = Expression::Function(Box::new(Function::new(
364                    "JULIANDAY".to_string(),
365                    vec![first],
366                )));
367                let jd_second = Expression::Function(Box::new(Function::new(
368                    "JULIANDAY".to_string(),
369                    vec![second],
370                )));
371                let diff = Expression::Sub(Box::new(BinaryOp::new(jd_first, jd_second)));
372                let paren_diff = Expression::Paren(Box::new(crate::expressions::Paren {
373                    this: diff,
374                    trailing_comments: Vec::new(),
375                }));
376
377                // Apply multiplier based on unit
378                let adjusted = match unit_str.as_str() {
379                    "hour" => Expression::Mul(Box::new(BinaryOp::new(
380                        paren_diff,
381                        Expression::Literal(Box::new(crate::expressions::Literal::Number(
382                            "24.0".to_string(),
383                        ))),
384                    ))),
385                    "minute" => Expression::Mul(Box::new(BinaryOp::new(
386                        paren_diff,
387                        Expression::Literal(Box::new(crate::expressions::Literal::Number(
388                            "1440.0".to_string(),
389                        ))),
390                    ))),
391                    "second" => Expression::Mul(Box::new(BinaryOp::new(
392                        paren_diff,
393                        Expression::Literal(Box::new(crate::expressions::Literal::Number(
394                            "86400.0".to_string(),
395                        ))),
396                    ))),
397                    "month" => Expression::Div(Box::new(BinaryOp::new(
398                        paren_diff,
399                        Expression::Literal(Box::new(crate::expressions::Literal::Number(
400                            "30.0".to_string(),
401                        ))),
402                    ))),
403                    "year" => Expression::Div(Box::new(BinaryOp::new(
404                        paren_diff,
405                        Expression::Literal(Box::new(crate::expressions::Literal::Number(
406                            "365.0".to_string(),
407                        ))),
408                    ))),
409                    _ => paren_diff, // day is the default
410                };
411
412                // CAST(... AS INTEGER)
413                Ok(Expression::Cast(Box::new(Cast {
414                    this: adjusted,
415                    to: crate::expressions::DataType::Int {
416                        length: None,
417                        integer_spelling: true,
418                    },
419                    trailing_comments: Vec::new(),
420                    double_colon_syntax: false,
421                    format: None,
422                    default: None,
423                    inferred_type: None,
424                })))
425            }
426
427            // STRFTIME with single arg -> add CURRENT_TIMESTAMP as second arg
428            "STRFTIME" if f.args.len() == 1 => {
429                let mut args = f.args;
430                args.push(Expression::CurrentTimestamp(
431                    crate::expressions::CurrentTimestamp {
432                        precision: None,
433                        sysdate: false,
434                    },
435                ));
436                Ok(Expression::Function(Box::new(Function::new(
437                    "STRFTIME".to_string(),
438                    args,
439                ))))
440            }
441
442            // CONCAT(a, b, ...) -> a || b || ... for SQLite
443            "CONCAT" if f.args.len() >= 2 => {
444                let mut args = f.args;
445                let mut result = args.remove(0);
446                for arg in args {
447                    result = Expression::DPipe(Box::new(crate::expressions::DPipe {
448                        this: Box::new(result),
449                        expression: Box::new(arg),
450                        safe: None,
451                    }));
452                }
453                Ok(result)
454            }
455
456            // TRUNC: SQLite doesn't support decimals arg, strip second arg
457            "TRUNC" if f.args.len() > 1 => Ok(Expression::Function(Box::new(Function::new(
458                "TRUNC".to_string(),
459                vec![f.args[0].clone()],
460            )))),
461
462            // Pass through everything else
463            _ => Ok(Expression::Function(Box::new(f))),
464        }
465    }
466
467    fn transform_aggregate_function(
468        &self,
469        f: Box<crate::expressions::AggregateFunction>,
470    ) -> Result<Expression> {
471        let name_upper = f.name.to_uppercase();
472        match name_upper.as_str() {
473            // COUNT_IF -> SUM(CASE WHEN...)
474            "COUNT_IF" if !f.args.is_empty() => {
475                let condition = f.args.into_iter().next().unwrap();
476                let case_expr = Expression::Case(Box::new(Case {
477                    operand: None,
478                    whens: vec![(condition, Expression::number(1))],
479                    else_: Some(Expression::number(0)),
480                    comments: Vec::new(),
481                    inferred_type: None,
482                }));
483                Ok(Expression::Sum(Box::new(AggFunc {
484                    ignore_nulls: None,
485                    having_max: None,
486                    this: case_expr,
487                    distinct: f.distinct,
488                    filter: f.filter,
489                    order_by: Vec::new(),
490                    name: None,
491                    limit: None,
492                    inferred_type: None,
493                })))
494            }
495
496            // ANY_VALUE -> MAX in SQLite
497            "ANY_VALUE" if !f.args.is_empty() => {
498                let arg = f.args.into_iter().next().unwrap();
499                Ok(Expression::Max(Box::new(AggFunc {
500                    ignore_nulls: None,
501                    having_max: None,
502                    this: arg,
503                    distinct: f.distinct,
504                    filter: f.filter,
505                    order_by: Vec::new(),
506                    name: None,
507                    limit: None,
508                    inferred_type: None,
509                })))
510            }
511
512            // STRING_AGG -> GROUP_CONCAT
513            "STRING_AGG" if !f.args.is_empty() => Ok(Expression::Function(Box::new(
514                Function::new("GROUP_CONCAT".to_string(), f.args),
515            ))),
516
517            // LISTAGG -> GROUP_CONCAT
518            "LISTAGG" if !f.args.is_empty() => Ok(Expression::Function(Box::new(Function::new(
519                "GROUP_CONCAT".to_string(),
520                f.args,
521            )))),
522
523            // ARRAY_AGG -> GROUP_CONCAT (SQLite doesn't have arrays)
524            "ARRAY_AGG" if !f.args.is_empty() => Ok(Expression::Function(Box::new(Function::new(
525                "GROUP_CONCAT".to_string(),
526                f.args,
527            )))),
528
529            // Pass through everything else
530            _ => Ok(Expression::AggregateFunction(f)),
531        }
532    }
533
534    fn transform_cast(&self, c: Cast) -> Result<Expression> {
535        // SQLite has limited type support, map types appropriately
536        // The type mapping is handled in the generator via type_mapping
537        // For now, just pass through
538        Ok(Expression::Cast(Box::new(c)))
539    }
540}
polyglot_sql/dialects/sqlite.rs

polyglot_sql/dialects/
sqlite.rs
