internal/store/dashboard.go
256 lines · 8831 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// DashboardMRs returns open merge requests involving the user: on their
49// repositories (owned, granted, org) or authored by them anywhere.
50func (s *Store) DashboardMRs(userID int64) ([]DashboardItem, error) {
51 return s.dashboardQuery(`
52 SELECT COALESCE(u.username, o.name) || '/' || r.name,
53 x.number, x.title, au.username, x.state, x.updated_at
54 FROM merge_requests x
55 JOIN repos r ON r.id = x.repo_id
56 LEFT JOIN users u ON r.owner_kind = 'user' AND u.id = r.owner_id
57 LEFT JOIN orgs o ON r.owner_kind = 'org' AND o.id = r.owner_id
58 JOIN users au ON au.id = x.author_id
59 WHERE x.state IN ('open', 'source_gone') AND `+involvedCond+`
60 ORDER BY x.updated_at DESC LIMIT 50`, userID)
61}
62
63// DashboardIssues is the issue counterpart of DashboardMRs.
64func (s *Store) DashboardIssues(userID int64) ([]DashboardItem, error) {
65 return s.dashboardQuery(`
66 SELECT COALESCE(u.username, o.name) || '/' || r.name,
67 x.number, x.title, au.username, x.state, x.updated_at
68 FROM issues x
69 JOIN repos r ON r.id = x.repo_id
70 LEFT JOIN users u ON r.owner_kind = 'user' AND u.id = r.owner_id
71 LEFT JOIN orgs o ON r.owner_kind = 'org' AND o.id = r.owner_id
72 JOIN users au ON au.id = x.author_id
73 WHERE x.state = 'open' AND `+involvedCond+`
74 ORDER BY x.updated_at DESC LIMIT 50`, userID)
75}
76
77func (s *Store) PinRepo(userID, repoID int64) error {
78 _, err := s.DB.Exec(
79 "INSERT INTO repo_pins (user_id, repo_id) VALUES (?, ?) ON CONFLICT DO NOTHING",
80 userID, repoID)
81 return err
82}
83
84func (s *Store) IsPinned(userID, repoID int64) bool {
85 var n int
86 s.DB.QueryRow("SELECT COUNT(*) FROM repo_pins WHERE user_id = ? AND repo_id = ?",
87 userID, repoID).Scan(&n)
88 return n > 0
89}
90
91func (s *Store) UnpinRepo(userID, repoID int64) error {
92 res, err := s.DB.Exec(
93 "DELETE FROM repo_pins WHERE user_id = ? AND repo_id = ?", userID, repoID)
94 if err != nil {
95 return err
96 }
97 if n, _ := res.RowsAffected(); n == 0 {
98 return ErrNotFound
99 }
100 return nil
101}
102
103// PinnedRepos returns the user's pinned repositories in pin order. The
104// caller applies visibility checks before rendering.
105func (s *Store) PinnedRepos(userID int64) ([]Repo, error) {
106 rows, err := s.DB.Query(repoSelect+`
107 JOIN repo_pins p ON p.repo_id = r.id AND p.user_id = ?
108 ORDER BY p.pinned_at`, userID)
109 if err != nil {
110 return nil, err
111 }
112 defer rows.Close()
113 var out []Repo
114 for rows.Next() {
115 r, err := scanRepo(rows)
116 if err != nil {
117 return nil, err
118 }
119 out = append(out, r)
120 }
121 return out, rows.Err()
122}
123
124// ReviewQueue returns open merge requests the user is involved in, has not
125// authored, and has not reviewed at the current head — what the rail shows
126// as waiting on them. Ordered most recently touched first.
127func (s *Store) ReviewQueue(userID int64) ([]DashboardItem, error) {
128 return s.dashboardQuery(`
129 SELECT COALESCE(u.username, o.name) || '/' || r.name,
130 x.number, x.title, au.username, x.state, x.updated_at
131 FROM merge_requests x
132 JOIN repos r ON r.id = x.repo_id
133 LEFT JOIN users u ON r.owner_kind = 'user' AND u.id = r.owner_id
134 LEFT JOIN orgs o ON r.owner_kind = 'org' AND o.id = r.owner_id
135 JOIN users au ON au.id = x.author_id
136 WHERE x.state IN ('open', 'source_gone')
137 AND x.author_id <> ?1
138 AND NOT EXISTS (SELECT 1 FROM mr_reviews rv
139 WHERE rv.mr_id = x.id AND rv.reviewer_id = ?1
140 AND rv.head_sha = x.head_sha)
141 AND `+involvedCond+`
142 ORDER BY x.updated_at DESC LIMIT 8`, userID)
143}
144
145// OpenCounts returns the repo's open issue and open merge request counts,
146// for the repo tab badges.
147func (s *Store) OpenCounts(repoID int64) (issues, mrs int) {
148 s.DB.QueryRow("SELECT COUNT(*) FROM issues WHERE repo_id = ? AND state = 'open'",
149 repoID).Scan(&issues)
150 s.DB.QueryRow("SELECT COUNT(*) FROM merge_requests WHERE repo_id = ? AND state IN ('open', 'source_gone')",
151 repoID).Scan(&mrs)
152 return
153}
154
155// AssignedIssues returns open issues assigned to the user, wherever they
156// live. Assignment is a direct request for someone's attention, so it is
157// not narrowed by the involvement rule the other lists use.
158func (s *Store) AssignedIssues(userID int64) ([]DashboardItem, error) {
159 return s.dashboardQuery(`
160 SELECT COALESCE(u.username, o.name) || '/' || r.name,
161 x.number, x.title, au.username, x.state, x.updated_at
162 FROM issues x
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 x.state = 'open'
168 AND EXISTS (SELECT 1 FROM issue_assignees ia
169 WHERE ia.issue_id = x.id AND ia.user_id = ?1)
170 ORDER BY x.updated_at DESC LIMIT 20`, userID)
171}
172
173// DashboardBuild is one build row on the dashboard, with its repo resolved.
174type DashboardBuild struct {
175 RepoPath string
176 Number int64
177 Job string
178 Status string
179 SHA string
180 Ref string
181 CreatedAt string
182 FinishedAt string
183}
184
185// RecentBuilds returns the newest builds on repositories the user can
186// reach, most recent first.
187func (s *Store) RecentBuilds(userID int64, limit int) ([]DashboardBuild, error) {
188 rows, err := s.DB.Query(`
189 SELECT COALESCE(u.username, o.name) || '/' || r.name,
190 b.number, b.job, b.status, b.sha, b.ref, b.created_at, b.finished_at
191 FROM builds b
192 JOIN repos r ON r.id = b.repo_id
193 LEFT JOIN users u ON r.owner_kind = 'user' AND u.id = r.owner_id
194 LEFT JOIN orgs o ON r.owner_kind = 'org' AND o.id = r.owner_id
195 WHERE `+reachableCond+`
196 ORDER BY b.id DESC LIMIT ?2`, userID, limit)
197 if err != nil {
198 return nil, err
199 }
200 defer rows.Close()
201 var out []DashboardBuild
202 for rows.Next() {
203 var b DashboardBuild
204 if err := rows.Scan(&b.RepoPath, &b.Number, &b.Job, &b.Status, &b.SHA, &b.Ref, &b.CreatedAt, &b.FinishedAt); err != nil {
205 return nil, err
206 }
207 out = append(out, b)
208 }
209 return out, rows.Err()
210}
211
212// FeedEvent is one line of the dashboard's activity feed.
213type FeedEvent struct {
214 ID int64
215 RepoPath string
216 Actor string
217 Kind string
218 Data string
219 CreatedAt string
220}
221
222// RecentEvents returns activity on repositories the user can reach. Push
223// events are excluded: they repeat what the commit lists already show.
224// before (an event id) starts the page strictly below it, matching the
225// id-descending order; 0 starts at the newest.
226func (s *Store) RecentEvents(userID int64, limit int, before int64) ([]FeedEvent, error) {
227 q := `
228 SELECT e.id, COALESCE(u.username, o.name) || '/' || r.name,
229 COALESCE(ac.username, ''), e.kind, e.data_json, e.created_at
230 FROM events e
231 JOIN repos r ON r.id = e.repo_id
232 LEFT JOIN users u ON r.owner_kind = 'user' AND u.id = r.owner_id
233 LEFT JOIN orgs o ON r.owner_kind = 'org' AND o.id = r.owner_id
234 LEFT JOIN users ac ON ac.id = e.actor_id
235 WHERE e.kind <> 'push' AND ` + reachableCond
236 args := []any{userID, limit}
237 if before > 0 {
238 q += " AND e.id < ?3"
239 args = append(args, before)
240 }
241 q += " ORDER BY e.id DESC LIMIT ?2"
242 rows, err := s.DB.Query(q, args...)
243 if err != nil {
244 return nil, err
245 }
246 defer rows.Close()
247 var out []FeedEvent
248 for rows.Next() {
249 var e FeedEvent
250 if err := rows.Scan(&e.ID, &e.RepoPath, &e.Actor, &e.Kind, &e.Data, &e.CreatedAt); err != nil {
251 return nil, err
252 }
253 out = append(out, e)
254 }
255 return out, rows.Err()
256}