BlueGit

timfa bluegit

Files

bluegit / internal / store / store.go

store.go

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