BlueGit

timfa bluegit

Files

bluegit / internal / store / store.go

store.go

store.go 34.3 KiB Download Raw
1// Package store bewaart BlueGit in één SQLite-bestand op de datadisk.
2package store
3
4import (
5 "context"
6 "crypto/rand"
7 "crypto/sha256"
8 "database/sql"
9 "encoding/hex"
10 "encoding/json"
11 "errors"
12 "fmt"
13 "strings"
14 "time"
15
16 "gitlab.com/Timfa/bluegit/internal/i18n"
17 "gitlab.com/Timfa/bluegit/internal/names"
18 "gitlab.com/Timfa/bluegit/internal/secretbox"
19
20 _ "modernc.org/sqlite"
21)
22
23// Store is de metadata van deze installatie.
24type Store struct {
25 db *sql.DB
26 key []byte
27}
28
29// Open opent of maakt het databasebestand. key is 32 bytes voor geheimen.
30func Open(path string, key []byte) (*Store, error) {
31 if len(key) != 32 {
32 return nil, fmt.Errorf("geheime sleutel moet 32 bytes zijn")
33 }
34 dsn := "file:" + path + "?_pragma=busy_timeout(5000)&_pragma=foreign_keys(1)"
35 db, err := sql.Open("sqlite", dsn)
36 if err != nil {
37 return nil, err
38 }
39 db.SetMaxOpenConns(8)
40 s := &Store{db: db, key: key}
41 if _, err := db.Exec(`PRAGMA journal_mode=WAL; PRAGMA foreign_keys=ON;`); err != nil {
42 _ = db.Close()
43 return nil, err
44 }
45 if err := s.migrate(); err != nil {
46 _ = db.Close()
47 return nil, err
48 }
49 return s, nil
50}
51
52// Close sluit de database.
53func (s *Store) Close() error { return s.db.Close() }
54
55func (s *Store) migrate() error {
56 if _, err := s.db.Exec(schema); err != nil {
57 return err
58 }
59 alters := []string{
60 `ALTER TABLE users ADD COLUMN language TEXT NOT NULL DEFAULT 'en'`,
61 `ALTER TABLE groups ADD COLUMN visibility TEXT NOT NULL DEFAULT 'private'`,
62 `ALTER TABLE groups ADD COLUMN default_role TEXT NOT NULL DEFAULT 'developer'`,
63 `ALTER TABLE groups ADD COLUMN logo_mime TEXT NOT NULL DEFAULT ''`,
64 `ALTER TABLE repos ADD COLUMN logo_mime TEXT NOT NULL DEFAULT ''`,
65 `ALTER TABLE repos ADD COLUMN release_visibility TEXT NOT NULL DEFAULT 'inherit'`,
66 `ALTER TABLE repos ADD COLUMN pushed_at TEXT NOT NULL DEFAULT ''`,
67 `ALTER TABLE secrets ADD COLUMN expand INTEGER NOT NULL DEFAULT 0`,
68 `ALTER TABLE repos ADD COLUMN fork_of INTEGER NOT NULL DEFAULT 0`,
69 `ALTER TABLE webhooks ADD COLUMN method TEXT NOT NULL DEFAULT 'POST'`,
70 `ALTER TABLE notes ADD COLUMN file_path TEXT NOT NULL DEFAULT ''`,
71 `ALTER TABLE notes ADD COLUMN line_start INTEGER NOT NULL DEFAULT 0`,
72 `ALTER TABLE notes ADD COLUMN line_end INTEGER NOT NULL DEFAULT 0`,
73 `ALTER TABLE notes ADD COLUMN suggestion TEXT NOT NULL DEFAULT ''`,
74 `ALTER TABLE notes ADD COLUMN base_text TEXT NOT NULL DEFAULT ''`,
75 `ALTER TABLE pipelines ADD COLUMN source TEXT NOT NULL DEFAULT 'push'`,
76 `ALTER TABLE pipelines ADD COLUMN actor TEXT NOT NULL DEFAULT ''`,
77 `ALTER TABLE pipelines ADD COLUMN commit_title TEXT NOT NULL DEFAULT ''`,
78 `ALTER TABLE pipelines ADD COLUMN started_at TEXT`,
79 `ALTER TABLE jobs ADD COLUMN before_script TEXT NOT NULL DEFAULT ''`,
80 `ALTER TABLE jobs ADD COLUMN after_script TEXT NOT NULL DEFAULT ''`,
81 `ALTER TABLE jobs ADD COLUMN when_mode TEXT NOT NULL DEFAULT 'on_success'`,
82 `ALTER TABLE jobs ADD COLUMN timeout_seconds INTEGER NOT NULL DEFAULT 0`,
83 `ALTER TABLE jobs ADD COLUMN failure_reason TEXT NOT NULL DEFAULT ''`,
84 // Oude geannuleerde jobs: alleen een onderbreekbare job met de melding
85 // in het log is echt door een nieuwere pipeline gestopt. Vóór deze
86 // versie schreef de runner die melding ook bij handmatig annuleren.
87 `UPDATE jobs SET failure_reason = CASE WHEN interruptible = 1 AND log LIKE '%canceled because a newer pipeline started for the same ref%' THEN 'canceled_by_newer_pipeline' ELSE 'canceled_by_user' END WHERE status = 'canceled' AND failure_reason = ''`,
88 `ALTER TABLE jobs ADD COLUMN tags_json TEXT NOT NULL DEFAULT '[]'`,
89 `ALTER TABLE jobs ADD COLUMN cache_paths TEXT NOT NULL DEFAULT '[]'`,
90 `ALTER TABLE jobs ADD COLUMN cache_key TEXT NOT NULL DEFAULT ''`,
91 `ALTER TABLE jobs ADD COLUMN resource_group TEXT NOT NULL DEFAULT ''`,
92 `ALTER TABLE jobs ADD COLUMN optional_json TEXT NOT NULL DEFAULT '[]'`,
93 `ALTER TABLE jobs ADD COLUMN retry_max INTEGER NOT NULL DEFAULT 0`,
94 `ALTER TABLE jobs ADD COLUMN retry_when TEXT NOT NULL DEFAULT '[]'`,
95 `ALTER TABLE jobs ADD COLUMN attempt INTEGER NOT NULL DEFAULT 0`,
96 `ALTER TABLE jobs ADD COLUMN services_json TEXT NOT NULL DEFAULT '[]'`,
97 `ALTER TABLE jobs ADD COLUMN environment_name TEXT NOT NULL DEFAULT ''`,
98 `ALTER TABLE jobs ADD COLUMN environment_url TEXT NOT NULL DEFAULT ''`,
99 `ALTER TABLE jobs ADD COLUMN interruptible INTEGER NOT NULL DEFAULT 0`,
100 `ALTER TABLE jobs ADD COLUMN do_not_interrupt INTEGER NOT NULL DEFAULT 0`,
101 `ALTER TABLE pipelines ADD COLUMN mr_id INTEGER NOT NULL DEFAULT 0`,
102 `ALTER TABLE groups ADD COLUMN owner_id INTEGER NOT NULL DEFAULT 0`,
103 `ALTER TABLE merge_requests ADD COLUMN draft INTEGER NOT NULL DEFAULT 0`,
104 `ALTER TABLE merge_requests ADD COLUMN labels TEXT NOT NULL DEFAULT ''`,
105 `ALTER TABLE merge_requests ADD COLUMN remove_source_branch INTEGER NOT NULL DEFAULT 1`,
106 `ALTER TABLE merge_requests ADD COLUMN squash INTEGER NOT NULL DEFAULT 0`,
107 `ALTER TABLE merge_requests ADD COLUMN squash_message TEXT NOT NULL DEFAULT ''`,
108 `ALTER TABLE notes ADD COLUMN parent_id INTEGER NOT NULL DEFAULT 0`,
109 `ALTER TABLE secrets ADD COLUMN invisible INTEGER NOT NULL DEFAULT 0`,
110 `ALTER TABLE webhooks ADD COLUMN fields_json TEXT NOT NULL DEFAULT ''`,
111 `ALTER TABLE access_tokens ADD COLUMN scopes TEXT NOT NULL DEFAULT 'api'`,
112 `ALTER TABLE pipelines ADD COLUMN target_sha TEXT NOT NULL DEFAULT ''`,
113 `ALTER TABLE users ADD COLUMN display_name TEXT NOT NULL DEFAULT ''`,
114 `ALTER TABLE users ADD COLUMN bio TEXT NOT NULL DEFAULT ''`,
115 `ALTER TABLE users ADD COLUMN runner_pref TEXT NOT NULL DEFAULT 'builtin_first'`,
116 `ALTER TABLE groups ADD COLUMN builtin_first INTEGER NOT NULL DEFAULT 0`,
117 `ALTER TABLE runners ADD COLUMN paused INTEGER NOT NULL DEFAULT 0`,
118 `ALTER TABLE runners ADD COLUMN draining INTEGER NOT NULL DEFAULT 0`,
119 }
120 for _, q := range alters {
121 if _, err := s.db.Exec(q); err != nil && !strings.Contains(strings.ToLower(err.Error()), "duplicate column") {
122 return err
123 }
124 }
125 if _, err := s.db.Exec(`UPDATE groups SET owner_id = created_by WHERE owner_id = 0 AND created_by != 0`); err != nil {
126 return err
127 }
128 return nil
129}
130
131const schema = `
132CREATE TABLE IF NOT EXISTS runners (
133 id INTEGER PRIMARY KEY,
134 owner_kind TEXT NOT NULL,
135 owner_id INTEGER NOT NULL,
136 name TEXT NOT NULL,
137 token_hash TEXT NOT NULL UNIQUE,
138 created_by INTEGER NOT NULL DEFAULT 0,
139 created_at TEXT NOT NULL,
140 last_seen TEXT NOT NULL DEFAULT '',
141 version TEXT NOT NULL DEFAULT '',
142 revoked INTEGER NOT NULL DEFAULT 0
143);
144CREATE TABLE IF NOT EXISTS runner_reg_tokens (
145 id INTEGER PRIMARY KEY,
146 owner_kind TEXT NOT NULL,
147 owner_id INTEGER NOT NULL,
148 token_hash TEXT NOT NULL UNIQUE,
149 created_by INTEGER NOT NULL,
150 expires_at TEXT NOT NULL,
151 used INTEGER NOT NULL DEFAULT 0
152);
153CREATE TABLE IF NOT EXISTS group_runner_optin (
154 group_id INTEGER NOT NULL,
155 user_id INTEGER NOT NULL,
156 PRIMARY KEY (group_id, user_id)
157);
158CREATE TABLE IF NOT EXISTS job_runs (
159 job_id INTEGER PRIMARY KEY,
160 runner_id INTEGER NOT NULL DEFAULT 0,
161 runner_name TEXT NOT NULL DEFAULT '',
162 offer_tier INTEGER NOT NULL DEFAULT 0,
163 offer_at TEXT NOT NULL DEFAULT ''
164);
165CREATE TABLE IF NOT EXISTS users (
166 id INTEGER PRIMARY KEY,
167 auth_id INTEGER NOT NULL UNIQUE,
168 username TEXT NOT NULL UNIQUE,
169 avatar_url TEXT NOT NULL DEFAULT '',
170 auth_admin INTEGER NOT NULL DEFAULT 0,
171 instance_admin INTEGER NOT NULL DEFAULT 0,
172 language TEXT NOT NULL DEFAULT 'en',
173 display_name TEXT NOT NULL DEFAULT '',
174 bio TEXT NOT NULL DEFAULT '',
175 created_at TEXT NOT NULL,
176 updated_at TEXT NOT NULL
177);
178CREATE TABLE IF NOT EXISTS sessions (
179 id TEXT PRIMARY KEY,
180 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
181 access_token TEXT NOT NULL,
182 expires_at TEXT NOT NULL,
183 csrf TEXT NOT NULL,
184 created_at TEXT NOT NULL
185);
186CREATE TABLE IF NOT EXISTS access_tokens (
187 id INTEGER PRIMARY KEY,
188 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
189 name TEXT NOT NULL,
190 token_hash TEXT NOT NULL UNIQUE,
191 prefix TEXT NOT NULL,
192 created_at TEXT NOT NULL,
193 last_used_at TEXT,
194 expires_at TEXT
195);
196CREATE TABLE IF NOT EXISTS ssh_keys (
197 id INTEGER PRIMARY KEY,
198 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
199 name TEXT NOT NULL,
200 public_key TEXT NOT NULL,
201 fingerprint TEXT NOT NULL UNIQUE,
202 created_at TEXT NOT NULL
203);
204CREATE TABLE IF NOT EXISTS groups (
205 id INTEGER PRIMARY KEY,
206 name TEXT NOT NULL,
207 path TEXT NOT NULL UNIQUE,
208 description TEXT NOT NULL DEFAULT '',
209 visibility TEXT NOT NULL DEFAULT 'private',
210 default_role TEXT NOT NULL DEFAULT 'developer',
211 logo_mime TEXT NOT NULL DEFAULT '',
212 created_by INTEGER NOT NULL,
213 created_at TEXT NOT NULL
214);
215CREATE TABLE IF NOT EXISTS group_members (
216 group_id INTEGER NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
217 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
218 role TEXT NOT NULL,
219 PRIMARY KEY (group_id, user_id)
220);
221CREATE TABLE IF NOT EXISTS repos (
222 id INTEGER PRIMARY KEY,
223 owner_kind TEXT NOT NULL,
224 owner_id INTEGER NOT NULL,
225 name TEXT NOT NULL,
226 description TEXT NOT NULL DEFAULT '',
227 default_branch TEXT NOT NULL,
228 visibility TEXT NOT NULL DEFAULT 'private',
229 logo_mime TEXT NOT NULL DEFAULT '',
230 created_at TEXT NOT NULL,
231 UNIQUE(owner_kind, owner_id, name)
232);
233CREATE TABLE IF NOT EXISTS repo_members (
234 repo_id INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
235 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
236 role TEXT NOT NULL,
237 PRIMARY KEY (repo_id, user_id)
238);
239CREATE TABLE IF NOT EXISTS pending_members (
240 id INTEGER PRIMARY KEY,
241 scope_kind TEXT NOT NULL,
242 scope_id INTEGER NOT NULL,
243 username TEXT NOT NULL,
244 role TEXT NOT NULL,
245 UNIQUE(scope_kind, scope_id, username)
246);
247CREATE TABLE IF NOT EXISTS protected_branches (
248 id INTEGER PRIMARY KEY,
249 repo_id INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
250 pattern TEXT NOT NULL,
251 UNIQUE(repo_id, pattern)
252);
253CREATE TABLE IF NOT EXISTS protected_tags (
254 id INTEGER PRIMARY KEY,
255 repo_id INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
256 pattern TEXT NOT NULL,
257 UNIQUE(repo_id, pattern)
258);
259CREATE TABLE IF NOT EXISTS secrets (
260 id INTEGER PRIMARY KEY,
261 scope_kind TEXT NOT NULL,
262 scope_id INTEGER NOT NULL,
263 name TEXT NOT NULL,
264 ciphertext BLOB NOT NULL,
265 nonce BLOB NOT NULL,
266 protected INTEGER NOT NULL DEFAULT 0,
267 invisible INTEGER NOT NULL DEFAULT 0,
268 created_at TEXT NOT NULL,
269 UNIQUE(scope_kind, scope_id, name)
270);
271CREATE TABLE IF NOT EXISTS webhooks (
272 id INTEGER PRIMARY KEY,
273 scope_kind TEXT NOT NULL,
274 scope_id INTEGER NOT NULL,
275 name TEXT NOT NULL,
276 preset TEXT NOT NULL,
277 url_template TEXT NOT NULL,
278 headers_json TEXT NOT NULL,
279 body_template TEXT NOT NULL,
280 events_json TEXT NOT NULL,
281 branch_filter TEXT NOT NULL DEFAULT '',
282 active INTEGER NOT NULL DEFAULT 1,
283 method TEXT NOT NULL DEFAULT 'POST',
284 fields_json TEXT NOT NULL DEFAULT '',
285 created_at TEXT NOT NULL
286);
287CREATE TABLE IF NOT EXISTS webhook_deliveries (
288 id INTEGER PRIMARY KEY,
289 webhook_id INTEGER NOT NULL REFERENCES webhooks(id) ON DELETE CASCADE,
290 event_type TEXT NOT NULL,
291 status_code INTEGER NOT NULL DEFAULT 0,
292 error TEXT NOT NULL DEFAULT '',
293 created_at TEXT NOT NULL
294);
295CREATE TABLE IF NOT EXISTS merge_requests (
296 id INTEGER PRIMARY KEY,
297 repo_id INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
298 number INTEGER NOT NULL,
299 title TEXT NOT NULL,
300 description TEXT NOT NULL DEFAULT '',
301 source_branch TEXT NOT NULL,
302 target_branch TEXT NOT NULL,
303 author_id INTEGER NOT NULL,
304 status TEXT NOT NULL,
305 created_at TEXT NOT NULL,
306 updated_at TEXT NOT NULL,
307 draft INTEGER NOT NULL DEFAULT 0,
308 labels TEXT NOT NULL DEFAULT '',
309 remove_source_branch INTEGER NOT NULL DEFAULT 1,
310 squash INTEGER NOT NULL DEFAULT 0,
311 squash_message TEXT NOT NULL DEFAULT '',
312 UNIQUE(repo_id, number)
313);
314CREATE TABLE IF NOT EXISTS pipelines (
315 id INTEGER PRIMARY KEY,
316 repo_id INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
317 commit_sha TEXT NOT NULL,
318 ref TEXT NOT NULL,
319 status TEXT NOT NULL,
320 created_at TEXT NOT NULL,
321 finished_at TEXT,
322 source TEXT NOT NULL DEFAULT 'push',
323 actor TEXT NOT NULL DEFAULT '',
324 commit_title TEXT NOT NULL DEFAULT '',
325 started_at TEXT
326);
327CREATE TABLE IF NOT EXISTS jobs (
328 id INTEGER PRIMARY KEY,
329 pipeline_id INTEGER NOT NULL REFERENCES pipelines(id) ON DELETE CASCADE,
330 repo_id INTEGER NOT NULL,
331 name TEXT NOT NULL,
332 stage TEXT NOT NULL,
333 position INTEGER NOT NULL,
334 status TEXT NOT NULL,
335 image TEXT NOT NULL,
336 script TEXT NOT NULL,
337 needs_json TEXT NOT NULL,
338 needs_set INTEGER NOT NULL,
339 variables_json TEXT NOT NULL,
340 artifact_paths TEXT NOT NULL,
341 artifact_expire_seconds INTEGER NOT NULL DEFAULT 0,
342 allow_failure INTEGER NOT NULL DEFAULT 0,
343 log TEXT NOT NULL DEFAULT '',
344 started_at TEXT,
345 finished_at TEXT,
346 before_script TEXT NOT NULL DEFAULT '',
347 after_script TEXT NOT NULL DEFAULT '',
348 when_mode TEXT NOT NULL DEFAULT 'on_success',
349 timeout_seconds INTEGER NOT NULL DEFAULT 0,
350 failure_reason TEXT NOT NULL DEFAULT '',
351 tags_json TEXT NOT NULL DEFAULT '[]',
352 cache_paths TEXT NOT NULL DEFAULT '[]',
353 cache_key TEXT NOT NULL DEFAULT '',
354 resource_group TEXT NOT NULL DEFAULT '',
355 optional_json TEXT NOT NULL DEFAULT '[]',
356 retry_max INTEGER NOT NULL DEFAULT 0,
357 retry_when TEXT NOT NULL DEFAULT '[]',
358 attempt INTEGER NOT NULL DEFAULT 0,
359 services_json TEXT NOT NULL DEFAULT '[]',
360 environment_name TEXT NOT NULL DEFAULT '',
361 environment_url TEXT NOT NULL DEFAULT '',
362 interruptible INTEGER NOT NULL DEFAULT 0,
363 do_not_interrupt INTEGER NOT NULL DEFAULT 0,
364 UNIQUE(pipeline_id, name)
365);
366CREATE TABLE IF NOT EXISTS artifacts (
367 id INTEGER PRIMARY KEY,
368 job_id INTEGER NOT NULL REFERENCES jobs(id) ON DELETE CASCADE,
369 path TEXT NOT NULL,
370 storage_path TEXT NOT NULL,
371 size INTEGER NOT NULL,
372 expires_at TEXT
373);
374CREATE TABLE IF NOT EXISTS job_tokens (
375 job_id INTEGER PRIMARY KEY REFERENCES jobs(id) ON DELETE CASCADE,
376 token_hash TEXT NOT NULL UNIQUE,
377 created_at TEXT NOT NULL
378);
379CREATE TABLE IF NOT EXISTS releases (
380 id INTEGER PRIMARY KEY,
381 repo_id INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
382 tag TEXT NOT NULL,
383 commit_sha TEXT NOT NULL,
384 name TEXT NOT NULL,
385 body TEXT NOT NULL DEFAULT '',
386 author_id INTEGER,
387 job_id INTEGER,
388 created_at TEXT NOT NULL,
389 UNIQUE(repo_id, tag)
390);
391CREATE TABLE IF NOT EXISTS release_assets (
392 id INTEGER PRIMARY KEY,
393 release_id INTEGER NOT NULL REFERENCES releases(id) ON DELETE CASCADE,
394 name TEXT NOT NULL,
395 storage_path TEXT NOT NULL,
396 size INTEGER NOT NULL,
397 UNIQUE(release_id, name)
398);
399CREATE TABLE IF NOT EXISTS contributions (
400 id INTEGER PRIMARY KEY,
401 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
402 repo_id INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
403 kind TEXT NOT NULL,
404 ref TEXT NOT NULL,
405 occurred_at TEXT NOT NULL,
406 UNIQUE(repo_id, kind, ref)
407);
408CREATE INDEX IF NOT EXISTS contributions_repo_time ON contributions(repo_id, occurred_at);
409CREATE INDEX IF NOT EXISTS contributions_user_time ON contributions(user_id, occurred_at);
410CREATE TABLE IF NOT EXISTS contribution_sync (
411 repo_id INTEGER PRIMARY KEY REFERENCES repos(id) ON DELETE CASCADE,
412 synced_at TEXT NOT NULL
413);
414CREATE TABLE IF NOT EXISTS stars (
415 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
416 repo_id INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
417 created_at TEXT NOT NULL,
418 PRIMARY KEY (user_id, repo_id)
419);
420CREATE TABLE IF NOT EXISTS notes (
421 id INTEGER PRIMARY KEY,
422 repo_id INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
423 mr_id INTEGER NOT NULL REFERENCES merge_requests(id) ON DELETE CASCADE,
424 author_id INTEGER NOT NULL REFERENCES users(id),
425 body TEXT NOT NULL,
426 created_at TEXT NOT NULL,
427 file_path TEXT NOT NULL DEFAULT '',
428 line_start INTEGER NOT NULL DEFAULT 0,
429 line_end INTEGER NOT NULL DEFAULT 0,
430 suggestion TEXT NOT NULL DEFAULT '',
431 base_text TEXT NOT NULL DEFAULT '',
432 parent_id INTEGER NOT NULL DEFAULT 0
433);
434CREATE TABLE IF NOT EXISTS mr_events (
435 id INTEGER PRIMARY KEY,
436 repo_id INTEGER NOT NULL,
437 mr_id INTEGER NOT NULL REFERENCES merge_requests(id) ON DELETE CASCADE,
438 actor_id INTEGER NOT NULL DEFAULT 0,
439 kind TEXT NOT NULL,
440 body TEXT NOT NULL DEFAULT '',
441 created_at TEXT NOT NULL
442);
443CREATE INDEX IF NOT EXISTS mr_events_mr ON mr_events(mr_id, id);
444CREATE TABLE IF NOT EXISTS commit_pushers (
445 repo_id INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
446 sha TEXT NOT NULL,
447 user_id INTEGER NOT NULL REFERENCES users(id),
448 author_name TEXT NOT NULL,
449 author_email TEXT NOT NULL,
450 created_at TEXT NOT NULL,
451 PRIMARY KEY (repo_id, sha)
452);
453CREATE TABLE IF NOT EXISTS approval_rules (
454 scope_kind TEXT NOT NULL,
455 scope_id INTEGER NOT NULL,
456 configured INTEGER NOT NULL DEFAULT 0,
457 maintainer_required INTEGER NOT NULL DEFAULT 0,
458 required_count INTEGER NOT NULL DEFAULT 1,
459 developers_approve INTEGER NOT NULL DEFAULT 0,
460 PRIMARY KEY (scope_kind, scope_id)
461);
462CREATE TABLE IF NOT EXISTS reviews (
463 id INTEGER PRIMARY KEY,
464 repo_id INTEGER NOT NULL,
465 mr_id INTEGER NOT NULL REFERENCES merge_requests(id) ON DELETE CASCADE,
466 user_id INTEGER NOT NULL,
467 status TEXT NOT NULL,
468 updated_at TEXT NOT NULL,
469 UNIQUE(mr_id, user_id)
470);
471CREATE TABLE IF NOT EXISTS labels (
472 id INTEGER PRIMARY KEY,
473 repo_id INTEGER NOT NULL,
474 name TEXT NOT NULL,
475 color TEXT NOT NULL,
476 UNIQUE(repo_id, name)
477);
478CREATE TABLE IF NOT EXISTS milestones (
479 id INTEGER PRIMARY KEY,
480 repo_id INTEGER NOT NULL,
481 title TEXT NOT NULL,
482 due_on TEXT NOT NULL DEFAULT '',
483 state TEXT NOT NULL DEFAULT 'open',
484 UNIQUE(repo_id, title)
485);
486CREATE TABLE IF NOT EXISTS issues (
487 id INTEGER PRIMARY KEY,
488 repo_id INTEGER NOT NULL,
489 number INTEGER NOT NULL,
490 title TEXT NOT NULL,
491 description TEXT NOT NULL DEFAULT '',
492 author_id INTEGER NOT NULL,
493 status TEXT NOT NULL DEFAULT 'open',
494 confidential INTEGER NOT NULL DEFAULT 0,
495 milestone_id INTEGER NOT NULL DEFAULT 0,
496 due_on TEXT NOT NULL DEFAULT '',
497 created_at TEXT NOT NULL,
498 updated_at TEXT NOT NULL,
499 closed_at TEXT NOT NULL DEFAULT '',
500 UNIQUE(repo_id, number)
501);
502CREATE TABLE IF NOT EXISTS issue_labels (
503 issue_id INTEGER NOT NULL,
504 label_id INTEGER NOT NULL,
505 PRIMARY KEY(issue_id, label_id)
506);
507CREATE TABLE IF NOT EXISTS issue_assignees (
508 issue_id INTEGER NOT NULL,
509 user_id INTEGER NOT NULL,
510 PRIMARY KEY(issue_id, user_id)
511);
512CREATE TABLE IF NOT EXISTS issue_notes (
513 id INTEGER PRIMARY KEY,
514 repo_id INTEGER NOT NULL,
515 issue_id INTEGER NOT NULL,
516 author_id INTEGER NOT NULL,
517 body TEXT NOT NULL,
518 created_at TEXT NOT NULL
519);
520CREATE TABLE IF NOT EXISTS issue_links (
521 repo_id INTEGER NOT NULL,
522 a INTEGER NOT NULL,
523 b INTEGER NOT NULL,
524 PRIMARY KEY (a, b)
525);
526CREATE INDEX IF NOT EXISTS issues_repo ON issues(repo_id, number);
527`
528
529func now() string { return time.Now().UTC().Format(time.RFC3339) }
530
531func parseTime(s string) time.Time {
532 t, err := time.Parse(time.RFC3339, s)
533 if err != nil {
534 return time.Time{}
535 }
536 return t
537}
538
539// User is een ingelogd account van de auth-server.
540type User struct {
541 ID int64
542 AuthID int64
543 Username string
544 AvatarURL string
545 AuthAdmin bool
546 InstanceAdmin bool
547 Language string
548 DisplayName string
549 Bio string
550 CreatedAt time.Time
551}
552
553// UpsertUser maakt of ververst een account. De eerste gebruiker wordt
554// installatiebeheerder. Wachtende lidmaatschappen op deze naam worden gekoppeld.
555func (s *Store) UpsertUser(ctx context.Context, authID int64, username, avatar string, authAdmin bool) (User, error) {
556 username = strings.ToLower(strings.TrimSpace(username))
557 if err := names.ValidUsername(username); err != nil {
558 return User{}, err
559 }
560 tx, err := s.db.BeginTx(ctx, nil)
561 if err != nil {
562 return User{}, err
563 }
564 defer func() { _ = tx.Rollback() }()
565
566 var count int
567 if err := tx.QueryRowContext(ctx, `SELECT COUNT(*) FROM users`).Scan(&count); err != nil {
568 return User{}, err
569 }
570 ts := now()
571 var existingID int64
572 err = tx.QueryRowContext(ctx, `SELECT id FROM users WHERE auth_id = ?`, authID).Scan(&existingID)
573 if errors.Is(err, sql.ErrNoRows) {
574 var nameTaken int
575 if err := tx.QueryRowContext(ctx, `SELECT COUNT(*) FROM users WHERE username = ?`, username).Scan(&nameTaken); err != nil {
576 return User{}, err
577 }
578 if nameTaken > 0 {
579 return User{}, i18n.E("err.userTaken", username)
580 }
581 admin := 0
582 if count == 0 {
583 admin = 1
584 }
585 res, err := tx.ExecContext(ctx, `INSERT INTO users (auth_id, username, avatar_url, auth_admin, instance_admin, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?)`,
586 authID, username, avatar, boolInt(authAdmin), admin, ts, ts)
587 if err != nil {
588 return User{}, err
589 }
590 existingID, _ = res.LastInsertId()
591 } else if err != nil {
592 return User{}, err
593 } else {
594 if _, err := tx.ExecContext(ctx, `UPDATE users SET username = ?, avatar_url = ?, auth_admin = ?, updated_at = ? WHERE id = ? AND username = ?`,
595 username, avatar, boolInt(authAdmin), ts, existingID, username); err != nil {
596 return User{}, err
597 }
598 // Naamwijziging alleen als de nieuwe naam vrij is en gelijk blijft als hij botst.
599 var current string
600 if err := tx.QueryRowContext(ctx, `SELECT username FROM users WHERE id = ?`, existingID).Scan(&current); err != nil {
601 return User{}, err
602 }
603 if current != username {
604 var taken int
605 if err := tx.QueryRowContext(ctx, `SELECT COUNT(*) FROM users WHERE username = ? AND id <> ?`, username, existingID).Scan(&taken); err != nil {
606 return User{}, err
607 }
608 var groupTaken int
609 if err := tx.QueryRowContext(ctx, `SELECT COUNT(*) FROM groups WHERE path = ?`, username).Scan(&groupTaken); err != nil {
610 return User{}, err
611 }
612 if taken == 0 && groupTaken == 0 {
613 if _, err := tx.ExecContext(ctx, `UPDATE users SET username = ?, avatar_url = ?, auth_admin = ?, updated_at = ? WHERE id = ?`,
614 username, avatar, boolInt(authAdmin), ts, existingID); err != nil {
615 return User{}, err
616 }
617 } else if _, err := tx.ExecContext(ctx, `UPDATE users SET avatar_url = ?, auth_admin = ?, updated_at = ? WHERE id = ?`,
618 avatar, boolInt(authAdmin), ts, existingID); err != nil {
619 return User{}, err
620 }
621 }
622 }
623 if err := claimPending(ctx, tx, existingID, username); err != nil {
624 return User{}, err
625 }
626 if err := tx.Commit(); err != nil {
627 return User{}, err
628 }
629 return s.UserByID(ctx, existingID)
630}
631
632func claimPending(ctx context.Context, tx *sql.Tx, userID int64, username string) error {
633 rows, err := tx.QueryContext(ctx, `SELECT id, scope_kind, scope_id, role FROM pending_members WHERE username = ?`, username)
634 if err != nil {
635 return err
636 }
637 defer rows.Close()
638 type pend struct {
639 id, scopeID int64
640 kind, role string
641 }
642 var all []pend
643 for rows.Next() {
644 var p pend
645 if err := rows.Scan(&p.id, &p.kind, &p.scopeID, &p.role); err != nil {
646 return err
647 }
648 all = append(all, p)
649 }
650 if err := rows.Err(); err != nil {
651 return err
652 }
653 for _, p := range all {
654 switch p.kind {
655 case "group":
656 if _, err := tx.ExecContext(ctx, `INSERT OR IGNORE INTO group_members (group_id, user_id, role) VALUES (?, ?, ?)`, p.scopeID, userID, p.role); err != nil {
657 return err
658 }
659 case "repo":
660 if _, err := tx.ExecContext(ctx, `INSERT OR IGNORE INTO repo_members (repo_id, user_id, role) VALUES (?, ?, ?)`, p.scopeID, userID, p.role); err != nil {
661 return err
662 }
663 }
664 if _, err := tx.ExecContext(ctx, `DELETE FROM pending_members WHERE id = ?`, p.id); err != nil {
665 return err
666 }
667 }
668 return nil
669}
670
671// UserByID leest één account.
672func (s *Store) UserByID(ctx context.Context, id int64) (User, error) {
673 row := s.db.QueryRowContext(ctx, `SELECT `+userCols+` FROM users WHERE id = ?`, id)
674 return scanUser(row)
675}
676
677// UserByUsername leest op naam.
678func (s *Store) UserByUsername(ctx context.Context, username string) (User, error) {
679 row := s.db.QueryRowContext(ctx, `SELECT `+userCols+` FROM users WHERE username = ?`, strings.ToLower(username))
680 return scanUser(row)
681}
682
683const userCols = `id, auth_id, username, avatar_url, auth_admin, instance_admin, language, display_name, bio, created_at`
684
685// SetProfile bewaart de weergavenaam en de bio. Lege tekst wist het veld.
686func (s *Store) SetProfile(ctx context.Context, userID int64, name, bio string) error {
687 _, err := s.db.ExecContext(ctx, `UPDATE users SET display_name = ?, bio = ?, updated_at = ? WHERE id = ?`, name, bio, now(), userID)
688 return err
689}
690
691func scanUser(row interface{ Scan(...any) error }) (User, error) {
692 var u User
693 var admin, inst int
694 var created string
695 if err := row.Scan(&u.ID, &u.AuthID, &u.Username, &u.AvatarURL, &admin, &inst, &u.Language, &u.DisplayName, &u.Bio, &created); err != nil {
696 if errors.Is(err, sql.ErrNoRows) {
697 return User{}, errNotFound
698 }
699 return User{}, err
700 }
701 u.AuthAdmin = admin == 1
702 u.InstanceAdmin = inst == 1
703 if u.Language == "" {
704 u.Language = "en"
705 }
706 u.CreatedAt = parseTime(created)
707 return u, nil
708}
709
710// Session is een browsersessie.
711type Session struct {
712 ID string
713 UserID int64
714 AccessToken string
715 ExpiresAt time.Time
716 CSRF string
717 User User
718}
719
720// CreateSession bewaart het bearer-token van de auth-server.
721func (s *Store) CreateSession(ctx context.Context, userID int64, accessToken string, expiresAt time.Time) (Session, error) {
722 id, err := randomID()
723 if err != nil {
724 return Session{}, err
725 }
726 csrf, err := randomID()
727 if err != nil {
728 return Session{}, err
729 }
730 _, err = s.db.ExecContext(ctx, `INSERT INTO sessions (id, user_id, access_token, expires_at, csrf, created_at) VALUES (?, ?, ?, ?, ?, ?)`,
731 id, userID, accessToken, expiresAt.UTC().Format(time.RFC3339), csrf, now())
732 if err != nil {
733 return Session{}, err
734 }
735 return s.Session(ctx, id)
736}
737
738// Session leest een sessie met de gebruiker.
739func (s *Store) Session(ctx context.Context, id string) (Session, error) {
740 var sess Session
741 var exp string
742 err := s.db.QueryRowContext(ctx, `SELECT id, user_id, access_token, expires_at, csrf FROM sessions WHERE id = ?`, id).
743 Scan(&sess.ID, &sess.UserID, &sess.AccessToken, &exp, &sess.CSRF)
744 if errors.Is(err, sql.ErrNoRows) {
745 return Session{}, errNotFound
746 }
747 if err != nil {
748 return Session{}, err
749 }
750 sess.ExpiresAt = parseTime(exp)
751 u, err := s.UserByID(ctx, sess.UserID)
752 if err != nil {
753 return Session{}, err
754 }
755 sess.User = u
756 return sess, nil
757}
758
759// UpdateSessionExpiry schuift de lokale kopie van de auth-sessie op.
760func (s *Store) UpdateSessionExpiry(ctx context.Context, id string, expiresAt time.Time) error {
761 _, err := s.db.ExecContext(ctx, `UPDATE sessions SET expires_at = ? WHERE id = ?`, expiresAt.UTC().Format(time.RFC3339), id)
762 return err
763}
764
765// DeleteSession verwijdert de lokale sessie.
766func (s *Store) DeleteSession(ctx context.Context, id string) error {
767 _, err := s.db.ExecContext(ctx, `DELETE FROM sessions WHERE id = ?`, id)
768 return err
769}
770
771// TokenInfo is een persoonlijk toegangstoken zonder het geheim.
772type TokenInfo struct {
773 ID int64
774 Name string
775 Prefix string
776 Scopes string
777 CreatedAt time.Time
778 LastUsedAt time.Time
779 ExpiresAt time.Time
780}
781
782// TokenAuth is een geldig token plus de eigenaar.
783type TokenAuth struct {
784 Token TokenInfo
785 User User
786}
787
788// TokenRequest beschrijft een nieuw token. Lege scopes worden api.
789type TokenRequest struct {
790 Name string
791 Scopes string
792 ExpiresAt time.Time
793}
794
795// CreateToken maakt een bg_-token met scope api. De tekst wordt één keer teruggegeven.
796func (s *Store) CreateToken(ctx context.Context, userID int64, name string) (string, error) {
797 plain, _, err := s.IssueToken(ctx, userID, TokenRequest{Name: name, Scopes: "api"})
798 return plain, err
799}
800
801// IssueToken maakt een token met scopes en een optionele vervaldatum.
802func (s *Store) IssueToken(ctx context.Context, userID int64, req TokenRequest) (string, TokenInfo, error) {
803 name := strings.TrimSpace(req.Name)
804 if name == "" || len(name) > 80 {
805 return "", TokenInfo{}, i18n.E("err.tokenNeedsName")
806 }
807 scopes := strings.TrimSpace(req.Scopes)
808 if scopes == "" {
809 scopes = "api"
810 }
811 if !req.ExpiresAt.IsZero() && !req.ExpiresAt.After(time.Now()) {
812 return "", TokenInfo{}, i18n.E("err.tokenExpired")
813 }
814 plain, err := newToken("bg_")
815 if err != nil {
816 return "", TokenInfo{}, err
817 }
818 var exp any
819 if !req.ExpiresAt.IsZero() {
820 exp = req.ExpiresAt.UTC().Format(time.RFC3339)
821 }
822 res, err := s.db.ExecContext(ctx, `INSERT INTO access_tokens (user_id, name, token_hash, prefix, scopes, created_at, expires_at) VALUES (?, ?, ?, ?, ?, ?, ?)`,
823 userID, name, hashToken(plain), plain[:10], scopes, now(), exp)
824 if err != nil {
825 return "", TokenInfo{}, err
826 }
827 id, _ := res.LastInsertId()
828 info := TokenInfo{ID: id, Name: name, Prefix: plain[:10], Scopes: scopes, CreatedAt: time.Now().UTC(), ExpiresAt: req.ExpiresAt}
829 return plain, info, nil
830}
831
832// ListTokens toont de tokens van een gebruiker.
833func (s *Store) ListTokens(ctx context.Context, userID int64) ([]TokenInfo, error) {
834 rows, err := s.db.QueryContext(ctx, `SELECT id, name, prefix, COALESCE(scopes, 'api'), created_at, COALESCE(last_used_at, ''), COALESCE(expires_at, '') FROM access_tokens WHERE user_id = ? ORDER BY id DESC`, userID)
835 if err != nil {
836 return nil, err
837 }
838 defer rows.Close()
839 var out []TokenInfo
840 for rows.Next() {
841 var t TokenInfo
842 var created, used, expires string
843 if err := rows.Scan(&t.ID, &t.Name, &t.Prefix, &t.Scopes, &created, &used, &expires); err != nil {
844 return nil, err
845 }
846 t.CreatedAt = parseTime(created)
847 t.LastUsedAt = parseTime(used)
848 t.ExpiresAt = parseTime(expires)
849 out = append(out, t)
850 }
851 return out, rows.Err()
852}
853
854// DeleteToken verwijdert een token van deze gebruiker.
855func (s *Store) DeleteToken(ctx context.Context, userID, id int64) error {
856 res, err := s.db.ExecContext(ctx, `DELETE FROM access_tokens WHERE id = ? AND user_id = ?`, id, userID)
857 if err != nil {
858 return err
859 }
860 n, _ := res.RowsAffected()
861 if n == 0 {
862 return errNotFound
863 }
864 return nil
865}
866
867// UserByToken zoekt de eigenaar van een persoonlijk token.
868func (s *Store) UserByToken(ctx context.Context, token string) (User, error) {
869 auth, err := s.AuthenticateToken(ctx, token)
870 if err != nil {
871 return User{}, err
872 }
873 return auth.User, nil
874}
875
876// AuthenticateToken controleert het geheim, de vervaldatum en geeft de scopes terug.
877func (s *Store) AuthenticateToken(ctx context.Context, token string) (TokenAuth, error) {
878 if !strings.HasPrefix(token, "bg_") {
879 return TokenAuth{}, errNotFound
880 }
881 var info TokenInfo
882 var userID int64
883 var created, expires, scopes string
884 err := s.db.QueryRowContext(ctx, `SELECT id, user_id, name, prefix, COALESCE(scopes, 'api'), created_at, COALESCE(expires_at, '') FROM access_tokens WHERE token_hash = ?`, hashToken(token)).
885 Scan(&info.ID, &userID, &info.Name, &info.Prefix, &scopes, &created, &expires)
886 if errors.Is(err, sql.ErrNoRows) {
887 return TokenAuth{}, errNotFound
888 }
889 if err != nil {
890 return TokenAuth{}, err
891 }
892 info.Scopes = scopes
893 info.CreatedAt = parseTime(created)
894 info.ExpiresAt = parseTime(expires)
895 if !info.ExpiresAt.IsZero() && !info.ExpiresAt.After(time.Now()) {
896 return TokenAuth{}, i18n.E("err.tokenExpired")
897 }
898 _, _ = s.db.ExecContext(ctx, `UPDATE access_tokens SET last_used_at = ? WHERE id = ?`, now(), info.ID)
899 user, err := s.UserByID(ctx, userID)
900 if err != nil {
901 return TokenAuth{}, err
902 }
903 return TokenAuth{Token: info, User: user}, nil
904}
905
906// SSHKey is een publieke sleutel voor git-over-SSH.
907type SSHKey struct {
908 ID int64
909 UserID int64
910 Name string
911 PublicKey string
912 Fingerprint string
913 CreatedAt time.Time
914}
915
916// AddSSHKey bewaart één OpenSSH-regel.
917func (s *Store) AddSSHKey(ctx context.Context, userID int64, name, line, fingerprint string) error {
918 name = strings.TrimSpace(name)
919 if name == "" {
920 name = "sleutel"
921 }
922 _, err := s.db.ExecContext(ctx, `INSERT INTO ssh_keys (user_id, name, public_key, fingerprint, created_at) VALUES (?, ?, ?, ?, ?)`,
923 userID, name, strings.TrimSpace(line), fingerprint, now())
924 if err != nil && strings.Contains(err.Error(), "UNIQUE") {
925 return i18n.E("err.sshDup")
926 }
927 return err
928}
929
930// ListSSHKeys toont de sleutels van een gebruiker.
931func (s *Store) ListSSHKeys(ctx context.Context, userID int64) ([]SSHKey, error) {
932 rows, err := s.db.QueryContext(ctx, `SELECT id, user_id, name, public_key, fingerprint, created_at FROM ssh_keys WHERE user_id = ? ORDER BY id`, userID)
933 if err != nil {
934 return nil, err
935 }
936 defer rows.Close()
937 return scanKeys(rows)
938}
939
940// SSHKeyByFingerprint zoekt een sleutel voor de SSH-server.
941func (s *Store) SSHKeyByFingerprint(ctx context.Context, fingerprint string) (SSHKey, error) {
942 row := s.db.QueryRowContext(ctx, `SELECT id, user_id, name, public_key, fingerprint, created_at FROM ssh_keys WHERE fingerprint = ?`, fingerprint)
943 var k SSHKey
944 var created string
945 if err := row.Scan(&k.ID, &k.UserID, &k.Name, &k.PublicKey, &k.Fingerprint, &created); err != nil {
946 if errors.Is(err, sql.ErrNoRows) {
947 return SSHKey{}, errNotFound
948 }
949 return SSHKey{}, err
950 }
951 k.CreatedAt = parseTime(created)
952 return k, nil
953}
954
955// DeleteSSHKey verwijdert een sleutel van deze gebruiker.
956func (s *Store) DeleteSSHKey(ctx context.Context, userID, id int64) error {
957 res, err := s.db.ExecContext(ctx, `DELETE FROM ssh_keys WHERE id = ? AND user_id = ?`, id, userID)
958 if err != nil {
959 return err
960 }
961 n, _ := res.RowsAffected()
962 if n == 0 {
963 return errNotFound
964 }
965 return nil
966}
967
968func scanKeys(rows *sql.Rows) ([]SSHKey, error) {
969 var out []SSHKey
970 for rows.Next() {
971 var k SSHKey
972 var created string
973 if err := rows.Scan(&k.ID, &k.UserID, &k.Name, &k.PublicKey, &k.Fingerprint, &created); err != nil {
974 return nil, err
975 }
976 k.CreatedAt = parseTime(created)
977 out = append(out, k)
978 }
979 return out, rows.Err()
980}
981
982func randomID() (string, error) {
983 b := make([]byte, 32)
984 if _, err := rand.Read(b); err != nil {
985 return "", err
986 }
987 return hex.EncodeToString(b), nil
988}
989
990func newToken(prefix string) (string, error) {
991 b := make([]byte, 32)
992 if _, err := rand.Read(b); err != nil {
993 return "", err
994 }
995 return prefix + hex.EncodeToString(b), nil
996}
997
998func hashToken(token string) string {
999 sum := sha256.Sum256([]byte(token))
1000 return hex.EncodeToString(sum[:])
1001}
1002
1003func boolInt(b bool) int {
1004 if b {
1005 return 1
1006 }
1007 return 0
1008}
1009
1010var errNotFound = fmt.Errorf("niet gevonden")
1011
1012// Missing meldt dat de gevraagde rij er niet is.
1013func Missing(err error) bool { return errors.Is(err, errNotFound) }
1014
1015// NotFound is de fout voor een ontbrekende rij.
1016func NotFound() error { return errNotFound }
1017
1018// ErrNotFound is het resultaat als een rij ontbreekt.
1019func ErrNotFound() error { return errNotFound }
1020
1021// IsNotFound meldt of err een ontbrekende rij is.
1022func IsNotFound(err error) bool { return errors.Is(err, errNotFound) }
1023
1024func mustJSON(v any) string {
1025 b, _ := json.Marshal(v)
1026 if b == nil {
1027 return "null"
1028 }
1029 return string(b)
1030}
1031
1032// EncryptSecret versleutelt een geheim met de installatiesleutel.
1033func (s *Store) encrypt(plain string) (nonce, ct []byte, err error) {
1034 return secretbox.Encrypt(s.key, plain)
1035}
1036
1037func (s *Store) decrypt(nonce, ct []byte) (string, error) {
1038 return secretbox.Decrypt(s.key, nonce, ct)
1039}