internal/store/store_test.go
309 lines · 10194 bytes
1package store
2
3import (
4 "os"
5 "path/filepath"
6 "strings"
7 "testing"
8)
9
10func open(t *testing.T) *Store {
11 t.Helper()
12 s, err := Open(filepath.Join(t.TempDir(), "gitbay.db"))
13 if err != nil {
14 t.Fatal(err)
15 }
16 t.Cleanup(func() { s.Close() })
17 return s
18}
19
20func TestMigrateUpDown(t *testing.T) {
21 s := open(t)
22
23 if err := s.MigrateUp(); err != nil {
24 t.Fatal(err)
25 }
26 v, err := s.Version()
27 if err != nil {
28 t.Fatal(err)
29 }
30 if v < 1 {
31 t.Fatalf("version %d after MigrateUp", v)
32 }
33
34 // Seeded settings row exists.
35 var epoch string
36 if err := s.DB.QueryRow("SELECT value FROM settings WHERE key = 'key_epoch'").Scan(&epoch); err != nil {
37 t.Fatal(err)
38 }
39 if epoch != "1" {
40 t.Fatalf("key_epoch = %q, want 1", epoch)
41 }
42
43 // Down to empty, then back up.
44 if err := s.MigrateTo(0); err != nil {
45 t.Fatal(err)
46 }
47 var n int
48 if err := s.DB.QueryRow("SELECT count(*) FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'").Scan(&n); err != nil {
49 t.Fatal(err)
50 }
51 if n != 0 {
52 t.Fatalf("%d tables remain after down-migration to 0", n)
53 }
54 if err := s.MigrateUp(); err != nil {
55 t.Fatal(err)
56 }
57 // Idempotent at latest.
58 if err := s.MigrateUp(); err != nil {
59 t.Fatal(err)
60 }
61}
62
63func TestKeyFingerprintGloballyUnique(t *testing.T) {
64 s := open(t)
65 if err := s.MigrateUp(); err != nil {
66 t.Fatal(err)
67 }
68 mustExec := func(q string, args ...any) {
69 t.Helper()
70 if _, err := s.DB.Exec(q, args...); err != nil {
71 t.Fatal(err)
72 }
73 }
74 mustExec("INSERT INTO users (username) VALUES ('alice'), ('bob')")
75 mustExec("INSERT INTO ssh_keys (user_id, fingerprint, algo, blob) VALUES (1, 'SHA256:aaa', 'ed25519', x'00')")
76
77 // Same fingerprint on a different account must be rejected.
78 _, err := s.DB.Exec("INSERT INTO ssh_keys (user_id, fingerprint, algo, blob) VALUES (2, 'SHA256:aaa', 'ed25519', x'00')")
79 if err == nil || !strings.Contains(err.Error(), "UNIQUE") {
80 t.Fatalf("duplicate ssh fingerprint across accounts: err = %v, want UNIQUE violation", err)
81 }
82
83 mustExec("INSERT INTO pgp_keys (user_id, fingerprint, armored) VALUES (1, 'FPR1', '-----')")
84 _, err = s.DB.Exec("INSERT INTO pgp_keys (user_id, fingerprint, armored) VALUES (2, 'FPR1', '-----')")
85 if err == nil || !strings.Contains(err.Error(), "UNIQUE") {
86 t.Fatalf("duplicate pgp fingerprint across accounts: err = %v, want UNIQUE violation", err)
87 }
88}
89
90func TestForeignKeysEnforced(t *testing.T) {
91 s := open(t)
92 if err := s.MigrateUp(); err != nil {
93 t.Fatal(err)
94 }
95 _, err := s.DB.Exec("INSERT INTO ssh_keys (user_id, fingerprint, algo, blob) VALUES (999, 'SHA256:zzz', 'ed25519', x'00')")
96 if err == nil {
97 t.Fatal("insert with dangling user_id succeeded; foreign keys are off")
98 }
99}
100
101// The database file carries token hashes, addresses and private repo names.
102// The directory above it is the real boundary; this is the second one.
103func TestDatabaseFileIsNotWorldReadable(t *testing.T) {
104 path := filepath.Join(t.TempDir(), "gitbay.db")
105 s, err := Open(path)
106 if err != nil {
107 t.Fatal(err)
108 }
109 defer s.Close()
110 fi, err := os.Stat(path)
111 if err != nil {
112 t.Fatal(err)
113 }
114 if mode := fi.Mode().Perm(); mode&0o007 != 0 {
115 t.Errorf("database mode %04o is other-readable", mode)
116 }
117}
118
119func TestSSHKeyLabel(t *testing.T) {
120 s := open(t)
121 if err := s.MigrateUp(); err != nil {
122 t.Fatal(err)
123 }
124 uid, err := s.CreateUser("alice", false)
125 if err != nil {
126 t.Fatal(err)
127 }
128 if err := s.AddSSHKey(uid, "SHA256:aaa", "ssh-ed25519", []byte{0}, "full", "laptop"); err != nil {
129 t.Fatal(err)
130 }
131 keys, err := s.ListSSHKeys(uid)
132 if err != nil || len(keys) != 1 || keys[0].Label != "laptop" {
133 t.Fatalf("ListSSHKeys = %+v, %v; want one key labelled laptop", keys, err)
134 }
135 if err := s.SetSSHKeyLabel(uid, "SHA256:aaa", "desk"); err != nil {
136 t.Fatal(err)
137 }
138 k, err := s.SSHKeyByFingerprint("SHA256:aaa")
139 if err != nil || k.Label != "desk" {
140 t.Fatalf("SSHKeyByFingerprint after relabel: %+v, %v", k, err)
141 }
142 // Only the owner may relabel; someone else's fingerprint is not found.
143 if err := s.SetSSHKeyLabel(uid+1, "SHA256:aaa", "x"); err != ErrNotFound {
144 t.Fatalf("relabel by another user: %v, want ErrNotFound", err)
145 }
146}
147
148// Migration 0052 rebuilds labels and milestones with an org scope. Foreign
149// keys are off for the migration: rebuilding a parent table with children
150// (issue_labels, issues.milestone_id) otherwise loses the children's rows.
151// legacy_alter_table keeps the children naming labels and milestones
152// through the rename, so they bind to the new tables rather than to
153// labels_old/milestones_old. foreign_key_check afterwards proves the ids
154// line up. This checks the ids, the memberships and the foreign keys all
155// survive.
156func TestMigration0052KeepsMembershipsAndForeignKeys(t *testing.T) {
157 s := open(t)
158 if err := s.MigrateTo(51); err != nil {
159 t.Fatal(err)
160 }
161 uid, err := s.CreateUser("alice", false)
162 if err != nil {
163 t.Fatal(err)
164 }
165 rid, err := s.CreateRepo("user", uid, "app", "public")
166 if err != nil {
167 t.Fatal(err)
168 }
169 number, err := s.CreateIssue(rid, uid, "one", "", "md")
170 if err != nil {
171 t.Fatal(err)
172 }
173 // CreateIssue returns the per-repo number; the rows below reference
174 // the issues.id row.
175 issue, err := s.IssueByNumber(rid, number)
176 if err != nil {
177 t.Fatal(err)
178 }
179 iid := issue.ID
180 if _, err := s.DB.Exec("INSERT INTO labels (repo_id, name, color) VALUES (?, 'bug', '#ff0000')", rid); err != nil {
181 t.Fatal(err)
182 }
183 if _, err := s.DB.Exec("INSERT INTO issue_labels (issue_id, label_id) SELECT ?, id FROM labels WHERE name = 'bug'", iid); err != nil {
184 t.Fatal(err)
185 }
186 if _, err := s.DB.Exec("INSERT INTO milestones (repo_id, title) VALUES (?, 'v1')", rid); err != nil {
187 t.Fatal(err)
188 }
189 if _, err := s.DB.Exec("UPDATE issues SET milestone_id = (SELECT id FROM milestones WHERE title = 'v1') WHERE id = ?", iid); err != nil {
190 t.Fatal(err)
191 }
192 if err := s.MigrateTo(52); err != nil {
193 t.Fatal(err)
194 }
195 var n int
196 if err := s.DB.QueryRow(`SELECT COUNT(*) FROM issue_labels il JOIN labels l ON l.id = il.label_id
197 WHERE il.issue_id = ? AND l.name = 'bug' AND l.repo_id = ? AND l.org_id IS NULL`, iid, rid).Scan(&n); err != nil || n != 1 {
198 t.Fatalf("label membership after 0052: %d, %v", n, err)
199 }
200 if err := s.DB.QueryRow(`SELECT COUNT(*) FROM issues i JOIN milestones m ON m.id = i.milestone_id
201 WHERE i.id = ? AND m.title = 'v1' AND m.repo_id = ?`, iid, rid).Scan(&n); err != nil || n != 1 {
202 t.Fatalf("milestone attachment after 0052: %d, %v", n, err)
203 }
204 rows, err := s.DB.Query("PRAGMA foreign_key_check")
205 if err != nil {
206 t.Fatal(err)
207 }
208 defer rows.Close()
209 if rows.Next() {
210 t.Fatal("foreign_key_check reported a violation after 0052")
211 }
212 // The scope CHECK holds: a row with neither or both scopes is refused.
213 if _, err := s.DB.Exec("INSERT INTO labels (name) VALUES ('neither')"); err == nil {
214 t.Fatal("label with no scope was accepted")
215 }
216 if _, err := s.DB.Exec("INSERT INTO labels (repo_id, org_id, name) VALUES (?, 1, 'both')", rid); err == nil {
217 t.Fatal("label with both scopes was accepted")
218 }
219 // Down refuses while an org-scoped row exists, and works once it is gone.
220 if _, err := s.DB.Exec("INSERT INTO orgs (name) VALUES ('acme')"); err != nil {
221 t.Fatal(err)
222 }
223 if _, err := s.DB.Exec("INSERT INTO labels (org_id, name) VALUES ((SELECT id FROM orgs WHERE name = 'acme'), 'org-only')"); err != nil {
224 t.Fatal(err)
225 }
226 if err := s.MigrateTo(51); err == nil {
227 t.Fatal("down migration accepted an org-scoped label")
228 }
229 if _, err := s.DB.Exec("DELETE FROM labels WHERE org_id IS NOT NULL"); err != nil {
230 t.Fatal(err)
231 }
232 if err := s.MigrateTo(51); err != nil {
233 t.Fatalf("down migration: %v", err)
234 }
235 if err := s.DB.QueryRow(`SELECT COUNT(*) FROM issue_labels il JOIN labels l ON l.id = il.label_id WHERE il.issue_id = ?`, iid).Scan(&n); err != nil || n != 1 {
236 t.Fatalf("label membership after down: %d, %v", n, err)
237 }
238}
239
240// Migrating all the way up runs 0052's "-- foreign_keys: off" step on its
241// own pinned connection and every other migration's script, which has no
242// such directive, on the pool as usual. A fresh query afterwards still
243// sees foreign keys on: the pinned connection re-enabled them before
244// returning to the pool, and no other connection was ever touched.
245func TestMigrationForeignKeysDirective(t *testing.T) {
246 s := open(t)
247 if err := s.MigrateUp(); err != nil {
248 t.Fatal(err)
249 }
250 var fk int
251 if err := s.DB.QueryRow("PRAGMA foreign_keys").Scan(&fk); err != nil {
252 t.Fatal(err)
253 }
254 if fk != 1 {
255 t.Fatalf("foreign_keys after MigrateUp: %d, want 1", fk)
256 }
257}
258
259// A migration marked "-- foreign_keys: off" must have its
260// foreign_key_check run before the transaction commits, not after —
261// otherwise a violation is reported once the bad schema and
262// user_version are already persisted (#261).
263func TestFKOffMigrationChecksBeforeCommit(t *testing.T) {
264 s := open(t)
265 if err := s.MigrateUp(); err != nil {
266 t.Fatal(err)
267 }
268 versionBefore, err := s.Version()
269 if err != nil {
270 t.Fatal(err)
271 }
272
273 // Insert a row a fkOff rebuild would have to preserve or complain
274 // about: a milestone with no matching repo_id (the deliberately
275 // impossible case a corrupt migration would produce).
276 if _, err := s.DB.Exec("PRAGMA foreign_keys = OFF"); err != nil {
277 t.Fatal(err)
278 }
279 if _, err := s.DB.Exec(
280 "INSERT INTO milestones (repo_id, title, state, created_at) VALUES (99999, 'orphan', 'open', datetime('now'))"); err != nil {
281 t.Fatal(err)
282 }
283 if _, err := s.DB.Exec("PRAGMA foreign_keys = ON"); err != nil {
284 t.Fatal(err)
285 }
286
287 // A no-op fkOff step (rewriting milestones to itself) must now
288 // refuse — before it commits, not after — because the orphan row
289 // fails foreign_key_check.
290 err = s.migrateStep(
291 "UPDATE sqlite_master SET name = name WHERE 0", versionBefore+1, true)
292 if err == nil {
293 t.Fatal("expected foreign_key_check to refuse the orphaned row")
294 }
295 after, err := s.Version()
296 if err != nil {
297 t.Fatal(err)
298 }
299 if after != versionBefore {
300 t.Fatalf("user_version changed to %d despite the refused check (should stay %d)", after, versionBefore)
301 }
302 var fk int
303 if err := s.DB.QueryRow("PRAGMA foreign_keys").Scan(&fk); err != nil {
304 t.Fatal(err)
305 }
306 if fk != 1 {
307 t.Fatalf("foreign_keys after refused fkOff step: %d, want 1", fk)
308 }
309}