citadeldb-sql 1.10.0

use citadel::{Argon2Profile, DatabaseBuilder};
use citadel_sql::{Connection, ExecutionResult, SqlError, Value};

fn create_db(dir: &std::path::Path) -> citadel::Database {
    DatabaseBuilder::new(dir.join("test.db"))
        .passphrase(b"x")
        .argon2_profile(Argon2Profile::Iot)
        .create()
        .unwrap()
}

fn assert_ok(r: ExecutionResult) {
    matches!(r, ExecutionResult::Ok | ExecutionResult::RowsAffected(_));
}

fn setup(conn: &Connection) {
    assert_ok(
        conn.execute("CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT)")
            .unwrap(),
    );
    conn.execute("INSERT INTO t VALUES (1, 'Alice'), (2, 'BOB'), (3, 'charlie'), (4, 'alice')")
        .unwrap();
}

#[test]
fn collate_nocase_expression_equality() {
    let dir = tempfile::tempdir().unwrap();
    let db = create_db(dir.path());
    let conn = Connection::open(&db).unwrap();
    setup(&conn);
    let qr = conn
        .query("SELECT id FROM t WHERE name = 'alice' COLLATE NOCASE ORDER BY id")
        .unwrap();
    assert_eq!(qr.rows.len(), 2);
    assert_eq!(qr.rows[0][0], Value::Integer(1));
    assert_eq!(qr.rows[1][0], Value::Integer(4));
}

#[test]
fn collate_binary_expression_equality() {
    let dir = tempfile::tempdir().unwrap();
    let db = create_db(dir.path());
    let conn = Connection::open(&db).unwrap();
    setup(&conn);
    let qr = conn
        .query("SELECT id FROM t WHERE name = 'alice' COLLATE BINARY")
        .unwrap();
    assert_eq!(qr.rows.len(), 1);
    assert_eq!(qr.rows[0][0], Value::Integer(4));
}

#[test]
fn collate_rtrim_expression_equality() {
    let dir = tempfile::tempdir().unwrap();
    let db = create_db(dir.path());
    let conn = Connection::open(&db).unwrap();
    assert_ok(
        conn.execute("CREATE TABLE r (id INTEGER PRIMARY KEY, s TEXT)")
            .unwrap(),
    );
    conn.execute("INSERT INTO r VALUES (1, 'abc'), (2, 'abc  '), (3, 'abcx')")
        .unwrap();
    let qr = conn
        .query("SELECT id FROM r WHERE s = 'abc' COLLATE RTRIM ORDER BY id")
        .unwrap();
    assert_eq!(qr.rows.len(), 2);
    assert_eq!(qr.rows[0][0], Value::Integer(1));
    assert_eq!(qr.rows[1][0], Value::Integer(2));
}

#[test]
fn collate_unsupported_name_rejected() {
    let dir = tempfile::tempdir().unwrap();
    let db = create_db(dir.path());
    let conn = Connection::open(&db).unwrap();
    setup(&conn);
    let err = conn
        .execute("SELECT * FROM t WHERE name = 'a' COLLATE FRENCH")
        .unwrap_err();
    assert!(matches!(err, SqlError::Unsupported(_)));
}

#[test]
fn order_by_column_collation_nocase() {
    let dir = tempfile::tempdir().unwrap();
    let db = create_db(dir.path());
    let conn = Connection::open(&db).unwrap();
    conn.execute("CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT COLLATE NOCASE)")
        .unwrap();
    conn.execute("INSERT INTO t VALUES (1, 'banana'), (2, 'Apple'), (3, 'cherry'), (4, 'apricot')")
        .unwrap();
    let qr = conn.query("SELECT id FROM t ORDER BY name").unwrap();
    assert_eq!(qr.rows.len(), 4);
    assert_eq!(qr.rows[0][0], Value::Integer(2));
    assert_eq!(qr.rows[1][0], Value::Integer(4));
    assert_eq!(qr.rows[2][0], Value::Integer(1));
    assert_eq!(qr.rows[3][0], Value::Integer(3));
}

#[test]
fn order_by_explicit_collate_nocase() {
    let dir = tempfile::tempdir().unwrap();
    let db = create_db(dir.path());
    let conn = Connection::open(&db).unwrap();
    setup(&conn);
    let qr = conn
        .query("SELECT id FROM t ORDER BY name COLLATE NOCASE")
        .unwrap();
    assert_eq!(qr.rows.len(), 4);
    assert_eq!(qr.rows[0][0], Value::Integer(1));
    assert_eq!(qr.rows[1][0], Value::Integer(4));
    assert_eq!(qr.rows[2][0], Value::Integer(2));
    assert_eq!(qr.rows[3][0], Value::Integer(3));
}

