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                // TODO: Replace path depth heuristic with schema metadata when available.
391                // We infer JSON type from !is_simple(), but with a schema registry we could
392                // look up the actual field type. See phase-3-schema.md for details.
393                let left_is_jsonb = matches!(left.as_ref(), Expr::Path(p) if !p.is_simple());
394                let right_is_jsonb = matches!(right.as_ref(), Expr::Path(p) if !p.is_simple());
395
396                self.expr(left)?;
397                self.sql(" ");
398                self.comparison_op(operator)?;
399                self.sql(" ");
400
401                if left_is_jsonb && matches!(right.as_ref(), Expr::Literal(_)) {
402                    // Comparing JSONB path to literal: cast literal to jsonb
403                    self.expr_as_jsonb(right)?;
404                } else if right_is_jsonb && matches!(left.as_ref(), Expr::Literal(_)) {
405                    // Comparing literal to JSONB path: cast literal to jsonb
406                    self.expr_as_jsonb(right)?;
407                } else {
408                    self.expr(right)?;
409                }
410            }
411            Predicate::And(left, right) => {
412                self.predicate(left)?;
413                self.sql(" AND ");
414                self.predicate(right)?;
415            }
416            Predicate::Or(left, right) => {
417                self.sql("(");
418                self.predicate(left)?;
419                self.sql(" OR ");
420                self.predicate(right)?;
421                self.sql(")");
422            }
423            Predicate::Not(pred) => {
424                self.sql("NOT (");
425                self.predicate(pred)?;
426                self.sql(")");
427            }
428            Predicate::IsNull(expr) => {
429                self.expr(expr)?;
430                self.sql(" IS NULL");
431            }
432            Predicate::True => {
433                self.sql("TRUE");
434            }
435            Predicate::False => {
436                self.sql("FALSE");
437            }
438            Predicate::Placeholder => {
439                return Err(SqlGenerationError::PlaceholderFound);
440            }
441        }
442        Ok(())
443    }
444
445    pub fn selection(&mut self, selection: &Selection) -> Result<(), SqlGenerationError> {
446        // Add the predicate (WHERE clause)
447        self.predicate(&selection.predicate)?;
448
449        // Add ORDER BY clause if present
450        if let Some(order_by_items) = &selection.order_by {
451            self.sql(" ORDER BY ");
452            for (i, order_by) in order_by_items.iter().enumerate() {
453                if i > 0 {
454                    self.sql(", ");
455                }
456                self.order_by_item(order_by)?;
457            }
458        }
459
460        // Add LIMIT clause if present
461        if let Some(limit) = selection.limit {
462            self.sql(" LIMIT ");
463            self.arg(limit as i64); // PostgreSQL expects i64 for LIMIT
464        }
465
466        Ok(())
467    }
468
469    pub fn order_by_item(&mut self, order_by: &OrderByItem) -> Result<(), SqlGenerationError> {
470        // Generate the path expression
471        for (i, step) in order_by.path.steps.iter().enumerate() {
472            if i > 0 {
473                self.sql(".");
474            }
475            // Escape any existing quotes in the step by doubling them
476            let escaped_step = step.replace('"', "\"\"");
477            self.sql(format!(r#""{}""#, escaped_step));
478        }
479
480        // Add the direction
481        match order_by.direction {
482            OrderDirection::Asc => self.sql(" ASC"),
483            OrderDirection::Desc => self.sql(" DESC"),
484        }
485
486        Ok(())
487    }
488}
489
490fn comparison_op_to_sql(op: &ComparisonOperator) -> Result<&'static str, SqlGenerationError> {
491    Ok(match op {
492        ComparisonOperator::Equal => "=",
493        ComparisonOperator::NotEqual => "<>",
494        ComparisonOperator::GreaterThan => ">",
495        ComparisonOperator::GreaterThanOrEqual => ">=",
496        ComparisonOperator::LessThan => "<",
497        ComparisonOperator::LessThanOrEqual => "<=",
498        ComparisonOperator::In => "IN",
499        ComparisonOperator::Between => return Err(SqlGenerationError::UnsupportedOperator("BETWEEN operator is not yet supported")),
500    })
501}
502
503#[cfg(test)]
504mod tests {
505    use super::*;
506    use ankql::parser::parse_selection;
507    use anyhow::Result;
508
509    fn assert_args<'a, 'b>(args: &Vec<Box<dyn ToSql + Send + Sync>>, expected: &Vec<Box<dyn ToSql + Send + Sync>>) {
510        // TODO: Maybe actually encoding these and comparing bytes?
511        assert_eq!(format!("{:?}", args), format!("{:?}", expected));
512    }
513
514    #[test]
515    fn test_simple_equality() -> Result<()> {
516        let selection = parse_selection("name = 'Alice'").unwrap();
517        let mut sql = SqlBuilder::new();
518        sql.selection(&selection)?;
519
520        let (sql_string, args) = sql.build_where_clause();
521        assert_eq!(sql_string, r#""name" = $1"#);
522        let expected: Vec<Box<dyn ToSql + Send + Sync>> = vec![Box::new("Alice")];
523        assert_args(&args, &expected);
524        Ok(())
525    }
526
527    #[test]
528    fn test_and_condition() -> Result<()> {
529        let selection = parse_selection("name = 'Alice' AND age = 30").unwrap();
530        let mut sql = SqlBuilder::with_fields(vec!["id", "name", "age"]);
531        sql.table_name("users");
532        sql.selection(&selection)?;
533        let (sql_string, args) = sql.build()?;
534
535        assert_eq!(sql_string, r#"SELECT "id", "name", "age" FROM "users" WHERE "name" = $1 AND "age" = $2"#);
536        let expected: Vec<Box<dyn ToSql + Send + Sync>> = vec![Box::new("Alice"), Box::new(30)];
537        assert_args(&args, &expected);
538        Ok(())
539    }
540
541    #[test]
542    fn test_complex_condition() -> Result<()> {
543        let selection = parse_selection("(name = 'Alice' OR name = 'Charlie') AND age >= 30 AND age <= 40").unwrap();
544
545        let mut sql = SqlBuilder::with_fields(vec!["id", "name", "age"]);
546        sql.table_name("users");
547        sql.selection(&selection)?;
548        let (sql_string, args) = sql.build()?;
549
550        assert_eq!(
551            sql_string,
552            r#"SELECT "id", "name", "age" FROM "users" WHERE ("name" = $1 OR "name" = $2) AND "age" >= $3 AND "age" <= $4"#
553        );
554        let expected: Vec<Box<dyn ToSql + Send + Sync>> = vec![Box::new("Alice"), Box::new("Charlie"), Box::new(30), Box::new(40)];
555        assert_args(&args, &expected);
556        Ok(())
557    }
558
559    #[test]
560    fn test_including_collection_identifier() -> Result<()> {
561        // Tests multi-step path SQL generation using JSONB operators.
562        // HACK: We infer "JSON property" from multi-step paths (e.g., `person.name`).
563        // TODO(Phase 3 - Schema Registry): With property metadata, we can distinguish
564        // Json traversal from Ref<T> traversal and generate appropriate SQL.
565        let selection = parse_selection("person.name = 'Alice'").unwrap();
566
567        let mut sql = SqlBuilder::with_fields(vec!["id", "name"]);
568        sql.table_name("people");
569        sql.selection(&selection)?;
570        let (sql_string, args) = sql.build()?;
571
572        // Multi-step paths generate JSONB syntax: -> with ::jsonb cast for proper comparison
573        assert_eq!(sql_string, r#"SELECT "id", "name" FROM "people" WHERE "person"->'name' = '"Alice"'::jsonb"#);
574        // No args - the value is inlined as ::jsonb cast
575        let expected: Vec<Box<dyn ToSql + Send + Sync>> = vec![];
576        assert_args(&args, &expected);
577        Ok(())
578    }
579
580    #[test]
581    fn test_false_predicate() -> Result<()> {
582        let mut sql = SqlBuilder::with_fields(vec!["id"]);
583        sql.table_name("test");
584        sql.predicate(&Predicate::False)?;
585        let (sql_string, args) = sql.build()?;
586
587        assert_eq!(sql_string, r#"SELECT "id" FROM "test" WHERE FALSE"#);
588        let expected: Vec<Box<dyn ToSql + Send + Sync>> = vec![];
589        assert_args(&args, &expected);
590        Ok(())
591    }
592
593    #[test]
594    fn test_in_operator() -> Result<()> {
595        let selection = parse_selection("name IN ('Alice', 'Bob', 'Charlie')").unwrap();
596        let mut sql = SqlBuilder::with_fields(vec!["id", "name"]);
597        sql.table_name("users");
598        sql.selection(&selection)?;
599        let (sql_string, args) = sql.build()?;
600
601        assert_eq!(sql_string, r#"SELECT "id", "name" FROM "users" WHERE "name" IN ($1, $2, $3)"#);
602        let expected: Vec<Box<dyn ToSql + Send + Sync>> = vec![Box::new("Alice"), Box::new("Bob"), Box::new("Charlie")];
603        assert_args(&args, &expected);
604        Ok(())
605    }
606
607    #[test]
608    fn test_placeholder_error() {
609        let mut sql = SqlBuilder::with_fields(vec!["id"]);
610        sql.table_name("test");
611        let err = sql.predicate(&Predicate::Placeholder).expect_err("Expected an error");
612        assert!(matches!(err, SqlGenerationError::PlaceholderFound));
613    }
614
615    #[test]
616    fn test_selection_with_order_by() -> Result<()> {
617        use ankql::ast::{OrderByItem, OrderDirection, PathExpr, Selection};
618
619        let base_selection = ankql::parser::parse_selection("name = 'Alice'").unwrap();
620        let selection = Selection {
621            predicate: base_selection.predicate,
622            order_by: Some(vec![OrderByItem { path: PathExpr::simple("created_at"), direction: OrderDirection::Desc }]),
623            limit: None,
624        };
625
626        let mut sql = SqlBuilder::with_fields(vec!["id", "name", "created_at"]);
627        sql.table_name("users");
628        sql.selection(&selection)?;
629        let (sql_string, args) = sql.build()?;
630
631        assert_eq!(sql_string, r#"SELECT "id", "name", "created_at" FROM "users" WHERE "name" = $1 ORDER BY "created_at" DESC"#);
632        let expected: Vec<Box<dyn ToSql + Send + Sync>> = vec![Box::new("Alice")];
633        assert_args(&args, &expected);
634        Ok(())
635    }
636
637    #[test]
638    fn test_selection_with_limit() -> Result<()> {
639        let base_selection = ankql::parser::parse_selection("age > 18").unwrap();
640        let selection = Selection { predicate: base_selection.predicate, order_by: None, limit: Some(10) };
641
642        let mut sql = SqlBuilder::with_fields(vec!["id", "name", "age"]);
643        sql.table_name("users");
644        sql.selection(&selection)?;
645        let (sql_string, args) = sql.build()?;
646
647        assert_eq!(sql_string, r#"SELECT "id", "name", "age" FROM "users" WHERE "age" > $1 LIMIT $2"#);
648        let expected: Vec<Box<dyn ToSql + Send + Sync>> = vec![Box::new(18i64), Box::new(10i64)];
649        assert_args(&args, &expected);
650        Ok(())
651    }
652
653    #[test]
654    fn test_selection_with_order_by_and_limit() -> Result<()> {
655        use ankql::ast::{OrderByItem, OrderDirection, PathExpr, Selection};
656
657        let base_selection = ankql::parser::parse_selection("status = 'active'").unwrap();
658        let selection = Selection {
659            predicate: base_selection.predicate,
660            order_by: Some(vec![
661                OrderByItem { path: PathExpr::simple("priority"), direction: OrderDirection::Desc },
662                OrderByItem { path: PathExpr::simple("created_at"), direction: OrderDirection::Asc },
663            ]),
664            limit: Some(5),
665        };
666
667        let mut sql = SqlBuilder::with_fields(vec!["id", "status", "priority", "created_at"]);
668        sql.table_name("tasks");
669        sql.selection(&selection)?;
670        let (sql_string, args) = sql.build()?;
671
672        assert_eq!(
673            sql_string,
674            r#"SELECT "id", "status", "priority", "created_at" FROM "tasks" WHERE "status" = $1 ORDER BY "priority" DESC, "created_at" ASC LIMIT $2"#
675        );
676        let expected: Vec<Box<dyn ToSql + Send + Sync>> = vec![Box::new("active"), Box::new(5i64)];
677        assert_args(&args, &expected);
678        Ok(())
679    }
680
681    // ============================================================================
682    // JSONB SQL Generation Tests
683    // These verify that multi-step paths generate correct PostgreSQL JSONB syntax.
684    //
685    // Key design decision: Use -> (not ->>) with ::jsonb cast on literals.
686    // This ensures PostgreSQL's type-aware JSONB comparison:
687    // - Numeric comparisons are numeric (not lexicographic)
688    // - Cross-type comparisons return false (e.g., 9::jsonb != '"9"'::jsonb)
689    // ============================================================================
690    mod jsonb_sql_tests {
691        use super::*;
692        use ankql::ast::PathExpr;
693
694        #[test]
695        fn test_two_step_json_path() -> Result<()> {
696            // licensing.territory = 'US' should use -> and ::jsonb cast
697            let selection = parse_selection("licensing.territory = 'US'").unwrap();
698            let mut sql = SqlBuilder::new();
699            sql.selection(&selection)?;
700            let (sql_string, _) = sql.build_where_clause();
701
702            // String literal becomes '"US"'::jsonb (JSON string)
703            assert_eq!(sql_string, r#""licensing"->'territory' = '"US"'::jsonb"#);
704            Ok(())
705        }
706
707        #[test]
708        fn test_three_step_json_path() -> Result<()> {
709            // licensing.rights.holder should become "licensing"->'rights'->'holder'
710            let selection = parse_selection("licensing.rights.holder = 'Label'").unwrap();
711            let mut sql = SqlBuilder::new();
712            sql.selection(&selection)?;
713            let (sql_string, _) = sql.build_where_clause();
714
715            assert_eq!(sql_string, r#""licensing"->'rights'->'holder' = '"Label"'::jsonb"#);
716            Ok(())
717        }
718
719        #[test]
720        fn test_four_step_json_path() -> Result<()> {
721            // a.b.c.d should become "a"->'b'->'c'->'d'
722            let selection = parse_selection("a.b.c.d = 'value'").unwrap();
723            let mut sql = SqlBuilder::new();
724            sql.selection(&selection)?;
725            let (sql_string, _) = sql.build_where_clause();
726
727            assert_eq!(sql_string, r#""a"->'b'->'c'->'d' = '"value"'::jsonb"#);
728            Ok(())
729        }
730
731        #[test]
732        fn test_json_path_with_numeric_comparison() -> Result<()> {
733            // Using -> with ::jsonb ensures proper numeric comparison:
734            // - "data"->'count' returns JSONB number
735            // - '10'::jsonb is JSONB number
736            // - JSONB numeric comparison is numeric (9 < 10), not lexicographic ("9" > "10")
737            let selection = parse_selection("data.count > 10").unwrap();
738            let mut sql = SqlBuilder::new();
739            sql.selection(&selection)?;
740            let (sql_string, _) = sql.build_where_clause();
741
742            assert_eq!(sql_string, r#""data"->'count' > '10'::jsonb"#);
743            Ok(())
744        }
745
746        #[test]
747        fn test_mixed_simple_and_json_paths() -> Result<()> {
748            // name = 'test' AND data.status = 'active'
749            // Simple path uses $1, JSON path uses ::jsonb cast
750            let selection = parse_selection("name = 'test' AND data.status = 'active'").unwrap();
751            let mut sql = SqlBuilder::new();
752            sql.selection(&selection)?;
753            let (sql_string, _) = sql.build_where_clause();
754
755            assert_eq!(sql_string, r#""name" = $1 AND "data"->'status' = '"active"'::jsonb"#);
756            Ok(())
757        }
758
759        #[test]
760        fn test_json_path_escaping() -> Result<()> {
761            // Field with quote in path step - should escape properly
762            // Note: This tests the SQL escaping, not JSON key escaping
763            let mut sql = SqlBuilder::new();
764            let path = PathExpr { steps: vec!["data".to_string(), "it's".to_string()] };
765            sql.expr(&Expr::Path(path))?;
766            let (sql_string, _) = sql.build_where_clause();
767
768            // Just the path, no comparison - still uses ->
769            assert_eq!(sql_string, r#""data"->'it''s'"#);
770            Ok(())
771        }
772
773        #[test]
774        fn test_json_path_with_boolean() -> Result<()> {
775            let selection = parse_selection("data.active = true").unwrap();
776            let mut sql = SqlBuilder::new();
777            sql.selection(&selection)?;
778            let (sql_string, _) = sql.build_where_clause();
779
780            assert_eq!(sql_string, r#""data"->'active' = 'true'::jsonb"#);
781            Ok(())
782        }
783
784        #[test]
785        fn test_json_path_with_float() -> Result<()> {
786            // Note: AnkQL parser may parse this as i64, but the principle stands
787            let selection = parse_selection("data.score >= 95").unwrap();
788            let mut sql = SqlBuilder::new();
789            sql.selection(&selection)?;
790            let (sql_string, _) = sql.build_where_clause();
791
792            assert_eq!(sql_string, r#""data"->'score' >= '95'::jsonb"#);
793            Ok(())
794        }
795    }
796
797    // ============================================================================
798    // Predicate Split Tests
799    // These verify that split_predicate_for_postgres correctly classifies predicates
800    // ============================================================================
801    mod predicate_split_tests {
802        use super::*;
803
804        #[test]
805        fn test_simple_predicate_fully_pushable() {
806            let selection = parse_selection("name = 'Alice'").unwrap();
807            let split = split_predicate_for_postgres(&selection.predicate);
808
809            // Simple predicate should be fully pushable
810            assert!(!split.needs_post_filter());
811            assert!(matches!(split.remaining_predicate, Predicate::True));
812        }
813
814        #[test]
815        fn test_json_path_predicate_pushable() {
816            // Multi-step paths ARE pushed down using JSONB operators.
817            // HACK: We infer "JSON property" from multi-step paths.
818            // TODO(Phase 3 - Schema Registry): Once we have property metadata,
819            // we can distinguish Json traversal (pushable) from Ref<T> (not pushable).
820            let selection = parse_selection("licensing.territory = 'US'").unwrap();
821            let split = split_predicate_for_postgres(&selection.predicate);
822
823            // JSON path IS pushable via JSONB syntax
824            assert!(!split.needs_post_filter());
825        }
826
827        #[test]
828        fn test_and_with_all_pushable() {
829            let selection = parse_selection("name = 'test' AND licensing.status = 'active'").unwrap();
830            let split = split_predicate_for_postgres(&selection.predicate);
831
832            // Both parts pushable (simple path + JSON path) = whole thing pushable
833            assert!(!split.needs_post_filter());
834        }
835
836        #[test]
837        fn test_or_with_all_pushable() {
838            let selection = parse_selection("name = 'a' OR name = 'b'").unwrap();
839            let split = split_predicate_for_postgres(&selection.predicate);
840
841            // Both branches pushable = whole OR pushable
842            assert!(!split.needs_post_filter());
843        }
844
845        #[test]
846        fn test_complex_nested_predicate() {
847            let selection = parse_selection("(name = 'test' OR data.type = 'special') AND status = 'active'").unwrap();
848            let split = split_predicate_for_postgres(&selection.predicate);
849
850            // All parts are pushable (simple paths + JSON paths)
851            assert!(!split.needs_post_filter());
852        }
853
854        #[test]
855        fn test_not_predicate_pushable() {
856            let selection = parse_selection("NOT (status = 'deleted')").unwrap();
857            let split = split_predicate_for_postgres(&selection.predicate);
858
859            assert!(!split.needs_post_filter());
860        }
861
862        #[test]
863        fn test_is_null_pushable() {
864            let selection = parse_selection("name IS NULL").unwrap();
865            let split = split_predicate_for_postgres(&selection.predicate);
866
867            assert!(!split.needs_post_filter());
868        }
869
870        // Test for future: when we have unpushable predicates (e.g., Ref traversal)
871        // #[test]
872        // fn test_unpushable_predicate_goes_to_remaining() {
873        //     // When we add Ref traversal, this test would verify:
874        //     // let selection = parse_selection("artist.name = 'Radiohead'").unwrap();
875        //     // let split = split_predicate_for_postgres(&selection.predicate);
876        //     // assert!(split.needs_post_filter());
877        //     // assert!(matches!(split.sql_predicate, Predicate::True));
878        // }
879    }
880}
ankurah_storage_postgres/sql_builder.rs

ankurah_storage_postgres/
sql_builder.rs
