BlueGit

timfa bluegit

Files

bluegit / internal / store / store.go

store.go

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