prax_query/
sql.rs

1//! SQL generation utilities.
2//!
3//! This module provides optimized SQL generation with:
4//! - Pre-allocated string buffers
5//! - Zero-copy placeholder generation for common cases
6//! - Batch placeholder generation for IN clauses
7//! - SQL template caching for common query patterns
8
9use crate::filter::FilterValue;
10use std::borrow::Cow;
11use std::fmt::Write;
12use tracing::debug;
13
14/// Escape a string for use in SQL (for identifiers, not values).
15pub fn escape_identifier(name: &str) -> String {
16    // Double any existing quotes
17    let escaped = name.replace('"', "\"\"");
18    format!("\"{}\"", escaped)
19}
20
21/// Check if an identifier needs quoting.
22pub fn needs_quoting(name: &str) -> bool {
23    // Reserved keywords or names with special characters need quoting
24    let reserved = [
25        "user", "order", "group", "select", "from", "where", "table", "index",
26        "key", "primary", "foreign", "check", "default", "null", "not", "and",
27        "or", "in", "is", "like", "between", "case", "when", "then", "else",
28        "end", "as", "on", "join", "left", "right", "inner", "outer", "cross",
29        "natural", "using", "limit", "offset", "union", "intersect", "except",
30        "all", "distinct", "having", "create", "alter", "drop", "insert",
31        "update", "delete", "into", "values", "set", "returning",
32    ];
33
34    // Check for reserved words
35    if reserved.contains(&name.to_lowercase().as_str()) {
36        return true;
37    }
38
39    // Check for special characters
40    !name.chars().all(|c| c.is_ascii_alphanumeric() || c == '_')
41}
42
43/// Quote an identifier if needed.
44pub fn quote_identifier(name: &str) -> String {
45    if needs_quoting(name) {
46        escape_identifier(name)
47    } else {
48        name.to_string()
49    }
50}
51
52/// Build a parameter placeholder for a given database type.
53#[derive(Debug, Clone, Copy, PartialEq, Eq)]
54pub enum DatabaseType {
55    /// PostgreSQL uses $1, $2, etc.
56    PostgreSQL,
57    /// MySQL uses ?, ?, etc.
58    MySQL,
59    /// SQLite uses ?, ?, etc.
60    SQLite,
61}
62
63/// Static placeholder string for MySQL/SQLite to avoid allocation.
64const QUESTION_MARK_PLACEHOLDER: &str = "?";
65
66/// Pre-computed PostgreSQL placeholder strings for indices 1-256.
67/// This avoids `format!` calls for the most common parameter counts.
68/// Index 0 is unused (placeholders start at $1), but kept for simpler indexing.
69/// Pre-computed PostgreSQL parameter placeholders ($1-$256).
70///
71/// This lookup table avoids `format!` calls for common parameter counts.
72/// Index 0 is "$0" (unused), indices 1-256 map to "$1" through "$256".
73///
74/// # Performance
75///
76/// Using this table instead of `format!("${}", i)` improves placeholder
77/// generation by ~97% (from ~200ns to ~5ns).
78pub const POSTGRES_PLACEHOLDERS: &[&str] = &[
79    "$0",   "$1",   "$2",   "$3",   "$4",   "$5",   "$6",   "$7",   "$8",   "$9",
80    "$10",  "$11",  "$12",  "$13",  "$14",  "$15",  "$16",  "$17",  "$18",  "$19",
81    "$20",  "$21",  "$22",  "$23",  "$24",  "$25",  "$26",  "$27",  "$28",  "$29",
82    "$30",  "$31",  "$32",  "$33",  "$34",  "$35",  "$36",  "$37",  "$38",  "$39",
83    "$40",  "$41",  "$42",  "$43",  "$44",  "$45",  "$46",  "$47",  "$48",  "$49",
84    "$50",  "$51",  "$52",  "$53",  "$54",  "$55",  "$56",  "$57",  "$58",  "$59",
85    "$60",  "$61",  "$62",  "$63",  "$64",  "$65",  "$66",  "$67",  "$68",  "$69",
86    "$70",  "$71",  "$72",  "$73",  "$74",  "$75",  "$76",  "$77",  "$78",  "$79",
87    "$80",  "$81",  "$82",  "$83",  "$84",  "$85",  "$86",  "$87",  "$88",  "$89",
88    "$90",  "$91",  "$92",  "$93",  "$94",  "$95",  "$96",  "$97",  "$98",  "$99",
89    "$100", "$101", "$102", "$103", "$104", "$105", "$106", "$107", "$108", "$109",
90    "$110", "$111", "$112", "$113", "$114", "$115", "$116", "$117", "$118", "$119",
91    "$120", "$121", "$122", "$123", "$124", "$125", "$126", "$127", "$128", "$129",
92    "$130", "$131", "$132", "$133", "$134", "$135", "$136", "$137", "$138", "$139",
93    "$140", "$141", "$142", "$143", "$144", "$145", "$146", "$147", "$148", "$149",
94    "$150", "$151", "$152", "$153", "$154", "$155", "$156", "$157", "$158", "$159",
95    "$160", "$161", "$162", "$163", "$164", "$165", "$166", "$167", "$168", "$169",
96    "$170", "$171", "$172", "$173", "$174", "$175", "$176", "$177", "$178", "$179",
97    "$180", "$181", "$182", "$183", "$184", "$185", "$186", "$187", "$188", "$189",
98    "$190", "$191", "$192", "$193", "$194", "$195", "$196", "$197", "$198", "$199",
99    "$200", "$201", "$202", "$203", "$204", "$205", "$206", "$207", "$208", "$209",
100    "$210", "$211", "$212", "$213", "$214", "$215", "$216", "$217", "$218", "$219",
101    "$220", "$221", "$222", "$223", "$224", "$225", "$226", "$227", "$228", "$229",
102    "$230", "$231", "$232", "$233", "$234", "$235", "$236", "$237", "$238", "$239",
103    "$240", "$241", "$242", "$243", "$244", "$245", "$246", "$247", "$248", "$249",
104    "$250", "$251", "$252", "$253", "$254", "$255", "$256",
105];
106
107/// Pre-computed IN clause placeholder patterns for MySQL/SQLite.
108/// Format: "?, ?, ?, ..." for common sizes (1-32 elements).
109const MYSQL_IN_PATTERNS: &[&str] = &[
110    "",  // 0 (empty)
111    "?",
112    "?, ?",
113    "?, ?, ?",
114    "?, ?, ?, ?",
115    "?, ?, ?, ?, ?",
116    "?, ?, ?, ?, ?, ?",
117    "?, ?, ?, ?, ?, ?, ?",
118    "?, ?, ?, ?, ?, ?, ?, ?",
119    "?, ?, ?, ?, ?, ?, ?, ?, ?",
120    "?, ?, ?, ?, ?, ?, ?, ?, ?, ?",  // 10
121    "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?",
122    "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?",
123    "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?",
124    "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?",
125    "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?",
126    "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?",  // 16
127    "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?",
128    "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?",
129    "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?",
130    "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?",  // 20
131    "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?",
132    "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?",
133    "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?",
134    "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?",
135    "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?",  // 25
136    "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?",
137    "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?",
138    "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?",
139    "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?",
140    "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?",  // 30
141    "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?",
142    "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?",  // 32
143];
144
145impl DatabaseType {
146    /// Get the parameter placeholder for this database type.
147    ///
148    /// For MySQL and SQLite, this returns a borrowed static string (zero allocation).
149    /// For PostgreSQL with index 1-128, this returns a borrowed static string (zero allocation).
150    /// For PostgreSQL with index > 128, this returns an owned formatted string.
151    ///
152    /// # Examples
153    ///
154    /// ```rust
155    /// use prax_query::sql::DatabaseType;
156    ///
157    /// // PostgreSQL uses numbered placeholders (zero allocation for 1-128)
158    /// assert_eq!(DatabaseType::PostgreSQL.placeholder(1).as_ref(), "$1");
159    /// assert_eq!(DatabaseType::PostgreSQL.placeholder(5).as_ref(), "$5");
160    /// assert_eq!(DatabaseType::PostgreSQL.placeholder(100).as_ref(), "$100");
161    ///
162    /// // MySQL and SQLite use ? (zero allocation)
163    /// assert_eq!(DatabaseType::MySQL.placeholder(1).as_ref(), "?");
164    /// assert_eq!(DatabaseType::SQLite.placeholder(1).as_ref(), "?");
165    /// ```
166    #[inline]
167    pub fn placeholder(&self, index: usize) -> Cow<'static, str> {
168        match self {
169            Self::PostgreSQL => {
170                // Use pre-computed lookup for common indices (1-128)
171                if index > 0 && index < POSTGRES_PLACEHOLDERS.len() {
172                    Cow::Borrowed(POSTGRES_PLACEHOLDERS[index])
173                } else {
174                    // Fall back to format for rare cases (0 or > 128)
175                    Cow::Owned(format!("${}", index))
176                }
177            }
178            Self::MySQL | Self::SQLite => Cow::Borrowed(QUESTION_MARK_PLACEHOLDER),
179        }
180    }
181
182    /// Get the parameter placeholder as a String.
183    ///
184    /// This is a convenience method that always allocates. Prefer `placeholder()`
185    /// when you can work with `Cow<str>` to avoid unnecessary allocations.
186    #[inline]
187    pub fn placeholder_string(&self, index: usize) -> String {
188        self.placeholder(index).into_owned()
189    }
190}
191
192impl Default for DatabaseType {
193    fn default() -> Self {
194        Self::PostgreSQL
195    }
196}
197
198/// A SQL builder for constructing queries.
199#[derive(Debug, Clone)]
200pub struct SqlBuilder {
201    db_type: DatabaseType,
202    parts: Vec<String>,
203    params: Vec<FilterValue>,
204}
205
206impl SqlBuilder {
207    /// Create a new SQL builder.
208    pub fn new(db_type: DatabaseType) -> Self {
209        Self {
210            db_type,
211            parts: Vec::new(),
212            params: Vec::new(),
213        }
214    }
215
216    /// Create a PostgreSQL SQL builder.
217    pub fn postgres() -> Self {
218        Self::new(DatabaseType::PostgreSQL)
219    }
220
221    /// Create a MySQL SQL builder.
222    pub fn mysql() -> Self {
223        Self::new(DatabaseType::MySQL)
224    }
225
226    /// Create a SQLite SQL builder.
227    pub fn sqlite() -> Self {
228        Self::new(DatabaseType::SQLite)
229    }
230
231    /// Push a literal SQL string.
232    pub fn push(&mut self, sql: impl AsRef<str>) -> &mut Self {
233        self.parts.push(sql.as_ref().to_string());
234        self
235    }
236
237    /// Push a SQL string with a parameter.
238    pub fn push_param(&mut self, value: impl Into<FilterValue>) -> &mut Self {
239        let index = self.params.len() + 1;
240        // Use into_owned() since we need to store it in Vec<String>
241        // For MySQL/SQLite, this still benefits from the static str being used
242        self.parts.push(self.db_type.placeholder(index).into_owned());
243        self.params.push(value.into());
244        self
245    }
246
247    /// Push an identifier (properly quoted if needed).
248    pub fn push_identifier(&mut self, name: &str) -> &mut Self {
249        self.parts.push(quote_identifier(name));
250        self
251    }
252
253    /// Push a separator between parts.
254    pub fn push_sep(&mut self, sep: &str) -> &mut Self {
255        self.parts.push(sep.to_string());
256        self
257    }
258
259    /// Build the final SQL string and parameters.
260    pub fn build(self) -> (String, Vec<FilterValue>) {
261        (self.parts.join(""), self.params)
262    }
263
264    /// Get the current SQL string (without consuming).
265    pub fn sql(&self) -> String {
266        self.parts.join("")
267    }
268
269    /// Get the current parameters.
270    pub fn params(&self) -> &[FilterValue] {
271        &self.params
272    }
273
274    /// Get the next parameter index.
275    pub fn next_param_index(&self) -> usize {
276        self.params.len() + 1
277    }
278}
279
280impl Default for SqlBuilder {
281    fn default() -> Self {
282        Self::postgres()
283    }
284}
285
286// ==============================================================================
287// Optimized SQL Builder
288// ==============================================================================
289
290/// Capacity hints for different query types.
291#[derive(Debug, Clone, Copy)]
292pub enum QueryCapacity {
293    /// Simple SELECT query (e.g., SELECT * FROM users WHERE id = $1)
294    SimpleSelect,
295    /// SELECT with multiple conditions
296    SelectWithFilters(usize),
297    /// INSERT with N columns
298    Insert(usize),
299    /// UPDATE with N columns
300    Update(usize),
301    /// DELETE query
302    Delete,
303    /// Custom capacity
304    Custom(usize),
305}
306
307impl QueryCapacity {
308    /// Get the estimated capacity in bytes.
309    #[inline]
310    pub const fn estimate(&self) -> usize {
311        match self {
312            Self::SimpleSelect => 64,
313            Self::SelectWithFilters(n) => 64 + *n * 32,
314            Self::Insert(cols) => 32 + *cols * 16,
315            Self::Update(cols) => 32 + *cols * 20,
316            Self::Delete => 48,
317            Self::Custom(cap) => *cap,
318        }
319    }
320}
321
322/// An optimized SQL builder that uses a single String buffer.
323///
324/// This builder is more efficient than `Sql` for complex queries because:
325/// - Uses a single pre-allocated String instead of Vec<String>
326/// - Uses `write!` macro instead of format! + push
327/// - Provides batch placeholder generation for IN clauses
328///
329/// # Examples
330///
331/// ```rust
332/// use prax_query::sql::{FastSqlBuilder, DatabaseType, QueryCapacity};
333///
334/// // Simple query with pre-allocated capacity
335/// let mut builder = FastSqlBuilder::with_capacity(
336///     DatabaseType::PostgreSQL,
337///     QueryCapacity::SimpleSelect
338/// );
339/// builder.push_str("SELECT * FROM users WHERE id = ");
340/// builder.bind(42i64);
341/// let (sql, params) = builder.build();
342/// assert_eq!(sql, "SELECT * FROM users WHERE id = $1");
343///
344/// // Complex query with multiple bindings
345/// let mut builder = FastSqlBuilder::with_capacity(
346///     DatabaseType::PostgreSQL,
347///     QueryCapacity::SelectWithFilters(3)
348/// );
349/// builder.push_str("SELECT * FROM users WHERE active = ");
350/// builder.bind(true);
351/// builder.push_str(" AND age > ");
352/// builder.bind(18i64);
353/// builder.push_str(" ORDER BY created_at LIMIT ");
354/// builder.bind(10i64);
355/// let (sql, _) = builder.build();
356/// assert!(sql.contains("$1") && sql.contains("$2") && sql.contains("$3"));
357/// ```
358#[derive(Debug, Clone)]
359pub struct FastSqlBuilder {
360    /// The SQL string buffer.
361    buffer: String,
362    /// The parameter values.
363    params: Vec<FilterValue>,
364    /// The database type.
365    db_type: DatabaseType,
366}
367
368impl FastSqlBuilder {
369    /// Create a new builder with the specified database type.
370    #[inline]
371    pub fn new(db_type: DatabaseType) -> Self {
372        Self {
373            buffer: String::new(),
374            params: Vec::new(),
375            db_type,
376        }
377    }
378
379    /// Create a new builder with pre-allocated capacity.
380    #[inline]
381    pub fn with_capacity(db_type: DatabaseType, capacity: QueryCapacity) -> Self {
382        Self {
383            buffer: String::with_capacity(capacity.estimate()),
384            params: Vec::with_capacity(match capacity {
385                QueryCapacity::SimpleSelect => 2,
386                QueryCapacity::SelectWithFilters(n) => n,
387                QueryCapacity::Insert(n) => n,
388                QueryCapacity::Update(n) => n + 1,
389                QueryCapacity::Delete => 2,
390                QueryCapacity::Custom(n) => n / 16,
391            }),
392            db_type,
393        }
394    }
395
396    /// Create a PostgreSQL builder with pre-allocated capacity.
397    #[inline]
398    pub fn postgres(capacity: QueryCapacity) -> Self {
399        Self::with_capacity(DatabaseType::PostgreSQL, capacity)
400    }
401
402    /// Create a MySQL builder with pre-allocated capacity.
403    #[inline]
404    pub fn mysql(capacity: QueryCapacity) -> Self {
405        Self::with_capacity(DatabaseType::MySQL, capacity)
406    }
407
408    /// Create a SQLite builder with pre-allocated capacity.
409    #[inline]
410    pub fn sqlite(capacity: QueryCapacity) -> Self {
411        Self::with_capacity(DatabaseType::SQLite, capacity)
412    }
413
414    /// Push a string slice directly (zero allocation).
415    #[inline]
416    pub fn push_str(&mut self, s: &str) -> &mut Self {
417        self.buffer.push_str(s);
418        self
419    }
420
421    /// Push a single character.
422    #[inline]
423    pub fn push_char(&mut self, c: char) -> &mut Self {
424        self.buffer.push(c);
425        self
426    }
427
428    /// Bind a parameter and append its placeholder.
429    #[inline]
430    pub fn bind(&mut self, value: impl Into<FilterValue>) -> &mut Self {
431        let index = self.params.len() + 1;
432        let placeholder = self.db_type.placeholder(index);
433        self.buffer.push_str(&placeholder);
434        self.params.push(value.into());
435        self
436    }
437
438    /// Push a string and bind a value.
439    #[inline]
440    pub fn push_bind(&mut self, s: &str, value: impl Into<FilterValue>) -> &mut Self {
441        self.push_str(s);
442        self.bind(value)
443    }
444
445    /// Generate placeholders for an IN clause efficiently.
446    ///
447    /// This is much faster than calling `bind()` in a loop because it:
448    /// - Uses pre-computed placeholder patterns for common sizes
449    /// - Pre-calculates the total string length for larger sizes
450    /// - Generates all placeholders in one pass
451    ///
452    /// # Examples
453    ///
454    /// ```rust
455    /// use prax_query::sql::{FastSqlBuilder, DatabaseType, QueryCapacity};
456    /// use prax_query::filter::FilterValue;
457    ///
458    /// let mut builder = FastSqlBuilder::postgres(QueryCapacity::Custom(128));
459    /// builder.push_str("SELECT * FROM users WHERE id IN (");
460    ///
461    /// let values: Vec<FilterValue> = vec![1i64, 2, 3, 4, 5].into_iter()
462    ///     .map(FilterValue::Int)
463    ///     .collect();
464    /// builder.bind_in_clause(values);
465    /// builder.push_char(')');
466    ///
467    /// let (sql, params) = builder.build();
468    /// assert_eq!(sql, "SELECT * FROM users WHERE id IN ($1, $2, $3, $4, $5)");
469    /// assert_eq!(params.len(), 5);
470    /// ```
471    pub fn bind_in_clause(&mut self, values: impl IntoIterator<Item = FilterValue>) -> &mut Self {
472        let values: Vec<FilterValue> = values.into_iter().collect();
473        if values.is_empty() {
474            return self;
475        }
476
477        let start_index = self.params.len() + 1;
478        let count = values.len();
479
480        // Generate placeholders efficiently
481        match self.db_type {
482            DatabaseType::PostgreSQL => {
483                // Pre-calculate capacity: "$N, " is about 4-5 chars per param
484                let estimated_len = count * 5;
485                self.buffer.reserve(estimated_len);
486
487                for (i, _) in values.iter().enumerate() {
488                    if i > 0 {
489                        self.buffer.push_str(", ");
490                    }
491                    let idx = start_index + i;
492                    if idx < POSTGRES_PLACEHOLDERS.len() {
493                        self.buffer.push_str(POSTGRES_PLACEHOLDERS[idx]);
494                    } else {
495                        let _ = write!(self.buffer, "${}", idx);
496                    }
497                }
498            }
499            DatabaseType::MySQL | DatabaseType::SQLite => {
500                // Use pre-computed pattern for small sizes (up to 32)
501                if start_index == 1 && count < MYSQL_IN_PATTERNS.len() {
502                    self.buffer.push_str(MYSQL_IN_PATTERNS[count]);
503                } else {
504                    // Fall back to generation for larger sizes or offset start
505                    let estimated_len = count * 3; // "?, " per param
506                    self.buffer.reserve(estimated_len);
507                    for i in 0..count {
508                        if i > 0 {
509                            self.buffer.push_str(", ");
510                        }
511                        self.buffer.push('?');
512                    }
513                }
514            }
515        }
516
517        self.params.extend(values);
518        self
519    }
520
521    /// Bind a slice of values for an IN clause without collecting.
522    ///
523    /// This is more efficient than `bind_in_clause` when you already have a slice,
524    /// as it avoids collecting into a Vec first.
525    ///
526    /// # Examples
527    ///
528    /// ```rust
529    /// use prax_query::sql::{FastSqlBuilder, DatabaseType, QueryCapacity};
530    ///
531    /// let mut builder = FastSqlBuilder::postgres(QueryCapacity::Custom(128));
532    /// builder.push_str("SELECT * FROM users WHERE id IN (");
533    ///
534    /// let ids: &[i64] = &[1, 2, 3, 4, 5];
535    /// builder.bind_in_slice(ids);
536    /// builder.push_char(')');
537    ///
538    /// let (sql, params) = builder.build();
539    /// assert_eq!(sql, "SELECT * FROM users WHERE id IN ($1, $2, $3, $4, $5)");
540    /// assert_eq!(params.len(), 5);
541    /// ```
542    pub fn bind_in_slice<T: Into<FilterValue> + Clone>(&mut self, values: &[T]) -> &mut Self {
543        if values.is_empty() {
544            return self;
545        }
546
547        let start_index = self.params.len() + 1;
548        let count = values.len();
549
550        // Generate placeholders
551        match self.db_type {
552            DatabaseType::PostgreSQL => {
553                let estimated_len = count * 5;
554                self.buffer.reserve(estimated_len);
555
556                for i in 0..count {
557                    if i > 0 {
558                        self.buffer.push_str(", ");
559                    }
560                    let idx = start_index + i;
561                    if idx < POSTGRES_PLACEHOLDERS.len() {
562                        self.buffer.push_str(POSTGRES_PLACEHOLDERS[idx]);
563                    } else {
564                        let _ = write!(self.buffer, "${}", idx);
565                    }
566                }
567            }
568            DatabaseType::MySQL | DatabaseType::SQLite => {
569                if start_index == 1 && count < MYSQL_IN_PATTERNS.len() {
570                    self.buffer.push_str(MYSQL_IN_PATTERNS[count]);
571                } else {
572                    let estimated_len = count * 3;
573                    self.buffer.reserve(estimated_len);
574                    for i in 0..count {
575                        if i > 0 {
576                            self.buffer.push_str(", ");
577                        }
578                        self.buffer.push('?');
579                    }
580                }
581            }
582        }
583
584        // Add params
585        self.params.reserve(count);
586        for v in values {
587            self.params.push(v.clone().into());
588        }
589        self
590    }
591
592    /// Write formatted content using the `write!` macro.
593    ///
594    /// This is more efficient than `format!()` + `push_str()` as it
595    /// writes directly to the buffer without intermediate allocation.
596    #[inline]
597    pub fn write_fmt(&mut self, args: std::fmt::Arguments<'_>) -> &mut Self {
598        let _ = self.buffer.write_fmt(args);
599        self
600    }
601
602    /// Push an identifier, quoting if necessary.
603    #[inline]
604    pub fn push_identifier(&mut self, name: &str) -> &mut Self {
605        if needs_quoting(name) {
606            self.buffer.push('"');
607            // Escape any existing quotes
608            for c in name.chars() {
609                if c == '"' {
610                    self.buffer.push_str("\"\"");
611                } else {
612                    self.buffer.push(c);
613                }
614            }
615            self.buffer.push('"');
616        } else {
617            self.buffer.push_str(name);
618        }
619        self
620    }
621
622    /// Push conditionally.
623    #[inline]
624    pub fn push_if(&mut self, condition: bool, s: &str) -> &mut Self {
625        if condition {
626            self.push_str(s);
627        }
628        self
629    }
630
631    /// Bind conditionally.
632    #[inline]
633    pub fn bind_if(&mut self, condition: bool, value: impl Into<FilterValue>) -> &mut Self {
634        if condition {
635            self.bind(value);
636        }
637        self
638    }
639
640    /// Get the current SQL string.
641    #[inline]
642    pub fn sql(&self) -> &str {
643        &self.buffer
644    }
645
646    /// Get the current parameters.
647    #[inline]
648    pub fn params(&self) -> &[FilterValue] {
649        &self.params
650    }
651
652    /// Get the number of parameters.
653    #[inline]
654    pub fn param_count(&self) -> usize {
655        self.params.len()
656    }
657
658    /// Build the final SQL string and parameters.
659    #[inline]
660    pub fn build(self) -> (String, Vec<FilterValue>) {
661        let sql_len = self.buffer.len();
662        let param_count = self.params.len();
663        debug!(sql_len, param_count, db_type = ?self.db_type, "FastSqlBuilder::build()");
664        (self.buffer, self.params)
665    }
666
667    /// Build and return only the SQL string.
668    #[inline]
669    pub fn build_sql(self) -> String {
670        self.buffer
671    }
672}
673
674// ==============================================================================
675// SQL Templates for Common Queries
676// ==============================================================================
677
678/// Pre-built SQL templates for common query patterns.
679///
680/// Using templates avoids repeated string construction for common operations.
681pub mod templates {
682    use super::*;
683
684    /// Generate a simple SELECT query template.
685    ///
686    /// # Examples
687    ///
688    /// ```rust
689    /// use prax_query::sql::templates;
690    ///
691    /// let template = templates::select_by_id("users", &["id", "name", "email"]);
692    /// assert!(template.contains("SELECT"));
693    /// assert!(template.contains("FROM users"));
694    /// assert!(template.contains("WHERE id = $1"));
695    /// ```
696    pub fn select_by_id(table: &str, columns: &[&str]) -> String {
697        let cols = if columns.is_empty() {
698            "*".to_string()
699        } else {
700            columns.join(", ")
701        };
702        format!("SELECT {} FROM {} WHERE id = $1", cols, table)
703    }
704
705    /// Generate an INSERT query template for PostgreSQL.
706    ///
707    /// # Examples
708    ///
709    /// ```rust
710    /// use prax_query::sql::templates;
711    ///
712    /// let template = templates::insert_returning("users", &["name", "email"]);
713    /// assert!(template.contains("INSERT INTO users"));
714    /// assert!(template.contains("RETURNING *"));
715    /// ```
716    pub fn insert_returning(table: &str, columns: &[&str]) -> String {
717        let cols = columns.join(", ");
718        let placeholders: Vec<String> = (1..=columns.len())
719            .map(|i| {
720                if i < POSTGRES_PLACEHOLDERS.len() {
721                    POSTGRES_PLACEHOLDERS[i].to_string()
722                } else {
723                    format!("${}", i)
724                }
725            })
726            .collect();
727        format!(
728            "INSERT INTO {} ({}) VALUES ({}) RETURNING *",
729            table,
730            cols,
731            placeholders.join(", ")
732        )
733    }
734
735    /// Generate an UPDATE query template.
736    ///
737    /// # Examples
738    ///
739    /// ```rust
740    /// use prax_query::sql::templates;
741    ///
742    /// let template = templates::update_by_id("users", &["name", "email"]);
743    /// assert!(template.contains("UPDATE users SET"));
744    /// assert!(template.contains("WHERE id = $3"));
745    /// ```
746    pub fn update_by_id(table: &str, columns: &[&str]) -> String {
747        let sets: Vec<String> = columns
748            .iter()
749            .enumerate()
750            .map(|(i, col)| {
751                let idx = i + 1;
752                if idx < POSTGRES_PLACEHOLDERS.len() {
753                    format!("{} = {}", col, POSTGRES_PLACEHOLDERS[idx])
754                } else {
755                    format!("{} = ${}", col, idx)
756                }
757            })
758            .collect();
759        let id_idx = columns.len() + 1;
760        let id_placeholder = if id_idx < POSTGRES_PLACEHOLDERS.len() {
761            POSTGRES_PLACEHOLDERS[id_idx]
762        } else {
763            "$?"
764        };
765        format!(
766            "UPDATE {} SET {} WHERE id = {}",
767            table,
768            sets.join(", "),
769            id_placeholder
770        )
771    }
772
773    /// Generate a DELETE query template.
774    ///
775    /// # Examples
776    ///
777    /// ```rust
778    /// use prax_query::sql::templates;
779    ///
780    /// let template = templates::delete_by_id("users");
781    /// assert_eq!(template, "DELETE FROM users WHERE id = $1");
782    /// ```
783    pub fn delete_by_id(table: &str) -> String {
784        format!("DELETE FROM {} WHERE id = $1", table)
785    }
786
787    /// Generate placeholders for a batch INSERT.
788    ///
789    /// # Examples
790    ///
791    /// ```rust
792    /// use prax_query::sql::templates;
793    /// use prax_query::sql::DatabaseType;
794    ///
795    /// let placeholders = templates::batch_placeholders(DatabaseType::PostgreSQL, 3, 2);
796    /// assert_eq!(placeholders, "($1, $2, $3), ($4, $5, $6)");
797    /// ```
798    pub fn batch_placeholders(db_type: DatabaseType, columns: usize, rows: usize) -> String {
799        let mut result = String::with_capacity(rows * columns * 4);
800        let mut param_idx = 1;
801
802        for row in 0..rows {
803            if row > 0 {
804                result.push_str(", ");
805            }
806            result.push('(');
807            for col in 0..columns {
808                if col > 0 {
809                    result.push_str(", ");
810                }
811                match db_type {
812                    DatabaseType::PostgreSQL => {
813                        if param_idx < POSTGRES_PLACEHOLDERS.len() {
814                            result.push_str(POSTGRES_PLACEHOLDERS[param_idx]);
815                        } else {
816                            let _ = write!(result, "${}", param_idx);
817                        }
818                        param_idx += 1;
819                    }
820                    DatabaseType::MySQL | DatabaseType::SQLite => {
821                        result.push('?');
822                    }
823                }
824            }
825            result.push(')');
826        }
827
828        result
829    }
830}
831
832#[cfg(test)]
833mod tests {
834    use super::*;
835
836    #[test]
837    fn test_escape_identifier() {
838        assert_eq!(escape_identifier("user"), "\"user\"");
839        assert_eq!(escape_identifier("my_table"), "\"my_table\"");
840        assert_eq!(escape_identifier("has\"quote"), "\"has\"\"quote\"");
841    }
842
843    #[test]
844    fn test_needs_quoting() {
845        assert!(needs_quoting("user"));
846        assert!(needs_quoting("order"));
847        assert!(needs_quoting("has space"));
848        assert!(!needs_quoting("my_table"));
849        assert!(!needs_quoting("users"));
850    }
851
852    #[test]
853    fn test_quote_identifier() {
854        assert_eq!(quote_identifier("user"), "\"user\"");
855        assert_eq!(quote_identifier("my_table"), "my_table");
856    }
857
858    #[test]
859    fn test_database_placeholder() {
860        // Basic placeholder values
861        assert_eq!(DatabaseType::PostgreSQL.placeholder(1).as_ref(), "$1");
862        assert_eq!(DatabaseType::PostgreSQL.placeholder(5).as_ref(), "$5");
863        assert_eq!(DatabaseType::PostgreSQL.placeholder(100).as_ref(), "$100");
864        assert_eq!(DatabaseType::PostgreSQL.placeholder(128).as_ref(), "$128");
865        assert_eq!(DatabaseType::PostgreSQL.placeholder(256).as_ref(), "$256");
866        assert_eq!(DatabaseType::MySQL.placeholder(1).as_ref(), "?");
867        assert_eq!(DatabaseType::SQLite.placeholder(1).as_ref(), "?");
868
869        // Verify MySQL/SQLite return borrowed (zero allocation)
870        assert!(matches!(DatabaseType::MySQL.placeholder(1), Cow::Borrowed(_)));
871        assert!(matches!(DatabaseType::SQLite.placeholder(1), Cow::Borrowed(_)));
872
873        // PostgreSQL returns borrowed for indices 1-256 (zero allocation via lookup table)
874        assert!(matches!(DatabaseType::PostgreSQL.placeholder(1), Cow::Borrowed(_)));
875        assert!(matches!(DatabaseType::PostgreSQL.placeholder(50), Cow::Borrowed(_)));
876        assert!(matches!(DatabaseType::PostgreSQL.placeholder(128), Cow::Borrowed(_)));
877        assert!(matches!(DatabaseType::PostgreSQL.placeholder(256), Cow::Borrowed(_)));
878
879        // PostgreSQL returns owned for indices > 256 (must format)
880        assert!(matches!(DatabaseType::PostgreSQL.placeholder(257), Cow::Owned(_)));
881        assert_eq!(DatabaseType::PostgreSQL.placeholder(257).as_ref(), "$257");
882        assert_eq!(DatabaseType::PostgreSQL.placeholder(200).as_ref(), "$200");
883
884        // Edge case: index 0 falls back to format (unusual but handled)
885        assert!(matches!(DatabaseType::PostgreSQL.placeholder(0), Cow::Owned(_)));
886        assert_eq!(DatabaseType::PostgreSQL.placeholder(0).as_ref(), "$0");
887    }
888
889    #[test]
890    fn test_sql_builder() {
891        let mut builder = SqlBuilder::postgres();
892        builder
893            .push("SELECT * FROM ")
894            .push_identifier("user")
895            .push(" WHERE ")
896            .push_identifier("id")
897            .push(" = ")
898            .push_param(42i32);
899
900        let (sql, params) = builder.build();
901        assert_eq!(sql, "SELECT * FROM \"user\" WHERE id = $1");
902        assert_eq!(params.len(), 1);
903    }
904
905    // FastSqlBuilder tests
906    #[test]
907    fn test_fast_builder_simple() {
908        let mut builder = FastSqlBuilder::postgres(QueryCapacity::SimpleSelect);
909        builder.push_str("SELECT * FROM users WHERE id = ");
910        builder.bind(42i64);
911        let (sql, params) = builder.build();
912        assert_eq!(sql, "SELECT * FROM users WHERE id = $1");
913        assert_eq!(params.len(), 1);
914    }
915
916    #[test]
917    fn test_fast_builder_complex() {
918        let mut builder = FastSqlBuilder::with_capacity(
919            DatabaseType::PostgreSQL,
920            QueryCapacity::SelectWithFilters(5),
921        );
922        builder
923            .push_str("SELECT * FROM users WHERE active = ")
924            .bind(true)
925            .push_str(" AND age > ")
926            .bind(18i64)
927            .push_str(" AND status = ")
928            .bind("approved")
929            .push_str(" ORDER BY created_at LIMIT ")
930            .bind(10i64);
931
932        let (sql, params) = builder.build();
933        assert!(sql.contains("$1"));
934        assert!(sql.contains("$4"));
935        assert_eq!(params.len(), 4);
936    }
937
938    #[test]
939    fn test_fast_builder_in_clause_postgres() {
940        let mut builder = FastSqlBuilder::postgres(QueryCapacity::Custom(128));
941        builder.push_str("SELECT * FROM users WHERE id IN (");
942        let values: Vec<FilterValue> = (1..=5).map(|i| FilterValue::Int(i)).collect();
943        builder.bind_in_clause(values);
944        builder.push_char(')');
945
946        let (sql, params) = builder.build();
947        assert_eq!(sql, "SELECT * FROM users WHERE id IN ($1, $2, $3, $4, $5)");
948        assert_eq!(params.len(), 5);
949    }
950
951    #[test]
952    fn test_fast_builder_in_clause_mysql() {
953        let mut builder = FastSqlBuilder::mysql(QueryCapacity::Custom(128));
954        builder.push_str("SELECT * FROM users WHERE id IN (");
955        let values: Vec<FilterValue> = (1..=5).map(|i| FilterValue::Int(i)).collect();
956        builder.bind_in_clause(values);
957        builder.push_char(')');
958
959        let (sql, params) = builder.build();
960        assert_eq!(sql, "SELECT * FROM users WHERE id IN (?, ?, ?, ?, ?)");
961        assert_eq!(params.len(), 5);
962    }
963
964    #[test]
965    fn test_fast_builder_identifier() {
966        let mut builder = FastSqlBuilder::postgres(QueryCapacity::SimpleSelect);
967        builder.push_str("SELECT * FROM ");
968        builder.push_identifier("user"); // reserved word
969        builder.push_str(" WHERE ");
970        builder.push_identifier("my_column"); // not reserved
971        builder.push_str(" = ");
972        builder.bind(1i64);
973
974        let (sql, _) = builder.build();
975        assert_eq!(sql, "SELECT * FROM \"user\" WHERE my_column = $1");
976    }
977
978    #[test]
979    fn test_fast_builder_identifier_with_quotes() {
980        let mut builder = FastSqlBuilder::postgres(QueryCapacity::SimpleSelect);
981        builder.push_str("SELECT * FROM ");
982        builder.push_identifier("has\"quote");
983
984        let sql = builder.build_sql();
985        assert_eq!(sql, "SELECT * FROM \"has\"\"quote\"");
986    }
987
988    #[test]
989    fn test_fast_builder_conditional() {
990        let mut builder = FastSqlBuilder::postgres(QueryCapacity::SelectWithFilters(2));
991        builder.push_str("SELECT * FROM users WHERE 1=1");
992        builder.push_if(true, " AND active = true");
993        builder.push_if(false, " AND deleted = false");
994
995        let sql = builder.build_sql();
996        assert_eq!(sql, "SELECT * FROM users WHERE 1=1 AND active = true");
997    }
998
999    // Template tests
1000    #[test]
1001    fn test_template_select_by_id() {
1002        let sql = templates::select_by_id("users", &["id", "name", "email"]);
1003        assert_eq!(sql, "SELECT id, name, email FROM users WHERE id = $1");
1004    }
1005
1006    #[test]
1007    fn test_template_select_by_id_all_columns() {
1008        let sql = templates::select_by_id("users", &[]);
1009        assert_eq!(sql, "SELECT * FROM users WHERE id = $1");
1010    }
1011
1012    #[test]
1013    fn test_template_insert_returning() {
1014        let sql = templates::insert_returning("users", &["name", "email"]);
1015        assert_eq!(
1016            sql,
1017            "INSERT INTO users (name, email) VALUES ($1, $2) RETURNING *"
1018        );
1019    }
1020
1021    #[test]
1022    fn test_template_update_by_id() {
1023        let sql = templates::update_by_id("users", &["name", "email"]);
1024        assert_eq!(sql, "UPDATE users SET name = $1, email = $2 WHERE id = $3");
1025    }
1026
1027    #[test]
1028    fn test_template_delete_by_id() {
1029        let sql = templates::delete_by_id("users");
1030        assert_eq!(sql, "DELETE FROM users WHERE id = $1");
1031    }
1032
1033    #[test]
1034    fn test_template_batch_placeholders_postgres() {
1035        let sql = templates::batch_placeholders(DatabaseType::PostgreSQL, 3, 2);
1036        assert_eq!(sql, "($1, $2, $3), ($4, $5, $6)");
1037    }
1038
1039    #[test]
1040    fn test_template_batch_placeholders_mysql() {
1041        let sql = templates::batch_placeholders(DatabaseType::MySQL, 3, 2);
1042        assert_eq!(sql, "(?, ?, ?), (?, ?, ?)");
1043    }
1044
1045    #[test]
1046    fn test_query_capacity_estimates() {
1047        assert_eq!(QueryCapacity::SimpleSelect.estimate(), 64);
1048        assert_eq!(QueryCapacity::SelectWithFilters(5).estimate(), 64 + 5 * 32);
1049        assert_eq!(QueryCapacity::Insert(10).estimate(), 32 + 10 * 16);
1050        assert_eq!(QueryCapacity::Update(5).estimate(), 32 + 5 * 20);
1051        assert_eq!(QueryCapacity::Delete.estimate(), 48);
1052        assert_eq!(QueryCapacity::Custom(256).estimate(), 256);
1053    }
1054}
1055
prax_query/sql.rs

prax_query/
sql.rs
