BlueGit

timfa bluegit

Files

bluegit / internal / store / store.go

store.go

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