Skip to main content

fv_streams_sql/
reference.rs

1//! The SQL reference: every statement form the front door compiles, every expression it translates,
2//! and every form it refuses — each described once here, with an example that is compiled to write
3//! `docs/reference/sql.md` (what a form becomes is the compiler's own output, and a refusal's message
4//! is the compiler's own words). A test fails when the page is stale, when an example stops compiling
5//! to the steps its form promises, or when a refused statement starts compiling.
6
7use crate::compile;
8
9/// A statement form the front door compiles.
10#[derive(Debug)]
11pub struct Form {
12    pub title: &'static str,
13    /// The shape, in SQL.
14    pub shape: &'static str,
15    pub doc: &'static str,
16    /// A complete statement of the form.
17    pub example: &'static str,
18    /// The steps the example becomes, stage by stage, in order (`;` between stages).
19    pub steps: &'static str,
20}
21
22/// A SQL expression and the expression-dialect text it becomes.
23#[derive(Debug)]
24pub struct Translation {
25    pub sql: &'static str,
26    pub dialect: &'static str,
27}
28
29/// A statement the front door refuses, and words its error must contain.
30#[derive(Debug)]
31pub struct Refused {
32    pub example: &'static str,
33    pub says: &'static str,
34}
35
36/// Every form, in the order the reference lists them.
37pub const FORMS: &[Form] = &[
38    Form {
39        title: "Filter and compute",
40        shape: "INSERT INTO <sink> SELECT <column or expression [AS name]>, … FROM <source> [WHERE <condition>]",
41        doc: "Keep the rows that match, compute columns, keep the listed ones.",
42        example: "INSERT INTO out SELECT auction, price * 0.908 AS eur FROM bids WHERE type = 'bid'",
43        steps: "filter, applyExpression, select",
44    },
45    Form {
46        title: "Keep every column and add some",
47        shape: "SELECT *, <expression> AS <name>, … FROM <source>",
48        doc: "Every input column stays; each computed column is added at the end.",
49        example: "INSERT INTO out SELECT *, price * 0.908 AS eur FROM bids WHERE type = 'bid'",
50        steps: "filter, applyExpression",
51    },
52    Form {
53        title: "Qualified columns",
54        shape: "SELECT <source>.<column>, <source>.* FROM <source>",
55        doc: "Over one input, a qualifier names that input's columns.",
56        example: "INSERT INTO out SELECT bids.auction, bids.price * 2 AS twice FROM bids",
57        steps: "applyExpression, select",
58    },
59    Form {
60        title: "Tumbling window",
61        shape: "GROUP BY <keys>, TUMBLE(<time column>, INTERVAL <size>)",
62        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.",
63        example: "INSERT INTO out SELECT auction, count(*) AS bids, max(price) AS top FROM bids GROUP BY auction, TUMBLE(dateTime, INTERVAL '10' SECOND)",
64        steps: "windowedAggregate",
65    },
66    Form {
67        title: "Sliding window",
68        shape: "GROUP BY <keys>, HOP(<time column>, INTERVAL <slide>, INTERVAL <size>)",
69        doc: "Fixed-size windows that start every `<slide>`, so they overlap. The slide comes before the size, as in Flink's `HOP`; Spark's `window(time, size, slide)` takes them the other way round.",
70        example: "INSERT INTO out SELECT auction, count(*) AS bids FROM bids GROUP BY auction, HOP(dateTime, INTERVAL '2' SECOND, INTERVAL '10' SECOND)",
71        steps: "windowedAggregate",
72    },
73    Form {
74        title: "Session window",
75        shape: "GROUP BY <keys>, SESSION(<time column>, INTERVAL <gap>)",
76        doc: "A window per group that stays open while events keep arriving within `<gap>` of each other.",
77        example: "INSERT INTO out SELECT bidder, sum(price) AS spent FROM bids GROUP BY bidder, SESSION(dateTime, INTERVAL '5' SECOND)",
78        steps: "sessionAggregate",
79    },
80    Form {
81        title: "Watermark delay",
82        shape: "TUMBLE(…, INTERVAL <size>, INTERVAL <delay>) · HOP(…, INTERVAL <delay>) · SESSION(…, INTERVAL <delay>)",
83        doc: "A last interval on any window sets how far the watermark trails the newest event time (the step's `allowedLatenessMs`): how out of order rows may arrive and still count. Flink calls it bounded out-of-orderness, Spark the watermark delay.",
84        example: "INSERT INTO out SELECT auction, count(*) AS bids FROM bids GROUP BY auction, TUMBLE(dateTime, INTERVAL '10' SECOND, INTERVAL '30' SECOND)",
85        steps: "windowedAggregate",
86    },
87    Form {
88        title: "Early results",
89        shape: "… GROUP BY …, TUMBLE(…) EMIT CHANGES EVERY <n> ROWS | <n> MS",
90        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.",
91        example: "INSERT INTO out SELECT auction, count(*) AS bids FROM bids GROUP BY auction, TUMBLE(dateTime, INTERVAL '10' SECOND) EMIT CHANGES EVERY 500 MS",
92        steps: "windowedAggregate",
93    },
94    Form {
95        title: "Filter after aggregating",
96        shape: "… GROUP BY … HAVING <condition>",
97        doc: "Keep only the results that match. An aggregate in the condition names the column it is selected as.",
98        example: "INSERT INTO out SELECT auction, count(*) AS bids FROM bids GROUP BY auction, TUMBLE(dateTime, INTERVAL '10' SECOND) HAVING count(*) > 5",
99        steps: "windowedAggregate, filter",
100    },
101    Form {
102        title: "Distinct counts",
103        shape: "count(DISTINCT <column>) · count(DISTINCT CASE WHEN … THEN <column> END)",
104        doc: "The number of distinct values per window and group; a `CASE` argument counts only the rows it keeps.",
105        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)",
106        steps: "applyExpression, windowedAggregate",
107    },
108    Form {
109        title: "Computed columns after aggregating",
110        shape: "SELECT <expression over windowStart, keys or aggregates> AS <name> … GROUP BY …",
111        doc: "A selected expression over the window's columns, keys or aggregates is computed on each result.",
112        example: "INSERT INTO out SELECT count(*) AS bids, substring(epochToIso(windowStart), 1, 10) AS day FROM bids GROUP BY TUMBLE(dateTime, INTERVAL '1' DAY)",
113        steps: "windowedAggregate, applyExpression",
114    },
115    Form {
116        title: "Interval join",
117        shape: "FROM <a> JOIN <b> ON <a>.<key> = <b>.<key> AND <a>.<time> BETWEEN <b>.<time> - INTERVAL … AND <b>.<time> + INTERVAL …",
118        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.",
119        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",
120        steps: "streamJoin",
121    },
122    Form {
123        title: "Lookup join",
124        shape: "FROM <stream> JOIN <table> ON <stream>.<key> = <table>.<key>",
125        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.",
126        example: "INSERT INTO out SELECT bids.auction, categories.name FROM bids JOIN categories ON bids.category = categories.category",
127        steps: "lookupJoin, applyExpression, select",
128    },
129    Form {
130        title: "Top-N",
131        shape: "FROM (SELECT …, ROW_NUMBER() OVER (PARTITION BY <keys> ORDER BY <column> [DESC], [<tie-break>]) AS rn FROM <source>) WHERE rn <= <n>",
132        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.",
133        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",
134        steps: "topN",
135    },
136    Form {
137        title: "Several stages",
138        shape: "INSERT INTO <sink> WITH <name> AS (<query>), … <query>",
139        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]]`.",
140        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",
141        steps: "windowedAggregate; topN, select",
142    },
143];
144
145/// Every expression translation, in the order the reference lists them.
146pub const EXPRESSIONS: &[Translation] = &[
147    Translation {
148        sql: "a = b",
149        dialect: "(a == b)",
150    },
151    Translation {
152        sql: "a <> b",
153        dialect: "(a != b)",
154    },
155    Translation {
156        sql: "a > 1 AND b < 2",
157        dialect: "((a > 1) && (b < 2))",
158    },
159    Translation {
160        sql: "a = 1 OR b = 2",
161        dialect: "((a == 1) || (b == 2))",
162    },
163    Translation {
164        sql: "NOT a",
165        dialect: "!(a)",
166    },
167    Translation {
168        sql: "a || b",
169        dialect: "(a + b)",
170    },
171    Translation {
172        sql: "price % 10",
173        dialect: "(price % 10)",
174    },
175    Translation {
176        sql: "CASE WHEN price < 100 THEN 'low' ELSE 'high' END",
177        dialect: "((price < 100) ? 'low' : 'high')",
178    },
179    Translation {
180        sql: "a IS NULL",
181        dialect: "(a == null)",
182    },
183    Translation {
184        sql: "a IS NOT NULL",
185        dialect: "(a != null)",
186    },
187    Translation {
188        sql: "a IN (1, 2)",
189        dialect: "(a in [1, 2])",
190    },
191    Translation {
192        sql: "a BETWEEN 1 AND 5",
193        dialect: "((a >= 1) && (a <= 5))",
194    },
195    Translation {
196        sql: "name LIKE 'a%b_'",
197        dialect: "regexMatch(name, '^a.*b.$')",
198    },
199    Translation {
200        sql: "SUBSTRING(name, 1, 3)",
201        dialect: "substring(name, 1, 3)",
202    },
203    Translation {
204        sql: "round(price * 0.9)",
205        dialect: "round((price * 0.9))",
206    },
207];
208
209/// Every refused form, in the order the reference lists them.
210pub const REFUSED: &[Refused] = &[
211    Refused {
212        example: "SELECT * FROM bids",
213        says: "INSERT INTO",
214    },
215    Refused {
216        example: "INSERT INTO out SELECT auction, count(*) AS n FROM bids GROUP BY auction",
217        says: "needs a window",
218    },
219    Refused {
220        example: "INSERT INTO out SELECT * FROM bids ORDER BY price",
221        says: "ORDER BY over an unbounded stream",
222    },
223    Refused {
224        example: "INSERT INTO out SELECT * FROM bids LIMIT 10",
225        says: "LIMIT over an unbounded stream",
226    },
227    Refused {
228        example: "INSERT INTO out SELECT * FROM a UNION SELECT * FROM b",
229        says: "UNION / INTERSECT / EXCEPT",
230    },
231    Refused {
232        example: "INSERT INTO out SELECT * FROM a, b",
233        says: "join",
234    },
235    Refused {
236        example: "INSERT INTO out SELECT * FROM a JOIN b ON a.x = b.y",
237        says: "share a name",
238    },
239    Refused {
240        example: "INSERT INTO out SELECT *, auction FROM bids",
241        says: "already keeps every column",
242    },
243    Refused {
244        example: "INSERT INTO out SELECT * FROM bids HAVING price > 1",
245        says: "use WHERE",
246    },
247    Refused {
248        example: "INSERT INTO out SELECT * FROM bids EMIT CHANGES EVERY 1 ROWS",
249        says: "EMIT",
250    },
251    Refused {
252        example: "INSERT INTO out WITH RECURSIVE r AS (SELECT * FROM bids) SELECT * FROM r",
253        says: "WITH RECURSIVE",
254    },
255];
256
257/// The steps `sql` compiles to, stage by stage (`;` between stages).
258pub fn steps_of(sql: &str) -> Result<String, String> {
259    let topo = compile(sql)?;
260    Ok(topo
261        .stages
262        .iter()
263        .map(|s| {
264            s.steps
265                .iter()
266                .map(|step| step["op"].as_str().unwrap_or("?").to_string())
267                .collect::<Vec<_>>()
268                .join(", ")
269        })
270        .collect::<Vec<_>>()
271        .join("; "))
272}
273
274/// The expression-dialect text `sql` (a scalar expression) becomes: the computed column of
275/// `INSERT INTO o SELECT <sql> AS x FROM s`.
276pub fn dialect_of(sql: &str) -> Result<String, String> {
277    let topo = compile(&format!("INSERT INTO o SELECT {sql} AS x FROM s"))?;
278    topo.stages
279        .iter()
280        .flat_map(|s| s.steps.iter())
281        .find(|step| step["op"] == "applyExpression" && step["column"] == "x")
282        .and_then(|step| step["expression"].as_str().map(String::from))
283        .ok_or_else(|| format!("`{sql}` compiled to no computed column"))
284}
285
286/// The steps as links into the steps reference (`windowedAggregate` → its section).
287fn linked(steps: &str) -> String {
288    steps
289        .split("; ")
290        .map(|stage| {
291            stage
292                .split(", ")
293                .map(|op| format!("[`{op}`](steps.md#{})", op.to_lowercase()))
294                .collect::<Vec<_>>()
295                .join(" → ")
296        })
297        .collect::<Vec<_>>()
298        .join(" ; then ")
299}
300
301fn anchor(title: &str) -> String {
302    title
303        .to_lowercase()
304        .chars()
305        .filter_map(|c| match c {
306            'a'..='z' | '0'..='9' | '-' => Some(c),
307            ' ' => Some('-'),
308            _ => None,
309        })
310        .collect()
311}
312
313/// `|` inside a table cell.
314fn cell(s: &str) -> String {
315    s.replace('|', "\\|")
316}
317
318/// The page. Each form's steps and each refusal's message come from compiling its example.
319pub fn render_markdown() -> String {
320    let mut md = String::new();
321    md.push_str("# SQL\n\n");
322    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");
323    md.push_str("A pipeline file's `sql = \"INSERT INTO <sink> SELECT …\"` takes the place of its `[[transforms]]`. ");
324    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]`. ");
325    md.push_str("The statement compiles to the same [steps](steps.md) a hand-written pipeline runs: one runtime, two front doors. ");
326    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");
327    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");
328    md.push_str("## Forms\n\n| form | shape | becomes |\n|---|---|---|\n");
329    for f in FORMS {
330        let steps = steps_of(f.example).unwrap_or_else(|e| format!("(does not compile: {e})"));
331        md.push_str(&format!(
332            "| [{}](#{}) | `{}` | {} |\n",
333            f.title,
334            anchor(f.title),
335            cell(f.shape),
336            linked(&steps)
337        ));
338    }
339    for f in FORMS {
340        let steps = steps_of(f.example).unwrap_or_else(|e| format!("(does not compile: {e})"));
341        md.push_str(&format!(
342            "\n### {}\n\n{}\n\n```sql\n{}\n```\n\n",
343            f.title, f.doc, f.example
344        ));
345        md.push_str(&format!("**Becomes:** {}\n", linked(&steps)));
346    }
347    md.push_str("\n## Expressions\n\n");
348    md.push_str(
349        "Scalar expressions translate into the expression dialect the `filter` and `applyExpression` steps run. ",
350    );
351    md.push_str("A function keeps the name and case it is written with (the dialect's names are case-sensitive: `epochToIso`, `regexMatch`). ");
352    md.push_str("`LIKE` takes a string-literal pattern; `ILIKE` matches regardless of case.\n\n");
353    md.push_str("| SQL | expression dialect |\n|---|---|\n");
354    for t in EXPRESSIONS {
355        md.push_str(&format!("| `{}` | `{}` |\n", cell(t.sql), cell(t.dialect)));
356    }
357    md.push_str("\n## Refused\n\nEach of these is refused when the pipeline starts, with this message:\n\n| statement | message |\n|---|---|\n");
358    for r in REFUSED {
359        let says = compile(r.example).err().unwrap_or_else(|| "(compiles)".into());
360        md.push_str(&format!("| `{}` | {} |\n", cell(r.example), cell(&says)));
361    }
362    md
363}
364
365#[cfg(test)]
366mod tests {
367    use super::*;
368
369    fn doc_path() -> std::path::PathBuf {
370        std::path::Path::new(env!("CARGO_MANIFEST_DIR")).join("../../docs/reference/sql.md")
371    }
372
373    #[test]
374    fn the_sql_reference_is_current() {
375        let rendered = render_markdown();
376        if std::env::var_os("FV_WRITE_REFERENCE").is_some() {
377            std::fs::write(doc_path(), &rendered).unwrap();
378        }
379        let on_disk = std::fs::read_to_string(doc_path()).unwrap_or_default();
380        assert!(
381            on_disk == rendered,
382            "docs/reference/sql.md is out of date with the code; run `make reference`"
383        );
384    }
385
386    #[test]
387    fn every_form_compiles_to_the_steps_it_promises() {
388        let wrong: Vec<String> = FORMS
389            .iter()
390            .filter_map(|f| match steps_of(f.example) {
391                Ok(got) if got == f.steps => None,
392                got => Some(format!("{}: promises `{}`, compiles to {got:?}", f.title, f.steps)),
393            })
394            .collect();
395        assert!(wrong.is_empty(), "\n{}", wrong.join("\n"));
396    }
397
398    #[test]
399    fn every_expression_translates_as_listed() {
400        for t in EXPRESSIONS {
401            assert_eq!(
402                dialect_of(t.sql).unwrap_or_else(|e| panic!("{}: {e}", t.sql)),
403                t.dialect,
404                "{}",
405                t.sql
406            );
407        }
408    }
409
410    #[test]
411    fn every_refused_statement_is_refused_with_its_reason() {
412        let wrong: Vec<String> = REFUSED
413            .iter()
414            .filter_map(|r| match compile(r.example) {
415                Err(e) if e.contains(r.says) => None,
416                Err(e) => Some(format!("`{}`: refused, but not with `{}`: {e}", r.example, r.says)),
417                Ok(_) => Some(format!("`{}` compiles", r.example)),
418            })
419            .collect();
420        assert!(wrong.is_empty(), "\n{}", wrong.join("\n"));
421    }
422}