internal/store/dashboard.go
270 lines · 9280 bytes
1package store
2
3// DashboardItem is one open issue or MR row on the logged-in homepage.
4type DashboardItem struct {
5 RepoPath string
6 Number int64
7 Title string
8 Author string
9 State string
10 UpdatedAt string
11}
12
13// reachableCond filters to repositories the user owns, is granted on, or
14// reaches through org or team membership.
15const reachableCond = `(
16 (r.owner_kind = 'user' AND r.owner_id = ?1)
17 OR EXISTS (SELECT 1 FROM repo_access a
18 WHERE a.repo_id = r.id AND a.subject_kind = 'user' AND a.subject_id = ?1)
19 OR EXISTS (SELECT 1 FROM org_members mm
20 JOIN orgs oo ON oo.id = mm.org_id
21 WHERE r.owner_kind = 'org' AND mm.org_id = r.owner_id AND mm.user_id = ?1
22 AND (mm.role = 'admin' OR oo.members_role <> 'none'))
23 OR EXISTS (SELECT 1 FROM team_repos tr
24 JOIN team_members tm ON tm.team_id = tr.team_id AND tm.user_id = ?1
25 WHERE tr.repo_id = r.id)
26)`
27
28// involvedCond widens reachableCond to rows the user authored anywhere.
29const involvedCond = `(x.author_id = ?1 OR ` + reachableCond + `)`
30
31func (s *Store) dashboardQuery(q string, userID int64) ([]DashboardItem, error) {
32 rows, err := s.DB.Query(q, userID)
33 if err != nil {
34 return nil, err
35 }
36 defer rows.Close()
37 var out []DashboardItem
38 for rows.Next() {
39 var d DashboardItem
40 if err := rows.Scan(&d.RepoPath, &d.Number, &d.Title, &d.Author, &d.State, &d.UpdatedAt); err != nil {
41 return nil, err
42 }
43 out = append(out, d)
44 }
45 return out, rows.Err()
46}
47
48// The dashboard's four list queries are named so the plan test can assert
49// each still walks the 0035 index that supplies its ORDER BY.
50const dashboardMRsQuery = `
51 SELECT COALESCE(u.username, o.name) || '/' || r.name,
52 x.number, x.title, au.username, x.state, x.updated_at
53 FROM merge_requests x
54 JOIN repos r ON r.id = x.repo_id
55 LEFT JOIN users u ON r.owner_kind = 'user' AND u.id = r.owner_id
56 LEFT JOIN orgs o ON r.owner_kind = 'org' AND o.id = r.owner_id
57 JOIN users au ON au.id = x.author_id
58 WHERE x.state IN ('open', 'source_gone') AND ` + involvedCond + `
59 ORDER BY x.updated_at DESC LIMIT 50`
60
61// DashboardMRs returns open merge requests involving the user: on their
62// repositories (owned, granted, org) or authored by them anywhere.
63func (s *Store) DashboardMRs(userID int64) ([]DashboardItem, error) {
64 return s.dashboardQuery(dashboardMRsQuery, userID)
65}
66
67// DashboardIssues is the issue counterpart of DashboardMRs.
68const dashboardIssuesQuery = `
69 SELECT COALESCE(u.username, o.name) || '/' || r.name,
70 x.number, x.title, au.username, x.state, x.updated_at
71 FROM issues x
72 JOIN repos r ON r.id = x.repo_id
73 LEFT JOIN users u ON r.owner_kind = 'user' AND u.id = r.owner_id
74 LEFT JOIN orgs o ON r.owner_kind = 'org' AND o.id = r.owner_id
75 JOIN users au ON au.id = x.author_id
76 WHERE x.state = 'open' AND ` + involvedCond + `
77 ORDER BY x.updated_at DESC LIMIT 50`
78
79func (s *Store) DashboardIssues(userID int64) ([]DashboardItem, error) {
80 return s.dashboardQuery(dashboardIssuesQuery, userID)
81}
82
83func (s *Store) PinRepo(userID, repoID int64) error {
84 _, err := s.DB.Exec(
85 "INSERT INTO repo_pins (user_id, repo_id) VALUES (?, ?) ON CONFLICT DO NOTHING",
86 userID, repoID)
87 return err
88}
89
90func (s *Store) IsPinned(userID, repoID int64) bool {
91 var n int
92 s.DB.QueryRow("SELECT COUNT(*) FROM repo_pins WHERE user_id = ? AND repo_id = ?",
93 userID, repoID).Scan(&n)
94 return n > 0
95}
96
97func (s *Store) UnpinRepo(userID, repoID int64) error {
98 res, err := s.DB.Exec(
99 "DELETE FROM repo_pins WHERE user_id = ? AND repo_id = ?", userID, repoID)
100 if err != nil {
101 return err
102 }
103 if n, _ := res.RowsAffected(); n == 0 {
104 return ErrNotFound
105 }
106 return nil
107}
108
109// PinnedRepos returns the user's pinned repositories in pin order. The
110// caller applies visibility checks before rendering.
111func (s *Store) PinnedRepos(userID int64) ([]Repo, error) {
112 rows, err := s.DB.Query(repoSelect+`
113 JOIN repo_pins p ON p.repo_id = r.id AND p.user_id = ?
114 ORDER BY p.pinned_at`, userID)
115 if err != nil {
116 return nil, err
117 }
118 defer rows.Close()
119 var out []Repo
120 for rows.Next() {
121 r, err := scanRepo(rows)
122 if err != nil {
123 return nil, err
124 }
125 out = append(out, r)
126 }
127 return out, rows.Err()
128}
129
130// ReviewQueue returns open merge requests the user is involved in, has not
131// authored, and has not reviewed at the current head — what the rail shows
132// as waiting on them. Ordered most recently touched first.
133const reviewQueueQuery = `
134 SELECT COALESCE(u.username, o.name) || '/' || r.name,
135 x.number, x.title, au.username, x.state, x.updated_at
136 FROM merge_requests x
137 JOIN repos r ON r.id = x.repo_id
138 LEFT JOIN users u ON r.owner_kind = 'user' AND u.id = r.owner_id
139 LEFT JOIN orgs o ON r.owner_kind = 'org' AND o.id = r.owner_id
140 JOIN users au ON au.id = x.author_id
141 WHERE x.state IN ('open', 'source_gone')
142 AND x.draft = 0
143 AND x.author_id <> ?1
144 AND NOT EXISTS (SELECT 1 FROM mr_reviews rv
145 WHERE rv.mr_id = x.id AND rv.reviewer_id = ?1
146 AND rv.head_sha = x.head_sha)
147 AND ` + involvedCond + `
148 ORDER BY x.updated_at DESC LIMIT 8`
149
150func (s *Store) ReviewQueue(userID int64) ([]DashboardItem, error) {
151 return s.dashboardQuery(reviewQueueQuery, userID)
152}
153
154// OpenCounts returns the repo's open issue and open merge request counts,
155// for the repo tab badges.
156func (s *Store) OpenCounts(repoID int64) (issues, mrs int) {
157 s.DB.QueryRow("SELECT COUNT(*) FROM issues WHERE repo_id = ? AND state = 'open'",
158 repoID).Scan(&issues)
159 s.DB.QueryRow("SELECT COUNT(*) FROM merge_requests WHERE repo_id = ? AND state IN ('open', 'source_gone')",
160 repoID).Scan(&mrs)
161 return
162}
163
164// AssignedIssues returns open issues assigned to the user, wherever they
165// live. Assignment is a direct request for someone's attention, so it is
166// not narrowed by the involvement rule the other lists use.
167//
168// It drives from issue_assignees rather than testing EXISTS against every
169// issue: the assignee rows for one user are a handful, the issues table
170// is the whole instance.
171const assignedIssuesQuery = `
172 SELECT COALESCE(u.username, o.name) || '/' || r.name,
173 x.number, x.title, au.username, x.state, x.updated_at
174 FROM issue_assignees ia
175 JOIN issues x ON x.id = ia.issue_id
176 JOIN repos r ON r.id = x.repo_id
177 LEFT JOIN users u ON r.owner_kind = 'user' AND u.id = r.owner_id
178 LEFT JOIN orgs o ON r.owner_kind = 'org' AND o.id = r.owner_id
179 JOIN users au ON au.id = x.author_id
180 WHERE ia.user_id = ?1 AND x.state = 'open'
181 ORDER BY x.updated_at DESC LIMIT 20`
182
183func (s *Store) AssignedIssues(userID int64) ([]DashboardItem, error) {
184 return s.dashboardQuery(assignedIssuesQuery, userID)
185}
186
187// DashboardBuild is one build row on the dashboard, with its repo resolved.
188type DashboardBuild struct {
189 RepoPath string
190 Number int64
191 Job string
192 Status string
193 SHA string
194 Ref string
195 CreatedAt string
196 FinishedAt string
197}
198
199// RecentBuilds returns the newest builds on repositories the user can
200// reach, most recent first.
201func (s *Store) RecentBuilds(userID int64, limit int) ([]DashboardBuild, error) {
202 rows, err := s.DB.Query(`
203 SELECT COALESCE(u.username, o.name) || '/' || r.name,
204 b.number, b.job, b.status, b.sha, b.ref, b.created_at, b.finished_at
205 FROM builds b
206 JOIN repos r ON r.id = b.repo_id
207 LEFT JOIN users u ON r.owner_kind = 'user' AND u.id = r.owner_id
208 LEFT JOIN orgs o ON r.owner_kind = 'org' AND o.id = r.owner_id
209 WHERE `+reachableCond+`
210 ORDER BY b.id DESC LIMIT ?2`, userID, limit)
211 if err != nil {
212 return nil, err
213 }
214 defer rows.Close()
215 var out []DashboardBuild
216 for rows.Next() {
217 var b DashboardBuild
218 if err := rows.Scan(&b.RepoPath, &b.Number, &b.Job, &b.Status, &b.SHA, &b.Ref, &b.CreatedAt, &b.FinishedAt); err != nil {
219 return nil, err
220 }
221 out = append(out, b)
222 }
223 return out, rows.Err()
224}
225
226// FeedEvent is one line of the dashboard's activity feed.
227type FeedEvent struct {
228 ID int64
229 RepoPath string
230 Actor string
231 Kind string
232 Data string
233 CreatedAt string
234}
235
236// RecentEvents returns activity on repositories the user can reach. Push
237// events are excluded: they repeat what the commit lists already show.
238// before (an event id) starts the page strictly below it, matching the
239// id-descending order; 0 starts at the newest.
240func (s *Store) RecentEvents(userID int64, limit int, before int64) ([]FeedEvent, error) {
241 q := `
242 SELECT e.id, COALESCE(u.username, o.name) || '/' || r.name,
243 COALESCE(ac.username, ''), e.kind, e.data_json, e.created_at
244 FROM events e
245 JOIN repos r ON r.id = e.repo_id
246 LEFT JOIN users u ON r.owner_kind = 'user' AND u.id = r.owner_id
247 LEFT JOIN orgs o ON r.owner_kind = 'org' AND o.id = r.owner_id
248 LEFT JOIN users ac ON ac.id = e.actor_id
249 WHERE e.kind <> 'push' AND ` + reachableCond
250 args := []any{userID, limit}
251 if before > 0 {
252 q += " AND e.id < ?3"
253 args = append(args, before)
254 }
255 q += " ORDER BY e.id DESC LIMIT ?2"
256 rows, err := s.DB.Query(q, args...)
257 if err != nil {
258 return nil, err
259 }
260 defer rows.Close()
261 var out []FeedEvent
262 for rows.Next() {
263 var e FeedEvent
264 if err := rows.Scan(&e.ID, &e.RepoPath, &e.Actor, &e.Kind, &e.Data, &e.CreatedAt); err != nil {
265 return nil, err
266 }
267 out = append(out, e)
268 }
269 return out, rows.Err()
270}