use std::io::Write;
use std::path::{Path, PathBuf};
use std::process::{Command, Output, Stdio};
fn workspace() -> PathBuf {
Path::new(env!("CARGO_MANIFEST_DIR"))
.parent()
.unwrap()
.to_path_buf()
}
fn karate_edges() -> String {
workspace()
.join("python/ursa/datasets/data/karate_edges.csv")
.display()
.to_string()
}
fn ursa(args: &[&str]) -> Output {
Command::new(env!("CARGO_BIN_EXE_ursa"))
.args(args)
.current_dir(workspace())
.output()
.expect("ursa runs")
}
fn ursa_with_stdin(args: &[&str], stdin: &str) -> Output {
let mut child = Command::new(env!("CARGO_BIN_EXE_ursa"))
.args(args)
.current_dir(workspace())
.stdin(Stdio::piped())
.stdout(Stdio::piped())
.stderr(Stdio::piped())
.spawn()
.expect("ursa runs");
child
.stdin
.take()
.unwrap()
.write_all(stdin.as_bytes())
.unwrap();
child.wait_with_output().unwrap()
}
fn ok(output: Output) -> String {
assert!(
output.status.success(),
"ursa failed: {}",
String::from_utf8_lossy(&output.stderr)
);
String::from_utf8(output.stdout).unwrap()
}
fn err(output: Output) -> String {
assert!(
!output.status.success(),
"ursa should have failed; stdout: {}",
String::from_utf8_lossy(&output.stdout)
);
String::from_utf8(output.stderr).unwrap()
}
#[test]
fn query_a_file_matches_getting_started() {
let out = ok(ursa(&[
"-c",
"SELECT * FROM 'python/ursa/datasets/data/karate_edges.csv' LIMIT 3",
]));
assert_eq!(
out,
"\
┌───────┬───────┐
│ src │ dst │
│ Int64 │ Int64 │
├───────┼───────┤
│ 0 │ 1 │
│ 0 │ 2 │
│ 0 │ 3 │
└───────┴───────┘
"
);
}
#[test]
fn output_formats() {
let sql = format!("SELECT * FROM '{}' LIMIT 2", karate_edges());
assert_eq!(
ok(ursa(&["--format", "csv", "-c", &sql])),
"src,dst\n0,1\n0,2\n"
);
assert_eq!(
ok(ursa(&["--format", "tsv", "-c", &sql])),
"src\tdst\n0\t1\n0\t2\n"
);
assert_eq!(
ok(ursa(&["--format", "json", "-c", &sql])),
"[{\"src\":0,\"dst\":1},{\"src\":0,\"dst\":2}]\n"
);
assert_eq!(
ok(ursa(&["--format", "ndjson", "-c", &sql])),
"{\"src\":0,\"dst\":1}\n{\"src\":0,\"dst\":2}\n"
);
let arrow = ursa(&["--format", "arrow", "-c", &sql]);
assert!(arrow.status.success());
assert_eq!(&arrow.stdout[..4], &[0xff, 0xff, 0xff, 0xff]);
}
#[test]
fn empty_results_keep_their_header() {
assert_eq!(
ok(ursa(&["-c", "SELECT 1 AS x WHERE false"])),
"┌───────┐\n│ x │\n│ Int64 │\n└───────┘\n 0 rows\n"
);
assert_eq!(
ok(ursa(&[
"--format",
"csv",
"-c",
"SELECT 1 AS x WHERE false"
])),
"x\n"
);
assert_eq!(
ok(ursa(&[
"--format",
"json",
"-c",
"SELECT 1 AS x WHERE false"
])),
"[]\n"
);
}
#[test]
fn several_statements_print_each_result() {
let sql = format!(
"CREATE VIEW ties AS SELECT * FROM '{}';
SELECT count(*) AS edges FROM ties;
SELECT max(dst) AS top FROM ties;",
karate_edges()
);
assert_eq!(
ok(ursa(&["--format", "csv", "-c", &sql])),
"edges\n78\ntop\n33\n"
);
}
#[test]
fn show_ursa_settings() {
assert_eq!(
ok(ursa(&[
"--format",
"csv",
"-c",
"SHOW ursa.index_storage_limit; SET ursa.index_storage_limit = '1GiB'; \
SHOW ursa.index_storage_limit",
])),
format!(
"name,value\nursa.index_storage_limit,\"{AUTO_LIMIT}\"\nname,value\n\
ursa.index_storage_limit,1GiB (SET for this session)\n"
)
);
let all = ok(ursa(&["--format", "csv", "-c", "SHOW ALL"]));
assert!(
all.contains(&format!("\nursa.index_storage_limit,\"{AUTO_LIMIT}\"\n")),
"{all}"
);
}
const AUTO_LIMIT: &str = "auto (the default: in a database, 25% of the free space on its \
disk; at least 4GiB, at most 64GiB)";
#[test]
fn commands_and_files_run_in_order() {
let dir = tempfile::tempdir().unwrap();
let file = dir.path().join("q.sql");
std::fs::write(&file, "SELECT 2 AS b;\n").unwrap();
let file = file.display().to_string();
let out = ok(ursa(&[
"--format",
"csv",
"-c",
"SELECT 1 AS a",
"--file",
&file,
"-c",
"SELECT 3 AS c",
]));
assert_eq!(out, "a\n1\nb\n2\nc\n3\n");
}
#[test]
fn short_f_runs_a_file() {
let dir = tempfile::tempdir().unwrap();
let file = dir.path().join("q.sql");
std::fs::write(&file, "SELECT 2 AS b;\n").unwrap();
let file = file.display().to_string();
assert_eq!(ok(ursa(&["--format", "csv", "-f", &file])), "b\n2\n");
}
#[test]
fn short_f_with_a_format_name_points_to_format() {
let message = err(ursa(&["-f", "json", "-c", "SELECT 1"]));
assert!(
message.contains("cannot read 'json'")
&& message.contains("to choose the output format, use --format json"),
"{message}"
);
}
#[cfg(unix)]
#[test]
fn a_closed_pipe_ends_quietly_in_every_format() {
use std::io::{BufRead, BufReader};
let sql = "SELECT value, value * 2 AS twice FROM generate_series(1, 300000)";
for format in ["csv", "tsv", "table", "json", "ndjson"] {
let mut child = Command::new(env!("CARGO_BIN_EXE_ursa"))
.args(["--format", format, "-c", sql])
.current_dir(workspace())
.stdout(Stdio::piped())
.stderr(Stdio::piped())
.spawn()
.expect("ursa runs");
let mut first = String::new();
BufReader::new(child.stdout.take().unwrap())
.read_line(&mut first)
.unwrap();
let out = child.wait_with_output().unwrap();
assert!(!first.is_empty(), "{format}: no first line");
assert!(
out.status.success(),
"{format}: exit {:?}, stderr: {}",
out.status.code(),
String::from_utf8_lossy(&out.stderr)
);
assert_eq!(String::from_utf8_lossy(&out.stderr), "", "{format}");
}
}
#[test]
fn sql_from_stdin() {
let sql = format!("SELECT count(*) AS n FROM '{}';", karate_edges());
let out = ursa_with_stdin(&["--format", "csv"], &sql);
assert_eq!(ok(out), "n\n78\n");
}
#[test]
fn copy_to_parquet_and_read_it_back() {
let dir = tempfile::tempdir().unwrap();
let target = dir.path().join("edges.parquet").display().to_string();
let copy = format!(
"COPY (SELECT * FROM '{}' WHERE src = 0) TO '{target}'",
karate_edges()
);
assert_eq!(ok(ursa(&["--format", "csv", "-c", ©])), "count\n16\n");
let read = format!("SELECT count(*) AS n, max(dst) AS top FROM '{target}'");
assert_eq!(
ok(ursa(&["--format", "csv", "-c", &read])),
"n,top\n16,31\n"
);
}
#[test]
fn gzip_csv() {
use flate2::write::GzEncoder;
use flate2::Compression;
let dir = tempfile::tempdir().unwrap();
let path = dir.path().join("edges.csv.gz");
let mut gz = GzEncoder::new(
std::fs::File::create(&path).unwrap(),
Compression::default(),
);
gz.write_all(b"src,dst\n0,1\n1,2\n2,0\n").unwrap();
gz.finish().unwrap();
let sql = format!(
"SELECT sum(dst) AS s, count(*) AS n FROM '{}'",
path.display()
);
assert_eq!(ok(ursa(&["--format", "csv", "-c", &sql])), "s,n\n3,3\n");
}
#[test]
fn json_lines_by_extension() {
use flate2::write::GzEncoder;
use flate2::Compression;
let dir = tempfile::tempdir().unwrap();
let lines = "{\"src\": 0, \"dst\": 1}\n{\"src\": 1, \"dst\": 2}\n";
std::fs::create_dir(dir.path().join("parts")).unwrap();
for name in [
"edges.json",
"edges.jsonl",
"edges.ndjson",
"parts/a.jsonl",
"parts/b.jsonl",
] {
std::fs::write(dir.path().join(name), lines).unwrap();
}
let mut gz = GzEncoder::new(
std::fs::File::create(dir.path().join("edges.jsonl.gz")).unwrap(),
Compression::default(),
);
gz.write_all(lines.as_bytes()).unwrap();
gz.finish().unwrap();
let root = dir.path().display();
for location in [
"edges.json",
"edges.jsonl",
"edges.ndjson",
"edges.jsonl.gz",
"parts/*.jsonl",
"parts/",
] {
let sql = format!("SELECT count(*) AS n, max(dst) AS top FROM '{root}/{location}'");
let n = if location.starts_with("parts") { 4 } else { 2 };
assert_eq!(
ok(ursa(&["--format", "csv", "-c", &sql])),
format!("n,top\n{n},2\n"),
"{location}"
);
}
let copy = format!("COPY (SELECT 7 AS x) TO '{root}/out.jsonl'");
assert_eq!(ok(ursa(&["--format", "csv", "-c", ©])), "count\n1\n");
let read = format!("SELECT x FROM '{root}/out.jsonl'");
assert_eq!(ok(ursa(&["--format", "csv", "-c", &read])), "x\n7\n");
}
#[test]
fn read_functions() {
let dir = tempfile::tempdir().unwrap();
std::fs::write(dir.path().join("edges.txt"), "0|1\n1|2\n2|0\n").unwrap();
std::fs::write(dir.path().join("a.csv"), "src,dst\n0,1\n").unwrap();
std::fs::write(dir.path().join("b.csv"), "src,dst\n1,2\n2,3\n").unwrap();
std::fs::write(
dir.path().join("array.json"),
"[{\"src\": 0, \"dst\": 5}, {\"src\": 1, \"dst\": 6}]",
)
.unwrap();
let root = dir.path().display();
let csv = |sql: String| ok(ursa(&["--format", "csv", "-c", &sql]));
assert_eq!(
csv(format!(
"SELECT sum(column_2) AS s FROM read_csv('{root}/edges.txt', header => false, \
delim => '|')"
)),
"s\n3\n"
);
assert_eq!(
csv(format!(
"SELECT count(*) AS n FROM read_csv('{root}/edges.txt', header = false, sep = '|')"
)),
"n\n3\n"
);
assert_eq!(
csv(format!(
"SELECT count(*) AS n FROM read_csv(['{root}/a.csv', '{root}/b.csv'])"
)),
"n\n3\n"
);
assert_eq!(
csv(format!(
"SELECT count(*) AS n FROM read_csv('{root}/*.csv')"
)),
"n\n3\n"
);
assert_eq!(
csv(format!(
"SELECT sum(dst) AS s FROM read_json('{root}/array.json', format => 'array')"
)),
"s\n11\n"
);
assert_eq!(
csv(format!(
"CREATE VIEW e AS SELECT * FROM read_csv('{root}/a.csv'); \
WITH b AS (SELECT * FROM read_csv('{root}/b.csv')) \
SELECT (SELECT count(*) FROM e) + (SELECT count(*) FROM b) AS n"
)),
"n\n3\n"
);
assert_eq!(
err(ursa(&[
"-c",
&format!("SELECT * FROM read_cvs('{root}/a.csv')")
])),
"Error: there is no table function 'read_cvs'; did you mean 'read_csv'?\n"
);
assert_eq!(
err(ursa(&[
"-c",
&format!("SELECT * FROM read_csv('{root}/a.csv', qoute => '\"')")
])),
"Error: read_csv() has no option 'qoute'; did you mean 'quote'? (it takes header, \
delim, sep, delimiter, quote, compression)\n"
);
}
#[test]
fn local_glob_and_directory() {
let dir = tempfile::tempdir().unwrap();
std::fs::write(dir.path().join("a.csv"), "src,dst\n0,1\n").unwrap();
std::fs::write(dir.path().join("b.csv"), "src,dst\n1,2\n2,3\n").unwrap();
let root = dir.path().display();
let glob = format!("SELECT count(*) AS n FROM '{root}/*.csv'");
assert_eq!(ok(ursa(&["--format", "csv", "-c", &glob])), "n\n3\n");
let directory = format!("SELECT count(*) AS n FROM '{root}/'");
assert_eq!(ok(ursa(&["--format", "csv", "-c", &directory])), "n\n3\n");
}
#[test]
fn errors_are_for_people() {
let e = err(ursa(&["-c", "SELECT * FROM 'no_such_file.csv'"]));
assert_eq!(e, "Error: no file found at 'no_such_file.csv'\n");
let e = err(ursa(&["-c", "SELEC 1"]));
assert!(e.starts_with("Error: syntax error: "), "{e}");
assert!(!e.contains("ParserError"), "{e}");
let e = err(ursa(&["-c", "SELECT 'a'::int"]));
assert!(!e.contains("Optimizer rule"), "{e}");
let out = ursa(&["-c", "SELECT 1 AS a; SELEC 2"]);
assert!(!out.status.success());
assert!(out.stdout.is_empty());
}
#[test]
fn define_a_graph_matches_getting_started() {
let data = "python/ursa/datasets/data";
let sql = format!(
"CREATE VIEW members AS SELECT * FROM '{data}/karate_nodes.csv';
CREATE VIEW ties AS SELECT * FROM '{data}/karate_edges.csv';
CREATE PROPERTY GRAPH karate
VERTEX TABLES (members KEY (id))
EDGE TABLES (
ties SOURCE KEY (src) REFERENCES members (id)
DESTINATION KEY (dst) REFERENCES members (id)
);
CREATE PROPERTY GRAPH karate_edges_only
EDGE TABLES (ties SOURCE KEY (src) DESTINATION KEY (dst));
SELECT * FROM karate.nodes ORDER BY id LIMIT 3;"
);
assert_eq!(
ok(ursa(&["-c", &sql])),
"\
┌───────┬────────┐
│ id │ club │
│ Int64 │ Utf8 │
├───────┼────────┤
│ 0 │ Mr. Hi │
│ 1 │ Mr. Hi │
│ 2 │ Mr. Hi │
└───────┴────────┘
"
);
}
#[test]
fn graphs_last_for_the_invocation() {
let define = format!(
"CREATE VIEW ties AS SELECT * FROM '{}';
CREATE PROPERTY GRAPH g EDGE TABLES (ties SOURCE KEY (src) DESTINATION KEY (dst))",
karate_edges()
);
let out = ok(ursa(&[
"--format",
"csv",
"-c",
&define,
"-c",
"SELECT count(*) AS nodes FROM g.nodes",
"-c",
"SELECT count(*) AS edges FROM g.edges; DROP PROPERTY GRAPH g",
]));
assert_eq!(out, "nodes\n34\nedges\n78\n");
let e = err(ursa(&["-c", "SELECT * FROM g.nodes"]));
assert!(e.contains("g.nodes"), "{e}");
let e = err(ursa(&[
"-c",
"CREATE PROPERTY GRAPH g EDGE TABLES (ties SOURCE KEY (src, x) DESTINATION KEY (dst))",
]));
assert!(
e.starts_with("Error: multi-column keys are not supported yet at Line: ")
&& e.ends_with("; KEY, SOURCE KEY, DESTINATION KEY and REFERENCES take one column\n"),
"{e}"
);
}
#[test]
fn only_ursa_files_are_databases() {
let e = err(ursa(&["query.sql", "-c", "SELECT 1"]));
assert!(e.contains("'query.sql' is not a database file"), "{e}");
let e = err(ursa(&["--readonly", "-c", "SELECT 1"]));
assert!(e.contains("--readonly"), "{e}");
}
#[test]
fn no_sql_on_a_pipe_does_nothing() {
let out = ursa_with_stdin(&[], "");
assert!(out.status.success());
assert_eq!(String::from_utf8(out.stdout).unwrap(), "");
assert_eq!(String::from_utf8(out.stderr).unwrap(), "");
}
#[test]
fn init_runs_before_the_sql() {
let dir = tempfile::tempdir().unwrap();
let init = dir.path().join("setup.sql");
std::fs::write(
&init,
".mode csv\nCREATE VIEW v AS SELECT 42 AS answer;\nSELECT nope;\n",
)
.unwrap();
let init = init.display().to_string();
for flag in ["-init", "--init"] {
let out = ursa(&[flag, &init, "-c", "SELECT * FROM v"]);
assert!(out.status.success());
assert_eq!(String::from_utf8(out.stdout).unwrap(), "answer\n42\n");
let e = String::from_utf8(out.stderr).unwrap();
assert!(e.starts_with("Error: ") && e.lines().count() == 1, "{e}");
}
let out = ok(ursa(&[
"-init",
&init,
"--format",
"json",
"-c",
"SELECT * FROM v",
]));
assert_eq!(out, "[{\"answer\":42}]\n");
let out = ok(ursa_with_stdin(&["-init", &init], "SELECT * FROM v;"));
assert_eq!(out, "answer\n42\n");
}
#[test]
fn scripts_do_not_read_ursarc() {
let home = tempfile::tempdir().unwrap();
std::fs::write(home.path().join(".ursarc"), ".mode csv\n").unwrap();
let out = Command::new(env!("CARGO_BIN_EXE_ursa"))
.args(["-c", "SELECT 1 AS x"])
.env("HOME", home.path())
.output()
.unwrap();
assert_eq!(
ok(out),
"┌───────┐\n│ x │\n│ Int64 │\n├───────┤\n│ 1 │\n└───────┘\n",
"~/.ursarc changed a script's output"
);
let e = err(ursa(&["-init", "x.sql", "-no-init", "-c", "SELECT 1"]));
assert!(e.contains("cannot be used with"), "{e}");
}
fn define_karate() -> String {
let data = "python/ursa/datasets/data";
format!(
"CREATE VIEW members AS SELECT * FROM '{data}/karate_nodes.csv';
CREATE VIEW ties AS SELECT * FROM '{data}/karate_edges.csv';
CREATE PROPERTY GRAPH karate
VERTEX TABLES (members KEY (id))
EDGE TABLES (
ties SOURCE KEY (src) REFERENCES members (id)
DESTINATION KEY (dst) REFERENCES members (id)
);"
)
}
#[test]
fn run_an_algorithm_matches_getting_started() {
let sql = format!(
"{}
SELECT id, round(pagerank, 4) AS pagerank
FROM pagerank(karate, direction => 'both')
ORDER BY pagerank DESC
LIMIT 5;",
define_karate()
);
assert_eq!(
ok(ursa(&["-c", &sql])),
"\
┌───────┬──────────┐
│ id │ pagerank │
│ Int64 │ Float64 │
├───────┼──────────┤
│ 33 │ 0.1009 │
│ 0 │ 0.097 │
│ 32 │ 0.0717 │
│ 2 │ 0.0571 │
│ 1 │ 0.0529 │
└───────┴──────────┘
"
);
}
#[test]
fn combine_algorithms_and_attributes() {
let sql = format!(
"{}
SELECT n.id, club, degree, triangle_count AS triangles, louvain AS community
FROM karate.nodes AS n
JOIN degree(karate, direction => 'both') AS d ON d.id = n.id
JOIN triangle_count(karate) AS t ON t.id = n.id
JOIN louvain(karate, seed => 1) AS l ON l.id = n.id
ORDER BY degree DESC
LIMIT 5;",
define_karate()
);
assert_eq!(
ok(ursa(&["-c", &sql])),
"\
┌───────┬─────────┬────────┬───────────┬───────────┐
│ id │ club │ degree │ triangles │ community │
│ Int64 │ Utf8 │ UInt32 │ UInt32 │ UInt32 │
├───────┼─────────┼────────┼───────────┼───────────┤
│ 33 │ Officer │ 17 │ 15 │ 2 │
│ 0 │ Mr. Hi │ 16 │ 18 │ 0 │
│ 32 │ Officer │ 12 │ 13 │ 2 │
│ 2 │ Mr. Hi │ 10 │ 11 │ 0 │
│ 1 │ Mr. Hi │ 9 │ 12 │ 0 │
└───────┴─────────┴────────┴───────────┴───────────┘
"
);
}
#[test]
fn an_algorithm_joins_using_id() {
let sql = format!(
"{}
SELECT id, club, degree FROM karate.nodes
JOIN degree(karate, direction => 'both') USING (id)
ORDER BY degree DESC, id LIMIT 2;",
define_karate()
);
assert_eq!(
ok(ursa(&["--format", "csv", "-c", &sql])),
"id,club,degree\n33,Officer,17\n0,Mr. Hi,16\n"
);
}
#[test]
fn graph_function_errors_are_for_people() {
let sql = format!("{} SELECT * FROM pagerank(karte)", define_karate());
assert_eq!(
err(ursa(&["-c", &sql])),
"Error: graph 'karte' does not exist; did you mean 'karate'?\n"
);
}
#[test]
fn find_out_what_is_available_matches_getting_started() {
let sql = format!(
"{}
CREATE PROPERTY GRAPH karate_edges_only
EDGE TABLES (ties SOURCE KEY (src) DESTINATION KEY (dst));",
define_karate()
);
let out = ok(ursa(&[
"-c",
&sql,
"-c",
"SHOW GRAPHS; DESCRIBE GRAPH karate;",
]));
assert_eq!(
out,
"\
┌───────────────────┬────────────┬────────┬─────────────┬──────────────┬────────────┐
│ name │ edge_table │ source │ destination │ vertex_table │ vertex_key │
│ Utf8 │ Utf8 │ Utf8 │ Utf8 │ Utf8 │ Utf8 │
├───────────────────┼────────────┼────────┼─────────────┼──────────────┼────────────┤
│ karate │ ties │ src │ dst │ members │ id │
│ karate_edges_only │ ties │ src │ dst │ NULL │ NULL │
└───────────────────┴────────────┴────────┴─────────────┴──────────────┴────────────┘
┌────────────┬─────────────┬───────────┬─────────────┬──────────────┐
│ table_name │ column_name │ data_type │ role │ origin │
│ Utf8 │ Utf8 │ Utf8 │ Utf8 │ Utf8 │
├────────────┼─────────────┼───────────┼─────────────┼──────────────┤
│ nodes │ id │ Int64 │ node id │ members.id │
│ nodes │ club │ Utf8 │ NULL │ members.club │
│ edges │ src │ Int64 │ source │ ties.src │
│ edges │ dst │ Int64 │ destination │ ties.dst │
└────────────┴─────────────┴───────────┴─────────────┴──────────────┘
"
);
let out = ok(ursa(&[
"--format",
"json",
"-c",
&sql,
"-c",
"SHOW GRAPHS; DESC GRAPH karate_edges_only",
]));
assert_eq!(
out,
"\
[{\"name\":\"karate\",\"edge_table\":\"ties\",\"source\":\"src\",\"destination\":\"dst\",\"vertex_table\":\"members\",\"vertex_key\":\"id\"},\
{\"name\":\"karate_edges_only\",\"edge_table\":\"ties\",\"source\":\"src\",\"destination\":\"dst\",\"vertex_table\":null,\"vertex_key\":null}]
[{\"table_name\":\"nodes\",\"column_name\":\"id\",\"data_type\":\"Int64\",\"role\":\"node id\",\"origin\":\"ties.src, ties.dst\"},\
{\"table_name\":\"edges\",\"column_name\":\"src\",\"data_type\":\"Int64\",\"role\":\"source\",\"origin\":\"ties.src\"},\
{\"table_name\":\"edges\",\"column_name\":\"dst\",\"data_type\":\"Int64\",\"role\":\"destination\",\"origin\":\"ties.dst\"}]
"
);
assert_eq!(
err(ursa(&["-c", &sql, "-c", "DESCRIBE GRAPH karat"])),
"Error: graph 'karat' does not exist; did you mean 'karate'?\n"
);
}
#[test]
fn a_graph_over_file_paths() {
let data = "python/ursa/datasets/data";
let sql = format!(
"CREATE PROPERTY GRAPH florentine
EDGE TABLES ('{data}/florentine_edges.csv' SOURCE KEY (src) DESTINATION KEY (dst));
SHOW GRAPHS;
SELECT id, degree FROM degree(florentine, direction => 'both')
ORDER BY degree DESC, id LIMIT 3;
SELECT count(*) AS families FROM florentine.nodes;"
);
assert_eq!(
ok(ursa(&["-c", &sql])),
"\
┌────────────┬──────────────────────────────────────────────────┬────────┬─────────────┬──────────────┬────────────┐
│ name │ edge_table │ source │ destination │ vertex_table │ vertex_key │
│ Utf8 │ Utf8 │ Utf8 │ Utf8 │ Utf8 │ Utf8 │
├────────────┼──────────────────────────────────────────────────┼────────┼─────────────┼──────────────┼────────────┤
│ florentine │ 'python/ursa/datasets/data/florentine_edges.csv' │ src │ dst │ NULL │ NULL │
└────────────┴──────────────────────────────────────────────────┴────────┴─────────────┴──────────────┴────────────┘
┌──────────┬────────┐
│ id │ degree │
│ Utf8 │ UInt32 │
├──────────┼────────┤
│ Medici │ 6 │
│ Guadagni │ 4 │
│ Strozzi │ 4 │
└──────────┴────────┘
┌──────────┐
│ families │
│ Int64 │
├──────────┤
│ 15 │
└──────────┘
"
);
}
#[test]
fn dropping_a_table_a_graph_uses_is_refused() {
let sql = format!("{} DROP VIEW ties", define_karate());
assert_eq!(
err(ursa(&["-c", &sql])),
"Error: cannot drop view 'ties': graph 'karate' uses it; drop property graph karate \
first\n"
);
let sql = format!(
"{} CREATE OR REPLACE VIEW members AS SELECT id AS member, club FROM members",
define_karate()
);
let e = err(ursa(&["-c", &sql]));
assert!(
e.starts_with("Error: cannot replace view 'members': graph 'karate' uses it"),
"{e}"
);
let sql = format!(
"{} DROP PROPERTY GRAPH karate; DROP VIEW ties; DROP VIEW members;
SELECT count(*) AS n FROM information_schema.tables WHERE table_schema = 'public'",
define_karate()
);
assert_eq!(ok(ursa(&["--format", "csv", "-c", &sql])), "n\n0\n");
}
#[test]
fn combine_algorithms_and_attributes_with_algorithms() {
let sql = format!(
"{}
SELECT id, club, degree, triangles, community
FROM karate.nodes
JOIN algorithms(karate,
degree(direction => 'both'),
triangles => triangle_count(),
community => louvain(seed => 1)) USING (id)
ORDER BY degree DESC
LIMIT 5;",
define_karate()
);
assert_eq!(
ok(ursa(&["-c", &sql])),
"\
┌───────┬─────────┬────────┬───────────┬───────────┐
│ id │ club │ degree │ triangles │ community │
│ Int64 │ Utf8 │ UInt32 │ UInt32 │ UInt32 │
├───────┼─────────┼────────┼───────────┼───────────┤
│ 33 │ Officer │ 17 │ 15 │ 2 │
│ 0 │ Mr. Hi │ 16 │ 18 │ 0 │
│ 32 │ Officer │ 12 │ 13 │ 2 │
│ 2 │ Mr. Hi │ 10 │ 11 │ 0 │
│ 1 │ Mr. Hi │ 9 │ 12 │ 0 │
└───────┴─────────┴────────┴───────────┴───────────┘
"
);
}
const LESMIS: &str =
"CREATE VIEW lesmis_edges AS SELECT * FROM 'python/ursa/datasets/data/lesmis_edges.csv';
CREATE PROPERTY GRAPH lesmis
EDGE TABLES (lesmis_edges SOURCE KEY (src) DESTINATION KEY (dst));";
#[test]
fn weights_match_getting_started() {
let sql = format!(
"{LESMIS}
SELECT * FROM pagerank(lesmis, weight => weight, direction => 'both')
ORDER BY pagerank DESC
LIMIT 5;"
);
assert_eq!(
ok(ursa(&["-c", &sql])),
"\
┌──────────┬──────────────────────┐
│ id │ pagerank │
│ Utf8 │ Float64 │
├──────────┼──────────────────────┤
│ Valjean │ 0.09956068175644767 │
│ Marius │ 0.051667656904091475 │
│ Myriel │ 0.03923416880292588 │
│ Cosette │ 0.03691018363299517 │
│ Enjolras │ 0.036614491459848165 │
└──────────┴──────────────────────┘
"
);
let sql =
format!("{LESMIS} SELECT * FROM louvain(lesmis, weight => log(1 + weight), seed => 7);");
let out = ok(ursa(&["--format", "csv", "-c", &sql]));
assert_eq!(out.lines().count(), 78, "{out}");
let communities: std::collections::BTreeSet<&str> = out
.lines()
.skip(1)
.map(|l| l.rsplit(',').next().unwrap())
.collect();
assert_eq!(communities.len(), 6, "{out}");
let sql = format!(
"{LESMIS}
SELECT * FROM louvain(lesmis, weight => log(1 + weight), seed => 7)
ORDER BY id
LIMIT 5;"
);
assert_eq!(
ok(ursa(&["-c", &sql])),
"\
┌────────────┬─────────┐
│ id │ louvain │
│ Utf8 │ UInt32 │
├────────────┼─────────┤
│ Anzelma │ 3 │
│ Babet │ 3 │
│ Bahorel │ 5 │
│ Bamatabois │ 1 │
│ BaronessT │ 4 │
└────────────┴─────────┘
"
);
assert_eq!(
err(ursa(&[
"-c",
&format!("{LESMIS} SELECT * FROM pagerank(lesmis, weight => weight - 2)")
])),
"Error: weight expression produced a negative value (-1); weights must be non-negative\n"
);
}
#[test]
fn subgraphs_match_getting_started() {
let sql = format!(
"{LESMIS}
CREATE VIEW strong_ties AS SELECT * FROM lesmis_edges WHERE weight >= 3;
CREATE PROPERTY GRAPH lesmis_strong
EDGE TABLES (strong_ties SOURCE KEY (src) DESTINATION KEY (dst));
SELECT count(DISTINCT connected_components) AS groups
FROM connected_components(lesmis_strong);"
);
assert_eq!(
ok(ursa(&["-c", &sql])),
"\
┌────────┐
│ groups │
│ Int64 │
├────────┤
│ 3 │
└────────┘
"
);
}
#[test]
fn traversals_match_getting_started() {
let sql = format!(
"{}
SELECT * FROM hop(karate, seeds => [0], n => 1, direction => 'both');",
define_karate()
);
let out = ok(ursa(&["--format", "csv", "-c", &sql]));
assert_eq!(out.lines().count(), 1 + 16, "{out}");
assert_eq!(out.lines().next(), Some("seed,id,distance"));
assert!(
out.lines()
.skip(1)
.all(|l| l.starts_with("0,") && l.ends_with(",1")),
"{out}"
);
let sql = format!(
"{}
SELECT * FROM hop(karate,
seeds => (SELECT id FROM karate.nodes WHERE club = 'Officer'),
n => 2);",
define_karate()
);
assert_eq!(
ok(ursa(&["-c", &sql])),
"\
┌───────┬───────┬──────────┐
│ seed │ id │ distance │
│ Int64 │ Int64 │ Int64 │
├───────┼───────┼──────────┤
│ 9 │ 33 │ 1 │
│ 14 │ 32 │ 1 │
│ 14 │ 33 │ 1 │
│ 15 │ 32 │ 1 │
│ 15 │ 33 │ 1 │
│ 18 │ 32 │ 1 │
│ 18 │ 33 │ 1 │
│ 20 │ 32 │ 1 │
│ 20 │ 33 │ 1 │
│ 22 │ 32 │ 1 │
│ 22 │ 33 │ 1 │
│ 23 │ 25 │ 1 │
│ 23 │ 27 │ 1 │
│ 23 │ 29 │ 1 │
│ 23 │ 32 │ 1 │
│ 23 │ 33 │ 1 │
│ 23 │ 31 │ 2 │
│ 24 │ 25 │ 1 │
│ 24 │ 27 │ 1 │
│ 24 │ 31 │ 1 │
│ 24 │ 33 │ 2 │
│ 24 │ 32 │ 2 │
│ 25 │ 31 │ 1 │
│ 25 │ 32 │ 2 │
│ 25 │ 33 │ 2 │
│ 26 │ 29 │ 1 │
│ 26 │ 33 │ 1 │
│ 26 │ 32 │ 2 │
│ 27 │ 33 │ 1 │
│ 28 │ 31 │ 1 │
│ 28 │ 33 │ 1 │
│ 28 │ 32 │ 2 │
│ 29 │ 32 │ 1 │
│ 29 │ 33 │ 1 │
│ 30 │ 32 │ 1 │
│ 30 │ 33 │ 1 │
│ 31 │ 32 │ 1 │
│ 31 │ 33 │ 1 │
│ 32 │ 33 │ 1 │
└───────┴───────┴──────────┘
"
);
let sql = format!(
"{}
SELECT * FROM shortest_path(karate, source => 0, target => 33, direction => 'both');",
define_karate()
);
assert_eq!(
ok(ursa(&["-c", &sql])),
"\
┌───────┬───────┬───────┬─────────┐
│ src │ dst │ hop │ cost │
│ Int64 │ Int64 │ Int64 │ Float64 │
├───────┼───────┼───────┼─────────┤
│ 0 │ 8 │ 0 │ 1.0 │
│ 8 │ 33 │ 1 │ 2.0 │
└───────┴───────┴───────┴─────────┘
"
);
assert_eq!(
err(ursa(&[
"-c",
&format!(
"{} SELECT * FROM algorithms(karate, hop(seeds => [0]))",
define_karate()
)
])),
"Error: algorithms(): hop() is a traversal, which returns rows of its own, not one per \
node; call it on its own: SELECT * FROM hop(<graph>, seeds => [<id>, ...], n => 1, \
direction => 'out')\n"
);
}
#[test]
fn ursa_functions_lists_the_catalog() {
let out = ok(ursa(&[
"--format",
"json",
"-c",
"SELECT * FROM ursa_functions() WHERE name = 'pagerank'",
]));
assert_eq!(
out,
"[{\"name\":\"pagerank\",\"family\":\"algorithm\",\
\"summary\":\"PageRank (pull-based fixpoint); a dangling node spreads its score to all nodes. Scores sum to 1 over every node and isolated nodes count too: with a vertex table more keys give lower scores.\",\
\"networkx\":\"nx.pagerank(G, alpha=damping) (both: nx.pagerank(U))\",\
\"parameters\":[\
{\"name\":\"damping\",\"type\":\"number\",\"default\":\"0.85\",\"choices\":[],\
\"doc\":\"Probability of following an edge.\"},\
{\"name\":\"max_iter\",\"type\":\"integer\",\"default\":\"30\",\"choices\":[],\
\"doc\":\"Iteration cap.\"},\
{\"name\":\"tol\",\"type\":\"number\",\"default\":\"1e-6\",\"choices\":[],\
\"doc\":\"Convergence tolerance on the L1 change.\"},\
{\"name\":\"weight\",\"type\":\"expression\",\"default\":\"NULL\",\"choices\":[],\
\"doc\":\"A non-negative weight per edge; null is unweighted.\"},\
{\"name\":\"direction\",\"type\":\"string\",\"default\":\"'out'\",\"choices\":[\"out\",\"both\"],\
\"doc\":\"Follow the rows as stored, or read the graph as undirected.\"}],\
\"directions\":[\"out\",\"both\"],\
\"outputs\":[{\"name\":\"pagerank\",\"type\":\"Float64\",\"nullable\":false}]}]\n"
);
let out = ok(ursa(&[
"--format",
"csv",
"-c",
"SELECT name, family, cardinality(parameters) AS parameters, \
array_to_string(directions, ' ') AS directions FROM ursa_functions()",
]));
assert_eq!(
out,
"\
name,family,parameters,directions
degree,algorithm,1,out in both
pagerank,algorithm,5,out both
connected_components,algorithm,1,\"\"
triangle_count,algorithm,0,\"\"
clustering_coefficient,algorithm,0,\"\"
betweenness,algorithm,4,out both
closeness,algorithm,2,out in both
label_propagation,algorithm,2,\"\"
louvain,algorithm,3,\"\"
neighbors_agg,aggregation,4,out in both
density,statistic,1,out in both
avg_path_length,statistic,2,out in both
diameter,statistic,2,out in both
describe,statistic,3,out in both
hop,traversal,3,out in both
shortest_path,traversal,4,out in both
random_walk,traversal,4,\"\"
algorithms,combinator,0,\"\"
"
);
assert_eq!(
err(ursa(&["-c", "SELECT * FROM ursa_functions('pagerank')"])),
"Error: ursa_functions() takes no arguments: SELECT * FROM ursa_functions()\n"
);
}
#[test]
fn scripts_take_dot_commands() {
let dir = tempfile::tempdir().unwrap();
let inner = dir.path().join("inner.sql");
std::fs::write(&inner, "SELECT 2 AS b;\n").unwrap();
let script = dir.path().join("q.sql");
std::fs::write(
&script,
format!(
".timer on\n.mode csv\nSELECT 1 AS a;\n.read {}\n",
inner.display()
),
)
.unwrap();
let out = ok(ursa(&["-f", &script.display().to_string()]));
let lines: Vec<&str> = out.lines().collect();
assert_eq!(lines.len(), 6, "{out}");
assert_eq!(&lines[..2], ["a", "1"]);
assert!(lines[2].starts_with("Run Time (s): real "), "{out}");
assert_eq!(&lines[3..5], ["b", "2"]);
assert!(lines[5].starts_with("Run Time (s): real "), "{out}");
let out = ok(ursa_with_stdin(&[], ".mode json\nSELECT 1 AS a;\n"));
assert_eq!(out, "[{\"a\":1}]\n");
let out = ok(ursa_with_stdin(&[], ".help\n"));
assert!(out.contains(".functions ?NAME?"), "{out}");
let out = ok(ursa(&["-c", ".mode csv", "-c", "SELECT 3 AS c"]));
assert_eq!(out, "c\n3\n");
}
#[test]
fn quit_ends_a_script() {
let out = ok(ursa_with_stdin(
&["--format", "csv"],
"SELECT 1 AS a;\n.exit\nSELECT 2 AS b;\n",
));
assert_eq!(out, "a\n1\n");
let out = ok(ursa(&[
"--format",
"csv",
"-c",
".quit",
"-c",
"SELECT 2 AS b",
]));
assert_eq!(out, "");
}
#[test]
fn a_script_stops_at_its_first_error() {
let out = ursa_with_stdin(
&["--format", "csv"],
"SELECT 1 AS a;\nSELECT * FROM nowhere;\nSELECT 2 AS b;\n",
);
assert!(!out.status.success());
assert_eq!(String::from_utf8(out.stdout).unwrap(), "a\n1\n");
let message = String::from_utf8(out.stderr).unwrap();
assert!(message.contains("nowhere"), "{message}");
let out = ursa_with_stdin(
&["--format", "csv"],
".mode json\nSELECT 1 AS a;\n\nSELECT 2\n FROM t WHERE;\n",
);
assert!(!out.status.success());
assert!(out.stdout.is_empty());
let message = String::from_utf8(out.stderr).unwrap();
assert!(
message.starts_with("Error: syntax error: ") && message.contains("at Line: 5, Column: "),
"{message}"
);
let e = err(ursa(&["-c", ".tabels", "-c", "SELECT 1"]));
assert!(e.contains("did you mean '.tables'?"), "{e}");
}
#[test]
fn arrow_scripts_keep_the_stream_clean() {
let out = ursa(&[
"--format",
"arrow",
"-c",
".timer on",
"-c",
"SELECT 1 AS a",
]);
assert!(out.status.success());
assert!(out.stdout.starts_with(&[0xff, 0xff, 0xff, 0xff]));
let stderr = String::from_utf8(out.stderr).unwrap();
assert!(stderr.starts_with("Run Time (s): real "), "{stderr}");
let e = err(ursa(&[
"--format",
"arrow",
"-c",
"SELECT 1 AS a",
"-c",
"SELECT 2 AS b",
]));
assert!(e.contains("--format arrow writes a single result"), "{e}");
}
#[test]
fn every_statement_prints_a_result_or_nothing_by_rule() {
let dir = tempfile::tempdir().unwrap();
let target = dir.path().join("out.csv").display().to_string();
let edges = karate_edges();
let graph = "CREATE VIEW ties AS SELECT * FROM (VALUES (1, 2)) AS t(src, dst); \
CREATE PROPERTY GRAPH g EDGE TABLES (ties SOURCE KEY (src) DESTINATION KEY (dst))";
let cases: Vec<(&str, String, Option<&str>)> = vec![
("", "SELECT 1 AS x WHERE false".into(), Some("x")),
("", "FROM (SELECT 1 AS x) WHERE x > 1".into(), Some("x")),
(
"",
format!("FROM '{edges}' WHERE src > 1000"),
Some("src,dst"),
),
(
"",
"WITH a AS (SELECT 1 AS x) SELECT * FROM a WHERE x = 2".into(),
Some("x"),
),
("", "VALUES (1) EXCEPT VALUES (1)".into(), Some("column1")),
(
"",
"SHOW GRAPHS".into(),
Some("name,edge_table,source,destination"),
),
(
"",
"SHOW datafusion.execution.batch_size".into(),
Some("name,value"),
),
(
"",
"SHOW ursa.index_storage_limit".into(),
Some("name,value"),
),
(
"",
"DESCRIBE SELECT 1 AS x".into(),
Some("column_name,data_type"),
),
(
"",
format!("COPY (SELECT 1 AS x WHERE false) TO '{target}'"),
Some("count"),
),
("", "EXPLAIN SELECT 1".into(), Some("plan_type,plan")),
(
"",
"SELECT name FROM ursa_functions() WHERE name = 'none'".into(),
Some("name"),
),
(
graph,
"SELECT * FROM GRAPH_TABLE (g MATCH (a)-[e]->(b) WHERE a.id > 100 \
COLUMNS (a.id AS a))"
.into(),
Some("a"),
),
(
graph,
"SELECT * FROM hop(g, seeds => [100])".into(),
Some("seed,id,distance"),
),
(
graph,
"DESCRIBE GRAPH g".into(),
Some("table_name,column_name"),
),
("", "CREATE VIEW v AS SELECT 1 AS x".into(), None),
(
"",
"CREATE TABLE t AS SELECT 1 AS x WHERE false".into(),
None,
),
(
"",
"SET datafusion.execution.batch_size = 1024".into(),
None,
),
("", graph.into(), None),
(graph, "DROP PROPERTY GRAPH g".into(), None),
(graph, "DROP VIEW IF EXISTS nothing".into(), None),
];
for (setup, statement, header) in &cases {
let mut args = vec!["--format", "csv"];
if !setup.is_empty() {
args.extend(["-c", setup]);
}
args.extend(["-c", statement]);
let out = ursa(&args);
assert!(
out.status.success(),
"{statement}: {}",
String::from_utf8_lossy(&out.stderr)
);
let stdout = String::from_utf8(out.stdout).unwrap();
match header {
Some(header) => assert!(
stdout.starts_with(header) && stdout.ends_with('\n'),
"{statement} printed:\n{stdout}"
),
None => assert_eq!(stdout, "", "{statement}"),
}
if header.is_some() {
for format in ["table", "json"] {
let mut args = vec!["--format", format];
if !setup.is_empty() {
args.extend(["-c", setup]);
}
args.extend(["-c", statement]);
let stdout = ok(ursa(&args));
assert!(!stdout.trim().is_empty(), "{statement} ({format})");
}
}
}
for statement in ["SELEC 1", "SELECT * FROM nowhere", "SUMMARIZE ties"] {
let e = err(ursa(&["-c", statement]));
assert!(e.starts_with("Error: "), "{statement}: {e}");
}
}
#[test]
fn duckdb_only_syntax_says_what_to_use() {
let e = err(ursa(&["-c", "SUMMARIZE t"]));
assert!(
e.starts_with("Error: SUMMARIZE is not supported; use DESCRIBE t"),
"{e}"
);
let e = err(ursa(&["-c", "SELECT * FROM t USING SAMPLE 10"]));
assert!(
e.starts_with("Error: USING SAMPLE is not supported; use ORDER BY random() LIMIT n"),
"{e}"
);
}
#[test]
fn skill_prints_the_agent_skill() {
let skill = std::fs::read_to_string(workspace().join("skills/ursa-sql/SKILL.md")).unwrap();
let copy = Path::new(env!("CARGO_MANIFEST_DIR")).join("src/ursa-sql-skill.md");
assert!(
std::fs::read_to_string(©).unwrap() == skill,
"{} is out of date: copy skills/ursa-sql/SKILL.md over it",
copy.display()
);
assert_eq!(ok(ursa(&["--skill"])), skill);
}
#[test]
fn help_points_onward() {
let help = ok(ursa(&["--help"]));
for needle in [
"https://cldixon.github.io/ursa/sql/",
"CREATE PROPERTY GRAPH g EDGE TABLES (ties SOURCE KEY (src) DESTINATION KEY (dst));",
"SELECT * FROM ursa_functions()",
"--format json",
"ursa --skill",
] {
assert!(help.contains(needle), "{needle} missing from:\n{help}");
}
}
#[test]
fn a_long_table_shows_its_first_and_last_rows() {
let sql = "SELECT * FROM generate_series(1, 1234) t(x)";
let out = ok(ursa(&["-c", sql]));
let lines: Vec<&str> = out.lines().collect();
assert_eq!(lines.len(), 4 + 20 + 3 + 20 + 1 + 1, "{out}");
assert_eq!(lines[4], "│ 1 │");
assert_eq!(lines[23], "│ 20 │");
assert_eq!(&lines[24..27], ["│ · │"; 3]);
assert_eq!(lines[27], "│ 1215 │");
assert_eq!(lines[46], "│ 1234 │");
assert_eq!(lines[47], "└───────┘");
assert_eq!(lines[48], " 1,234 rows (40 shown)");
let out = ok(ursa(&["-c", "SELECT * FROM generate_series(1, 40) t(x)"]));
assert_eq!(out.lines().count(), 4 + 40 + 1, "{out}");
assert!(!out.contains("shown"), "{out}");
let out = ok(ursa(&["--max-rows", "3", "-c", sql]));
assert_eq!(
out,
"\
┌───────┐
│ x │
│ Int64 │
├───────┤
│ 1 │
│ 2 │
│ · │
│ · │
│ · │
│ 1234 │
└───────┘
1,234 rows (3 shown)
"
);
for args in [
&["--max-rows", "0", "-c", sql][..],
&["-c", ".maxrows 0", "-c", sql],
] {
let out = ok(ursa(args));
assert_eq!(out.lines().count(), 4 + 1234 + 1, "{args:?}");
}
let out = ok(ursa(&["-c", ".maxrows 2", "-c", ".maxrows", "-c", sql]));
assert!(out.starts_with("Tables show at most 2 rows.\n"), "{out}");
assert!(out.ends_with("1,234 rows (2 shown)\n"), "{out}");
assert_eq!(
err(ursa(&["-c", ".maxrows many"])),
"Error: usage: .maxrows N, a number of rows (0 shows every row); got 'many'\n"
);
for format in ["csv", "tsv", "ndjson"] {
let out = ok(ursa(&["--format", format, "-c", sql]));
let header = usize::from(format != "ndjson");
assert_eq!(out.lines().count(), header + 1234, "{format}");
}
let out = ok(ursa(&["--format", "json", "-c", sql]));
assert_eq!(out.matches("{\"x\":").count(), 1234);
let dir = tempfile::tempdir().unwrap();
let init = dir.path().join("init.sql");
std::fs::write(&init, ".maxrows 4\n").unwrap();
let init = init.display().to_string();
let out = ok(ursa(&["-init", &init, "-c", sql]));
assert!(out.ends_with("1,234 rows (4 shown)\n"), "{out}");
let out = ok(ursa(&["-init", &init, "--max-rows", "6", "-c", sql]));
assert!(out.ends_with("1,234 rows (6 shown)\n"), "{out}");
}
#[cfg(unix)]
#[test]
fn sigint_cancels_a_query() {
use std::os::unix::process::ExitStatusExt;
let dir = tempfile::tempdir().unwrap();
let db = dir.path().join("club.ursa").display().to_string();
for long in [
"SELECT sum(value) AS s FROM generate_series(1, 100000000000)",
"CREATE OR REPLACE VIEW e AS SELECT value % 2000000 AS src, \
(value * 7919 + 13) % 2000000 AS dst FROM generate_series(1, 20000000); \
CREATE OR REPLACE PROPERTY GRAPH g \
EDGE TABLES (e SOURCE KEY (src) DESTINATION KEY (dst)); \
SELECT count(*) AS n FROM louvain(g)",
] {
let child = Command::new(env!("CARGO_BIN_EXE_ursa"))
.args([db.as_str(), "-c", "SELECT 1 AS first", "-c", long])
.current_dir(workspace())
.stdout(Stdio::piped())
.stderr(Stdio::piped())
.spawn()
.unwrap();
std::thread::sleep(std::time::Duration::from_millis(2500));
let killed = Command::new("kill")
.args(["-INT", &child.id().to_string()])
.status()
.unwrap();
assert!(killed.success());
let output = child.wait_with_output().unwrap();
let stderr = String::from_utf8_lossy(&output.stderr);
assert_eq!(output.status.code(), Some(130), "{stderr}");
assert_eq!(output.status.signal(), None);
assert!(stderr.ends_with("Error: interrupted\n"), "{stderr}");
assert!(
stderr
.lines()
.rev()
.skip(1)
.all(|line| line.starts_with("ursa: ")),
"{stderr}"
);
assert_eq!(
String::from_utf8_lossy(&output.stdout),
"┌───────┐\n│ first │\n│ Int64 │\n├───────┤\n│ 1 │\n└───────┘\n"
);
let out = ok(ursa(&[&db, "-c", "SELECT 2 AS again"]));
assert!(out.contains("│ 2 │"), "{out}");
}
}
#[test]
fn the_timer_reports_real_user_and_sys() {
let out = ok(ursa(&["-c", ".timer on", "-c", "SELECT 1 AS a"]));
let last = out.lines().last().unwrap();
assert!(last.starts_with("Run Time (s): real "), "{out}");
if cfg!(unix) {
assert!(last.contains(" user ") && last.contains(" sys "), "{out}");
}
}
#[test]
fn reserved_words_as_names() {
let dir = tempfile::tempdir().unwrap();
let db = dir.path().join("club.ursa").display().to_string();
ok(ursa(&[
&db,
"-c",
"CREATE VIEW follows AS SELECT * FROM (VALUES (1, 2), (2, 3)) t(\"from\", \"to\");
CREATE PROPERTY GRAPH g EDGE TABLES (follows SOURCE KEY (from) DESTINATION KEY (to));",
]));
let out = ok(ursa(&[
&db,
"--format",
"csv",
"-c",
"SELECT * FROM degree(g) ORDER BY id",
]));
assert_eq!(out, "id,degree\n1,1\n2,1\n3,0\n");
assert_eq!(
err(ursa(&["-c", "SELECT from, to FROM t"])),
"Error: syntax error: Expected: identifier, found: , at Line: 1, Column: 12. `from` is a \
reserved word; to use it as a name, quote it: \"from\"\n"
);
}
#[test]
fn a_null_column_shows_null() {
let out = ok(ursa(&["-c", "SELECT NULL AS n, 1 AS i;"]));
assert_eq!(
out,
"\
┌──────┬───────┐
│ n │ i │
│ Null │ Int64 │
├──────┼───────┤
│ NULL │ 1 │
└──────┴───────┘
"
);
}
#[test]
fn max_width_cuts_a_table_to_fit() {
let sql = "SELECT 1 a, 'abcdefghijklmnopqrstuvwxyz' b, 3 c, 4 d, 5 e, 6 f;";
let out = ok(ursa(&["-c", sql]));
assert!(out.contains("abcdefghijklmnopqrstuvwxyz"), "{out}");
let expected = "\
┌───────┬───┬───────┬───────┬───────┐
│ a │ … │ d │ e │ f │
│ Int64 │ … │ Int64 │ Int64 │ Int64 │
├───────┼───┼───────┼───────┼───────┤
│ 1 │ … │ 4 │ 5 │ 6 │
└───────┴───┴───────┴───────┴───────┘
1 row 6 columns (4 shown)
";
assert_eq!(ok(ursa(&["--max-width", "40", "-c", sql])), expected);
assert_eq!(ok(ursa(&["-c", ".maxwidth 40;", "-c", sql])), expected);
let out = ok(ursa(&[
"--max-width",
"70",
"-c",
"SELECT repeat('x', 60) AS s, 1 AS n",
]));
assert!(
out.contains(&format!("│ {}… │ 1 │\n", "x".repeat(57))),
"{out}"
);
assert!(out.lines().all(|l| l.chars().count() <= 70), "{out}");
let dir = tempfile::tempdir().unwrap();
let init = dir.path().join("init.sql");
std::fs::write(&init, ".maxwidth 30\n").unwrap();
let init = init.display().to_string();
let out = ok(ursa(&["-init", &init, "-c", sql]));
assert!(out.lines().all(|l| l.chars().count() <= 30), "{out}");
assert_eq!(
ok(ursa(&["-init", &init, "--max-width", "40", "-c", sql])),
expected
);
}
#[test]
fn arrow_needs_a_result() {
let out = ursa(&["--format", "arrow", "-c", "CREATE VIEW v AS SELECT 1 AS a"]);
assert!(out.stdout.is_empty());
let e = err(out);
assert_eq!(
e,
"Error: --format arrow writes the result of one query, but no statement returned one; \
end the script with a query that returns rows\n"
);
let e = err(ursa(&["-c", ".mode arrow", "-c", "SELECT 1 AS a"]));
assert!(
e.starts_with("Error: .mode cannot switch to the arrow format")
&& e.contains("Give --format arrow on the command line"),
"{e}"
);
let out = ursa(&[
"--format",
"arrow",
"-c",
".mode arrow;",
"-c",
"SELECT 1 AS a",
]);
assert!(out.status.success());
assert!(out.stdout.starts_with(&[0xff, 0xff, 0xff, 0xff]));
}
#[test]
fn dot_commands_take_a_semicolon_and_describe_takes_a_name() {
let out = ok(ursa(&[
"-c",
".mode csv;",
"-c",
"CREATE TABLE \"Mixed\" AS SELECT 1 AS x;",
"-c",
".tables;",
"-c",
".describe Mixed;",
]));
assert!(
out.starts_with("name,type\nMixed,table\ncolumn_name,"),
"{out}"
);
let out = ursa(&[
"-c",
"CREATE VIEW v AS SELECT 1 AS x",
"-c",
".describe v; SELECT 99",
]);
assert!(!String::from_utf8_lossy(&out.stdout).contains("99"));
assert!(err(out).contains("'v; SELECT 99' is not a name"));
}
#[test]
fn output_formats_keep_lists_nulls_and_empty_strings() {
let sql = "SELECT NULL AS a, '' AS b, [1, 2] AS l";
assert_eq!(
ok(ursa(&["--format", "csv", "-c", sql])),
"a,b,l\n,\"\",\"[1, 2]\"\n"
);
assert_eq!(
ok(ursa(&["--format", "tsv", "-c", sql])),
"a\tb\tl\n\t\"\"\t[1, 2]\n"
);
assert_eq!(
ok(ursa(&["--format", "json", "-c", sql])),
"[{\"a\":null,\"b\":\"\",\"l\":[1,2]}]\n"
);
assert_eq!(
ok(ursa(&["--format", "ndjson", "-c", sql])),
"{\"a\":null,\"b\":\"\",\"l\":[1,2]}\n"
);
assert!(ok(ursa(&["-c", "SELECT NULL AS n"])).contains("│ NULL │"));
}
#[test]
fn a_file_that_cannot_be_read_is_named_once() {
let dir = tempfile::tempdir().unwrap();
let dir = std::fs::canonicalize(dir.path()).unwrap();
std::fs::write(dir.join("bad.csv"), "a,b\n1,2\n1,2,3\n").unwrap();
std::fs::write(dir.join("bad.parquet"), "not parquet\n").unwrap();
let bad = dir.join("bad.csv").display().to_string();
let glob = dir.join("b*.csv").display().to_string();
for from in [
format!("'{bad}'"),
format!("'{glob}'"),
format!("read_csv(['{bad}', '{bad}'])"),
] {
let message = err(ursa(&["-c", &format!("SELECT * FROM {from};")]));
assert_eq!(message.matches("cannot read").count(), 1, "{message}");
assert!(
message.starts_with(&format!("Error: cannot read '{bad}': Csv error: ")),
"{message}"
);
}
let parquet = dir.join("bad.parquet").display().to_string();
let message = err(ursa(&["-c", &format!("SELECT * FROM '{parquet}';")]));
assert!(
message.starts_with(&format!("Error: cannot read '{parquet}': Parquet error: ")),
"{message}"
);
}