hearthforge

hearthforge/internal/db/issues.go
issues.go
⎇
Raw
1package db
2
3import (
4 "context"
5 "database/sql"
6 "errors"
7)
8
9type Issue struct {
10 ID int64
11 RepoID int64
12 AuthorID *int64
13 Number int64
14 Title string
15 Body string
16 Status string
17 CreatedAt string
18 UpdatedAt string
19 EditedAt *string
20 // Joined from users, null when the author was deleted.
21 AuthorUsername *string
22 AuthorAvatarVersion *int64
23}
24
25// IssueRef is the small lookup result the mutating routes work with.
26type IssueRef struct {
27 ID int64
28 AuthorID *int64
29 Status string
30}
31
32type IssueComment struct {
33 ID int64
34 IssueID int64
35 AuthorID *int64
36 Body string
37 CreatedAt string
38 EditedAt *string
39 AuthorUsername *string
40 AuthorAvatarVersion *int64
41}
42
43// Reaction is one row of issue_reactions or patch_reactions.
44type Reaction struct {
45 ID int64
46 CommentID *int64
47 UserID int64
48 Emoji string
49}
50
51// CommentAuth carries the fields needed to authorise a comment edit.
52type CommentAuth struct {
53 AuthorID *int64
54 // Status of the parent issue or patch.
55 Status string
56}
57
58const issueColumns = `issues.id, issues.repo_id, issues.author_id, issues.number, issues.title,
59 issues.body, issues.status, issues.created_at, issues.updated_at, issues.edited_at,
60 users.username, users.avatar_version`
61
62func scanIssue(s rowScanner) (*Issue, error) {
63 var i Issue
64 err := s.Scan(&i.ID, &i.RepoID, &i.AuthorID, &i.Number, &i.Title, &i.Body, &i.Status,
65 &i.CreatedAt, &i.UpdatedAt, &i.EditedAt, &i.AuthorUsername, &i.AuthorAvatarVersion)
66 if errors.Is(err, sql.ErrNoRows) {
67 return nil, nil
68 }
69 if err != nil {
70 return nil, err
71 }
72 return &i, nil
73}
74
75// labelFilter builds the EXISTS clause that keeps only rows carrying one of
76// the given labels. linkTable, linkColumn and parentTable are
77// "issue_labels", "issue_id", "issues" or the patch equivalents.
78func labelFilter(linkTable, linkColumn, parentTable string, labelIDs []int64, args []any) (string, []any) {
79 if len(labelIDs) == 0 {
80 return "", args
81 }
82 clause := ` AND EXISTS (SELECT 1 FROM ` + linkTable + ` WHERE ` + linkTable + `.` + linkColumn +
83 ` = ` + parentTable + `.id AND ` + linkTable + `.label_id IN (` + placeholders(len(labelIDs)) + `))`
84 return clause, append(args, int64Args(labelIDs)...)
85}
86
87// IssueCounts returns the number of issues per status, honouring the label filter.
88func (d *DB) IssueCounts(ctx context.Context, repoID int64, labelIDs []int64) (map[string]int, error) {
89 args := []any{repoID}
90 filter, args := labelFilter("issue_labels", "issue_id", "issues", labelIDs, args)
91 rows, err := d.QueryContext(ctx,
92 `SELECT issues.status, COUNT(*) FROM issues WHERE issues.repo_id = ?`+filter+
93 ` GROUP BY issues.status`, args...)
94 if err != nil {
95 return nil, err
96 }
97 defer rows.Close()
98 counts := map[string]int{}
99 for rows.Next() {
100 var status string
101 var n int
102 if err := rows.Scan(&status, &n); err != nil {
103 return nil, err
104 }
105 counts[status] = n
106 }
107 return counts, rows.Err()
108}
109
110// ListIssues returns one page of issues with the given status, newest number first.
111func (d *DB) ListIssues(ctx context.Context, repoID int64, status string, labelIDs []int64,
112 limit, offset int,
113) ([]Issue, error) {
114 args := []any{repoID, status}
115 filter, args := labelFilter("issue_labels", "issue_id", "issues", labelIDs, args)
116 args = append(args, limit, offset)
117 rows, err := d.QueryContext(ctx,
118 `SELECT `+issueColumns+` FROM issues
119 LEFT JOIN users ON users.id = issues.author_id
120 WHERE issues.repo_id = ? AND issues.status = ?`+filter+
121 ` ORDER BY issues.number DESC LIMIT ? OFFSET ?`, args...)
122 if err != nil {
123 return nil, err
124 }
125 defer rows.Close()
126 var out []Issue
127 for rows.Next() {
128 i, err := scanIssue(rows)
129 if err != nil {
130 return nil, err
131 }
132 out = append(out, *i)
133 }
134 return out, rows.Err()
135}
136
137func (d *DB) IssueByNumber(ctx context.Context, repoID, number int64) (*Issue, error) {
138 return scanIssue(d.QueryRowContext(ctx,
139 `SELECT `+issueColumns+` FROM issues
140 LEFT JOIN users ON users.id = issues.author_id
141 WHERE issues.repo_id = ? AND issues.number = ?`, repoID, number))
142}
143
144func (d *DB) IssueRefByNumber(ctx context.Context, repoID, number int64) (*IssueRef, error) {
145 var r IssueRef
146 err := d.QueryRowContext(ctx,
147 `SELECT id, author_id, status FROM issues WHERE repo_id = ? AND number = ?`,
148 repoID, number).Scan(&r.ID, &r.AuthorID, &r.Status)
149 if errors.Is(err, sql.ErrNoRows) {
150 return nil, nil
151 }
152 if err != nil {
153 return nil, err
154 }
155 return &r, nil
156}
157
158// CreateIssue allocates the next issue number and inserts the issue with its
159// labels in one transaction. Only labels belonging to the repo are attached.
160func (d *DB) CreateIssue(ctx context.Context, repoID int64, authorID *int64,
161 title, body, now string, labelIDs []int64,
162) (int64, error) {
163 tx, err := d.BeginTx(ctx, nil)
164 if err != nil {
165 return 0, err
166 }
167 defer tx.Rollback()
168
169 var number int64
170 if err := tx.QueryRowContext(ctx,
171 `UPDATE repositories SET issue_seq = issue_seq + 1 WHERE id = ? RETURNING issue_seq`,
172 repoID).Scan(&number); err != nil {
173 return 0, err
174 }
175 res, err := tx.ExecContext(ctx,
176 `INSERT INTO issues (repo_id, author_id, number, title, body, status, created_at, updated_at)
177 VALUES (?, ?, ?, ?, ?, 'open', ?, ?)`,
178 repoID, authorID, number, title, body, now, now)
179 if err != nil {
180 return 0, err
181 }
182 issueID, err := res.LastInsertId()
183 if err != nil {
184 return 0, err
185 }
186 if err := attachLabels(ctx, tx, "issue_labels", "issue_id", issueID, repoID, labelIDs); err != nil {
187 return 0, err
188 }
189 return number, tx.Commit()
190}
191
192// attachLabels links the valid subset of labelIDs to an issue or patch.
193func attachLabels(ctx context.Context, tx *sql.Tx, linkTable, linkColumn string,
194 parentID, repoID int64, labelIDs []int64,
195) error {
196 if len(labelIDs) == 0 {
197 return nil
198 }
199 args := append([]any{repoID}, int64Args(labelIDs)...)
200 rows, err := tx.QueryContext(ctx,
201 `SELECT id FROM labels WHERE repo_id = ? AND id IN (`+placeholders(len(labelIDs))+`)`, args...)
202 if err != nil {
203 return err
204 }
205 var valid []int64
206 for rows.Next() {
207 var id int64
208 if err := rows.Scan(&id); err != nil {
209 rows.Close()
210 return err
211 }
212 valid = append(valid, id)
213 }
214 rows.Close()
215 if err := rows.Err(); err != nil {
216 return err
217 }
218 for _, id := range valid {
219 if _, err := tx.ExecContext(ctx,
220 `INSERT INTO `+linkTable+` (`+linkColumn+`, label_id) VALUES (?, ?)
221 ON CONFLICT DO NOTHING`, parentID, id); err != nil {
222 return err
223 }
224 }
225 return nil
226}
227
228func (d *DB) UpdateIssue(ctx context.Context, id int64, title, body, now string) error {
229 _, err := d.ExecContext(ctx,
230 `UPDATE issues SET title = ?, body = ?, edited_at = ?, updated_at = ? WHERE id = ?`,
231 title, body, now, now, id)
232 return err
233}
234
235func (d *DB) CompleteIssue(ctx context.Context, id int64, now string) error {
236 _, err := d.ExecContext(ctx,
237 `UPDATE issues SET status = 'completed', updated_at = ? WHERE id = ?`, now, id)
238 return err
239}
240
241// ToggleIssueClosed flips an issue between open and closed.
242func (d *DB) ToggleIssueClosed(ctx context.Context, id int64, now string) error {
243 _, err := d.ExecContext(ctx,
244 `UPDATE issues SET status = CASE WHEN status = 'open' THEN 'closed' ELSE 'open' END,
245 updated_at = ? WHERE id = ?`, now, id)
246 return err
247}
248
249func (d *DB) DeleteIssue(ctx context.Context, id int64) error {
250 _, err := d.ExecContext(ctx, `DELETE FROM issues WHERE id = ?`, id)
251 return err
252}
253
254func (d *DB) ListIssueComments(ctx context.Context, issueID int64) ([]IssueComment, error) {
255 rows, err := d.QueryContext(ctx,
256 `SELECT issue_comments.id, issue_comments.issue_id, issue_comments.author_id,
257 issue_comments.body, issue_comments.created_at, issue_comments.edited_at,
258 users.username, users.avatar_version
259 FROM issue_comments
260 LEFT JOIN users ON users.id = issue_comments.author_id
261 WHERE issue_comments.issue_id = ?
262 ORDER BY issue_comments.created_at ASC`, issueID)
263 if err != nil {
264 return nil, err
265 }
266 defer rows.Close()
267 var out []IssueComment
268 for rows.Next() {
269 var c IssueComment
270 if err := rows.Scan(&c.ID, &c.IssueID, &c.AuthorID, &c.Body, &c.CreatedAt,
271 &c.EditedAt, &c.AuthorUsername, &c.AuthorAvatarVersion); err != nil {
272 return nil, err
273 }
274 out = append(out, c)
275 }
276 return out, rows.Err()
277}
278
279// AddIssueComment inserts a comment and bumps the issue's updated_at together.
280func (d *DB) AddIssueComment(ctx context.Context, issueID int64, authorID *int64, body, now string) error {
281 tx, err := d.BeginTx(ctx, nil)
282 if err != nil {
283 return err
284 }
285 defer tx.Rollback()
286 if _, err := tx.ExecContext(ctx,
287 `INSERT INTO issue_comments (issue_id, author_id, body, created_at) VALUES (?, ?, ?, ?)`,
288 issueID, authorID, body, now); err != nil {
289 return err
290 }
291 if _, err := tx.ExecContext(ctx,
292 `UPDATE issues SET updated_at = ? WHERE id = ?`, now, issueID); err != nil {
293 return err
294 }
295 return tx.Commit()
296}
297
298func (d *DB) UpdateIssueComment(ctx context.Context, id int64, body, now string) error {
299 _, err := d.ExecContext(ctx,
300 `UPDATE issue_comments SET body = ?, edited_at = ? WHERE id = ?`, body, now, id)
301 return err
302}
303
304// IssueCommentAuth loads the comment author and parent status in one query,
305// scoped to the repo so a comment from another repo cannot be edited.
306func (d *DB) IssueCommentAuth(ctx context.Context, commentID, repoID int64) (*CommentAuth, error) {
307 var a CommentAuth
308 err := d.QueryRowContext(ctx,
309 `SELECT issue_comments.author_id, issues.status
310 FROM issue_comments
311 JOIN issues ON issues.id = issue_comments.issue_id
312 WHERE issue_comments.id = ? AND issues.repo_id = ?`,
313 commentID, repoID).Scan(&a.AuthorID, &a.Status)
314 if errors.Is(err, sql.ErrNoRows) {
315 return nil, nil
316 }
317 if err != nil {
318 return nil, err
319 }
320 return &a, nil
321}
322
323func (d *DB) ListIssueReactions(ctx context.Context, issueID int64) ([]Reaction, error) {
324 rows, err := d.QueryContext(ctx,
325 `SELECT id, comment_id, user_id, emoji FROM issue_reactions WHERE issue_id = ?`, issueID)
326 if err != nil {
327 return nil, err
328 }
329 defer rows.Close()
330 return scanReactions(rows)
331}
332
333func scanReactions(rows *sql.Rows) ([]Reaction, error) {
334 var out []Reaction
335 for rows.Next() {
336 var r Reaction
337 if err := rows.Scan(&r.ID, &r.CommentID, &r.UserID, &r.Emoji); err != nil {
338 return nil, err
339 }
340 out = append(out, r)
341 }
342 return out, rows.Err()
343}
344
345// ToggleIssueReaction keeps one reaction per user per target: the same emoji
346// removes it, a different emoji replaces it.
347func (d *DB) ToggleIssueReaction(ctx context.Context, issueID int64, commentID *int64,
348 userID int64, emoji string,
349) error {
350 return d.toggleReaction(ctx, "issue_reactions", "issue_comments", "issue_id", issueID, commentID, userID, emoji)
351}
352
353// ErrCommentNotFound reports a reaction on a comment that is not part of the
354// target issue or patch.
355var ErrCommentNotFound = errors.New("comment not found")
356
357func (d *DB) toggleReaction(ctx context.Context, table, commentTable, parentColumn string, parentID int64,
358 commentID *int64, userID int64, emoji string,
359) error {
360 tx, err := d.BeginTx(ctx, nil)
361 if err != nil {
362 return err
363 }
364 defer tx.Rollback()
365
366 if commentID != nil {
367 var one int
368 err := tx.QueryRowContext(ctx,
369 `SELECT 1 FROM `+commentTable+` WHERE id = ? AND `+parentColumn+` = ?`,
370 *commentID, parentID).Scan(&one)
371 if errors.Is(err, sql.ErrNoRows) {
372 return ErrCommentNotFound
373 }
374 if err != nil {
375 return err
376 }
377 }
378
379 commentCond := `comment_id IS NULL`
380 args := []any{parentID}
381 if commentID != nil {
382 commentCond = `comment_id = ?`
383 args = append(args, *commentID)
384 }
385 args = append(args, userID)
386
387 var existingID int64
388 var existingEmoji string
389 err = tx.QueryRowContext(ctx,
390 `SELECT id, emoji FROM `+table+` WHERE `+parentColumn+` = ? AND `+commentCond+` AND user_id = ?`,
391 args...).Scan(&existingID, &existingEmoji)
392 switch {
393 case errors.Is(err, sql.ErrNoRows):
394 _, err = tx.ExecContext(ctx,
395 `INSERT INTO `+table+` (`+parentColumn+`, comment_id, user_id, emoji) VALUES (?, ?, ?, ?)`,
396 parentID, commentID, userID, emoji)
397 case err != nil:
398 return err
399 case existingEmoji == emoji:
400 _, err = tx.ExecContext(ctx, `DELETE FROM `+table+` WHERE id = ?`, existingID)
401 default:
402 _, err = tx.ExecContext(ctx, `UPDATE `+table+` SET emoji = ? WHERE id = ?`, emoji, existingID)
403 }
404 if err != nil {
405 return err
406 }
407 return tx.Commit()
408}
409
410// IssueLabels returns the labels attached to one issue.
411func (d *DB) IssueLabels(ctx context.Context, issueID int64) ([]Label, error) {
412 rows, err := d.QueryContext(ctx,
413 `SELECT labels.id, labels.repo_id, labels.name, labels.color, labels.created_at
414 FROM issue_labels
415 JOIN labels ON labels.id = issue_labels.label_id
416 WHERE issue_labels.issue_id = ?`, issueID)
417 if err != nil {
418 return nil, err
419 }
420 defer rows.Close()
421 return scanLabels(rows)
422}
423
424// IssueLabelsByIssue batch-loads labels for a list view.
425func (d *DB) IssueLabelsByIssue(ctx context.Context, issueIDs []int64) (map[int64][]Label, error) {
426 return d.labelsByParent(ctx, "issue_labels", "issue_id", issueIDs)
427}
428
429func (d *DB) labelsByParent(ctx context.Context, linkTable, linkColumn string,
430 parentIDs []int64,
431) (map[int64][]Label, error) {
432 out := map[int64][]Label{}
433 if len(parentIDs) == 0 {
434 return out, nil
435 }
436 rows, err := d.QueryContext(ctx,
437 `SELECT `+linkTable+`.`+linkColumn+`, labels.id, labels.repo_id, labels.name,
438 labels.color, labels.created_at
439 FROM `+linkTable+`
440 JOIN labels ON labels.id = `+linkTable+`.label_id
441 WHERE `+linkTable+`.`+linkColumn+` IN (`+placeholders(len(parentIDs))+`)`,
442 int64Args(parentIDs)...)
443 if err != nil {
444 return nil, err
445 }
446 defer rows.Close()
447 for rows.Next() {
448 var parentID int64
449 var l Label
450 if err := rows.Scan(&parentID, &l.ID, &l.RepoID, &l.Name, &l.Color, &l.CreatedAt); err != nil {
451 return nil, err
452 }
453 out[parentID] = append(out[parentID], l)
454 }
455 return out, rows.Err()
456}
457
458func (d *DB) AddIssueLabel(ctx context.Context, issueID, labelID int64) error {
459 _, err := d.ExecContext(ctx,
460 `INSERT INTO issue_labels (issue_id, label_id) VALUES (?, ?) ON CONFLICT DO NOTHING`,
461 issueID, labelID)
462 return err
463}
464
465func (d *DB) RemoveIssueLabel(ctx context.Context, issueID, labelID int64) error {
466 _, err := d.ExecContext(ctx,
467 `DELETE FROM issue_labels WHERE issue_id = ? AND label_id = ?`, issueID, labelID)
468 return err
469}
470