polyglot_sql/
builder.rs

1//! Fluent SQL Builder API
2//!
3//! Provides a programmatic way to construct SQL [`Expression`] trees without parsing raw SQL
4//! strings. The API mirrors Python sqlglot's builder functions (`select()`, `from_()`,
5//! `condition()`, etc.) and is the primary entry point for constructing queries
6//! programmatically in Rust.
7//!
8//! # Design
9//!
10//! The builder is organized around a few key concepts:
11//!
12//! - **Expression helpers** ([`col`], [`lit`], [`star`], [`null`], [`boolean`], [`func`],
13//!   [`cast`], [`alias`], [`sql_expr`], [`condition`]) create leaf-level [`Expr`] values.
14//! - **Query starters** ([`select`], [`from`], [`delete`], [`insert_into`], [`update`])
15//!   return fluent builder structs ([`SelectBuilder`], [`DeleteBuilder`], etc.).
16//! - **[`Expr`]** wraps an [`Expression`] and exposes operator methods (`.eq()`, `.gt()`,
17//!   `.and()`, `.like()`, etc.) so conditions can be built without manual AST construction.
18//! - **[`IntoExpr`]** and **[`IntoLiteral`]** allow ergonomic coercion of `&str`, `i64`,
19//!   `f64`, and other primitives wherever an expression or literal is expected.
20//!
21//! # Examples
22//!
23//! ```
24//! use polyglot_sql::builder::*;
25//!
26//! // SELECT id, name FROM users WHERE age > 18 ORDER BY name LIMIT 10
27//! let expr = select(["id", "name"])
28//!     .from("users")
29//!     .where_(col("age").gt(lit(18)))
30//!     .order_by(["name"])
31//!     .limit(10)
32//!     .build();
33//! ```
34//!
35//! ```
36//! use polyglot_sql::builder::*;
37//!
38//! // CASE WHEN x > 0 THEN 'positive' ELSE 'non-positive' END
39//! let expr = case()
40//!     .when(col("x").gt(lit(0)), lit("positive"))
41//!     .else_(lit("non-positive"))
42//!     .build();
43//! ```
44//!
45//! ```
46//! use polyglot_sql::builder::*;
47//!
48//! // SELECT id FROM a UNION ALL SELECT id FROM b ORDER BY id LIMIT 5
49//! let expr = union_all(
50//!     select(["id"]).from("a"),
51//!     select(["id"]).from("b"),
52//! )
53//! .order_by(["id"])
54//! .limit(5)
55//! .build();
56//! ```
57
58use crate::expressions::*;
59use crate::generator::Generator;
60use crate::parser::Parser;
61
62// ---------------------------------------------------------------------------
63// Expression helpers
64// ---------------------------------------------------------------------------
65
66/// Create a column reference expression.
67///
68/// If `name` contains a dot, it is split on the **last** `.` to produce a table-qualified
69/// column (e.g. `"u.id"` becomes `u.id`). Unqualified names produce a bare column
70/// reference.
71///
72/// # Examples
73///
74/// ```
75/// use polyglot_sql::builder::col;
76///
77/// // Unqualified column
78/// let c = col("name");
79/// assert_eq!(c.to_sql(), "name");
80///
81/// // Table-qualified column
82/// let c = col("users.name");
83/// assert_eq!(c.to_sql(), "users.name");
84/// ```
85pub fn col(name: &str) -> Expr {
86    if let Some((table, column)) = name.rsplit_once('.') {
87        Expr(Expression::Column(Column {
88            name: Identifier::new(column),
89            table: Some(Identifier::new(table)),
90            join_mark: false,
91            trailing_comments: Vec::new(),
92        }))
93    } else {
94        Expr(Expression::Column(Column {
95            name: Identifier::new(name),
96            table: None,
97            join_mark: false,
98            trailing_comments: Vec::new(),
99        }))
100    }
101}
102
103/// Create a literal expression from any type implementing [`IntoLiteral`].
104///
105/// Supported types include `&str` / `String` (string literal), `i32` / `i64` / `usize` /
106/// `f64` (numeric literal), and `bool` (boolean literal).
107///
108/// # Examples
109///
110/// ```
111/// use polyglot_sql::builder::lit;
112///
113/// let s = lit("hello");   // 'hello'
114/// let n = lit(42);        // 42
115/// let f = lit(3.14);      // 3.14
116/// let b = lit(true);      // TRUE
117/// ```
118pub fn lit<V: IntoLiteral>(value: V) -> Expr {
119    value.into_literal()
120}
121
122/// Create a star (`*`) expression, typically used in `SELECT *`.
123pub fn star() -> Expr {
124    Expr(Expression::star())
125}
126
127/// Create a SQL `NULL` literal expression.
128pub fn null() -> Expr {
129    Expr(Expression::Null(Null))
130}
131
132/// Create a SQL boolean literal expression (`TRUE` or `FALSE`).
133pub fn boolean(value: bool) -> Expr {
134    Expr(Expression::Boolean(BooleanLiteral { value }))
135}
136
137/// Create a table reference expression.
138///
139/// The `name` string is split on `.` to determine qualification level:
140///
141/// - `"table"` -- unqualified table reference
142/// - `"schema.table"` -- schema-qualified
143/// - `"catalog.schema.table"` -- fully qualified with catalog
144///
145/// # Examples
146///
147/// ```
148/// use polyglot_sql::builder::table;
149///
150/// let t = table("my_schema.users");
151/// assert_eq!(t.to_sql(), "my_schema.users");
152/// ```
153pub fn table(name: &str) -> Expr {
154    let parts: Vec<&str> = name.split('.').collect();
155    let table_ref = match parts.len() {
156        3 => TableRef::new_with_catalog(parts[2], parts[1], parts[0]),
157        2 => TableRef::new_with_schema(parts[1], parts[0]),
158        _ => TableRef::new(parts[0]),
159    };
160    Expr(Expression::Table(table_ref))
161}
162
163/// Create a SQL function call expression.
164///
165/// `name` is the function name (e.g. `"COUNT"`, `"UPPER"`, `"COALESCE"`), and `args`
166/// provides zero or more argument expressions.
167///
168/// # Examples
169///
170/// ```
171/// use polyglot_sql::builder::{func, col, star};
172///
173/// let upper = func("UPPER", [col("name")]);
174/// assert_eq!(upper.to_sql(), "UPPER(name)");
175///
176/// let count = func("COUNT", [star()]);
177/// assert_eq!(count.to_sql(), "COUNT(*)");
178/// ```
179pub fn func(name: &str, args: impl IntoIterator<Item = Expr>) -> Expr {
180    Expr(Expression::Function(Box::new(Function {
181        name: name.to_string(),
182        args: args.into_iter().map(|a| a.0).collect(),
183        ..Function::default()
184    })))
185}
186
187/// Create a `CAST(expr AS type)` expression.
188///
189/// The `to` parameter is parsed as a data type name. Common built-in types (`INT`, `BIGINT`,
190/// `VARCHAR`, `BOOLEAN`, `TIMESTAMP`, etc.) are recognized directly. More complex types
191/// (e.g. `"DECIMAL(10,2)"`, `"ARRAY<INT>"`) are parsed via the full SQL parser as a
192/// fallback.
193///
194/// # Examples
195///
196/// ```
197/// use polyglot_sql::builder::{cast, col};
198///
199/// let expr = cast(col("id"), "VARCHAR");
200/// assert_eq!(expr.to_sql(), "CAST(id AS VARCHAR)");
201/// ```
202pub fn cast(expr: Expr, to: &str) -> Expr {
203    let data_type = parse_simple_data_type(to);
204    Expr(Expression::Cast(Box::new(Cast {
205        this: expr.0,
206        to: data_type,
207        trailing_comments: Vec::new(),
208        double_colon_syntax: false,
209        format: None,
210        default: None,
211    })))
212}
213
214/// Create a `NOT expr` unary expression.
215///
216/// Wraps the given expression in a logical negation. Equivalent to calling
217/// [`Expr::not()`] on the expression.
218pub fn not(expr: Expr) -> Expr {
219    Expr(Expression::Not(Box::new(UnaryOp::new(expr.0))))
220}
221
222/// Combine two expressions with `AND`.
223///
224/// Equivalent to `left.and(right)`. Useful when you do not have the left-hand side
225/// as the receiver.
226pub fn and(left: Expr, right: Expr) -> Expr {
227    left.and(right)
228}
229
230/// Combine two expressions with `OR`.
231///
232/// Equivalent to `left.or(right)`. Useful when you do not have the left-hand side
233/// as the receiver.
234pub fn or(left: Expr, right: Expr) -> Expr {
235    left.or(right)
236}
237
238/// Create an `expr AS name` alias expression.
239///
240/// This is the free-function form. The method form [`Expr::alias()`] is often more
241/// convenient for chaining.
242pub fn alias(expr: Expr, name: &str) -> Expr {
243    Expr(Expression::Alias(Box::new(Alias {
244        this: expr.0,
245        alias: Identifier::new(name),
246        column_aliases: Vec::new(),
247        pre_alias_comments: Vec::new(),
248        trailing_comments: Vec::new(),
249    })))
250}
251
252/// Parse a raw SQL expression fragment into an [`Expr`].
253///
254/// Internally wraps the string in `SELECT <sql>`, parses it with the full SQL parser,
255/// and extracts the first expression from the SELECT list. This is useful for
256/// embedding complex SQL fragments (window functions, subquery predicates, etc.)
257/// that would be cumbersome to build purely through the builder API.
258///
259/// # Examples
260///
261/// ```
262/// use polyglot_sql::builder::sql_expr;
263///
264/// let expr = sql_expr("COALESCE(a, b, 0)");
265/// assert_eq!(expr.to_sql(), "COALESCE(a, b, 0)");
266///
267/// let cond = sql_expr("age > 18 AND status = 'active'");
268/// ```
269///
270/// # Panics
271///
272/// Panics if the SQL fragment cannot be parsed, or if the parser fails to extract a
273/// valid expression from the result. Invalid SQL will cause a panic with a message
274/// prefixed by `"sql_expr:"`.
275pub fn sql_expr(sql: &str) -> Expr {
276    let wrapped = format!("SELECT {}", sql);
277    let ast = Parser::parse_sql(&wrapped).expect("sql_expr: failed to parse SQL expression");
278    if let Expression::Select(s) = &ast[0] {
279        if let Some(first) = s.expressions.first() {
280            return Expr(first.clone());
281        }
282    }
283    panic!("sql_expr: failed to extract expression from parsed SQL");
284}
285
286/// Parse a SQL condition string into an [`Expr`].
287///
288/// This is a convenience alias for [`sql_expr()`]. The name `condition` reads more
289/// naturally when the fragment is intended as a WHERE or HAVING predicate.
290///
291/// # Panics
292///
293/// Panics under the same conditions as [`sql_expr()`].
294pub fn condition(sql: &str) -> Expr {
295    sql_expr(sql)
296}
297
298// ---------------------------------------------------------------------------
299// Query starters
300// ---------------------------------------------------------------------------
301
302/// Start building a SELECT query with the given column expressions.
303///
304/// Accepts any iterable of items implementing [`IntoExpr`], which includes `&str`
305/// (interpreted as column names), [`Expr`] values, and raw [`Expression`] nodes.
306/// Returns a [`SelectBuilder`] that can be further refined with `.from()`, `.where_()`,
307/// `.order_by()`, etc.
308///
309/// # Examples
310///
311/// ```
312/// use polyglot_sql::builder::*;
313///
314/// // Using string slices (converted to column refs automatically)
315/// let sql = select(["id", "name"]).from("users").to_sql();
316/// assert_eq!(sql, "SELECT id, name FROM users");
317///
318/// // Using Expr values for computed columns
319/// let sql = select([col("price").mul(col("qty")).alias("total")])
320///     .from("items")
321///     .to_sql();
322/// assert_eq!(sql, "SELECT price * qty AS total FROM items");
323/// ```
324pub fn select<I, E>(expressions: I) -> SelectBuilder
325where
326    I: IntoIterator<Item = E>,
327    E: IntoExpr,
328{
329    let mut builder = SelectBuilder::new();
330    for expr in expressions {
331        builder.select = builder.select.column(expr.into_expr().0);
332    }
333    builder
334}
335
336/// Start building a SELECT query beginning with a FROM clause.
337///
338/// Returns a [`SelectBuilder`] with the FROM clause already set. Use
339/// [`SelectBuilder::select_cols()`] to add columns afterward. This is an alternative
340/// entry point for queries where specifying the table first feels more natural.
341///
342/// # Examples
343///
344/// ```
345/// use polyglot_sql::builder::*;
346///
347/// let sql = from("users").select_cols(["id", "name"]).to_sql();
348/// assert_eq!(sql, "SELECT id, name FROM users");
349/// ```
350pub fn from(table_name: &str) -> SelectBuilder {
351    let mut builder = SelectBuilder::new();
352    builder.select.from = Some(From {
353        expressions: vec![Expression::Table(TableRef::new(table_name))],
354    });
355    builder
356}
357
358/// Start building a `DELETE FROM` statement targeting the given table.
359///
360/// Returns a [`DeleteBuilder`] which supports `.where_()` to add a predicate.
361///
362/// # Examples
363///
364/// ```
365/// use polyglot_sql::builder::*;
366///
367/// let sql = delete("users").where_(col("id").eq(lit(1))).to_sql();
368/// assert_eq!(sql, "DELETE FROM users WHERE id = 1");
369/// ```
370pub fn delete(table_name: &str) -> DeleteBuilder {
371    DeleteBuilder {
372        delete: Delete {
373            table: TableRef::new(table_name),
374            on_cluster: None,
375            alias: None,
376            alias_explicit_as: false,
377            using: Vec::new(),
378            where_clause: None,
379            output: None,
380            leading_comments: Vec::new(),
381            with: None,
382            limit: None,
383            order_by: None,
384            returning: Vec::new(),
385            tables: Vec::new(),
386            tables_from_using: false,
387            joins: Vec::new(),
388            force_index: None,
389            no_from: false,
390        },
391    }
392}
393
394/// Start building an `INSERT INTO` statement targeting the given table.
395///
396/// Returns an [`InsertBuilder`] which supports `.columns()`, `.values()`, and
397/// `.query()` for INSERT ... SELECT.
398///
399/// # Examples
400///
401/// ```
402/// use polyglot_sql::builder::*;
403///
404/// let sql = insert_into("users")
405///     .columns(["id", "name"])
406///     .values([lit(1), lit("Alice")])
407///     .to_sql();
408/// assert_eq!(sql, "INSERT INTO users (id, name) VALUES (1, 'Alice')");
409/// ```
410pub fn insert_into(table_name: &str) -> InsertBuilder {
411    InsertBuilder {
412        insert: Insert {
413            table: TableRef::new(table_name),
414            columns: Vec::new(),
415            values: Vec::new(),
416            query: None,
417            overwrite: false,
418            partition: Vec::new(),
419            directory: None,
420            returning: Vec::new(),
421            output: None,
422            on_conflict: None,
423            leading_comments: Vec::new(),
424            if_exists: false,
425            with: None,
426            ignore: false,
427            source_alias: None,
428            alias: None,
429            alias_explicit_as: false,
430            default_values: false,
431            by_name: false,
432            conflict_action: None,
433            is_replace: false,
434            hint: None,
435            replace_where: None,
436            source: None,
437            function_target: None,
438            partition_by: None,
439            settings: Vec::new(),
440        },
441    }
442}
443
444/// Start building an `UPDATE` statement targeting the given table.
445///
446/// Returns an [`UpdateBuilder`] which supports `.set()` for column assignments,
447/// `.where_()` for predicates, and `.from()` for PostgreSQL/Snowflake-style
448/// UPDATE ... FROM syntax.
449///
450/// # Examples
451///
452/// ```
453/// use polyglot_sql::builder::*;
454///
455/// let sql = update("users")
456///     .set("name", lit("Bob"))
457///     .where_(col("id").eq(lit(1)))
458///     .to_sql();
459/// assert_eq!(sql, "UPDATE users SET name = 'Bob' WHERE id = 1");
460/// ```
461pub fn update(table_name: &str) -> UpdateBuilder {
462    UpdateBuilder {
463        update: Update {
464            table: TableRef::new(table_name),
465            extra_tables: Vec::new(),
466            table_joins: Vec::new(),
467            set: Vec::new(),
468            from_clause: None,
469            from_joins: Vec::new(),
470            where_clause: None,
471            returning: Vec::new(),
472            output: None,
473            with: None,
474            leading_comments: Vec::new(),
475            limit: None,
476            order_by: None,
477            from_before_set: false,
478        },
479    }
480}
481
482// ---------------------------------------------------------------------------
483// Expr wrapper (for operator methods)
484// ---------------------------------------------------------------------------
485
486/// A thin wrapper around [`Expression`] that provides fluent operator methods.
487///
488/// `Expr` is the primary value type flowing through the builder API. It wraps a single
489/// AST [`Expression`] node and adds convenience methods for comparisons (`.eq()`,
490/// `.gt()`, etc.), logical connectives (`.and()`, `.or()`, `.not()`), arithmetic
491/// (`.add()`, `.sub()`, `.mul()`, `.div()`), pattern matching (`.like()`, `.ilike()`,
492/// `.rlike()`), and other SQL operations (`.in_list()`, `.between()`, `.is_null()`,
493/// `.alias()`, `.cast()`, `.asc()`, `.desc()`).
494///
495/// The inner [`Expression`] is publicly accessible via the `.0` field or
496/// [`Expr::into_inner()`].
497///
498/// # Examples
499///
500/// ```
501/// use polyglot_sql::builder::*;
502///
503/// let condition = col("age").gte(lit(18)).and(col("active").eq(boolean(true)));
504/// assert_eq!(condition.to_sql(), "age >= 18 AND active = TRUE");
505/// ```
506#[derive(Debug, Clone)]
507pub struct Expr(pub Expression);
508
509impl Expr {
510    /// Consume this wrapper and return the inner [`Expression`] node.
511    pub fn into_inner(self) -> Expression {
512        self.0
513    }
514
515    /// Generate a SQL string from this expression using the default (generic) dialect.
516    ///
517    /// Returns an empty string if generation fails.
518    pub fn to_sql(&self) -> String {
519        Generator::sql(&self.0).unwrap_or_default()
520    }
521
522    // -- Comparison operators --
523
524    /// Produce a `self = other` equality comparison.
525    pub fn eq(self, other: Expr) -> Expr {
526        Expr(Expression::Eq(Box::new(binary_op(self.0, other.0))))
527    }
528
529    /// Produce a `self <> other` inequality comparison.
530    pub fn neq(self, other: Expr) -> Expr {
531        Expr(Expression::Neq(Box::new(binary_op(self.0, other.0))))
532    }
533
534    /// Produce a `self < other` less-than comparison.
535    pub fn lt(self, other: Expr) -> Expr {
536        Expr(Expression::Lt(Box::new(binary_op(self.0, other.0))))
537    }
538
539    /// Produce a `self <= other` less-than-or-equal comparison.
540    pub fn lte(self, other: Expr) -> Expr {
541        Expr(Expression::Lte(Box::new(binary_op(self.0, other.0))))
542    }
543
544    /// Produce a `self > other` greater-than comparison.
545    pub fn gt(self, other: Expr) -> Expr {
546        Expr(Expression::Gt(Box::new(binary_op(self.0, other.0))))
547    }
548
549    /// Produce a `self >= other` greater-than-or-equal comparison.
550    pub fn gte(self, other: Expr) -> Expr {
551        Expr(Expression::Gte(Box::new(binary_op(self.0, other.0))))
552    }
553
554    // -- Logical operators --
555
556    /// Produce a `self AND other` logical conjunction.
557    pub fn and(self, other: Expr) -> Expr {
558        Expr(Expression::And(Box::new(binary_op(self.0, other.0))))
559    }
560
561    /// Produce a `self OR other` logical disjunction.
562    pub fn or(self, other: Expr) -> Expr {
563        Expr(Expression::Or(Box::new(binary_op(self.0, other.0))))
564    }
565
566    /// Produce a `NOT self` logical negation.
567    pub fn not(self) -> Expr {
568        Expr(Expression::Not(Box::new(UnaryOp::new(self.0))))
569    }
570
571    /// Produce a `self XOR other` logical exclusive-or.
572    pub fn xor(self, other: Expr) -> Expr {
573        Expr(Expression::Xor(Box::new(Xor {
574            this: Some(Box::new(self.0)),
575            expression: Some(Box::new(other.0)),
576            expressions: vec![],
577        })))
578    }
579
580    // -- Arithmetic operators --
581
582    /// Produce a `self + other` addition expression.
583    pub fn add(self, other: Expr) -> Expr {
584        Expr(Expression::Add(Box::new(binary_op(self.0, other.0))))
585    }
586
587    /// Produce a `self - other` subtraction expression.
588    pub fn sub(self, other: Expr) -> Expr {
589        Expr(Expression::Sub(Box::new(binary_op(self.0, other.0))))
590    }
591
592    /// Produce a `self * other` multiplication expression.
593    pub fn mul(self, other: Expr) -> Expr {
594        Expr(Expression::Mul(Box::new(binary_op(self.0, other.0))))
595    }
596
597    /// Produce a `self / other` division expression.
598    pub fn div(self, other: Expr) -> Expr {
599        Expr(Expression::Div(Box::new(binary_op(self.0, other.0))))
600    }
601
602    // -- Other operators --
603
604    /// Produce a `self IS NULL` predicate.
605    pub fn is_null(self) -> Expr {
606        Expr(Expression::Is(Box::new(BinaryOp {
607            left: self.0,
608            right: Expression::Null(Null),
609            left_comments: Vec::new(),
610            operator_comments: Vec::new(),
611            trailing_comments: Vec::new(),
612        })))
613    }
614
615    /// Produce a `self IS NOT NULL` predicate (implemented as `NOT (self IS NULL)`).
616    pub fn is_not_null(self) -> Expr {
617        Expr(Expression::Not(Box::new(UnaryOp::new(
618            Expression::Is(Box::new(BinaryOp {
619                left: self.0,
620                right: Expression::Null(Null),
621                left_comments: Vec::new(),
622                operator_comments: Vec::new(),
623                trailing_comments: Vec::new(),
624            })),
625        ))))
626    }
627
628    /// Produce a `self IN (values...)` membership test.
629    ///
630    /// Each element of `values` becomes an item in the parenthesized list.
631    pub fn in_list(self, values: impl IntoIterator<Item = Expr>) -> Expr {
632        Expr(Expression::In(Box::new(In {
633            this: self.0,
634            expressions: values.into_iter().map(|v| v.0).collect(),
635            query: None,
636            not: false,
637            global: false,
638            unnest: None,
639        })))
640    }
641
642    /// Produce a `self BETWEEN low AND high` range test.
643    pub fn between(self, low: Expr, high: Expr) -> Expr {
644        Expr(Expression::Between(Box::new(Between {
645            this: self.0,
646            low: low.0,
647            high: high.0,
648            not: false,
649        })))
650    }
651
652    /// Produce a `self LIKE pattern` case-sensitive pattern match.
653    pub fn like(self, pattern: Expr) -> Expr {
654        Expr(Expression::Like(Box::new(LikeOp {
655            left: self.0,
656            right: pattern.0,
657            escape: None,
658            quantifier: None,
659        })))
660    }
661
662    /// Produce a `self AS alias` expression alias.
663    pub fn alias(self, name: &str) -> Expr {
664        alias(self, name)
665    }
666
667    /// Produce a `CAST(self AS type)` type conversion.
668    ///
669    /// The `to` parameter is parsed as a data type name; see [`cast()`] for details.
670    pub fn cast(self, to: &str) -> Expr {
671        cast(self, to)
672    }
673
674    /// Wrap this expression with ascending sort order (`self ASC`).
675    ///
676    /// Used in ORDER BY clauses. Expressions without an explicit `.asc()` or `.desc()`
677    /// call default to ascending order when passed to [`SelectBuilder::order_by()`].
678    pub fn asc(self) -> Expr {
679        Expr(Expression::Ordered(Box::new(Ordered {
680            this: self.0,
681            desc: false,
682            nulls_first: None,
683            explicit_asc: true,
684            with_fill: None,
685        })))
686    }
687
688    /// Wrap this expression with descending sort order (`self DESC`).
689    ///
690    /// Used in ORDER BY clauses.
691    pub fn desc(self) -> Expr {
692        Expr(Expression::Ordered(Box::new(Ordered {
693            this: self.0,
694            desc: true,
695            nulls_first: None,
696            explicit_asc: false,
697            with_fill: None,
698        })))
699    }
700
701    /// Produce a `self ILIKE pattern` case-insensitive pattern match.
702    ///
703    /// Supported by PostgreSQL, Snowflake, and other dialects. Dialects that do not
704    /// support `ILIKE` natively may need transpilation.
705    pub fn ilike(self, pattern: Expr) -> Expr {
706        Expr(Expression::ILike(Box::new(LikeOp {
707            left: self.0,
708            right: pattern.0,
709            escape: None,
710            quantifier: None,
711        })))
712    }
713
714    /// Produce a `REGEXP_LIKE(self, pattern)` regular expression match.
715    ///
716    /// The generated SQL uses the `REGEXP_LIKE` function form. Different dialects may
717    /// render this as `RLIKE`, `REGEXP`, or `REGEXP_LIKE` after transpilation.
718    pub fn rlike(self, pattern: Expr) -> Expr {
719        Expr(Expression::RegexpLike(Box::new(RegexpFunc {
720            this: self.0,
721            pattern: pattern.0,
722            flags: None,
723        })))
724    }
725
726    /// Produce a `self NOT IN (values...)` negated membership test.
727    ///
728    /// Each element of `values` becomes an item in the parenthesized list.
729    pub fn not_in(self, values: impl IntoIterator<Item = Expr>) -> Expr {
730        Expr(Expression::In(Box::new(In {
731            this: self.0,
732            expressions: values.into_iter().map(|v| v.0).collect(),
733            query: None,
734            not: true,
735            global: false,
736            unnest: None,
737        })))
738    }
739}
740
741// ---------------------------------------------------------------------------
742// SelectBuilder
743// ---------------------------------------------------------------------------
744
745/// Fluent builder for constructing `SELECT` statements.
746///
747/// Created by the [`select()`] or [`from()`] entry-point functions. Methods on this
748/// builder return `self` so they can be chained. Call [`.build()`](SelectBuilder::build)
749/// to obtain an [`Expression`], or [`.to_sql()`](SelectBuilder::to_sql) to generate a
750/// SQL string directly.
751///
752/// # Examples
753///
754/// ```
755/// use polyglot_sql::builder::*;
756///
757/// let sql = select(["u.id", "u.name"])
758///     .from("users")
759///     .left_join("orders", col("u.id").eq(col("o.user_id")))
760///     .where_(col("u.active").eq(boolean(true)))
761///     .group_by(["u.id", "u.name"])
762///     .order_by([col("u.name").asc()])
763///     .limit(100)
764///     .to_sql();
765/// ```
766pub struct SelectBuilder {
767    select: Select,
768}
769
770impl SelectBuilder {
771    fn new() -> Self {
772        SelectBuilder {
773            select: Select::new(),
774        }
775    }
776
777    /// Append columns to the SELECT list.
778    ///
779    /// Accepts any iterable of [`IntoExpr`] items. This is primarily useful when the
780    /// builder was created via [`from()`] and columns need to be added afterward.
781    pub fn select_cols<I, E>(mut self, expressions: I) -> Self
782    where
783        I: IntoIterator<Item = E>,
784        E: IntoExpr,
785    {
786        for expr in expressions {
787            self.select.expressions.push(expr.into_expr().0);
788        }
789        self
790    }
791
792    /// Set the FROM clause to reference the given table by name.
793    pub fn from(mut self, table_name: &str) -> Self {
794        self.select.from = Some(From {
795            expressions: vec![Expression::Table(TableRef::new(table_name))],
796        });
797        self
798    }
799
800    /// Set the FROM clause to an arbitrary expression (e.g. a subquery or table function).
801    ///
802    /// Use this instead of [`SelectBuilder::from()`] when the source is not a simple
803    /// table name -- for example, a [`subquery()`] or a table-valued function.
804    pub fn from_expr(mut self, expr: Expr) -> Self {
805        self.select.from = Some(From {
806            expressions: vec![expr.0],
807        });
808        self
809    }
810
811    /// Add an inner `JOIN` clause with the given ON condition.
812    pub fn join(mut self, table_name: &str, on: Expr) -> Self {
813        self.select.joins.push(Join {
814            kind: JoinKind::Inner,
815            this: Expression::Table(TableRef::new(table_name)),
816            on: Some(on.0),
817            using: Vec::new(),
818            use_inner_keyword: false,
819            use_outer_keyword: false,
820            deferred_condition: false,
821            join_hint: None,
822            match_condition: None,
823            pivots: Vec::new(),
824        });
825        self
826    }
827
828    /// Add a `LEFT JOIN` clause with the given ON condition.
829    pub fn left_join(mut self, table_name: &str, on: Expr) -> Self {
830        self.select.joins.push(Join {
831            kind: JoinKind::Left,
832            this: Expression::Table(TableRef::new(table_name)),
833            on: Some(on.0),
834            using: Vec::new(),
835            use_inner_keyword: false,
836            use_outer_keyword: false,
837            deferred_condition: false,
838            join_hint: None,
839            match_condition: None,
840            pivots: Vec::new(),
841        });
842        self
843    }
844
845    /// Set the WHERE clause to filter rows by the given condition.
846    ///
847    /// Calling this multiple times replaces the previous WHERE condition. To combine
848    /// multiple predicates, chain them with [`.and()`](Expr::and) or [`.or()`](Expr::or)
849    /// on a single [`Expr`].
850    pub fn where_(mut self, condition: Expr) -> Self {
851        self.select.where_clause = Some(Where { this: condition.0 });
852        self
853    }
854
855    /// Set the GROUP BY clause with the given grouping expressions.
856    pub fn group_by<I, E>(mut self, expressions: I) -> Self
857    where
858        I: IntoIterator<Item = E>,
859        E: IntoExpr,
860    {
861        self.select.group_by = Some(GroupBy {
862            expressions: expressions.into_iter().map(|e| e.into_expr().0).collect(),
863            all: None,
864            totals: false,
865        });
866        self
867    }
868
869    /// Set the HAVING clause to filter groups by the given condition.
870    pub fn having(mut self, condition: Expr) -> Self {
871        self.select.having = Some(Having { this: condition.0 });
872        self
873    }
874
875    /// Set the ORDER BY clause with the given sort expressions.
876    ///
877    /// Expressions that are not already wrapped with [`.asc()`](Expr::asc) or
878    /// [`.desc()`](Expr::desc) default to ascending order. String values are
879    /// interpreted as column names via [`IntoExpr`].
880    pub fn order_by<I, E>(mut self, expressions: I) -> Self
881    where
882        I: IntoIterator<Item = E>,
883        E: IntoExpr,
884    {
885        self.select.order_by = Some(OrderBy {
886            siblings: false,
887            expressions: expressions
888                .into_iter()
889                .map(|e| {
890                    let expr = e.into_expr().0;
891                    match expr {
892                        Expression::Ordered(_) => expr,
893                        other => Expression::Ordered(Box::new(Ordered {
894                            this: other,
895                            desc: false,
896                            nulls_first: None,
897                            explicit_asc: false,
898                            with_fill: None,
899                        })),
900                    }
901                })
902                .collect::<Vec<_>>()
903                .into_iter()
904                .map(|e| {
905                    if let Expression::Ordered(o) = e {
906                        *o
907                    } else {
908                        Ordered {
909                            this: e,
910                            desc: false,
911                            nulls_first: None,
912                            explicit_asc: false,
913                            with_fill: None,
914                        }
915                    }
916                })
917                .collect(),
918        });
919        self
920    }
921
922    /// Set the LIMIT clause to restrict the result set to `count` rows.
923    pub fn limit(mut self, count: usize) -> Self {
924        self.select.limit = Some(Limit {
925            this: Expression::Literal(Literal::Number(count.to_string())),
926            percent: false,
927        });
928        self
929    }
930
931    /// Set the OFFSET clause to skip the first `count` rows.
932    pub fn offset(mut self, count: usize) -> Self {
933        self.select.offset = Some(Offset {
934            this: Expression::Literal(Literal::Number(count.to_string())),
935            rows: None,
936        });
937        self
938    }
939
940    /// Enable the DISTINCT modifier on the SELECT clause.
941    pub fn distinct(mut self) -> Self {
942        self.select.distinct = true;
943        self
944    }
945
946    /// Add a QUALIFY clause to filter rows after window function evaluation.
947    ///
948    /// QUALIFY is supported by Snowflake, BigQuery, DuckDB, and Databricks. It acts
949    /// like a WHERE clause but is applied after window functions are computed.
950    pub fn qualify(mut self, condition: Expr) -> Self {
951        self.select.qualify = Some(Qualify { this: condition.0 });
952        self
953    }
954
955    /// Add a `RIGHT JOIN` clause with the given ON condition.
956    pub fn right_join(mut self, table_name: &str, on: Expr) -> Self {
957        self.select.joins.push(Join {
958            kind: JoinKind::Right,
959            this: Expression::Table(TableRef::new(table_name)),
960            on: Some(on.0),
961            using: Vec::new(),
962            use_inner_keyword: false,
963            use_outer_keyword: false,
964            deferred_condition: false,
965            join_hint: None,
966            match_condition: None,
967            pivots: Vec::new(),
968        });
969        self
970    }
971
972    /// Add a `CROSS JOIN` clause (Cartesian product, no ON condition).
973    pub fn cross_join(mut self, table_name: &str) -> Self {
974        self.select.joins.push(Join {
975            kind: JoinKind::Cross,
976            this: Expression::Table(TableRef::new(table_name)),
977            on: None,
978            using: Vec::new(),
979            use_inner_keyword: false,
980            use_outer_keyword: false,
981            deferred_condition: false,
982            join_hint: None,
983            match_condition: None,
984            pivots: Vec::new(),
985        });
986        self
987    }
988
989    /// Add a `LATERAL VIEW` clause for Hive/Spark user-defined table function (UDTF)
990    /// expansion.
991    ///
992    /// `table_function` is the UDTF expression (e.g. `func("EXPLODE", [col("arr")])`),
993    /// `table_alias` names the virtual table, and `column_aliases` name the output
994    /// columns produced by the function.
995    pub fn lateral_view<S: AsRef<str>>(
996        mut self,
997        table_function: Expr,
998        table_alias: &str,
999        column_aliases: impl IntoIterator<Item = S>,
1000    ) -> Self {
1001        self.select.lateral_views.push(LateralView {
1002            this: table_function.0,
1003            table_alias: Some(Identifier::new(table_alias)),
1004            column_aliases: column_aliases
1005                .into_iter()
1006                .map(|c| Identifier::new(c.as_ref()))
1007                .collect(),
1008            outer: false,
1009        });
1010        self
1011    }
1012
1013    /// Add a named `WINDOW` clause definition.
1014    ///
1015    /// The window `name` can then be referenced in window function OVER clauses
1016    /// elsewhere in the query. The definition is constructed via [`WindowDefBuilder`].
1017    /// Multiple calls append additional named windows.
1018    pub fn window(mut self, name: &str, def: WindowDefBuilder) -> Self {
1019        let named_window = NamedWindow {
1020            name: Identifier::new(name),
1021            spec: Over {
1022                window_name: None,
1023                partition_by: def.partition_by,
1024                order_by: def.order_by,
1025                frame: None,
1026                alias: None,
1027            },
1028        };
1029        match self.select.windows {
1030            Some(ref mut windows) => windows.push(named_window),
1031            None => self.select.windows = Some(vec![named_window]),
1032        }
1033        self
1034    }
1035
1036    /// Add a `FOR UPDATE` locking clause.
1037    ///
1038    /// Appends a `FOR UPDATE` lock to the SELECT statement. This is used by
1039    /// databases (PostgreSQL, MySQL, Oracle) to lock selected rows for update.
1040    pub fn for_update(mut self) -> Self {
1041        self.select.locks.push(Lock {
1042            update: Some(Box::new(Expression::Boolean(BooleanLiteral { value: true }))),
1043            expressions: vec![],
1044            wait: None,
1045            key: None,
1046        });
1047        self
1048    }
1049
1050    /// Add a `FOR SHARE` locking clause.
1051    ///
1052    /// Appends a `FOR SHARE` lock to the SELECT statement. This allows other
1053    /// transactions to read the locked rows but prevents updates.
1054    pub fn for_share(mut self) -> Self {
1055        self.select.locks.push(Lock {
1056            update: None,
1057            expressions: vec![],
1058            wait: None,
1059            key: None,
1060        });
1061        self
1062    }
1063
1064    /// Add a query hint (e.g., Oracle `/*+ FULL(t) */`).
1065    ///
1066    /// Hints are rendered for Oracle, MySQL, Spark, Hive, Databricks, and PostgreSQL
1067    /// dialects. Multiple calls append additional hints.
1068    pub fn hint(mut self, hint_text: &str) -> Self {
1069        let hint_expr = HintExpression::Raw(hint_text.to_string());
1070        match &mut self.select.hint {
1071            Some(h) => h.expressions.push(hint_expr),
1072            None => {
1073                self.select.hint = Some(Hint {
1074                    expressions: vec![hint_expr],
1075                })
1076            }
1077        }
1078        self
1079    }
1080
1081    /// Convert this SELECT into a `CREATE TABLE AS SELECT` statement.
1082    ///
1083    /// Consumes the builder and returns an [`Expression::CreateTable`] with this
1084    /// query as the `as_select` source.
1085    ///
1086    /// # Examples
1087    ///
1088    /// ```
1089    /// use polyglot_sql::builder::*;
1090    ///
1091    /// let sql = polyglot_sql::generator::Generator::sql(
1092    ///     &select(["*"]).from("t").ctas("new_table")
1093    /// ).unwrap();
1094    /// assert_eq!(sql, "CREATE TABLE new_table AS SELECT * FROM t");
1095    /// ```
1096    pub fn ctas(self, table_name: &str) -> Expression {
1097        Expression::CreateTable(Box::new(CreateTable {
1098            name: TableRef::new(table_name),
1099            on_cluster: None,
1100            columns: vec![],
1101            constraints: vec![],
1102            if_not_exists: false,
1103            temporary: false,
1104            or_replace: false,
1105            table_modifier: None,
1106            as_select: Some(self.build()),
1107            as_select_parenthesized: false,
1108            on_commit: None,
1109            clone_source: None,
1110            clone_at_clause: None,
1111            is_copy: false,
1112            shallow_clone: false,
1113            leading_comments: vec![],
1114            with_properties: vec![],
1115            teradata_post_name_options: vec![],
1116            with_data: None,
1117            with_statistics: None,
1118            teradata_indexes: vec![],
1119            with_cte: None,
1120            properties: vec![],
1121            partition_of: None,
1122            post_table_properties: vec![],
1123            mysql_table_options: vec![],
1124            inherits: vec![],
1125            on_property: None,
1126            copy_grants: false,
1127            using_template: None,
1128            rollup: None,
1129        }))
1130    }
1131
1132    /// Combine this SELECT with another via `UNION` (duplicate elimination).
1133    ///
1134    /// Returns a [`SetOpBuilder`] for further chaining (e.g. `.order_by()`, `.limit()`).
1135    pub fn union(self, other: SelectBuilder) -> SetOpBuilder {
1136        SetOpBuilder::new(SetOpKind::Union, self, other, false)
1137    }
1138
1139    /// Combine this SELECT with another via `UNION ALL` (keep duplicates).
1140    ///
1141    /// Returns a [`SetOpBuilder`] for further chaining.
1142    pub fn union_all(self, other: SelectBuilder) -> SetOpBuilder {
1143        SetOpBuilder::new(SetOpKind::Union, self, other, true)
1144    }
1145
1146    /// Combine this SELECT with another via `INTERSECT` (rows common to both).
1147    ///
1148    /// Returns a [`SetOpBuilder`] for further chaining.
1149    pub fn intersect(self, other: SelectBuilder) -> SetOpBuilder {
1150        SetOpBuilder::new(SetOpKind::Intersect, self, other, false)
1151    }
1152
1153    /// Combine this SELECT with another via `EXCEPT` (rows in left but not right).
1154    ///
1155    /// Returns a [`SetOpBuilder`] for further chaining.
1156    pub fn except_(self, other: SelectBuilder) -> SetOpBuilder {
1157        SetOpBuilder::new(SetOpKind::Except, self, other, false)
1158    }
1159
1160    /// Consume this builder and produce the final [`Expression::Select`] AST node.
1161    pub fn build(self) -> Expression {
1162        Expression::Select(Box::new(self.select))
1163    }
1164
1165    /// Consume this builder, generate, and return the SQL string.
1166    ///
1167    /// Equivalent to calling `.build()` followed by [`Generator::sql()`]. Returns an
1168    /// empty string if generation fails.
1169    pub fn to_sql(self) -> String {
1170        Generator::sql(&self.build()).unwrap_or_default()
1171    }
1172}
1173
1174// ---------------------------------------------------------------------------
1175// DeleteBuilder
1176// ---------------------------------------------------------------------------
1177
1178/// Fluent builder for constructing `DELETE FROM` statements.
1179///
1180/// Created by the [`delete()`] entry-point function. Supports an optional `.where_()`
1181/// predicate.
1182pub struct DeleteBuilder {
1183    delete: Delete,
1184}
1185
1186impl DeleteBuilder {
1187    /// Set the WHERE clause to restrict which rows are deleted.
1188    pub fn where_(mut self, condition: Expr) -> Self {
1189        self.delete.where_clause = Some(Where { this: condition.0 });
1190        self
1191    }
1192
1193    /// Consume this builder and produce the final [`Expression::Delete`] AST node.
1194    pub fn build(self) -> Expression {
1195        Expression::Delete(Box::new(self.delete))
1196    }
1197
1198    /// Consume this builder, generate, and return the SQL string.
1199    pub fn to_sql(self) -> String {
1200        Generator::sql(&self.build()).unwrap_or_default()
1201    }
1202}
1203
1204// ---------------------------------------------------------------------------
1205// InsertBuilder
1206// ---------------------------------------------------------------------------
1207
1208/// Fluent builder for constructing `INSERT INTO` statements.
1209///
1210/// Created by the [`insert_into()`] entry-point function. Supports specifying target
1211/// columns via [`.columns()`](InsertBuilder::columns), row values via
1212/// [`.values()`](InsertBuilder::values) (can be called multiple times for multiple rows),
1213/// and INSERT ... SELECT via [`.query()`](InsertBuilder::query).
1214pub struct InsertBuilder {
1215    insert: Insert,
1216}
1217
1218impl InsertBuilder {
1219    /// Set the target column names for the INSERT statement.
1220    pub fn columns<I, S>(mut self, columns: I) -> Self
1221    where
1222        I: IntoIterator<Item = S>,
1223        S: AsRef<str>,
1224    {
1225        self.insert.columns = columns
1226            .into_iter()
1227            .map(|c| Identifier::new(c.as_ref()))
1228            .collect();
1229        self
1230    }
1231
1232    /// Append a row of values to the VALUES clause.
1233    ///
1234    /// Call this method multiple times to insert multiple rows in a single statement.
1235    pub fn values<I>(mut self, values: I) -> Self
1236    where
1237        I: IntoIterator<Item = Expr>,
1238    {
1239        self.insert
1240            .values
1241            .push(values.into_iter().map(|v| v.0).collect());
1242        self
1243    }
1244
1245    /// Set the source query for an `INSERT INTO ... SELECT ...` statement.
1246    ///
1247    /// When a query is set, the VALUES clause is ignored during generation.
1248    pub fn query(mut self, query: SelectBuilder) -> Self {
1249        self.insert.query = Some(query.build());
1250        self
1251    }
1252
1253    /// Consume this builder and produce the final [`Expression::Insert`] AST node.
1254    pub fn build(self) -> Expression {
1255        Expression::Insert(Box::new(self.insert))
1256    }
1257
1258    /// Consume this builder, generate, and return the SQL string.
1259    pub fn to_sql(self) -> String {
1260        Generator::sql(&self.build()).unwrap_or_default()
1261    }
1262}
1263
1264// ---------------------------------------------------------------------------
1265// UpdateBuilder
1266// ---------------------------------------------------------------------------
1267
1268/// Fluent builder for constructing `UPDATE` statements.
1269///
1270/// Created by the [`update()`] entry-point function. Supports column assignments via
1271/// [`.set()`](UpdateBuilder::set), an optional WHERE predicate, and an optional
1272/// FROM clause for PostgreSQL/Snowflake-style multi-table updates.
1273pub struct UpdateBuilder {
1274    update: Update,
1275}
1276
1277impl UpdateBuilder {
1278    /// Add a `SET column = value` assignment.
1279    ///
1280    /// Call this method multiple times to set multiple columns.
1281    pub fn set(mut self, column: &str, value: Expr) -> Self {
1282        self.update
1283            .set
1284            .push((Identifier::new(column), value.0));
1285        self
1286    }
1287
1288    /// Set the WHERE clause to restrict which rows are updated.
1289    pub fn where_(mut self, condition: Expr) -> Self {
1290        self.update.where_clause = Some(Where { this: condition.0 });
1291        self
1292    }
1293
1294    /// Set the FROM clause for PostgreSQL/Snowflake-style `UPDATE ... FROM ...` syntax.
1295    ///
1296    /// This allows joining against other tables within the UPDATE statement.
1297    pub fn from(mut self, table_name: &str) -> Self {
1298        self.update.from_clause = Some(From {
1299            expressions: vec![Expression::Table(TableRef::new(table_name))],
1300        });
1301        self
1302    }
1303
1304    /// Consume this builder and produce the final [`Expression::Update`] AST node.
1305    pub fn build(self) -> Expression {
1306        Expression::Update(Box::new(self.update))
1307    }
1308
1309    /// Consume this builder, generate, and return the SQL string.
1310    pub fn to_sql(self) -> String {
1311        Generator::sql(&self.build()).unwrap_or_default()
1312    }
1313}
1314
1315// ---------------------------------------------------------------------------
1316// CaseBuilder
1317// ---------------------------------------------------------------------------
1318
1319/// Start building a searched CASE expression (`CASE WHEN cond THEN result ... END`).
1320///
1321/// A searched CASE evaluates each WHEN condition independently. Use [`case_of()`] for
1322/// a simple CASE that compares an operand against values.
1323///
1324/// # Examples
1325///
1326/// ```
1327/// use polyglot_sql::builder::*;
1328///
1329/// let expr = case()
1330///     .when(col("x").gt(lit(0)), lit("positive"))
1331///     .when(col("x").eq(lit(0)), lit("zero"))
1332///     .else_(lit("negative"))
1333///     .build();
1334/// assert_eq!(
1335///     expr.to_sql(),
1336///     "CASE WHEN x > 0 THEN 'positive' WHEN x = 0 THEN 'zero' ELSE 'negative' END"
1337/// );
1338/// ```
1339pub fn case() -> CaseBuilder {
1340    CaseBuilder {
1341        operand: None,
1342        whens: Vec::new(),
1343        else_: None,
1344    }
1345}
1346
1347/// Start building a simple CASE expression (`CASE operand WHEN value THEN result ... END`).
1348///
1349/// A simple CASE compares the `operand` against each WHEN value for equality. Use
1350/// [`case()`] for a searched CASE with arbitrary boolean conditions.
1351///
1352/// # Examples
1353///
1354/// ```
1355/// use polyglot_sql::builder::*;
1356///
1357/// let expr = case_of(col("status"))
1358///     .when(lit(1), lit("active"))
1359///     .when(lit(0), lit("inactive"))
1360///     .else_(lit("unknown"))
1361///     .build();
1362/// assert_eq!(
1363///     expr.to_sql(),
1364///     "CASE status WHEN 1 THEN 'active' WHEN 0 THEN 'inactive' ELSE 'unknown' END"
1365/// );
1366/// ```
1367pub fn case_of(operand: Expr) -> CaseBuilder {
1368    CaseBuilder {
1369        operand: Some(operand.0),
1370        whens: Vec::new(),
1371        else_: None,
1372    }
1373}
1374
1375/// Fluent builder for SQL `CASE` expressions (both searched and simple forms).
1376///
1377/// Created by [`case()`] (searched form) or [`case_of()`] (simple form). Add branches
1378/// with [`.when()`](CaseBuilder::when) and an optional default with
1379/// [`.else_()`](CaseBuilder::else_). Finalize with [`.build()`](CaseBuilder::build) to
1380/// get an [`Expr`], or [`.build_expr()`](CaseBuilder::build_expr) for a raw
1381/// [`Expression`].
1382pub struct CaseBuilder {
1383    operand: Option<Expression>,
1384    whens: Vec<(Expression, Expression)>,
1385    else_: Option<Expression>,
1386}
1387
1388impl CaseBuilder {
1389    /// Add a `WHEN condition THEN result` branch to the CASE expression.
1390    ///
1391    /// For a searched CASE ([`case()`]), `condition` is a boolean predicate. For a simple
1392    /// CASE ([`case_of()`]), `condition` is the value to compare against the operand.
1393    pub fn when(mut self, condition: Expr, result: Expr) -> Self {
1394        self.whens.push((condition.0, result.0));
1395        self
1396    }
1397
1398    /// Set the `ELSE result` default branch of the CASE expression.
1399    ///
1400    /// If not called, the CASE expression has no ELSE clause (implicitly NULL when
1401    /// no WHEN matches).
1402    pub fn else_(mut self, result: Expr) -> Self {
1403        self.else_ = Some(result.0);
1404        self
1405    }
1406
1407    /// Consume this builder and produce an [`Expr`] wrapping the CASE expression.
1408    pub fn build(self) -> Expr {
1409        Expr(self.build_expr())
1410    }
1411
1412    /// Consume this builder and produce the raw [`Expression::Case`] AST node.
1413    ///
1414    /// Use this instead of [`.build()`](CaseBuilder::build) when you need the
1415    /// [`Expression`] directly rather than an [`Expr`] wrapper.
1416    pub fn build_expr(self) -> Expression {
1417        Expression::Case(Box::new(Case {
1418            operand: self.operand,
1419            whens: self.whens,
1420            else_: self.else_,
1421        }))
1422    }
1423}
1424
1425// ---------------------------------------------------------------------------
1426// Subquery builders
1427// ---------------------------------------------------------------------------
1428
1429/// Wrap a [`SelectBuilder`] as a named subquery for use in FROM or JOIN clauses.
1430///
1431/// The resulting [`Expr`] can be passed to [`SelectBuilder::from_expr()`] or used
1432/// in a join condition.
1433///
1434/// # Examples
1435///
1436/// ```
1437/// use polyglot_sql::builder::*;
1438///
1439/// let inner = select(["id", "name"]).from("users").where_(col("active").eq(boolean(true)));
1440/// let sql = select(["sub.id"])
1441///     .from_expr(subquery(inner, "sub"))
1442///     .to_sql();
1443/// assert_eq!(
1444///     sql,
1445///     "SELECT sub.id FROM (SELECT id, name FROM users WHERE active = TRUE) AS sub"
1446/// );
1447/// ```
1448pub fn subquery(query: SelectBuilder, alias_name: &str) -> Expr {
1449    subquery_expr(query.build(), alias_name)
1450}
1451
1452/// Wrap an existing [`Expression`] as a named subquery.
1453///
1454/// This is the lower-level version of [`subquery()`] that accepts a pre-built
1455/// [`Expression`] instead of a [`SelectBuilder`].
1456pub fn subquery_expr(expr: Expression, alias_name: &str) -> Expr {
1457    Expr(Expression::Subquery(Box::new(Subquery {
1458        this: expr,
1459        alias: Some(Identifier::new(alias_name)),
1460        column_aliases: Vec::new(),
1461        order_by: None,
1462        limit: None,
1463        offset: None,
1464        distribute_by: None,
1465        sort_by: None,
1466        cluster_by: None,
1467        lateral: false,
1468        modifiers_inside: true,
1469        trailing_comments: Vec::new(),
1470    })))
1471}
1472
1473// ---------------------------------------------------------------------------
1474// SetOpBuilder
1475// ---------------------------------------------------------------------------
1476
1477/// Internal enum distinguishing the three kinds of set operations.
1478#[derive(Debug, Clone, Copy)]
1479enum SetOpKind {
1480    Union,
1481    Intersect,
1482    Except,
1483}
1484
1485/// Fluent builder for `UNION`, `INTERSECT`, and `EXCEPT` set operations.
1486///
1487/// Created by the free functions [`union()`], [`union_all()`], [`intersect()`],
1488/// [`intersect_all()`], [`except_()`], [`except_all()`], or the corresponding methods
1489/// on [`SelectBuilder`]. Supports optional `.order_by()`, `.limit()`, and `.offset()`
1490/// clauses applied to the combined result.
1491///
1492/// # Examples
1493///
1494/// ```
1495/// use polyglot_sql::builder::*;
1496///
1497/// let sql = union_all(
1498///     select(["id"]).from("a"),
1499///     select(["id"]).from("b"),
1500/// )
1501/// .order_by(["id"])
1502/// .limit(10)
1503/// .to_sql();
1504/// ```
1505pub struct SetOpBuilder {
1506    kind: SetOpKind,
1507    left: Expression,
1508    right: Expression,
1509    all: bool,
1510    order_by: Option<OrderBy>,
1511    limit: Option<Box<Expression>>,
1512    offset: Option<Box<Expression>>,
1513}
1514
1515impl SetOpBuilder {
1516    fn new(kind: SetOpKind, left: SelectBuilder, right: SelectBuilder, all: bool) -> Self {
1517        SetOpBuilder {
1518            kind,
1519            left: left.build(),
1520            right: right.build(),
1521            all,
1522            order_by: None,
1523            limit: None,
1524            offset: None,
1525        }
1526    }
1527
1528    /// Add an ORDER BY clause applied to the combined set operation result.
1529    ///
1530    /// Expressions not already wrapped with [`.asc()`](Expr::asc) or
1531    /// [`.desc()`](Expr::desc) default to ascending order.
1532    pub fn order_by<I, E>(mut self, expressions: I) -> Self
1533    where
1534        I: IntoIterator<Item = E>,
1535        E: IntoExpr,
1536    {
1537        self.order_by = Some(OrderBy {
1538            siblings: false,
1539            expressions: expressions
1540                .into_iter()
1541                .map(|e| {
1542                    let expr = e.into_expr().0;
1543                    match expr {
1544                        Expression::Ordered(o) => *o,
1545                        other => Ordered {
1546                            this: other,
1547                            desc: false,
1548                            nulls_first: None,
1549                            explicit_asc: false,
1550                            with_fill: None,
1551                        },
1552                    }
1553                })
1554                .collect(),
1555        });
1556        self
1557    }
1558
1559    /// Restrict the combined set operation result to `count` rows.
1560    pub fn limit(mut self, count: usize) -> Self {
1561        self.limit = Some(Box::new(Expression::Literal(Literal::Number(
1562            count.to_string(),
1563        ))));
1564        self
1565    }
1566
1567    /// Skip the first `count` rows from the combined set operation result.
1568    pub fn offset(mut self, count: usize) -> Self {
1569        self.offset = Some(Box::new(Expression::Literal(Literal::Number(
1570            count.to_string(),
1571        ))));
1572        self
1573    }
1574
1575    /// Consume this builder and produce the final set operation [`Expression`] AST node.
1576    ///
1577    /// The returned expression is one of [`Expression::Union`], [`Expression::Intersect`],
1578    /// or [`Expression::Except`] depending on how the builder was created.
1579    pub fn build(self) -> Expression {
1580        match self.kind {
1581            SetOpKind::Union => Expression::Union(Box::new(Union {
1582                left: self.left,
1583                right: self.right,
1584                all: self.all,
1585                distinct: false,
1586                with: None,
1587                order_by: self.order_by,
1588                limit: self.limit,
1589                offset: self.offset,
1590                distribute_by: None,
1591                sort_by: None,
1592                cluster_by: None,
1593                by_name: false,
1594                side: None,
1595                kind: None,
1596                corresponding: false,
1597                strict: false,
1598                on_columns: Vec::new(),
1599            })),
1600            SetOpKind::Intersect => Expression::Intersect(Box::new(Intersect {
1601                left: self.left,
1602                right: self.right,
1603                all: self.all,
1604                distinct: false,
1605                with: None,
1606                order_by: self.order_by,
1607                limit: self.limit,
1608                offset: self.offset,
1609                distribute_by: None,
1610                sort_by: None,
1611                cluster_by: None,
1612                by_name: false,
1613                side: None,
1614                kind: None,
1615                corresponding: false,
1616                strict: false,
1617                on_columns: Vec::new(),
1618            })),
1619            SetOpKind::Except => Expression::Except(Box::new(Except {
1620                left: self.left,
1621                right: self.right,
1622                all: self.all,
1623                distinct: false,
1624                with: None,
1625                order_by: self.order_by,
1626                limit: self.limit,
1627                offset: self.offset,
1628                distribute_by: None,
1629                sort_by: None,
1630                cluster_by: None,
1631                by_name: false,
1632                side: None,
1633                kind: None,
1634                corresponding: false,
1635                strict: false,
1636                on_columns: Vec::new(),
1637            })),
1638        }
1639    }
1640
1641    /// Consume this builder, generate, and return the SQL string.
1642    pub fn to_sql(self) -> String {
1643        Generator::sql(&self.build()).unwrap_or_default()
1644    }
1645}
1646
1647/// Create a `UNION` (duplicate elimination) of two SELECT queries.
1648///
1649/// Returns a [`SetOpBuilder`] for optional ORDER BY / LIMIT / OFFSET chaining.
1650pub fn union(left: SelectBuilder, right: SelectBuilder) -> SetOpBuilder {
1651    SetOpBuilder::new(SetOpKind::Union, left, right, false)
1652}
1653
1654/// Create a `UNION ALL` (keep duplicates) of two SELECT queries.
1655///
1656/// Returns a [`SetOpBuilder`] for optional ORDER BY / LIMIT / OFFSET chaining.
1657pub fn union_all(left: SelectBuilder, right: SelectBuilder) -> SetOpBuilder {
1658    SetOpBuilder::new(SetOpKind::Union, left, right, true)
1659}
1660
1661/// Create an `INTERSECT` (rows common to both) of two SELECT queries.
1662///
1663/// Returns a [`SetOpBuilder`] for optional ORDER BY / LIMIT / OFFSET chaining.
1664pub fn intersect(left: SelectBuilder, right: SelectBuilder) -> SetOpBuilder {
1665    SetOpBuilder::new(SetOpKind::Intersect, left, right, false)
1666}
1667
1668/// Create an `INTERSECT ALL` (keep duplicate common rows) of two SELECT queries.
1669///
1670/// Returns a [`SetOpBuilder`] for optional ORDER BY / LIMIT / OFFSET chaining.
1671pub fn intersect_all(left: SelectBuilder, right: SelectBuilder) -> SetOpBuilder {
1672    SetOpBuilder::new(SetOpKind::Intersect, left, right, true)
1673}
1674
1675/// Create an `EXCEPT` (rows in left but not right) of two SELECT queries.
1676///
1677/// Returns a [`SetOpBuilder`] for optional ORDER BY / LIMIT / OFFSET chaining.
1678pub fn except_(left: SelectBuilder, right: SelectBuilder) -> SetOpBuilder {
1679    SetOpBuilder::new(SetOpKind::Except, left, right, false)
1680}
1681
1682/// Create an `EXCEPT ALL` (keep duplicate difference rows) of two SELECT queries.
1683///
1684/// Returns a [`SetOpBuilder`] for optional ORDER BY / LIMIT / OFFSET chaining.
1685pub fn except_all(left: SelectBuilder, right: SelectBuilder) -> SetOpBuilder {
1686    SetOpBuilder::new(SetOpKind::Except, left, right, true)
1687}
1688
1689// ---------------------------------------------------------------------------
1690// WindowDefBuilder
1691// ---------------------------------------------------------------------------
1692
1693/// Builder for constructing named `WINDOW` clause definitions.
1694///
1695/// Used with [`SelectBuilder::window()`] to define reusable window specifications.
1696/// Supports PARTITION BY and ORDER BY clauses.
1697///
1698/// # Examples
1699///
1700/// ```
1701/// use polyglot_sql::builder::*;
1702///
1703/// let sql = select(["id"])
1704///     .from("t")
1705///     .window(
1706///         "w",
1707///         WindowDefBuilder::new()
1708///             .partition_by(["dept"])
1709///             .order_by([col("salary").desc()]),
1710///     )
1711///     .to_sql();
1712/// ```
1713pub struct WindowDefBuilder {
1714    partition_by: Vec<Expression>,
1715    order_by: Vec<Ordered>,
1716}
1717
1718impl WindowDefBuilder {
1719    /// Create a new, empty window definition builder with no partitioning or ordering.
1720    pub fn new() -> Self {
1721        WindowDefBuilder {
1722            partition_by: Vec::new(),
1723            order_by: Vec::new(),
1724        }
1725    }
1726
1727    /// Set the PARTITION BY expressions for the window definition.
1728    pub fn partition_by<I, E>(mut self, expressions: I) -> Self
1729    where
1730        I: IntoIterator<Item = E>,
1731        E: IntoExpr,
1732    {
1733        self.partition_by = expressions.into_iter().map(|e| e.into_expr().0).collect();
1734        self
1735    }
1736
1737    /// Set the ORDER BY expressions for the window definition.
1738    ///
1739    /// Expressions not already wrapped with [`.asc()`](Expr::asc) or
1740    /// [`.desc()`](Expr::desc) default to ascending order.
1741    pub fn order_by<I, E>(mut self, expressions: I) -> Self
1742    where
1743        I: IntoIterator<Item = E>,
1744        E: IntoExpr,
1745    {
1746        self.order_by = expressions
1747            .into_iter()
1748            .map(|e| {
1749                let expr = e.into_expr().0;
1750                match expr {
1751                    Expression::Ordered(o) => *o,
1752                    other => Ordered {
1753                        this: other,
1754                        desc: false,
1755                        nulls_first: None,
1756                        explicit_asc: false,
1757                        with_fill: None,
1758                    },
1759                }
1760            })
1761            .collect();
1762        self
1763    }
1764}
1765
1766// ---------------------------------------------------------------------------
1767// Trait: IntoExpr
1768// ---------------------------------------------------------------------------
1769
1770/// Conversion trait for types that can be turned into an [`Expr`].
1771///
1772/// This trait is implemented for:
1773///
1774/// - [`Expr`] -- returned as-is.
1775/// - `&str` and `String` -- converted to a column reference via [`col()`].
1776/// - [`Expression`] -- wrapped directly in an [`Expr`].
1777///
1778/// It is used as a generic bound throughout the builder API so that functions like
1779/// [`select()`], [`SelectBuilder::order_by()`], and [`SelectBuilder::group_by()`] can
1780/// accept plain strings, [`Expr`] values, or raw [`Expression`] nodes interchangeably.
1781pub trait IntoExpr {
1782    /// Convert this value into an [`Expr`].
1783    fn into_expr(self) -> Expr;
1784}
1785
1786impl IntoExpr for Expr {
1787    fn into_expr(self) -> Expr {
1788        self
1789    }
1790}
1791
1792impl IntoExpr for &str {
1793    /// Convert a string slice to a column reference via [`col()`].
1794    fn into_expr(self) -> Expr {
1795        col(self)
1796    }
1797}
1798
1799impl IntoExpr for String {
1800    /// Convert an owned string to a column reference via [`col()`].
1801    fn into_expr(self) -> Expr {
1802        col(&self)
1803    }
1804}
1805
1806impl IntoExpr for Expression {
1807    /// Wrap a raw [`Expression`] in an [`Expr`].
1808    fn into_expr(self) -> Expr {
1809        Expr(self)
1810    }
1811}
1812
1813// ---------------------------------------------------------------------------
1814// Trait: IntoLiteral
1815// ---------------------------------------------------------------------------
1816
1817/// Conversion trait for types that can be turned into a SQL literal [`Expr`].
1818///
1819/// This trait is used by [`lit()`] to accept various Rust primitive types and convert
1820/// them into the appropriate SQL literal representation.
1821///
1822/// Implemented for:
1823///
1824/// - `&str`, `String` -- produce a SQL string literal (e.g. `'hello'`).
1825/// - `i32`, `i64`, `usize`, `f64` -- produce a SQL numeric literal (e.g. `42`, `3.14`).
1826/// - `bool` -- produce a SQL boolean literal (`TRUE` or `FALSE`).
1827pub trait IntoLiteral {
1828    /// Convert this value into a literal [`Expr`].
1829    fn into_literal(self) -> Expr;
1830}
1831
1832impl IntoLiteral for &str {
1833    /// Produce a SQL string literal (e.g. `'hello'`).
1834    fn into_literal(self) -> Expr {
1835        Expr(Expression::Literal(Literal::String(self.to_string())))
1836    }
1837}
1838
1839impl IntoLiteral for String {
1840    /// Produce a SQL string literal from an owned string.
1841    fn into_literal(self) -> Expr {
1842        Expr(Expression::Literal(Literal::String(self)))
1843    }
1844}
1845
1846impl IntoLiteral for i64 {
1847    /// Produce a SQL numeric literal from a 64-bit integer.
1848    fn into_literal(self) -> Expr {
1849        Expr(Expression::Literal(Literal::Number(self.to_string())))
1850    }
1851}
1852
1853impl IntoLiteral for i32 {
1854    /// Produce a SQL numeric literal from a 32-bit integer.
1855    fn into_literal(self) -> Expr {
1856        Expr(Expression::Literal(Literal::Number(self.to_string())))
1857    }
1858}
1859
1860impl IntoLiteral for usize {
1861    /// Produce a SQL numeric literal from a `usize`.
1862    fn into_literal(self) -> Expr {
1863        Expr(Expression::Literal(Literal::Number(self.to_string())))
1864    }
1865}
1866
1867impl IntoLiteral for f64 {
1868    /// Produce a SQL numeric literal from a 64-bit float.
1869    fn into_literal(self) -> Expr {
1870        Expr(Expression::Literal(Literal::Number(self.to_string())))
1871    }
1872}
1873
1874impl IntoLiteral for bool {
1875    /// Produce a SQL boolean literal (`TRUE` or `FALSE`).
1876    fn into_literal(self) -> Expr {
1877        Expr(Expression::Boolean(BooleanLiteral { value: self }))
1878    }
1879}
1880
1881// ---------------------------------------------------------------------------
1882// Helpers
1883// ---------------------------------------------------------------------------
1884
1885fn binary_op(left: Expression, right: Expression) -> BinaryOp {
1886    BinaryOp {
1887        left,
1888        right,
1889        left_comments: Vec::new(),
1890        operator_comments: Vec::new(),
1891        trailing_comments: Vec::new(),
1892    }
1893}
1894
1895// ---------------------------------------------------------------------------
1896// MergeBuilder
1897// ---------------------------------------------------------------------------
1898
1899/// Start building a `MERGE INTO` statement targeting the given table.
1900///
1901/// Returns a [`MergeBuilder`] which supports `.using()`, `.when_matched_update()`,
1902/// `.when_matched_delete()`, and `.when_not_matched_insert()`.
1903///
1904/// # Examples
1905///
1906/// ```
1907/// use polyglot_sql::builder::*;
1908///
1909/// let sql = merge_into("target")
1910///     .using("source", col("target.id").eq(col("source.id")))
1911///     .when_matched_update(vec![("name", col("source.name"))])
1912///     .when_not_matched_insert(&["id", "name"], vec![col("source.id"), col("source.name")])
1913///     .to_sql();
1914/// assert!(sql.contains("MERGE INTO"));
1915/// ```
1916pub fn merge_into(target: &str) -> MergeBuilder {
1917    MergeBuilder {
1918        target: Expression::Table(TableRef::new(target)),
1919        using: None,
1920        on: None,
1921        whens: Vec::new(),
1922    }
1923}
1924
1925/// Fluent builder for constructing `MERGE INTO` statements.
1926///
1927/// Created by the [`merge_into()`] entry-point function.
1928pub struct MergeBuilder {
1929    target: Expression,
1930    using: Option<Expression>,
1931    on: Option<Expression>,
1932    whens: Vec<Expression>,
1933}
1934
1935impl MergeBuilder {
1936    /// Set the source table and ON join condition.
1937    pub fn using(mut self, source: &str, on: Expr) -> Self {
1938        self.using = Some(Expression::Table(TableRef::new(source)));
1939        self.on = Some(on.0);
1940        self
1941    }
1942
1943    /// Add a `WHEN MATCHED THEN UPDATE SET` clause.
1944    pub fn when_matched_update(mut self, assignments: Vec<(&str, Expr)>) -> Self {
1945        let eqs: Vec<Expression> = assignments
1946            .into_iter()
1947            .map(|(col_name, val)| {
1948                Expression::Eq(Box::new(BinaryOp {
1949                    left: Expression::Column(Column {
1950                        name: Identifier::new(col_name),
1951                        table: None,
1952                        join_mark: false,
1953                        trailing_comments: Vec::new(),
1954                    }),
1955                    right: val.0,
1956                    left_comments: Vec::new(),
1957                    operator_comments: Vec::new(),
1958                    trailing_comments: Vec::new(),
1959                }))
1960            })
1961            .collect();
1962
1963        let action = Expression::Tuple(Box::new(Tuple {
1964            expressions: vec![
1965                Expression::Var(Box::new(Var {
1966                    this: "UPDATE".to_string(),
1967                })),
1968                Expression::Tuple(Box::new(Tuple { expressions: eqs })),
1969            ],
1970        }));
1971
1972        let when = Expression::When(Box::new(When {
1973            matched: Some(Box::new(Expression::Boolean(BooleanLiteral { value: true }))),
1974            source: None,
1975            condition: None,
1976            then: Box::new(action),
1977        }));
1978        self.whens.push(when);
1979        self
1980    }
1981
1982    /// Add a `WHEN MATCHED THEN UPDATE SET` clause with an additional condition.
1983    pub fn when_matched_update_where(
1984        mut self,
1985        condition: Expr,
1986        assignments: Vec<(&str, Expr)>,
1987    ) -> Self {
1988        let eqs: Vec<Expression> = assignments
1989            .into_iter()
1990            .map(|(col_name, val)| {
1991                Expression::Eq(Box::new(BinaryOp {
1992                    left: Expression::Column(Column {
1993                        name: Identifier::new(col_name),
1994                        table: None,
1995                        join_mark: false,
1996                        trailing_comments: Vec::new(),
1997                    }),
1998                    right: val.0,
1999                    left_comments: Vec::new(),
2000                    operator_comments: Vec::new(),
2001                    trailing_comments: Vec::new(),
2002                }))
2003            })
2004            .collect();
2005
2006        let action = Expression::Tuple(Box::new(Tuple {
2007            expressions: vec![
2008                Expression::Var(Box::new(Var {
2009                    this: "UPDATE".to_string(),
2010                })),
2011                Expression::Tuple(Box::new(Tuple { expressions: eqs })),
2012            ],
2013        }));
2014
2015        let when = Expression::When(Box::new(When {
2016            matched: Some(Box::new(Expression::Boolean(BooleanLiteral { value: true }))),
2017            source: None,
2018            condition: Some(Box::new(condition.0)),
2019            then: Box::new(action),
2020        }));
2021        self.whens.push(when);
2022        self
2023    }
2024
2025    /// Add a `WHEN MATCHED THEN DELETE` clause.
2026    pub fn when_matched_delete(mut self) -> Self {
2027        let action = Expression::Var(Box::new(Var {
2028            this: "DELETE".to_string(),
2029        }));
2030
2031        let when = Expression::When(Box::new(When {
2032            matched: Some(Box::new(Expression::Boolean(BooleanLiteral { value: true }))),
2033            source: None,
2034            condition: None,
2035            then: Box::new(action),
2036        }));
2037        self.whens.push(when);
2038        self
2039    }
2040
2041    /// Add a `WHEN NOT MATCHED THEN INSERT (cols) VALUES (vals)` clause.
2042    pub fn when_not_matched_insert(mut self, columns: &[&str], values: Vec<Expr>) -> Self {
2043        let col_exprs: Vec<Expression> = columns
2044            .iter()
2045            .map(|c| {
2046                Expression::Column(Column {
2047                    name: Identifier::new(*c),
2048                    table: None,
2049                    join_mark: false,
2050                    trailing_comments: Vec::new(),
2051                })
2052            })
2053            .collect();
2054        let val_exprs: Vec<Expression> = values.into_iter().map(|v| v.0).collect();
2055
2056        let action = Expression::Tuple(Box::new(Tuple {
2057            expressions: vec![
2058                Expression::Var(Box::new(Var {
2059                    this: "INSERT".to_string(),
2060                })),
2061                Expression::Tuple(Box::new(Tuple {
2062                    expressions: col_exprs,
2063                })),
2064                Expression::Tuple(Box::new(Tuple {
2065                    expressions: val_exprs,
2066                })),
2067            ],
2068        }));
2069
2070        let when = Expression::When(Box::new(When {
2071            matched: Some(Box::new(Expression::Boolean(BooleanLiteral {
2072                value: false,
2073            }))),
2074            source: None,
2075            condition: None,
2076            then: Box::new(action),
2077        }));
2078        self.whens.push(when);
2079        self
2080    }
2081
2082    /// Consume this builder and produce the final [`Expression::Merge`] AST node.
2083    pub fn build(self) -> Expression {
2084        let whens_expr = Expression::Whens(Box::new(Whens {
2085            expressions: self.whens,
2086        }));
2087
2088        Expression::Merge(Box::new(Merge {
2089            this: Box::new(self.target),
2090            using: Box::new(
2091                self.using
2092                    .unwrap_or(Expression::Null(crate::expressions::Null)),
2093            ),
2094            on: self.on.map(Box::new),
2095            using_cond: None,
2096            whens: Some(Box::new(whens_expr)),
2097            with_: None,
2098            returning: None,
2099        }))
2100    }
2101
2102    /// Consume this builder, generate, and return the SQL string.
2103    pub fn to_sql(self) -> String {
2104        Generator::sql(&self.build()).unwrap_or_default()
2105    }
2106}
2107
2108fn parse_simple_data_type(name: &str) -> DataType {
2109    let upper = name.trim().to_uppercase();
2110    match upper.as_str() {
2111        "INT" | "INTEGER" => DataType::Int {
2112            length: None,
2113            integer_spelling: upper == "INTEGER",
2114        },
2115        "BIGINT" => DataType::BigInt { length: None },
2116        "SMALLINT" => DataType::SmallInt { length: None },
2117        "TINYINT" => DataType::TinyInt { length: None },
2118        "FLOAT" => DataType::Float { precision: None, scale: None, real_spelling: false },
2119        "DOUBLE" => DataType::Double {
2120            precision: None,
2121            scale: None,
2122        },
2123        "BOOLEAN" | "BOOL" => DataType::Boolean,
2124        "TEXT" => DataType::Text,
2125        "DATE" => DataType::Date,
2126        "TIMESTAMP" => DataType::Timestamp {
2127            precision: None,
2128            timezone: false,
2129        },
2130        "VARCHAR" => DataType::VarChar {
2131            length: None,
2132            parenthesized_length: false,
2133        },
2134        "CHAR" => DataType::Char { length: None },
2135        _ => {
2136            // Try to parse as a full type via the parser for complex types
2137            if let Ok(ast) = crate::parser::Parser::parse_sql(&format!("SELECT CAST(x AS {})", name)) {
2138                if let Expression::Select(s) = &ast[0] {
2139                    if let Some(Expression::Cast(c)) = s.expressions.first() {
2140                        return c.to.clone();
2141                    }
2142                }
2143            }
2144            // Fallback: treat as a custom type
2145            DataType::Custom { name: name.to_string() }
2146        }
2147    }
2148}
2149
2150#[cfg(test)]
2151mod tests {
2152    use super::*;
2153
2154    #[test]
2155    fn test_simple_select() {
2156        let sql = select(["id", "name"]).from("users").to_sql();
2157        assert_eq!(sql, "SELECT id, name FROM users");
2158    }
2159
2160    #[test]
2161    fn test_select_star() {
2162        let sql = select([star()]).from("users").to_sql();
2163        assert_eq!(sql, "SELECT * FROM users");
2164    }
2165
2166    #[test]
2167    fn test_select_with_where() {
2168        let sql = select(["id", "name"])
2169            .from("users")
2170            .where_(col("age").gt(lit(18)))
2171            .to_sql();
2172        assert_eq!(sql, "SELECT id, name FROM users WHERE age > 18");
2173    }
2174
2175    #[test]
2176    fn test_select_with_join() {
2177        let sql = select(["u.id", "o.amount"])
2178            .from("users")
2179            .join("orders", col("u.id").eq(col("o.user_id")))
2180            .to_sql();
2181        assert_eq!(
2182            sql,
2183            "SELECT u.id, o.amount FROM users JOIN orders ON u.id = o.user_id"
2184        );
2185    }
2186
2187    #[test]
2188    fn test_select_with_group_by_having() {
2189        let sql = select([col("dept"), func("COUNT", [star()]).alias("cnt")])
2190            .from("employees")
2191            .group_by(["dept"])
2192            .having(func("COUNT", [star()]).gt(lit(5)))
2193            .to_sql();
2194        assert_eq!(
2195            sql,
2196            "SELECT dept, COUNT(*) AS cnt FROM employees GROUP BY dept HAVING COUNT(*) > 5"
2197        );
2198    }
2199
2200    #[test]
2201    fn test_select_with_order_limit_offset() {
2202        let sql = select(["id", "name"])
2203            .from("users")
2204            .order_by(["name"])
2205            .limit(10)
2206            .offset(20)
2207            .to_sql();
2208        assert_eq!(
2209            sql,
2210            "SELECT id, name FROM users ORDER BY name LIMIT 10 OFFSET 20"
2211        );
2212    }
2213
2214    #[test]
2215    fn test_select_distinct() {
2216        let sql = select(["name"]).from("users").distinct().to_sql();
2217        assert_eq!(sql, "SELECT DISTINCT name FROM users");
2218    }
2219
2220    #[test]
2221    fn test_insert_values() {
2222        let sql = insert_into("users")
2223            .columns(["id", "name"])
2224            .values([lit(1), lit("Alice")])
2225            .values([lit(2), lit("Bob")])
2226            .to_sql();
2227        assert_eq!(
2228            sql,
2229            "INSERT INTO users (id, name) VALUES (1, 'Alice'), (2, 'Bob')"
2230        );
2231    }
2232
2233    #[test]
2234    fn test_insert_select() {
2235        let sql = insert_into("archive")
2236            .columns(["id", "name"])
2237            .query(select(["id", "name"]).from("users"))
2238            .to_sql();
2239        assert_eq!(
2240            sql,
2241            "INSERT INTO archive (id, name) SELECT id, name FROM users"
2242        );
2243    }
2244
2245    #[test]
2246    fn test_update() {
2247        let sql = update("users")
2248            .set("name", lit("Bob"))
2249            .set("age", lit(30))
2250            .where_(col("id").eq(lit(1)))
2251            .to_sql();
2252        assert_eq!(
2253            sql,
2254            "UPDATE users SET name = 'Bob', age = 30 WHERE id = 1"
2255        );
2256    }
2257
2258    #[test]
2259    fn test_delete() {
2260        let sql = delete("users")
2261            .where_(col("id").eq(lit(1)))
2262            .to_sql();
2263        assert_eq!(sql, "DELETE FROM users WHERE id = 1");
2264    }
2265
2266    #[test]
2267    fn test_complex_where() {
2268        let sql = select(["id"])
2269            .from("users")
2270            .where_(
2271                col("age").gte(lit(18))
2272                    .and(col("active").eq(boolean(true)))
2273                    .and(col("name").like(lit("%test%"))),
2274            )
2275            .to_sql();
2276        assert_eq!(
2277            sql,
2278            "SELECT id FROM users WHERE age >= 18 AND active = TRUE AND name LIKE '%test%'"
2279        );
2280    }
2281
2282    #[test]
2283    fn test_in_list() {
2284        let sql = select(["id"])
2285            .from("users")
2286            .where_(col("status").in_list([lit("active"), lit("pending")]))
2287            .to_sql();
2288        assert_eq!(
2289            sql,
2290            "SELECT id FROM users WHERE status IN ('active', 'pending')"
2291        );
2292    }
2293
2294    #[test]
2295    fn test_between() {
2296        let sql = select(["id"])
2297            .from("orders")
2298            .where_(col("amount").between(lit(100), lit(500)))
2299            .to_sql();
2300        assert_eq!(
2301            sql,
2302            "SELECT id FROM orders WHERE amount BETWEEN 100 AND 500"
2303        );
2304    }
2305
2306    #[test]
2307    fn test_is_null() {
2308        let sql = select(["id"])
2309            .from("users")
2310            .where_(col("email").is_null())
2311            .to_sql();
2312        assert_eq!(sql, "SELECT id FROM users WHERE email IS NULL");
2313    }
2314
2315    #[test]
2316    fn test_arithmetic() {
2317        let sql = select([col("price").mul(col("quantity")).alias("total")])
2318            .from("items")
2319            .to_sql();
2320        assert_eq!(sql, "SELECT price * quantity AS total FROM items");
2321    }
2322
2323    #[test]
2324    fn test_cast() {
2325        let sql = select([col("id").cast("VARCHAR")])
2326            .from("users")
2327            .to_sql();
2328        assert_eq!(sql, "SELECT CAST(id AS VARCHAR) FROM users");
2329    }
2330
2331    #[test]
2332    fn test_from_starter() {
2333        let sql = from("users").select_cols(["id", "name"]).to_sql();
2334        assert_eq!(sql, "SELECT id, name FROM users");
2335    }
2336
2337    #[test]
2338    fn test_qualified_column() {
2339        let sql = select([col("u.id"), col("u.name")])
2340            .from("users")
2341            .to_sql();
2342        assert_eq!(sql, "SELECT u.id, u.name FROM users");
2343    }
2344
2345    #[test]
2346    fn test_not_condition() {
2347        let sql = select(["id"])
2348            .from("users")
2349            .where_(not(col("active").eq(boolean(true))))
2350            .to_sql();
2351        assert_eq!(sql, "SELECT id FROM users WHERE NOT active = TRUE");
2352    }
2353
2354    #[test]
2355    fn test_order_by_desc() {
2356        let sql = select(["id", "name"])
2357            .from("users")
2358            .order_by([col("name").desc()])
2359            .to_sql();
2360        assert_eq!(sql, "SELECT id, name FROM users ORDER BY name DESC");
2361    }
2362
2363    #[test]
2364    fn test_left_join() {
2365        let sql = select(["u.id", "o.amount"])
2366            .from("users")
2367            .left_join("orders", col("u.id").eq(col("o.user_id")))
2368            .to_sql();
2369        assert_eq!(
2370            sql,
2371            "SELECT u.id, o.amount FROM users LEFT JOIN orders ON u.id = o.user_id"
2372        );
2373    }
2374
2375    #[test]
2376    fn test_build_returns_expression() {
2377        let expr = select(["id"]).from("users").build();
2378        assert!(matches!(expr, Expression::Select(_)));
2379    }
2380
2381    #[test]
2382    fn test_expr_interop() {
2383        // Can use Expr in select list
2384        let age_check = col("age").gt(lit(18));
2385        let sql = select([col("id"), age_check.alias("is_adult")])
2386            .from("users")
2387            .to_sql();
2388        assert_eq!(sql, "SELECT id, age > 18 AS is_adult FROM users");
2389    }
2390
2391    // -- Step 2: sql_expr / condition tests --
2392
2393    #[test]
2394    fn test_sql_expr_simple() {
2395        let expr = sql_expr("age > 18");
2396        let sql = select(["id"])
2397            .from("users")
2398            .where_(expr)
2399            .to_sql();
2400        assert_eq!(sql, "SELECT id FROM users WHERE age > 18");
2401    }
2402
2403    #[test]
2404    fn test_sql_expr_compound() {
2405        let expr = sql_expr("a > 1 AND b < 10");
2406        let sql = select(["*"])
2407            .from("t")
2408            .where_(expr)
2409            .to_sql();
2410        assert_eq!(sql, "SELECT * FROM t WHERE a > 1 AND b < 10");
2411    }
2412
2413    #[test]
2414    fn test_sql_expr_function() {
2415        let expr = sql_expr("COALESCE(a, b, 0)");
2416        let sql = select([expr.alias("val")]).from("t").to_sql();
2417        assert_eq!(sql, "SELECT COALESCE(a, b, 0) AS val FROM t");
2418    }
2419
2420    #[test]
2421    fn test_condition_alias() {
2422        let cond = condition("x > 0");
2423        let sql = select(["*"]).from("t").where_(cond).to_sql();
2424        assert_eq!(sql, "SELECT * FROM t WHERE x > 0");
2425    }
2426
2427    // -- Step 3: ilike, rlike, not_in tests --
2428
2429    #[test]
2430    fn test_ilike() {
2431        let sql = select(["id"])
2432            .from("users")
2433            .where_(col("name").ilike(lit("%test%")))
2434            .to_sql();
2435        assert_eq!(sql, "SELECT id FROM users WHERE name ILIKE '%test%'");
2436    }
2437
2438    #[test]
2439    fn test_rlike() {
2440        let sql = select(["id"])
2441            .from("users")
2442            .where_(col("name").rlike(lit("^[A-Z]")))
2443            .to_sql();
2444        assert_eq!(sql, "SELECT id FROM users WHERE REGEXP_LIKE(name, '^[A-Z]')");
2445    }
2446
2447    #[test]
2448    fn test_not_in() {
2449        let sql = select(["id"])
2450            .from("users")
2451            .where_(col("status").not_in([lit("deleted"), lit("banned")]))
2452            .to_sql();
2453        assert_eq!(
2454            sql,
2455            "SELECT id FROM users WHERE status NOT IN ('deleted', 'banned')"
2456        );
2457    }
2458
2459    // -- Step 4: CaseBuilder tests --
2460
2461    #[test]
2462    fn test_case_searched() {
2463        let expr = case()
2464            .when(col("x").gt(lit(0)), lit("positive"))
2465            .when(col("x").eq(lit(0)), lit("zero"))
2466            .else_(lit("negative"))
2467            .build();
2468        let sql = select([expr.alias("label")]).from("t").to_sql();
2469        assert_eq!(
2470            sql,
2471            "SELECT CASE WHEN x > 0 THEN 'positive' WHEN x = 0 THEN 'zero' ELSE 'negative' END AS label FROM t"
2472        );
2473    }
2474
2475    #[test]
2476    fn test_case_simple() {
2477        let expr = case_of(col("status"))
2478            .when(lit(1), lit("active"))
2479            .when(lit(0), lit("inactive"))
2480            .build();
2481        let sql = select([expr.alias("status_label")]).from("t").to_sql();
2482        assert_eq!(
2483            sql,
2484            "SELECT CASE status WHEN 1 THEN 'active' WHEN 0 THEN 'inactive' END AS status_label FROM t"
2485        );
2486    }
2487
2488    #[test]
2489    fn test_case_no_else() {
2490        let expr = case()
2491            .when(col("x").gt(lit(0)), lit("yes"))
2492            .build();
2493        let sql = select([expr]).from("t").to_sql();
2494        assert_eq!(sql, "SELECT CASE WHEN x > 0 THEN 'yes' END FROM t");
2495    }
2496
2497    // -- Step 5: subquery tests --
2498
2499    #[test]
2500    fn test_subquery_in_from() {
2501        let inner = select(["id", "name"]).from("users").where_(col("active").eq(boolean(true)));
2502        let outer = select(["sub.id"])
2503            .from_expr(subquery(inner, "sub"))
2504            .to_sql();
2505        assert_eq!(
2506            outer,
2507            "SELECT sub.id FROM (SELECT id, name FROM users WHERE active = TRUE) AS sub"
2508        );
2509    }
2510
2511    #[test]
2512    fn test_subquery_in_join() {
2513        let inner = select([col("user_id"), func("SUM", [col("amount")]).alias("total")])
2514            .from("orders")
2515            .group_by(["user_id"]);
2516        let sql = select(["u.name", "o.total"])
2517            .from("users")
2518            .join("orders", col("u.id").eq(col("o.user_id")))
2519            .to_sql();
2520        assert!(sql.contains("JOIN"));
2521        // Just verify the subquery builder doesn't panic
2522        let _sub = subquery(inner, "o");
2523    }
2524
2525    // -- Step 6: SetOpBuilder tests --
2526
2527    #[test]
2528    fn test_union() {
2529        let sql = union(
2530            select(["id"]).from("a"),
2531            select(["id"]).from("b"),
2532        )
2533        .to_sql();
2534        assert_eq!(sql, "SELECT id FROM a UNION SELECT id FROM b");
2535    }
2536
2537    #[test]
2538    fn test_union_all() {
2539        let sql = union_all(
2540            select(["id"]).from("a"),
2541            select(["id"]).from("b"),
2542        )
2543        .to_sql();
2544        assert_eq!(sql, "SELECT id FROM a UNION ALL SELECT id FROM b");
2545    }
2546
2547    #[test]
2548    fn test_intersect_builder() {
2549        let sql = intersect(
2550            select(["id"]).from("a"),
2551            select(["id"]).from("b"),
2552        )
2553        .to_sql();
2554        assert_eq!(sql, "SELECT id FROM a INTERSECT SELECT id FROM b");
2555    }
2556
2557    #[test]
2558    fn test_except_builder() {
2559        let sql = except_(
2560            select(["id"]).from("a"),
2561            select(["id"]).from("b"),
2562        )
2563        .to_sql();
2564        assert_eq!(sql, "SELECT id FROM a EXCEPT SELECT id FROM b");
2565    }
2566
2567    #[test]
2568    fn test_union_with_order_limit() {
2569        let sql = union(
2570            select(["id"]).from("a"),
2571            select(["id"]).from("b"),
2572        )
2573        .order_by(["id"])
2574        .limit(10)
2575        .to_sql();
2576        assert!(sql.contains("UNION"));
2577        assert!(sql.contains("ORDER BY"));
2578        assert!(sql.contains("LIMIT"));
2579    }
2580
2581    #[test]
2582    fn test_select_builder_union() {
2583        let sql = select(["id"])
2584            .from("a")
2585            .union(select(["id"]).from("b"))
2586            .to_sql();
2587        assert_eq!(sql, "SELECT id FROM a UNION SELECT id FROM b");
2588    }
2589
2590    // -- Step 7: SelectBuilder extensions tests --
2591
2592    #[test]
2593    fn test_qualify() {
2594        let sql = select(["id", "name"])
2595            .from("users")
2596            .qualify(col("rn").eq(lit(1)))
2597            .to_sql();
2598        assert_eq!(
2599            sql,
2600            "SELECT id, name FROM users QUALIFY rn = 1"
2601        );
2602    }
2603
2604    #[test]
2605    fn test_right_join() {
2606        let sql = select(["u.id", "o.amount"])
2607            .from("users")
2608            .right_join("orders", col("u.id").eq(col("o.user_id")))
2609            .to_sql();
2610        assert_eq!(
2611            sql,
2612            "SELECT u.id, o.amount FROM users RIGHT JOIN orders ON u.id = o.user_id"
2613        );
2614    }
2615
2616    #[test]
2617    fn test_cross_join() {
2618        let sql = select(["a.x", "b.y"])
2619            .from("a")
2620            .cross_join("b")
2621            .to_sql();
2622        assert_eq!(sql, "SELECT a.x, b.y FROM a CROSS JOIN b");
2623    }
2624
2625    #[test]
2626    fn test_lateral_view() {
2627        let sql = select(["id", "col_val"])
2628            .from("t")
2629            .lateral_view(func("EXPLODE", [col("arr")]), "lv", ["col_val"])
2630            .to_sql();
2631        assert!(sql.contains("LATERAL VIEW"));
2632        assert!(sql.contains("EXPLODE"));
2633    }
2634
2635    #[test]
2636    fn test_window_clause() {
2637        let sql = select(["id"])
2638            .from("t")
2639            .window(
2640                "w",
2641                WindowDefBuilder::new()
2642                    .partition_by(["dept"])
2643                    .order_by(["salary"]),
2644            )
2645            .to_sql();
2646        assert!(sql.contains("WINDOW"));
2647        assert!(sql.contains("PARTITION BY"));
2648    }
2649
2650    // -- XOR operator tests --
2651
2652    #[test]
2653    fn test_xor() {
2654        let sql = select(["*"])
2655            .from("t")
2656            .where_(col("a").xor(col("b")))
2657            .to_sql();
2658        assert_eq!(sql, "SELECT * FROM t WHERE a XOR b");
2659    }
2660
2661    // -- FOR UPDATE / FOR SHARE tests --
2662
2663    #[test]
2664    fn test_for_update() {
2665        let sql = select(["id"]).from("t").for_update().to_sql();
2666        assert_eq!(sql, "SELECT id FROM t FOR UPDATE");
2667    }
2668
2669    #[test]
2670    fn test_for_share() {
2671        let sql = select(["id"]).from("t").for_share().to_sql();
2672        assert_eq!(sql, "SELECT id FROM t FOR SHARE");
2673    }
2674
2675    // -- Hint tests --
2676
2677    #[test]
2678    fn test_hint() {
2679        let sql = select(["*"]).from("t").hint("FULL(t)").to_sql();
2680        assert!(sql.contains("FULL(t)"), "Expected hint in: {}", sql);
2681    }
2682
2683    // -- CTAS tests --
2684
2685    #[test]
2686    fn test_ctas() {
2687        let expr = select(["*"]).from("t").ctas("new_table");
2688        let sql = Generator::sql(&expr).unwrap();
2689        assert_eq!(sql, "CREATE TABLE new_table AS SELECT * FROM t");
2690    }
2691
2692    // -- MergeBuilder tests --
2693
2694    #[test]
2695    fn test_merge_update_insert() {
2696        let sql = merge_into("target")
2697            .using("source", col("target.id").eq(col("source.id")))
2698            .when_matched_update(vec![("name", col("source.name"))])
2699            .when_not_matched_insert(
2700                &["id", "name"],
2701                vec![col("source.id"), col("source.name")],
2702            )
2703            .to_sql();
2704        assert!(sql.contains("MERGE INTO"), "Expected MERGE INTO in: {}", sql);
2705        assert!(sql.contains("USING"), "Expected USING in: {}", sql);
2706        assert!(sql.contains("WHEN MATCHED"), "Expected WHEN MATCHED in: {}", sql);
2707        assert!(sql.contains("UPDATE SET"), "Expected UPDATE SET in: {}", sql);
2708        assert!(
2709            sql.contains("WHEN NOT MATCHED"),
2710            "Expected WHEN NOT MATCHED in: {}",
2711            sql
2712        );
2713        assert!(sql.contains("INSERT"), "Expected INSERT in: {}", sql);
2714    }
2715
2716    #[test]
2717    fn test_merge_delete() {
2718        let sql = merge_into("target")
2719            .using("source", col("target.id").eq(col("source.id")))
2720            .when_matched_delete()
2721            .to_sql();
2722        assert!(sql.contains("MERGE INTO"), "Expected MERGE INTO in: {}", sql);
2723        assert!(sql.contains("WHEN MATCHED THEN DELETE"), "Expected WHEN MATCHED THEN DELETE in: {}", sql);
2724    }
2725
2726    #[test]
2727    fn test_merge_with_condition() {
2728        let sql = merge_into("target")
2729            .using("source", col("target.id").eq(col("source.id")))
2730            .when_matched_update_where(
2731                col("source.active").eq(boolean(true)),
2732                vec![("name", col("source.name"))],
2733            )
2734            .to_sql();
2735        assert!(sql.contains("MERGE INTO"), "Expected MERGE INTO in: {}", sql);
2736        assert!(sql.contains("AND source.active = TRUE"), "Expected condition in: {}", sql);
2737    }
2738}
polyglot_sql/builder.rs

polyglot_sql/
builder.rs
