internal/store/dashboard.go

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