From eed10ce514f28e4acf772f76c92ca05eebec105f Mon Sep 17 00:00:00 2001 From: Brian Picciano Date: Thu, 5 May 2022 21:20:22 -0600 Subject: Fix various problems with the srv build --- srv/src/mailinglist/store.go | 240 +++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 240 insertions(+) create mode 100644 srv/src/mailinglist/store.go (limited to 'srv/src/mailinglist/store.go') 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() +} -- cgit v1.2.3