sync_engine/search/
sql_translator.rs

1// Copyright (c) 2025-2026 Adrian Robinson. Licensed under the AGPL-3.0.
2// See LICENSE file in the project root for full license text.
3
4//! SQL Translator
5//!
6//! Translates Query AST to SQL WHERE clauses for JSON column queries.
7//! Uses MySQL JSON_EXTRACT syntax for querying JSON documents.
8//!
9//! # SQL Syntax Generated
10//!
11//! ```sql
12//! JSON_EXTRACT(data, '$.field') = 'value'              -- Exact match
13//! JSON_EXTRACT(data, '$.field') LIKE '%value%'         -- Contains
14//! JSON_EXTRACT(data, '$.age') BETWEEN 25 AND 40        -- Range
15//! JSON_EXTRACT(data, '$.field') LIKE 'prefix%'         -- Prefix
16//! JSON_CONTAINS(data->'$.tags', '"value"')             -- Tag membership
17//! ```
18
19use super::query_builder::{FieldOperator, FieldQuery, Query, QueryNode, QueryValue};
20
21/// SQL query translator for JSON column queries
22pub struct SqlTranslator;
23
24/// SQL query result with parameterized placeholders
25#[derive(Debug, Clone)]
26pub struct SqlQuery {
27    /// The WHERE clause (without "WHERE" keyword)
28    pub clause: String,
29    /// The parameter values in order
30    pub params: Vec<SqlParam>,
31}
32
33/// SQL parameter value
34#[derive(Debug, Clone, PartialEq)]
35pub enum SqlParam {
36    Text(String),
37    Numeric(f64),
38    Boolean(bool),
39}
40
41impl SqlTranslator {
42    /// Translate Query AST to parameterized SQL WHERE clause
43    ///
44    /// Uses `?` placeholders for parameters (MySQL style)
45    pub fn translate(query: &Query, json_column: &str) -> SqlQuery {
46        let mut params = Vec::new();
47        let clause = Self::translate_node(&query.root, json_column, &mut params);
48        SqlQuery { clause, params }
49    }
50
51    /// Translate Query AST to SQL WHERE clause with inline values
52    ///
53    /// Warning: Only use for debugging, not for actual queries (SQL injection risk)
54    pub fn translate_inline(query: &Query, json_column: &str) -> String {
55        let mut params = Vec::new();
56        let clause = Self::translate_node(&query.root, json_column, &mut params);
57
58        // Replace placeholders with actual values
59        let mut result = clause;
60        for param in params {
61            let value = match param {
62                SqlParam::Text(s) => format!("'{}'", s.replace('\'', "''")),
63                SqlParam::Numeric(n) => n.to_string(),
64                SqlParam::Boolean(b) => if b { "TRUE" } else { "FALSE" }.to_string(),
65            };
66            result = result.replacen('?', &value, 1);
67        }
68        result
69    }
70
71    fn translate_node(node: &QueryNode, json_col: &str, params: &mut Vec<SqlParam>) -> String {
72        match node {
73            QueryNode::Field(field_query) => Self::translate_field(field_query, json_col, params),
74            QueryNode::And(nodes) => {
75                let parts: Vec<String> = nodes
76                    .iter()
77                    .map(|n| Self::translate_node(n, json_col, params))
78                    .collect();
79                if parts.len() == 1 {
80                    parts[0].clone()
81                } else {
82                    format!("({})", parts.join(" AND "))
83                }
84            }
85            QueryNode::Or(nodes) => {
86                let parts: Vec<String> = nodes
87                    .iter()
88                    .map(|n| Self::translate_node(n, json_col, params))
89                    .collect();
90                if parts.len() == 1 {
91                    parts[0].clone()
92                } else {
93                    format!("({})", parts.join(" OR "))
94                }
95            }
96            QueryNode::Not(inner) => {
97                format!("NOT ({})", Self::translate_node(inner, json_col, params))
98            }
99        }
100    }
101
102    fn translate_field(
103        field: &FieldQuery,
104        json_col: &str,
105        params: &mut Vec<SqlParam>,
106    ) -> String {
107        let json_path = Self::json_path(&field.field);
108
109        match (&field.operator, &field.value) {
110            (FieldOperator::Equals, QueryValue::Text(text)) => {
111                params.push(SqlParam::Text(text.clone()));
112                format!("JSON_UNQUOTE(JSON_EXTRACT({}, '{}')) = ?", json_col, json_path)
113            }
114            (FieldOperator::Equals, QueryValue::Numeric(num)) => {
115                params.push(SqlParam::Numeric(*num));
116                format!("JSON_EXTRACT({}, '{}') = ?", json_col, json_path)
117            }
118            (FieldOperator::Equals, QueryValue::Boolean(b)) => {
119                params.push(SqlParam::Boolean(*b));
120                format!("JSON_EXTRACT({}, '{}') = ?", json_col, json_path)
121            }
122            (FieldOperator::Contains, QueryValue::Text(text)) => {
123                params.push(SqlParam::Text(format!("%{}%", text)));
124                format!(
125                    "JSON_UNQUOTE(JSON_EXTRACT({}, '{}')) LIKE ?",
126                    json_col, json_path
127                )
128            }
129            (FieldOperator::Range, QueryValue::NumericRange { min, max }) => {
130                match (min, max) {
131                    (Some(min_val), Some(max_val)) => {
132                        params.push(SqlParam::Numeric(*min_val));
133                        params.push(SqlParam::Numeric(*max_val));
134                        format!(
135                            "JSON_EXTRACT({}, '{}') BETWEEN ? AND ?",
136                            json_col, json_path
137                        )
138                    }
139                    (Some(min_val), None) => {
140                        params.push(SqlParam::Numeric(*min_val));
141                        format!("JSON_EXTRACT({}, '{}') >= ?", json_col, json_path)
142                    }
143                    (None, Some(max_val)) => {
144                        params.push(SqlParam::Numeric(*max_val));
145                        format!("JSON_EXTRACT({}, '{}') <= ?", json_col, json_path)
146                    }
147                    (None, None) => "1=1".to_string(), // Always true - no bounds
148                }
149            }
150            (FieldOperator::In, QueryValue::Tags(tags)) => {
151                // Use JSON_CONTAINS for tag membership
152                // Check if any of the tags are in the array
153                let conditions: Vec<String> = tags
154                    .iter()
155                    .map(|tag| {
156                        params.push(SqlParam::Text(format!("\"{}\"", tag)));
157                        format!("JSON_CONTAINS({}->'{}', ?)", json_col, json_path)
158                    })
159                    .collect();
160
161                if conditions.len() == 1 {
162                    conditions[0].clone()
163                } else {
164                    format!("({})", conditions.join(" OR "))
165                }
166            }
167            (FieldOperator::Prefix, QueryValue::Text(text)) => {
168                params.push(SqlParam::Text(format!("{}%", text)));
169                format!(
170                    "JSON_UNQUOTE(JSON_EXTRACT({}, '{}')) LIKE ?",
171                    json_col, json_path
172                )
173            }
174            (FieldOperator::Fuzzy, QueryValue::Text(text)) => {
175                // SQL doesn't have native fuzzy matching
176                // Fallback to contains for now
177                params.push(SqlParam::Text(format!("%{}%", text)));
178                format!(
179                    "JSON_UNQUOTE(JSON_EXTRACT({}, '{}')) LIKE ?",
180                    json_col, json_path
181                )
182            }
183            _ => {
184                // Fallback - always false for unsupported combinations
185                "1=0".to_string()
186            }
187        }
188    }
189
190    fn json_path(field: &str) -> String {
191        // Support dot notation for nested fields
192        // e.g., "user.name" -> "$.user.name"
193        if field.starts_with('$') {
194            field.to_string()
195        } else {
196            format!("$.{}", field)
197        }
198    }
199}
200
201#[cfg(test)]
202mod tests {
203    use super::*;
204
205    #[test]
206    fn test_simple_field_query() {
207        let query = Query::field_eq("name", "Alice");
208        let sql = SqlTranslator::translate(&query, "data");
209        assert_eq!(sql.clause, "JSON_UNQUOTE(JSON_EXTRACT(data, '$.name')) = ?");
210        assert_eq!(sql.params, vec![SqlParam::Text("Alice".to_string())]);
211    }
212
213    #[test]
214    fn test_inline_simple_field() {
215        let query = Query::field_eq("name", "Alice");
216        let sql = SqlTranslator::translate_inline(&query, "data");
217        assert_eq!(sql, "JSON_UNQUOTE(JSON_EXTRACT(data, '$.name')) = 'Alice'");
218    }
219
220    #[test]
221    fn test_numeric_range() {
222        let query = Query::numeric_range("age", Some(25.0), Some(40.0));
223        let sql = SqlTranslator::translate(&query, "data");
224        assert_eq!(sql.clause, "JSON_EXTRACT(data, '$.age') BETWEEN ? AND ?");
225        assert_eq!(
226            sql.params,
227            vec![SqlParam::Numeric(25.0), SqlParam::Numeric(40.0)]
228        );
229    }
230
231    #[test]
232    fn test_numeric_range_unbounded_min() {
233        let query = Query::numeric_range("age", None, Some(40.0));
234        let sql = SqlTranslator::translate(&query, "data");
235        assert_eq!(sql.clause, "JSON_EXTRACT(data, '$.age') <= ?");
236        assert_eq!(sql.params, vec![SqlParam::Numeric(40.0)]);
237    }
238
239    #[test]
240    fn test_numeric_range_unbounded_max() {
241        let query = Query::numeric_range("score", Some(100.0), None);
242        let sql = SqlTranslator::translate(&query, "data");
243        assert_eq!(sql.clause, "JSON_EXTRACT(data, '$.score') >= ?");
244        assert_eq!(sql.params, vec![SqlParam::Numeric(100.0)]);
245    }
246
247    #[test]
248    fn test_tag_query() {
249        let query = Query::tags("tags", vec!["rust".to_string(), "database".to_string()]);
250        let sql = SqlTranslator::translate(&query, "data");
251        assert_eq!(
252            sql.clause,
253            "(JSON_CONTAINS(data->'$.tags', ?) OR JSON_CONTAINS(data->'$.tags', ?))"
254        );
255        assert_eq!(
256            sql.params,
257            vec![
258                SqlParam::Text("\"rust\"".to_string()),
259                SqlParam::Text("\"database\"".to_string())
260            ]
261        );
262    }
263
264    #[test]
265    fn test_and_query() {
266        let query =
267            Query::field_eq("name", "Alice").and(Query::numeric_range("age", Some(25.0), Some(40.0)));
268        let sql = SqlTranslator::translate(&query, "data");
269        assert_eq!(
270            sql.clause,
271            "(JSON_UNQUOTE(JSON_EXTRACT(data, '$.name')) = ? AND JSON_EXTRACT(data, '$.age') BETWEEN ? AND ?)"
272        );
273        assert_eq!(
274            sql.params,
275            vec![
276                SqlParam::Text("Alice".to_string()),
277                SqlParam::Numeric(25.0),
278                SqlParam::Numeric(40.0)
279            ]
280        );
281    }
282
283    #[test]
284    fn test_or_query() {
285        let query =
286            Query::field_eq("status", "active").or(Query::field_eq("status", "pending"));
287        let sql = SqlTranslator::translate(&query, "data");
288        assert_eq!(
289            sql.clause,
290            "(JSON_UNQUOTE(JSON_EXTRACT(data, '$.status')) = ? OR JSON_UNQUOTE(JSON_EXTRACT(data, '$.status')) = ?)"
291        );
292    }
293
294    #[test]
295    fn test_not_query() {
296        let query = Query::field_eq("deleted", "true").negate();
297        let sql = SqlTranslator::translate(&query, "data");
298        assert_eq!(
299            sql.clause,
300            "NOT (JSON_UNQUOTE(JSON_EXTRACT(data, '$.deleted')) = ?)"
301        );
302    }
303
304    #[test]
305    fn test_contains_query() {
306        let query = Query::text_search("description", "database");
307        let sql = SqlTranslator::translate(&query, "data");
308        assert_eq!(
309            sql.clause,
310            "JSON_UNQUOTE(JSON_EXTRACT(data, '$.description')) LIKE ?"
311        );
312        assert_eq!(sql.params, vec![SqlParam::Text("%database%".to_string())]);
313    }
314
315    #[test]
316    fn test_prefix_query() {
317        let query = Query::prefix("email", "admin");
318        let sql = SqlTranslator::translate(&query, "data");
319        assert_eq!(
320            sql.clause,
321            "JSON_UNQUOTE(JSON_EXTRACT(data, '$.email')) LIKE ?"
322        );
323        assert_eq!(sql.params, vec![SqlParam::Text("admin%".to_string())]);
324    }
325
326    #[test]
327    fn test_nested_field() {
328        let query = Query::field_eq("user.profile.name", "Alice");
329        let sql = SqlTranslator::translate(&query, "data");
330        assert_eq!(
331            sql.clause,
332            "JSON_UNQUOTE(JSON_EXTRACT(data, '$.user.profile.name')) = ?"
333        );
334    }
335
336    #[test]
337    fn test_complex_query() {
338        let alice_query =
339            Query::field_eq("name", "Alice").and(Query::numeric_range("age", Some(25.0), Some(40.0)));
340
341        let bob_query = Query::field_eq("name", "Bob")
342            .and(Query::tags("tags", vec!["rust".to_string()]));
343
344        let query = alice_query.or(bob_query);
345        let sql = SqlTranslator::translate(&query, "data");
346
347        // Should be ((name=Alice AND age BETWEEN) OR (name=Bob AND tags CONTAINS))
348        assert!(sql.clause.starts_with("(("));
349        assert!(sql.clause.contains(" AND "));
350        assert!(sql.clause.contains(" OR "));
351    }
352}
sync_engine/search/sql_translator.rs

sync_engine/search/
sql_translator.rs
