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
//! Lower the typed [`Plan`] to `StageDef`s — the engine's JSON step vocabulary, the **same** the TOML
//! frontend emits. This is the one place the SQL and TOML front doors converge, so they cannot drift
//! (the anti-drift gate, 5c-iv, proves it byte-for-byte).
//!
//! **Multi-stage** (5c-v): a `Named` input (a CTE) or a subquery that feeds a join/aggregate is
//! *materialised* as its own stage under a dataset name, and referenced by that name downstream —
//! the SQL equivalent of the TOML `[[transforms]]` DAG wired by intermediate dataset names. A plain
//! `Source` emits no stage, so every single-stage statement lowers byte-identically to before.

use std::collections::HashSet;

use fv_streams_types::StageDef;
use serde_json::{json, Value};

use crate::plan::{AggSpec, JoinKind, Plan, Proj, Trigger, Win};

/// Lower a whole plan (terminating in a sink) to its stages, upstream stages first, the sink last.
pub fn lower(plan: &Plan) -> Result<Vec<StageDef>, String> {
    let (body, sink) = match plan {
        Plan::Sink { input, name } => (input.as_ref(), name.clone()),
        _ => return Err("a streaming statement must terminate in a sink (`INSERT INTO <sink>`)".into()),
    };
    let mut ctx = Ctx::default();
    ctx.emit(body, sink)?;
    Ok(ctx.stages)
}

/// The accumulator: the stages built so far (upstream before downstream), the CTE names already
/// materialised (each CTE is one stage, however many times it is referenced), and a counter for the
/// anonymous intermediate names a bare subquery needs.
#[derive(Default)]
struct Ctx {
    stages: Vec<StageDef>,
    emitted: HashSet<String>,
    next: usize,
}

/// The base a stateless stage reads: one dataset, or an operator (join / topN) that runs first over
/// its input(s), whose rows the WHERE and projection then post-process.
enum Base {
    Source(String),
    Op { inputs: Vec<String>, steps: Vec<Value> },
}

impl Ctx {
    fn fresh(&mut self) -> String {
        let n = format!("_fv_stage{}", self.next);
        self.next += 1;
        n
    }

    /// Make `plan`'s rows available as a dataset and return its name, emitting any upstream stages.
    /// A plain `Source` is already a dataset (no stage); a `Named` CTE emits one stage under its name
    /// (once); any other sub-plan emits an anonymous intermediate stage.
    fn materialize(&mut self, plan: &Plan) -> Result<String, String> {
        match plan {
            Plan::Source(name) => Ok(name.clone()),
            Plan::Named { name, input } => {
                if self.emitted.insert(name.clone()) {
                    self.emit(input, name.clone())?;
                }
                Ok(name.clone())
            }
            other => {
                let name = self.fresh();
                self.emit(other, name.clone())?;
                Ok(name)
            }
        }
    }

    /// Lower `body` to ONE stage whose output is `out`, emitting any upstream stages its inputs need,
    /// then pushing the stage (so upstream stages always precede it in `self.stages`).
    fn emit(&mut self, body: &Plan, out: String) -> Result<(), String> {
        let stage = match body {
            Plan::Project { input, star, items } => self.lower_stateless(input, *star, items, out)?,
            Plan::Aggregate {
                input,
                time_column,
                window,
                group_by,
                pre,
                aggs,
                post,
                having,
                trigger,
            } => self.lower_aggregate(
                input,
                time_column,
                window,
                group_by,
                pre,
                aggs,
                post,
                having.as_deref(),
                *trigger,
                out,
            )?,
            // A CTE whose body is just another named stream / source (`WITH a AS (SELECT * FROM b)`
            // with no ops): a pass-through stage with no steps.
            Plan::Source(_) | Plan::Named { .. } => {
                let input = self.materialize(body)?;
                StageDef {
                    steps: vec![],
                    inputs: vec![input],
                    output: out,
                }
            }
            _ => return Err("expected a SELECT projection or a GROUP BY aggregate".into()),
        };
        self.stages.push(stage);
        Ok(())
    }

