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

Latest change d971623579ac6cb425eea3ca33f24af5983c18b2 - feat(sqlexec): SQL executor over the storage B+tree (CREATE/INSERT/SELECT) 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 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]);
}