internal/store/issues.go
438 lines · 13236 bytes
1package store
2
3import (
4 "database/sql"
5 "errors"
6 "fmt"
7 "strings"
8)
9
10type Issue struct {
11 ID int64
12 RepoID int64
13 Number int64
14 Author string
15 Title string
16 Body string
17 BodyFormat string // md | org
18 State string // open | closed
19 Milestone string
20 CreatedAt string
21 UpdatedAt string
22 Labels []string
23 Assignees []string
24}
25
26// IssueCommentCounts counts, per issue, the comments people wrote —
27// system comments do not count — for every issue on a list page in one
28// query.
29func (s *Store) IssueCommentCounts(issueIDs []int64) (map[int64]int, error) {
30 out := map[int64]int{}
31 if len(issueIDs) == 0 {
32 return out, nil
33 }
34 args := make([]any, len(issueIDs))
35 for i, id := range issueIDs {
36 args[i] = id
37 }
38 rows, err := s.DB.Query(`
39 SELECT issue_id, COUNT(*) FROM issue_comments
40 WHERE kind <> 'system' AND issue_id IN (?`+strings.Repeat(",?", len(issueIDs)-1)+`)
41 GROUP BY issue_id`, args...)
42 if err != nil {
43 return nil, err
44 }
45 defer rows.Close()
46 for rows.Next() {
47 var id int64
48 var n int
49 if err := rows.Scan(&id, &n); err != nil {
50 return nil, err
51 }
52 out[id] = n
53 }
54 return out, rows.Err()
55}
56
57type IssueComment struct {
58 ID int64
59 Author string
60 Body string
61 BodyFormat string // md | org
62 CreatedAt string
63 Kind string // comment | system
64}
65
66// CreateIssue allocates the per-repo number from the repo counter inside the
67// same transaction as the insert — MAX(number)+1 races.
68func (s *Store) CreateIssue(repoID, authorID int64, title, body, format string) (int64, error) {
69 tx, err := s.DB.Begin()
70 if err != nil {
71 return 0, err
72 }
73 defer tx.Rollback()
74 if _, err := tx.Exec("UPDATE repos SET issue_counter = issue_counter + 1 WHERE id = ?", repoID); err != nil {
75 return 0, err
76 }
77 var n int64
78 if err := tx.QueryRow("SELECT issue_counter FROM repos WHERE id = ?", repoID).Scan(&n); err != nil {
79 return 0, err
80 }
81 if _, err := tx.Exec(
82 "INSERT INTO issues (repo_id, number, author_id, title, body, body_format) VALUES (?, ?, ?, ?, ?, ?)",
83 repoID, n, authorID, title, body, format); err != nil {
84 return 0, err
85 }
86 return n, tx.Commit()
87}
88
89func (s *Store) IssueByNumber(repoID, number int64) (Issue, error) {
90 var i Issue
91 err := s.DB.QueryRow(`
92 SELECT i.id, i.repo_id, i.number, u.username, i.title, i.body, i.body_format, i.state,
93 COALESCE(m.title, ''), i.created_at, i.updated_at
94 FROM issues i JOIN users u ON u.id = i.author_id
95 LEFT JOIN milestones m ON m.id = i.milestone_id
96 WHERE i.repo_id = ? AND i.number = ?`, repoID, number).
97 Scan(&i.ID, &i.RepoID, &i.Number, &i.Author, &i.Title, &i.Body, &i.BodyFormat, &i.State, &i.Milestone, &i.CreatedAt, &i.UpdatedAt)
98 if errors.Is(err, sql.ErrNoRows) {
99 return i, ErrNotFound
100 }
101 if err != nil {
102 return i, err
103 }
104 if i.Labels, err = s.issueStrings(i.ID, `
105 SELECT l.name FROM issue_labels il JOIN labels l ON l.id = il.label_id
106 WHERE il.issue_id = ? ORDER BY l.name`); err != nil {
107 return i, err
108 }
109 i.Assignees, err = s.issueStrings(i.ID, `
110 SELECT u.username FROM issue_assignees ia JOIN users u ON u.id = ia.user_id
111 WHERE ia.issue_id = ? ORDER BY u.username`)
112 return i, err
113}
114
115// IssueLabelsFor is the labels and assignees of every issue on a list
116// page, two queries for the page rather than two per issue.
117func (s *Store) IssueLabelsFor(issueIDs []int64) (labels, assignees map[int64][]string, err error) {
118 if labels, err = s.stringsFor(issueIDs, `
119 SELECT il.issue_id, l.name FROM issue_labels il JOIN labels l ON l.id = il.label_id
120 WHERE il.issue_id IN (%s) ORDER BY l.name`); err != nil {
121 return nil, nil, err
122 }
123 assignees, err = s.stringsFor(issueIDs, `
124 SELECT ia.issue_id, u.username FROM issue_assignees ia JOIN users u ON u.id = ia.user_id
125 WHERE ia.issue_id IN (%s) ORDER BY u.username`)
126 return labels, assignees, err
127}
128
129// MRReviewRequestsFor is ReviewRequests for every merge request on a
130// list page in one query.
131func (s *Store) MRReviewRequestsFor(mrIDs []int64) (map[int64][]string, error) {
132 return s.stringsFor(mrIDs, `
133 SELECT rr.mr_id, u.username FROM mr_review_requests rr JOIN users u ON u.id = rr.user_id
134 WHERE rr.mr_id IN (%s) ORDER BY u.username`)
135}
136
137// stringsFor runs a query selecting (id, value) rows, its %s replaced by
138// one placeholder per id, and groups the values by id in row order.
139func (s *Store) stringsFor(ids []int64, query string) (map[int64][]string, error) {
140 out := map[int64][]string{}
141 if len(ids) == 0 {
142 return out, nil
143 }
144 args := make([]any, len(ids))
145 for i, id := range ids {
146 args[i] = id
147 }
148 rows, err := s.DB.Query(fmt.Sprintf(query, "?"+strings.Repeat(",?", len(ids)-1)), args...)
149 if err != nil {
150 return nil, err
151 }
152 defer rows.Close()
153 for rows.Next() {
154 var id int64
155 var v string
156 if err := rows.Scan(&id, &v); err != nil {
157 return nil, err
158 }
159 out[id] = append(out[id], v)
160 }
161 return out, rows.Err()
162}
163
164func (s *Store) issueStrings(issueID int64, query string) ([]string, error) {
165 rows, err := s.DB.Query(query, issueID)
166 if err != nil {
167 return nil, err
168 }
169 defer rows.Close()
170 var out []string
171 for rows.Next() {
172 var v string
173 if err := rows.Scan(&v); err != nil {
174 return nil, err
175 }
176 out = append(out, v)
177 }
178 return out, rows.Err()
179}
180
181// ListIssues returns issues for a repo; state is "open", "closed", or
182// "all". limit 0 means everything; before (an issue number) starts the
183// page strictly below it, matching the number-descending order.
184// IssueFilter narrows a listing. Empty strings match anything; State
185// "all" too. Milestone "none" selects issues with no milestone.
186type IssueFilter struct {
187 State string
188 Label string
189 Assignee string
190 Author string
191 Milestone string
192 Search string // full-text over title and body
193 Limit int
194 Before int64
195}
196
197func (s *Store) ListIssues(repoID int64, state string, limit int, before int64) ([]Issue, error) {
198 return s.QueryIssues(repoID, IssueFilter{State: state, Limit: limit, Before: before})
199}
200
201// QueryIssues lists a repository's issues, newest first, narrowed by f.
202func (s *Store) QueryIssues(repoID int64, f IssueFilter) ([]Issue, error) {
203 q := `SELECT i.id, i.repo_id, i.number, u.username, i.title, i.body, i.body_format, i.state,
204 COALESCE(m.title, ''), i.created_at, i.updated_at
205 FROM issues i JOIN users u ON u.id = i.author_id
206 LEFT JOIN milestones m ON m.id = i.milestone_id
207 WHERE i.repo_id = ?`
208 args := []any{repoID}
209 if f.State != "" && f.State != "all" {
210 q += " AND i.state = ?"
211 args = append(args, f.State)
212 }
213 if f.Label != "" {
214 q += ` AND EXISTS (SELECT 1 FROM issue_labels il JOIN labels l ON l.id = il.label_id
215 WHERE il.issue_id = i.id AND l.name = ?)`
216 args = append(args, f.Label)
217 }
218 if f.Assignee != "" {
219 q += ` AND EXISTS (SELECT 1 FROM issue_assignees ia JOIN users au ON au.id = ia.user_id
220 WHERE ia.issue_id = i.id AND au.username = ?)`
221 args = append(args, f.Assignee)
222 }
223 if f.Author != "" {
224 q += " AND u.username = ?"
225 args = append(args, f.Author)
226 }
227 switch f.Milestone {
228 case "":
229 case "none":
230 q += " AND i.milestone_id IS NULL"
231 default:
232 q += " AND m.title = ?"
233 args = append(args, f.Milestone)
234 }
235 if f.Search != "" {
236 q += " AND i.id IN (SELECT rowid FROM issue_fts WHERE issue_fts MATCH ?)"
237 args = append(args, FTSQuery(f.Search))
238 }
239 if f.Before > 0 {
240 q += " AND i.number < ?"
241 args = append(args, f.Before)
242 }
243 q += " ORDER BY i.number DESC"
244 if f.Limit > 0 {
245 q += " LIMIT ?"
246 args = append(args, f.Limit)
247 }
248 rows, err := s.DB.Query(q, args...)
249 if err != nil {
250 return nil, err
251 }
252 defer rows.Close()
253 var out []Issue
254 for rows.Next() {
255 var i Issue
256 if err := rows.Scan(&i.ID, &i.RepoID, &i.Number, &i.Author, &i.Title, &i.Body, &i.BodyFormat, &i.State, &i.Milestone, &i.CreatedAt, &i.UpdatedAt); err != nil {
257 return nil, err
258 }
259 out = append(out, i)
260 }
261 return out, rows.Err()
262}
263
264// UpdateIssueText edits title, body, and/or markup format; nil leaves a field
265// unchanged.
266func (s *Store) UpdateIssueText(issueID int64, title, body, format *string) error {
267 set, args := []string{}, []any{}
268 if title != nil {
269 set, args = append(set, "title = ?"), append(args, *title)
270 }
271 if body != nil {
272 set, args = append(set, "body = ?"), append(args, *body)
273 }
274 if format != nil {
275 set, args = append(set, "body_format = ?"), append(args, *format)
276 }
277 if len(set) == 0 {
278 return nil
279 }
280 set = append(set, "updated_at = strftime('%Y-%m-%dT%H:%M:%fZ','now')")
281 args = append(args, issueID)
282 res, err := s.DB.Exec("UPDATE issues SET "+strings.Join(set, ", ")+" WHERE id = ?", args...)
283 if err != nil {
284 return err
285 }
286 if n, _ := res.RowsAffected(); n == 0 {
287 return ErrNotFound
288 }
289 return nil
290}
291
292func (s *Store) SetIssueState(issueID int64, state string) error {
293 res, err := s.DB.Exec(
294 "UPDATE issues SET state = ?, updated_at = strftime('%Y-%m-%dT%H:%M:%fZ','now') WHERE id = ?",
295 state, issueID)
296 if err != nil {
297 return err
298 }
299 if n, _ := res.RowsAffected(); n == 0 {
300 return ErrNotFound
301 }
302 return nil
303}
304
305func (s *Store) AddIssueComment(issueID, authorID int64, body, format string) error {
306 tx, err := s.DB.Begin()
307 if err != nil {
308 return err
309 }
310 defer tx.Rollback()
311 if _, err := tx.Exec(
312 "INSERT INTO issue_comments (issue_id, author_id, body, body_format) VALUES (?, ?, ?, ?)",
313 issueID, authorID, body, format); err != nil {
314 return err
315 }
316 if _, err := tx.Exec(
317 "UPDATE issues SET updated_at = strftime('%Y-%m-%dT%H:%M:%fZ','now') WHERE id = ?", issueID); err != nil {
318 return err
319 }
320 return tx.Commit()
321}
322
323func (s *Store) ListIssueComments(issueID int64) ([]IssueComment, error) {
324 rows, err := s.DB.Query(`
325 SELECT c.id, CASE WHEN c.kind = 'system' THEN 'system' ELSE u.username END,
326 c.body, c.body_format, c.created_at, c.kind
327 FROM issue_comments c JOIN users u ON u.id = c.author_id
328 WHERE c.issue_id = ? ORDER BY c.id`, issueID)
329 if err != nil {
330 return nil, err
331 }
332 defer rows.Close()
333 var out []IssueComment
334 for rows.Next() {
335 var c IssueComment
336 if err := rows.Scan(&c.ID, &c.Author, &c.Body, &c.BodyFormat, &c.CreatedAt, &c.Kind); err != nil {
337 return nil, err
338 }
339 out = append(out, c)
340 }
341 return out, rows.Err()
342}
343
344// AddIssueSystemComment records an informational entry (commit references,
345// automated closes). The actor is kept for provenance but the entry
346// displays as coming from the system, not the user.
347func (s *Store) AddIssueSystemComment(issueID, actorID int64, body string) error {
348 _, err := s.DB.Exec(
349 "INSERT INTO issue_comments (issue_id, author_id, body, kind) VALUES (?, ?, ?, 'system')",
350 issueID, actorID, body)
351 return err
352}
353
354// ListIssueLabels returns the label names attached to each issue of a
355// repo, keyed by issue id, its org's labels included. Used by the web
356// issue listing; ListIssues itself stays label-free for the CLI's lean
357// list output.
358func (s *Store) ListIssueLabels(repo Repo) (map[int64][]string, error) {
359 return s.listItemLabels(issueLabelJoin, repo)
360}
361
362// LabelColors returns the colours of the labels a repository sees, keyed
363// by name. Labels with no stored colour map to "".
364func (s *Store) LabelColors(repo Repo) (map[string]string, error) {
365 where, args := scopeClause("l", repo)
366 rows, err := s.DB.Query("SELECT l.name, l.color FROM labels l WHERE "+where, args...)
367 if err != nil {
368 return nil, err
369 }
370 defer rows.Close()
371 out := map[string]string{}
372 for rows.Next() {
373 var name, color string
374 if err := rows.Scan(&name, &color); err != nil {
375 return nil, err
376 }
377 out[name] = color
378 }
379 return out, rows.Err()
380}
381
382// SetIssueLabel attaches (add) or detaches a label by name. Adding
383// resolves the org's row when the org has the name, else the repository's,
384// creating that on first use.
385func (s *Store) SetIssueLabel(repo Repo, issueID int64, name string, add bool) error {
386 return s.setItemLabel(issueLabelJoin, repo, issueID, name, add)
387}
388
389// SetIssueAssignee adds or removes an assignee by user id.
390func (s *Store) SetIssueAssignee(issueID, userID int64, add bool) error {
391 if add {
392 _, err := s.DB.Exec(
393 "INSERT INTO issue_assignees (issue_id, user_id) VALUES (?, ?) ON CONFLICT DO NOTHING",
394 issueID, userID)
395 return err
396 }
397 res, err := s.DB.Exec(
398 "DELETE FROM issue_assignees WHERE issue_id = ? AND user_id = ?", issueID, userID)
399 if err != nil {
400 return err
401 }
402 if n, _ := res.RowsAffected(); n == 0 {
403 return ErrNotFound
404 }
405 return nil
406}
407
408// RecordEvent appends to the event log and enqueues a delivery for every
409// active webhook on the repo whose event filter matches.
410func (s *Store) RecordEvent(repoID, actorID int64, kind, dataJSON string) error {
411 if dataJSON == "" {
412 dataJSON = "{}"
413 }
414 tx, err := s.DB.Begin()
415 if err != nil {
416 return err
417 }
418 defer tx.Rollback()
419 res, err := tx.Exec(
420 "INSERT INTO events (repo_id, actor_id, kind, data_json) VALUES (?, ?, ?, ?)",
421 repoID, actorID, kind, dataJSON)
422 if err != nil {
423 return err
424 }
425 eventID, err := res.LastInsertId()
426 if err != nil {
427 return err
428 }
429 if _, err := tx.Exec(`
430 INSERT INTO webhook_deliveries (webhook_id, event_id)
431 SELECT id, ? FROM webhooks
432 WHERE repo_id = ? AND active = 1
433 AND (events = '*' OR ',' || events || ',' LIKE '%,' || ? || ',%')`,
434 eventID, repoID, kind); err != nil {
435 return err
436 }
437 return tx.Commit()
438}