internal/store/mrs.go

  1package store
  2
  3import (
  4	"database/sql"
  5	"errors"
  6	"strings"
  7)
  8
  9type MR struct {
 10	ID           int64
 11	RepoID       int64
 12	Number       int64
 13	Author       string
 14	SourceRepoID int64  // 0 when the source repo is gone
 15	SourcePath   string // owner/name of source repo, "" when gone
 16	SourceRef    string
 17	TargetRef    string
 18	Title        string
 19	Body         string
 20	BodyFormat   string // md | org
 21	State        string // open | merged | closed | source_gone
 22	// Draft marks an open merge request that is not asking to be merged
 23	// yet. Not a state: see migration 0037.
 24	Draft      bool
 25	Milestone  string
 26	HeadSHA    string
 27	MergedBase string // target tip at merge time; base for historical diffs
 28	MergedAt   string // "" unless merged
 29	MergedBy   string // "" when unknown (imports) or the account is gone
 30	ClosedAt   string // "" unless closed without merging
 31	ClosedBy   string
 32	// SupersededBy is the number, within this repository, of the merge
 33	// request this one was closed in favour of. 0 means none.
 34	SupersededBy int64
 35	CreatedAt    string
 36	UpdatedAt    string
 37	Labels       []string
 38	// ReviewRequests is who has been asked, directly, for a review — the
 39	// mr review request counterpart of Issue.Assignees.
 40	ReviewRequests []string
 41	// The queued merge (mr merge --when-ready); QueuedAt is "" when there
 42	// is none. QueueReason is why the last attempt did not merge.
 43	QueuedByID    int64
 44	QueuedBy      string
 45	QueueStrategy string
 46	QueueReason   string
 47	QueuedAt      string
 48}
 49
 50type MRReview struct {
 51	Reviewer  string
 52	Verdict   string
 53	HeadSHA   string
 54	Stale     bool
 55	CreatedAt string
 56}
 57
 58func (s *Store) CreateMR(repoID, authorID, sourceRepoID int64, sourceRef, targetRef, title, body, headSHA, format string, draft bool) (int64, error) {
 59	tx, err := s.DB.Begin()
 60	if err != nil {
 61		return 0, err
 62	}
 63	defer tx.Rollback()
 64	if _, err := tx.Exec("UPDATE repos SET mr_counter = mr_counter + 1 WHERE id = ?", repoID); err != nil {
 65		return 0, err
 66	}
 67	var n int64
 68	if err := tx.QueryRow("SELECT mr_counter FROM repos WHERE id = ?", repoID).Scan(&n); err != nil {
 69		return 0, err
 70	}
 71	if _, err := tx.Exec(`
 72		INSERT INTO merge_requests (repo_id, number, author_id, source_repo_id, source_ref, target_ref, title, body, head_sha, body_format, draft)
 73		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
 74		repoID, n, authorID, sourceRepoID, sourceRef, targetRef, title, body, headSHA, format, draft); err != nil {
 75		return 0, err
 76	}
 77	if headSHA != "" {
 78		var mrID int64
 79		if err := tx.QueryRow("SELECT id FROM merge_requests WHERE repo_id = ? AND number = ?",
 80			repoID, n).Scan(&mrID); err != nil {
 81			return 0, err
 82		}
 83		if _, err := tx.Exec("INSERT INTO mr_heads (mr_id, sha) VALUES (?, ?)", mrID, headSHA); err != nil {
 84			return 0, err
 85		}
 86	}
 87	return n, tx.Commit()
 88}
 89
 90// SetMRDraft marks an open merge request as a draft, or takes the mark
 91// off. Merging is refused while it is set.
 92func (s *Store) SetMRDraft(mrID int64, draft bool) error {
 93	_, err := s.DB.Exec(
 94		"UPDATE merge_requests SET draft = ?, updated_at = strftime('%Y-%m-%dT%H:%M:%fZ','now') WHERE id = ?",
 95		draft, mrID)
 96	return err
 97}
 98
 99const mrSelect = `
100	SELECT m.id, m.repo_id, m.number, u.username,
101	       COALESCE(m.source_repo_id, 0),
102	       COALESCE(COALESCE(su.username, so.name) || '/' || sr.name, ''),
103	       m.source_ref, m.target_ref, m.title, m.body, m.body_format, m.state, m.draft,
104	       COALESCE(ms.title, ''), m.head_sha,
105	       m.merged_base, m.merged_at, COALESCE(mu.username, ''),
106	       m.closed_at, COALESCE(cu.username, ''), COALESCE(m.superseded_by, 0), m.created_at, m.updated_at,
107	       COALESCE(q.user_id, 0), COALESCE(qu.username, ''), COALESCE(q.strategy, ''),
108	       COALESCE(q.reason, ''), COALESCE(q.queued_at, '')
109	FROM merge_requests m
110	JOIN users u ON u.id = m.author_id
111	LEFT JOIN users mu ON mu.id = m.merged_by
112	LEFT JOIN users cu ON cu.id = m.closed_by
113	LEFT JOIN repos sr ON sr.id = m.source_repo_id
114	LEFT JOIN users su ON sr.owner_kind = 'user' AND su.id = sr.owner_id
115	LEFT JOIN orgs so  ON sr.owner_kind = 'org'  AND so.id = sr.owner_id
116	LEFT JOIN milestones ms ON ms.id = m.milestone_id
117	LEFT JOIN mr_merge_queue q ON q.mr_id = m.id
118	LEFT JOIN users qu ON qu.id = q.user_id`
119
120func scanMR(row interface{ Scan(...any) error }) (MR, error) {
121	var m MR
122	err := row.Scan(&m.ID, &m.RepoID, &m.Number, &m.Author, &m.SourceRepoID, &m.SourcePath,
123		&m.SourceRef, &m.TargetRef, &m.Title, &m.Body, &m.BodyFormat, &m.State, &m.Draft, &m.Milestone, &m.HeadSHA, &m.MergedBase,
124		&m.MergedAt, &m.MergedBy, &m.ClosedAt, &m.ClosedBy, &m.SupersededBy, &m.CreatedAt, &m.UpdatedAt,
125		&m.QueuedByID, &m.QueuedBy, &m.QueueStrategy, &m.QueueReason, &m.QueuedAt)
126	return m, err
127}
128
129func (s *Store) MRByNumber(repoID, number int64) (MR, error) {
130	m, err := scanMR(s.DB.QueryRow(mrSelect+" WHERE m.repo_id = ? AND m.number = ?", repoID, number))
131	if errors.Is(err, sql.ErrNoRows) {
132		return m, ErrNotFound
133	}
134	if err != nil {
135		return m, err
136	}
137	if m.Labels, err = s.issueStrings(m.ID, `
138		SELECT l.name FROM mr_labels ml JOIN labels l ON l.id = ml.label_id
139		WHERE ml.mr_id = ? ORDER BY l.name`); err != nil {
140		return m, err
141	}
142	m.ReviewRequests, err = s.issueStrings(m.ID, `
143		SELECT u.username FROM mr_review_requests rr JOIN users u ON u.id = rr.user_id
144		WHERE rr.mr_id = ? ORDER BY u.username`)
145	return m, err
146}
147
148// ListMRLabels returns the label names attached to each merge request of
149// a repo, keyed by merge request id, its org's labels included. Used by
150// the web merge request listing; ListMRs itself stays label-free for the
151// CLI's lean list output.
152func (s *Store) ListMRLabels(repo Repo) (map[int64][]string, error) {
153	return s.listItemLabels(mrLabelJoin, repo)
154}
155
156// SetMRLabel attaches (add) or detaches a label by name, the issue rules
157// exactly: the org's row when the org has the name, else the
158// repository's, created on first use.
159func (s *Store) SetMRLabel(repo Repo, mrID int64, name string, add bool) error {
160	return s.setItemLabel(mrLabelJoin, repo, mrID, name, add)
161}
162
163// SetMRReviewRequest adds or removes a review request by user id — the
164// mr review request counterpart of SetIssueAssignee.
165func (s *Store) SetMRReviewRequest(mrID, userID int64, add bool) error {
166	if add {
167		_, err := s.DB.Exec(
168			"INSERT INTO mr_review_requests (mr_id, user_id) VALUES (?, ?) ON CONFLICT DO NOTHING",
169			mrID, userID)
170		return err
171	}
172	res, err := s.DB.Exec(
173		"DELETE FROM mr_review_requests WHERE mr_id = ? AND user_id = ?", mrID, userID)
174	if err != nil {
175		return err
176	}
177	if n, _ := res.RowsAffected(); n == 0 {
178		return ErrNotFound
179	}
180	return nil
181}
182
183// MRReviewRequestIDs returns who has been asked for a review, by id — for
184// notifying them without a username round trip.
185func (s *Store) MRReviewRequestIDs(mrID int64) ([]int64, error) {
186	return s.idQuery("SELECT user_id FROM mr_review_requests WHERE mr_id = ?", mrID)
187}
188
189// ListMRs returns merge requests for a repo. limit 0 means everything;
190// before (an MR number) starts the page strictly below it, matching the
191// number-descending order.
192// MRFilter narrows a listing. Empty strings match anything; State "all"
193// too. Milestone "none" selects merge requests with no milestone.
194type MRFilter struct {
195	State     string
196	Label     string
197	Author    string
198	Milestone string
199	Search    string // full-text over title and body
200	Limit     int
201	Before    int64
202}
203
204func (s *Store) ListMRs(repoID int64, state string, limit int, before int64) ([]MR, error) {
205	return s.QueryMRs(repoID, MRFilter{State: state, Limit: limit, Before: before})
206}
207
208// QueryMRs lists a repository's merge requests, newest first, narrowed
209// by f.
210func (s *Store) QueryMRs(repoID int64, f MRFilter) ([]MR, error) {
211	q := mrSelect + " WHERE m.repo_id = ?"
212	args := []any{repoID}
213	if f.State != "" && f.State != "all" {
214		q += " AND m.state = ?"
215		args = append(args, f.State)
216	}
217	if f.Label != "" {
218		q += ` AND EXISTS (SELECT 1 FROM mr_labels ml JOIN labels l ON l.id = ml.label_id
219			WHERE ml.mr_id = m.id AND l.name = ?)`
220		args = append(args, f.Label)
221	}
222	if f.Author != "" {
223		q += " AND u.username = ?"
224		args = append(args, f.Author)
225	}
226	switch f.Milestone {
227	case "":
228	case "none":
229		q += " AND m.milestone_id IS NULL"
230	default:
231		q += " AND ms.title = ?"
232		args = append(args, f.Milestone)
233	}
234	if f.Search != "" {
235		q += " AND m.id IN (SELECT rowid FROM mr_fts WHERE mr_fts MATCH ?)"
236		args = append(args, FTSQuery(f.Search))
237	}
238	if f.Before > 0 {
239		q += " AND m.number < ?"
240		args = append(args, f.Before)
241	}
242	q += " ORDER BY m.number DESC"
243	if f.Limit > 0 {
244		q += " LIMIT ?"
245		args = append(args, f.Limit)
246	}
247	rows, err := s.DB.Query(q, args...)
248	if err != nil {
249		return nil, err
250	}
251	defer rows.Close()
252	var out []MR
253	for rows.Next() {
254		m, err := scanMR(rows)
255		if err != nil {
256			return nil, err
257		}
258		out = append(out, m)
259	}
260	return out, rows.Err()
261}
262
263// OpenMRsBySource returns open (and source_gone) MRs fed by the given source
264// repo branch — the cross-repo hook effect consults this.
265func (s *Store) OpenMRsBySource(sourceRepoID int64, sourceRef string) ([]MR, error) {
266	rows, err := s.DB.Query(
267		mrSelect+" WHERE m.source_repo_id = ? AND m.source_ref = ? AND m.state IN ('open','source_gone')",
268		sourceRepoID, sourceRef)
269	if err != nil {
270		return nil, err
271	}
272	defer rows.Close()
273	var out []MR
274	for rows.Next() {
275		m, err := scanMR(rows)
276		if err != nil {
277			return nil, err
278		}
279		out = append(out, m)
280	}
281	return out, rows.Err()
282}
283
284// MarkMerged records the merge along with the target tip it landed on, so
285// the MR's diff stays reconstructable after fast-forwards. actorID 0 and an
286// empty at leave the merger unknown and stamp the current time, which is
287// what an import that carries neither can say.
288func (s *Store) MarkMerged(mrID int64, baseSHA string, actorID int64, at string) error {
289	_, err := s.DB.Exec(
290		`UPDATE merge_requests SET state = 'merged', merged_base = ?,
291			merged_at = COALESCE(NULLIF(?, ''), strftime('%Y-%m-%dT%H:%M:%fZ','now')),
292			merged_by = NULLIF(?, 0),
293			updated_at = strftime('%Y-%m-%dT%H:%M:%fZ','now') WHERE id = ?`,
294		baseSHA, at, actorID, mrID)
295	return err
296}
297
298// MarkClosed is MarkMerged's counterpart for a merge request closed without
299// merging.
300func (s *Store) MarkClosed(mrID, actorID int64, at string) error {
301	_, err := s.DB.Exec(
302		`UPDATE merge_requests SET state = 'closed',
303			closed_at = COALESCE(NULLIF(?, ''), strftime('%Y-%m-%dT%H:%M:%fZ','now')),
304			closed_by = NULLIF(?, 0),
305			updated_at = strftime('%Y-%m-%dT%H:%M:%fZ','now') WHERE id = ?`,
306		at, actorID, mrID)
307	return err
308}
309
310// SetSupersededBy records which merge request, by number within the same
311// repository, this one was closed in favour of. n of 0 clears it.
312func (s *Store) SetSupersededBy(mrID, n int64) error {
313	var v any
314	if n != 0 {
315		v = n
316	}
317	_, err := s.DB.Exec("UPDATE merge_requests SET superseded_by = ? WHERE id = ?", v, mrID)
318	return err
319}
320
321// MRsSuperseding returns the merge requests in a repository whose
322// superseded_by names number, oldest first — the reverse of
323// MR.SupersededBy.
324func (s *Store) MRsSuperseding(repoID, number int64) ([]MR, error) {
325	rows, err := s.DB.Query(mrSelect+" WHERE m.repo_id = ? AND m.superseded_by = ? ORDER BY m.number ASC", repoID, number)
326	if err != nil {
327		return nil, err
328	}
329	defer rows.Close()
330	var out []MR
331	for rows.Next() {
332		m, err := scanMR(rows)
333		if err != nil {
334			return nil, err
335		}
336		out = append(out, m)
337	}
338	return out, rows.Err()
339}
340
341// SetMRState moves an MR between states that carry no resolution stamp.
342// Returning to open (a source branch that came back) clears one.
343func (s *Store) SetMRState(mrID int64, state string) error {
344	stamp := ""
345	if state == "open" || state == "source_gone" {
346		stamp = ", merged_at = '', merged_by = NULL, closed_at = '', closed_by = NULL"
347	}
348	res, err := s.DB.Exec(
349		"UPDATE merge_requests SET state = ?"+stamp+", updated_at = strftime('%Y-%m-%dT%H:%M:%fZ','now') WHERE id = ?",
350		state, mrID)
351	if err != nil {
352		return err
353	}
354	if n, _ := res.RowsAffected(); n == 0 {
355		return ErrNotFound
356	}
357	return nil
358}
359
360// UpdateMRHead moves a merge request onto a new head, stales the reviews
361// of the old one, and records the head in the history a range-diff reads.
362// baseSHA is the merge base at this moment; "" when the caller could not
363// work it out, which only costs the range-diff its precision.
364//
365// sameDiff says the new head proposes the change the old one did (a
366// rebase onto a moved target, or the same commits pushed again). Then the
367// fresh reviews of the old head are reviews of this diff and move to the
368// new head rather than going stale (#198). Reviews already stale stay so.
369func (s *Store) UpdateMRHead(mrID int64, headSHA, baseSHA string, sameDiff bool) error {
370	tx, err := s.DB.Begin()
371	if err != nil {
372		return err
373	}
374	defer tx.Rollback()
375	if sameDiff {
376		if _, err := tx.Exec(`
377			UPDATE mr_reviews SET head_sha = ? WHERE mr_id = ? AND stale = 0
378			AND head_sha = (SELECT head_sha FROM merge_requests WHERE id = ?)`,
379			headSHA, mrID, mrID); err != nil {
380			return err
381		}
382	}
383	if _, err := tx.Exec(
384		"UPDATE merge_requests SET head_sha = ?, updated_at = strftime('%Y-%m-%dT%H:%M:%fZ','now') WHERE id = ?",
385		headSHA, mrID); err != nil {
386		return err
387	}
388	if _, err := tx.Exec(
389		"UPDATE mr_reviews SET stale = 1 WHERE mr_id = ? AND head_sha <> ?", mrID, headSHA); err != nil {
390		return err
391	}
392	// Same head twice is a push that changed nothing about this merge
393	// request; it should not add a revision to compare against.
394	var last string
395	tx.QueryRow("SELECT sha FROM mr_heads WHERE mr_id = ? ORDER BY id DESC LIMIT 1", mrID).Scan(&last)
396	if last != headSHA {
397		if _, err := tx.Exec(
398			"INSERT INTO mr_heads (mr_id, sha, base_sha) VALUES (?, ?, ?)", mrID, headSHA, baseSHA); err != nil {
399			return err
400		}
401	}
402	return tx.Commit()
403}
404
405// MRHead is one revision a merge request has had.
406type MRHead struct {
407	SHA       string
408	BaseSHA   string
409	CreatedAt string
410}
411
412// MRHeads returns a merge request's revisions, oldest first.
413func (s *Store) MRHeads(mrID int64) ([]MRHead, error) {
414	rows, err := s.DB.Query(
415		"SELECT sha, base_sha, created_at FROM mr_heads WHERE mr_id = ? ORDER BY id", mrID)
416	if err != nil {
417		return nil, err
418	}
419	defer rows.Close()
420	var out []MRHead
421	for rows.Next() {
422		var h MRHead
423		if err := rows.Scan(&h.SHA, &h.BaseSHA, &h.CreatedAt); err != nil {
424			return nil, err
425		}
426		out = append(out, h)
427	}
428	return out, rows.Err()
429}
430
431// SetMRTarget retargets a merge request and marks every existing review
432// stale, in one transaction. The base of the diff is derived from the
433// target on every read, so nothing else has to move; an approval,
434// though, was of the diff against the old branch.
435func (s *Store) SetMRTarget(mrID int64, targetRef string) error {
436	tx, err := s.DB.Begin()
437	if err != nil {
438		return err
439	}
440	defer tx.Rollback()
441	res, err := tx.Exec(
442		"UPDATE merge_requests SET target_ref = ?, updated_at = strftime('%Y-%m-%dT%H:%M:%fZ','now') WHERE id = ?",
443		targetRef, mrID)
444	if err != nil {
445		return err
446	}
447	if n, _ := res.RowsAffected(); n == 0 {
448		return ErrNotFound
449	}
450	if _, err := tx.Exec("UPDATE mr_reviews SET stale = 1 WHERE mr_id = ?", mrID); err != nil {
451		return err
452	}
453	return tx.Commit()
454}
455
456// MarkSourceGoneForRepo flags every open MR sourced from the repo; called
457// when a fork is deleted. Head refs in the target repos are retained.
458func (s *Store) MarkSourceGoneForRepo(sourceRepoID int64) error {
459	_, err := s.DB.Exec(
460		"UPDATE merge_requests SET state = 'source_gone', updated_at = strftime('%Y-%m-%dT%H:%M:%fZ','now') WHERE source_repo_id = ? AND state = 'open'",
461		sourceRepoID)
462	return err
463}
464
465func (s *Store) AddMRComment(mrID, authorID int64, body, format string) error {
466	_, err := s.DB.Exec(
467		"INSERT INTO mr_comments (mr_id, author_id, body, body_format) VALUES (?, ?, ?, ?)",
468		mrID, authorID, body, format)
469	return err
470}
471
472// UpdateMRText edits title, body, and/or markup format; nil leaves a field
473// unchanged.
474func (s *Store) UpdateMRText(mrID int64, title, body, format *string) error {
475	set, args := []string{}, []any{}
476	if title != nil {
477		set, args = append(set, "title = ?"), append(args, *title)
478	}
479	if body != nil {
480		set, args = append(set, "body = ?"), append(args, *body)
481	}
482	if format != nil {
483		set, args = append(set, "body_format = ?"), append(args, *format)
484	}
485	if len(set) == 0 {
486		return nil
487	}
488	set = append(set, "updated_at = strftime('%Y-%m-%dT%H:%M:%fZ','now')")
489	args = append(args, mrID)
490	res, err := s.DB.Exec("UPDATE merge_requests SET "+strings.Join(set, ", ")+" WHERE id = ?", args...)
491	if err != nil {
492		return err
493	}
494	if n, _ := res.RowsAffected(); n == 0 {
495		return ErrNotFound
496	}
497	return nil
498}
499
500// AddMRSystemComment is the informational counterpart of AddMRComment.
501func (s *Store) AddMRSystemComment(mrID, actorID int64, body string) error {
502	_, err := s.DB.Exec(
503		"INSERT INTO mr_comments (mr_id, author_id, body, kind) VALUES (?, ?, ?, 'system')",
504		mrID, actorID, body)
505	return err
506}
507
508func (s *Store) ListMRComments(mrID int64) ([]IssueComment, error) {
509	rows, err := s.DB.Query(`
510		SELECT CASE WHEN c.kind = 'system' THEN 'system' ELSE u.username END,
511		       c.body, c.body_format, c.created_at, c.kind
512		FROM mr_comments c JOIN users u ON u.id = c.author_id
513		WHERE c.mr_id = ? ORDER BY c.id`, mrID)
514	if err != nil {
515		return nil, err
516	}
517	defer rows.Close()
518	var out []IssueComment
519	for rows.Next() {
520		var c IssueComment
521		if err := rows.Scan(&c.Author, &c.Body, &c.BodyFormat, &c.CreatedAt, &c.Kind); err != nil {
522			return nil, err
523		}
524		out = append(out, c)
525	}
526	return out, rows.Err()
527}
528
529// MRCommentCounts totals, per MR, conversation comments plus diff-thread
530// roots — what the list page shows as one comment count. System comments,
531// diff-thread replies, and pending (unpublished) diff comments do not
532// count. The list handler asks for every row on a page in one call rather
533// than one query per MR.
534func (s *Store) MRCommentCounts(repoID int64, mrIDs []int64) (map[int64]int, error) {
535	out := map[int64]int{}
536	if len(mrIDs) == 0 {
537		return out, nil
538	}
539	ph := "?" + strings.Repeat(",?", len(mrIDs)-1)
540	args := make([]any, 0, len(mrIDs)+1)
541	args = append(args, repoID)
542	for _, id := range mrIDs {
543		args = append(args, id)
544	}
545	add := func(query string) error {
546		rows, err := s.DB.Query(query, args...)
547		if err != nil {
548			return err
549		}
550		defer rows.Close()
551		for rows.Next() {
552			var mrID int64
553			var n int
554			if err := rows.Scan(&mrID, &n); err != nil {
555				return err
556			}
557			out[mrID] += n
558		}
559		return rows.Err()
560	}
561	if err := add(`
562		SELECT c.mr_id, COUNT(*) FROM mr_comments c
563		JOIN merge_requests m ON m.id = c.mr_id
564		WHERE m.repo_id = ? AND c.kind <> 'system' AND c.mr_id IN (` + ph + `)
565		GROUP BY c.mr_id`); err != nil {
566		return nil, err
567	}
568	if err := add(`
569		SELECT c.mr_id, COUNT(*) FROM mr_diff_comments c
570		JOIN merge_requests m ON m.id = c.mr_id
571		WHERE m.repo_id = ? AND c.reply_to IS NULL AND c.pending = 0 AND c.mr_id IN (` + ph + `)
572		GROUP BY c.mr_id`); err != nil {
573		return nil, err
574	}
575	return out, nil
576}
577
578func (s *Store) AddMRReview(mrID, reviewerID int64, verdict, headSHA string) error {
579	_, err := s.DB.Exec(
580		"INSERT INTO mr_reviews (mr_id, reviewer_id, verdict, head_sha) VALUES (?, ?, ?, ?)",
581		mrID, reviewerID, verdict, headSHA)
582	return err
583}
584
585func (s *Store) ListMRReviews(mrID int64) ([]MRReview, error) {
586	rows, err := s.DB.Query(`
587		SELECT u.username, r.verdict, r.head_sha, r.stale, r.created_at
588		FROM mr_reviews r JOIN users u ON u.id = r.reviewer_id
589		WHERE r.mr_id = ? ORDER BY r.id`, mrID)
590	if err != nil {
591		return nil, err
592	}
593	defer rows.Close()
594	var out []MRReview
595	for rows.Next() {
596		var r MRReview
597		var stale int
598		if err := rows.Scan(&r.Reviewer, &r.Verdict, &r.HeadSHA, &stale, &r.CreatedAt); err != nil {
599			return nil, err
600		}
601		r.Stale = stale != 0
602		out = append(out, r)
603	}
604	return out, rows.Err()
605}
606
607// PrimaryVerifiedEmail returns the user's primary email if verified, else "".
608func (s *Store) PrimaryVerifiedEmail(userID int64) (string, error) {
609	var addr string
610	err := s.DB.QueryRow(
611		"SELECT address FROM emails WHERE user_id = ? AND is_primary = 1 AND verified_at IS NOT NULL",
612		userID).Scan(&addr)
613	if errors.Is(err, sql.ErrNoRows) {
614		return "", nil
615	}
616	return addr, err
617}
618
619// ActivityMailAddress returns where activity mail for an account goes:
620// its verified primary address, or "" when there is none or the account
621// turned activity mail off (#194).
622func (s *Store) ActivityMailAddress(userID int64) (string, error) {
623	on, err := s.MailEnabled(userID)
624	if err != nil || !on {
625		return "", err
626	}
627	return s.PrimaryVerifiedEmail(userID)
628}
629
630// PreferredVerifiedEmail returns the primary address if it is verified,
631// otherwise the account's other verified address that sorts first by
632// address; "" if none is verified. Unlike PrimaryVerifiedEmail, a verified
633// secondary counts: an account that verified one address but not its
634// primary still has somewhere to send a login link.
635func (s *Store) PreferredVerifiedEmail(userID int64) (string, error) {
636	var addr string
637	err := s.DB.QueryRow(
638		`SELECT address FROM emails WHERE user_id = ? AND verified_at IS NOT NULL
639		 ORDER BY is_primary DESC, address LIMIT 1`,
640		userID).Scan(&addr)
641	if errors.Is(err, sql.ErrNoRows) {
642		return "", nil
643	}
644	return addr, err
645}