    /// The base (source / join / topN, with its upstream stages materialised) and any `WHERE`
    /// predicate under a projection.
    fn base_and_filter(&mut self, body: &Plan) -> Result<(Base, Option<String>), String> {
        let (inner, pred) = match body {
            Plan::Filter { input, predicate } => (input.as_ref(), Some(predicate.clone())),
            other => (other, None),
        };
        let base = match inner {
            Plan::Source(_) | Plan::Named { .. } => Base::Source(self.materialize(inner)?),
            Plan::Join {
                left,
                right,
                join_key,
                kind,
                right_rename,
            } => {
                // `FROM <left> JOIN <right>` → inputs[0]=left, inputs[1]=right, each materialised (a
                // CTE / subquery side becomes its own upstream stage; a base source stays a name).
                let step = match kind {
                    JoinKind::Interval { time_column, window_ms } => json!({
                        "op": "streamJoin",
                        "joinKey": join_key,
                        "timeColumn": time_column,
                        "windowMs": window_ms,
                        "keyBy": true,
                    }),
                    // the 5b lookup join: distribution defaults to broadcast (the small table replicated).
                    JoinKind::Lookup => json!({ "op": "lookupJoin", "joinKey": join_key }),
                };
                let mut step = step;
                if !right_rename.is_empty() {
                    step["rightRename"] = json!(right_rename);
                }
                let inputs = vec![self.materialize(left)?, self.materialize(right)?];
                Base::Op {
                    inputs,
                    steps: vec![step],
                }
            }
            Plan::TopN {
                input,
                group_by,
                order_by,
                tie_by,
                descending,
                n,
            } => {
                // the subquery's WHERE (if any) filters before the rank; its source is materialised.
                let (base, pre) = self.base_and_filter(input)?;
                let Base::Source(source) = base else {
                    return Err("a ROW_NUMBER top-N must read a single source or CTE".into());
                };
                let mut steps = Vec::new();
                if let Some(pred) = pre {
                    steps.push(json!({ "op": "filter", "expression": pred }));
                }
                let mut topn = json!({
                    "op": "topN",
                    "n": n,
                    "orderBy": order_by,
                    "direction": if *descending { "desc" } else { "asc" },
                    "keyBy": true,
                    "groupBy": group_by,
                });
                if let Some(tie) = tie_by {
                    topn.as_object_mut().expect("obj").insert("tieBy".into(), json!(tie));
                }
                steps.push(topn);
                Base::Op {
                    inputs: vec![source],
                    steps,
                }
            }
            _ => return Err("expected a source, a join, or a top-N under the projection".into()),
        };
        Ok((base, pred))
    }

    fn lower_stateless(&mut self, input: &Plan, star: bool, items: &[Proj], sink: String) -> Result<StageDef, String> {
        let (base, predicate) = self.base_and_filter(input)?;
        let mut steps: Vec<Value> = Vec::new();
        let inputs = match base {
            Base::Source(name) => vec![name],
            Base::Op {
                inputs,
                steps: op_steps,
            } => {
                // the operator (join / topN) runs first; a WHERE and the projection are its post steps.
                steps.extend(op_steps);
                inputs
            }
        };
        if let Some(pred) = predicate {
            steps.push(json!({ "op": "filter", "expression": pred }));
        }
        // computed/renamed columns become applyExpression steps; a `SELECT *` keeps every column (no
        // trailing select — `*, <expr> AS name` just adds the computed one), else a select trims to
        // the projected list.
        for Proj { out, expr } in items {
            if let Some(expr) = expr {
                steps.push(json!({ "op": "applyExpression", "column": out, "expression": expr }));
            }
        }
        if !star {
            let columns: Vec<&str> = items.iter().map(|p| p.out.as_str()).collect();
            steps.push(json!({ "op": "select", "columns": columns }));
        }
        Ok(StageDef {
            steps,
            inputs,
            output: sink,
        })
    }

