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