BlueGit

timfa bluegit

Files

bluegit / internal / store / store.go

store.go

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