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