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, 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
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}