BlueGit

timfa bluegit

Files

bluegit / internal / store / store.go

store.go

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