BlueGit

timfa bluegit

Files

bluegit / internal / store / store.go

store.go

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