internal/store/savedqueries.go
301 lines · 9604 bytes
1package store
2
3import (
4 "database/sql"
5 "errors"
6 "fmt"
7 "strings"
8)
9
10// SavedQuery is a user's named issue and merge request query (#292).
11type SavedQuery struct {
12 Name string
13 Query string
14 Pinned bool
15 CreatedAt string
16 UpdatedAt string
17}
18
19// SaveQuery stores a query under name. replace allows overwriting one the
20// user already has; without it an existing name is ErrExists. Replacing
21// keeps the pin.
22func (s *Store) SaveQuery(userID int64, name, query string, replace bool) error {
23 if replace {
24 _, err := s.DB.Exec(`
25 INSERT INTO saved_queries (user_id, name, query) VALUES (?, ?, ?)
26 ON CONFLICT (user_id, name) DO UPDATE SET query = excluded.query,
27 updated_at = strftime('%Y-%m-%dT%H:%M:%fZ','now')`, userID, name, query)
28 return err
29 }
30 _, err := s.DB.Exec("INSERT INTO saved_queries (user_id, name, query) VALUES (?, ?, ?)", userID, name, query)
31 if isUniqueErr(err) {
32 return ErrExists
33 }
34 return err
35}
36
37func (s *Store) SavedQueryByName(userID int64, name string) (SavedQuery, error) {
38 var q SavedQuery
39 err := s.DB.QueryRow(`SELECT name, query, pinned, created_at, updated_at
40 FROM saved_queries WHERE user_id = ? AND name = ?`, userID, name).
41 Scan(&q.Name, &q.Query, &q.Pinned, &q.CreatedAt, &q.UpdatedAt)
42 if errors.Is(err, sql.ErrNoRows) {
43 return q, ErrNotFound
44 }
45 return q, err
46}
47
48// SavedQueries returns the user's queries by name; pinnedOnly narrows to
49// the ones the dashboard shows.
50func (s *Store) SavedQueries(userID int64, pinnedOnly bool) ([]SavedQuery, error) {
51 q := "SELECT name, query, pinned, created_at, updated_at FROM saved_queries WHERE user_id = ?"
52 if pinnedOnly {
53 q += " AND pinned = 1"
54 }
55 rows, err := s.DB.Query(q+" ORDER BY name", userID)
56 if err != nil {
57 return nil, err
58 }
59 defer rows.Close()
60 var out []SavedQuery
61 for rows.Next() {
62 var sq SavedQuery
63 if err := rows.Scan(&sq.Name, &sq.Query, &sq.Pinned, &sq.CreatedAt, &sq.UpdatedAt); err != nil {
64 return nil, err
65 }
66 out = append(out, sq)
67 }
68 return out, rows.Err()
69}
70
71// CountSavedQueries is how many queries the user has saved, and how many
72// of them are pinned.
73func (s *Store) CountSavedQueries(userID int64) (saved, pinned int, err error) {
74 err = s.DB.QueryRow("SELECT COUNT(*), COALESCE(SUM(pinned), 0) FROM saved_queries WHERE user_id = ?", userID).
75 Scan(&saved, &pinned)
76 return
77}
78
79func (s *Store) RemoveSavedQuery(userID int64, name string) error {
80 res, err := s.DB.Exec("DELETE FROM saved_queries WHERE user_id = ? AND name = ?", userID, name)
81 if err != nil {
82 return err
83 }
84 if n, _ := res.RowsAffected(); n == 0 {
85 return ErrNotFound
86 }
87 return nil
88}
89
90func (s *Store) PinSavedQuery(userID int64, name string, pinned bool) error {
91 res, err := s.DB.Exec("UPDATE saved_queries SET pinned = ? WHERE user_id = ? AND name = ?", pinned, userID, name)
92 if err != nil {
93 return err
94 }
95 if n, _ := res.RowsAffected(); n == 0 {
96 return ErrNotFound
97 }
98 return nil
99}
100
101// RepoScope is one repo:/owner: term. Name is a GLOB pattern; "" means
102// every repository of Owner.
103type RepoScope struct {
104 Owner string
105 Name string
106}
107
108// ItemFilter is a parsed query, ready to run. Usernames are resolved
109// (@me is the caller's name by now). Empty fields match anything.
110type ItemFilter struct {
111 Scopes []RepoScope // any of them; none means every repository
112 Issues, MRs bool // which tables to read
113 State string // open, closed, merged
114 Labels []string // all of them
115 NoLabel bool
116 Milestone string
117 NoMilestone bool
118 Assignee string // issues only
119 Author string
120 Text string // full-text over title and body
121}
122
123// ItemCursor is the sort key of the last row of a page: rows are newest
124// first by creation, merge requests before issues at the same instant,
125// then by id, highest first.
126type ItemCursor struct {
127 CreatedAt string
128 Kind int // 0 issue, 1 merge request
129 ID int64
130}
131
132// Item is one row of a cross-repository query.
133type Item struct {
134 Kind string // issue or mr
135 ID int64
136 RepoPath string
137 Number int64
138 Title string
139 Author string
140 State string
141 Draft bool
142 Milestone string
143 CreatedAt string
144 UpdatedAt string
145}
146
147// Cursor is the key a page ending on this row continues from.
148func (it Item) Cursor() ItemCursor {
149 k := 0
150 if it.Kind == "mr" {
151 k = 1
152 }
153 return ItemCursor{it.CreatedAt, k, it.ID}
154}
155
156// numbered collects arguments for SQL written with ?N placeholders, which
157// visibleCond needs: it names the user as ?1 several times.
158type numbered struct{ args []any }
159
160func (n *numbered) add(v any) string {
161 n.args = append(n.args, v)
162 return fmt.Sprintf("?%d", len(n.args))
163}
164
165// itemRepos is the subquery naming the repositories a query reads: those
166// the user may read (visibleCond, public or reached) within f's scopes.
167// Deciding readability per repository rather than per row lets each
168// table be read through its repo_id index. userID is ?1 in a.
169func itemRepos(f ItemFilter, a *numbered) string {
170 where := []string{visibleCond}
171 if len(f.Scopes) > 0 {
172 var scopes []string
173 for _, sc := range f.Scopes {
174 cond := "COALESCE(u.username, o.name) = " + a.add(sc.Owner)
175 if sc.Name != "" {
176 cond += " AND r.name GLOB " + a.add(sc.Name)
177 }
178 scopes = append(scopes, "("+cond+")")
179 }
180 where = append(where, "("+strings.Join(scopes, " OR ")+")")
181 }
182 return `SELECT r.id FROM repos r
183 LEFT JOIN users u ON r.owner_kind = 'user' AND u.id = r.owner_id
184 LEFT JOIN orgs o ON r.owner_kind = 'org' AND o.id = r.owner_id
185 WHERE ` + strings.Join(where, " AND ")
186}
187
188// itemBranch is one table's half of the query: every row of it in one of
189// the repositories repos names that f admits.
190func itemBranch(kind string, f ItemFilter, a *numbered, repos string, after *ItemCursor) string {
191 table, kord, labels, draft := "issues", "0", "issue_labels il", "0"
192 onItem := "il.issue_id = x.id"
193 if kind == "mr" {
194 table, kord, labels, draft = "merge_requests", "1", "mr_labels il", "x.draft"
195 onItem = "il.mr_id = x.id"
196 }
197 where := []string{"x.repo_id IN (" + repos + ")"}
198 switch {
199 case f.State == "":
200 case f.State == "open" && kind == "mr":
201 where = append(where, "x.state IN ('open', 'source_gone')")
202 default:
203 where = append(where, "x.state = "+a.add(f.State))
204 }
205 for _, l := range f.Labels {
206 where = append(where, "EXISTS (SELECT 1 FROM "+labels+" JOIN labels l ON l.id = il.label_id WHERE "+onItem+" AND l.name = "+a.add(l)+")")
207 }
208 if f.NoLabel {
209 where = append(where, "NOT EXISTS (SELECT 1 FROM "+labels+" WHERE "+onItem+")")
210 }
211 if f.NoMilestone {
212 where = append(where, "x.milestone_id IS NULL")
213 } else if f.Milestone != "" {
214 where = append(where, "ms.title = "+a.add(f.Milestone))
215 }
216 if f.Assignee != "" {
217 where = append(where, `EXISTS (SELECT 1 FROM issue_assignees ia JOIN users iu ON iu.id = ia.user_id
218 WHERE ia.issue_id = x.id AND iu.username = `+a.add(f.Assignee)+")")
219 }
220 if f.Author != "" {
221 where = append(where, "au.username = "+a.add(f.Author))
222 }
223 if f.Text != "" {
224 index := "issue_fts"
225 if kind == "mr" {
226 index = "mr_fts"
227 }
228 where = append(where, "x.id IN (SELECT rowid FROM "+index+" WHERE "+index+" MATCH "+a.add(FTSQuery(f.Text))+")")
229 }
230 if after != nil {
231 where = append(where, "(x.created_at, "+kord+", x.id) < ("+a.add(after.CreatedAt)+", "+a.add(after.Kind)+", "+a.add(after.ID)+")")
232 }
233 return `SELECT '` + kind + `' AS kind, ` + kord + ` AS kord, x.id AS id,
234 COALESCE(u.username, o.name) || '/' || r.name, x.number, x.title, au.username,
235 x.state, ` + draft + `, COALESCE(ms.title, ''), x.created_at AS created_at, x.updated_at
236 FROM ` + table + ` x
237 JOIN repos r ON r.id = x.repo_id
238 LEFT JOIN users u ON r.owner_kind = 'user' AND u.id = r.owner_id
239 LEFT JOIN orgs o ON r.owner_kind = 'org' AND o.id = r.owner_id
240 JOIN users au ON au.id = x.author_id
241 LEFT JOIN milestones ms ON ms.id = x.milestone_id
242 WHERE ` + strings.Join(where, "\n\t AND ")
243}
244
245// itemUnion is the query over both tables f reads, or "" when it reads
246// neither.
247func itemUnion(userID int64, f ItemFilter, after *ItemCursor) (string, []any) {
248 a := &numbered{}
249 a.add(userID)
250 repos := itemRepos(f, a)
251 var parts []string
252 if f.Issues {
253 parts = append(parts, itemBranch("issue", f, a, repos, after))
254 }
255 if f.MRs {
256 parts = append(parts, itemBranch("mr", f, a, repos, after))
257 }
258 return strings.Join(parts, "\nUNION ALL\n"), a.args
259}
260
261// QueryItems runs f for the user across every repository they may read,
262// newest first. after continues from a page's last row; limit 0 means
263// every row.
264func (s *Store) QueryItems(userID int64, f ItemFilter, after *ItemCursor, limit int) ([]Item, error) {
265 q, args := itemUnion(userID, f, after)
266 if q == "" {
267 return nil, nil
268 }
269 q = "SELECT * FROM (" + q + ") ORDER BY created_at DESC, kord DESC, id DESC"
270 if limit > 0 {
271 q += fmt.Sprintf(" LIMIT %d", limit)
272 }
273 rows, err := s.DB.Query(q, args...)
274 if err != nil {
275 return nil, err
276 }
277 defer rows.Close()
278 var out []Item
279 for rows.Next() {
280 var it Item
281 var kord int
282 if err := rows.Scan(&it.Kind, &kord, &it.ID, &it.RepoPath, &it.Number, &it.Title, &it.Author,
283 &it.State, &it.Draft, &it.Milestone, &it.CreatedAt, &it.UpdatedAt); err != nil {
284 return nil, err
285 }
286 out = append(out, it)
287 }
288 return out, rows.Err()
289}
290
291// CountItems is how many rows QueryItems would return without a limit,
292// over the same readable repositories.
293func (s *Store) CountItems(userID int64, f ItemFilter) (int, error) {
294 q, args := itemUnion(userID, f, nil)
295 if q == "" {
296 return 0, nil
297 }
298 var n int
299 err := s.DB.QueryRow("SELECT COUNT(*) FROM ("+q+")", args...).Scan(&n)
300 return n, err
301}