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                // Filter out vector nodes (not supported in SQL)
76                let parts: Vec<String> = nodes
77                    .iter()
78                    .filter(|n| !matches!(n, QueryNode::Vector(_)))
79                    .map(|n| Self::translate_node(n, json_col, params))
80                    .collect();
81                if parts.is_empty() {
82                    // Only had vector query, return true (match all)
83                    "1=1".to_string()
84                } else if parts.len() == 1 {
85                    parts[0].clone()
86                } else {
87                    format!("({})", parts.join(" AND "))
88                }
89            }
90            QueryNode::Or(nodes) => {
91                let parts: Vec<String> = nodes
92                    .iter()
93                    .filter(|n| !matches!(n, QueryNode::Vector(_)))
94                    .map(|n| Self::translate_node(n, json_col, params))
95                    .collect();
96                if parts.is_empty() {
97                    "1=1".to_string()
98                } else if parts.len() == 1 {
99                    parts[0].clone()
100                } else {
101                    format!("({})", parts.join(" OR "))
102                }
103            }
104            QueryNode::Not(inner) => {
105                format!("NOT ({})", Self::translate_node(inner, json_col, params))
106            }
107            QueryNode::Vector(_) => {
108                // Vector search is not supported in SQL fallback
109                // Return a condition that matches nothing (or we could panic)
110                // For graceful degradation, we return 1=0 (false)
111                "1=0 /* vector search not supported in SQL */".to_string()
112            }
113        }
114    }
115
116    fn translate_field(
117        field: &FieldQuery,
118        json_col: &str,
119        params: &mut Vec<SqlParam>,
120    ) -> String {
121        let json_path = Self::json_path(&field.field);
122
123        match (&field.operator, &field.value) {
124            (FieldOperator::Equals, QueryValue::Text(text)) => {
125                params.push(SqlParam::Text(text.clone()));
126                format!("JSON_UNQUOTE(JSON_EXTRACT({}, '{}')) = ?", json_col, json_path)
127            }
128            (FieldOperator::Equals, QueryValue::Numeric(num)) => {
129                params.push(SqlParam::Numeric(*num));
130                format!("JSON_EXTRACT({}, '{}') = ?", json_col, json_path)
131            }
132            (FieldOperator::Equals, QueryValue::Boolean(b)) => {
133                params.push(SqlParam::Boolean(*b));
134                format!("JSON_EXTRACT({}, '{}') = ?", json_col, json_path)
135            }
136            (FieldOperator::Contains, QueryValue::Text(text)) => {
137                params.push(SqlParam::Text(format!("%{}%", text)));
138                format!(
139                    "JSON_UNQUOTE(JSON_EXTRACT({}, '{}')) LIKE ?",
140                    json_col, json_path
141                )
142            }
143            (FieldOperator::Range, QueryValue::NumericRange { min, max }) => {
144                match (min, max) {
145                    (Some(min_val), Some(max_val)) => {
146                        params.push(SqlParam::Numeric(*min_val));
147                        params.push(SqlParam::Numeric(*max_val));
148                        format!(
149                            "JSON_EXTRACT({}, '{}') BETWEEN ? AND ?",
150                            json_col, json_path
151                        )
152                    }
153                    (Some(min_val), None) => {
154                        params.push(SqlParam::Numeric(*min_val));
155                        format!("JSON_EXTRACT({}, '{}') >= ?", json_col, json_path)
156                    }
157                    (None, Some(max_val)) => {
158                        params.push(SqlParam::Numeric(*max_val));
159                        format!("JSON_EXTRACT({}, '{}') <= ?", json_col, json_path)
160                    }
161                    (None, None) => "1=1".to_string(), // Always true - no bounds
162                }
163            }
164            (FieldOperator::In, QueryValue::Tags(tags)) => {
165                // Use JSON_CONTAINS for tag membership
166                // Check if any of the tags are in the array
167                let conditions: Vec<String> = tags
168                    .iter()
169                    .map(|tag| {
170                        params.push(SqlParam::Text(format!("\"{}\"", tag)));
171                        format!("JSON_CONTAINS({}->'{}', ?)", json_col, json_path)
172                    })
173                    .collect();
174
175                if conditions.len() == 1 {
176                    conditions[0].clone()
177                } else {
178                    format!("({})", conditions.join(" OR "))
179                }
180            }
181            (FieldOperator::Prefix, QueryValue::Text(text)) => {
182                params.push(SqlParam::Text(format!("{}%", text)));
183                format!(
184                    "JSON_UNQUOTE(JSON_EXTRACT({}, '{}')) LIKE ?",
185                    json_col, json_path
186                )
187            }
188            (FieldOperator::Fuzzy, QueryValue::Text(text)) => {
189                // SQL doesn't have native fuzzy matching
190                // Fallback to contains for now
191                params.push(SqlParam::Text(format!("%{}%", text)));
192                format!(
193                    "JSON_UNQUOTE(JSON_EXTRACT({}, '{}')) LIKE ?",
194                    json_col, json_path
195                )
196            }
197            _ => {
198                // Fallback - always false for unsupported combinations
199                "1=0".to_string()
200            }
201        }
202    }
203
204    fn json_path(field: &str) -> String {
205        // Support dot notation for nested fields
206        // Views are stored as {"data": {...}, "meta": {...}}
207        // So field "score" -> "$.data.score"
208        if field.starts_with('$') {
209            field.to_string()
210        } else if field.starts_with("meta.") {
211            // Meta fields (updated_at, version) are at $.meta.X
212            format!("$.{}", field)
213        } else {
214            // Data fields are nested under $.data
215            format!("$.data.{}", field)
216        }
217    }
218}
219
220#[cfg(test)]
221mod tests {
222    use super::*;
223
224    #[test]
225    fn test_simple_field_query() {
226        let query = Query::field_eq("name", "Alice");
227        let sql = SqlTranslator::translate(&query, "payload");
228        assert_eq!(sql.clause, "JSON_UNQUOTE(JSON_EXTRACT(payload, '$.data.name')) = ?");
229        assert_eq!(sql.params, vec![SqlParam::Text("Alice".to_string())]);
230    }
231
232    #[test]
233    fn test_inline_simple_field() {
234        let query = Query::field_eq("name", "Alice");
235        let sql = SqlTranslator::translate_inline(&query, "payload");
236        assert_eq!(sql, "JSON_UNQUOTE(JSON_EXTRACT(payload, '$.data.name')) = 'Alice'");
237    }
238
239    #[test]
240    fn test_numeric_range() {
241        let query = Query::numeric_range("age", Some(25.0), Some(40.0));
242        let sql = SqlTranslator::translate(&query, "payload");
243        assert_eq!(sql.clause, "JSON_EXTRACT(payload, '$.data.age') BETWEEN ? AND ?");
244        assert_eq!(
245            sql.params,
246            vec![SqlParam::Numeric(25.0), SqlParam::Numeric(40.0)]
247        );
248    }
249
250    #[test]
251    fn test_numeric_range_unbounded_min() {
252        let query = Query::numeric_range("age", None, Some(40.0));
253        let sql = SqlTranslator::translate(&query, "payload");
254        assert_eq!(sql.clause, "JSON_EXTRACT(payload, '$.data.age') <= ?");
255        assert_eq!(sql.params, vec![SqlParam::Numeric(40.0)]);
256    }
257
258    #[test]
259    fn test_numeric_range_unbounded_max() {
260        let query = Query::numeric_range("score", Some(100.0), None);
261        let sql = SqlTranslator::translate(&query, "payload");
262        assert_eq!(sql.clause, "JSON_EXTRACT(payload, '$.data.score') >= ?");
263        assert_eq!(sql.params, vec![SqlParam::Numeric(100.0)]);
264    }
265
266    #[test]
267    fn test_tag_query() {
268        let query = Query::tags("tags", vec!["rust".to_string(), "database".to_string()]);
269        let sql = SqlTranslator::translate(&query, "payload");
270        assert_eq!(
271            sql.clause,
272            "(JSON_CONTAINS(payload->'$.data.tags', ?) OR JSON_CONTAINS(payload->'$.data.tags', ?))"
273        );
274        assert_eq!(
275            sql.params,
276            vec![
277                SqlParam::Text("\"rust\"".to_string()),
278                SqlParam::Text("\"database\"".to_string())
279            ]
280        );
281    }
282
283    #[test]
284    fn test_and_query() {
285        let query =
286            Query::field_eq("name", "Alice").and(Query::numeric_range("age", Some(25.0), Some(40.0)));
287        let sql = SqlTranslator::translate(&query, "payload");
288        assert_eq!(
289            sql.clause,
290            "(JSON_UNQUOTE(JSON_EXTRACT(payload, '$.data.name')) = ? AND JSON_EXTRACT(payload, '$.data.age') BETWEEN ? AND ?)"
291        );
292        assert_eq!(
293            sql.params,
294            vec![
295                SqlParam::Text("Alice".to_string()),
296                SqlParam::Numeric(25.0),
297                SqlParam::Numeric(40.0)
298            ]
299        );
300    }
301
302    #[test]
303    fn test_or_query() {
304        let query =
305            Query::field_eq("status", "active").or(Query::field_eq("status", "pending"));
306        let sql = SqlTranslator::translate(&query, "payload");
307        assert_eq!(
308            sql.clause,
309            "(JSON_UNQUOTE(JSON_EXTRACT(payload, '$.data.status')) = ? OR JSON_UNQUOTE(JSON_EXTRACT(payload, '$.data.status')) = ?)"
310        );
311    }
312
313    #[test]
314    fn test_not_query() {
315        let query = Query::field_eq("deleted", "true").negate();
316        let sql = SqlTranslator::translate(&query, "payload");
317        assert_eq!(
318            sql.clause,
319            "NOT (JSON_UNQUOTE(JSON_EXTRACT(payload, '$.data.deleted')) = ?)"
320        );
321    }
322
323    #[test]
324    fn test_contains_query() {
325        let query = Query::text_search("description", "database");
326        let sql = SqlTranslator::translate(&query, "payload");
327        assert_eq!(
328            sql.clause,
329            "JSON_UNQUOTE(JSON_EXTRACT(payload, '$.data.description')) LIKE ?"
330        );
331        assert_eq!(sql.params, vec![SqlParam::Text("%database%".to_string())]);
332    }
333
334    #[test]
335    fn test_prefix_query() {
336        let query = Query::prefix("email", "admin");
337        let sql = SqlTranslator::translate(&query, "payload");
338        assert_eq!(
339            sql.clause,
340            "JSON_UNQUOTE(JSON_EXTRACT(payload, '$.data.email')) LIKE ?"
341        );
342        assert_eq!(sql.params, vec![SqlParam::Text("admin%".to_string())]);
343    }
344
345    #[test]
346    fn test_nested_field() {
347        let query = Query::field_eq("user.profile.name", "Alice");
348        let sql = SqlTranslator::translate(&query, "payload");
349        assert_eq!(
350            sql.clause,
351            "JSON_UNQUOTE(JSON_EXTRACT(payload, '$.data.user.profile.name')) = ?"
352        );
353    }
354
355    #[test]
356    fn test_complex_query() {
357        let alice_query =
358            Query::field_eq("name", "Alice").and(Query::numeric_range("age", Some(25.0), Some(40.0)));
359
360        let bob_query = Query::field_eq("name", "Bob")
361            .and(Query::tags("tags", vec!["rust".to_string()]));
362
363        let query = alice_query.or(bob_query);
364        let sql = SqlTranslator::translate(&query, "payload");
365
366        // Should be ((name=Alice AND age BETWEEN) OR (name=Bob AND tags CONTAINS))
367        assert!(sql.clause.starts_with("(("));
368        assert!(sql.clause.contains(" AND "));
369        assert!(sql.clause.contains(" OR "));
370        assert!(sql.clause.contains("$.data.name"));
371    }
372}
sync_engine/search/sql_translator.rs

sync_engine/search/
sql_translator.rs
