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(feature = "postgres")]
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(feature = "postgres")]
94            schema_cache: None,
95            current_schema: "public".to_string(),
96        }
97    }
98
99    /// Sets the schema cache for relation resolution
100    #[cfg(feature = "postgres")]
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(feature = "postgres")]
198        let rel_table = &item.name;
199
200        #[cfg(feature = "postgres")]
201        {
202            // With schema cache: generate proper JOINs
203            if let Some(cache) = &self.schema_cache {
204                // Get current table (last in tables vec)
205                let current_table = self.tables.last().ok_or(SqlError::NoTableContext)?;
206
207                // Find relationship
208                if let Some(rel) =
209                    cache.find_relationship(&self.current_schema, current_table, rel_table)
210                {
211                    return self.build_relation_with_fk(item, &rel);
212                } else {
213                    // No relationship found - return error with helpful message
214                    return Err(SqlError::RelationNotFound {
215                        from_table: current_table.clone(),
216                        to_table: rel_table.clone(),
217                    });
218                }
219            }
220        }
221
222        // Without schema cache: generate placeholder (won't work!)
223        // This maintains backward compatibility but produces invalid SQL
224        self.build_relation_placeholder(item)
225    }
226
227    #[cfg(feature = "postgres")]
228    fn build_relation_with_fk(
229        &self,
230        item: &SelectItem,
231        rel: &crate::schema_cache::Relationship,
232    ) -> Result<String, SqlError> {
233        use crate::schema_cache::RelationType;
234
235        let rel_table = &item.name;
236        let current_table = self.tables.last().unwrap();
237
238        // Build column list for the subquery
239        let column_list = if let Some(children) = &item.children {
240            children
241                .iter()
242                .filter(|c| c.item_type == ItemType::Field)
243                .map(|c| {
244                    if c.name == "*" {
245                        "*".to_string()
246                    } else {
247                        self.quote_identifier(&c.name)
248                    }
249                })
250                .collect::<Vec<_>>()
251                .join(", ")
252        } else {
253            "*".to_string()
254        };
255
256        match rel.relation_type {
257            RelationType::ManyToOne => {
258                // orders.customer_id -> customers.id
259                // Generate: (SELECT row_to_json(c) FROM customers c WHERE c.id = orders.customer_id)
260                Ok(format!(
261                    "COALESCE((SELECT row_to_json({}_1) FROM (SELECT {} FROM {} {} WHERE {}) {}_1), 'null'::json) AS {}",
262                    rel_table,
263                    column_list,
264                    self.quote_identifier(rel_table),
265                    rel_table,
266                    rel.foreign_key.join_condition(current_table, rel_table),
267                    rel_table,
268                    self.quote_identifier(rel_table)
269                ))
270            }
271            RelationType::OneToMany => {
272                // customers.id <- orders.customer_id
273                // Generate: (SELECT COALESCE(json_agg(o), '[]') FROM orders o WHERE o.customer_id = customers.id)
274                Ok(format!(
275                    "COALESCE((SELECT json_agg({}_1) FROM (SELECT {} FROM {} {} WHERE {}) {}_1), '[]'::json) AS {}",
276                    rel_table,
277                    column_list,
278                    self.quote_identifier(rel_table),
279                    rel_table,
280                    rel.foreign_key.join_condition(rel_table, current_table),
281                    rel_table,
282                    self.quote_identifier(rel_table)
283                ))
284            }
285            RelationType::ManyToMany { junction_table } => {
286                // TODO: Implement M2M through junction tables
287                Err(SqlError::ManyToManyNotYetSupported {
288                    junction_table: junction_table.to_string(),
289                })
290            }
291        }
292    }
293
294    fn build_relation_placeholder(&self, item: &SelectItem) -> Result<String, SqlError> {
295        let rel_alias = &item.name;
296
297        if let Some(children) = &item.children {
298            let child_columns: Vec<String> = children
299                .iter()
300                .filter(|c| c.item_type == ItemType::Field)
301                .map(|c| {
302                    if c.name == "*" {
303                        format!("{}.*", rel_alias)
304                    } else {
305                        format!("{}.{}", rel_alias, self.quote_identifier(&c.name))
306                    }
307                })
308                .collect();
309
310            if child_columns.is_empty() {
311                Ok(format!(
312                    "(SELECT json_agg(row_to_json({}.*)) AS {} FROM {})",
313                    rel_alias, rel_alias, rel_alias
314                ))
315            } else {
316                Ok(format!(
317                    "(SELECT json_agg(row_to_json({rel_alias}_sub)) FROM (SELECT {columns} FROM {rel_alias}) {rel_alias}_sub) AS {rel_alias}",
318                    rel_alias = rel_alias,
319                    columns = child_columns.join(", "),
320                ))
321            }
322        } else {
323            Ok(format!(
324                "(SELECT json_agg(row_to_json({}.*)) AS {} FROM {})",
325                rel_alias, rel_alias, rel_alias
326            ))
327        }
328    }
329
330    fn build_from_clause(&mut self, table: &str) -> Result<(), SqlError> {
331        if table.is_empty() {
332            return Err(SqlError::EmptyTableName);
333        }
334
335        self.sql.push_str(" FROM ");
336        self.sql.push_str(&self.quote_identifier(table));
337        Ok(())
338    }
339
340    pub fn build_where_clause(&mut self, filters: &[LogicCondition]) -> Result<(), SqlError> {
341        self.sql.push_str(" WHERE ");
342
343        let clauses: Result<Vec<String>, SqlError> = filters
344            .iter()
345            .map(|filter| self.build_filter(filter))
346            .collect();
347
348        match clauses {
349            Ok(clauses) => {
350                self.sql.push_str(&clauses.join(" AND "));
351                Ok(())
352            }
353            Err(e) => Err(e),
354        }
355    }
356
357    pub(crate) fn build_filter(&mut self, condition: &LogicCondition) -> Result<String, SqlError> {
358        match condition {
359            LogicCondition::Filter(filter) => self.build_single_filter(filter),
360            LogicCondition::Logic(tree) => self.build_logic_tree(tree),
361        }
362    }
363
364    fn build_single_filter(&mut self, filter: &Filter) -> Result<String, SqlError> {
365        let field_sql = self.field_to_sql(&filter.field);
366        let (clause, _) = self.operator_to_sql(&field_sql, filter)?;
367        Ok(clause)
368    }
369
370    fn build_logic_tree(&mut self, tree: &LogicTree) -> Result<String, SqlError> {
371        let joiner = if tree.operator == LogicOperator::And {
372            " AND "
373        } else {
374            " OR "
375        };
376
377        let conditions: Result<Vec<String>, SqlError> = tree
378            .conditions
379            .iter()
380            .map(|c| self.build_filter(c))
381            .collect();
382
383        let conditions_sql = conditions?.join(joiner);
384
385        if tree.negated {
386            Ok(format!("NOT ({})", conditions_sql))
387        } else {
388            Ok(format!("({})", conditions_sql))
389        }
390    }
391
392    pub(crate) fn build_order_clause(&mut self, order_terms: &[OrderTerm]) -> Result<(), SqlError> {
393        let clauses: Result<Vec<String>, SqlError> = order_terms
394            .iter()
395            .map(|term| self.order_term_to_sql(term))
396            .collect();
397
398        match clauses {
399            Ok(clauses) => {
400                self.sql.push_str(" ORDER BY ");
401                self.sql.push_str(&clauses.join(", "));
402                Ok(())
403            }
404            Err(e) => Err(e),
405        }
406    }
407
408    fn order_term_to_sql(&self, term: &OrderTerm) -> Result<String, SqlError> {
409        let field_sql = self.field_to_sql(&term.field);
410        let dir_sql = if term.direction == Direction::Desc {
411            " DESC"
412        } else {
413            " ASC"
414        };
415
416        let nulls_sql = match term.nulls {
417            Some(Nulls::First) => " NULLS FIRST",
418            Some(Nulls::Last) => " NULLS LAST",
419            None => "",
420        };
421
422        Ok(format!("{}{}{}", field_sql, dir_sql, nulls_sql))
423    }
424
425    pub(crate) fn build_limit_offset(
426        &mut self,
427        limit: Option<u64>,
428        offset: Option<u64>,
429    ) -> Result<(), SqlError> {
430        match (limit, offset) {
431            (Some(lim), Some(off)) => {
432                let lim_ref = self.add_param(serde_json::Value::Number(lim.into()));
433                let off_ref = self.add_param(serde_json::Value::Number(off.into()));
434                self.sql
435                    .push_str(&format!(" LIMIT {} OFFSET {}", lim_ref, off_ref));
436            }
437            (Some(lim), None) => {
438                let lim_ref = self.add_param(serde_json::Value::Number(lim.into()));
439                self.sql.push_str(&format!(" LIMIT {}", lim_ref));
440            }
441            (None, Some(off)) => {
442                let off_ref = self.add_param(serde_json::Value::Number(off.into()));
443                self.sql.push_str(&format!(" OFFSET {}", off_ref));
444            }
445            (None, None) => {}
446        }
447
448        Ok(())
449    }
450
451    fn operator_to_sql(
452        &mut self,
453        field: &str,
454        filter: &Filter,
455    ) -> Result<(String, usize), SqlError> {
456        let (op_sql, value) = match (&filter.operator, &filter.quantifier, &filter.value) {
457            // Eq with quantifiers
458            (FilterOperator::Eq, Some(Quantifier::Any), FilterValue::List(ref _vals)) => {
459                let param_ref = self.add_param(filter.value.to_json());
460                (format!("{} = ANY({})", field, param_ref), param_ref.len())
461            }
462            (FilterOperator::Eq, Some(Quantifier::All), FilterValue::List(ref _vals)) => {
463                let param_ref = self.add_param(filter.value.to_json());
464                (format!("{} = ALL({})", field, param_ref), param_ref.len())
465            }
466            (FilterOperator::Eq, _, FilterValue::Single(ref _val)) => {
467                let param_ref = self.add_param(filter.value.to_json());
468                let op_sql = if filter.negated { "<>" } else { "=" };
469                (format!("{} {} {}", field, op_sql, param_ref), 1)
470            }
471
472            // Neq
473            (FilterOperator::Neq, _, FilterValue::Single(_)) => {
474                let param_ref = self.add_param(filter.value.to_json());
475                let op_sql = if filter.negated { "=" } else { "<>" };
476                (format!("{} {} {}", field, op_sql, param_ref), 1)
477            }
478
479            // Comparison operators
480            (FilterOperator::Gt, _, FilterValue::Single(_)) => {
481                let param_ref = self.add_param(filter.value.to_json());
482                let op_sql = if filter.negated { "<=" } else { ">" };
483                (format!("{} {} {}", field, op_sql, param_ref), 1)
484            }
485            (FilterOperator::Gte, _, FilterValue::Single(_)) => {
486                let param_ref = self.add_param(filter.value.to_json());
487                let op_sql = if filter.negated { "<" } else { ">=" };
488                (format!("{} {} {}", field, op_sql, param_ref), 1)
489            }
490            (FilterOperator::Lt, _, FilterValue::Single(_)) => {
491                let param_ref = self.add_param(filter.value.to_json());
492                let op_sql = if filter.negated { ">=" } else { "<" };
493                (format!("{} {} {}", field, op_sql, param_ref), 1)
494            }
495            (FilterOperator::Lte, _, FilterValue::Single(_)) => {
496                let param_ref = self.add_param(filter.value.to_json());
497                let op_sql = if filter.negated { ">" } else { "<=" };
498                (format!("{} {} {}", field, op_sql, param_ref), 1)
499            }
500
501            // IN operator
502            (FilterOperator::In, _, FilterValue::List(_)) => {
503                let param_ref = self.add_param(filter.value.to_json());
504                let not_prefix = if filter.negated { "NOT " } else { "" };
505                (format!("{}{} = ANY({})", field, not_prefix, param_ref), 1)
506            }
507
508            // IS operator
509            (FilterOperator::Is, _, FilterValue::Single(ref val)) => {
510                let clause = self.build_is_clause(field, val, filter.negated)?;
511                (clause, 0)
512            }
513
514            // LIKE/ILIKE operators
515            (
516                FilterOperator::Like | FilterOperator::Ilike,
517                Some(Quantifier::Any),
518                FilterValue::List(_),
519            ) => {
520                let param_ref = self.add_param(filter.value.to_json());
521                let not_prefix = if filter.negated { "NOT " } else { "" };
522                let op_str = if filter.operator == FilterOperator::Like {
523                    "LIKE"
524                } else {
525                    "ILIKE"
526                };
527                (
528                    format!("{}{} {} ANY({})", field, not_prefix, op_str, param_ref),
529                    1,
530                )
531            }
532            (FilterOperator::Like | FilterOperator::Ilike, _, FilterValue::Single(_)) => {
533                let param_ref = self.add_param(filter.value.to_json());
534                let not_prefix = if filter.negated { "NOT " } else { "" };
535                let op_str = if filter.operator == FilterOperator::Like {
536                    "LIKE"
537                } else {
538                    "ILIKE"
539                };
540                (
541                    format!("{}{} {} {}", field, not_prefix, op_str, param_ref),
542                    1,
543                )
544            }
545
546            // Regex match operators
547            (FilterOperator::Match, _, FilterValue::Single(_)) => {
548                let param_ref = self.add_param(filter.value.to_json());
549                let op_sql = if filter.negated { "!~" } else { "~" };
550                (format!("{} {} {}", field, op_sql, param_ref), 1)
551            }
552            (FilterOperator::Imatch, _, FilterValue::Single(_)) => {
553                let param_ref = self.add_param(filter.value.to_json());
554                let op_sql = if filter.negated { "!~*" } else { "~*" };
555                (format!("{} {} {}", field, op_sql, param_ref), 1)
556            }
557
558            // Full-text search operators
559            (
560                FilterOperator::Fts
561                | FilterOperator::Plfts
562                | FilterOperator::Phfts
563                | FilterOperator::Wfts,
564                _,
565                FilterValue::Single(_),
566            ) => {
567                let param_ref = self.add_param(filter.value.to_json());
568                let lang = filter.language.as_deref().unwrap_or("english");
569                let ts_fn = match filter.operator {
570                    FilterOperator::Fts | FilterOperator::Plfts => "plainto_tsquery",
571                    FilterOperator::Phfts => "phraseto_tsquery",
572                    FilterOperator::Wfts => "websearch_to_tsquery",
573                    _ => unreachable!(),
574                };
575                let not_prefix = if filter.negated { "NOT " } else { "" };
576                (
577                    format!(
578                        "{}to_tsvector('{}', {}) @@ {}('{}', {})",
579                        not_prefix, lang, field, ts_fn, lang, param_ref
580                    ),
581                    1,
582                )
583            }
584
585            // Array/Range operators
586            (FilterOperator::Cs, _, FilterValue::Single(_)) => {
587                let param_ref = self.add_param(filter.value.to_json());
588                let op_sql = if filter.negated { "NOT @>" } else { "@>" };
589                (format!("{} {} {}", field, op_sql, param_ref), 1)
590            }
591            (FilterOperator::Cd, _, FilterValue::Single(_)) => {
592                let param_ref = self.add_param(filter.value.to_json());
593                let op_sql = if filter.negated { "NOT <@" } else { "<@" };
594                (format!("{} {} {}", field, op_sql, param_ref), 1)
595            }
596            (FilterOperator::Ov, _, FilterValue::List(_)) => {
597                let param_ref = self.add_param(filter.value.to_json());
598                let not_prefix = if filter.negated { "NOT " } else { "" };
599                (format!("{}{} && {}", field, not_prefix, param_ref), 1)
600            }
601
602            // Range operators
603            (FilterOperator::Sl, _, FilterValue::Single(_)) => {
604                let param_ref = self.add_param(filter.value.to_json());
605                let op_sql = if filter.negated { "NOT <<" } else { "<<" };
606                (format!("{} {} {}", field, op_sql, param_ref), 1)
607            }
608            (FilterOperator::Sr, _, FilterValue::Single(_)) => {
609                let param_ref = self.add_param(filter.value.to_json());
610                let op_sql = if filter.negated { "NOT >>" } else { ">>" };
611                (format!("{} {} {}", field, op_sql, param_ref), 1)
612            }
613            (FilterOperator::Nxl, _, FilterValue::Single(_)) => {
614                let param_ref = self.add_param(filter.value.to_json());
615                let op_sql = if filter.negated { "NOT &<" } else { "&<" };
616                (format!("{} {} {}", field, op_sql, param_ref), 1)
617            }
618            (FilterOperator::Nxr, _, FilterValue::Single(_)) => {
619                let param_ref = self.add_param(filter.value.to_json());
620                let op_sql = if filter.negated { "NOT &>" } else { "&>" };
621                (format!("{} {} {}", field, op_sql, param_ref), 1)
622            }
623            (FilterOperator::Adj, _, FilterValue::Single(_)) => {
624                let param_ref = self.add_param(filter.value.to_json());
625                let op_sql = if filter.negated { "NOT -|-" } else { "-|-" };
626                (format!("{} {} {}", field, op_sql, param_ref), 1)
627            }
628
629            // Fallback
630            _ => {
631                return Err(SqlError::InvalidParameter(format!(
632                    "unsupported operator/value combination: {:?} with {:?}",
633                    filter.operator, filter.value
634                )));
635            }
636        };
637
638        Ok((op_sql, value))
639    }
640
641    fn build_is_clause(&self, field: &str, value: &str, negated: bool) -> Result<String, SqlError> {
642        match (value.to_lowercase().as_str(), negated) {
643            ("null", false) => Ok(format!("{} IS NULL", field)),
644            ("null", true) => Ok(format!("{} IS NOT NULL", field)),
645            ("not_null", false) => Ok(format!("{} IS NOT NULL", field)),
646            ("not_null", true) => Ok(format!("{} IS NULL", field)),
647            ("true", false) => Ok(format!("{} IS TRUE", field)),
648            ("true", true) => Ok(format!("{} IS NOT TRUE", field)),
649            ("false", false) => Ok(format!("{} IS FALSE", field)),
650            ("false", true) => Ok(format!("{} IS NOT FALSE", field)),
651            ("unknown", false) => Ok(format!("{} IS UNKNOWN", field)),
652            ("unknown", true) => Ok(format!("{} IS NOT UNKNOWN", field)),
653            _ => Err(SqlError::InvalidParameter(format!(
654                "invalid IS value: {}",
655                value
656            ))),
657        }
658    }
659
660    fn field_to_sql(&self, field: &Field) -> String {
661        let base = self.quote_identifier(&field.name);
662
663        match (&field.json_path[..], &field.cast) {
664            ([], None) => base,
665            ([], Some(cast)) => format!("{}::{}", base, cast),
666            (json_path, cast_opt) => {
667                let json_path_sql: Vec<String> = json_path
668                    .iter()
669                    .map(|op| match op {
670                        JsonOp::Arrow(key) => format!("->'{}'", key),
671                        JsonOp::DoubleArrow(key) => format!("->>'{}'", key),
672                        JsonOp::ArrayIndex(idx) => format!("->{}", idx),
673                    })
674                    .collect();
675
676                let json_path_str = json_path_sql.join("");
677
678                if let Some(cast) = cast_opt {
679                    format!("({}{})::{}", base, json_path_str, cast)
680                } else {
681                    format!("{}{}", base, json_path_str)
682                }
683            }
684        }
685    }
686
687    pub(crate) fn add_param(&mut self, value: serde_json::Value) -> String {
688        let idx = self.param_index + 1;
689        self.param_index = idx;
690        self.params.push(value);
691        format!("${}", idx)
692    }
693
694    fn quote_identifier(&self, name: &str) -> String {
695        let escaped = name.replace('"', "\"\"");
696        format!("\"{}\"", escaped)
697    }
698}
699
700#[cfg(test)]
701mod tests {
702    use super::*;
703
704    #[test]
705    fn test_query_builder_new() {
706        let builder = QueryBuilder::new();
707        assert!(builder.sql.is_empty());
708        assert!(builder.params.is_empty());
709        assert_eq!(builder.param_index, 0);
710    }
711
712    #[test]
713    fn test_add_param() {
714        let mut builder = QueryBuilder::new();
715        let param_ref = builder.add_param(serde_json::Value::String("test".to_string()));
716        assert_eq!(param_ref, "$1");
717        assert_eq!(builder.params.len(), 1);
718        assert_eq!(builder.param_index, 1);
719    }
720
721    #[test]
722    fn test_quote_identifier() {
723        let builder = QueryBuilder::new();
724        assert_eq!(builder.quote_identifier("id"), "\"id\"");
725        assert_eq!(builder.quote_identifier("user\"id"), "\"user\"\"id\"");
726    }
727
728    #[test]
729    fn test_field_to_sql_simple() {
730        let builder = QueryBuilder::new();
731        let field = Field::new("id");
732        assert_eq!(builder.field_to_sql(&field), "\"id\"");
733    }
734
735    #[test]
736    fn test_field_to_sql_with_json_path() {
737        let builder = QueryBuilder::new();
738        let field = Field::new("data").with_json_path(vec![JsonOp::Arrow("key".to_string())]);
739        let sql = builder.field_to_sql(&field);
740        assert!(sql.contains("\"data\"->'key'"));
741    }
742
743    #[test]
744    fn test_field_to_sql_with_cast() {
745        let builder = QueryBuilder::new();
746        let field = Field::new("price").with_cast("numeric");
747        assert_eq!(builder.field_to_sql(&field), "\"price\"::numeric");
748    }
749
750    #[test]
751    fn test_build_is_clause() {
752        let builder = QueryBuilder::new();
753
754        assert_eq!(
755            builder.build_is_clause("\"id\"", "null", false).unwrap(),
756            "\"id\" IS NULL"
757        );
758
759        assert_eq!(
760            builder.build_is_clause("\"id\"", "null", true).unwrap(),
761            "\"id\" IS NOT NULL"
762        );
763    }
764
765    #[test]
766    fn test_operator_to_sql_comparison() {
767        let mut builder = QueryBuilder::new();
768
769        // GT operator
770        let filter = Filter::new(
771            Field::new("age"),
772            FilterOperator::Gt,
773            FilterValue::Single("18".to_string()),
774        );
775        let (sql, _) = builder.operator_to_sql("\"age\"", &filter).unwrap();
776        assert_eq!(sql, "\"age\" > $1");
777        assert_eq!(
778            builder.params[0],
779            serde_json::Value::String("18".to_string())
780        );
781
782        // GTE operator
783        builder = QueryBuilder::new();
784        let filter = Filter::new(
785            Field::new("age"),
786            FilterOperator::Gte,
787            FilterValue::Single("18".to_string()),
788        );
789        let (sql, _) = builder.operator_to_sql("\"age\"", &filter).unwrap();
790        assert_eq!(sql, "\"age\" >= $1");
791
792        // LT operator
793        builder = QueryBuilder::new();
794        let filter = Filter::new(
795            Field::new("age"),
796            FilterOperator::Lt,
797            FilterValue::Single("65".to_string()),
798        );
799        let (sql, _) = builder.operator_to_sql("\"age\"", &filter).unwrap();
800        assert_eq!(sql, "\"age\" < $1");
801
802        // LTE operator
803        builder = QueryBuilder::new();
804        let filter = Filter::new(
805            Field::new("age"),
806            FilterOperator::Lte,
807            FilterValue::Single("65".to_string()),
808        );
809        let (sql, _) = builder.operator_to_sql("\"age\"", &filter).unwrap();
810        assert_eq!(sql, "\"age\" <= $1");
811
812        // NEQ operator
813        builder = QueryBuilder::new();
814        let filter = Filter::new(
815            Field::new("status"),
816            FilterOperator::Neq,
817            FilterValue::Single("active".to_string()),
818        );
819        let (sql, _) = builder.operator_to_sql("\"status\"", &filter).unwrap();
820        assert_eq!(sql, "\"status\" <> $1");
821    }
822
823    #[test]
824    fn test_operator_to_sql_in_operator() {
825        let mut builder = QueryBuilder::new();
826        let filter = Filter::new(
827            Field::new("status"),
828            FilterOperator::In,
829            FilterValue::List(vec!["active".to_string(), "pending".to_string()]),
830        );
831        let (sql, _) = builder.operator_to_sql("\"status\"", &filter).unwrap();
832        assert_eq!(sql, "\"status\" = ANY($1)");
833        assert!(matches!(builder.params[0], serde_json::Value::Array(_)));
834    }
835
836    #[test]
837    fn test_operator_to_sql_pattern_matching() {
838        let mut builder = QueryBuilder::new();
839
840        // Match operator (regex)
841        let filter = Filter::new(
842            Field::new("name"),
843            FilterOperator::Match,
844            FilterValue::Single("^John".to_string()),
845        );
846        let (sql, _) = builder.operator_to_sql("\"name\"", &filter).unwrap();
847        assert_eq!(sql, "\"name\" ~ $1");
848
849        // Imatch operator (case-insensitive regex)
850        builder = QueryBuilder::new();
851        let filter = Filter::new(
852            Field::new("name"),
853            FilterOperator::Imatch,
854            FilterValue::Single("^john".to_string()),
855        );
856        let (sql, _) = builder.operator_to_sql("\"name\"", &filter).unwrap();
857        assert_eq!(sql, "\"name\" ~* $1");
858    }
859
860    #[test]
861    fn test_operator_to_sql_fts() {
862        let mut builder = QueryBuilder::new();
863
864        // FTS operator without language (defaults to english)
865        let filter = Filter::new(
866            Field::new("content"),
867            FilterOperator::Fts,
868            FilterValue::Single("search".to_string()),
869        );
870        let (sql, _) = builder.operator_to_sql("\"content\"", &filter).unwrap();
871        assert_eq!(
872            sql,
873            "to_tsvector('english', \"content\") @@ plainto_tsquery('english', $1)"
874        );
875
876        // FTS operator with custom language
877        builder = QueryBuilder::new();
878        let mut filter = Filter::new(
879            Field::new("content"),
880            FilterOperator::Fts,
881            FilterValue::Single("search".to_string()),
882        );
883        filter.language = Some("french".to_string());
884        let (sql, _) = builder.operator_to_sql("\"content\"", &filter).unwrap();
885        assert_eq!(
886            sql,
887            "to_tsvector('french', \"content\") @@ plainto_tsquery('french', $1)"
888        );
889
890        // PHFTS operator (phrase search)
891        builder = QueryBuilder::new();
892        let filter = Filter::new(
893            Field::new("content"),
894            FilterOperator::Phfts,
895            FilterValue::Single("search phrase".to_string()),
896        );
897        let (sql, _) = builder.operator_to_sql("\"content\"", &filter).unwrap();
898        assert_eq!(
899            sql,
900            "to_tsvector('english', \"content\") @@ phraseto_tsquery('english', $1)"
901        );
902
903        // WFTS operator (websearch)
904        builder = QueryBuilder::new();
905        let filter = Filter::new(
906            Field::new("content"),
907            FilterOperator::Wfts,
908            FilterValue::Single("search query".to_string()),
909        );
910        let (sql, _) = builder.operator_to_sql("\"content\"", &filter).unwrap();
911        assert_eq!(
912            sql,
913            "to_tsvector('english', \"content\") @@ websearch_to_tsquery('english', $1)"
914        );
915    }
916
917    #[test]
918    fn test_operator_to_sql_array_operators() {
919        let mut builder = QueryBuilder::new();
920
921        // CS operator (contains)
922        let filter = Filter::new(
923            Field::new("tags"),
924            FilterOperator::Cs,
925            FilterValue::Single("{rust}".to_string()),
926        );
927        let (sql, _) = builder.operator_to_sql("\"tags\"", &filter).unwrap();
928        assert_eq!(sql, "\"tags\" @> $1");
929
930        // CD operator (contained in)
931        builder = QueryBuilder::new();
932        let filter = Filter::new(
933            Field::new("tags"),
934            FilterOperator::Cd,
935            FilterValue::Single("{rust,elixir}".to_string()),
936        );
937        let (sql, _) = builder.operator_to_sql("\"tags\"", &filter).unwrap();
938        assert_eq!(sql, "\"tags\" <@ $1");
939
940        // OV operator (overlaps)
941        builder = QueryBuilder::new();
942        let filter = Filter::new(
943            Field::new("tags"),
944            FilterOperator::Ov,
945            FilterValue::List(vec!["rust".to_string(), "elixir".to_string()]),
946        );
947        let (sql, _) = builder.operator_to_sql("\"tags\"", &filter).unwrap();
948        assert_eq!(sql, "\"tags\" && $1");
949    }
950
951    #[test]
952    fn test_operator_to_sql_range_operators() {
953        let mut builder = QueryBuilder::new();
954
955        // SL operator (strictly left)
956        let filter = Filter::new(
957            Field::new("range"),
958            FilterOperator::Sl,
959            FilterValue::Single("[1,10)".to_string()),
960        );
961        let (sql, _) = builder.operator_to_sql("\"range\"", &filter).unwrap();
962        assert_eq!(sql, "\"range\" << $1");
963
964        // SR operator (strictly right)
965        builder = QueryBuilder::new();
966        let filter = Filter::new(
967            Field::new("range"),
968            FilterOperator::Sr,
969            FilterValue::Single("[1,10)".to_string()),
970        );
971        let (sql, _) = builder.operator_to_sql("\"range\"", &filter).unwrap();
972        assert_eq!(sql, "\"range\" >> $1");
973
974        // NXL operator (does not extend to right)
975        builder = QueryBuilder::new();
976        let filter = Filter::new(
977            Field::new("range"),
978            FilterOperator::Nxl,
979            FilterValue::Single("[1,10)".to_string()),
980        );
981        let (sql, _) = builder.operator_to_sql("\"range\"", &filter).unwrap();
982        assert_eq!(sql, "\"range\" &< $1");
983
984        // NXR operator (does not extend to left)
985        builder = QueryBuilder::new();
986        let filter = Filter::new(
987            Field::new("range"),
988            FilterOperator::Nxr,
989            FilterValue::Single("[1,10)".to_string()),
990        );
991        let (sql, _) = builder.operator_to_sql("\"range\"", &filter).unwrap();
992        assert_eq!(sql, "\"range\" &> $1");
993
994        // ADJ operator (adjacent)
995        builder = QueryBuilder::new();
996        let filter = Filter::new(
997            Field::new("range"),
998            FilterOperator::Adj,
999            FilterValue::Single("[1,10)".to_string()),
1000        );
1001        let (sql, _) = builder.operator_to_sql("\"range\"", &filter).unwrap();
1002        assert_eq!(sql, "\"range\" -|- $1");
1003    }
1004
1005    #[test]
1006    fn test_operator_to_sql_negated() {
1007        let mut builder = QueryBuilder::new();
1008
1009        // Negated EQ becomes NEQ
1010        let mut filter = Filter::new(
1011            Field::new("status"),
1012            FilterOperator::Eq,
1013            FilterValue::Single("active".to_string()),
1014        );
1015        filter.negated = true;
1016        let (sql, _) = builder.operator_to_sql("\"status\"", &filter).unwrap();
1017        assert_eq!(sql, "\"status\" <> $1");
1018
1019        // Negated GT becomes LTE
1020        builder = QueryBuilder::new();
1021        let mut filter = Filter::new(
1022            Field::new("age"),
1023            FilterOperator::Gt,
1024            FilterValue::Single("18".to_string()),
1025        );
1026        filter.negated = true;
1027        let (sql, _) = builder.operator_to_sql("\"age\"", &filter).unwrap();
1028        assert_eq!(sql, "\"age\" <= $1");
1029
1030        // Negated FTS
1031        builder = QueryBuilder::new();
1032        let mut filter = Filter::new(
1033            Field::new("content"),
1034            FilterOperator::Fts,
1035            FilterValue::Single("search".to_string()),
1036        );
1037        filter.negated = true;
1038        let (sql, _) = builder.operator_to_sql("\"content\"", &filter).unwrap();
1039        assert_eq!(
1040            sql,
1041            "NOT to_tsvector('english', \"content\") @@ plainto_tsquery('english', $1)"
1042        );
1043    }
1044
1045    #[test]
1046    fn test_operator_to_sql_with_quantifiers() {
1047        let mut builder = QueryBuilder::new();
1048
1049        // EQ with ANY quantifier
1050        let mut filter = Filter::new(
1051            Field::new("status"),
1052            FilterOperator::Eq,
1053            FilterValue::List(vec!["active".to_string(), "pending".to_string()]),
1054        );
1055        filter.quantifier = Some(Quantifier::Any);
1056        let (sql, _) = builder.operator_to_sql("\"status\"", &filter).unwrap();
1057        assert_eq!(sql, "\"status\" = ANY($1)");
1058
1059        // EQ with ALL quantifier
1060        builder = QueryBuilder::new();
1061        let mut filter = Filter::new(
1062            Field::new("status"),
1063            FilterOperator::Eq,
1064            FilterValue::List(vec!["active".to_string()]),
1065        );
1066        filter.quantifier = Some(Quantifier::All);
1067        let (sql, _) = builder.operator_to_sql("\"status\"", &filter).unwrap();
1068        assert_eq!(sql, "\"status\" = ALL($1)");
1069    }
1070}
postgrest_parser/sql/builder.rs

postgrest_parser/sql/
builder.rs
