diff options
Diffstat (limited to 'tests/queries.sql')
| -rw-r--r-- | tests/queries.sql | 35 |
1 files changed, 22 insertions, 13 deletions
diff --git a/tests/queries.sql b/tests/queries.sql index 6e68db1..85cc5fb 100644 --- a/tests/queries.sql +++ b/tests/queries.sql @@ -8,14 +8,16 @@ email TEXT NOT NULL UNIQUE, salt BLOB NOT NULL UNIQUE, pwhash BLOB NOT NULL - ); + ) STRICT; + CREATE TABLE IF NOT EXISTS "cracha_confirmation_attempts" ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, timestamp TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now')), -- uuid BLOB NOT NULL UNIQUE, user_id INTEGER NOT NULL REFERENCES "cracha_users"(id), token TEXT NOT NULL UNIQUE - ); + ) STRICT; + CREATE TABLE IF NOT EXISTS "cracha_user_confirmations" ( id INTEGER PRIMARY KEY AUTOINCREMENT, timestamp TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now')), @@ -23,34 +25,39 @@ REFERENCES "cracha_users"(id) UNIQUE, attempt_id INTEGER NOT NULL REFERENCES "cracha_confirmation_attempts"(id) UNIQUE - ); + ) STRICT; + CREATE TABLE IF NOT EXISTS "cracha_user_changes" ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, timestamp TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now')), user_id INTEGER NOT NULL REFERENCES "cracha_users"(id), attribute TEXT NOT NULL, value TEXT NOT NULL, - op BOOLEAN NOT NULL - ); + op INT NOT NULL CHECK(op IN (0, 1)) + ) STRICT; + -- CREATE TABLE IF NOT EXISTS "cracha_tokens" ( -- id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, -- timestamp TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now')), -- uuid BLOB NOT NULL UNIQUE, -- type TEXT NOT NULL - -- ); + -- ) STRICT; + CREATE TABLE IF NOT EXISTS "cracha_roles" ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, user_id INTEGER NOT NULL REFERENCES "cracha_users"(id), role TEXT NOT NULL, UNIQUE (user_id, role) - ); + ) STRICT; + CREATE TABLE IF NOT EXISTS "cracha_role_changes" ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, timestamp TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now')), user_id INTEGER NOT NULL REFERENCES "cracha_roles"(id), role TEXT NOT NULL, - op BOOLEAN NOT NULL - ); + op INT NOT NULL CHECK(op IN (0, 1)) + ) STRICT; + CREATE TABLE IF NOT EXISTS "cracha_sessions" ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, timestamp TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now')), @@ -60,21 +67,23 @@ -- revoked_at TEXT, -- revoker_id INTEGER REFERENCES "cracha_users"(id), -- FIXME: add provenance: login, refresh, confirmation, etc. - ); + ) STRICT; + CREATE TABLE IF NOT EXISTS "cracha_attempts" ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, timestamp TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now')), user_id INTEGER REFERENCES "cracha_users"(id), session_id INTEGER REFERENCES "cracha_sessions"(id) - ); + ) STRICT; + CREATE TABLE IF NOT EXISTS "cracha_audit" ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, timestamp TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now')), uuid BLOB NOT NULL UNIQUE, attribute TEXT NOT NULL, value TEXT NOT NULL, - op BOOLEAN NOT NULL - ); + op INT NOT NULL CHECK(op IN (0, 1)) + ) STRICT; -- read: |