oxify_storage/
query_builder.rs

1//! SQL Query Builder Utilities
2//!
3//! Provides a type-safe, fluent API for constructing dynamic SQL queries.
4//!
5//! ## Overview
6//!
7//! The query builder helps construct complex SQL queries programmatically while maintaining
8//! type safety and preventing SQL injection through parameter binding.
9//!
10//! ## Features
11//!
12//! - **Fluent API**: Chain methods to build queries naturally
13//! - **Type Safety**: Compile-time checks for query structure
14//! - **Parameter Binding**: Automatic SQL injection protection
15//! - **Dynamic Filters**: Conditionally add WHERE clauses
16//! - **Sorting**: Type-safe ORDER BY clauses
17//! - **Pagination**: LIMIT and OFFSET support
18//! - **Aggregations**: COUNT, SUM, AVG, MIN, MAX support
19//!
20//! ## Example
21//!
22//! ```rust
23//! use oxify_storage::query_builder::{QueryBuilder, Condition, SortOrder};
24//!
25//! let mut builder = QueryBuilder::new("workflows")
26//!     .select(&["id", "name", "created_at"])
27//!     .where_condition(Condition::Eq("user_id".to_string()))
28//!     .where_condition(Condition::IsNotNull("deleted_at".to_string()))
29//!     .order_by("created_at", SortOrder::Desc)
30//!     .limit(20)
31//!     .offset(0);
32//!
33//! let (sql, param_count) = builder.build();
34//! // SELECT id, name, created_at FROM workflows
35//! // WHERE user_id = $1 AND deleted_at IS NOT NULL
36//! // ORDER BY created_at DESC LIMIT 20 OFFSET 0
37//! ```
38
39use std::fmt;
40
41/// Sort order for ORDER BY clauses
42#[derive(Debug, Clone, Copy, PartialEq, Eq)]
43pub enum SortOrder {
44    /// Ascending order (smallest to largest)
45    Asc,
46    /// Descending order (largest to smallest)
47    Desc,
48}
49
50impl fmt::Display for SortOrder {
51    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
52        match self {
53            SortOrder::Asc => write!(f, "ASC"),
54            SortOrder::Desc => write!(f, "DESC"),
55        }
56    }
57}
58
59/// SQL condition types for WHERE clauses
60#[derive(Debug, Clone)]
61pub enum Condition {
62    /// column = $n
63    Eq(String),
64    /// column != $n
65    NotEq(String),
66    /// column > $n
67    Gt(String),
68    /// column >= $n
69    Gte(String),
70    /// column < $n
71    Lt(String),
72    /// column <= $n
73    Lte(String),
74    /// column LIKE $n
75    Like(String),
76    /// column ILIKE $n (case-insensitive)
77    ILike(String),
78    /// column IN ($n, $n+1, ...)
79    In(String, usize),
80    /// column NOT IN ($n, $n+1, ...)
81    NotIn(String, usize),
82    /// column BETWEEN $n AND $n+1
83    Between(String),
84    /// column IS NULL
85    IsNull(String),
86    /// column IS NOT NULL
87    IsNotNull(String),
88    /// Custom SQL condition (use with caution, ensure no injection)
89    Raw(String),
90}
91
92impl Condition {
93    /// Get the number of parameters this condition requires
94    pub fn param_count(&self) -> usize {
95        match self {
96            Condition::Eq(_)
97            | Condition::NotEq(_)
98            | Condition::Gt(_)
99            | Condition::Gte(_)
100            | Condition::Lt(_)
101            | Condition::Lte(_)
102            | Condition::Like(_)
103            | Condition::ILike(_) => 1,
104            Condition::In(_, count) | Condition::NotIn(_, count) => *count,
105            Condition::Between(_) => 2,
106            Condition::IsNull(_) | Condition::IsNotNull(_) | Condition::Raw(_) => 0,
107        }
108    }
109
110    /// Build the SQL fragment for this condition
111    pub fn to_sql(&self, param_start: usize) -> String {
112        match self {
113            Condition::Eq(col) => format!("{col} = ${param_start}"),
114            Condition::NotEq(col) => format!("{col} != ${param_start}"),
115            Condition::Gt(col) => format!("{col} > ${param_start}"),
116            Condition::Gte(col) => format!("{col} >= ${param_start}"),
117            Condition::Lt(col) => format!("{col} < ${param_start}"),
118            Condition::Lte(col) => format!("{col} <= ${param_start}"),
119            Condition::Like(col) => format!("{col} LIKE ${param_start}"),
120            Condition::ILike(col) => format!("{col} ILIKE ${param_start}"),
121            Condition::In(col, count) => {
122                let placeholders: Vec<String> = (param_start..param_start + count)
123                    .map(|i| format!("${i}"))
124                    .collect();
125                format!("{col} IN ({})", placeholders.join(", "))
126            }
127            Condition::NotIn(col, count) => {
128                let placeholders: Vec<String> = (param_start..param_start + count)
129                    .map(|i| format!("${i}"))
130                    .collect();
131                format!("{col} NOT IN ({})", placeholders.join(", "))
132            }
133            Condition::Between(col) => {
134                format!("{col} BETWEEN ${param_start} AND ${}", param_start + 1)
135            }
136            Condition::IsNull(col) => format!("{col} IS NULL"),
137            Condition::IsNotNull(col) => format!("{col} IS NOT NULL"),
138            Condition::Raw(sql) => sql.clone(),
139        }
140    }
141}
142
143/// JOIN types for table joins
144#[derive(Debug, Clone)]
145pub enum JoinType {
146    /// INNER JOIN
147    Inner,
148    /// LEFT JOIN
149    Left,
150    /// RIGHT JOIN
151    Right,
152    /// FULL OUTER JOIN
153    Full,
154}
155
156impl fmt::Display for JoinType {
157    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
158        match self {
159            JoinType::Inner => write!(f, "INNER JOIN"),
160            JoinType::Left => write!(f, "LEFT JOIN"),
161            JoinType::Right => write!(f, "RIGHT JOIN"),
162            JoinType::Full => write!(f, "FULL OUTER JOIN"),
163        }
164    }
165}
166
167/// JOIN clause definition
168#[derive(Debug, Clone)]
169pub struct Join {
170    join_type: JoinType,
171    table: String,
172    on_condition: String,
173}
174
175/// SQL query builder for SELECT statements
176#[derive(Debug, Clone)]
177pub struct QueryBuilder {
178    table: String,
179    select_columns: Vec<String>,
180    conditions: Vec<Condition>,
181    joins: Vec<Join>,
182    order_by: Vec<(String, SortOrder)>,
183    group_by: Vec<String>,
184    having: Vec<Condition>,
185    limit_value: Option<i64>,
186    offset_value: Option<i64>,
187    distinct: bool,
188}
189
190impl QueryBuilder {
191    /// Create a new query builder for the specified table
192    ///
193    /// # Example
194    ///
195    /// ```rust
196    /// use oxify_storage::query_builder::QueryBuilder;
197    ///
198    /// let builder = QueryBuilder::new("users");
199    /// ```
200    pub fn new(table: &str) -> Self {
201        Self {
202            table: table.to_string(),
203            select_columns: vec!["*".to_string()],
204            conditions: Vec::new(),
205            joins: Vec::new(),
206            order_by: Vec::new(),
207            group_by: Vec::new(),
208            having: Vec::new(),
209            limit_value: None,
210            offset_value: None,
211            distinct: false,
212        }
213    }
214
215    /// Specify columns to select
216    ///
217    /// # Example
218    ///
219    /// ```rust
220    /// use oxify_storage::query_builder::QueryBuilder;
221    ///
222    /// let builder = QueryBuilder::new("users")
223    ///     .select(&["id", "name", "email"]);
224    /// ```
225    pub fn select(mut self, columns: &[&str]) -> Self {
226        self.select_columns = columns.iter().map(|s| s.to_string()).collect();
227        self
228    }
229
230    /// Add SELECT DISTINCT
231    pub fn distinct(mut self) -> Self {
232        self.distinct = true;
233        self
234    }
235
236    /// Add a WHERE condition
237    ///
238    /// # Example
239    ///
240    /// ```rust
241    /// use oxify_storage::query_builder::{QueryBuilder, Condition};
242    ///
243    /// let builder = QueryBuilder::new("users")
244    ///     .where_condition(Condition::Eq("status".to_string()));
245    /// ```
246    pub fn where_condition(mut self, condition: Condition) -> Self {
247        self.conditions.push(condition);
248        self
249    }
250
251    /// Add a WHERE condition only if the predicate is true
252    ///
253    /// This is useful for optional filters
254    pub fn where_if(self, predicate: bool, condition: Condition) -> Self {
255        if predicate {
256            self.where_condition(condition)
257        } else {
258            self
259        }
260    }
261
262    /// Add a JOIN clause
263    pub fn join(mut self, join_type: JoinType, table: &str, on_condition: &str) -> Self {
264        self.joins.push(Join {
265            join_type,
266            table: table.to_string(),
267            on_condition: on_condition.to_string(),
268        });
269        self
270    }
271
272    /// Add an ORDER BY clause
273    ///
274    /// # Example
275    ///
276    /// ```rust
277    /// use oxify_storage::query_builder::{QueryBuilder, SortOrder};
278    ///
279    /// let builder = QueryBuilder::new("users")
280    ///     .order_by("created_at", SortOrder::Desc);
281    /// ```
282    pub fn order_by(mut self, column: &str, order: SortOrder) -> Self {
283        self.order_by.push((column.to_string(), order));
284        self
285    }
286
287    /// Add a GROUP BY clause
288    pub fn group_by(mut self, column: &str) -> Self {
289        self.group_by.push(column.to_string());
290        self
291    }
292
293    /// Add a HAVING condition (requires GROUP BY)
294    pub fn having(mut self, condition: Condition) -> Self {
295        self.having.push(condition);
296        self
297    }
298
299    /// Set LIMIT
300    ///
301    /// # Example
302    ///
303    /// ```rust
304    /// use oxify_storage::query_builder::QueryBuilder;
305    ///
306    /// let builder = QueryBuilder::new("users").limit(10);
307    /// ```
308    pub fn limit(mut self, limit: i64) -> Self {
309        self.limit_value = Some(limit);
310        self
311    }
312
313    /// Set OFFSET
314    pub fn offset(mut self, offset: i64) -> Self {
315        self.offset_value = Some(offset);
316        self
317    }
318
319    /// Build the final SQL query and return the query string and total parameter count
320    ///
321    /// Returns a tuple of (SQL string, number of parameters expected)
322    ///
323    /// # Example
324    ///
325    /// ```rust
326    /// use oxify_storage::query_builder::{QueryBuilder, Condition};
327    ///
328    /// let mut builder = QueryBuilder::new("users")
329    ///     .select(&["id", "name"])
330    ///     .where_condition(Condition::Eq("status".to_string()));
331    ///
332    /// let (sql, param_count) = builder.build();
333    /// assert_eq!(param_count, 1);
334    /// ```
335    pub fn build(&mut self) -> (String, usize) {
336        let mut sql = String::new();
337        let mut param_counter = 1;
338
339        // SELECT clause
340        sql.push_str("SELECT ");
341        if self.distinct {
342            sql.push_str("DISTINCT ");
343        }
344        sql.push_str(&self.select_columns.join(", "));
345
346        // FROM clause
347        sql.push_str(&format!(" FROM {}", self.table));
348
349        // JOIN clauses
350        for join in &self.joins {
351            sql.push_str(&format!(
352                " {} {} ON {}",
353                join.join_type, join.table, join.on_condition
354            ));
355        }
356
357        // WHERE clause
358        if !self.conditions.is_empty() {
359            sql.push_str(" WHERE ");
360            let where_clauses: Vec<String> = self
361                .conditions
362                .iter()
363                .map(|cond| {
364                    let clause = cond.to_sql(param_counter);
365                    param_counter += cond.param_count();
366                    clause
367                })
368                .collect();
369            sql.push_str(&where_clauses.join(" AND "));
370        }
371
372        // GROUP BY clause
373        if !self.group_by.is_empty() {
374            sql.push_str(" GROUP BY ");
375            sql.push_str(&self.group_by.join(", "));
376        }
377
378        // HAVING clause
379        if !self.having.is_empty() {
380            sql.push_str(" HAVING ");
381            let having_clauses: Vec<String> = self
382                .having
383                .iter()
384                .map(|cond| {
385                    let clause = cond.to_sql(param_counter);
386                    param_counter += cond.param_count();
387                    clause
388                })
389                .collect();
390            sql.push_str(&having_clauses.join(" AND "));
391        }
392
393        // ORDER BY clause
394        if !self.order_by.is_empty() {
395            sql.push_str(" ORDER BY ");
396            let order_clauses: Vec<String> = self
397                .order_by
398                .iter()
399                .map(|(col, order)| format!("{col} {order}"))
400                .collect();
401            sql.push_str(&order_clauses.join(", "));
402        }
403
404        // LIMIT clause
405        if let Some(limit) = self.limit_value {
406            sql.push_str(&format!(" LIMIT {limit}"));
407        }
408
409        // OFFSET clause
410        if let Some(offset) = self.offset_value {
411            sql.push_str(&format!(" OFFSET {offset}"));
412        }
413
414        (sql, param_counter - 1)
415    }
416
417    /// Build a COUNT query from this builder
418    ///
419    /// This creates a COUNT(*) query using the same WHERE conditions
420    pub fn build_count(&mut self) -> (String, usize) {
421        let original_select = self.select_columns.clone();
422        let original_order = self.order_by.clone();
423        let original_limit = self.limit_value;
424        let original_offset = self.offset_value;
425
426        self.select_columns = vec!["COUNT(*)".to_string()];
427        self.order_by.clear();
428        self.limit_value = None;
429        self.offset_value = None;
430
431        let result = self.build();
432
433        self.select_columns = original_select;
434        self.order_by = original_order;
435        self.limit_value = original_limit;
436        self.offset_value = original_offset;
437
438        result
439    }
440}
441
442/// Builder for UPDATE queries
443#[derive(Debug, Clone)]
444pub struct UpdateBuilder {
445    table: String,
446    set_columns: Vec<String>,
447    conditions: Vec<Condition>,
448}
449
450impl UpdateBuilder {
451    /// Create a new UPDATE query builder
452    pub fn new(table: &str) -> Self {
453        Self {
454            table: table.to_string(),
455            set_columns: Vec::new(),
456            conditions: Vec::new(),
457        }
458    }
459
460    /// Add a column to SET
461    pub fn set(mut self, column: &str) -> Self {
462        self.set_columns.push(column.to_string());
463        self
464    }
465
466    /// Add a WHERE condition
467    pub fn where_condition(mut self, condition: Condition) -> Self {
468        self.conditions.push(condition);
469        self
470    }
471
472    /// Build the UPDATE query
473    pub fn build(&self) -> (String, usize) {
474        let mut sql = format!("UPDATE {}", self.table);
475        let mut param_counter = 1;
476
477        // SET clause
478        if !self.set_columns.is_empty() {
479            sql.push_str(" SET ");
480            let set_clauses: Vec<String> = self
481                .set_columns
482                .iter()
483                .map(|col| {
484                    let clause = format!("{col} = ${param_counter}");
485                    param_counter += 1;
486                    clause
487                })
488                .collect();
489            sql.push_str(&set_clauses.join(", "));
490        }
491
492        // WHERE clause
493        if !self.conditions.is_empty() {
494            sql.push_str(" WHERE ");
495            let where_clauses: Vec<String> = self
496                .conditions
497                .iter()
498                .map(|cond| {
499                    let clause = cond.to_sql(param_counter);
500                    param_counter += cond.param_count();
501                    clause
502                })
503                .collect();
504            sql.push_str(&where_clauses.join(" AND "));
505        }
506
507        (sql, param_counter - 1)
508    }
509}
510
511/// Builder for DELETE queries
512#[derive(Debug, Clone)]
513pub struct DeleteBuilder {
514    table: String,
515    conditions: Vec<Condition>,
516}
517
518impl DeleteBuilder {
519    /// Create a new DELETE query builder
520    pub fn new(table: &str) -> Self {
521        Self {
522            table: table.to_string(),
523            conditions: Vec::new(),
524        }
525    }
526
527    /// Add a WHERE condition
528    pub fn where_condition(mut self, condition: Condition) -> Self {
529        self.conditions.push(condition);
530        self
531    }
532
533    /// Build the DELETE query
534    pub fn build(&self) -> (String, usize) {
535        let mut sql = format!("DELETE FROM {}", self.table);
536        let mut param_counter = 1;
537
538        // WHERE clause
539        if !self.conditions.is_empty() {
540            sql.push_str(" WHERE ");
541            let where_clauses: Vec<String> = self
542                .conditions
543                .iter()
544                .map(|cond| {
545                    let clause = cond.to_sql(param_counter);
546                    param_counter += cond.param_count();
547                    clause
548                })
549                .collect();
550            sql.push_str(&where_clauses.join(" AND "));
551        }
552
553        (sql, param_counter - 1)
554    }
555}
556
557#[cfg(test)]
558mod tests {
559    use super::*;
560
561    #[test]
562    fn test_simple_select() {
563        let mut builder = QueryBuilder::new("users");
564        let (sql, params) = builder.build();
565        assert_eq!(sql, "SELECT * FROM users");
566        assert_eq!(params, 0);
567    }
568
569    #[test]
570    fn test_select_with_columns() {
571        let mut builder = QueryBuilder::new("users").select(&["id", "name", "email"]);
572        let (sql, params) = builder.build();
573        assert_eq!(sql, "SELECT id, name, email FROM users");
574        assert_eq!(params, 0);
575    }
576
577    #[test]
578    fn test_where_equal() {
579        let mut builder =
580            QueryBuilder::new("users").where_condition(Condition::Eq("status".to_string()));
581        let (sql, params) = builder.build();
582        assert_eq!(sql, "SELECT * FROM users WHERE status = $1");
583        assert_eq!(params, 1);
584    }
585
586    #[test]
587    fn test_multiple_where_conditions() {
588        let mut builder = QueryBuilder::new("users")
589            .where_condition(Condition::Eq("status".to_string()))
590            .where_condition(Condition::Gt("age".to_string()));
591        let (sql, params) = builder.build();
592        assert_eq!(sql, "SELECT * FROM users WHERE status = $1 AND age > $2");
593        assert_eq!(params, 2);
594    }
595
596    #[test]
597    fn test_where_in() {
598        let mut builder =
599            QueryBuilder::new("users").where_condition(Condition::In("role".to_string(), 3));
600        let (sql, params) = builder.build();
601        assert_eq!(sql, "SELECT * FROM users WHERE role IN ($1, $2, $3)");
602        assert_eq!(params, 3);
603    }
604
605    #[test]
606    fn test_where_between() {
607        let mut builder =
608            QueryBuilder::new("users").where_condition(Condition::Between("age".to_string()));
609        let (sql, params) = builder.build();
610        assert_eq!(sql, "SELECT * FROM users WHERE age BETWEEN $1 AND $2");
611        assert_eq!(params, 2);
612    }
613
614    #[test]
615    fn test_where_null() {
616        let mut builder =
617            QueryBuilder::new("users").where_condition(Condition::IsNull("deleted_at".to_string()));
618        let (sql, params) = builder.build();
619        assert_eq!(sql, "SELECT * FROM users WHERE deleted_at IS NULL");
620        assert_eq!(params, 0);
621    }
622
623    #[test]
624    fn test_order_by() {
625        let mut builder = QueryBuilder::new("users")
626            .order_by("created_at", SortOrder::Desc)
627            .order_by("name", SortOrder::Asc);
628        let (sql, params) = builder.build();
629        assert_eq!(
630            sql,
631            "SELECT * FROM users ORDER BY created_at DESC, name ASC"
632        );
633        assert_eq!(params, 0);
634    }
635
636    #[test]
637    fn test_limit_offset() {
638        let mut builder = QueryBuilder::new("users").limit(10).offset(20);
639        let (sql, params) = builder.build();
640        assert_eq!(sql, "SELECT * FROM users LIMIT 10 OFFSET 20");
641        assert_eq!(params, 0);
642    }
643
644    #[test]
645    fn test_complex_query() {
646        let mut builder = QueryBuilder::new("users")
647            .select(&["id", "name"])
648            .where_condition(Condition::Eq("status".to_string()))
649            .where_condition(Condition::IsNotNull("email".to_string()))
650            .order_by("created_at", SortOrder::Desc)
651            .limit(20);
652        let (sql, params) = builder.build();
653        assert_eq!(
654            sql,
655            "SELECT id, name FROM users WHERE status = $1 AND email IS NOT NULL ORDER BY created_at DESC LIMIT 20"
656        );
657        assert_eq!(params, 1);
658    }
659
660    #[test]
661    fn test_count_query() {
662        let mut builder = QueryBuilder::new("users")
663            .select(&["id", "name"])
664            .where_condition(Condition::Eq("status".to_string()))
665            .order_by("created_at", SortOrder::Desc)
666            .limit(20);
667        let (sql, params) = builder.build_count();
668        assert_eq!(sql, "SELECT COUNT(*) FROM users WHERE status = $1");
669        assert_eq!(params, 1);
670    }
671
672    #[test]
673    fn test_distinct_query() {
674        let mut builder = QueryBuilder::new("users").select(&["email"]).distinct();
675        let (sql, params) = builder.build();
676        assert_eq!(sql, "SELECT DISTINCT email FROM users");
677        assert_eq!(params, 0);
678    }
679
680    #[test]
681    fn test_group_by() {
682        let mut builder = QueryBuilder::new("users")
683            .select(&["role", "COUNT(*) as count"])
684            .group_by("role");
685        let (sql, params) = builder.build();
686        assert_eq!(
687            sql,
688            "SELECT role, COUNT(*) as count FROM users GROUP BY role"
689        );
690        assert_eq!(params, 0);
691    }
692
693    #[test]
694    fn test_update_builder() {
695        let builder = UpdateBuilder::new("users")
696            .set("name")
697            .set("email")
698            .where_condition(Condition::Eq("id".to_string()));
699        let (sql, params) = builder.build();
700        assert_eq!(sql, "UPDATE users SET name = $1, email = $2 WHERE id = $3");
701        assert_eq!(params, 3);
702    }
703
704    #[test]
705    fn test_delete_builder() {
706        let builder =
707            DeleteBuilder::new("users").where_condition(Condition::Lt("last_login".to_string()));
708        let (sql, params) = builder.build();
709        assert_eq!(sql, "DELETE FROM users WHERE last_login < $1");
710        assert_eq!(params, 1);
711    }
712
713    #[test]
714    fn test_join() {
715        let mut builder = QueryBuilder::new("users")
716            .join(JoinType::Inner, "roles", "users.role_id = roles.id")
717            .select(&["users.name", "roles.role_name"]);
718        let (sql, params) = builder.build();
719        assert_eq!(
720            sql,
721            "SELECT users.name, roles.role_name FROM users INNER JOIN roles ON users.role_id = roles.id"
722        );
723        assert_eq!(params, 0);
724    }
725
726    #[test]
727    fn test_where_if() {
728        let mut builder1 =
729            QueryBuilder::new("users").where_if(true, Condition::Eq("status".to_string()));
730        let (sql1, params1) = builder1.build();
731        assert_eq!(sql1, "SELECT * FROM users WHERE status = $1");
732        assert_eq!(params1, 1);
733
734        let mut builder2 =
735            QueryBuilder::new("users").where_if(false, Condition::Eq("status".to_string()));
736        let (sql2, params2) = builder2.build();
737        assert_eq!(sql2, "SELECT * FROM users");
738        assert_eq!(params2, 0);
739    }
740
741    #[test]
742    fn test_like_condition() {
743        let mut builder =
744            QueryBuilder::new("users").where_condition(Condition::Like("name".to_string()));
745        let (sql, params) = builder.build();
746        assert_eq!(sql, "SELECT * FROM users WHERE name LIKE $1");
747        assert_eq!(params, 1);
748    }
749
750    #[test]
751    fn test_having_clause() {
752        let mut builder = QueryBuilder::new("orders")
753            .select(&["customer_id", "COUNT(*) as order_count"])
754            .group_by("customer_id")
755            .having(Condition::Gt("COUNT(*)".to_string()));
756        let (sql, params) = builder.build();
757        assert_eq!(
758            sql,
759            "SELECT customer_id, COUNT(*) as order_count FROM orders GROUP BY customer_id HAVING COUNT(*) > $1"
760        );
761        assert_eq!(params, 1);
762    }
763}
oxify_storage/query_builder.rs

oxify_storage/
query_builder.rs
