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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
//! The SQL reference: every statement form the front door compiles, every expression it translates,
//! and every form it refuses — each described once here, with an example that is compiled to write
//! `docs/reference/sql.md` (what a form becomes is the compiler's own output, and a refusal's message
//! is the compiler's own words). A test fails when the page is stale, when an example stops compiling
//! to the steps its form promises, or when a refused statement starts compiling.

use crate::compile;

/// A statement form the front door compiles.
#[derive(Debug)]
pub struct Form {
    pub title: &'static str,
    /// The shape, in SQL.
    pub shape: &'static str,
    pub doc: &'static str,
    /// A complete statement of the form.
    pub example: &'static str,
    /// The steps the example becomes, stage by stage, in order (`;` between stages).
    pub steps: &'static str,
}

/// A SQL expression and the expression-dialect text it becomes.
#[derive(Debug)]
pub struct Translation {
    pub sql: &'static str,
    pub dialect: &'static str,
}

/// A statement the front door refuses, and words its error must contain.
#[derive(Debug)]
pub struct Refused {
    pub example: &'static str,
    pub says: &'static str,
}

/// Every form, in the order the reference lists them.
pub const FORMS: &[Form] = &[
    Form {
        title: "Filter and compute",
        shape: "INSERT INTO <sink> SELECT <column or expression [AS name]>, … FROM <source> [WHERE <condition>]",
        doc: "Keep the rows that match, compute columns, keep the listed ones.",
        example: "INSERT INTO out SELECT auction, price * 0.908 AS eur FROM bids WHERE type = 'bid'",
        steps: "filter, applyExpression, select",
    },
    Form {
        title: "Keep every column and add some",
        shape: "SELECT *, <expression> AS <name>, … FROM <source>",
        doc: "Every input column stays; each computed column is added at the end.",
        example: "INSERT INTO out SELECT *, price * 0.908 AS eur FROM bids WHERE type = 'bid'",
        steps: "filter, applyExpression",
    },
    Form {
        title: "Qualified columns",
        shape: "SELECT <source>.<column>, <source>.* FROM <source>",
        doc: "Over one input, a qualifier names that input's columns.",
        example: "INSERT INTO out SELECT bids.auction, bids.price * 2 AS twice FROM bids",
        steps: "applyExpression, select",
    },
    Form {
        title: "Tumbling window",
        shape: "GROUP BY <keys>, TUMBLE(<time column>, INTERVAL <size>)",
        doc: "Fixed-size, back-to-back windows of event time, one result per window and group when the window closes. The aggregates are `count`, `sum`, `avg`, `min` and `max`. The output is `windowStart` and `windowEnd`, the group keys, then the aggregates — the window columns whether or not they are selected.",
        example: "INSERT INTO out SELECT auction, count(*) AS bids, max(price) AS top FROM bids GROUP BY auction, TUMBLE(dateTime, INTERVAL '10' SECOND)",
        steps: "windowedAggregate",
    },
    Form {
        title: "Sliding window",
        shape: "GROUP BY <keys>, HOP(<time column>, INTERVAL <slide>, INTERVAL <size>)",
        doc: "Fixed-size windows that start every `<slide>`, so they overlap. The slide comes before the size: `HOP(dateTime, INTERVAL '2' SECOND, INTERVAL '10' SECOND)` starts a 10-second window every 2 seconds.",
        example: "INSERT INTO out SELECT auction, count(*) AS bids FROM bids GROUP BY auction, HOP(dateTime, INTERVAL '2' SECOND, INTERVAL '10' SECOND)",
        steps: "windowedAggregate",
    },
    Form {
        title: "Session window",
        shape: "GROUP BY <keys>, SESSION(<time column>, INTERVAL <gap>)",
        doc: "A window per group that stays open while events keep arriving within `<gap>` of each other.",
        example: "INSERT INTO out SELECT bidder, sum(price) AS spent FROM bids GROUP BY bidder, SESSION(dateTime, INTERVAL '5' SECOND)",
        steps: "sessionAggregate",
    },
    Form {
        title: "Watermark delay",
        shape: "TUMBLE(…, INTERVAL <size>, INTERVAL <delay>) · HOP(…, INTERVAL <delay>) · SESSION(…, INTERVAL <delay>)",
        doc: "A last interval on any window sets how far the watermark trails the newest event time, which is how out of order rows may arrive and still count. It is the step's `allowedLatenessMs`, also called the watermark delay.",
        example: "INSERT INTO out SELECT auction, count(*) AS bids FROM bids GROUP BY auction, TUMBLE(dateTime, INTERVAL '10' SECOND, INTERVAL '30' SECOND)",
        steps: "windowedAggregate",
    },
    Form {
        title: "Early results",
        shape: "… GROUP BY …, TUMBLE(…) EMIT CHANGES EVERY <n> ROWS | <n> MS",
        doc: "Also emit a window's result while it is still open, every `<n>` rows or every `<n>` milliseconds of wall-clock time. The result becomes updating: a window and group is emitted again under the same key. Tumbling and sliding windows only.",
        example: "INSERT INTO out SELECT auction, count(*) AS bids FROM bids GROUP BY auction, TUMBLE(dateTime, INTERVAL '10' SECOND) EMIT CHANGES EVERY 500 MS",
        steps: "windowedAggregate",
    },
    Form {
        title: "Filter after aggregating",
        shape: "… GROUP BY … HAVING <condition>",
        doc: "Keep only the results that match. An aggregate in the condition names the column it is selected as.",
        example: "INSERT INTO out SELECT auction, count(*) AS bids FROM bids GROUP BY auction, TUMBLE(dateTime, INTERVAL '10' SECOND) HAVING count(*) > 5",
        steps: "windowedAggregate, filter",
    },
    Form {
        title: "Distinct counts",
        shape: "count(DISTINCT <column>) · count(DISTINCT CASE WHEN … THEN <column> END)",
        doc: "The number of distinct values per window and group; a `CASE` argument counts only the rows it keeps.",
        example: "INSERT INTO out SELECT count(DISTINCT bidder) AS bidders, count(DISTINCT CASE WHEN price < 10000 THEN bidder END) AS smallBidders FROM bids GROUP BY TUMBLE(dateTime, INTERVAL '1' DAY)",
        steps: "applyExpression, windowedAggregate",
    },
    Form {
        title: "Computed columns after aggregating",
        shape: "SELECT <expression over windowStart, keys or aggregates> AS <name> … GROUP BY …",
        doc: "A selected expression over the window's columns, keys or aggregates is computed on each result.",
        example: "INSERT INTO out SELECT count(*) AS bids, substring(epochToIso(windowStart), 1, 10) AS day FROM bids GROUP BY TUMBLE(dateTime, INTERVAL '1' DAY)",
        steps: "windowedAggregate, applyExpression",
    },
    Form {
        title: "Interval join",
        shape: "FROM <a> JOIN <b> ON <a>.<key> = <b>.<key> AND <a>.<time> BETWEEN <b>.<time> - INTERVAL … AND <b>.<time> + INTERVAL …",
        doc: "Join two streams on a key, pairing rows whose event times are within the interval of each other. The key column must have the same name on both sides.",
        example: "INSERT INTO out SELECT * FROM bids JOIN auctions ON bids.auction = auctions.auction AND bids.dateTime BETWEEN auctions.dateTime - INTERVAL '10' SECOND AND auctions.dateTime + INTERVAL '10' SECOND",
        steps: "streamJoin",
    },
    Form {
        title: "Lookup join",
        shape: "FROM <stream> JOIN <table> ON <stream>.<key> = <table>.<key>",
        doc: "Enrich each row of a stream from a table (a `connector = \"table\"` dataset): a left join, so a row with no match keeps nulls. The key column must have the same name on both sides.",
        example: "INSERT INTO out SELECT bids.auction, categories.name FROM bids JOIN categories ON bids.category = categories.category",
        steps: "lookupJoin, applyExpression, select",
    },
    Form {
        title: "Top-N",
        shape: "FROM (SELECT …, ROW_NUMBER() OVER (PARTITION BY <keys> ORDER BY <column> [DESC], [<tie-break>]) AS rn FROM <source>) WHERE rn <= <n>",
        doc: "The first `<n>` rows per group by the order given; a second `ORDER BY` column breaks ties. The result is updating: the rows are emitted again as the ranking changes. Each row's key is its group and its rank, so an update replaces the row that held that rank, even when the `SELECT` leaves the rank out. The columns are written in the `SELECT` order, each in its input type.",
        example: "INSERT INTO out SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY auction ORDER BY price DESC, dateTime) AS rn FROM bids) WHERE rn <= 10",
        steps: "topN",
    },
    Form {
        title: "Several stages",
        shape: "INSERT INTO <sink> WITH <name> AS (<query>), … <query>",
        doc: "Each `WITH` query becomes its own stage, named by it and read by the stages after it: the SQL form of a pipeline's `[[transforms]]`.",
        example: "INSERT INTO out WITH counts AS (SELECT auction, count(*) AS n FROM bids GROUP BY auction, HOP(dateTime, INTERVAL '2' SECOND, INTERVAL '10' SECOND)) SELECT auction, n, windowStart FROM (SELECT auction, n, windowStart, ROW_NUMBER() OVER (PARTITION BY windowStart ORDER BY n DESC, auction) AS rn FROM counts) WHERE rn <= 1",
        steps: "windowedAggregate; topN, select",
    },
];

