oxide_sql_core/builder/
select.rs

1//! Dynamic SELECT statement builder using the typestate pattern.
2//!
3//! This module provides string-based query building. For compile-time
4//! validated queries using schema traits, use `Select` from `builder::typed`.
5//!
6//! Invalid SQL constructs are caught at compile time.
7
8use std::marker::PhantomData;
9
10use super::expr::ExprBuilder;
11use super::value::SqlValue;
12
13// Typestate markers (zero-sized types)
14
15/// Marker: No columns specified yet.
16pub struct NoColumns;
17/// Marker: Columns have been specified.
18pub struct HasColumns;
19/// Marker: No FROM clause specified yet.
20pub struct NoFrom;
21/// Marker: FROM clause has been specified.
22pub struct HasFrom;
23
24/// A dynamic SELECT statement builder using string-based column names.
25///
26/// For compile-time validated queries, use `Select` from `builder::typed`.
27///
28/// Uses the typestate pattern to ensure that:
29/// - `build()` is only available when both columns and FROM are specified
30/// - `where_clause()` is only available after FROM is specified
31/// - `group_by()`, `having()`, `order_by()` follow SQL semantics
32pub struct SelectDyn<Cols, From> {
33    distinct: bool,
34    columns: Vec<String>,
35    from: Option<String>,
36    joins: Vec<String>,
37    where_clause: Option<ExprBuilder>,
38    group_by: Vec<String>,
39    having: Option<ExprBuilder>,
40    order_by: Vec<String>,
41    limit: Option<u64>,
42    offset: Option<u64>,
43    _state: PhantomData<(Cols, From)>,
44}
45
46impl SelectDyn<NoColumns, NoFrom> {
47    /// Creates a new SELECT builder.
48    #[must_use]
49    pub fn new() -> Self {
50        Self {
51            distinct: false,
52            columns: vec![],
53            from: None,
54            joins: vec![],
55            where_clause: None,
56            group_by: vec![],
57            having: None,
58            order_by: vec![],
59            limit: None,
60            offset: None,
61            _state: PhantomData,
62        }
63    }
64}
65
66impl Default for SelectDyn<NoColumns, NoFrom> {
67    fn default() -> Self {
68        Self::new()
69    }
70}
71
72// Transition: NoColumns -> HasColumns
73impl<From> SelectDyn<NoColumns, From> {
74    /// Specifies the columns to select.
75    #[must_use]
76    pub fn columns(self, cols: &[&str]) -> SelectDyn<HasColumns, From> {
77        SelectDyn {
78            distinct: self.distinct,
79            columns: cols.iter().map(|s| String::from(*s)).collect(),
80            from: self.from,
81            joins: self.joins,
82            where_clause: self.where_clause,
83            group_by: self.group_by,
84            having: self.having,
85            order_by: self.order_by,
86            limit: self.limit,
87            offset: self.offset,
88            _state: PhantomData,
89        }
90    }
91
92    /// Selects all columns (*).
93    #[must_use]
94    pub fn all(self) -> SelectDyn<HasColumns, From> {
95        SelectDyn {
96            distinct: self.distinct,
97            columns: vec![String::from("*")],
98            from: self.from,
99            joins: self.joins,
100            where_clause: self.where_clause,
101            group_by: self.group_by,
102            having: self.having,
103            order_by: self.order_by,
104            limit: self.limit,
105            offset: self.offset,
106            _state: PhantomData,
107        }
108    }
109}
110
111// Transition: NoFrom -> HasFrom
112impl<Cols> SelectDyn<Cols, NoFrom> {
113    /// Specifies the table to select from.
114    #[must_use]
115    pub fn from(self, table: &str) -> SelectDyn<Cols, HasFrom> {
116        SelectDyn {
117            distinct: self.distinct,
118            columns: self.columns,
119            from: Some(String::from(table)),
120            joins: self.joins,
121            where_clause: self.where_clause,
122            group_by: self.group_by,
123            having: self.having,
124            order_by: self.order_by,
125            limit: self.limit,
126            offset: self.offset,
127            _state: PhantomData,
128        }
129    }
130}
131
132// Methods available after FROM
133impl<Cols> SelectDyn<Cols, HasFrom> {
134    /// Adds a WHERE clause.
135    #[must_use]
136    pub fn where_clause(mut self, expr: ExprBuilder) -> Self {
137        self.where_clause = Some(expr);
138        self
139    }
140
141    /// Adds an INNER JOIN.
142    #[must_use]
143    pub fn join(mut self, table: &str, on: &str) -> Self {
144        self.joins.push(format!("INNER JOIN {table} ON {on}"));
145        self
146    }
147
148    /// Adds a LEFT JOIN.
149    #[must_use]
150    pub fn left_join(mut self, table: &str, on: &str) -> Self {
151        self.joins.push(format!("LEFT JOIN {table} ON {on}"));
152        self
153    }
154
155    /// Adds a RIGHT JOIN.
156    #[must_use]
157    pub fn right_join(mut self, table: &str, on: &str) -> Self {
158        self.joins.push(format!("RIGHT JOIN {table} ON {on}"));
159        self
160    }
161
162    /// Adds a CROSS JOIN.
163    #[must_use]
164    pub fn cross_join(mut self, table: &str) -> Self {
165        self.joins.push(format!("CROSS JOIN {table}"));
166        self
167    }
168}
169
170// Methods available with columns
171impl<From> SelectDyn<HasColumns, From> {
172    /// Sets DISTINCT.
173    #[must_use]
174    pub fn distinct(mut self) -> Self {
175        self.distinct = true;
176        self
177    }
178}
179
180// Methods available with FROM (for grouping)
181impl SelectDyn<HasColumns, HasFrom> {
182    /// Adds a GROUP BY clause.
183    #[must_use]
184    pub fn group_by(mut self, cols: &[&str]) -> Self {
185        self.group_by = cols.iter().map(|s| String::from(*s)).collect();
186        self
187    }
188
189    /// Adds a HAVING clause (only valid after GROUP BY).
190    #[must_use]
191    pub fn having(mut self, expr: ExprBuilder) -> Self {
192        self.having = Some(expr);
193        self
194    }
195
196    /// Adds an ORDER BY clause.
197    #[must_use]
198    pub fn order_by(mut self, cols: &[&str]) -> Self {
199        self.order_by = cols.iter().map(|s| String::from(*s)).collect();
200        self
201    }
202
203    /// Adds an ORDER BY DESC clause.
204    #[must_use]
205    pub fn order_by_desc(mut self, cols: &[&str]) -> Self {
206        self.order_by = cols.iter().map(|s| format!("{s} DESC")).collect();
207        self
208    }
209
210    /// Adds a LIMIT clause.
211    #[must_use]
212    pub const fn limit(mut self, n: u64) -> Self {
213        self.limit = Some(n);
214        self
215    }
216
217    /// Adds an OFFSET clause.
218    #[must_use]
219    pub const fn offset(mut self, n: u64) -> Self {
220        self.offset = Some(n);
221        self
222    }
223
224    /// Builds the SELECT statement and returns SQL with parameters.
225    #[must_use]
226    pub fn build(self) -> (String, Vec<SqlValue>) {
227        let mut sql = String::from("SELECT ");
228        let mut params = vec![];
229
230        if self.distinct {
231            sql.push_str("DISTINCT ");
232        }
233
234        sql.push_str(&self.columns.join(", "));
235
236        if let Some(ref table) = self.from {
237            sql.push_str(" FROM ");
238            sql.push_str(table);
239        }
240
241        for join in &self.joins {
242            sql.push(' ');
243            sql.push_str(join);
244        }
245
246        if let Some(ref where_expr) = self.where_clause {
247            sql.push_str(" WHERE ");
248            sql.push_str(where_expr.sql());
249            params.extend(where_expr.params().iter().cloned());
250        }
251
252        if !self.group_by.is_empty() {
253            sql.push_str(" GROUP BY ");
254            sql.push_str(&self.group_by.join(", "));
255        }
256
257        if let Some(ref having_expr) = self.having {
258            sql.push_str(" HAVING ");
259            sql.push_str(having_expr.sql());
260            params.extend(having_expr.params().iter().cloned());
261        }
262
263        if !self.order_by.is_empty() {
264            sql.push_str(" ORDER BY ");
265            sql.push_str(&self.order_by.join(", "));
266        }
267
268        if let Some(n) = self.limit {
269            sql.push_str(&format!(" LIMIT {n}"));
270        }
271
272        if let Some(n) = self.offset {
273            sql.push_str(&format!(" OFFSET {n}"));
274        }
275
276        (sql, params)
277    }
278
279    /// Builds the SELECT statement and returns only the SQL string.
280    ///
281    /// **Warning**: Parameters are inlined using proper escaping.
282    /// Prefer `build()` for parameterized queries.
283    #[must_use]
284    pub fn build_sql(self) -> String {
285        let (sql, _params) = self.build();
286        sql
287    }
288}
289
290#[cfg(test)]
291mod tests {
292    use super::*;
293    use crate::builder::dyn_col;
294
295    #[test]
296    fn test_simple_select() {
297        let (sql, params) = SelectDyn::new()
298            .columns(&["id", "name"])
299            .from("users")
300            .build();
301
302        assert_eq!(sql, "SELECT id, name FROM users");
303        assert!(params.is_empty());
304    }
305
306    #[test]
307    fn test_select_all() {
308        let (sql, _) = SelectDyn::new().all().from("users").build();
309        assert_eq!(sql, "SELECT * FROM users");
310    }
311
312    #[test]
313    fn test_select_distinct() {
314        let (sql, _) = SelectDyn::new()
315            .columns(&["status"])
316            .distinct()
317            .from("orders")
318            .build();
319
320        assert_eq!(sql, "SELECT DISTINCT status FROM orders");
321    }
322
323    #[test]
324    fn test_select_with_where() {
325        let (sql, params) = SelectDyn::new()
326            .columns(&["id", "name"])
327            .from("users")
328            .where_clause(dyn_col("active").eq(true))
329            .build();
330
331        assert_eq!(sql, "SELECT id, name FROM users WHERE active = ?");
332        assert_eq!(params.len(), 1);
333    }
334
335    #[test]
336    fn test_select_with_join() {
337        let (sql, _) = SelectDyn::new()
338            .columns(&["u.id", "o.amount"])
339            .from("users u")
340            .join("orders o", "u.id = o.user_id")
341            .build();
342
343        assert_eq!(
344            sql,
345            "SELECT u.id, o.amount FROM users u INNER JOIN orders o ON u.id = o.user_id"
346        );
347    }
348
349    #[test]
350    fn test_select_with_group_by() {
351        let (sql, _) = SelectDyn::new()
352            .columns(&["status", "COUNT(*)"])
353            .from("orders")
354            .group_by(&["status"])
355            .build();
356
357        assert_eq!(sql, "SELECT status, COUNT(*) FROM orders GROUP BY status");
358    }
359
360    #[test]
361    fn test_select_with_order_by() {
362        let (sql, _) = SelectDyn::new()
363            .columns(&["id", "name"])
364            .from("users")
365            .order_by(&["name"])
366            .build();
367
368        assert_eq!(sql, "SELECT id, name FROM users ORDER BY name");
369    }
370
371    #[test]
372    fn test_select_with_limit_offset() {
373        let (sql, _) = SelectDyn::new()
374            .columns(&["id"])
375            .from("users")
376            .limit(10)
377            .offset(20)
378            .build();
379
380        assert_eq!(sql, "SELECT id FROM users LIMIT 10 OFFSET 20");
381    }
382
383    #[test]
384    fn test_complex_select() {
385        let (sql, params) = SelectDyn::new()
386            .columns(&["u.id", "u.name", "COUNT(o.id) as order_count"])
387            .from("users u")
388            .left_join("orders o", "u.id = o.user_id")
389            .where_clause(
390                dyn_col("u.active")
391                    .eq(true)
392                    .and(dyn_col("o.status").not_eq("cancelled")),
393            )
394            .group_by(&["u.id", "u.name"])
395            .order_by_desc(&["order_count"])
396            .limit(10)
397            .build();
398
399        assert!(sql.contains("SELECT u.id, u.name, COUNT(o.id) as order_count"));
400        assert!(sql.contains("FROM users u"));
401        assert!(sql.contains("LEFT JOIN orders o ON u.id = o.user_id"));
402        assert!(sql.contains("WHERE u.active = ? AND o.status != ?"));
403        assert!(sql.contains("GROUP BY u.id, u.name"));
404        assert!(sql.contains("ORDER BY order_count DESC"));
405        assert!(sql.contains("LIMIT 10"));
406        assert_eq!(params.len(), 2);
407    }
408
409    // Compile-time tests (these would fail to compile if uncommented)
410
411    // This would fail to compile: SELECT without FROM
412    // #[test]
413    // fn test_select_without_from_fails() {
414    //     let _ = SelectDyn::new()
415    //         .columns(&["id"])
416    //         .build();  // Error: method `build` not found
417    // }
418
419    // This would fail to compile: WHERE without FROM
420    // #[test]
421    // fn test_where_without_from_fails() {
422    //     let _ = SelectDyn::new()
423    //         .columns(&["id"])
424    //         .where_clause(dyn_col("id").eq(1));  // Error: no method `where_clause`
425    // }
426
427    // This would fail to compile: SELECT without columns
428    // #[test]
429    // fn test_select_without_columns_fails() {
430    //     let _ = SelectDyn::new()
431    //         .from("users")
432    //         .build();  // Error: method `build` not found
433    // }
434}
oxide_sql_core/builder/select.rs

oxide_sql_core/builder/
select.rs
