BlueGit

timfa bluegit

Files

bluegit / internal / store / store.go

store.go

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