/// Every expression translation, in the order the reference lists them.
pub const EXPRESSIONS: &[Translation] = &[
    Translation {
        sql: "a = b",
        dialect: "(a == b)",
    },
    Translation {
        sql: "a <> b",
        dialect: "(a != b)",
    },
    Translation {
        sql: "a > 1 AND b < 2",
        dialect: "((a > 1) && (b < 2))",
    },
    Translation {
        sql: "a = 1 OR b = 2",
        dialect: "((a == 1) || (b == 2))",
    },
    Translation {
        sql: "NOT a",
        dialect: "!(a)",
    },
    Translation {
        sql: "a || b",
        dialect: "(a + b)",
    },
    Translation {
        sql: "price % 10",
        dialect: "(price % 10)",
    },
    Translation {
        sql: "CASE WHEN price < 100 THEN 'low' ELSE 'high' END",
        dialect: "((price < 100) ? 'low' : 'high')",
    },
    Translation {
        sql: "a IS NULL",
        dialect: "(a == null)",
    },
    Translation {
        sql: "a IS NOT NULL",
        dialect: "(a != null)",
    },
    Translation {
        sql: "a IN (1, 2)",
        dialect: "(a in [1, 2])",
    },
    Translation {
        sql: "a BETWEEN 1 AND 5",
        dialect: "((a >= 1) && (a <= 5))",
    },
    Translation {
        sql: "name LIKE 'a%b_'",
        dialect: "regexMatch(name, '^a.*b.$')",
    },
    Translation {
        sql: "SUBSTRING(name, 1, 3)",
        dialect: "substring(name, 1, 3)",
    },
    Translation {
        sql: "round(price * 0.9)",
        dialect: "round((price * 0.9))",
    },
];

