nodedb-sql 0.6.1

// SPDX-License-Identifier: Apache-2.0

//! AST-level parameter binding for prepared statements.
//!
//! Every `Value::Placeholder("$N")` in a parsed statement is rewritten to
//! a concrete literal value via sqlparser's `VisitorMut`. The visitor
//! traverses every expression position the AST defines — CTE bodies,
//! window specs, `Update.from/returning/limit`, `Delete.returning`,
//! `Insert.on_conflict`, `Expr::Array` elements, `Expr::AnyOp`/`AllOp`
//! right-hand sides, `Expr::Interval`, and any variant sqlparser adds in
//! the future — without us maintaining a hand-written walker.
//!
//! # Why a visitor, not a hand-written walker
//!
//! The previous implementation was a recursive match on ~20 `Expr` /
//! `Statement` / `Query` variants. Every new sqlparser variant it didn't
//! enumerate was a silent bug: placeholders survived into the planner and
//! surfaced as "unsupported expression: $1" in the resolver. The walker
//! was opt-in where it had to be exhaustive to be correct. `VisitorMut`
//! moves the exhaustiveness burden to sqlparser itself.

use core::ops::ControlFlow;

use sqlparser::ast::{Statement, Value, ValueWithSpan, VisitMut, VisitorMut};

/// Parameter value for AST substitution.
///
/// Converted from pgwire binary parameters + type info in the Control Plane.
#[derive(Debug, Clone)]
pub enum ParamValue {
    Null,
    Bool(bool),
    Int64(i64),
    Float64(f64),
    /// Exact arbitrary-precision decimal from a NUMERIC/DECIMAL pgwire parameter.
    Decimal(rust_decimal::Decimal),
    Text(String),
    /// Naive (no-timezone) timestamp from a TIMESTAMP pgwire parameter.
    Timestamp(nodedb_types::datetime::NdbDateTime),
    /// UTC (timezone-aware) timestamp from a TIMESTAMPTZ pgwire parameter.
    Timestamptz(nodedb_types::datetime::NdbDateTime),
}

/// Substitute all `$N` placeholders in a parsed statement with concrete values.
pub fn bind_params(stmt: &mut Statement, params: &[ParamValue]) {
    if params.is_empty() {
        return;
    }
    let mut binder = ParamBinder { params };
    let _ = stmt.visit(&mut binder);
}

/// Visitor that rewrites every `Value::Placeholder("$N")` it encounters.
///
/// sqlparser's `VisitMut` impls take us into every expression position of
/// every `Expr`, `Statement`, `Query`, `SetExpr`, `TableFactor`, etc. —
/// so we only care about the leaf: the `Value` itself.
struct ParamBinder<'a> {
    params: &'a [ParamValue],
}

impl VisitorMut for ParamBinder<'_> {
    type Break = ();

    fn pre_visit_value(&mut self, value: &mut ValueWithSpan) -> ControlFlow<Self::Break> {
        // Only the literal is rewritten; `value.span` keeps pointing at the
        // placeholder token, so a later diagnostic still names the source
        // position the client wrote rather than a synthetic one.
        if let Value::Placeholder(p) = &value.value
            && let Some(v) = placeholder_to_value(p, self.params)
        {
            value.value = v;
        }
        ControlFlow::Continue(())
    }
}

fn placeholder_to_value(placeholder: &str, params: &[ParamValue]) -> Option<Value> {
    let idx_str = placeholder.strip_prefix('$')?;
    let idx: usize = idx_str.parse().ok()?;
    let param = params.get(idx.checked_sub(1)?)?;
    Some(match param {
        ParamValue::Null => Value::Null,
        ParamValue::Bool(true) => Value::Boolean(true),
        ParamValue::Bool(false) => Value::Boolean(false),
        ParamValue::Int64(n) => Value::Number(n.to_string(), false),
        ParamValue::Float64(f) => Value::Number(float_literal_text(*f), false),
        ParamValue::Decimal(d) => Value::Number(d.to_string(), false),
        ParamValue::Text(s) => Value::SingleQuotedString(s.clone()),
        // Timestamp/Timestamptz: emit as a typed SQL literal so the resolver
        // can produce the correct SqlValue variant (Timestamp vs Timestamptz).
        ParamValue::Timestamp(dt) => Value::SingleQuotedString(dt.to_iso8601()),
        ParamValue::Timestamptz(dt) => Value::SingleQuotedString(dt.to_iso8601()),
    })
}

