internal/store/dashboard.go
217 lines · 7865 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// involvedRepos filters to repositories the user owns, is granted on, or
14// reaches through org membership — or rows the user authored anywhere.
15const involvedCond = `(
16 x.author_id = ?1
17 OR (r.owner_kind = 'user' AND r.owner_id = ?1)
18 OR EXISTS (SELECT 1 FROM repo_access a
19 WHERE a.repo_id = r.id AND a.subject_kind = 'user' AND a.subject_id = ?1)
20 OR EXISTS (SELECT 1 FROM org_members mm
21 JOIN orgs oo ON oo.id = mm.org_id
22 WHERE r.owner_kind = 'org' AND mm.org_id = r.owner_id AND mm.user_id = ?1
23 AND (mm.role = 'admin' OR oo.members_role <> 'none'))
24 OR EXISTS (SELECT 1 FROM team_repos tr
25 JOIN team_members tm ON tm.team_id = tr.team_id AND tm.user_id = ?1
26 WHERE tr.repo_id = r.id)
27)`
28
29func (s *Store) dashboardQuery(q string, userID int64) ([]DashboardItem, error) {
30 rows, err := s.DB.Query(q, userID)
31 if err != nil {
32 return nil, err
33 }
34 defer rows.Close()
35 var out []DashboardItem
36 for rows.Next() {
37 var d DashboardItem
38 if err := rows.Scan(&d.RepoPath, &d.Number, &d.Title, &d.Author, &d.State, &d.UpdatedAt); err != nil {
39 return nil, err
40 }
41 out = append(out, d)
42 }
43 return out, rows.Err()
44}
45
46// DashboardMRs returns open merge requests involving the user: on their
47// repositories (owned, granted, org) or authored by them anywhere.
48func (s *Store) DashboardMRs(userID int64) ([]DashboardItem, error) {
49 return s.dashboardQuery(`
50 SELECT COALESCE(u.username, o.name) || '/' || r.name,
51 x.number, x.title, au.username, x.state, x.updated_at
52 FROM merge_requests x
53 JOIN repos r ON r.id = x.repo_id
54 LEFT JOIN users u ON r.owner_kind = 'user' AND u.id = r.owner_id
55 LEFT JOIN orgs o ON r.owner_kind = 'org' AND o.id = r.owner_id
56 JOIN users au ON au.id = x.author_id
57 WHERE x.state IN ('open', 'source_gone') AND `+involvedCond+`
58 ORDER BY x.updated_at DESC LIMIT 50`, userID)
59}
60
61// DashboardIssues is the issue counterpart of DashboardMRs.
62func (s *Store) DashboardIssues(userID int64) ([]DashboardItem, error) {
63 return s.dashboardQuery(`
64 SELECT COALESCE(u.username, o.name) || '/' || r.name,
65 x.number, x.title, au.username, x.state, x.updated_at
66 FROM issues x
67 JOIN repos r ON r.id = x.repo_id
68 LEFT JOIN users u ON r.owner_kind = 'user' AND u.id = r.owner_id
69 LEFT JOIN orgs o ON r.owner_kind = 'org' AND o.id = r.owner_id
70 JOIN users au ON au.id = x.author_id
71 WHERE x.state = 'open' AND `+involvedCond+`
72 ORDER BY x.updated_at DESC LIMIT 50`, userID)
73}
74
75func (s *Store) PinRepo(userID, repoID int64) error {
76 _, err := s.DB.Exec(
77 "INSERT INTO repo_pins (user_id, repo_id) VALUES (?, ?) ON CONFLICT DO NOTHING",
78 userID, repoID)
79 return err
80}
81
82func (s *Store) IsPinned(userID, repoID int64) bool {
83 var n int
84 s.DB.QueryRow("SELECT COUNT(*) FROM repo_pins WHERE user_id = ? AND repo_id = ?",
85 userID, repoID).Scan(&n)
86 return n > 0
87}
88
89func (s *Store) UnpinRepo(userID, repoID int64) error {
90 res, err := s.DB.Exec(
91 "DELETE FROM repo_pins WHERE user_id = ? AND repo_id = ?", userID, repoID)
92 if err != nil {
93 return err
94 }
95 if n, _ := res.RowsAffected(); n == 0 {
96 return ErrNotFound
97 }
98 return nil
99}
100
101// PinnedRepos returns the user's pinned repositories in pin order. The
102// caller applies visibility checks before rendering.
103func (s *Store) PinnedRepos(userID int64) ([]Repo, error) {
104 rows, err := s.DB.Query(repoSelect+`
105 JOIN repo_pins p ON p.repo_id = r.id AND p.user_id = ?
106 ORDER BY p.pinned_at`, userID)
107 if err != nil {
108 return nil, err
109 }
110 defer rows.Close()
111 var out []Repo
112 for rows.Next() {
113 r, err := scanRepo(rows)
114 if err != nil {
115 return nil, err
116 }
117 out = append(out, r)
118 }
119 return out, rows.Err()
120}
121
122// ReviewQueue returns open merge requests the user is involved in, has not
123// authored, and has not reviewed at the current head — what the rail shows
124// as waiting on them. Ordered most recently touched first.
125func (s *Store) ReviewQueue(userID int64) ([]DashboardItem, error) {
126 return s.dashboardQuery(`
127 SELECT COALESCE(u.username, o.name) || '/' || r.name,
128 x.number, x.title, au.username, x.state, x.updated_at
129 FROM merge_requests x
130 JOIN repos r ON r.id = x.repo_id
131 LEFT JOIN users u ON r.owner_kind = 'user' AND u.id = r.owner_id
132 LEFT JOIN orgs o ON r.owner_kind = 'org' AND o.id = r.owner_id
133 JOIN users au ON au.id = x.author_id
134 WHERE x.state IN ('open', 'source_gone')
135 AND x.author_id <> ?1
136 AND NOT EXISTS (SELECT 1 FROM mr_reviews rv
137 WHERE rv.mr_id = x.id AND rv.reviewer_id = ?1
138 AND rv.head_sha = x.head_sha)
139 AND `+involvedCond+`
140 ORDER BY x.updated_at DESC LIMIT 8`, userID)
141}
142
143// OpenCounts returns the repo's open issue and open merge request counts,
144// for the repo tab badges.
145func (s *Store) OpenCounts(repoID int64) (issues, mrs int) {
146 s.DB.QueryRow("SELECT COUNT(*) FROM issues WHERE repo_id = ? AND state = 'open'",
147 repoID).Scan(&issues)
148 s.DB.QueryRow("SELECT COUNT(*) FROM merge_requests WHERE repo_id = ? AND state IN ('open', 'source_gone')",
149 repoID).Scan(&mrs)
150 return
151}
152
153// AssignedIssues returns open issues assigned to the user, wherever they
154// live. Assignment is a direct request for someone's attention, so it is
155// not narrowed by the involvement rule the other lists use.
156func (s *Store) AssignedIssues(userID int64) ([]DashboardItem, error) {
157 return s.dashboardQuery(`
158 SELECT COALESCE(u.username, o.name) || '/' || r.name,
159 x.number, x.title, au.username, x.state, x.updated_at
160 FROM issues x
161 JOIN repos r ON r.id = x.repo_id
162 LEFT JOIN users u ON r.owner_kind = 'user' AND u.id = r.owner_id
163 LEFT JOIN orgs o ON r.owner_kind = 'org' AND o.id = r.owner_id
164 JOIN users au ON au.id = x.author_id
165 WHERE x.state = 'open'
166 AND EXISTS (SELECT 1 FROM issue_assignees ia
167 WHERE ia.issue_id = x.id AND ia.user_id = ?1)
168 ORDER BY x.updated_at DESC LIMIT 20`, userID)
169}
170
171// FeedEvent is one line of the dashboard's activity feed.
172type FeedEvent struct {
173 RepoPath string
174 Actor string
175 Kind string
176 Data string
177 CreatedAt string
178}
179
180// RecentEvents returns activity on repositories the user can reach. Push
181// events are excluded: they repeat what the commit lists already show.
182func (s *Store) RecentEvents(userID int64, limit int) ([]FeedEvent, error) {
183 rows, err := s.DB.Query(`
184 SELECT COALESCE(u.username, o.name) || '/' || r.name,
185 COALESCE(ac.username, ''), e.kind, e.data_json, e.created_at
186 FROM events e
187 JOIN repos r ON r.id = e.repo_id
188 LEFT JOIN users u ON r.owner_kind = 'user' AND u.id = r.owner_id
189 LEFT JOIN orgs o ON r.owner_kind = 'org' AND o.id = r.owner_id
190 LEFT JOIN users ac ON ac.id = e.actor_id
191 WHERE e.kind <> 'push' AND (
192 (r.owner_kind = 'user' AND r.owner_id = ?1)
193 OR EXISTS (SELECT 1 FROM repo_access a
194 WHERE a.repo_id = r.id AND a.subject_kind = 'user' AND a.subject_id = ?1)
195 OR EXISTS (SELECT 1 FROM org_members mm
196 JOIN orgs oo ON oo.id = mm.org_id
197 WHERE r.owner_kind = 'org' AND mm.org_id = r.owner_id AND mm.user_id = ?1
198 AND (mm.role = 'admin' OR oo.members_role <> 'none'))
199 OR EXISTS (SELECT 1 FROM team_repos tr
200 JOIN team_members tm ON tm.team_id = tr.team_id AND tm.user_id = ?1
201 WHERE tr.repo_id = r.id)
202 )
203 ORDER BY e.id DESC LIMIT ?2`, userID, limit)
204 if err != nil {
205 return nil, err
206 }
207 defer rows.Close()
208 var out []FeedEvent
209 for rows.Next() {
210 var e FeedEvent
211 if err := rows.Scan(&e.RepoPath, &e.Actor, &e.Kind, &e.Data, &e.CreatedAt); err != nil {
212 return nil, err
213 }
214 out = append(out, e)
215 }
216 return out, rows.Err()
217}