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, DataType, DateTimeField, DateTruncFunc,
9    Expression, ExtractFunc, Function, LikeOp, Literal, TrimFunc, TrimPosition, UnaryFunc,
10    VarArgFunc,
11};
12use crate::generator::GeneratorConfig;
13use crate::tokens::TokenizerConfig;
14
15/// SQLite dialect
16pub struct SQLiteDialect;
17
18impl DialectImpl for SQLiteDialect {
19    fn dialect_type(&self) -> DialectType {
20        DialectType::SQLite
21    }
22
23    fn tokenizer_config(&self) -> TokenizerConfig {
24        let mut config = TokenizerConfig::default();
25        // SQLite supports multiple identifier quote styles
26        config.identifiers.insert('"', '"');
27        config.identifiers.insert('[', ']');
28        config.identifiers.insert('`', '`');
29        // SQLite does NOT support nested comments
30        config.nested_comments = false;
31        // SQLite supports 0x/0X hex number literals (e.g., 0XCC -> x'CC')
32        config.hex_number_strings = true;
33        config
34    }
35
36    fn generator_config(&self) -> GeneratorConfig {
37        use crate::generator::IdentifierQuoteStyle;
38        GeneratorConfig {
39            identifier_quote: '"',
40            identifier_quote_style: IdentifierQuoteStyle::DOUBLE_QUOTE,
41            dialect: Some(DialectType::SQLite),
42            // SQLite uses comma syntax for JSON_OBJECT: JSON_OBJECT('key', value)
43            json_key_value_pair_sep: ",",
44            // SQLite doesn't support table alias columns: t AS t(c1, c2)
45            supports_table_alias_columns: false,
46            ..Default::default()
47        }
48    }
49
50    fn transform_expr(&self, expr: Expression) -> Result<Expression> {
51        match expr {
52            // IFNULL is native to SQLite, but we also support COALESCE
53            Expression::Nvl(f) => Ok(Expression::IfNull(f)),
54
55            // TryCast -> CAST (SQLite doesn't support TRY_CAST)
56            Expression::TryCast(c) => Ok(Expression::Cast(c)),
57
58            // SafeCast -> CAST (SQLite doesn't support safe casts)
59            Expression::SafeCast(c) => Ok(Expression::Cast(c)),
60
61            // RAND -> RANDOM in SQLite
62            Expression::Rand(r) => {
63                // SQLite's RANDOM() doesn't take a seed argument
64                let _ = r.seed; // Ignore seed
65                Ok(Expression::Function(Box::new(Function::new(
66                    "RANDOM".to_string(),
67                    vec![],
68                ))))
69            }
70
71            // RANDOM expression -> RANDOM() function
72            Expression::Random(_) => Ok(Expression::Function(Box::new(Function::new(
73                "RANDOM".to_string(),
74                vec![],
75            )))),
76
77            // ILike -> LOWER() LIKE LOWER() (SQLite doesn't support ILIKE)
78            Expression::ILike(op) => {
79                let lower_left = Expression::Lower(Box::new(UnaryFunc::new(op.left.clone())));
80                let lower_right = Expression::Lower(Box::new(UnaryFunc::new(op.right.clone())));
81                Ok(Expression::Like(Box::new(LikeOp {
82                    left: lower_left,
83                    right: lower_right,
84                    escape: op.escape,
85                    quantifier: op.quantifier.clone(),
86                    inferred_type: None,
87                })))
88            }
89
90            // CountIf -> SUM(IIF(condition, 1, 0))
91            Expression::CountIf(f) => {
92                let iif_expr = Expression::Function(Box::new(Function::new(
93                    "IIF".to_string(),
94                    vec![f.this.clone(), Expression::number(1), Expression::number(0)],
95                )));
96                Ok(Expression::Sum(Box::new(AggFunc {
97                    ignore_nulls: None,
98                    having_max: None,
99                    this: iif_expr,
100                    distinct: f.distinct,
101                    filter: f.filter,
102                    order_by: Vec::new(),
103                    name: None,
104                    limit: None,
105                    inferred_type: None,
106                })))
107            }
108
109            // UNNEST -> not supported in SQLite, pass through
110            Expression::Unnest(_) => Ok(expr),
111
112            // EXPLODE -> not supported in SQLite, pass through
113            Expression::Explode(_) => Ok(expr),
114
115            // Concat expressions -> use || operator (handled in generator)
116            Expression::Concat(c) => {
117                // SQLite uses || for concatenation
118                // We'll keep the Concat expression and let the generator handle it
119                Ok(Expression::Concat(c))
120            }
121
122            // IfFunc -> IIF in SQLite
123            Expression::IfFunc(f) => {
124                let mut args = vec![f.condition, f.true_value];
125                if let Some(false_val) = f.false_value {
126                    args.push(false_val);
127                }
128                Ok(Expression::Function(Box::new(Function::new(
129                    "IIF".to_string(),
130                    args,
131                ))))
132            }
133
134            // Normalize single-argument PRAGMAs to assignment syntax.
135            Expression::Pragma(mut p) => {
136                if p.value.is_some() || p.args.len() == 1 {
137                    p.use_assignment_syntax = true;
138                }
139                Ok(Expression::Pragma(p))
140            }
141
142            // PostgreSQL DATE '...' literals are not SQLite syntax.
143            Expression::Literal(lit) if matches!(lit.as_ref(), Literal::Date(_)) => {
144                let Literal::Date(date) = lit.as_ref() else {
145                    unreachable!()
146                };
147                Ok(Self::string_literal(date))
148            }
149
150            // SQLite scalar MIN/MAX are multi-argument equivalents of LEAST/GREATEST.
151            Expression::Least(f) => Ok(Self::function("MIN", f.expressions)),
152            Expression::Greatest(f) => Ok(Self::function("MAX", f.expressions)),
153
154            // PostgreSQL EXTRACT(...) lowers to SQLite strftime() for common units.
155            Expression::Extract(f) => Self::transform_extract(*f),
156
157            // PostgreSQL DATE_TRUNC(...) lowers to date/strftime() for common units.
158            Expression::DateTrunc(f) => Self::transform_date_trunc(*f),
159
160            // SQLite uses comma-call SUBSTRING/SUBSTR syntax, not SQL-standard FROM/FOR.
161            Expression::Substring(mut f) => {
162                f.from_for_syntax = false;
163                Ok(Expression::Substring(f))
164            }
165
166            // SQLite supports LTRIM/RTRIM/TRIM(str, chars), not TRIM(LEADING ... FROM ...).
167            Expression::Trim(f) => Ok(Self::transform_trim(*f)),
168
169            // Strip PostgreSQL's default public schema in SQLite DDL.
170            Expression::CreateTable(mut ct)
171                if ct
172                    .name
173                    .schema
174                    .as_ref()
175                    .is_some_and(|schema| schema.name.eq_ignore_ascii_case("public"))
176                    && ct.name.catalog.is_none() =>
177            {
178                ct.name.schema = None;
179                Ok(Expression::CreateTable(ct))
180            }
181
182            // Generic function transformations
183            Expression::Function(f) => self.transform_function(*f),
184
185            // Generic aggregate function transformations
186            Expression::AggregateFunction(f) => self.transform_aggregate_function(f),
187
188            // Cast transformations for type mapping
189            Expression::Cast(c) => self.transform_cast(*c),
190
191            // Div: SQLite has TYPED_DIVISION - wrap left operand in CAST(AS REAL)
192            Expression::Div(mut op) => {
193                // Don't add CAST AS REAL if either operand is already a float literal
194                let right_is_float = matches!(&op.right, Expression::Literal(lit) if matches!(lit.as_ref(), crate::expressions::Literal::Number(n) if n.contains('.')));
195                let right_is_float_cast = Self::is_float_cast(&op.right);
196                if !Self::is_float_cast(&op.left) && !right_is_float && !right_is_float_cast {
197                    op.left = Expression::Cast(Box::new(crate::expressions::Cast {
198                        this: op.left,
199                        to: crate::expressions::DataType::Float {
200                            precision: None,
201                            scale: None,
202                            real_spelling: true,
203                        },
204                        trailing_comments: Vec::new(),
205                        double_colon_syntax: false,
206                        format: None,
207                        default: None,
208                        inferred_type: None,
209                    }));
210                }
211                Ok(Expression::Div(op))
212            }
213
214            // Pass through everything else
215            _ => Ok(expr),
216        }
217    }
218}
219
220impl SQLiteDialect {
221    fn function(name: &str, args: Vec<Expression>) -> Expression {
222        Expression::Function(Box::new(Function::new(name.to_string(), args)))
223    }
224
225    fn string_literal(value: &str) -> Expression {
226        Expression::Literal(Box::new(Literal::String(value.to_string())))
227    }
228
229    fn strftime(format: &str, expr: Expression) -> Expression {
230        Expression::Function(Box::new(Function::new(
231            "STRFTIME".to_string(),
232            vec![Self::string_literal(format), expr],
233        )))
234    }
235
236    fn cast(expr: Expression, to: DataType) -> Expression {
237        Expression::Cast(Box::new(Cast {
238            this: expr,
239            to,
240            trailing_comments: Vec::new(),
241            double_colon_syntax: false,
242            format: None,
243            default: None,
244            inferred_type: None,
245        }))
246    }
247
248    fn sqlite_int_type() -> DataType {
249        DataType::Int {
250            length: None,
251            integer_spelling: true,
252        }
253    }
254
255    fn sqlite_real_type() -> DataType {
256        DataType::Float {
257            precision: None,
258            scale: None,
259            real_spelling: true,
260        }
261    }
262
263    fn transform_extract(f: ExtractFunc) -> Result<Expression> {
264        let strftime_format = match &f.field {
265            DateTimeField::Year => "%Y",
266            DateTimeField::Month => "%m",
267            DateTimeField::Day => "%d",
268            DateTimeField::Hour => "%H",
269            DateTimeField::Minute => "%M",
270            DateTimeField::Second => "%f",
271            DateTimeField::DayOfWeek => "%w",
272            DateTimeField::DayOfYear => "%j",
273            DateTimeField::Epoch => "%s",
274            _ => return Ok(Expression::Extract(Box::new(f))),
275        };
276        let target_type = if matches!(f.field, DateTimeField::Epoch | DateTimeField::Second) {
277            Self::sqlite_real_type()
278        } else {
279            Self::sqlite_int_type()
280        };
281        Ok(Self::cast(
282            Self::strftime(strftime_format, f.this),
283            target_type,
284        ))
285    }
286
287    fn transform_date_trunc(f: DateTruncFunc) -> Result<Expression> {
288        match &f.unit {
289            DateTimeField::Day => Ok(Self::function("DATE", vec![f.this])),
290            DateTimeField::Hour => Ok(Self::strftime("%Y-%m-%d %H:00:00", f.this)),
291            DateTimeField::Minute => Ok(Self::strftime("%Y-%m-%d %H:%M:00", f.this)),
292            DateTimeField::Second => Ok(Self::strftime("%Y-%m-%d %H:%M:%S", f.this)),
293            DateTimeField::Month => Ok(Self::strftime("%Y-%m-01", f.this)),
294            DateTimeField::Year => Ok(Self::strftime("%Y-01-01", f.this)),
295            _ => Ok(Expression::DateTrunc(Box::new(f))),
296        }
297    }
298
299    fn datetime_field_from_expr(expr: &Expression) -> Option<DateTimeField> {
300        let unit = match expr {
301            Expression::Literal(lit) => match lit.as_ref() {
302                Literal::String(s) => s.as_str(),
303                _ => return None,
304            },
305            Expression::Identifier(id) => id.name.as_str(),
306            Expression::Var(v) => v.this.as_str(),
307            Expression::Column(col) if col.table.is_none() => col.name.name.as_str(),
308            _ => return None,
309        };
310        match unit.to_ascii_lowercase().as_str() {
311            "year" | "yyyy" | "yy" => Some(DateTimeField::Year),
312            "month" | "mon" | "mm" => Some(DateTimeField::Month),
313            "day" | "dd" => Some(DateTimeField::Day),
314            "hour" | "hours" | "h" | "hh" | "hr" | "hrs" => Some(DateTimeField::Hour),
315            "minute" | "minutes" | "mi" | "min" | "mins" => Some(DateTimeField::Minute),
316            "second" | "seconds" | "s" | "sec" | "secs" | "ss" => Some(DateTimeField::Second),
317            "dow" | "dayofweek" | "dw" => Some(DateTimeField::DayOfWeek),
318            "doy" | "dayofyear" | "dy" => Some(DateTimeField::DayOfYear),
319            "epoch" => Some(DateTimeField::Epoch),
320            _ => None,
321        }
322    }
323
324    fn transform_trim(f: TrimFunc) -> Expression {
325        let function_name = match f.position {
326            TrimPosition::Leading => "LTRIM",
327            TrimPosition::Trailing => "RTRIM",
328            TrimPosition::Both => "TRIM",
329        };
330        let mut args = vec![f.this];
331        if let Some(characters) = f.characters {
332            args.push(characters);
333        }
334        Expression::Function(Box::new(Function::new(function_name.to_string(), args)))
335    }
336
337    /// Check if an expression is already a CAST to a float type
338    fn is_float_cast(expr: &Expression) -> bool {
339        if let Expression::Cast(cast) = expr {
340            match &cast.to {
341                crate::expressions::DataType::Double { .. }
342                | crate::expressions::DataType::Float { .. } => true,
343                crate::expressions::DataType::Custom { name } => {
344                    name.eq_ignore_ascii_case("REAL") || name.eq_ignore_ascii_case("DOUBLE")
345                }
346                _ => false,
347            }
348        } else {
349            false
350        }
351    }
352
353    fn transform_function(&self, f: Function) -> Result<Expression> {
354        let name_upper = f.name.to_uppercase();
355        match name_upper.as_str() {
356            // LIKE(pattern, string) -> string LIKE pattern (SQLite function form)
357            "LIKE" if f.args.len() == 2 => {
358                let mut args = f.args;
359                let pattern = args.remove(0);
360                let string = args.remove(0);
361                // Swap: string LIKE pattern
362                Ok(Expression::Like(Box::new(LikeOp::new(string, pattern))))
363            }
364            // LIKE(pattern, string, escape) -> string LIKE pattern ESCAPE escape
365            "LIKE" if f.args.len() == 3 => {
366                let mut args = f.args;
367                let pattern = args.remove(0);
368                let string = args.remove(0);
369                let escape = args.remove(0);
370                Ok(Expression::Like(Box::new(LikeOp {
371                    left: string,
372                    right: pattern,
373                    escape: Some(escape),
374                    quantifier: None,
375                    inferred_type: None,
376                })))
377            }
378            // GLOB(pattern, string) -> string GLOB pattern (SQLite function form)
379            "GLOB" if f.args.len() == 2 => {
380                let mut args = f.args;
381                let pattern = args.remove(0);
382                let string = args.remove(0);
383                // Swap: string GLOB pattern
384                Ok(Expression::Glob(Box::new(BinaryOp::new(string, pattern))))
385            }
386            // NVL -> IFNULL
387            "NVL" if f.args.len() == 2 => {
388                let mut args = f.args;
389                let expr1 = args.remove(0);
390                let expr2 = args.remove(0);
391                Ok(Expression::IfNull(Box::new(BinaryFunc {
392                    original_name: None,
393                    this: expr1,
394                    expression: expr2,
395                    inferred_type: None,
396                })))
397            }
398
399            // COALESCE stays as COALESCE (native to SQLite)
400            "COALESCE" => Ok(Expression::Coalesce(Box::new(VarArgFunc {
401                original_name: None,
402                expressions: f.args,
403                inferred_type: None,
404            }))),
405
406            // RAND -> RANDOM in SQLite
407            "RAND" => Ok(Expression::Function(Box::new(Function::new(
408                "RANDOM".to_string(),
409                vec![],
410            )))),
411
412            // CHR -> CHAR in SQLite
413            "CHR" if f.args.len() == 1 => Ok(Expression::Function(Box::new(Function::new(
414                "CHAR".to_string(),
415                f.args,
416            )))),
417
418            // POSITION -> INSTR in SQLite (with swapped arguments)
419            "POSITION" if f.args.len() == 2 => {
420                let mut args = f.args;
421                let substring = args.remove(0);
422                let string = args.remove(0);
423                // INSTR(string, substring) - note: argument order is reversed from POSITION
424                Ok(Expression::Function(Box::new(Function::new(
425                    "INSTR".to_string(),
426                    vec![string, substring],
427                ))))
428            }
429
430            // STRPOS -> INSTR in SQLite (with swapped arguments)
431            "STRPOS" if f.args.len() == 2 => {
432                let mut args = f.args;
433                let string = args.remove(0);
434                let substring = args.remove(0);
435                // INSTR(string, substring)
436                Ok(Expression::Function(Box::new(Function::new(
437                    "INSTR".to_string(),
438                    vec![string, substring],
439                ))))
440            }
441
442            // CHARINDEX -> INSTR in SQLite
443            "CHARINDEX" if f.args.len() >= 2 => {
444                let mut args = f.args;
445                let substring = args.remove(0);
446                let string = args.remove(0);
447                // INSTR(string, substring)
448                Ok(Expression::Function(Box::new(Function::new(
449                    "INSTR".to_string(),
450                    vec![string, substring],
451                ))))
452            }
453
454            // LEVENSHTEIN -> EDITDIST3 in SQLite
455            "LEVENSHTEIN" if !f.args.is_empty() => Ok(Expression::Function(Box::new(
456                Function::new("EDITDIST3".to_string(), f.args),
457            ))),
458
459            // PostgreSQL-compatible scalar/JSON rewrites.
460            "LEAST" if !f.args.is_empty() => Ok(Self::function("MIN", f.args)),
461            "GREATEST" if !f.args.is_empty() => Ok(Self::function("MAX", f.args)),
462            "JSON_BUILD_ARRAY" => Ok(Self::function("JSON_ARRAY", f.args)),
463            "JSON_BUILD_OBJECT" => Ok(Self::function("JSON_OBJECT", f.args)),
464            "JSON_AGG" | "JSONB_AGG" if f.args.len() == 1 => {
465                Ok(Self::function("JSON_GROUP_ARRAY", f.args))
466            }
467            "JSON_OBJECT_AGG" if f.args.len() == 2 => {
468                Ok(Self::function("JSON_GROUP_OBJECT", f.args))
469            }
470
471            // GETDATE -> CURRENT_TIMESTAMP
472            "GETDATE" => Ok(Expression::CurrentTimestamp(
473                crate::expressions::CurrentTimestamp {
474                    precision: None,
475                    sysdate: false,
476                },
477            )),
478
479            // NOW -> CURRENT_TIMESTAMP
480            "NOW" => Ok(Expression::CurrentTimestamp(
481                crate::expressions::CurrentTimestamp {
482                    precision: None,
483                    sysdate: false,
484                },
485            )),
486
487            // CEILING -> CEIL (not supported in SQLite, but we try)
488            "CEILING" if f.args.len() == 1 => Ok(Expression::Ceil(Box::new(CeilFunc {
489                this: f.args.into_iter().next().unwrap(),
490                decimals: None,
491                to: None,
492            }))),
493
494            // LEN -> LENGTH in SQLite
495            "LEN" if f.args.len() == 1 => Ok(Expression::Length(Box::new(UnaryFunc::new(
496                f.args.into_iter().next().unwrap(),
497            )))),
498
499            // SUBSTRING is native to SQLite (keep as-is)
500            "SUBSTRING" => Ok(Self::function("SUBSTRING", f.args)),
501
502            // STRING_AGG -> GROUP_CONCAT in SQLite
503            "STRING_AGG" if !f.args.is_empty() => Ok(Expression::Function(Box::new(
504                Function::new("GROUP_CONCAT".to_string(), f.args),
505            ))),
506
507            // LISTAGG -> GROUP_CONCAT in SQLite
508            "LISTAGG" if !f.args.is_empty() => Ok(Expression::Function(Box::new(Function::new(
509                "GROUP_CONCAT".to_string(),
510                f.args,
511            )))),
512
513            "DATE_PART" if f.args.len() == 2 => {
514                let mut args = f.args;
515                let unit = args.remove(0);
516                let expr = args.remove(0);
517                if let Some(field) = Self::datetime_field_from_expr(&unit) {
518                    Self::transform_extract(ExtractFunc { this: expr, field })
519                } else {
520                    Ok(Self::function("DATE_PART", vec![unit, expr]))
521                }
522            }
523
524            "DATE_TRUNC" if f.args.len() == 2 => {
525                let mut args = f.args;
526                let unit = args.remove(0);
527                let expr = args.remove(0);
528                if let Some(unit) = Self::datetime_field_from_expr(&unit) {
529                    Self::transform_date_trunc(DateTruncFunc { this: expr, unit })
530                } else {
531                    Ok(Self::function("DATE_TRUNC", vec![unit, expr]))
532                }
533            }
534
535            // DATEDIFF(a, b, unit_string) -> JULIANDAY arithmetic for SQLite
536            "DATEDIFF" | "DATE_DIFF" if f.args.len() == 3 => {
537                let mut args = f.args;
538                let first = args.remove(0); // date1
539                let second = args.remove(0); // date2
540                let unit_expr = args.remove(0); // unit string like 'day'
541
542                // Extract unit string
543                let unit_str = match &unit_expr {
544                    Expression::Literal(lit)
545                        if matches!(lit.as_ref(), crate::expressions::Literal::String(_)) =>
546                    {
547                        let crate::expressions::Literal::String(s) = lit.as_ref() else {
548                            unreachable!()
549                        };
550                        s.to_lowercase()
551                    }
552                    Expression::Identifier(id) => id.name.to_lowercase(),
553                    Expression::Var(v) => v.this.to_lowercase(),
554                    Expression::Column(col) if col.table.is_none() => col.name.name.to_lowercase(),
555                    _ => "day".to_string(),
556                };
557
558                // JULIANDAY(first) - JULIANDAY(second)
559                let jd_first = Expression::Function(Box::new(Function::new(
560                    "JULIANDAY".to_string(),
561                    vec![first],
562                )));
563                let jd_second = Expression::Function(Box::new(Function::new(
564                    "JULIANDAY".to_string(),
565                    vec![second],
566                )));
567                let diff = Expression::Sub(Box::new(BinaryOp::new(jd_first, jd_second)));
568                let paren_diff = Expression::Paren(Box::new(crate::expressions::Paren {
569                    this: diff,
570                    trailing_comments: Vec::new(),
571                }));
572
573                // Apply multiplier based on unit
574                let adjusted = match unit_str.as_str() {
575                    "hour" => Expression::Mul(Box::new(BinaryOp::new(
576                        paren_diff,
577                        Expression::Literal(Box::new(crate::expressions::Literal::Number(
578                            "24.0".to_string(),
579                        ))),
580                    ))),
581                    "minute" => Expression::Mul(Box::new(BinaryOp::new(
582                        paren_diff,
583                        Expression::Literal(Box::new(crate::expressions::Literal::Number(
584                            "1440.0".to_string(),
585                        ))),
586                    ))),
587                    "second" => Expression::Mul(Box::new(BinaryOp::new(
588                        paren_diff,
589                        Expression::Literal(Box::new(crate::expressions::Literal::Number(
590                            "86400.0".to_string(),
591                        ))),
592                    ))),
593                    "month" => Expression::Div(Box::new(BinaryOp::new(
594                        paren_diff,
595                        Expression::Literal(Box::new(crate::expressions::Literal::Number(
596                            "30.0".to_string(),
597                        ))),
598                    ))),
599                    "year" => Expression::Div(Box::new(BinaryOp::new(
600                        paren_diff,
601                        Expression::Literal(Box::new(crate::expressions::Literal::Number(
602                            "365.0".to_string(),
603                        ))),
604                    ))),
605                    _ => paren_diff, // day is the default
606                };
607
608                // CAST(... AS INTEGER)
609                Ok(Expression::Cast(Box::new(Cast {
610                    this: adjusted,
611                    to: crate::expressions::DataType::Int {
612                        length: None,
613                        integer_spelling: true,
614                    },
615                    trailing_comments: Vec::new(),
616                    double_colon_syntax: false,
617                    format: None,
618                    default: None,
619                    inferred_type: None,
620                })))
621            }
622
623            // STRFTIME with single arg -> add CURRENT_TIMESTAMP as second arg
624            "STRFTIME" if f.args.len() == 1 => {
625                let mut args = f.args;
626                args.push(Expression::CurrentTimestamp(
627                    crate::expressions::CurrentTimestamp {
628                        precision: None,
629                        sysdate: false,
630                    },
631                ));
632                Ok(Expression::Function(Box::new(Function::new(
633                    "STRFTIME".to_string(),
634                    args,
635                ))))
636            }
637
638            // CONCAT(a, b, ...) -> a || b || ... for SQLite
639            "CONCAT" if f.args.len() >= 2 => {
640                let mut args = f.args;
641                let mut result = args.remove(0);
642                for arg in args {
643                    result = Expression::DPipe(Box::new(crate::expressions::DPipe {
644                        this: Box::new(result),
645                        expression: Box::new(arg),
646                        safe: None,
647                    }));
648                }
649                Ok(result)
650            }
651
652            // TRUNC: SQLite doesn't support decimals arg, strip second arg
653            "TRUNC" if f.args.len() > 1 => Ok(Expression::Function(Box::new(Function::new(
654                "TRUNC".to_string(),
655                vec![f.args[0].clone()],
656            )))),
657
658            // Pass through everything else
659            _ => Ok(Expression::Function(Box::new(f))),
660        }
661    }
662
663    fn transform_aggregate_function(
664        &self,
665        f: Box<crate::expressions::AggregateFunction>,
666    ) -> Result<Expression> {
667        let name_upper = f.name.to_uppercase();
668        match name_upper.as_str() {
669            // COUNT_IF -> SUM(CASE WHEN...)
670            "COUNT_IF" if !f.args.is_empty() => {
671                let condition = f.args.into_iter().next().unwrap();
672                let case_expr = Expression::Case(Box::new(Case {
673                    operand: None,
674                    whens: vec![(condition, Expression::number(1))],
675                    else_: Some(Expression::number(0)),
676                    comments: Vec::new(),
677                    inferred_type: None,
678                }));
679                Ok(Expression::Sum(Box::new(AggFunc {
680                    ignore_nulls: None,
681                    having_max: None,
682                    this: case_expr,
683                    distinct: f.distinct,
684                    filter: f.filter,
685                    order_by: Vec::new(),
686                    name: None,
687                    limit: None,
688                    inferred_type: None,
689                })))
690            }
691
692            // ANY_VALUE -> MAX in SQLite
693            "ANY_VALUE" if !f.args.is_empty() => {
694                let arg = f.args.into_iter().next().unwrap();
695                Ok(Expression::Max(Box::new(AggFunc {
696                    ignore_nulls: None,
697                    having_max: None,
698                    this: arg,
699                    distinct: f.distinct,
700                    filter: f.filter,
701                    order_by: Vec::new(),
702                    name: None,
703                    limit: None,
704                    inferred_type: None,
705                })))
706            }
707
708            // STRING_AGG -> GROUP_CONCAT
709            "STRING_AGG" if !f.args.is_empty() => Ok(Expression::Function(Box::new(
710                Function::new("GROUP_CONCAT".to_string(), f.args),
711            ))),
712
713            // LISTAGG -> GROUP_CONCAT
714            "LISTAGG" if !f.args.is_empty() => Ok(Expression::Function(Box::new(Function::new(
715                "GROUP_CONCAT".to_string(),
716                f.args,
717            )))),
718
719            // ARRAY_AGG -> GROUP_CONCAT (SQLite doesn't have arrays)
720            "ARRAY_AGG" if !f.args.is_empty() => Ok(Expression::Function(Box::new(Function::new(
721                "GROUP_CONCAT".to_string(),
722                f.args,
723            )))),
724
725            "JSON_AGG" | "JSONB_AGG" if f.args.len() == 1 => {
726                Ok(Self::function("JSON_GROUP_ARRAY", f.args))
727            }
728
729            "JSON_OBJECT_AGG" if f.args.len() == 2 => {
730                Ok(Self::function("JSON_GROUP_OBJECT", f.args))
731            }
732
733            // Pass through everything else
734            _ => Ok(Expression::AggregateFunction(f)),
735        }
736    }
737
738    fn transform_cast(&self, c: Cast) -> Result<Expression> {
739        // SQLite has limited type support, map types appropriately
740        // The type mapping is handled in the generator via type_mapping
741        // For now, just pass through
742        Ok(Expression::Cast(Box::new(c)))
743    }
744}
polyglot_sql/dialects/sqlite.rs

polyglot_sql/dialects/
sqlite.rs
