oxisql_parse/
builder.rs

1//! Fluent SQL query builder.
2//!
3//! Provides [`QueryBuilder`] for constructing SELECT, INSERT, UPDATE, and
4//! DELETE statements programmatically, with optional verification via
5//! [`sqlparser`].
6
7use crate::parse;
8use sqlparser::ast::Statement;
9
10// ── Internal clause types ────────────────────────────────────────────────────
11
12struct JoinClause {
13    table: String,
14    on: String,
15    join_type: &'static str, // "INNER", "LEFT", "RIGHT", "CROSS"
16}
17
18struct OrderByClause {
19    expr: String,
20    ascending: bool,
21}
22
23// ── QueryBuilder ─────────────────────────────────────────────────────────────
24
25/// Fluent SQL query builder.
26///
27/// # Example
28///
29/// ```rust
30/// use oxisql_parse::QueryBuilder;
31///
32/// let sql = QueryBuilder::select(&["id", "name", "email"])
33///     .from("users")
34///     .join("orders", "users.id = orders.user_id")
35///     .where_clause("users.active = TRUE")
36///     .where_clause("orders.status = 'pending'")
37///     .order_by("users.name", true)
38///     .limit(10)
39///     .offset(20)
40///     .build()
41///     .expect("valid query");
42/// assert!(sql.contains("SELECT id, name, email"));
43/// ```
44pub struct QueryBuilder {
45    select_cols: Vec<String>,
46    from_table: Option<String>,
47    joins: Vec<JoinClause>,
48    wheres: Vec<String>,
49    group_by: Vec<String>,
50    having: Option<String>,
51    order_bys: Vec<OrderByClause>,
52    limit_val: Option<u64>,
53    offset_val: Option<u64>,
54    distinct: bool,
55}
56
57impl QueryBuilder {
58    // ── Constructors ─────────────────────────────────────────────────────────
59
60    /// Start a SELECT query with the given columns.
61    pub fn select(columns: &[&str]) -> Self {
62        Self {
63            select_cols: columns.iter().map(|c| c.to_string()).collect(),
64            from_table: None,
65            joins: Vec::new(),
66            wheres: Vec::new(),
67            group_by: Vec::new(),
68            having: None,
69            order_bys: Vec::new(),
70            limit_val: None,
71            offset_val: None,
72            distinct: false,
73        }
74    }
75
76    /// Start a `SELECT *` query.
77    pub fn select_all() -> Self {
78        Self::select(&["*"])
79    }
80
81    // ── Modifiers ────────────────────────────────────────────────────────────
82
83    /// Add `DISTINCT` to the SELECT clause.
84    pub fn distinct(mut self) -> Self {
85        self.distinct = true;
86        self
87    }
88
89    /// Set the `FROM` table.
90    pub fn from(mut self, table: &str) -> Self {
91        self.from_table = Some(table.to_string());
92        self
93    }
94
95    /// Add an `INNER JOIN`.
96    pub fn join(mut self, table: &str, on: &str) -> Self {
97        self.joins.push(JoinClause {
98            table: table.to_string(),
99            on: on.to_string(),
100            join_type: "INNER",
101        });
102        self
103    }
104
105    /// Add a `LEFT JOIN`.
106    pub fn left_join(mut self, table: &str, on: &str) -> Self {
107        self.joins.push(JoinClause {
108            table: table.to_string(),
109            on: on.to_string(),
110            join_type: "LEFT",
111        });
112        self
113    }
114
115    /// Add a `RIGHT JOIN`.
116    pub fn right_join(mut self, table: &str, on: &str) -> Self {
117        self.joins.push(JoinClause {
118            table: table.to_string(),
119            on: on.to_string(),
120            join_type: "RIGHT",
121        });
122        self
123    }
124
125    /// Add a `WHERE` condition.  Multiple calls are combined with `AND`.
126    pub fn where_clause(mut self, condition: &str) -> Self {
127        self.wheres.push(condition.to_string());
128        self
129    }
130
131    /// Add a `GROUP BY` expression.
132    pub fn group_by(mut self, expr: &str) -> Self {
133        self.group_by.push(expr.to_string());
134        self
135    }
136
137    /// Set the `HAVING` clause.
138    pub fn having(mut self, condition: &str) -> Self {
139        self.having = Some(condition.to_string());
140        self
141    }
142
143    /// Add an `ORDER BY` expression.  `ascending = true` → `ASC`, `false` → `DESC`.
144    pub fn order_by(mut self, expr: &str, ascending: bool) -> Self {
145        self.order_bys.push(OrderByClause {
146            expr: expr.to_string(),
147            ascending,
148        });
149        self
150    }
151
152    /// Set `LIMIT`.
153    pub fn limit(mut self, n: u64) -> Self {
154        self.limit_val = Some(n);
155        self
156    }
157
158    /// Set `OFFSET`.
159    pub fn offset(mut self, n: u64) -> Self {
160        self.offset_val = Some(n);
161        self
162    }
163
164    // ── Build ────────────────────────────────────────────────────────────────
165
166    /// Build the final SQL string.
167    ///
168    /// Returns `Err` if no `FROM` table has been set.
169    pub fn build(self) -> Result<String, String> {
170        let from = self
171            .from_table
172            .ok_or_else(|| "QueryBuilder: no FROM table set".to_string())?;
173
174        let mut parts: Vec<String> = Vec::new();
175
176        // SELECT [DISTINCT] cols
177        let cols = if self.select_cols.is_empty() {
178            "*".to_string()
179        } else {
180            self.select_cols.join(", ")
181        };
182        if self.distinct {
183            parts.push(format!("SELECT DISTINCT {cols}"));
184        } else {
185            parts.push(format!("SELECT {cols}"));
186        }
187
188        // FROM
189        parts.push(format!("FROM {from}"));
190
191        // JOINs
192        for j in &self.joins {
193            parts.push(format!("{} JOIN {} ON {}", j.join_type, j.table, j.on));
194        }
195
196        // WHERE
197        if !self.wheres.is_empty() {
198            parts.push(format!("WHERE {}", self.wheres.join(" AND ")));
199        }
200
201        // GROUP BY
202        if !self.group_by.is_empty() {
203            parts.push(format!("GROUP BY {}", self.group_by.join(", ")));
204        }
205
206        // HAVING
207        if let Some(h) = &self.having {
208            parts.push(format!("HAVING {h}"));
209        }
210
211        // ORDER BY
212        if !self.order_bys.is_empty() {
213            let order_exprs: Vec<String> = self
214                .order_bys
215                .iter()
216                .map(|o| {
217                    let dir = if o.ascending { "ASC" } else { "DESC" };
218                    format!("{} {dir}", o.expr)
219                })
220                .collect();
221            parts.push(format!("ORDER BY {}", order_exprs.join(", ")));
222        }
223
224        // LIMIT
225        if let Some(n) = self.limit_val {
226            parts.push(format!("LIMIT {n}"));
227        }
228
229        // OFFSET
230        if let Some(n) = self.offset_val {
231            parts.push(format!("OFFSET {n}"));
232        }
233
234        Ok(parts.join("\n"))
235    }
236
237    /// Build and immediately parse the SQL to verify it is syntactically valid.
238    ///
239    /// Returns `Err` if building fails or if the resulting SQL cannot be parsed.
240    pub fn build_and_parse(self) -> Result<Vec<Statement>, String> {
241        let sql = self.build()?;
242        parse(&sql).map_err(|e| e.to_string())
243    }
244
245    /// Build the SQL string without consuming `self`.
246    ///
247    /// Internally clones the builder fields and delegates to [`Self::build`].
248    /// Prefer [`build`][Self::build] when the builder is no longer needed.
249    pub fn build_ref(&self) -> Result<String, String> {
250        let cloned = QueryBuilder {
251            select_cols: self.select_cols.clone(),
252            from_table: self.from_table.clone(),
253            joins: self
254                .joins
255                .iter()
256                .map(|j| JoinClause {
257                    table: j.table.clone(),
258                    on: j.on.clone(),
259                    join_type: j.join_type,
260                })
261                .collect(),
262            wheres: self.wheres.clone(),
263            group_by: self.group_by.clone(),
264            having: self.having.clone(),
265            order_bys: self
266                .order_bys
267                .iter()
268                .map(|o| OrderByClause {
269                    expr: o.expr.clone(),
270                    ascending: o.ascending,
271                })
272                .collect(),
273            limit_val: self.limit_val,
274            offset_val: self.offset_val,
275            distinct: self.distinct,
276        };
277        cloned.build()
278    }
279
280    // ── Static DML helpers ───────────────────────────────────────────────────
281
282    /// Build an `INSERT INTO … VALUES (…)` statement.
283    ///
284    /// # Example
285    ///
286    /// ```rust
287    /// use oxisql_parse::QueryBuilder;
288    ///
289    /// let sql = QueryBuilder::insert("users", &["id", "name"], &["1", "'Alice'"]);
290    /// assert_eq!(sql, "INSERT INTO users (id, name) VALUES (1, 'Alice')");
291    /// ```
292    pub fn insert(table: &str, columns: &[&str], values: &[&str]) -> String {
293        let cols = columns.join(", ");
294        let vals = values.join(", ");
295        format!("INSERT INTO {table} ({cols}) VALUES ({vals})")
296    }
297
298    /// Build an `UPDATE … SET … [WHERE …]` statement.
299    ///
300    /// # Example
301    ///
302    /// ```rust
303    /// use oxisql_parse::QueryBuilder;
304    ///
305    /// let sql = QueryBuilder::update(
306    ///     "users",
307    ///     &[("name", "'Bob'"), ("active", "FALSE")],
308    ///     Some("id = 42"),
309    /// );
310    /// assert!(sql.starts_with("UPDATE users SET"));
311    /// assert!(sql.contains("WHERE id = 42"));
312    /// ```
313    pub fn update(table: &str, sets: &[(&str, &str)], where_clause: Option<&str>) -> String {
314        let set_exprs: Vec<String> = sets.iter().map(|(k, v)| format!("{k} = {v}")).collect();
315        let mut sql = format!("UPDATE {table} SET {}", set_exprs.join(", "));
316        if let Some(w) = where_clause {
317            sql.push_str(&format!(" WHERE {w}"));
318        }
319        sql
320    }
321
322    /// Build a `DELETE FROM … [WHERE …]` statement.
323    ///
324    /// # Example
325    ///
326    /// ```rust
327    /// use oxisql_parse::QueryBuilder;
328    ///
329    /// let sql = QueryBuilder::delete("users", Some("id = 99"));
330    /// assert_eq!(sql, "DELETE FROM users WHERE id = 99");
331    /// ```
332    pub fn delete(table: &str, where_clause: Option<&str>) -> String {
333        let mut sql = format!("DELETE FROM {table}");
334        if let Some(w) = where_clause {
335            sql.push_str(&format!(" WHERE {w}"));
336        }
337        sql
338    }
339}
340
341// ── Tests ────────────────────────────────────────────────────────────────────
342
343#[cfg(test)]
344mod tests {
345    use super::QueryBuilder;
346
347    #[test]
348    fn test_simple_select() {
349        let sql = QueryBuilder::select(&["id", "name"])
350            .from("users")
351            .build()
352            .expect("build");
353        assert_eq!(sql, "SELECT id, name\nFROM users");
354    }
355
356    #[test]
357    fn test_select_with_where() {
358        let sql = QueryBuilder::select(&["id", "name"])
359            .from("users")
360            .where_clause("active = TRUE")
361            .build()
362            .expect("build");
363        assert!(sql.contains("WHERE active = TRUE"), "got: {sql}");
364    }
365
366    #[test]
367    fn test_multiple_where_anded() {
368        let sql = QueryBuilder::select(&["*"])
369            .from("t")
370            .where_clause("a = 1")
371            .where_clause("b = 2")
372            .build()
373            .expect("build");
374        assert!(sql.contains("WHERE a = 1 AND b = 2"), "got: {sql}");
375    }
376
377    #[test]
378    fn test_join() {
379        let sql = QueryBuilder::select(&["u.id", "o.id"])
380            .from("users u")
381            .join("orders o", "u.id = o.user_id")
382            .build()
383            .expect("build");
384        assert!(
385            sql.contains("INNER JOIN orders o ON u.id = o.user_id"),
386            "got: {sql}"
387        );
388    }
389
390    #[test]
391    fn test_left_join() {
392        let sql = QueryBuilder::select(&["u.id"])
393            .from("users u")
394            .left_join("orders o", "u.id = o.user_id")
395            .build()
396            .expect("build");
397        assert!(
398            sql.contains("LEFT JOIN orders o ON u.id = o.user_id"),
399            "got: {sql}"
400        );
401    }
402
403    #[test]
404    fn test_order_limit_offset() {
405        let sql = QueryBuilder::select(&["id"])
406            .from("users")
407            .order_by("name", true)
408            .order_by("id", false)
409            .limit(10)
410            .offset(20)
411            .build()
412            .expect("build");
413        assert!(sql.contains("ORDER BY name ASC, id DESC"), "got: {sql}");
414        assert!(sql.contains("LIMIT 10"), "got: {sql}");
415        assert!(sql.contains("OFFSET 20"), "got: {sql}");
416    }
417
418    #[test]
419    fn test_group_by_having() {
420        let sql = QueryBuilder::select(&["dept", "COUNT(*)"])
421            .from("employees")
422            .group_by("dept")
423            .having("COUNT(*) > 5")
424            .build()
425            .expect("build");
426        assert!(sql.contains("GROUP BY dept"), "got: {sql}");
427        assert!(sql.contains("HAVING COUNT(*) > 5"), "got: {sql}");
428    }
429
430    #[test]
431    fn test_distinct() {
432        let sql = QueryBuilder::select(&["email"])
433            .distinct()
434            .from("users")
435            .build()
436            .expect("build");
437        assert!(sql.starts_with("SELECT DISTINCT email"), "got: {sql}");
438    }
439
440    #[test]
441    fn test_insert() {
442        let sql = QueryBuilder::insert("users", &["id", "name"], &["1", "'Alice'"]);
443        assert_eq!(sql, "INSERT INTO users (id, name) VALUES (1, 'Alice')");
444    }
445
446    #[test]
447    fn test_update() {
448        let sql = QueryBuilder::update(
449            "users",
450            &[("name", "'Bob'"), ("active", "FALSE")],
451            Some("id = 42"),
452        );
453        assert!(sql.starts_with("UPDATE users SET"), "got: {sql}");
454        assert!(sql.contains("name = 'Bob'"), "got: {sql}");
455        assert!(sql.contains("active = FALSE"), "got: {sql}");
456        assert!(sql.contains("WHERE id = 42"), "got: {sql}");
457    }
458
459    #[test]
460    fn test_delete_with_where() {
461        let sql = QueryBuilder::delete("users", Some("id = 99"));
462        assert_eq!(sql, "DELETE FROM users WHERE id = 99");
463    }
464
465    #[test]
466    fn test_build_and_parse() {
467        let result = QueryBuilder::select(&["u.id", "u.name", "o.total"])
468            .from("users u")
469            .join("orders o", "u.id = o.user_id")
470            .where_clause("u.active = TRUE")
471            .order_by("u.name", true)
472            .limit(50)
473            .build_and_parse();
474        assert!(result.is_ok(), "build_and_parse failed: {:?}", result.err());
475        let stmts = result.expect("stmts");
476        assert_eq!(stmts.len(), 1);
477    }
478
479    #[test]
480    fn test_build_no_from_errors() {
481        let result = QueryBuilder::select_all().build();
482        assert!(result.is_err(), "expected Err when no FROM is set");
483    }
484
485    #[test]
486    fn test_select_all() {
487        let sql = QueryBuilder::select_all()
488            .from("products")
489            .build()
490            .expect("build");
491        assert!(sql.starts_with("SELECT *"), "got: {sql}");
492    }
493
494    #[test]
495    fn test_build_ref_matches_build() {
496        let qb = QueryBuilder::select(&["id", "name"])
497            .from("users")
498            .where_clause("active = TRUE")
499            .limit(10);
500        let via_ref = qb.build_ref().expect("build_ref");
501        let via_owned = QueryBuilder::select(&["id", "name"])
502            .from("users")
503            .where_clause("active = TRUE")
504            .limit(10)
505            .build()
506            .expect("build");
507        assert_eq!(via_ref, via_owned);
508    }
509}
oxisql_parse/builder.rs

oxisql_parse/
builder.rs
