use std::path::PathBuf;
use clap::{ArgAction, ArgGroup, Args, Parser, Subcommand, ValueEnum};
#[derive(Debug, Parser)]
#[command(
author,
version,
about = "Query XLSX/XML/CSV/JSON/JSONL/Markdown/Parquet datasets with SQL"
)]
pub(crate) struct Cli {
#[command(subcommand)]
pub(crate) command: Commands,
}
#[derive(Debug, Subcommand)]
pub(crate) enum Commands {
#[command(after_long_help = "Examples:\n\
qf query --input ./input.xlsx:Sheet1 --sql \"SELECT * FROM table\"\n\
qf query --input ./inventory.xml:Inventory --sql \"SELECT product, price FROM table WHERE active = 1\"\n\
qf query --input ./inventory.csv --sql \"SELECT product, price FROM table WHERE active = 1\"\n\
qf query --input ./inventory.json:Inventory --sql \"SELECT product, price FROM table WHERE active = 1\"\n\
qf query --input ./inventory.jsonl --sql \"SELECT product, price FROM table WHERE active = 1\"\n\
qf query --input ./a.xlsx:Sheet1 --input ./b.csv --sql \"SELECT * FROM table UNION ALL SELECT * FROM table2\"\n\
qf query --input sales=./sales.xlsx:Q1 --input costs=./costs.csv --sql \"SELECT * FROM sales JOIN costs ON sales.id = costs.id\"\n\
qf query --input ./inventory.md --sql \"SELECT product, price FROM table WHERE active = 1\"\n\
qf query --input ./inventory.md:2 --sql \"SELECT product, price FROM table\"\n\
qf query --input ./input.xlsx:Sheet1 --sql-file ./query.sql --output ./result.csv --format csv\n\
qf query --input ./summary.json --sql \"SELECT key, value FROM table\" --json-mode object\n\
qf query --input ./record.json --sql \"SELECT \\\"user.name\\\", \\\"address.city\\\" FROM table\" --json-mode flatten\n\
qf query --input ./config.xml --sql \"SELECT tag, value FROM table WHERE tag = 'timeout'\" --xml-mode descendants\n\
qf query --input ./items.xml --sql \"SELECT id, type, value FROM table\" --xml-mode attributes\n\
\n\
Query source:\n\
Provide SQL inline with --sql or load it from disk with --sql-file.\n\
\n\
Table naming:\n\
Without explicit names, inputs become table, table2, table3, ...\n\
Prefix inputs as NAME=PATH to use readable table names in SQL.\n\
\n\
JSON extraction modes (--json-mode):\n\
array (default): each element of a top-level array becomes a row.\n\
object: each key-value pair of a top-level object becomes a row with 'key' and 'value' columns.\n\
flatten: recursively flattens nested objects/arrays into a single row using dot-separated column names.\n\
\n\
XML extraction modes (--xml-mode):\n\
rows (default): detect and extract tabular rows from the XML structure.\n\
descendants: collect every leaf text element as a row with 'tag' and 'value' columns.\n\
attributes: collect elements with attributes; each attribute name becomes a column.")]
Query(QueryCommand),
#[command(after_long_help = "Examples:\n\
qf tables --input sales=./sales.xlsx:Q1 --input costs=./costs.csv\n\
qf tables --input ./inventory.json:Inventory --format json\n\
qf tables --input ./inventory.xml:Inventory --input ./inventory.csv --format markdown\n\
Output:\n\
Lists the logical SQL table names created from your inputs, in input order.\n\
Use this command before writing queries when you want to confirm automatic table names.")]
Tables(TablesCommand),
#[command(after_long_help = "Examples:\n\
qf schema --input ./inventory.json:Inventory\n\
qf schema --input sales=./sales.xlsx:Q1 --input costs=./costs.csv --format json\n\
qf schema --input ./inventory.xml:Inventory --format markdown\n\
Output:\n\
Shows one row per column with the logical table name and inferred SQL-compatible type.\n\
Use this command to verify names and types before writing filters, joins, or casts.")]
Schema(SchemaCommand),
#[command(after_long_help = "Examples:\n\
qf inspect --input ./inventory.md:2 --sample 5\n\
qf inspect --input sales=./sales.xlsx:Q1 --input costs=./costs.csv --format json\n\
qf inspect --input ./inventory.csv --stats --format markdown\n\
Output:\n\
Includes a compact summary for each table, sampled rows, and optional extra metrics.\n\
Use this command when you need fast diagnostics before or after adjusting normalization flags.")]
Inspect(InspectCommand),
}
#[derive(Debug, Args)]
#[command(group(
ArgGroup::new("query_source")
.required(true)
.args(["sql", "sql_file"])
))]
pub(crate) struct QueryCommand {
#[arg(
short,
long,
required = true,
action = ArgAction::Append,
value_name = "[NAME=]PATH[:SHEET|KEY]",
long_help = "Input dataset path. Supported formats: .xlsx, .xml, .csv, .json, .jsonl, .md, .markdown, .parquet.\n\
Optionally prefix with a table name: NAME=PATH (e.g. sales=./sales.xlsx:Q1).\n\
Without a name prefix, tables are named 'table', 'table2', 'table3', ...\n\
For XLSX use PATH:SheetName (or PATH#SheetName) to select a worksheet.\n\
For XML use PATH:TagName (or PATH#TagName) to select a subtree; without sheet/tag the whole file is used.\n\
For JSON use PATH:Key (or PATH#Key) to select a top-level key; without key the whole JSON root is used.\n\
For Markdown use PATH:N (or PATH#N), where N is the 1-based table number; without key the first table is used.\n\
CSV, JSONL and Parquet do not support sheet/key selection."
)]
pub(crate) input: Vec<String>,
#[arg(
long,
alias = "query",
short = 'q',
conflicts_with = "sql_file",
value_name = "SQL",
long_help = "SQL query to execute. Use 'table', 'table2', 'table3', ... or explicit table names.\n\
The flag --query is also accepted as a legacy alias."
)]
pub(crate) sql: Option<String>,
#[arg(
long = "sql-file",
value_name = "PATH",
conflicts_with = "sql",
long_help = "Path to a file containing the SQL query to execute.\n\
The file is read as UTF-8 text and trimmed before execution."
)]
pub(crate) sql_file: Option<PathBuf>,
#[arg(
long = "infer-types",
conflicts_with = "all_text",
long_help = "Infer typed SQLite values from input data when possible.\n\
This is useful for numeric comparisons, boolean filtering, and date-aware ingestion."
)]
pub(crate) infer_types: bool,
#[arg(
long = "all-text",
conflicts_with = "infer_types",
long_help = "Load all incoming values as text instead of inferring typed SQLite values.\n\
Use this when you want exact string preservation or need to avoid automatic conversions."
)]
pub(crate) all_text: bool,
#[arg(
long = "decimal-comma",
long_help = "Interpret commas as decimal separators during type inference.\n\
Useful for locale-formatted values such as 12,50."
)]
pub(crate) decimal_comma: bool,
#[arg(
long = "date-format",
value_name = "STRFTIME",
long_help = "Expected date format used during type inference, for example %d/%m/%Y."
)]
pub(crate) date_format: Option<String>,
#[arg(
long = "null-values",
value_delimiter = ',',
action = ArgAction::Append,
value_name = "VALUE[,VALUE...]",
long_help = "Additional strings to interpret as NULL during type inference.\n\
Repeat the flag or pass comma-separated values."
)]
pub(crate) null_values: Vec<String>,
#[arg(
long = "true-values",
value_delimiter = ',',
action = ArgAction::Append,
value_name = "VALUE[,VALUE...]",
long_help = "Additional strings to interpret as boolean true during type inference.\n\
Repeat the flag or pass comma-separated values."
)]
pub(crate) true_values: Vec<String>,
#[arg(
long = "false-values",
value_delimiter = ',',
action = ArgAction::Append,
value_name = "VALUE[,VALUE...]",
long_help = "Additional strings to interpret as boolean false during type inference.\n\
Repeat the flag or pass comma-separated values."
)]
pub(crate) false_values: Vec<String>,
#[arg(
long = "trim",
long_help = "Trim surrounding whitespace from every cell before loading it."
)]
pub(crate) trim: bool,
#[arg(
long = "skip-empty-rows",
long_help = "Discard rows whose values are entirely empty after normalization."
)]
pub(crate) skip_empty_rows: bool,
#[arg(
long = "normalize-headers",
long_help = "Normalize header names before loading them into SQLite.\n\
Combine with --header-case and --dedupe-headers for more stable schemas."
)]
pub(crate) normalize_headers: bool,
#[arg(
long = "header-case",
value_enum,
requires = "normalize_headers",
long_help = "Header naming style to apply after normalization.\n\
Requires --normalize-headers."
)]
pub(crate) header_case: Option<HeaderCase>,
#[arg(
long = "dedupe-headers",
long_help = "Make duplicate normalized headers unique by appending numeric suffixes."
)]
pub(crate) dedupe_headers: bool,
#[arg(
long = "param",
value_name = "NAME=VALUE",
action = ArgAction::Append,
long_help = "Bind a named SQL parameter.\n\
Repeat the flag for multiple parameters; values are parsed as null, booleans, integers, reals, or text."
)]
pub(crate) params: Vec<String>,
#[arg(
short,
long,
value_name = "PATH",
long_help = "Write query results to a file instead of stdout.\n\
Pair with --format to choose the serialization format when the extension is ambiguous."
)]
pub(crate) output: Option<PathBuf>,
#[arg(
long,
value_enum,
long_help = "Output format for query results.\n\
Supported values: text, csv, json, jsonl, markdown, xlsx, xml, parquet."
)]
pub(crate) format: Option<OutputFormat>,
#[arg(
long = "no-headers",
long_help = "Treat the first row as data instead of column headers.\n\
Generated column names follow the pattern column1, column2, ..."
)]
pub(crate) no_headers: bool,
#[arg(
long = "json-mode",
value_enum,
long_help = "JSON extraction strategy.\n\
'array' (default): each element of a top-level JSON array becomes a row.\n\
'object': each key-value pair of a JSON object becomes a row with 'key' and 'value' columns.\n\
'flatten': recursively flatten nested JSON objects and arrays using dotted key paths."
)]
pub(crate) json_mode: Option<JsonMode>,
#[arg(
long = "xml-mode",
value_enum,
long_help = "XML extraction strategy.\n\
'rows' (default): detect and extract tabular rows from the XML structure.\n\
'descendants': collect every leaf text element as a row with 'tag' and 'value' columns.\n\
'attributes': extract element attributes as columns; each element with attributes becomes a row."
)]
pub(crate) xml_mode: Option<XmlMode>,
#[arg(
long,
long_help = "Print query execution metadata to stderr after running the query.\n\
Includes row count, output column names, input tables loaded, and execution time.\n\
Use --meta-format to choose between text (default) and json output."
)]
pub(crate) meta: bool,
#[arg(
long = "meta-format",
value_enum,
requires = "meta",
long_help = "Format for metadata output printed to stderr.\n\
Supported values: text (default), json.\n\
Requires --meta."
)]
pub(crate) meta_format: Option<MetadataFormat>,
}
#[derive(Debug, Args)]
pub(crate) struct TablesCommand {
#[arg(
short,
long,
required = true,
action = ArgAction::Append,
value_name = "[NAME=]PATH[:SHEET|KEY]",
long_help = "Input dataset path. Supported formats: .xlsx, .xml, .csv, .json, .jsonl, .md, .markdown, .parquet.\n\
Optionally prefix with a table name: NAME=PATH (e.g. sales=./sales.xlsx:Q1).\n\
Without a name prefix, tables are named 'table', 'table2', 'table3', ...\n\
For XLSX use PATH:SheetName to select a worksheet.\n\
For XML use PATH:TagName to select a subtree.\n\
For JSON use PATH:Key to select a top-level key.\n\
For Markdown use PATH:N for the 1-based table index."
)]
pub(crate) input: Vec<String>,
#[arg(
short,
long,
value_name = "SELECTOR",
long_help = "Default sheet/tag/key applied to every --input that does not already specify one."
)]
pub(crate) sheet: Option<String>,
#[arg(
long,
value_enum,
default_value = "text",
long_help = "Output format for table discovery results. Supported values: text, json, markdown."
)]
pub(crate) format: InspectionFormat,
}
#[derive(Debug, Args)]
pub(crate) struct SchemaCommand {
#[arg(
short,
long,
required = true,
action = ArgAction::Append,
value_name = "[NAME=]PATH[:SHEET|KEY]",
long_help = "Input dataset path. Supported formats: .xlsx, .xml, .csv, .json, .jsonl, .md, .markdown, .parquet.\n\
Optionally prefix with a table name: NAME=PATH (e.g. sales=./sales.xlsx:Q1).\n\
Without a name prefix, tables are named 'table', 'table2', 'table3', ...\n\
For XLSX use PATH:SheetName to select a worksheet.\n\
For XML use PATH:TagName to select a subtree.\n\
For JSON use PATH:Key to select a top-level key.\n\
For Markdown use PATH:N for the 1-based table index."
)]
pub(crate) input: Vec<String>,
#[arg(
short,
long,
value_name = "SELECTOR",
long_help = "Default sheet/tag/key applied to every --input that does not already specify one."
)]
pub(crate) sheet: Option<String>,
#[arg(
long,
value_enum,
default_value = "text",
long_help = "Output format for schema results. Supported values: text, json, markdown."
)]
pub(crate) format: InspectionFormat,
}
#[derive(Debug, Args)]
pub(crate) struct InspectCommand {
#[arg(
short,
long,
required = true,
action = ArgAction::Append,
value_name = "[NAME=]PATH[:SHEET|KEY]",
long_help = "Input dataset path. Supported formats: .xlsx, .xml, .csv, .json, .jsonl, .md, .markdown, .parquet.\n\
Optionally prefix with a table name: NAME=PATH (e.g. sales=./sales.xlsx:Q1).\n\
Without a name prefix, tables are named 'table', 'table2', 'table3', ...\n\
For XLSX use PATH:SheetName to select a worksheet.\n\
For XML use PATH:TagName to select a subtree.\n\
For JSON use PATH:Key to select a top-level key.\n\
For Markdown use PATH:N for the 1-based table index."
)]
pub(crate) input: Vec<String>,
#[arg(
short,
long,
value_name = "SELECTOR",
long_help = "Default sheet/tag/key applied to every --input that does not already specify one."
)]
pub(crate) sheet: Option<String>,
#[arg(
long,
value_enum,
default_value = "text",
long_help = "Output format for inspection results. Supported values: text, json, markdown."
)]
pub(crate) format: InspectionFormat,
#[arg(
long,
default_value = "5",
long_help = "Number of sample rows to include per table in the inspection output."
)]
pub(crate) sample: usize,
#[arg(
long,
long_help = "Include additional metrics such as distinct counts and numeric min/max values when available."
)]
pub(crate) stats: bool,
}
#[derive(Clone, Debug, ValueEnum)]
pub(crate) enum OutputFormat {
Text,
Csv,
Json,
Jsonl,
Markdown,
Xlsx,
Xml,
Parquet,
}
#[derive(Clone, Debug, ValueEnum)]
pub(crate) enum InspectionFormat {
Text,
Json,
Markdown,
}
#[derive(Clone, Debug, ValueEnum)]
pub(crate) enum MetadataFormat {
Text,
Json,
}
#[derive(Clone, Debug, ValueEnum)]
pub(crate) enum HeaderCase {
Snake,
}
#[derive(Clone, Debug, ValueEnum)]
pub(crate) enum JsonMode {
Array,
Object,
Flatten,
}
#[derive(Clone, Debug, ValueEnum)]
pub(crate) enum XmlMode {
Rows,
Descendants,
Attributes,
}
#[cfg(test)]
mod tests {
use clap::Parser;
use super::{Cli, Commands};
#[test]
fn parses_configurable_type_inference_flags() {
let cli = Cli::parse_from([
"qf",
"query",
"--input",
"data.csv",
"--sql",
"SELECT * FROM table",
"--all-text",
"--decimal-comma",
"--date-format",
"%d/%m/%Y",
"--null-values",
"NULL,N/A",
"--true-values",
"YES,Y",
"--false-values",
"NO,N",
]);
let Commands::Query(cmd) = cli.command else {
panic!("expected query command");
};
assert!(cmd.all_text);
assert!(cmd.decimal_comma);
assert_eq!(cmd.date_format.as_deref(), Some("%d/%m/%Y"));
assert_eq!(cmd.null_values, vec!["NULL", "N/A"]);
assert_eq!(cmd.true_values, vec!["YES", "Y"]);
assert_eq!(cmd.false_values, vec!["NO", "N"]);
}
#[test]
fn parses_input_normalization_flags() {
let cli = Cli::parse_from([
"qf",
"query",
"--input",
"data.csv",
"--sql",
"SELECT * FROM table",
"--trim",
"--skip-empty-rows",
"--normalize-headers",
"--header-case",
"snake",
"--dedupe-headers",
]);
let Commands::Query(cmd) = cli.command else {
panic!("expected query command");
};
assert!(cmd.trim);
assert!(cmd.skip_empty_rows);
assert!(cmd.normalize_headers);
assert!(matches!(cmd.header_case, Some(super::HeaderCase::Snake)));
assert!(cmd.dedupe_headers);
}
#[test]
fn rejects_infer_types_and_all_text_together() {
let parsed = Cli::try_parse_from([
"qf",
"query",
"--input",
"data.csv",
"--sql",
"SELECT * FROM table",
"--infer-types",
"--all-text",
]);
assert!(parsed.is_err());
}
#[test]
fn parses_sql_file_flag() {
let cli = Cli::parse_from([
"qf",
"query",
"--input",
"data.csv",
"--sql-file",
"query.sql",
]);
let Commands::Query(cmd) = cli.command else {
panic!("expected query command");
};
assert_eq!(cmd.sql_file.as_deref(), Some(std::path::Path::new("query.sql")));
}
#[test]
fn parses_json_mode_flag() {
let cli = Cli::parse_from([
"qf",
"query",
"--input",
"data.json",
"--sql",
"SELECT * FROM table",
"--json-mode",
"flatten",
]);
let Commands::Query(cmd) = cli.command else {
panic!("expected query command");
};
assert!(matches!(cmd.json_mode, Some(super::JsonMode::Flatten)));
}
#[test]
fn parses_xml_mode_flag() {
let cli = Cli::parse_from([
"qf",
"query",
"--input",
"data.xml",
"--sql",
"SELECT * FROM table",
"--xml-mode",
"attributes",
]);
let Commands::Query(cmd) = cli.command else {
panic!("expected query command");
};
assert!(matches!(cmd.xml_mode, Some(super::XmlMode::Attributes)));
}
#[test]
fn json_mode_defaults_to_none_when_not_specified() {
let cli = Cli::parse_from([
"qf",
"query",
"--input",
"data.json",
"--sql",
"SELECT * FROM table",
]);
let Commands::Query(cmd) = cli.command else {
panic!("expected query command");
};
assert!(cmd.json_mode.is_none());
assert!(cmd.xml_mode.is_none());
}
#[test]
fn parses_meta_flag() {
let cli = Cli::parse_from([
"qf",
"query",
"--input",
"data.csv",
"--sql",
"SELECT * FROM table",
"--meta",
]);
let Commands::Query(cmd) = cli.command else {
panic!("expected query command");
};
assert!(cmd.meta);
assert!(cmd.meta_format.is_none());
}
#[test]
fn parses_meta_with_json_format() {
let cli = Cli::parse_from([
"qf",
"query",
"--input",
"data.csv",
"--sql",
"SELECT * FROM table",
"--meta",
"--meta-format",
"json",
]);
let Commands::Query(cmd) = cli.command else {
panic!("expected query command");
};
assert!(cmd.meta);
assert!(matches!(cmd.meta_format, Some(super::MetadataFormat::Json)));
}
#[test]
fn parses_meta_with_text_format() {
let cli = Cli::parse_from([
"qf",
"query",
"--input",
"data.csv",
"--sql",
"SELECT * FROM table",
"--meta",
"--meta-format",
"text",
]);
let Commands::Query(cmd) = cli.command else {
panic!("expected query command");
};
assert!(cmd.meta);
assert!(matches!(cmd.meta_format, Some(super::MetadataFormat::Text)));
}
#[test]
fn meta_flag_is_false_when_not_specified() {
let cli = Cli::parse_from([
"qf",
"query",
"--input",
"data.csv",
"--sql",
"SELECT * FROM table",
]);
let Commands::Query(cmd) = cli.command else {
panic!("expected query command");
};
assert!(!cmd.meta);
assert!(cmd.meta_format.is_none());
}
}