postgrest_parser/sql/
builder.rs

1use crate::ast::*;
2use crate::error::SqlError;
3
4/// Result of building a SQL query with parameterized values.
5///
6/// Contains the final SQL query string, parameter values (for use with prepared statements),
7/// and a list of tables referenced in the query.
8///
9/// # SQL Injection Prevention
10///
11/// All user input is passed via parameters, never interpolated into SQL strings.
12/// This provides complete protection against SQL injection attacks.
13///
14/// # Examples
15///
16/// ```
17/// use postgrest_parser::{query_string_to_sql, QueryResult};
18///
19/// let result: QueryResult = query_string_to_sql(
20///     "users",
21///     "age=gte.18&status=eq.active&order=name.asc&limit=10"
22/// ).unwrap();
23///
24/// // SQL with parameter placeholders
25/// assert!(result.query.contains("$1"));
26/// assert!(result.query.contains("$2"));
27///
28/// // Actual parameter values (age, status, limit - order is not a param)
29/// assert_eq!(result.params.len(), 3);
30///
31/// // Tables referenced
32/// assert_eq!(result.tables, vec!["users"]);
33/// ```
34#[derive(Debug, Clone, serde::Serialize)]
35#[serde(rename_all = "camelCase")]
36pub struct QueryResult {
37    /// The generated SQL query with parameter placeholders ($1, $2, etc.)
38    pub query: String,
39    /// Parameter values in order matching the placeholders
40    pub params: Vec<serde_json::Value>,
41    /// List of table names referenced in the query
42    pub tables: Vec<String>,
43}
44
45/// Builder for generating parameterized PostgreSQL SQL queries.
46///
47/// Converts PostgREST AST types into safe, parameterized SQL queries.
48/// All user input is placed in parameters to prevent SQL injection.
49///
50/// # Examples
51///
52/// ```
53/// use postgrest_parser::{QueryBuilder, parse_query_string};
54///
55/// let params = parse_query_string("age=gte.18&status=eq.active").unwrap();
56/// let mut builder = QueryBuilder::new();
57/// let result = builder.build_select("users", &params).unwrap();
58///
59/// assert!(result.query.contains("SELECT"));
60/// assert!(result.query.contains("WHERE"));
61/// assert_eq!(result.params.len(), 2);
62/// ```
63pub struct QueryBuilder {
64    /// The SQL query being built
65    pub sql: String,
66    /// Parameter values for the query
67    pub params: Vec<serde_json::Value>,
68    /// Current parameter index (for $1, $2, etc.)
69    pub param_index: usize,
70    /// Tables referenced in the query
71    pub tables: Vec<String>,
72    /// Optional schema cache for relation resolution
73    #[cfg(any(feature = "postgres", feature = "wasm"))]
74    pub schema_cache: Option<std::sync::Arc<crate::schema_cache::SchemaCache>>,
75    /// Current schema being queried (for relation resolution)
76    pub current_schema: String,
77}
78
79impl Default for QueryBuilder {
80    fn default() -> Self {
81        Self::new()
82    }
83}
84
85impl QueryBuilder {
86    /// Creates a new empty query builder.
87    pub fn new() -> Self {
88        Self {
89            sql: String::new(),
90            params: Vec::new(),
91            param_index: 0,
92            tables: Vec::new(),
93            #[cfg(any(feature = "postgres", feature = "wasm"))]
94            schema_cache: None,
95            current_schema: "public".to_string(),
96        }
97    }
98
99    /// Sets the schema cache for relation resolution
100    #[cfg(any(feature = "postgres", feature = "wasm"))]
101    pub fn with_schema_cache(
102        mut self,
103        cache: std::sync::Arc<crate::schema_cache::SchemaCache>,
104    ) -> Self {
105        self.schema_cache = Some(cache);
106        self
107    }
108
109    /// Sets the current schema
110    pub fn with_schema(mut self, schema: impl Into<String>) -> Self {
111        self.current_schema = schema.into();
112        self
113    }
114
115    /// Builds a SELECT query from parsed parameters.
116    ///
117    /// # Examples
118    ///
119    /// ```
120    /// use postgrest_parser::{QueryBuilder, ParsedParams, parse_filter, LogicCondition};
121    ///
122    /// let filter = parse_filter("age", "gte.21").unwrap();
123    /// let params = ParsedParams::new()
124    ///     .with_filters(vec![LogicCondition::Filter(filter)]);
125    ///
126    /// let mut builder = QueryBuilder::new();
127    /// let result = builder.build_select("users", &params).unwrap();
128    ///
129    /// assert!(result.query.contains("SELECT * FROM"));
130    /// assert!(result.query.contains("WHERE"));
131    /// ```
132    pub fn build_select(
133        &mut self,
134        table: &str,
135        params: &ParsedParams,
136    ) -> Result<QueryResult, SqlError> {
137        self.tables.push(table.to_string());
138
139        if let Some(select) = &params.select {
140            self.build_select_clause(select)?;
141        } else {
142            self.sql.push_str("SELECT *");
143        }
144
145        self.build_from_clause(table)?;
146
147        if !params.filters.is_empty() {
148            self.build_where_clause(&params.filters)?;
149        }
150
151        if !params.order.is_empty() {
152            self.build_order_clause(&params.order)?;
153        }
154
155        self.build_limit_offset(params.limit, params.offset)?;
156
157        Ok(QueryResult {
158            query: self.sql.clone(),
159            params: self.params.clone(),
160            tables: self.tables.clone(),
161        })
162    }
163
164    pub(crate) fn build_select_clause(&mut self, items: &[SelectItem]) -> Result<(), SqlError> {
165        if items.is_empty() {
166            return Err(SqlError::NoSelectItems);
167        }
168
169        let columns: Vec<String> = items
170            .iter()
171            .map(|item| self.select_item_to_sql(item))
172            .collect::<Result<Vec<_>, _>>()?;
173        self.sql.push_str("SELECT ");
174        self.sql.push_str(&columns.join(", "));
175        Ok(())
176    }
177
178    fn select_item_to_sql(&self, item: &SelectItem) -> Result<String, SqlError> {
179        match item.item_type {
180            ItemType::Field => {
181                if item.name == "*" {
182                    Ok("*".to_string())
183                } else {
184                    let field_sql = self.field_to_sql(&Field::new(&item.name));
185                    if let Some(alias) = &item.alias {
186                        Ok(format!("{} AS {}", field_sql, self.quote_identifier(alias)))
187                    } else {
188                        Ok(field_sql)
189                    }
190                }
191            }
192            ItemType::Relation | ItemType::Spread => self.build_relation_sql(item),
193        }
194    }
195
196    fn build_relation_sql(&self, item: &SelectItem) -> Result<String, SqlError> {
197        #[cfg(any(feature = "postgres", feature = "wasm"))]
198        if let Some(cache) = &self.schema_cache {
199            let rel_table = &item.name;
200            let current_table = self.tables.last().ok_or(SqlError::NoTableContext)?;
201
202            return match cache.find_relationship(&self.current_schema, current_table, rel_table) {
203                Some(rel) => self.build_relation_with_fk(item, &rel),
204                None => Err(SqlError::RelationNotFound {
205                    from_table: current_table.clone(),
206                    to_table: rel_table.clone(),
207                }),
208            };
209        }
210
211        // Without schema cache: generate placeholder subquery.
212        // Maintains backward compatibility but produces non-functional SQL.
213        self.build_relation_placeholder(item)
214    }
215
216    #[cfg(any(feature = "postgres", feature = "wasm"))]
217    fn build_relation_with_fk(
218        &self,
219        item: &SelectItem,
220        rel: &crate::schema_cache::Relationship,
221    ) -> Result<String, SqlError> {
222        use crate::schema_cache::RelationType;
223
224        let rel_table = &item.name;
225        let current_table = self.tables.last().unwrap();
226
227        // Build column list for the subquery
228        let column_list = if let Some(children) = &item.children {
229            children
230                .iter()
231                .filter(|c| c.item_type == ItemType::Field)
232                .map(|c| {
233                    if c.name == "*" {
234                        "*".to_string()
235                    } else {
236                        self.quote_identifier(&c.name)
237                    }
238                })
239                .collect::<Vec<_>>()
240                .join(", ")
241        } else {
242            "*".to_string()
243        };
244
245        match rel.relation_type {
246            RelationType::ManyToOne => {
247                // orders.customer_id -> customers.id
248                // Generate: (SELECT row_to_json(c) FROM customers c WHERE c.id = orders.customer_id)
249                Ok(format!(
250                    "COALESCE((SELECT row_to_json({}_1) FROM (SELECT {} FROM {} {} WHERE {}) {}_1), 'null'::json) AS {}",
251                    rel_table,
252                    column_list,
253                    self.quote_identifier(rel_table),
254                    rel_table,
255                    rel.foreign_key.join_condition(current_table, rel_table),
256                    rel_table,
257                    self.quote_identifier(rel_table)
258                ))
259            }
260            RelationType::OneToMany => {
261                // customers.id <- orders.customer_id
262                // Generate: (SELECT COALESCE(json_agg(o), '[]') FROM orders o WHERE o.customer_id = customers.id)
263                Ok(format!(
264                    "COALESCE((SELECT json_agg({}_1) FROM (SELECT {} FROM {} {} WHERE {}) {}_1), '[]'::json) AS {}",
265                    rel_table,
266                    column_list,
267                    self.quote_identifier(rel_table),
268                    rel_table,
269                    rel.foreign_key.join_condition(rel_table, current_table),
270                    rel_table,
271                    self.quote_identifier(rel_table)
272                ))
273            }
274            RelationType::ManyToMany { ref junction_table } => {
275                // TODO: Implement M2M through junction tables
276                Err(SqlError::ManyToManyNotYetSupported {
277                    junction_table: junction_table.clone(),
278                })
279            }
280        }
281    }
282
283    fn build_relation_placeholder(&self, item: &SelectItem) -> Result<String, SqlError> {
284        let rel_alias = &item.name;
285
286        if let Some(children) = &item.children {
287            let child_columns: Vec<String> = children
288                .iter()
289                .filter(|c| c.item_type == ItemType::Field)
290                .map(|c| {
291                    if c.name == "*" {
292                        format!("{}.*", rel_alias)
293                    } else {
294                        format!("{}.{}", rel_alias, self.quote_identifier(&c.name))
295                    }
296                })
297                .collect();
298
299            if child_columns.is_empty() {
300                Ok(format!(
301                    "(SELECT json_agg(row_to_json({}.*)) AS {} FROM {})",
302                    rel_alias, rel_alias, rel_alias
303                ))
304            } else {
305                Ok(format!(
306                    "(SELECT json_agg(row_to_json({rel_alias}_sub)) FROM (SELECT {columns} FROM {rel_alias}) {rel_alias}_sub) AS {rel_alias}",
307                    rel_alias = rel_alias,
308                    columns = child_columns.join(", "),
309                ))
310            }
311        } else {
312            Ok(format!(
313                "(SELECT json_agg(row_to_json({}.*)) AS {} FROM {})",
314                rel_alias, rel_alias, rel_alias
315            ))
316        }
317    }
318
319    fn build_from_clause(&mut self, table: &str) -> Result<(), SqlError> {
320        if table.is_empty() {
321            return Err(SqlError::EmptyTableName);
322        }
323
324        self.sql.push_str(" FROM ");
325        self.sql.push_str(&self.quote_identifier(table));
326        Ok(())
327    }
328
329    pub fn build_where_clause(&mut self, filters: &[LogicCondition]) -> Result<(), SqlError> {
330        self.sql.push_str(" WHERE ");
331
332        let clauses: Result<Vec<String>, SqlError> = filters
333            .iter()
334            .map(|filter| self.build_filter(filter))
335            .collect();
336
337        match clauses {
338            Ok(clauses) => {
339                self.sql.push_str(&clauses.join(" AND "));
340                Ok(())
341            }
342            Err(e) => Err(e),
343        }
344    }
345
346    pub(crate) fn build_filter(&mut self, condition: &LogicCondition) -> Result<String, SqlError> {
347        match condition {
348            LogicCondition::Filter(filter) => self.build_single_filter(filter),
349            LogicCondition::Logic(tree) => self.build_logic_tree(tree),
350        }
351    }
352
353    fn build_single_filter(&mut self, filter: &Filter) -> Result<String, SqlError> {
354        let field_sql = self.field_to_sql(&filter.field);
355        let (clause, _) = self.operator_to_sql(&field_sql, filter)?;
356        Ok(clause)
357    }
358
359    fn build_logic_tree(&mut self, tree: &LogicTree) -> Result<String, SqlError> {
360        let joiner = if tree.operator == LogicOperator::And {
361            " AND "
362        } else {
363            " OR "
364        };
365
366        let conditions: Result<Vec<String>, SqlError> = tree
367            .conditions
368            .iter()
369            .map(|c| self.build_filter(c))
370            .collect();
371
372        let conditions_sql = conditions?.join(joiner);
373
374        if tree.negated {
375            Ok(format!("NOT ({})", conditions_sql))
376        } else {
377            Ok(format!("({})", conditions_sql))
378        }
379    }
380
381    pub(crate) fn build_order_clause(&mut self, order_terms: &[OrderTerm]) -> Result<(), SqlError> {
382        let clauses: Result<Vec<String>, SqlError> = order_terms
383            .iter()
384            .map(|term| self.order_term_to_sql(term))
385            .collect();
386
387        match clauses {
388            Ok(clauses) => {
389                self.sql.push_str(" ORDER BY ");
390                self.sql.push_str(&clauses.join(", "));
391                Ok(())
392            }
393            Err(e) => Err(e),
394        }
395    }
396
397    fn order_term_to_sql(&self, term: &OrderTerm) -> Result<String, SqlError> {
398        let field_sql = self.field_to_sql(&term.field);
399        let dir_sql = if term.direction == Direction::Desc {
400            " DESC"
401        } else {
402            " ASC"
403        };
404
405        let nulls_sql = match term.nulls {
406            Some(Nulls::First) => " NULLS FIRST",
407            Some(Nulls::Last) => " NULLS LAST",
408            None => "",
409        };
410
411        Ok(format!("{}{}{}", field_sql, dir_sql, nulls_sql))
412    }
413
414    pub(crate) fn build_limit_offset(
415        &mut self,
416        limit: Option<u64>,
417        offset: Option<u64>,
418    ) -> Result<(), SqlError> {
419        match (limit, offset) {
420            (Some(lim), Some(off)) => {
421                let lim_ref = self.add_param(serde_json::Value::Number(lim.into()));
422                let off_ref = self.add_param(serde_json::Value::Number(off.into()));
423                self.sql
424                    .push_str(&format!(" LIMIT {} OFFSET {}", lim_ref, off_ref));
425            }
426            (Some(lim), None) => {
427                let lim_ref = self.add_param(serde_json::Value::Number(lim.into()));
428                self.sql.push_str(&format!(" LIMIT {}", lim_ref));
429            }
430            (None, Some(off)) => {
431                let off_ref = self.add_param(serde_json::Value::Number(off.into()));
432                self.sql.push_str(&format!(" OFFSET {}", off_ref));
433            }
434            (None, None) => {}
435        }
436
437        Ok(())
438    }
439
440    fn operator_to_sql(
441        &mut self,
442        field: &str,
443        filter: &Filter,
444    ) -> Result<(String, usize), SqlError> {
445        let (op_sql, value) = match (&filter.operator, &filter.quantifier, &filter.value) {
446            // Eq with quantifiers
447            (FilterOperator::Eq, Some(Quantifier::Any), FilterValue::List(ref _vals)) => {
448                let param_ref = self.add_param(filter.value.to_json());
449                (format!("{} = ANY({})", field, param_ref), param_ref.len())
450            }
451            (FilterOperator::Eq, Some(Quantifier::All), FilterValue::List(ref _vals)) => {
452                let param_ref = self.add_param(filter.value.to_json());
453                (format!("{} = ALL({})", field, param_ref), param_ref.len())
454            }
455            (FilterOperator::Eq, _, FilterValue::Single(ref _val)) => {
456                let param_ref = self.add_param(filter.value.to_json());
457                let op_sql = if filter.negated { "<>" } else { "=" };
458                (format!("{} {} {}", field, op_sql, param_ref), 1)
459            }
460
461            // Neq
462            (FilterOperator::Neq, _, FilterValue::Single(_)) => {
463                let param_ref = self.add_param(filter.value.to_json());
464                let op_sql = if filter.negated { "=" } else { "<>" };
465                (format!("{} {} {}", field, op_sql, param_ref), 1)
466            }
467
468            // Comparison operators
469            (FilterOperator::Gt, _, FilterValue::Single(_)) => {
470                let param_ref = self.add_param(filter.value.to_json());
471                let op_sql = if filter.negated { "<=" } else { ">" };
472                (format!("{} {} {}", field, op_sql, param_ref), 1)
473            }
474            (FilterOperator::Gte, _, FilterValue::Single(_)) => {
475                let param_ref = self.add_param(filter.value.to_json());
476                let op_sql = if filter.negated { "<" } else { ">=" };
477                (format!("{} {} {}", field, op_sql, param_ref), 1)
478            }
479            (FilterOperator::Lt, _, FilterValue::Single(_)) => {
480                let param_ref = self.add_param(filter.value.to_json());
481                let op_sql = if filter.negated { ">=" } else { "<" };
482                (format!("{} {} {}", field, op_sql, param_ref), 1)
483            }
484            (FilterOperator::Lte, _, FilterValue::Single(_)) => {
485                let param_ref = self.add_param(filter.value.to_json());
486                let op_sql = if filter.negated { ">" } else { "<=" };
487                (format!("{} {} {}", field, op_sql, param_ref), 1)
488            }
489
490            // IN operator
491            (FilterOperator::In, _, FilterValue::List(_)) => {
492                let param_ref = self.add_param(filter.value.to_json());
493                let not_prefix = if filter.negated { "NOT " } else { "" };
494                (format!("{}{} = ANY({})", field, not_prefix, param_ref), 1)
495            }
496
497            // IS operator
498            (FilterOperator::Is, _, FilterValue::Single(ref val)) => {
499                let clause = self.build_is_clause(field, val, filter.negated)?;
500                (clause, 0)
501            }
502
503            // LIKE/ILIKE operators
504            (
505                FilterOperator::Like | FilterOperator::Ilike,
506                Some(Quantifier::Any),
507                FilterValue::List(_),
508            ) => {
509                let param_ref = self.add_param(filter.value.to_json());
510                let not_prefix = if filter.negated { "NOT " } else { "" };
511                let op_str = if filter.operator == FilterOperator::Like {
512                    "LIKE"
513                } else {
514                    "ILIKE"
515                };
516                (
517                    format!("{}{} {} ANY({})", field, not_prefix, op_str, param_ref),
518                    1,
519                )
520            }
521            (FilterOperator::Like | FilterOperator::Ilike, _, FilterValue::Single(_)) => {
522                let param_ref = self.add_param(filter.value.to_json());
523                let not_prefix = if filter.negated { "NOT " } else { "" };
524                let op_str = if filter.operator == FilterOperator::Like {
525                    "LIKE"
526                } else {
527                    "ILIKE"
528                };
529                (
530                    format!("{}{} {} {}", field, not_prefix, op_str, param_ref),
531                    1,
532                )
533            }
534
535            // Regex match operators
536            (FilterOperator::Match, _, FilterValue::Single(_)) => {
537                let param_ref = self.add_param(filter.value.to_json());
538                let op_sql = if filter.negated { "!~" } else { "~" };
539                (format!("{} {} {}", field, op_sql, param_ref), 1)
540            }
541            (FilterOperator::Imatch, _, FilterValue::Single(_)) => {
542                let param_ref = self.add_param(filter.value.to_json());
543                let op_sql = if filter.negated { "!~*" } else { "~*" };
544                (format!("{} {} {}", field, op_sql, param_ref), 1)
545            }
546
547            // Full-text search operators
548            (
549                FilterOperator::Fts
550                | FilterOperator::Plfts
551                | FilterOperator::Phfts
552                | FilterOperator::Wfts,
553                _,
554                FilterValue::Single(_),
555            ) => {
556                let param_ref = self.add_param(filter.value.to_json());
557                let lang = filter.language.as_deref().unwrap_or("english");
558                let ts_fn = match filter.operator {
559                    FilterOperator::Fts | FilterOperator::Plfts => "plainto_tsquery",
560                    FilterOperator::Phfts => "phraseto_tsquery",
561                    FilterOperator::Wfts => "websearch_to_tsquery",
562                    _ => unreachable!(),
563                };
564                let not_prefix = if filter.negated { "NOT " } else { "" };
565                (
566                    format!(
567                        "{}to_tsvector('{}', {}) @@ {}('{}', {})",
568                        not_prefix, lang, field, ts_fn, lang, param_ref
569                    ),
570                    1,
571                )
572            }
573
574            // Array/Range operators
575            (FilterOperator::Cs, _, FilterValue::Single(_)) => {
576                let param_ref = self.add_param(filter.value.to_json());
577                let op_sql = if filter.negated { "NOT @>" } else { "@>" };
578                (format!("{} {} {}", field, op_sql, param_ref), 1)
579            }
580            (FilterOperator::Cd, _, FilterValue::Single(_)) => {
581                let param_ref = self.add_param(filter.value.to_json());
582                let op_sql = if filter.negated { "NOT <@" } else { "<@" };
583                (format!("{} {} {}", field, op_sql, param_ref), 1)
584            }
585            (FilterOperator::Ov, _, FilterValue::List(_)) => {
586                let param_ref = self.add_param(filter.value.to_json());
587                let not_prefix = if filter.negated { "NOT " } else { "" };
588                (format!("{}{} && {}", field, not_prefix, param_ref), 1)
589            }
590
591            // Range operators
592            (FilterOperator::Sl, _, FilterValue::Single(_)) => {
593                let param_ref = self.add_param(filter.value.to_json());
594                let op_sql = if filter.negated { "NOT <<" } else { "<<" };
595                (format!("{} {} {}", field, op_sql, param_ref), 1)
596            }
597            (FilterOperator::Sr, _, FilterValue::Single(_)) => {
598                let param_ref = self.add_param(filter.value.to_json());
599                let op_sql = if filter.negated { "NOT >>" } else { ">>" };
600                (format!("{} {} {}", field, op_sql, param_ref), 1)
601            }
602            (FilterOperator::Nxl, _, FilterValue::Single(_)) => {
603                let param_ref = self.add_param(filter.value.to_json());
604                let op_sql = if filter.negated { "NOT &<" } else { "&<" };
605                (format!("{} {} {}", field, op_sql, param_ref), 1)
606            }
607            (FilterOperator::Nxr, _, FilterValue::Single(_)) => {
608                let param_ref = self.add_param(filter.value.to_json());
609                let op_sql = if filter.negated { "NOT &>" } else { "&>" };
610                (format!("{} {} {}", field, op_sql, param_ref), 1)
611            }
612            (FilterOperator::Adj, _, FilterValue::Single(_)) => {
613                let param_ref = self.add_param(filter.value.to_json());
614                let op_sql = if filter.negated { "NOT -|-" } else { "-|-" };
615                (format!("{} {} {}", field, op_sql, param_ref), 1)
616            }
617
618            // Fallback
619            _ => {
620                return Err(SqlError::InvalidParameter(format!(
621                    "unsupported operator/value combination: {:?} with {:?}",
622                    filter.operator, filter.value
623                )));
624            }
625        };
626
627        Ok((op_sql, value))
628    }
629
630    fn build_is_clause(&self, field: &str, value: &str, negated: bool) -> Result<String, SqlError> {
631        match (value.to_lowercase().as_str(), negated) {
632            ("null", false) => Ok(format!("{} IS NULL", field)),
633            ("null", true) => Ok(format!("{} IS NOT NULL", field)),
634            ("not_null", false) => Ok(format!("{} IS NOT NULL", field)),
635            ("not_null", true) => Ok(format!("{} IS NULL", field)),
636            ("true", false) => Ok(format!("{} IS TRUE", field)),
637            ("true", true) => Ok(format!("{} IS NOT TRUE", field)),
638            ("false", false) => Ok(format!("{} IS FALSE", field)),
639            ("false", true) => Ok(format!("{} IS NOT FALSE", field)),
640            ("unknown", false) => Ok(format!("{} IS UNKNOWN", field)),
641            ("unknown", true) => Ok(format!("{} IS NOT UNKNOWN", field)),
642            _ => Err(SqlError::InvalidParameter(format!(
643                "invalid IS value: {}",
644                value
645            ))),
646        }
647    }
648
649    fn field_to_sql(&self, field: &Field) -> String {
650        let base = self.quote_identifier(&field.name);
651
652        match (&field.json_path[..], &field.cast) {
653            ([], None) => base,
654            ([], Some(cast)) => format!("{}::{}", base, cast),
655            (json_path, cast_opt) => {
656                let json_path_sql: Vec<String> = json_path
657                    .iter()
658                    .map(|op| match op {
659                        JsonOp::Arrow(key) => format!("->'{}'", key),
660                        JsonOp::DoubleArrow(key) => format!("->>'{}'", key),
661                        JsonOp::ArrayIndex(idx) => format!("->{}", idx),
662                    })
663                    .collect();
664
665                let json_path_str = json_path_sql.join("");
666
667                if let Some(cast) = cast_opt {
668                    format!("({}{})::{}", base, json_path_str, cast)
669                } else {
670                    format!("{}{}", base, json_path_str)
671                }
672            }
673        }
674    }
675
676    pub(crate) fn add_param(&mut self, value: serde_json::Value) -> String {
677        let idx = self.param_index + 1;
678        self.param_index = idx;
679        self.params.push(value);
680        format!("${}", idx)
681    }
682
683    fn quote_identifier(&self, name: &str) -> String {
684        let escaped = name.replace('"', "\"\"");
685        format!("\"{}\"", escaped)
686    }
687}
688
689#[cfg(test)]
690mod tests {
691    use super::*;
692
693    #[test]
694    fn test_query_builder_new() {
695        let builder = QueryBuilder::new();
696        assert!(builder.sql.is_empty());
697        assert!(builder.params.is_empty());
698        assert_eq!(builder.param_index, 0);
699    }
700
701    #[test]
702    fn test_add_param() {
703        let mut builder = QueryBuilder::new();
704        let param_ref = builder.add_param(serde_json::Value::String("test".to_string()));
705        assert_eq!(param_ref, "$1");
706        assert_eq!(builder.params.len(), 1);
707        assert_eq!(builder.param_index, 1);
708    }
709
710    #[test]
711    fn test_quote_identifier() {
712        let builder = QueryBuilder::new();
713        assert_eq!(builder.quote_identifier("id"), "\"id\"");
714        assert_eq!(builder.quote_identifier("user\"id"), "\"user\"\"id\"");
715    }
716
717    #[test]
718    fn test_field_to_sql_simple() {
719        let builder = QueryBuilder::new();
720        let field = Field::new("id");
721        assert_eq!(builder.field_to_sql(&field), "\"id\"");
722    }
723
724    #[test]
725    fn test_field_to_sql_with_json_path() {
726        let builder = QueryBuilder::new();
727        let field = Field::new("data").with_json_path(vec![JsonOp::Arrow("key".to_string())]);
728        let sql = builder.field_to_sql(&field);
729        assert!(sql.contains("\"data\"->'key'"));
730    }
731
732    #[test]
733    fn test_field_to_sql_with_cast() {
734        let builder = QueryBuilder::new();
735        let field = Field::new("price").with_cast("numeric");
736        assert_eq!(builder.field_to_sql(&field), "\"price\"::numeric");
737    }
738
739    #[test]
740    fn test_build_is_clause() {
741        let builder = QueryBuilder::new();
742
743        assert_eq!(
744            builder.build_is_clause("\"id\"", "null", false).unwrap(),
745            "\"id\" IS NULL"
746        );
747
748        assert_eq!(
749            builder.build_is_clause("\"id\"", "null", true).unwrap(),
750            "\"id\" IS NOT NULL"
751        );
752    }
753
754    #[test]
755    fn test_operator_to_sql_comparison() {
756        let mut builder = QueryBuilder::new();
757
758        // GT operator
759        let filter = Filter::new(
760            Field::new("age"),
761            FilterOperator::Gt,
762            FilterValue::Single("18".to_string()),
763        );
764        let (sql, _) = builder.operator_to_sql("\"age\"", &filter).unwrap();
765        assert_eq!(sql, "\"age\" > $1");
766        assert_eq!(
767            builder.params[0],
768            serde_json::Value::String("18".to_string())
769        );
770
771        // GTE operator
772        builder = QueryBuilder::new();
773        let filter = Filter::new(
774            Field::new("age"),
775            FilterOperator::Gte,
776            FilterValue::Single("18".to_string()),
777        );
778        let (sql, _) = builder.operator_to_sql("\"age\"", &filter).unwrap();
779        assert_eq!(sql, "\"age\" >= $1");
780
781        // LT operator
782        builder = QueryBuilder::new();
783        let filter = Filter::new(
784            Field::new("age"),
785            FilterOperator::Lt,
786            FilterValue::Single("65".to_string()),
787        );
788        let (sql, _) = builder.operator_to_sql("\"age\"", &filter).unwrap();
789        assert_eq!(sql, "\"age\" < $1");
790
791        // LTE operator
792        builder = QueryBuilder::new();
793        let filter = Filter::new(
794            Field::new("age"),
795            FilterOperator::Lte,
796            FilterValue::Single("65".to_string()),
797        );
798        let (sql, _) = builder.operator_to_sql("\"age\"", &filter).unwrap();
799        assert_eq!(sql, "\"age\" <= $1");
800
801        // NEQ operator
802        builder = QueryBuilder::new();
803        let filter = Filter::new(
804            Field::new("status"),
805            FilterOperator::Neq,
806            FilterValue::Single("active".to_string()),
807        );
808        let (sql, _) = builder.operator_to_sql("\"status\"", &filter).unwrap();
809        assert_eq!(sql, "\"status\" <> $1");
810    }
811
812    #[test]
813    fn test_operator_to_sql_in_operator() {
814        let mut builder = QueryBuilder::new();
815        let filter = Filter::new(
816            Field::new("status"),
817            FilterOperator::In,
818            FilterValue::List(vec!["active".to_string(), "pending".to_string()]),
819        );
820        let (sql, _) = builder.operator_to_sql("\"status\"", &filter).unwrap();
821        assert_eq!(sql, "\"status\" = ANY($1)");
822        assert!(matches!(builder.params[0], serde_json::Value::Array(_)));
823    }
824
825    #[test]
826    fn test_operator_to_sql_pattern_matching() {
827        let mut builder = QueryBuilder::new();
828
829        // Match operator (regex)
830        let filter = Filter::new(
831            Field::new("name"),
832            FilterOperator::Match,
833            FilterValue::Single("^John".to_string()),
834        );
835        let (sql, _) = builder.operator_to_sql("\"name\"", &filter).unwrap();
836        assert_eq!(sql, "\"name\" ~ $1");
837
838        // Imatch operator (case-insensitive regex)
839        builder = QueryBuilder::new();
840        let filter = Filter::new(
841            Field::new("name"),
842            FilterOperator::Imatch,
843            FilterValue::Single("^john".to_string()),
844        );
845        let (sql, _) = builder.operator_to_sql("\"name\"", &filter).unwrap();
846        assert_eq!(sql, "\"name\" ~* $1");
847    }
848
849    #[test]
850    fn test_operator_to_sql_fts() {
851        let mut builder = QueryBuilder::new();
852
853        // FTS operator without language (defaults to english)
854        let filter = Filter::new(
855            Field::new("content"),
856            FilterOperator::Fts,
857            FilterValue::Single("search".to_string()),
858        );
859        let (sql, _) = builder.operator_to_sql("\"content\"", &filter).unwrap();
860        assert_eq!(
861            sql,
862            "to_tsvector('english', \"content\") @@ plainto_tsquery('english', $1)"
863        );
864
865        // FTS operator with custom language
866        builder = QueryBuilder::new();
867        let mut filter = Filter::new(
868            Field::new("content"),
869            FilterOperator::Fts,
870            FilterValue::Single("search".to_string()),
871        );
872        filter.language = Some("french".to_string());
873        let (sql, _) = builder.operator_to_sql("\"content\"", &filter).unwrap();
874        assert_eq!(
875            sql,
876            "to_tsvector('french', \"content\") @@ plainto_tsquery('french', $1)"
877        );
878
879        // PHFTS operator (phrase search)
880        builder = QueryBuilder::new();
881        let filter = Filter::new(
882            Field::new("content"),
883            FilterOperator::Phfts,
884            FilterValue::Single("search phrase".to_string()),
885        );
886        let (sql, _) = builder.operator_to_sql("\"content\"", &filter).unwrap();
887        assert_eq!(
888            sql,
889            "to_tsvector('english', \"content\") @@ phraseto_tsquery('english', $1)"
890        );
891
892        // WFTS operator (websearch)
893        builder = QueryBuilder::new();
894        let filter = Filter::new(
895            Field::new("content"),
896            FilterOperator::Wfts,
897            FilterValue::Single("search query".to_string()),
898        );
899        let (sql, _) = builder.operator_to_sql("\"content\"", &filter).unwrap();
900        assert_eq!(
901            sql,
902            "to_tsvector('english', \"content\") @@ websearch_to_tsquery('english', $1)"
903        );
904    }
905
906    #[test]
907    fn test_operator_to_sql_array_operators() {
908        let mut builder = QueryBuilder::new();
909
910        // CS operator (contains)
911        let filter = Filter::new(
912            Field::new("tags"),
913            FilterOperator::Cs,
914            FilterValue::Single("{rust}".to_string()),
915        );
916        let (sql, _) = builder.operator_to_sql("\"tags\"", &filter).unwrap();
917        assert_eq!(sql, "\"tags\" @> $1");
918
919        // CD operator (contained in)
920        builder = QueryBuilder::new();
921        let filter = Filter::new(
922            Field::new("tags"),
923            FilterOperator::Cd,
924            FilterValue::Single("{rust,elixir}".to_string()),
925        );
926        let (sql, _) = builder.operator_to_sql("\"tags\"", &filter).unwrap();
927        assert_eq!(sql, "\"tags\" <@ $1");
928
929        // OV operator (overlaps)
930        builder = QueryBuilder::new();
931        let filter = Filter::new(
932            Field::new("tags"),
933            FilterOperator::Ov,
934            FilterValue::List(vec!["rust".to_string(), "elixir".to_string()]),
935        );
936        let (sql, _) = builder.operator_to_sql("\"tags\"", &filter).unwrap();
937        assert_eq!(sql, "\"tags\" && $1");
938    }
939
940    #[test]
941    fn test_operator_to_sql_range_operators() {
942        let mut builder = QueryBuilder::new();
943
944        // SL operator (strictly left)
945        let filter = Filter::new(
946            Field::new("range"),
947            FilterOperator::Sl,
948            FilterValue::Single("[1,10)".to_string()),
949        );
950        let (sql, _) = builder.operator_to_sql("\"range\"", &filter).unwrap();
951        assert_eq!(sql, "\"range\" << $1");
952
953        // SR operator (strictly right)
954        builder = QueryBuilder::new();
955        let filter = Filter::new(
956            Field::new("range"),
957            FilterOperator::Sr,
958            FilterValue::Single("[1,10)".to_string()),
959        );
960        let (sql, _) = builder.operator_to_sql("\"range\"", &filter).unwrap();
961        assert_eq!(sql, "\"range\" >> $1");
962
963        // NXL operator (does not extend to right)
964        builder = QueryBuilder::new();
965        let filter = Filter::new(
966            Field::new("range"),
967            FilterOperator::Nxl,
968            FilterValue::Single("[1,10)".to_string()),
969        );
970        let (sql, _) = builder.operator_to_sql("\"range\"", &filter).unwrap();
971        assert_eq!(sql, "\"range\" &< $1");
972
973        // NXR operator (does not extend to left)
974        builder = QueryBuilder::new();
975        let filter = Filter::new(
976            Field::new("range"),
977            FilterOperator::Nxr,
978            FilterValue::Single("[1,10)".to_string()),
979        );
980        let (sql, _) = builder.operator_to_sql("\"range\"", &filter).unwrap();
981        assert_eq!(sql, "\"range\" &> $1");
982
983        // ADJ operator (adjacent)
984        builder = QueryBuilder::new();
985        let filter = Filter::new(
986            Field::new("range"),
987            FilterOperator::Adj,
988            FilterValue::Single("[1,10)".to_string()),
989        );
990        let (sql, _) = builder.operator_to_sql("\"range\"", &filter).unwrap();
991        assert_eq!(sql, "\"range\" -|- $1");
992    }
993
994    #[test]
995    fn test_operator_to_sql_negated() {
996        let mut builder = QueryBuilder::new();
997
998        // Negated EQ becomes NEQ
999        let mut filter = Filter::new(
1000            Field::new("status"),
1001            FilterOperator::Eq,
1002            FilterValue::Single("active".to_string()),
1003        );
1004        filter.negated = true;
1005        let (sql, _) = builder.operator_to_sql("\"status\"", &filter).unwrap();
1006        assert_eq!(sql, "\"status\" <> $1");
1007
1008        // Negated GT becomes LTE
1009        builder = QueryBuilder::new();
1010        let mut filter = Filter::new(
1011            Field::new("age"),
1012            FilterOperator::Gt,
1013            FilterValue::Single("18".to_string()),
1014        );
1015        filter.negated = true;
1016        let (sql, _) = builder.operator_to_sql("\"age\"", &filter).unwrap();
1017        assert_eq!(sql, "\"age\" <= $1");
1018
1019        // Negated FTS
1020        builder = QueryBuilder::new();
1021        let mut filter = Filter::new(
1022            Field::new("content"),
1023            FilterOperator::Fts,
1024            FilterValue::Single("search".to_string()),
1025        );
1026        filter.negated = true;
1027        let (sql, _) = builder.operator_to_sql("\"content\"", &filter).unwrap();
1028        assert_eq!(
1029            sql,
1030            "NOT to_tsvector('english', \"content\") @@ plainto_tsquery('english', $1)"
1031        );
1032    }
1033
1034    #[test]
1035    fn test_operator_to_sql_with_quantifiers() {
1036        let mut builder = QueryBuilder::new();
1037
1038        // EQ with ANY quantifier
1039        let mut filter = Filter::new(
1040            Field::new("status"),
1041            FilterOperator::Eq,
1042            FilterValue::List(vec!["active".to_string(), "pending".to_string()]),
1043        );
1044        filter.quantifier = Some(Quantifier::Any);
1045        let (sql, _) = builder.operator_to_sql("\"status\"", &filter).unwrap();
1046        assert_eq!(sql, "\"status\" = ANY($1)");
1047
1048        // EQ with ALL quantifier
1049        builder = QueryBuilder::new();
1050        let mut filter = Filter::new(
1051            Field::new("status"),
1052            FilterOperator::Eq,
1053            FilterValue::List(vec!["active".to_string()]),
1054        );
1055        filter.quantifier = Some(Quantifier::All);
1056        let (sql, _) = builder.operator_to_sql("\"status\"", &filter).unwrap();
1057        assert_eq!(sql, "\"status\" = ALL($1)");
1058    }
1059}
postgrest_parser/sql/builder.rs

postgrest_parser/sql/
builder.rs
