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

polyglot_sql/dialects/
sqlite.rs
