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

---
 src/cracha.go | 35 ++++++++++++++++++++++-------------
 1 file changed, 22 insertions(+), 13 deletions(-)

(limited to 'src/cracha.go')

diff --git a/src/cracha.go b/src/cracha.go
index 5547d6e..e9ef266 100644
--- a/src/cracha.go
+++ b/src/cracha.go
@@ -177,14 +177,16 @@ func createTablesSQL(prefix string) queryT {
 			email        TEXT    NOT NULL UNIQUE,
 			salt         BLOB    NOT NULL UNIQUE,
 			pwhash       BLOB    NOT NULL
-		);
+		) STRICT;
+
 		CREATE TABLE IF NOT EXISTS "%s_confirmation_attempts" (
 			id           INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 			timestamp    TEXT    NOT NULL DEFAULT (%s),
 			-- uuid         BLOB    NOT NULL UNIQUE,
 			user_id      INTEGER NOT NULL REFERENCES "%s_users"(id),
 			token        TEXT    NOT NULL UNIQUE
-		);
+		) STRICT;
+
 		CREATE TABLE IF NOT EXISTS "%s_user_confirmations" (
 			id           INTEGER PRIMARY KEY AUTOINCREMENT,
 			timestamp    TEXT    NOT NULL DEFAULT (%s),
@@ -192,34 +194,39 @@ func createTablesSQL(prefix string) queryT {
 				REFERENCES "%s_users"(id) UNIQUE,
 			attempt_id   INTEGER NOT NULL
 				REFERENCES "%s_confirmation_attempts"(id) UNIQUE
-		);
+		) STRICT;
+
 		CREATE TABLE IF NOT EXISTS "%s_user_changes" (
 			id           INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 			timestamp    TEXT    NOT NULL DEFAULT (%s),
 			user_id      INTEGER NOT NULL REFERENCES "%s_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 "%s_tokens" (
 			-- id        INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 			-- timestamp    TEXT    NOT NULL DEFAULT (%s),
 			-- uuid         BLOB    NOT NULL UNIQUE,
 			-- type         TEXT    NOT NULL
-		-- );
+		-- ) STRICT;
+
 		CREATE TABLE IF NOT EXISTS "%s_roles" (
 			id           INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 			user_id      INTEGER NOT NULL REFERENCES "%s_users"(id),
 			role         TEXT    NOT NULL,
 			UNIQUE (user_id, role)
-		);
+		) STRICT;
+
 		CREATE TABLE IF NOT EXISTS "%s_role_changes" (
 			id           INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 			timestamp    TEXT    NOT NULL DEFAULT (%s),
 			user_id      INTEGER NOT NULL REFERENCES "%s_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 "%s_sessions" (
 			id           INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 			timestamp    TEXT    NOT NULL DEFAULT (%s),
@@ -229,21 +236,23 @@ func createTablesSQL(prefix string) queryT {
 			-- revoked_at   TEXT,
 			-- revoker_id   INTEGER          REFERENCES "%s_users"(id),
 			-- FIXME: add provenance: login, refresh, confirmation, etc.
-		);
+		) STRICT;
+
 		CREATE TABLE IF NOT EXISTS "%s_attempts" (
 			id           INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 			timestamp    TEXT    NOT NULL DEFAULT (%s),
 			user_id      INTEGER          REFERENCES "%s_users"(id),
 			session_id   INTEGER          REFERENCES "%s_sessions"(id)
-		);
+		) STRICT;
+
 		CREATE TABLE IF NOT EXISTS "%s_audit" (
 			id           INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 			timestamp    TEXT    NOT NULL DEFAULT (%s),
 			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;
 	`
 	return queryT{
 		write: fmt.Sprintf(
-- 
cgit v1.2.3