Skip to main content

wabot_feature_sqlite/
query_sql.rs

1//! Turning a [`Query`] into SQLite SQL.
2//!
3//! Pure and database-free, so the interesting cases are unit tests
4//! rather than integration tests. Deliberately a sibling of
5//! `wabot-feature-pg`'s `query_sql`, not a shared abstraction — see
6//! the note at the bottom of this file for why, and when that should
7//! change.
8//!
9//! ## Four things SQLite does differently
10//!
11//! Each one is a silently-wrong result if you assume the Postgres
12//! spelling works:
13//!
14//! 1. **Placeholders are `?n`.** `$n` parses in SQLite but means a
15//!    *named* parameter, which rusqlite's positional binding will not
16//!    fill.
17//! 2. **`= ANY(?)` does not exist.** A membership test expands to one
18//!    placeholder per element, so `In` consumes N of them and the
19//!    caller has to flatten the array to match.
20//! 3. **`->>` preserves types.** Postgres's `->>` always yields text,
21//!    so a numeric comparison there needs `::numeric` or `"9" > "10"`
22//!    comes out true. SQLite returns an INTEGER for an integer, so the
23//!    cast is not merely unnecessary — `::numeric` is not SQLite
24//!    syntax at all.
25//! 4. **A JSON path is explicit.** `data->>'$."field"'`, with the
26//!    label quoted, so a field name containing a `.` looks up that key
27//!    instead of traversing into a nested object.
28//!
29//! ## Values never enter the string
30//!
31//! Every value becomes a `?n` placeholder. A field *name* does become
32//! part of the statement, so it is validated against the columns the
33//! store declared — an unknown one is an error, not an escaped
34//! identifier.
35
36use std::collections::HashSet;
37
38use wabot_core::repository::query::{Direction, Query, QueryOperator};
39
40/// Which fields are real columns.
41#[derive(Debug, Clone)]
42pub enum FieldPlacement {
43    /// Document storage: these fields are promoted columns, the rest
44    /// live in the JSON blob.
45    Promoted(HashSet<String>),
46    /// Columnar storage: every field is a column, and there is no blob
47    /// to fall back to.
48    All,
49}
50
51impl FieldPlacement {
52    pub fn promoted<S: Into<String>>(fields: impl IntoIterator<Item = S>) -> Self {
53        Self::Promoted(fields.into_iter().map(Into::into).collect())
54    }
55
56    pub fn none() -> Self {
57        Self::Promoted(HashSet::new())
58    }
59
60    fn is_column(&self, field: &str) -> bool {
61        matches!(field, "id" | "created_at" | "createdAt")
62            || match self {
63                FieldPlacement::All => true,
64                FieldPlacement::Promoted(fields) => fields.contains(field),
65            }
66    }
67}
68
69#[derive(Debug, thiserror::Error, PartialEq)]
70pub enum QuerySqlError {
71    /// A field name reaches the statement as an identifier, so an
72    /// unrecognised one is refused rather than quoted and hoped for.
73    #[error("unknown field {field:?}: the columnar store has no such column")]
74    UnknownField { field: String },
75    #[error("{operator:?} on {field:?} needs a value")]
76    MissingValue { field: String, operator: String },
77    /// `In` / `NotIn` need a list to expand into placeholders.
78    #[error("{operator:?} on {field:?} needs an array of values")]
79    NotAList { field: String, operator: String },
80}
81
82/// A rendered `WHERE`/`ORDER BY`/`LIMIT` and how many placeholders it
83/// used.
84#[derive(Debug, Clone, PartialEq)]
85pub struct BuiltQuery {
86    /// Everything after the table: `WHERE … ORDER BY … LIMIT …`.
87    /// Empty when the query selects everything.
88    pub sql: String,
89    /// Total `?n` slots. Equal to the number of *values* to bind,
90    /// which is not the number of conditions once `In` is involved.
91    pub placeholders: usize,
92}
93
94/// `id` / `createdAt` are what a caller writes; `created_at` is the
95/// column.
96fn column_for(field: &str) -> &str {
97    match field {
98        "createdAt" => "created_at",
99        other => other,
100    }
101}
102
103fn quote_ident(name: &str) -> String {
104    format!("\"{}\"", name.replace('"', "\"\""))
105}
106
107/// A JSON path with the label quoted, so `a.b` is one key rather than
108/// a traversal into `b`.
109///
110/// Two layers of escaping, which is why this is its own function: the
111/// backslash-escape belongs to the JSON path grammar, and the doubled
112/// single quote belongs to the surrounding SQL string literal.
113fn json_path(field: &str) -> String {
114    let label = field.replace('\\', "\\\\").replace('"', "\\\"");
115    format!("'$.\"{}\"'", label.replace('\'', "''"))
116}
117
118/// The SQL for one field.
119///
120/// Public to the crate so an *index* can be built on the very same
121/// expression a query filters on — an index on anything else is one
122/// the planner will not use.
123pub fn field_expression(field: &str, placement: &FieldPlacement) -> String {
124    if placement.is_column(field) {
125        quote_ident(column_for(field))
126    } else {
127        // No cast: unlike Postgres, SQLite's `->>` gives back an
128        // INTEGER for an integer and a REAL for a float, so ordering
129        // comparisons are already numeric.
130        format!("\"data\"->>{}", json_path(field))
131    }
132}
133
134/// Flatten a query's values in the order the rendered placeholders
135/// expect them.
136///
137/// Not the same as [`Query::bind_values`]: an `In` condition holds one
138/// array but renders N placeholders, so the array has to be unpacked
139/// here or every value after it binds to the wrong slot.
140pub fn bind_values(query: &Query) -> Result<Vec<serde_json::Value>, QuerySqlError> {
141    let mut values = Vec::with_capacity(query.conditions.len());
142    for condition in &query.conditions {
143        if !condition.operator.takes_value() {
144            continue;
145        }
146        let Some(value) = &condition.value else {
147            return Err(QuerySqlError::MissingValue {
148                field: condition.field.clone(),
149                operator: format!("{:?}", condition.operator),
150            });
151        };
152        match condition.operator {
153            QueryOperator::In | QueryOperator::NotIn => match value {
154                serde_json::Value::Array(items) => values.extend(items.iter().cloned()),
155                _ => {
156                    return Err(QuerySqlError::NotAList {
157                        field: condition.field.clone(),
158                        operator: format!("{:?}", condition.operator),
159                    })
160                }
161            },
162            _ => values.push(value.clone()),
163        }
164    }
165    Ok(values)
166}
167
168/// Render the tail of a SELECT/DELETE for `query`.
169///
170/// `placement` decides which fields are columns. In
171/// [`FieldPlacement::All`] an unknown field is an error, because there
172/// is no blob it could have lived in; with promoted columns it falls
173/// back to the document.
174pub fn build_query_sql(
175    query: &Query,
176    placement: &FieldPlacement,
177    columns: Option<&HashSet<String>>,
178) -> Result<BuiltQuery, QuerySqlError> {
179    let mut sql = String::new();
180    let mut placeholders = 0usize;
181
182    let known = |field: &str| -> Result<(), QuerySqlError> {
183        match columns {
184            Some(columns)
185                if !columns.contains(field)
186                    && !matches!(field, "id" | "created_at" | "createdAt") =>
187            {
188                Err(QuerySqlError::UnknownField {
189                    field: field.to_string(),
190                })
191            }
192            _ => Ok(()),
193        }
194    };
195
196    if !query.conditions.is_empty() {
197        let mut rendered = Vec::with_capacity(query.conditions.len());
198        for condition in &query.conditions {
199            known(&condition.field)?;
200            let lhs = field_expression(&condition.field, placement);
201
202            if condition.operator.takes_value() && condition.value.is_none() {
203                return Err(QuerySqlError::MissingValue {
204                    field: condition.field.clone(),
205                    operator: format!("{:?}", condition.operator),
206                });
207            }
208
209            let mut slot = || {
210                placeholders += 1;
211                format!("?{placeholders}")
212            };
213
214            rendered.push(match condition.operator {
215                QueryOperator::IsNull => format!("{lhs} IS NULL"),
216                QueryOperator::IsNotNull => format!("{lhs} IS NOT NULL"),
217                QueryOperator::Equals => format!("{lhs} = {}", slot()),
218                QueryOperator::Not => format!("{lhs} <> {}", slot()),
219                QueryOperator::Gt => format!("{lhs} > {}", slot()),
220                QueryOperator::Gte => format!("{lhs} >= {}", slot()),
221                QueryOperator::Lt => format!("{lhs} < {}", slot()),
222                QueryOperator::Lte => format!("{lhs} <= {}", slot()),
223                QueryOperator::Like => format!("{lhs} LIKE {}", slot()),
224                QueryOperator::NotLike => format!("{lhs} NOT LIKE {}", slot()),
225                QueryOperator::In | QueryOperator::NotIn => {
226                    let list = expand_in(condition, &mut placeholders)?;
227                    let negate = matches!(condition.operator, QueryOperator::NotIn);
228                    match (negate, list.is_empty()) {
229                        // `x IN ()` is a syntax error in SQLite, and an
230                        // empty set has an unambiguous answer anyway.
231                        (false, true) => "0 = 1".to_string(),
232                        (true, true) => "1 = 1".to_string(),
233                        (false, false) => format!("{lhs} IN ({})", list.join(", ")),
234                        (true, false) => format!("{lhs} NOT IN ({})", list.join(", ")),
235                    }
236                }
237            });
238        }
239        sql.push_str(" WHERE ");
240        sql.push_str(&rendered.join(" AND "));
241    }
242
243    if !query.order.is_empty() {
244        let mut parts = Vec::with_capacity(query.order.len());
245        for (field, direction) in &query.order {
246            known(field)?;
247            parts.push(format!(
248                "{} {}",
249                field_expression(field, placement),
250                match direction {
251                    Direction::Asc => "ASC",
252                    Direction::Desc => "DESC",
253                }
254            ));
255        }
256        sql.push_str(" ORDER BY ");
257        sql.push_str(&parts.join(", "));
258    }
259
260    // Rendered rather than bound: they are numbers this code produced,
261    // never a caller's string, and inlining keeps the placeholder
262    // numbering aligned with `bind_values`.
263    if let Some(limit) = query.limit {
264        sql.push_str(&format!(" LIMIT {limit}"));
265    }
266    if let Some(offset) = query.offset {
267        // SQLite refuses OFFSET without LIMIT; -1 is its documented
268        // spelling for "no limit".
269        if query.limit.is_none() {
270            sql.push_str(" LIMIT -1");
271        }
272        sql.push_str(&format!(" OFFSET {offset}"));
273    }
274
275    Ok(BuiltQuery { sql, placeholders })
276}
277
278fn expand_in(
279    condition: &wabot_core::repository::query::Condition,
280    placeholders: &mut usize,
281) -> Result<Vec<String>, QuerySqlError> {
282    let Some(serde_json::Value::Array(items)) = &condition.value else {
283        return Err(QuerySqlError::NotAList {
284            field: condition.field.clone(),
285            operator: format!("{:?}", condition.operator),
286        });
287    };
288    Ok(items
289        .iter()
290        .map(|_| {
291            *placeholders += 1;
292            format!("?{placeholders}")
293        })
294        .collect())
295}
296
297// A note for whoever adds the third backend, or gets tired of reading
298// this file next to the Postgres one:
299//
300// The obvious move is a `SqlDialect` in `wabot-core` that both crates
301// render through. It is probably right — but it was deliberately not
302// done when this file was written, because a dialect abstraction
303// designed against one implementation is a guess. Now that there are
304// two, the differences are visible and small (placeholder syntax, `IN`
305// expansion, the JSON extraction and its cast), and the extraction can
306// be driven by what actually differs rather than by what might.
307//
308// Do it before a third backend, not after: two files drifting is
309// annoying, three is a bug farm.
310
311#[cfg(test)]
312mod tests {
313    use super::*;
314    use serde_json::json;
315
316    fn columnar() -> FieldPlacement {
317        FieldPlacement::All
318    }
319
320    fn built(query: &Query, placement: &FieldPlacement) -> String {
321        build_query_sql(query, placement, None).unwrap().sql
322    }
323
324    #[test]
325    fn an_empty_query_adds_nothing() {
326        assert_eq!(built(&Query::new(), &columnar()), "");
327    }
328
329    #[test]
330    fn conditions_become_and_ed_placeholders() {
331        let sql = built(
332            &Query::new().eq("owner_id", "u-1").gt("total", 100),
333            &columnar(),
334        );
335        assert_eq!(sql, " WHERE \"owner_id\" = ?1 AND \"total\" > ?2");
336    }
337
338    #[test]
339    fn every_operator_renders() {
340        let query = Query::new()
341            .eq("a", 1)
342            .not("b", 1)
343            .gt("c", 1)
344            .gte("d", 1)
345            .lt("e", 1)
346            .lte("f", 1)
347            .like("g", "x%")
348            .not_like("h", "x%")
349            .is_in("i", json!([1]))
350            .not_in("j", json!([1]))
351            .is_null("k")
352            .is_not_null("l");
353
354        let built = build_query_sql(&query, &columnar(), None).unwrap();
355        assert!(built.sql.contains("\"a\" = ?1"));
356        assert!(built.sql.contains("\"b\" <> ?2"));
357        assert!(built.sql.contains("\"g\" LIKE ?7"));
358        assert!(built.sql.contains("\"i\" IN (?9)"));
359        assert!(built.sql.contains("\"j\" NOT IN (?10)"));
360        assert!(built.sql.contains("\"k\" IS NULL"));
361        assert!(built.sql.contains("\"l\" IS NOT NULL"));
362        assert_eq!(built.placeholders, 10);
363        assert_eq!(
364            built.placeholders,
365            bind_values(&query).unwrap().len(),
366            "placeholders and bind values must agree, or every value lands on the wrong condition"
367        );
368    }
369
370    /// The divergence most likely to be got wrong by porting the
371    /// Postgres spelling: one array becomes N placeholders, so the
372    /// values after it shift.
373    #[test]
374    fn membership_expands_to_one_placeholder_per_element() {
375        let query = Query::new()
376            .eq("before", "x")
377            .is_in("status", json!(["open", "paid", "void"]))
378            .eq("after", "y");
379
380        let built = build_query_sql(&query, &columnar(), None).unwrap();
381        assert_eq!(
382            built.sql,
383            " WHERE \"before\" = ?1 AND \"status\" IN (?2, ?3, ?4) AND \"after\" = ?5"
384        );
385        assert_eq!(built.placeholders, 5);
386
387        let values = bind_values(&query).unwrap();
388        assert_eq!(values.len(), 5, "the array was flattened");
389        assert_eq!(
390            values[4],
391            json!("y"),
392            "the value after the list still lines up"
393        );
394    }
395
396    /// `x IN ()` is a syntax error in SQLite, but an empty set has an
397    /// obvious answer — so it becomes a constant rather than a crash.
398    #[test]
399    fn an_empty_membership_list_is_a_constant() {
400        let sql = built(&Query::new().is_in("s", json!([])), &columnar());
401        assert_eq!(sql, " WHERE 0 = 1");
402
403        let sql = built(&Query::new().not_in("s", json!([])), &columnar());
404        assert_eq!(sql, " WHERE 1 = 1");
405    }
406
407    /// Where SQLite is *better* than Postgres, and the reason there is
408    /// no `::numeric` anywhere in this file: `->>` keeps the type.
409    #[test]
410    fn a_json_field_needs_no_cast() {
411        let placement = FieldPlacement::promoted(["owner_id"]);
412
413        assert_eq!(
414            built(&Query::new().gt("total", 100), &placement),
415            " WHERE \"data\"->>'$.\"total\"' > ?1"
416        );
417        assert_eq!(
418            built(&Query::new().gt("owner_id", "a"), &placement),
419            " WHERE \"owner_id\" > ?1",
420            "a promoted column is already typed"
421        );
422    }
423
424    #[test]
425    fn the_reserved_columns_are_always_native() {
426        let placement = FieldPlacement::none();
427        assert_eq!(
428            built(&Query::new().gt("createdAt", 1).eq("id", "x"), &placement),
429            " WHERE \"created_at\" > ?1 AND \"id\" = ?2",
430            "`createdAt` is the field name; `created_at` is the column"
431        );
432    }
433
434    #[test]
435    fn ordering_limit_and_offset_are_rendered() {
436        let sql = built(
437            &Query::new()
438                .order_desc("created_at")
439                .order_asc("id")
440                .limit(20)
441                .offset(40),
442            &columnar(),
443        );
444        assert_eq!(
445            sql,
446            " ORDER BY \"created_at\" DESC, \"id\" ASC LIMIT 20 OFFSET 40"
447        );
448    }
449
450    /// SQLite refuses `OFFSET` without `LIMIT`, where Postgres accepts
451    /// it — so the builder supplies the documented "no limit" value.
452    #[test]
453    fn an_offset_without_a_limit_still_parses() {
454        let sql = built(&Query::new().offset(10), &columnar());
455        assert_eq!(sql, " LIMIT -1 OFFSET 10");
456    }
457
458    /// A field name becomes part of the statement, so an unknown one
459    /// is refused rather than quoted and hoped for.
460    #[test]
461    fn an_unknown_field_is_refused_when_the_columns_are_known() {
462        let columns: HashSet<String> = ["owner_id".to_string()].into_iter().collect();
463
464        assert!(build_query_sql(
465            &Query::new().eq("owner_id", "u-1"),
466            &columnar(),
467            Some(&columns)
468        )
469        .is_ok());
470
471        let error = build_query_sql(
472            &Query::new().eq("total; DROP TABLE orders", 1),
473            &columnar(),
474            Some(&columns),
475        )
476        .unwrap_err();
477        assert!(
478            matches!(error, QuerySqlError::UnknownField { .. }),
479            "{error}"
480        );
481
482        let error = build_query_sql(&Query::new().order_asc("nope"), &columnar(), Some(&columns))
483            .unwrap_err();
484        assert!(
485            matches!(error, QuerySqlError::UnknownField { .. }),
486            "ordering too"
487        );
488    }
489
490    /// Even without a column list a hostile field name cannot break
491    /// out: an identifier's quotes are doubled, and a JSON label is
492    /// escaped for both the path grammar and the SQL literal around
493    /// it.
494    #[test]
495    fn identifiers_and_json_labels_are_escaped() {
496        assert_eq!(
497            built(&Query::new().eq("we\"ird", 1), &columnar()),
498            " WHERE \"we\"\"ird\" = ?1"
499        );
500        assert_eq!(
501            built(&Query::new().eq("we'ird", 1), &FieldPlacement::none()),
502            " WHERE \"data\"->>'$.\"we''ird\"' = ?1"
503        );
504        assert_eq!(
505            built(&Query::new().eq("we\"ird", 1), &FieldPlacement::none()),
506            " WHERE \"data\"->>'$.\"we\\\"ird\"' = ?1"
507        );
508    }
509
510    #[test]
511    fn a_value_less_comparison_is_an_error_not_a_broken_statement() {
512        let query = Query::new().condition(wabot_core::repository::query::Condition {
513            field: "total".into(),
514            operator: QueryOperator::Gt,
515            value: None,
516        });
517        assert!(matches!(
518            build_query_sql(&query, &columnar(), None),
519            Err(QuerySqlError::MissingValue { .. })
520        ));
521    }
522
523    #[test]
524    fn membership_against_a_non_list_is_an_error() {
525        let query = Query::new().is_in("s", json!("not-a-list"));
526        assert!(matches!(
527            build_query_sql(&query, &columnar(), None),
528            Err(QuerySqlError::NotAList { .. })
529        ));
530        assert!(matches!(
531            bind_values(&query),
532            Err(QuerySqlError::NotAList { .. })
533        ));
534    }
535}