1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
|
package mailinglist
import (
"crypto/sha512"
"database/sql"
"encoding/base64"
"errors"
"fmt"
"io"
"path"
"strings"
"time"
_ "github.com/mattn/go-sqlite3"
"github.com/mediocregopher/blog.mediocregopher.com/srv/cfg"
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 a sqlite3 database in the given
// DataDir.
func NewStore(dataDir cfg.DataDir) (Store, error) {
path := path.Join(dataDir.Path, "mailinglist.sqlite3")
db, err := sql.Open("sqlite3", path)
if err != nil {
return nil, fmt.Errorf("opening sqlite file at %q: %w", path, 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()
}
|
