mik_sql/
builder.rs

1//! Query builder for SQL generation with parameterization.
2
3use crate::dialect::{Dialect, Postgres, Sqlite};
4use crate::pagination::{Cursor, IntoCursor};
5use crate::validate::{assert_valid_sql_expression, assert_valid_sql_identifier};
6
7/// SQL comparison operators.
8#[derive(Debug, Clone, Copy, PartialEq, Eq)]
9pub enum Operator {
10    /// Equal: `=`
11    Eq,
12    /// Not equal: `!=`
13    Ne,
14    /// Greater than: `>`
15    Gt,
16    /// Greater than or equal: `>=`
17    Gte,
18    /// Less than: `<`
19    Lt,
20    /// Less than or equal: `<=`
21    Lte,
22    /// In array: `IN` or `= ANY`
23    In,
24    /// Not in array: `NOT IN` or `!= ALL`
25    NotIn,
26    /// Regex match: `~` (Postgres) or `LIKE` (`SQLite`)
27    Regex,
28    /// Pattern match: `LIKE`
29    Like,
30    /// Case-insensitive pattern match: `ILIKE` (Postgres) or `LIKE` (`SQLite`)
31    ILike,
32    /// String starts with: `LIKE $1 || '%'`
33    StartsWith,
34    /// String ends with: `LIKE '%' || $1`
35    EndsWith,
36    /// String contains: `LIKE '%' || $1 || '%'`
37    Contains,
38    /// Between two values: `BETWEEN $1 AND $2`
39    Between,
40}
41
42/// Logical operators for compound filters.
43#[derive(Debug, Clone, Copy, PartialEq, Eq)]
44pub enum LogicalOp {
45    /// All conditions must match: `AND`
46    And,
47    /// At least one condition must match: `OR`
48    Or,
49    /// Negate the condition: `NOT`
50    Not,
51}
52
53/// A filter expression that can be simple or compound.
54#[derive(Debug, Clone)]
55pub enum FilterExpr {
56    /// A simple field comparison.
57    Simple(Filter),
58    /// A compound filter with logical operator.
59    Compound(CompoundFilter),
60}
61
62/// A compound filter combining multiple expressions with a logical operator.
63#[derive(Debug, Clone)]
64pub struct CompoundFilter {
65    pub op: LogicalOp,
66    pub filters: Vec<FilterExpr>,
67}
68
69impl CompoundFilter {
70    /// Create an AND compound filter.
71    #[must_use]
72    pub fn and(filters: Vec<FilterExpr>) -> Self {
73        Self {
74            op: LogicalOp::And,
75            filters,
76        }
77    }
78
79    /// Create an OR compound filter.
80    #[must_use]
81    pub fn or(filters: Vec<FilterExpr>) -> Self {
82        Self {
83            op: LogicalOp::Or,
84            filters,
85        }
86    }
87
88    /// Create a NOT compound filter (wraps a single filter).
89    #[must_use]
90    pub fn not(filter: FilterExpr) -> Self {
91        Self {
92            op: LogicalOp::Not,
93            filters: vec![filter],
94        }
95    }
96}
97
98/// Aggregation functions.
99#[derive(Debug, Clone, Copy, PartialEq, Eq)]
100pub enum AggregateFunc {
101    /// Count rows: `COUNT(*)`
102    Count,
103    /// Count distinct values: `COUNT(DISTINCT field)`
104    CountDistinct,
105    /// Sum values: `SUM(field)`
106    Sum,
107    /// Average value: `AVG(field)`
108    Avg,
109    /// Minimum value: `MIN(field)`
110    Min,
111    /// Maximum value: `MAX(field)`
112    Max,
113}
114
115/// An aggregation expression.
116#[derive(Debug, Clone)]
117pub struct Aggregate {
118    pub func: AggregateFunc,
119    /// Field to aggregate, None for COUNT(*)
120    pub field: Option<String>,
121    /// Optional alias for the result
122    pub alias: Option<String>,
123}
124
125impl Aggregate {
126    /// Create a COUNT(*) aggregation.
127    #[must_use]
128    pub fn count() -> Self {
129        Self {
130            func: AggregateFunc::Count,
131            field: None,
132            alias: Some("count".to_string()),
133        }
134    }
135
136    /// Create a COUNT(field) aggregation.
137    ///
138    /// # Panics
139    ///
140    /// Panics if the field name is not a valid SQL identifier.
141    pub fn count_field(field: impl Into<String>) -> Self {
142        let field = field.into();
143        assert_valid_sql_identifier(&field, "aggregate field");
144        Self {
145            func: AggregateFunc::Count,
146            field: Some(field),
147            alias: None,
148        }
149    }
150
151    /// Create a COUNT(DISTINCT field) aggregation.
152    ///
153    /// # Panics
154    ///
155    /// Panics if the field name is not a valid SQL identifier.
156    pub fn count_distinct(field: impl Into<String>) -> Self {
157        let field = field.into();
158        assert_valid_sql_identifier(&field, "aggregate field");
159        Self {
160            func: AggregateFunc::CountDistinct,
161            field: Some(field),
162            alias: None,
163        }
164    }
165
166    /// Create a SUM(field) aggregation.
167    ///
168    /// # Panics
169    ///
170    /// Panics if the field name is not a valid SQL identifier.
171    pub fn sum(field: impl Into<String>) -> Self {
172        let field = field.into();
173        assert_valid_sql_identifier(&field, "aggregate field");
174        Self {
175            func: AggregateFunc::Sum,
176            field: Some(field),
177            alias: None,
178        }
179    }
180
181    /// Create an AVG(field) aggregation.
182    ///
183    /// # Panics
184    ///
185    /// Panics if the field name is not a valid SQL identifier.
186    pub fn avg(field: impl Into<String>) -> Self {
187        let field = field.into();
188        assert_valid_sql_identifier(&field, "aggregate field");
189        Self {
190            func: AggregateFunc::Avg,
191            field: Some(field),
192            alias: None,
193        }
194    }
195
196    /// Create a MIN(field) aggregation.
197    ///
198    /// # Panics
199    ///
200    /// Panics if the field name is not a valid SQL identifier.
201    pub fn min(field: impl Into<String>) -> Self {
202        let field = field.into();
203        assert_valid_sql_identifier(&field, "aggregate field");
204        Self {
205            func: AggregateFunc::Min,
206            field: Some(field),
207            alias: None,
208        }
209    }
210
211    /// Create a MAX(field) aggregation.
212    ///
213    /// # Panics
214    ///
215    /// Panics if the field name is not a valid SQL identifier.
216    pub fn max(field: impl Into<String>) -> Self {
217        let field = field.into();
218        assert_valid_sql_identifier(&field, "aggregate field");
219        Self {
220            func: AggregateFunc::Max,
221            field: Some(field),
222            alias: None,
223        }
224    }
225
226    /// Set an alias for the aggregation result.
227    ///
228    /// # Panics
229    ///
230    /// Panics if the alias is not a valid SQL identifier.
231    pub fn as_alias(mut self, alias: impl Into<String>) -> Self {
232        let alias = alias.into();
233        assert_valid_sql_identifier(&alias, "aggregate alias");
234        self.alias = Some(alias);
235        self
236    }
237
238    /// Generate SQL for this aggregation.
239    #[must_use]
240    pub fn to_sql(&self) -> String {
241        let expr = match (&self.func, &self.field) {
242            (AggregateFunc::Count, None) => "COUNT(*)".to_string(),
243            (AggregateFunc::Count, Some(f)) => format!("COUNT({f})"),
244            (AggregateFunc::CountDistinct, Some(f)) => format!("COUNT(DISTINCT {f})"),
245            (AggregateFunc::Sum, Some(f)) => format!("SUM({f})"),
246            (AggregateFunc::Avg, Some(f)) => format!("AVG({f})"),
247            (AggregateFunc::Min, Some(f)) => format!("MIN({f})"),
248            (AggregateFunc::Max, Some(f)) => format!("MAX({f})"),
249            _ => "COUNT(*)".to_string(),
250        };
251
252        match &self.alias {
253            Some(a) => format!("{expr} AS {a}"),
254            None => expr,
255        }
256    }
257}
258
259/// SQL parameter values.
260#[derive(Debug, Clone, PartialEq)]
261pub enum Value {
262    Null,
263    Bool(bool),
264    Int(i64),
265    Float(f64),
266    String(String),
267    Array(Vec<Value>),
268}
269
270/// Sort direction.
271#[derive(Debug, Clone, Copy, PartialEq, Eq)]
272pub enum SortDir {
273    Asc,
274    Desc,
275}
276
277/// Sort field with direction.
278#[derive(Debug, Clone, PartialEq, Eq)]
279pub struct SortField {
280    pub field: String,
281    pub dir: SortDir,
282}
283
284impl SortField {
285    /// Create a new sort field.
286    pub fn new(field: impl Into<String>, dir: SortDir) -> Self {
287        Self {
288            field: field.into(),
289            dir,
290        }
291    }
292
293    /// Parse a sort string like "name,-created_at" into sort fields.
294    ///
295    /// Fields prefixed with `-` are sorted descending.
296    /// Validates against allowed fields list.
297    ///
298    /// # Security Note
299    ///
300    /// If `allowed` is empty, ALL fields are allowed. For user input, always
301    /// provide an explicit whitelist to prevent sorting by sensitive columns.
302    pub fn parse_sort_string(sort: &str, allowed: &[&str]) -> Result<Vec<SortField>, String> {
303        let mut result = Vec::new();
304
305        for part in sort.split(',') {
306            let part = part.trim();
307            if part.is_empty() {
308                continue;
309            }
310
311            let (field, dir) = if let Some(stripped) = part.strip_prefix('-') {
312                (stripped, SortDir::Desc)
313            } else {
314                (part, SortDir::Asc)
315            };
316
317            // Validate against whitelist (empty = allow all, consistent with FilterValidator)
318            if !allowed.is_empty() && !allowed.contains(&field) {
319                return Err(format!(
320                    "Sort field '{field}' not allowed. Allowed: {allowed:?}"
321                ));
322            }
323
324            result.push(SortField::new(field, dir));
325        }
326
327        Ok(result)
328    }
329}
330
331/// Filter condition.
332#[derive(Debug, Clone)]
333pub struct Filter {
334    pub field: String,
335    pub op: Operator,
336    pub value: Value,
337}
338
339/// Query result with SQL string and parameters.
340#[derive(Debug)]
341#[must_use = "QueryResult must be used to execute the query"]
342pub struct QueryResult {
343    pub sql: String,
344    pub params: Vec<Value>,
345}
346
347/// A computed field expression with alias.
348#[derive(Debug, Clone)]
349pub struct ComputedField {
350    /// The alias for the computed field.
351    pub alias: String,
352    /// The SQL expression (e.g., "`first_name` || ' ' || `last_name`").
353    pub expression: String,
354}
355
356impl ComputedField {
357    /// Create a new computed field.
358    pub fn new(alias: impl Into<String>, expression: impl Into<String>) -> Self {
359        Self {
360            alias: alias.into(),
361            expression: expression.into(),
362        }
363    }
364
365    /// Generate the SQL for this computed field.
366    #[must_use]
367    pub fn to_sql(&self) -> String {
368        format!("({}) AS {}", self.expression, self.alias)
369    }
370}
371
372/// Cursor pagination direction.
373#[derive(Debug, Clone, Copy, PartialEq, Eq)]
374pub enum CursorDirection {
375    /// Paginate forward (after the cursor).
376    After,
377    /// Paginate backward (before the cursor).
378    Before,
379}
380
381// ═══════════════════════════════════════════════════════════════════════════
382// SHARED FILTER BUILDING FUNCTIONS
383// ═══════════════════════════════════════════════════════════════════════════
384
385/// Build a filter expression (simple or compound).
386fn build_filter_expr_impl<D: Dialect>(
387    dialect: &D,
388    expr: &FilterExpr,
389    start_idx: usize,
390) -> (String, Vec<Value>, usize) {
391    match expr {
392        FilterExpr::Simple(filter) => build_condition_impl(dialect, filter, start_idx),
393        FilterExpr::Compound(compound) => build_compound_filter_impl(dialect, compound, start_idx),
394    }
395}
396
397/// Build a compound filter (AND, OR, NOT).
398fn build_compound_filter_impl<D: Dialect>(
399    dialect: &D,
400    compound: &CompoundFilter,
401    start_idx: usize,
402) -> (String, Vec<Value>, usize) {
403    let mut idx = start_idx;
404    let mut all_params = Vec::new();
405    let mut conditions = Vec::new();
406
407    for filter_expr in &compound.filters {
408        let (condition, params, new_idx) = build_filter_expr_impl(dialect, filter_expr, idx);
409        conditions.push(condition);
410        all_params.extend(params);
411        idx = new_idx;
412    }
413
414    let sql = match compound.op {
415        LogicalOp::And => {
416            if conditions.len() == 1 {
417                conditions.into_iter().next().unwrap()
418            } else {
419                format!("({})", conditions.join(" AND "))
420            }
421        },
422        LogicalOp::Or => {
423            if conditions.len() == 1 {
424                conditions.into_iter().next().unwrap()
425            } else {
426                format!("({})", conditions.join(" OR "))
427            }
428        },
429        LogicalOp::Not => {
430            let inner = conditions.into_iter().next().unwrap_or_default();
431            format!("NOT ({inner})")
432        },
433    };
434
435    (sql, all_params, idx)
436}
437
438/// Build a single filter condition.
439fn build_condition_impl<D: Dialect>(
440    dialect: &D,
441    filter: &Filter,
442    start_idx: usize,
443) -> (String, Vec<Value>, usize) {
444    let field = &filter.field;
445    let idx = start_idx;
446
447    match (&filter.op, &filter.value) {
448        // NULL handling
449        (Operator::Eq, Value::Null) => (format!("{field} IS NULL"), vec![], idx),
450        (Operator::Ne, Value::Null) => (format!("{field} IS NOT NULL"), vec![], idx),
451
452        // IN/NOT IN with arrays
453        (Operator::In, Value::Array(values)) => {
454            let (sql, params) = dialect.in_clause(field, values, idx);
455            let new_idx = idx + params.len();
456            (sql, params, new_idx)
457        },
458        (Operator::NotIn, Value::Array(values)) => {
459            let (sql, params) = dialect.not_in_clause(field, values, idx);
460            let new_idx = idx + params.len();
461            (sql, params, new_idx)
462        },
463
464        // Boolean values - parameterized
465        (Operator::Eq, Value::Bool(_)) => {
466            let sql = format!("{} = {}", field, dialect.param(idx));
467            (sql, vec![filter.value.clone()], idx + 1)
468        },
469        (Operator::Ne, Value::Bool(_)) => {
470            let sql = format!("{} != {}", field, dialect.param(idx));
471            (sql, vec![filter.value.clone()], idx + 1)
472        },
473
474        // Regex
475        (Operator::Regex, value) => {
476            let op = dialect.regex_op();
477            let sql = format!("{} {} {}", field, op, dialect.param(idx));
478            (sql, vec![value.clone()], idx + 1)
479        },
480
481        // ILIKE (falls back to LIKE on SQLite)
482        (Operator::ILike, value) => {
483            let sql = if dialect.supports_ilike() {
484                format!("{} ILIKE {}", field, dialect.param(idx))
485            } else {
486                format!("{} LIKE {}", field, dialect.param(idx))
487            };
488            (sql, vec![value.clone()], idx + 1)
489        },
490
491        // String pattern operators
492        (Operator::StartsWith, value) => {
493            let sql = dialect.starts_with_clause(field, idx);
494            (sql, vec![value.clone()], idx + 1)
495        },
496        (Operator::EndsWith, value) => {
497            let sql = dialect.ends_with_clause(field, idx);
498            (sql, vec![value.clone()], idx + 1)
499        },
500        (Operator::Contains, value) => {
501            let sql = dialect.contains_clause(field, idx);
502            (sql, vec![value.clone()], idx + 1)
503        },
504
505        // BETWEEN operator - takes an array with exactly 2 values
506        (Operator::Between, Value::Array(values)) => {
507            if values.len() != 2 {
508                // Return a safe fallback that produces no results (consistent in debug and release)
509                return (
510                    format!("1=0 /* BETWEEN requires 2 values, got {} */", values.len()),
511                    vec![],
512                    idx,
513                );
514            }
515            let sql = format!(
516                "{} BETWEEN {} AND {}",
517                field,
518                dialect.param(idx),
519                dialect.param(idx + 1)
520            );
521            (sql, values.clone(), idx + 2)
522        },
523
524        // Standard comparisons
525        (op, value) => {
526            let op_str = match op {
527                Operator::Eq => "=",
528                Operator::Ne => "!=",
529                Operator::Gt => ">",
530                Operator::Gte => ">=",
531                Operator::Lt => "<",
532                Operator::Lte => "<=",
533                Operator::Like => "LIKE",
534                _ => "=", // fallback for unhandled cases
535            };
536            let sql = format!("{} {} {}", field, op_str, dialect.param(idx));
537            (sql, vec![value.clone()], idx + 1)
538        },
539    }
540}
541
542/// SQL query builder with dialect support.
543#[derive(Debug)]
544pub struct QueryBuilder<D: Dialect> {
545    dialect: D,
546    table: String,
547    fields: Vec<String>,
548    computed: Vec<ComputedField>,
549    aggregates: Vec<Aggregate>,
550    filters: Vec<Filter>,
551    filter_expr: Option<FilterExpr>,
552    group_by: Vec<String>,
553    having: Option<FilterExpr>,
554    sorts: Vec<SortField>,
555    limit: Option<u32>,
556    offset: Option<u32>,
557    cursor: Option<Cursor>,
558    cursor_direction: Option<CursorDirection>,
559}
560
561impl<D: Dialect> QueryBuilder<D> {
562    /// Create a new query builder for the given table.
563    ///
564    /// # Panics
565    ///
566    /// Panics if the table name is not a valid SQL identifier.
567    pub fn new(dialect: D, table: impl Into<String>) -> Self {
568        let table = table.into();
569        assert_valid_sql_identifier(&table, "table");
570        Self {
571            dialect,
572            table,
573            fields: Vec::new(),
574            computed: Vec::new(),
575            aggregates: Vec::new(),
576            filters: Vec::new(),
577            filter_expr: None,
578            group_by: Vec::new(),
579            having: None,
580            sorts: Vec::new(),
581            limit: None,
582            offset: None,
583            cursor: None,
584            cursor_direction: None,
585        }
586    }
587
588    /// Set the fields to SELECT.
589    ///
590    /// # Panics
591    ///
592    /// Panics if any field name is not a valid SQL identifier.
593    pub fn fields(mut self, fields: &[&str]) -> Self {
594        for field in fields {
595            assert_valid_sql_identifier(field, "field");
596        }
597        self.fields = fields.iter().map(|s| (*s).to_string()).collect();
598        self
599    }
600
601    /// Add a computed field to the SELECT clause.
602    ///
603    /// # Example
604    /// ```ignore
605    /// .computed("full_name", "first_name || ' ' || last_name")
606    /// .computed("line_total", "quantity * price")
607    /// ```
608    ///
609    /// # Panics
610    ///
611    /// Panics if alias is not a valid SQL identifier or expression contains
612    /// dangerous patterns (comments, semicolons, SQL keywords).
613    ///
614    /// # Security
615    ///
616    /// **WARNING**: Only use with trusted expressions from code, never with user input.
617    pub fn computed(mut self, alias: impl Into<String>, expression: impl Into<String>) -> Self {
618        let alias = alias.into();
619        let expression = expression.into();
620        assert_valid_sql_identifier(&alias, "computed field alias");
621        assert_valid_sql_expression(&expression, "computed field");
622        self.computed.push(ComputedField::new(alias, expression));
623        self
624    }
625
626    /// Add an aggregation to the SELECT clause.
627    pub fn aggregate(mut self, agg: Aggregate) -> Self {
628        self.aggregates.push(agg);
629        self
630    }
631
632    /// Add a COUNT(*) aggregation.
633    pub fn count(mut self) -> Self {
634        self.aggregates.push(Aggregate::count());
635        self
636    }
637
638    /// Add a SUM(field) aggregation.
639    pub fn sum(mut self, field: impl Into<String>) -> Self {
640        self.aggregates.push(Aggregate::sum(field));
641        self
642    }
643
644    /// Add an AVG(field) aggregation.
645    pub fn avg(mut self, field: impl Into<String>) -> Self {
646        self.aggregates.push(Aggregate::avg(field));
647        self
648    }
649
650    /// Add a MIN(field) aggregation.
651    pub fn min(mut self, field: impl Into<String>) -> Self {
652        self.aggregates.push(Aggregate::min(field));
653        self
654    }
655
656    /// Add a MAX(field) aggregation.
657    pub fn max(mut self, field: impl Into<String>) -> Self {
658        self.aggregates.push(Aggregate::max(field));
659        self
660    }
661
662    /// Add a filter condition.
663    ///
664    /// # Panics
665    ///
666    /// Panics if the field name is not a valid SQL identifier.
667    pub fn filter(mut self, field: impl Into<String>, op: Operator, value: Value) -> Self {
668        let field = field.into();
669        assert_valid_sql_identifier(&field, "filter field");
670        self.filters.push(Filter { field, op, value });
671        self
672    }
673
674    /// Set a compound filter expression (replaces simple filters for WHERE clause).
675    pub fn filter_expr(mut self, expr: FilterExpr) -> Self {
676        self.filter_expr = Some(expr);
677        self
678    }
679
680    /// Add an AND compound filter.
681    pub fn and(mut self, filters: Vec<FilterExpr>) -> Self {
682        self.filter_expr = Some(FilterExpr::Compound(CompoundFilter::and(filters)));
683        self
684    }
685
686    /// Add an OR compound filter.
687    pub fn or(mut self, filters: Vec<FilterExpr>) -> Self {
688        self.filter_expr = Some(FilterExpr::Compound(CompoundFilter::or(filters)));
689        self
690    }
691
692    /// Add GROUP BY fields.
693    ///
694    /// # Panics
695    ///
696    /// Panics if any field name is not a valid SQL identifier.
697    pub fn group_by(mut self, fields: &[&str]) -> Self {
698        for field in fields {
699            assert_valid_sql_identifier(field, "group by field");
700        }
701        self.group_by = fields.iter().map(|s| (*s).to_string()).collect();
702        self
703    }
704
705    /// Add a HAVING clause (for filtering aggregated results).
706    pub fn having(mut self, expr: FilterExpr) -> Self {
707        self.having = Some(expr);
708        self
709    }
710
711    /// Add a sort field.
712    ///
713    /// # Panics
714    ///
715    /// Panics if the field name is not a valid SQL identifier.
716    pub fn sort(mut self, field: impl Into<String>, dir: SortDir) -> Self {
717        let field = field.into();
718        assert_valid_sql_identifier(&field, "sort field");
719        self.sorts.push(SortField::new(field, dir));
720        self
721    }
722
723    /// Add multiple sort fields.
724    pub fn sorts(mut self, sorts: &[SortField]) -> Self {
725        self.sorts.extend(sorts.iter().cloned());
726        self
727    }
728
729    /// Set pagination with page number (1-indexed) and limit.
730    pub fn page(mut self, page: u32, limit: u32) -> Self {
731        self.limit = Some(limit);
732        self.offset = Some(page.saturating_sub(1).saturating_mul(limit));
733        self
734    }
735
736    /// Set explicit limit and offset.
737    pub fn limit_offset(mut self, limit: u32, offset: u32) -> Self {
738        self.limit = Some(limit);
739        self.offset = Some(offset);
740        self
741    }
742
743    /// Set a limit without offset.
744    pub fn limit(mut self, limit: u32) -> Self {
745        self.limit = Some(limit);
746        self
747    }
748
749    /// Paginate after this cursor (forward pagination).
750    ///
751    /// This method accepts flexible input types for great DX:
752    /// - `&Cursor` - when you have an already-parsed cursor
753    /// - `&str` - automatically decodes the base64 cursor
754    /// - `Option<&str>` - perfect for `req.query("after")` results
755    ///
756    /// If the cursor is invalid or None, it's silently ignored.
757    /// This makes it safe to pass `req.query("after")` directly.
758    pub fn after_cursor(mut self, cursor: impl IntoCursor) -> Self {
759        if let Some(c) = cursor.into_cursor() {
760            self.cursor = Some(c);
761            self.cursor_direction = Some(CursorDirection::After);
762        }
763        self
764    }
765
766    /// Paginate before this cursor (backward pagination).
767    ///
768    /// This method accepts flexible input types for great DX:
769    /// - `&Cursor` - when you have an already-parsed cursor
770    /// - `&str` - automatically decodes the base64 cursor
771    /// - `Option<&str>` - perfect for `req.query("before")` results
772    ///
773    /// If the cursor is invalid or None, it's silently ignored.
774    pub fn before_cursor(mut self, cursor: impl IntoCursor) -> Self {
775        if let Some(c) = cursor.into_cursor() {
776            self.cursor = Some(c);
777            self.cursor_direction = Some(CursorDirection::Before);
778        }
779        self
780    }
781
782    /// Build the SQL query and parameters.
783    pub fn build(self) -> QueryResult {
784        let mut sql = String::new();
785        let mut params = Vec::new();
786        let mut param_idx = 1usize;
787
788        // SELECT clause
789        let mut select_parts = Vec::new();
790
791        // Add regular fields
792        if !self.fields.is_empty() {
793            select_parts.extend(self.fields.clone());
794        }
795
796        // Add computed fields
797        for comp in &self.computed {
798            select_parts.push(comp.to_sql());
799        }
800
801        // Add aggregations
802        for agg in &self.aggregates {
803            select_parts.push(agg.to_sql());
804        }
805
806        let select_str = if select_parts.is_empty() {
807            "*".to_string()
808        } else {
809            select_parts.join(", ")
810        };
811
812        sql.push_str(&format!("SELECT {} FROM {}", select_str, self.table));
813
814        // WHERE clause - combine filter_expr, simple filters, and cursor conditions
815        let has_filter_expr = self.filter_expr.is_some();
816        let has_simple_filters = !self.filters.is_empty();
817        let has_cursor = self.cursor.is_some() && self.cursor_direction.is_some();
818
819        if has_filter_expr || has_simple_filters || has_cursor {
820            sql.push_str(" WHERE ");
821            let mut all_conditions = Vec::new();
822
823            // Add filter_expr conditions first
824            if let Some(ref expr) = self.filter_expr {
825                let (condition, new_params, new_idx) =
826                    build_filter_expr_impl(&self.dialect, expr, param_idx);
827                all_conditions.push(condition);
828                params.extend(new_params);
829                param_idx = new_idx;
830            }
831
832            // Add simple filters (from merge or direct .filter() calls)
833            for filter in &self.filters {
834                let (condition, new_params, new_idx) =
835                    build_condition_impl(&self.dialect, filter, param_idx);
836                all_conditions.push(condition);
837                params.extend(new_params);
838                param_idx = new_idx;
839            }
840
841            // Add cursor pagination conditions
842            if let (Some(cursor), Some(direction)) = (&self.cursor, self.cursor_direction) {
843                let (condition, new_params, new_idx) =
844                    self.build_cursor_condition(cursor, direction, param_idx);
845                if !condition.is_empty() {
846                    all_conditions.push(condition);
847                    params.extend(new_params);
848                    param_idx = new_idx;
849                }
850            }
851
852            sql.push_str(&all_conditions.join(" AND "));
853        }
854
855        // GROUP BY clause
856        if !self.group_by.is_empty() {
857            sql.push_str(&format!(" GROUP BY {}", self.group_by.join(", ")));
858        }
859
860        // HAVING clause
861        // Note: _new_idx intentionally unused - ORDER BY/LIMIT/OFFSET don't use parameters
862        if let Some(ref expr) = self.having {
863            let (condition, new_params, _new_idx) =
864                build_filter_expr_impl(&self.dialect, expr, param_idx);
865            sql.push_str(&format!(" HAVING {condition}"));
866            params.extend(new_params);
867        }
868
869        // ORDER BY clause
870        if !self.sorts.is_empty() {
871            sql.push_str(" ORDER BY ");
872            let sort_parts: Vec<String> = self
873                .sorts
874                .iter()
875                .map(|s| {
876                    let dir = match s.dir {
877                        SortDir::Asc => "ASC",
878                        SortDir::Desc => "DESC",
879                    };
880                    format!("{} {}", s.field, dir)
881                })
882                .collect();
883            sql.push_str(&sort_parts.join(", "));
884        }
885
886        // LIMIT/OFFSET clause
887        if let Some(limit) = self.limit {
888            sql.push_str(&format!(" LIMIT {limit}"));
889        }
890        if let Some(offset) = self.offset {
891            sql.push_str(&format!(" OFFSET {offset}"));
892        }
893
894        QueryResult { sql, params }
895    }
896
897    /// Build cursor pagination condition.
898    ///
899    /// Generates keyset-style WHERE conditions based on sort fields and cursor values.
900    /// For single field: `field > $1` (or `<` for DESC)
901    /// For multiple fields: `(a, b) > ($1, $2)` using row comparison.
902    fn build_cursor_condition(
903        &self,
904        cursor: &Cursor,
905        direction: CursorDirection,
906        start_idx: usize,
907    ) -> (String, Vec<Value>, usize) {
908        // If no sorts defined, try using cursor fields directly with ascending order
909        let sort_fields: Vec<SortField> = if self.sorts.is_empty() {
910            cursor
911                .fields
912                .iter()
913                .map(|(name, _)| SortField::new(name.clone(), SortDir::Asc))
914                .collect()
915        } else {
916            self.sorts.clone()
917        };
918
919        if sort_fields.is_empty() {
920            return (String::new(), vec![], start_idx);
921        }
922
923        // Collect values for each sort field from cursor
924        let mut cursor_values: Vec<(&str, &Value)> = Vec::new();
925        for sort in &sort_fields {
926            if let Some((_, value)) = cursor.fields.iter().find(|(name, _)| name == &sort.field) {
927                cursor_values.push((&sort.field, value));
928            }
929        }
930
931        if cursor_values.is_empty() {
932            return (String::new(), vec![], start_idx);
933        }
934
935        let mut idx = start_idx;
936        let mut params = Vec::new();
937
938        if cursor_values.len() == 1 {
939            // Single field: simple comparison
940            let (field, value) = cursor_values[0];
941            let sort = &sort_fields[0];
942            let op = match (direction, sort.dir) {
943                (CursorDirection::After, SortDir::Asc) => ">",
944                (CursorDirection::After, SortDir::Desc) => "<",
945                (CursorDirection::Before, SortDir::Asc) => "<",
946                (CursorDirection::Before, SortDir::Desc) => ">",
947            };
948
949            let sql = format!("{} {} {}", field, op, self.dialect.param(idx));
950            params.push(value.clone());
951            idx += 1;
952
953            (sql, params, idx)
954        } else {
955            // Multiple fields: use row/tuple comparison for efficiency
956            // (a, b, c) > ($1, $2, $3) handles lexicographic ordering correctly
957            let fields: Vec<&str> = cursor_values.iter().map(|(f, _)| *f).collect();
958            let placeholders: Vec<String> = cursor_values
959                .iter()
960                .enumerate()
961                .map(|(i, (_, value))| {
962                    params.push((*value).clone());
963                    self.dialect.param(idx + i)
964                })
965                .collect();
966            idx += cursor_values.len();
967
968            // Determine comparison operator based on primary sort direction
969            let primary_dir = sort_fields[0].dir;
970            let op = match (direction, primary_dir) {
971                (CursorDirection::After, SortDir::Asc) => ">",
972                (CursorDirection::After, SortDir::Desc) => "<",
973                (CursorDirection::Before, SortDir::Asc) => "<",
974                (CursorDirection::Before, SortDir::Desc) => ">",
975            };
976
977            let sql = format!(
978                "({}) {} ({})",
979                fields.join(", "),
980                op,
981                placeholders.join(", ")
982            );
983
984            (sql, params, idx)
985        }
986    }
987}
988
989/// Helper function to create a simple filter expression.
990///
991/// # Panics
992///
993/// Panics if the field name is not a valid SQL identifier.
994pub fn simple(field: impl Into<String>, op: Operator, value: Value) -> FilterExpr {
995    let field = field.into();
996    assert_valid_sql_identifier(&field, "filter field");
997    FilterExpr::Simple(Filter { field, op, value })
998}
999
1000/// Helper function to create an AND compound filter.
1001#[must_use]
1002pub fn and(filters: Vec<FilterExpr>) -> FilterExpr {
1003    FilterExpr::Compound(CompoundFilter::and(filters))
1004}
1005
1006/// Helper function to create an OR compound filter.
1007#[must_use]
1008pub fn or(filters: Vec<FilterExpr>) -> FilterExpr {
1009    FilterExpr::Compound(CompoundFilter::or(filters))
1010}
1011
1012/// Helper function to create a NOT filter.
1013#[must_use]
1014pub fn not(filter: FilterExpr) -> FilterExpr {
1015    FilterExpr::Compound(CompoundFilter::not(filter))
1016}
1017
1018// ═══════════════════════════════════════════════════════════════════════════
1019// INSERT BUILDER
1020// ═══════════════════════════════════════════════════════════════════════════
1021
1022/// Builder for INSERT queries.
1023#[derive(Debug)]
1024pub struct InsertBuilder<D: Dialect> {
1025    dialect: D,
1026    table: String,
1027    columns: Vec<String>,
1028    values: Vec<Vec<Value>>,
1029    returning: Vec<String>,
1030}
1031
1032impl<D: Dialect> InsertBuilder<D> {
1033    /// Create a new insert builder.
1034    ///
1035    /// # Panics
1036    ///
1037    /// Panics if the table name is not a valid SQL identifier.
1038    pub fn new(dialect: D, table: impl Into<String>) -> Self {
1039        let table = table.into();
1040        assert_valid_sql_identifier(&table, "table");
1041        Self {
1042            dialect,
1043            table,
1044            columns: Vec::new(),
1045            values: Vec::new(),
1046            returning: Vec::new(),
1047        }
1048    }
1049
1050    /// Set the columns for insertion.
1051    ///
1052    /// # Panics
1053    ///
1054    /// Panics if any column name is not a valid SQL identifier.
1055    pub fn columns(mut self, columns: &[&str]) -> Self {
1056        for col in columns {
1057            assert_valid_sql_identifier(col, "column");
1058        }
1059        self.columns = columns.iter().map(|s| (*s).to_string()).collect();
1060        self
1061    }
1062
1063    /// Add a row of values.
1064    pub fn values(mut self, values: Vec<Value>) -> Self {
1065        self.values.push(values);
1066        self
1067    }
1068
1069    /// Add multiple rows of values.
1070    pub fn values_many(mut self, rows: Vec<Vec<Value>>) -> Self {
1071        self.values.extend(rows);
1072        self
1073    }
1074
1075    /// Add RETURNING clause (Postgres).
1076    ///
1077    /// # Panics
1078    ///
1079    /// Panics if any column name is not a valid SQL identifier.
1080    pub fn returning(mut self, columns: &[&str]) -> Self {
1081        for col in columns {
1082            assert_valid_sql_identifier(col, "returning column");
1083        }
1084        self.returning = columns.iter().map(|s| (*s).to_string()).collect();
1085        self
1086    }
1087
1088    /// Build the INSERT query.
1089    pub fn build(self) -> QueryResult {
1090        let mut sql = String::new();
1091        let mut params = Vec::new();
1092        let mut param_idx = 1usize;
1093
1094        // INSERT INTO table (columns)
1095        sql.push_str(&format!(
1096            "INSERT INTO {} ({})",
1097            self.table,
1098            self.columns.join(", ")
1099        ));
1100
1101        // VALUES (...)
1102        let mut value_groups = Vec::new();
1103        for row in &self.values {
1104            let placeholders: Vec<String> = row
1105                .iter()
1106                .map(|v| {
1107                    let p = self.dialect.param(param_idx);
1108                    params.push(v.clone());
1109                    param_idx += 1;
1110                    p
1111                })
1112                .collect();
1113            value_groups.push(format!("({})", placeholders.join(", ")));
1114        }
1115        sql.push_str(&format!(" VALUES {}", value_groups.join(", ")));
1116
1117        // RETURNING clause
1118        if !self.returning.is_empty() {
1119            sql.push_str(&format!(" RETURNING {}", self.returning.join(", ")));
1120        }
1121
1122        QueryResult { sql, params }
1123    }
1124}
1125
1126// ═══════════════════════════════════════════════════════════════════════════
1127// UPDATE BUILDER
1128// ═══════════════════════════════════════════════════════════════════════════
1129
1130/// Builder for UPDATE queries.
1131#[derive(Debug)]
1132pub struct UpdateBuilder<D: Dialect> {
1133    dialect: D,
1134    table: String,
1135    sets: Vec<(String, Value)>,
1136    filters: Vec<Filter>,
1137    filter_expr: Option<FilterExpr>,
1138    returning: Vec<String>,
1139}
1140
1141impl<D: Dialect> UpdateBuilder<D> {
1142    /// Create a new update builder.
1143    ///
1144    /// # Panics
1145    ///
1146    /// Panics if the table name is not a valid SQL identifier.
1147    pub fn new(dialect: D, table: impl Into<String>) -> Self {
1148        let table = table.into();
1149        assert_valid_sql_identifier(&table, "table");
1150        Self {
1151            dialect,
1152            table,
1153            sets: Vec::new(),
1154            filters: Vec::new(),
1155            filter_expr: None,
1156            returning: Vec::new(),
1157        }
1158    }
1159
1160    /// Set a column to a value.
1161    ///
1162    /// # Panics
1163    ///
1164    /// Panics if the column name is not a valid SQL identifier.
1165    pub fn set(mut self, column: impl Into<String>, value: Value) -> Self {
1166        let column = column.into();
1167        assert_valid_sql_identifier(&column, "column");
1168        self.sets.push((column, value));
1169        self
1170    }
1171
1172    /// Set multiple columns at once.
1173    ///
1174    /// # Panics
1175    ///
1176    /// Panics if any column name is not a valid SQL identifier.
1177    pub fn set_many(mut self, pairs: Vec<(&str, Value)>) -> Self {
1178        for (col, val) in pairs {
1179            assert_valid_sql_identifier(col, "column");
1180            self.sets.push((col.to_string(), val));
1181        }
1182        self
1183    }
1184
1185    /// Add a simple WHERE filter.
1186    ///
1187    /// # Panics
1188    ///
1189    /// Panics if the field name is not a valid SQL identifier.
1190    pub fn filter(mut self, field: impl Into<String>, op: Operator, value: Value) -> Self {
1191        let field = field.into();
1192        assert_valid_sql_identifier(&field, "filter field");
1193        self.filters.push(Filter { field, op, value });
1194        self
1195    }
1196
1197    /// Set a compound filter expression (AND, OR, NOT).
1198    /// Use with `simple()`, `and()`, `or()`, `not()` helpers.
1199    pub fn filter_expr(mut self, expr: FilterExpr) -> Self {
1200        self.filter_expr = Some(expr);
1201        self
1202    }
1203
1204    /// Add RETURNING clause (Postgres).
1205    ///
1206    /// # Panics
1207    ///
1208    /// Panics if any column name is not a valid SQL identifier.
1209    pub fn returning(mut self, columns: &[&str]) -> Self {
1210        for col in columns {
1211            assert_valid_sql_identifier(col, "returning column");
1212        }
1213        self.returning = columns.iter().map(|s| (*s).to_string()).collect();
1214        self
1215    }
1216
1217    /// Build the UPDATE query.
1218    pub fn build(self) -> QueryResult {
1219        let mut sql = String::new();
1220        let mut params = Vec::new();
1221        let mut param_idx = 1usize;
1222
1223        // UPDATE table SET col = val, ...
1224        sql.push_str(&format!("UPDATE {} SET ", self.table));
1225
1226        let set_parts: Vec<String> = self
1227            .sets
1228            .iter()
1229            .map(|(col, val)| {
1230                let p = self.dialect.param(param_idx);
1231                params.push(val.clone());
1232                param_idx += 1;
1233                format!("{col} = {p}")
1234            })
1235            .collect();
1236        sql.push_str(&set_parts.join(", "));
1237
1238        // WHERE clause - combine filter_expr and simple filters
1239        let has_filter_expr = self.filter_expr.is_some();
1240        let has_simple_filters = !self.filters.is_empty();
1241
1242        if has_filter_expr || has_simple_filters {
1243            sql.push_str(" WHERE ");
1244            let mut all_conditions = Vec::new();
1245
1246            if let Some(ref expr) = self.filter_expr {
1247                let (condition, new_params, new_idx) =
1248                    build_filter_expr_impl(&self.dialect, expr, param_idx);
1249                all_conditions.push(condition);
1250                params.extend(new_params);
1251                param_idx = new_idx;
1252            }
1253
1254            for filter in &self.filters {
1255                let (condition, new_params, new_idx) =
1256                    build_condition_impl(&self.dialect, filter, param_idx);
1257                all_conditions.push(condition);
1258                params.extend(new_params);
1259                param_idx = new_idx;
1260            }
1261
1262            sql.push_str(&all_conditions.join(" AND "));
1263        }
1264
1265        // RETURNING clause
1266        if !self.returning.is_empty() {
1267            sql.push_str(&format!(" RETURNING {}", self.returning.join(", ")));
1268        }
1269
1270        QueryResult { sql, params }
1271    }
1272}
1273
1274// ═══════════════════════════════════════════════════════════════════════════
1275// DELETE BUILDER
1276// ═══════════════════════════════════════════════════════════════════════════
1277
1278/// Builder for DELETE queries.
1279#[derive(Debug)]
1280pub struct DeleteBuilder<D: Dialect> {
1281    dialect: D,
1282    table: String,
1283    filters: Vec<Filter>,
1284    filter_expr: Option<FilterExpr>,
1285    returning: Vec<String>,
1286}
1287
1288impl<D: Dialect> DeleteBuilder<D> {
1289    /// Create a new delete builder.
1290    ///
1291    /// # Panics
1292    ///
1293    /// Panics if the table name is not a valid SQL identifier.
1294    pub fn new(dialect: D, table: impl Into<String>) -> Self {
1295        let table = table.into();
1296        assert_valid_sql_identifier(&table, "table");
1297        Self {
1298            dialect,
1299            table,
1300            filters: Vec::new(),
1301            filter_expr: None,
1302            returning: Vec::new(),
1303        }
1304    }
1305
1306    /// Add a simple WHERE filter.
1307    ///
1308    /// # Panics
1309    ///
1310    /// Panics if the field name is not a valid SQL identifier.
1311    pub fn filter(mut self, field: impl Into<String>, op: Operator, value: Value) -> Self {
1312        let field = field.into();
1313        assert_valid_sql_identifier(&field, "filter field");
1314        self.filters.push(Filter { field, op, value });
1315        self
1316    }
1317
1318    /// Set a compound filter expression (AND, OR, NOT).
1319    /// Use with `simple()`, `and()`, `or()`, `not()` helpers.
1320    pub fn filter_expr(mut self, expr: FilterExpr) -> Self {
1321        self.filter_expr = Some(expr);
1322        self
1323    }
1324
1325    /// Add RETURNING clause (Postgres/SQLite 3.35+).
1326    ///
1327    /// # Panics
1328    ///
1329    /// Panics if any column name is not a valid SQL identifier.
1330    pub fn returning(mut self, columns: &[&str]) -> Self {
1331        for col in columns {
1332            assert_valid_sql_identifier(col, "returning column");
1333        }
1334        self.returning = columns.iter().map(|s| (*s).to_string()).collect();
1335        self
1336    }
1337
1338    /// Build the DELETE query.
1339    pub fn build(self) -> QueryResult {
1340        let mut sql = String::new();
1341        let mut params = Vec::new();
1342        let mut param_idx = 1usize;
1343
1344        // DELETE FROM table
1345        sql.push_str(&format!("DELETE FROM {}", self.table));
1346
1347        // WHERE clause - combine filter_expr and simple filters
1348        let has_filter_expr = self.filter_expr.is_some();
1349        let has_simple_filters = !self.filters.is_empty();
1350
1351        if has_filter_expr || has_simple_filters {
1352            sql.push_str(" WHERE ");
1353            let mut all_conditions = Vec::new();
1354
1355            if let Some(ref expr) = self.filter_expr {
1356                let (condition, new_params, new_idx) =
1357                    build_filter_expr_impl(&self.dialect, expr, param_idx);
1358                all_conditions.push(condition);
1359                params.extend(new_params);
1360                param_idx = new_idx;
1361            }
1362
1363            for filter in &self.filters {
1364                let (condition, new_params, new_idx) =
1365                    build_condition_impl(&self.dialect, filter, param_idx);
1366                all_conditions.push(condition);
1367                params.extend(new_params);
1368                param_idx = new_idx;
1369            }
1370
1371            sql.push_str(&all_conditions.join(" AND "));
1372        }
1373
1374        // RETURNING clause
1375        if !self.returning.is_empty() {
1376            sql.push_str(&format!(" RETURNING {}", self.returning.join(", ")));
1377        }
1378
1379        QueryResult { sql, params }
1380    }
1381}
1382
1383// ═══════════════════════════════════════════════════════════════════════════
1384// CONVENIENCE CONSTRUCTORS
1385// ═══════════════════════════════════════════════════════════════════════════
1386
1387/// Create an INSERT builder for Postgres.
1388pub fn insert(table: impl Into<String>) -> InsertBuilder<Postgres> {
1389    InsertBuilder::new(Postgres, table)
1390}
1391
1392/// Create an UPDATE builder for Postgres.
1393pub fn update(table: impl Into<String>) -> UpdateBuilder<Postgres> {
1394    UpdateBuilder::new(Postgres, table)
1395}
1396
1397/// Create a DELETE builder for Postgres.
1398pub fn delete(table: impl Into<String>) -> DeleteBuilder<Postgres> {
1399    DeleteBuilder::new(Postgres, table)
1400}
1401
1402/// Create an INSERT builder for `SQLite`.
1403pub fn insert_sqlite(table: impl Into<String>) -> InsertBuilder<Sqlite> {
1404    InsertBuilder::new(Sqlite, table)
1405}
1406
1407/// Create an UPDATE builder for `SQLite`.
1408pub fn update_sqlite(table: impl Into<String>) -> UpdateBuilder<Sqlite> {
1409    UpdateBuilder::new(Sqlite, table)
1410}
1411
1412/// Create a DELETE builder for `SQLite`.
1413pub fn delete_sqlite(table: impl Into<String>) -> DeleteBuilder<Sqlite> {
1414    DeleteBuilder::new(Sqlite, table)
1415}
mik_sql/builder.rs

mik_sql/
builder.rs
