BlueGit

timfa bluegit

Files

bluegit / internal / store / store.go

store.go

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