timekeeper

commit b0885e50e93ccb08c91580826b5b47ab2b149742
parent 8d67ca0b2285747b5a691a496455c6a7e620b964
Author: Jacob R. Edwards <jacob@jacobedwards.org>
Date:   Sun,  3 Mar 2024 09:35:22 -0800

Use triggers and foreign keys in SQL database

This should help prevent errors with keeping the data updated.

Diffstat:
M
timekeeper.c
 | 
37
++++++++++++++++++++++---------------

1 file changed, 22 insertions(+), 15 deletions(-)
diff --git a/timekeeper.c b/timekeeper.c
@@ -50,10 +50,12 @@ enum Page {
 };
 
 enum StmtID {
-	StmtInitUsers,
-	StmtInitUserdata,
+	StmtInit0,
+	StmtInit1,
+	StmtInit2,
+	StmtInit3,
+	StmtInitLast,
 	StmtAddUser,
-	StmtAddUserdata,
 	StmtGetUserByHash,
 	StmtGetUserByName,
 	StmtDeleteUser,
@@ -116,9 +118,11 @@ enum LoginStatus {
 int
 initdb(struct pagedata *pd)
 {
-	if (sqlbox_exec(pd->db, pd->dbid, StmtInitUsers, 0, NULL, 0) ||
-	    sqlbox_exec(pd->db, pd->dbid, StmtInitUserdata, 0, NULL, 0))
-		return 1;
+	enum StmtID i;
+
+	for (i = StmtInit0; i <= StmtInitLast; ++i)
+		if (sqlbox_exec(pd->db, pd->dbid, i, 0, NULL, 0) != SQLBOX_CODE_OK)
+			return 1;
 	return 0;
 }
 
@@ -137,8 +141,7 @@ adduser(struct pagedata *pd, char *name, char *key)
 		{ .sparm = name, .type = SQLBOX_PARM_STRING }
 	};
 
-	if (sqlbox_exec(pd->db, pd->dbid, StmtAddUser, Len(p), p, 0) != SQLBOX_CODE_OK ||
-	    sqlbox_exec(pd->db, pd->dbid, StmtAddUserdata, 1, p, 0) != SQLBOX_CODE_OK)
+	if (sqlbox_exec(pd->db, pd->dbid, StmtAddUser, Len(p), p, 0) != SQLBOX_CODE_OK)
 		return 1;
 	return 0;
 }
@@ -661,13 +664,17 @@ main(void)
 		{ .fname = "/tmp/timekeeper.db", .mode = SQLBOX_SRC_RWC }
 	};
 	struct sqlbox_pstmt pstmts[] = {
-		[StmtInitUsers] = { .stmt = "CREATE TABLE IF NOT EXISTS users (hash TEXT PRIMARY KEY, name TEXT UNIQUE)" },
-		[StmtAddUser] = { .stmt = "INSERT INTO users (hash, name) VALUES (?, ?)" },
-		[StmtGetUserByName] = { .stmt = "SELECT * FROM users WHERE name IS ?" },
-		[StmtGetUserByHash] = { .stmt = "SELECT * FROM users WHERE hash IS ?" },
-		[StmtDeleteUser] = { .stmt = "DELETE FROM users WHERE hash IS ?" },
-		[StmtInitUserdata] = { .stmt = "CREATE TABLE IF NOT EXISTS userdata (hash TEXT PRIMARY KEY, start INTEGER)" },
-		[StmtAddUserdata] = { .stmt = "INSERT INTO userdata (hash) VALUES (?)" },
+		[StmtInit0] = { .stmt = "PRAGMA foreign_keys = ON" },
+		[StmtInit1] = { .stmt = "CREATE TABLE IF NOT EXISTS auth (hash TEXT PRIMARY KEY, name TEXT UNIQUE)" },
+		[StmtInit2] = { .stmt = "CREATE TABLE IF NOT EXISTS userdata (id TEXT PRIMARY KEY REFERENCES auth (hash), start INTEGER)" },
+		[StmtInit3] = { .stmt = "CREATE TRIGGER IF NOT EXISTS userdatahash AFTER INSERT ON auth FOR EACH ROW BEGIN\n"
+			"INSERT INTO userdata (id) VALUES (NEW.hash); END" },
+		[StmtInitLast] = { .stmt = "CREATE TRIGGER IF NOT EXISTS userdelete AFTER DELETE ON auth FOR EACH ROW BEGIN\n"
+			"DELETE FROM userdata WHERE id IS OLD.hash; END" },
+		[StmtAddUser] = { .stmt = "INSERT INTO auth (hash, name) VALUES (?, ?)" },
+		[StmtGetUserByName] = { .stmt = "SELECT * FROM auth WHERE name IS ?" },
+		[StmtGetUserByHash] = { .stmt = "SELECT * FROM auth WHERE hash IS ?" },
+		[StmtDeleteUser] = { .stmt = "DELETE FROM auth WHERE hash IS ?" },
 		[StmtStartTime] = { .stmt = "UPDATE userdata SET start = ? WHERE hash IS ?" },
 		[StmtGetStartTime] = { .stmt = "SELECT start FROM userdata WHERE hash IS ?" }
 	};