fraiseql_core/db/postgres/
where_generator.rs

1//! PostgreSQL WHERE clause SQL generation.
2
3use std::{
4    collections::{HashMap, HashSet},
5    sync::Arc,
6};
7
8use crate::{
9    db::where_clause::{WhereClause, WhereOperator},
10    error::{FraiseQLError, Result},
11};
12
13/// Cache of indexed columns for views.
14///
15/// This cache stores column names that follow the FraiseQL indexed column naming conventions:
16/// - Human-readable: `items__product__category__code` (double underscore separated path)
17/// - Entity ID format: `f{entity_id}__{field_name}` (e.g., `f200100__code`)
18///
19/// When a WHERE clause references a nested path that has a corresponding indexed column,
20/// the generator will use the indexed column directly instead of JSONB extraction,
21/// enabling the database to use indexes for the query.
22///
23/// # Example
24///
25/// ```rust
26/// use fraiseql_core::db::postgres::IndexedColumnsCache;
27/// use std::collections::{HashMap, HashSet};
28/// use std::sync::Arc;
29///
30/// let mut cache = IndexedColumnsCache::new();
31///
32/// // Register indexed columns for a view
33/// let mut columns = HashSet::new();
34/// columns.insert("items__product__category__code".to_string());
35/// columns.insert("f200100__code".to_string());
36/// cache.insert("v_order_items".to_string(), columns);
37///
38/// // Later, the generator uses this to optimize WHERE clauses
39/// let arc_cache = Arc::new(cache);
40/// ```
41pub type IndexedColumnsCache = HashMap<String, HashSet<String>>;
42
43/// PostgreSQL WHERE clause generator.
44///
45/// Converts `WhereClause` AST to PostgreSQL SQL with parameterized queries.
46///
47/// # Interior Mutability Pattern
48///
49/// This struct uses `Cell<usize>` for the parameter counter. This is safe because:
50///
51/// 1. **Single-threaded usage**: Each WHERE generator is created for a single query execution and
52///    isn't shared across async tasks.
53///
54/// 2. **Reset per call**: The counter is reset at the start of `generate()`, ensuring no state
55///    leakage between calls.
56///
57/// 3. **Performance**: Avoids mutex overhead for a simple counter that needs frequent updates.
58///
59/// # If Shared Across Tasks
60///
61/// If this generator were Arc-shared across multiple async tasks, replace
62/// `Cell<usize>` with `AtomicUsize` to prevent data races:
63///
64/// ```rust,ignore
65/// // Instead of: Cell<usize>
66/// // Use: AtomicUsize
67///
68/// param_counter: std::sync::atomic::AtomicUsize::new(0),
69/// // Then use compare_and_swap or fetch_add operations
70/// ```
71///
72/// # Indexed Column Optimization
73///
74/// When an `IndexedColumnsCache` is provided, the generator checks if nested paths
75/// have corresponding indexed columns. If found, it uses the indexed column directly
76/// instead of JSONB extraction, enabling index usage.
77///
78/// For example, with `items__product__category__code` indexed:
79/// - Without cache: `data->'items'->'product'->'category'->>'code' = $1`
80/// - With cache: `items__product__category__code = $1`
81///
82/// # Example
83///
84/// ```rust
85/// use fraiseql_core::db::postgres::PostgresWhereGenerator;
86/// use fraiseql_core::db::{WhereClause, WhereOperator};
87/// use serde_json::json;
88///
89/// let generator = PostgresWhereGenerator::new();
90///
91/// let clause = WhereClause::Field {
92///     path: vec!["email".to_string()],
93///     operator: WhereOperator::Icontains,
94///     value: json!("example.com"),
95/// };
96///
97/// let (sql, params) = generator.generate(&clause).expect("Failed to generate SQL");
98/// // sql: "data->>'email' ILIKE '%' || $1 || '%'"
99/// // params: ["example.com"]
100/// ```
101pub struct PostgresWhereGenerator {
102    /// Parameter counter for generating placeholder names ($1, $2, etc.)
103    ///
104    /// Uses `Cell<usize>` for interior mutability. Safe because:
105    /// - Single-threaded context (not shared across async tasks)
106    /// - Reset at start of each `generate()` call
107    /// - No concurrent access possible within query execution
108    ///
109    /// See struct documentation for why this is safe and how to fix if shared.
110    param_counter:   std::cell::Cell<usize>,
111    /// Optional indexed columns cache for the current view.
112    /// When set, the generator will use indexed columns instead of JSONB extraction
113    /// for nested paths that have corresponding indexed columns.
114    indexed_columns: Option<Arc<HashSet<String>>>,
115}
116
117impl PostgresWhereGenerator {
118    /// Create new PostgreSQL WHERE generator.
119    #[must_use]
120    pub fn new() -> Self {
121        Self {
122            param_counter:   std::cell::Cell::new(0),
123            indexed_columns: None,
124        }
125    }
126
127    /// Create new PostgreSQL WHERE generator with indexed columns for a view.
128    ///
129    /// When indexed columns are provided, the generator will use them instead of
130    /// JSONB extraction for nested paths that have corresponding indexed columns.
131    ///
132    /// # Arguments
133    ///
134    /// * `indexed_columns` - Set of indexed column names for the current view
135    ///
136    /// # Example
137    ///
138    /// ```rust
139    /// use fraiseql_core::db::postgres::PostgresWhereGenerator;
140    /// use std::collections::HashSet;
141    /// use std::sync::Arc;
142    ///
143    /// let mut columns = HashSet::new();
144    /// columns.insert("items__product__category__code".to_string());
145    /// let generator = PostgresWhereGenerator::with_indexed_columns(Arc::new(columns));
146    /// ```
147    #[must_use]
148    pub fn with_indexed_columns(indexed_columns: Arc<HashSet<String>>) -> Self {
149        Self {
150            param_counter:   std::cell::Cell::new(0),
151            indexed_columns: Some(indexed_columns),
152        }
153    }
154
155    /// Generate SQL WHERE clause and parameters.
156    ///
157    /// # Arguments
158    ///
159    /// * `clause` - WHERE clause AST
160    ///
161    /// # Returns
162    ///
163    /// Returns tuple of (SQL string, parameter values).
164    ///
165    /// # Errors
166    ///
167    /// Returns `FraiseQLError::Validation` if clause is invalid.
168    pub fn generate(&self, clause: &WhereClause) -> Result<(String, Vec<serde_json::Value>)> {
169        self.param_counter.set(0);
170        let mut params = Vec::new();
171        let sql = self.generate_clause(clause, &mut params)?;
172        Ok((sql, params))
173    }
174
175    fn generate_clause(
176        &self,
177        clause: &WhereClause,
178        params: &mut Vec<serde_json::Value>,
179    ) -> Result<String> {
180        match clause {
181            WhereClause::Field {
182                path,
183                operator,
184                value,
185            } => self.generate_field(path, operator, value, params),
186            WhereClause::And(clauses) => {
187                if clauses.is_empty() {
188                    return Ok("TRUE".to_string());
189                }
190                let parts: Result<Vec<String>> =
191                    clauses.iter().map(|c| self.generate_clause(c, params)).collect();
192                Ok(format!("({})", parts?.join(" AND ")))
193            },
194            WhereClause::Or(clauses) => {
195                if clauses.is_empty() {
196                    return Ok("FALSE".to_string());
197                }
198                let parts: Result<Vec<String>> =
199                    clauses.iter().map(|c| self.generate_clause(c, params)).collect();
200                Ok(format!("({})", parts?.join(" OR ")))
201            },
202            WhereClause::Not(clause) => {
203                let inner = self.generate_clause(clause, params)?;
204                Ok(format!("NOT ({inner})"))
205            },
206        }
207    }
208
209    fn generate_field(
210        &self,
211        path: &[String],
212        operator: &WhereOperator,
213        value: &serde_json::Value,
214        params: &mut Vec<serde_json::Value>,
215    ) -> Result<String> {
216        // Build JSONB path accessor
217        let field_path = self.build_jsonb_path(path);
218
219        // Generate operator-specific SQL
220        match operator {
221            // Comparison operators
222            WhereOperator::Eq => self.generate_comparison(&field_path, "=", value, params),
223            WhereOperator::Neq => self.generate_comparison(&field_path, "!=", value, params),
224            WhereOperator::Gt => self.generate_comparison(&field_path, ">", value, params),
225            WhereOperator::Gte => self.generate_comparison(&field_path, ">=", value, params),
226            WhereOperator::Lt => self.generate_comparison(&field_path, "<", value, params),
227            WhereOperator::Lte => self.generate_comparison(&field_path, "<=", value, params),
228
229            // Containment operators
230            WhereOperator::In => self.generate_in(&field_path, value, params),
231            WhereOperator::Nin => {
232                let in_clause = self.generate_in(&field_path, value, params)?;
233                Ok(format!("NOT ({in_clause})"))
234            },
235
236            // String operators
237            WhereOperator::Contains => {
238                self.generate_like(&field_path, "LIKE", value, params, true, true)
239            },
240            WhereOperator::Icontains => {
241                self.generate_like(&field_path, "ILIKE", value, params, true, true)
242            },
243            WhereOperator::Startswith => {
244                self.generate_like(&field_path, "LIKE", value, params, false, true)
245            },
246            WhereOperator::Istartswith => {
247                self.generate_like(&field_path, "ILIKE", value, params, false, true)
248            },
249            WhereOperator::Endswith => {
250                self.generate_like(&field_path, "LIKE", value, params, true, false)
251            },
252            WhereOperator::Iendswith => {
253                self.generate_like(&field_path, "ILIKE", value, params, true, false)
254            },
255            WhereOperator::Like => self.generate_comparison(&field_path, "LIKE", value, params),
256            WhereOperator::Ilike => self.generate_comparison(&field_path, "ILIKE", value, params),
257
258            // Null checks
259            WhereOperator::IsNull => {
260                let is_null = if value.as_bool().unwrap_or(true) {
261                    "IS NULL"
262                } else {
263                    "IS NOT NULL"
264                };
265                Ok(format!("{field_path} {is_null}"))
266            },
267
268            // Array operators
269            WhereOperator::ArrayContains => {
270                self.generate_jsonb_op(&field_path, "@>", value, params)
271            },
272            WhereOperator::ArrayContainedBy => {
273                self.generate_jsonb_op(&field_path, "<@", value, params)
274            },
275            WhereOperator::ArrayOverlaps => {
276                self.generate_jsonb_op(&field_path, "&&", value, params)
277            },
278            WhereOperator::LenEq => self.generate_array_length(&field_path, "=", value, params),
279            WhereOperator::LenGt => self.generate_array_length(&field_path, ">", value, params),
280            WhereOperator::LenLt => self.generate_array_length(&field_path, "<", value, params),
281            WhereOperator::LenGte => self.generate_array_length(&field_path, ">=", value, params),
282            WhereOperator::LenLte => self.generate_array_length(&field_path, "<=", value, params),
283            WhereOperator::LenNeq => self.generate_array_length(&field_path, "!=", value, params),
284
285            // Vector operators (pgvector)
286            WhereOperator::CosineDistance => {
287                self.generate_vector_distance(&field_path, "<=>", value, params)
288            },
289            WhereOperator::L2Distance => {
290                self.generate_vector_distance(&field_path, "<->", value, params)
291            },
292            WhereOperator::L1Distance => {
293                self.generate_vector_distance(&field_path, "<+>", value, params)
294            },
295            WhereOperator::HammingDistance => {
296                self.generate_vector_distance(&field_path, "<~>", value, params)
297            },
298            WhereOperator::InnerProduct => {
299                self.generate_vector_distance(&field_path, "<#>", value, params)
300            },
301            WhereOperator::JaccardDistance => {
302                self.generate_jaccard_distance(&field_path, value, params)
303            },
304
305            // Full-text search
306            WhereOperator::Matches => self.generate_fts(&field_path, "@@", value, params),
307            WhereOperator::PlainQuery => {
308                self.generate_fts_func(&field_path, "plainto_tsquery", value, params)
309            },
310            WhereOperator::PhraseQuery => {
311                self.generate_fts_func(&field_path, "phraseto_tsquery", value, params)
312            },
313            WhereOperator::WebsearchQuery => {
314                self.generate_fts_func(&field_path, "websearch_to_tsquery", value, params)
315            },
316
317            // Network operators
318            WhereOperator::IsIPv4 => Ok(format!("family({field_path}::inet) = 4")),
319            WhereOperator::IsIPv6 => Ok(format!("family({field_path}::inet) = 6")),
320            WhereOperator::IsPrivate => Ok(format!(
321                "({field_path}::inet << '10.0.0.0/8'::inet OR {field_path}::inet << '172.16.0.0/12'::inet OR {field_path}::inet << '192.168.0.0/16'::inet OR {field_path}::inet << '169.254.0.0/16'::inet)"
322            )),
323            WhereOperator::IsPublic => Ok(format!(
324                "NOT ({field_path}::inet << '10.0.0.0/8'::inet OR {field_path}::inet << '172.16.0.0/12'::inet OR {field_path}::inet << '192.168.0.0/16'::inet OR {field_path}::inet << '169.254.0.0/16'::inet)"
325            )),
326            WhereOperator::IsLoopback => Ok(format!(
327                "(family({field_path}::inet) = 4 AND {field_path}::inet << '127.0.0.0/8'::inet) OR (family({field_path}::inet) = 6 AND {field_path}::inet << '::1/128'::inet)"
328            )),
329            WhereOperator::InSubnet => self.generate_inet_op(&field_path, "<<", value, params),
330            WhereOperator::ContainsSubnet => {
331                self.generate_inet_op(&field_path, ">>", value, params)
332            },
333            WhereOperator::ContainsIP => self.generate_inet_op(&field_path, ">>", value, params),
334            WhereOperator::Overlaps => self.generate_inet_op(&field_path, "&&", value, params),
335
336            // JSONB operators
337            WhereOperator::StrictlyContains => {
338                self.generate_jsonb_op(&field_path, "@>", value, params)
339            },
340
341            // LTree operators
342            WhereOperator::AncestorOf => {
343                self.generate_ltree_op(&field_path, "@>", "ltree", value, params)
344            },
345            WhereOperator::DescendantOf => {
346                self.generate_ltree_op(&field_path, "<@", "ltree", value, params)
347            },
348            WhereOperator::MatchesLquery => {
349                self.generate_ltree_op(&field_path, "~", "lquery", value, params)
350            },
351            WhereOperator::MatchesLtxtquery => {
352                self.generate_ltree_op(&field_path, "@", "ltxtquery", value, params)
353            },
354            WhereOperator::MatchesAnyLquery => {
355                self.generate_ltree_array_op(&field_path, value, params)
356            },
357            WhereOperator::DepthEq => self.generate_ltree_depth(&field_path, "=", value, params),
358            WhereOperator::DepthNeq => self.generate_ltree_depth(&field_path, "!=", value, params),
359            WhereOperator::DepthGt => self.generate_ltree_depth(&field_path, ">", value, params),
360            WhereOperator::DepthGte => self.generate_ltree_depth(&field_path, ">=", value, params),
361            WhereOperator::DepthLt => self.generate_ltree_depth(&field_path, "<", value, params),
362            WhereOperator::DepthLte => self.generate_ltree_depth(&field_path, "<=", value, params),
363            WhereOperator::Lca => self.generate_ltree_lca(&field_path, value, params),
364
365            // Extended operators for rich scalar types
366            WhereOperator::Extended(op) => {
367                use crate::filters::ExtendedOperatorHandler;
368                self.generate_extended_sql(op, &field_path, params)
369            },
370        }
371    }
372
373    fn build_jsonb_path(&self, path: &[String]) -> String {
374        // Check if an indexed column exists for this path
375        if let Some(indexed_col) = self.find_indexed_column(path) {
376            // Use the indexed column directly instead of JSONB extraction
377            return format!("\"{indexed_col}\"");
378        }
379
380        // Fall back to JSONB extraction with proper escaping
381        if path.len() == 1 {
382            let escaped = crate::db::path_escape::escape_postgres_jsonb_segment(&path[0]);
383            format!("data->>'{}'", escaped)
384        } else {
385            let escaped_path = crate::db::path_escape::escape_postgres_jsonb_path(path);
386            let mut result = "data".to_string();
387            for (i, segment) in escaped_path.iter().enumerate() {
388                if i < escaped_path.len() - 1 {
389                    result.push_str(&format!("->'{}'", segment));
390                } else {
391                    result.push_str(&format!("->>'{}' ", segment));
392                }
393            }
394            result.trim_end().to_string()
395        }
396    }
397
398    /// Find an indexed column for the given path.
399    ///
400    /// Checks the indexed columns cache for columns matching the path using both
401    /// naming conventions:
402    /// 1. Human-readable: `items__product__category__code`
403    /// 2. Entity ID format: `f{entity_id}__field_name` (not checked here as it requires entity ID)
404    ///
405    /// Returns the column name if found, None otherwise.
406    fn find_indexed_column(&self, path: &[String]) -> Option<String> {
407        let indexed_columns = self.indexed_columns.as_ref()?;
408
409        // Build human-readable column name: join with __
410        let human_readable = path.join("__");
411
412        // Check if this column exists in the cache
413        if indexed_columns.contains(&human_readable) {
414            return Some(human_readable);
415        }
416
417        // Note: Entity ID format (f{entity_id}__field) would require entity ID mapping
418        // which is not available at this level. The DBA can use human-readable names
419        // for most cases. Entity ID format is primarily useful for very long paths
420        // that exceed PostgreSQL's 63-character identifier limit.
421
422        None
423    }
424
425    fn next_param(&self) -> String {
426        let current = self.param_counter.get();
427        self.param_counter.set(current + 1);
428        format!("${}", current + 1)
429    }
430
431    fn generate_comparison(
432        &self,
433        field_path: &str,
434        op: &str,
435        value: &serde_json::Value,
436        params: &mut Vec<serde_json::Value>,
437    ) -> Result<String> {
438        let param = self.next_param();
439        params.push(value.clone());
440
441        // For numeric comparisons, cast both sides to numeric type
442        // Use text format for parameter to avoid wire protocol issues
443        if value.is_number()
444            && (op == ">" || op == ">=" || op == "<" || op == "<=" || op == "=" || op == "!=")
445        {
446            Ok(format!("({field_path})::numeric {op} ({param}::text)::numeric"))
447        } else if value.is_boolean() && (op == "=" || op == "!=") {
448            // For boolean comparisons, cast the JSONB text field to boolean
449            Ok(format!("({field_path})::boolean {op} {param}"))
450        } else {
451            Ok(format!("{field_path} {op} {param}"))
452        }
453    }
454
455    fn generate_in(
456        &self,
457        field_path: &str,
458        value: &serde_json::Value,
459        params: &mut Vec<serde_json::Value>,
460    ) -> Result<String> {
461        let array = value.as_array().ok_or_else(|| {
462            FraiseQLError::validation("IN operator requires array value".to_string())
463        })?;
464
465        if array.is_empty() {
466            return Ok("FALSE".to_string());
467        }
468
469        let placeholders: Vec<String> = array
470            .iter()
471            .map(|v| {
472                let param = self.next_param();
473                params.push(v.clone());
474                param
475            })
476            .collect();
477
478        Ok(format!("{field_path} IN ({})", placeholders.join(", ")))
479    }
480
481    fn generate_like(
482        &self,
483        field_path: &str,
484        op: &str,
485        value: &serde_json::Value,
486        params: &mut Vec<serde_json::Value>,
487        prefix: bool,
488        suffix: bool,
489    ) -> Result<String> {
490        let param = self.next_param();
491        let val_str = value.as_str().ok_or_else(|| {
492            FraiseQLError::validation("LIKE operator requires string value".to_string())
493        })?;
494
495        let pattern = if prefix && suffix {
496            format!("'%' || {param} || '%'")
497        } else if prefix {
498            format!("'%' || {param}")
499        } else if suffix {
500            format!("{param} || '%'")
501        } else {
502            param.clone()
503        };
504
505        params.push(serde_json::Value::String(val_str.to_string()));
506        Ok(format!("{field_path} {op} {pattern}"))
507    }
508
509    fn generate_jsonb_op(
510        &self,
511        field_path: &str,
512        op: &str,
513        value: &serde_json::Value,
514        params: &mut Vec<serde_json::Value>,
515    ) -> Result<String> {
516        let param = self.next_param();
517        params.push(value.clone());
518        Ok(format!("{field_path}::jsonb {op} {param}::jsonb"))
519    }
520
521    fn generate_array_length(
522        &self,
523        field_path: &str,
524        op: &str,
525        value: &serde_json::Value,
526        params: &mut Vec<serde_json::Value>,
527    ) -> Result<String> {
528        let param = self.next_param();
529        params.push(value.clone());
530        Ok(format!("jsonb_array_length({field_path}::jsonb) {op} {param}"))
531    }
532
533    fn generate_vector_distance(
534        &self,
535        field_path: &str,
536        op: &str,
537        value: &serde_json::Value,
538        params: &mut Vec<serde_json::Value>,
539    ) -> Result<String> {
540        let param = self.next_param();
541        params.push(value.clone());
542        Ok(format!("{field_path}::vector {op} {param}::vector"))
543    }
544
545    fn generate_fts(
546        &self,
547        field_path: &str,
548        op: &str,
549        value: &serde_json::Value,
550        params: &mut Vec<serde_json::Value>,
551    ) -> Result<String> {
552        let param = self.next_param();
553        params.push(value.clone());
554        Ok(format!("to_tsvector({field_path}) {op} to_tsquery({param})"))
555    }
556
557    fn generate_fts_func(
558        &self,
559        field_path: &str,
560        func: &str,
561        value: &serde_json::Value,
562        params: &mut Vec<serde_json::Value>,
563    ) -> Result<String> {
564        let param = self.next_param();
565        params.push(value.clone());
566        Ok(format!("to_tsvector({field_path}) @@ {func}({param})"))
567    }
568
569    fn generate_jaccard_distance(
570        &self,
571        field_path: &str,
572        value: &serde_json::Value,
573        params: &mut Vec<serde_json::Value>,
574    ) -> Result<String> {
575        let param = self.next_param();
576        params.push(value.clone());
577        // Jaccard distance uses text arrays
578        Ok(format!("({field_path})::text[] <%> ({param})::text[]"))
579    }
580
581    fn generate_inet_op(
582        &self,
583        field_path: &str,
584        op: &str,
585        value: &serde_json::Value,
586        params: &mut Vec<serde_json::Value>,
587    ) -> Result<String> {
588        let param = self.next_param();
589        params.push(value.clone());
590        Ok(format!("{field_path}::inet {op} {param}::inet"))
591    }
592
593    fn generate_ltree_op(
594        &self,
595        field_path: &str,
596        op: &str,
597        value_type: &str,
598        value: &serde_json::Value,
599        params: &mut Vec<serde_json::Value>,
600    ) -> Result<String> {
601        let param = self.next_param();
602        params.push(value.clone());
603        Ok(format!("{field_path}::ltree {op} {param}::{value_type}"))
604    }
605
606    fn generate_ltree_array_op(
607        &self,
608        field_path: &str,
609        value: &serde_json::Value,
610        params: &mut Vec<serde_json::Value>,
611    ) -> Result<String> {
612        let array = value.as_array().ok_or_else(|| {
613            FraiseQLError::validation(
614                "matches_any_lquery operator requires array value".to_string(),
615            )
616        })?;
617
618        if array.is_empty() {
619            return Ok("FALSE".to_string());
620        }
621
622        let placeholders: Vec<String> = array
623            .iter()
624            .map(|v| {
625                let param = self.next_param();
626                params.push(v.clone());
627                format!("{param}::lquery")
628            })
629            .collect();
630
631        Ok(format!("{field_path}::ltree ? ARRAY[{}]", placeholders.join(", ")))
632    }
633
634    fn generate_ltree_depth(
635        &self,
636        field_path: &str,
637        op: &str,
638        value: &serde_json::Value,
639        params: &mut Vec<serde_json::Value>,
640    ) -> Result<String> {
641        let param = self.next_param();
642        params.push(value.clone());
643        Ok(format!("nlevel({field_path}::ltree) {op} {param}"))
644    }
645
646    fn generate_ltree_lca(
647        &self,
648        field_path: &str,
649        value: &serde_json::Value,
650        params: &mut Vec<serde_json::Value>,
651    ) -> Result<String> {
652        let array = value.as_array().ok_or_else(|| {
653            FraiseQLError::validation("lca operator requires array value".to_string())
654        })?;
655
656        if array.is_empty() {
657            return Err(FraiseQLError::validation(
658                "lca operator requires at least one path".to_string(),
659            ));
660        }
661
662        let placeholders: Vec<String> = array
663            .iter()
664            .map(|v| {
665                let param = self.next_param();
666                params.push(v.clone());
667                format!("{param}::ltree")
668            })
669            .collect();
670
671        Ok(format!("{field_path}::ltree = lca(ARRAY[{}])", placeholders.join(", ")))
672    }
673}
674
675impl Default for PostgresWhereGenerator {
676    fn default() -> Self {
677        Self::new()
678    }
679}
680
681impl crate::filters::ExtendedOperatorHandler for PostgresWhereGenerator {
682    fn generate_extended_sql(
683        &self,
684        operator: &crate::filters::ExtendedOperator,
685        field_sql: &str,
686        params: &mut Vec<serde_json::Value>,
687    ) -> Result<String> {
688        match operator {
689            // Email domain extraction: extract part after @
690            crate::filters::ExtendedOperator::EmailDomainEq(domain) => {
691                params.push(serde_json::Value::String(domain.clone()));
692                let param_idx = params.len();
693                // PostgreSQL: SPLIT_PART(field, '@', 2) = $param_idx
694                Ok(format!("SPLIT_PART({}, '@', 2) = ${}", field_sql, param_idx))
695            },
696
697            crate::filters::ExtendedOperator::EmailDomainIn(domains) => {
698                let placeholders: Vec<String> = domains
699                    .iter()
700                    .map(|d| {
701                        params.push(serde_json::Value::String(d.clone()));
702                        format!("${}", params.len())
703                    })
704                    .collect();
705                Ok(format!("SPLIT_PART({}, '@', 2) IN ({})", field_sql, placeholders.join(", ")))
706            },
707
708            crate::filters::ExtendedOperator::EmailDomainEndswith(suffix) => {
709                params.push(serde_json::Value::String(suffix.clone()));
710                let param_idx = params.len();
711                // PostgreSQL: SPLIT_PART(field, '@', 2) LIKE '%' || $param
712                Ok(format!("SPLIT_PART({}, '@', 2) LIKE '%' || ${}", field_sql, param_idx))
713            },
714
715            crate::filters::ExtendedOperator::EmailLocalPartStartswith(prefix) => {
716                params.push(serde_json::Value::String(prefix.clone()));
717                let param_idx = params.len();
718                // PostgreSQL: SPLIT_PART(field, '@', 1) LIKE $param || '%'
719                Ok(format!("SPLIT_PART({}, '@', 1) LIKE ${} || '%'", field_sql, param_idx))
720            },
721
722            // VIN operations
723            crate::filters::ExtendedOperator::VinWmiEq(wmi) => {
724                params.push(serde_json::Value::String(wmi.clone()));
725                let param_idx = params.len();
726                // PostgreSQL: SUBSTRING(field FROM 1 FOR 3) = $param
727                Ok(format!("SUBSTRING({} FROM 1 FOR 3) = ${}", field_sql, param_idx))
728            },
729
730            // IBAN operations
731            crate::filters::ExtendedOperator::IbanCountryEq(country) => {
732                params.push(serde_json::Value::String(country.clone()));
733                let param_idx = params.len();
734                // PostgreSQL: SUBSTRING(field FROM 1 FOR 2) = $param
735                Ok(format!("SUBSTRING({} FROM 1 FOR 2) = ${}", field_sql, param_idx))
736            },
737
738            // Fallback: not implemented
739            _ => Err(FraiseQLError::validation(format!(
740                "Extended operator not yet implemented: {}",
741                operator
742            ))),
743        }
744    }
745}
746
747#[cfg(test)]
748mod tests {
749    use std::{collections::HashSet, sync::Arc};
750
751    use serde_json::json;
752
753    use super::*;
754
755    #[test]
756    fn test_simple_equality() {
757        let gen = PostgresWhereGenerator::new();
758        let clause = WhereClause::Field {
759            path:     vec!["email".to_string()],
760            operator: WhereOperator::Eq,
761            value:    json!("test@example.com"),
762        };
763
764        let (sql, params) = gen.generate(&clause).unwrap();
765        assert_eq!(sql, "data->>'email' = $1");
766        assert_eq!(params, vec![json!("test@example.com")]);
767    }
768
769    #[test]
770    fn test_icontains() {
771        let gen = PostgresWhereGenerator::new();
772        let clause = WhereClause::Field {
773            path:     vec!["email".to_string()],
774            operator: WhereOperator::Icontains,
775            value:    json!("example.com"),
776        };
777
778        let (sql, params) = gen.generate(&clause).unwrap();
779        assert_eq!(sql, "data->>'email' ILIKE '%' || $1 || '%'");
780        assert_eq!(params, vec![json!("example.com")]);
781    }
782
783    #[test]
784    fn test_nested_path() {
785        let gen = PostgresWhereGenerator::new();
786        let clause = WhereClause::Field {
787            path:     vec!["address".to_string(), "city".to_string()],
788            operator: WhereOperator::Eq,
789            value:    json!("Paris"),
790        };
791
792        let (sql, params) = gen.generate(&clause).unwrap();
793        assert_eq!(sql, "data->'address'->>'city' = $1");
794        assert_eq!(params, vec![json!("Paris")]);
795    }
796
797    #[test]
798    fn test_and_clause() {
799        let gen = PostgresWhereGenerator::new();
800        let clause = WhereClause::And(vec![
801            WhereClause::Field {
802                path:     vec!["age".to_string()],
803                operator: WhereOperator::Gte,
804                value:    json!(18),
805            },
806            WhereClause::Field {
807                path:     vec!["active".to_string()],
808                operator: WhereOperator::Eq,
809                value:    json!(true),
810            },
811        ]);
812
813        let (sql, params) = gen.generate(&clause).unwrap();
814        // Numeric comparisons cast to ::numeric, boolean comparisons cast to ::boolean
815        assert_eq!(
816            sql,
817            "((data->>'age')::numeric >= ($1::text)::numeric AND (data->>'active')::boolean = $2)"
818        );
819        assert_eq!(params, vec![json!(18), json!(true)]);
820    }
821
822    #[test]
823    fn test_or_clause() {
824        let gen = PostgresWhereGenerator::new();
825        let clause = WhereClause::Or(vec![
826            WhereClause::Field {
827                path:     vec!["role".to_string()],
828                operator: WhereOperator::Eq,
829                value:    json!("admin"),
830            },
831            WhereClause::Field {
832                path:     vec!["role".to_string()],
833                operator: WhereOperator::Eq,
834                value:    json!("moderator"),
835            },
836        ]);
837
838        let (sql, params) = gen.generate(&clause).unwrap();
839        assert_eq!(sql, "(data->>'role' = $1 OR data->>'role' = $2)");
840        assert_eq!(params, vec![json!("admin"), json!("moderator")]);
841    }
842
843    #[test]
844    fn test_not_clause() {
845        let gen = PostgresWhereGenerator::new();
846        let clause = WhereClause::Not(Box::new(WhereClause::Field {
847            path:     vec!["deleted".to_string()],
848            operator: WhereOperator::Eq,
849            value:    json!(true),
850        }));
851
852        let (sql, params) = gen.generate(&clause).unwrap();
853        // Boolean comparisons now cast to ::boolean
854        assert_eq!(sql, "NOT ((data->>'deleted')::boolean = $1)");
855        assert_eq!(params, vec![json!(true)]);
856    }
857
858    #[test]
859    fn test_in_operator() {
860        let gen = PostgresWhereGenerator::new();
861        let clause = WhereClause::Field {
862            path:     vec!["status".to_string()],
863            operator: WhereOperator::In,
864            value:    json!(["active", "pending"]),
865        };
866
867        let (sql, params) = gen.generate(&clause).unwrap();
868        assert_eq!(sql, "data->>'status' IN ($1, $2)");
869        assert_eq!(params, vec![json!("active"), json!("pending")]);
870    }
871
872    #[test]
873    fn test_is_null() {
874        let gen = PostgresWhereGenerator::new();
875        let clause = WhereClause::Field {
876            path:     vec!["deleted_at".to_string()],
877            operator: WhereOperator::IsNull,
878            value:    json!(true),
879        };
880
881        let (sql, _params) = gen.generate(&clause).unwrap();
882        assert_eq!(sql, "data->>'deleted_at' IS NULL");
883    }
884
885    #[test]
886    fn test_array_contains() {
887        let gen = PostgresWhereGenerator::new();
888        let clause = WhereClause::Field {
889            path:     vec!["tags".to_string()],
890            operator: WhereOperator::ArrayContains,
891            value:    json!(["rust"]),
892        };
893
894        let (sql, params) = gen.generate(&clause).unwrap();
895        assert_eq!(sql, "data->>'tags'::jsonb @> $1::jsonb");
896        assert_eq!(params, vec![json!(["rust"])]);
897    }
898
899    // ============ LTree Operator Tests ============
900
901    #[test]
902    fn test_ltree_ancestor_of() {
903        let gen = PostgresWhereGenerator::new();
904        let clause = WhereClause::Field {
905            path:     vec!["path".to_string()],
906            operator: WhereOperator::AncestorOf,
907            value:    json!("Top.Sciences.Astronomy"),
908        };
909
910        let (sql, params) = gen.generate(&clause).unwrap();
911        assert_eq!(sql, "data->>'path'::ltree @> $1::ltree");
912        assert_eq!(params, vec![json!("Top.Sciences.Astronomy")]);
913    }
914
915    #[test]
916    fn test_ltree_descendant_of() {
917        let gen = PostgresWhereGenerator::new();
918        let clause = WhereClause::Field {
919            path:     vec!["path".to_string()],
920            operator: WhereOperator::DescendantOf,
921            value:    json!("Top.Sciences"),
922        };
923
924        let (sql, params) = gen.generate(&clause).unwrap();
925        assert_eq!(sql, "data->>'path'::ltree <@ $1::ltree");
926        assert_eq!(params, vec![json!("Top.Sciences")]);
927    }
928
929    #[test]
930    fn test_ltree_matches_lquery() {
931        let gen = PostgresWhereGenerator::new();
932        let clause = WhereClause::Field {
933            path:     vec!["path".to_string()],
934            operator: WhereOperator::MatchesLquery,
935            value:    json!("Top.*.Ast*"),
936        };
937
938        let (sql, params) = gen.generate(&clause).unwrap();
939        assert_eq!(sql, "data->>'path'::ltree ~ $1::lquery");
940        assert_eq!(params, vec![json!("Top.*.Ast*")]);
941    }
942
943    #[test]
944    fn test_ltree_matches_ltxtquery() {
945        let gen = PostgresWhereGenerator::new();
946        let clause = WhereClause::Field {
947            path:     vec!["path".to_string()],
948            operator: WhereOperator::MatchesLtxtquery,
949            value:    json!("Science & !Deprecated"),
950        };
951
952        let (sql, params) = gen.generate(&clause).unwrap();
953        assert_eq!(sql, "data->>'path'::ltree @ $1::ltxtquery");
954        assert_eq!(params, vec![json!("Science & !Deprecated")]);
955    }
956
957    #[test]
958    fn test_ltree_matches_any_lquery() {
959        let gen = PostgresWhereGenerator::new();
960        let clause = WhereClause::Field {
961            path:     vec!["path".to_string()],
962            operator: WhereOperator::MatchesAnyLquery,
963            value:    json!(["Top.*", "Other.*"]),
964        };
965
966        let (sql, params) = gen.generate(&clause).unwrap();
967        assert_eq!(sql, "data->>'path'::ltree ? ARRAY[$1::lquery, $2::lquery]");
968        assert_eq!(params, vec![json!("Top.*"), json!("Other.*")]);
969    }
970
971    #[test]
972    fn test_ltree_depth_eq() {
973        let gen = PostgresWhereGenerator::new();
974        let clause = WhereClause::Field {
975            path:     vec!["path".to_string()],
976            operator: WhereOperator::DepthEq,
977            value:    json!(3),
978        };
979
980        let (sql, params) = gen.generate(&clause).unwrap();
981        assert_eq!(sql, "nlevel(data->>'path'::ltree) = $1");
982        assert_eq!(params, vec![json!(3)]);
983    }
984
985    #[test]
986    fn test_ltree_depth_gt() {
987        let gen = PostgresWhereGenerator::new();
988        let clause = WhereClause::Field {
989            path:     vec!["path".to_string()],
990            operator: WhereOperator::DepthGt,
991            value:    json!(2),
992        };
993
994        let (sql, params) = gen.generate(&clause).unwrap();
995        assert_eq!(sql, "nlevel(data->>'path'::ltree) > $1");
996        assert_eq!(params, vec![json!(2)]);
997    }
998
999    #[test]
1000    fn test_ltree_lca() {
1001        let gen = PostgresWhereGenerator::new();
1002        let clause = WhereClause::Field {
1003            path:     vec!["path".to_string()],
1004            operator: WhereOperator::Lca,
1005            value:    json!(["Org.Engineering.Backend", "Org.Engineering.Frontend"]),
1006        };
1007
1008        let (sql, params) = gen.generate(&clause).unwrap();
1009        assert_eq!(sql, "data->>'path'::ltree = lca(ARRAY[$1::ltree, $2::ltree])");
1010        assert_eq!(
1011            params,
1012            vec![
1013                json!("Org.Engineering.Backend"),
1014                json!("Org.Engineering.Frontend")
1015            ]
1016        );
1017    }
1018
1019    // ============ Indexed Column Optimization Tests ============
1020
1021    #[test]
1022    fn test_indexed_column_simple_path() {
1023        // When an indexed column exists for a simple path, use it directly
1024        let mut indexed = HashSet::new();
1025        indexed.insert("category__code".to_string());
1026        let gen = PostgresWhereGenerator::with_indexed_columns(Arc::new(indexed));
1027
1028        let clause = WhereClause::Field {
1029            path:     vec!["category".to_string(), "code".to_string()],
1030            operator: WhereOperator::Eq,
1031            value:    json!("ELEC"),
1032        };
1033
1034        let (sql, params) = gen.generate(&clause).unwrap();
1035        // Uses indexed column instead of JSONB extraction
1036        assert_eq!(sql, "\"category__code\" = $1");
1037        assert_eq!(params, vec![json!("ELEC")]);
1038    }
1039
1040    #[test]
1041    fn test_indexed_column_nested_path() {
1042        // Deep nested path with indexed column
1043        let mut indexed = HashSet::new();
1044        indexed.insert("items__product__category__code".to_string());
1045        let gen = PostgresWhereGenerator::with_indexed_columns(Arc::new(indexed));
1046
1047        let clause = WhereClause::Field {
1048            path:     vec![
1049                "items".to_string(),
1050                "product".to_string(),
1051                "category".to_string(),
1052                "code".to_string(),
1053            ],
1054            operator: WhereOperator::Eq,
1055            value:    json!("ELEC"),
1056        };
1057
1058        let (sql, params) = gen.generate(&clause).unwrap();
1059        // Uses indexed column instead of deep JSONB extraction
1060        assert_eq!(sql, "\"items__product__category__code\" = $1");
1061        assert_eq!(params, vec![json!("ELEC")]);
1062    }
1063
1064    #[test]
1065    fn test_indexed_column_fallback_to_jsonb() {
1066        // Path without indexed column falls back to JSONB
1067        let mut indexed = HashSet::new();
1068        indexed.insert("items__product__category__code".to_string());
1069        let gen = PostgresWhereGenerator::with_indexed_columns(Arc::new(indexed));
1070
1071        let clause = WhereClause::Field {
1072            path:     vec![
1073                "items".to_string(),
1074                "product".to_string(),
1075                "name".to_string(),
1076            ],
1077            operator: WhereOperator::Eq,
1078            value:    json!("Widget"),
1079        };
1080
1081        let (sql, params) = gen.generate(&clause).unwrap();
1082        // Falls back to JSONB extraction since no indexed column exists
1083        assert_eq!(sql, "data->'items'->'product'->>'name' = $1");
1084        assert_eq!(params, vec![json!("Widget")]);
1085    }
1086
1087    #[test]
1088    fn test_indexed_column_with_like_operator() {
1089        // Indexed columns work with all operators
1090        let mut indexed = HashSet::new();
1091        indexed.insert("category__name".to_string());
1092        let gen = PostgresWhereGenerator::with_indexed_columns(Arc::new(indexed));
1093
1094        let clause = WhereClause::Field {
1095            path:     vec!["category".to_string(), "name".to_string()],
1096            operator: WhereOperator::Icontains,
1097            value:    json!("electronics"),
1098        };
1099
1100        let (sql, params) = gen.generate(&clause).unwrap();
1101        // Uses indexed column with ILIKE operator
1102        assert_eq!(sql, "\"category__name\" ILIKE '%' || $1 || '%'");
1103        assert_eq!(params, vec![json!("electronics")]);
1104    }
1105
1106    #[test]
1107    fn test_indexed_column_with_numeric_comparison() {
1108        // Indexed columns with numeric values
1109        let mut indexed = HashSet::new();
1110        indexed.insert("order__total".to_string());
1111        let gen = PostgresWhereGenerator::with_indexed_columns(Arc::new(indexed));
1112
1113        let clause = WhereClause::Field {
1114            path:     vec!["order".to_string(), "total".to_string()],
1115            operator: WhereOperator::Gt,
1116            value:    json!(100),
1117        };
1118
1119        let (sql, params) = gen.generate(&clause).unwrap();
1120        // Uses indexed column with numeric cast
1121        assert_eq!(sql, "(\"order__total\")::numeric > ($1::text)::numeric");
1122        assert_eq!(params, vec![json!(100)]);
1123    }
1124
1125    #[test]
1126    fn test_indexed_column_empty_cache() {
1127        // Empty cache falls back to JSONB
1128        let indexed = HashSet::new();
1129        let gen = PostgresWhereGenerator::with_indexed_columns(Arc::new(indexed));
1130
1131        let clause = WhereClause::Field {
1132            path:     vec!["category".to_string(), "code".to_string()],
1133            operator: WhereOperator::Eq,
1134            value:    json!("ELEC"),
1135        };
1136
1137        let (sql, params) = gen.generate(&clause).unwrap();
1138        // Falls back to JSONB extraction
1139        assert_eq!(sql, "data->'category'->>'code' = $1");
1140        assert_eq!(params, vec![json!("ELEC")]);
1141    }
1142
1143    #[test]
1144    fn test_no_indexed_columns_cache() {
1145        // No cache provided uses JSONB (default behavior)
1146        let gen = PostgresWhereGenerator::new();
1147
1148        let clause = WhereClause::Field {
1149            path:     vec!["category".to_string(), "code".to_string()],
1150            operator: WhereOperator::Eq,
1151            value:    json!("ELEC"),
1152        };
1153
1154        let (sql, params) = gen.generate(&clause).unwrap();
1155        // Uses JSONB extraction
1156        assert_eq!(sql, "data->'category'->>'code' = $1");
1157        assert_eq!(params, vec![json!("ELEC")]);
1158    }
1159}
fraiseql_core/db/postgres/where_generator.rs

fraiseql_core/db/postgres/
where_generator.rs
