use assert_cmd::Command;
use std::fs;
use tempfile::tempdir;
fn query(dir: &std::path::Path, sql: &str) -> String {
let output = Command::cargo_bin("sheeter")
.unwrap()
.current_dir(dir)
.args(["query", sql])
.output()
.unwrap();
assert!(
output.status.success(),
"query failed: {}",
String::from_utf8_lossy(&output.stderr)
);
String::from_utf8(output.stdout)
.unwrap()
.trim_end()
.to_string()
}
#[test]
fn select_all_columns() {
let dir = tempdir().unwrap();
fs::write(dir.path().join("t.csv"), "a,b\n1,x\n2,y\n").unwrap();
let out = query(dir.path(), "SELECT * FROM t.csv");
assert_eq!(out, r#"[{"a":"1","b":"x"},{"a":"2","b":"y"}]"#);
}
#[test]
fn where_filters_rows() {
let dir = tempdir().unwrap();
fs::write(dir.path().join("t.csv"), "n\n1\n2\n3\n").unwrap();
let out = query(dir.path(), "SELECT n FROM t.csv WHERE n >= 2");
assert_eq!(out, r#"[{"n":"2"},{"n":"3"}]"#);
}
#[test]
fn order_by_desc() {
let dir = tempdir().unwrap();
fs::write(dir.path().join("t.csv"), "n\n3\n1\n2\n").unwrap();
let out = query(dir.path(), "SELECT n FROM t.csv ORDER BY n DESC");
assert_eq!(out, r#"[{"n":"3"},{"n":"2"},{"n":"1"}]"#);
}
#[test]
fn limit_caps_rows() {
let dir = tempdir().unwrap();
fs::write(dir.path().join("t.csv"), "n\n1\n2\n3\n4\n").unwrap();
let out = query(dir.path(), "SELECT n FROM t.csv LIMIT 2");
assert_eq!(out, r#"[{"n":"1"},{"n":"2"}]"#);
}
#[test]
fn group_by_sum() {
let dir = tempdir().unwrap();
fs::write(
dir.path().join("t.csv"),
"k,v\nBR,100\nUS,200\nBR,300\nUS,50\n",
)
.unwrap();
let out = query(
dir.path(),
"SELECT k, SUM(v) AS total FROM t.csv GROUP BY k ORDER BY k",
);
assert_eq!(out, r#"[{"k":"BR","total":400},{"k":"US","total":250}]"#);
}
#[test]
fn join_across_formats() {
let dir = tempdir().unwrap();
fs::write(dir.path().join("users.csv"), "id,name\n1,Alice\n2,Bob\n").unwrap();
fs::write(
dir.path().join("orders.json"),
r#"[{"user_id":"1","amount":10},{"user_id":"2","amount":20}]"#,
)
.unwrap();
let out = query(
dir.path(),
"SELECT u.name, o.amount FROM users.csv u JOIN orders.json o ON u.id = o.user_id ORDER BY u.name",
);
assert_eq!(
out,
r#"[{"name":"Alice","amount":10},{"name":"Bob","amount":20}]"#
);
}
#[test]
fn arithmetic_expression() {
let dir = tempdir().unwrap();
fs::write(dir.path().join("t.csv"), "price,qty\n10,3\n5,2\n").unwrap();
let out = query(
dir.path(),
"SELECT price * qty AS total FROM t.csv ORDER BY total DESC",
);
assert_eq!(out, r#"[{"total":30},{"total":10}]"#);
}
#[test]
fn glob_loads_multiple_files() {
let dir = tempdir().unwrap();
fs::create_dir_all(dir.path().join("d")).unwrap();
fs::write(dir.path().join("d/a.json"), r#"[{"x":1}]"#).unwrap();
fs::write(dir.path().join("d/b.json"), r#"[{"x":2}]"#).unwrap();
let out = query(dir.path(), r#"SELECT x FROM "d/*.json" ORDER BY x"#);
assert_eq!(out, r#"[{"x":1},{"x":2}]"#);
}
#[test]
fn distinct_dedupes_rows() {
let dir = tempdir().unwrap();
fs::write(dir.path().join("t.csv"), "k\nA\nB\nA\nB\nA\n").unwrap();
let out = query(dir.path(), "SELECT DISTINCT k FROM t.csv ORDER BY k");
assert_eq!(out, r#"[{"k":"A"},{"k":"B"}]"#);
}
#[test]
fn null_join_keys_do_not_match() {
let dir = tempdir().unwrap();
fs::write(
dir.path().join("users.json"),
r#"[{"id":1,"name":"Alice"},{"id":null,"name":"Nobody"}]"#,
)
.unwrap();
fs::write(
dir.path().join("orders.json"),
r#"[{"user_id":1,"product":"A"},{"user_id":null,"product":"X"}]"#,
)
.unwrap();
let out = query(
dir.path(),
"SELECT u.name, o.product FROM users.json u JOIN orders.json o ON u.id = o.user_id",
);
let parsed: serde_json::Value = serde_json::from_str(&out).unwrap();
let arr = parsed.as_array().unwrap();
assert_eq!(arr.len(), 1, "NULL keys must not match: {out}");
assert_eq!(arr[0]["name"], "Alice");
}
#[test]
fn null_in_where_filters_out() {
let dir = tempdir().unwrap();
fs::write(dir.path().join("t.json"), r#"[{"a":1},{"a":null},{"a":2}]"#).unwrap();
let out = query(dir.path(), "SELECT a FROM t.json WHERE a >= 1");
let parsed: serde_json::Value = serde_json::from_str(&out).unwrap();
let arr = parsed.as_array().unwrap();
assert_eq!(arr.len(), 2, "NULL filtered by WHERE: {out}");
}
#[test]
fn join_xlsx_with_json() {
use sheeter_engine::CanonicalValue;
let dir = tempdir().unwrap();
let users = CanonicalValue::from_csv("id,name\n1,Alice\n2,Bob\n").unwrap();
let bytes = CanonicalValue::to_xlsx_workbook(&[("Sheet1".to_string(), users)]).unwrap();
fs::write(dir.path().join("users.xlsx"), bytes).unwrap();
fs::write(
dir.path().join("orders.json"),
r#"[{"user_id":1,"amount":10},{"user_id":2,"amount":20}]"#,
)
.unwrap();
let out = query(
dir.path(),
"SELECT u.name, o.amount FROM users.xlsx u JOIN orders.json o ON u.id = o.user_id ORDER BY u.name",
);
assert_eq!(
out,
r#"[{"name":"Alice","amount":10},{"name":"Bob","amount":20}]"#
);
}
#[test]
fn join_mixed_numeric_and_string_keys() {
let dir = tempdir().unwrap();
fs::write(dir.path().join("u.csv"), "id,name\n1,Alice\n2,Bob\n").unwrap();
fs::write(
dir.path().join("o.json"),
r#"[{"uid":1,"v":10},{"uid":2,"v":20}]"#,
)
.unwrap();
let out = query(
dir.path(),
"SELECT u.name, o.v FROM u.csv u JOIN o.json o ON u.id = o.uid ORDER BY u.name",
);
assert_eq!(out, r#"[{"name":"Alice","v":10},{"name":"Bob","v":20}]"#);
}
#[test]
fn stdout_output_ends_with_newline() {
let dir = tempdir().unwrap();
fs::write(dir.path().join("t.csv"), "a\n1\n").unwrap();
let output = Command::cargo_bin("sheeter")
.unwrap()
.current_dir(dir.path())
.args(["query", "SELECT a FROM t.csv"])
.output()
.unwrap();
assert!(output.status.success());
let raw = String::from_utf8(output.stdout).unwrap();
assert!(raw.ends_with('\n'), "stdout must end with newline: {raw:?}");
}