ankurah_storage_postgres/
sql_builder.rs

1use ankql::ast::{ComparisonOperator, Expr, Literal, OrderByItem, OrderDirection, Predicate, Selection};
2use ankurah_core::{error::RetrievalError, EntityId};
3use thiserror::Error;
4use tokio_postgres::types::ToSql;
5
6#[derive(Debug, Error, Clone)]
7pub enum SqlGenerationError {
8    #[error("Placeholder found in predicate - placeholders should be replaced before predicate processing")]
9    PlaceholderFound,
10    #[error("Unsupported expression type: {0}")]
11    UnsupportedExpression(&'static str),
12    #[error("Unsupported operator: {0}")]
13    UnsupportedOperator(&'static str),
14    #[error("SqlBuilder requires both fields and table_name to be set for complete SELECT generation, or neither for WHERE-only mode")]
15    IncompleteConfiguration,
16}
17
18/// Result of splitting a predicate for PostgreSQL execution.
19///
20/// "Pushdown" refers to moving predicate evaluation from the application layer
21/// down to the database layer. Some predicates can be translated to SQL and
22/// executed by PostgreSQL (pushdown), while others must be evaluated in Rust
23/// after fetching results (e.g., future features like Ref traversal).
24#[derive(Debug, Clone)]
25pub struct SplitPredicate {
26    /// Predicate that can be pushed down to PostgreSQL WHERE clause
27    pub sql_predicate: Predicate,
28    /// Predicate that must be evaluated in Rust after fetching (Predicate::True if nothing remains)
29    pub remaining_predicate: Predicate,
30}
31
32impl SplitPredicate {
33    /// Check if there's any remaining predicate that needs post-filtering
34    pub fn needs_post_filter(&self) -> bool { !matches!(self.remaining_predicate, Predicate::True) }
35}
36
37/// Split a predicate into parts that can be pushed down to PostgreSQL vs evaluated post-fetch.
38///
39/// **Pushdown-capable** (translated to SQL):
40/// - Simple column comparisons (single-step paths like `name = 'value'`)
41/// - JSONB path comparisons (multi-step paths like `data.field = 'value'`)
42/// - AND/OR/NOT combinations of pushdown-capable predicates
43/// - IS NULL, TRUE, FALSE
44///
45/// **Requires post-filtering** (evaluated in Rust):
46/// - Future: Ref traversals, complex expressions
47pub fn split_predicate_for_postgres(predicate: &Predicate) -> SplitPredicate {
48    // Walk the predicate tree and classify each leaf comparison.
49    // If ANY part of an OR branch can't be pushed down, the whole OR must be post-filtered.
50    // For AND, we can split: pushdown what we can, post-filter the rest.
51
52    let (sql_pred, remaining_pred) = split_predicate_recursive(predicate);
53
54    SplitPredicate { sql_predicate: sql_pred, remaining_predicate: remaining_pred }
55}
56
57/// Recursively split a predicate into (pushdown, remaining) parts.
58fn split_predicate_recursive(predicate: &Predicate) -> (Predicate, Predicate) {
59    match predicate {
60        // Leaf predicates - check if they support pushdown
61        Predicate::Comparison { left, operator: _, right } => {
62            if can_pushdown_comparison(left, right) {
63                (predicate.clone(), Predicate::True)
64            } else {
65                // Can't pushdown - keep for post-filter
66                (Predicate::True, predicate.clone())
67            }
68        }
69
70        // AND: can split - pushdown what we can, keep the rest
71        Predicate::And(left, right) => {
72            let (left_sql, left_remaining) = split_predicate_recursive(left);
73            let (right_sql, right_remaining) = split_predicate_recursive(right);
74
75            let sql_pred = match (&left_sql, &right_sql) {
76                (Predicate::True, Predicate::True) => Predicate::True,
77                (Predicate::True, _) => right_sql,
78                (_, Predicate::True) => left_sql,
79                _ => Predicate::And(Box::new(left_sql), Box::new(right_sql)),
80            };
81
82            let remaining_pred = match (&left_remaining, &right_remaining) {
83                (Predicate::True, Predicate::True) => Predicate::True,
84                (Predicate::True, _) => right_remaining,
85                (_, Predicate::True) => left_remaining,
86                _ => Predicate::And(Box::new(left_remaining), Box::new(right_remaining)),
87            };
88
89            (sql_pred, remaining_pred)
90        }
91
92        // OR: if any branch can't be fully pushed down, keep the whole OR for post-filter
93        // (but still pushdown what we can to reduce row count)
94        Predicate::Or(left, right) => {
95            let (left_sql, left_remaining) = split_predicate_recursive(left);
96            let (right_sql, right_remaining) = split_predicate_recursive(right);
97
98            // If both branches fully support pushdown, pushdown the whole OR
99            if matches!(left_remaining, Predicate::True) && matches!(right_remaining, Predicate::True) {
100                (predicate.clone(), Predicate::True)
101            } else {
102                // Partial pushdown - still send what we can to reduce rows,
103                // but must also post-filter with the full OR
104                let sql_pred = match (&left_sql, &right_sql) {
105                    (Predicate::True, Predicate::True) => Predicate::True,
106                    (Predicate::True, _) => right_sql,
107                    (_, Predicate::True) => left_sql,
108                    _ => Predicate::Or(Box::new(left_sql), Box::new(right_sql)),
109                };
110                (sql_pred, predicate.clone())
111            }
112        }
113
114        // NOT: pushdown if inner supports pushdown
115        Predicate::Not(inner) => {
116            let (inner_sql, inner_remaining) = split_predicate_recursive(inner);
117            if matches!(inner_remaining, Predicate::True) {
118                (Predicate::Not(Box::new(inner_sql)), Predicate::True)
119            } else {
120                // Can't pushdown the NOT - keep whole thing for post-filter
121                (Predicate::True, predicate.clone())
122            }
123        }
124
125        // IS NULL - pushdown if expression supports pushdown
126        Predicate::IsNull(expr) => {
127            if can_pushdown_expr(expr) {
128                (predicate.clone(), Predicate::True)
129            } else {
130                (Predicate::True, predicate.clone())
131            }
132        }
133
134        Predicate::True => (Predicate::True, Predicate::True),
135        Predicate::False => (Predicate::False, Predicate::True),
136        Predicate::Placeholder => (Predicate::True, predicate.clone()), // Shouldn't happen, but be safe
137    }
138}
139
140/// Check if a comparison can be pushed down to PostgreSQL.
141fn can_pushdown_comparison(left: &Expr, right: &Expr) -> bool { can_pushdown_expr(left) && can_pushdown_expr(right) }
142
143/// Check if an expression can be pushed down to PostgreSQL SQL.
144///
145/// Returns true if the expression can be translated to valid PostgreSQL syntax.
146/// Currently supports:
147/// - Literals (strings, numbers, booleans, etc.)
148/// - Simple column paths (`name`) - regular column reference
149/// - Multi-step paths (`data.field`) - JSONB traversal via `->` and `->>`
150/// - Expression lists (for IN clauses)
151///
152/// NOT pushdown-capable (will be post-filtered in Rust):
153/// - Nested predicates as expressions
154/// - Infix expressions (not yet implemented)
155/// - Placeholders (should be replaced before we get here)
156///
157/// HACK: We currently infer "JSON property" from multi-step paths. This works for Phase 1
158/// where only Json properties support nested traversal.
159///
160/// TODO(Phase 3 - Schema Registry): Once we have property type metadata, we can:
161/// 1. Know definitively if a path traverses a Json property vs Ref<T>
162/// 2. Ref<T> traversal will NOT be pushable (requires entity joins)
163/// 3. Distinguish Json traversal from Ref<T> traversal based on schema
164fn can_pushdown_expr(expr: &Expr) -> bool {
165    match expr {
166        Expr::Literal(_) => true,
167        Expr::Path(path) => {
168            // All paths are currently pushdown-capable:
169            // - Single-step: regular column reference
170            // - Multi-step: JSONB traversal (inferred as Json property for now)
171            //
172            // HACK: We assume multi-step paths are Json properties.
173            // TODO(Phase 3 - Schema Registry): Check property type to distinguish
174            // Json traversal (pushable) from Ref<T> traversal (not pushable).
175            !path.steps.is_empty()
176        }
177        Expr::ExprList(exprs) => exprs.iter().all(can_pushdown_expr),
178        Expr::Predicate(_) => false,     // Nested predicates - not supported in SQL expressions
179        Expr::InfixExpr { .. } => false, // Not yet supported
180        Expr::Placeholder => false,      // Should be replaced before we get here
181    }
182}
183
184impl From<SqlGenerationError> for RetrievalError {
185    fn from(err: SqlGenerationError) -> Self { RetrievalError::StorageError(Box::new(err)) }
186}
187
188pub enum SqlExpr {
189    Sql(String),
190    Argument(Box<dyn ToSql + Send + Sync>),
191}
192
193pub struct SqlBuilder {
194    expressions: Vec<SqlExpr>,
195    fields: Vec<String>,
196    table_name: Option<String>,
197}
198
199impl Default for SqlBuilder {
200    fn default() -> Self { Self::new() }
201}
202
203impl SqlBuilder {
204    pub fn new() -> Self { Self { expressions: Vec::new(), fields: Vec::new(), table_name: None } }
205
206    pub fn with_fields<T: Into<String>>(fields: Vec<T>) -> Self {
207        Self { expressions: Vec::new(), fields: fields.into_iter().map(|f| f.into()).collect(), table_name: None }
208    }
209
210    pub fn table_name(&mut self, name: impl Into<String>) -> &mut Self {
211        self.table_name = Some(name.into());
212        self
213    }
214
215    pub fn push(&mut self, expr: SqlExpr) { self.expressions.push(expr); }
216
217    pub fn arg(&mut self, arg: impl ToSql + Send + Sync + 'static) {
218        self.push(SqlExpr::Argument(Box::new(arg) as Box<dyn ToSql + Send + Sync>));
219    }
220
221    pub fn sql(&mut self, s: impl AsRef<str>) { self.push(SqlExpr::Sql(s.as_ref().to_owned())); }
222
223    pub fn build(self) -> Result<(String, Vec<Box<dyn ToSql + Send + Sync>>), SqlGenerationError> {
224        let mut counter = 1;
225        let mut where_clause = String::new();
226        let mut args = Vec::new();
227
228        // Build WHERE clause from expressions
229        for expr in self.expressions {
230            match expr {
231                SqlExpr::Argument(arg) => {
232                    where_clause += &format!("${}", counter);
233                    args.push(arg);
234                    counter += 1;
235                }
236                SqlExpr::Sql(s) => {
237                    where_clause += &s;
238                }
239            }
240        }
241
242        // Build complete SELECT statement - fields and table are required
243        if self.fields.is_empty() || self.table_name.is_none() {
244            return Err(SqlGenerationError::IncompleteConfiguration);
245        }
246
247        let fields_clause = self.fields.iter().map(|field| format!(r#""{}""#, field.replace('"', "\"\""))).collect::<Vec<_>>().join(", ");
248        let table = self.table_name.unwrap();
249        let sql = format!(r#"SELECT {} FROM "{}" WHERE {}"#, fields_clause, table.replace('"', "\"\""), where_clause);
250
251        Ok((sql, args))
252    }
253
254    pub fn build_where_clause(self) -> (String, Vec<Box<dyn ToSql + Send + Sync>>) {
255        let mut counter = 1;
256        let mut where_clause = String::new();
257        let mut args = Vec::new();
258
259        // Build WHERE clause from expressions
260        for expr in self.expressions {
261            match expr {
262                SqlExpr::Argument(arg) => {
263                    where_clause += &format!("${}", counter);
264                    args.push(arg);
265                    counter += 1;
266                }
267                SqlExpr::Sql(s) => {
268                    where_clause += &s;
269                }
270            }
271        }
272
273        (where_clause, args)
274    }
275
276    // --- AST flattening ---
277    pub fn expr(&mut self, expr: &Expr) -> Result<(), SqlGenerationError> {
278        match expr {
279            Expr::Placeholder => return Err(SqlGenerationError::PlaceholderFound),
280            Expr::Literal(lit) => match lit {
281                Literal::String(s) => self.arg(s.to_owned()),
282                Literal::I64(int) => self.arg(*int),
283                Literal::F64(float) => self.arg(*float),
284                Literal::Bool(bool) => self.arg(*bool),
285                Literal::I16(i) => self.arg(*i),
286                Literal::I32(i) => self.arg(*i),
287                Literal::EntityId(ulid) => self.arg(EntityId::from_ulid(*ulid).to_base64()),
288                Literal::Object(bytes) => self.arg(bytes.clone()),
289                Literal::Binary(bytes) => self.arg(bytes.clone()),
290                Literal::Json(json) => self.arg(json.clone()),
291            },
292            Expr::Path(path) => {
293                if path.is_simple() {
294                    // Single-step path: regular column reference "column_name"
295                    let escaped = path.first().replace('"', "\"\"");
296                    self.sql(format!(r#""{}""#, escaped));
297                } else {
298                    // Multi-step path: JSONB traversal "column"->'nested'->'path'
299                    // Use -> for ALL steps to preserve JSONB type for proper comparison semantics.
300                    // The comparison will use ::jsonb cast on literals to ensure type-aware comparison.
301                    let first = path.first().replace('"', "\"\"");
302                    self.sql(format!(r#""{}""#, first));
303
304                    for step in path.steps.iter().skip(1) {
305                        let escaped = step.replace('\'', "''");
306                        // Always use -> to keep as JSONB (not ->> which extracts as text)
307                        self.sql(format!("->'{}'", escaped));
308                    }
309                }
310            }
311            Expr::ExprList(exprs) => {
312                self.sql("(");
313                for (i, expr) in exprs.iter().enumerate() {
314                    if i > 0 {
315                        self.sql(", ");
316                    }
317                    match expr {
318                        Expr::Placeholder => return Err(SqlGenerationError::PlaceholderFound),
319                        Expr::Literal(lit) => match lit {
320                            Literal::String(s) => self.arg(s.to_owned()),
321                            Literal::I64(int) => self.arg(*int),
322                            Literal::F64(float) => self.arg(*float),
323                            Literal::Bool(bool) => self.arg(*bool),
324                            Literal::I16(i) => self.arg(*i),
325                            Literal::I32(i) => self.arg(*i),
326                            Literal::EntityId(ulid) => self.arg(EntityId::from_ulid(*ulid).to_base64()),
327                            Literal::Object(bytes) => self.arg(bytes.clone()),
328                            Literal::Binary(bytes) => self.arg(bytes.clone()),
329                            Literal::Json(json) => self.arg(json.clone()),
330                        },
331                        _ => {
332                            return Err(SqlGenerationError::UnsupportedExpression(
333                                "Only literal expressions and placeholders are supported in IN lists",
334                            ))
335                        }
336                    }
337                }
338                self.sql(")");
339            }
340            _ => return Err(SqlGenerationError::UnsupportedExpression("Only literal, identifier, and list expressions are supported")),
341        }
342        Ok(())
343    }
344
345    /// Emit a literal expression with ::jsonb cast for proper JSONB comparison semantics.
346    /// This ensures that comparisons like `"data"->'count' > '10'::jsonb` work correctly
347    /// with PostgreSQL's type-aware JSONB comparison (numeric vs lexicographic).
348    pub fn expr_as_jsonb(&mut self, expr: &Expr) -> Result<(), SqlGenerationError> {
349        match expr {
350            Expr::Literal(lit) => {
351                // For literals, we need to cast to jsonb
352                // PostgreSQL will compare jsonb values with proper type semantics
353                match lit {
354                    Literal::String(s) => {
355                        // String literals need to be JSON strings: '"value"'::jsonb
356                        // Escape for JSON (backslash and quote) then for SQL (single quotes)
357                        let json_escaped = s.replace('\\', "\\\\").replace('"', "\\\"");
358                        let sql_escaped = format!("\"{}\"", json_escaped).replace('\'', "''");
359                        self.sql(format!("'{}'::jsonb", sql_escaped));
360                    }
361                    Literal::I64(n) => self.sql(format!("'{}'::jsonb", n)),
362                    Literal::F64(n) => self.sql(format!("'{}'::jsonb", n)),
363                    Literal::Bool(b) => self.sql(format!("'{}'::jsonb", b)),
364                    Literal::I16(n) => self.sql(format!("'{}'::jsonb", n)),
365                    Literal::I32(n) => self.sql(format!("'{}'::jsonb", n)),
366                    // EntityId and binary types don't make sense as JSONB
367                    Literal::EntityId(_) | Literal::Object(_) | Literal::Binary(_) => {
368                        // Fall back to regular expression (will likely fail comparison, but that's correct)
369                        self.expr(expr)?;
370                    }
371                    // JSON literal is already properly typed
372                    Literal::Json(json) => self.sql(format!("'{}'::jsonb", json)),
373                }
374                Ok(())
375            }
376            // For non-literals, just emit normally (they're already JSONB paths or complex expressions)
377            _ => self.expr(expr),
378        }
379    }
380
381    pub fn comparison_op(&mut self, op: &ComparisonOperator) -> Result<(), SqlGenerationError> {
382        self.sql(comparison_op_to_sql(op)?);
383        Ok(())
384    }
385
386    pub fn predicate(&mut self, predicate: &Predicate) -> Result<(), SqlGenerationError> {
387        match predicate {
388            Predicate::Comparison { left, operator, right } => {
389                // Check if either side is a JSONB path (multi-step path)
390                let left_is_jsonb = matches!(left.as_ref(), Expr::Path(p) if !p.is_simple());
391                let right_is_jsonb = matches!(right.as_ref(), Expr::Path(p) if !p.is_simple());
392
393                self.expr(left)?;
394                self.sql(" ");
395                self.comparison_op(operator)?;
396                self.sql(" ");
397
398                if left_is_jsonb && matches!(right.as_ref(), Expr::Literal(_)) {
399                    // Comparing JSONB path to literal: cast literal to jsonb
400                    self.expr_as_jsonb(right)?;
401                } else if right_is_jsonb && matches!(left.as_ref(), Expr::Literal(_)) {
402                    // Comparing literal to JSONB path: cast literal to jsonb
403                    self.expr_as_jsonb(right)?;
404                } else {
405                    self.expr(right)?;
406                }
407            }
408            Predicate::And(left, right) => {
409                self.predicate(left)?;
410                self.sql(" AND ");
411                self.predicate(right)?;
412            }
413            Predicate::Or(left, right) => {
414                self.sql("(");
415                self.predicate(left)?;
416                self.sql(" OR ");
417                self.predicate(right)?;
418                self.sql(")");
419            }
420            Predicate::Not(pred) => {
421                self.sql("NOT (");
422                self.predicate(pred)?;
423                self.sql(")");
424            }
425            Predicate::IsNull(expr) => {
426                self.expr(expr)?;
427                self.sql(" IS NULL");
428            }
429            Predicate::True => {
430                self.sql("TRUE");
431            }
432            Predicate::False => {
433                self.sql("FALSE");
434            }
435            Predicate::Placeholder => {
436                return Err(SqlGenerationError::PlaceholderFound);
437            }
438        }
439        Ok(())
440    }
441
442    pub fn selection(&mut self, selection: &Selection) -> Result<(), SqlGenerationError> {
443        // Add the predicate (WHERE clause)
444        self.predicate(&selection.predicate)?;
445
446        // Add ORDER BY clause if present
447        if let Some(order_by_items) = &selection.order_by {
448            self.sql(" ORDER BY ");
449            for (i, order_by) in order_by_items.iter().enumerate() {
450                if i > 0 {
451                    self.sql(", ");
452                }
453                self.order_by_item(order_by)?;
454            }
455        }
456
457        // Add LIMIT clause if present
458        if let Some(limit) = selection.limit {
459            self.sql(" LIMIT ");
460            self.arg(limit as i64); // PostgreSQL expects i64 for LIMIT
461        }
462
463        Ok(())
464    }
465
466    pub fn order_by_item(&mut self, order_by: &OrderByItem) -> Result<(), SqlGenerationError> {
467        // Generate the path expression
468        for (i, step) in order_by.path.steps.iter().enumerate() {
469            if i > 0 {
470                self.sql(".");
471            }
472            // Escape any existing quotes in the step by doubling them
473            let escaped_step = step.replace('"', "\"\"");
474            self.sql(format!(r#""{}""#, escaped_step));
475        }
476
477        // Add the direction
478        match order_by.direction {
479            OrderDirection::Asc => self.sql(" ASC"),
480            OrderDirection::Desc => self.sql(" DESC"),
481        }
482
483        Ok(())
484    }
485}
486
487fn comparison_op_to_sql(op: &ComparisonOperator) -> Result<&'static str, SqlGenerationError> {
488    Ok(match op {
489        ComparisonOperator::Equal => "=",
490        ComparisonOperator::NotEqual => "<>",
491        ComparisonOperator::GreaterThan => ">",
492        ComparisonOperator::GreaterThanOrEqual => ">=",
493        ComparisonOperator::LessThan => "<",
494        ComparisonOperator::LessThanOrEqual => "<=",
495        ComparisonOperator::In => "IN",
496        ComparisonOperator::Between => return Err(SqlGenerationError::UnsupportedOperator("BETWEEN operator is not yet supported")),
497    })
498}
499
500#[cfg(test)]
501mod tests {
502    use super::*;
503    use ankql::parser::parse_selection;
504    use anyhow::Result;
505
506    fn assert_args<'a, 'b>(args: &Vec<Box<dyn ToSql + Send + Sync>>, expected: &Vec<Box<dyn ToSql + Send + Sync>>) {
507        // TODO: Maybe actually encoding these and comparing bytes?
508        assert_eq!(format!("{:?}", args), format!("{:?}", expected));
509    }
510
511    #[test]
512    fn test_simple_equality() -> Result<()> {
513        let selection = parse_selection("name = 'Alice'").unwrap();
514        let mut sql = SqlBuilder::new();
515        sql.selection(&selection)?;
516
517        let (sql_string, args) = sql.build_where_clause();
518        assert_eq!(sql_string, r#""name" = $1"#);
519        let expected: Vec<Box<dyn ToSql + Send + Sync>> = vec![Box::new("Alice")];
520        assert_args(&args, &expected);
521        Ok(())
522    }
523
524    #[test]
525    fn test_and_condition() -> Result<()> {
526        let selection = parse_selection("name = 'Alice' AND age = 30").unwrap();
527        let mut sql = SqlBuilder::with_fields(vec!["id", "name", "age"]);
528        sql.table_name("users");
529        sql.selection(&selection)?;
530        let (sql_string, args) = sql.build()?;
531
532        assert_eq!(sql_string, r#"SELECT "id", "name", "age" FROM "users" WHERE "name" = $1 AND "age" = $2"#);
533        let expected: Vec<Box<dyn ToSql + Send + Sync>> = vec![Box::new("Alice"), Box::new(30)];
534        assert_args(&args, &expected);
535        Ok(())
536    }
537
538    #[test]
539    fn test_complex_condition() -> Result<()> {
540        let selection = parse_selection("(name = 'Alice' OR name = 'Charlie') AND age >= 30 AND age <= 40").unwrap();
541
542        let mut sql = SqlBuilder::with_fields(vec!["id", "name", "age"]);
543        sql.table_name("users");
544        sql.selection(&selection)?;
545        let (sql_string, args) = sql.build()?;
546
547        assert_eq!(
548            sql_string,
549            r#"SELECT "id", "name", "age" FROM "users" WHERE ("name" = $1 OR "name" = $2) AND "age" >= $3 AND "age" <= $4"#
550        );
551        let expected: Vec<Box<dyn ToSql + Send + Sync>> = vec![Box::new("Alice"), Box::new("Charlie"), Box::new(30), Box::new(40)];
552        assert_args(&args, &expected);
553        Ok(())
554    }
555
556    #[test]
557    fn test_including_collection_identifier() -> Result<()> {
558        // Tests multi-step path SQL generation using JSONB operators.
559        // HACK: We infer "JSON property" from multi-step paths (e.g., `person.name`).
560        // TODO(Phase 3 - Schema Registry): With property metadata, we can distinguish
561        // Json traversal from Ref<T> traversal and generate appropriate SQL.
562        let selection = parse_selection("person.name = 'Alice'").unwrap();
563
564        let mut sql = SqlBuilder::with_fields(vec!["id", "name"]);
565        sql.table_name("people");
566        sql.selection(&selection)?;
567        let (sql_string, args) = sql.build()?;
568
569        // Multi-step paths generate JSONB syntax: -> with ::jsonb cast for proper comparison
570        assert_eq!(sql_string, r#"SELECT "id", "name" FROM "people" WHERE "person"->'name' = '"Alice"'::jsonb"#);
571        // No args - the value is inlined as ::jsonb cast
572        let expected: Vec<Box<dyn ToSql + Send + Sync>> = vec![];
573        assert_args(&args, &expected);
574        Ok(())
575    }
576
577    #[test]
578    fn test_false_predicate() -> Result<()> {
579        let mut sql = SqlBuilder::with_fields(vec!["id"]);
580        sql.table_name("test");
581        sql.predicate(&Predicate::False)?;
582        let (sql_string, args) = sql.build()?;
583
584        assert_eq!(sql_string, r#"SELECT "id" FROM "test" WHERE FALSE"#);
585        let expected: Vec<Box<dyn ToSql + Send + Sync>> = vec![];
586        assert_args(&args, &expected);
587        Ok(())
588    }
589
590    #[test]
591    fn test_in_operator() -> Result<()> {
592        let selection = parse_selection("name IN ('Alice', 'Bob', 'Charlie')").unwrap();
593        let mut sql = SqlBuilder::with_fields(vec!["id", "name"]);
594        sql.table_name("users");
595        sql.selection(&selection)?;
596        let (sql_string, args) = sql.build()?;
597
598        assert_eq!(sql_string, r#"SELECT "id", "name" FROM "users" WHERE "name" IN ($1, $2, $3)"#);
599        let expected: Vec<Box<dyn ToSql + Send + Sync>> = vec![Box::new("Alice"), Box::new("Bob"), Box::new("Charlie")];
600        assert_args(&args, &expected);
601        Ok(())
602    }
603
604    #[test]
605    fn test_placeholder_error() {
606        let mut sql = SqlBuilder::with_fields(vec!["id"]);
607        sql.table_name("test");
608        let err = sql.predicate(&Predicate::Placeholder).expect_err("Expected an error");
609        assert!(matches!(err, SqlGenerationError::PlaceholderFound));
610    }
611
612    #[test]
613    fn test_selection_with_order_by() -> Result<()> {
614        use ankql::ast::{OrderByItem, OrderDirection, PathExpr, Selection};
615
616        let base_selection = ankql::parser::parse_selection("name = 'Alice'").unwrap();
617        let selection = Selection {
618            predicate: base_selection.predicate,
619            order_by: Some(vec![OrderByItem { path: PathExpr::simple("created_at"), direction: OrderDirection::Desc }]),
620            limit: None,
621        };
622
623        let mut sql = SqlBuilder::with_fields(vec!["id", "name", "created_at"]);
624        sql.table_name("users");
625        sql.selection(&selection)?;
626        let (sql_string, args) = sql.build()?;
627
628        assert_eq!(sql_string, r#"SELECT "id", "name", "created_at" FROM "users" WHERE "name" = $1 ORDER BY "created_at" DESC"#);
629        let expected: Vec<Box<dyn ToSql + Send + Sync>> = vec![Box::new("Alice")];
630        assert_args(&args, &expected);
631        Ok(())
632    }
633
634    #[test]
635    fn test_selection_with_limit() -> Result<()> {
636        let base_selection = ankql::parser::parse_selection("age > 18").unwrap();
637        let selection = Selection { predicate: base_selection.predicate, order_by: None, limit: Some(10) };
638
639        let mut sql = SqlBuilder::with_fields(vec!["id", "name", "age"]);
640        sql.table_name("users");
641        sql.selection(&selection)?;
642        let (sql_string, args) = sql.build()?;
643
644        assert_eq!(sql_string, r#"SELECT "id", "name", "age" FROM "users" WHERE "age" > $1 LIMIT $2"#);
645        let expected: Vec<Box<dyn ToSql + Send + Sync>> = vec![Box::new(18i64), Box::new(10i64)];
646        assert_args(&args, &expected);
647        Ok(())
648    }
649
650    #[test]
651    fn test_selection_with_order_by_and_limit() -> Result<()> {
652        use ankql::ast::{OrderByItem, OrderDirection, PathExpr, Selection};
653
654        let base_selection = ankql::parser::parse_selection("status = 'active'").unwrap();
655        let selection = Selection {
656            predicate: base_selection.predicate,
657            order_by: Some(vec![
658                OrderByItem { path: PathExpr::simple("priority"), direction: OrderDirection::Desc },
659                OrderByItem { path: PathExpr::simple("created_at"), direction: OrderDirection::Asc },
660            ]),
661            limit: Some(5),
662        };
663
664        let mut sql = SqlBuilder::with_fields(vec!["id", "status", "priority", "created_at"]);
665        sql.table_name("tasks");
666        sql.selection(&selection)?;
667        let (sql_string, args) = sql.build()?;
668
669        assert_eq!(
670            sql_string,
671            r#"SELECT "id", "status", "priority", "created_at" FROM "tasks" WHERE "status" = $1 ORDER BY "priority" DESC, "created_at" ASC LIMIT $2"#
672        );
673        let expected: Vec<Box<dyn ToSql + Send + Sync>> = vec![Box::new("active"), Box::new(5i64)];
674        assert_args(&args, &expected);
675        Ok(())
676    }
677
678    // ============================================================================
679    // JSONB SQL Generation Tests
680    // These verify that multi-step paths generate correct PostgreSQL JSONB syntax.
681    //
682    // Key design decision: Use -> (not ->>) with ::jsonb cast on literals.
683    // This ensures PostgreSQL's type-aware JSONB comparison:
684    // - Numeric comparisons are numeric (not lexicographic)
685    // - Cross-type comparisons return false (e.g., 9::jsonb != '"9"'::jsonb)
686    // ============================================================================
687    mod jsonb_sql_tests {
688        use super::*;
689        use ankql::ast::PathExpr;
690
691        #[test]
692        fn test_two_step_json_path() -> Result<()> {
693            // licensing.territory = 'US' should use -> and ::jsonb cast
694            let selection = parse_selection("licensing.territory = 'US'").unwrap();
695            let mut sql = SqlBuilder::new();
696            sql.selection(&selection)?;
697            let (sql_string, _) = sql.build_where_clause();
698
699            // String literal becomes '"US"'::jsonb (JSON string)
700            assert_eq!(sql_string, r#""licensing"->'territory' = '"US"'::jsonb"#);
701            Ok(())
702        }
703
704        #[test]
705        fn test_three_step_json_path() -> Result<()> {
706            // licensing.rights.holder should become "licensing"->'rights'->'holder'
707            let selection = parse_selection("licensing.rights.holder = 'Label'").unwrap();
708            let mut sql = SqlBuilder::new();
709            sql.selection(&selection)?;
710            let (sql_string, _) = sql.build_where_clause();
711
712            assert_eq!(sql_string, r#""licensing"->'rights'->'holder' = '"Label"'::jsonb"#);
713            Ok(())
714        }
715
716        #[test]
717        fn test_four_step_json_path() -> Result<()> {
718            // a.b.c.d should become "a"->'b'->'c'->'d'
719            let selection = parse_selection("a.b.c.d = 'value'").unwrap();
720            let mut sql = SqlBuilder::new();
721            sql.selection(&selection)?;
722            let (sql_string, _) = sql.build_where_clause();
723
724            assert_eq!(sql_string, r#""a"->'b'->'c'->'d' = '"value"'::jsonb"#);
725            Ok(())
726        }
727
728        #[test]
729        fn test_json_path_with_numeric_comparison() -> Result<()> {
730            // Using -> with ::jsonb ensures proper numeric comparison:
731            // - "data"->'count' returns JSONB number
732            // - '10'::jsonb is JSONB number
733            // - JSONB numeric comparison is numeric (9 < 10), not lexicographic ("9" > "10")
734            let selection = parse_selection("data.count > 10").unwrap();
735            let mut sql = SqlBuilder::new();
736            sql.selection(&selection)?;
737            let (sql_string, _) = sql.build_where_clause();
738
739            assert_eq!(sql_string, r#""data"->'count' > '10'::jsonb"#);
740            Ok(())
741        }
742
743        #[test]
744        fn test_mixed_simple_and_json_paths() -> Result<()> {
745            // name = 'test' AND data.status = 'active'
746            // Simple path uses $1, JSON path uses ::jsonb cast
747            let selection = parse_selection("name = 'test' AND data.status = 'active'").unwrap();
748            let mut sql = SqlBuilder::new();
749            sql.selection(&selection)?;
750            let (sql_string, _) = sql.build_where_clause();
751
752            assert_eq!(sql_string, r#""name" = $1 AND "data"->'status' = '"active"'::jsonb"#);
753            Ok(())
754        }
755
756        #[test]
757        fn test_json_path_escaping() -> Result<()> {
758            // Field with quote in path step - should escape properly
759            // Note: This tests the SQL escaping, not JSON key escaping
760            let mut sql = SqlBuilder::new();
761            let path = PathExpr { steps: vec!["data".to_string(), "it's".to_string()] };
762            sql.expr(&Expr::Path(path))?;
763            let (sql_string, _) = sql.build_where_clause();
764
765            // Just the path, no comparison - still uses ->
766            assert_eq!(sql_string, r#""data"->'it''s'"#);
767            Ok(())
768        }
769
770        #[test]
771        fn test_json_path_with_boolean() -> Result<()> {
772            let selection = parse_selection("data.active = true").unwrap();
773            let mut sql = SqlBuilder::new();
774            sql.selection(&selection)?;
775            let (sql_string, _) = sql.build_where_clause();
776
777            assert_eq!(sql_string, r#""data"->'active' = 'true'::jsonb"#);
778            Ok(())
779        }
780
781        #[test]
782        fn test_json_path_with_float() -> Result<()> {
783            // Note: AnkQL parser may parse this as i64, but the principle stands
784            let selection = parse_selection("data.score >= 95").unwrap();
785            let mut sql = SqlBuilder::new();
786            sql.selection(&selection)?;
787            let (sql_string, _) = sql.build_where_clause();
788
789            assert_eq!(sql_string, r#""data"->'score' >= '95'::jsonb"#);
790            Ok(())
791        }
792    }
793
794    // ============================================================================
795    // Predicate Split Tests
796    // These verify that split_predicate_for_postgres correctly classifies predicates
797    // ============================================================================
798    mod predicate_split_tests {
799        use super::*;
800
801        #[test]
802        fn test_simple_predicate_fully_pushable() {
803            let selection = parse_selection("name = 'Alice'").unwrap();
804            let split = split_predicate_for_postgres(&selection.predicate);
805
806            // Simple predicate should be fully pushable
807            assert!(!split.needs_post_filter());
808            assert!(matches!(split.remaining_predicate, Predicate::True));
809        }
810
811        #[test]
812        fn test_json_path_predicate_pushable() {
813            // Multi-step paths ARE pushed down using JSONB operators.
814            // HACK: We infer "JSON property" from multi-step paths.
815            // TODO(Phase 3 - Schema Registry): Once we have property metadata,
816            // we can distinguish Json traversal (pushable) from Ref<T> (not pushable).
817            let selection = parse_selection("licensing.territory = 'US'").unwrap();
818            let split = split_predicate_for_postgres(&selection.predicate);
819
820            // JSON path IS pushable via JSONB syntax
821            assert!(!split.needs_post_filter());
822        }
823
824        #[test]
825        fn test_and_with_all_pushable() {
826            let selection = parse_selection("name = 'test' AND licensing.status = 'active'").unwrap();
827            let split = split_predicate_for_postgres(&selection.predicate);
828
829            // Both parts pushable (simple path + JSON path) = whole thing pushable
830            assert!(!split.needs_post_filter());
831        }
832
833        #[test]
834        fn test_or_with_all_pushable() {
835            let selection = parse_selection("name = 'a' OR name = 'b'").unwrap();
836            let split = split_predicate_for_postgres(&selection.predicate);
837
838            // Both branches pushable = whole OR pushable
839            assert!(!split.needs_post_filter());
840        }
841
842        #[test]
843        fn test_complex_nested_predicate() {
844            let selection = parse_selection("(name = 'test' OR data.type = 'special') AND status = 'active'").unwrap();
845            let split = split_predicate_for_postgres(&selection.predicate);
846
847            // All parts are pushable (simple paths + JSON paths)
848            assert!(!split.needs_post_filter());
849        }
850
851        #[test]
852        fn test_not_predicate_pushable() {
853            let selection = parse_selection("NOT (status = 'deleted')").unwrap();
854            let split = split_predicate_for_postgres(&selection.predicate);
855
856            assert!(!split.needs_post_filter());
857        }
858
859        #[test]
860        fn test_is_null_pushable() {
861            let selection = parse_selection("name IS NULL").unwrap();
862            let split = split_predicate_for_postgres(&selection.predicate);
863
864            assert!(!split.needs_post_filter());
865        }
866
867        // Test for future: when we have unpushable predicates (e.g., Ref traversal)
868        // #[test]
869        // fn test_unpushable_predicate_goes_to_remaining() {
870        //     // When we add Ref traversal, this test would verify:
871        //     // let selection = parse_selection("artist.name = 'Radiohead'").unwrap();
872        //     // let split = split_predicate_for_postgres(&selection.predicate);
873        //     // assert!(split.needs_post_filter());
874        //     // assert!(matches!(split.sql_predicate, Predicate::True));
875        // }
876    }
877}
ankurah_storage_postgres/sql_builder.rs

ankurah_storage_postgres/
sql_builder.rs
