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

fraiseql_wire/operators/
sql_gen.rs
