BlueGit

timfa bluegit

Files

bluegit / internal / store / store.go

store.go

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