/// Every refused form, in the order the reference lists them.
pub const REFUSED: &[Refused] = &[
    Refused {
        example: "SELECT * FROM bids",
        says: "INSERT INTO",
    },
    Refused {
        example: "INSERT INTO out SELECT auction, count(*) AS n FROM bids GROUP BY auction",
        says: "needs a window",
    },
    Refused {
        example: "INSERT INTO out SELECT * FROM bids ORDER BY price",
        says: "ORDER BY over an unbounded stream",
    },
    Refused {
        example: "INSERT INTO out SELECT * FROM bids LIMIT 10",
        says: "LIMIT over an unbounded stream",
    },
    Refused {
        example: "INSERT INTO out SELECT * FROM a UNION SELECT * FROM b",
        says: "UNION / INTERSECT / EXCEPT",
    },
    Refused {
        example: "INSERT INTO out SELECT * FROM a, b",
        says: "join",
    },
    Refused {
        example: "INSERT INTO out SELECT * FROM a JOIN b ON a.x = b.y",
        says: "share a name",
    },
    Refused {
        example: "INSERT INTO out SELECT *, auction FROM bids",
        says: "already keeps every column",
    },
    Refused {
        example: "INSERT INTO out SELECT * FROM bids HAVING price > 1",
        says: "use WHERE",
    },
    Refused {
        example: "INSERT INTO out SELECT * FROM bids EMIT CHANGES EVERY 1 ROWS",
        says: "EMIT",
    },
    Refused {
        example: "INSERT INTO out WITH RECURSIVE r AS (SELECT * FROM bids) SELECT * FROM r",
        says: "WITH RECURSIVE",
    },
];