/// The number-literal text of a float parameter.
///
/// The text always carries an exponent: `1.5e0`, `2e0`. The literal
/// resolver reads an exponent as a float, so the value stays a float. The
/// plain form reads `1.5` as a decimal and `2` as an integer. The mantissa
/// is the shortest text that round-trips the `f64`. A non-finite float is
/// `NaN`, `inf` or `-inf`, which the resolver also reads as a float.
pub(crate) fn float_literal_text(f: f64) -> String {
    format!("{f:e}")
}

#[cfg(test)]
mod tests {
    use super::*;
    use crate::parser::statement::parse_sql;

    fn bind_and_format(sql: &str, params: &[ParamValue]) -> String {
        let mut stmts = parse_sql(sql).unwrap();
        for stmt in &mut stmts {
            bind_params(stmt, params);
        }
        stmts
            .iter()
            .map(|s| s.to_string())
            .collect::<Vec<_>>()
            .join("; ")
    }

    #[test]
    fn bind_select_where() {
        let result = bind_and_format(
            "SELECT * FROM users WHERE id = $1",
            &[ParamValue::Int64(42)],
        );
        assert!(result.contains("id = 42"), "got: {result}");
    }

    #[test]
    fn bind_string_param() {
        let result = bind_and_format(
            "SELECT * FROM users WHERE name = $1",
            &[ParamValue::Text("alice".into())],
        );
        assert!(result.contains("name = 'alice'"), "got: {result}");
    }

    #[test]
    fn bind_null_param() {
        let result = bind_and_format("SELECT * FROM users WHERE name = $1", &[ParamValue::Null]);
        assert!(result.contains("name = NULL"), "got: {result}");
    }

    #[test]
    fn bind_multiple_params() {
        let result = bind_and_format(
            "SELECT * FROM users WHERE age > $1 AND name = $2",
            &[ParamValue::Int64(18), ParamValue::Text("bob".into())],
        );
        assert!(result.contains("age > 18"), "got: {result}");
        assert!(result.contains("name = 'bob'"), "got: {result}");
    }

    #[test]
    fn bind_insert_values() {
        let result = bind_and_format(
            "INSERT INTO users (id, name) VALUES ($1, $2)",
            &[ParamValue::Int64(1), ParamValue::Text("eve".into())],
        );
        assert!(result.contains("1, 'eve'"), "got: {result}");
    }

    #[test]
    fn bind_bool_param() {
        let result = bind_and_format(
            "SELECT * FROM users WHERE active = $1",
            &[ParamValue::Bool(true)],
        );
        assert!(result.contains("active = true"), "got: {result}");
    }

    #[test]
    fn no_params_noop() {
        let result = bind_and_format("SELECT 1", &[]);
        assert!(result.contains("SELECT 1"));
    }

    #[test]
    fn bind_cte_body_placeholders() {
        let result = bind_and_format(
            "WITH x AS (SELECT $1 AS v) SELECT v FROM x",
            &[ParamValue::Int64(42)],
        );
        assert!(
            result.contains("SELECT 42"),
            "CTE body placeholder not substituted: {result}"
        );
        assert!(!result.contains("$1"), "placeholder survived: {result}");
    }

    #[test]
    fn bind_recursive_cte_placeholders() {
        let result = bind_and_format(
            "WITH RECURSIVE chain AS (SELECT $1 AS id UNION ALL SELECT chain.id + 1 FROM chain WHERE chain.id < $2) SELECT * FROM chain",
            &[ParamValue::Int64(1), ParamValue::Int64(10)],
        );
        assert!(
            !result.contains("$1") && !result.contains("$2"),
            "got: {result}"
        );
    }

    #[test]
    fn bind_array_elements() {
        let result = bind_and_format(
            "SELECT ARRAY[$1, $2, $3]",
            &[
                ParamValue::Int64(1),
                ParamValue::Int64(2),
                ParamValue::Int64(3),
            ],
        );
        assert!(
            !result.contains("$1"),
            "array placeholder survived: {result}"
        );
        assert!(
            result.contains('1') && result.contains('2') && result.contains('3'),
            "got: {result}"
        );
    }

