mdql_core/
executor.rs

1//! Unified SQL execution — single entry point for CLI, REPL, and web server.
2
3use std::path::Path;
4
5use crate::api::Table;
6use crate::cascade;
7use crate::database::{ViewDef, is_database_dir, load_database_config, save_database_config};
8use crate::errors::{MdqlError, ValidationError};
9use crate::model::Row;
10use crate::query_ast::DeleteMode;
11use crate::query_engine::{execute_join_query, execute_query};
12use crate::query_parser::{Statement, parse_query};
13
14#[derive(Debug)]
15pub enum QueryResult {
16    Rows { rows: Vec<Row>, columns: Vec<String> },
17    Message(String),
18}
19
20pub fn execute(path: &Path, sql: &str) -> crate::errors::Result<(QueryResult, Vec<ValidationError>)> {
21    let stmt = parse_query(sql)?;
22    let is_db = is_database_dir(path);
23
24    match stmt {
25        Statement::Select(ref q) if !q.ctes.is_empty() => {
26            if !is_db {
27                return Err(MdqlError::QueryExecution(
28                    "CTEs (WITH) require a database directory".into(),
29                ));
30            }
31            let (_config, mut tables, errors) = crate::loader::load_database(path)?;
32            for cte in &q.ctes {
33                let (rows, cols) = materialize_cte(&cte.query, &tables)?;
34                let schema = crate::loader::build_view_schema(&cte.name, &cols, &rows);
35                tables.insert(cte.name.clone(), (schema, rows));
36            }
37            let (rows, cols) = if !q.joins.is_empty() {
38                execute_join_query(q, &tables)?
39            } else {
40                let (schema, rows) = tables.get(&q.table).ok_or_else(|| {
41                    MdqlError::QueryExecution(format!(
42                        "table '{}' not found in database",
43                        q.table
44                    ))
45                })?;
46                execute_query(q, rows, schema)?
47            };
48            Ok((QueryResult::Rows { rows, columns: cols }, errors))
49        }
50        Statement::Select(ref q) => {
51            if q.subquery.is_some() || !q.joins.is_empty() || is_db {
52                let (_config, tables, errors) = crate::loader::load_database(path)?;
53                let (rows, cols) = if let Some(ref sub) = q.subquery {
54                    let source_table = &sub.table;
55                    let (schema, table_rows) = tables.get(source_table).ok_or_else(|| {
56                        MdqlError::QueryExecution(format!(
57                            "table '{}' not found in database",
58                            source_table
59                        ))
60                    })?;
61                    execute_query(q, table_rows, schema)?
62                } else if !q.joins.is_empty() {
63                    execute_join_query(q, &tables)?
64                } else {
65                    let (schema, rows) = tables.get(&q.table).ok_or_else(|| {
66                        MdqlError::QueryExecution(format!(
67                            "table '{}' not found in database",
68                            q.table
69                        ))
70                    })?;
71                    execute_query(q, rows, schema)?
72                };
73                Ok((QueryResult::Rows { rows, columns: cols }, errors))
74            } else {
75                let (schema, rows, errors) = crate::loader::load_table(path)?;
76                let (rows, cols) = execute_query(q, &rows, &schema)?;
77                Ok((QueryResult::Rows { rows, columns: cols }, errors))
78            }
79        }
80        Statement::CreateView(ref cv) => {
81            if !is_db {
82                return Err(MdqlError::QueryExecution(
83                    "CREATE VIEW requires a database directory".into(),
84                ));
85            }
86            let mut config = load_database_config(path)?;
87
88            let (_config_check, tables, _errors) = crate::loader::load_database(path)?;
89            if tables.contains_key(&cv.view_name) {
90                return Err(MdqlError::QueryExecution(format!(
91                    "Name '{}' already exists as a table or view",
92                    cv.view_name
93                )));
94            }
95
96            if config.views.iter().any(|v| v.name == cv.view_name) {
97                return Err(MdqlError::QueryExecution(format!(
98                    "View '{}' already exists",
99                    cv.view_name
100                )));
101            }
102
103            let query_str = extract_view_query(sql)?;
104
105            let view_def = ViewDef {
106                name: cv.view_name.clone(),
107                query: query_str,
108            };
109
110            let test_result = crate::loader::load_database(path);
111            if let Ok((_cfg, test_tables, _errs)) = test_result {
112                let test_view = ViewDef {
113                    name: view_def.name.clone(),
114                    query: view_def.query.clone(),
115                };
116                if let Err(e) = super::loader::materialize_view(&test_view, &test_tables) {
117                    return Err(MdqlError::QueryExecution(format!(
118                        "View query failed validation: {}",
119                        e
120                    )));
121                }
122            }
123
124            config.views.push(view_def);
125            save_database_config(path, &config)?;
126            Ok((
127                QueryResult::Message(format!("View '{}' created", cv.view_name)),
128                vec![],
129            ))
130        }
131        Statement::DropView(ref dv) => {
132            if !is_db {
133                return Err(MdqlError::QueryExecution(
134                    "DROP VIEW requires a database directory".into(),
135                ));
136            }
137            let mut config = load_database_config(path)?;
138            let len_before = config.views.len();
139            config.views.retain(|v| v.name != dv.view_name);
140            if config.views.len() == len_before {
141                return Err(MdqlError::QueryExecution(format!(
142                    "View '{}' does not exist",
143                    dv.view_name
144                )));
145            }
146            save_database_config(path, &config)?;
147            Ok((
148                QueryResult::Message(format!("View '{}' dropped", dv.view_name)),
149                vec![],
150            ))
151        }
152        Statement::Delete(ref dq) if dq.mode != DeleteMode::Default => {
153            if !is_db {
154                return Err(MdqlError::QueryExecution(
155                    "CASCADE/RESTRICT requires a database directory".into(),
156                ));
157            }
158            let config = load_database_config(path)?;
159            if config.views.iter().any(|v| v.name == dq.table) {
160                return Err(MdqlError::QueryExecution(format!(
161                    "Cannot write to view '{}' — views are read-only",
162                    dq.table
163                )));
164            }
165            let (_cfg, tables, errors) = crate::loader::load_database(path)?;
166            let (_, rows) = tables.get(&dq.table).ok_or_else(|| {
167                MdqlError::QueryExecution(format!("table '{}' not found in database", dq.table))
168            })?;
169            let matched_filenames: Vec<String> = if let Some(ref wc) = dq.where_clause {
170                rows.iter()
171                    .filter(|r| crate::query_engine::evaluate(wc, r))
172                    .filter_map(|r| r.get("path").and_then(|v| v.as_str()).map(|s| s.to_string()))
173                    .collect()
174            } else {
175                rows.iter()
176                    .filter_map(|r| r.get("path").and_then(|v| v.as_str()).map(|s| s.to_string()))
177                    .collect()
178            };
179
180            match dq.mode {
181                DeleteMode::Cascade => {
182                    let plan = cascade::build_cascade_plan(
183                        &dq.table, &matched_filenames, &config, &tables,
184                    );
185                    let msg = cascade::execute_cascade_plan(&plan, path)?;
186                    Ok((QueryResult::Message(msg), errors))
187                }
188                DeleteMode::Restrict => {
189                    let plan = cascade::build_restrict_plan(
190                        &dq.table, &matched_filenames, &config, &tables,
191                    );
192                    if !plan.restrict_violations.is_empty() {
193                        let violations = plan.restrict_violations.join("\n  ");
194                        return Err(MdqlError::QueryExecution(format!(
195                            "RESTRICT: cannot delete — {} dependent references:\n  {}",
196                            plan.restrict_violations.len(),
197                            violations,
198                        )));
199                    }
200                    let table_path = path.join(&dq.table);
201                    let table = Table::new(&table_path)?;
202                    let msg = table.exec_delete_matched(&matched_filenames)?;
203                    Ok((QueryResult::Message(msg), errors))
204                }
205                DeleteMode::Default => unreachable!(),
206            }
207        }
208        ref stmt @ (Statement::Insert(_)
209        | Statement::Update(_)
210        | Statement::Delete(_)
211        | Statement::AlterRename(_)
212        | Statement::AlterDrop(_)
213        | Statement::AlterMerge(_)) => {
214            if is_db {
215                let config = load_database_config(path)?;
216                let target = stmt.table_name();
217                if config.views.iter().any(|v| v.name == target) {
218                    return Err(MdqlError::QueryExecution(format!(
219                        "Cannot write to view '{}' — views are read-only",
220                        target
221                    )));
222                }
223            }
224            let table_path = if is_db {
225                path.join(stmt.table_name())
226            } else {
227                path.to_path_buf()
228            };
229            let mut table = Table::new(&table_path)?;
230            let msg = table.execute_sql(sql)?;
231            Ok((QueryResult::Message(msg), vec![]))
232        }
233    }
234}
235
236pub fn materialize_cte(
237    query: &crate::query_ast::SelectQuery,
238    tables: &std::collections::HashMap<String, (crate::schema::Schema, Vec<Row>)>,
239) -> crate::errors::Result<(Vec<Row>, Vec<String>)> {
240    if let Some(ref sub) = query.subquery {
241        let (_, sub_rows) = tables.get(&sub.table).ok_or_else(|| {
242            MdqlError::QueryExecution(format!("table '{}' not found in database", sub.table))
243        })?;
244        let (sub_rows, _) = execute_query(sub, sub_rows, &tables.get(&sub.table).unwrap().0)?;
245        execute_query(query, &sub_rows, &tables.get(&sub.table).unwrap().0)
246    } else if !query.joins.is_empty() {
247        execute_join_query(query, tables)
248    } else {
249        let (schema, rows) = tables.get(&query.table).ok_or_else(|| {
250            MdqlError::QueryExecution(format!("table '{}' not found in database", query.table))
251        })?;
252        execute_query(query, rows, schema)
253    }
254}
255
256fn extract_view_query(sql: &str) -> crate::errors::Result<String> {
257    let upper = sql.to_uppercase();
258    let as_keyword = upper.find(" AS ");
259    if let Some(pos) = as_keyword {
260        let after = &sql[pos + 4..];
261        let trimmed = after.trim_start();
262        let trimmed_upper = trimmed.to_uppercase();
263        if trimmed_upper.starts_with("SELECT") {
264            return Ok(trimmed.to_string());
265        }
266    }
267    // Fallback: scan for any whitespace-surrounded AS that precedes SELECT
268    let bytes = upper.as_bytes();
269    let mut i = 0;
270    while i + 4 < bytes.len() {
271        if bytes[i].is_ascii_whitespace()
272            && bytes[i + 1] == b'A'
273            && bytes[i + 2] == b'S'
274            && bytes[i + 3].is_ascii_whitespace()
275        {
276            let after = &sql[i + 3..];
277            let trimmed = after.trim_start();
278            let trimmed_upper = trimmed.to_uppercase();
279            if trimmed_upper.starts_with("SELECT") {
280                return Ok(trimmed.to_string());
281            }
282        }
283        i += 1;
284    }
285    Err(crate::errors::MdqlError::QueryExecution(
286        "CREATE VIEW must contain AS clause followed by SELECT".into(),
287    ))
288}
289
290#[cfg(test)]
291mod tests {
292    use super::*;
293    use crate::model::Value;
294    use std::fs;
295
296    fn make_test_db() -> tempfile::TempDir {
297        let dir = tempfile::tempdir().unwrap();
298
299        // Database-level _mdql.md
300        fs::write(
301            dir.path().join("_mdql.md"),
302            "---\ntype: database\nname: testdb\n---\n",
303        )
304        .unwrap();
305
306        // Table: strategies
307        let strats = dir.path().join("strategies");
308        fs::create_dir(&strats).unwrap();
309        fs::write(
310            strats.join("_mdql.md"),
311            "---\ntype: schema\ntable: strategies\nprimary_key: path\nfrontmatter:\n  title:\n    type: string\n  status:\n    type: string\n---\n",
312        )
313        .unwrap();
314        fs::write(
315            strats.join("alpha.md"),
316            "---\ntitle: Alpha\nstatus: LIVE\n---\n# Alpha\n",
317        )
318        .unwrap();
319        fs::write(
320            strats.join("beta.md"),
321            "---\ntitle: Beta\nstatus: DRAFT\n---\n# Beta\n",
322        )
323        .unwrap();
324
325        dir
326    }
327
328    #[test]
329    fn test_create_and_query_view() {
330        let dir = make_test_db();
331        let (result, _) = execute(
332            dir.path(),
333            "CREATE VIEW live AS SELECT * FROM strategies WHERE status = 'LIVE'",
334        )
335        .unwrap();
336        assert!(matches!(result, QueryResult::Message(ref m) if m.contains("created")));
337
338        let (result, _) = execute(dir.path(), "SELECT * FROM live").unwrap();
339        if let QueryResult::Rows { rows, columns } = result {
340            assert_eq!(rows.len(), 1);
341            assert!(columns.contains(&"title".to_string()));
342        } else {
343            panic!("Expected Rows");
344        }
345    }
346
347    #[test]
348    fn test_drop_view() {
349        let dir = make_test_db();
350        execute(
351            dir.path(),
352            "CREATE VIEW live AS SELECT * FROM strategies WHERE status = 'LIVE'",
353        )
354        .unwrap();
355
356        let (result, _) = execute(dir.path(), "DROP VIEW live").unwrap();
357        assert!(matches!(result, QueryResult::Message(ref m) if m.contains("dropped")));
358
359        let err = execute(dir.path(), "SELECT * FROM live");
360        assert!(err.is_err());
361    }
362
363    #[test]
364    fn test_drop_nonexistent_view() {
365        let dir = make_test_db();
366        let err = execute(dir.path(), "DROP VIEW nonexistent");
367        assert!(err.is_err());
368        assert!(err.unwrap_err().to_string().contains("does not exist"));
369    }
370
371    #[test]
372    fn test_create_view_duplicate_name() {
373        let dir = make_test_db();
374        execute(
375            dir.path(),
376            "CREATE VIEW live AS SELECT * FROM strategies WHERE status = 'LIVE'",
377        )
378        .unwrap();
379
380        let err = execute(
381            dir.path(),
382            "CREATE VIEW live AS SELECT * FROM strategies",
383        );
384        assert!(err.is_err());
385        assert!(err.unwrap_err().to_string().contains("already exists"));
386    }
387
388    #[test]
389    fn test_create_view_conflicts_with_table() {
390        let dir = make_test_db();
391        let err = execute(
392            dir.path(),
393            "CREATE VIEW strategies AS SELECT * FROM strategies",
394        );
395        assert!(err.is_err());
396        assert!(err.unwrap_err().to_string().contains("already exists"));
397    }
398
399    #[test]
400    fn test_write_to_view_rejected() {
401        let dir = make_test_db();
402        execute(
403            dir.path(),
404            "CREATE VIEW live AS SELECT * FROM strategies WHERE status = 'LIVE'",
405        )
406        .unwrap();
407
408        let err = execute(
409            dir.path(),
410            "INSERT INTO live (title, status) VALUES ('Gamma', 'LIVE')",
411        );
412        assert!(err.is_err());
413        assert!(err.unwrap_err().to_string().contains("read-only"));
414    }
415
416    #[test]
417    fn test_create_view_not_database() {
418        let dir = tempfile::tempdir().unwrap();
419        fs::write(
420            dir.path().join("_mdql.md"),
421            "---\ntype: schema\ntable: t\nprimary_key: path\nfrontmatter:\n  x:\n    type: string\n---\n",
422        )
423        .unwrap();
424
425        let err = execute(
426            dir.path(),
427            "CREATE VIEW v AS SELECT * FROM t",
428        );
429        assert!(err.is_err());
430        assert!(err.unwrap_err().to_string().contains("database directory"));
431    }
432
433    #[test]
434    fn test_extract_view_query_basic() {
435        let q = extract_view_query("CREATE VIEW v AS SELECT * FROM t").unwrap();
436        assert!(q.starts_with("SELECT"));
437    }
438
439    #[test]
440    fn test_extract_view_query_with_column_alias() {
441        let q = extract_view_query(
442            "CREATE VIEW v AS SELECT token, SUM(size) as sell_size FROM orders GROUP BY token HAVING sell_size > 0"
443        ).unwrap();
444        assert!(q.starts_with("SELECT"));
445        assert!(q.contains("HAVING"));
446    }
447
448    #[test]
449    fn test_extract_view_query_newline_after_as() {
450        let q = extract_view_query("CREATE VIEW v AS\nSELECT * FROM t").unwrap();
451        assert!(q.starts_with("SELECT"));
452    }
453
454    #[test]
455    fn test_create_view_with_aggregate_arithmetic() {
456        let dir = make_test_db();
457        let result = execute(
458            dir.path(),
459            "CREATE VIEW v AS SELECT status, COUNT(*) - COUNT(*) as zero FROM strategies GROUP BY status",
460        );
461        assert!(result.is_ok());
462    }
463
464    // ── Issue #44: HAVING in CREATE VIEW ──
465
466    #[test]
467    fn test_create_view_with_having() {
468        let dir = make_test_db();
469        // Create a view with HAVING — both statuses have cnt=1, so HAVING cnt > 0 keeps both
470        let (result, _) = execute(
471            dir.path(),
472            "CREATE VIEW popular AS SELECT status, COUNT(*) as cnt FROM strategies GROUP BY status HAVING cnt > 0",
473        )
474        .unwrap();
475        assert!(matches!(result, QueryResult::Message(ref m) if m.contains("created")));
476
477        // Query the view to confirm it works
478        let (result, _) = execute(dir.path(), "SELECT * FROM popular").unwrap();
479        if let QueryResult::Rows { rows, columns } = result {
480            assert!(columns.contains(&"status".to_string()));
481            assert!(columns.contains(&"cnt".to_string()));
482            // Both LIVE and DRAFT have count 1, both > 0
483            assert_eq!(rows.len(), 2);
484        } else {
485            panic!("Expected Rows, got {:?}", result);
486        }
487    }
488
489    #[test]
490    fn test_extract_view_query_tab_after_as() {
491        let q = extract_view_query("CREATE VIEW v AS\tSELECT * FROM t").unwrap();
492        assert!(q.starts_with("SELECT"));
493        assert!(q.contains("FROM t"));
494    }
495
496    fn make_join_db() -> tempfile::TempDir {
497        let dir = tempfile::tempdir().unwrap();
498        fs::write(
499            dir.path().join("_mdql.md"),
500            "---\ntype: database\nname: testdb\n---\n",
501        )
502        .unwrap();
503
504        let strats = dir.path().join("strategies");
505        fs::create_dir(&strats).unwrap();
506        fs::write(
507            strats.join("_mdql.md"),
508            "---\ntype: schema\ntable: strategies\nprimary_key: path\nfrontmatter:\n  title:\n    type: string\n---\n",
509        )
510        .unwrap();
511        fs::write(strats.join("alpha.md"), "---\ntitle: Alpha\n---\n# Alpha\n").unwrap();
512        fs::write(strats.join("beta.md"), "---\ntitle: Beta\n---\n# Beta\n").unwrap();
513        fs::write(strats.join("gamma.md"), "---\ntitle: Gamma\n---\n# Gamma\n").unwrap();
514
515        let bt = dir.path().join("backtests");
516        fs::create_dir(&bt).unwrap();
517        fs::write(
518            bt.join("_mdql.md"),
519            "---\ntype: schema\ntable: backtests\nprimary_key: path\nfrontmatter:\n  strategy:\n    type: string\n  sharpe:\n    type: float\n---\n",
520        )
521        .unwrap();
522        fs::write(bt.join("bt-alpha.md"), "---\nstrategy: alpha.md\nsharpe: 1.5\n---\n# BT Alpha\n").unwrap();
523
524        dir
525    }
526
527    #[test]
528    fn test_inner_join() {
529        let dir = make_join_db();
530        let (result, _) = execute(
531            dir.path(),
532            "SELECT s.title, b.sharpe FROM strategies s JOIN backtests b ON b.strategy = s.path",
533        )
534        .unwrap();
535        if let QueryResult::Rows { rows, .. } = result {
536            assert_eq!(rows.len(), 1);
537            assert_eq!(rows[0].get("s.title").unwrap(), &Value::String("Alpha".into()));
538        } else {
539            panic!("Expected Rows");
540        }
541    }
542
543    #[test]
544    fn test_left_join() {
545        let dir = make_join_db();
546        let (result, _) = execute(
547            dir.path(),
548            "SELECT s.title, b.sharpe FROM strategies s LEFT JOIN backtests b ON b.strategy = s.path",
549        )
550        .unwrap();
551        if let QueryResult::Rows { rows, .. } = result {
552            assert_eq!(rows.len(), 3);
553            let alpha = rows.iter().find(|r| r.get("s.title") == Some(&Value::String("Alpha".into()))).unwrap();
554            assert_eq!(alpha.get("b.sharpe"), Some(&Value::Float(1.5)));
555            let beta = rows.iter().find(|r| r.get("s.title") == Some(&Value::String("Beta".into()))).unwrap();
556            assert_eq!(beta.get("b.sharpe"), Some(&Value::Null));
557        } else {
558            panic!("Expected Rows");
559        }
560    }
561
562    #[test]
563    fn test_left_join_in_view() {
564        let dir = make_join_db();
565        execute(
566            dir.path(),
567            "CREATE VIEW overview AS SELECT s.title, b.sharpe FROM strategies s LEFT JOIN backtests b ON b.strategy = s.path",
568        )
569        .unwrap();
570        let (result, _) = execute(dir.path(), "SELECT * FROM overview").unwrap();
571        if let QueryResult::Rows { rows, .. } = result {
572            assert_eq!(rows.len(), 3);
573        } else {
574            panic!("Expected Rows");
575        }
576    }
577
578    fn make_compound_join_db() -> tempfile::TempDir {
579        let dir = tempfile::tempdir().unwrap();
580        fs::write(
581            dir.path().join("_mdql.md"),
582            "---\ntype: database\nname: testdb\n---\n",
583        )
584        .unwrap();
585
586        let strats = dir.path().join("strategies");
587        fs::create_dir(&strats).unwrap();
588        fs::write(
589            strats.join("_mdql.md"),
590            "---\ntype: schema\ntable: strategies\nprimary_key: path\nfrontmatter:\n  title:\n    type: string\n---\n",
591        )
592        .unwrap();
593        fs::write(strats.join("alpha.md"), "---\ntitle: Alpha\n---\n# Alpha\n").unwrap();
594        fs::write(strats.join("beta.md"), "---\ntitle: Beta\n---\n# Beta\n").unwrap();
595
596        let bt = dir.path().join("backtests");
597        fs::create_dir(&bt).unwrap();
598        fs::write(
599            bt.join("_mdql.md"),
600            "---\ntype: schema\ntable: backtests\nprimary_key: path\nfrontmatter:\n  strategy:\n    type: string\n  mode:\n    type: string\n  sharpe:\n    type: float\n---\n",
601        )
602        .unwrap();
603        fs::write(bt.join("bt-alpha-paper.md"), "---\nstrategy: alpha.md\nmode: PAPER\nsharpe: 1.5\n---\n# BT\n").unwrap();
604        fs::write(bt.join("bt-alpha-live.md"), "---\nstrategy: alpha.md\nmode: LIVE\nsharpe: 1.2\n---\n# BT\n").unwrap();
605        fs::write(bt.join("bt-beta-paper.md"), "---\nstrategy: beta.md\nmode: PAPER\nsharpe: 0.8\n---\n# BT\n").unwrap();
606
607        dir
608    }
609
610    #[test]
611    fn test_join_compound_and() {
612        let dir = make_compound_join_db();
613        let (result, _) = execute(
614            dir.path(),
615            "SELECT s.title, b.sharpe FROM strategies s JOIN backtests b ON b.strategy = s.path AND b.mode = 'PAPER'",
616        )
617        .unwrap();
618        if let QueryResult::Rows { rows, .. } = result {
619            assert_eq!(rows.len(), 2);
620            let alpha = rows.iter().find(|r| r.get("s.title") == Some(&Value::String("Alpha".into()))).unwrap();
621            assert_eq!(alpha.get("b.sharpe"), Some(&Value::Float(1.5)));
622            let beta = rows.iter().find(|r| r.get("s.title") == Some(&Value::String("Beta".into()))).unwrap();
623            assert_eq!(beta.get("b.sharpe"), Some(&Value::Float(0.8)));
624        } else {
625            panic!("Expected Rows");
626        }
627    }
628
629    #[test]
630    fn test_left_join_compound() {
631        let dir = make_compound_join_db();
632        let (result, _) = execute(
633            dir.path(),
634            "SELECT s.title, b.sharpe FROM strategies s LEFT JOIN backtests b ON b.strategy = s.path AND b.mode = 'LIVE'",
635        )
636        .unwrap();
637        if let QueryResult::Rows { rows, .. } = result {
638            assert_eq!(rows.len(), 2);
639            let alpha = rows.iter().find(|r| r.get("s.title") == Some(&Value::String("Alpha".into()))).unwrap();
640            assert_eq!(alpha.get("b.sharpe"), Some(&Value::Float(1.2)));
641            let beta = rows.iter().find(|r| r.get("s.title") == Some(&Value::String("Beta".into()))).unwrap();
642            assert_eq!(beta.get("b.sharpe"), Some(&Value::Null));
643        } else {
644            panic!("Expected Rows");
645        }
646    }
647
648    #[test]
649    fn test_join_compound_with_comparison() {
650        let dir = make_compound_join_db();
651        let (result, _) = execute(
652            dir.path(),
653            "SELECT s.title, b.sharpe FROM strategies s JOIN backtests b ON b.strategy = s.path AND b.sharpe > 1.0",
654        )
655        .unwrap();
656        if let QueryResult::Rows { rows, .. } = result {
657            assert_eq!(rows.len(), 2);
658            assert!(rows.iter().all(|r| {
659                if let Some(Value::Float(s)) = r.get("b.sharpe") { *s > 1.0 } else { false }
660            }));
661        } else {
662            panic!("Expected Rows");
663        }
664    }
665
666    fn make_cascade_db() -> tempfile::TempDir {
667        let dir = tempfile::tempdir().unwrap();
668
669        fs::write(
670            dir.path().join("_mdql.md"),
671            "---\ntype: database\nname: testdb\nforeign_keys:\n  - from: backtests.strategy\n    to: strategies.path\n---\n",
672        )
673        .unwrap();
674
675        let strats = dir.path().join("strategies");
676        fs::create_dir(&strats).unwrap();
677        fs::write(
678            strats.join("_mdql.md"),
679            "---\ntype: schema\ntable: strategies\nprimary_key: path\nfrontmatter:\n  title:\n    type: string\n  status:\n    type: string\n---\n",
680        )
681        .unwrap();
682        fs::write(strats.join("alpha.md"), "---\ntitle: Alpha\nstatus: KILLED\n---\n# Alpha\n").unwrap();
683        fs::write(strats.join("beta.md"), "---\ntitle: Beta\nstatus: LIVE\n---\n# Beta\n").unwrap();
684
685        let bt = dir.path().join("backtests");
686        fs::create_dir(&bt).unwrap();
687        fs::write(
688            bt.join("_mdql.md"),
689            "---\ntype: schema\ntable: backtests\nprimary_key: path\nfrontmatter:\n  strategy:\n    type: string\n  sharpe:\n    type: float\n---\n",
690        )
691        .unwrap();
692        fs::write(bt.join("bt-alpha.md"), "---\nstrategy: alpha.md\nsharpe: 1.5\n---\n# BT Alpha\n").unwrap();
693        fs::write(bt.join("bt-beta.md"), "---\nstrategy: beta.md\nsharpe: 0.8\n---\n# BT Beta\n").unwrap();
694
695        dir
696    }
697
698    #[test]
699    fn test_cascade_delete() {
700        let dir = make_cascade_db();
701        let (result, _) = execute(
702            dir.path(),
703            "DELETE FROM strategies WHERE status = 'KILLED' CASCADE",
704        )
705        .unwrap();
706        if let QueryResult::Message(msg) = result {
707            assert!(msg.contains("DELETE 1"));
708            assert!(msg.contains("cascade"));
709        } else {
710            panic!("Expected Message");
711        }
712        assert!(!dir.path().join("strategies/alpha.md").exists());
713        assert!(!dir.path().join("backtests/bt-alpha.md").exists());
714        assert!(dir.path().join("strategies/beta.md").exists());
715        assert!(dir.path().join("backtests/bt-beta.md").exists());
716    }
717
718    #[test]
719    fn test_restrict_delete_blocks() {
720        let dir = make_cascade_db();
721        let err = execute(
722            dir.path(),
723            "DELETE FROM strategies WHERE status = 'KILLED' RESTRICT",
724        );
725        assert!(err.is_err());
726        let msg = err.unwrap_err().to_string();
727        assert!(msg.contains("RESTRICT"));
728        assert!(dir.path().join("strategies/alpha.md").exists());
729    }
730
731    #[test]
732    fn test_restrict_delete_allows_no_dependents() {
733        let dir = make_cascade_db();
734        fs::remove_file(dir.path().join("backtests/bt-alpha.md")).unwrap();
735
736        let (result, _) = execute(
737            dir.path(),
738            "DELETE FROM strategies WHERE status = 'KILLED' RESTRICT",
739        )
740        .unwrap();
741        if let QueryResult::Message(msg) = result {
742            assert!(msg.contains("DELETE 1"));
743        } else {
744            panic!("Expected Message");
745        }
746        assert!(!dir.path().join("strategies/alpha.md").exists());
747    }
748
749    #[test]
750    fn test_cascade_default_unchanged() {
751        let dir = make_cascade_db();
752        let (result, _) = execute(
753            dir.path(),
754            "DELETE FROM strategies WHERE status = 'KILLED'",
755        )
756        .unwrap();
757        if let QueryResult::Message(msg) = result {
758            assert!(msg.contains("DELETE 1"));
759        } else {
760            panic!("Expected Message");
761        }
762        assert!(!dir.path().join("strategies/alpha.md").exists());
763        assert!(dir.path().join("backtests/bt-alpha.md").exists());
764    }
765
766    // ── CTE (WITH) integration tests ──
767
768    #[test]
769    fn test_cte_basic() {
770        let dir = make_test_db();
771        let (result, _) = execute(
772            dir.path(),
773            "WITH live AS (SELECT * FROM strategies WHERE status = 'LIVE') SELECT * FROM live",
774        )
775        .unwrap();
776        if let QueryResult::Rows { rows, columns } = result {
777            assert_eq!(rows.len(), 1);
778            assert!(columns.contains(&"title".to_string()));
779            assert_eq!(rows[0].get("title"), Some(&Value::String("Alpha".into())));
780        } else {
781            panic!("Expected Rows");
782        }
783    }
784
785    #[test]
786    fn test_cte_with_where_on_cte() {
787        let dir = make_join_db();
788        let (result, _) = execute(
789            dir.path(),
790            "WITH bt AS (SELECT * FROM backtests WHERE sharpe > 1.0) SELECT * FROM bt",
791        )
792        .unwrap();
793        if let QueryResult::Rows { rows, .. } = result {
794            assert_eq!(rows.len(), 1);
795            assert_eq!(rows[0].get("sharpe"), Some(&Value::Float(1.5)));
796        } else {
797            panic!("Expected Rows");
798        }
799    }
800
801    #[test]
802    fn test_cte_multi_with_join() {
803        let dir = make_join_db();
804        let (result, _) = execute(
805            dir.path(),
806            "WITH s AS (SELECT * FROM strategies), b AS (SELECT * FROM backtests) SELECT s.title, b.sharpe FROM s JOIN b ON b.strategy = s.path",
807        )
808        .unwrap();
809        if let QueryResult::Rows { rows, .. } = result {
810            assert_eq!(rows.len(), 1);
811            assert_eq!(rows[0].get("s.title"), Some(&Value::String("Alpha".into())));
812        } else {
813            panic!("Expected Rows");
814        }
815    }
816
817    #[test]
818    fn test_cte_with_aggregation() {
819        let dir = make_test_db();
820        let (result, _) = execute(
821            dir.path(),
822            "WITH counts AS (SELECT status, COUNT(*) AS cnt FROM strategies GROUP BY status) SELECT * FROM counts WHERE cnt > 0",
823        )
824        .unwrap();
825        if let QueryResult::Rows { rows, columns } = result {
826            assert!(columns.contains(&"status".to_string()));
827            assert!(columns.contains(&"cnt".to_string()));
828            assert!(rows.len() >= 1);
829        } else {
830            panic!("Expected Rows");
831        }
832    }
833
834    #[test]
835    fn test_cte_chained() {
836        let dir = make_join_db();
837        let (result, _) = execute(
838            dir.path(),
839            "WITH good AS (SELECT * FROM backtests WHERE sharpe > 1.0), matched AS (SELECT s.title, g.sharpe FROM strategies s JOIN good g ON g.strategy = s.path) SELECT * FROM matched",
840        )
841        .unwrap();
842        if let QueryResult::Rows { rows, .. } = result {
843            assert_eq!(rows.len(), 1);
844        } else {
845            panic!("Expected Rows");
846        }
847    }
848}
mdql_core/executor.rs

mdql_core/
executor.rs
