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