queryweave_sql/
sql.rs

1//! The SQL every query builder shares: operators, directions, `LIKE`
2//! patterns and page offsets, portable across Postgres, MySQL/MariaDB and
3//! SQLite.
4//!
5//! Only standard SQL is written: comparisons, `LIKE … ESCAPE`, `ORDER BY`,
6//! `col = value` assignments and `LIMIT … OFFSET …`, every value bound, never
7//! interpolated. The targets (`backend`) write it with these helpers, which
8//! are public for a target of your own.
9
10use crate::{Junction, PageTarget};
11use queryweave::{FilterOperator, SortDirectionModel};
12
13/// The character escaping the `LIKE` metacharacters in a bound pattern
14/// ([`escape_like`]), to name in the clause: `… LIKE ? ESCAPE '!'`.
15///
16/// Not the backslash: MySQL reads `'\'` as an escape inside string literals
17/// (so `ESCAPE '\'` does not even parse), and SQLite has no default escape
18/// character at all. `!` means the same everywhere.
19pub const LIKE_ESCAPE: char = '!';
20
21/// The SQL comparison operator for a filter operator.
22#[must_use]
23pub const fn sql_operator(operator: FilterOperator) -> &'static str {
24    match operator {
25        FilterOperator::LessThan => "<",
26        FilterOperator::Equal => "=",
27        FilterOperator::GreaterThan => ">",
28        FilterOperator::StartsWith | FilterOperator::Contains | FilterOperator::EndsWith => "LIKE",
29    }
30}
31
32/// The SQL keyword joining conditions.
33#[must_use]
34pub const fn sql_junction(junction: Junction) -> &'static str {
35    match junction {
36        Junction::And => "AND",
37        Junction::Or => "OR",
38    }
39}
40
41/// The SQL keyword for a sort direction.
42#[must_use]
43pub const fn sql_direction(direction: SortDirectionModel) -> &'static str {
44    match direction {
45        SortDirectionModel::Ascending => "ASC",
46        SortDirectionModel::Descending => "DESC",
47    }
48}
49
50/// The `LIKE` pattern to bind for a `LIKE` operator: the escaped text wrapped
51/// in `%` wildcards. `None` for the comparison operators, which bind the value
52/// itself.
53///
54/// Kept separate so the wildcard placement can be unit-tested directly: the
55/// value is pushed as a bound parameter and therefore never appears in the
56/// rendered SQL.
57#[must_use]
58pub fn like_pattern(operator: FilterOperator, text: &str) -> Option<String> {
59    let text = escape_like(text);
60
61    match operator {
62        FilterOperator::StartsWith => Some(format!("{text}%")),
63        FilterOperator::Contains => Some(format!("%{text}%")),
64        FilterOperator::EndsWith => Some(format!("%{text}")),
65        FilterOperator::LessThan | FilterOperator::Equal | FilterOperator::GreaterThan => None,
66    }
67}
68
69/// Escape the `LIKE` metacharacters (`%`, `_` and [`LIKE_ESCAPE`] itself) so
70/// user input matches literally instead of acting as wildcards.
71#[must_use]
72pub fn escape_like(value: &str) -> String {
73    let mut escaped = String::with_capacity(value.len());
74
75    for character in value.chars() {
76        if matches!(character, '%' | '_') || character == LIKE_ESCAPE {
77            escaped.push(LIKE_ESCAPE);
78        }
79        escaped.push(character);
80    }
81
82    escaped
83}
84
85/// The row offset of page `page` (0 based) of `page_size` rows, saturated at
86/// `i64::MAX`: `u32 * u32` always fits a `u64`, and an offset past every
87/// database's signed 64-bit row count just yields an empty page.
88#[must_use]
89pub fn page_offset(page: u32, page_size: u32) -> i64 {
90    i64::try_from(u64::from(page) * u64::from(page_size)).unwrap_or(i64::MAX)
91}
92
93/// Write a `LIMIT page_size OFFSET page * page_size` clause, binding both,
94/// when both a page and a page size are requested; nothing otherwise.
95///
96/// See [`PageTarget::write_page`].
97pub fn push_page<Q: PageTarget + ?Sized>(query: &mut Q, page: Option<u32>, page_size: Option<u32>) {
98    query.write_page(page, page_size);
99}
100
101#[cfg(test)]
102mod tests {
103    use super::{escape_like, like_pattern, page_offset};
104    use pretty_assertions::assert_eq;
105    use queryweave::FilterOperator;
106    use rstest::rstest;
107
108    // The `LIKE` operators bind the value wrapped in `%`; the comparisons bind
109    // the typed value itself, so they have no pattern. The pattern is a bound
110    // parameter and never appears in the rendered SQL, so it is asserted
111    // through the extracted helper.
112    #[rstest]
113    #[case(FilterOperator::StartsWith, Some("needle%"))]
114    #[case(FilterOperator::Contains, Some("%needle%"))]
115    #[case(FilterOperator::EndsWith, Some("%needle"))]
116    #[case(FilterOperator::Equal, None)]
117    #[case(FilterOperator::LessThan, None)]
118    #[case(FilterOperator::GreaterThan, None)]
119    fn the_like_pattern_matches_the_operator(
120        #[case] operator: FilterOperator,
121        #[case] expected_pattern: Option<&str>,
122    ) {
123        assert_eq!(
124            like_pattern(operator, "needle").as_deref(),
125            expected_pattern
126        );
127    }
128
129    // `LIKE` metacharacters (and the escape character itself) are escaped
130    // with `!`, so they match literally; a backslash is an ordinary character.
131    #[rstest]
132    #[case("%", "!%")]
133    #[case("_", "!_")]
134    #[case("!", "!!")]
135    #[case(r"\", r"\")]
136    #[case(r"50%_off!\now", r"50!%!_off!!\now")]
137    #[case("plain", "plain")]
138    fn metacharacters_are_escaped_with_the_escape_character(
139        #[case] value: &str,
140        #[case] expected: &str,
141    ) {
142        assert_eq!(escape_like(value), expected);
143    }
144
145    // Only the wildcards the operator adds stay live.
146    #[test]
147    fn the_pattern_wraps_the_escaped_text() {
148        assert_eq!(
149            like_pattern(FilterOperator::Contains, "50%").as_deref(),
150            Some("%50!%%")
151        );
152    }
153
154    // The offset is `page * page_size`, saturated rather than overflowing.
155    #[rstest]
156    #[case(0, 10, 0)]
157    #[case(3, 10, 30)]
158    #[case(u32::MAX, 1, 4_294_967_295)]
159    #[case(u32::MAX, u32::MAX, i64::MAX)]
160    fn the_offset_saturates(#[case] page: u32, #[case] page_size: u32, #[case] expected: i64) {
161        assert_eq!(page_offset(page, page_size), expected);
162    }
163}
queryweave_sql/sql.rs

queryweave_sql/
sql.rs