    #[allow(clippy::too_many_arguments)]
    fn lower_aggregate(
        &mut self,
        input: &Plan,
        time_column: &str,
        window: &Win,
        group_by: &[String],
        pre: &[Proj],
        aggs: &[AggSpec],
        post: &[Proj],
        having: Option<&str>,
        trigger: Option<Trigger>,
        sink: String,
    ) -> Result<StageDef, String> {
        // the aggregate's source: a base dataset, a CTE, or a subquery (materialised as an upstream
        // stage — the nested-aggregate shape, e.g. avg over a per-key max).
        let (source, predicate) = match input {
            Plan::Filter { input, predicate } => (self.materialize(input)?, Some(predicate.clone())),
            other => (self.materialize(other)?, None),
        };
        let mut steps: Vec<Value> = Vec::new();
        if let Some(pred) = predicate {
            steps.push(json!({ "op": "filter", "expression": pred }));
        }
        // pre-aggregate computed columns (e.g. a null-masking CASE fed to count(DISTINCT …)) run
        // before the aggregate, as applyExpression steps.
        for Proj { out, expr } in pre {
            if let Some(expr) = expr {
                steps.push(json!({ "op": "applyExpression", "column": out, "expression": expr }));
            }
        }
        let agg_json: Vec<Value> = aggs
            .iter()
            .map(|a| json!({ "op": a.op, "column": a.column, "alias": a.alias }))
            .collect();
        // A keyBy aggregate: the SQL groups by key, so the input is re-keyed (shuffled) before it.
        let mut step = match window {
            Win::Tumble { size_ms, .. } => json!({
                "op": "windowedAggregate",
                "timeColumn": time_column,
                "windowMs": size_ms,
                "keyBy": true,
                "groupBy": group_by,
                "aggs": agg_json,
            }),
            Win::Hop { slide_ms, size_ms, .. } => json!({
                "op": "windowedAggregate",
                "timeColumn": time_column,
                "windowMs": size_ms,
                "slideMs": slide_ms,
                "keyBy": true,
                "groupBy": group_by,
                "aggs": agg_json,
            }),
            Win::Session { gap_ms, .. } => json!({
                "op": "sessionAggregate",
                "timeColumn": time_column,
                "gapMs": gap_ms,
                "keyBy": true,
                "groupBy": group_by,
                "aggs": agg_json,
            }),
        };
        // an optional allowed-lateness INTERVAL on the window function → the `allowedLatenessMs` the
        // engine reads (how long a closed window still accepts late events).
        let lateness = match window {
            Win::Tumble { lateness_ms, .. } | Win::Hop { lateness_ms, .. } | Win::Session { lateness_ms, .. } => {
                *lateness_ms
            }
        };
        if let Some(ms) = lateness {
            step.as_object_mut()
                .expect("agg step is an object")
                .insert("allowedLatenessMs".into(), json!(ms));
        }
        // `EMIT CHANGES EVERY …` → the 5a updating aggregate: the `emit` block on the same step
        // (matching the engine's `parse_trigger`, so SQL and TOML produce the identical step).
        if let Some(trigger) = trigger {
            let emit = match trigger {
                Trigger::EveryRows(n) => json!({ "everyRows": n }),
                Trigger::EveryMs(ms) => json!({ "everyMs": ms }),
            };
            step.as_object_mut()
                .expect("agg step is an object")
                .insert("emit".into(), emit);
        }
        steps.push(step);
        // post-aggregate computed columns (day/minute over windowStart) — applyExpression after the agg.
        for Proj { out, expr } in post {
            if let Some(expr) = expr {
                steps.push(json!({ "op": "applyExpression", "column": out, "expression": expr }));
            }
        }
        // HAVING: a filter over the aggregate's rows, after the post columns it may read.
        if let Some(pred) = having {
            steps.push(json!({ "op": "filter", "expression": pred }));
        }
        Ok(StageDef {
            steps,
            inputs: vec![source],
            output: sink,
        })
    }
}