From 0dfcbe88cd70e78b20370197b8250d296913067b Mon Sep 17 00:00:00 2001
From: EuAndreh <eu@euandre.org>
Date: Mon, 21 Oct 2024 20:26:38 -0300
Subject: Start implementing first queries and its tests

---
 tests/queries.sql | 205 ++++++++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 205 insertions(+)

(limited to 'tests/queries.sql')

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:
-- 
cgit v1.2.3