/// The steps `sql` compiles to, stage by stage (`;` between stages).
pub fn steps_of(sql: &str) -> Result<String, String> {
    let topo = compile(sql)?;
    Ok(topo
        .stages
        .iter()
        .map(|s| {
            s.steps
                .iter()
                .map(|step| step["op"].as_str().unwrap_or("?").to_string())
                .collect::<Vec<_>>()
                .join(", ")
        })
        .collect::<Vec<_>>()
        .join("; "))
}

/// The expression-dialect text `sql` (a scalar expression) becomes: the computed column of
/// `INSERT INTO o SELECT <sql> AS x FROM s`.
pub fn dialect_of(sql: &str) -> Result<String, String> {
    let topo = compile(&format!("INSERT INTO o SELECT {sql} AS x FROM s"))?;
    topo.stages
        .iter()
        .flat_map(|s| s.steps.iter())
        .find(|step| step["op"] == "applyExpression" && step["column"] == "x")
        .and_then(|step| step["expression"].as_str().map(String::from))
        .ok_or_else(|| format!("`{sql}` compiled to no computed column"))
}

/// The steps as links into the steps reference (`windowedAggregate` → its section).
fn linked(steps: &str) -> String {
    steps
        .split("; ")
        .map(|stage| {
            stage
                .split(", ")
                .map(|op| format!("[`{op}`](steps.md#{})", op.to_lowercase()))
                .collect::<Vec<_>>()
                .join(" → ")
        })
        .collect::<Vec<_>>()
        .join(" ; then ")
}

fn anchor(title: &str) -> String {
    title
        .to_lowercase()
        .chars()
        .filter_map(|c| match c {
            'a'..='z' | '0'..='9' | '-' => Some(c),
            ' ' => Some('-'),
            _ => None,
        })
        .collect()
}

/// `|` inside a table cell.
fn cell(s: &str) -> String {
    s.replace('|', "\\|")
}

