summaryrefslogtreecommitdiff
path: root/tests/queries.sql
diff options
context:
space:
mode:
Diffstat (limited to 'tests/queries.sql')
-rw-r--r--tests/queries.sql92
1 files changed, 46 insertions, 46 deletions
diff --git a/tests/queries.sql b/tests/queries.sql
index 7e20ae9..6e68db1 100644
--- a/tests/queries.sql
+++ b/tests/queries.sql
@@ -1,7 +1,7 @@
-- createTables.sql:
-- write:
- CREATE TABLE IF NOT EXISTS "gracha_users" (
+ CREATE TABLE IF NOT EXISTS "cracha_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,
@@ -9,65 +9,65 @@
salt BLOB NOT NULL UNIQUE,
pwhash BLOB NOT NULL
);
- CREATE TABLE IF NOT EXISTS "gracha_confirmation_attempts" (
+ 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 "gracha_users"(id),
+ user_id INTEGER NOT NULL REFERENCES "cracha_users"(id),
token TEXT NOT NULL UNIQUE
);
- CREATE TABLE IF NOT EXISTS "gracha_user_confirmations" (
+ 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')),
user_id INTEGER NOT NULL
- REFERENCES "gracha_users"(id) UNIQUE,
+ REFERENCES "cracha_users"(id) UNIQUE,
attempt_id INTEGER NOT NULL
- REFERENCES "gracha_confirmation_attempts"(id) UNIQUE
+ REFERENCES "cracha_confirmation_attempts"(id) UNIQUE
);
- CREATE TABLE IF NOT EXISTS "gracha_user_changes" (
+ 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 "gracha_users"(id),
+ user_id INTEGER NOT NULL REFERENCES "cracha_users"(id),
attribute TEXT NOT NULL,
value TEXT NOT NULL,
op BOOLEAN NOT NULL
);
- -- CREATE TABLE IF NOT EXISTS "gracha_tokens" (
+ -- 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
-- );
- CREATE TABLE IF NOT EXISTS "gracha_roles" (
+ CREATE TABLE IF NOT EXISTS "cracha_roles" (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
- user_id INTEGER NOT NULL REFERENCES "gracha_users"(id),
+ user_id INTEGER NOT NULL REFERENCES "cracha_users"(id),
role TEXT NOT NULL,
UNIQUE (user_id, role)
);
- CREATE TABLE IF NOT EXISTS "gracha_role_changes" (
+ 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 "gracha_roles"(id),
+ user_id INTEGER NOT NULL REFERENCES "cracha_roles"(id),
role TEXT NOT NULL,
op BOOLEAN NOT NULL
);
- CREATE TABLE IF NOT EXISTS "gracha_sessions" (
+ 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')),
uuid BLOB NOT NULL UNIQUE,
- user_id INTEGER NOT NULL REFERENCES "gracha_users"(id)
+ user_id INTEGER NOT NULL REFERENCES "cracha_users"(id)
-- type TEXT NOT NULL,
-- revoked_at TEXT,
- -- revoker_id INTEGER REFERENCES "gracha_users"(id),
+ -- revoker_id INTEGER REFERENCES "cracha_users"(id),
-- FIXME: add provenance: login, refresh, confirmation, etc.
);
- CREATE TABLE IF NOT EXISTS "gracha_attempts" (
+ 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 "gracha_users"(id),
- session_id INTEGER REFERENCES "gracha_sessions"(id)
+ user_id INTEGER REFERENCES "cracha_users"(id),
+ session_id INTEGER REFERENCES "cracha_sessions"(id)
);
- CREATE TABLE IF NOT EXISTS "gracha_audit" (
+ 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,
@@ -85,34 +85,34 @@
-- read:
SELECT id, timestamp, uuid, salt, pwhash, (
CASE WHEN EXISTS (
- SELECT id FROM "gracha_user_confirmations"
+ SELECT id FROM "cracha_user_confirmations"
WHERE user_id = (
- SELECT id FROM "gracha_users"
+ SELECT id FROM "cracha_users"
WHERE email = ?
)
) THEN 1
ELSE 0
END
) as confirmed
- FROM "gracha_users" WHERE email = ?;
+ FROM "cracha_users" WHERE email = ?;
-- register.sql:
-- write:
- INSERT INTO "gracha_users" (uuid, email, salt, pwhash)
+ INSERT INTO "cracha_users" (uuid, email, salt, pwhash)
VALUES (?, ?, ?, ?) RETURNING id, timestamp;
-- read:
- SELECT id, timestamp from "gracha_users"
+ SELECT id, timestamp from "cracha_users"
WHERE uuid = ?;
-- sendToken.sql:
-- write:
- INSERT INTO "gracha_confirmation_attempts" (user_id, token)
+ INSERT INTO "cracha_confirmation_attempts" (user_id, token)
VALUES (
- (SELECT id FROM "gracha_users" WHERE uuid = ?),
+ (SELECT id FROM "cracha_users" WHERE uuid = ?),
?
)
@@ -121,18 +121,18 @@
-- confirm.sql:
-- write:
- INSERT INTO "gracha_user_confirmations" (user_id, attempt_id)
+ INSERT INTO "cracha_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
+ "cracha_confirmation_attempts".id,
+ "cracha_confirmation_attempts".user_id,
+ "cracha_users".uuid
+ FROM "cracha_confirmation_attempts"
+ JOIN "cracha_users" ON
+ "cracha_confirmation_attempts".user_id = "cracha_users".id
WHERE token = ?;
@@ -143,15 +143,15 @@
-- refresh.sql:
-- write:
- INSERT INTO "gracha_sessions" (uuid, user_id)
+ INSERT INTO "cracha_sessions" (uuid, user_id)
VALUES (
?,
- (SELECT user_id FROM "gracha_sessions" WHERE uuid = ?)
+ (SELECT user_id FROM "cracha_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 = ?
+ SELECT "cracha_users".uuid FROM "cracha_users"
+ JOIN "cracha_sessions" ON
+ "cracha_users".id = "cracha_sessions".user_id
+ WHERE "cracha_sessions".uuid = ?
) AS userID;
@@ -159,14 +159,14 @@
-- reset.sql:
-- write:
- -- INSERT SOMETHING gracha
+ -- INSERT SOMETHING cracha
-- read:
-- change.sql:
-- write:
- -- INSERT SOMETHING gracha
+ -- INSERT SOMETHING cracha
-- read:
@@ -175,26 +175,26 @@
-- write:
-- read:
- -- INSERT SOMETHING gracha
+ -- INSERT SOMETHING cracha
-- logout.sql:
-- write:
- -- INSERT SOMETHING gracha
+ -- INSERT SOMETHING cracha
-- read:
-- outOthers.sql:
-- write:
- -- INSERT SOMETHING gracha
+ -- INSERT SOMETHING cracha
-- read:
-- outAll.sql:
-- write:
- -- INSERT SOMETHING gracha
+ -- INSERT SOMETHING cracha
-- read:
generated by cgit v1.2.3 (git 2.49.0) at 2025-07-13 08:53:30 +0000