sql_cli/
non_interactive.rs

1use anyhow::{Context, Result};
2use std::fs;
3use std::io::{self, Write};
4use std::path::Path;
5use std::time::Instant;
6use tracing::{debug, info};
7
8use crate::config::config::Config;
9use crate::data::data_view::DataView;
10use crate::data::datatable::{DataTable, DataValue};
11use crate::data::datatable_loaders::{load_csv_to_datatable, load_json_to_datatable};
12use crate::services::query_execution_service::QueryExecutionService;
13use crate::sql::script_parser::{ScriptParser, ScriptResult};
14
15/// Output format for query results
16#[derive(Debug, Clone)]
17pub enum OutputFormat {
18    Csv,
19    Json,
20    Table,
21    Tsv,
22}
23
24impl OutputFormat {
25    pub fn from_str(s: &str) -> Result<Self> {
26        match s.to_lowercase().as_str() {
27            "csv" => Ok(OutputFormat::Csv),
28            "json" => Ok(OutputFormat::Json),
29            "table" => Ok(OutputFormat::Table),
30            "tsv" => Ok(OutputFormat::Tsv),
31            _ => Err(anyhow::anyhow!(
32                "Invalid output format: {}. Use csv, json, table, or tsv",
33                s
34            )),
35        }
36    }
37}
38
39/// Configuration for non-interactive query execution
40pub struct NonInteractiveConfig {
41    pub data_file: String,
42    pub query: String,
43    pub output_format: OutputFormat,
44    pub output_file: Option<String>,
45    pub case_insensitive: bool,
46    pub auto_hide_empty: bool,
47    pub limit: Option<usize>,
48    pub query_plan: bool,
49    pub execution_plan: bool,
50    pub script_file: Option<String>, // Path to the script file for relative path resolution
51}
52
53/// Execute a query in non-interactive mode
54pub fn execute_non_interactive(config: NonInteractiveConfig) -> Result<()> {
55    let start_time = Instant::now();
56
57    // Check if query uses DUAL or has no FROM clause
58    use crate::sql::recursive_parser::{Parser, SelectStatement};
59
60    fn check_statement_for_range(stmt: &SelectStatement) -> bool {
61        // Check main query
62        if stmt.from_function.is_some() {
63            return true;
64        }
65
66        // Check if it's DUAL or no FROM
67        if stmt
68            .from_table
69            .as_ref()
70            .is_some_and(|t| t.to_uppercase() == "DUAL")
71        {
72            return true;
73        }
74
75        if stmt.from_table.is_none() && stmt.from_subquery.is_none() && stmt.from_function.is_none()
76        {
77            return true;
78        }
79
80        // Recursively check CTEs
81        for cte in &stmt.ctes {
82            if check_statement_for_range(&cte.query) {
83                return true;
84            }
85        }
86
87        // Check subqueries
88        if let Some(ref subquery) = stmt.from_subquery {
89            if check_statement_for_range(subquery) {
90                return true;
91            }
92        }
93
94        false
95    }
96
97    let mut parser = Parser::new(&config.query);
98    let statement = parser
99        .parse()
100        .map_err(|e| anyhow::anyhow!("Parse error: {}", e))?;
101
102    // 1. Load the data file or create DUAL table
103    let (data_table, _is_dual) =
104        if check_statement_for_range(&statement) || config.data_file.is_empty() {
105            info!("Using DUAL table for expression evaluation");
106            (crate::data::datatable::DataTable::dual(), true)
107        } else {
108            info!("Loading data from: {}", config.data_file);
109            let table = load_data_file(&config.data_file)?;
110            info!(
111                "Loaded {} rows with {} columns",
112                table.row_count(),
113                table.column_count()
114            );
115            (table, false)
116        };
117    let _table_name = data_table.name.clone();
118
119    // 2. Create a DataView from the table
120    let dataview = DataView::new(std::sync::Arc::new(data_table));
121
122    // 3. Execute the query
123    info!("Executing query: {}", config.query);
124
125    // If execution_plan is requested, show detailed execution information
126    if config.execution_plan {
127        println!("\n=== EXECUTION PLAN ===");
128        println!("Query: {}", config.query);
129        println!("\nExecution Steps:");
130        println!("1. PARSE - Parse SQL query");
131        println!("2. LOAD_DATA - Load data from {}", &config.data_file);
132        println!(
133            "   • Loaded {} rows, {} columns",
134            dataview.row_count(),
135            dataview.column_count()
136        );
137    }
138
139    // If query_plan is requested, parse and display the AST
140    if config.query_plan {
141        use crate::sql::recursive_parser::Parser;
142        let mut parser = Parser::new(&config.query);
143        match parser.parse() {
144            Ok(statement) => {
145                println!("\n=== QUERY PLAN (AST) ===");
146                println!("{statement:#?}");
147                println!("=== END QUERY PLAN ===\n");
148            }
149            Err(e) => {
150                eprintln!("Failed to parse query for plan: {e}");
151            }
152        }
153    }
154
155    let query_start = Instant::now();
156
157    // Load configuration file to get date notation and other settings
158    let app_config = Config::load().unwrap_or_else(|e| {
159        debug!("Could not load config file: {}. Using defaults.", e);
160        Config::default()
161    });
162
163    // Initialize global config for function registry
164    crate::config::global::init_config(app_config.clone());
165
166    // Use QueryExecutionService with full BehaviorConfig
167    let mut behavior_config = app_config.behavior.clone();
168    debug!(
169        "Using date notation: {}",
170        behavior_config.default_date_notation
171    );
172    // Command line args override config file settings
173    if config.case_insensitive {
174        behavior_config.case_insensitive_default = true;
175    }
176    if config.auto_hide_empty {
177        behavior_config.hide_empty_columns = true;
178    }
179
180    let query_service = QueryExecutionService::with_behavior_config(behavior_config);
181
182    let exec_start = Instant::now();
183    let result = query_service.execute(&config.query, Some(&dataview), Some(dataview.source()))?;
184    let exec_time = exec_start.elapsed();
185
186    let query_time = query_start.elapsed();
187    info!("Query executed in {:?}", query_time);
188    info!(
189        "Result: {} rows, {} columns",
190        result.dataview.row_count(),
191        result.dataview.column_count()
192    );
193
194    // Show execution plan details if requested
195    if config.execution_plan {
196        println!(
197            "3. QUERY_EXECUTION [{:.3}ms]",
198            exec_time.as_secs_f64() * 1000.0
199        );
200
201        // Parse query to understand what operations are being performed
202        use crate::sql::recursive_parser::Parser;
203        let mut parser = Parser::new(&config.query);
204        if let Ok(stmt) = parser.parse() {
205            if stmt.where_clause.is_some() {
206                println!("   • WHERE clause filtering applied");
207                println!("   • Rows after filter: {}", result.dataview.row_count());
208            }
209
210            if let Some(ref order_by) = stmt.order_by {
211                println!("   • ORDER BY: {} column(s)", order_by.len());
212            }
213
214            if let Some(ref group_by) = stmt.group_by {
215                println!("   • GROUP BY: {} column(s)", group_by.len());
216            }
217
218            if let Some(limit) = stmt.limit {
219                println!("   • LIMIT: {} rows", limit);
220            }
221
222            if stmt.distinct {
223                println!("   • DISTINCT applied");
224            }
225        }
226
227        println!("\nExecution Statistics:");
228        println!(
229            "  Preparation:    {:.3}ms",
230            (exec_start - start_time).as_secs_f64() * 1000.0
231        );
232        println!(
233            "  Query time:     {:.3}ms",
234            exec_time.as_secs_f64() * 1000.0
235        );
236        println!(
237            "  Total time:     {:.3}ms",
238            query_time.as_secs_f64() * 1000.0
239        );
240        println!("  Rows returned:  {}", result.dataview.row_count());
241        println!("  Columns:        {}", result.dataview.column_count());
242        println!("\n=== END EXECUTION PLAN ===");
243        println!();
244    }
245
246    // 4. Apply limit if specified
247    let final_view = if let Some(limit) = config.limit {
248        let limited_table = limit_results(&result.dataview, limit)?;
249        DataView::new(std::sync::Arc::new(limited_table))
250    } else {
251        result.dataview
252    };
253
254    // 5. Output the results
255    let output_result = if let Some(ref path) = config.output_file {
256        let mut file = fs::File::create(path)
257            .with_context(|| format!("Failed to create output file: {path}"))?;
258        output_results(&final_view, config.output_format, &mut file)?;
259        info!("Results written to: {}", path);
260        Ok(())
261    } else {
262        output_results(&final_view, config.output_format, &mut io::stdout())?;
263        Ok(())
264    };
265
266    let total_time = start_time.elapsed();
267    debug!("Total execution time: {:?}", total_time);
268
269    // Print stats to stderr so they don't interfere with output
270    if config.output_file.is_none() {
271        eprintln!(
272            "\n# Query completed: {} rows in {:?}",
273            final_view.row_count(),
274            query_time
275        );
276    }
277
278    output_result
279}
280
281/// Execute a script file with multiple SQL statements separated by GO
282pub fn execute_script(config: NonInteractiveConfig) -> Result<()> {
283    let _start_time = Instant::now();
284
285    // Parse the script into individual statements
286    let parser = ScriptParser::new(&config.query);
287    let statements = parser.parse_and_validate()?;
288
289    info!("Found {} statements in script", statements.len());
290
291    // Determine data file to use (command-line overrides script hint)
292    let data_file = if !config.data_file.is_empty() {
293        // Command-line argument takes precedence
294        config.data_file.clone()
295    } else if let Some(hint) = parser.data_file_hint() {
296        // Use data file hint from script
297        info!("Using data file from script hint: {}", hint);
298
299        // Resolve relative paths relative to script file if provided
300        if let Some(script_path) = config.script_file.as_ref() {
301            let script_dir = std::path::Path::new(script_path)
302                .parent()
303                .unwrap_or(std::path::Path::new("."));
304            let hint_path = std::path::Path::new(hint);
305
306            if hint_path.is_relative() {
307                script_dir.join(hint_path).to_string_lossy().to_string()
308            } else {
309                hint.to_string()
310            }
311        } else {
312            hint.to_string()
313        }
314    } else {
315        String::new()
316    };
317
318    // Check if script needs a data file
319    let needs_data_file = if data_file.is_empty() {
320        // Parse statements to check if they use DUAL or RANGE
321        use crate::sql::recursive_parser::Parser;
322
323        let mut needs_file = false;
324        for statement_sql in &statements {
325            let mut parser = Parser::new(statement_sql);
326            match parser.parse() {
327                Ok(stmt) => {
328                    // Check if statement uses DUAL, RANGE, or has no FROM clause
329                    let uses_dual = stmt
330                        .from_table
331                        .as_ref()
332                        .map_or(false, |t| t.eq_ignore_ascii_case("dual"));
333                    let uses_range = stmt.from_function.is_some();
334                    let no_from = stmt.from_table.is_none()
335                        && stmt.from_subquery.is_none()
336                        && stmt.from_function.is_none();
337
338                    // Check CTEs for RANGE usage
339                    let cte_has_range = stmt.ctes.iter().any(|cte| {
340                        cte.query.from_function.is_some()
341                            || cte
342                                .query
343                                .from_table
344                                .as_ref()
345                                .map_or(false, |t| t.eq_ignore_ascii_case("dual"))
346                    });
347
348                    if !uses_dual && !uses_range && !no_from && !cte_has_range {
349                        needs_file = true;
350                        break;
351                    }
352                }
353                Err(_) => {
354                    // If we can't parse, assume it needs a data file
355                    needs_file = true;
356                    break;
357                }
358            }
359        }
360        needs_file
361    } else {
362        // Data file was specified, so we'll use it
363        false
364    };
365
366    // Load the data file once (or use DUAL)
367    let (data_table, _is_dual) = if data_file.is_empty() {
368        if needs_data_file {
369            anyhow::bail!(
370                "Script requires a data file. Either:\n\
371                1. Provide a data file: sql-cli data.csv -f script.sql\n\
372                2. Add a data hint to your script: -- #!data: path/to/data.csv"
373            );
374        } else {
375            // Script doesn't need a data file, use DUAL
376            info!("Using DUAL table for script execution");
377            (DataTable::dual(), true)
378        }
379    } else {
380        // Check if file exists before trying to load
381        if !std::path::Path::new(&data_file).exists() {
382            anyhow::bail!(
383                "Data file not found: {}\n\
384                Please check the path is correct",
385                data_file
386            );
387        }
388
389        info!("Loading data from: {}", data_file);
390        let table = load_data_file(&data_file)?;
391        info!(
392            "Loaded {} rows with {} columns",
393            table.row_count(),
394            table.column_count()
395        );
396        (table, false)
397    };
398
399    // Track script results
400    let mut script_result = ScriptResult::new();
401    let mut output = Vec::new();
402
403    // Create Arc<DataTable> once for all statements - avoids expensive cloning
404    let arc_data_table = std::sync::Arc::new(data_table);
405
406    // Execute each statement
407    for (idx, statement) in statements.iter().enumerate() {
408        let statement_num = idx + 1;
409        let stmt_start = Instant::now();
410
411        // Print separator for table format
412        if matches!(config.output_format, OutputFormat::Table) {
413            if idx > 0 {
414                output.push(String::new()); // Empty line between queries
415            }
416            output.push(format!("-- Query {} --", statement_num));
417        }
418
419        // Create a fresh DataView for each statement (reuses the Arc)
420        let dataview = DataView::new(arc_data_table.clone());
421
422        // Execute the statement
423        let service = QueryExecutionService::new(config.case_insensitive, config.auto_hide_empty);
424        match service.execute(statement, Some(&dataview), None) {
425            Ok(result) => {
426                let exec_time = stmt_start.elapsed().as_secs_f64() * 1000.0;
427                let final_view = result.dataview;
428
429                // Format the output based on the output format
430                let mut statement_output = Vec::new();
431                match config.output_format {
432                    OutputFormat::Csv => {
433                        output_csv(&final_view, &mut statement_output, ',')?;
434                    }
435                    OutputFormat::Json => {
436                        output_json(&final_view, &mut statement_output)?;
437                    }
438                    OutputFormat::Table => {
439                        output_table(&final_view, &mut statement_output)?;
440                        writeln!(
441                            &mut statement_output,
442                            "Query completed: {} rows in {:.2}ms",
443                            final_view.row_count(),
444                            exec_time
445                        )?;
446                    }
447                    OutputFormat::Tsv => {
448                        output_csv(&final_view, &mut statement_output, '\t')?;
449                    }
450                }
451
452                // Add to overall output
453                output.extend(
454                    String::from_utf8_lossy(&statement_output)
455                        .lines()
456                        .map(String::from),
457                );
458
459                script_result.add_success(
460                    statement_num,
461                    statement.clone(),
462                    final_view.row_count(),
463                    exec_time,
464                );
465            }
466            Err(e) => {
467                let exec_time = stmt_start.elapsed().as_secs_f64() * 1000.0;
468                let error_msg = format!("Query {} failed: {}", statement_num, e);
469
470                if matches!(config.output_format, OutputFormat::Table) {
471                    output.push(error_msg.clone());
472                }
473
474                script_result.add_failure(
475                    statement_num,
476                    statement.clone(),
477                    e.to_string(),
478                    exec_time,
479                );
480
481                // Continue to next statement (don't stop on error)
482            }
483        }
484    }
485
486    // Write output
487    if let Some(ref output_file) = config.output_file {
488        let mut file = fs::File::create(output_file)?;
489        for line in &output {
490            writeln!(file, "{}", line)?;
491        }
492        info!("Results written to: {}", output_file);
493    } else {
494        for line in &output {
495            println!("{}", line);
496        }
497    }
498
499    // Print summary if in table mode
500    if matches!(config.output_format, OutputFormat::Table) {
501        println!("\n=== Script Summary ===");
502        println!("Total statements: {}", script_result.total_statements);
503        println!("Successful: {}", script_result.successful_statements);
504        println!("Failed: {}", script_result.failed_statements);
505        println!(
506            "Total execution time: {:.2}ms",
507            script_result.total_execution_time_ms
508        );
509    }
510
511    if !script_result.all_successful() {
512        return Err(anyhow::anyhow!(
513            "{} of {} statements failed",
514            script_result.failed_statements,
515            script_result.total_statements
516        ));
517    }
518
519    Ok(())
520}
521
522/// Load a data file (CSV or JSON) into a `DataTable`
523fn load_data_file(path: &str) -> Result<DataTable> {
524    let path = Path::new(path);
525
526    if !path.exists() {
527        return Err(anyhow::anyhow!("File not found: {}", path.display()));
528    }
529
530    // Determine file type by extension
531    let extension = path
532        .extension()
533        .and_then(|ext| ext.to_str())
534        .map(str::to_lowercase)
535        .unwrap_or_default();
536
537    let table_name = path
538        .file_stem()
539        .and_then(|stem| stem.to_str())
540        .unwrap_or("data")
541        .to_string();
542
543    match extension.as_str() {
544        "csv" => load_csv_to_datatable(path, &table_name)
545            .with_context(|| format!("Failed to load CSV file: {}", path.display())),
546        "json" => load_json_to_datatable(path, &table_name)
547            .with_context(|| format!("Failed to load JSON file: {}", path.display())),
548        _ => Err(anyhow::anyhow!(
549            "Unsupported file type: {}. Use .csv or .json",
550            extension
551        )),
552    }
553}
554
555/// Limit the number of rows in results
556fn limit_results(dataview: &DataView, limit: usize) -> Result<DataTable> {
557    let source = dataview.source();
558    let mut limited_table = DataTable::new(&source.name);
559
560    // Copy columns
561    for col in &source.columns {
562        limited_table.add_column(col.clone());
563    }
564
565    // Copy limited rows
566    let rows_to_copy = dataview.row_count().min(limit);
567    for i in 0..rows_to_copy {
568        if let Some(row) = dataview.get_row(i) {
569            limited_table.add_row(row.clone());
570        }
571    }
572
573    Ok(limited_table)
574}
575
576/// Output query results in the specified format
577fn output_results<W: Write>(
578    dataview: &DataView,
579    format: OutputFormat,
580    writer: &mut W,
581) -> Result<()> {
582    match format {
583        OutputFormat::Csv => output_csv(dataview, writer, ','),
584        OutputFormat::Tsv => output_csv(dataview, writer, '\t'),
585        OutputFormat::Json => output_json(dataview, writer),
586        OutputFormat::Table => output_table(dataview, writer),
587    }
588}
589
590/// Output results as CSV/TSV
591fn output_csv<W: Write>(dataview: &DataView, writer: &mut W, delimiter: char) -> Result<()> {
592    // Write headers
593    let columns = dataview.column_names();
594    for (i, col) in columns.iter().enumerate() {
595        if i > 0 {
596            write!(writer, "{delimiter}")?;
597        }
598        write!(writer, "{}", escape_csv_field(col, delimiter))?;
599    }
600    writeln!(writer)?;
601
602    // Write rows
603    for row_idx in 0..dataview.row_count() {
604        if let Some(row) = dataview.get_row(row_idx) {
605            for (i, value) in row.values.iter().enumerate() {
606                if i > 0 {
607                    write!(writer, "{delimiter}")?;
608                }
609                write!(
610                    writer,
611                    "{}",
612                    escape_csv_field(&format_value(value), delimiter)
613                )?;
614            }
615            writeln!(writer)?;
616        }
617    }
618
619    Ok(())
620}
621
622/// Output results as JSON
623fn output_json<W: Write>(dataview: &DataView, writer: &mut W) -> Result<()> {
624    let columns = dataview.column_names();
625    let mut rows = Vec::new();
626
627    for row_idx in 0..dataview.row_count() {
628        if let Some(row) = dataview.get_row(row_idx) {
629            let mut json_row = serde_json::Map::new();
630            for (col_idx, value) in row.values.iter().enumerate() {
631                if col_idx < columns.len() {
632                    json_row.insert(columns[col_idx].clone(), value_to_json(value));
633                }
634            }
635            rows.push(serde_json::Value::Object(json_row));
636        }
637    }
638
639    let json = serde_json::to_string_pretty(&rows)?;
640    writeln!(writer, "{json}")?;
641
642    Ok(())
643}
644
645/// Output results as an ASCII table
646fn output_table<W: Write>(dataview: &DataView, writer: &mut W) -> Result<()> {
647    let columns = dataview.column_names();
648
649    // Calculate column widths
650    let mut widths = vec![0; columns.len()];
651    for (i, col) in columns.iter().enumerate() {
652        widths[i] = col.len();
653    }
654
655    // Check first 100 rows for width calculation
656    let sample_size = dataview.row_count().min(100);
657    for row_idx in 0..sample_size {
658        if let Some(row) = dataview.get_row(row_idx) {
659            for (i, value) in row.values.iter().enumerate() {
660                if i < widths.len() {
661                    let value_str = format_value(value);
662                    widths[i] = widths[i].max(value_str.len());
663                }
664            }
665        }
666    }
667
668    // Limit column widths to 50 characters
669    for width in &mut widths {
670        *width = (*width).min(50);
671    }
672
673    // Print header separator
674    write!(writer, "+")?;
675    for width in &widths {
676        write!(writer, "-{}-+", "-".repeat(*width))?;
677    }
678    writeln!(writer)?;
679
680    // Print headers
681    write!(writer, "|")?;
682    for (i, col) in columns.iter().enumerate() {
683        write!(writer, " {:^width$} |", col, width = widths[i])?;
684    }
685    writeln!(writer)?;
686
687    // Print header separator
688    write!(writer, "+")?;
689    for width in &widths {
690        write!(writer, "-{}-+", "-".repeat(*width))?;
691    }
692    writeln!(writer)?;
693
694    // Print rows
695    for row_idx in 0..dataview.row_count() {
696        if let Some(row) = dataview.get_row(row_idx) {
697            write!(writer, "|")?;
698            for (i, value) in row.values.iter().enumerate() {
699                if i < widths.len() {
700                    let value_str = format_value(value);
701                    let truncated = if value_str.len() > widths[i] {
702                        format!("{}...", &value_str[..widths[i] - 3])
703                    } else {
704                        value_str
705                    };
706                    write!(writer, " {:<width$} |", truncated, width = widths[i])?;
707                }
708            }
709            writeln!(writer)?;
710        }
711    }
712
713    // Print bottom separator
714    write!(writer, "+")?;
715    for width in &widths {
716        write!(writer, "-{}-+", "-".repeat(*width))?;
717    }
718    writeln!(writer)?;
719
720    Ok(())
721}
722
723/// Format a `DataValue` for display
724fn format_value(value: &DataValue) -> String {
725    match value {
726        DataValue::Null => String::new(),
727        DataValue::Integer(i) => i.to_string(),
728        DataValue::Float(f) => f.to_string(),
729        DataValue::String(s) => s.clone(),
730        DataValue::InternedString(s) => s.to_string(),
731        DataValue::Boolean(b) => b.to_string(),
732        DataValue::DateTime(dt) => dt.to_string(),
733    }
734}
735
736/// Convert `DataValue` to JSON
737fn value_to_json(value: &DataValue) -> serde_json::Value {
738    match value {
739        DataValue::Null => serde_json::Value::Null,
740        DataValue::Integer(i) => serde_json::Value::Number((*i).into()),
741        DataValue::Float(f) => {
742            if let Some(n) = serde_json::Number::from_f64(*f) {
743                serde_json::Value::Number(n)
744            } else {
745                serde_json::Value::Null
746            }
747        }
748        DataValue::String(s) => serde_json::Value::String(s.clone()),
749        DataValue::InternedString(s) => serde_json::Value::String(s.to_string()),
750        DataValue::Boolean(b) => serde_json::Value::Bool(*b),
751        DataValue::DateTime(dt) => serde_json::Value::String(dt.to_string()),
752    }
753}
754
755/// Escape a CSV field if it contains special characters
756fn escape_csv_field(field: &str, delimiter: char) -> String {
757    if field.contains(delimiter)
758        || field.contains('"')
759        || field.contains('\n')
760        || field.contains('\r')
761    {
762        format!("\"{}\"", field.replace('"', "\"\""))
763    } else {
764        field.to_string()
765    }
766}
sql_cli/non_interactive.rs

sql_cli/
non_interactive.rs
