backbone_orm/
raw_query.rs

1//! Raw SQL query builder with parameter binding and advanced SQL features
2
3use anyhow::Result;
4use sqlx::{PgPool, FromRow, postgres::PgRow, Postgres};
5use crate::query_builder::QueryValue;
6
7/// Raw SQL query builder with parameter binding
8pub struct RawQueryBuilder {
9    sql: String,
10    params: Vec<QueryValue>,
11    param_count: usize,
12}
13
14impl RawQueryBuilder {
15    /// Create a new raw query builder
16    pub fn new(sql: &str) -> Self {
17        Self {
18            sql: sql.to_string(),
19            params: Vec::new(),
20            param_count: 1,
21        }
22    }
23
24    /// Add a parameter to the query
25    pub fn bind<T: Into<QueryValue>>(mut self, value: T) -> Self {
26        self.params.push(value.into());
27        self.param_count += 1;
28        self
29    }
30
31    /// Add multiple parameters
32    pub fn bind_many<T: Into<QueryValue>>(mut self, values: Vec<T>) -> Self {
33        for value in values {
34            self.params.push(value.into());
35            self.param_count += 1;
36        }
37        self
38    }
39
40    /// Build the final query with parameter substitution
41    pub fn build(self) -> (String, Vec<QueryValue>) {
42        let mut sql = self.sql;
43
44        // Replace $1, $2, etc. with actual parameter values in order
45        // Note: In a real implementation, we'd let SQLx handle parameter binding
46        // This is a simplified version for demonstration
47        for (i, param) in self.params.iter().enumerate() {
48            let placeholder = format!("${}", i + 1);
49            if let Some(pos) = sql.find(&placeholder) {
50                let replacement = match param {
51                    QueryValue::Text(s) => format!("'{}'", s.replace('\'', "''")),
52                    QueryValue::Integer(n) => n.to_string(),
53                    QueryValue::Float(f) => f.to_string(),
54                    QueryValue::Boolean(b) => b.to_string(),
55                    QueryValue::Uuid(u) => format!("'{}'", u),
56                    QueryValue::Timestamp(ts) => format!("'{}'", ts),
57                    QueryValue::Null => "NULL".to_string(),
58                };
59                sql.replace_range(pos..pos + placeholder.len(), &replacement);
60            }
61        }
62
63        (sql, self.params)
64    }
65
66    /// Execute the raw query and map results to a struct
67    pub async fn execute<T>(self, pool: &PgPool) -> Result<Vec<T>>
68    where
69        T: for<'r> FromRow<'r, PgRow> + Send + Unpin,
70    {
71        let (sql, params) = self.build_parameterized();
72
73        let mut query = sqlx::query_as::<Postgres, T>(&sql);
74
75        // Bind parameters in order for SQLx
76        for param in params {
77            query = match param {
78                QueryValue::Text(val) => query.bind(val),
79                QueryValue::Integer(val) => query.bind(val),
80                QueryValue::Float(val) => query.bind(val),
81                QueryValue::Boolean(val) => query.bind(val),
82                QueryValue::Uuid(val) => query.bind(val),
83                QueryValue::Timestamp(val) => query.bind(val),
84                QueryValue::Null => query.bind::<Option<String>>(None),
85            };
86        }
87
88        let results = query.fetch_all(pool).await?;
89        Ok(results)
90    }
91
92    /// Execute the raw query and return the first result
93    pub async fn execute_first<T>(self, pool: &PgPool) -> Result<Option<T>>
94    where
95        T: for<'r> FromRow<'r, PgRow> + Send + Unpin,
96    {
97        let (sql, params) = self.build_parameterized();
98
99        let mut query = sqlx::query_as::<Postgres, T>(&sql);
100
101        // Bind parameters in order for SQLx
102        for param in params {
103            query = match param {
104                QueryValue::Text(val) => query.bind(val),
105                QueryValue::Integer(val) => query.bind(val),
106                QueryValue::Float(val) => query.bind(val),
107                QueryValue::Boolean(val) => query.bind(val),
108                QueryValue::Uuid(val) => query.bind(val),
109                QueryValue::Timestamp(val) => query.bind(val),
110                QueryValue::Null => query.bind::<Option<String>>(None),
111            };
112        }
113
114        let result = query.fetch_optional(pool).await?;
115        Ok(result)
116    }
117
118    /// Execute the raw query and return affected row count
119    pub async fn execute_raw(self, pool: &PgPool) -> Result<u64> {
120        let (sql, params) = self.build_parameterized();
121
122        let mut query = sqlx::query(&sql);
123
124        // Bind parameters in order for SQLx
125        for param in params {
126            query = match param {
127                QueryValue::Text(val) => query.bind(val),
128                QueryValue::Integer(val) => query.bind(val),
129                QueryValue::Float(val) => query.bind(val),
130                QueryValue::Boolean(val) => query.bind(val),
131                QueryValue::Uuid(val) => query.bind(val),
132                QueryValue::Timestamp(val) => query.bind(val),
133                QueryValue::Null => query.bind::<Option<String>>(None),
134            };
135        }
136
137        let result = query.execute(pool).await?;
138        Ok(result.rows_affected())
139    }
140
141    /// Build query with parameters for SQLx (keeps parameter placeholders)
142    fn build_parameterized(self) -> (String, Vec<QueryValue>) {
143        (self.sql, self.params)
144    }
145}
146
147/// Advanced Query Builder with JOIN support
148pub struct AdvancedQueryBuilder {
149    base_table: String,
150    fields: Vec<String>,
151    joins: Vec<JoinClause>,
152    conditions: Vec<(String, QueryValue)>,
153    group_by: Vec<String>,
154    having: Vec<(String, QueryValue)>,
155    order_by: Vec<String>,
156    limit: Option<u32>,
157    offset: Option<u32>,
158    ctes: Vec<CteClause>,
159    window_functions: Vec<WindowFunction>,
160    next_param_id: usize,
161}
162
163/// JOIN clause specification
164#[derive(Debug, Clone)]
165pub struct JoinClause {
166    pub join_type: JoinType,
167    pub table: String,
168    pub on_condition: String,
169    pub alias: Option<String>,
170}
171
172/// Join types
173#[derive(Debug, Clone)]
174pub enum JoinType {
175    Inner,
176    Left,
177    Right,
178    Full,
179    Cross,
180}
181
182/// Common Table Expression (CTE) clause
183#[derive(Debug, Clone)]
184pub struct CteClause {
185    pub name: String,
186    pub query: String,
187}
188
189/// Window function specification
190#[derive(Debug, Clone)]
191pub struct WindowFunction {
192    pub expression: String,
193    pub alias: String,
194    pub partition_by: Vec<String>,
195    pub order_by: Vec<String>,
196    pub frame: Option<String>,
197}
198
199impl AdvancedQueryBuilder {
200    /// Create a new advanced query builder
201    pub fn new(base_table: &str) -> Self {
202        Self {
203            base_table: base_table.to_string(),
204            fields: vec!["*".to_string()],
205            joins: Vec::new(),
206            conditions: Vec::new(),
207            group_by: Vec::new(),
208            having: Vec::new(),
209            order_by: Vec::new(),
210            limit: None,
211            offset: None,
212            ctes: Vec::new(),
213            window_functions: Vec::new(),
214            next_param_id: 1,
215        }
216    }
217
218    /// Select specific fields
219    pub fn select(mut self, fields: &[&str]) -> Self {
220        self.fields = fields.iter().map(|f| f.to_string()).collect();
221        self
222    }
223
224    /// Add a JOIN clause
225    pub fn join(mut self, join_type: JoinType, table: &str, on_condition: &str) -> Self {
226        self.joins.push(JoinClause {
227            join_type,
228            table: table.to_string(),
229            on_condition: on_condition.to_string(),
230            alias: None,
231        });
232        self
233    }
234
235    /// Add a JOIN clause with alias
236    pub fn join_alias(mut self, join_type: JoinType, table: &str, alias: &str, on_condition: &str) -> Self {
237        self.joins.push(JoinClause {
238            join_type,
239            table: table.to_string(),
240            on_condition: on_condition.to_string(),
241            alias: Some(alias.to_string()),
242        });
243        self
244    }
245
246    /// Add WHERE condition (raw SQL)
247    pub fn where_raw(mut self, condition: &str, param: QueryValue) -> Self {
248        self.conditions.push((condition.to_string(), param));
249        self.next_param_id += 1;
250        self
251    }
252
253    /// Add GROUP BY clause
254    pub fn group_by(mut self, fields: &[&str]) -> Self {
255        self.group_by = fields.iter().map(|f| f.to_string()).collect();
256        self
257    }
258
259    /// Add HAVING condition
260    pub fn having(mut self, condition: &str, param: QueryValue) -> Self {
261        self.having.push((condition.to_string(), param));
262        self.next_param_id += 1;
263        self
264    }
265
266    /// Add a Common Table Expression (CTE)
267    pub fn with_cte(mut self, name: &str, query: &str) -> Self {
268        self.ctes.push(CteClause {
269            name: name.to_string(),
270            query: query.to_string(),
271        });
272        self
273    }
274
275    /// Add a window function
276    pub fn window_fn(
277        mut self,
278        expression: &str,
279        alias: &str,
280        partition_by: &[&str],
281        order_by: &[&str]
282    ) -> Self {
283        self.window_functions.push(WindowFunction {
284            expression: expression.to_string(),
285            alias: alias.to_string(),
286            partition_by: partition_by.iter().map(|f| f.to_string()).collect(),
287            order_by: order_by.iter().map(|f| f.to_string()).collect(),
288            frame: None,
289        });
290        self
291    }
292
293    /// Add window function with frame clause
294    pub fn window_fn_with_frame(
295        mut self,
296        expression: &str,
297        alias: &str,
298        partition_by: &[&str],
299        order_by: &[&str],
300        frame: &str
301    ) -> Self {
302        self.window_functions.push(WindowFunction {
303            expression: expression.to_string(),
304            alias: alias.to_string(),
305            partition_by: partition_by.iter().map(|f| f.to_string()).collect(),
306            order_by: order_by.iter().map(|f| f.to_string()).collect(),
307            frame: Some(frame.to_string()),
308        });
309        self
310    }
311
312    /// Add ORDER BY clause
313    pub fn order_by(mut self, field: &str, direction: &str) -> Self {
314        self.order_by.push(format!("{} {}", field, direction));
315        self
316    }
317
318    /// Set LIMIT
319    pub fn limit(mut self, limit: u32) -> Self {
320        self.limit = Some(limit);
321        self
322    }
323
324    /// Set OFFSET
325    pub fn offset(mut self, offset: u32) -> Self {
326        self.offset = Some(offset);
327        self
328    }
329
330    /// Build the complete SQL query
331    pub fn build_sql(&self) -> (String, Vec<QueryValue>) {
332        let mut sql = String::new();
333        let mut params = Vec::new();
334
335        // Add CTEs if any
336        if !self.ctes.is_empty() {
337            sql.push_str("WITH ");
338            let cte_strings: Vec<String> = self.ctes.iter()
339                .map(|cte| format!("{} AS ({})", cte.name, cte.query))
340                .collect();
341            sql.push_str(&cte_strings.join(", "));
342            sql.push(' ');
343        }
344
345        // Build SELECT clause
346        sql.push_str("SELECT ");
347
348        // Add window functions to fields
349        let mut all_fields = self.fields.clone();
350        for wf in &self.window_functions {
351            let mut wf_expr = wf.expression.clone();
352
353            // Check if expression already contains OVER clause
354            if wf_expr.contains("OVER") {
355                // Expression is already complete, just add alias
356                all_fields.push(format!("{} AS {}", wf_expr, wf.alias));
357            } else {
358                // Build OVER clause dynamically
359                if !wf.partition_by.is_empty() {
360                    wf_expr = format!("{} OVER (PARTITION BY {}", wf_expr, wf.partition_by.join(", "));
361                    if !wf.order_by.is_empty() {
362                        wf_expr = format!("{} ORDER BY {}", wf_expr, wf.order_by.join(", "));
363                    }
364
365                    // Add frame if present
366                    if let Some(frame) = &wf.frame {
367                        wf_expr = format!("{} {})", wf_expr, frame);
368                    } else {
369                        wf_expr = format!("{})", wf_expr);
370                    }
371                } else if !wf.order_by.is_empty() {
372                    wf_expr = format!("{} OVER (ORDER BY {}", wf_expr, wf.order_by.join(", "));
373                    if let Some(frame) = &wf.frame {
374                        wf_expr = format!("{} {})", wf_expr, frame);
375                    } else {
376                        wf_expr = format!("{})", wf_expr);
377                    }
378                }
379
380                all_fields.push(format!("{} AS {}", wf_expr, wf.alias));
381            }
382        }
383
384        sql.push_str(&all_fields.join(", "));
385        sql.push_str(" FROM ");
386        sql.push_str(&self.base_table);
387
388        // Add JOINs
389        for join in &self.joins {
390            let join_str = match join.join_type {
391                JoinType::Inner => "INNER JOIN",
392                JoinType::Left => "LEFT JOIN",
393                JoinType::Right => "RIGHT JOIN",
394                JoinType::Full => "FULL JOIN",
395                JoinType::Cross => "CROSS JOIN",
396            };
397
398            sql.push(' ');
399            sql.push_str(join_str);
400            sql.push(' ');
401            sql.push_str(&join.table);
402
403            if let Some(alias) = &join.alias {
404                sql.push_str(" AS ");
405                sql.push_str(alias);
406            }
407
408            sql.push_str(" ON ");
409            sql.push_str(&join.on_condition);
410        }
411
412        // Add WHERE conditions
413        if !self.conditions.is_empty() {
414            sql.push_str(" WHERE ");
415            let condition_strings: Vec<String> = self.conditions.iter()
416                .map(|(cond, _)| cond.clone())
417                .collect();
418            sql.push_str(&condition_strings.join(" AND "));
419
420            // Collect parameters
421            for (_, param) in &self.conditions {
422                params.push(param.clone());
423            }
424        }
425
426        // Add GROUP BY
427        if !self.group_by.is_empty() {
428            sql.push_str(" GROUP BY ");
429            sql.push_str(&self.group_by.join(", "));
430        }
431
432        // Add HAVING
433        if !self.having.is_empty() {
434            sql.push_str(" HAVING ");
435            let having_strings: Vec<String> = self.having.iter()
436                .map(|(cond, _)| cond.clone())
437                .collect();
438            sql.push_str(&having_strings.join(" AND "));
439
440            // Collect parameters
441            for (_, param) in &self.having {
442                params.push(param.clone());
443            }
444        }
445
446        // Add ORDER BY
447        if !self.order_by.is_empty() {
448            sql.push_str(" ORDER BY ");
449            sql.push_str(&self.order_by.join(", "));
450        }
451
452        // Add LIMIT and OFFSET
453        if let Some(limit) = self.limit {
454            sql.push_str(&format!(" LIMIT {}", limit));
455        }
456        if let Some(offset) = self.offset {
457            sql.push_str(&format!(" OFFSET {}", offset));
458        }
459
460        (sql, params)
461    }
462
463    /// Execute the advanced query
464    pub async fn execute<T>(self, pool: &PgPool) -> Result<Vec<T>>
465    where
466        T: for<'r> FromRow<'r, PgRow> + Send + Unpin,
467    {
468        let (sql, params) = self.build_sql();
469
470        let mut query = sqlx::query_as::<Postgres, T>(&sql);
471
472        // Bind parameters in order
473        for param in params {
474            query = match param {
475                QueryValue::Text(val) => query.bind(val),
476                QueryValue::Integer(val) => query.bind(val),
477                QueryValue::Float(val) => query.bind(val),
478                QueryValue::Boolean(val) => query.bind(val),
479                QueryValue::Uuid(val) => query.bind(val),
480                QueryValue::Timestamp(val) => query.bind(val),
481                QueryValue::Null => query.bind::<Option<String>>(None),
482            };
483        }
484
485        let results = query.fetch_all(pool).await?;
486        Ok(results)
487    }
488
489    /// Execute and return first result
490    pub async fn execute_first<T>(self, pool: &PgPool) -> Result<Option<T>>
491    where
492        T: for<'r> FromRow<'r, PgRow> + Send + Unpin,
493    {
494        let (sql, params) = self.build_sql();
495
496        let mut query = sqlx::query_as::<Postgres, T>(&sql);
497
498        // Bind parameters in order
499        for param in params {
500            query = match param {
501                QueryValue::Text(val) => query.bind(val),
502                QueryValue::Integer(val) => query.bind(val),
503                QueryValue::Float(val) => query.bind(val),
504                QueryValue::Boolean(val) => query.bind(val),
505                QueryValue::Uuid(val) => query.bind(val),
506                QueryValue::Timestamp(val) => query.bind(val),
507                QueryValue::Null => query.bind::<Option<String>>(None),
508            };
509        }
510
511        let result = query.fetch_optional(pool).await?;
512        Ok(result)
513    }
514}
515
516/// Convenience functions for common raw query patterns
517pub struct RawQuery;
518
519impl RawQuery {
520    /// Execute a simple scalar query (returns single value)
521    pub async fn scalar<T>(pool: &PgPool, sql: &str, params: Vec<QueryValue>) -> Result<T>
522    where
523        T: for<'r> sqlx::Decode<'r, sqlx::Postgres> + sqlx::Type<sqlx::Postgres> + Send + Unpin,
524    {
525        let mut query = sqlx::query_scalar::<Postgres, T>(sql);
526
527        for param in params {
528            query = match param {
529                QueryValue::Text(val) => query.bind(val),
530                QueryValue::Integer(val) => query.bind(val),
531                QueryValue::Float(val) => query.bind(val),
532                QueryValue::Boolean(val) => query.bind(val),
533                QueryValue::Uuid(val) => query.bind(val),
534                QueryValue::Timestamp(val) => query.bind(val),
535                QueryValue::Null => query.bind::<Option<String>>(None),
536            };
537        }
538
539        let result = query.fetch_one(pool).await?;
540        Ok(result)
541    }
542
543    /// Execute a query and return multiple values
544    pub async fn many<T>(pool: &PgPool, sql: &str, params: Vec<QueryValue>) -> Result<Vec<T>>
545    where
546        T: for<'r> sqlx::Decode<'r, sqlx::Postgres> + sqlx::Type<sqlx::Postgres> + Send + Unpin,
547    {
548        let mut query = sqlx::query_scalar::<Postgres, T>(sql);
549
550        for param in params {
551            query = match param {
552                QueryValue::Text(val) => query.bind(val),
553                QueryValue::Integer(val) => query.bind(val),
554                QueryValue::Float(val) => query.bind(val),
555                QueryValue::Boolean(val) => query.bind(val),
556                QueryValue::Uuid(val) => query.bind(val),
557                QueryValue::Timestamp(val) => query.bind(val),
558                QueryValue::Null => query.bind::<Option<String>>(None),
559            };
560        }
561
562        let result = query.fetch_all(pool).await?;
563        Ok(result)
564    }
565}
backbone_orm/raw_query.rs

backbone_orm/
raw_query.rs
