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