AkurAI-Framework / crates/sqlexec/tests/end_to_end.rs

Latest change a26d2091b5fd875b4928c72190c7dbf9f37b9252 - feat(sql): add UPDATE and DELETE to parser and executor 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]);
}