1use crate::compile;
8
9#[derive(Debug)]
11pub struct Form {
12 pub title: &'static str,
13 pub shape: &'static str,
15 pub doc: &'static str,
16 pub example: &'static str,
18 pub steps: &'static str,
20}
21
22#[derive(Debug)]
24pub struct Translation {
25 pub sql: &'static str,
26 pub dialect: &'static str,
27}
28
29#[derive(Debug)]
31pub struct Refused {
32 pub example: &'static str,
33 pub says: &'static str,
34}
35
36pub 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
145pub 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
209pub 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
257pub 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
274pub 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
286fn 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
313fn cell(s: &str) -> String {
315 s.replace('|', "\\|")
316}
317
318pub 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}