summaryrefslogtreecommitdiff
path: root/srv/src/mailinglist/store.go
diff options
context:
space:
mode:
Diffstat (limited to 'srv/src/mailinglist/store.go')
-rw-r--r--srv/src/mailinglist/store.go240
1 files changed, 240 insertions, 0 deletions
diff --git a/srv/src/mailinglist/store.go b/srv/src/mailinglist/store.go
new file mode 100644
index 0000000..f9790c0
--- /dev/null
+++ b/srv/src/mailinglist/store.go
@@ -0,0 +1,240 @@
+package mailinglist
+
+import (
+ "crypto/sha512"
+ "database/sql"
+ "encoding/base64"
+ "errors"
+ "fmt"
+ "io"
+ "strings"
+ "time"
+
+ _ "github.com/mattn/go-sqlite3"
+ migrate "github.com/rubenv/sql-migrate"
+)
+
+var (
+ // ErrNotFound is used to indicate an email could not be found in the
+ // database.
+ ErrNotFound = errors.New("no record found")
+)
+
+// EmailIterator will iterate through a sequence of emails, returning the next
+// email in the sequence on each call, or returning io.EOF.
+type EmailIterator func() (Email, error)
+
+// Email describes all information related to an email which has yet
+// to be verified.
+type Email struct {
+ Email string
+ SubToken string
+ CreatedAt time.Time
+
+ UnsubToken string
+ VerifiedAt time.Time
+}
+
+// Store is used for storing MailingList related information.
+type Store interface {
+
+ // Set is used to set the information related to an email.
+ Set(Email) error
+
+ // Get will return the record for the given email, or ErrNotFound.
+ Get(email string) (Email, error)
+
+ // GetBySubToken will return the record for the given SubToken, or
+ // ErrNotFound.
+ GetBySubToken(subToken string) (Email, error)
+
+ // GetByUnsubToken will return the record for the given UnsubToken, or
+ // ErrNotFound.
+ GetByUnsubToken(unsubToken string) (Email, error)
+
+ // Delete will delete the record for the given email.
+ Delete(email string) error
+
+ // GetAll returns all emails for which there is a record.
+ GetAll() EmailIterator
+
+ Close() error
+}
+
+var migrations = []*migrate.Migration{
+ &migrate.Migration{
+ Id: "1",
+ Up: []string{
+ `CREATE TABLE emails (
+ id TEXT PRIMARY KEY,
+ email TEXT NOT NULL,
+ sub_token TEXT NOT NULL,
+ created_at INTEGER NOT NULL,
+
+ unsub_token TEXT,
+ verified_at INTEGER
+ )`,
+ },
+ Down: []string{"DROP TABLE emails"},
+ },
+}
+
+type store struct {
+ db *sql.DB
+}
+
+// NewStore initializes a new store using the given SQL DB instance.
+func NewStore(dbFile string) (Store, error) {
+
+ db, err := sql.Open("sqlite3", dbFile)
+ if err != nil {
+ return nil, fmt.Errorf("opening sqlite file: %w", err)
+ }
+
+ migrations := &migrate.MemoryMigrationSource{Migrations: migrations}
+
+ if _, err := migrate.Exec(db, "sqlite3", migrations, migrate.Up); err != nil {
+ return nil, fmt.Errorf("running migrations: %w", err)
+ }
+
+ return &store{
+ db: db,
+ }, nil
+}
+
+func (s *store) emailID(email string) string {
+ email = strings.ToLower(email)
+ h := sha512.New()
+ h.Write([]byte(email))
+ return base64.URLEncoding.EncodeToString(h.Sum(nil))
+}
+
+func (s *store) Set(email Email) error {
+ _, err := s.db.Exec(
+ `INSERT INTO emails (
+ id, email, sub_token, created_at, unsub_token, verified_at
+ )
+ VALUES
+ (?, ?, ?, ?, ?, ?)
+ ON CONFLICT (id) DO UPDATE SET
+ email=excluded.email,
+ sub_token=excluded.sub_token,
+ unsub_token=excluded.unsub_token,
+ verified_at=excluded.verified_at
+ `,
+ s.emailID(email.Email),
+ email.Email,
+ email.SubToken,
+ email.CreatedAt.Unix(),
+ email.UnsubToken,
+ sql.NullInt64{
+ Int64: email.VerifiedAt.Unix(),
+ Valid: !email.VerifiedAt.IsZero(),
+ },
+ )
+
+ return err
+}
+
+var scanCols = []string{
+ "email", "sub_token", "created_at", "unsub_token", "verified_at",
+}
+
+type row interface {
+ Scan(...interface{}) error
+}
+
+func (s *store) scanRow(row row) (Email, error) {
+ var email Email
+ var createdAt int64
+ var verifiedAt sql.NullInt64
+
+ err := row.Scan(
+ &email.Email,
+ &email.SubToken,
+ &createdAt,
+ &email.UnsubToken,
+ &verifiedAt,
+ )
+ if err != nil {
+ return Email{}, err
+ }
+
+ email.CreatedAt = time.Unix(createdAt, 0)
+ if verifiedAt.Valid {
+ email.VerifiedAt = time.Unix(verifiedAt.Int64, 0)
+ }
+
+ return email, nil
+}
+
+func (s *store) scanSingleRow(row *sql.Row) (Email, error) {
+ email, err := s.scanRow(row)
+ if errors.Is(err, sql.ErrNoRows) {
+ return Email{}, ErrNotFound
+ }
+
+ return email, err
+}
+
+func (s *store) Get(email string) (Email, error) {
+ row := s.db.QueryRow(
+ `SELECT `+strings.Join(scanCols, ",")+`
+ FROM emails
+ WHERE id=?`,
+ s.emailID(email),
+ )
+
+ return s.scanSingleRow(row)
+}
+
+func (s *store) GetBySubToken(subToken string) (Email, error) {
+ row := s.db.QueryRow(
+ `SELECT `+strings.Join(scanCols, ",")+`
+ FROM emails
+ WHERE sub_token=?`,
+ subToken,
+ )
+
+ return s.scanSingleRow(row)
+}
+
+func (s *store) GetByUnsubToken(unsubToken string) (Email, error) {
+ row := s.db.QueryRow(
+ `SELECT `+strings.Join(scanCols, ",")+`
+ FROM emails
+ WHERE unsub_token=?`,
+ unsubToken,
+ )
+
+ return s.scanSingleRow(row)
+}
+
+func (s *store) Delete(email string) error {
+ _, err := s.db.Exec(
+ `DELETE FROM emails WHERE id=?`,
+ s.emailID(email),
+ )
+ return err
+}
+
+func (s *store) GetAll() EmailIterator {
+ rows, err := s.db.Query(
+ `SELECT ` + strings.Join(scanCols, ",") + `
+ FROM emails`,
+ )
+
+ return func() (Email, error) {
+ if err != nil {
+ return Email{}, err
+
+ } else if !rows.Next() {
+ return Email{}, io.EOF
+ }
+ return s.scanRow(rows)
+ }
+}
+
+func (s *store) Close() error {
+ return s.db.Close()
+}