BlueGit

timfa bluegit

Files

bluegit / internal / store / store.go

store.go

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