oxisql_core/
query.rs

1//! Fluent query builder for constructing SQL strings with positional parameters.
2
3use crate::{ToSqlValue, Value};
4
5/// Sort direction for ORDER BY clauses.
6#[derive(Debug, Default, Clone, Copy, PartialEq, Eq)]
7pub enum SortDirection {
8    /// Ascending order (default).
9    #[default]
10    Asc,
11    /// Descending order.
12    Desc,
13}
14
15impl std::fmt::Display for SortDirection {
16    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
17        match self {
18            SortDirection::Asc => write!(f, "ASC"),
19            SortDirection::Desc => write!(f, "DESC"),
20        }
21    }
22}
23
24/// A fully-built SQL query: the SQL string and its bound parameter values.
25#[derive(Debug, Clone, PartialEq)]
26pub struct BuiltQuery {
27    /// The SQL string with `$1`, `$2`, … placeholders.
28    pub sql: String,
29    /// Parameter values in order.
30    pub params: Vec<Value>,
31}
32
33/// Fluent builder for SELECT queries.
34///
35/// # Example
36///
37/// ```rust
38/// use oxisql_core::query::{SelectBuilder, SortDirection};
39///
40/// let q = SelectBuilder::new()
41///     .columns(&["id", "name", "email"])
42///     .from("users")
43///     .where_eq("active", &true)
44///     .order_by("name", Default::default())
45///     .limit(20)
46///     .offset(40)
47///     .build();
48///
49/// assert_eq!(q.sql, "SELECT id, name, email FROM users WHERE active = $1 ORDER BY name ASC LIMIT 20 OFFSET 40");
50/// ```
51#[derive(Debug, Default, Clone)]
52pub struct SelectBuilder {
53    columns: Vec<String>,
54    table: Option<String>,
55    conditions: Vec<String>,
56    params: Vec<Value>,
57    order_by: Vec<(String, SortDirection)>,
58    limit: Option<usize>,
59    offset: Option<usize>,
60}
61
62impl SelectBuilder {
63    /// Create a new empty SELECT builder.
64    pub fn new() -> Self {
65        Self::default()
66    }
67
68    /// Add columns to SELECT. Call multiple times or pass a slice.
69    pub fn columns(mut self, cols: &[&str]) -> Self {
70        self.columns.extend(cols.iter().map(|s| s.to_string()));
71        self
72    }
73
74    /// Set the FROM table.
75    pub fn from(mut self, table: &str) -> Self {
76        self.table = Some(table.to_string());
77        self
78    }
79
80    /// Add a raw WHERE condition (e.g. `"id > $1"`). Call multiple times to AND them.
81    pub fn where_raw(mut self, condition: &str) -> Self {
82        self.conditions.push(condition.to_string());
83        self
84    }
85
86    /// Add a `WHERE col = $N` condition, binding the value.
87    pub fn where_eq(mut self, col: &str, val: &dyn ToSqlValue) -> Self {
88        let n = self.params.len() + 1;
89        self.conditions.push(format!("{col} = ${n}"));
90        self.params.push(val.to_value());
91        self
92    }
93
94    /// Add an ORDER BY clause.
95    pub fn order_by(mut self, col: &str, dir: SortDirection) -> Self {
96        self.order_by.push((col.to_string(), dir));
97        self
98    }
99
100    /// Set LIMIT.
101    pub fn limit(mut self, n: usize) -> Self {
102        self.limit = Some(n);
103        self
104    }
105
106    /// Set OFFSET.
107    pub fn offset(mut self, n: usize) -> Self {
108        self.offset = Some(n);
109        self
110    }
111
112    /// Build the query into a [`BuiltQuery`].
113    pub fn build(self) -> BuiltQuery {
114        let cols = if self.columns.is_empty() {
115            "*".to_string()
116        } else {
117            self.columns.join(", ")
118        };
119        let table = self.table.as_deref().unwrap_or("unknown");
120        let mut sql = format!("SELECT {cols} FROM {table}");
121        if !self.conditions.is_empty() {
122            sql.push_str(" WHERE ");
123            sql.push_str(&self.conditions.join(" AND "));
124        }
125        if !self.order_by.is_empty() {
126            sql.push_str(" ORDER BY ");
127            sql.push_str(
128                &self
129                    .order_by
130                    .iter()
131                    .map(|(c, d)| format!("{c} {d}"))
132                    .collect::<Vec<_>>()
133                    .join(", "),
134            );
135        }
136        if let Some(l) = self.limit {
137            sql.push_str(&format!(" LIMIT {l}"));
138        }
139        if let Some(o) = self.offset {
140            sql.push_str(&format!(" OFFSET {o}"));
141        }
142        BuiltQuery {
143            sql,
144            params: self.params,
145        }
146    }
147}
148
149/// Fluent builder for INSERT queries.
150///
151/// # Example
152///
153/// ```rust
154/// use oxisql_core::query::InsertBuilder;
155///
156/// let q = InsertBuilder::new()
157///     .into_table("users")
158///     .column("name", &"Alice")
159///     .column("age", &30i64)
160///     .build();
161///
162/// assert_eq!(q.sql, "INSERT INTO users (name, age) VALUES ($1, $2)");
163/// ```
164#[derive(Debug, Default, Clone)]
165pub struct InsertBuilder {
166    table: Option<String>,
167    columns: Vec<String>,
168    params: Vec<Value>,
169}
170
171impl InsertBuilder {
172    /// Create a new empty INSERT builder.
173    pub fn new() -> Self {
174        Self::default()
175    }
176
177    /// Set the target table for the INSERT.
178    pub fn into_table(mut self, table: &str) -> Self {
179        self.table = Some(table.to_string());
180        self
181    }
182
183    /// Add a column-value pair for the INSERT.
184    pub fn column(mut self, col: &str, val: &dyn ToSqlValue) -> Self {
185        self.columns.push(col.to_string());
186        self.params.push(val.to_value());
187        self
188    }
189
190    /// Build the query into a [`BuiltQuery`].
191    pub fn build(self) -> BuiltQuery {
192        let table = self.table.as_deref().unwrap_or("unknown");
193        let n = self.columns.len();
194        let cols = self.columns.join(", ");
195        let placeholders = (1..=n)
196            .map(|i| format!("${i}"))
197            .collect::<Vec<_>>()
198            .join(", ");
199        let sql = format!("INSERT INTO {table} ({cols}) VALUES ({placeholders})");
200        BuiltQuery {
201            sql,
202            params: self.params,
203        }
204    }
205}
206
207/// Fluent builder for UPDATE queries.
208///
209/// # Example
210///
211/// ```rust
212/// use oxisql_core::query::UpdateBuilder;
213///
214/// let q = UpdateBuilder::new()
215///     .table("users")
216///     .set("email", &"new@example.com")
217///     .where_eq("id", &42i64)
218///     .build();
219///
220/// assert_eq!(q.sql, "UPDATE users SET email = $1 WHERE id = $2");
221/// ```
222#[derive(Debug, Default, Clone)]
223pub struct UpdateBuilder {
224    table: Option<String>,
225    sets: Vec<String>,
226    conditions: Vec<String>,
227    params: Vec<Value>,
228}
229
230impl UpdateBuilder {
231    /// Create a new empty UPDATE builder.
232    pub fn new() -> Self {
233        Self::default()
234    }
235
236    /// Set the target table for the UPDATE.
237    pub fn table(mut self, t: &str) -> Self {
238        self.table = Some(t.to_string());
239        self
240    }
241
242    /// Add a `SET col = $N` assignment, binding the value.
243    pub fn set(mut self, col: &str, val: &dyn ToSqlValue) -> Self {
244        let n = self.params.len() + 1;
245        self.sets.push(format!("{col} = ${n}"));
246        self.params.push(val.to_value());
247        self
248    }
249
250    /// Add a raw WHERE condition. Call multiple times to AND them.
251    pub fn where_raw(mut self, cond: &str) -> Self {
252        self.conditions.push(cond.to_string());
253        self
254    }
255
256    /// Add a `WHERE col = $N` condition, binding the value.
257    pub fn where_eq(mut self, col: &str, val: &dyn ToSqlValue) -> Self {
258        let n = self.params.len() + 1;
259        self.conditions.push(format!("{col} = ${n}"));
260        self.params.push(val.to_value());
261        self
262    }
263
264    /// Build the query into a [`BuiltQuery`].
265    pub fn build(self) -> BuiltQuery {
266        let table = self.table.as_deref().unwrap_or("unknown");
267        let sets = self.sets.join(", ");
268        let mut sql = format!("UPDATE {table} SET {sets}");
269        if !self.conditions.is_empty() {
270            sql.push_str(" WHERE ");
271            sql.push_str(&self.conditions.join(" AND "));
272        }
273        BuiltQuery {
274            sql,
275            params: self.params,
276        }
277    }
278}
279
280/// Fluent builder for DELETE queries.
281///
282/// # Example
283///
284/// ```rust
285/// use oxisql_core::query::DeleteBuilder;
286///
287/// let q = DeleteBuilder::new()
288///     .from("users")
289///     .where_eq("id", &99i64)
290///     .build();
291///
292/// assert_eq!(q.sql, "DELETE FROM users WHERE id = $1");
293/// ```
294#[derive(Debug, Default, Clone)]
295pub struct DeleteBuilder {
296    table: Option<String>,
297    conditions: Vec<String>,
298    params: Vec<Value>,
299}
300
301impl DeleteBuilder {
302    /// Create a new empty DELETE builder.
303    pub fn new() -> Self {
304        Self::default()
305    }
306
307    /// Set the target table for the DELETE.
308    pub fn from(mut self, table: &str) -> Self {
309        self.table = Some(table.to_string());
310        self
311    }
312
313    /// Add a raw WHERE condition. Call multiple times to AND them.
314    pub fn where_raw(mut self, cond: &str) -> Self {
315        self.conditions.push(cond.to_string());
316        self
317    }
318
319    /// Add a `WHERE col = $N` condition, binding the value.
320    pub fn where_eq(mut self, col: &str, val: &dyn ToSqlValue) -> Self {
321        let n = self.params.len() + 1;
322        self.conditions.push(format!("{col} = ${n}"));
323        self.params.push(val.to_value());
324        self
325    }
326
327    /// Build the query into a [`BuiltQuery`].
328    pub fn build(self) -> BuiltQuery {
329        let table = self.table.as_deref().unwrap_or("unknown");
330        let mut sql = format!("DELETE FROM {table}");
331        if !self.conditions.is_empty() {
332            sql.push_str(" WHERE ");
333            sql.push_str(&self.conditions.join(" AND "));
334        }
335        BuiltQuery {
336            sql,
337            params: self.params,
338        }
339    }
340}
341
342// ── Tests ────────────────────────────────────────────────────────────────────
343
344#[cfg(test)]
345mod tests {
346    use super::*;
347
348    #[test]
349    fn select_all_from_table() {
350        let q = SelectBuilder::new().from("users").build();
351        assert_eq!(q.sql, "SELECT * FROM users");
352        assert!(q.params.is_empty());
353    }
354
355    #[test]
356    fn select_columns_with_where_and_limit() {
357        let q = SelectBuilder::new()
358            .columns(&["id", "name"])
359            .from("users")
360            .where_eq("active", &true)
361            .limit(10)
362            .build();
363        assert_eq!(
364            q.sql,
365            "SELECT id, name FROM users WHERE active = $1 LIMIT 10"
366        );
367        assert_eq!(q.params, vec![Value::Bool(true)]);
368    }
369
370    #[test]
371    fn select_order_by_multiple() {
372        let q = SelectBuilder::new()
373            .from("items")
374            .order_by("price", SortDirection::Desc)
375            .order_by("name", SortDirection::Asc)
376            .build();
377        assert_eq!(q.sql, "SELECT * FROM items ORDER BY price DESC, name ASC");
378    }
379
380    #[test]
381    fn select_with_offset() {
382        let q = SelectBuilder::new()
383            .from("logs")
384            .limit(20)
385            .offset(40)
386            .build();
387        assert_eq!(q.sql, "SELECT * FROM logs LIMIT 20 OFFSET 40");
388    }
389
390    #[test]
391    fn insert_single_row() {
392        let q = InsertBuilder::new()
393            .into_table("users")
394            .column("name", &"Alice")
395            .column("age", &30i64)
396            .build();
397        assert_eq!(q.sql, "INSERT INTO users (name, age) VALUES ($1, $2)");
398        assert_eq!(q.params.len(), 2);
399    }
400
401    #[test]
402    fn update_with_where() {
403        let q = UpdateBuilder::new()
404            .table("users")
405            .set("email", &"new@example.com")
406            .where_eq("id", &42i64)
407            .build();
408        assert_eq!(q.sql, "UPDATE users SET email = $1 WHERE id = $2");
409        assert_eq!(q.params.len(), 2);
410    }
411
412    #[test]
413    fn delete_with_condition() {
414        let q = DeleteBuilder::new()
415            .from("users")
416            .where_eq("id", &99i64)
417            .build();
418        assert_eq!(q.sql, "DELETE FROM users WHERE id = $1");
419        assert_eq!(q.params[0], Value::I64(99));
420    }
421
422    #[test]
423    fn multiple_where_conditions_joined_with_and() {
424        let q = SelectBuilder::new()
425            .from("orders")
426            .where_eq("status", &"active")
427            .where_eq("user_id", &5i64)
428            .build();
429        assert_eq!(
430            q.sql,
431            "SELECT * FROM orders WHERE status = $1 AND user_id = $2"
432        );
433        assert_eq!(q.params.len(), 2);
434    }
435
436    #[test]
437    fn select_no_table_falls_back_to_unknown() {
438        let q = SelectBuilder::new().columns(&["x"]).build();
439        assert_eq!(q.sql, "SELECT x FROM unknown");
440    }
441
442    #[test]
443    fn update_no_table_falls_back_to_unknown() {
444        let q = UpdateBuilder::new().set("x", &1i64).build();
445        assert_eq!(q.sql, "UPDATE unknown SET x = $1");
446    }
447
448    #[test]
449    fn delete_no_conditions() {
450        let q = DeleteBuilder::new().from("logs").build();
451        assert_eq!(q.sql, "DELETE FROM logs");
452        assert!(q.params.is_empty());
453    }
454
455    #[test]
456    fn sort_direction_default_is_asc() {
457        assert_eq!(SortDirection::default(), SortDirection::Asc);
458    }
459
460    #[test]
461    fn sort_direction_display() {
462        assert_eq!(format!("{}", SortDirection::Asc), "ASC");
463        assert_eq!(format!("{}", SortDirection::Desc), "DESC");
464    }
465
466    #[test]
467    fn where_raw_passthrough() {
468        let q = SelectBuilder::new()
469            .from("events")
470            .where_raw("created_at > NOW()")
471            .build();
472        assert_eq!(q.sql, "SELECT * FROM events WHERE created_at > NOW()");
473        assert!(q.params.is_empty());
474    }
475
476    #[test]
477    fn insert_empty_columns() {
478        let q = InsertBuilder::new().into_table("empty_table").build();
479        assert_eq!(q.sql, "INSERT INTO empty_table () VALUES ()");
480    }
481
482    #[test]
483    fn built_query_clone_and_eq() {
484        let q1 = SelectBuilder::new().from("t").build();
485        let q2 = q1.clone();
486        assert_eq!(q1, q2);
487    }
488}
oxisql_core/query.rs

oxisql_core/
query.rs
