Skip to main content

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// Function helpers — typed AST constructors
300// ---------------------------------------------------------------------------
301
302// -- Aggregates ---------------------------------------------------------------
303
304/// Create a `COUNT(expr)` expression.
305pub fn count(expr: Expr) -> Expr {
306    Expr(Expression::Count(Box::new(CountFunc {
307        this: Some(expr.0),
308        star: false,
309        distinct: false,
310        filter: None,
311        ignore_nulls: None,
312        original_name: None,
313    })))
314}
315
316/// Create a `COUNT(*)` expression.
317pub fn count_star() -> Expr {
318    Expr(Expression::Count(Box::new(CountFunc {
319        this: None,
320        star: true,
321        distinct: false,
322        filter: None,
323        ignore_nulls: None,
324        original_name: None,
325    })))
326}
327
328/// Create a `COUNT(DISTINCT expr)` expression.
329pub fn count_distinct(expr: Expr) -> Expr {
330    Expr(Expression::Count(Box::new(CountFunc {
331        this: Some(expr.0),
332        star: false,
333        distinct: true,
334        filter: None,
335        ignore_nulls: None,
336        original_name: None,
337    })))
338}
339
340/// Create a `SUM(expr)` expression.
341pub fn sum(expr: Expr) -> Expr {
342    Expr(Expression::Sum(Box::new(AggFunc {
343        this: expr.0,
344        distinct: false,
345        filter: None,
346        order_by: vec![],
347        name: None,
348        ignore_nulls: None,
349        having_max: None,
350        limit: None,
351    })))
352}
353
354/// Create an `AVG(expr)` expression.
355pub fn avg(expr: Expr) -> Expr {
356    Expr(Expression::Avg(Box::new(AggFunc {
357        this: expr.0,
358        distinct: false,
359        filter: None,
360        order_by: vec![],
361        name: None,
362        ignore_nulls: None,
363        having_max: None,
364        limit: None,
365    })))
366}
367
368/// Create a `MIN(expr)` expression. Named `min_` to avoid conflict with `std::cmp::min`.
369pub fn min_(expr: Expr) -> Expr {
370    Expr(Expression::Min(Box::new(AggFunc {
371        this: expr.0,
372        distinct: false,
373        filter: None,
374        order_by: vec![],
375        name: None,
376        ignore_nulls: None,
377        having_max: None,
378        limit: None,
379    })))
380}
381
382/// Create a `MAX(expr)` expression. Named `max_` to avoid conflict with `std::cmp::max`.
383pub fn max_(expr: Expr) -> Expr {
384    Expr(Expression::Max(Box::new(AggFunc {
385        this: expr.0,
386        distinct: false,
387        filter: None,
388        order_by: vec![],
389        name: None,
390        ignore_nulls: None,
391        having_max: None,
392        limit: None,
393    })))
394}
395
396/// Create an `APPROX_DISTINCT(expr)` expression.
397pub fn approx_distinct(expr: Expr) -> Expr {
398    Expr(Expression::ApproxDistinct(Box::new(AggFunc {
399        this: expr.0,
400        distinct: false,
401        filter: None,
402        order_by: vec![],
403        name: None,
404        ignore_nulls: None,
405        having_max: None,
406        limit: None,
407    })))
408}
409
410// -- String functions ---------------------------------------------------------
411
412/// Create an `UPPER(expr)` expression.
413pub fn upper(expr: Expr) -> Expr {
414    Expr(Expression::Upper(Box::new(UnaryFunc::new(expr.0))))
415}
416
417/// Create a `LOWER(expr)` expression.
418pub fn lower(expr: Expr) -> Expr {
419    Expr(Expression::Lower(Box::new(UnaryFunc::new(expr.0))))
420}
421
422/// Create a `LENGTH(expr)` expression.
423pub fn length(expr: Expr) -> Expr {
424    Expr(Expression::Length(Box::new(UnaryFunc::new(expr.0))))
425}
426
427/// Create a `TRIM(expr)` expression.
428pub fn trim(expr: Expr) -> Expr {
429    Expr(Expression::Trim(Box::new(TrimFunc {
430        this: expr.0,
431        characters: None,
432        position: TrimPosition::Both,
433        sql_standard_syntax: false,
434        position_explicit: false,
435    })))
436}
437
438/// Create an `LTRIM(expr)` expression.
439pub fn ltrim(expr: Expr) -> Expr {
440    Expr(Expression::LTrim(Box::new(UnaryFunc::new(expr.0))))
441}
442
443/// Create an `RTRIM(expr)` expression.
444pub fn rtrim(expr: Expr) -> Expr {
445    Expr(Expression::RTrim(Box::new(UnaryFunc::new(expr.0))))
446}
447
448/// Create a `REVERSE(expr)` expression.
449pub fn reverse(expr: Expr) -> Expr {
450    Expr(Expression::Reverse(Box::new(UnaryFunc::new(expr.0))))
451}
452
453/// Create an `INITCAP(expr)` expression.
454pub fn initcap(expr: Expr) -> Expr {
455    Expr(Expression::Initcap(Box::new(UnaryFunc::new(expr.0))))
456}
457
458/// Create a `SUBSTRING(expr, start, len)` expression.
459pub fn substring(expr: Expr, start: Expr, len: Option<Expr>) -> Expr {
460    Expr(Expression::Substring(Box::new(SubstringFunc {
461        this: expr.0,
462        start: start.0,
463        length: len.map(|l| l.0),
464        from_for_syntax: false,
465    })))
466}
467
468/// Create a `REPLACE(expr, old, new)` expression. Named `replace_` to avoid
469/// conflict with the `str::replace` method.
470pub fn replace_(expr: Expr, old: Expr, new: Expr) -> Expr {
471    Expr(Expression::Replace(Box::new(ReplaceFunc {
472        this: expr.0,
473        old: old.0,
474        new: new.0,
475    })))
476}
477
478/// Create a `CONCAT_WS(separator, exprs...)` expression.
479pub fn concat_ws(separator: Expr, exprs: impl IntoIterator<Item = Expr>) -> Expr {
480    Expr(Expression::ConcatWs(Box::new(ConcatWs {
481        separator: separator.0,
482        expressions: exprs.into_iter().map(|e| e.0).collect(),
483    })))
484}
485
486// -- Null handling ------------------------------------------------------------
487
488/// Create a `COALESCE(exprs...)` expression.
489pub fn coalesce(exprs: impl IntoIterator<Item = Expr>) -> Expr {
490    Expr(Expression::Coalesce(Box::new(VarArgFunc {
491        expressions: exprs.into_iter().map(|e| e.0).collect(),
492        original_name: None,
493    })))
494}
495
496/// Create a `NULLIF(expr1, expr2)` expression.
497pub fn null_if(expr1: Expr, expr2: Expr) -> Expr {
498    Expr(Expression::NullIf(Box::new(BinaryFunc {
499        this: expr1.0,
500        expression: expr2.0,
501        original_name: None,
502    })))
503}
504
505/// Create an `IFNULL(expr, fallback)` expression.
506pub fn if_null(expr: Expr, fallback: Expr) -> Expr {
507    Expr(Expression::IfNull(Box::new(BinaryFunc {
508        this: expr.0,
509        expression: fallback.0,
510        original_name: None,
511    })))
512}
513
514// -- Math functions -----------------------------------------------------------
515
516/// Create an `ABS(expr)` expression.
517pub fn abs(expr: Expr) -> Expr {
518    Expr(Expression::Abs(Box::new(UnaryFunc::new(expr.0))))
519}
520
521/// Create a `ROUND(expr, decimals)` expression.
522pub fn round(expr: Expr, decimals: Option<Expr>) -> Expr {
523    Expr(Expression::Round(Box::new(RoundFunc {
524        this: expr.0,
525        decimals: decimals.map(|d| d.0),
526    })))
527}
528
529/// Create a `FLOOR(expr)` expression.
530pub fn floor(expr: Expr) -> Expr {
531    Expr(Expression::Floor(Box::new(FloorFunc {
532        this: expr.0,
533        scale: None,
534        to: None,
535    })))
536}
537
538/// Create a `CEIL(expr)` expression.
539pub fn ceil(expr: Expr) -> Expr {
540    Expr(Expression::Ceil(Box::new(CeilFunc {
541        this: expr.0,
542        decimals: None,
543        to: None,
544    })))
545}
546
547/// Create a `POWER(base, exp)` expression.
548pub fn power(base: Expr, exponent: Expr) -> Expr {
549    Expr(Expression::Power(Box::new(BinaryFunc {
550        this: base.0,
551        expression: exponent.0,
552        original_name: None,
553    })))
554}
555
556/// Create a `SQRT(expr)` expression.
557pub fn sqrt(expr: Expr) -> Expr {
558    Expr(Expression::Sqrt(Box::new(UnaryFunc::new(expr.0))))
559}
560
561/// Create a `LN(expr)` expression.
562pub fn ln(expr: Expr) -> Expr {
563    Expr(Expression::Ln(Box::new(UnaryFunc::new(expr.0))))
564}
565
566/// Create an `EXP(expr)` expression. Named `exp_` to avoid conflict with `std::f64::consts`.
567pub fn exp_(expr: Expr) -> Expr {
568    Expr(Expression::Exp(Box::new(UnaryFunc::new(expr.0))))
569}
570
571/// Create a `SIGN(expr)` expression.
572pub fn sign(expr: Expr) -> Expr {
573    Expr(Expression::Sign(Box::new(UnaryFunc::new(expr.0))))
574}
575
576/// Create a `GREATEST(exprs...)` expression.
577pub fn greatest(exprs: impl IntoIterator<Item = Expr>) -> Expr {
578    Expr(Expression::Greatest(Box::new(VarArgFunc {
579        expressions: exprs.into_iter().map(|e| e.0).collect(),
580        original_name: None,
581    })))
582}
583
584/// Create a `LEAST(exprs...)` expression.
585pub fn least(exprs: impl IntoIterator<Item = Expr>) -> Expr {
586    Expr(Expression::Least(Box::new(VarArgFunc {
587        expressions: exprs.into_iter().map(|e| e.0).collect(),
588        original_name: None,
589    })))
590}
591
592// -- Date/time functions ------------------------------------------------------
593
594/// Create a `CURRENT_DATE` expression.
595pub fn current_date_() -> Expr {
596    Expr(Expression::CurrentDate(CurrentDate))
597}
598
599/// Create a `CURRENT_TIME` expression.
600pub fn current_time_() -> Expr {
601    Expr(Expression::CurrentTime(CurrentTime { precision: None }))
602}
603
604/// Create a `CURRENT_TIMESTAMP` expression.
605pub fn current_timestamp_() -> Expr {
606    Expr(Expression::CurrentTimestamp(CurrentTimestamp {
607        precision: None,
608        sysdate: false,
609    }))
610}
611
612/// Create an `EXTRACT(field FROM expr)` expression.
613pub fn extract_(field: &str, expr: Expr) -> Expr {
614    Expr(Expression::Extract(Box::new(ExtractFunc {
615        this: expr.0,
616        field: parse_datetime_field(field),
617    })))
618}
619
620/// Parse a datetime field name string into a [`DateTimeField`] enum value.
621fn parse_datetime_field(field: &str) -> DateTimeField {
622    match field.to_uppercase().as_str() {
623        "YEAR" => DateTimeField::Year,
624        "MONTH" => DateTimeField::Month,
625        "DAY" => DateTimeField::Day,
626        "HOUR" => DateTimeField::Hour,
627        "MINUTE" => DateTimeField::Minute,
628        "SECOND" => DateTimeField::Second,
629        "MILLISECOND" => DateTimeField::Millisecond,
630        "MICROSECOND" => DateTimeField::Microsecond,
631        "DOW" | "DAYOFWEEK" => DateTimeField::DayOfWeek,
632        "DOY" | "DAYOFYEAR" => DateTimeField::DayOfYear,
633        "WEEK" => DateTimeField::Week,
634        "QUARTER" => DateTimeField::Quarter,
635        "EPOCH" => DateTimeField::Epoch,
636        "TIMEZONE" => DateTimeField::Timezone,
637        "TIMEZONE_HOUR" => DateTimeField::TimezoneHour,
638        "TIMEZONE_MINUTE" => DateTimeField::TimezoneMinute,
639        "DATE" => DateTimeField::Date,
640        "TIME" => DateTimeField::Time,
641        other => DateTimeField::Custom(other.to_string()),
642    }
643}
644
645// -- Window functions ---------------------------------------------------------
646
647/// Create a `ROW_NUMBER()` expression.
648pub fn row_number() -> Expr {
649    Expr(Expression::RowNumber(RowNumber))
650}
651
652/// Create a `RANK()` expression. Named `rank_` to avoid confusion with `Rank` struct.
653pub fn rank_() -> Expr {
654    Expr(Expression::Rank(Rank {
655        order_by: None,
656        args: vec![],
657    }))
658}
659
660/// Create a `DENSE_RANK()` expression.
661pub fn dense_rank() -> Expr {
662    Expr(Expression::DenseRank(DenseRank { args: vec![] }))
663}
664
665// ---------------------------------------------------------------------------
666// Query starters
667// ---------------------------------------------------------------------------
668
669/// Start building a SELECT query with the given column expressions.
670///
671/// Accepts any iterable of items implementing [`IntoExpr`], which includes `&str`
672/// (interpreted as column names), [`Expr`] values, and raw [`Expression`] nodes.
673/// Returns a [`SelectBuilder`] that can be further refined with `.from()`, `.where_()`,
674/// `.order_by()`, etc.
675///
676/// # Examples
677///
678/// ```
679/// use polyglot_sql::builder::*;
680///
681/// // Using string slices (converted to column refs automatically)
682/// let sql = select(["id", "name"]).from("users").to_sql();
683/// assert_eq!(sql, "SELECT id, name FROM users");
684///
685/// // Using Expr values for computed columns
686/// let sql = select([col("price").mul(col("qty")).alias("total")])
687///     .from("items")
688///     .to_sql();
689/// assert_eq!(sql, "SELECT price * qty AS total FROM items");
690/// ```
691pub fn select<I, E>(expressions: I) -> SelectBuilder
692where
693    I: IntoIterator<Item = E>,
694    E: IntoExpr,
695{
696    let mut builder = SelectBuilder::new();
697    for expr in expressions {
698        builder.select = builder.select.column(expr.into_expr().0);
699    }
700    builder
701}
702
703/// Start building a SELECT query beginning with a FROM clause.
704///
705/// Returns a [`SelectBuilder`] with the FROM clause already set. Use
706/// [`SelectBuilder::select_cols()`] to add columns afterward. This is an alternative
707/// entry point for queries where specifying the table first feels more natural.
708///
709/// # Examples
710///
711/// ```
712/// use polyglot_sql::builder::*;
713///
714/// let sql = from("users").select_cols(["id", "name"]).to_sql();
715/// assert_eq!(sql, "SELECT id, name FROM users");
716/// ```
717pub fn from(table_name: &str) -> SelectBuilder {
718    let mut builder = SelectBuilder::new();
719    builder.select.from = Some(From {
720        expressions: vec![Expression::Table(TableRef::new(table_name))],
721    });
722    builder
723}
724
725/// Start building a `DELETE FROM` statement targeting the given table.
726///
727/// Returns a [`DeleteBuilder`] which supports `.where_()` to add a predicate.
728///
729/// # Examples
730///
731/// ```
732/// use polyglot_sql::builder::*;
733///
734/// let sql = delete("users").where_(col("id").eq(lit(1))).to_sql();
735/// assert_eq!(sql, "DELETE FROM users WHERE id = 1");
736/// ```
737pub fn delete(table_name: &str) -> DeleteBuilder {
738    DeleteBuilder {
739        delete: Delete {
740            table: TableRef::new(table_name),
741            on_cluster: None,
742            alias: None,
743            alias_explicit_as: false,
744            using: Vec::new(),
745            where_clause: None,
746            output: None,
747            leading_comments: Vec::new(),
748            with: None,
749            limit: None,
750            order_by: None,
751            returning: Vec::new(),
752            tables: Vec::new(),
753            tables_from_using: false,
754            joins: Vec::new(),
755            force_index: None,
756            no_from: false,
757        },
758    }
759}
760
761/// Start building an `INSERT INTO` statement targeting the given table.
762///
763/// Returns an [`InsertBuilder`] which supports `.columns()`, `.values()`, and
764/// `.query()` for INSERT ... SELECT.
765///
766/// # Examples
767///
768/// ```
769/// use polyglot_sql::builder::*;
770///
771/// let sql = insert_into("users")
772///     .columns(["id", "name"])
773///     .values([lit(1), lit("Alice")])
774///     .to_sql();
775/// assert_eq!(sql, "INSERT INTO users (id, name) VALUES (1, 'Alice')");
776/// ```
777pub fn insert_into(table_name: &str) -> InsertBuilder {
778    InsertBuilder {
779        insert: Insert {
780            table: TableRef::new(table_name),
781            columns: Vec::new(),
782            values: Vec::new(),
783            query: None,
784            overwrite: false,
785            partition: Vec::new(),
786            directory: None,
787            returning: Vec::new(),
788            output: None,
789            on_conflict: None,
790            leading_comments: Vec::new(),
791            if_exists: false,
792            with: None,
793            ignore: false,
794            source_alias: None,
795            alias: None,
796            alias_explicit_as: false,
797            default_values: false,
798            by_name: false,
799            conflict_action: None,
800            is_replace: false,
801            hint: None,
802            replace_where: None,
803            source: None,
804            function_target: None,
805            partition_by: None,
806            settings: Vec::new(),
807        },
808    }
809}
810
811/// Start building an `UPDATE` statement targeting the given table.
812///
813/// Returns an [`UpdateBuilder`] which supports `.set()` for column assignments,
814/// `.where_()` for predicates, and `.from()` for PostgreSQL/Snowflake-style
815/// UPDATE ... FROM syntax.
816///
817/// # Examples
818///
819/// ```
820/// use polyglot_sql::builder::*;
821///
822/// let sql = update("users")
823///     .set("name", lit("Bob"))
824///     .where_(col("id").eq(lit(1)))
825///     .to_sql();
826/// assert_eq!(sql, "UPDATE users SET name = 'Bob' WHERE id = 1");
827/// ```
828pub fn update(table_name: &str) -> UpdateBuilder {
829    UpdateBuilder {
830        update: Update {
831            table: TableRef::new(table_name),
832            extra_tables: Vec::new(),
833            table_joins: Vec::new(),
834            set: Vec::new(),
835            from_clause: None,
836            from_joins: Vec::new(),
837            where_clause: None,
838            returning: Vec::new(),
839            output: None,
840            with: None,
841            leading_comments: Vec::new(),
842            limit: None,
843            order_by: None,
844            from_before_set: false,
845        },
846    }
847}
848
849// ---------------------------------------------------------------------------
850// Expr wrapper (for operator methods)
851// ---------------------------------------------------------------------------
852
853/// A thin wrapper around [`Expression`] that provides fluent operator methods.
854///
855/// `Expr` is the primary value type flowing through the builder API. It wraps a single
856/// AST [`Expression`] node and adds convenience methods for comparisons (`.eq()`,
857/// `.gt()`, etc.), logical connectives (`.and()`, `.or()`, `.not()`), arithmetic
858/// (`.add()`, `.sub()`, `.mul()`, `.div()`), pattern matching (`.like()`, `.ilike()`,
859/// `.rlike()`), and other SQL operations (`.in_list()`, `.between()`, `.is_null()`,
860/// `.alias()`, `.cast()`, `.asc()`, `.desc()`).
861///
862/// The inner [`Expression`] is publicly accessible via the `.0` field or
863/// [`Expr::into_inner()`].
864///
865/// # Examples
866///
867/// ```
868/// use polyglot_sql::builder::*;
869///
870/// let condition = col("age").gte(lit(18)).and(col("active").eq(boolean(true)));
871/// assert_eq!(condition.to_sql(), "age >= 18 AND active = TRUE");
872/// ```
873#[derive(Debug, Clone)]
874pub struct Expr(pub Expression);
875
876impl Expr {
877    /// Consume this wrapper and return the inner [`Expression`] node.
878    pub fn into_inner(self) -> Expression {
879        self.0
880    }
881
882    /// Generate a SQL string from this expression using the default (generic) dialect.
883    ///
884    /// Returns an empty string if generation fails.
885    pub fn to_sql(&self) -> String {
886        Generator::sql(&self.0).unwrap_or_default()
887    }
888
889    // -- Comparison operators --
890
891    /// Produce a `self = other` equality comparison.
892    pub fn eq(self, other: Expr) -> Expr {
893        Expr(Expression::Eq(Box::new(binary_op(self.0, other.0))))
894    }
895
896    /// Produce a `self <> other` inequality comparison.
897    pub fn neq(self, other: Expr) -> Expr {
898        Expr(Expression::Neq(Box::new(binary_op(self.0, other.0))))
899    }
900
901    /// Produce a `self < other` less-than comparison.
902    pub fn lt(self, other: Expr) -> Expr {
903        Expr(Expression::Lt(Box::new(binary_op(self.0, other.0))))
904    }
905
906    /// Produce a `self <= other` less-than-or-equal comparison.
907    pub fn lte(self, other: Expr) -> Expr {
908        Expr(Expression::Lte(Box::new(binary_op(self.0, other.0))))
909    }
910
911    /// Produce a `self > other` greater-than comparison.
912    pub fn gt(self, other: Expr) -> Expr {
913        Expr(Expression::Gt(Box::new(binary_op(self.0, other.0))))
914    }
915
916    /// Produce a `self >= other` greater-than-or-equal comparison.
917    pub fn gte(self, other: Expr) -> Expr {
918        Expr(Expression::Gte(Box::new(binary_op(self.0, other.0))))
919    }
920
921    // -- Logical operators --
922
923    /// Produce a `self AND other` logical conjunction.
924    pub fn and(self, other: Expr) -> Expr {
925        Expr(Expression::And(Box::new(binary_op(self.0, other.0))))
926    }
927
928    /// Produce a `self OR other` logical disjunction.
929    pub fn or(self, other: Expr) -> Expr {
930        Expr(Expression::Or(Box::new(binary_op(self.0, other.0))))
931    }
932
933    /// Produce a `NOT self` logical negation.
934    pub fn not(self) -> Expr {
935        Expr(Expression::Not(Box::new(UnaryOp::new(self.0))))
936    }
937
938    /// Produce a `self XOR other` logical exclusive-or.
939    pub fn xor(self, other: Expr) -> Expr {
940        Expr(Expression::Xor(Box::new(Xor {
941            this: Some(Box::new(self.0)),
942            expression: Some(Box::new(other.0)),
943            expressions: vec![],
944        })))
945    }
946
947    // -- Arithmetic operators --
948
949    /// Produce a `self + other` addition expression.
950    pub fn add(self, other: Expr) -> Expr {
951        Expr(Expression::Add(Box::new(binary_op(self.0, other.0))))
952    }
953
954    /// Produce a `self - other` subtraction expression.
955    pub fn sub(self, other: Expr) -> Expr {
956        Expr(Expression::Sub(Box::new(binary_op(self.0, other.0))))
957    }
958
959    /// Produce a `self * other` multiplication expression.
960    pub fn mul(self, other: Expr) -> Expr {
961        Expr(Expression::Mul(Box::new(binary_op(self.0, other.0))))
962    }
963
964    /// Produce a `self / other` division expression.
965    pub fn div(self, other: Expr) -> Expr {
966        Expr(Expression::Div(Box::new(binary_op(self.0, other.0))))
967    }
968
969    // -- Other operators --
970
971    /// Produce a `self IS NULL` predicate.
972    pub fn is_null(self) -> Expr {
973        Expr(Expression::Is(Box::new(BinaryOp {
974            left: self.0,
975            right: Expression::Null(Null),
976            left_comments: Vec::new(),
977            operator_comments: Vec::new(),
978            trailing_comments: Vec::new(),
979        })))
980    }
981
982    /// Produce a `self IS NOT NULL` predicate (implemented as `NOT (self IS NULL)`).
983    pub fn is_not_null(self) -> Expr {
984        Expr(Expression::Not(Box::new(UnaryOp::new(
985            Expression::Is(Box::new(BinaryOp {
986                left: self.0,
987                right: Expression::Null(Null),
988                left_comments: Vec::new(),
989                operator_comments: Vec::new(),
990                trailing_comments: Vec::new(),
991            })),
992        ))))
993    }
994
995    /// Produce a `self IN (values...)` membership test.
996    ///
997    /// Each element of `values` becomes an item in the parenthesized list.
998    pub fn in_list(self, values: impl IntoIterator<Item = Expr>) -> Expr {
999        Expr(Expression::In(Box::new(In {
1000            this: self.0,
1001            expressions: values.into_iter().map(|v| v.0).collect(),
1002            query: None,
1003            not: false,
1004            global: false,
1005            unnest: None,
1006        })))
1007    }
1008
1009    /// Produce a `self BETWEEN low AND high` range test.
1010    pub fn between(self, low: Expr, high: Expr) -> Expr {
1011        Expr(Expression::Between(Box::new(Between {
1012            this: self.0,
1013            low: low.0,
1014            high: high.0,
1015            not: false,
1016        })))
1017    }
1018
1019    /// Produce a `self LIKE pattern` case-sensitive pattern match.
1020    pub fn like(self, pattern: Expr) -> Expr {
1021        Expr(Expression::Like(Box::new(LikeOp {
1022            left: self.0,
1023            right: pattern.0,
1024            escape: None,
1025            quantifier: None,
1026        })))
1027    }
1028
1029    /// Produce a `self AS alias` expression alias.
1030    pub fn alias(self, name: &str) -> Expr {
1031        alias(self, name)
1032    }
1033
1034    /// Produce a `CAST(self AS type)` type conversion.
1035    ///
1036    /// The `to` parameter is parsed as a data type name; see [`cast()`] for details.
1037    pub fn cast(self, to: &str) -> Expr {
1038        cast(self, to)
1039    }
1040
1041    /// Wrap this expression with ascending sort order (`self ASC`).
1042    ///
1043    /// Used in ORDER BY clauses. Expressions without an explicit `.asc()` or `.desc()`
1044    /// call default to ascending order when passed to [`SelectBuilder::order_by()`].
1045    pub fn asc(self) -> Expr {
1046        Expr(Expression::Ordered(Box::new(Ordered {
1047            this: self.0,
1048            desc: false,
1049            nulls_first: None,
1050            explicit_asc: true,
1051            with_fill: None,
1052        })))
1053    }
1054
1055    /// Wrap this expression with descending sort order (`self DESC`).
1056    ///
1057    /// Used in ORDER BY clauses.
1058    pub fn desc(self) -> Expr {
1059        Expr(Expression::Ordered(Box::new(Ordered {
1060            this: self.0,
1061            desc: true,
1062            nulls_first: None,
1063            explicit_asc: false,
1064            with_fill: None,
1065        })))
1066    }
1067
1068    /// Produce a `self ILIKE pattern` case-insensitive pattern match.
1069    ///
1070    /// Supported by PostgreSQL, Snowflake, and other dialects. Dialects that do not
1071    /// support `ILIKE` natively may need transpilation.
1072    pub fn ilike(self, pattern: Expr) -> Expr {
1073        Expr(Expression::ILike(Box::new(LikeOp {
1074            left: self.0,
1075            right: pattern.0,
1076            escape: None,
1077            quantifier: None,
1078        })))
1079    }
1080
1081    /// Produce a `REGEXP_LIKE(self, pattern)` regular expression match.
1082    ///
1083    /// The generated SQL uses the `REGEXP_LIKE` function form. Different dialects may
1084    /// render this as `RLIKE`, `REGEXP`, or `REGEXP_LIKE` after transpilation.
1085    pub fn rlike(self, pattern: Expr) -> Expr {
1086        Expr(Expression::RegexpLike(Box::new(RegexpFunc {
1087            this: self.0,
1088            pattern: pattern.0,
1089            flags: None,
1090        })))
1091    }
1092
1093    /// Produce a `self NOT IN (values...)` negated membership test.
1094    ///
1095    /// Each element of `values` becomes an item in the parenthesized list.
1096    pub fn not_in(self, values: impl IntoIterator<Item = Expr>) -> Expr {
1097        Expr(Expression::In(Box::new(In {
1098            this: self.0,
1099            expressions: values.into_iter().map(|v| v.0).collect(),
1100            query: None,
1101            not: true,
1102            global: false,
1103            unnest: None,
1104        })))
1105    }
1106}
1107
1108// ---------------------------------------------------------------------------
1109// SelectBuilder
1110// ---------------------------------------------------------------------------
1111
1112/// Fluent builder for constructing `SELECT` statements.
1113///
1114/// Created by the [`select()`] or [`from()`] entry-point functions. Methods on this
1115/// builder return `self` so they can be chained. Call [`.build()`](SelectBuilder::build)
1116/// to obtain an [`Expression`], or [`.to_sql()`](SelectBuilder::to_sql) to generate a
1117/// SQL string directly.
1118///
1119/// # Examples
1120///
1121/// ```
1122/// use polyglot_sql::builder::*;
1123///
1124/// let sql = select(["u.id", "u.name"])
1125///     .from("users")
1126///     .left_join("orders", col("u.id").eq(col("o.user_id")))
1127///     .where_(col("u.active").eq(boolean(true)))
1128///     .group_by(["u.id", "u.name"])
1129///     .order_by([col("u.name").asc()])
1130///     .limit(100)
1131///     .to_sql();
1132/// ```
1133pub struct SelectBuilder {
1134    select: Select,
1135}
1136
1137impl SelectBuilder {
1138    fn new() -> Self {
1139        SelectBuilder {
1140            select: Select::new(),
1141        }
1142    }
1143
1144    /// Append columns to the SELECT list.
1145    ///
1146    /// Accepts any iterable of [`IntoExpr`] items. This is primarily useful when the
1147    /// builder was created via [`from()`] and columns need to be added afterward.
1148    pub fn select_cols<I, E>(mut self, expressions: I) -> Self
1149    where
1150        I: IntoIterator<Item = E>,
1151        E: IntoExpr,
1152    {
1153        for expr in expressions {
1154            self.select.expressions.push(expr.into_expr().0);
1155        }
1156        self
1157    }
1158
1159    /// Set the FROM clause to reference the given table by name.
1160    pub fn from(mut self, table_name: &str) -> Self {
1161        self.select.from = Some(From {
1162            expressions: vec![Expression::Table(TableRef::new(table_name))],
1163        });
1164        self
1165    }
1166
1167    /// Set the FROM clause to an arbitrary expression (e.g. a subquery or table function).
1168    ///
1169    /// Use this instead of [`SelectBuilder::from()`] when the source is not a simple
1170    /// table name -- for example, a [`subquery()`] or a table-valued function.
1171    pub fn from_expr(mut self, expr: Expr) -> Self {
1172        self.select.from = Some(From {
1173            expressions: vec![expr.0],
1174        });
1175        self
1176    }
1177
1178    /// Add an inner `JOIN` clause with the given ON condition.
1179    pub fn join(mut self, table_name: &str, on: Expr) -> Self {
1180        self.select.joins.push(Join {
1181            kind: JoinKind::Inner,
1182            this: Expression::Table(TableRef::new(table_name)),
1183            on: Some(on.0),
1184            using: Vec::new(),
1185            use_inner_keyword: false,
1186            use_outer_keyword: false,
1187            deferred_condition: false,
1188            join_hint: None,
1189            match_condition: None,
1190            pivots: Vec::new(),
1191        });
1192        self
1193    }
1194
1195    /// Add a `LEFT JOIN` clause with the given ON condition.
1196    pub fn left_join(mut self, table_name: &str, on: Expr) -> Self {
1197        self.select.joins.push(Join {
1198            kind: JoinKind::Left,
1199            this: Expression::Table(TableRef::new(table_name)),
1200            on: Some(on.0),
1201            using: Vec::new(),
1202            use_inner_keyword: false,
1203            use_outer_keyword: false,
1204            deferred_condition: false,
1205            join_hint: None,
1206            match_condition: None,
1207            pivots: Vec::new(),
1208        });
1209        self
1210    }
1211
1212    /// Set the WHERE clause to filter rows by the given condition.
1213    ///
1214    /// Calling this multiple times replaces the previous WHERE condition. To combine
1215    /// multiple predicates, chain them with [`.and()`](Expr::and) or [`.or()`](Expr::or)
1216    /// on a single [`Expr`].
1217    pub fn where_(mut self, condition: Expr) -> Self {
1218        self.select.where_clause = Some(Where { this: condition.0 });
1219        self
1220    }
1221
1222    /// Set the GROUP BY clause with the given grouping expressions.
1223    pub fn group_by<I, E>(mut self, expressions: I) -> Self
1224    where
1225        I: IntoIterator<Item = E>,
1226        E: IntoExpr,
1227    {
1228        self.select.group_by = Some(GroupBy {
1229            expressions: expressions.into_iter().map(|e| e.into_expr().0).collect(),
1230            all: None,
1231            totals: false,
1232        });
1233        self
1234    }
1235
1236    /// Set the HAVING clause to filter groups by the given condition.
1237    pub fn having(mut self, condition: Expr) -> Self {
1238        self.select.having = Some(Having { this: condition.0 });
1239        self
1240    }
1241
1242    /// Set the ORDER BY clause with the given sort expressions.
1243    ///
1244    /// Expressions that are not already wrapped with [`.asc()`](Expr::asc) or
1245    /// [`.desc()`](Expr::desc) default to ascending order. String values are
1246    /// interpreted as column names via [`IntoExpr`].
1247    pub fn order_by<I, E>(mut self, expressions: I) -> Self
1248    where
1249        I: IntoIterator<Item = E>,
1250        E: IntoExpr,
1251    {
1252        self.select.order_by = Some(OrderBy {
1253            siblings: false,
1254            expressions: expressions
1255                .into_iter()
1256                .map(|e| {
1257                    let expr = e.into_expr().0;
1258                    match expr {
1259                        Expression::Ordered(_) => expr,
1260                        other => Expression::Ordered(Box::new(Ordered {
1261                            this: other,
1262                            desc: false,
1263                            nulls_first: None,
1264                            explicit_asc: false,
1265                            with_fill: None,
1266                        })),
1267                    }
1268                })
1269                .collect::<Vec<_>>()
1270                .into_iter()
1271                .map(|e| {
1272                    if let Expression::Ordered(o) = e {
1273                        *o
1274                    } else {
1275                        Ordered {
1276                            this: e,
1277                            desc: false,
1278                            nulls_first: None,
1279                            explicit_asc: false,
1280                            with_fill: None,
1281                        }
1282                    }
1283                })
1284                .collect(),
1285        });
1286        self
1287    }
1288
1289    /// Set the SORT BY clause with the given sort expressions.
1290    ///
1291    /// SORT BY is used in Hive/Spark to sort data within each reducer (partition),
1292    /// as opposed to ORDER BY which sorts globally. Expressions that are not already
1293    /// wrapped with [`.asc()`](Expr::asc) or [`.desc()`](Expr::desc) default to
1294    /// ascending order.
1295    pub fn sort_by<I, E>(mut self, expressions: I) -> Self
1296    where
1297        I: IntoIterator<Item = E>,
1298        E: IntoExpr,
1299    {
1300        self.select.sort_by = Some(SortBy {
1301            expressions: expressions
1302                .into_iter()
1303                .map(|e| {
1304                    let expr = e.into_expr().0;
1305                    match expr {
1306                        Expression::Ordered(o) => *o,
1307                        other => Ordered {
1308                            this: other,
1309                            desc: false,
1310                            nulls_first: None,
1311                            explicit_asc: false,
1312                            with_fill: None,
1313                        },
1314                    }
1315                })
1316                .collect(),
1317        });
1318        self
1319    }
1320
1321    /// Set the LIMIT clause to restrict the result set to `count` rows.
1322    pub fn limit(mut self, count: usize) -> Self {
1323        self.select.limit = Some(Limit {
1324            this: Expression::Literal(Literal::Number(count.to_string())),
1325            percent: false,
1326        });
1327        self
1328    }
1329
1330    /// Set the OFFSET clause to skip the first `count` rows.
1331    pub fn offset(mut self, count: usize) -> Self {
1332        self.select.offset = Some(Offset {
1333            this: Expression::Literal(Literal::Number(count.to_string())),
1334            rows: None,
1335        });
1336        self
1337    }
1338
1339    /// Enable the DISTINCT modifier on the SELECT clause.
1340    pub fn distinct(mut self) -> Self {
1341        self.select.distinct = true;
1342        self
1343    }
1344
1345    /// Add a QUALIFY clause to filter rows after window function evaluation.
1346    ///
1347    /// QUALIFY is supported by Snowflake, BigQuery, DuckDB, and Databricks. It acts
1348    /// like a WHERE clause but is applied after window functions are computed.
1349    pub fn qualify(mut self, condition: Expr) -> Self {
1350        self.select.qualify = Some(Qualify { this: condition.0 });
1351        self
1352    }
1353
1354    /// Add a `RIGHT JOIN` clause with the given ON condition.
1355    pub fn right_join(mut self, table_name: &str, on: Expr) -> Self {
1356        self.select.joins.push(Join {
1357            kind: JoinKind::Right,
1358            this: Expression::Table(TableRef::new(table_name)),
1359            on: Some(on.0),
1360            using: Vec::new(),
1361            use_inner_keyword: false,
1362            use_outer_keyword: false,
1363            deferred_condition: false,
1364            join_hint: None,
1365            match_condition: None,
1366            pivots: Vec::new(),
1367        });
1368        self
1369    }
1370
1371    /// Add a `CROSS JOIN` clause (Cartesian product, no ON condition).
1372    pub fn cross_join(mut self, table_name: &str) -> Self {
1373        self.select.joins.push(Join {
1374            kind: JoinKind::Cross,
1375            this: Expression::Table(TableRef::new(table_name)),
1376            on: None,
1377            using: Vec::new(),
1378            use_inner_keyword: false,
1379            use_outer_keyword: false,
1380            deferred_condition: false,
1381            join_hint: None,
1382            match_condition: None,
1383            pivots: Vec::new(),
1384        });
1385        self
1386    }
1387
1388    /// Add a `LATERAL VIEW` clause for Hive/Spark user-defined table function (UDTF)
1389    /// expansion.
1390    ///
1391    /// `table_function` is the UDTF expression (e.g. `func("EXPLODE", [col("arr")])`),
1392    /// `table_alias` names the virtual table, and `column_aliases` name the output
1393    /// columns produced by the function.
1394    pub fn lateral_view<S: AsRef<str>>(
1395        mut self,
1396        table_function: Expr,
1397        table_alias: &str,
1398        column_aliases: impl IntoIterator<Item = S>,
1399    ) -> Self {
1400        self.select.lateral_views.push(LateralView {
1401            this: table_function.0,
1402            table_alias: Some(Identifier::new(table_alias)),
1403            column_aliases: column_aliases
1404                .into_iter()
1405                .map(|c| Identifier::new(c.as_ref()))
1406                .collect(),
1407            outer: false,
1408        });
1409        self
1410    }
1411
1412    /// Add a named `WINDOW` clause definition.
1413    ///
1414    /// The window `name` can then be referenced in window function OVER clauses
1415    /// elsewhere in the query. The definition is constructed via [`WindowDefBuilder`].
1416    /// Multiple calls append additional named windows.
1417    pub fn window(mut self, name: &str, def: WindowDefBuilder) -> Self {
1418        let named_window = NamedWindow {
1419            name: Identifier::new(name),
1420            spec: Over {
1421                window_name: None,
1422                partition_by: def.partition_by,
1423                order_by: def.order_by,
1424                frame: None,
1425                alias: None,
1426            },
1427        };
1428        match self.select.windows {
1429            Some(ref mut windows) => windows.push(named_window),
1430            None => self.select.windows = Some(vec![named_window]),
1431        }
1432        self
1433    }
1434
1435    /// Add a `FOR UPDATE` locking clause.
1436    ///
1437    /// Appends a `FOR UPDATE` lock to the SELECT statement. This is used by
1438    /// databases (PostgreSQL, MySQL, Oracle) to lock selected rows for update.
1439    pub fn for_update(mut self) -> Self {
1440        self.select.locks.push(Lock {
1441            update: Some(Box::new(Expression::Boolean(BooleanLiteral { value: true }))),
1442            expressions: vec![],
1443            wait: None,
1444            key: None,
1445        });
1446        self
1447    }
1448
1449    /// Add a `FOR SHARE` locking clause.
1450    ///
1451    /// Appends a `FOR SHARE` lock to the SELECT statement. This allows other
1452    /// transactions to read the locked rows but prevents updates.
1453    pub fn for_share(mut self) -> Self {
1454        self.select.locks.push(Lock {
1455            update: None,
1456            expressions: vec![],
1457            wait: None,
1458            key: None,
1459        });
1460        self
1461    }
1462
1463    /// Add a query hint (e.g., Oracle `/*+ FULL(t) */`).
1464    ///
1465    /// Hints are rendered for Oracle, MySQL, Spark, Hive, Databricks, and PostgreSQL
1466    /// dialects. Multiple calls append additional hints.
1467    pub fn hint(mut self, hint_text: &str) -> Self {
1468        let hint_expr = HintExpression::Raw(hint_text.to_string());
1469        match &mut self.select.hint {
1470            Some(h) => h.expressions.push(hint_expr),
1471            None => {
1472                self.select.hint = Some(Hint {
1473                    expressions: vec![hint_expr],
1474                })
1475            }
1476        }
1477        self
1478    }
1479
1480    /// Convert this SELECT into a `CREATE TABLE AS SELECT` statement.
1481    ///
1482    /// Consumes the builder and returns an [`Expression::CreateTable`] with this
1483    /// query as the `as_select` source.
1484    ///
1485    /// # Examples
1486    ///
1487    /// ```
1488    /// use polyglot_sql::builder::*;
1489    ///
1490    /// let sql = polyglot_sql::generator::Generator::sql(
1491    ///     &select(["*"]).from("t").ctas("new_table")
1492    /// ).unwrap();
1493    /// assert_eq!(sql, "CREATE TABLE new_table AS SELECT * FROM t");
1494    /// ```
1495    pub fn ctas(self, table_name: &str) -> Expression {
1496        Expression::CreateTable(Box::new(CreateTable {
1497            name: TableRef::new(table_name),
1498            on_cluster: None,
1499            columns: vec![],
1500            constraints: vec![],
1501            if_not_exists: false,
1502            temporary: false,
1503            or_replace: false,
1504            table_modifier: None,
1505            as_select: Some(self.build()),
1506            as_select_parenthesized: false,
1507            on_commit: None,
1508            clone_source: None,
1509            clone_at_clause: None,
1510            is_copy: false,
1511            shallow_clone: false,
1512            leading_comments: vec![],
1513            with_properties: vec![],
1514            teradata_post_name_options: vec![],
1515            with_data: None,
1516            with_statistics: None,
1517            teradata_indexes: vec![],
1518            with_cte: None,
1519            properties: vec![],
1520            partition_of: None,
1521            post_table_properties: vec![],
1522            mysql_table_options: vec![],
1523            inherits: vec![],
1524            on_property: None,
1525            copy_grants: false,
1526            using_template: None,
1527            rollup: None,
1528        }))
1529    }
1530
1531    /// Combine this SELECT with another via `UNION` (duplicate elimination).
1532    ///
1533    /// Returns a [`SetOpBuilder`] for further chaining (e.g. `.order_by()`, `.limit()`).
1534    pub fn union(self, other: SelectBuilder) -> SetOpBuilder {
1535        SetOpBuilder::new(SetOpKind::Union, self, other, false)
1536    }
1537
1538    /// Combine this SELECT with another via `UNION ALL` (keep duplicates).
1539    ///
1540    /// Returns a [`SetOpBuilder`] for further chaining.
1541    pub fn union_all(self, other: SelectBuilder) -> SetOpBuilder {
1542        SetOpBuilder::new(SetOpKind::Union, self, other, true)
1543    }
1544
1545    /// Combine this SELECT with another via `INTERSECT` (rows common to both).
1546    ///
1547    /// Returns a [`SetOpBuilder`] for further chaining.
1548    pub fn intersect(self, other: SelectBuilder) -> SetOpBuilder {
1549        SetOpBuilder::new(SetOpKind::Intersect, self, other, false)
1550    }
1551
1552    /// Combine this SELECT with another via `EXCEPT` (rows in left but not right).
1553    ///
1554    /// Returns a [`SetOpBuilder`] for further chaining.
1555    pub fn except_(self, other: SelectBuilder) -> SetOpBuilder {
1556        SetOpBuilder::new(SetOpKind::Except, self, other, false)
1557    }
1558
1559    /// Consume this builder and produce the final [`Expression::Select`] AST node.
1560    pub fn build(self) -> Expression {
1561        Expression::Select(Box::new(self.select))
1562    }
1563
1564    /// Consume this builder, generate, and return the SQL string.
1565    ///
1566    /// Equivalent to calling `.build()` followed by [`Generator::sql()`]. Returns an
1567    /// empty string if generation fails.
1568    pub fn to_sql(self) -> String {
1569        Generator::sql(&self.build()).unwrap_or_default()
1570    }
1571}
1572
1573// ---------------------------------------------------------------------------
1574// DeleteBuilder
1575// ---------------------------------------------------------------------------
1576
1577/// Fluent builder for constructing `DELETE FROM` statements.
1578///
1579/// Created by the [`delete()`] entry-point function. Supports an optional `.where_()`
1580/// predicate.
1581pub struct DeleteBuilder {
1582    delete: Delete,
1583}
1584
1585impl DeleteBuilder {
1586    /// Set the WHERE clause to restrict which rows are deleted.
1587    pub fn where_(mut self, condition: Expr) -> Self {
1588        self.delete.where_clause = Some(Where { this: condition.0 });
1589        self
1590    }
1591
1592    /// Consume this builder and produce the final [`Expression::Delete`] AST node.
1593    pub fn build(self) -> Expression {
1594        Expression::Delete(Box::new(self.delete))
1595    }
1596
1597    /// Consume this builder, generate, and return the SQL string.
1598    pub fn to_sql(self) -> String {
1599        Generator::sql(&self.build()).unwrap_or_default()
1600    }
1601}
1602
1603// ---------------------------------------------------------------------------
1604// InsertBuilder
1605// ---------------------------------------------------------------------------
1606
1607/// Fluent builder for constructing `INSERT INTO` statements.
1608///
1609/// Created by the [`insert_into()`] entry-point function. Supports specifying target
1610/// columns via [`.columns()`](InsertBuilder::columns), row values via
1611/// [`.values()`](InsertBuilder::values) (can be called multiple times for multiple rows),
1612/// and INSERT ... SELECT via [`.query()`](InsertBuilder::query).
1613pub struct InsertBuilder {
1614    insert: Insert,
1615}
1616
1617impl InsertBuilder {
1618    /// Set the target column names for the INSERT statement.
1619    pub fn columns<I, S>(mut self, columns: I) -> Self
1620    where
1621        I: IntoIterator<Item = S>,
1622        S: AsRef<str>,
1623    {
1624        self.insert.columns = columns
1625            .into_iter()
1626            .map(|c| Identifier::new(c.as_ref()))
1627            .collect();
1628        self
1629    }
1630
1631    /// Append a row of values to the VALUES clause.
1632    ///
1633    /// Call this method multiple times to insert multiple rows in a single statement.
1634    pub fn values<I>(mut self, values: I) -> Self
1635    where
1636        I: IntoIterator<Item = Expr>,
1637    {
1638        self.insert
1639            .values
1640            .push(values.into_iter().map(|v| v.0).collect());
1641        self
1642    }
1643
1644    /// Set the source query for an `INSERT INTO ... SELECT ...` statement.
1645    ///
1646    /// When a query is set, the VALUES clause is ignored during generation.
1647    pub fn query(mut self, query: SelectBuilder) -> Self {
1648        self.insert.query = Some(query.build());
1649        self
1650    }
1651
1652    /// Consume this builder and produce the final [`Expression::Insert`] AST node.
1653    pub fn build(self) -> Expression {
1654        Expression::Insert(Box::new(self.insert))
1655    }
1656
1657    /// Consume this builder, generate, and return the SQL string.
1658    pub fn to_sql(self) -> String {
1659        Generator::sql(&self.build()).unwrap_or_default()
1660    }
1661}
1662
1663// ---------------------------------------------------------------------------
1664// UpdateBuilder
1665// ---------------------------------------------------------------------------
1666
1667/// Fluent builder for constructing `UPDATE` statements.
1668///
1669/// Created by the [`update()`] entry-point function. Supports column assignments via
1670/// [`.set()`](UpdateBuilder::set), an optional WHERE predicate, and an optional
1671/// FROM clause for PostgreSQL/Snowflake-style multi-table updates.
1672pub struct UpdateBuilder {
1673    update: Update,
1674}
1675
1676impl UpdateBuilder {
1677    /// Add a `SET column = value` assignment.
1678    ///
1679    /// Call this method multiple times to set multiple columns.
1680    pub fn set(mut self, column: &str, value: Expr) -> Self {
1681        self.update
1682            .set
1683            .push((Identifier::new(column), value.0));
1684        self
1685    }
1686
1687    /// Set the WHERE clause to restrict which rows are updated.
1688    pub fn where_(mut self, condition: Expr) -> Self {
1689        self.update.where_clause = Some(Where { this: condition.0 });
1690        self
1691    }
1692
1693    /// Set the FROM clause for PostgreSQL/Snowflake-style `UPDATE ... FROM ...` syntax.
1694    ///
1695    /// This allows joining against other tables within the UPDATE statement.
1696    pub fn from(mut self, table_name: &str) -> Self {
1697        self.update.from_clause = Some(From {
1698            expressions: vec![Expression::Table(TableRef::new(table_name))],
1699        });
1700        self
1701    }
1702
1703    /// Consume this builder and produce the final [`Expression::Update`] AST node.
1704    pub fn build(self) -> Expression {
1705        Expression::Update(Box::new(self.update))
1706    }
1707
1708    /// Consume this builder, generate, and return the SQL string.
1709    pub fn to_sql(self) -> String {
1710        Generator::sql(&self.build()).unwrap_or_default()
1711    }
1712}
1713
1714// ---------------------------------------------------------------------------
1715// CaseBuilder
1716// ---------------------------------------------------------------------------
1717
1718/// Start building a searched CASE expression (`CASE WHEN cond THEN result ... END`).
1719///
1720/// A searched CASE evaluates each WHEN condition independently. Use [`case_of()`] for
1721/// a simple CASE that compares an operand against values.
1722///
1723/// # Examples
1724///
1725/// ```
1726/// use polyglot_sql::builder::*;
1727///
1728/// let expr = case()
1729///     .when(col("x").gt(lit(0)), lit("positive"))
1730///     .when(col("x").eq(lit(0)), lit("zero"))
1731///     .else_(lit("negative"))
1732///     .build();
1733/// assert_eq!(
1734///     expr.to_sql(),
1735///     "CASE WHEN x > 0 THEN 'positive' WHEN x = 0 THEN 'zero' ELSE 'negative' END"
1736/// );
1737/// ```
1738pub fn case() -> CaseBuilder {
1739    CaseBuilder {
1740        operand: None,
1741        whens: Vec::new(),
1742        else_: None,
1743    }
1744}
1745
1746/// Start building a simple CASE expression (`CASE operand WHEN value THEN result ... END`).
1747///
1748/// A simple CASE compares the `operand` against each WHEN value for equality. Use
1749/// [`case()`] for a searched CASE with arbitrary boolean conditions.
1750///
1751/// # Examples
1752///
1753/// ```
1754/// use polyglot_sql::builder::*;
1755///
1756/// let expr = case_of(col("status"))
1757///     .when(lit(1), lit("active"))
1758///     .when(lit(0), lit("inactive"))
1759///     .else_(lit("unknown"))
1760///     .build();
1761/// assert_eq!(
1762///     expr.to_sql(),
1763///     "CASE status WHEN 1 THEN 'active' WHEN 0 THEN 'inactive' ELSE 'unknown' END"
1764/// );
1765/// ```
1766pub fn case_of(operand: Expr) -> CaseBuilder {
1767    CaseBuilder {
1768        operand: Some(operand.0),
1769        whens: Vec::new(),
1770        else_: None,
1771    }
1772}
1773
1774/// Fluent builder for SQL `CASE` expressions (both searched and simple forms).
1775///
1776/// Created by [`case()`] (searched form) or [`case_of()`] (simple form). Add branches
1777/// with [`.when()`](CaseBuilder::when) and an optional default with
1778/// [`.else_()`](CaseBuilder::else_). Finalize with [`.build()`](CaseBuilder::build) to
1779/// get an [`Expr`], or [`.build_expr()`](CaseBuilder::build_expr) for a raw
1780/// [`Expression`].
1781pub struct CaseBuilder {
1782    operand: Option<Expression>,
1783    whens: Vec<(Expression, Expression)>,
1784    else_: Option<Expression>,
1785}
1786
1787impl CaseBuilder {
1788    /// Add a `WHEN condition THEN result` branch to the CASE expression.
1789    ///
1790    /// For a searched CASE ([`case()`]), `condition` is a boolean predicate. For a simple
1791    /// CASE ([`case_of()`]), `condition` is the value to compare against the operand.
1792    pub fn when(mut self, condition: Expr, result: Expr) -> Self {
1793        self.whens.push((condition.0, result.0));
1794        self
1795    }
1796
1797    /// Set the `ELSE result` default branch of the CASE expression.
1798    ///
1799    /// If not called, the CASE expression has no ELSE clause (implicitly NULL when
1800    /// no WHEN matches).
1801    pub fn else_(mut self, result: Expr) -> Self {
1802        self.else_ = Some(result.0);
1803        self
1804    }
1805
1806    /// Consume this builder and produce an [`Expr`] wrapping the CASE expression.
1807    pub fn build(self) -> Expr {
1808        Expr(self.build_expr())
1809    }
1810
1811    /// Consume this builder and produce the raw [`Expression::Case`] AST node.
1812    ///
1813    /// Use this instead of [`.build()`](CaseBuilder::build) when you need the
1814    /// [`Expression`] directly rather than an [`Expr`] wrapper.
1815    pub fn build_expr(self) -> Expression {
1816        Expression::Case(Box::new(Case {
1817            operand: self.operand,
1818            whens: self.whens,
1819            else_: self.else_,
1820        }))
1821    }
1822}
1823
1824// ---------------------------------------------------------------------------
1825// Subquery builders
1826// ---------------------------------------------------------------------------
1827
1828/// Wrap a [`SelectBuilder`] as a named subquery for use in FROM or JOIN clauses.
1829///
1830/// The resulting [`Expr`] can be passed to [`SelectBuilder::from_expr()`] or used
1831/// in a join condition.
1832///
1833/// # Examples
1834///
1835/// ```
1836/// use polyglot_sql::builder::*;
1837///
1838/// let inner = select(["id", "name"]).from("users").where_(col("active").eq(boolean(true)));
1839/// let sql = select(["sub.id"])
1840///     .from_expr(subquery(inner, "sub"))
1841///     .to_sql();
1842/// assert_eq!(
1843///     sql,
1844///     "SELECT sub.id FROM (SELECT id, name FROM users WHERE active = TRUE) AS sub"
1845/// );
1846/// ```
1847pub fn subquery(query: SelectBuilder, alias_name: &str) -> Expr {
1848    subquery_expr(query.build(), alias_name)
1849}
1850
1851/// Wrap an existing [`Expression`] as a named subquery.
1852///
1853/// This is the lower-level version of [`subquery()`] that accepts a pre-built
1854/// [`Expression`] instead of a [`SelectBuilder`].
1855pub fn subquery_expr(expr: Expression, alias_name: &str) -> Expr {
1856    Expr(Expression::Subquery(Box::new(Subquery {
1857        this: expr,
1858        alias: Some(Identifier::new(alias_name)),
1859        column_aliases: Vec::new(),
1860        order_by: None,
1861        limit: None,
1862        offset: None,
1863        distribute_by: None,
1864        sort_by: None,
1865        cluster_by: None,
1866        lateral: false,
1867        modifiers_inside: true,
1868        trailing_comments: Vec::new(),
1869    })))
1870}
1871
1872// ---------------------------------------------------------------------------
1873// SetOpBuilder
1874// ---------------------------------------------------------------------------
1875
1876/// Internal enum distinguishing the three kinds of set operations.
1877#[derive(Debug, Clone, Copy)]
1878enum SetOpKind {
1879    Union,
1880    Intersect,
1881    Except,
1882}
1883
1884/// Fluent builder for `UNION`, `INTERSECT`, and `EXCEPT` set operations.
1885///
1886/// Created by the free functions [`union()`], [`union_all()`], [`intersect()`],
1887/// [`intersect_all()`], [`except_()`], [`except_all()`], or the corresponding methods
1888/// on [`SelectBuilder`]. Supports optional `.order_by()`, `.limit()`, and `.offset()`
1889/// clauses applied to the combined result.
1890///
1891/// # Examples
1892///
1893/// ```
1894/// use polyglot_sql::builder::*;
1895///
1896/// let sql = union_all(
1897///     select(["id"]).from("a"),
1898///     select(["id"]).from("b"),
1899/// )
1900/// .order_by(["id"])
1901/// .limit(10)
1902/// .to_sql();
1903/// ```
1904pub struct SetOpBuilder {
1905    kind: SetOpKind,
1906    left: Expression,
1907    right: Expression,
1908    all: bool,
1909    order_by: Option<OrderBy>,
1910    limit: Option<Box<Expression>>,
1911    offset: Option<Box<Expression>>,
1912}
1913
1914impl SetOpBuilder {
1915    fn new(kind: SetOpKind, left: SelectBuilder, right: SelectBuilder, all: bool) -> Self {
1916        SetOpBuilder {
1917            kind,
1918            left: left.build(),
1919            right: right.build(),
1920            all,
1921            order_by: None,
1922            limit: None,
1923            offset: None,
1924        }
1925    }
1926
1927    /// Add an ORDER BY clause applied to the combined set operation result.
1928    ///
1929    /// Expressions not already wrapped with [`.asc()`](Expr::asc) or
1930    /// [`.desc()`](Expr::desc) default to ascending order.
1931    pub fn order_by<I, E>(mut self, expressions: I) -> Self
1932    where
1933        I: IntoIterator<Item = E>,
1934        E: IntoExpr,
1935    {
1936        self.order_by = Some(OrderBy {
1937            siblings: false,
1938            expressions: expressions
1939                .into_iter()
1940                .map(|e| {
1941                    let expr = e.into_expr().0;
1942                    match expr {
1943                        Expression::Ordered(o) => *o,
1944                        other => Ordered {
1945                            this: other,
1946                            desc: false,
1947                            nulls_first: None,
1948                            explicit_asc: false,
1949                            with_fill: None,
1950                        },
1951                    }
1952                })
1953                .collect(),
1954        });
1955        self
1956    }
1957
1958    /// Restrict the combined set operation result to `count` rows.
1959    pub fn limit(mut self, count: usize) -> Self {
1960        self.limit = Some(Box::new(Expression::Literal(Literal::Number(
1961            count.to_string(),
1962        ))));
1963        self
1964    }
1965
1966    /// Skip the first `count` rows from the combined set operation result.
1967    pub fn offset(mut self, count: usize) -> Self {
1968        self.offset = Some(Box::new(Expression::Literal(Literal::Number(
1969            count.to_string(),
1970        ))));
1971        self
1972    }
1973
1974    /// Consume this builder and produce the final set operation [`Expression`] AST node.
1975    ///
1976    /// The returned expression is one of [`Expression::Union`], [`Expression::Intersect`],
1977    /// or [`Expression::Except`] depending on how the builder was created.
1978    pub fn build(self) -> Expression {
1979        match self.kind {
1980            SetOpKind::Union => Expression::Union(Box::new(Union {
1981                left: self.left,
1982                right: self.right,
1983                all: self.all,
1984                distinct: false,
1985                with: None,
1986                order_by: self.order_by,
1987                limit: self.limit,
1988                offset: self.offset,
1989                distribute_by: None,
1990                sort_by: None,
1991                cluster_by: None,
1992                by_name: false,
1993                side: None,
1994                kind: None,
1995                corresponding: false,
1996                strict: false,
1997                on_columns: Vec::new(),
1998            })),
1999            SetOpKind::Intersect => Expression::Intersect(Box::new(Intersect {
2000                left: self.left,
2001                right: self.right,
2002                all: self.all,
2003                distinct: false,
2004                with: None,
2005                order_by: self.order_by,
2006                limit: self.limit,
2007                offset: self.offset,
2008                distribute_by: None,
2009                sort_by: None,
2010                cluster_by: None,
2011                by_name: false,
2012                side: None,
2013                kind: None,
2014                corresponding: false,
2015                strict: false,
2016                on_columns: Vec::new(),
2017            })),
2018            SetOpKind::Except => Expression::Except(Box::new(Except {
2019                left: self.left,
2020                right: self.right,
2021                all: self.all,
2022                distinct: false,
2023                with: None,
2024                order_by: self.order_by,
2025                limit: self.limit,
2026                offset: self.offset,
2027                distribute_by: None,
2028                sort_by: None,
2029                cluster_by: None,
2030                by_name: false,
2031                side: None,
2032                kind: None,
2033                corresponding: false,
2034                strict: false,
2035                on_columns: Vec::new(),
2036            })),
2037        }
2038    }
2039
2040    /// Consume this builder, generate, and return the SQL string.
2041    pub fn to_sql(self) -> String {
2042        Generator::sql(&self.build()).unwrap_or_default()
2043    }
2044}
2045
2046/// Create a `UNION` (duplicate elimination) of two SELECT queries.
2047///
2048/// Returns a [`SetOpBuilder`] for optional ORDER BY / LIMIT / OFFSET chaining.
2049pub fn union(left: SelectBuilder, right: SelectBuilder) -> SetOpBuilder {
2050    SetOpBuilder::new(SetOpKind::Union, left, right, false)
2051}
2052
2053/// Create a `UNION ALL` (keep duplicates) of two SELECT queries.
2054///
2055/// Returns a [`SetOpBuilder`] for optional ORDER BY / LIMIT / OFFSET chaining.
2056pub fn union_all(left: SelectBuilder, right: SelectBuilder) -> SetOpBuilder {
2057    SetOpBuilder::new(SetOpKind::Union, left, right, true)
2058}
2059
2060/// Create an `INTERSECT` (rows common to both) of two SELECT queries.
2061///
2062/// Returns a [`SetOpBuilder`] for optional ORDER BY / LIMIT / OFFSET chaining.
2063pub fn intersect(left: SelectBuilder, right: SelectBuilder) -> SetOpBuilder {
2064    SetOpBuilder::new(SetOpKind::Intersect, left, right, false)
2065}
2066
2067/// Create an `INTERSECT ALL` (keep duplicate common rows) of two SELECT queries.
2068///
2069/// Returns a [`SetOpBuilder`] for optional ORDER BY / LIMIT / OFFSET chaining.
2070pub fn intersect_all(left: SelectBuilder, right: SelectBuilder) -> SetOpBuilder {
2071    SetOpBuilder::new(SetOpKind::Intersect, left, right, true)
2072}
2073
2074/// Create an `EXCEPT` (rows in left but not right) of two SELECT queries.
2075///
2076/// Returns a [`SetOpBuilder`] for optional ORDER BY / LIMIT / OFFSET chaining.
2077pub fn except_(left: SelectBuilder, right: SelectBuilder) -> SetOpBuilder {
2078    SetOpBuilder::new(SetOpKind::Except, left, right, false)
2079}
2080
2081/// Create an `EXCEPT ALL` (keep duplicate difference rows) of two SELECT queries.
2082///
2083/// Returns a [`SetOpBuilder`] for optional ORDER BY / LIMIT / OFFSET chaining.
2084pub fn except_all(left: SelectBuilder, right: SelectBuilder) -> SetOpBuilder {
2085    SetOpBuilder::new(SetOpKind::Except, left, right, true)
2086}
2087
2088// ---------------------------------------------------------------------------
2089// WindowDefBuilder
2090// ---------------------------------------------------------------------------
2091
2092/// Builder for constructing named `WINDOW` clause definitions.
2093///
2094/// Used with [`SelectBuilder::window()`] to define reusable window specifications.
2095/// Supports PARTITION BY and ORDER BY clauses.
2096///
2097/// # Examples
2098///
2099/// ```
2100/// use polyglot_sql::builder::*;
2101///
2102/// let sql = select(["id"])
2103///     .from("t")
2104///     .window(
2105///         "w",
2106///         WindowDefBuilder::new()
2107///             .partition_by(["dept"])
2108///             .order_by([col("salary").desc()]),
2109///     )
2110///     .to_sql();
2111/// ```
2112pub struct WindowDefBuilder {
2113    partition_by: Vec<Expression>,
2114    order_by: Vec<Ordered>,
2115}
2116
2117impl WindowDefBuilder {
2118    /// Create a new, empty window definition builder with no partitioning or ordering.
2119    pub fn new() -> Self {
2120        WindowDefBuilder {
2121            partition_by: Vec::new(),
2122            order_by: Vec::new(),
2123        }
2124    }
2125
2126    /// Set the PARTITION BY expressions for the window definition.
2127    pub fn partition_by<I, E>(mut self, expressions: I) -> Self
2128    where
2129        I: IntoIterator<Item = E>,
2130        E: IntoExpr,
2131    {
2132        self.partition_by = expressions.into_iter().map(|e| e.into_expr().0).collect();
2133        self
2134    }
2135
2136    /// Set the ORDER BY expressions for the window definition.
2137    ///
2138    /// Expressions not already wrapped with [`.asc()`](Expr::asc) or
2139    /// [`.desc()`](Expr::desc) default to ascending order.
2140    pub fn order_by<I, E>(mut self, expressions: I) -> Self
2141    where
2142        I: IntoIterator<Item = E>,
2143        E: IntoExpr,
2144    {
2145        self.order_by = expressions
2146            .into_iter()
2147            .map(|e| {
2148                let expr = e.into_expr().0;
2149                match expr {
2150                    Expression::Ordered(o) => *o,
2151                    other => Ordered {
2152                        this: other,
2153                        desc: false,
2154                        nulls_first: None,
2155                        explicit_asc: false,
2156                        with_fill: None,
2157                    },
2158                }
2159            })
2160            .collect();
2161        self
2162    }
2163}
2164
2165// ---------------------------------------------------------------------------
2166// Trait: IntoExpr
2167// ---------------------------------------------------------------------------
2168
2169/// Conversion trait for types that can be turned into an [`Expr`].
2170///
2171/// This trait is implemented for:
2172///
2173/// - [`Expr`] -- returned as-is.
2174/// - `&str` and `String` -- converted to a column reference via [`col()`].
2175/// - [`Expression`] -- wrapped directly in an [`Expr`].
2176///
2177/// It is used as a generic bound throughout the builder API so that functions like
2178/// [`select()`], [`SelectBuilder::order_by()`], and [`SelectBuilder::group_by()`] can
2179/// accept plain strings, [`Expr`] values, or raw [`Expression`] nodes interchangeably.
2180pub trait IntoExpr {
2181    /// Convert this value into an [`Expr`].
2182    fn into_expr(self) -> Expr;
2183}
2184
2185impl IntoExpr for Expr {
2186    fn into_expr(self) -> Expr {
2187        self
2188    }
2189}
2190
2191impl IntoExpr for &str {
2192    /// Convert a string slice to a column reference via [`col()`].
2193    fn into_expr(self) -> Expr {
2194        col(self)
2195    }
2196}
2197
2198impl IntoExpr for String {
2199    /// Convert an owned string to a column reference via [`col()`].
2200    fn into_expr(self) -> Expr {
2201        col(&self)
2202    }
2203}
2204
2205impl IntoExpr for Expression {
2206    /// Wrap a raw [`Expression`] in an [`Expr`].
2207    fn into_expr(self) -> Expr {
2208        Expr(self)
2209    }
2210}
2211
2212// ---------------------------------------------------------------------------
2213// Trait: IntoLiteral
2214// ---------------------------------------------------------------------------
2215
2216/// Conversion trait for types that can be turned into a SQL literal [`Expr`].
2217///
2218/// This trait is used by [`lit()`] to accept various Rust primitive types and convert
2219/// them into the appropriate SQL literal representation.
2220///
2221/// Implemented for:
2222///
2223/// - `&str`, `String` -- produce a SQL string literal (e.g. `'hello'`).
2224/// - `i32`, `i64`, `usize`, `f64` -- produce a SQL numeric literal (e.g. `42`, `3.14`).
2225/// - `bool` -- produce a SQL boolean literal (`TRUE` or `FALSE`).
2226pub trait IntoLiteral {
2227    /// Convert this value into a literal [`Expr`].
2228    fn into_literal(self) -> Expr;
2229}
2230
2231impl IntoLiteral for &str {
2232    /// Produce a SQL string literal (e.g. `'hello'`).
2233    fn into_literal(self) -> Expr {
2234        Expr(Expression::Literal(Literal::String(self.to_string())))
2235    }
2236}
2237
2238impl IntoLiteral for String {
2239    /// Produce a SQL string literal from an owned string.
2240    fn into_literal(self) -> Expr {
2241        Expr(Expression::Literal(Literal::String(self)))
2242    }
2243}
2244
2245impl IntoLiteral for i64 {
2246    /// Produce a SQL numeric literal from a 64-bit integer.
2247    fn into_literal(self) -> Expr {
2248        Expr(Expression::Literal(Literal::Number(self.to_string())))
2249    }
2250}
2251
2252impl IntoLiteral for i32 {
2253    /// Produce a SQL numeric literal from a 32-bit integer.
2254    fn into_literal(self) -> Expr {
2255        Expr(Expression::Literal(Literal::Number(self.to_string())))
2256    }
2257}
2258
2259impl IntoLiteral for usize {
2260    /// Produce a SQL numeric literal from a `usize`.
2261    fn into_literal(self) -> Expr {
2262        Expr(Expression::Literal(Literal::Number(self.to_string())))
2263    }
2264}
2265
2266impl IntoLiteral for f64 {
2267    /// Produce a SQL numeric literal from a 64-bit float.
2268    fn into_literal(self) -> Expr {
2269        Expr(Expression::Literal(Literal::Number(self.to_string())))
2270    }
2271}
2272
2273impl IntoLiteral for bool {
2274    /// Produce a SQL boolean literal (`TRUE` or `FALSE`).
2275    fn into_literal(self) -> Expr {
2276        Expr(Expression::Boolean(BooleanLiteral { value: self }))
2277    }
2278}
2279
2280// ---------------------------------------------------------------------------
2281// Helpers
2282// ---------------------------------------------------------------------------
2283
2284fn binary_op(left: Expression, right: Expression) -> BinaryOp {
2285    BinaryOp {
2286        left,
2287        right,
2288        left_comments: Vec::new(),
2289        operator_comments: Vec::new(),
2290        trailing_comments: Vec::new(),
2291    }
2292}
2293
2294// ---------------------------------------------------------------------------
2295// MergeBuilder
2296// ---------------------------------------------------------------------------
2297
2298/// Start building a `MERGE INTO` statement targeting the given table.
2299///
2300/// Returns a [`MergeBuilder`] which supports `.using()`, `.when_matched_update()`,
2301/// `.when_matched_delete()`, and `.when_not_matched_insert()`.
2302///
2303/// # Examples
2304///
2305/// ```
2306/// use polyglot_sql::builder::*;
2307///
2308/// let sql = merge_into("target")
2309///     .using("source", col("target.id").eq(col("source.id")))
2310///     .when_matched_update(vec![("name", col("source.name"))])
2311///     .when_not_matched_insert(&["id", "name"], vec![col("source.id"), col("source.name")])
2312///     .to_sql();
2313/// assert!(sql.contains("MERGE INTO"));
2314/// ```
2315pub fn merge_into(target: &str) -> MergeBuilder {
2316    MergeBuilder {
2317        target: Expression::Table(TableRef::new(target)),
2318        using: None,
2319        on: None,
2320        whens: Vec::new(),
2321    }
2322}
2323
2324/// Fluent builder for constructing `MERGE INTO` statements.
2325///
2326/// Created by the [`merge_into()`] entry-point function.
2327pub struct MergeBuilder {
2328    target: Expression,
2329    using: Option<Expression>,
2330    on: Option<Expression>,
2331    whens: Vec<Expression>,
2332}
2333
2334impl MergeBuilder {
2335    /// Set the source table and ON join condition.
2336    pub fn using(mut self, source: &str, on: Expr) -> Self {
2337        self.using = Some(Expression::Table(TableRef::new(source)));
2338        self.on = Some(on.0);
2339        self
2340    }
2341
2342    /// Add a `WHEN MATCHED THEN UPDATE SET` clause.
2343    pub fn when_matched_update(mut self, assignments: Vec<(&str, Expr)>) -> Self {
2344        let eqs: Vec<Expression> = assignments
2345            .into_iter()
2346            .map(|(col_name, val)| {
2347                Expression::Eq(Box::new(BinaryOp {
2348                    left: Expression::Column(Column {
2349                        name: Identifier::new(col_name),
2350                        table: None,
2351                        join_mark: false,
2352                        trailing_comments: Vec::new(),
2353                    }),
2354                    right: val.0,
2355                    left_comments: Vec::new(),
2356                    operator_comments: Vec::new(),
2357                    trailing_comments: Vec::new(),
2358                }))
2359            })
2360            .collect();
2361
2362        let action = Expression::Tuple(Box::new(Tuple {
2363            expressions: vec![
2364                Expression::Var(Box::new(Var {
2365                    this: "UPDATE".to_string(),
2366                })),
2367                Expression::Tuple(Box::new(Tuple { expressions: eqs })),
2368            ],
2369        }));
2370
2371        let when = Expression::When(Box::new(When {
2372            matched: Some(Box::new(Expression::Boolean(BooleanLiteral { value: true }))),
2373            source: None,
2374            condition: None,
2375            then: Box::new(action),
2376        }));
2377        self.whens.push(when);
2378        self
2379    }
2380
2381    /// Add a `WHEN MATCHED THEN UPDATE SET` clause with an additional condition.
2382    pub fn when_matched_update_where(
2383        mut self,
2384        condition: Expr,
2385        assignments: Vec<(&str, Expr)>,
2386    ) -> Self {
2387        let eqs: Vec<Expression> = assignments
2388            .into_iter()
2389            .map(|(col_name, val)| {
2390                Expression::Eq(Box::new(BinaryOp {
2391                    left: Expression::Column(Column {
2392                        name: Identifier::new(col_name),
2393                        table: None,
2394                        join_mark: false,
2395                        trailing_comments: Vec::new(),
2396                    }),
2397                    right: val.0,
2398                    left_comments: Vec::new(),
2399                    operator_comments: Vec::new(),
2400                    trailing_comments: Vec::new(),
2401                }))
2402            })
2403            .collect();
2404
2405        let action = Expression::Tuple(Box::new(Tuple {
2406            expressions: vec![
2407                Expression::Var(Box::new(Var {
2408                    this: "UPDATE".to_string(),
2409                })),
2410                Expression::Tuple(Box::new(Tuple { expressions: eqs })),
2411            ],
2412        }));
2413
2414        let when = Expression::When(Box::new(When {
2415            matched: Some(Box::new(Expression::Boolean(BooleanLiteral { value: true }))),
2416            source: None,
2417            condition: Some(Box::new(condition.0)),
2418            then: Box::new(action),
2419        }));
2420        self.whens.push(when);
2421        self
2422    }
2423
2424    /// Add a `WHEN MATCHED THEN DELETE` clause.
2425    pub fn when_matched_delete(mut self) -> Self {
2426        let action = Expression::Var(Box::new(Var {
2427            this: "DELETE".to_string(),
2428        }));
2429
2430        let when = Expression::When(Box::new(When {
2431            matched: Some(Box::new(Expression::Boolean(BooleanLiteral { value: true }))),
2432            source: None,
2433            condition: None,
2434            then: Box::new(action),
2435        }));
2436        self.whens.push(when);
2437        self
2438    }
2439
2440    /// Add a `WHEN NOT MATCHED THEN INSERT (cols) VALUES (vals)` clause.
2441    pub fn when_not_matched_insert(mut self, columns: &[&str], values: Vec<Expr>) -> Self {
2442        let col_exprs: Vec<Expression> = columns
2443            .iter()
2444            .map(|c| {
2445                Expression::Column(Column {
2446                    name: Identifier::new(*c),
2447                    table: None,
2448                    join_mark: false,
2449                    trailing_comments: Vec::new(),
2450                })
2451            })
2452            .collect();
2453        let val_exprs: Vec<Expression> = values.into_iter().map(|v| v.0).collect();
2454
2455        let action = Expression::Tuple(Box::new(Tuple {
2456            expressions: vec![
2457                Expression::Var(Box::new(Var {
2458                    this: "INSERT".to_string(),
2459                })),
2460                Expression::Tuple(Box::new(Tuple {
2461                    expressions: col_exprs,
2462                })),
2463                Expression::Tuple(Box::new(Tuple {
2464                    expressions: val_exprs,
2465                })),
2466            ],
2467        }));
2468
2469        let when = Expression::When(Box::new(When {
2470            matched: Some(Box::new(Expression::Boolean(BooleanLiteral {
2471                value: false,
2472            }))),
2473            source: None,
2474            condition: None,
2475            then: Box::new(action),
2476        }));
2477        self.whens.push(when);
2478        self
2479    }
2480
2481    /// Consume this builder and produce the final [`Expression::Merge`] AST node.
2482    pub fn build(self) -> Expression {
2483        let whens_expr = Expression::Whens(Box::new(Whens {
2484            expressions: self.whens,
2485        }));
2486
2487        Expression::Merge(Box::new(Merge {
2488            this: Box::new(self.target),
2489            using: Box::new(
2490                self.using
2491                    .unwrap_or(Expression::Null(crate::expressions::Null)),
2492            ),
2493            on: self.on.map(Box::new),
2494            using_cond: None,
2495            whens: Some(Box::new(whens_expr)),
2496            with_: None,
2497            returning: None,
2498        }))
2499    }
2500
2501    /// Consume this builder, generate, and return the SQL string.
2502    pub fn to_sql(self) -> String {
2503        Generator::sql(&self.build()).unwrap_or_default()
2504    }
2505}
2506
2507fn parse_simple_data_type(name: &str) -> DataType {
2508    let upper = name.trim().to_uppercase();
2509    match upper.as_str() {
2510        "INT" | "INTEGER" => DataType::Int {
2511            length: None,
2512            integer_spelling: upper == "INTEGER",
2513        },
2514        "BIGINT" => DataType::BigInt { length: None },
2515        "SMALLINT" => DataType::SmallInt { length: None },
2516        "TINYINT" => DataType::TinyInt { length: None },
2517        "FLOAT" => DataType::Float { precision: None, scale: None, real_spelling: false },
2518        "DOUBLE" => DataType::Double {
2519            precision: None,
2520            scale: None,
2521        },
2522        "BOOLEAN" | "BOOL" => DataType::Boolean,
2523        "TEXT" => DataType::Text,
2524        "DATE" => DataType::Date,
2525        "TIMESTAMP" => DataType::Timestamp {
2526            precision: None,
2527            timezone: false,
2528        },
2529        "VARCHAR" => DataType::VarChar {
2530            length: None,
2531            parenthesized_length: false,
2532        },
2533        "CHAR" => DataType::Char { length: None },
2534        _ => {
2535            // Try to parse as a full type via the parser for complex types
2536            if let Ok(ast) = crate::parser::Parser::parse_sql(&format!("SELECT CAST(x AS {})", name)) {
2537                if let Expression::Select(s) = &ast[0] {
2538                    if let Some(Expression::Cast(c)) = s.expressions.first() {
2539                        return c.to.clone();
2540                    }
2541                }
2542            }
2543            // Fallback: treat as a custom type
2544            DataType::Custom { name: name.to_string() }
2545        }
2546    }
2547}
2548
2549#[cfg(test)]
2550mod tests {
2551    use super::*;
2552
2553    #[test]
2554    fn test_simple_select() {
2555        let sql = select(["id", "name"]).from("users").to_sql();
2556        assert_eq!(sql, "SELECT id, name FROM users");
2557    }
2558
2559    #[test]
2560    fn test_select_star() {
2561        let sql = select([star()]).from("users").to_sql();
2562        assert_eq!(sql, "SELECT * FROM users");
2563    }
2564
2565    #[test]
2566    fn test_select_with_where() {
2567        let sql = select(["id", "name"])
2568            .from("users")
2569            .where_(col("age").gt(lit(18)))
2570            .to_sql();
2571        assert_eq!(sql, "SELECT id, name FROM users WHERE age > 18");
2572    }
2573
2574    #[test]
2575    fn test_select_with_join() {
2576        let sql = select(["u.id", "o.amount"])
2577            .from("users")
2578            .join("orders", col("u.id").eq(col("o.user_id")))
2579            .to_sql();
2580        assert_eq!(
2581            sql,
2582            "SELECT u.id, o.amount FROM users JOIN orders ON u.id = o.user_id"
2583        );
2584    }
2585
2586    #[test]
2587    fn test_select_with_group_by_having() {
2588        let sql = select([col("dept"), func("COUNT", [star()]).alias("cnt")])
2589            .from("employees")
2590            .group_by(["dept"])
2591            .having(func("COUNT", [star()]).gt(lit(5)))
2592            .to_sql();
2593        assert_eq!(
2594            sql,
2595            "SELECT dept, COUNT(*) AS cnt FROM employees GROUP BY dept HAVING COUNT(*) > 5"
2596        );
2597    }
2598
2599    #[test]
2600    fn test_select_with_order_limit_offset() {
2601        let sql = select(["id", "name"])
2602            .from("users")
2603            .order_by(["name"])
2604            .limit(10)
2605            .offset(20)
2606            .to_sql();
2607        assert_eq!(
2608            sql,
2609            "SELECT id, name FROM users ORDER BY name LIMIT 10 OFFSET 20"
2610        );
2611    }
2612
2613    #[test]
2614    fn test_select_distinct() {
2615        let sql = select(["name"]).from("users").distinct().to_sql();
2616        assert_eq!(sql, "SELECT DISTINCT name FROM users");
2617    }
2618
2619    #[test]
2620    fn test_insert_values() {
2621        let sql = insert_into("users")
2622            .columns(["id", "name"])
2623            .values([lit(1), lit("Alice")])
2624            .values([lit(2), lit("Bob")])
2625            .to_sql();
2626        assert_eq!(
2627            sql,
2628            "INSERT INTO users (id, name) VALUES (1, 'Alice'), (2, 'Bob')"
2629        );
2630    }
2631
2632    #[test]
2633    fn test_insert_select() {
2634        let sql = insert_into("archive")
2635            .columns(["id", "name"])
2636            .query(select(["id", "name"]).from("users"))
2637            .to_sql();
2638        assert_eq!(
2639            sql,
2640            "INSERT INTO archive (id, name) SELECT id, name FROM users"
2641        );
2642    }
2643
2644    #[test]
2645    fn test_update() {
2646        let sql = update("users")
2647            .set("name", lit("Bob"))
2648            .set("age", lit(30))
2649            .where_(col("id").eq(lit(1)))
2650            .to_sql();
2651        assert_eq!(
2652            sql,
2653            "UPDATE users SET name = 'Bob', age = 30 WHERE id = 1"
2654        );
2655    }
2656
2657    #[test]
2658    fn test_delete() {
2659        let sql = delete("users")
2660            .where_(col("id").eq(lit(1)))
2661            .to_sql();
2662        assert_eq!(sql, "DELETE FROM users WHERE id = 1");
2663    }
2664
2665    #[test]
2666    fn test_complex_where() {
2667        let sql = select(["id"])
2668            .from("users")
2669            .where_(
2670                col("age").gte(lit(18))
2671                    .and(col("active").eq(boolean(true)))
2672                    .and(col("name").like(lit("%test%"))),
2673            )
2674            .to_sql();
2675        assert_eq!(
2676            sql,
2677            "SELECT id FROM users WHERE age >= 18 AND active = TRUE AND name LIKE '%test%'"
2678        );
2679    }
2680
2681    #[test]
2682    fn test_in_list() {
2683        let sql = select(["id"])
2684            .from("users")
2685            .where_(col("status").in_list([lit("active"), lit("pending")]))
2686            .to_sql();
2687        assert_eq!(
2688            sql,
2689            "SELECT id FROM users WHERE status IN ('active', 'pending')"
2690        );
2691    }
2692
2693    #[test]
2694    fn test_between() {
2695        let sql = select(["id"])
2696            .from("orders")
2697            .where_(col("amount").between(lit(100), lit(500)))
2698            .to_sql();
2699        assert_eq!(
2700            sql,
2701            "SELECT id FROM orders WHERE amount BETWEEN 100 AND 500"
2702        );
2703    }
2704
2705    #[test]
2706    fn test_is_null() {
2707        let sql = select(["id"])
2708            .from("users")
2709            .where_(col("email").is_null())
2710            .to_sql();
2711        assert_eq!(sql, "SELECT id FROM users WHERE email IS NULL");
2712    }
2713
2714    #[test]
2715    fn test_arithmetic() {
2716        let sql = select([col("price").mul(col("quantity")).alias("total")])
2717            .from("items")
2718            .to_sql();
2719        assert_eq!(sql, "SELECT price * quantity AS total FROM items");
2720    }
2721
2722    #[test]
2723    fn test_cast() {
2724        let sql = select([col("id").cast("VARCHAR")])
2725            .from("users")
2726            .to_sql();
2727        assert_eq!(sql, "SELECT CAST(id AS VARCHAR) FROM users");
2728    }
2729
2730    #[test]
2731    fn test_from_starter() {
2732        let sql = from("users").select_cols(["id", "name"]).to_sql();
2733        assert_eq!(sql, "SELECT id, name FROM users");
2734    }
2735
2736    #[test]
2737    fn test_qualified_column() {
2738        let sql = select([col("u.id"), col("u.name")])
2739            .from("users")
2740            .to_sql();
2741        assert_eq!(sql, "SELECT u.id, u.name FROM users");
2742    }
2743
2744    #[test]
2745    fn test_not_condition() {
2746        let sql = select(["id"])
2747            .from("users")
2748            .where_(not(col("active").eq(boolean(true))))
2749            .to_sql();
2750        assert_eq!(sql, "SELECT id FROM users WHERE NOT active = TRUE");
2751    }
2752
2753    #[test]
2754    fn test_order_by_desc() {
2755        let sql = select(["id", "name"])
2756            .from("users")
2757            .order_by([col("name").desc()])
2758            .to_sql();
2759        assert_eq!(sql, "SELECT id, name FROM users ORDER BY name DESC");
2760    }
2761
2762    #[test]
2763    fn test_left_join() {
2764        let sql = select(["u.id", "o.amount"])
2765            .from("users")
2766            .left_join("orders", col("u.id").eq(col("o.user_id")))
2767            .to_sql();
2768        assert_eq!(
2769            sql,
2770            "SELECT u.id, o.amount FROM users LEFT JOIN orders ON u.id = o.user_id"
2771        );
2772    }
2773
2774    #[test]
2775    fn test_build_returns_expression() {
2776        let expr = select(["id"]).from("users").build();
2777        assert!(matches!(expr, Expression::Select(_)));
2778    }
2779
2780    #[test]
2781    fn test_expr_interop() {
2782        // Can use Expr in select list
2783        let age_check = col("age").gt(lit(18));
2784        let sql = select([col("id"), age_check.alias("is_adult")])
2785            .from("users")
2786            .to_sql();
2787        assert_eq!(sql, "SELECT id, age > 18 AS is_adult FROM users");
2788    }
2789
2790    // -- Step 2: sql_expr / condition tests --
2791
2792    #[test]
2793    fn test_sql_expr_simple() {
2794        let expr = sql_expr("age > 18");
2795        let sql = select(["id"])
2796            .from("users")
2797            .where_(expr)
2798            .to_sql();
2799        assert_eq!(sql, "SELECT id FROM users WHERE age > 18");
2800    }
2801
2802    #[test]
2803    fn test_sql_expr_compound() {
2804        let expr = sql_expr("a > 1 AND b < 10");
2805        let sql = select(["*"])
2806            .from("t")
2807            .where_(expr)
2808            .to_sql();
2809        assert_eq!(sql, "SELECT * FROM t WHERE a > 1 AND b < 10");
2810    }
2811
2812    #[test]
2813    fn test_sql_expr_function() {
2814        let expr = sql_expr("COALESCE(a, b, 0)");
2815        let sql = select([expr.alias("val")]).from("t").to_sql();
2816        assert_eq!(sql, "SELECT COALESCE(a, b, 0) AS val FROM t");
2817    }
2818
2819    #[test]
2820    fn test_condition_alias() {
2821        let cond = condition("x > 0");
2822        let sql = select(["*"]).from("t").where_(cond).to_sql();
2823        assert_eq!(sql, "SELECT * FROM t WHERE x > 0");
2824    }
2825
2826    // -- Step 3: ilike, rlike, not_in tests --
2827
2828    #[test]
2829    fn test_ilike() {
2830        let sql = select(["id"])
2831            .from("users")
2832            .where_(col("name").ilike(lit("%test%")))
2833            .to_sql();
2834        assert_eq!(sql, "SELECT id FROM users WHERE name ILIKE '%test%'");
2835    }
2836
2837    #[test]
2838    fn test_rlike() {
2839        let sql = select(["id"])
2840            .from("users")
2841            .where_(col("name").rlike(lit("^[A-Z]")))
2842            .to_sql();
2843        assert_eq!(sql, "SELECT id FROM users WHERE REGEXP_LIKE(name, '^[A-Z]')");
2844    }
2845
2846    #[test]
2847    fn test_not_in() {
2848        let sql = select(["id"])
2849            .from("users")
2850            .where_(col("status").not_in([lit("deleted"), lit("banned")]))
2851            .to_sql();
2852        assert_eq!(
2853            sql,
2854            "SELECT id FROM users WHERE status NOT IN ('deleted', 'banned')"
2855        );
2856    }
2857
2858    // -- Step 4: CaseBuilder tests --
2859
2860    #[test]
2861    fn test_case_searched() {
2862        let expr = case()
2863            .when(col("x").gt(lit(0)), lit("positive"))
2864            .when(col("x").eq(lit(0)), lit("zero"))
2865            .else_(lit("negative"))
2866            .build();
2867        let sql = select([expr.alias("label")]).from("t").to_sql();
2868        assert_eq!(
2869            sql,
2870            "SELECT CASE WHEN x > 0 THEN 'positive' WHEN x = 0 THEN 'zero' ELSE 'negative' END AS label FROM t"
2871        );
2872    }
2873
2874    #[test]
2875    fn test_case_simple() {
2876        let expr = case_of(col("status"))
2877            .when(lit(1), lit("active"))
2878            .when(lit(0), lit("inactive"))
2879            .build();
2880        let sql = select([expr.alias("status_label")]).from("t").to_sql();
2881        assert_eq!(
2882            sql,
2883            "SELECT CASE status WHEN 1 THEN 'active' WHEN 0 THEN 'inactive' END AS status_label FROM t"
2884        );
2885    }
2886
2887    #[test]
2888    fn test_case_no_else() {
2889        let expr = case()
2890            .when(col("x").gt(lit(0)), lit("yes"))
2891            .build();
2892        let sql = select([expr]).from("t").to_sql();
2893        assert_eq!(sql, "SELECT CASE WHEN x > 0 THEN 'yes' END FROM t");
2894    }
2895
2896    // -- Step 5: subquery tests --
2897
2898    #[test]
2899    fn test_subquery_in_from() {
2900        let inner = select(["id", "name"]).from("users").where_(col("active").eq(boolean(true)));
2901        let outer = select(["sub.id"])
2902            .from_expr(subquery(inner, "sub"))
2903            .to_sql();
2904        assert_eq!(
2905            outer,
2906            "SELECT sub.id FROM (SELECT id, name FROM users WHERE active = TRUE) AS sub"
2907        );
2908    }
2909
2910    #[test]
2911    fn test_subquery_in_join() {
2912        let inner = select([col("user_id"), func("SUM", [col("amount")]).alias("total")])
2913            .from("orders")
2914            .group_by(["user_id"]);
2915        let sql = select(["u.name", "o.total"])
2916            .from("users")
2917            .join("orders", col("u.id").eq(col("o.user_id")))
2918            .to_sql();
2919        assert!(sql.contains("JOIN"));
2920        // Just verify the subquery builder doesn't panic
2921        let _sub = subquery(inner, "o");
2922    }
2923
2924    // -- Step 6: SetOpBuilder tests --
2925
2926    #[test]
2927    fn test_union() {
2928        let sql = union(
2929            select(["id"]).from("a"),
2930            select(["id"]).from("b"),
2931        )
2932        .to_sql();
2933        assert_eq!(sql, "SELECT id FROM a UNION SELECT id FROM b");
2934    }
2935
2936    #[test]
2937    fn test_union_all() {
2938        let sql = union_all(
2939            select(["id"]).from("a"),
2940            select(["id"]).from("b"),
2941        )
2942        .to_sql();
2943        assert_eq!(sql, "SELECT id FROM a UNION ALL SELECT id FROM b");
2944    }
2945
2946    #[test]
2947    fn test_intersect_builder() {
2948        let sql = intersect(
2949            select(["id"]).from("a"),
2950            select(["id"]).from("b"),
2951        )
2952        .to_sql();
2953        assert_eq!(sql, "SELECT id FROM a INTERSECT SELECT id FROM b");
2954    }
2955
2956    #[test]
2957    fn test_except_builder() {
2958        let sql = except_(
2959            select(["id"]).from("a"),
2960            select(["id"]).from("b"),
2961        )
2962        .to_sql();
2963        assert_eq!(sql, "SELECT id FROM a EXCEPT SELECT id FROM b");
2964    }
2965
2966    #[test]
2967    fn test_union_with_order_limit() {
2968        let sql = union(
2969            select(["id"]).from("a"),
2970            select(["id"]).from("b"),
2971        )
2972        .order_by(["id"])
2973        .limit(10)
2974        .to_sql();
2975        assert!(sql.contains("UNION"));
2976        assert!(sql.contains("ORDER BY"));
2977        assert!(sql.contains("LIMIT"));
2978    }
2979
2980    #[test]
2981    fn test_select_builder_union() {
2982        let sql = select(["id"])
2983            .from("a")
2984            .union(select(["id"]).from("b"))
2985            .to_sql();
2986        assert_eq!(sql, "SELECT id FROM a UNION SELECT id FROM b");
2987    }
2988
2989    // -- Step 7: SelectBuilder extensions tests --
2990
2991    #[test]
2992    fn test_qualify() {
2993        let sql = select(["id", "name"])
2994            .from("users")
2995            .qualify(col("rn").eq(lit(1)))
2996            .to_sql();
2997        assert_eq!(
2998            sql,
2999            "SELECT id, name FROM users QUALIFY rn = 1"
3000        );
3001    }
3002
3003    #[test]
3004    fn test_right_join() {
3005        let sql = select(["u.id", "o.amount"])
3006            .from("users")
3007            .right_join("orders", col("u.id").eq(col("o.user_id")))
3008            .to_sql();
3009        assert_eq!(
3010            sql,
3011            "SELECT u.id, o.amount FROM users RIGHT JOIN orders ON u.id = o.user_id"
3012        );
3013    }
3014
3015    #[test]
3016    fn test_cross_join() {
3017        let sql = select(["a.x", "b.y"])
3018            .from("a")
3019            .cross_join("b")
3020            .to_sql();
3021        assert_eq!(sql, "SELECT a.x, b.y FROM a CROSS JOIN b");
3022    }
3023
3024    #[test]
3025    fn test_lateral_view() {
3026        let sql = select(["id", "col_val"])
3027            .from("t")
3028            .lateral_view(func("EXPLODE", [col("arr")]), "lv", ["col_val"])
3029            .to_sql();
3030        assert!(sql.contains("LATERAL VIEW"));
3031        assert!(sql.contains("EXPLODE"));
3032    }
3033
3034    #[test]
3035    fn test_window_clause() {
3036        let sql = select(["id"])
3037            .from("t")
3038            .window(
3039                "w",
3040                WindowDefBuilder::new()
3041                    .partition_by(["dept"])
3042                    .order_by(["salary"]),
3043            )
3044            .to_sql();
3045        assert!(sql.contains("WINDOW"));
3046        assert!(sql.contains("PARTITION BY"));
3047    }
3048
3049    // -- XOR operator tests --
3050
3051    #[test]
3052    fn test_xor() {
3053        let sql = select(["*"])
3054            .from("t")
3055            .where_(col("a").xor(col("b")))
3056            .to_sql();
3057        assert_eq!(sql, "SELECT * FROM t WHERE a XOR b");
3058    }
3059
3060    // -- FOR UPDATE / FOR SHARE tests --
3061
3062    #[test]
3063    fn test_for_update() {
3064        let sql = select(["id"]).from("t").for_update().to_sql();
3065        assert_eq!(sql, "SELECT id FROM t FOR UPDATE");
3066    }
3067
3068    #[test]
3069    fn test_for_share() {
3070        let sql = select(["id"]).from("t").for_share().to_sql();
3071        assert_eq!(sql, "SELECT id FROM t FOR SHARE");
3072    }
3073
3074    // -- Hint tests --
3075
3076    #[test]
3077    fn test_hint() {
3078        let sql = select(["*"]).from("t").hint("FULL(t)").to_sql();
3079        assert!(sql.contains("FULL(t)"), "Expected hint in: {}", sql);
3080    }
3081
3082    // -- CTAS tests --
3083
3084    #[test]
3085    fn test_ctas() {
3086        let expr = select(["*"]).from("t").ctas("new_table");
3087        let sql = Generator::sql(&expr).unwrap();
3088        assert_eq!(sql, "CREATE TABLE new_table AS SELECT * FROM t");
3089    }
3090
3091    // -- MergeBuilder tests --
3092
3093    #[test]
3094    fn test_merge_update_insert() {
3095        let sql = merge_into("target")
3096            .using("source", col("target.id").eq(col("source.id")))
3097            .when_matched_update(vec![("name", col("source.name"))])
3098            .when_not_matched_insert(
3099                &["id", "name"],
3100                vec![col("source.id"), col("source.name")],
3101            )
3102            .to_sql();
3103        assert!(sql.contains("MERGE INTO"), "Expected MERGE INTO in: {}", sql);
3104        assert!(sql.contains("USING"), "Expected USING in: {}", sql);
3105        assert!(sql.contains("WHEN MATCHED"), "Expected WHEN MATCHED in: {}", sql);
3106        assert!(sql.contains("UPDATE SET"), "Expected UPDATE SET in: {}", sql);
3107        assert!(
3108            sql.contains("WHEN NOT MATCHED"),
3109            "Expected WHEN NOT MATCHED in: {}",
3110            sql
3111        );
3112        assert!(sql.contains("INSERT"), "Expected INSERT in: {}", sql);
3113    }
3114
3115    #[test]
3116    fn test_merge_delete() {
3117        let sql = merge_into("target")
3118            .using("source", col("target.id").eq(col("source.id")))
3119            .when_matched_delete()
3120            .to_sql();
3121        assert!(sql.contains("MERGE INTO"), "Expected MERGE INTO in: {}", sql);
3122        assert!(sql.contains("WHEN MATCHED THEN DELETE"), "Expected WHEN MATCHED THEN DELETE in: {}", sql);
3123    }
3124
3125    #[test]
3126    fn test_merge_with_condition() {
3127        let sql = merge_into("target")
3128            .using("source", col("target.id").eq(col("source.id")))
3129            .when_matched_update_where(
3130                col("source.active").eq(boolean(true)),
3131                vec![("name", col("source.name"))],
3132            )
3133            .to_sql();
3134        assert!(sql.contains("MERGE INTO"), "Expected MERGE INTO in: {}", sql);
3135        assert!(sql.contains("AND source.active = TRUE"), "Expected condition in: {}", sql);
3136    }
3137}