Menu
Public repository / master
AkurAI-Framework / crates/sqlexec/tests/end_to_end.rs
Latest change fa37e2544d31a1199954a395f9e5b3ebb237b17f - feat(sql): aggregates (COUNT/SUM/AVG/MIN/MAX) + GROUP BY (gpt-5.5) by Ólafur Búi Ólafsson
//! End-to-end executor tests against a real on-disk B+tree.
//!
//! Every test writes to a unique file under `CARGO_TARGET_TMPDIR` (never
//! `/tmp`) so runs are isolated and the temp dir is cleaned by cargo.
use akurai_sqlexec::{Executor, Outcome, ResultSet, Value};
fn db_path(name: &str) -> std::path::PathBuf {
let mut p = std::path::PathBuf::from(env!("CARGO_TARGET_TMPDIR"));
p.push(format!("sqlexec-{}-{}.db", name, std::process::id()));
let _ = std::fs::remove_file(&p);
p
}
fn rows_of(outcome: Outcome) -> ResultSet {
match outcome {
Outcome::Rows(rs) => rs,
other => panic!("expected rows, got {other:?}"),
}
}
#[test]
fn create_insert_select_where_order_limit() {
let path = db_path("full");
let mut db = Executor::open(&path).unwrap();
assert_eq!(
db.execute("CREATE TABLE users (id INTEGER NOT NULL, name TEXT, age INTEGER)")
.unwrap(),
Outcome::Created("users".into())
);
for sql in [
"INSERT INTO users (id, name, age) VALUES (1, 'ada', 36)",
"INSERT INTO users (id, name, age) VALUES (2, 'bob', 17)",
"INSERT INTO users (id, name, age) VALUES (3, 'cora', 52)",
"INSERT INTO users (id, name, age) VALUES (4, 'dan', 28)",
] {
assert_eq!(db.execute(sql).unwrap(), Outcome::Affected(1));
}
// WHERE age >= 18, ordered by age DESC, limited to 2.
let rs = rows_of(
db.execute("SELECT name, age FROM users WHERE age >= 18 ORDER BY age DESC LIMIT 2")
.unwrap(),
);
assert_eq!(rs.columns, vec!["name".to_string(), "age".to_string()]);
assert_eq!(
rs.rows,
vec![
vec![Value::Str("cora".into()), Value::Int(52)],
vec![Value::Str("ada".into()), Value::Int(36)],
]
);
}
#[test]
fn select_star_projects_all_columns() {
let path = db_path("star");
let mut db = Executor::open(&path).unwrap();
db.execute("CREATE TABLE t (a INTEGER NOT NULL, b TEXT)")
.unwrap();
db.execute("INSERT INTO t (a, b) VALUES (7, 'x')").unwrap();
let rs = rows_of(db.execute("SELECT * FROM t").unwrap());
assert_eq!(rs.columns, vec!["a".to_string(), "b".to_string()]);
assert_eq!(rs.rows, vec![vec![Value::Int(7), Value::Str("x".into())]]);
}
#[test]
fn catalog_and_rows_persist_across_reopen() {
let path = db_path("persist");
{
let mut db = Executor::open(&path).unwrap();
db.execute("CREATE TABLE k (id INTEGER NOT NULL, v TEXT)")
.unwrap();
db.execute("INSERT INTO k (id, v) VALUES (1, 'alpha')")
.unwrap();
db.execute("INSERT INTO k (id, v) VALUES (2, 'beta')")
.unwrap();
}
// Reopen: the catalog and rows must still be there.
let mut db = Executor::open(&path).unwrap();
let rs = rows_of(db.execute("SELECT id, v FROM k ORDER BY id ASC").unwrap());
assert_eq!(
rs.rows,
vec![
vec![Value::Int(1), Value::Str("alpha".into())],
vec![Value::Int(2), Value::Str("beta".into())],
]
);
// Inserting after reopen continues the rowid sequence, not restart it.
db.execute("INSERT INTO k (id, v) VALUES (3, 'gamma')")
.unwrap();
let rs = rows_of(db.execute("SELECT id FROM k").unwrap());
assert_eq!(rs.rows.len(), 3);
}
#[test]
fn errors_are_reported_not_panicked() {
let path = db_path("errors");
let mut db = Executor::open(&path).unwrap();
db.execute("CREATE TABLE t (id INTEGER NOT NULL, name TEXT)")
.unwrap();
// Duplicate table.
assert!(db.execute("CREATE TABLE t (id INTEGER NOT NULL)").is_err());
// Unknown table.
assert!(db.execute("SELECT * FROM ghost").is_err());
// NOT NULL violation.
assert!(db
.execute("INSERT INTO t (id, name) VALUES (NULL, 'x')")
.is_err());
// Unknown column in WHERE.
db.execute("INSERT INTO t (id, name) VALUES (1, 'x')")
.unwrap();
assert!(db.execute("SELECT * FROM t WHERE nope = 1").is_err());
}
#[test]
fn update_with_where_touches_only_matching_rows() {
let path = db_path("update");
let mut db = Executor::open(&path).unwrap();
db.execute("CREATE TABLE u (id INTEGER NOT NULL, name TEXT, age INTEGER)")
.unwrap();
for sql in [
"INSERT INTO u (id, name, age) VALUES (1, 'ada', 36)",
"INSERT INTO u (id, name, age) VALUES (2, 'bob', 17)",
"INSERT INTO u (id, name, age) VALUES (3, 'cora', 52)",
] {
db.execute(sql).unwrap();
}
// Update the two adults' name + age; the minor is untouched.
assert_eq!(
db.execute("UPDATE u SET name = 'adult', age = 40 WHERE age >= 18")
.unwrap(),
Outcome::Affected(2)
);
let rs = rows_of(
db.execute("SELECT id, name, age FROM u ORDER BY id ASC")
.unwrap(),
);
assert_eq!(
rs.rows,
vec![
vec![Value::Int(1), Value::Str("adult".into()), Value::Int(40)],
vec![Value::Int(2), Value::Str("bob".into()), Value::Int(17)],
vec![Value::Int(3), Value::Str("adult".into()), Value::Int(40)],
]
);
}
#[test]
fn update_type_mismatch_is_rejected() {
let path = db_path("update_mismatch");
let mut db = Executor::open(&path).unwrap();
db.execute("CREATE TABLE u (id INTEGER NOT NULL, age INTEGER)")
.unwrap();
db.execute("INSERT INTO u (id, age) VALUES (1, 10)")
.unwrap();
// Assigning a string into an INTEGER column must error, not panic.
assert!(db
.execute("UPDATE u SET age = 'oops' WHERE id = 1")
.is_err());
// Unknown column in SET must error too.
assert!(db.execute("UPDATE u SET nope = 1").is_err());
// The row is unchanged.
let rs = rows_of(db.execute("SELECT age FROM u").unwrap());
assert_eq!(rs.rows, vec![vec![Value::Int(10)]]);
}
#[test]
fn delete_with_where_and_persistence() {
let path = db_path("delete");
{
let mut db = Executor::open(&path).unwrap();
db.execute("CREATE TABLE d (id INTEGER NOT NULL, v INTEGER)")
.unwrap();
for i in 1..=4 {
db.execute(&format!("INSERT INTO d (id, v) VALUES ({i}, {})", i * 10))
.unwrap();
}
// Delete the two rows with v > 20 (ids 3 and 4).
assert_eq!(
db.execute("DELETE FROM d WHERE v > 20").unwrap(),
Outcome::Affected(2)
);
let rs = rows_of(db.execute("SELECT id FROM d ORDER BY id ASC").unwrap());
let ids: Vec<i64> = rs
.rows
.iter()
.map(|r| match r[0] {
Value::Int(i) => i,
_ => panic!(),
})
.collect();
assert_eq!(ids, vec![1, 2]);
}
// Reopen: the deletes must have persisted.
let mut db = Executor::open(&path).unwrap();
let rs = rows_of(db.execute("SELECT id FROM d ORDER BY id ASC").unwrap());
assert_eq!(rs.rows.len(), 2);
// DELETE without WHERE clears the table.
assert_eq!(db.execute("DELETE FROM d").unwrap(), Outcome::Affected(2));
let rs = rows_of(db.execute("SELECT id FROM d").unwrap());
assert!(rs.rows.is_empty());
}
#[test]
fn update_delete_unknown_table_errors() {
let path = db_path("ud_ghost");
let mut db = Executor::open(&path).unwrap();
assert!(db.execute("UPDATE ghost SET a = 1").is_err());
assert!(db.execute("DELETE FROM ghost").is_err());
}
#[test]
fn where_and_or_not_combinations() {
let path = db_path("bool");
let mut db = Executor::open(&path).unwrap();
db.execute("CREATE TABLE n (id INTEGER NOT NULL, v INTEGER)")
.unwrap();
for i in 1..=5 {
db.execute(&format!("INSERT INTO n (id, v) VALUES ({i}, {})", i * 10))
.unwrap();
}
let rs = rows_of(
db.execute("SELECT id FROM n WHERE (v > 10 AND v < 50) OR v = 50 ORDER BY id ASC")
.unwrap(),
);
let ids: Vec<i64> = rs
.rows
.iter()
.map(|r| match r[0] {
Value::Int(i) => i,
_ => panic!("expected int"),
})
.collect();
assert_eq!(ids, vec![2, 3, 4, 5]);
}
#[test]
fn count_star_and_count_column_handle_nulls() {
let path = db_path("agg_count");
let mut db = Executor::open(&path).unwrap();
db.execute("CREATE TABLE c (id INTEGER NOT NULL, points INTEGER)")
.unwrap();
for sql in [
"INSERT INTO c (id, points) VALUES (1, 10)",
"INSERT INTO c (id, points) VALUES (2, NULL)",
"INSERT INTO c (id, points) VALUES (3, 30)",
] {
db.execute(sql).unwrap();
}
let rs = rows_of(db.execute("SELECT COUNT(*), COUNT(points) FROM c").unwrap());
assert_eq!(
rs.columns,
vec!["COUNT(*)".to_string(), "COUNT(points)".to_string()]
);
assert_eq!(rs.rows, vec![vec![Value::Int(3), Value::Int(2)]]);
}
#[test]
fn sum_avg_min_and_max_aggregate_values() {
let path = db_path("agg_values");
let mut db = Executor::open(&path).unwrap();
db.execute("CREATE TABLE a (id INTEGER NOT NULL, name TEXT, points INTEGER)")
.unwrap();
for sql in [
"INSERT INTO a (id, name, points) VALUES (1, 'cora', 10)",
"INSERT INTO a (id, name, points) VALUES (2, 'ada', NULL)",
"INSERT INTO a (id, name, points) VALUES (3, 'bob', 30)",
] {
db.execute(sql).unwrap();
}
let rs = rows_of(
db.execute(
"SELECT SUM(points), AVG(points), MIN(points), MAX(points), MIN(name), MAX(name) FROM a",
)
.unwrap(),
);
assert_eq!(
rs.columns,
vec![
"SUM(points)".to_string(),
"AVG(points)".to_string(),
"MIN(points)".to_string(),
"MAX(points)".to_string(),
"MIN(name)".to_string(),
"MAX(name)".to_string(),
]
);
assert_eq!(
rs.rows,
vec![vec![
Value::Int(40),
Value::Float(20.0),
Value::Int(10),
Value::Int(30),
Value::Str("ada".into()),
Value::Str("cora".into()),
]]
);
}
#[test]
fn aggregates_group_by_where_order_by_and_limit() {
let path = db_path("agg_group");
let mut db = Executor::open(&path).unwrap();
db.execute("CREATE TABLE g (id INTEGER NOT NULL, dept TEXT, active INTEGER, points INTEGER)")
.unwrap();
for sql in [
"INSERT INTO g (id, dept, active, points) VALUES (1, 'eng', 1, 10)",
"INSERT INTO g (id, dept, active, points) VALUES (2, 'eng', 1, 20)",
"INSERT INTO g (id, dept, active, points) VALUES (3, 'ops', 1, 5)",
"INSERT INTO g (id, dept, active, points) VALUES (4, 'ops', 0, 100)",
"INSERT INTO g (id, dept, active, points) VALUES (5, 'sales', 1, NULL)",
] {
db.execute(sql).unwrap();
}
let rs = rows_of(
db.execute(
"SELECT dept, COUNT(*), COUNT(points), SUM(points), AVG(points), MIN(points), MAX(points) \
FROM g WHERE active = 1 GROUP BY dept ORDER BY dept ASC LIMIT 2",
)
.unwrap(),
);
assert_eq!(
rs.columns,
vec![
"dept".to_string(),
"COUNT(*)".to_string(),
"COUNT(points)".to_string(),
"SUM(points)".to_string(),
"AVG(points)".to_string(),
"MIN(points)".to_string(),
"MAX(points)".to_string(),
]
);
assert_eq!(
rs.rows,
vec![
vec![
Value::Str("eng".into()),
Value::Int(2),
Value::Int(2),
Value::Int(30),
Value::Float(15.0),
Value::Int(10),
Value::Int(20),
],
vec![
Value::Str("ops".into()),
Value::Int(1),
Value::Int(1),
Value::Int(5),
Value::Float(5.0),
Value::Int(5),
Value::Int(5),
],
]
);
}
#[test]
fn aggregate_errors_are_reported_not_panicked() {
let path = db_path("agg_errors");
let mut db = Executor::open(&path).unwrap();
db.execute("CREATE TABLE e (id INTEGER NOT NULL, name TEXT, points INTEGER)")
.unwrap();
db.execute("INSERT INTO e (id, name, points) VALUES (1, 'ada', 10)")
.unwrap();
assert!(db.execute("SELECT SUM(name) FROM e").is_err());
assert!(db.execute("SELECT name, COUNT(*) FROM e").is_err());
assert!(db
.execute("SELECT dept, COUNT(*) FROM e GROUP BY dept")
.is_err());
assert!(db.execute("SELECT COUNT(*) FROM e GROUP BY nope").is_err());
}