/
db.go
64 lines (57 loc) · 1.54 KB
/
db.go
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
package verifymanager
import (
"context"
"database/sql"
"github.com/target/goalert/engine/processinglock"
"github.com/target/goalert/util"
)
// DB will manage verification codes.
type DB struct {
lock *processinglock.Lock
insertMessages *sql.Stmt
cleanupExpired *sql.Stmt
}
// Name returns the name of the module.
func (db *DB) Name() string { return "Engine.VerificationManager" }
// NewDB will create a new DB instance, preparing all statements.
func NewDB(ctx context.Context, db *sql.DB) (*DB, error) {
lock, err := processinglock.NewLock(ctx, db, processinglock.Config{
Type: processinglock.TypeVerify,
Version: 2,
})
if err != nil {
return nil, err
}
p := &util.Prepare{DB: db, Ctx: ctx}
return &DB{
lock: lock,
insertMessages: p.P(`
with rows as (
insert into outgoing_messages (message_type, contact_method_id, user_id, user_verification_code_id)
select 'verification_message', contact_method_id, cm.user_id, code.id
from user_verification_codes code
join user_contact_methods cm on cm.id = contact_method_id
where not sent and now() < expires_at
limit 100
for update skip locked
returning user_verification_code_id id
)
update user_verification_codes code
set sent = true
from rows
where code.id = rows.id
`),
cleanupExpired: p.P(`
with rows as (
select id
from user_verification_codes
where now() >= expires_at
limit 100
for update skip locked
)
delete from user_verification_codes code
using rows
where code.id = rows.id
`),
}, p.Err
}