vibesql_ast/
pretty_print.rs

1//! SQL pretty-printer for AST nodes
2//!
3//! This module provides the [`ToSql`] trait for converting AST nodes back to valid SQL strings.
4//!
5//! # Example
6//!
7//! ```
8//! use vibesql_ast::pretty_print::ToSql;
9//! use vibesql_ast::{BinaryOperator, Expression};
10//! use vibesql_types::SqlValue;
11//!
12//! // Convert operators to SQL
13//! assert_eq!(BinaryOperator::Equal.to_sql(), "=");
14//! assert_eq!(BinaryOperator::GreaterThan.to_sql(), ">");
15//!
16//! // Convert expressions to SQL
17//! let expr = Expression::Literal(SqlValue::Integer(42));
18//! assert_eq!(expr.to_sql(), "42");
19//! ```
20
21use vibesql_types::{DataType, SqlValue};
22
23use crate::{
24    expression::{
25        CaseWhen, CharacterUnit, Expression, FrameBound, FrameExclude, FrameUnit, FulltextMode,
26        IntervalUnit, PseudoTable, Quantifier, TrimPosition, WindowFrame, WindowFunctionSpec,
27        WindowSpec,
28    },
29    operators::{BinaryOperator, UnaryOperator},
30    select::{
31        CommonTableExpr, CteMaterialization, FromClause, GroupByClause, GroupingElement,
32        GroupingSet, JoinType, MixedGroupingItem, OrderByItem, OrderDirection, SelectItem,
33        SelectStmt, SetOperation, SetOperator,
34    },
35};
36
37/// Trait for converting AST nodes to SQL strings
38pub trait ToSql {
39    /// Convert this AST node to a SQL string
40    fn to_sql(&self) -> String;
41}
42
43// ============================================================================
44// Operators
45// ============================================================================
46
47impl ToSql for BinaryOperator {
48    fn to_sql(&self) -> String {
49        match self {
50            BinaryOperator::Plus => "+".to_string(),
51            BinaryOperator::Minus => "-".to_string(),
52            BinaryOperator::Multiply => "*".to_string(),
53            BinaryOperator::Divide => "/".to_string(),
54            BinaryOperator::IntegerDivide => "DIV".to_string(),
55            BinaryOperator::Modulo => "%".to_string(),
56            BinaryOperator::Equal => "=".to_string(),
57            BinaryOperator::NotEqual => "<>".to_string(),
58            BinaryOperator::LessThan => "<".to_string(),
59            BinaryOperator::LessThanOrEqual => "<=".to_string(),
60            BinaryOperator::GreaterThan => ">".to_string(),
61            BinaryOperator::GreaterThanOrEqual => ">=".to_string(),
62            BinaryOperator::And => "AND".to_string(),
63            BinaryOperator::Or => "OR".to_string(),
64            BinaryOperator::Concat => "||".to_string(),
65            BinaryOperator::CosineDistance => "<->".to_string(),
66            BinaryOperator::NegativeInnerProduct => "<#>".to_string(),
67            BinaryOperator::L2Distance => "<=>".to_string(),
68            BinaryOperator::BitwiseAnd => "&".to_string(),
69            BinaryOperator::BitwiseOr => "|".to_string(),
70            BinaryOperator::LeftShift => "<<".to_string(),
71            BinaryOperator::RightShift => ">>".to_string(),
72            BinaryOperator::JsonExtract => "->".to_string(),
73            BinaryOperator::JsonExtractText => "->>".to_string(),
74        }
75    }
76}
77
78impl ToSql for UnaryOperator {
79    fn to_sql(&self) -> String {
80        match self {
81            UnaryOperator::Not => "NOT".to_string(),
82            UnaryOperator::Minus => "-".to_string(),
83            UnaryOperator::Plus => "+".to_string(),
84            UnaryOperator::BitwiseNot => "~".to_string(),
85            UnaryOperator::IsNull => "IS NULL".to_string(),
86            UnaryOperator::IsNotNull => "IS NOT NULL".to_string(),
87        }
88    }
89}
90
91// ============================================================================
92// Helper functions
93// ============================================================================
94
95/// Format a SQL string literal with proper escaping
96fn format_string_literal(s: &str) -> String {
97    // Escape single quotes by doubling them
98    let escaped = s.replace('\'', "''");
99    format!("'{}'", escaped)
100}
101
102/// Format an identifier, quoting if necessary
103fn format_identifier(name: &str) -> String {
104    // Check if the identifier needs quoting (contains special chars or is a reserved word)
105    let needs_quoting = name.contains(' ')
106        || name.contains('-')
107        || name.contains('.')
108        || name.chars().next().is_some_and(|c| c.is_ascii_digit());
109
110    if needs_quoting {
111        format!("\"{}\"", name.replace('"', "\"\""))
112    } else {
113        name.to_string()
114    }
115}
116
117// ============================================================================
118// SqlValue
119// ============================================================================
120
121impl ToSql for SqlValue {
122    fn to_sql(&self) -> String {
123        match self {
124            SqlValue::Integer(i) => i.to_string(),
125            SqlValue::Smallint(i) => i.to_string(),
126            SqlValue::Bigint(i) => i.to_string(),
127            SqlValue::Unsigned(u) => u.to_string(),
128            SqlValue::Numeric(n) => {
129                if n.is_nan() || n.is_infinite() {
130                    // Can't represent these in SQL, use NULL as fallback
131                    "NULL".to_string()
132                } else if n.fract() == 0.0 {
133                    format!("{:.1}", n) // Show as 1.0 not 1
134                } else {
135                    n.to_string()
136                }
137            }
138            SqlValue::Float(f) => {
139                let f64_val = *f as f64;
140                if f64_val.is_nan() || f64_val.is_infinite() {
141                    "NULL".to_string()
142                } else if f64_val.fract() == 0.0 {
143                    format!("{:.1}", f64_val)
144                } else {
145                    f64_val.to_string()
146                }
147            }
148            SqlValue::Real(r) => {
149                let f64_val = *r;
150                if f64_val.is_nan() || f64_val.is_infinite() {
151                    "NULL".to_string()
152                } else if f64_val.fract() == 0.0 {
153                    format!("{:.1}", f64_val)
154                } else {
155                    f64_val.to_string()
156                }
157            }
158            SqlValue::Double(d) => {
159                if d.is_nan() || d.is_infinite() {
160                    "NULL".to_string()
161                } else if d.fract() == 0.0 {
162                    format!("{:.1}", d)
163                } else {
164                    d.to_string()
165                }
166            }
167            SqlValue::Character(s) | SqlValue::Varchar(s) => format_string_literal(s),
168            SqlValue::Boolean(true) => "TRUE".to_string(),
169            SqlValue::Boolean(false) => "FALSE".to_string(),
170            SqlValue::Date(s) => format!("DATE '{}'", s),
171            SqlValue::Time(s) => format!("TIME '{}'", s),
172            SqlValue::Timestamp(s) => format!("TIMESTAMP '{}'", s),
173            SqlValue::Interval(s) => format!("INTERVAL '{}'", s),
174            SqlValue::Vector(v) => {
175                let formatted: Vec<String> = v.iter().map(|x| x.to_string()).collect();
176                format!("[{}]", formatted.join(", "))
177            }
178            SqlValue::Blob(b) => {
179                let hex: String = b.iter().map(|byte| format!("{:02X}", byte)).collect();
180                format!("x'{}'", hex)
181            }
182            SqlValue::Null => "NULL".to_string(),
183        }
184    }
185}
186
187// ============================================================================
188// DataType
189// ============================================================================
190
191impl ToSql for DataType {
192    fn to_sql(&self) -> String {
193        match self {
194            DataType::Integer => "INTEGER".to_string(),
195            DataType::Smallint => "SMALLINT".to_string(),
196            DataType::Bigint => "BIGINT".to_string(),
197            DataType::Unsigned => "BIGINT UNSIGNED".to_string(),
198            DataType::Numeric { precision, scale } => {
199                if *scale == 0 {
200                    format!("NUMERIC({})", precision)
201                } else {
202                    format!("NUMERIC({}, {})", precision, scale)
203                }
204            }
205            DataType::Decimal { precision, scale } => {
206                if *scale == 0 {
207                    format!("DECIMAL({})", precision)
208                } else {
209                    format!("DECIMAL({}, {})", precision, scale)
210                }
211            }
212            DataType::Float { precision } => {
213                if *precision == 53 {
214                    "FLOAT".to_string()
215                } else {
216                    format!("FLOAT({})", precision)
217                }
218            }
219            DataType::Real => "REAL".to_string(),
220            DataType::DoublePrecision => "DOUBLE PRECISION".to_string(),
221            DataType::Character { length } => format!("CHARACTER({})", length),
222            DataType::Varchar { max_length } => match max_length {
223                Some(len) => format!("VARCHAR({})", len),
224                None => "VARCHAR".to_string(),
225            },
226            DataType::CharacterLargeObject => "CLOB".to_string(),
227            DataType::Name => "NAME".to_string(),
228            DataType::Boolean => "BOOLEAN".to_string(),
229            DataType::Date => "DATE".to_string(),
230            DataType::Time { with_timezone } => {
231                if *with_timezone {
232                    "TIME WITH TIME ZONE".to_string()
233                } else {
234                    "TIME".to_string()
235                }
236            }
237            DataType::Timestamp { with_timezone } => {
238                if *with_timezone {
239                    "TIMESTAMP WITH TIME ZONE".to_string()
240                } else {
241                    "TIMESTAMP".to_string()
242                }
243            }
244            DataType::Interval { start_field, end_field } => {
245                let start = interval_field_to_sql(start_field);
246                match end_field {
247                    Some(end) => format!("INTERVAL {} TO {}", start, interval_field_to_sql(end)),
248                    None => format!("INTERVAL {}", start),
249                }
250            }
251            DataType::BinaryLargeObject => "BLOB".to_string(),
252            DataType::Bit { length } => match length {
253                Some(len) => format!("BIT({})", len),
254                None => "BIT".to_string(),
255            },
256            DataType::Vector { dimensions } => format!("VECTOR({})", dimensions),
257            DataType::UserDefined { type_name } => type_name.clone(),
258            DataType::Null => "NULL".to_string(),
259        }
260    }
261}
262
263fn interval_field_to_sql(field: &vibesql_types::IntervalField) -> &'static str {
264    use vibesql_types::IntervalField;
265    match field {
266        IntervalField::Year => "YEAR",
267        IntervalField::Month => "MONTH",
268        IntervalField::Day => "DAY",
269        IntervalField::Hour => "HOUR",
270        IntervalField::Minute => "MINUTE",
271        IntervalField::Second => "SECOND",
272    }
273}
274
275// ============================================================================
276// Expression
277// ============================================================================
278
279impl ToSql for Expression {
280    fn to_sql(&self) -> String {
281        match self {
282            Expression::Literal(value) => value.to_sql(),
283
284            Expression::Placeholder(_) => "?".to_string(),
285
286            Expression::NumberedPlaceholder(n) => format!("${}", n),
287
288            Expression::NamedPlaceholder(name) => format!(":{}", name),
289
290            Expression::ColumnRef(col_id) => {
291                // Use display form which preserves user's original input
292                col_id.display().to_string()
293            }
294
295            Expression::BinaryOp { op, left, right } => {
296                let left_sql = left.to_sql();
297                let right_sql = right.to_sql();
298                let op_sql = op.to_sql();
299
300                // Add parentheses for nested binary ops to ensure correct precedence
301                let left_str =
302                    if needs_parens(left, op, true) { format!("({})", left_sql) } else { left_sql };
303                let right_str = if needs_parens(right, op, false) {
304                    format!("({})", right_sql)
305                } else {
306                    right_sql
307                };
308
309                // Use compact format (no spaces) for symbolic operators, spaces for word operators
310                // This matches SQLite's behavior in CHECK constraint error messages
311                if op.is_word_operator() {
312                    format!("{} {} {}", left_str, op_sql, right_str)
313                } else {
314                    format!("{}{}{}", left_str, op_sql, right_str)
315                }
316            }
317
318            Expression::Conjunction(exprs) => {
319                let parts: Vec<String> = exprs
320                    .iter()
321                    .map(|e| {
322                        // Parenthesize OR expressions within AND
323                        if matches!(e, Expression::Disjunction(_)) {
324                            format!("({})", e.to_sql())
325                        } else {
326                            e.to_sql()
327                        }
328                    })
329                    .collect();
330                parts.join(" AND ")
331            }
332
333            Expression::Disjunction(exprs) => {
334                let parts: Vec<String> = exprs.iter().map(|e| e.to_sql()).collect();
335                parts.join(" OR ")
336            }
337
338            Expression::UnaryOp { op, expr } => {
339                let expr_sql = expr.to_sql();
340                match op {
341                    UnaryOperator::Not => format!("NOT {}", maybe_paren(&expr_sql, expr)),
342                    UnaryOperator::Minus => format!("-{}", maybe_paren(&expr_sql, expr)),
343                    UnaryOperator::Plus => format!("+{}", maybe_paren(&expr_sql, expr)),
344                    UnaryOperator::BitwiseNot => format!("~{}", maybe_paren(&expr_sql, expr)),
345                    UnaryOperator::IsNull => format!("{} IS NULL", expr_sql),
346                    UnaryOperator::IsNotNull => format!("{} IS NOT NULL", expr_sql),
347                }
348            }
349
350            Expression::Function { name, args, character_unit } => {
351                let args_sql: Vec<String> = args.iter().map(|a| a.to_sql()).collect();
352                let mut result =
353                    format!("{}({})", name.canonical().to_uppercase(), args_sql.join(", "));
354                if let Some(unit) = character_unit {
355                    result.push_str(&format!(" USING {}", unit.to_sql()));
356                }
357                result
358            }
359
360            Expression::AggregateFunction { name, distinct, args, order_by, filter } => {
361                let args_sql: Vec<String> = args.iter().map(|a| a.to_sql()).collect();
362                let order_by_sql = order_by.as_ref().map(|items| {
363                    let items_sql: Vec<String> = items
364                        .iter()
365                        .map(|item| {
366                            let dir = match item.direction {
367                                crate::OrderDirection::Asc => "",
368                                crate::OrderDirection::Desc => " DESC",
369                            };
370                            format!("{}{}", item.expr.to_sql(), dir)
371                        })
372                        .collect();
373                    format!(" ORDER BY {}", items_sql.join(", "))
374                });
375                let filter_sql = filter
376                    .as_ref()
377                    .map(|f| format!(" FILTER (WHERE {})", f.to_sql()))
378                    .unwrap_or_default();
379                if *distinct {
380                    format!(
381                        "{}(DISTINCT {}{}){}",
382                        name.canonical().to_uppercase(),
383                        args_sql.join(", "),
384                        order_by_sql.unwrap_or_default(),
385                        filter_sql
386                    )
387                } else {
388                    format!(
389                        "{}({}{}){}",
390                        name.canonical().to_uppercase(),
391                        args_sql.join(", "),
392                        order_by_sql.unwrap_or_default(),
393                        filter_sql
394                    )
395                }
396            }
397
398            Expression::IsNull { expr, negated } => {
399                let expr_sql = expr.to_sql();
400                if *negated {
401                    format!("{} IS NOT NULL", expr_sql)
402                } else {
403                    format!("{} IS NULL", expr_sql)
404                }
405            }
406
407            Expression::IsDistinctFrom { left, right, negated } => {
408                let left_sql = left.to_sql();
409                let right_sql = right.to_sql();
410                if *negated {
411                    format!("{} IS NOT DISTINCT FROM {}", left_sql, right_sql)
412                } else {
413                    format!("{} IS DISTINCT FROM {}", left_sql, right_sql)
414                }
415            }
416
417            Expression::IsTruthValue { expr, truth_value, negated } => {
418                let expr_sql = expr.to_sql();
419                let tv_str = match truth_value {
420                    crate::TruthValue::True => "TRUE",
421                    crate::TruthValue::False => "FALSE",
422                    crate::TruthValue::Unknown => "UNKNOWN",
423                };
424                if *negated {
425                    format!("{} IS NOT {}", expr_sql, tv_str)
426                } else {
427                    format!("{} IS {}", expr_sql, tv_str)
428                }
429            }
430
431            Expression::Wildcard => "*".to_string(),
432
433            Expression::Case { operand, when_clauses, else_result } => {
434                let mut result = "CASE".to_string();
435                if let Some(op) = operand {
436                    result.push_str(&format!(" {}", op.to_sql()));
437                }
438                for when in when_clauses {
439                    result.push_str(&format!(" {}", when.to_sql()));
440                }
441                if let Some(else_expr) = else_result {
442                    result.push_str(&format!(" ELSE {}", else_expr.to_sql()));
443                }
444                result.push_str(" END");
445                result
446            }
447
448            Expression::ScalarSubquery(query) => format!("({})", query.to_sql()),
449
450            Expression::In { expr, subquery, negated } => {
451                let not_str = if *negated { "NOT " } else { "" };
452                format!("{} {}IN ({})", expr.to_sql(), not_str, subquery.to_sql())
453            }
454
455            Expression::InList { expr, values, negated } => {
456                let values_sql: Vec<String> = values.iter().map(|v| v.to_sql()).collect();
457                let not_str = if *negated { "NOT " } else { "" };
458                format!("{} {}IN ({})", expr.to_sql(), not_str, values_sql.join(", "))
459            }
460
461            Expression::Between { expr, low, high, negated, symmetric } => {
462                let not_str = if *negated { "NOT " } else { "" };
463                let sym_str = if *symmetric { "SYMMETRIC " } else { "" };
464                format!(
465                    "{} {}BETWEEN {}{} AND {}",
466                    expr.to_sql(),
467                    not_str,
468                    sym_str,
469                    low.to_sql(),
470                    high.to_sql()
471                )
472            }
473
474            Expression::Cast { expr, data_type } => {
475                format!("CAST({} AS {})", expr.to_sql(), data_type.to_sql())
476            }
477
478            Expression::Position { substring, string, character_unit } => {
479                let mut result = format!("POSITION({} IN {})", substring.to_sql(), string.to_sql());
480                if let Some(unit) = character_unit {
481                    result.push_str(&format!(" USING {}", unit.to_sql()));
482                }
483                result
484            }
485
486            Expression::Trim { position, removal_char, string } => {
487                let mut result = "TRIM(".to_string();
488                if let Some(pos) = position {
489                    result.push_str(&format!("{} ", pos.to_sql()));
490                }
491                if let Some(char_expr) = removal_char {
492                    result.push_str(&format!("{} FROM ", char_expr.to_sql()));
493                }
494                result.push_str(&string.to_sql());
495                result.push(')');
496                result
497            }
498
499            Expression::Extract { field, expr } => {
500                format!("EXTRACT({} FROM {})", field.to_sql(), expr.to_sql())
501            }
502
503            Expression::Like { expr, pattern, negated, escape } => {
504                let not_str = if *negated { "NOT " } else { "" };
505                let escape_str = escape
506                    .as_ref()
507                    .map(|e| format!(" ESCAPE {}", e.to_sql()))
508                    .unwrap_or_default();
509                format!("{} {}LIKE {}{}", expr.to_sql(), not_str, pattern.to_sql(), escape_str)
510            }
511
512            Expression::Glob { expr, pattern, negated, escape } => {
513                let not_str = if *negated { "NOT " } else { "" };
514                let escape_str = escape
515                    .as_ref()
516                    .map(|e| format!(" ESCAPE {}", e.to_sql()))
517                    .unwrap_or_default();
518                format!("{} {}GLOB {}{}", expr.to_sql(), not_str, pattern.to_sql(), escape_str)
519            }
520
521            Expression::Exists { subquery, negated } => {
522                let not_str = if *negated { "NOT " } else { "" };
523                format!("{}EXISTS ({})", not_str, subquery.to_sql())
524            }
525
526            Expression::QuantifiedComparison { expr, op, quantifier, subquery } => {
527                format!(
528                    "{} {} {} ({})",
529                    expr.to_sql(),
530                    op.to_sql(),
531                    quantifier.to_sql(),
532                    subquery.to_sql()
533                )
534            }
535
536            Expression::CurrentDate => "CURRENT_DATE".to_string(),
537
538            Expression::CurrentTime { precision } => match precision {
539                Some(p) => format!("CURRENT_TIME({})", p),
540                None => "CURRENT_TIME".to_string(),
541            },
542
543            Expression::CurrentTimestamp { precision } => match precision {
544                Some(p) => format!("CURRENT_TIMESTAMP({})", p),
545                None => "CURRENT_TIMESTAMP".to_string(),
546            },
547
548            Expression::Interval { value, unit, leading_precision, fractional_precision } => {
549                let mut result = format!("INTERVAL {} {}", value.to_sql(), unit.to_sql());
550                if let Some(lp) = leading_precision {
551                    result.push_str(&format!("({})", lp));
552                }
553                if let Some(fp) = fractional_precision {
554                    result.push_str(&format!("({})", fp));
555                }
556                result
557            }
558
559            Expression::Default => "DEFAULT".to_string(),
560
561            Expression::DuplicateKeyValue { column } => {
562                format!("VALUES({})", format_identifier(column))
563            }
564
565            Expression::WindowFunction { function, over } => {
566                format!("{} OVER ({})", function.to_sql(), over.to_sql())
567            }
568
569            Expression::NextValue { sequence_name } => {
570                format!("NEXT VALUE FOR {}", format_identifier(sequence_name))
571            }
572
573            Expression::MatchAgainst { columns, search_modifier, mode } => {
574                let cols_sql: Vec<String> = columns.iter().map(|c| format_identifier(c)).collect();
575                let mode_sql = match mode {
576                    FulltextMode::NaturalLanguage => "",
577                    FulltextMode::Boolean => " IN BOOLEAN MODE",
578                    FulltextMode::QueryExpansion => " WITH QUERY EXPANSION",
579                };
580                format!(
581                    "MATCH({}) AGAINST ({}{})",
582                    cols_sql.join(", "),
583                    search_modifier.to_sql(),
584                    mode_sql
585                )
586            }
587
588            Expression::PseudoVariable { pseudo_table, column } => {
589                format!("{}.{}", pseudo_table.to_sql(), format_identifier(column))
590            }
591
592            Expression::SessionVariable { name } => format!("@@{}", name),
593
594            Expression::RowValueConstructor(values) => {
595                let values_sql: Vec<String> = values.iter().map(|v| v.to_sql()).collect();
596                format!("({})", values_sql.join(", "))
597            }
598
599            Expression::Collate { expr, collation } => {
600                format!("{} COLLATE {}", expr.to_sql(), collation)
601            }
602        }
603    }
604}
605
606/// Check if parentheses are needed for a child expression
607fn needs_parens(expr: &Expression, parent_op: &BinaryOperator, is_left: bool) -> bool {
608    match expr {
609        Expression::BinaryOp { op, .. } => {
610            let child_prec = operator_precedence(op);
611            let parent_prec = operator_precedence(parent_op);
612
613            // Need parens if child has lower precedence
614            // Or if same precedence and on the right (for left-associative ops)
615            child_prec < parent_prec || (child_prec == parent_prec && !is_left)
616        }
617        Expression::Conjunction(_) | Expression::Disjunction(_) => true,
618        _ => false,
619    }
620}
621
622/// Get operator precedence (higher = binds tighter)
623fn operator_precedence(op: &BinaryOperator) -> u8 {
624    match op {
625        BinaryOperator::Or => 1,
626        BinaryOperator::And => 2,
627        // Bitwise OR has lower precedence than AND in SQLite
628        BinaryOperator::BitwiseOr => 3,
629        BinaryOperator::BitwiseAnd => 4,
630        BinaryOperator::Equal
631        | BinaryOperator::NotEqual
632        | BinaryOperator::LessThan
633        | BinaryOperator::LessThanOrEqual
634        | BinaryOperator::GreaterThan
635        | BinaryOperator::GreaterThanOrEqual
636        | BinaryOperator::CosineDistance
637        | BinaryOperator::NegativeInnerProduct
638        | BinaryOperator::L2Distance => 5,
639        // Shift operators are between comparison and addition
640        BinaryOperator::LeftShift | BinaryOperator::RightShift => 6,
641        BinaryOperator::Plus | BinaryOperator::Minus | BinaryOperator::Concat => 7,
642        BinaryOperator::Multiply
643        | BinaryOperator::Divide
644        | BinaryOperator::IntegerDivide
645        | BinaryOperator::Modulo => 8,
646        // JSON operators have highest precedence (postfix-like)
647        BinaryOperator::JsonExtract | BinaryOperator::JsonExtractText => 9,
648    }
649}
650
651/// Maybe wrap expression in parentheses if it's complex
652fn maybe_paren(sql: &str, expr: &Expression) -> String {
653    match expr {
654        Expression::BinaryOp { .. }
655        | Expression::Conjunction(_)
656        | Expression::Disjunction(_)
657        | Expression::UnaryOp { .. } => format!("({})", sql),
658        _ => sql.to_string(),
659    }
660}
661
662// ============================================================================
663// Expression helper types
664// ============================================================================
665
666impl ToSql for CaseWhen {
667    fn to_sql(&self) -> String {
668        let conds: Vec<String> = self.conditions.iter().map(|c| c.to_sql()).collect();
669        format!("WHEN {} THEN {}", conds.join(", "), self.result.to_sql())
670    }
671}
672
673impl ToSql for Quantifier {
674    fn to_sql(&self) -> String {
675        match self {
676            Quantifier::All => "ALL".to_string(),
677            Quantifier::Any => "ANY".to_string(),
678            Quantifier::Some => "SOME".to_string(),
679        }
680    }
681}
682
683impl ToSql for CharacterUnit {
684    fn to_sql(&self) -> String {
685        match self {
686            CharacterUnit::Characters => "CHARACTERS".to_string(),
687            CharacterUnit::Octets => "OCTETS".to_string(),
688        }
689    }
690}
691
692impl ToSql for TrimPosition {
693    fn to_sql(&self) -> String {
694        match self {
695            TrimPosition::Both => "BOTH".to_string(),
696            TrimPosition::Leading => "LEADING".to_string(),
697            TrimPosition::Trailing => "TRAILING".to_string(),
698        }
699    }
700}
701
702impl ToSql for IntervalUnit {
703    fn to_sql(&self) -> String {
704        match self {
705            IntervalUnit::Microsecond => "MICROSECOND".to_string(),
706            IntervalUnit::Second => "SECOND".to_string(),
707            IntervalUnit::Minute => "MINUTE".to_string(),
708            IntervalUnit::Hour => "HOUR".to_string(),
709            IntervalUnit::Day => "DAY".to_string(),
710            IntervalUnit::Week => "WEEK".to_string(),
711            IntervalUnit::Month => "MONTH".to_string(),
712            IntervalUnit::Quarter => "QUARTER".to_string(),
713            IntervalUnit::Year => "YEAR".to_string(),
714            IntervalUnit::SecondMicrosecond => "SECOND_MICROSECOND".to_string(),
715            IntervalUnit::MinuteMicrosecond => "MINUTE_MICROSECOND".to_string(),
716            IntervalUnit::MinuteSecond => "MINUTE_SECOND".to_string(),
717            IntervalUnit::HourMicrosecond => "HOUR_MICROSECOND".to_string(),
718            IntervalUnit::HourSecond => "HOUR_SECOND".to_string(),
719            IntervalUnit::HourMinute => "HOUR_MINUTE".to_string(),
720            IntervalUnit::DayMicrosecond => "DAY_MICROSECOND".to_string(),
721            IntervalUnit::DaySecond => "DAY_SECOND".to_string(),
722            IntervalUnit::DayMinute => "DAY_MINUTE".to_string(),
723            IntervalUnit::DayHour => "DAY_HOUR".to_string(),
724            IntervalUnit::YearMonth => "YEAR_MONTH".to_string(),
725        }
726    }
727}
728
729impl ToSql for PseudoTable {
730    fn to_sql(&self) -> String {
731        match self {
732            PseudoTable::Old => "OLD".to_string(),
733            PseudoTable::New => "NEW".to_string(),
734        }
735    }
736}
737
738impl ToSql for WindowFunctionSpec {
739    fn to_sql(&self) -> String {
740        match self {
741            WindowFunctionSpec::Aggregate { name, args, filter } => {
742                let args_sql: Vec<String> = args.iter().map(|a| a.to_sql()).collect();
743                let filter_sql = filter
744                    .as_ref()
745                    .map(|f| format!(" FILTER (WHERE {})", f.to_sql()))
746                    .unwrap_or_default();
747                format!(
748                    "{}({}){}",
749                    name.canonical().to_uppercase(),
750                    args_sql.join(", "),
751                    filter_sql
752                )
753            }
754            WindowFunctionSpec::Ranking { name, args } => {
755                let args_sql: Vec<String> = args.iter().map(|a| a.to_sql()).collect();
756                if args_sql.is_empty() {
757                    format!("{}()", name.canonical().to_uppercase())
758                } else {
759                    format!("{}({})", name.canonical().to_uppercase(), args_sql.join(", "))
760                }
761            }
762            WindowFunctionSpec::Value { name, args } => {
763                let args_sql: Vec<String> = args.iter().map(|a| a.to_sql()).collect();
764                format!("{}({})", name.canonical().to_uppercase(), args_sql.join(", "))
765            }
766        }
767    }
768}
769
770impl ToSql for WindowSpec {
771    fn to_sql(&self) -> String {
772        let mut parts = Vec::new();
773
774        if let Some(partition_by) = &self.partition_by {
775            let exprs: Vec<String> = partition_by.iter().map(|e| e.to_sql()).collect();
776            parts.push(format!("PARTITION BY {}", exprs.join(", ")));
777        }
778
779        if let Some(order_by) = &self.order_by {
780            let items: Vec<String> = order_by.iter().map(|o| o.to_sql()).collect();
781            parts.push(format!("ORDER BY {}", items.join(", ")));
782        }
783
784        if let Some(frame) = &self.frame {
785            parts.push(frame.to_sql());
786        }
787
788        parts.join(" ")
789    }
790}
791
792impl ToSql for WindowFrame {
793    fn to_sql(&self) -> String {
794        let unit = match self.unit {
795            FrameUnit::Rows => "ROWS",
796            FrameUnit::Range => "RANGE",
797            FrameUnit::Groups => "GROUPS",
798        };
799
800        let frame_bounds = match &self.end {
801            Some(end) => {
802                format!("{} BETWEEN {} AND {}", unit, self.start.to_sql(), end.to_sql())
803            }
804            None => format!("{} {}", unit, self.start.to_sql()),
805        };
806
807        // Append EXCLUDE clause if present and not the default NO OTHERS
808        match &self.exclude {
809            Some(exclude) if *exclude != FrameExclude::NoOthers => {
810                format!("{} {}", frame_bounds, exclude.to_sql())
811            }
812            _ => frame_bounds,
813        }
814    }
815}
816
817impl ToSql for FrameExclude {
818    fn to_sql(&self) -> String {
819        match self {
820            FrameExclude::NoOthers => "EXCLUDE NO OTHERS".to_string(),
821            FrameExclude::CurrentRow => "EXCLUDE CURRENT ROW".to_string(),
822            FrameExclude::Group => "EXCLUDE GROUP".to_string(),
823            FrameExclude::Ties => "EXCLUDE TIES".to_string(),
824        }
825    }
826}
827
828impl ToSql for FrameBound {
829    fn to_sql(&self) -> String {
830        match self {
831            FrameBound::UnboundedPreceding => "UNBOUNDED PRECEDING".to_string(),
832            FrameBound::Preceding(n) => format!("{} PRECEDING", n.to_sql()),
833            FrameBound::CurrentRow => "CURRENT ROW".to_string(),
834            FrameBound::Following(n) => format!("{} FOLLOWING", n.to_sql()),
835            FrameBound::UnboundedFollowing => "UNBOUNDED FOLLOWING".to_string(),
836        }
837    }
838}
839
840// ============================================================================
841// SELECT Statement
842// ============================================================================
843
844impl ToSql for SelectStmt {
845    fn to_sql(&self) -> String {
846        let mut result = String::new();
847
848        // WITH clause
849        if let Some(ctes) = &self.with_clause {
850            let cte_strs: Vec<String> = ctes.iter().map(|c| c.to_sql()).collect();
851            result.push_str(&format!("WITH {} ", cte_strs.join(", ")));
852        }
853
854        // Check if this is a VALUES statement
855        if let Some(rows) = &self.values {
856            // VALUES clause
857            result.push_str("VALUES");
858            let row_strs: Vec<String> = rows
859                .iter()
860                .map(|row| {
861                    let exprs: Vec<String> = row.iter().map(|e| e.to_sql()).collect();
862                    format!("({})", exprs.join(", "))
863                })
864                .collect();
865            result.push_str(&row_strs.join(", "));
866        } else {
867            // SELECT
868            result.push_str("SELECT ");
869
870            // DISTINCT
871            if self.distinct {
872                result.push_str("DISTINCT ");
873            }
874
875            // Select list
876            let items: Vec<String> = self.select_list.iter().map(|i| i.to_sql()).collect();
877            result.push_str(&items.join(", "));
878
879            // INTO clause (DDL)
880            if let Some(table) = &self.into_table {
881                result.push_str(&format!(" INTO {}", format_identifier(table)));
882            }
883
884            // INTO variables (procedural)
885            if let Some(vars) = &self.into_variables {
886                result.push_str(&format!(" INTO {}", vars.join(", ")));
887            }
888
889            // FROM
890            if let Some(from) = &self.from {
891                result.push_str(&format!(" FROM {}", from.to_sql()));
892            }
893
894            // WHERE
895            if let Some(where_clause) = &self.where_clause {
896                result.push_str(&format!(" WHERE {}", where_clause.to_sql()));
897            }
898
899            // GROUP BY
900            if let Some(group_by) = &self.group_by {
901                result.push_str(&format!(" GROUP BY {}", group_by.to_sql()));
902            }
903
904            // HAVING
905            if let Some(having) = &self.having {
906                result.push_str(&format!(" HAVING {}", having.to_sql()));
907            }
908        }
909
910        // ORDER BY (applies to both SELECT and VALUES)
911        if let Some(order_by) = &self.order_by {
912            let items: Vec<String> = order_by.iter().map(|o| o.to_sql()).collect();
913            result.push_str(&format!(" ORDER BY {}", items.join(", ")));
914        }
915
916        // LIMIT
917        if let Some(limit) = &self.limit {
918            result.push_str(&format!(" LIMIT {}", limit.to_sql()));
919        }
920
921        // OFFSET
922        if let Some(offset) = &self.offset {
923            result.push_str(&format!(" OFFSET {}", offset.to_sql()));
924        }
925
926        // Set operation
927        if let Some(set_op) = &self.set_operation {
928            result.push_str(&format!(" {}", set_op.to_sql()));
929        }
930
931        result
932    }
933}
934
935impl ToSql for CommonTableExpr {
936    fn to_sql(&self) -> String {
937        let mut result = format_identifier(&self.name);
938        if let Some(cols) = &self.columns {
939            result.push_str(&format!(" ({})", cols.join(", ")));
940        }
941        // Include materialization hint if specified
942        let materialization_hint = match self.materialization {
943            CteMaterialization::Default => "",
944            CteMaterialization::Materialized => " MATERIALIZED",
945            CteMaterialization::NotMaterialized => " NOT MATERIALIZED",
946        };
947        result.push_str(&format!(" AS{}({})", materialization_hint, self.query.to_sql()));
948        result
949    }
950}
951
952impl ToSql for SelectItem {
953    fn to_sql(&self) -> String {
954        match self {
955            SelectItem::Wildcard { alias } => match alias {
956                Some(cols) => format!("* AS ({})", cols.join(", ")),
957                None => "*".to_string(),
958            },
959            SelectItem::QualifiedWildcard { qualifier, alias } => {
960                let base = format!("{}.*", format_identifier(qualifier));
961                match alias {
962                    Some(cols) => format!("{} AS ({})", base, cols.join(", ")),
963                    None => base,
964                }
965            }
966            SelectItem::Expression { expr, alias, .. } => {
967                let expr_sql = expr.to_sql();
968                match alias {
969                    Some(a) => format!("{} AS {}", expr_sql, format_identifier(a)),
970                    None => expr_sql,
971                }
972            }
973        }
974    }
975}
976
977impl ToSql for FromClause {
978    fn to_sql(&self) -> String {
979        match self {
980            FromClause::Table { name, alias, column_aliases, quoted } => {
981                // Format name with quotes if it was originally quoted
982                let mut result = if *quoted {
983                    format!("\"{}\"", name.replace('"', "\"\""))
984                } else {
985                    format_identifier(name)
986                };
987                if let Some(a) = alias {
988                    result.push_str(&format!(" AS {}", format_identifier(a)));
989                    if let Some(cols) = column_aliases {
990                        result.push_str(&format!(" ({})", cols.join(", ")));
991                    }
992                }
993                result
994            }
995            FromClause::Join { left, right, join_type, condition, using_columns, natural, alias } => {
996                let mut result = left.to_sql();
997
998                if *natural {
999                    result.push_str(" NATURAL");
1000                }
1001
1002                result.push_str(&format!(" {} {}", join_type.to_sql(), right.to_sql()));
1003
1004                if let Some(cond) = condition {
1005                    result.push_str(&format!(" ON {}", cond.to_sql()));
1006                }
1007
1008                if let Some(cols) = using_columns {
1009                    result.push_str(&format!(" USING ({})", cols.join(", ")));
1010                }
1011
1012                if let Some(a) = alias {
1013                    result.push_str(&format!(" AS {}", format_identifier(a)));
1014                }
1015
1016                result
1017            }
1018            FromClause::Subquery { query, alias, column_aliases } => {
1019                let mut result = format!("({}) AS {}", query.to_sql(), format_identifier(alias));
1020                if let Some(cols) = column_aliases {
1021                    result.push_str(&format!(" ({})", cols.join(", ")));
1022                }
1023                result
1024            }
1025            FromClause::Values { rows, alias, column_aliases } => {
1026                let rows_sql: Vec<String> = rows
1027                    .iter()
1028                    .map(|row| {
1029                        let cols: Vec<String> = row.iter().map(|e| e.to_sql()).collect();
1030                        format!("({})", cols.join(", "))
1031                    })
1032                    .collect();
1033                let mut result =
1034                    format!("(VALUES {}) AS {}", rows_sql.join(", "), format_identifier(alias));
1035                if let Some(cols) = column_aliases {
1036                    result.push_str(&format!(" ({})", cols.join(", ")));
1037                }
1038                result
1039            }
1040        }
1041    }
1042}
1043
1044impl ToSql for JoinType {
1045    fn to_sql(&self) -> String {
1046        match self {
1047            JoinType::Inner => "INNER JOIN".to_string(),
1048            JoinType::LeftOuter => "LEFT OUTER JOIN".to_string(),
1049            JoinType::RightOuter => "RIGHT OUTER JOIN".to_string(),
1050            JoinType::FullOuter => "FULL OUTER JOIN".to_string(),
1051            JoinType::Cross => "CROSS JOIN".to_string(),
1052            JoinType::Semi => "SEMI JOIN".to_string(),
1053            JoinType::Anti => "ANTI JOIN".to_string(),
1054        }
1055    }
1056}
1057
1058impl ToSql for OrderByItem {
1059    fn to_sql(&self) -> String {
1060        format!("{} {}", self.expr.to_sql(), self.direction.to_sql())
1061    }
1062}
1063
1064impl ToSql for OrderDirection {
1065    fn to_sql(&self) -> String {
1066        match self {
1067            OrderDirection::Asc => "ASC".to_string(),
1068            OrderDirection::Desc => "DESC".to_string(),
1069        }
1070    }
1071}
1072
1073impl ToSql for SetOperation {
1074    fn to_sql(&self) -> String {
1075        let op = self.op.to_sql();
1076        let all_str = if self.all { " ALL" } else { "" };
1077        format!("{}{} {}", op, all_str, self.right.to_sql())
1078    }
1079}
1080
1081impl ToSql for SetOperator {
1082    fn to_sql(&self) -> String {
1083        match self {
1084            SetOperator::Union => "UNION".to_string(),
1085            SetOperator::Intersect => "INTERSECT".to_string(),
1086            SetOperator::Except => "EXCEPT".to_string(),
1087        }
1088    }
1089}
1090
1091// ============================================================================
1092// GROUP BY
1093// ============================================================================
1094
1095impl ToSql for GroupByClause {
1096    fn to_sql(&self) -> String {
1097        match self {
1098            GroupByClause::Simple(exprs) => {
1099                let strs: Vec<String> = exprs.iter().map(|e| e.to_sql()).collect();
1100                strs.join(", ")
1101            }
1102            GroupByClause::Rollup(elements) => {
1103                let strs: Vec<String> = elements.iter().map(|e| e.to_sql()).collect();
1104                format!("ROLLUP({})", strs.join(", "))
1105            }
1106            GroupByClause::Cube(elements) => {
1107                let strs: Vec<String> = elements.iter().map(|e| e.to_sql()).collect();
1108                format!("CUBE({})", strs.join(", "))
1109            }
1110            GroupByClause::GroupingSets(sets) => {
1111                let strs: Vec<String> = sets.iter().map(|s| s.to_sql()).collect();
1112                format!("GROUPING SETS({})", strs.join(", "))
1113            }
1114            GroupByClause::Mixed(items) => {
1115                let strs: Vec<String> = items.iter().map(|i| i.to_sql()).collect();
1116                strs.join(", ")
1117            }
1118        }
1119    }
1120}
1121
1122impl ToSql for GroupingElement {
1123    fn to_sql(&self) -> String {
1124        match self {
1125            GroupingElement::Single(expr) => expr.to_sql(),
1126            GroupingElement::Composite(exprs) => {
1127                let strs: Vec<String> = exprs.iter().map(|e| e.to_sql()).collect();
1128                format!("({})", strs.join(", "))
1129            }
1130        }
1131    }
1132}
1133
1134impl ToSql for GroupingSet {
1135    fn to_sql(&self) -> String {
1136        if self.columns.is_empty() {
1137            "()".to_string()
1138        } else {
1139            let strs: Vec<String> = self.columns.iter().map(|e| e.to_sql()).collect();
1140            format!("({})", strs.join(", "))
1141        }
1142    }
1143}
1144
1145impl ToSql for MixedGroupingItem {
1146    fn to_sql(&self) -> String {
1147        match self {
1148            MixedGroupingItem::Simple(expr) => expr.to_sql(),
1149            MixedGroupingItem::Rollup(elements) => {
1150                let strs: Vec<String> = elements.iter().map(|e| e.to_sql()).collect();
1151                format!("ROLLUP({})", strs.join(", "))
1152            }
1153            MixedGroupingItem::Cube(elements) => {
1154                let strs: Vec<String> = elements.iter().map(|e| e.to_sql()).collect();
1155                format!("CUBE({})", strs.join(", "))
1156            }
1157            MixedGroupingItem::GroupingSets(sets) => {
1158                let strs: Vec<String> = sets.iter().map(|s| s.to_sql()).collect();
1159                format!("GROUPING SETS({})", strs.join(", "))
1160            }
1161        }
1162    }
1163}
1164
1165// ============================================================================
1166// Tests
1167// ============================================================================
1168
1169#[cfg(test)]
1170mod tests {
1171    use super::*;
1172    use crate::{ColumnIdentifier, FunctionIdentifier};
1173
1174    #[test]
1175    fn test_binary_operators() {
1176        assert_eq!(BinaryOperator::Plus.to_sql(), "+");
1177        assert_eq!(BinaryOperator::Equal.to_sql(), "=");
1178        assert_eq!(BinaryOperator::And.to_sql(), "AND");
1179        assert_eq!(BinaryOperator::Or.to_sql(), "OR");
1180        assert_eq!(BinaryOperator::Concat.to_sql(), "||");
1181    }
1182
1183    #[test]
1184    fn test_unary_operators() {
1185        assert_eq!(UnaryOperator::Not.to_sql(), "NOT");
1186        assert_eq!(UnaryOperator::Minus.to_sql(), "-");
1187        assert_eq!(UnaryOperator::IsNull.to_sql(), "IS NULL");
1188    }
1189
1190    #[test]
1191    fn test_sql_values() {
1192        assert_eq!(SqlValue::Integer(42).to_sql(), "42");
1193        assert_eq!(SqlValue::Varchar("hello".into()).to_sql(), "'hello'");
1194        assert_eq!(SqlValue::Varchar("it's".into()).to_sql(), "'it''s'");
1195        assert_eq!(SqlValue::Boolean(true).to_sql(), "TRUE");
1196        assert_eq!(SqlValue::Null.to_sql(), "NULL");
1197    }
1198
1199    #[test]
1200    fn test_data_types() {
1201        assert_eq!(DataType::Integer.to_sql(), "INTEGER");
1202        assert_eq!(DataType::Varchar { max_length: Some(255) }.to_sql(), "VARCHAR(255)");
1203        assert_eq!(DataType::Numeric { precision: 10, scale: 2 }.to_sql(), "NUMERIC(10, 2)");
1204        assert_eq!(DataType::Boolean.to_sql(), "BOOLEAN");
1205    }
1206
1207    #[test]
1208    fn test_column_ref() {
1209        let expr = Expression::ColumnRef(ColumnIdentifier::simple("id", false));
1210        assert_eq!(expr.to_sql(), "id");
1211
1212        let expr =
1213            Expression::ColumnRef(ColumnIdentifier::qualified("users", false, "name", false));
1214        assert_eq!(expr.to_sql(), "users.name");
1215    }
1216
1217    #[test]
1218    fn test_binary_op() {
1219        let expr = Expression::BinaryOp {
1220            op: BinaryOperator::Equal,
1221            left: Box::new(Expression::ColumnRef(ColumnIdentifier::simple("id", false))),
1222            right: Box::new(Expression::Literal(SqlValue::Integer(1))),
1223        };
1224        // Compact format: no spaces around symbolic operators
1225        assert_eq!(expr.to_sql(), "id=1");
1226    }
1227
1228    #[test]
1229    fn test_simple_select() {
1230        let stmt = SelectStmt {
1231            with_clause: None,
1232            distinct: false,
1233            select_list: vec![SelectItem::Expression {
1234                expr: Expression::ColumnRef(ColumnIdentifier::simple("id", false)),
1235                alias: None,
1236                source_text: None,
1237            }],
1238            into_table: None,
1239            into_variables: None,
1240            from: Some(FromClause::Table {
1241                name: "users".to_string(),
1242                alias: None,
1243                column_aliases: None,
1244                quoted: false,
1245            }),
1246            where_clause: None,
1247            group_by: None,
1248            having: None,
1249            order_by: None,
1250            limit: None,
1251            offset: None,
1252            set_operation: None,
1253            values: None,
1254        };
1255        assert_eq!(stmt.to_sql(), "SELECT id FROM users");
1256    }
1257
1258    #[test]
1259    fn test_select_with_where() {
1260        let stmt = SelectStmt {
1261            with_clause: None,
1262            distinct: false,
1263            select_list: vec![
1264                SelectItem::Expression {
1265                    expr: Expression::ColumnRef(ColumnIdentifier::simple("id", false)),
1266                    alias: None,
1267                    source_text: None,
1268                },
1269                SelectItem::Expression {
1270                    expr: Expression::ColumnRef(ColumnIdentifier::simple("name", false)),
1271                    alias: None,
1272                    source_text: None,
1273                },
1274            ],
1275            into_table: None,
1276            into_variables: None,
1277            from: Some(FromClause::Table {
1278                name: "users".to_string(),
1279                alias: None,
1280                column_aliases: None,
1281                quoted: false,
1282            }),
1283            where_clause: Some(Expression::BinaryOp {
1284                op: BinaryOperator::Equal,
1285                left: Box::new(Expression::ColumnRef(ColumnIdentifier::simple("active", false))),
1286                right: Box::new(Expression::Literal(SqlValue::Integer(1))),
1287            }),
1288            group_by: None,
1289            having: None,
1290            order_by: None,
1291            limit: None,
1292            offset: None,
1293            set_operation: None,
1294            values: None,
1295        };
1296        // Compact format: no spaces around symbolic operators
1297        assert_eq!(stmt.to_sql(), "SELECT id, name FROM users WHERE active=1");
1298    }
1299
1300    #[test]
1301    fn test_select_distinct_with_order() {
1302        let stmt = SelectStmt {
1303            with_clause: None,
1304            distinct: true,
1305            select_list: vec![SelectItem::Expression {
1306                expr: Expression::ColumnRef(ColumnIdentifier::simple("name", false)),
1307                alias: None,
1308                source_text: None,
1309            }],
1310            into_table: None,
1311            into_variables: None,
1312            from: Some(FromClause::Table {
1313                name: "users".to_string(),
1314                alias: None,
1315                column_aliases: None,
1316                quoted: false,
1317            }),
1318            where_clause: None,
1319            group_by: None,
1320            having: None,
1321            order_by: Some(vec![OrderByItem {
1322                expr: Expression::ColumnRef(ColumnIdentifier::simple("name", false)),
1323                direction: OrderDirection::Asc,
1324                nulls_order: None,
1325            }]),
1326            limit: Some(Expression::Literal(SqlValue::Integer(10))),
1327            offset: None,
1328            set_operation: None,
1329            values: None,
1330        };
1331        assert_eq!(stmt.to_sql(), "SELECT DISTINCT name FROM users ORDER BY name ASC LIMIT 10");
1332    }
1333
1334    #[test]
1335    fn test_join() {
1336        let from = FromClause::Join {
1337            left: Box::new(FromClause::Table {
1338                name: "orders".to_string(),
1339                alias: Some("o".to_string()),
1340                column_aliases: None,
1341                quoted: false,
1342            }),
1343            right: Box::new(FromClause::Table {
1344                name: "customers".to_string(),
1345                alias: Some("c".to_string()),
1346                column_aliases: None,
1347                quoted: false,
1348            }),
1349            join_type: JoinType::Inner,
1350            condition: Some(Expression::BinaryOp {
1351                op: BinaryOperator::Equal,
1352                left: Box::new(Expression::ColumnRef(ColumnIdentifier::qualified(
1353                    "o",
1354                    false,
1355                    "customer_id",
1356                    false,
1357                ))),
1358                right: Box::new(Expression::ColumnRef(ColumnIdentifier::qualified(
1359                    "c", false, "id", false,
1360                ))),
1361            }),
1362            using_columns: None,
1363            natural: false,
1364            alias: None,
1365        };
1366
1367        // Compact format: no spaces around symbolic operators
1368        assert_eq!(from.to_sql(), "orders AS o INNER JOIN customers AS c ON o.customer_id=c.id");
1369    }
1370
1371    #[test]
1372    fn test_aggregate_function() {
1373        let expr = Expression::AggregateFunction {
1374            name: FunctionIdentifier::new("count"),
1375            distinct: true,
1376            args: vec![Expression::ColumnRef(ColumnIdentifier::simple("id", false))],
1377            order_by: None,
1378            filter: None,
1379        };
1380        assert_eq!(expr.to_sql(), "COUNT(DISTINCT id)");
1381    }
1382
1383    #[test]
1384    fn test_case_expression() {
1385        let expr = Expression::Case {
1386            operand: None,
1387            when_clauses: vec![CaseWhen {
1388                conditions: vec![Expression::BinaryOp {
1389                    op: BinaryOperator::GreaterThan,
1390                    left: Box::new(Expression::ColumnRef(ColumnIdentifier::simple("x", false))),
1391                    right: Box::new(Expression::Literal(SqlValue::Integer(0))),
1392                }],
1393                result: Expression::Literal(SqlValue::Varchar("positive".into())),
1394            }],
1395            else_result: Some(Box::new(Expression::Literal(SqlValue::Varchar(
1396                "non-positive".into(),
1397            )))),
1398        };
1399        // Compact format: no spaces around symbolic operators
1400        assert_eq!(expr.to_sql(), "CASE WHEN x>0 THEN 'positive' ELSE 'non-positive' END");
1401    }
1402
1403    #[test]
1404    fn test_in_list() {
1405        let expr = Expression::InList {
1406            expr: Box::new(Expression::ColumnRef(ColumnIdentifier::simple("id", false))),
1407            values: vec![
1408                Expression::Literal(SqlValue::Integer(1)),
1409                Expression::Literal(SqlValue::Integer(2)),
1410                Expression::Literal(SqlValue::Integer(3)),
1411            ],
1412            negated: false,
1413        };
1414        assert_eq!(expr.to_sql(), "id IN (1, 2, 3)");
1415    }
1416
1417    #[test]
1418    fn test_between() {
1419        let expr = Expression::Between {
1420            expr: Box::new(Expression::ColumnRef(ColumnIdentifier::simple("age", false))),
1421            low: Box::new(Expression::Literal(SqlValue::Integer(18))),
1422            high: Box::new(Expression::Literal(SqlValue::Integer(65))),
1423            negated: false,
1424            symmetric: false,
1425        };
1426        assert_eq!(expr.to_sql(), "age BETWEEN 18 AND 65");
1427    }
1428
1429    #[test]
1430    fn test_group_by_rollup() {
1431        let group_by = GroupByClause::Rollup(vec![
1432            GroupingElement::Single(Expression::ColumnRef(ColumnIdentifier::simple("year", false))),
1433            GroupingElement::Single(Expression::ColumnRef(ColumnIdentifier::simple(
1434                "month", false,
1435            ))),
1436        ]);
1437        assert_eq!(group_by.to_sql(), "ROLLUP(year, month)");
1438    }
1439
1440    #[test]
1441    fn test_window_function() {
1442        let expr = Expression::WindowFunction {
1443            function: WindowFunctionSpec::Ranking {
1444                name: FunctionIdentifier::new("row_number"),
1445                args: vec![],
1446            },
1447            over: WindowSpec {
1448                partition_by: Some(vec![Expression::ColumnRef(ColumnIdentifier::simple(
1449                    "dept", false,
1450                ))]),
1451                order_by: Some(vec![OrderByItem {
1452                    expr: Expression::ColumnRef(ColumnIdentifier::simple("salary", false)),
1453                    direction: OrderDirection::Desc,
1454                    nulls_order: None,
1455                }]),
1456                frame: None,
1457            },
1458        };
1459        assert_eq!(expr.to_sql(), "ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC)");
1460    }
1461}
vibesql_ast/pretty_print.rs

vibesql_ast/
pretty_print.rs
