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

sql_cli/
non_interactive.rs
