use crate::compile;
#[derive(Debug)]
pub struct Form {
pub title: &'static str,
pub shape: &'static str,
pub doc: &'static str,
pub example: &'static str,
pub steps: &'static str,
}
#[derive(Debug)]
pub struct Translation {
pub sql: &'static str,
pub dialect: &'static str,
}
#[derive(Debug)]
pub struct Refused {
pub example: &'static str,
pub says: &'static str,
}
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, as in Flink's `HOP`; Spark's `window(time, size, slide)` takes them the other way round.",
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 (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.",
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.",
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",
},
];
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))",
},
];
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",
},
];
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("; "))
}
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"))
}
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()
}
fn cell(s: &str) -> String {
s.replace('|', "\\|")
}
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"));
}
}