    #[test]
    fn bind_any_op_rhs() {
        let result = bind_and_format(
            "SELECT * FROM t WHERE id = ANY($1)",
            &[ParamValue::Text("{a,b}".into())],
        );
        assert!(!result.contains("$1"), "got: {result}");
    }

    #[test]
    fn bind_all_op_rhs() {
        let result = bind_and_format(
            "SELECT * FROM t WHERE id = ALL($1)",
            &[ParamValue::Text("{a,b}".into())],
        );
        assert!(!result.contains("$1"), "got: {result}");
    }

    #[test]
    fn bind_update_returning() {
        let result = bind_and_format(
            "UPDATE t SET n = 1 WHERE id = $1 RETURNING $2 AS tag",
            &[ParamValue::Int64(7), ParamValue::Text("note".into())],
        );
        assert!(result.contains("id = 7"), "got: {result}");
        assert!(result.contains("'note'"), "got: {result}");
        assert!(!result.contains("$2"), "got: {result}");
    }

    #[test]
    fn bind_delete_returning() {
        let result = bind_and_format(
            "DELETE FROM t WHERE id = $1 RETURNING $2 AS tag",
            &[ParamValue::Int64(3), ParamValue::Text("gone".into())],
        );
        assert!(
            result.contains("id = 3") && result.contains("'gone'"),
            "got: {result}"
        );
        assert!(!result.contains("$2"), "got: {result}");
    }

    #[test]
    fn bind_update_limit() {
        let result = bind_and_format(
            "UPDATE t SET n = 1 WHERE id > 0 LIMIT $1",
            &[ParamValue::Int64(5)],
        );
        assert!(!result.contains("$1"), "got: {result}");
    }

    #[test]
    fn bind_interval_value_placeholder() {
        let result = bind_and_format(
            "SELECT now() - INTERVAL $1",
            &[ParamValue::Text("1 day".into())],
        );
        assert!(!result.contains("$1"), "got: {result}");
    }

    #[test]
    fn bind_update_from_subquery() {
        let result = bind_and_format(
            "UPDATE t SET n = s.y FROM (SELECT $1 AS y) s WHERE t.id = s.y",
            &[ParamValue::Int64(7)],
        );
        assert!(!result.contains("$1"), "got: {result}");
    }

    #[test]
    fn bind_insert_on_conflict_update_placeholder() {
        let result = bind_and_format(
            "INSERT INTO t (id, n) VALUES ($1, $2) ON CONFLICT (id) DO UPDATE SET n = $3",
            &[
                ParamValue::Int64(1),
                ParamValue::Int64(2),
                ParamValue::Int64(99),
            ],
        );
        assert!(!result.contains("$3"), "got: {result}");
    }

    #[test]
    fn bind_window_partition_by_placeholder() {
        let result = bind_and_format(
            "SELECT LAG(x) OVER (PARTITION BY $1 ORDER BY b) FROM t",
            &[ParamValue::Text("user_id".into())],
        );
        assert!(!result.contains("$1"), "got: {result}");
    }

    /// A float parameter resolves to a float, whole or fractional, never to
    /// the decimal or integer its plain text reads as.
    #[test]
    fn a_float_param_resolves_to_a_float() {
        use crate::resolver::expr::convert_value;
        use crate::types::SqlValue;
        for f in [1.5, 2.0, 0.1, -3.25, 1e300, 1e-300, f64::INFINITY] {
            let literal = placeholder_to_value("$1", &[ParamValue::Float64(f)])
                .expect("the placeholder names the one parameter");
            assert_eq!(
                convert_value(&literal).expect("a float literal resolves"),
                SqlValue::Float(f),
                "{f}"
            );
        }
        let literal = placeholder_to_value("$1", &[ParamValue::Float64(f64::NAN)])
            .expect("the placeholder names the one parameter");
        assert!(
            matches!(convert_value(&literal), Ok(SqlValue::Float(f)) if f.is_nan()),
            "{literal}"
        );
    }

    #[test]
    fn bind_window_order_by_placeholder() {
        let result = bind_and_format(
            "SELECT LAG(x) OVER (PARTITION BY a ORDER BY $1) FROM t",
            &[ParamValue::Text("ts".into())],
        );
        assert!(!result.contains("$1"), "got: {result}");
    }
}