citadeldb-sql 0.14.0

SQL parser, planner, and executor for Citadel encrypted database
Documentation 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123

use citadel::{Argon2Profile, DatabaseBuilder};
use citadel_sql::{Connection, 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()
}

#[test]
fn create_gin_index_succeeds() {
    let dir = tempfile::tempdir().unwrap();
    let db = create_db(dir.path());
    let conn = Connection::open(&db).unwrap();
    conn.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, data JSONB)")
        .unwrap();
    conn.execute("INSERT INTO users VALUES (1, CAST('{\"role\":\"admin\"}' AS JSONB))")
        .unwrap();
    conn.execute("CREATE INDEX idx_data ON users USING gin (data)")
        .unwrap();
}

#[test]
fn gin_index_accelerates_contains_query() {
    let dir = tempfile::tempdir().unwrap();
    let db = create_db(dir.path());
    let conn = Connection::open(&db).unwrap();
    conn.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, data JSONB)")
        .unwrap();
    conn.execute("BEGIN").unwrap();
    for i in 0..100 {
        let role = if i % 10 == 0 { "admin" } else { "member" };
        let payload = format!(r#"{{"id":{i},"role":"{role}"}}"#);
        conn.execute(&format!(
            "INSERT INTO users VALUES ({i}, CAST('{payload}' AS JSONB))"
        ))
        .unwrap();
    }
    conn.execute("COMMIT").unwrap();
    conn.execute("CREATE INDEX idx_data ON users USING gin (data)")
        .unwrap();

    let qr = conn
        .query("SELECT id FROM users WHERE data @> CAST('{\"role\":\"admin\"}' AS JSONB)")
        .unwrap();
    assert_eq!(qr.rows.len(), 10);
    let ids: Vec<i64> = qr
        .rows
        .iter()
        .map(|r| match &r[0] {
            Value::Integer(i) => *i,
            _ => panic!("expected integer"),
        })
        .collect();
    for id in ids {
        assert_eq!(id % 10, 0);
    }
}

#[test]
fn gin_index_maintained_on_insert_after_create() {
    let dir = tempfile::tempdir().unwrap();
    let db = create_db(dir.path());
    let conn = Connection::open(&db).unwrap();
    conn.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, data JSONB)")
        .unwrap();
    conn.execute("CREATE INDEX idx_data ON users USING gin (data)")
        .unwrap();
    conn.execute("INSERT INTO users VALUES (1, CAST('{\"role\":\"admin\"}' AS JSONB))")
        .unwrap();
    conn.execute("INSERT INTO users VALUES (2, CAST('{\"role\":\"member\"}' AS JSONB))")
        .unwrap();
    let qr = conn
        .query("SELECT id FROM users WHERE data @> CAST('{\"role\":\"admin\"}' AS JSONB)")
        .unwrap();
    assert_eq!(qr.rows.len(), 1);
    assert_eq!(qr.rows[0][0], Value::Integer(1));
}

#[test]
fn gin_index_maintained_on_delete() {
    let dir = tempfile::tempdir().unwrap();
    let db = create_db(dir.path());
    let conn = Connection::open(&db).unwrap();
    conn.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, data JSONB)")
        .unwrap();
    conn.execute("INSERT INTO users VALUES (1, CAST('{\"role\":\"admin\"}' AS JSONB))")
        .unwrap();
    conn.execute("INSERT INTO users VALUES (2, CAST('{\"role\":\"admin\"}' AS JSONB))")
        .unwrap();
    conn.execute("CREATE INDEX idx_data ON users USING gin (data)")
        .unwrap();
    conn.execute("DELETE FROM users WHERE id = 1").unwrap();
    let qr = conn
        .query("SELECT id FROM users WHERE data @> CAST('{\"role\":\"admin\"}' AS JSONB)")
        .unwrap();
    assert_eq!(qr.rows.len(), 1);
    assert_eq!(qr.rows[0][0], Value::Integer(2));
}

#[test]
fn gin_rejects_on_non_jsonb_column() {
    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)")
        .unwrap();
    let result = conn.execute("CREATE INDEX idx_name ON t USING gin (name)");
    assert!(result.is_err(), "GIN on TEXT column should be rejected");
}

#[test]
fn gin_rejects_unique() {
    let dir = tempfile::tempdir().unwrap();
    let db = create_db(dir.path());
    let conn = Connection::open(&db).unwrap();
    conn.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, data JSONB)")
        .unwrap();
    let result = conn.execute("CREATE UNIQUE INDEX idx_data ON users USING gin (data)");
    assert!(result.is_err(), "UNIQUE GIN should be rejected");
}