BlueGit

timfa bluegit

Files

bluegit / internal / store / store.go

store.go

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