BlueGit

timfa bluegit

Files

bluegit / internal / store / store.go

store.go

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