BlueGit

timfa bluegit

Files

bluegit / internal / store / store.go

store.go

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