#[test]
fn unique_index_nocase_rejects_case_variant() {
    let dir = tempfile::tempdir().unwrap();
    let db = create_db(dir.path());
    let conn = Connection::open(&db).unwrap();
    conn.execute("CREATE TABLE u (id INTEGER PRIMARY KEY, name TEXT COLLATE NOCASE UNIQUE)")
        .unwrap();
    conn.execute("INSERT INTO u VALUES (1, 'Alice')").unwrap();
    let err = conn
        .execute("INSERT INTO u VALUES (2, 'ALICE')")
        .unwrap_err();
    assert!(matches!(err, SqlError::UniqueViolation(_)), "{err:?}");
}

#[test]
fn unique_index_explicit_collate_in_create_index() {
    let dir = tempfile::tempdir().unwrap();
    let db = create_db(dir.path());
    let conn = Connection::open(&db).unwrap();
    conn.execute("CREATE TABLE u (id INTEGER PRIMARY KEY, name TEXT)")
        .unwrap();
    conn.execute("CREATE UNIQUE INDEX u_name ON u (name COLLATE NOCASE)")
        .unwrap();
    conn.execute("INSERT INTO u VALUES (1, 'Alice')").unwrap();
    let err = conn
        .execute("INSERT INTO u VALUES (2, 'alice')")
        .unwrap_err();
    assert!(matches!(err, SqlError::UniqueViolation(_)), "{err:?}");
}

#[test]
fn create_table_with_column_collate_persists() {
    let dir = tempfile::tempdir().unwrap();
    {
        let db = create_db(dir.path());
        let conn = Connection::open(&db).unwrap();
        conn.execute("CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT COLLATE NOCASE)")
            .unwrap();
    }
    let db = DatabaseBuilder::new(dir.path().join("test.db"))
        .passphrase(b"x")
        .argon2_profile(Argon2Profile::Iot)
        .open()
        .unwrap();
    let conn = Connection::open(&db).unwrap();
    conn.execute("INSERT INTO t VALUES (1, 'Alice')").unwrap();
    let qr = conn.query("SELECT id FROM t WHERE name = 'alice'").unwrap();
    assert_eq!(qr.rows.len(), 1);
}

#[test]
fn nocase_column_index_equality_probe_matches_all_cases() {
    let dir = tempfile::tempdir().unwrap();
    let db = create_db(dir.path());
    let conn = Connection::open(&db).unwrap();
    conn.execute("CREATE TABLE p (id INTEGER PRIMARY KEY, name TEXT COLLATE NOCASE)")
        .unwrap();
    conn.execute("CREATE INDEX p_name ON p (name)").unwrap();
    conn.execute("INSERT INTO p VALUES (1, 'alice'), (2, 'Bob'), (3, 'CAROL')")
        .unwrap();

    for (probe, expected) in [("ALICE", 1i64), ("alice", 1), ("bob", 2), ("Carol", 3)] {
        let qr = conn
            .query(&format!("SELECT id FROM p WHERE name = '{probe}'"))
            .unwrap();
        assert_eq!(
            qr.rows,
            vec![vec![Value::Integer(expected)]],
            "probe {probe:?} against the NOCASE index"
        );
    }
}

#[test]
fn nocase_column_index_range_probe_folds_bounds() {
    let dir = tempfile::tempdir().unwrap();
    let db = create_db(dir.path());
    let conn = Connection::open(&db).unwrap();
    conn.execute("CREATE TABLE p (id INTEGER PRIMARY KEY, name TEXT COLLATE NOCASE)")
        .unwrap();
    conn.execute("CREATE INDEX p_name ON p (name)").unwrap();
    conn.execute("INSERT INTO p VALUES (1, 'apple'), (2, 'Banana'), (3, 'CHERRY')")
        .unwrap();

    // NOCASE range: 'B' <= name <= 'c' covers Banana and CHERRY case-insensitively.
    let qr = conn
        .query("SELECT id FROM p WHERE name >= 'B' AND name <= 'cz' ORDER BY id")
        .unwrap();
    assert_eq!(
        qr.rows,
        vec![vec![Value::Integer(2)], vec![Value::Integer(3)]]
    );
}

#[test]
fn rtrim_column_index_equality_probe_folds() {
    let dir = tempfile::tempdir().unwrap();
    let db = create_db(dir.path());
    let conn = Connection::open(&db).unwrap();
    conn.execute("CREATE TABLE p (id INTEGER PRIMARY KEY, name TEXT COLLATE RTRIM)")
        .unwrap();
    conn.execute("CREATE INDEX p_name ON p (name)").unwrap();
    conn.execute("INSERT INTO p VALUES (1, 'x  '), (2, 'y')")
        .unwrap();

    let qr = conn.query("SELECT id FROM p WHERE name = 'x'").unwrap();
    assert_eq!(qr.rows, vec![vec![Value::Integer(1)]]);
    let qr = conn.query("SELECT id FROM p WHERE name = 'y   '").unwrap();
    assert_eq!(qr.rows, vec![vec![Value::Integer(2)]]);
}