1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
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 = ?`,
},
}
|
