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

polyglot_sql/dialects/
sqlite.rs
