fraiseql_wire/operators/
sql_gen.rs

1//! SQL generation from operators
2//!
3//! Converts operator enums to PostgreSQL WHERE clause SQL strings.
4//! Handles parameter binding, type casting, and operator-specific SQL generation
5//! for both JSONB and direct column sources.
6//!
7//! # Type Casting Strategy
8//!
9//! JSONB fields extracted with `->>` are always text. When comparing with non-string values,
10//! we apply explicit type casting:
11//!
12//! - String comparisons: No cast needed (text = text)
13//! - Numeric comparisons: Cast to integer or float (`text::integer` > $1)
14//! - Boolean comparisons: Cast to boolean (`text::boolean` = true)
15//! - Array comparisons: No special handling (uses array operators)
16//!
17//! Direct columns use native types from the database schema.
18
19use super::{Field, Value, WhereOperator};
20use crate::Result;
21use std::collections::HashMap;
22
23/// Escapes LIKE metacharacters in a literal string.
24///
25/// Escapes `\`, `%`, and `_` so that user-supplied substrings, prefixes, and
26/// suffixes are always treated as literals inside a `LIKE` or `ILIKE` pattern.
27/// PostgreSQL uses `\` as the default LIKE escape character.
28fn escape_like_literal(s: &str) -> String {
29    // Order matters: escape `\` first to avoid double-escaping.
30    s.replace('\\', "\\\\")
31        .replace('%', "\\%")
32        .replace('_', "\\_")
33}
34
35/// Infers the PostgreSQL type cast needed for a value
36///
37/// Returns the type cast suffix (e.g., "`::integer`", "`::text`") if needed
38const fn infer_type_cast(value: &Value) -> &'static str {
39    match value {
40        Value::String(_) => "::text",
41        Value::Number(_) => "::numeric", // numeric handles both int and float
42        Value::Bool(_) => "::boolean",
43        Value::Null => "",          // no cast for NULL
44        Value::Array(_) => "",      // arrays handled by operators
45        Value::FloatArray(_) => "", // vector operators handle their own casting
46        Value::RawSql(_) => "",     // raw SQL is assumed correct
47    }
48}
49
50/// Generates SQL from a WHERE operator with parameter binding support
51///
52/// # Parameters
53///
54/// - `operator`: The WHERE operator to generate SQL for
55/// - `param_index`: Mutable reference to parameter counter (for $1, $2, etc.)
56/// - `params`: Mutable map to accumulate parameter values (for later binding)
57///
58/// # Returns
59///
60/// SQL string with parameter placeholders ($1, $2, etc.)
61///
62/// # Errors
63///
64/// Returns `WireError::InvalidSchema` if the operator fails validation (e.g., invalid
65/// field names or unsupported value types for the given operator).
66///
67/// # Examples
68///
69/// ```no_run
70/// // Requires: fraiseql_wire::operators re-exports; Value has no PartialEq so assert_eq on params omitted.
71/// use std::collections::HashMap;
72/// use fraiseql_wire::operators::{Field, Value, WhereOperator, generate_where_operator_sql};
73/// let mut param_index = 0;
74/// let mut params = HashMap::new();
75/// let op = WhereOperator::Eq(Field::JsonbField("name".to_string()), Value::String("John".to_string()));
76/// let sql = generate_where_operator_sql(&op, &mut param_index, &mut params).unwrap();
77/// assert_eq!(sql, "(data->'name')::text = $1");
78/// ```
79pub fn generate_where_operator_sql(
80    operator: &WhereOperator,
81    param_index: &mut usize,
82    params: &mut HashMap<usize, Value>,
83) -> Result<String> {
84    operator
85        .validate()
86        .map_err(crate::WireError::InvalidSchema)?;
87
88    match operator {
89        // ============ Comparison Operators ============
90        // These operators work on both JSONB and direct columns.
91        // For JSONB text extraction, we apply type casting for proper comparison.
92        WhereOperator::Eq(field, value) => {
93            let field_sql = field.to_sql();
94            if value.is_null() {
95                Ok(format!("{} IS NULL", field_sql))
96            } else {
97                let param_num = *param_index + 1;
98                *param_index += 1;
99                params.insert(param_num, value.clone());
100                // JSONB fields need type cast for non-string comparisons
101                let cast = match field {
102                    Field::JsonbField(_) | Field::JsonbPath(_) => infer_type_cast(value),
103                    Field::DirectColumn(_) => "", // direct columns use native types
104                };
105                Ok(format!("{}{} = ${}", field_sql, cast, param_num))
106            }
107        }
108
109        WhereOperator::Neq(field, value) => {
110            let field_sql = field.to_sql();
111            if value.is_null() {
112                Ok(format!("{} IS NOT NULL", field_sql))
113            } else {
114                let param_num = *param_index + 1;
115                *param_index += 1;
116                params.insert(param_num, value.clone());
117                let cast = match field {
118                    Field::JsonbField(_) | Field::JsonbPath(_) => infer_type_cast(value),
119                    Field::DirectColumn(_) => "",
120                };
121                Ok(format!("{}{} != ${}", field_sql, cast, param_num))
122            }
123        }
124
125        WhereOperator::Gt(field, value) => {
126            let field_sql = field.to_sql();
127            let param_num = *param_index + 1;
128            *param_index += 1;
129            params.insert(param_num, value.clone());
130            let cast = match field {
131                Field::JsonbField(_) | Field::JsonbPath(_) => infer_type_cast(value),
132                Field::DirectColumn(_) => "",
133            };
134            Ok(format!("{}{} > ${}", field_sql, cast, param_num))
135        }
136
137        WhereOperator::Gte(field, value) => {
138            let field_sql = field.to_sql();
139            let param_num = *param_index + 1;
140            *param_index += 1;
141            params.insert(param_num, value.clone());
142            let cast = match field {
143                Field::JsonbField(_) | Field::JsonbPath(_) => infer_type_cast(value),
144                Field::DirectColumn(_) => "",
145            };
146            Ok(format!("{}{} >= ${}", field_sql, cast, param_num))
147        }
148
149        WhereOperator::Lt(field, value) => {
150            let field_sql = field.to_sql();
151            let param_num = *param_index + 1;
152            *param_index += 1;
153            params.insert(param_num, value.clone());
154            let cast = match field {
155                Field::JsonbField(_) | Field::JsonbPath(_) => infer_type_cast(value),
156                Field::DirectColumn(_) => "",
157            };
158            Ok(format!("{}{} < ${}", field_sql, cast, param_num))
159        }
160
161        WhereOperator::Lte(field, value) => {
162            let field_sql = field.to_sql();
163            let param_num = *param_index + 1;
164            *param_index += 1;
165            params.insert(param_num, value.clone());
166            let cast = match field {
167                Field::JsonbField(_) | Field::JsonbPath(_) => infer_type_cast(value),
168                Field::DirectColumn(_) => "",
169            };
170            Ok(format!("{}{} <= ${}", field_sql, cast, param_num))
171        }
172
173        // ============ Array Operators ============
174        WhereOperator::In(field, values) => {
175            // Empty IN () is a syntax error in all databases; semantically equivalent to FALSE.
176            if values.is_empty() {
177                return Ok("FALSE".to_string());
178            }
179            let field_sql = field.to_sql();
180            let placeholders: Vec<String> = values
181                .iter()
182                .map(|v| {
183                    let param_num = *param_index + 1;
184                    *param_index += 1;
185                    params.insert(param_num, v.clone());
186                    format!("${}", param_num)
187                })
188                .collect();
189            Ok(format!("{} IN ({})", field_sql, placeholders.join(", ")))
190        }
191
192        WhereOperator::Nin(field, values) => {
193            // Empty NOT IN () is a syntax error in all databases; semantically equivalent to TRUE.
194            if values.is_empty() {
195                return Ok("TRUE".to_string());
196            }
197            let field_sql = field.to_sql();
198            let placeholders: Vec<String> = values
199                .iter()
200                .map(|v| {
201                    let param_num = *param_index + 1;
202                    *param_index += 1;
203                    params.insert(param_num, v.clone());
204                    format!("${}", param_num)
205                })
206                .collect();
207            Ok(format!(
208                "{} NOT IN ({})",
209                field_sql,
210                placeholders.join(", ")
211            ))
212        }
213
214        WhereOperator::Contains(field, substring) => {
215            let field_sql = field.to_sql();
216            let param_num = *param_index + 1;
217            *param_index += 1;
218            params.insert(param_num, Value::String(escape_like_literal(substring)));
219            Ok(format!(
220                "{} LIKE '%' || ${}::text || '%'",
221                field_sql, param_num
222            ))
223        }
224
225        WhereOperator::ArrayContains(field, value) => {
226            let field_sql = field.to_sql();
227            let param_num = *param_index + 1;
228            *param_index += 1;
229            params.insert(param_num, value.clone());
230            Ok(format!("{} @> ARRAY[${}]", field_sql, param_num))
231        }
232
233        WhereOperator::ArrayContainedBy(field, value) => {
234            let field_sql = field.to_sql();
235            let param_num = *param_index + 1;
236            *param_index += 1;
237            params.insert(param_num, value.clone());
238            Ok(format!("{} <@ ARRAY[${}]", field_sql, param_num))
239        }
240
241        WhereOperator::ArrayOverlaps(field, values) => {
242            let field_sql = field.to_sql();
243            let placeholders: Vec<String> = values
244                .iter()
245                .map(|v| {
246                    let param_num = *param_index + 1;
247                    *param_index += 1;
248                    params.insert(param_num, v.clone());
249                    format!("${}", param_num)
250                })
251                .collect();
252            Ok(format!(
253                "{} && ARRAY[{}]",
254                field_sql,
255                placeholders.join(", ")
256            ))
257        }
258
259        // ============ Array Length Operators ============
260        WhereOperator::LenEq(field, len) => {
261            let field_sql = field.to_sql();
262            Ok(format!("array_length({}, 1) = {}", field_sql, len))
263        }
264
265        WhereOperator::LenGt(field, len) => {
266            let field_sql = field.to_sql();
267            Ok(format!("array_length({}, 1) > {}", field_sql, len))
268        }
269
270        WhereOperator::LenGte(field, len) => {
271            let field_sql = field.to_sql();
272            Ok(format!("array_length({}, 1) >= {}", field_sql, len))
273        }
274
275        WhereOperator::LenLt(field, len) => {
276            let field_sql = field.to_sql();
277            Ok(format!("array_length({}, 1) < {}", field_sql, len))
278        }
279
280        WhereOperator::LenLte(field, len) => {
281            let field_sql = field.to_sql();
282            Ok(format!("array_length({}, 1) <= {}", field_sql, len))
283        }
284
285        // ============ String Operators ============
286        WhereOperator::Icontains(field, substring) => {
287            let field_sql = field.to_sql();
288            let param_num = *param_index + 1;
289            *param_index += 1;
290            params.insert(param_num, Value::String(escape_like_literal(substring)));
291            Ok(format!(
292                "{} ILIKE '%' || ${}::text || '%'",
293                field_sql, param_num
294            ))
295        }
296
297        WhereOperator::Startswith(field, prefix) => {
298            let field_sql = field.to_sql();
299            let param_num = *param_index + 1;
300            *param_index += 1;
301            params.insert(
302                param_num,
303                Value::String(format!("{}%", escape_like_literal(prefix))),
304            );
305            Ok(format!("{} LIKE ${}", field_sql, param_num))
306        }
307
308        WhereOperator::Istartswith(field, prefix) => {
309            let field_sql = field.to_sql();
310            let param_num = *param_index + 1;
311            *param_index += 1;
312            params.insert(
313                param_num,
314                Value::String(format!("{}%", escape_like_literal(prefix))),
315            );
316            Ok(format!("{} ILIKE ${}", field_sql, param_num))
317        }
318
319        WhereOperator::Endswith(field, suffix) => {
320            let field_sql = field.to_sql();
321            let param_num = *param_index + 1;
322            *param_index += 1;
323            params.insert(
324                param_num,
325                Value::String(format!("%{}", escape_like_literal(suffix))),
326            );
327            Ok(format!("{} LIKE ${}", field_sql, param_num))
328        }
329
330        WhereOperator::Iendswith(field, suffix) => {
331            let field_sql = field.to_sql();
332            let param_num = *param_index + 1;
333            *param_index += 1;
334            params.insert(
335                param_num,
336                Value::String(format!("%{}", escape_like_literal(suffix))),
337            );
338            Ok(format!("{} ILIKE ${}", field_sql, param_num))
339        }
340
341        WhereOperator::Like(field, pattern) => {
342            let field_sql = field.to_sql();
343            let param_num = *param_index + 1;
344            *param_index += 1;
345            params.insert(param_num, Value::String(pattern.clone()));
346            Ok(format!("{} LIKE ${}", field_sql, param_num))
347        }
348
349        WhereOperator::Ilike(field, pattern) => {
350            let field_sql = field.to_sql();
351            let param_num = *param_index + 1;
352            *param_index += 1;
353            params.insert(param_num, Value::String(pattern.clone()));
354            Ok(format!("{} ILIKE ${}", field_sql, param_num))
355        }
356
357        // ============ Null Operator ============
358        WhereOperator::IsNull(field, is_null) => {
359            let field_sql = field.to_sql();
360            if *is_null {
361                Ok(format!("{} IS NULL", field_sql))
362            } else {
363                Ok(format!("{} IS NOT NULL", field_sql))
364            }
365        }
366
367        // ============ Vector Distance Operators (pgvector) ============
368        WhereOperator::L2Distance {
369            field,
370            vector,
371            threshold,
372        } => {
373            let field_sql = field.to_sql();
374            let param_num = *param_index + 1;
375            *param_index += 1;
376            params.insert(param_num, Value::FloatArray(vector.clone()));
377            Ok(format!(
378                "l2_distance({}::vector, ${}::vector) < {}",
379                field_sql, param_num, threshold
380            ))
381        }
382
383        WhereOperator::CosineDistance {
384            field,
385            vector,
386            threshold,
387        } => {
388            let field_sql = field.to_sql();
389            let param_num = *param_index + 1;
390            *param_index += 1;
391            params.insert(param_num, Value::FloatArray(vector.clone()));
392            Ok(format!(
393                "cosine_distance({}::vector, ${}::vector) < {}",
394                field_sql, param_num, threshold
395            ))
396        }
397
398        WhereOperator::InnerProduct {
399            field,
400            vector,
401            threshold,
402        } => {
403            let field_sql = field.to_sql();
404            let param_num = *param_index + 1;
405            *param_index += 1;
406            params.insert(param_num, Value::FloatArray(vector.clone()));
407            Ok(format!(
408                "inner_product({}::vector, ${}::vector) > {}",
409                field_sql, param_num, threshold
410            ))
411        }
412
413        WhereOperator::L1Distance {
414            field,
415            vector,
416            threshold,
417        } => {
418            let field_sql = field.to_sql();
419            let param_num = *param_index + 1;
420            *param_index += 1;
421            params.insert(param_num, Value::FloatArray(vector.clone()));
422            Ok(format!(
423                "l1_distance({}::vector, ${}::vector) < {}",
424                field_sql, param_num, threshold
425            ))
426        }
427
428        WhereOperator::HammingDistance {
429            field,
430            vector,
431            threshold,
432        } => {
433            let field_sql = field.to_sql();
434            let param_num = *param_index + 1;
435            *param_index += 1;
436            params.insert(param_num, Value::FloatArray(vector.clone()));
437            Ok(format!(
438                "hamming_distance({}::bit, ${}::bit) < {}",
439                field_sql, param_num, threshold
440            ))
441        }
442
443        WhereOperator::JaccardDistance {
444            field,
445            set,
446            threshold,
447        } => {
448            let field_sql = field.to_sql();
449            let param_num = *param_index + 1;
450            *param_index += 1;
451            let value_array: Vec<Value> = set.iter().map(|s| Value::String(s.clone())).collect();
452            params.insert(param_num, Value::Array(value_array));
453            Ok(format!(
454                "jaccard_distance({}::text[], ${}::text[]) < {}",
455                field_sql, param_num, threshold
456            ))
457        }
458
459        // ============ Full-Text Search Operators ============
460        WhereOperator::Matches {
461            field,
462            query,
463            language,
464        } => {
465            let field_sql = field.to_sql();
466            let param_num = *param_index + 1;
467            *param_index += 1;
468            params.insert(param_num, Value::String(query.clone()));
469            let lang = language.as_deref().unwrap_or("english");
470            Ok(format!(
471                "{} @@ plainto_tsquery('{}', ${})",
472                field_sql, lang, param_num
473            ))
474        }
475
476        WhereOperator::PlainQuery { field, query } => {
477            let field_sql = field.to_sql();
478            let param_num = *param_index + 1;
479            *param_index += 1;
480            params.insert(param_num, Value::String(query.clone()));
481            Ok(format!(
482                "{} @@ plainto_tsquery(${})::tsvector",
483                field_sql, param_num
484            ))
485        }
486
487        WhereOperator::PhraseQuery {
488            field,
489            query,
490            language,
491        } => {
492            let field_sql = field.to_sql();
493            let param_num = *param_index + 1;
494            *param_index += 1;
495            params.insert(param_num, Value::String(query.clone()));
496            let lang = language.as_deref().unwrap_or("english");
497            Ok(format!(
498                "{} @@ phraseto_tsquery('{}', ${})",
499                field_sql, lang, param_num
500            ))
501        }
502
503        WhereOperator::WebsearchQuery {
504            field,
505            query,
506            language,
507        } => {
508            let field_sql = field.to_sql();
509            let param_num = *param_index + 1;
510            *param_index += 1;
511            params.insert(param_num, Value::String(query.clone()));
512            let lang = language.as_deref().unwrap_or("english");
513            Ok(format!(
514                "{} @@ websearch_to_tsquery('{}', ${})",
515                field_sql, lang, param_num
516            ))
517        }
518
519        // ============ Network/INET Operators ============
520        WhereOperator::IsIPv4(field) => {
521            let field_sql = field.to_sql();
522            Ok(format!("family({}::inet) = 4", field_sql))
523        }
524
525        WhereOperator::IsIPv6(field) => {
526            let field_sql = field.to_sql();
527            Ok(format!("family({}::inet) = 6", field_sql))
528        }
529
530        WhereOperator::IsPrivate(field) => {
531            let field_sql = field.to_sql();
532            // RFC1918 private ranges + link-local
533            Ok(format!(
534                "({}::inet << '10.0.0.0/8'::inet OR {}::inet << '172.16.0.0/12'::inet OR {}::inet << '192.168.0.0/16'::inet OR {}::inet << '169.254.0.0/16'::inet)",
535                field_sql, field_sql, field_sql, field_sql
536            ))
537        }
538
539        WhereOperator::IsPublic(field) => {
540            let field_sql = field.to_sql();
541            // NOT private (opposite of IsPrivate)
542            Ok(format!(
543                "NOT ({}::inet << '10.0.0.0/8'::inet OR {}::inet << '172.16.0.0/12'::inet OR {}::inet << '192.168.0.0/16'::inet OR {}::inet << '169.254.0.0/16'::inet)",
544                field_sql, field_sql, field_sql, field_sql
545            ))
546        }
547
548        WhereOperator::IsLoopback(field) => {
549            let field_sql = field.to_sql();
550            Ok(format!(
551                "(family({}::inet) = 4 AND {}::inet << '127.0.0.0/8'::inet) OR (family({}::inet) = 6 AND {}::inet << '::1/128'::inet)",
552                field_sql, field_sql, field_sql, field_sql
553            ))
554        }
555
556        WhereOperator::InSubnet { field, subnet } => {
557            let field_sql = field.to_sql();
558            let param_num = *param_index + 1;
559            *param_index += 1;
560            params.insert(param_num, Value::String(subnet.clone()));
561            Ok(format!("{}::inet << ${}::inet", field_sql, param_num))
562        }
563
564        WhereOperator::ContainsSubnet { field, subnet } => {
565            let field_sql = field.to_sql();
566            let param_num = *param_index + 1;
567            *param_index += 1;
568            params.insert(param_num, Value::String(subnet.clone()));
569            Ok(format!("{}::inet >> ${}::inet", field_sql, param_num))
570        }
571
572        WhereOperator::ContainsIP { field, ip } => {
573            let field_sql = field.to_sql();
574            let param_num = *param_index + 1;
575            *param_index += 1;
576            params.insert(param_num, Value::String(ip.clone()));
577            Ok(format!("{}::inet >> ${}::inet", field_sql, param_num))
578        }
579
580        WhereOperator::IPRangeOverlap { field, range } => {
581            let field_sql = field.to_sql();
582            let param_num = *param_index + 1;
583            *param_index += 1;
584            params.insert(param_num, Value::String(range.clone()));
585            Ok(format!("{}::inet && ${}::inet", field_sql, param_num))
586        }
587
588        // ============ JSONB Operators ============
589        WhereOperator::StrictlyContains(field, value) => {
590            let field_sql = field.to_sql();
591            let param_num = *param_index + 1;
592            *param_index += 1;
593            params.insert(param_num, value.clone());
594            Ok(format!("{}::jsonb @> ${}::jsonb", field_sql, param_num))
595        }
596
597        // ============ LTree Operators ============
598        WhereOperator::AncestorOf { field, path } => {
599            let field_sql = field.to_sql();
600            let param_num = *param_index + 1;
601            *param_index += 1;
602            params.insert(param_num, Value::String(path.clone()));
603            Ok(format!("{}::ltree @> ${}::ltree", field_sql, param_num))
604        }
605
606        WhereOperator::DescendantOf { field, path } => {
607            let field_sql = field.to_sql();
608            let param_num = *param_index + 1;
609            *param_index += 1;
610            params.insert(param_num, Value::String(path.clone()));
611            Ok(format!("{}::ltree <@ ${}::ltree", field_sql, param_num))
612        }
613
614        WhereOperator::MatchesLquery { field, pattern } => {
615            let field_sql = field.to_sql();
616            let param_num = *param_index + 1;
617            *param_index += 1;
618            params.insert(param_num, Value::String(pattern.clone()));
619            Ok(format!("{}::ltree ~ ${}::lquery", field_sql, param_num))
620        }
621
622        WhereOperator::MatchesLtxtquery { field, query } => {
623            let field_sql = field.to_sql();
624            let param_num = *param_index + 1;
625            *param_index += 1;
626            params.insert(param_num, Value::String(query.clone()));
627            Ok(format!("{}::ltree @ ${}::ltxtquery", field_sql, param_num))
628        }
629
630        WhereOperator::MatchesAnyLquery { field, patterns } => {
631            let field_sql = field.to_sql();
632            let placeholders: Vec<String> = patterns
633                .iter()
634                .map(|p| {
635                    let param_num = *param_index + 1;
636                    *param_index += 1;
637                    params.insert(param_num, Value::String(p.clone()));
638                    format!("${}::lquery", param_num)
639                })
640                .collect();
641            Ok(format!(
642                "{}::ltree ? ARRAY[{}]",
643                field_sql,
644                placeholders.join(", ")
645            ))
646        }
647
648        // ============ LTree Depth Operators ============
649        WhereOperator::DepthEq { field, depth } => {
650            let field_sql = field.to_sql();
651            Ok(format!("nlevel({}::ltree) = {}", field_sql, depth))
652        }
653
654        WhereOperator::DepthNeq { field, depth } => {
655            let field_sql = field.to_sql();
656            Ok(format!("nlevel({}::ltree) != {}", field_sql, depth))
657        }
658
659        WhereOperator::DepthGt { field, depth } => {
660            let field_sql = field.to_sql();
661            Ok(format!("nlevel({}::ltree) > {}", field_sql, depth))
662        }
663
664        WhereOperator::DepthGte { field, depth } => {
665            let field_sql = field.to_sql();
666            Ok(format!("nlevel({}::ltree) >= {}", field_sql, depth))
667        }
668
669        WhereOperator::DepthLt { field, depth } => {
670            let field_sql = field.to_sql();
671            Ok(format!("nlevel({}::ltree) < {}", field_sql, depth))
672        }
673
674        WhereOperator::DepthLte { field, depth } => {
675            let field_sql = field.to_sql();
676            Ok(format!("nlevel({}::ltree) <= {}", field_sql, depth))
677        }
678
679        // ============ LTree LCA Operator ============
680        WhereOperator::Lca { field, paths } => {
681            let field_sql = field.to_sql();
682            let placeholders: Vec<String> = paths
683                .iter()
684                .map(|p| {
685                    let param_num = *param_index + 1;
686                    *param_index += 1;
687                    params.insert(param_num, Value::String(p.clone()));
688                    format!("${}::ltree", param_num)
689                })
690                .collect();
691            Ok(format!(
692                "{}::ltree = lca(ARRAY[{}])",
693                field_sql,
694                placeholders.join(", ")
695            ))
696        }
697    }
698}
699
700#[cfg(test)]
701mod tests {
702    #![allow(clippy::unwrap_used)] // Reason: test code, panics are acceptable
703    use super::*;
704
705    #[test]
706    fn test_eq_operator_jsonb_string() {
707        let mut param_index = 0;
708        let mut params = HashMap::new();
709        let op = WhereOperator::Eq(
710            Field::JsonbField("name".to_string()),
711            Value::String("John".to_string()),
712        );
713        let sql = generate_where_operator_sql(&op, &mut param_index, &mut params).unwrap();
714        // JSONB string fields get ::text cast for proper text comparison
715        assert_eq!(sql, "(data->'name')::text = $1");
716        assert_eq!(param_index, 1);
717    }
718
719    #[test]
720    fn test_eq_operator_direct_column() {
721        let mut param_index = 0;
722        let mut params = HashMap::new();
723        let op = WhereOperator::Eq(
724            Field::DirectColumn("status".to_string()),
725            Value::String("active".to_string()),
726        );
727        let sql = generate_where_operator_sql(&op, &mut param_index, &mut params).unwrap();
728        // Direct columns don't need casting (use native types)
729        assert_eq!(sql, "status = $1");
730        assert_eq!(param_index, 1);
731    }
732
733    #[test]
734    fn test_len_eq_operator() {
735        let mut param_index = 0;
736        let mut params = HashMap::new();
737        let op = WhereOperator::LenEq(Field::JsonbField("tags".to_string()), 5);
738        let sql = generate_where_operator_sql(&op, &mut param_index, &mut params).unwrap();
739        assert_eq!(sql, "array_length((data->'tags'), 1) = 5");
740        assert_eq!(param_index, 0); // No parameters for length operators
741    }
742
743    #[test]
744    fn test_is_ipv4_operator() {
745        let mut param_index = 0;
746        let mut params = HashMap::new();
747        let op = WhereOperator::IsIPv4(Field::JsonbField("ip".to_string()));
748        let sql = generate_where_operator_sql(&op, &mut param_index, &mut params).unwrap();
749        assert_eq!(sql, "family((data->'ip')::inet) = 4");
750    }
751
752    #[test]
753    fn test_l2_distance_operator() {
754        let mut param_index = 0;
755        let mut params = HashMap::new();
756        let op = WhereOperator::L2Distance {
757            field: Field::JsonbField("embedding".to_string()),
758            vector: vec![0.1, 0.2, 0.3],
759            threshold: 0.5,
760        };
761        let sql = generate_where_operator_sql(&op, &mut param_index, &mut params).unwrap();
762        assert_eq!(
763            sql,
764            "l2_distance((data->'embedding')::vector, $1::vector) < 0.5"
765        );
766        assert_eq!(param_index, 1);
767    }
768
769    #[test]
770    fn test_in_operator() {
771        let mut param_index = 0;
772        let mut params = HashMap::new();
773        let op = WhereOperator::In(
774            Field::JsonbField("status".to_string()),
775            vec![
776                Value::String("active".to_string()),
777                Value::String("pending".to_string()),
778            ],
779        );
780        let sql = generate_where_operator_sql(&op, &mut param_index, &mut params).unwrap();
781        assert_eq!(sql, "(data->'status') IN ($1, $2)");
782        assert_eq!(param_index, 2);
783    }
784
785    #[test]
786    fn test_in_empty_list_returns_false() {
787        let mut param_index = 0;
788        let mut params = HashMap::new();
789        let op = WhereOperator::In(Field::DirectColumn("status".to_string()), vec![]);
790        let sql = generate_where_operator_sql(&op, &mut param_index, &mut params).unwrap();
791        assert_eq!(sql, "FALSE");
792        assert_eq!(param_index, 0, "no parameters consumed for empty IN");
793    }
794
795    #[test]
796    fn test_nin_empty_list_returns_true() {
797        let mut param_index = 0;
798        let mut params = HashMap::new();
799        let op = WhereOperator::Nin(Field::DirectColumn("status".to_string()), vec![]);
800        let sql = generate_where_operator_sql(&op, &mut param_index, &mut params).unwrap();
801        assert_eq!(sql, "TRUE");
802        assert_eq!(param_index, 0, "no parameters consumed for empty NOT IN");
803    }
804
805    // Helper: extract the inner string from Value::String via Debug, panics otherwise.
806    fn value_as_str(v: &Value) -> &str {
807        match v {
808            Value::String(s) => s.as_str(),
809            other => panic!("expected Value::String, got {other:?}"),
810        }
811    }
812
813    #[test]
814    fn test_contains_escapes_percent() {
815        let mut param_index = 0;
816        let mut params = HashMap::new();
817        let op =
818            WhereOperator::Contains(Field::DirectColumn("note".to_string()), "50%".to_string());
819        generate_where_operator_sql(&op, &mut param_index, &mut params).unwrap();
820        assert_eq!(value_as_str(&params[&1]), "50\\%");
821    }
822
823    #[test]
824    fn test_contains_escapes_underscore() {
825        let mut param_index = 0;
826        let mut params = HashMap::new();
827        let op =
828            WhereOperator::Contains(Field::DirectColumn("code".to_string()), "A_B".to_string());
829        generate_where_operator_sql(&op, &mut param_index, &mut params).unwrap();
830        assert_eq!(value_as_str(&params[&1]), "A\\_B");
831    }
832
833    #[test]
834    fn test_startswith_escapes_wildcard_in_prefix() {
835        let mut param_index = 0;
836        let mut params = HashMap::new();
837        let op =
838            WhereOperator::Startswith(Field::DirectColumn("name".to_string()), "C%D".to_string());
839        generate_where_operator_sql(&op, &mut param_index, &mut params).unwrap();
840        // prefix escaped, trailing % appended for LIKE
841        assert_eq!(value_as_str(&params[&1]), "C\\%D%");
842    }
843
844    #[test]
845    fn test_endswith_escapes_wildcard_in_suffix() {
846        let mut param_index = 0;
847        let mut params = HashMap::new();
848        let op = WhereOperator::Endswith(
849            Field::DirectColumn("name".to_string()),
850            "_suffix".to_string(),
851        );
852        generate_where_operator_sql(&op, &mut param_index, &mut params).unwrap();
853        // suffix escaped, leading % prepended for LIKE
854        assert_eq!(value_as_str(&params[&1]), "%\\_suffix");
855    }
856
857    #[test]
858    fn test_escape_like_literal_backslash() {
859        assert_eq!(escape_like_literal("a\\b"), "a\\\\b");
860        assert_eq!(escape_like_literal("a%b"), "a\\%b");
861        assert_eq!(escape_like_literal("a_b"), "a\\_b");
862        // Combined: order matters — backslash must be escaped first
863        assert_eq!(escape_like_literal("100%_\\n"), "100\\%\\_\\\\n");
864    }
865
866    // ============ LTree Operator Tests ============
867
868    #[test]
869    fn test_ltree_ancestor_of() {
870        let mut param_index = 0;
871        let mut params = HashMap::new();
872        let op = WhereOperator::AncestorOf {
873            field: Field::DirectColumn("path".to_string()),
874            path: "Top.Sciences.Astronomy".to_string(),
875        };
876        let sql = generate_where_operator_sql(&op, &mut param_index, &mut params).unwrap();
877        assert_eq!(sql, "path::ltree @> $1::ltree");
878        assert_eq!(param_index, 1);
879    }
880
881    #[test]
882    fn test_ltree_descendant_of() {
883        let mut param_index = 0;
884        let mut params = HashMap::new();
885        let op = WhereOperator::DescendantOf {
886            field: Field::DirectColumn("path".to_string()),
887            path: "Top.Sciences".to_string(),
888        };
889        let sql = generate_where_operator_sql(&op, &mut param_index, &mut params).unwrap();
890        assert_eq!(sql, "path::ltree <@ $1::ltree");
891        assert_eq!(param_index, 1);
892    }
893
894    #[test]
895    fn test_ltree_matches_lquery() {
896        let mut param_index = 0;
897        let mut params = HashMap::new();
898        let op = WhereOperator::MatchesLquery {
899            field: Field::DirectColumn("path".to_string()),
900            pattern: "Top.*.Ast*".to_string(),
901        };
902        let sql = generate_where_operator_sql(&op, &mut param_index, &mut params).unwrap();
903        assert_eq!(sql, "path::ltree ~ $1::lquery");
904        assert_eq!(param_index, 1);
905    }
906
907    #[test]
908    fn test_ltree_matches_ltxtquery() {
909        let mut param_index = 0;
910        let mut params = HashMap::new();
911        let op = WhereOperator::MatchesLtxtquery {
912            field: Field::DirectColumn("path".to_string()),
913            query: "Science & !Deprecated".to_string(),
914        };
915        let sql = generate_where_operator_sql(&op, &mut param_index, &mut params).unwrap();
916        assert_eq!(sql, "path::ltree @ $1::ltxtquery");
917        assert_eq!(param_index, 1);
918    }
919
920    #[test]
921    fn test_ltree_matches_any_lquery() {
922        let mut param_index = 0;
923        let mut params = HashMap::new();
924        let op = WhereOperator::MatchesAnyLquery {
925            field: Field::DirectColumn("path".to_string()),
926            patterns: vec!["Top.*".to_string(), "Other.*".to_string()],
927        };
928        let sql = generate_where_operator_sql(&op, &mut param_index, &mut params).unwrap();
929        assert_eq!(sql, "path::ltree ? ARRAY[$1::lquery, $2::lquery]");
930        assert_eq!(param_index, 2);
931    }
932
933    #[test]
934    fn test_ltree_depth_eq() {
935        let mut param_index = 0;
936        let mut params = HashMap::new();
937        let op = WhereOperator::DepthEq {
938            field: Field::DirectColumn("path".to_string()),
939            depth: 3,
940        };
941        let sql = generate_where_operator_sql(&op, &mut param_index, &mut params).unwrap();
942        assert_eq!(sql, "nlevel(path::ltree) = 3");
943        assert_eq!(param_index, 0); // Depth is inlined, not parameterized
944    }
945
946    #[test]
947    fn test_ltree_depth_gt() {
948        let mut param_index = 0;
949        let mut params = HashMap::new();
950        let op = WhereOperator::DepthGt {
951            field: Field::DirectColumn("path".to_string()),
952            depth: 2,
953        };
954        let sql = generate_where_operator_sql(&op, &mut param_index, &mut params).unwrap();
955        assert_eq!(sql, "nlevel(path::ltree) > 2");
956        assert_eq!(param_index, 0);
957    }
958
959    #[test]
960    fn test_ltree_depth_lte() {
961        let mut param_index = 0;
962        let mut params = HashMap::new();
963        let op = WhereOperator::DepthLte {
964            field: Field::DirectColumn("path".to_string()),
965            depth: 5,
966        };
967        let sql = generate_where_operator_sql(&op, &mut param_index, &mut params).unwrap();
968        assert_eq!(sql, "nlevel(path::ltree) <= 5");
969        assert_eq!(param_index, 0);
970    }
971
972    #[test]
973    fn test_ltree_lca() {
974        let mut param_index = 0;
975        let mut params = HashMap::new();
976        let op = WhereOperator::Lca {
977            field: Field::DirectColumn("path".to_string()),
978            paths: vec![
979                "Org.Engineering.Backend".to_string(),
980                "Org.Engineering.Frontend".to_string(),
981            ],
982        };
983        let sql = generate_where_operator_sql(&op, &mut param_index, &mut params).unwrap();
984        assert_eq!(sql, "path::ltree = lca(ARRAY[$1::ltree, $2::ltree])");
985        assert_eq!(param_index, 2);
986    }
987}
fraiseql_wire/operators/sql_gen.rs

fraiseql_wire/operators/
sql_gen.rs
