use citadel::{Argon2Profile, DatabaseBuilder};
use citadel_sql::{Connection, QueryResult, SqlError, Value};
fn create_db(dir: &std::path::Path) -> citadel::Database {
let db_path = dir.join("test.db");
DatabaseBuilder::new(db_path)
.passphrase(b"test-passphrase")
.argon2_profile(Argon2Profile::Iot)
.create()
.unwrap()
}
fn query(conn: &Connection, sql: &str) -> QueryResult {
conn.query(sql).unwrap()
}
#[test]
fn stored_basic_insert_select() {
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, a INTEGER, b INTEGER, \
sum INTEGER GENERATED ALWAYS AS (a + b) STORED)",
)
.unwrap();
conn.execute("INSERT INTO t (id, a, b) VALUES (1, 3, 4)")
.unwrap();
let qr = query(&conn, "SELECT sum FROM t WHERE id = 1");
assert_eq!(qr.rows[0][0], Value::Integer(7));
}
#[test]
fn stored_concat() {
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, fn TEXT NOT NULL, ln TEXT NOT NULL, \
full TEXT GENERATED ALWAYS AS (fn || ' ' || ln) STORED)",
)
.unwrap();
conn.execute("INSERT INTO u (id, fn, ln) VALUES (1, 'Alice', 'Doe')")
.unwrap();
let qr = query(&conn, "SELECT full FROM u");
assert_eq!(qr.rows[0][0], Value::Text("Alice Doe".into()));
}
#[test]
fn stored_function() {
let dir = tempfile::tempdir().unwrap();
let db = create_db(dir.path());
let conn = Connection::open(&db).unwrap();
conn.execute(
"CREATE TABLE a (id INTEGER PRIMARY KEY, email TEXT NOT NULL, \
email_lower TEXT GENERATED ALWAYS AS (LOWER(email)) STORED)",
)
.unwrap();
conn.execute("INSERT INTO a (id, email) VALUES (1, 'Alice@Example.COM')")
.unwrap();
let qr = query(&conn, "SELECT email_lower FROM a");
assert_eq!(qr.rows[0][0], Value::Text("alice@example.com".into()));
}
#[test]
fn stored_case_expression() {
let dir = tempfile::tempdir().unwrap();
let db = create_db(dir.path());
let conn = Connection::open(&db).unwrap();
conn.execute(
"CREATE TABLE g (id INTEGER PRIMARY KEY, score INTEGER NOT NULL, \
grade TEXT GENERATED ALWAYS AS (CASE WHEN score >= 90 THEN 'A' \
WHEN score >= 60 THEN 'B' ELSE 'F' END) STORED)",
)
.unwrap();
conn.execute("INSERT INTO g (id, score) VALUES (1, 95), (2, 75), (3, 30)")
.unwrap();
let qr = query(&conn, "SELECT id, grade FROM g ORDER BY id");
assert_eq!(qr.rows[0][1], Value::Text("A".into()));
assert_eq!(qr.rows[1][1], Value::Text("B".into()));
assert_eq!(qr.rows[2][1], Value::Text("F".into()));
}
#[test]
fn stored_null_input_yields_null() {
let dir = tempfile::tempdir().unwrap();
let db = create_db(dir.path());
let conn = Connection::open(&db).unwrap();
conn.execute(
"CREATE TABLE n (id INTEGER PRIMARY KEY, a INTEGER, \
doubled INTEGER GENERATED ALWAYS AS (a * 2) STORED)",
)
.unwrap();
conn.execute("INSERT INTO n (id, a) VALUES (1, NULL)")
.unwrap();
let qr = query(&conn, "SELECT doubled FROM n");
assert_eq!(qr.rows[0][0], Value::Null);
}
#[test]
fn stored_not_null_with_null_expr_errors() {
let dir = tempfile::tempdir().unwrap();
let db = create_db(dir.path());
let conn = Connection::open(&db).unwrap();
conn.execute(
"CREATE TABLE nn (id INTEGER PRIMARY KEY, a INTEGER, \
d INTEGER NOT NULL GENERATED ALWAYS AS (a * 2) STORED)",
)
.unwrap();
let err = conn
.execute("INSERT INTO nn (id, a) VALUES (1, NULL)")
.unwrap_err();
assert!(matches!(err, SqlError::NotNullViolation(_)));
}
#[test]
fn create_index_on_stored_column_works() {
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, email TEXT NOT NULL, \
el TEXT GENERATED ALWAYS AS (LOWER(email)) STORED)",
)
.unwrap();
conn.execute("CREATE INDEX idx_el ON u(el)").unwrap();
conn.execute("INSERT INTO u (id, email) VALUES (1, 'X@Y.COM')")
.unwrap();
let qr = query(&conn, "SELECT id FROM u WHERE el = 'x@y.com'");
assert_eq!(qr.rows[0][0], Value::Integer(1));
}
#[test]
fn virtual_basic() {
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, cents INTEGER NOT NULL, \
dollars REAL GENERATED ALWAYS AS (cents / 100.0) VIRTUAL)",
)
.unwrap();
conn.execute("INSERT INTO p (id, cents) VALUES (1, 250)")
.unwrap();
let qr = query(&conn, "SELECT dollars FROM p");
assert_eq!(qr.rows[0][0], Value::Real(2.5));
}
#[test]
fn virtual_recomputes_after_update() {
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, cents INTEGER NOT NULL, \
dollars REAL GENERATED ALWAYS AS (cents / 100.0) VIRTUAL)",
)
.unwrap();
conn.execute("INSERT INTO p (id, cents) VALUES (1, 250)")
.unwrap();
conn.execute("UPDATE p SET cents = 999 WHERE id = 1")
.unwrap();
let qr = query(&conn, "SELECT dollars FROM p");
assert_eq!(qr.rows[0][0], Value::Real(9.99));
}
#[test]
fn create_index_on_virtual_errors() {
let dir = tempfile::tempdir().unwrap();
let db = create_db(dir.path());
let conn = Connection::open(&db).unwrap();
conn.execute(
"CREATE TABLE v (id INTEGER PRIMARY KEY, x INTEGER, \
d INTEGER GENERATED ALWAYS AS (x * 2) VIRTUAL)",
)
.unwrap();
let err = conn.execute("CREATE INDEX i ON v(d)").unwrap_err();
assert!(matches!(err, SqlError::Unsupported(ref m) if m.contains("VIRTUAL")));
}
#[test]
fn update_propagates_to_stored() {
let dir = tempfile::tempdir().unwrap();
let db = create_db(dir.path());
let conn = Connection::open(&db).unwrap();
conn.execute(
"CREATE TABLE o (id INTEGER PRIMARY KEY, qty INTEGER, price REAL, \
total REAL GENERATED ALWAYS AS (qty * price) STORED)",
)
.unwrap();
conn.execute("INSERT INTO o (id, qty, price) VALUES (1, 3, 9.99)")
.unwrap();
conn.execute("UPDATE o SET qty = 5 WHERE id = 1").unwrap();
let qr = query(&conn, "SELECT total FROM o");
if let Value::Real(v) = qr.rows[0][0] {
assert!((v - 49.95).abs() < 1e-9);
} else {
panic!("expected real");
}
}
#[test]
fn update_multiple_base_cols_recomputes() {
let dir = tempfile::tempdir().unwrap();
let db = create_db(dir.path());
let conn = Connection::open(&db).unwrap();
conn.execute(
"CREATE TABLE m (id INTEGER PRIMARY KEY, a INTEGER, b INTEGER, \
s INTEGER GENERATED ALWAYS AS (a + b) STORED)",
)
.unwrap();
conn.execute("INSERT INTO m (id, a, b) VALUES (1, 1, 2)")
.unwrap();
conn.execute("UPDATE m SET a = 10, b = 20 WHERE id = 1")
.unwrap();
let qr = query(&conn, "SELECT s FROM m");
assert_eq!(qr.rows[0][0], Value::Integer(30));
}
#[test]
fn insert_into_generated_errors() {
let dir = tempfile::tempdir().unwrap();
let db = create_db(dir.path());
let conn = Connection::open(&db).unwrap();
conn.execute(
"CREATE TABLE g (id INTEGER PRIMARY KEY, a INTEGER, \
d INTEGER GENERATED ALWAYS AS (a * 2) STORED)",
)
.unwrap();
let err = conn
.execute("INSERT INTO g (id, a, d) VALUES (1, 3, 99)")
.unwrap_err();
assert!(matches!(err, SqlError::CannotInsertIntoGeneratedColumn(_)));
}
#[test]
fn update_set_generated_errors() {
let dir = tempfile::tempdir().unwrap();
let db = create_db(dir.path());
let conn = Connection::open(&db).unwrap();
conn.execute(
"CREATE TABLE g (id INTEGER PRIMARY KEY, a INTEGER, \
d INTEGER GENERATED ALWAYS AS (a * 2) STORED)",
)
.unwrap();
conn.execute("INSERT INTO g (id, a) VALUES (1, 3)").unwrap();
let err = conn
.execute("UPDATE g SET d = 99 WHERE id = 1")
.unwrap_err();
assert!(matches!(err, SqlError::CannotUpdateGeneratedColumn(_)));
}
#[test]
fn default_and_generated_combined_errors() {
let dir = tempfile::tempdir().unwrap();
let db = create_db(dir.path());
let conn = Connection::open(&db).unwrap();
let err = conn
.execute(
"CREATE TABLE x (id INTEGER PRIMARY KEY, a INTEGER, \
d INTEGER DEFAULT 5 GENERATED ALWAYS AS (a * 2) STORED)",
)
.unwrap_err();
assert!(matches!(err, SqlError::Unsupported(ref m) if m.contains("DEFAULT")));
}
#[test]
fn primary_key_and_generated_errors() {
let dir = tempfile::tempdir().unwrap();
let db = create_db(dir.path());
let conn = Connection::open(&db).unwrap();
let err = conn
.execute(
"CREATE TABLE pk (a INTEGER, \
d INTEGER PRIMARY KEY GENERATED ALWAYS AS (a * 2) STORED)",
)
.unwrap_err();
assert!(matches!(err, SqlError::Unsupported(ref m) if m.contains("PRIMARY KEY")));
}
#[test]
fn chained_generated_refs_rejected() {
let dir = tempfile::tempdir().unwrap();
let db = create_db(dir.path());
let conn = Connection::open(&db).unwrap();
let err = conn
.execute(
"CREATE TABLE c (id INTEGER PRIMARY KEY, a INTEGER, \
b INTEGER GENERATED ALWAYS AS (a * 2) STORED, \
c INTEGER GENERATED ALWAYS AS (b * 2) STORED)",
)
.unwrap_err();
assert!(matches!(err, SqlError::GeneratedColumnReference(_)));
}
#[test]
fn aggregate_in_generated_rejected() {
let dir = tempfile::tempdir().unwrap();
let db = create_db(dir.path());
let conn = Connection::open(&db).unwrap();
let err = conn
.execute(
"CREATE TABLE a (id INTEGER PRIMARY KEY, x INTEGER, \
c INTEGER GENERATED ALWAYS AS (COUNT(x)) STORED)",
)
.unwrap_err();
assert!(matches!(err, SqlError::Unsupported(ref m) if m.contains("aggregate")));
}
#[test]
fn random_in_generated_rejected() {
let dir = tempfile::tempdir().unwrap();
let db = create_db(dir.path());
let conn = Connection::open(&db).unwrap();
let err = conn
.execute(
"CREATE TABLE r (id INTEGER PRIMARY KEY, \
v INTEGER GENERATED ALWAYS AS (RANDOM()) STORED)",
)
.unwrap_err();
assert!(matches!(err, SqlError::Unsupported(ref m) if m.contains("volatile")));
}
#[test]
fn now_in_generated_rejected() {
let dir = tempfile::tempdir().unwrap();
let db = create_db(dir.path());
let conn = Connection::open(&db).unwrap();
let err = conn
.execute(
"CREATE TABLE r (id INTEGER PRIMARY KEY, \
v TIMESTAMP GENERATED ALWAYS AS (NOW()) STORED)",
)
.unwrap_err();
assert!(matches!(err, SqlError::Unsupported(ref m) if m.contains("volatile")));
}
#[test]
fn alter_add_virtual_on_populated_table_works() {
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, a INTEGER)")
.unwrap();
conn.execute("INSERT INTO t VALUES (1, 5), (2, 10)")
.unwrap();
conn.execute("ALTER TABLE t ADD COLUMN d INTEGER GENERATED ALWAYS AS (a * 2) VIRTUAL")
.unwrap();
let qr = query(&conn, "SELECT id, d FROM t ORDER BY id");
assert_eq!(qr.rows[0][1], Value::Integer(10));
assert_eq!(qr.rows[1][1], Value::Integer(20));
}
#[test]
fn alter_add_stored_on_populated_table_errors() {
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, a INTEGER)")
.unwrap();
conn.execute("INSERT INTO t VALUES (1, 5)").unwrap();
let err = conn
.execute("ALTER TABLE t ADD COLUMN d INTEGER GENERATED ALWAYS AS (a * 2) STORED")
.unwrap_err();
assert!(matches!(err, SqlError::Unsupported(ref m) if m.contains("STORED")));
}
#[test]
fn alter_add_stored_on_empty_table_works() {
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, a INTEGER)")
.unwrap();
conn.execute("ALTER TABLE t ADD COLUMN d INTEGER GENERATED ALWAYS AS (a * 2) STORED")
.unwrap();
conn.execute("INSERT INTO t (id, a) VALUES (1, 7)").unwrap();
let qr = query(&conn, "SELECT d FROM t");
assert_eq!(qr.rows[0][0], Value::Integer(14));
}
#[test]
fn schema_persistence_v5_roundtrip() {
let dir = tempfile::tempdir().unwrap();
let db_path = dir.path().join("p.db");
{
let db = DatabaseBuilder::new(&db_path)
.passphrase(b"pw")
.argon2_profile(Argon2Profile::Iot)
.create()
.unwrap();
let conn = Connection::open(&db).unwrap();
conn.execute(
"CREATE TABLE t (id INTEGER PRIMARY KEY, a INTEGER, \
d INTEGER GENERATED ALWAYS AS (a * 3) STORED, \
v INTEGER GENERATED ALWAYS AS (a + 1) VIRTUAL)",
)
.unwrap();
conn.execute("INSERT INTO t (id, a) VALUES (1, 4)").unwrap();
}
let db = DatabaseBuilder::new(&db_path)
.passphrase(b"pw")
.argon2_profile(Argon2Profile::Iot)
.open()
.unwrap();
let conn = Connection::open(&db).unwrap();
let qr = query(&conn, "SELECT a, d, v FROM t WHERE id = 1");
assert_eq!(qr.rows[0][0], Value::Integer(4));
assert_eq!(qr.rows[0][1], Value::Integer(12));
assert_eq!(qr.rows[0][2], Value::Integer(5));
conn.execute("INSERT INTO t (id, a) VALUES (2, 10)")
.unwrap();
let qr2 = query(&conn, "SELECT d, v FROM t WHERE id = 2");
assert_eq!(qr2.rows[0][0], Value::Integer(30));
assert_eq!(qr2.rows[0][1], Value::Integer(11));
}
#[test]
fn returning_includes_generated_value() {
let dir = tempfile::tempdir().unwrap();
let db = create_db(dir.path());
let conn = Connection::open(&db).unwrap();
conn.execute(
"CREATE TABLE r (id INTEGER PRIMARY KEY, a INTEGER, \
d INTEGER GENERATED ALWAYS AS (a * 2) STORED)",
)
.unwrap();
let qr = query(&conn, "INSERT INTO r (id, a) VALUES (1, 6) RETURNING d");
assert_eq!(qr.rows[0][0], Value::Integer(12));
}
#[test]
fn identity_column_rejected() {
let dir = tempfile::tempdir().unwrap();
let db = create_db(dir.path());
let conn = Connection::open(&db).unwrap();
let err = conn
.execute("CREATE TABLE i (id INTEGER GENERATED BY DEFAULT AS IDENTITY, x INTEGER)")
.unwrap_err();
assert!(matches!(err, SqlError::Unsupported(ref m) if m.contains("identity")));
}
#[test]
fn bare_as_syntax_works() {
let dir = tempfile::tempdir().unwrap();
let db = create_db(dir.path());
let conn = Connection::open(&db).unwrap();
conn.execute("CREATE TABLE b (id INTEGER PRIMARY KEY, a INTEGER, d INTEGER AS (a * 5) STORED)")
.unwrap();
conn.execute("INSERT INTO b (id, a) VALUES (1, 4)").unwrap();
let qr = query(&conn, "SELECT d FROM b");
assert_eq!(qr.rows[0][0], Value::Integer(20));
}
#[test]
fn drop_base_column_with_gen_ref_errors() {
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, a INTEGER, \
d INTEGER GENERATED ALWAYS AS (a * 2) STORED)",
)
.unwrap();
let err = conn.execute("ALTER TABLE t DROP COLUMN a").unwrap_err();
assert!(matches!(err, SqlError::Unsupported(ref m) if m.contains("generated")));
}
#[test]
fn rename_base_column_rewrites_generated_sql() {
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, a INTEGER, \
d INTEGER GENERATED ALWAYS AS (a * 2) STORED)",
)
.unwrap();
conn.execute("INSERT INTO t (id, a) VALUES (1, 3)").unwrap();
conn.execute("ALTER TABLE t RENAME COLUMN a TO base")
.unwrap();
conn.execute("INSERT INTO t (id, base) VALUES (2, 7)")
.unwrap();
let qr = query(&conn, "SELECT id, d FROM t ORDER BY id");
assert_eq!(qr.rows[0][1], Value::Integer(6));
assert_eq!(qr.rows[1][1], Value::Integer(14));
}
#[test]
fn where_filter_against_virtual() {
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, a INTEGER, \
d INTEGER GENERATED ALWAYS AS (a * 10) VIRTUAL)",
)
.unwrap();
conn.execute("INSERT INTO t (id, a) VALUES (1, 1), (2, 5), (3, 10)")
.unwrap();
let qr = query(&conn, "SELECT id FROM t WHERE d >= 50 ORDER BY id");
assert_eq!(qr.rows.len(), 2);
assert_eq!(qr.rows[0][0], Value::Integer(2));
assert_eq!(qr.rows[1][0], Value::Integer(3));
}
#[test]
fn group_by_virtual() {
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, a INTEGER, \
bucket INTEGER GENERATED ALWAYS AS (a / 10) VIRTUAL)",
)
.unwrap();
conn.execute("INSERT INTO t (id, a) VALUES (1, 5), (2, 12), (3, 18), (4, 25)")
.unwrap();
let qr = query(
&conn,
"SELECT bucket, COUNT(*) FROM t GROUP BY bucket ORDER BY bucket",
);
assert_eq!(qr.rows.len(), 3);
assert_eq!(qr.rows[0][0], Value::Integer(0));
assert_eq!(qr.rows[0][1], Value::Integer(1));
assert_eq!(qr.rows[1][0], Value::Integer(1));
assert_eq!(qr.rows[1][1], Value::Integer(2));
assert_eq!(qr.rows[2][0], Value::Integer(2));
assert_eq!(qr.rows[2][1], Value::Integer(1));
}