qail_core/
transpiler.rs

1//! SQL Transpiler for QAIL AST.
2//!
3//! Converts parsed QAIL commands into executable SQL strings.
4
5use crate::ast::*;
6
7/// SQL reserved words that must be quoted when used as identifiers.
8const RESERVED_WORDS: &[&str] = &[
9    "order", "group", "user", "table", "select", "from", "where", "join",
10    "left", "right", "inner", "outer", "on", "and", "or", "not", "null",
11    "true", "false", "limit", "offset", "as", "in", "is", "like", "between",
12    "having", "union", "all", "distinct", "case", "when", "then", "else", "end",
13    "create", "alter", "drop", "insert", "update", "delete", "index", "key",
14    "primary", "foreign", "references", "default", "constraint", "check",
15];
16
17/// Escape an identifier if it's a reserved word or contains special chars.
18/// Returns the identifier quoted with double quotes if needed.
19pub fn escape_identifier(name: &str) -> String {
20    let lower = name.to_lowercase();
21    let needs_escaping = RESERVED_WORDS.contains(&lower.as_str())
22        || name.chars().any(|c| !c.is_alphanumeric() && c != '_')
23        || name.chars().next().map(|c| c.is_numeric()).unwrap_or(false);
24    
25    if needs_escaping {
26        format!("\"{}\"", name.replace('"', "\"\""))
27    } else {
28        name.to_string()
29    }
30}
31
32/// Trait for converting AST nodes to SQL.
33pub trait ToSql {
34    /// Convert this node to a SQL string.
35    fn to_sql(&self) -> String;
36}
37
38impl ToSql for QailCmd {
39    fn to_sql(&self) -> String {
40        match self.action {
41            Action::Get => self.to_select_sql(),
42            Action::Set => self.to_update_sql(),
43            Action::Del => self.to_delete_sql(),
44            Action::Add => self.to_insert_sql(),
45            Action::Gen => format!("-- gen::{}  (generates Rust struct, not SQL)", self.table),
46            Action::Make => self.to_create_table_sql(),
47            Action::Mod => self.to_alter_table_sql(),
48            Action::Over => self.to_window_sql(),
49            Action::With => self.to_cte_sql(),
50        }
51    }
52}
53
54impl QailCmd {
55    /// Generate SELECT SQL.
56    fn to_select_sql(&self) -> String {
57        let mut sql = if self.distinct {
58            String::from("SELECT DISTINCT ")
59        } else {
60            String::from("SELECT ")
61        };
62
63        // Columns
64        if self.columns.is_empty() {
65            sql.push('*');
66        } else {
67            let cols: Vec<String> = self.columns.iter().map(|c| c.to_string()).collect();
68            sql.push_str(&cols.join(", "));
69        }
70
71        // FROM
72        sql.push_str(" FROM ");
73        sql.push_str(&self.table);
74
75        // JOINS
76        for join in &self.joins {
77            let kind = match join.kind {
78                JoinKind::Inner => "INNER",
79                JoinKind::Left => "LEFT",
80                JoinKind::Right => "RIGHT",
81            };
82            // Heuristic: target.source_singular_id = source.id
83            // e.g. users -> posts => posts.user_id = users.id
84            let source_singular = self.table.trim_end_matches('s');
85            sql.push_str(&format!(
86                " {} JOIN {} ON {}.{}_id = {}.id",
87                kind, join.table, join.table, source_singular, self.table
88            ));
89        }
90        
91        // Prepare for GROUP BY check
92        let has_aggregates = self.columns.iter().any(|c| matches!(c, Column::Aggregate { .. }));
93        let mut non_aggregated_cols = Vec::new();
94        if has_aggregates {
95             for col in &self.columns {
96                 if let Column::Named(name) = col {
97                     non_aggregated_cols.push(name.clone());
98                 }
99             }
100        }
101
102        // Process cages
103        let mut where_groups: Vec<String> = Vec::new();
104        let mut order_by: Option<String> = None;
105        let mut limit: Option<usize> = None;
106        let mut offset: Option<usize> = None;
107
108        for cage in &self.cages {
109            match &cage.kind {
110                CageKind::Filter => {
111                    if !cage.conditions.is_empty() {
112                        let joiner = match cage.logical_op {
113                            LogicalOp::And => " AND ",
114                            LogicalOp::Or => " OR ",
115                        };
116                        let conditions: Vec<String> = cage.conditions.iter().map(|c| c.to_sql()).collect();
117                        let group = conditions.join(joiner);
118                        // Wrap OR groups in parentheses for correct precedence
119                        if cage.logical_op == LogicalOp::Or && cage.conditions.len() > 1 {
120                            where_groups.push(format!("({})", group));
121                        } else {
122                            where_groups.push(group);
123                        }
124                    }
125                }
126                CageKind::Sort(order) => {
127                    if let Some(cond) = cage.conditions.first() {
128                        let dir = match order {
129                            SortOrder::Asc => "ASC",
130                            SortOrder::Desc => "DESC",
131                        };
132                        order_by = Some(format!("{} {}", cond.column, dir));
133                    }
134                }
135                CageKind::Limit(n) => {
136                    limit = Some(*n);
137                }
138                CageKind::Offset(n) => {
139                    offset = Some(*n);
140                }
141                CageKind::Payload => {
142                    // Not used in SELECT
143                }
144            }
145        }
146
147        // WHERE - each cage group is joined with AND
148        if !where_groups.is_empty() {
149            sql.push_str(" WHERE ");
150            sql.push_str(&where_groups.join(" AND "));
151        }
152
153        // GROUP BY
154        if !non_aggregated_cols.is_empty() {
155            sql.push_str(" GROUP BY ");
156            sql.push_str(&non_aggregated_cols.join(", "));
157        }
158
159        // ORDER BY
160        if let Some(order) = order_by {
161            sql.push_str(" ORDER BY ");
162            sql.push_str(&order);
163        }
164
165        // LIMIT
166        if let Some(n) = limit {
167            sql.push_str(&format!(" LIMIT {}", n));
168        }
169
170        // OFFSET
171        if let Some(n) = offset {
172            sql.push_str(&format!(" OFFSET {}", n));
173        }
174
175        sql
176    }
177
178    /// Generate UPDATE SQL.
179    fn to_update_sql(&self) -> String {
180        let mut sql = String::from("UPDATE ");
181        sql.push_str(&self.table);
182
183        // For SET queries, first cage is payload, rest are filters
184        let mut set_clauses: Vec<String> = Vec::new();
185        let mut where_clauses: Vec<String> = Vec::new();
186        let mut is_first_filter = true;
187
188        for cage in &self.cages {
189            if let CageKind::Filter = cage.kind {
190                if is_first_filter {
191                    // First filter cage is the SET payload
192                    for cond in &cage.conditions {
193                        set_clauses.push(format!("{} = {}", cond.column, cond.value));
194                    }
195                    is_first_filter = false;
196                } else {
197                    // Subsequent filter cages are WHERE conditions
198                    for cond in &cage.conditions {
199                        where_clauses.push(cond.to_sql());
200                    }
201                }
202            }
203        }
204
205        // SET clause
206        if !set_clauses.is_empty() {
207            sql.push_str(" SET ");
208            sql.push_str(&set_clauses.join(", "));
209        }
210
211        // WHERE clause
212        if !where_clauses.is_empty() {
213            sql.push_str(" WHERE ");
214            sql.push_str(&where_clauses.join(" AND "));
215        }
216
217        sql
218    }
219
220    /// Generate DELETE SQL.
221    fn to_delete_sql(&self) -> String {
222        let mut sql = String::from("DELETE FROM ");
223        sql.push_str(&self.table);
224
225        // Process WHERE clauses
226        let mut where_clauses: Vec<String> = Vec::new();
227
228        for cage in &self.cages {
229            if let CageKind::Filter = cage.kind {
230                for cond in &cage.conditions {
231                    where_clauses.push(cond.to_sql());
232                }
233            }
234        }
235
236        if !where_clauses.is_empty() {
237            sql.push_str(" WHERE ");
238            sql.push_str(&where_clauses.join(" AND "));
239        }
240
241        sql
242    }
243
244    /// Generate INSERT SQL.
245    fn to_insert_sql(&self) -> String {
246        let mut sql = String::from("INSERT INTO ");
247        sql.push_str(&self.table);
248
249        // For ADD queries, we use columns and first cage contains values
250        let cols: Vec<String> = self.columns.iter().map(|c| c.to_string()).collect();
251        
252        if !cols.is_empty() {
253            sql.push_str(" (");
254            sql.push_str(&cols.join(", "));
255            sql.push(')');
256        }
257
258        // Values from first payload/filter cage
259        if let Some(cage) = self.cages.first() {
260            let values: Vec<String> = cage.conditions.iter().map(|c| c.value.to_string()).collect();
261            
262            if !values.is_empty() {
263                sql.push_str(" VALUES (");
264                sql.push_str(&values.join(", "));
265                sql.push(')');
266            }
267        }
268
269        // RETURNING clause - if columns are specified, return them
270        if !self.columns.is_empty() {
271            let cols: Vec<String> = self.columns.iter().map(|c| c.to_string()).collect();
272            sql.push_str(" RETURNING ");
273            sql.push_str(&cols.join(", "));
274        } else {
275            // Default to returning * for convenience
276            sql.push_str(" RETURNING *");
277        }
278
279        sql
280    }
281
282    /// Generate CREATE TABLE SQL.
283    fn to_create_table_sql(&self) -> String {
284        let mut sql = String::new();
285        sql.push_str("CREATE TABLE ");
286        sql.push_str(&self.table);
287        sql.push_str(" (\n");
288
289        let mut defs = Vec::new();
290        for col in &self.columns {
291            if let Column::Def {
292                name,
293                data_type,
294                constraints,
295            } = col
296            {
297                let sql_type = map_type(data_type);
298                let mut line = format!("    {} {}", name, sql_type);
299
300                // Default to NOT NULL unless Nullable (?) constraint is present
301                let is_nullable = constraints.contains(&Constraint::Nullable);
302                if !is_nullable {
303                    line.push_str(" NOT NULL");
304                }
305
306                if constraints.contains(&Constraint::PrimaryKey) {
307                    line.push_str(" PRIMARY KEY");
308                }
309                if constraints.contains(&Constraint::Unique) {
310                    line.push_str(" UNIQUE");
311                }
312                defs.push(line);
313            }
314        }
315        sql.push_str(&defs.join(",\n"));
316        sql.push_str("\n)");
317        sql
318    }
319
320    /// Generate ALTER TABLE SQL.
321    fn to_alter_table_sql(&self) -> String {
322        let mut stmts = Vec::new();
323        for col in &self.columns {
324            match col {
325                Column::Mod { kind, col } => {
326                    match kind {
327                        ModKind::Add => {
328                            if let Column::Def { name, data_type, constraints } = col.as_ref() {
329                                let sql_type = map_type(data_type);
330                                let mut line = format!("ALTER TABLE {} ADD COLUMN {} {}", self.table, name, sql_type);
331                                
332                                let is_nullable = constraints.contains(&Constraint::Nullable);
333                                if !is_nullable {
334                                    line.push_str(" NOT NULL");
335                                }
336
337                                if constraints.contains(&Constraint::Unique) {
338                                    line.push_str(" UNIQUE");
339                                }
340                                stmts.push(line);
341                            }
342                        }
343                        ModKind::Drop => {
344                            if let Column::Named(name) = col.as_ref() {
345                                stmts.push(format!("ALTER TABLE {} DROP COLUMN {}", self.table, name));
346                            }
347                        }
348                    }
349                }
350                _ => {}
351            }
352        }
353        stmts.join(";\n")
354    }
355
356    /// Generate Window Function SQL (Pillar 8).
357    fn to_window_sql(&self) -> String {
358        // Build SELECT with window function columns
359        let mut sql = String::from("SELECT ");
360
361        let cols: Vec<String> = self.columns.iter().map(|c| {
362            match c {
363                Column::Window { name, func, params, partition, order } => {
364                    let params_str = if params.is_empty() {
365                        String::new()
366                    } else {
367                        params.iter().map(|v| v.to_string()).collect::<Vec<_>>().join(", ")
368                    };
369                    
370                    let mut over_clause = String::from("OVER (");
371                    if !partition.is_empty() {
372                        over_clause.push_str("PARTITION BY ");
373                        over_clause.push_str(&partition.join(", "));
374                        if !order.is_empty() {
375                            over_clause.push(' ');
376                        }
377                    }
378                    if !order.is_empty() {
379                        over_clause.push_str("ORDER BY ");
380                        let order_parts: Vec<String> = order.iter().map(|cage| {
381                            match &cage.kind {
382                                CageKind::Sort(SortOrder::Asc) => {
383                                    if let Some(cond) = cage.conditions.first() {
384                                        format!("{} ASC", cond.column)
385                                    } else {
386                                        String::new()
387                                    }
388                                }
389                                CageKind::Sort(SortOrder::Desc) => {
390                                    if let Some(cond) = cage.conditions.first() {
391                                        format!("{} DESC", cond.column)
392                                    } else {
393                                        String::new()
394                                    }
395                                }
396                                _ => String::new(),
397                            }
398                        }).filter(|s| !s.is_empty()).collect();
399                        over_clause.push_str(&order_parts.join(", "));
400                    }
401                    over_clause.push(')');
402                    
403                    format!("{}({}) {} AS {}", func, params_str, over_clause, name)
404                }
405                _ => c.to_string(),
406            }
407        }).collect();
408
409        sql.push_str(&cols.join(", "));
410        sql.push_str(" FROM ");
411        sql.push_str(&self.table);
412
413        // Handle cages (WHERE, LIMIT, etc.)
414        let where_clauses: Vec<String> = self.cages.iter()
415            .filter(|c| matches!(c.kind, CageKind::Filter))
416            .flat_map(|c| c.conditions.iter().map(|cond| cond.to_sql()))
417            .collect();
418
419        if !where_clauses.is_empty() {
420            sql.push_str(" WHERE ");
421            sql.push_str(&where_clauses.join(" AND "));
422        }
423
424        sql
425    }
426
427    /// Generate CTE SQL (Pillar 8).
428    fn to_cte_sql(&self) -> String {
429        // For WITH, the table is the CTE name. We generate: WITH cte_name AS (...) SELECT * FROM cte_name
430        // The inner query comes from cages (filter/limit). This is a stub—full impl needs chained queries.
431        let mut sql = String::from("WITH ");
432        sql.push_str(&self.table);
433        sql.push_str(" AS (");
434
435        // Generate inner SELECT from columns and cages
436        sql.push_str("SELECT ");
437        if self.columns.is_empty() {
438            sql.push('*');
439        } else {
440            let cols: Vec<String> = self.columns.iter().map(|c| c.to_string()).collect();
441            sql.push_str(&cols.join(", "));
442        }
443        sql.push_str(" FROM ");
444        sql.push_str(&self.table);
445
446        // Cages for inner query
447        let where_clauses: Vec<String> = self.cages.iter()
448            .filter(|c| matches!(c.kind, CageKind::Filter))
449            .flat_map(|c| c.conditions.iter().map(|cond| cond.to_sql()))
450            .collect();
451        if !where_clauses.is_empty() {
452            sql.push_str(" WHERE ");
453            sql.push_str(&where_clauses.join(" AND "));
454        }
455        
456        for cage in &self.cages {
457            if let CageKind::Limit(n) = cage.kind {
458                sql.push_str(&format!(" LIMIT {}", n));
459            }
460        }
461
462        sql.push_str(") SELECT * FROM ");
463        sql.push_str(&self.table);
464
465        sql
466    }
467}
468
469fn map_type(t: &str) -> &str {
470    match t {
471        "str" | "text" | "string" => "VARCHAR(255)",
472        "int" | "i32" => "INT",
473        "bigint" | "i64" => "BIGINT",
474        "uuid" => "UUID",
475        "bool" | "boolean" => "BOOLEAN",
476        "dec" | "decimal" => "DECIMAL",
477        "float" | "f64" => "DOUBLE PRECISION",
478        "serial" => "SERIAL",
479        "timestamp" | "time" => "TIMESTAMP",
480        "json" | "jsonb" => "JSONB",
481        _ => t,
482    }
483}
484
485impl Condition {
486    /// Convert condition to SQL string.
487    fn to_sql(&self) -> String {
488        // Handle array unnest conditions: EXISTS (SELECT 1 FROM unnest(col) _el WHERE _el OP val)
489        if self.is_array_unnest {
490            let inner_condition = match self.op {
491                Operator::Eq => format!("_el = {}", self.value),
492                Operator::Ne => format!("_el != {}", self.value),
493                Operator::Gt => format!("_el > {}", self.value),
494                Operator::Gte => format!("_el >= {}", self.value),
495                Operator::Lt => format!("_el < {}", self.value),
496                Operator::Lte => format!("_el <= {}", self.value),
497                Operator::Fuzzy => {
498                    let val = match &self.value {
499                        Value::String(s) => format!("'%{}%'", s),
500                        Value::Param(n) => format!("'%' || ${} || '%'", n),
501                        v => format!("'%{}%'", v),
502                    };
503                    format!("_el ILIKE {}", val)
504                }
505                _ => format!("_el = {}", self.value),
506            };
507            return format!(
508                "EXISTS (SELECT 1 FROM unnest({}) _el WHERE {})",
509                self.column, inner_condition
510            );
511        }
512        
513        // Normal conditions
514        match self.op {
515            Operator::Eq => format!("{} = {}", self.column, self.value),
516            Operator::Ne => format!("{} != {}", self.column, self.value),
517            Operator::Gt => format!("{} > {}", self.column, self.value),
518            Operator::Gte => format!("{} >= {}", self.column, self.value),
519            Operator::Lt => format!("{} < {}", self.column, self.value),
520            Operator::Lte => format!("{} <= {}", self.column, self.value),
521            Operator::Fuzzy => {
522                // For fuzzy match, wrap the value in %...%
523                let val = match &self.value {
524                    Value::String(s) => format!("'%{}%'", s),
525                    Value::Param(n) => format!("'%' || ${} || '%'", n),
526                    v => format!("'%{}%'", v),
527                };
528                format!("{} ILIKE {}", self.column, val)
529            }
530            Operator::In => format!("{} = ANY({})", self.column, self.value),
531            Operator::NotIn => format!("{} != ALL({})", self.column, self.value),
532            Operator::IsNull => format!("{} IS NULL", self.column),
533            Operator::IsNotNull => format!("{} IS NOT NULL", self.column),
534        }
535    }
536}
537
538
539#[cfg(test)]
540mod tests {
541    use super::*;
542    use crate::parser::parse;
543
544    #[test]
545    fn test_simple_select() {
546        let cmd = parse("get::users:'_").unwrap();
547        assert_eq!(cmd.to_sql(), "SELECT * FROM users");
548    }
549
550    #[test]
551    fn test_select_columns() {
552        let cmd = parse("get::users:'id'email'role").unwrap();
553        assert_eq!(cmd.to_sql(), "SELECT id, email, role FROM users");
554    }
555
556    #[test]
557    fn test_select_with_where() {
558        let cmd = parse("get::users:'_[active=true]").unwrap();
559        assert_eq!(cmd.to_sql(), "SELECT * FROM users WHERE active = true");
560    }
561
562    #[test]
563    fn test_select_with_limit() {
564        let cmd = parse("get::users:'_[lim=10]").unwrap();
565        assert_eq!(cmd.to_sql(), "SELECT * FROM users LIMIT 10");
566    }
567
568    #[test]
569    fn test_select_with_order() {
570        let cmd = parse("get::users:'_[^!created_at]").unwrap();
571        assert_eq!(cmd.to_sql(), "SELECT * FROM users ORDER BY created_at DESC");
572    }
573
574    #[test]
575    fn test_select_complex() {
576        let cmd = parse("get::users:'id'email[active=true][^!created_at][lim=10]").unwrap();
577        assert_eq!(
578            cmd.to_sql(),
579            "SELECT id, email FROM users WHERE active = true ORDER BY created_at DESC LIMIT 10"
580        );
581    }
582
583    #[test]
584    fn test_update() {
585        let cmd = parse("set::users:[verified=true][id=$1]").unwrap();
586        assert_eq!(cmd.to_sql(), "UPDATE users SET verified = true WHERE id = $1");
587    }
588
589    #[test]
590    fn test_delete() {
591        let cmd = parse("del::sessions:[expired_at<now]").unwrap();
592        // Note: 'now' is parsed as a function
593        assert_eq!(cmd.to_sql(), "DELETE FROM sessions WHERE expired_at < now()");
594    }
595
596    #[test]
597    fn test_fuzzy_match() {
598        let cmd = parse("get::users:'_[name~$1]").unwrap();
599        assert_eq!(cmd.to_sql(), "SELECT * FROM users WHERE name ILIKE '%' || $1 || '%'");
600    }
601
602    #[test]
603    fn test_or_conditions() {
604        let cmd = parse("get::users:'_[role=admin|role=mod]").unwrap();
605        assert_eq!(cmd.to_sql(), "SELECT * FROM users WHERE (role = 'admin' OR role = 'mod')");
606    }
607
608    #[test]
609    fn test_mixed_and_or() {
610        let cmd = parse("get::users:'_[active=true][role=admin|role=mod]").unwrap();
611        assert_eq!(cmd.to_sql(), "SELECT * FROM users WHERE active = true AND (role = 'admin' OR role = 'mod')");
612    }
613
614    #[test]
615    fn test_array_unnest() {
616        let cmd = parse("get::posts:'_[tags[*]~$1]").unwrap();
617        assert_eq!(cmd.to_sql(), "SELECT * FROM posts WHERE EXISTS (SELECT 1 FROM unnest(tags) _el WHERE _el ILIKE '%' || $1 || '%')");
618    }
619
620    #[test]
621    fn test_complex_array_search() {
622        let cmd = parse("get::kb:'_[active=true][keywords[*]~$1|question~$1]").unwrap();
623        assert_eq!(
624            cmd.to_sql(),
625            "SELECT * FROM kb WHERE active = true AND (EXISTS (SELECT 1 FROM unnest(keywords) _el WHERE _el ILIKE '%' || $1 || '%') OR question ILIKE '%' || $1 || '%')"
626        );
627    }
628
629    #[test]
630    fn test_offset_pagination() {
631        let cmd = parse("get::users:'_[lim=10][off=20]").unwrap();
632        assert_eq!(cmd.to_sql(), "SELECT * FROM users LIMIT 10 OFFSET 20");
633    }
634
635    #[test]
636    fn test_insert_returning() {
637        let cmd = parse("add::users:'id'email[name=John]").unwrap();
638        let sql = cmd.to_sql();
639        assert!(sql.contains("RETURNING"));
640    }
641
642    #[test]
643    fn test_escape_reserved_words() {
644        assert_eq!(escape_identifier("users"), "users");
645        assert_eq!(escape_identifier("order"), "\"order\"");
646        assert_eq!(escape_identifier("group"), "\"group\"");
647        assert_eq!(escape_identifier("user"), "\"user\"");
648    }
649
650    #[test]
651    fn test_left_join() {
652        let cmd = parse("get::users<-profiles:'id'name").unwrap();
653        let sql = cmd.to_sql();
654        assert!(sql.contains("LEFT JOIN"));
655    }
656
657    #[test]
658    fn test_right_join() {
659        let cmd = parse("get::users->>profiles:'id'name").unwrap();
660        let sql = cmd.to_sql();
661        assert!(sql.contains("RIGHT JOIN"));
662    }
663
664    #[test]
665    fn test_distinct() {
666        let cmd = parse("get!::users:'role").unwrap();
667        assert!(cmd.distinct);
668        assert_eq!(cmd.to_sql(), "SELECT DISTINCT role FROM users");
669    }
670}
671
qail_core/transpiler.rs

qail_core/
transpiler.rs
