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: `HOP(dateTime, INTERVAL '2' SECOND, INTERVAL '10' SECOND)` starts a 10-second window every 2 seconds.",
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, which is how out of order rows may arrive and still count. It is the step's `allowedLatenessMs`, also called 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. 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.",
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}