timekeeper

commit 5e8b95ab652d56417925052d06f9ba4df579bfae
parent ce568ee3b4bbbf1ef07ea1ac2d5f005fa2a46295
Author: Jacob R. Edwards <jacob@jacobedwards.org>
Date:   Thu, 14 Mar 2024 11:53:28 -0700

Improve readability on SQL statements in stmt.c

Instead of only breaking lines when they get way too long, actually
write them how I might if they were in a file and going to be
directly executed by sqlite3(1) or the like.

I considered actually reading statements from files during program
execution, but I figured that might not be the safest idea.  Another
option would be to make a pre-processer which escapes it's input
for C, but I'll decide on the best method later.

Diffstat:
M
stmt.c
 | 
65
++++++++++++++++++++++++++++++++++++++++++++++++-----------------

1 file changed, 48 insertions(+), 17 deletions(-)
diff --git a/stmt.c b/stmt.c
@@ -6,25 +6,56 @@
 
 struct sqlbox_pstmt pstmts[] = {
 	[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), times BLOB DEFAULT '[]')" },
-	[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 (?, ?)" },
+	[StmtInit1] = { .stmt =
+		"CREATE TABLE IF NOT EXISTS auth (\n"
+		"	hash TEXT PRIMARY KEY,\n"
+		"	name TEXT UNIQUE\n"
+		")"
+	},
+	[StmtInit2] = { .stmt =
+		"CREATE TABLE IF NOT EXISTS userdata (\n"
+		"	id TEXT PRIMARY KEY REFERENCES auth (hash),\n"
+		"	times BLOB DEFAULT '[]'\n"
+		")"
+	},
+	[StmtInit3] = { .stmt = "CREATE TRIGGER IF NOT EXISTS userdatahash\n"
+		"	AFTER INSERT ON auth FOR EACH ROW BEGIN"
+		"	INSERT INTO userdata (id) VALUES (NEW.hash);\n"
+		"END"
+	},
+	[StmtInitLast] = { .stmt =
+		"CREATE TRIGGER IF NOT EXISTS userdelete\n"
+		"	AFTER DELETE ON auth FOR EACH ROW BEGIN\n"
+		"	DELETE FROM userdata WHERE id IS OLD.hash;\n
+		"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 ?" },
-	[StmtNewTimeRow] = { .stmt = "UPDATE userdata SET times =\n"
-		"json_insert(times, '$[#]', json_array(null, null, null, null)) WHERE id IS ? AND\n"
-		"(json_array_length(times) = 0 OR json_extract(times, '$[#-1][3]') IS NOT null)" },
-                /* Currently, the user can set the end time with a
-                 * break open and the break won't be ended. This is
-                 * intentional, but also it should set the break end
-                 * time to the end time in that case.
+	[StmtNewTimeRow] = { .stmt =
+		"UPDATE userdata SET times =\n"
+		"	json_insert(times, '$[#]', json_array(null, null, null, null))\n"
+		"	WHERE id IS ? AND (json_array_length(times) = 0 OR\n"
+		"	    json_extract(times, '$[#-1][3]') IS NOT null)"
+	},
+
+	/* Currently, the user can set the end time with a
+	 * break open and the break won't be ended. This is
+	 * intentional, but also it should set the break end
+	 * time to the end time in that case.
 	 */
-	[StmtSetTime] = { .stmt = "UPDATE userdata SET times =\n"
-		"json_replace(times, '$[#-1][' || ? || ']', ?) WHERE id IS ? AND json_extract(times, '$[#-1][' || ? || ']') IS null" },
-	[StmtGetTimes] = { .stmt = "SELECT value FROM userdata, json_tree(times) WHERE userdata.id IS ? AND type IS NOT 'array'" }
+	[StmtSetTime] = { .stmt =
+		"UPDATE userdata SET times =\n"
+		"	json_replace(times, '$[#-1][' || ? || ']', ?)\n"
+		"	WHERE id IS ? AND\n"
+		"	    json_extract(times, '$[#-1][' || ? || ']') IS null"
+	},
+
+	[StmtGetTimes] = { .stmt =
+		"SELECT value FROM userdata, json_tree(times)\n"
+		"	WHERE userdata.id IS ? AND type IS NOT 'array'"
+	}
 };