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

aster_forge_db/
search_query.rs
