package store import ( "context" "database/sql" "errors" "regexp" "sort" "strconv" "strings" "time" "unicode" "gitlab.com/Timfa/bluegit/internal/i18n" ) // Label is een label binnen één repository. type Label struct { ID int64 RepoID int64 Name string Color string } // Milestone is een mijlpaal binnen één repository. type Milestone struct { ID int64 RepoID int64 Title string DueOn string State string } // Issue is één issue, genummerd per repository. type Issue struct { ID int64 RepoID int64 Number int64 Title string Description string AuthorID int64 Author string Status string Confidential bool MilestoneID int64 Milestone string DueOn string CreatedAt time.Time UpdatedAt time.Time ClosedAt time.Time Labels []Label AssigneeIDs []int64 Assignees []string } // IssueNote is een reactie op een issue. type IssueNote struct { ID int64 RepoID int64 IssueID int64 AuthorID int64 Author string Body string CreatedAt time.Time } // IssueFilter beperkt de lijst. State is open, closed of all. type IssueFilter struct { State string Query string Label string Assignee string ViewerID int64 Triage bool } // NewIssue is de invoer voor een nieuw issue. type NewIssue struct { Title string Description string Confidential bool MilestoneID int64 DueOn string LabelIDs []int64 AssigneeIDs []int64 } var labelColor = regexp.MustCompile(`^#[0-9a-fA-F]{6}$`) // ValidDue eist leeg of YYYY-MM-DD. func ValidDue(s string) error { s = strings.TrimSpace(s) if s == "" { return nil } if _, err := time.Parse("2006-01-02", s); err != nil { return i18n.E("err.issueDue") } return nil } // ValidLabelName eist letters, cijfers, spaties, streepjes of underscores. func ValidLabelName(s string) error { s = strings.TrimSpace(s) if s == "" || len([]rune(s)) > 50 { return i18n.E("err.labelName") } for _, r := range s { if r == ' ' || r == '-' || r == '_' || unicode.IsLetter(r) || unicode.IsDigit(r) { continue } return i18n.E("err.labelName") } return nil } // NormalizeColor vult de standaardkleur aan of keurt een hexkleur goed. func NormalizeColor(s string) (string, error) { s = strings.TrimSpace(s) if s == "" { return "#428fdc", nil } if !labelColor.MatchString(s) { return "", i18n.E("err.labelColor") } return strings.ToLower(s), nil } // ValidMilestoneTitle eist een korte titel. func ValidMilestoneTitle(s string) error { s = strings.TrimSpace(s) if s == "" || len([]rune(s)) > 80 { return i18n.E("err.milestoneName") } for _, r := range s { if r == ' ' || r == '-' || r == '_' || r == '.' || unicode.IsLetter(r) || unicode.IsDigit(r) { continue } return i18n.E("err.milestoneName") } return nil } func limitIssueText(s string) error { if len([]rune(s)) > 20000 { return i18n.E("err.issueBody") } return nil } // UpsertLabel maakt een label of werkt de kleur bij. func (s *Store) UpsertLabel(ctx context.Context, repoID int64, name, color string) (Label, error) { name = strings.TrimSpace(name) if err := ValidLabelName(name); err != nil { return Label{}, err } color, err := NormalizeColor(color) if err != nil { return Label{}, err } if _, err := s.db.ExecContext(ctx, `INSERT INTO labels (repo_id, name, color) VALUES (?, ?, ?) ON CONFLICT(repo_id, name) DO UPDATE SET color = excluded.color`, repoID, name, color); err != nil { return Label{}, err } return s.LabelByName(ctx, repoID, name) } // LabelByName leest één label. func (s *Store) LabelByName(ctx context.Context, repoID int64, name string) (Label, error) { var l Label err := s.db.QueryRowContext(ctx, `SELECT id, repo_id, name, color FROM labels WHERE repo_id = ? AND name = ?`, repoID, strings.TrimSpace(name)). Scan(&l.ID, &l.RepoID, &l.Name, &l.Color) if errors.Is(err, sql.ErrNoRows) { return Label{}, errNotFound } return l, err } // ListLabels geeft de labels op naam. func (s *Store) ListLabels(ctx context.Context, repoID int64) ([]Label, error) { rows, err := s.db.QueryContext(ctx, `SELECT id, repo_id, name, color FROM labels WHERE repo_id = ? ORDER BY name`, repoID) if err != nil { return nil, err } defer rows.Close() out := []Label{} for rows.Next() { var l Label if err := rows.Scan(&l.ID, &l.RepoID, &l.Name, &l.Color); err != nil { return nil, err } out = append(out, l) } return out, rows.Err() } // UpsertMilestone maakt een mijlpaal of werkt de datum bij. func (s *Store) UpsertMilestone(ctx context.Context, repoID int64, title, due string) (Milestone, error) { title = strings.TrimSpace(title) if err := ValidMilestoneTitle(title); err != nil { return Milestone{}, err } due = strings.TrimSpace(due) if err := ValidDue(due); err != nil { return Milestone{}, err } if _, err := s.db.ExecContext(ctx, `INSERT INTO milestones (repo_id, title, due_on, state) VALUES (?, ?, ?, 'open') ON CONFLICT(repo_id, title) DO UPDATE SET due_on = excluded.due_on`, repoID, title, due); err != nil { return Milestone{}, err } return s.MilestoneByTitle(ctx, repoID, title) } // MilestoneByTitle leest één mijlpaal. func (s *Store) MilestoneByTitle(ctx context.Context, repoID int64, title string) (Milestone, error) { var m Milestone err := s.db.QueryRowContext(ctx, `SELECT id, repo_id, title, due_on, state FROM milestones WHERE repo_id = ? AND title = ?`, repoID, strings.TrimSpace(title)). Scan(&m.ID, &m.RepoID, &m.Title, &m.DueOn, &m.State) if errors.Is(err, sql.ErrNoRows) { return Milestone{}, errNotFound } return m, err } // ListMilestones geeft de mijlpalen op titel. func (s *Store) ListMilestones(ctx context.Context, repoID int64) ([]Milestone, error) { rows, err := s.db.QueryContext(ctx, `SELECT id, repo_id, title, due_on, state FROM milestones WHERE repo_id = ? ORDER BY title`, repoID) if err != nil { return nil, err } defer rows.Close() out := []Milestone{} for rows.Next() { var m Milestone if err := rows.Scan(&m.ID, &m.RepoID, &m.Title, &m.DueOn, &m.State); err != nil { return nil, err } out = append(out, m) } return out, rows.Err() } const issueSelect = `SELECT i.id, i.repo_id, i.number, i.title, i.description, i.author_id, u.username, i.status, i.confidential, i.milestone_id, COALESCE(m.title, ''), i.due_on, i.created_at, i.updated_at, i.closed_at, COALESCE((SELECT group_concat(l.id || char(31) || l.name || char(31) || l.color, char(30)) FROM issue_labels il JOIN labels l ON l.id = il.label_id WHERE il.issue_id = i.id), ''), COALESCE((SELECT group_concat(au.id || char(31) || au.username, char(30)) FROM issue_assignees ia JOIN users au ON au.id = ia.user_id WHERE ia.issue_id = i.id), '') FROM issues i JOIN users u ON u.id = i.author_id LEFT JOIN milestones m ON m.id = i.milestone_id AND m.id <> 0` // CreateIssue nummert per repository en zet labels en toewijzingen. func (s *Store) CreateIssue(ctx context.Context, repoID, authorID int64, in NewIssue) (Issue, error) { title := trim(strings.TrimSpace(in.Title), 200) if title == "" { return Issue{}, i18n.E("err.issueTitle") } if err := limitIssueText(in.Description); err != nil { return Issue{}, err } in.DueOn = strings.TrimSpace(in.DueOn) if err := ValidDue(in.DueOn); err != nil { return Issue{}, err } tx, err := s.db.BeginTx(ctx, nil) if err != nil { return Issue{}, err } defer func() { _ = tx.Rollback() }() if err := ensureIssueRefs(ctx, tx, repoID, in.MilestoneID, in.LabelIDs, in.AssigneeIDs); err != nil { return Issue{}, err } var n int64 if err := tx.QueryRowContext(ctx, `SELECT COALESCE(MAX(number), 0) FROM issues WHERE repo_id = ?`, repoID).Scan(&n); err != nil { return Issue{}, err } ts := now() conf := 0 if in.Confidential { conf = 1 } res, err := tx.ExecContext(ctx, `INSERT INTO issues (repo_id, number, title, description, author_id, status, confidential, milestone_id, due_on, created_at, updated_at) VALUES (?, ?, ?, ?, ?, 'open', ?, ?, ?, ?, ?)`, repoID, n+1, title, in.Description, authorID, conf, in.MilestoneID, in.DueOn, ts, ts) if err != nil { return Issue{}, err } id, _ := res.LastInsertId() if err := replaceIssueLinks(ctx, tx, id, in.LabelIDs, in.AssigneeIDs); err != nil { return Issue{}, err } if err := tx.Commit(); err != nil { return Issue{}, err } return s.IssueByNumber(ctx, repoID, n+1) } // IssueByNumber leest #nummer. func (s *Store) IssueByNumber(ctx context.Context, repoID, number int64) (Issue, error) { row := s.db.QueryRowContext(ctx, issueSelect+` WHERE i.repo_id = ? AND i.number = ?`, repoID, number) return scanIssue(row) } // ListIssues geeft issues, nieuwste nummer eerst. // Zonder triage blijven andermans vertrouwelijke issues weg. func (s *Store) ListIssues(ctx context.Context, repoID int64, f IssueFilter) ([]Issue, error) { q := issueSelect + ` WHERE i.repo_id = ?` args := []any{repoID} if !f.Triage { q += ` AND (i.confidential = 0 OR i.author_id = ?)` args = append(args, f.ViewerID) } switch f.State { case "closed": q += ` AND i.status = 'closed'` case "all": default: q += ` AND i.status = 'open'` } if strings.TrimSpace(f.Query) != "" { q += ` AND (instr(lower(i.title), lower(?)) > 0 OR instr(lower(i.description), lower(?)) > 0)` args = append(args, strings.TrimSpace(f.Query), strings.TrimSpace(f.Query)) } if strings.TrimSpace(f.Label) != "" { q += ` AND EXISTS (SELECT 1 FROM issue_labels il JOIN labels l ON l.id = il.label_id WHERE il.issue_id = i.id AND l.name = ?)` args = append(args, strings.TrimSpace(f.Label)) } if strings.TrimSpace(f.Assignee) != "" { q += ` AND EXISTS (SELECT 1 FROM issue_assignees ia JOIN users au ON au.id = ia.user_id WHERE ia.issue_id = i.id AND au.username = ?)` args = append(args, strings.ToLower(strings.TrimSpace(f.Assignee))) } q += ` ORDER BY i.number DESC` rows, err := s.db.QueryContext(ctx, q, args...) if err != nil { return nil, err } defer rows.Close() out := []Issue{} for rows.Next() { iss, err := scanIssue(rows) if err != nil { return nil, err } out = append(out, iss) } return out, rows.Err() } // UpdateIssueText wijzigt titel en beschrijving. func (s *Store) UpdateIssueText(ctx context.Context, id int64, title, description string) error { title = trim(strings.TrimSpace(title), 200) if title == "" { return i18n.E("err.issueTitle") } if err := limitIssueText(description); err != nil { return err } _, err := s.db.ExecContext(ctx, `UPDATE issues SET title = ?, description = ?, updated_at = ? WHERE id = ?`, title, description, now(), id) return err } // SetIssueMeta wijzigt vertrouwelijkheid, mijlpaal en vervaldatum. func (s *Store) SetIssueMeta(ctx context.Context, repoID, id, milestoneID int64, confidential bool, due string) error { due = strings.TrimSpace(due) if err := ValidDue(due); err != nil { return err } if err := ensureIssueRefs(ctx, s.db, repoID, milestoneID, nil, nil); err != nil { return err } flag := 0 if confidential { flag = 1 } _, err := s.db.ExecContext(ctx, `UPDATE issues SET confidential = ?, milestone_id = ?, due_on = ?, updated_at = ? WHERE id = ?`, flag, milestoneID, due, now(), id) return err } // SetIssueLabels vervangt de labels van een issue. func (s *Store) SetIssueLabels(ctx context.Context, repoID, id int64, labelIDs []int64) error { tx, err := s.db.BeginTx(ctx, nil) if err != nil { return err } defer func() { _ = tx.Rollback() }() if err := ensureIssueRefs(ctx, tx, repoID, 0, labelIDs, nil); err != nil { return err } if _, err := tx.ExecContext(ctx, `DELETE FROM issue_labels WHERE issue_id = ?`, id); err != nil { return err } for _, lid := range labelIDs { if _, err := tx.ExecContext(ctx, `INSERT INTO issue_labels (issue_id, label_id) VALUES (?, ?)`, id, lid); err != nil { return err } } if _, err := tx.ExecContext(ctx, `UPDATE issues SET updated_at = ? WHERE id = ?`, now(), id); err != nil { return err } return tx.Commit() } // SetIssueAssignees vervangt de toewijzingen. func (s *Store) SetIssueAssignees(ctx context.Context, repoID, id int64, userIDs []int64) error { tx, err := s.db.BeginTx(ctx, nil) if err != nil { return err } defer func() { _ = tx.Rollback() }() if err := ensureIssueRefs(ctx, tx, repoID, 0, nil, userIDs); err != nil { return err } if _, err := tx.ExecContext(ctx, `DELETE FROM issue_assignees WHERE issue_id = ?`, id); err != nil { return err } for _, uid := range userIDs { if _, err := tx.ExecContext(ctx, `INSERT INTO issue_assignees (issue_id, user_id) VALUES (?, ?)`, id, uid); err != nil { return err } } if _, err := tx.ExecContext(ctx, `UPDATE issues SET updated_at = ? WHERE id = ?`, now(), id); err != nil { return err } return tx.Commit() } // SetIssueState zet open of closed. changed is onwaar als de status al zo was. func (s *Store) SetIssueState(ctx context.Context, id int64, status string) (bool, error) { if status != "open" && status != "closed" { return false, i18n.E("err.issueState") } closed := "" if status == "closed" { closed = now() } res, err := s.db.ExecContext(ctx, `UPDATE issues SET status = ?, closed_at = ?, updated_at = ? WHERE id = ? AND status <> ?`, status, closed, now(), id, status) if err != nil { return false, err } n, _ := res.RowsAffected() return n > 0, nil } // DeleteIssue verwijdert een issue met reacties, labels en koppelingen. func (s *Store) DeleteIssue(ctx context.Context, repoID, id int64) error { tx, err := s.db.BeginTx(ctx, nil) if err != nil { return err } defer func() { _ = tx.Rollback() }() for _, q := range []string{ `DELETE FROM issue_notes WHERE issue_id = ?`, `DELETE FROM issue_labels WHERE issue_id = ?`, `DELETE FROM issue_assignees WHERE issue_id = ?`, } { if _, err := tx.ExecContext(ctx, q, id); err != nil { return err } } if _, err := tx.ExecContext(ctx, `DELETE FROM issue_links WHERE a = ? OR b = ?`, id, id); err != nil { return err } res, err := tx.ExecContext(ctx, `DELETE FROM issues WHERE id = ? AND repo_id = ?`, id, repoID) if err != nil { return err } n, _ := res.RowsAffected() if n == 0 { return errNotFound } return tx.Commit() } // AddIssueNote bewaart een reactie. func (s *Store) AddIssueNote(ctx context.Context, repoID, issueID, authorID int64, body string) (IssueNote, error) { body = strings.TrimSpace(body) if body == "" { return IssueNote{}, i18n.E("err.issueNote") } if err := limitIssueText(body); err != nil { return IssueNote{}, err } ts := now() res, err := s.db.ExecContext(ctx, `INSERT INTO issue_notes (repo_id, issue_id, author_id, body, created_at) VALUES (?, ?, ?, ?, ?)`, repoID, issueID, authorID, body, ts) if err != nil { return IssueNote{}, err } _, _ = s.db.ExecContext(ctx, `UPDATE issues SET updated_at = ? WHERE id = ?`, ts, issueID) id, _ := res.LastInsertId() return s.issueNote(ctx, id) } // ListIssueNotes geeft reacties, oudste eerst. func (s *Store) ListIssueNotes(ctx context.Context, issueID int64) ([]IssueNote, error) { rows, err := s.db.QueryContext(ctx, `SELECT n.id, n.repo_id, n.issue_id, n.author_id, u.username, n.body, n.created_at FROM issue_notes n JOIN users u ON u.id = n.author_id WHERE n.issue_id = ? ORDER BY n.id`, issueID) if err != nil { return nil, err } defer rows.Close() out := []IssueNote{} for rows.Next() { n, err := scanIssueNote(rows) if err != nil { return nil, err } out = append(out, n) } return out, rows.Err() } // LinkIssues koppelt twee issues in beide richtingen. func (s *Store) LinkIssues(ctx context.Context, repoID, a, b int64) error { if a == 0 || b == 0 || a == b { return nil } _, err := s.db.ExecContext(ctx, `INSERT OR IGNORE INTO issue_links (repo_id, a, b) VALUES (?, ?, ?), (?, ?, ?)`, repoID, a, b, repoID, b, a) return err } // RelatedIssues geeft de gekoppelde issues. func (s *Store) RelatedIssues(ctx context.Context, repoID, issueID int64) ([]Issue, error) { rows, err := s.db.QueryContext(ctx, `SELECT b FROM issue_links WHERE repo_id = ? AND a = ? ORDER BY b`, repoID, issueID) if err != nil { return nil, err } defer rows.Close() var ids []int64 for rows.Next() { var id int64 if err := rows.Scan(&id); err != nil { return nil, err } ids = append(ids, id) } if err := rows.Err(); err != nil { return nil, err } out := []Issue{} for _, id := range ids { iss, err := s.issueByID(ctx, repoID, id) if err != nil { continue } out = append(out, iss) } return out, nil } func (s *Store) issueByID(ctx context.Context, repoID, id int64) (Issue, error) { row := s.db.QueryRowContext(ctx, issueSelect+` WHERE i.repo_id = ? AND i.id = ?`, repoID, id) return scanIssue(row) } // IssueByID leest een issue op de interne id. func (s *Store) IssueByID(ctx context.Context, repoID, id int64) (Issue, error) { return s.issueByID(ctx, repoID, id) } // IssueNoteByID leest een reactie in deze repository. func (s *Store) IssueNoteByID(ctx context.Context, repoID, id int64) (IssueNote, error) { n, err := s.issueNote(ctx, id) if err != nil { return IssueNote{}, err } if n.RepoID != repoID { return IssueNote{}, errNotFound } return n, nil } // UpdateIssueNoteBody vervangt de tekst van een issue-reactie. func (s *Store) UpdateIssueNoteBody(ctx context.Context, repoID, id int64, body string) error { if strings.TrimSpace(body) == "" { return i18n.E("err.issueNote") } if err := limitIssueText(body); err != nil { return err } res, err := s.db.ExecContext(ctx, `UPDATE issue_notes SET body = ? WHERE id = ? AND repo_id = ?`, body, id, repoID) if err != nil { return err } n, _ := res.RowsAffected() if n == 0 { return errNotFound } return nil } func (s *Store) issueNote(ctx context.Context, id int64) (IssueNote, error) { row := s.db.QueryRowContext(ctx, `SELECT n.id, n.repo_id, n.issue_id, n.author_id, u.username, n.body, n.created_at FROM issue_notes n JOIN users u ON u.id = n.author_id WHERE n.id = ?`, id) return scanIssueNote(row) } type rowScanner interface { Scan(dest ...any) error } func scanIssue(sc rowScanner) (Issue, error) { var iss Issue var created, updated, closed, packedLabels, packedUsers string var conf int if err := sc.Scan(&iss.ID, &iss.RepoID, &iss.Number, &iss.Title, &iss.Description, &iss.AuthorID, &iss.Author, &iss.Status, &conf, &iss.MilestoneID, &iss.Milestone, &iss.DueOn, &created, &updated, &closed, &packedLabels, &packedUsers); err != nil { if errors.Is(err, sql.ErrNoRows) { return Issue{}, errNotFound } return Issue{}, err } iss.Confidential = conf == 1 iss.CreatedAt = parseTime(created) iss.UpdatedAt = parseTime(updated) iss.ClosedAt = parseTime(closed) iss.Labels = parsePackedLabels(packedLabels) iss.AssigneeIDs, iss.Assignees = parsePackedUsers(packedUsers) return iss, nil } func scanIssueNote(sc rowScanner) (IssueNote, error) { var n IssueNote var created string if err := sc.Scan(&n.ID, &n.RepoID, &n.IssueID, &n.AuthorID, &n.Author, &n.Body, &created); err != nil { if errors.Is(err, sql.ErrNoRows) { return IssueNote{}, errNotFound } return IssueNote{}, err } n.CreatedAt = parseTime(created) return n, nil } func parsePackedLabels(raw string) []Label { out := []Label{} if raw == "" { return out } for _, part := range strings.Split(raw, "\x1e") { bits := strings.Split(part, "\x1f") if len(bits) != 3 { continue } id, _ := strconv.ParseInt(bits[0], 10, 64) out = append(out, Label{ID: id, Name: bits[1], Color: bits[2]}) } sort.Slice(out, func(i, j int) bool { return out[i].Name < out[j].Name }) return out } func parsePackedUsers(raw string) ([]int64, []string) { type pair struct { id int64 name string } var pairs []pair if raw != "" { for _, part := range strings.Split(raw, "\x1e") { bits := strings.SplitN(part, "\x1f", 2) if len(bits) != 2 { continue } id, _ := strconv.ParseInt(bits[0], 10, 64) pairs = append(pairs, pair{id, bits[1]}) } } sort.Slice(pairs, func(i, j int) bool { return pairs[i].name < pairs[j].name }) ids := []int64{} names := []string{} for _, p := range pairs { ids = append(ids, p.id) names = append(names, p.name) } return ids, names } type execQuerier interface { ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error) QueryRowContext(ctx context.Context, query string, args ...any) *sql.Row } func ensureIssueRefs(ctx context.Context, q execQuerier, repoID, milestoneID int64, labelIDs, userIDs []int64) error { if milestoneID != 0 { var n int if err := q.QueryRowContext(ctx, `SELECT COUNT(*) FROM milestones WHERE id = ? AND repo_id = ?`, milestoneID, repoID).Scan(&n); err != nil { return err } if n != 1 { return i18n.E("err.milestone") } } for _, id := range labelIDs { var n int if err := q.QueryRowContext(ctx, `SELECT COUNT(*) FROM labels WHERE id = ? AND repo_id = ?`, id, repoID).Scan(&n); err != nil { return err } if n != 1 { return i18n.E("err.labelMissing", id) } } for _, id := range userIDs { var name string err := q.QueryRowContext(ctx, `SELECT username FROM users WHERE id = ?`, id).Scan(&name) if errors.Is(err, sql.ErrNoRows) { return i18n.E("err.assignee", id) } if err != nil { return err } } return nil } func replaceIssueLinks(ctx context.Context, tx *sql.Tx, issueID int64, labelIDs, userIDs []int64) error { for _, id := range labelIDs { if _, err := tx.ExecContext(ctx, `INSERT OR IGNORE INTO issue_labels (issue_id, label_id) VALUES (?, ?)`, issueID, id); err != nil { return err } } for _, id := range userIDs { if _, err := tx.ExecContext(ctx, `INSERT OR IGNORE INTO issue_assignees (issue_id, user_id) VALUES (?, ?)`, issueID, id); err != nil { return err } } return nil }