fv-streams-sql 0.9.0

The SQL front door for FusionVault Streams: a streaming SQL statement parses to a small typed logical plan and lowers to the same topology the TOML frontend produces — one runtime, two front doors. No DataFusion planner, no fork; expressions compile through the value dialect as the steps already do.
Documentation
//! The typed streaming **logical plan** — our own IR, the seam between the SQL front-end and the
//! `StageDef` lowering. Small and streaming-aware by construction (unlike DataFusion's `LogicalPlan`);
//! a future optimiser is a pass over this, and a second dialect is another producer of it.

use sqlparser::ast::{Expr, Query, SetExpr, Statement};
use sqlparser::dialect::GenericDialect;
use sqlparser::parser::Parser;

mod cte;
mod emit;
mod functions;
mod join;
mod select;

use cte::{bind_ctes, parse_ctes};
use emit::{attach_trigger, split_emit};
use select::plan_select;

/// One projected output column.
#[derive(Debug, Clone, PartialEq)]
pub struct Proj {
    /// The output column name.
    pub out: String,
    /// `Some(dialect_expr)` when computed or renamed (lowers to an `applyExpression`); `None` for a
    /// bare pass-through column named `out` (kept by the `select`).
    pub expr: Option<String>,
}

/// One aggregate in a `SELECT` over a window — `op` over `column` (empty for `count(*)`), out `alias`.
#[derive(Debug, Clone, PartialEq)]
pub struct AggSpec {
    pub op: String,
    pub column: String,
    pub alias: String,
    /// The call as written (`count(*)`, `sum(price)`), for `HAVING` to match by.
    pub call: String,
}

/// The windowing a `GROUP BY <window-fn>(…)` selects. An optional trailing `INTERVAL` on any window
/// function is the **allowed lateness** — how long a window stays open for late events after its end
/// (the engine's `allowedLatenessMs`); `None` leaves it at the engine default. q4/q5 need a large
/// lateness so no out-of-order bid is dropped from a closed window.
#[derive(Debug, Clone, PartialEq)]
pub enum Win {
    /// `TUMBLE(ts, INTERVAL <size> [, INTERVAL <lateness>])` — fixed windows.
    Tumble { size_ms: i64, lateness_ms: Option<i64> },
    /// `HOP(ts, INTERVAL <slide>, INTERVAL <size> [, INTERVAL <lateness>])` — sliding windows.
    Hop {
        slide_ms: i64,
        size_ms: i64,
        lateness_ms: Option<i64>,
    },
    /// `SESSION(ts, INTERVAL <gap> [, INTERVAL <lateness>])` — dynamic session windows.
    Session { gap_ms: i64, lateness_ms: Option<i64> },
}

/// The early-firing trigger a trailing `EMIT CHANGES EVERY …` clause selects — the 5a updating
/// aggregate. sqlparser does not know `EMIT`, so a small lexical pre-pass ([`split_emit`]) strips
/// and parses this clause before the rest of the statement reaches sqlparser (design §10 / A).
#[derive(Debug, Clone, Copy, PartialEq)]
pub enum Trigger {
    EveryRows(u64),
    EveryMs(i64),
}

/// How a two-input `JOIN` maps to an operator. An interval predicate (`ts BETWEEN …`) in the `ON`
/// makes it a stream×stream interval join; a plain equi-join makes the right side a **bounded
/// table** — the 5b lookup join.
#[derive(Debug, Clone, PartialEq)]
pub enum JoinKind {
    /// `JOIN … ON k=k AND ts BETWEEN … ` → `streamJoin`.
    Interval { time_column: String, window_ms: i64 },
    /// `JOIN <bounded> ON k=k` → the 5b `lookupJoin` (right = the bounded table).
    Lookup,
}

/// The typed logical plan. 5c-i covers the stateless shape (source → filter → project → sink);
/// 5c-ii adds the windowed aggregate, the updating aggregate, and the joins.
#[derive(Debug, Clone, PartialEq)]
pub enum Plan {
    /// `FROM <dataset>` — a source dataset by name.
    Source(String),
    /// `FROM <left> JOIN <right> ON <key> [interval]` — a two-input join.
    Join {
        left: Box<Plan>,
        right: Box<Plan>,
        join_key: String,
        kind: JoinKind,
        /// Right columns the query names with the right's qualifier → their output name (the
        /// join's `rightRename`), so `a.ts` can never mean the left's `ts`.
        right_rename: std::collections::BTreeMap<String, String>,
    },
    /// `WHERE <predicate>` — the predicate as a value-dialect string.
    Filter { input: Box<Plan>, predicate: String },
    /// `SELECT …`. `star` = `SELECT *` (pass-through, no step); else the projected columns.
    Project {
        input: Box<Plan>,
        star: bool,
        items: Vec<Proj>,
    },
    /// `SELECT … FROM … GROUP BY <window-fn>(ts,…), <keys>` — a windowed keyed aggregate. A
    /// `Some(trigger)` makes it the **5a updating aggregate** (early firing).
    Aggregate {
        input: Box<Plan>,
        time_column: String,
        window: Win,
        group_by: Vec<String>,
        /// Pre-aggregate computed columns (an aggregate's expression argument, e.g. a `CASE` fed to
        /// `count(DISTINCT …)`) — lowered to `applyExpression` steps BEFORE the aggregate.
        pre: Vec<Proj>,
        aggs: Vec<AggSpec>,
        /// Post-aggregate computed columns — a `SELECT` expression over the window/group/aggregates
        /// (e.g. `day = substring(epochToIso(windowStart), 1, 10)`), lowered to `applyExpression`
        /// steps AFTER the aggregate (the q15/q16 day/minute columns).
        post: Vec<Proj>,
        /// `HAVING <predicate>` — a filter over the aggregate's output rows (the window, the group
        /// columns, the aggregate aliases), lowered to a `filter` step AFTER the aggregate and its
        /// post columns.
        having: Option<String>,
        trigger: Option<Trigger>,
    },
    /// `SELECT … FROM (SELECT …, ROW_NUMBER() OVER (PARTITION BY g ORDER BY c [DESC]) AS rn FROM s)
    /// WHERE rn <= n` — the streaming top-N idiom → the `topN` operator.
    TopN {
        input: Box<Plan>,
        group_by: Vec<String>,
        order_by: String,
        /// A second `ORDER BY` column → the tie-break (`tieBy`, ascending).
        tie_by: Option<String>,
        descending: bool,
        n: usize,
    },
    /// A CTE (`WITH <name> AS (<subquery>)`): the sub-plan `input` materialises as its own stage
    /// under `name`, and downstream references to `name` read that stage. The multi-stage boundary —
    /// the SQL equivalent of a named intermediate dataset in the TOML `[[transforms]]` DAG.
    Named { name: String, input: Box<Plan> },
    /// `INSERT INTO <dataset>` — the terminal sink dataset by name.
    Sink { input: Box<Plan>, name: String },
}

