From 66d6afb01c76aec07f05787425feaaf1a23d2c5b Mon Sep 17 00:00:00 2001
From: EuAndreh <eu@euandre.org>
Date: Thu, 31 Oct 2024 15:52:10 -0300
Subject: Use STRICT in CREATE TABLE statmements

---
 tests/queries.sql | 35 ++++++++++++++++++++++-------------
 1 file changed, 22 insertions(+), 13 deletions(-)

(limited to 'tests/queries.sql')

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

