mod clipboard;
mod engine;
mod input;
mod output;
mod summary;
mod types;
mod value;
mod writers;
pub use engine::{
run_query, run_query_with_params, run_query_with_params_multi,
run_query_with_params_multi_inputs, run_query_with_params_multi_inputs_and_options,
run_query_with_params_multi_inputs_and_options_and_normalization,
};
pub use input::{SheetData, load_input};
pub use output::{
render_csv, render_html, render_json, render_jsonl, render_markdown, render_text, render_xml,
write_csv, write_jsonl, write_text,
};
pub use summary::load_table_summaries;
pub use types::{
ColumnInfo, ColumnStats, ExtractionOptions, HeaderCase, InferredType,
InputNormalizationOptions, JsonMode, QueryParam, QueryResult, QueryValue, TableSummary,
TypeInferenceOptions, WorkbookInput, XmlMode,
};
pub use writers::{write_feather, write_parquet, write_xlsx};
#[cfg(test)]
use anyhow::Result;
#[cfg(test)]
use rust_xlsxwriter::Workbook;
#[cfg(test)]
use std::path::Path;
#[cfg(test)]
mod tests {
use std::{
fs,
path::PathBuf,
time::{SystemTime, UNIX_EPOCH},
};
use super::clipboard;
use super::*;
fn temp_path(name: &str) -> PathBuf {
let unique = SystemTime::now()
.duration_since(UNIX_EPOCH)
.expect("time should move forward")
.as_nanos();
std::env::temp_dir().join(format!("query-forge-{name}-{unique}.xlsx"))
}
fn temp_xml_path(name: &str) -> PathBuf {
let unique = SystemTime::now()
.duration_since(UNIX_EPOCH)
.expect("time should move forward")
.as_nanos();
std::env::temp_dir().join(format!("query-forge-{name}-{unique}.xml"))
}
fn temp_csv_path(name: &str) -> PathBuf {
let unique = SystemTime::now()
.duration_since(UNIX_EPOCH)
.expect("time should move forward")
.as_nanos();
std::env::temp_dir().join(format!("query-forge-{name}-{unique}.csv"))
}
fn temp_jsonl_path(name: &str) -> PathBuf {
let unique = SystemTime::now()
.duration_since(UNIX_EPOCH)
.expect("time should move forward")
.as_nanos();
std::env::temp_dir().join(format!("query-forge-{name}-{unique}.jsonl"))
}
fn temp_json_path(name: &str) -> PathBuf {
let unique = SystemTime::now()
.duration_since(UNIX_EPOCH)
.expect("time should move forward")
.as_nanos();
std::env::temp_dir().join(format!("query-forge-{name}-{unique}.json"))
}
fn temp_markdown_path(name: &str) -> PathBuf {
let unique = SystemTime::now()
.duration_since(UNIX_EPOCH)
.expect("time should move forward")
.as_nanos();
std::env::temp_dir().join(format!("query-forge-{name}-{unique}.md"))
}
fn temp_parquet_path(name: &str) -> PathBuf {
let unique = SystemTime::now()
.duration_since(UNIX_EPOCH)
.expect("time should move forward")
.as_nanos();
std::env::temp_dir().join(format!("query-forge-{name}-{unique}.parquet"))
}
fn temp_feather_path(name: &str) -> PathBuf {
let unique = SystemTime::now()
.duration_since(UNIX_EPOCH)
.expect("time should move forward")
.as_nanos();
std::env::temp_dir().join(format!("query-forge-{name}-{unique}.feather"))
}
fn write_test_workbook(path: &Path) -> Result<()> {
let mut workbook = Workbook::new();
let worksheet = workbook.add_worksheet();
worksheet.write_string(0, 0, "name")?;
worksheet.write_string(0, 1, "price")?;
worksheet.write_string(0, 2, "active")?;
worksheet.write_string(1, 0, "Keyboard")?;
worksheet.write_number(1, 1, 12.5)?;
worksheet.write_boolean(1, 2, true)?;
worksheet.write_string(2, 0, "Cable")?;
worksheet.write_number(2, 1, 5.0)?;
worksheet.write_boolean(2, 2, false)?;
workbook.save(path)?;
Ok(())
}
fn write_test_workbook_on_sheet(path: &Path, sheet_name: &str) -> Result<()> {
let mut workbook = Workbook::new();
let worksheet = workbook.add_worksheet();
worksheet.set_name(sheet_name)?;
worksheet.write_string(0, 0, "name")?;
worksheet.write_string(0, 1, "price")?;
worksheet.write_string(1, 0, "Keyboard")?;
worksheet.write_number(1, 1, 12.5)?;
workbook.save(path)?;
Ok(())
}
fn write_test_xml(path: &Path) -> Result<()> {
fs::write(
path,
r#"<?xml version="1.0" encoding="UTF-8"?>
<data>
<row>
<name>Keyboard</name>
<price>12.5</price>
<active>true</active>
</row>
<row>
<name>Cable</name>
<price>5</price>
<active>false</active>
</row>
</data>
"#,
)?;
Ok(())
}
fn write_test_xml_with_sections(path: &Path) -> Result<()> {
fs::write(
path,
r#"<?xml version="1.0" encoding="UTF-8"?>
<root>
<Inventory>
<row>
<name>Keyboard</name>
<price>12.5</price>
</row>
</Inventory>
<Archive>
<row>
<name>Legacy Cable</name>
<price>3.5</price>
</row>
</Archive>
</root>
"#,
)?;
Ok(())
}
fn write_test_csv(path: &Path) -> Result<()> {
fs::write(
path,
"name,price,active\nKeyboard,12.5,true\nCable,5,false\n",
)?;
Ok(())
}
fn write_test_csv_with_custom_inference_values(path: &Path) -> Result<()> {
fs::write(
path,
"name,amount,flag,date,note\nKeyboard,\"1,5\",YES,31/12/2025,N/A\nCable,\"2,0\",NO,01/01/2026,ok\n",
)?;
Ok(())
}
fn write_test_csv_no_headers(path: &Path) -> Result<()> {
fs::write(path, "Keyboard,12.5,true\nCable,5,false\n")?;
Ok(())
}
fn write_test_csv_for_normalization(path: &Path) -> Result<()> {
fs::write(
path,
"First Name,First-Name, Notes \n Alice ,Alice, hello \n , , \n",
)?;
Ok(())
}
fn write_test_jsonl(path: &Path) -> Result<()> {
fs::write(
path,
"{\"name\":\"Keyboard\",\"price\":12.5,\"active\":true}\n{\"name\":\"Cable\",\"price\":5,\"active\":false}\n",
)?;
Ok(())
}
fn write_test_jsonl_with_sparse_columns(path: &Path) -> Result<()> {
fs::write(
path,
"{\"name\":\"Keyboard\",\"price\":12.5}\n\n{\"name\":\"Cable\",\"active\":false}\n",
)?;
Ok(())
}
fn write_test_json(path: &Path) -> Result<()> {
fs::write(
path,
"[{\"name\":\"Keyboard\",\"price\":12.5,\"active\":true},{\"name\":\"Cable\",\"price\":5,\"active\":false}]",
)?;
Ok(())
}
fn write_test_json_with_sections(path: &Path) -> Result<()> {
fs::write(
path,
"{\"Inventory\":[{\"name\":\"Keyboard\",\"price\":12.5}],\"Archive\":[{\"name\":\"Legacy Cable\",\"price\":3.5}]}",
)?;
Ok(())
}
fn write_test_markdown_with_tables(path: &Path) -> Result<()> {
fs::write(
path,
r#"# Inventory Report
| name | price | active |
| --- | ---: | :---: |
| Keyboard | 12.5 | true |
| Cable | 5 | false |
## Archive
| name | price |
| --- | --- |
| Legacy Cable | 3.5 |
"#,
)?;
Ok(())
}
fn write_test_markdown_with_headers_only(path: &Path) -> Result<()> {
fs::write(
path,
r#"| name | price |
| --- | --- |
"#,
)?;
Ok(())
}
#[test]
fn applies_input_normalization_options_before_query() -> Result<()> {
let csv_path = temp_csv_path("csv-normalization");
write_test_csv_for_normalization(&csv_path)?;
let options = InputNormalizationOptions {
trim: true,
skip_empty_rows: true,
normalize_headers: true,
header_case: Some(HeaderCase::Snake),
dedupe_headers: true,
};
let inputs = [WorkbookInput {
path: &csv_path,
sheet_name: None,
table_name: None,
explicit_format: None,
}];
let result = run_query_with_params_multi_inputs_and_options_and_normalization(
&inputs,
"SELECT first_name, first_name_2, notes FROM table",
&[],
&TypeInferenceOptions::default(),
&options,
&ExtractionOptions::default(),
true,
)?;
assert_eq!(result.columns, vec!["first_name", "first_name_2", "notes"]);
assert_eq!(
result.rows,
vec![vec![
QueryValue::Text("Alice".into()),
QueryValue::Text("Alice".into()),
QueryValue::Text("hello".into())
]]
);
fs::remove_file(csv_path)?;
Ok(())
}
#[test]
fn executes_sql_query_against_sheet() -> Result<()> {
let workbook_path = temp_path("query");
write_test_workbook(&workbook_path)?;
let result = run_query(
&workbook_path,
Some("Sheet1"),
"SELECT name, price FROM table WHERE price > 10 ORDER BY price DESC",
true,
)?;
assert_eq!(result.columns, vec!["name", "price"]);
assert_eq!(
result.rows,
vec![vec![
QueryValue::Text("Keyboard".into()),
QueryValue::Real(12.5)
]]
);
fs::remove_file(workbook_path)?;
Ok(())
}
#[test]
fn executes_sql_query_against_sheet1_alias() -> Result<()> {
let workbook_path = temp_path("query-sheet1-alias");
write_test_workbook(&workbook_path)?;
let result = run_query(
&workbook_path,
Some("Sheet1"),
"SELECT name, price FROM table1 WHERE price > 10 ORDER BY price DESC",
true,
)?;
assert_eq!(result.columns, vec!["name", "price"]);
assert_eq!(
result.rows,
vec![vec![
QueryValue::Text("Keyboard".into()),
QueryValue::Real(12.5)
]]
);
fs::remove_file(workbook_path)?;
Ok(())
}
#[test]
fn executes_sql_query_with_named_parameter() -> Result<()> {
let workbook_path = temp_path("query-with-param");
write_test_workbook(&workbook_path)?;
let result = run_query_with_params(
&workbook_path,
Some("Sheet1"),
"SELECT name, price FROM table WHERE price > :min_price ORDER BY price DESC",
&[QueryParam {
name: "min_price".to_owned(),
value: QueryValue::Real(10.0),
}],
true,
)?;
assert_eq!(
result,
QueryResult {
columns: vec!["name".to_owned(), "price".to_owned()],
rows: vec![vec![
QueryValue::Text("Keyboard".to_owned()),
QueryValue::Real(12.5),
]],
}
);
fs::remove_file(&workbook_path)?;
Ok(())
}
#[test]
fn executes_query_against_multiple_workbooks() -> Result<()> {
let workbook_path_1 = temp_path("multi-1");
let workbook_path_2 = temp_path("multi-2");
write_test_workbook(&workbook_path_1)?;
write_test_workbook(&workbook_path_2)?;
let result = run_query_with_params_multi(
&[workbook_path_1.as_path(), workbook_path_2.as_path()],
Some("Sheet1"),
"SELECT COUNT(*) AS total_rows FROM table UNION ALL SELECT COUNT(*) AS total_rows FROM table2",
&[],
true,
)?;
assert_eq!(result.columns, vec!["total_rows"]);
assert_eq!(
result.rows,
vec![vec![QueryValue::Integer(2)], vec![QueryValue::Integer(2)]]
);
fs::remove_file(&workbook_path_1)?;
fs::remove_file(&workbook_path_2)?;
Ok(())
}
#[test]
fn executes_query_against_multiple_workbooks_with_distinct_sheet_names() -> Result<()> {
let workbook_path_1 = temp_path("multi-sheet-1");
let workbook_path_2 = temp_path("multi-sheet-2");
write_test_workbook_on_sheet(&workbook_path_1, "Consuntivo")?;
write_test_workbook_on_sheet(&workbook_path_2, "WKL")?;
let result = run_query_with_params_multi_inputs(
&[
WorkbookInput {
path: workbook_path_1.as_path(),
sheet_name: Some("Consuntivo"),
table_name: None,
explicit_format: None,
},
WorkbookInput {
path: workbook_path_2.as_path(),
sheet_name: Some("WKL"),
table_name: None,
explicit_format: None,
},
],
"SELECT COUNT(*) AS total_rows FROM table UNION ALL SELECT COUNT(*) AS total_rows FROM table2",
&[],
true,
)?;
assert_eq!(result.columns, vec!["total_rows"]);
assert_eq!(
result.rows,
vec![vec![QueryValue::Integer(1)], vec![QueryValue::Integer(1)]]
);
fs::remove_file(&workbook_path_1)?;
fs::remove_file(&workbook_path_2)?;
Ok(())
}
#[test]
fn executes_query_with_explicit_table_names() -> Result<()> {
let sales_path = temp_path("explicit-name-sales");
let costs_path = temp_path("explicit-name-costs");
write_test_workbook_on_sheet(&sales_path, "Sheet1")?;
write_test_workbook_on_sheet(&costs_path, "Sheet1")?;
let result = run_query_with_params_multi_inputs(
&[
WorkbookInput {
path: sales_path.as_path(),
sheet_name: Some("Sheet1"),
table_name: Some("sales"),
explicit_format: None,
},
WorkbookInput {
path: costs_path.as_path(),
sheet_name: Some("Sheet1"),
table_name: Some("costs"),
explicit_format: None,
},
],
"SELECT COUNT(*) AS total_rows FROM sales UNION ALL SELECT COUNT(*) AS total_rows FROM costs",
&[],
true,
)?;
assert_eq!(result.columns, vec!["total_rows"]);
assert_eq!(
result.rows,
vec![vec![QueryValue::Integer(1)], vec![QueryValue::Integer(1)]]
);
fs::remove_file(&sales_path)?;
fs::remove_file(&costs_path)?;
Ok(())
}
#[test]
fn executes_sql_query_against_whole_xml_file() -> Result<()> {
let xml_path = temp_xml_path("xml-whole");
write_test_xml(&xml_path)?;
let result = run_query(
&xml_path,
None,
"SELECT name, price FROM table WHERE active = 1 ORDER BY price DESC",
true,
)?;
assert_eq!(result.columns, vec!["name", "price"]);
assert_eq!(
result.rows,
vec![vec![
QueryValue::Text("Keyboard".into()),
QueryValue::Real(12.5)
]]
);
fs::remove_file(xml_path)?;
Ok(())
}
#[test]
fn executes_sql_query_against_xml_sheet_tag() -> Result<()> {
let xml_path = temp_xml_path("xml-sheet-tag");
write_test_xml_with_sections(&xml_path)?;
let result = run_query(
&xml_path,
Some("Archive"),
"SELECT name, price FROM table",
true,
)?;
assert_eq!(result.columns, vec!["name", "price"]);
assert_eq!(
result.rows,
vec![vec![
QueryValue::Text("Legacy Cable".into()),
QueryValue::Real(3.5)
]]
);
fs::remove_file(xml_path)?;
Ok(())
}
#[test]
fn executes_query_against_heterogeneous_xlsx_and_xml_inputs() -> Result<()> {
let workbook_path = temp_path("mixed-xlsx");
let xml_path = temp_xml_path("mixed-xml");
write_test_workbook(&workbook_path)?;
write_test_xml(&xml_path)?;
let result = run_query_with_params_multi_inputs(
&[
WorkbookInput {
path: workbook_path.as_path(),
sheet_name: Some("Sheet1"),
table_name: None,
explicit_format: None,
},
WorkbookInput {
path: xml_path.as_path(),
sheet_name: None,
table_name: None,
explicit_format: None,
},
],
"SELECT COUNT(*) AS total_rows FROM table UNION ALL SELECT COUNT(*) AS total_rows FROM table2",
&[],
true,
)?;
assert_eq!(result.columns, vec!["total_rows"]);
assert_eq!(
result.rows,
vec![vec![QueryValue::Integer(2)], vec![QueryValue::Integer(2)]]
);
fs::remove_file(&workbook_path)?;
fs::remove_file(&xml_path)?;
Ok(())
}
#[test]
fn executes_sql_query_against_csv_file() -> Result<()> {
let csv_path = temp_csv_path("csv");
write_test_csv(&csv_path)?;
let result = run_query(
&csv_path,
None,
"SELECT name, price FROM table WHERE active = 1 ORDER BY price DESC",
true,
)?;
assert_eq!(result.columns, vec!["name", "price"]);
assert_eq!(
result.rows,
vec![vec![
QueryValue::Text("Keyboard".into()),
QueryValue::Real(12.5)
]]
);
fs::remove_file(csv_path)?;
Ok(())
}
#[test]
fn supports_configurable_type_inference_options() -> Result<()> {
let csv_path = temp_csv_path("csv-custom-inference");
write_test_csv_with_custom_inference_values(&csv_path)?;
let options = TypeInferenceOptions {
infer_types: true,
decimal_comma: true,
date_format: Some("%d/%m/%Y".to_owned()),
null_values: vec!["N/A".to_owned()],
true_values: vec!["YES".to_owned()],
false_values: vec!["NO".to_owned()],
};
let workbook_inputs = [WorkbookInput {
path: csv_path.as_path(),
sheet_name: None,
table_name: None,
explicit_format: None,
}];
let result = run_query_with_params_multi_inputs_and_options(
&workbook_inputs,
"SELECT amount, flag, date, note FROM table ORDER BY amount",
&[],
&options,
true,
)?;
assert_eq!(result.columns, vec!["amount", "flag", "date", "note"]);
assert_eq!(
result.rows,
vec![
vec![
QueryValue::Real(1.5),
QueryValue::Integer(1),
QueryValue::Text("2025-12-31".into()),
QueryValue::Null
],
vec![
QueryValue::Real(2.0),
QueryValue::Integer(0),
QueryValue::Text("2026-01-01".into()),
QueryValue::Text("ok".into())
]
]
);
fs::remove_file(csv_path)?;
Ok(())
}
#[test]
fn supports_all_text_mode() -> Result<()> {
let csv_path = temp_csv_path("csv-all-text");
write_test_csv(&csv_path)?;
let options = TypeInferenceOptions {
infer_types: false,
..TypeInferenceOptions::default()
};
let workbook_inputs = [WorkbookInput {
path: csv_path.as_path(),
sheet_name: None,
table_name: None,
explicit_format: None,
}];
let result = run_query_with_params_multi_inputs_and_options(
&workbook_inputs,
"SELECT name, price, active FROM table ORDER BY name DESC",
&[],
&options,
true,
)?;
assert_eq!(
result.rows,
vec![
vec![
QueryValue::Text("Keyboard".into()),
QueryValue::Text("12.5".into()),
QueryValue::Text("true".into())
],
vec![
QueryValue::Text("Cable".into()),
QueryValue::Text("5".into()),
QueryValue::Text("false".into())
]
]
);
fs::remove_file(csv_path)?;
Ok(())
}
#[test]
fn executes_sql_query_against_csv_without_headers() -> Result<()> {
let csv_path = temp_csv_path("csv-no-headers");
write_test_csv_no_headers(&csv_path)?;
let result = run_query(
&csv_path,
None,
"SELECT column1, column2 FROM table WHERE column3 = 1 ORDER BY column2 DESC",
false,
)?;
assert_eq!(result.columns, vec!["column1", "column2"]);
assert_eq!(
result.rows,
vec![vec![
QueryValue::Text("Keyboard".into()),
QueryValue::Real(12.5)
]]
);
fs::remove_file(csv_path)?;
Ok(())
}
#[test]
fn executes_sql_query_against_jsonl_file() -> Result<()> {
let jsonl_path = temp_jsonl_path("jsonl");
write_test_jsonl(&jsonl_path)?;
let result = run_query(
&jsonl_path,
None,
"SELECT name, price FROM table WHERE active = 1 ORDER BY price DESC",
true,
)?;
assert_eq!(result.columns, vec!["name", "price"]);
assert_eq!(
result.rows,
vec![vec![
QueryValue::Text("Keyboard".into()),
QueryValue::Real(12.5)
]]
);
fs::remove_file(jsonl_path)?;
Ok(())
}
#[test]
fn executes_sql_query_against_jsonl_file_with_sparse_columns() -> Result<()> {
let jsonl_path = temp_jsonl_path("jsonl-sparse");
write_test_jsonl_with_sparse_columns(&jsonl_path)?;
let result = run_query(
&jsonl_path,
None,
"SELECT name, price, active FROM table ORDER BY name DESC",
true,
)?;
assert_eq!(result.columns, vec!["name", "price", "active"]);
assert_eq!(
result.rows,
vec![
vec![
QueryValue::Text("Keyboard".into()),
QueryValue::Real(12.5),
QueryValue::Null
],
vec![
QueryValue::Text("Cable".into()),
QueryValue::Null,
QueryValue::Integer(0)
]
]
);
fs::remove_file(jsonl_path)?;
Ok(())
}
#[test]
fn executes_sql_query_against_json_file() -> Result<()> {
let json_path = temp_json_path("json");
write_test_json(&json_path)?;
let result = run_query(
&json_path,
None,
"SELECT name, price FROM table WHERE active = 1 ORDER BY price DESC",
true,
)?;
assert_eq!(result.columns, vec!["name", "price"]);
assert_eq!(
result.rows,
vec![vec![
QueryValue::Text("Keyboard".into()),
QueryValue::Real(12.5)
]]
);
fs::remove_file(json_path)?;
Ok(())
}
#[test]
fn executes_sql_query_against_json_sheet_key() -> Result<()> {
let json_path = temp_json_path("json-sheet-key");
write_test_json_with_sections(&json_path)?;
let result = run_query(
&json_path,
Some("Archive"),
"SELECT name, price FROM table",
true,
)?;
assert_eq!(result.columns, vec!["name", "price"]);
assert_eq!(
result.rows,
vec![vec![
QueryValue::Text("Legacy Cable".into()),
QueryValue::Real(3.5)
]]
);
fs::remove_file(json_path)?;
Ok(())
}
#[test]
fn executes_sql_query_against_first_markdown_table_by_default() -> Result<()> {
let markdown_path = temp_markdown_path("markdown-default");
write_test_markdown_with_tables(&markdown_path)?;
let result = run_query(
&markdown_path,
None,
"SELECT name, price FROM table WHERE active = 1",
true,
)?;
assert_eq!(result.columns, vec!["name", "price"]);
assert_eq!(
result.rows,
vec![vec![
QueryValue::Text("Keyboard".into()),
QueryValue::Real(12.5)
]]
);
fs::remove_file(markdown_path)?;
Ok(())
}
#[test]
fn reports_actionable_error_for_csv_selector() -> Result<()> {
let csv_path = temp_csv_path("csv-selector-error");
write_test_csv(&csv_path)?;
let error = run_query(&csv_path, Some("Sheet1"), "SELECT name FROM table", true)
.expect_err("CSV selector should fail");
let message = error.to_string();
assert!(message.contains("does not support selector 'Sheet1'"));
assert!(message.contains("Remove ':Sheet1'"));
fs::remove_file(csv_path)?;
Ok(())
}
#[test]
fn reports_actionable_error_for_jsonl_selector() -> Result<()> {
let jsonl_path = temp_jsonl_path("jsonl-selector-error");
write_test_jsonl(&jsonl_path)?;
let error = run_query(&jsonl_path, Some("Records"), "SELECT name FROM table", true)
.expect_err("JSONL selector should fail");
let message = error.to_string();
assert!(message.contains("does not support selector 'Records'"));
assert!(message.contains("Remove ':Records'"));
fs::remove_file(jsonl_path)?;
Ok(())
}
#[test]
fn reports_json_key_error_with_available_keys() -> Result<()> {
let json_path = temp_json_path("json-missing-key");
write_test_json_with_sections(&json_path)?;
let error = run_query(&json_path, Some("Missing"), "SELECT name FROM table", true)
.expect_err("missing JSON key should fail");
let message = error.to_string();
assert!(message.contains("JSON key 'Missing' not found"));
assert!(message.contains("Available keys: Archive, Inventory"));
fs::remove_file(json_path)?;
Ok(())
}
#[test]
fn reports_invalid_markdown_selector_with_guidance() -> Result<()> {
let markdown_path = temp_markdown_path("markdown-invalid-selector");
write_test_markdown_with_tables(&markdown_path)?;
let error = run_query(&markdown_path, Some("abc"), "SELECT name FROM table", true)
.expect_err("non-numeric markdown selector should fail");
let message = error.to_string();
assert!(message.contains("invalid Markdown table selector 'abc'"));
assert!(message.contains("':1'"));
fs::remove_file(markdown_path)?;
Ok(())
}
#[test]
fn reports_empty_markdown_table_as_actionable_error() -> Result<()> {
let markdown_path = temp_markdown_path("markdown-empty-table");
write_test_markdown_with_headers_only(&markdown_path)?;
let error = run_query(&markdown_path, None, "SELECT name FROM table", true)
.expect_err("empty markdown table should fail");
let message = error.to_string();
assert!(message.contains("is empty (no data rows)"));
fs::remove_file(markdown_path)?;
Ok(())
}
#[test]
fn reports_unknown_table_with_inspection_hint() -> Result<()> {
let workbook_path = temp_path("unknown-table-query");
write_test_workbook(&workbook_path)?;
let error = run_query(
&workbook_path,
Some("Sheet1"),
"SELECT * FROM missing_table",
true,
)
.expect_err("unknown table should fail");
let message = error.to_string();
assert!(message.contains("unknown table 'missing_table'"));
assert!(message.contains("qf tables --input"));
fs::remove_file(workbook_path)?;
Ok(())
}
#[test]
fn reports_unknown_column_with_schema_hint() -> Result<()> {
let workbook_path = temp_path("unknown-column-query");
write_test_workbook(&workbook_path)?;
let error = run_query(
&workbook_path,
Some("Sheet1"),
"SELECT missing_column FROM table",
true,
)
.expect_err("unknown column should fail");
let message = error.to_string();
assert!(message.contains("unknown column 'missing_column'"));
assert!(message.contains("qf schema --input"));
fs::remove_file(workbook_path)?;
Ok(())
}
#[test]
fn executes_sql_query_against_markdown_table_by_numeric_key() -> Result<()> {
let markdown_path = temp_markdown_path("markdown-key");
write_test_markdown_with_tables(&markdown_path)?;
let result = run_query(
&markdown_path,
Some("2"),
"SELECT name, price FROM table",
true,
)?;
assert_eq!(result.columns, vec!["name", "price"]);
assert_eq!(
result.rows,
vec![vec![
QueryValue::Text("Legacy Cable".into()),
QueryValue::Real(3.5)
]]
);
fs::remove_file(markdown_path)?;
Ok(())
}
#[test]
fn executes_query_against_heterogeneous_xlsx_xml_csv_jsonl_json_markdown_inputs() -> Result<()>
{
let workbook_path = temp_path("mixed4-xlsx");
let xml_path = temp_xml_path("mixed4-xml");
let csv_path = temp_csv_path("mixed4-csv");
let jsonl_path = temp_jsonl_path("mixed4-jsonl");
let json_path = temp_json_path("mixed4-json");
let markdown_path = temp_markdown_path("mixed4-markdown");
write_test_workbook(&workbook_path)?;
write_test_xml(&xml_path)?;
write_test_csv(&csv_path)?;
write_test_jsonl(&jsonl_path)?;
write_test_json(&json_path)?;
write_test_markdown_with_tables(&markdown_path)?;
let result = run_query_with_params_multi_inputs(
&[
WorkbookInput {
path: workbook_path.as_path(),
sheet_name: Some("Sheet1"),
table_name: None,
explicit_format: None,
},
WorkbookInput {
path: xml_path.as_path(),
sheet_name: None,
table_name: None,
explicit_format: None,
},
WorkbookInput {
path: csv_path.as_path(),
sheet_name: None,
table_name: None,
explicit_format: None,
},
WorkbookInput {
path: jsonl_path.as_path(),
sheet_name: None,
table_name: None,
explicit_format: None,
},
WorkbookInput {
path: json_path.as_path(),
sheet_name: None,
table_name: None,
explicit_format: None,
},
WorkbookInput {
path: markdown_path.as_path(),
sheet_name: None,
table_name: None,
explicit_format: None,
},
],
"SELECT COUNT(*) AS total_rows FROM table UNION ALL SELECT COUNT(*) AS total_rows FROM table2 UNION ALL SELECT COUNT(*) AS total_rows FROM table3 UNION ALL SELECT COUNT(*) AS total_rows FROM table4 UNION ALL SELECT COUNT(*) AS total_rows FROM table5 UNION ALL SELECT COUNT(*) AS total_rows FROM table6",
&[],
true,
)?;
assert_eq!(result.columns, vec!["total_rows"]);
assert_eq!(
result.rows,
vec![
vec![QueryValue::Integer(2)],
vec![QueryValue::Integer(2)],
vec![QueryValue::Integer(2)],
vec![QueryValue::Integer(2)],
vec![QueryValue::Integer(2)],
vec![QueryValue::Integer(2)]
]
);
fs::remove_file(&workbook_path)?;
fs::remove_file(&xml_path)?;
fs::remove_file(&csv_path)?;
fs::remove_file(&jsonl_path)?;
fs::remove_file(&json_path)?;
fs::remove_file(&markdown_path)?;
Ok(())
}
#[test]
fn writes_query_result_to_xlsx() -> Result<()> {
let output_path = temp_path("output");
let result = QueryResult {
columns: vec!["item".into(), "total".into()],
rows: vec![vec![
QueryValue::Text("Mouse".into()),
QueryValue::Integer(3),
]],
};
write_xlsx(&result, &output_path)?;
let written = run_query(
&output_path,
Some("Sheet1"),
"SELECT item, total FROM table",
true,
)?;
assert_eq!(written.columns, vec!["item", "total"]);
assert_eq!(
written.rows,
vec![vec![
QueryValue::Text("Mouse".into()),
QueryValue::Real(3.0)
]]
);
fs::remove_file(output_path)?;
Ok(())
}
#[test]
fn writes_query_result_to_parquet() -> Result<()> {
let output_path = temp_parquet_path("output");
let result = QueryResult {
columns: vec!["item".into(), "qty".into(), "price".into()],
rows: vec![
vec![
QueryValue::Text("Mouse".into()),
QueryValue::Integer(3),
QueryValue::Real(9.99),
],
vec![
QueryValue::Text("Keyboard".into()),
QueryValue::Integer(1),
QueryValue::Null,
],
],
};
write_parquet(&result, &output_path)?;
let bytes = fs::read(&output_path)?;
assert!(bytes.len() > 8, "parquet file should have content");
assert_eq!(&bytes[..4], b"PAR1", "parquet file should start with PAR1");
assert_eq!(
&bytes[bytes.len() - 4..],
b"PAR1",
"parquet file should end with PAR1"
);
fs::remove_file(output_path)?;
Ok(())
}
#[test]
fn writes_query_result_to_feather() -> Result<()> {
let output_path = temp_feather_path("output");
let result = QueryResult {
columns: vec!["item".into(), "qty".into(), "price".into()],
rows: vec![
vec![
QueryValue::Text("Mouse".into()),
QueryValue::Integer(3),
QueryValue::Real(9.99),
],
vec![
QueryValue::Text("Keyboard".into()),
QueryValue::Integer(1),
QueryValue::Null,
],
],
};
write_feather(&result, &output_path)?;
let bytes = fs::read(&output_path)?;
assert!(bytes.len() > 12, "feather file should have content");
assert_eq!(
&bytes[..6],
b"ARROW1",
"feather file should start with ARROW1"
);
assert_eq!(
&bytes[bytes.len() - 6..],
b"ARROW1",
"feather file should end with ARROW1"
);
fs::remove_file(output_path)?;
Ok(())
}
#[test]
fn executes_sql_query_against_parquet_file() -> Result<()> {
let parquet_path = temp_parquet_path("input");
let result = QueryResult {
columns: vec!["name".into(), "price".into(), "active".into()],
rows: vec![
vec![
QueryValue::Text("Keyboard".into()),
QueryValue::Real(12.5),
QueryValue::Integer(1),
],
vec![
QueryValue::Text("Cable".into()),
QueryValue::Real(5.0),
QueryValue::Integer(0),
],
],
};
write_parquet(&result, &parquet_path)?;
let queried = run_query(
&parquet_path,
None,
"SELECT name, price FROM table WHERE active = 1 ORDER BY price DESC",
true,
)?;
assert_eq!(queried.columns, vec!["name", "price"]);
assert_eq!(
queried.rows,
vec![vec![
QueryValue::Text("Keyboard".into()),
QueryValue::Real(12.5)
]]
);
fs::remove_file(parquet_path)?;
Ok(())
}
#[test]
fn executes_sql_query_against_feather_file() -> Result<()> {
let feather_path = temp_feather_path("input");
let result = QueryResult {
columns: vec!["name".into(), "price".into(), "active".into()],
rows: vec![
vec![
QueryValue::Text("Keyboard".into()),
QueryValue::Real(12.5),
QueryValue::Integer(1),
],
vec![
QueryValue::Text("Cable".into()),
QueryValue::Real(5.0),
QueryValue::Integer(0),
],
],
};
write_feather(&result, &feather_path)?;
let queried = run_query(
&feather_path,
None,
"SELECT name, price FROM table WHERE active = 1 ORDER BY price DESC",
true,
)?;
assert_eq!(queried.columns, vec!["name", "price"]);
assert_eq!(
queried.rows,
vec![vec![
QueryValue::Text("Keyboard".into()),
QueryValue::Real(12.5)
]]
);
fs::remove_file(feather_path)?;
Ok(())
}
#[test]
fn reports_actionable_error_for_parquet_selector() -> Result<()> {
let parquet_path = temp_parquet_path("parquet-selector-error");
let result = QueryResult {
columns: vec!["name".into()],
rows: vec![vec![QueryValue::Text("Keyboard".into())]],
};
write_parquet(&result, &parquet_path)?;
let error = run_query(
&parquet_path,
Some("Sheet1"),
"SELECT name FROM table",
true,
)
.expect_err("Parquet selector should fail");
let message = error.to_string();
assert!(message.contains("does not support selector 'Sheet1'"));
assert!(message.contains("Remove ':Sheet1'"));
fs::remove_file(parquet_path)?;
Ok(())
}
#[test]
fn reports_actionable_error_for_feather_selector() -> Result<()> {
let feather_path = temp_feather_path("feather-selector-error");
let result = QueryResult {
columns: vec!["name".into()],
rows: vec![vec![QueryValue::Text("Keyboard".into())]],
};
write_feather(&result, &feather_path)?;
let error = run_query(
&feather_path,
Some("Sheet1"),
"SELECT name FROM table",
true,
)
.expect_err("Feather selector should fail");
let message = error.to_string();
assert!(message.contains("does not support selector 'Sheet1'"));
assert!(message.contains("Remove ':Sheet1'"));
fs::remove_file(feather_path)?;
Ok(())
}
#[test]
fn renders_csv_with_escaping() {
let result = QueryResult {
columns: vec!["name".into(), "notes".into()],
rows: vec![vec![
QueryValue::Text("Mouse".into()),
QueryValue::Text("line1,line2".into()),
]],
};
assert_eq!(render_csv(&result), "name,notes\nMouse,\"line1,line2\"");
}
#[test]
fn streams_csv_with_escaping() {
let result = QueryResult {
columns: vec!["name".into(), "notes".into()],
rows: vec![vec![
QueryValue::Text("Mouse".into()),
QueryValue::Text("line1,line2".into()),
]],
};
let mut output = Vec::new();
write_csv(&result, &mut output).expect("streaming csv should succeed");
assert_eq!(
String::from_utf8(output).expect("valid utf-8"),
"name,notes\nMouse,\"line1,line2\""
);
}
#[test]
fn renders_text_with_aligned_columns() {
let result = QueryResult {
columns: vec!["mese".into(), "totale_ore".into()],
rows: vec![
vec![
QueryValue::Text("2026-01-01".into()),
QueryValue::Real(10.5),
],
vec![
QueryValue::Text("2026-12-01".into()),
QueryValue::Integer(2),
],
],
};
assert_eq!(
render_text(&result),
"mese | totale_ore\n-----------+-----------\n2026-01-01 | 10.5 \n2026-12-01 | 2 "
);
}
#[test]
fn streams_text_with_aligned_columns() {
let result = QueryResult {
columns: vec!["mese".into(), "totale_ore".into()],
rows: vec![
vec![
QueryValue::Text("2026-01-01".into()),
QueryValue::Real(10.5),
],
vec![
QueryValue::Text("2026-12-01".into()),
QueryValue::Integer(2),
],
],
};
let mut output = Vec::new();
write_text(&result, &mut output).expect("streaming text should succeed");
assert_eq!(
String::from_utf8(output).expect("valid utf-8"),
"mese | totale_ore\n-----------+-----------\n2026-01-01 | 10.5 \n2026-12-01 | 2 "
);
}
#[test]
fn renders_jsonl() {
let result = QueryResult {
columns: vec!["item".into(), "stock".into(), "note".into()],
rows: vec![vec![
QueryValue::Text("Desk".into()),
QueryValue::Integer(8),
QueryValue::Null,
]],
};
assert_eq!(
render_jsonl(&result),
"{\"item\":\"Desk\",\"stock\":8,\"note\":null}"
);
}
#[test]
fn streams_jsonl() {
let result = QueryResult {
columns: vec!["item".into(), "stock".into(), "note".into()],
rows: vec![vec![
QueryValue::Text("Desk".into()),
QueryValue::Integer(8),
QueryValue::Null,
]],
};
let mut output = Vec::new();
write_jsonl(&result, &mut output).expect("streaming jsonl should succeed");
assert_eq!(
String::from_utf8(output).expect("valid utf-8"),
"{\"item\":\"Desk\",\"stock\":8,\"note\":null}"
);
}
#[test]
fn renders_json() {
let result = QueryResult {
columns: vec!["item".into(), "stock".into(), "note".into()],
rows: vec![vec![
QueryValue::Text("Desk".into()),
QueryValue::Integer(8),
QueryValue::Null,
]],
};
assert_eq!(
render_json(&result),
"[{\"item\":\"Desk\",\"stock\":8,\"note\":null}]"
);
}
#[test]
fn renders_markdown() {
let result = QueryResult {
columns: vec!["category".into(), "total".into()],
rows: vec![vec![
QueryValue::Text("electronics".into()),
QueryValue::Integer(47),
]],
};
assert_eq!(
render_markdown(&result),
"| category | total |\n| --- | --- |\n| electronics | 47 |"
);
}
fn write_test_json_object(path: &Path) -> Result<()> {
fs::write(path, r#"{"name":"Alice","age":30,"city":"Rome"}"#)?;
Ok(())
}
fn write_test_json_nested(path: &Path) -> Result<()> {
fs::write(
path,
r#"[{"user":{"name":"Alice","address":{"city":"Rome"}},"score":10},{"user":{"name":"Bob","address":{"city":"Paris"}},"score":20}]"#,
)?;
Ok(())
}
#[test]
fn json_object_mode_turns_keys_into_rows() -> Result<()> {
let json_path = temp_json_path("json-object-mode");
write_test_json_object(&json_path)?;
let opts = ExtractionOptions {
json_mode: JsonMode::Object,
..ExtractionOptions::default()
};
let inputs = [WorkbookInput {
path: &json_path,
sheet_name: None,
table_name: None,
explicit_format: None,
}];
let result = run_query_with_params_multi_inputs_and_options_and_normalization(
&inputs,
"SELECT key, value FROM table ORDER BY key",
&[],
&TypeInferenceOptions::default(),
&InputNormalizationOptions::default(),
&opts,
true,
)?;
assert_eq!(result.columns, vec!["key", "value"]);
assert_eq!(result.rows.len(), 3);
assert_eq!(result.rows[0][0], QueryValue::Text("age".into()));
assert_eq!(result.rows[0][1], QueryValue::Integer(30));
assert_eq!(result.rows[1][0], QueryValue::Text("city".into()));
assert_eq!(result.rows[1][1], QueryValue::Text("Rome".into()));
assert_eq!(result.rows[2][0], QueryValue::Text("name".into()));
assert_eq!(result.rows[2][1], QueryValue::Text("Alice".into()));
fs::remove_file(json_path)?;
Ok(())
}
#[test]
fn json_flatten_mode_expands_nested_objects() -> Result<()> {
let json_path = temp_json_path("json-flatten-mode");
write_test_json_nested(&json_path)?;
let opts = ExtractionOptions {
json_mode: JsonMode::Flatten,
..ExtractionOptions::default()
};
let inputs = [WorkbookInput {
path: &json_path,
sheet_name: None,
table_name: None,
explicit_format: None,
}];
let result = run_query_with_params_multi_inputs_and_options_and_normalization(
&inputs,
"SELECT \"user.name\", \"user.address.city\", score FROM table ORDER BY score",
&[],
&TypeInferenceOptions::default(),
&InputNormalizationOptions::default(),
&opts,
true,
)?;
assert!(result.columns.contains(&"user.name".to_owned()));
assert!(result.columns.contains(&"user.address.city".to_owned()));
assert_eq!(result.rows.len(), 2);
let alice_col = result
.columns
.iter()
.position(|c| c == "user.name")
.unwrap();
let city_col = result
.columns
.iter()
.position(|c| c == "user.address.city")
.unwrap();
let score_col = result.columns.iter().position(|c| c == "score").unwrap();
assert_eq!(result.rows[0][alice_col], QueryValue::Text("Alice".into()));
assert_eq!(result.rows[0][city_col], QueryValue::Text("Rome".into()));
assert_eq!(result.rows[0][score_col], QueryValue::Integer(10));
fs::remove_file(json_path)?;
Ok(())
}
#[test]
fn json_array_mode_is_default_behavior() -> Result<()> {
let json_path = temp_json_path("json-array-mode-default");
write_test_json(&json_path)?;
let opts = ExtractionOptions {
json_mode: JsonMode::Array,
..ExtractionOptions::default()
};
let inputs = [WorkbookInput {
path: &json_path,
sheet_name: None,
table_name: None,
explicit_format: None,
}];
let result = run_query_with_params_multi_inputs_and_options_and_normalization(
&inputs,
"SELECT name, price FROM table WHERE active = 1",
&[],
&TypeInferenceOptions::default(),
&InputNormalizationOptions::default(),
&opts,
true,
)?;
assert_eq!(result.columns, vec!["name", "price"]);
assert_eq!(
result.rows,
vec![vec![
QueryValue::Text("Keyboard".into()),
QueryValue::Real(12.5)
]]
);
fs::remove_file(json_path)?;
Ok(())
}
fn write_test_xml_with_attributes(path: &Path) -> Result<()> {
fs::write(
path,
r#"<?xml version="1.0" encoding="UTF-8"?>
<products>
<product id="1" name="Keyboard" price="12.5" active="true"/>
<product id="2" name="Cable" price="5" active="false"/>
</products>
"#,
)?;
Ok(())
}
fn write_test_xml_leaf_elements(path: &Path) -> Result<()> {
fs::write(
path,
r#"<?xml version="1.0" encoding="UTF-8"?>
<config>
<host>localhost</host>
<port>5432</port>
<database>mydb</database>
</config>
"#,
)?;
Ok(())
}
#[test]
fn xml_attributes_mode_extracts_attributes_as_columns() -> Result<()> {
let xml_path = temp_xml_path("xml-attributes-mode");
write_test_xml_with_attributes(&xml_path)?;
let opts = ExtractionOptions {
xml_mode: XmlMode::Attributes,
..ExtractionOptions::default()
};
let inputs = [WorkbookInput {
path: &xml_path,
sheet_name: None,
table_name: None,
explicit_format: None,
}];
let result = run_query_with_params_multi_inputs_and_options_and_normalization(
&inputs,
"SELECT id, name, price FROM table ORDER BY id",
&[],
&TypeInferenceOptions::default(),
&InputNormalizationOptions::default(),
&opts,
true,
)?;
assert_eq!(result.rows.len(), 2);
let id_col = result.columns.iter().position(|c| c == "id").unwrap();
let name_col = result.columns.iter().position(|c| c == "name").unwrap();
assert_eq!(result.rows[0][id_col], QueryValue::Integer(1));
assert_eq!(
result.rows[0][name_col],
QueryValue::Text("Keyboard".into())
);
assert_eq!(result.rows[1][id_col], QueryValue::Integer(2));
assert_eq!(result.rows[1][name_col], QueryValue::Text("Cable".into()));
fs::remove_file(xml_path)?;
Ok(())
}
#[test]
fn xml_descendants_mode_collects_leaf_elements_as_tag_value_rows() -> Result<()> {
let xml_path = temp_xml_path("xml-descendants-mode");
write_test_xml_leaf_elements(&xml_path)?;
let opts = ExtractionOptions {
xml_mode: XmlMode::Descendants,
..ExtractionOptions::default()
};
let inputs = [WorkbookInput {
path: &xml_path,
sheet_name: None,
table_name: None,
explicit_format: None,
}];
let result = run_query_with_params_multi_inputs_and_options_and_normalization(
&inputs,
"SELECT tag, value FROM table ORDER BY tag",
&[],
&TypeInferenceOptions::default(),
&InputNormalizationOptions::default(),
&opts,
true,
)?;
assert_eq!(result.columns, vec!["tag", "value"]);
assert_eq!(result.rows.len(), 3);
assert_eq!(result.rows[0][0], QueryValue::Text("database".into()));
assert_eq!(result.rows[0][1], QueryValue::Text("mydb".into()));
assert_eq!(result.rows[1][0], QueryValue::Text("host".into()));
assert_eq!(result.rows[1][1], QueryValue::Text("localhost".into()));
assert_eq!(result.rows[2][0], QueryValue::Text("port".into()));
assert_eq!(result.rows[2][1], QueryValue::Integer(5432));
fs::remove_file(xml_path)?;
Ok(())
}
#[test]
fn xml_rows_mode_is_default_behavior() -> Result<()> {
let xml_path = temp_xml_path("xml-rows-mode-default");
write_test_xml(&xml_path)?;
let opts = ExtractionOptions {
xml_mode: XmlMode::Rows,
..ExtractionOptions::default()
};
let inputs = [WorkbookInput {
path: &xml_path,
sheet_name: None,
table_name: None,
explicit_format: None,
}];
let result = run_query_with_params_multi_inputs_and_options_and_normalization(
&inputs,
"SELECT name, price FROM table WHERE active = 1 ORDER BY price DESC",
&[],
&TypeInferenceOptions::default(),
&InputNormalizationOptions::default(),
&opts,
true,
)?;
assert_eq!(result.columns, vec!["name", "price"]);
assert_eq!(
result.rows,
vec![vec![
QueryValue::Text("Keyboard".into()),
QueryValue::Real(12.5)
]]
);
fs::remove_file(xml_path)?;
Ok(())
}
#[test]
fn executes_query_against_csv_from_clipboard() -> Result<()> {
clipboard::set_test_text("product,price\nKeyboard,12.5\nCable,5.0\n");
let result = run_query(
Path::new("@clipboard.csv"),
None,
"SELECT product FROM table WHERE CAST(price AS REAL) > 10",
true,
)?;
assert_eq!(result.columns, vec!["product"]);
assert_eq!(
result.rows,
vec![vec![QueryValue::Text("Keyboard".to_owned())]]
);
Ok(())
}
#[test]
fn loads_markdown_table_from_clipboard_by_index() -> Result<()> {
clipboard::set_test_text(
"| name | value |\n| --- | --- |\n| first | 1 |\n\n| name | value |\n| --- | --- |\n| second | 2 |\n",
);
let sheet = load_input(
Path::new("@clipboard.md"),
Some("2"),
&TypeInferenceOptions::default(),
&ExtractionOptions::default(),
true,
None,
)?;
assert_eq!(sheet.columns, vec!["name", "value"]);
assert_eq!(
sheet.rows,
vec![vec![
QueryValue::Text("second".to_owned()),
QueryValue::Integer(2),
]]
);
Ok(())
}
}