diff options
Diffstat (limited to 'tests/queries.sql')
| -rw-r--r-- | tests/queries.sql | 205 |
1 files changed, 205 insertions, 0 deletions
diff --git a/tests/queries.sql b/tests/queries.sql index e69de29..c44a6c5 100644 --- a/tests/queries.sql +++ b/tests/queries.sql @@ -0,0 +1,205 @@ + +-- createTables.sql: +-- write: + CREATE TABLE IF NOT EXISTS "gracha_users" ( + 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, + email TEXT NOT NULL UNIQUE, + salt BLOB NOT NULL UNIQUE, + pwhash BLOB NOT NULL, + metadata TEXT + ); + CREATE TABLE IF NOT EXISTS "gracha_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 "gracha_users"(id), + token TEXT NOT NULL UNIQUE + ); + CREATE TABLE IF NOT EXISTS "gracha_user_confirmations" ( + id INTEGER PRIMARY KEY AUTOINCREMENT, + timestamp TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now')), + user_id INTEGER NOT NULL + REFERENCES "gracha_users"(id) UNIQUE, + attempt_id INTEGER NOT NULL + REFERENCES "gracha_confirmation_attempts"(id) UNIQUE + ); + CREATE TABLE IF NOT EXISTS "gracha_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 "gracha_users"(id), + attribute TEXT NOT NULL, + value TEXT NOT NULL, + op BOOLEAN NOT NULL + ); + -- CREATE TABLE IF NOT EXISTS "gracha_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 + -- ); + CREATE TABLE IF NOT EXISTS "gracha_roles" ( + id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, + user_id INTEGER NOT NULL REFERENCES "gracha_users"(id), + role TEXT NOT NULL, + metadata TEXT, + UNIQUE (user_id, role) + ); + CREATE TABLE IF NOT EXISTS "gracha_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 "gracha_roles"(id), + role TEXT NOT NULL, + op BOOLEAN NOT NULL + ); + CREATE TABLE IF NOT EXISTS "gracha_sessions" ( + 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 "gracha_users"(id), + -- type TEXT NOT NULL, + -- revoked_at TEXT, + -- revoker_id INTEGER REFERENCES "gracha_users"(id), + -- FIXME: add provenance: login, refresh, confirmation, etc. + metadata TEXT + ); + CREATE TABLE IF NOT EXISTS "gracha_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 "gracha_users"(id), + session_id INTEGER REFERENCES "gracha_sessions"(id), + metadata TEXT + ); + CREATE TABLE IF NOT EXISTS "gracha_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, + metadata TEXT + ); + + +-- read: + +-- byEmail.sql: +-- write: + +-- read: + SELECT id, timestamp, uuid, salt, pwhash, metadata, ( + CASE WHEN EXISTS ( + SELECT id FROM "gracha_user_confirmations" + WHERE user_id = ( + SELECT id FROM "gracha_users" + WHERE email = ? + ) + ) THEN 1 + ELSE 0 + END + ) as confirmed + FROM "gracha_users" WHERE email = ?; + + +-- register.sql: +-- write: + INSERT INTO "gracha_users" (uuid, email, salt, pwhash) + VALUES (?, ?, ?, ?) RETURNING id, timestamp; + + +-- read: + SELECT id, timestamp from "gracha_users" + WHERE uuid = ?; + + +-- sendToken.sql: +-- write: + INSERT INTO "gracha_confirmation_attempts" (user_id, token) + VALUES ( + (SELECT id FROM "gracha_users" WHERE uuid = ?), + ? + ) + + +-- read: + +-- confirm.sql: +-- write: + INSERT INTO "gracha_user_confirmations" (user_id, attempt_id) + VALUES (?, ?); + + +-- read: + SELECT + "gracha_confirmation_attempts".id, + "gracha_confirmation_attempts".user_id, + "gracha_users".uuid + FROM "gracha_confirmation_attempts" + JOIN "gracha_users" ON + "gracha_confirmation_attempts".user_id = "gracha_users".id + WHERE token = ?; + + +-- login.sql: +-- write: + +-- read: + +-- refresh.sql: +-- write: + INSERT INTO "gracha_sessions" (uuid, user_id) + VALUES ( + ?, + (SELECT user_id FROM "gracha_sessions" WHERE uuid = ?) + ) RETURNING id, timestamp, ( + SELECT "gracha_users".uuid FROM "gracha_users" + JOIN "gracha_sessions" ON + "gracha_users".id = "gracha_sessions".user_id + WHERE "gracha_sessions".uuid = ? + ) AS userID; + + +-- read: + +-- reset.sql: +-- write: + -- INSERT SOMETHING gracha + + +-- read: + +-- change.sql: +-- write: + -- INSERT SOMETHING gracha + + +-- read: + +-- byUUID.sql: +-- write: + +-- read: + -- INSERT SOMETHING gracha + + +-- logout.sql: +-- write: + -- INSERT SOMETHING gracha + + +-- read: + +-- outOthers.sql: +-- write: + -- INSERT SOMETHING gracha + + +-- read: + +-- outAll.sql: +-- write: + -- INSERT SOMETHING gracha + + +-- read: |