/// Parse one streaming SQL statement into a [`Plan`]. Fails LOUDLY, streaming-aware, on anything
/// outside the supported subset.
pub fn parse(sql: &str) -> Result<Plan, String> {
    // Strip a trailing `EMIT CHANGES EVERY …` (sqlparser does not know it) before parsing the rest.
    let (sql, trigger) = split_emit(sql)?;
    let plan = parse_statement(&sql)?;
    match trigger {
        Some(t) => attach_trigger(plan, t),
        None => Ok(plan),
    }
}

fn parse_statement(sql: &str) -> Result<Plan, String> {
    let mut stmts = Parser::parse_sql(&GenericDialect {}, sql).map_err(|e| format!("SQL parse error: {e}"))?;
    if stmts.len() != 1 {
        return Err(format!("expected exactly one statement, got {}", stmts.len()));
    }
    match stmts.pop().expect("one") {
        Statement::Insert(insert) => {
            let sink = object_name(&insert.table.to_string());
            let query = insert
                .source
                .ok_or("INSERT INTO <sink> needs a SELECT (`INSERT INTO out SELECT …`)")?;
            let input = plan_query(&query)?;
            Ok(Plan::Sink {
                input: Box::new(input),
                name: sink,
            })
        }
        Statement::Query(_) => Err("a streaming statement must write to a sink: `INSERT INTO <sink> SELECT …`".into()),
        other => Err(format!(
            "unsupported statement `{}` — the supported shape is `INSERT INTO <sink> SELECT … FROM … [WHERE …]`",
            statement_kind(&other)
        )),
    }
}

fn plan_query(query: &Query) -> Result<Plan, String> {
    // `WITH name AS (subquery), …` — each CTE becomes a named intermediate stream (the multi-stage
    // boundary). SQL scoping: a CTE sees only the CTEs declared before it, so bodies are bound in
    // order against the ones already built; the outer body binds against all of them.
    let ctes = parse_ctes(query.with.as_ref())?;
    if query.order_by.is_some() {
        return Err("ORDER BY over an unbounded stream has no streaming meaning — use `ROW_NUMBER() OVER (PARTITION BY …) <= n` for a top-N".into());
    }
    if query.limit_clause.is_some() {
        return Err("LIMIT over an unbounded stream is not supported — use a top-N (`ROW_NUMBER() … <= n`)".into());
    }
    let select = match query.body.as_ref() {
        SetExpr::Select(s) => s.as_ref(),
        SetExpr::SetOperation { .. } => {
            return Err("UNION / INTERSECT / EXCEPT are not supported — a stage reads one input; write one pipeline per branch into the same sink (`INSERT INTO out SELECT … FROM a` and `INSERT INTO out SELECT … FROM b`), which is UNION ALL by construction".into())
        }
        _ => return Err("only a SELECT body is supported".into()),
    };
    Ok(bind_ctes(plan_select(select)?, &ctes))
}

/// The output name a bare projected expression takes (a column keeps its own name; a compound
/// `a.b` takes `b`). `None` for a computed expression that needs an explicit alias.
fn column_name_of(e: &Expr) -> Option<String> {
    match e {
        Expr::Identifier(id) => Some(id.value.clone()),
        Expr::CompoundIdentifier(parts) => parts.last().map(|p| p.value.clone()),
        _ => None,
    }
}

/// An object name (`schema.name` → `name`) reduced to the last part — datasets are referenced by a
/// single name in the pipeline config.
fn object_name(raw: &str) -> String {
    raw.rsplit('.').next().unwrap_or(raw).trim_matches('"').to_string()
}

fn statement_kind(s: &Statement) -> &'static str {
    match s {
        Statement::Insert(_) => "INSERT",
        Statement::Query(_) => "SELECT",
        Statement::CreateTable(_) => "CREATE TABLE",
        _ => "statement",
    }
}