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

polyglot_sql/dialects/
sqlite.rs
