internal/store/mailreply.go

 1package store
 2
 3import (
 4	"database/sql"
 5	"errors"
 6	"strings"
 7	"time"
 8)
 9
10// CreatedAt is when the account (table "users") or repository ("repos")
11// with id was created.
12func (s *Store) CreatedAt(table string, id int64) (time.Time, error) {
13	if table != "users" && table != "repos" {
14		return time.Time{}, errors.New("CreatedAt: unknown table " + table)
15	}
16	var v string
17	err := s.DB.QueryRow("SELECT created_at FROM "+table+" WHERE id = ?", id).Scan(&v)
18	if errors.Is(err, sql.ErrNoRows) {
19		return time.Time{}, ErrNotFound
20	}
21	if err != nil {
22		return time.Time{}, err
23	}
24	return time.Parse("2006-01-02T15:04:05.000Z", v)
25}
26
27// ClaimMailReply records that the reply identified by key is being
28// posted. False means an earlier fetch of the same message already
29// claimed it.
30func (s *Store) ClaimMailReply(key string) (bool, error) {
31	res, err := s.DB.Exec("INSERT INTO mail_replies (message_key) VALUES (?) ON CONFLICT DO NOTHING", key)
32	if err != nil {
33		return false, err
34	}
35	n, err := res.RowsAffected()
36	return n == 1, err
37}
38
39// ReleaseMailReply drops a claim whose comment was not posted, so the
40// message can be tried again.
41func (s *Store) ReleaseMailReply(key string) error {
42	_, err := s.DB.Exec("DELETE FROM mail_replies WHERE message_key = ?", key)
43	return err
44}
45
46// PruneMailReplies drops claims older than before. A reply older than a
47// reply token's lifetime is refused on its token, so its claim has no
48// work left to do.
49func (s *Store) PruneMailReplies(before time.Time) error {
50	_, err := s.DB.Exec("DELETE FROM mail_replies WHERE created_at < ?", fmtTime(before))
51	return err
52}
53
54// VerifiedEmailOf reports whether address is a verified address of the
55// account, ignoring case.
56func (s *Store) VerifiedEmailOf(userID int64, address string) (bool, error) {
57	var n int
58	err := s.DB.QueryRow(
59		"SELECT COUNT(*) FROM emails WHERE user_id = ? AND verified_at IS NOT NULL AND lower(address) = ?",
60		userID, strings.ToLower(address)).Scan(&n)
61	return n > 0, err
62}