internal/store/dashboard.go
269 lines · 9261 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.author_id <> ?1
143 AND NOT EXISTS (SELECT 1 FROM mr_reviews rv
144 WHERE rv.mr_id = x.id AND rv.reviewer_id = ?1
145 AND rv.head_sha = x.head_sha)
146 AND ` + involvedCond + `
147 ORDER BY x.updated_at DESC LIMIT 8`
148
149func (s *Store) ReviewQueue(userID int64) ([]DashboardItem, error) {
150 return s.dashboardQuery(reviewQueueQuery, userID)
151}
152
153// OpenCounts returns the repo's open issue and open merge request counts,
154// for the repo tab badges.
155func (s *Store) OpenCounts(repoID int64) (issues, mrs int) {
156 s.DB.QueryRow("SELECT COUNT(*) FROM issues WHERE repo_id = ? AND state = 'open'",
157 repoID).Scan(&issues)
158 s.DB.QueryRow("SELECT COUNT(*) FROM merge_requests WHERE repo_id = ? AND state IN ('open', 'source_gone')",
159 repoID).Scan(&mrs)
160 return
161}
162
163// AssignedIssues returns open issues assigned to the user, wherever they
164// live. Assignment is a direct request for someone's attention, so it is
165// not narrowed by the involvement rule the other lists use.
166//
167// It drives from issue_assignees rather than testing EXISTS against every
168// issue: the assignee rows for one user are a handful, the issues table
169// is the whole instance.
170const assignedIssuesQuery = `
171 SELECT COALESCE(u.username, o.name) || '/' || r.name,
172 x.number, x.title, au.username, x.state, x.updated_at
173 FROM issue_assignees ia
174 JOIN issues x ON x.id = ia.issue_id
175 JOIN repos r ON r.id = x.repo_id
176 LEFT JOIN users u ON r.owner_kind = 'user' AND u.id = r.owner_id
177 LEFT JOIN orgs o ON r.owner_kind = 'org' AND o.id = r.owner_id
178 JOIN users au ON au.id = x.author_id
179 WHERE ia.user_id = ?1 AND x.state = 'open'
180 ORDER BY x.updated_at DESC LIMIT 20`
181
182func (s *Store) AssignedIssues(userID int64) ([]DashboardItem, error) {
183 return s.dashboardQuery(assignedIssuesQuery, userID)
184}
185
186// DashboardBuild is one build row on the dashboard, with its repo resolved.
187type DashboardBuild struct {
188 RepoPath string
189 Number int64
190 Job string
191 Status string
192 SHA string
193 Ref string
194 CreatedAt string
195 FinishedAt string
196}
197
198// RecentBuilds returns the newest builds on repositories the user can
199// reach, most recent first.
200func (s *Store) RecentBuilds(userID int64, limit int) ([]DashboardBuild, error) {
201 rows, err := s.DB.Query(`
202 SELECT COALESCE(u.username, o.name) || '/' || r.name,
203 b.number, b.job, b.status, b.sha, b.ref, b.created_at, b.finished_at
204 FROM builds b
205 JOIN repos r ON r.id = b.repo_id
206 LEFT JOIN users u ON r.owner_kind = 'user' AND u.id = r.owner_id
207 LEFT JOIN orgs o ON r.owner_kind = 'org' AND o.id = r.owner_id
208 WHERE `+reachableCond+`
209 ORDER BY b.id DESC LIMIT ?2`, userID, limit)
210 if err != nil {
211 return nil, err
212 }
213 defer rows.Close()
214 var out []DashboardBuild
215 for rows.Next() {
216 var b DashboardBuild
217 if err := rows.Scan(&b.RepoPath, &b.Number, &b.Job, &b.Status, &b.SHA, &b.Ref, &b.CreatedAt, &b.FinishedAt); err != nil {
218 return nil, err
219 }
220 out = append(out, b)
221 }
222 return out, rows.Err()
223}
224
225// FeedEvent is one line of the dashboard's activity feed.
226type FeedEvent struct {
227 ID int64
228 RepoPath string
229 Actor string
230 Kind string
231 Data string
232 CreatedAt string
233}
234
235// RecentEvents returns activity on repositories the user can reach. Push
236// events are excluded: they repeat what the commit lists already show.
237// before (an event id) starts the page strictly below it, matching the
238// id-descending order; 0 starts at the newest.
239func (s *Store) RecentEvents(userID int64, limit int, before int64) ([]FeedEvent, error) {
240 q := `
241 SELECT e.id, COALESCE(u.username, o.name) || '/' || r.name,
242 COALESCE(ac.username, ''), e.kind, e.data_json, e.created_at
243 FROM events e
244 JOIN repos r ON r.id = e.repo_id
245 LEFT JOIN users u ON r.owner_kind = 'user' AND u.id = r.owner_id
246 LEFT JOIN orgs o ON r.owner_kind = 'org' AND o.id = r.owner_id
247 LEFT JOIN users ac ON ac.id = e.actor_id
248 WHERE e.kind <> 'push' AND ` + reachableCond
249 args := []any{userID, limit}
250 if before > 0 {
251 q += " AND e.id < ?3"
252 args = append(args, before)
253 }
254 q += " ORDER BY e.id DESC LIMIT ?2"
255 rows, err := s.DB.Query(q, args...)
256 if err != nil {
257 return nil, err
258 }
259 defer rows.Close()
260 var out []FeedEvent
261 for rows.Next() {
262 var e FeedEvent
263 if err := rows.Scan(&e.ID, &e.RepoPath, &e.Actor, &e.Kind, &e.Data, &e.CreatedAt); err != nil {
264 return nil, err
265 }
266 out = append(out, e)
267 }
268 return out, rows.Err()
269}