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