BlueGit

timfa bluegit

Files

bluegit / internal / store / store.go

store.go

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