mtgjson_sdk/
sql_builder.rs

1//! SQL builder with parameterized query construction.
2//!
3//! All user-supplied values go through DuckDB's parameter binding (`?` placeholders),
4//! never through string interpolation. Builder methods return `&mut Self` for chaining.
5//!
6//! # Example
7//!
8//! ```rust
9//! use mtgjson_sdk::SqlBuilder;
10//! let (sql, params) = SqlBuilder::new("cards")
11//!     .where_eq("setCode", "MH3")
12//!     .where_like("name", "Lightning%")
13//!     .order_by(&["name ASC"])
14//!     .limit(10)
15//!     .build();
16//! ```
17
18/// Builds parameterized SQL queries safely.
19///
20/// All user-supplied values go through DuckDB's parameter binding (`?` placeholders),
21/// never through string interpolation. Methods return `&mut Self` for chaining.
22pub struct SqlBuilder {
23    select_cols: Vec<String>,
24    is_distinct: bool,
25    from_table: String,
26    joins: Vec<String>,
27    where_clauses: Vec<String>,
28    params: Vec<String>,
29    group_by_cols: Vec<String>,
30    having_clauses: Vec<String>,
31    order_by_cols: Vec<String>,
32    limit_val: Option<usize>,
33    offset_val: Option<usize>,
34}
35
36impl SqlBuilder {
37    /// Create a builder targeting the given table or view.
38    pub fn new(table: &str) -> Self {
39        Self {
40            select_cols: vec!["*".to_string()],
41            is_distinct: false,
42            from_table: table.to_string(),
43            joins: Vec::new(),
44            where_clauses: Vec::new(),
45            params: Vec::new(),
46            group_by_cols: Vec::new(),
47            having_clauses: Vec::new(),
48            order_by_cols: Vec::new(),
49            limit_val: None,
50            offset_val: None,
51        }
52    }
53
54    /// Set the columns to select (replaces the default `*`).
55    pub fn select(&mut self, cols: &[&str]) -> &mut Self {
56        self.select_cols = cols.iter().map(|c| c.to_string()).collect();
57        self
58    }
59
60    /// Add DISTINCT to the SELECT clause.
61    pub fn distinct(&mut self) -> &mut Self {
62        self.is_distinct = true;
63        self
64    }
65
66    /// Add a JOIN clause.
67    ///
68    /// The clause should be a full JOIN expression, e.g.
69    /// `"JOIN sets s ON cards.setCode = s.code"`.
70    pub fn join(&mut self, clause: &str) -> &mut Self {
71        self.joins.push(clause.to_string());
72        self
73    }
74
75    /// Add a WHERE condition with `?` placeholders for each param.
76    ///
77    /// The caller provides a condition using `?` for each parameter value.
78    /// Parameters are appended in order.
79    pub fn where_clause(&mut self, condition: &str, params: &[&str]) -> &mut Self {
80        self.where_clauses.push(condition.to_string());
81        self.params.extend(params.iter().map(|p| p.to_string()));
82        self
83    }
84
85    /// Add a case-insensitive LIKE condition.
86    ///
87    /// Generates: `LOWER({column}) LIKE LOWER(?)`
88    pub fn where_like(&mut self, column: &str, value: &str) -> &mut Self {
89        self.where_clauses
90            .push(format!("LOWER({}) LIKE LOWER(?)", column));
91        self.params.push(value.to_string());
92        self
93    }
94
95    /// Add an IN condition with parameterized values.
96    ///
97    /// Empty values list produces `FALSE`.
98    pub fn where_in(&mut self, column: &str, values: &[&str]) -> &mut Self {
99        if values.is_empty() {
100            self.where_clauses.push("FALSE".to_string());
101            return self;
102        }
103        let placeholders: Vec<&str> = values.iter().map(|_| "?").collect();
104        self.where_clauses
105            .push(format!("{} IN ({})", column, placeholders.join(", ")));
106        self.params.extend(values.iter().map(|v| v.to_string()));
107        self
108    }
109
110    /// Add an equality condition: `{column} = ?`.
111    pub fn where_eq(&mut self, column: &str, value: &str) -> &mut Self {
112        self.where_clauses
113            .push(format!("{} = ?", column));
114        self.params.push(value.to_string());
115        self
116    }
117
118    /// Add a greater-than-or-equal condition: `{column} >= ?`.
119    pub fn where_gte(&mut self, column: &str, value: &str) -> &mut Self {
120        self.where_clauses
121            .push(format!("{} >= ?", column));
122        self.params.push(value.to_string());
123        self
124    }
125
126    /// Add a less-than-or-equal condition: `{column} <= ?`.
127    pub fn where_lte(&mut self, column: &str, value: &str) -> &mut Self {
128        self.where_clauses
129            .push(format!("{} <= ?", column));
130        self.params.push(value.to_string());
131        self
132    }
133
134    /// Add a regex match condition using DuckDB's `regexp_matches`.
135    ///
136    /// Generates: `regexp_matches({column}, ?)`
137    pub fn where_regex(&mut self, column: &str, pattern: &str) -> &mut Self {
138        self.where_clauses
139            .push(format!("regexp_matches({}, ?)", column));
140        self.params.push(pattern.to_string());
141        self
142    }
143
144    /// Add a fuzzy string match condition using Jaro-Winkler similarity.
145    ///
146    /// Generates: `jaro_winkler_similarity({column}, ?) > {threshold}`
147    ///
148    /// The threshold must be between 0.0 and 1.0 (inclusive).
149    pub fn where_fuzzy(&mut self, column: &str, value: &str, threshold: f64) -> &mut Self {
150        self.where_clauses.push(format!(
151            "jaro_winkler_similarity({}, ?) > {}",
152            column, threshold
153        ));
154        self.params.push(value.to_string());
155        self
156    }
157
158    /// Add OR-combined conditions.
159    ///
160    /// Each condition is a `(sql_fragment, param_value)` tuple where the fragment
161    /// uses `?` as a placeholder.
162    ///
163    /// # Example
164    ///
165    /// ```rust
166    /// use mtgjson_sdk::SqlBuilder;
167    /// let mut builder = SqlBuilder::new("cards");
168    /// builder.where_or(&[("name = ?", "Bolt"), ("name = ?", "Counter")]);
169    /// // -> WHERE (name = ? OR name = ?)
170    /// ```
171    pub fn where_or(&mut self, conditions: &[(&str, &str)]) -> &mut Self {
172        if conditions.is_empty() {
173            return self;
174        }
175        let mut or_parts = Vec::with_capacity(conditions.len());
176        for (cond, param) in conditions {
177            or_parts.push(cond.to_string());
178            self.params.push(param.to_string());
179        }
180        self.where_clauses
181            .push(format!("({})", or_parts.join(" OR ")));
182        self
183    }
184
185    /// Add GROUP BY columns.
186    pub fn group_by(&mut self, cols: &[&str]) -> &mut Self {
187        self.group_by_cols
188            .extend(cols.iter().map(|c| c.to_string()));
189        self
190    }
191
192    /// Add a HAVING condition with `?` placeholders.
193    pub fn having(&mut self, condition: &str, params: &[&str]) -> &mut Self {
194        self.having_clauses.push(condition.to_string());
195        self.params.extend(params.iter().map(|p| p.to_string()));
196        self
197    }
198
199    /// Add ORDER BY clauses (e.g. `"name ASC"`, `"price DESC"`).
200    pub fn order_by(&mut self, clauses: &[&str]) -> &mut Self {
201        self.order_by_cols
202            .extend(clauses.iter().map(|c| c.to_string()));
203        self
204    }
205
206    /// Set the maximum number of rows to return.
207    pub fn limit(&mut self, n: usize) -> &mut Self {
208        self.limit_val = Some(n);
209        self
210    }
211
212    /// Set the number of rows to skip before returning results.
213    pub fn offset(&mut self, n: usize) -> &mut Self {
214        self.offset_val = Some(n);
215        self
216    }
217
218    /// Build the final SQL string and parameter list.
219    ///
220    /// Returns a tuple of `(sql_string, params_list)` ready for execution.
221    pub fn build(&self) -> (String, Vec<String>) {
222        let distinct = if self.is_distinct { "DISTINCT " } else { "" };
223        let cols = self.select_cols.join(", ");
224        let mut parts = vec![
225            format!("SELECT {}{}", distinct, cols),
226            format!("FROM {}", self.from_table),
227        ];
228
229        for j in &self.joins {
230            parts.push(j.clone());
231        }
232
233        if !self.where_clauses.is_empty() {
234            parts.push(format!("WHERE {}", self.where_clauses.join(" AND ")));
235        }
236
237        if !self.group_by_cols.is_empty() {
238            parts.push(format!("GROUP BY {}", self.group_by_cols.join(", ")));
239        }
240
241        if !self.having_clauses.is_empty() {
242            parts.push(format!("HAVING {}", self.having_clauses.join(" AND ")));
243        }
244
245        if !self.order_by_cols.is_empty() {
246            parts.push(format!("ORDER BY {}", self.order_by_cols.join(", ")));
247        }
248
249        if let Some(n) = self.limit_val {
250            parts.push(format!("LIMIT {}", n));
251        }
252
253        if let Some(n) = self.offset_val {
254            parts.push(format!("OFFSET {}", n));
255        }
256
257        (parts.join("\n"), self.params.clone())
258    }
259}
mtgjson_sdk/sql_builder.rs

mtgjson_sdk/
sql_builder.rs
