1 files changed, 108 insertions, 0 deletions
diff --git a/internal/db/transaction_definitions.go b/internal/db/transaction_definitions.go
new file mode 100644
index 0000000..cdfa433
--- /dev/null
+++ b/internal/db/transaction_definitions.go
@@ -0,0 +1,108 @@
+package db
+
+type transactionName int
+
+const (
+	CreateUser transactionName = iota
+	CreateUserSecret
+	CreateTag
+	CreateRole
+	RemoveUser
+	RemoveRole
+	RemoveUnusedTags
+	AssignRoleToUser
+	AssignTagToTask
+	RemoveRoleFromUser
+	RemoveTagFromTask
+	UpdateSetting
+	GetSingleUser
+	GetAllUsers
+	GetSingleTask
+	GetAllTasks
+	GetSingleUserWithSecretAndRoles
+	GetAllTagsRelatedToTask
+)
+
+var commonTransactions = [...]struct {
+	Name transactionName
+	Cmd  string
+}{
+	{ // Create a user (including salted secret)
+		Name: CreateUser,
+		Cmd:  "INSERT INTO Users (userID, name, email) VALUES (?, ?, ?)",
+	},
+	{ // Create user secrets
+		Name: CreateUserSecret,
+		Cmd:  "INSERT INTO UserSecrets (userID, saltAndHash) VALUES (?, ?)",
+	},
+	{ // Create a tag
+		Name: CreateTag,
+		Cmd:  "INSERT INTO Tags (name) VALUES (?)",
+	},
+	{ // Create a role
+		Name: CreateRole,
+		Cmd:  "INSERT INTO Roles (role) VALUES (?)",
+	},
+	{ // Remove a user
+		Name: RemoveUser,
+		Cmd:  "DELETE FROM Users WHERE userID = ?",
+	},
+	{ // Remove a role
+		Name: RemoveRole,
+		Cmd:  "DELETE FROM Roles WHERE role = ?",
+	},
+	{ // Remove unused tags (assigned to no tasks)
+		Name: RemoveUnusedTags,
+		Cmd:  "DELETE FROM Tags WHERE tagID NOT IN (SELECT tagID FROM TaskTags)",
+	},
+	{ // Assign a new role to a user
+		Name: AssignRoleToUser,
+		Cmd:  "INSERT INTO UserRoles (userID, role) VALUES (?, ?)",
+	},
+	{ // Assign a new tag to a task
+		Name: AssignTagToTask,
+		Cmd:  "INSERT INTO TaskTags (taskID, tagID) VALUES (?, ?)",
+	},
+	{ // Remove a role from a user
+		Name: RemoveRoleFromUser,
+		Cmd:  "DELETE FROM UserRoles WHERE userID = ? AND role = ?",
+	},
+	{ // Remove a tag from a task
+		Name: RemoveTagFromTask,
+		Cmd:  "DELETE FROM TaskTags WHERE taskID = ? AND tagID = ?",
+	},
+	{ // Update a setting KeyPair
+		Name: UpdateSetting,
+		Cmd:  "UPDATE Settings SET value = ? WHERE key = ?",
+	},
+	{ // Get a single user
+		Name: GetSingleUser,
+		Cmd:  "SELECT * FROM Users WHERE userID = ?",
+	},
+	{ // Get all users
+		Name: GetAllUsers,
+		Cmd:  "SELECT * FROM Users",
+	},
+	{ // Get a single task
+		Name: GetSingleTask,
+		Cmd:  "SELECT * FROM Tasks WHERE taskID = ?",
+	},
+	{ // Get all tasks
+		Name: GetAllTasks,
+		Cmd:  "SELECT * FROM Tasks",
+	},
+	{ // Get a single user with secret info and roles
+		Name: GetSingleUserWithSecretAndRoles,
+		Cmd: `SELECT u.*, us.saltAndHash, ur.role
+				FROM Users u
+				JOIN UserSecrets us ON u.userID = us.userID
+				LEFT JOIN UserRoles ur ON u.userID = ur.userID
+				WHERE u.userID = ?`,
+	},
+	{ // Get all tags related to a task
+		Name: GetAllTagsRelatedToTask,
+		Cmd: `SELECT t.* FROM Tags t
+				JOIN TaskTags tt ON t.tagID = tt.tagID
+				WHERE tt.taskID = ?`,
+	},
+}