Skip to main content

h5i_app_pgsql/
read.rs

1//! SQL for the declarative reads of `h5i_app_sql` (`Read`, `Pred`).
2//!
3//! Not extracted to Lean: `compile_read` is checked by differential tests
4//! (`crates/h5i-app-pg/src/reads_test.rs`) against `h5i_app_sql::eval`, which
5//! mirrors `H5iAppLib.Query`. The checks below are what makes the SQL mean
6//! what the Lean says: membership only between `NOT NULL` columns, windows
7//! only on tables whose key columns are all integers or booleans (so the key
8//! order is not a collation's), range comparisons (`>=`, `<`) only between an
9//! integer column and an integer (signed `BIGINT` order, as the Lean compares),
10//! and the result always sorted by key ascending.
11//! In opt-in ScalarWhenUnique mode, a lookup fixed by its entire PK uses `=`;
12//! its empty-result UNKNOWN selects the same rows as FALSE in this positive
13//! AND/OR language. Adding negation would require revisiting that rewrite.
14
15use super::{has_kind, push_lit, push_name, tenant_col, valid, Kind, Table};
16use h5i_app_sql::{Pred, Read, Val, Window};
17
18/// A read's text and its parameters, `$1` (the tenant) first. Every value,
19/// limit and offset is a parameter, so the text depends only on the read's
20/// shape and on whether its window has a limit and a non-zero offset: at
21/// most four texts per shape, whatever the numbers. `OFFSET 0` is left out
22/// on purpose: with an `OFFSET` parameter PostgreSQL estimates a generic plan
23/// as dearer than the custom ones and never reuses it, re-planning on every
24/// execution of the prepared statement.
25#[derive(Clone, Debug, PartialEq, Eq)]
26pub struct ReadQuery {
27    pub text: Vec<u8>,
28    pub params: Vec<Val>,
29}
30
31/// SQL spelling for membership predicates. Both modes preserve the same read
32/// and exact-count semantics; their PostgreSQL execution plans can differ.
33#[derive(Clone, Copy, Debug, Default, PartialEq, Eq)]
34pub enum ReadSqlMode {
35    /// Always use `IN (SELECT ...)`, retaining the original renderer behavior.
36    #[default]
37    Membership,
38    /// Use scalar `= (SELECT ...)` when all primary-key columns are fixed.
39    /// Other lookups retain `IN`. This is workload-dependent, not always faster.
40    ScalarWhenUnique,
41}
42
43struct Out<'a> {
44    tables: &'a Vec<Table>,
45    mode: ReadSqlMode,
46    text: Vec<u8>,
47    params: Vec<Val>,
48}
49
50fn windowed(w: &Window) -> bool {
51    w.offset > 0 || w.limit.is_some()
52}
53
54/// A column that never holds `NULL`: a key column or a non-nullable one.
55fn not_null(t: &Table, i: usize) -> bool {
56    i < t.key_len as usize || !t.columns[i].nullable
57}
58
59/// A conjunction fixes this key column to one value. Disjunctions and
60/// membership predicates are deliberately not used to infer uniqueness.
61fn fixes_column(p: &Pred, key: u32) -> bool {
62    match p {
63        Pred::Eq { col, .. } => *col == key,
64        Pred::And(a, b) => fixes_column(a, key) || fixes_column(b, key),
65        _ => false,
66    }
67}
68
69/// Every SELECT is already restricted to one tenant. Fixing the remaining
70/// primary-key columns therefore leaves at most one row; a window cannot
71/// increase that cardinality. With no key columns the tenant itself is the PK.
72fn at_most_one(t: &Table, p: &Pred) -> bool {
73    (0..t.key_len).all(|key| fixes_column(p, key))
74}
75
76fn ordered_key(t: &Table) -> bool {
77    t.key_len > 0
78        && t.columns[..t.key_len as usize].iter().all(|c| matches!(c.kind, Kind::Int | Kind::Bool))
79}
80
81fn int_param(n: u64) -> Val {
82    Val::Int(n.min(i64::MAX as u64) as i64)
83}
84
85impl Out<'_> {
86    fn lit(&mut self, s: &str) {
87        push_lit(&mut self.text, s.as_bytes());
88    }
89
90    fn param(&mut self, v: Val) {
91        self.params.push(v);
92        let n = self.params.len();
93        self.lit(&format!("${n}"));
94    }
95
96    fn col(&mut self, alias: usize, name: &Vec<u8>) {
97        self.lit(&format!("q{alias}."));
98        push_name(&mut self.text, name);
99    }
100
101    /// `SELECT <cols> FROM "t" AS q<alias> WHERE tenant AND <pred> [ORDER BY key [LIMIT/OFFSET]]`.
102    fn select(&mut self, table: u32, pred: &Pred, w: &Window, cols: &[usize], alias: usize, order: bool) -> Option<()> {
103        let t = self.tables.get(table as usize)?;
104        if windowed(w) && !ordered_key(t) {
105            return None;
106        }
107        self.lit("SELECT ");
108        for (n, &i) in cols.iter().enumerate() {
109            if n > 0 {
110                self.lit(", ");
111            }
112            self.col(alias, &t.columns.get(i)?.name.clone());
113        }
114        self.lit(" FROM ");
115        push_name(&mut self.text, &t.name);
116        self.lit(&format!(" AS q{alias} WHERE "));
117        self.col(alias, &tenant_col());
118        self.lit(" = $1 AND ");
119        self.pred(t, pred, alias)?;
120        if order || windowed(w) {
121            self.order_by(t, alias, w.desc && windowed(w));
122        }
123        if windowed(w) {
124            if let Some(n) = w.limit {
125                self.lit(" LIMIT ");
126                self.param(int_param(n));
127            }
128            if w.offset > 0 {
129                self.lit(" OFFSET ");
130                self.param(int_param(w.offset));
131            }
132        }
133        Some(())
134    }
135
136    fn order_by(&mut self, t: &Table, alias: usize, desc: bool) {
137        self.lit(" ORDER BY ");
138        for (i, c) in t.columns.iter().take(t.key_len as usize).enumerate() {
139            if i > 0 {
140                self.lit(", ");
141            }
142            self.col(alias, &c.name.clone());
143            if desc {
144                self.lit(" DESC");
145            }
146        }
147    }
148
149    fn pred(&mut self, t: &Table, p: &Pred, alias: usize) -> Option<()> {
150        match p {
151            Pred::All => self.lit("TRUE"),
152            Pred::Eq { col, val } => {
153                let c = t.columns.get(*col as usize)?;
154                if !has_kind(val, c.kind) {
155                    return None;
156                }
157                self.col(alias, &c.name.clone());
158                // The same on a column that is never NULL, and `=` can use an index.
159                self.lit(if not_null(t, *col as usize) { " = " } else { " IS NOT DISTINCT FROM " });
160                self.param(val.clone());
161            }
162            Pred::Ge { col, val } | Pred::Lt { col, val } => {
163                // Integers only: `BIGINT` order is the signed order the Lean
164                // compares in; text and bytes would bring collations in.
165                let c = t.columns.get(*col as usize)?;
166                if c.kind != Kind::Int || !matches!(val, Val::Int(_)) {
167                    return None;
168                }
169                self.col(alias, &c.name.clone());
170                // `NULL >= x` is UNKNOWN, which `WHERE` drops: false, as in the Lean.
171                self.lit(if matches!(p, Pred::Ge { .. }) { " >= " } else { " < " });
172                self.param(val.clone());
173            }
174            Pred::In { col, table, pred, of, window } => {
175                let i = *col as usize;
176                let c = t.columns.get(i)?;
177                let sub = self.tables.get(*table as usize)?;
178                let j = *of as usize;
179                let d = sub.columns.get(j)?;
180                if c.kind != d.kind || !not_null(t, i) || !not_null(sub, j) {
181                    return None;
182                }
183                self.col(alias, &c.name.clone());
184                // An uncorrelated scalar lookup is evaluated once (an InitPlan)
185                // instead of joining every outer row to the lookup table. Only
186                // use it when the schema's PK proves there cannot be two rows.
187                // Both columns are NOT NULL. On an empty subquery `=` yields
188                // UNKNOWN rather than IN's FALSE; these are interchangeable in
189                // this positive AND/OR predicate language's WHERE selection.
190                self.lit(if self.mode == ReadSqlMode::ScalarWhenUnique && at_most_one(sub, pred) { " = (" } else { " IN (" });
191                self.select(*table, pred, window, &[j], alias + 1, false)?;
192                self.lit(")");
193            }
194            Pred::And(a, b) | Pred::Or(a, b) => {
195                let op = if matches!(p, Pred::And(..)) { " AND " } else { " OR " };
196                self.lit("(");
197                self.pred(t, a, alias)?;
198                self.lit(op);
199                self.pred(t, b, alias)?;
200                self.lit(")");
201            }
202        }
203        Some(())
204    }
205}
206
207/// The `SELECT` returning `q`'s rows of `tenant` (no `tenant_id`) in
208/// ascending key order, or with `count` the number of them. `None` on an
209/// invalid schema, a missing table or column, a value of the wrong type, `IN`
210/// on a nullable column, a range comparison on a column or with a value that
211/// is not an integer, or a window on a table without an integer key.
212pub fn compile_read(tables: &Vec<Table>, tenant: i64, q: &Read, count: bool) -> Option<ReadQuery> {
213    compile_read_with_mode(tables, tenant, q, count, ReadSqlMode::default())
214}
215
216/// Compile a read or exact count with an explicit SQL strategy. The mode
217/// applies recursively to nested membership predicates; validation is identical.
218pub fn compile_read_with_mode(tables: &Vec<Table>, tenant: i64, q: &Read, count: bool, mode: ReadSqlMode) -> Option<ReadQuery> {
219    if !valid(tables) {
220        return None;
221    }
222    let t = tables.get(q.table as usize)?;
223    let all: Vec<usize> = (0..t.columns.len()).collect();
224    let mut out = Out { tables, mode, text: Vec::new(), params: vec![Val::Int(tenant)] };
225    let desc = q.window.desc && windowed(&q.window);
226    if count {
227        out.lit("SELECT count(*) FROM (");
228        out.select(q.table, &q.pred, &q.window, &all, 0, false)?;
229        out.lit(") AS c");
230    } else if desc {
231        // The window counts from the newest key; the rows come back oldest first.
232        out.lit("SELECT * FROM (");
233        out.select(q.table, &q.pred, &q.window, &all, 0, false)?;
234        out.lit(") AS w ORDER BY ");
235        for (i, c) in t.columns.iter().take(t.key_len as usize).enumerate() {
236            if i > 0 {
237                out.lit(", ");
238            }
239            push_name(&mut out.text, &c.name);
240        }
241    } else {
242        out.select(q.table, &q.pred, &q.window, &all, 0, t.key_len > 0)?;
243    }
244    Some(ReadQuery { text: out.text, params: out.params })
245}
246
247/// `CREATE INDEX IF NOT EXISTS` on `tenant_id` and columns `cols` of table
248/// `table`, so reads that filter on them need not scan the tenant. Indexes
249/// change no result, only speed. `None` on a bad schema, table or column.
250pub fn create_index(tables: &Vec<Table>, table: u32, cols: &[u32]) -> Option<Vec<u8>> {
251    if !valid(tables) || cols.is_empty() {
252        return None;
253    }
254    let t = tables.get(table as usize)?;
255    let mut names = vec![tenant_col()];
256    for &c in cols {
257        names.push(t.columns.get(c as usize)?.name.clone());
258    }
259    // A name PostgreSQL keeps whole: the table's number and the columns'.
260    let mut idx = format!("h5i_idx_{table}");
261    for &c in cols {
262        idx.push_str(&format!("_{c}"));
263    }
264    let mut out = Vec::new();
265    push_lit(&mut out, b"CREATE INDEX IF NOT EXISTS ");
266    push_name(&mut out, &[&t.name[..], b"_", idx.as_bytes()].concat().iter().take(63).copied().collect());
267    push_lit(&mut out, b" ON ");
268    push_name(&mut out, &t.name);
269    push_lit(&mut out, b" (");
270    for (i, n) in names.iter().enumerate() {
271        if i > 0 {
272            push_lit(&mut out, b", ");
273        }
274        push_name(&mut out, n);
275    }
276    push_lit(&mut out, b")");
277    Some(out)
278}
279
280#[cfg(test)]
281mod tests {
282    use super::*;
283    use crate::Column;
284    use h5i_app_sql::whole;
285
286    fn col(name: &str, kind: Kind, nullable: bool) -> Column {
287        Column { name: name.as_bytes().to_vec(), kind, nullable }
288    }
289
290    fn schema() -> Vec<Table> {
291        vec![
292            Table {
293                name: b"articles".to_vec(),
294                columns: vec![col("id", Kind::Int, false), col("author", Kind::Int, false), col("slug", Kind::Bytes, false)],
295                key_len: 1,
296            },
297            Table { name: b"tags".to_vec(), columns: vec![col("article", Kind::Int, false), col("tag", Kind::Bytes, false)], key_len: 2 },
298            Table { name: b"notes".to_vec(), columns: vec![col("tag", Kind::Bytes, false), col("n", Kind::Int, true)], key_len: 1 },
299        ]
300    }
301
302    fn text(q: &ReadQuery) -> String {
303        String::from_utf8(q.text.clone()).unwrap()
304    }
305
306    #[test]
307    fn renders_a_page_of_a_tag() {
308        let q = Read {
309            table: 0,
310            pred: Pred::In {
311                col: 0,
312                table: 1,
313                pred: Box::new(Pred::Eq { col: 1, val: Val::Bytes(b"rust".to_vec()) }),
314                of: 0,
315                window: whole(),
316            },
317            window: Window { desc: true, offset: 0, limit: Some(20) },
318        };
319        let c = compile_read(&schema(), 7, &q, false).unwrap();
320        assert_eq!(
321            text(&c),
322            "SELECT * FROM (SELECT q0.\"id\", q0.\"author\", q0.\"slug\" FROM \"articles\" AS q0 WHERE q0.\"tenant_id\" = $1 \
323             AND q0.\"id\" IN (SELECT q1.\"article\" FROM \"tags\" AS q1 WHERE q1.\"tenant_id\" = $1 AND \
324             q1.\"tag\" = $2) ORDER BY q0.\"id\" DESC LIMIT $3) AS w ORDER BY \"id\""
325        );
326        assert_eq!(c.params, vec![Val::Int(7), Val::Bytes(b"rust".to_vec()), Val::Int(20)]);
327        // Another page size, same text: one prepared statement serves both.
328        let mut other = q.clone();
329        other.window.limit = Some(50);
330        assert_eq!(compile_read(&schema(), 9, &other, false).unwrap().text, c.text);
331        let n = compile_read(&schema(), 7, &q, true).unwrap();
332        assert!(text(&n).starts_with("SELECT count(*) FROM (SELECT q0."));
333    }
334
335    #[test]
336    fn scalar_membership_requires_the_entire_primary_key() {
337        let eq = |col| Pred::Eq { col, val: Val::Int(1) };
338        let s = schema();
339        assert!(at_most_one(&s[0], &eq(0)));
340        assert!(!at_most_one(&s[0], &eq(1)));
341        assert!(!at_most_one(&s[1], &eq(0)));
342        assert!(at_most_one(&s[1], &Pred::And(Box::new(eq(0)), Box::new(eq(1)))));
343        assert!(!at_most_one(&s[0], &Pred::Or(Box::new(eq(0)), Box::new(eq(0)))));
344        let singleton = Table { name: b"counter".to_vec(), columns: vec![col("value", Kind::Int, false)], key_len: 0 };
345        assert!(at_most_one(&singleton, &Pred::All));
346    }
347
348    #[test]
349    fn modes_preserve_parameters_and_default_to_membership() {
350        let q = Read { table: 0, pred: Pred::In {
351            col: 1, table: 0, pred: Box::new(Pred::Eq { col: 0, val: Val::Int(1) }),
352            of: 1, window: whole(),
353        }, window: whole() };
354        assert_eq!(ReadSqlMode::default(), ReadSqlMode::Membership);
355        for count in [false, true] {
356            let old = compile_read(&schema(), 7, &q, count).unwrap();
357            let member = compile_read_with_mode(&schema(), 7, &q, count, ReadSqlMode::Membership).unwrap();
358            let scalar = compile_read_with_mode(&schema(), 7, &q, count, ReadSqlMode::ScalarWhenUnique).unwrap();
359            assert_eq!(old, member);
360            assert!(text(&member).contains(" IN (SELECT"));
361            assert_eq!(text(&member).replace(" IN (SELECT", " = (SELECT"), text(&scalar));
362            assert_eq!(member.params, scalar.params);
363        }
364        // A nonunique lookup must keep IN even in scalar mode.
365        let mut multi = q.clone();
366        if let Pred::In { pred, .. } = &mut multi.pred { **pred = Pred::All; }
367        assert_eq!(compile_read(&schema(), 7, &multi, false),
368            compile_read_with_mode(&schema(), 7, &multi, false, ReadSqlMode::ScalarWhenUnique));
369    }
370
371    #[test]
372    fn rejects_what_the_lean_does_not_mean() {
373        let s = schema();
374        // A window over a text key.
375        let w = Read { table: 2, pred: Pred::All, window: Window { desc: false, offset: 1, limit: None } };
376        assert!(compile_read(&s, 1, &w, false).is_none());
377        // IN on a nullable column.
378        let n = Read {
379            table: 0,
380            pred: Pred::In { col: 1, table: 2, pred: Box::new(Pred::All), of: 1, window: whole() },
381            window: whole(),
382        };
383        assert!(compile_read(&s, 1, &n, false).is_none());
384        // A value of the wrong type, a missing column.
385        assert!(compile_read(&s, 1, &h5i_app_sql::read_eq(0, 2, Val::Int(1)), false).is_none());
386        assert!(compile_read(&s, 1, &h5i_app_sql::read_eq(0, 9, Val::Int(1)), false).is_none());
387        assert!(compile_read(&s, 1, &h5i_app_sql::read_all(0), false).is_some());
388        // A range comparison on bytes, with NULL or bytes, or on a missing column.
389        let range = |table, col, val| Read { table, pred: Pred::Ge { col, val }, window: whole() };
390        assert!(compile_read(&s, 1, &range(0, 2, Val::Bytes(b"a".to_vec())), false).is_none());
391        assert!(compile_read(&s, 1, &range(0, 0, Val::Null), false).is_none());
392        assert!(compile_read(&s, 1, &range(0, 0, Val::Bytes(b"a".to_vec())), false).is_none());
393        assert!(compile_read(&s, 1, &range(0, 9, Val::Int(1)), false).is_none());
394        let lt = Read { table: 2, pred: Pred::Lt { col: 0, val: Val::Int(1) }, window: whole() };
395        assert!(compile_read(&s, 1, &lt, false).is_none());
396    }
397
398    #[test]
399    fn renders_an_id_range() {
400        let q = Read {
401            table: 0,
402            pred: Pred::And(Box::new(Pred::Ge { col: 0, val: Val::Int(64) }), Box::new(Pred::Lt { col: 0, val: Val::Int(128) })),
403            window: whole(),
404        };
405        let c = compile_read(&schema(), 7, &q, false).unwrap();
406        assert_eq!(
407            text(&c),
408            "SELECT q0.\"id\", q0.\"author\", q0.\"slug\" FROM \"articles\" AS q0 WHERE q0.\"tenant_id\" = $1 \
409             AND (q0.\"id\" >= $2 AND q0.\"id\" < $3) ORDER BY q0.\"id\""
410        );
411        assert_eq!(c.params, vec![Val::Int(7), Val::Int(64), Val::Int(128)]);
412        // A nullable integer column.
413        let n = Read { table: 2, pred: Pred::Lt { col: 1, val: Val::Int(-3) }, window: whole() };
414        assert!(text(&compile_read(&schema(), 7, &n, false).unwrap()).contains("q0.\"n\" < $2"));
415    }
416
417    #[test]
418    fn renders_an_index() {
419        let i = create_index(&schema(), 0, &[2]).unwrap();
420        assert_eq!(
421            String::from_utf8(i).unwrap(),
422            "CREATE INDEX IF NOT EXISTS \"articles_h5i_idx_0_2\" ON \"articles\" (\"tenant_id\", \"slug\")"
423        );
424        assert!(create_index(&schema(), 0, &[9]).is_none());
425    }
426}