lance_graph/
sql_converter.rs

1//! SQL converter for logical plans
2//!
3//! This module converts our logical plan representation to optimized SQL
4//! that can be executed by DataFusion or LanceDB for better query optimization.
5//!
6//! # Target Platforms
7//! - **DataFusion**: Direct SQL execution via DataFusion's SQL interface
8//! - **LanceDB**: Native SQL execution with potential for vector/full-text extensions
9
10use crate::ast::{
11    BooleanExpression, ComparisonOperator, PropertyRef, PropertyValue, RelationshipDirection,
12    ValueExpression,
13};
14use crate::config::GraphConfig;
15use crate::error::{GraphError, Result};
16use crate::logical_plan::{LogicalOperator, ProjectionItem, SortItem};
17use std::collections::HashMap;
18
19/// Converts logical plans to SQL for DataFusion or LanceDB execution
20pub struct LogicalPlanToSqlConverter<'a> {
21    config: &'a Option<GraphConfig>,
22    variable_counter: u32,
23    table_aliases: HashMap<String, String>,
24}
25
26impl<'a> LogicalPlanToSqlConverter<'a> {
27    pub fn new(config: &'a Option<GraphConfig>) -> Self {
28        Self {
29            config,
30            variable_counter: 0,
31            table_aliases: HashMap::new(),
32        }
33    }
34
35    /// Convert a logical plan to SQL compatible with DataFusion and LanceDB
36    pub fn convert(&mut self, plan: &LogicalOperator) -> Result<String> {
37        match plan {
38            LogicalOperator::Project { input, projections } => {
39                self.convert_project(input, projections)
40            }
41
42            LogicalOperator::Filter { input, predicate } => self.convert_filter(input, predicate),
43
44            LogicalOperator::ScanByLabel {
45                variable,
46                label,
47                properties,
48            } => self.convert_scan(variable, label, properties),
49
50            LogicalOperator::Expand {
51                input,
52                source_variable,
53                target_variable,
54                relationship_types,
55                direction,
56                relationship_variable,
57                properties,
58                ..
59            } => self.convert_expand(
60                input,
61                source_variable,
62                target_variable,
63                relationship_types,
64                direction,
65                relationship_variable,
66                properties,
67            ),
68
69            LogicalOperator::Distinct { input } => self.convert_distinct(input),
70
71            LogicalOperator::Limit { input, count } => self.convert_limit(input, *count as i64),
72
73            LogicalOperator::Offset { input, offset } => self.convert_offset(input, *offset as i64),
74
75            LogicalOperator::Sort { input, sort_items } => self.convert_sort(input, sort_items),
76
77            LogicalOperator::VariableLengthExpand { .. } => Err(GraphError::PlanError {
78                message: "Variable length paths not supported in SQL conversion".to_string(),
79                location: snafu::Location::new(file!(), line!(), column!()),
80            }),
81
82            LogicalOperator::Join { .. } => Err(GraphError::PlanError {
83                message: "Complex joins not supported in SQL conversion".to_string(),
84                location: snafu::Location::new(file!(), line!(), column!()),
85            }),
86        }
87    }
88
89    fn convert_project(
90        &mut self,
91        input: &LogicalOperator,
92        projections: &[ProjectionItem],
93    ) -> Result<String> {
94        let input_sql = self.convert(input)?;
95
96        if projections.is_empty() {
97            return Ok(format!("SELECT * FROM ({})", input_sql));
98        }
99
100        let proj_list = projections
101            .iter()
102            .map(|p| self.projection_to_sql(p))
103            .collect::<Result<Vec<_>>>()?
104            .join(", ");
105
106        Ok(format!("SELECT {} FROM ({})", proj_list, input_sql))
107    }
108
109    fn convert_filter(
110        &mut self,
111        input: &LogicalOperator,
112        predicate: &BooleanExpression,
113    ) -> Result<String> {
114        let input_sql = self.convert(input)?;
115        let where_clause = self.boolean_expr_to_sql(predicate)?;
116        Ok(format!(
117            "SELECT * FROM ({}) WHERE {}",
118            input_sql, where_clause
119        ))
120    }
121
122    fn convert_scan(
123        &mut self,
124        variable: &str,
125        label: &str,
126        properties: &HashMap<String, PropertyValue>,
127    ) -> Result<String> {
128        // Store table alias for this variable - use the variable name as the alias
129        self.table_aliases
130            .insert(variable.to_string(), variable.to_string());
131
132        let mut sql = format!("SELECT * FROM {} AS {}", label, variable);
133
134        if !properties.is_empty() {
135            let filters = properties
136                .iter()
137                .map(|(k, v)| {
138                    Ok(format!(
139                        "{}.{} = {}",
140                        variable,
141                        k,
142                        self.property_value_to_sql(v)?
143                    ))
144                })
145                .collect::<Result<Vec<_>>>()?
146                .join(" AND ");
147            sql = format!("{} WHERE {}", sql, filters);
148        }
149
150        Ok(sql)
151    }
152
153    #[allow(clippy::too_many_arguments)]
154    fn convert_expand(
155        &mut self,
156        _input: &LogicalOperator,
157        source_variable: &str,
158        target_variable: &str,
159        relationship_types: &[String],
160        _direction: &RelationshipDirection,
161        relationship_variable: &Option<String>,
162        _properties: &HashMap<String, PropertyValue>,
163    ) -> Result<String> {
164        let _input_sql = self.convert(_input)?;
165        let rel_type = relationship_types
166            .first()
167            .ok_or_else(|| GraphError::PlanError {
168                message: "No relationship type specified".to_string(),
169                location: snafu::Location::new(file!(), line!(), column!()),
170            })?;
171
172        let config = self.config.as_ref().ok_or_else(|| GraphError::PlanError {
173            message: "Config required for relationship queries".to_string(),
174            location: snafu::Location::new(file!(), line!(), column!()),
175        })?;
176
177        let _rel_mapping =
178            config
179                .get_relationship_mapping(rel_type)
180                .ok_or_else(|| GraphError::PlanError {
181                    message: format!("No relationship mapping for {}", rel_type),
182                    location: snafu::Location::new(file!(), line!(), column!()),
183                })?;
184
185        // Generate unique aliases
186        let src_alias = format!("src_{}", self.variable_counter);
187        let rel_alias = format!("rel_{}", self.variable_counter);
188        let tgt_alias = format!("tgt_{}", self.variable_counter);
189        self.variable_counter += 1;
190
191        // Store aliases for variables
192        self.table_aliases
193            .insert(source_variable.to_string(), src_alias.clone());
194        self.table_aliases
195            .insert(target_variable.to_string(), tgt_alias.clone());
196        if let Some(rel_var) = relationship_variable {
197            self.table_aliases
198                .insert(rel_var.clone(), rel_alias.clone());
199        }
200
201        // TODO: CRITICAL BUG - This code hardcodes "id" but should use configured ID fields
202        // The problem is we need to track variable -> label mappings to get the right ID field
203        // For now, we'll return an error since relationship queries are unsupported anyway
204        Err(GraphError::PlanError {
205            message: "Relationship traversal not supported in SQL conversion - would require proper ID field mapping".to_string(),
206            location: snafu::Location::new(file!(), line!(), column!()),
207        })
208    }
209
210    fn convert_distinct(&mut self, input: &LogicalOperator) -> Result<String> {
211        let input_sql = self.convert(input)?;
212        Ok(format!("SELECT DISTINCT * FROM ({})", input_sql))
213    }
214
215    fn convert_limit(&mut self, input: &LogicalOperator, count: i64) -> Result<String> {
216        let input_sql = self.convert(input)?;
217        Ok(format!("SELECT * FROM ({}) LIMIT {}", input_sql, count))
218    }
219
220    fn convert_offset(&mut self, input: &LogicalOperator, offset: i64) -> Result<String> {
221        let input_sql = self.convert(input)?;
222        Ok(format!("SELECT * FROM ({}) OFFSET {}", input_sql, offset))
223    }
224
225    fn convert_sort(
226        &mut self,
227        input: &LogicalOperator,
228        _sort_items: &[SortItem],
229    ) -> Result<String> {
230        // For now, just pass through the input (ORDER BY is complex to implement)
231        // TODO: Implement proper ORDER BY conversion
232        self.convert(input)
233    }
234
235    fn projection_to_sql(&self, projection: &ProjectionItem) -> Result<String> {
236        let expr_sql = self.value_expr_to_sql(&projection.expression)?;
237
238        if let Some(alias) = &projection.alias {
239            Ok(format!("{} AS {}", expr_sql, alias))
240        } else {
241            Ok(expr_sql)
242        }
243    }
244
245    fn boolean_expr_to_sql(&self, expr: &BooleanExpression) -> Result<String> {
246        match expr {
247            BooleanExpression::Comparison {
248                left,
249                operator,
250                right,
251            } => {
252                let left_sql = self.value_expr_to_sql(left)?;
253                let right_sql = self.value_expr_to_sql(right)?;
254                let op_sql = match operator {
255                    ComparisonOperator::Equal => "=",
256                    ComparisonOperator::NotEqual => "!=",
257                    ComparisonOperator::LessThan => "<",
258                    ComparisonOperator::LessThanOrEqual => "<=",
259                    ComparisonOperator::GreaterThan => ">",
260                    ComparisonOperator::GreaterThanOrEqual => ">=",
261                };
262                Ok(format!("{} {} {}", left_sql, op_sql, right_sql))
263            }
264
265            BooleanExpression::In { expression, list } => {
266                let expr_sql = self.value_expr_to_sql(expression)?;
267                let list_sql = list
268                    .iter()
269                    .map(|v| self.value_expr_to_sql(v))
270                    .collect::<Result<Vec<_>>>()?
271                    .join(", ");
272                Ok(format!("{} IN ({})", expr_sql, list_sql))
273            }
274
275            BooleanExpression::And(left, right) => {
276                let left_sql = self.boolean_expr_to_sql(left)?;
277                let right_sql = self.boolean_expr_to_sql(right)?;
278                Ok(format!("({}) AND ({})", left_sql, right_sql))
279            }
280
281            BooleanExpression::Or(left, right) => {
282                let left_sql = self.boolean_expr_to_sql(left)?;
283                let right_sql = self.boolean_expr_to_sql(right)?;
284                Ok(format!("({}) OR ({})", left_sql, right_sql))
285            }
286
287            BooleanExpression::Not(inner) => {
288                let inner_sql = self.boolean_expr_to_sql(inner)?;
289                Ok(format!("NOT ({})", inner_sql))
290            }
291
292            BooleanExpression::Exists(prop) => {
293                let prop_sql = self.property_ref_to_sql(prop)?;
294                Ok(format!("{} IS NOT NULL", prop_sql))
295            }
296
297            _ => Err(GraphError::PlanError {
298                message: "Unsupported boolean expression in SQL conversion".to_string(),
299                location: snafu::Location::new(file!(), line!(), column!()),
300            }),
301        }
302    }
303
304    fn value_expr_to_sql(&self, expr: &ValueExpression) -> Result<String> {
305        match expr {
306            ValueExpression::Property(prop) => self.property_ref_to_sql(prop),
307            ValueExpression::Variable(var) => Ok(var.clone()),
308            ValueExpression::Literal(value) => self.property_value_to_sql(value),
309            _ => Err(GraphError::PlanError {
310                message: "Unsupported value expression in SQL conversion".to_string(),
311                location: snafu::Location::new(file!(), line!(), column!()),
312            }),
313        }
314    }
315
316    fn property_ref_to_sql(&self, prop: &PropertyRef) -> Result<String> {
317        if let Some(table_alias) = self.table_aliases.get(&prop.variable) {
318            Ok(format!("{}.{}", table_alias, prop.property))
319        } else {
320            // Fallback to unqualified column name
321            Ok(prop.property.clone())
322        }
323    }
324
325    fn property_value_to_sql(&self, value: &PropertyValue) -> Result<String> {
326        match value {
327            PropertyValue::String(s) => Ok(format!("'{}'", s.replace('\'', "''"))), // Escape single quotes
328            PropertyValue::Integer(i) => Ok(i.to_string()),
329            PropertyValue::Float(f) => Ok(f.to_string()),
330            PropertyValue::Boolean(b) => Ok(b.to_string()),
331            PropertyValue::Null => Ok("NULL".to_string()),
332            PropertyValue::Parameter(p) => Ok(format!("${}", p)), // Parameter placeholder
333            PropertyValue::Property(prop) => self.property_ref_to_sql(prop),
334        }
335    }
336}
337
338#[cfg(test)]
339mod tests {
340    use super::*;
341    use crate::ast::{BooleanExpression, ComparisonOperator, PropertyRef, ValueExpression};
342    use crate::logical_plan::{LogicalOperator, ProjectionItem};
343    use std::collections::HashMap;
344
345    #[test]
346    fn test_simple_scan_conversion() {
347        let mut converter = LogicalPlanToSqlConverter::new(&None);
348
349        let scan = LogicalOperator::ScanByLabel {
350            variable: "n".to_string(),
351            label: "Person".to_string(),
352            properties: HashMap::new(),
353        };
354
355        let sql = converter.convert(&scan).unwrap();
356        assert_eq!(sql, "SELECT * FROM Person AS n");
357    }
358
359    #[test]
360    fn test_scan_with_properties() {
361        let mut converter = LogicalPlanToSqlConverter::new(&None);
362
363        let mut properties = HashMap::new();
364        properties.insert(
365            "name".to_string(),
366            PropertyValue::String("Alice".to_string()),
367        );
368
369        let scan = LogicalOperator::ScanByLabel {
370            variable: "n".to_string(),
371            label: "Person".to_string(),
372            properties,
373        };
374
375        let sql = converter.convert(&scan).unwrap();
376        assert_eq!(sql, "SELECT * FROM Person AS n WHERE n.name = 'Alice'");
377    }
378
379    #[test]
380    fn test_project_conversion() {
381        let mut converter = LogicalPlanToSqlConverter::new(&None);
382
383        let scan = LogicalOperator::ScanByLabel {
384            variable: "n".to_string(),
385            label: "Person".to_string(),
386            properties: HashMap::new(),
387        };
388
389        let project = LogicalOperator::Project {
390            input: Box::new(scan),
391            projections: vec![ProjectionItem {
392                expression: ValueExpression::Property(PropertyRef {
393                    variable: "n".to_string(),
394                    property: "name".to_string(),
395                }),
396                alias: None,
397            }],
398        };
399
400        let sql = converter.convert(&project).unwrap();
401        assert_eq!(sql, "SELECT n.name FROM (SELECT * FROM Person AS n)");
402    }
403
404    #[test]
405    fn test_filter_conversion() {
406        let mut converter = LogicalPlanToSqlConverter::new(&None);
407
408        let scan = LogicalOperator::ScanByLabel {
409            variable: "n".to_string(),
410            label: "Person".to_string(),
411            properties: HashMap::new(),
412        };
413
414        let filter = LogicalOperator::Filter {
415            input: Box::new(scan),
416            predicate: BooleanExpression::Comparison {
417                left: ValueExpression::Property(PropertyRef {
418                    variable: "n".to_string(),
419                    property: "age".to_string(),
420                }),
421                operator: ComparisonOperator::GreaterThan,
422                right: ValueExpression::Literal(PropertyValue::Integer(30)),
423            },
424        };
425
426        let sql = converter.convert(&filter).unwrap();
427        assert_eq!(
428            sql,
429            "SELECT * FROM (SELECT * FROM Person AS n) WHERE n.age > 30"
430        );
431    }
432}
lance_graph/sql_converter.rs

lance_graph/
sql_converter.rs
