aster_forge_db/
search_query.rs

1//! Shared search-query expression helpers.
2//!
3//! The helpers in this module build small `SeaQuery` expressions for common
4//! search behavior used by Aster repositories: escaped `LIKE` patterns,
5//! case-insensitive substring checks, `SQLite` FTS phrase queries, and `MySQL`
6//! boolean-mode phrase queries. They do not depend on product entities and leave
7//! database-specific query composition to the caller.
8
9use sea_orm::ExprTrait;
10use sea_orm::sea_query::{
11    Alias, Expr, Func, IntoColumnRef, LikeExpr, Query, SimpleExpr, extension::sqlite::SqliteExpr,
12};
13
14/// Escapes the escape character itself and the wildcard characters for SQL `LIKE` queries.
15///
16/// The pattern assumes `\` as the escape character. `MySQL` and `PostgreSQL` use it by default,
17/// but `SQLite` has no default escape character, so callers building their own condition must
18/// pair the pattern with `ESCAPE '\'` (e.g. via [`sea_orm::sea_query::LikeExpr::escape`])
19/// for the pattern to mean the same thing on every backend.
20#[must_use]
21pub fn escape_like_query(query: &str) -> String {
22    query
23        .replace('\\', "\\\\")
24        .replace('%', "\\%")
25        .replace('_', "\\_")
26}
27
28/// Builds a case-insensitive `LIKE '%query%'` condition for a column.
29///
30/// The condition declares `ESCAPE '\'` explicitly so `SQLite` applies the same escape
31/// semantics as `MySQL` and `PostgreSQL` instead of treating backslashes as literal text.
32pub fn lower_like_condition(column: impl IntoColumnRef + Copy, query: &str) -> SimpleExpr {
33    let mut pattern = String::with_capacity(query.len() + 2);
34    pattern.push('%');
35    for ch in query.chars() {
36        match ch {
37            '\\' => pattern.push_str("\\\\"),
38            '%' => pattern.push_str("\\%"),
39            '_' => pattern.push_str("\\_"),
40            _ => pattern.extend(ch.to_lowercase()),
41        }
42    }
43    pattern.push('%');
44    Expr::expr(Func::lower(Expr::col(column))).like(LikeExpr::new(pattern).escape('\\'))
45}
46
47/// Builds a quoted `SQLite` FTS phrase query when the input is long enough.
48#[must_use]
49pub fn sqlite_match_query(query: &str) -> Option<String> {
50    if query.chars().count() < 3 {
51        return None;
52    }
53
54    Some(format!("\"{}\"", query.replace('"', "\"\"")))
55}
56
57/// Builds a quoted `MySQL` boolean-mode phrase query when the input is safe.
58#[must_use]
59pub fn mysql_boolean_mode_query(query: &str) -> Option<String> {
60    if query.chars().count() < 3 || query.chars().any(|ch| !ch.is_alphanumeric()) {
61        return None;
62    }
63
64    let escaped = query.replace('\\', "\\\\").replace('"', "\\\"");
65    Some(format!("\"{escaped}\""))
66}
67
68/// Builds a `SQLite` FTS subquery condition matching row ids from an FTS table.
69pub fn sqlite_fts_match_condition(
70    id_column: impl IntoColumnRef + Copy,
71    fts_table: &str,
72    match_query: &str,
73) -> SimpleExpr {
74    Expr::col(id_column).in_subquery(
75        Query::select()
76            .expr(Expr::col(Alias::new("rowid")))
77            .from(Alias::new(fts_table))
78            .and_where(Expr::col(Alias::new(fts_table)).matches(Expr::val(match_query)))
79            .to_owned(),
80    )
81}
82
83#[cfg(test)]
84mod tests {
85    use super::{
86        escape_like_query, lower_like_condition, mysql_boolean_mode_query, sqlite_match_query,
87    };
88    use sea_orm::sea_query::{
89        Alias, MysqlQueryBuilder, PostgresQueryBuilder, Query, SqliteQueryBuilder, Value,
90    };
91
92    #[derive(Copy, Clone)]
93    struct NameColumn;
94
95    impl sea_orm::sea_query::Iden for NameColumn {
96        fn unquoted(&self) -> &'static str {
97            "name"
98        }
99    }
100
101    #[test]
102    fn escape_like_query_escapes_wildcards() {
103        assert_eq!(escape_like_query("100%_done"), "100\\%\\_done");
104    }
105
106    #[test]
107    fn escape_like_query_escapes_backslash_before_wildcards() {
108        // The backslash must be escaped first: replacing `%` before `\` would turn an
109        // already-escaped `\%` into `\\%` (escaped backslash + live wildcard).
110        assert_eq!(escape_like_query("a\\%"), "a\\\\\\%");
111        assert_eq!(escape_like_query("a\\_b"), "a\\\\\\_b");
112        assert_eq!(escape_like_query("a\\b"), "a\\\\b");
113    }
114
115    #[test]
116    fn escape_like_query_escapes_trailing_backslash() {
117        // A lone trailing backslash would otherwise escape the closing `%` added by
118        // `lower_like_condition`, silently breaking the suffix match.
119        assert_eq!(escape_like_query("a\\"), "a\\\\");
120        assert_eq!(escape_like_query("\\"), "\\\\");
121    }
122
123    fn like_condition_parts(
124        query: &str,
125        builder: impl sea_orm::sea_query::QueryBuilder,
126    ) -> (String, Vec<Value>) {
127        let (sql, values) = Query::select()
128            .column(Alias::new("name"))
129            .from(Alias::new("items"))
130            .and_where(lower_like_condition(NameColumn, query))
131            .build(builder);
132        (sql, values.0)
133    }
134
135    #[test]
136    fn lower_like_condition_declares_escape_clause_on_all_backends() {
137        for (sql, _) in [
138            like_condition_parts("report", SqliteQueryBuilder),
139            like_condition_parts("report", MysqlQueryBuilder),
140            like_condition_parts("report", PostgresQueryBuilder),
141        ] {
142            assert!(sql.contains("ESCAPE"), "expected ESCAPE clause in: {sql}");
143        }
144    }
145
146    #[test]
147    fn lower_like_condition_binds_fully_escaped_pattern_on_all_backends() {
148        // `a\%_B` must arrive at the database as one literal `a\%_` prefix followed by a
149        // lowercased `b`, with every metacharacter (including the backslash itself) escaped.
150        let expected = vec![Value::String(Some("%a\\\\\\%\\_b%".to_string()))];
151        for (sql, values) in [
152            like_condition_parts("a\\%_B", SqliteQueryBuilder),
153            like_condition_parts("a\\%_B", MysqlQueryBuilder),
154            like_condition_parts("a\\%_B", PostgresQueryBuilder),
155        ] {
156            assert!(sql.contains("LIKE"), "expected LIKE in: {sql}");
157            assert_eq!(values, expected);
158        }
159    }
160
161    #[test]
162    fn lower_like_condition_renders_sqlite_sql_with_escape_clause() {
163        let sql = Query::select()
164            .column(Alias::new("name"))
165            .from(Alias::new("items"))
166            .and_where(lower_like_condition(NameColumn, "a\\%"))
167            .to_string(SqliteQueryBuilder);
168        assert_eq!(
169            sql,
170            r#"SELECT "name" FROM "items" WHERE LOWER("name") LIKE '%a\\\%%' ESCAPE '\'"#
171        );
172    }
173
174    #[test]
175    fn sqlite_match_query_wraps_multi_character_input_in_phrase_quotes() {
176        assert_eq!(sqlite_match_query("report"), Some("\"report\"".into()));
177        assert_eq!(
178            sqlite_match_query("report\"2026"),
179            Some("\"report\"\"2026\"".into())
180        );
181    }
182
183    #[test]
184    fn sqlite_match_query_falls_back_for_short_input() {
185        assert_eq!(sqlite_match_query("r"), None);
186        assert_eq!(sqlite_match_query("re"), None);
187    }
188
189    #[test]
190    fn mysql_boolean_mode_query_uses_phrase_search_for_multi_char_input() {
191        assert_eq!(
192            mysql_boolean_mode_query("report"),
193            Some("\"report\"".into())
194        );
195        assert_eq!(
196            mysql_boolean_mode_query("report2026"),
197            Some("\"report2026\"".into())
198        );
199    }
200
201    #[test]
202    fn mysql_boolean_mode_query_falls_back_for_invalid_input() {
203        assert_eq!(mysql_boolean_mode_query("r"), None);
204        assert_eq!(mysql_boolean_mode_query("re"), None);
205        assert_eq!(mysql_boolean_mode_query("re-port"), None);
206    }
207}
aster_forge_db/search_query.rs

aster_forge_db/
search_query.rs
