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

polyglot_sql/dialects/
sqlite.rs
