internal/store/dashboard.go
397 lines · 14230 bytes
1package store
2
3import "sort"
4
5// DashboardItem is one open issue or MR row on the logged-in homepage.
6type DashboardItem struct {
7 RepoPath string
8 Number int64
9 Title string
10 Author string
11 State string
12 UpdatedAt string
13 Queued bool // a merge request with a queued merge
14}
15
16// reachableCond filters to repositories the user owns, is granted on, or
17// reaches through org or team membership.
18const reachableCond = `(
19 (r.owner_kind = 'user' AND r.owner_id = ?1)
20 OR EXISTS (SELECT 1 FROM repo_access a
21 WHERE a.repo_id = r.id AND a.subject_kind = 'user' AND a.subject_id = ?1)
22 OR EXISTS (SELECT 1 FROM org_members mm
23 JOIN orgs oo ON oo.id = mm.org_id
24 WHERE r.owner_kind = 'org' AND mm.org_id = r.owner_id AND mm.user_id = ?1
25 AND (mm.role = 'admin' OR oo.members_role <> 'none'))
26 OR EXISTS (SELECT 1 FROM team_repos tr
27 JOIN team_members tm ON tm.team_id = tr.team_id AND tm.user_id = ?1
28 WHERE tr.repo_id = r.id)
29)`
30
31// involvedCond widens reachableCond to rows the user authored anywhere.
32const involvedCond = `(x.author_id = ?1 OR ` + reachableCond + `)`
33
34func (s *Store) dashboardQuery(q string, userID int64) ([]DashboardItem, error) {
35 rows, err := s.DB.Query(q, userID)
36 if err != nil {
37 return nil, err
38 }
39 defer rows.Close()
40 var out []DashboardItem
41 for rows.Next() {
42 var d DashboardItem
43 if err := rows.Scan(&d.RepoPath, &d.Number, &d.Title, &d.Author, &d.State, &d.UpdatedAt, &d.Queued); err != nil {
44 return nil, err
45 }
46 out = append(out, d)
47 }
48 return out, rows.Err()
49}
50
51// The dashboard's four list queries are named so the plan test can assert
52// each still walks the 0035 index that supplies its ORDER BY.
53const dashboardMRsQuery = `
54 SELECT COALESCE(u.username, o.name) || '/' || r.name,
55 x.number, x.title, au.username, x.state, x.updated_at,
56 EXISTS (SELECT 1 FROM mr_merge_queue q WHERE q.mr_id = x.id)
57 FROM merge_requests x
58 JOIN repos r ON r.id = x.repo_id
59 LEFT JOIN users u ON r.owner_kind = 'user' AND u.id = r.owner_id
60 LEFT JOIN orgs o ON r.owner_kind = 'org' AND o.id = r.owner_id
61 JOIN users au ON au.id = x.author_id
62 WHERE x.state IN ('open', 'source_gone') AND ` + involvedCond + `
63 ORDER BY x.updated_at DESC, x.id DESC LIMIT 50`
64
65// DashboardMRs returns open merge requests involving the user: on their
66// repositories (owned, granted, org) or authored by them anywhere.
67func (s *Store) DashboardMRs(userID int64) ([]DashboardItem, error) {
68 return s.dashboardQuery(dashboardMRsQuery, userID)
69}
70
71// DashboardIssues is the issue counterpart of DashboardMRs.
72const dashboardIssuesQuery = `
73 SELECT COALESCE(u.username, o.name) || '/' || r.name,
74 x.number, x.title, au.username, x.state, x.updated_at, 0
75 FROM issues x
76 JOIN repos r ON r.id = x.repo_id
77 LEFT JOIN users u ON r.owner_kind = 'user' AND u.id = r.owner_id
78 LEFT JOIN orgs o ON r.owner_kind = 'org' AND o.id = r.owner_id
79 JOIN users au ON au.id = x.author_id
80 WHERE x.state = 'open' AND ` + involvedCond + `
81 AND NOT EXISTS (SELECT 1 FROM issue_assignees ia
82 WHERE ia.issue_id = x.id AND ia.user_id = ?1)
83 ORDER BY x.updated_at DESC, x.id DESC LIMIT 50`
84
85func (s *Store) DashboardIssues(userID int64) ([]DashboardItem, error) {
86 return s.dashboardQuery(dashboardIssuesQuery, userID)
87}
88
89func (s *Store) PinRepo(userID, repoID int64) error {
90 _, err := s.DB.Exec(
91 "INSERT INTO repo_pins (user_id, repo_id) VALUES (?, ?) ON CONFLICT DO NOTHING",
92 userID, repoID)
93 return err
94}
95
96func (s *Store) IsPinned(userID, repoID int64) bool {
97 var n int
98 s.DB.QueryRow("SELECT COUNT(*) FROM repo_pins WHERE user_id = ? AND repo_id = ?",
99 userID, repoID).Scan(&n)
100 return n > 0
101}
102
103func (s *Store) UnpinRepo(userID, repoID int64) error {
104 res, err := s.DB.Exec(
105 "DELETE FROM repo_pins WHERE user_id = ? AND repo_id = ?", userID, repoID)
106 if err != nil {
107 return err
108 }
109 if n, _ := res.RowsAffected(); n == 0 {
110 return ErrNotFound
111 }
112 return nil
113}
114
115// PinnedRepos returns the user's pinned repositories in pin order. The
116// caller applies visibility checks before rendering.
117func (s *Store) PinnedRepos(userID int64) ([]Repo, error) {
118 rows, err := s.DB.Query(repoSelect+`
119 JOIN repo_pins p ON p.repo_id = r.id AND p.user_id = ?
120 ORDER BY p.pinned_at`, userID)
121 if err != nil {
122 return nil, err
123 }
124 defer rows.Close()
125 var out []Repo
126 for rows.Next() {
127 r, err := scanRepo(rows)
128 if err != nil {
129 return nil, err
130 }
131 out = append(out, r)
132 }
133 return out, rows.Err()
134}
135
136// reviewQueueQuery is ReviewQueue's involved half: open merge requests the
137// user is involved in, has not authored, and has not reviewed at the
138// current head.
139const reviewQueueQuery = `
140 SELECT COALESCE(u.username, o.name) || '/' || r.name,
141 x.number, x.title, au.username, x.state, x.updated_at,
142 EXISTS (SELECT 1 FROM mr_merge_queue q WHERE q.mr_id = x.id)
143 FROM merge_requests x
144 JOIN repos r ON r.id = x.repo_id
145 LEFT JOIN users u ON r.owner_kind = 'user' AND u.id = r.owner_id
146 LEFT JOIN orgs o ON r.owner_kind = 'org' AND o.id = r.owner_id
147 JOIN users au ON au.id = x.author_id
148 WHERE x.state IN ('open', 'source_gone')
149 AND x.draft = 0
150 AND x.author_id <> ?1
151 AND NOT EXISTS (SELECT 1 FROM mr_reviews rv
152 WHERE rv.mr_id = x.id AND rv.reviewer_id = ?1
153 AND rv.head_sha = x.head_sha)
154 AND ` + involvedCond + `
155 ORDER BY x.updated_at DESC, x.id DESC LIMIT 8`
156
157// requestedReviewsQuery is ReviewQueue's other half: MRs where the user was
158// asked directly, regardless of involvement — the same exemption
159// AssignedIssues gives assignment, and for the same reason (dashboard.go
160// above). It drives from mr_review_requests rather than testing EXISTS
161// against every merge request: one user's requests are a handful, the
162// merge_requests table is the whole instance.
163const requestedReviewsQuery = `
164 SELECT COALESCE(u.username, o.name) || '/' || r.name,
165 x.number, x.title, au.username, x.state, x.updated_at,
166 EXISTS (SELECT 1 FROM mr_merge_queue q WHERE q.mr_id = x.id)
167 FROM mr_review_requests rr
168 JOIN merge_requests x ON x.id = rr.mr_id
169 JOIN repos r ON r.id = x.repo_id
170 LEFT JOIN users u ON r.owner_kind = 'user' AND u.id = r.owner_id
171 LEFT JOIN orgs o ON r.owner_kind = 'org' AND o.id = r.owner_id
172 JOIN users au ON au.id = x.author_id
173 WHERE rr.user_id = ?1
174 AND x.state IN ('open', 'source_gone')
175 AND x.draft = 0
176 AND x.author_id <> ?1
177 AND NOT EXISTS (SELECT 1 FROM mr_reviews rv
178 WHERE rv.mr_id = x.id AND rv.reviewer_id = ?1
179 AND rv.head_sha = x.head_sha)
180 ORDER BY x.updated_at DESC, x.id DESC LIMIT 8`
181
182// ReviewQueue returns open merge requests the user is involved in, has not
183// authored, and has not reviewed at the current head — what the rail shows
184// as waiting on them — unioned with merge requests where they were asked
185// directly. Both halves drop an MR once its current head has been
186// reviewed, so a requested reviewer's queue empties the same way an
187// involved one's does. Ordered most recently touched first.
188func (s *Store) ReviewQueue(userID int64) ([]DashboardItem, error) {
189 involved, err := s.dashboardQuery(reviewQueueQuery, userID)
190 if err != nil {
191 return nil, err
192 }
193 requested, err := s.dashboardQuery(requestedReviewsQuery, userID)
194 if err != nil {
195 return nil, err
196 }
197 type key struct {
198 repo string
199 n int64
200 }
201 seen := make(map[key]bool, len(involved))
202 out := make([]DashboardItem, 0, len(involved)+len(requested))
203 for _, d := range involved {
204 seen[key{d.RepoPath, d.Number}] = true
205 out = append(out, d)
206 }
207 for _, d := range requested {
208 k := key{d.RepoPath, d.Number}
209 if !seen[k] {
210 seen[k] = true
211 out = append(out, d)
212 }
213 }
214 sort.SliceStable(out, func(i, j int) bool { return out[i].UpdatedAt > out[j].UpdatedAt })
215 if len(out) > 8 {
216 out = out[:8]
217 }
218 return out, nil
219}
220
221// OpenCounts returns the repo's open issue and open merge request counts,
222// for the repo tab badges.
223func (s *Store) OpenCounts(repoID int64) (issues, mrs int) {
224 s.DB.QueryRow("SELECT COUNT(*) FROM issues WHERE repo_id = ? AND state = 'open'",
225 repoID).Scan(&issues)
226 s.DB.QueryRow("SELECT COUNT(*) FROM merge_requests WHERE repo_id = ? AND state IN ('open', 'source_gone')",
227 repoID).Scan(&mrs)
228 return
229}
230
231// AssignedIssues returns open issues assigned to the user, wherever they
232// live. Assignment is a direct request for someone's attention, so it is
233// not narrowed by the involvement rule the other lists use.
234//
235// It drives from issue_assignees rather than testing EXISTS against every
236// issue: the assignee rows for one user are a handful, the issues table
237// is the whole instance.
238const assignedIssuesQuery = `
239 SELECT COALESCE(u.username, o.name) || '/' || r.name,
240 x.number, x.title, au.username, x.state, x.updated_at, 0
241 FROM issue_assignees ia
242 JOIN issues x ON x.id = ia.issue_id
243 JOIN repos r ON r.id = x.repo_id
244 LEFT JOIN users u ON r.owner_kind = 'user' AND u.id = r.owner_id
245 LEFT JOIN orgs o ON r.owner_kind = 'org' AND o.id = r.owner_id
246 JOIN users au ON au.id = x.author_id
247 WHERE ia.user_id = ?1 AND x.state = 'open'
248 ORDER BY x.updated_at DESC, x.id DESC LIMIT 20`
249
250func (s *Store) AssignedIssues(userID int64) ([]DashboardItem, error) {
251 return s.dashboardQuery(assignedIssuesQuery, userID)
252}
253
254// DashboardBuild is one build row on the dashboard, with its repo resolved.
255type DashboardBuild struct {
256 RepoPath string
257 Number int64
258 Job string
259 Status string
260 SHA string
261 Ref string
262 CreatedAt string
263 FinishedAt string
264}
265
266// RecentBuilds returns the newest builds on repositories the user can
267// reach, most recent first.
268func (s *Store) RecentBuilds(userID int64, limit int) ([]DashboardBuild, error) {
269 rows, err := s.DB.Query(`
270 SELECT COALESCE(u.username, o.name) || '/' || r.name,
271 b.number, b.job, b.status, b.sha, b.ref, b.created_at, b.finished_at
272 FROM builds b
273 JOIN repos r ON r.id = b.repo_id
274 LEFT JOIN users u ON r.owner_kind = 'user' AND u.id = r.owner_id
275 LEFT JOIN orgs o ON r.owner_kind = 'org' AND o.id = r.owner_id
276 WHERE `+reachableCond+`
277 ORDER BY b.id DESC LIMIT ?2`, userID, limit)
278 if err != nil {
279 return nil, err
280 }
281 defer rows.Close()
282 var out []DashboardBuild
283 for rows.Next() {
284 var b DashboardBuild
285 if err := rows.Scan(&b.RepoPath, &b.Number, &b.Job, &b.Status, &b.SHA, &b.Ref, &b.CreatedAt, &b.FinishedAt); err != nil {
286 return nil, err
287 }
288 out = append(out, b)
289 }
290 return out, rows.Err()
291}
292
293// FeedEvent is one line of the dashboard's activity feed.
294type FeedEvent struct {
295 ID int64
296 RepoPath string
297 Actor string
298 Kind string
299 Data string
300 CreatedAt string
301}
302
303// OwnerPublicEvents returns activity on an owner's public repositories,
304// newest first, for readers carrying no session. Push events are
305// excluded as in RecentEvents.
306func (s *Store) OwnerPublicEvents(ownerKind string, ownerID int64, limit int) ([]FeedEvent, error) {
307 rows, err := s.DB.Query(`
308 SELECT e.id, COALESCE(u.username, o.name) || '/' || r.name,
309 COALESCE(ac.username, ''), e.kind, e.data_json, e.created_at
310 FROM events e
311 JOIN repos r ON r.id = e.repo_id
312 LEFT JOIN users u ON r.owner_kind = 'user' AND u.id = r.owner_id
313 LEFT JOIN orgs o ON r.owner_kind = 'org' AND o.id = r.owner_id
314 LEFT JOIN users ac ON ac.id = e.actor_id
315 WHERE e.kind <> 'push' AND r.visibility = 'public' AND r.owner_kind = ? AND r.owner_id = ?
316 ORDER BY e.id DESC LIMIT ?`, ownerKind, ownerID, limit)
317 if err != nil {
318 return nil, err
319 }
320 defer rows.Close()
321 var out []FeedEvent
322 for rows.Next() {
323 var e FeedEvent
324 if err := rows.Scan(&e.ID, &e.RepoPath, &e.Actor, &e.Kind, &e.Data, &e.CreatedAt); err != nil {
325 return nil, err
326 }
327 out = append(out, e)
328 }
329 return out, rows.Err()
330}
331
332// UserPublicEvents returns what a user did on public repositories,
333// newest first. It keys on the actor, not the repository's owner, which
334// is what ActivityByDay counts for a user: the profile's log has to
335// agree with the total printed above its graph. Push events are
336// excluded as in RecentEvents.
337func (s *Store) UserPublicEvents(userID int64, limit int) ([]FeedEvent, error) {
338 rows, err := s.DB.Query(`
339 SELECT e.id, COALESCE(u.username, o.name) || '/' || r.name,
340 COALESCE(ac.username, ''), e.kind, e.data_json, e.created_at
341 FROM events e
342 JOIN repos r ON r.id = e.repo_id
343 LEFT JOIN users u ON r.owner_kind = 'user' AND u.id = r.owner_id
344 LEFT JOIN orgs o ON r.owner_kind = 'org' AND o.id = r.owner_id
345 LEFT JOIN users ac ON ac.id = e.actor_id
346 WHERE e.kind <> 'push' AND r.visibility = 'public' AND e.actor_id = ?
347 ORDER BY e.id DESC LIMIT ?`, userID, limit)
348 if err != nil {
349 return nil, err
350 }
351 defer rows.Close()
352 var out []FeedEvent
353 for rows.Next() {
354 var e FeedEvent
355 if err := rows.Scan(&e.ID, &e.RepoPath, &e.Actor, &e.Kind, &e.Data, &e.CreatedAt); err != nil {
356 return nil, err
357 }
358 out = append(out, e)
359 }
360 return out, rows.Err()
361}
362
363// RecentEvents returns activity on repositories the user can reach. Push
364// events are excluded: they repeat what the commit lists already show.
365// before (an event id) starts the page strictly below it, matching the
366// id-descending order; 0 starts at the newest.
367func (s *Store) RecentEvents(userID int64, limit int, before int64) ([]FeedEvent, error) {
368 q := `
369 SELECT e.id, COALESCE(u.username, o.name) || '/' || r.name,
370 COALESCE(ac.username, ''), e.kind, e.data_json, e.created_at
371 FROM events e
372 JOIN repos r ON r.id = e.repo_id
373 LEFT JOIN users u ON r.owner_kind = 'user' AND u.id = r.owner_id
374 LEFT JOIN orgs o ON r.owner_kind = 'org' AND o.id = r.owner_id
375 LEFT JOIN users ac ON ac.id = e.actor_id
376 WHERE e.kind <> 'push' AND ` + reachableCond
377 args := []any{userID, limit}
378 if before > 0 {
379 q += " AND e.id < ?3"
380 args = append(args, before)
381 }
382 q += " ORDER BY e.id DESC LIMIT ?2"
383 rows, err := s.DB.Query(q, args...)
384 if err != nil {
385 return nil, err
386 }
387 defer rows.Close()
388 var out []FeedEvent
389 for rows.Next() {
390 var e FeedEvent
391 if err := rows.Scan(&e.ID, &e.RepoPath, &e.Actor, &e.Kind, &e.Data, &e.CreatedAt); err != nil {
392 return nil, err
393 }
394 out = append(out, e)
395 }
396 return out, rows.Err()
397}