/// The page. Each form's steps and each refusal's message come from compiling its example.
pub fn render_markdown() -> String {
    let mut md = String::new();
    md.push_str("# SQL\n\n");
    md.push_str("<!-- Generated from crates/fv-streams-sql/src/reference.rs by `make reference`: every example below is compiled to write this page. Do not edit by hand: the gate fails when this file is out of date. -->\n\n");
    md.push_str("A pipeline file's `sql = \"INSERT INTO <sink> SELECT …\"` takes the place of its `[[transforms]]`. ");
    md.push_str("The datasets it reads and writes stay `[datasets.<name>]` tables ([connectors](connectors.md)), and `sql` is a top-level key, so it goes before the first `[table]`. ");
    md.push_str("The statement compiles to the same [steps](steps.md) a hand-written pipeline runs: one runtime, two front doors. ");
    md.push_str("A statement outside the forms below is refused when the pipeline starts, with a message that names the shape to use.\n\n");
    md.push_str("```toml\nname = \"eur-bids\"\nsql = \"INSERT INTO out SELECT auction, price * 0.908 AS eur FROM bids WHERE type = 'bid'\"\n\n[datasets.bids]\ntopic = \"bids\"\n\n[datasets.out]\ntopic = \"eur-bids\"\n```\n\n");
    md.push_str("## Forms\n\n| form | shape | becomes |\n|---|---|---|\n");
    for f in FORMS {
        let steps = steps_of(f.example).unwrap_or_else(|e| format!("(does not compile: {e})"));
        md.push_str(&format!(
            "| [{}](#{}) | `{}` | {} |\n",
            f.title,
            anchor(f.title),
            cell(f.shape),
            linked(&steps)
        ));
    }
    for f in FORMS {
        let steps = steps_of(f.example).unwrap_or_else(|e| format!("(does not compile: {e})"));
        md.push_str(&format!(
            "\n### {}\n\n{}\n\n```sql\n{}\n```\n\n",
            f.title, f.doc, f.example
        ));
        md.push_str(&format!("**Becomes:** {}\n", linked(&steps)));
    }
    md.push_str("\n## Expressions\n\n");
    md.push_str(
        "Scalar expressions translate into the expression dialect the `filter` and `applyExpression` steps run. ",
    );
    md.push_str("A function keeps the name and case it is written with (the dialect's names are case-sensitive: `epochToIso`, `regexMatch`). ");
    md.push_str("`LIKE` takes a string-literal pattern; `ILIKE` matches regardless of case.\n\n");
    md.push_str("| SQL | expression dialect |\n|---|---|\n");
    for t in EXPRESSIONS {
        md.push_str(&format!("| `{}` | `{}` |\n", cell(t.sql), cell(t.dialect)));
    }
    md.push_str("\n## Refused\n\nEach of these is refused when the pipeline starts, with this message:\n\n| statement | message |\n|---|---|\n");
    for r in REFUSED {
        let says = compile(r.example).err().unwrap_or_else(|| "(compiles)".into());
        md.push_str(&format!("| `{}` | {} |\n", cell(r.example), cell(&says)));
    }
    md
}

#[cfg(test)]
mod tests {
    use super::*;

    fn doc_path() -> std::path::PathBuf {
        std::path::Path::new(env!("CARGO_MANIFEST_DIR")).join("../../docs/reference/sql.md")
    }

    #[test]
    fn the_sql_reference_is_current() {
        let rendered = render_markdown();
        if std::env::var_os("FV_WRITE_REFERENCE").is_some() {
            std::fs::write(doc_path(), &rendered).unwrap();
        }
        let on_disk = std::fs::read_to_string(doc_path()).unwrap_or_default();
        assert!(
            on_disk == rendered,
            "docs/reference/sql.md is out of date with the code; run `make reference`"
        );
    }

    #[test]
    fn every_form_compiles_to_the_steps_it_promises() {
        let wrong: Vec<String> = FORMS
            .iter()
            .filter_map(|f| match steps_of(f.example) {
                Ok(got) if got == f.steps => None,
                got => Some(format!("{}: promises `{}`, compiles to {got:?}", f.title, f.steps)),
            })
            .collect();
        assert!(wrong.is_empty(), "\n{}", wrong.join("\n"));
    }

    #[test]
    fn every_expression_translates_as_listed() {
        for t in EXPRESSIONS {
            assert_eq!(
                dialect_of(t.sql).unwrap_or_else(|e| panic!("{}: {e}", t.sql)),
                t.dialect,
                "{}",
                t.sql
            );
        }
    }

    #[test]
    fn every_refused_statement_is_refused_with_its_reason() {
        let wrong: Vec<String> = REFUSED
            .iter()
            .filter_map(|r| match compile(r.example) {
                Err(e) if e.contains(r.says) => None,
                Err(e) => Some(format!("`{}`: refused, but not with `{}`: {e}", r.example, r.says)),
                Ok(_) => Some(format!("`{}` compiles", r.example)),
            })
            .collect();
        assert!(wrong.is_empty(), "\n{}", wrong.join("\n"));
    }
}