457 lines
10 KiB
Go
457 lines
10 KiB
Go
package back
|
|
|
|
import (
|
|
"context"
|
|
"errors"
|
|
"fmt"
|
|
"log"
|
|
"os"
|
|
|
|
"crawshaw.io/sqlite"
|
|
"crawshaw.io/sqlite/sqlitex"
|
|
"github.com/matthewhartstonge/argon2"
|
|
"github.com/rickb777/date"
|
|
)
|
|
|
|
var ErrNotFound = errors.New("not found")
|
|
|
|
type UnauthorizedError struct {
|
|
EventID string
|
|
AdminCode string
|
|
}
|
|
|
|
func (u UnauthorizedError) Error() string {
|
|
return fmt.Sprintf("unauthorized: EventID = %s, AdminCode = %s", u.EventID, u.AdminCode)
|
|
}
|
|
|
|
func hash(password string) (string, error) {
|
|
hashed, err := (&argon2.Config{
|
|
HashLength: 32,
|
|
// We don't need a salt because our random passwords are not
|
|
// susceptible to dictionary attacks.
|
|
SaltLength: 0,
|
|
TimeCost: 3,
|
|
MemoryCost: 64 * 1024,
|
|
Parallelism: 4,
|
|
Mode: argon2.ModeArgon2id,
|
|
Version: argon2.Version13,
|
|
}).HashEncoded([]byte(password))
|
|
return string(hashed), err
|
|
}
|
|
|
|
type GenString func(length int) (string, error)
|
|
|
|
type Store struct {
|
|
pool *sqlitex.Pool
|
|
genString GenString
|
|
}
|
|
|
|
func NewStore(filename string, genString GenString) (*Store, error) {
|
|
var needCreate bool
|
|
if _, err := os.Stat(filename); os.IsNotExist(err) {
|
|
needCreate = true
|
|
}
|
|
// If the file exists, then assume it was created properly.
|
|
|
|
pool, err := sqlitex.Open(filename, 0, 10)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
store := &Store{
|
|
pool: pool,
|
|
genString: genString,
|
|
}
|
|
if needCreate {
|
|
log.Println("creating schema")
|
|
err = store.createSchema()
|
|
if err != nil {
|
|
defer pool.Close()
|
|
return nil, err
|
|
}
|
|
}
|
|
|
|
return store, nil
|
|
}
|
|
|
|
func NewMemoryStore(genString GenString) (*Store, error) {
|
|
pool, err := sqlitex.Open("file::memory:?mode=memory&cache=shared", 0, 10)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
store := &Store{
|
|
pool: pool,
|
|
genString: genString,
|
|
}
|
|
|
|
err = store.createSchema()
|
|
if err != nil {
|
|
defer pool.Close()
|
|
return nil, err
|
|
}
|
|
return store, nil
|
|
}
|
|
|
|
func (s *Store) Close() error {
|
|
return s.pool.Close()
|
|
}
|
|
|
|
const schema = `
|
|
CREATE TABLE event (
|
|
id TEXT NOT NULL PRIMARY KEY,
|
|
admin_code_hash TEXT NOT NULL,
|
|
name TEXT NOT NULL,
|
|
description TEXT NOT NULL,
|
|
earliest_date DATE NOT NULL,
|
|
latest_date DATE NOT NULL
|
|
);
|
|
|
|
CREATE TABLE response (
|
|
id TEXT NOT NULL,
|
|
event_id TEXT NOT NULL,
|
|
guest_name TEXT NOT NULL,
|
|
|
|
PRIMARY KEY (id, event_id),
|
|
FOREIGN KEY (event_id) REFERENCES event(id)
|
|
);
|
|
|
|
CREATE TABLE response_time (
|
|
event_id TEXT NOT NULL,
|
|
response_id TEXT NOT NULL,
|
|
date DATE NOT NULL,
|
|
time INTEGER NOT NULL,
|
|
|
|
CHECK (0 <= time < 24),
|
|
UNIQUE (response_id, date, time),
|
|
FOREIGN KEY(event_id, response_id) REFERENCES response(event_id, id)
|
|
);
|
|
|
|
CREATE TRIGGER enforce_valid_response_dates
|
|
BEFORE INSERT ON response_time
|
|
BEGIN
|
|
SELECT
|
|
CASE
|
|
WHEN NEW.date NOT BETWEEN event.earliest_date AND event.latest_date
|
|
THEN RAISE(ABORT, 'response date is out of range')
|
|
END
|
|
FROM response
|
|
JOIN event ON response.event_id = event.id
|
|
WHERE response.id = NEW.response_id;
|
|
END;`
|
|
|
|
func (s *Store) createSchema() error {
|
|
conn := s.pool.Get(context.Background())
|
|
defer s.pool.Put(conn)
|
|
return sqlitex.ExecScript(conn, schema)
|
|
}
|
|
|
|
type CreateEventCommand struct {
|
|
Name string
|
|
Description string
|
|
Earliest, Latest date.Date
|
|
}
|
|
|
|
type CreateEventResult struct {
|
|
EventID, AdminCode string
|
|
}
|
|
|
|
const dbDateLayout = "2006-01-02"
|
|
|
|
func (s *Store) CreateEvent(ctx context.Context, cmd CreateEventCommand) (result CreateEventResult, err error) {
|
|
const idLength = 10
|
|
const adminCodeLength = 10
|
|
|
|
conn := s.pool.Get(ctx)
|
|
defer s.pool.Put(conn)
|
|
|
|
id, err := s.genString(idLength)
|
|
if err != nil {
|
|
return
|
|
}
|
|
|
|
adminCode, err := s.genString(adminCodeLength)
|
|
if err != nil {
|
|
return
|
|
}
|
|
|
|
adminCodeHash, err := hash(adminCode)
|
|
if err != nil {
|
|
return
|
|
}
|
|
|
|
const query = `
|
|
INSERT INTO event(id, admin_code_hash, name, description, earliest_date, latest_date)
|
|
VALUES (?, ?, ?, ?, ?, ?);`
|
|
err = sqlitex.Exec(conn, query, nil,
|
|
id,
|
|
adminCodeHash,
|
|
cmd.Name,
|
|
cmd.Description,
|
|
cmd.Earliest.Format(dbDateLayout),
|
|
cmd.Latest.Format(dbDateLayout),
|
|
)
|
|
if err != nil {
|
|
return
|
|
}
|
|
|
|
result.AdminCode = adminCode
|
|
result.EventID = id
|
|
return
|
|
}
|
|
|
|
type CheckEventAdminCodeQuery struct {
|
|
EventID string
|
|
AdminCode string
|
|
}
|
|
|
|
func (s *Store) AuthorizeEventAdmin(ctx context.Context, query CheckEventAdminCodeQuery) error {
|
|
conn := s.pool.Get(ctx)
|
|
defer s.pool.Put(conn)
|
|
|
|
adminCodeHash, err := hash(query.AdminCode)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
|
|
const dbQuery = `
|
|
SELECT 1
|
|
FROM event
|
|
WHERE id = ? AND admin_code_hash = ?;`
|
|
var doesMatch bool
|
|
err = sqlitex.Exec(conn, dbQuery,
|
|
func(stmt *sqlite.Stmt) error {
|
|
doesMatch = true
|
|
return nil
|
|
}, query.EventID, adminCodeHash)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
if !doesMatch {
|
|
return UnauthorizedError{
|
|
EventID: query.EventID,
|
|
AdminCode: query.AdminCode,
|
|
}
|
|
}
|
|
return nil
|
|
}
|
|
|
|
type GetEventMetadataQuery struct {
|
|
EventID string
|
|
}
|
|
|
|
type GetEventMetadataResult struct {
|
|
Name string
|
|
Description string
|
|
Earliest, Latest date.Date
|
|
}
|
|
|
|
func (s *Store) GetEventMetadata(ctx context.Context, query GetEventMetadataQuery) (GetEventMetadataResult, error) {
|
|
conn := s.pool.Get(ctx)
|
|
defer s.pool.Put(conn)
|
|
|
|
const dbQuery = `
|
|
SELECT name, description, earliest_date, latest_date
|
|
FROM event
|
|
WHERE id = ?;`
|
|
var result GetEventMetadataResult
|
|
var found bool
|
|
err := sqlitex.Exec(conn, dbQuery,
|
|
func(stmt *sqlite.Stmt) error {
|
|
found = true
|
|
|
|
result.Name = stmt.ColumnText(0)
|
|
result.Description = stmt.ColumnText(1)
|
|
earliestDateString := stmt.ColumnText(2)
|
|
latestDateString := stmt.ColumnText(3)
|
|
|
|
var err error
|
|
result.Earliest, err = date.Parse(dbDateLayout, earliestDateString)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
result.Latest, err = date.Parse(dbDateLayout, latestDateString)
|
|
return err
|
|
}, query.EventID)
|
|
if err != nil {
|
|
return GetEventMetadataResult{}, err
|
|
}
|
|
if !found {
|
|
return GetEventMetadataResult{}, ErrNotFound
|
|
}
|
|
return result, nil
|
|
}
|
|
|
|
type GetEventResponseSummaryQuery struct {
|
|
EventID string
|
|
}
|
|
|
|
type GetEventResponseSummaryResult struct {
|
|
RespondentNames []string
|
|
PerHourCounts map[date.Date]map[int]int
|
|
}
|
|
|
|
func (s *Store) GetEventResponseSummary(ctx context.Context, query GetEventResponseSummaryQuery) (GetEventResponseSummaryResult, error) {
|
|
conn := s.pool.Get(ctx)
|
|
defer s.pool.Put(conn)
|
|
|
|
var found bool
|
|
|
|
const eventIDQuery = `
|
|
SELECT 1
|
|
FROM event
|
|
WHERE id = ?`
|
|
var err = sqlitex.Exec(conn, eventIDQuery,
|
|
func(stmt *sqlite.Stmt) error {
|
|
found = true
|
|
return nil
|
|
}, query.EventID)
|
|
if err != nil {
|
|
return GetEventResponseSummaryResult{}, err
|
|
}
|
|
if !found {
|
|
return GetEventResponseSummaryResult{}, ErrNotFound
|
|
}
|
|
|
|
var result GetEventResponseSummaryResult
|
|
const respondentNameQuery = `
|
|
SELECT guest_name
|
|
FROM response
|
|
WHERE event_id = ?
|
|
ORDER BY guest_name ASC;`
|
|
err = sqlitex.Exec(conn, respondentNameQuery,
|
|
func(stmt *sqlite.Stmt) error {
|
|
result.RespondentNames = append(result.RespondentNames, stmt.ColumnText(0))
|
|
return nil
|
|
}, query.EventID)
|
|
if err != nil {
|
|
return GetEventResponseSummaryResult{}, err
|
|
}
|
|
|
|
result.PerHourCounts = make(map[date.Date]map[int]int)
|
|
const perHourCountQuery = `
|
|
SELECT date, time, COUNT(*)
|
|
FROM response_time
|
|
WHERE event_id = ?
|
|
GROUP BY date, time;`
|
|
err = sqlitex.Exec(conn, perHourCountQuery,
|
|
func(stmt *sqlite.Stmt) error {
|
|
d, err := date.Parse(dbDateLayout, stmt.ColumnText(0))
|
|
if err != nil {
|
|
return err
|
|
}
|
|
if result.PerHourCounts[d] == nil {
|
|
result.PerHourCounts[d] = make(map[int]int)
|
|
}
|
|
result.PerHourCounts[d][stmt.ColumnInt(1)] = stmt.ColumnInt(2)
|
|
return nil
|
|
}, query.EventID)
|
|
if err != nil {
|
|
return GetEventResponseSummaryResult{}, err
|
|
}
|
|
return result, nil
|
|
}
|
|
|
|
type CreateEventResponseCommand struct {
|
|
EventID string
|
|
GuestName string
|
|
DateHours map[date.Date]map[int]struct{}
|
|
}
|
|
|
|
type CreateEventResponseResult struct {
|
|
ResponseID string
|
|
}
|
|
|
|
func (s *Store) CreateEventResponse(ctx context.Context, cmd CreateEventResponseCommand) (result CreateEventResponseResult, err error) {
|
|
const responseIDLength = 10
|
|
|
|
conn := s.pool.Get(ctx)
|
|
defer s.pool.Put(conn)
|
|
|
|
defer sqlitex.Save(conn)(&err)
|
|
responseID, err := s.genString(responseIDLength)
|
|
if err != nil {
|
|
return
|
|
}
|
|
|
|
const responseQuery = `
|
|
INSERT INTO response(event_id, id, guest_name)
|
|
VALUES (?, ?, ?);`
|
|
err = sqlitex.Exec(conn, responseQuery, nil, cmd.EventID, responseID, cmd.GuestName)
|
|
if err != nil {
|
|
return
|
|
}
|
|
|
|
const responseTimeQuery = `
|
|
INSERT INTO response_time(event_id, response_id, date, time)
|
|
VALUES (?, ?, ?, ?);`
|
|
for d, hs := range cmd.DateHours {
|
|
for h := range hs {
|
|
err = sqlitex.Exec(conn, responseTimeQuery, nil,
|
|
cmd.EventID, responseID, d.Format(dbDateLayout), h)
|
|
if err != nil {
|
|
return
|
|
}
|
|
}
|
|
}
|
|
|
|
return CreateEventResponseResult{
|
|
ResponseID: responseID,
|
|
}, nil
|
|
}
|
|
|
|
type GetEventResponseQuery struct {
|
|
EventID string
|
|
ResponseID string
|
|
}
|
|
|
|
type GetEventResponseResult struct {
|
|
GuestName string
|
|
DateHours map[date.Date]map[int]struct{}
|
|
}
|
|
|
|
func (s *Store) GetEventResponse(ctx context.Context, query GetEventResponseQuery) (GetEventResponseResult, error) {
|
|
conn := s.pool.Get(ctx)
|
|
defer s.pool.Put(conn)
|
|
|
|
var found bool
|
|
var result GetEventResponseResult
|
|
|
|
const responseQuery = `
|
|
SELECT guest_name
|
|
FROM response
|
|
WHERE id = ? AND event_id = ?;`
|
|
var err = sqlitex.Exec(conn, responseQuery,
|
|
func(stmt *sqlite.Stmt) error {
|
|
found = true
|
|
result.GuestName = stmt.ColumnText(0)
|
|
return nil
|
|
}, query.ResponseID, query.EventID)
|
|
if err != nil {
|
|
return GetEventResponseResult{}, err
|
|
}
|
|
if !found {
|
|
return GetEventResponseResult{}, ErrNotFound
|
|
}
|
|
|
|
result.DateHours = make(map[date.Date]map[int]struct{})
|
|
const responseTimeQuery = `
|
|
SELECT date, time
|
|
FROM response_time
|
|
WHERE event_id = ? AND response_id = ?`
|
|
err = sqlitex.Exec(conn, responseTimeQuery,
|
|
func(stmt *sqlite.Stmt) error {
|
|
d, err := date.Parse(dbDateLayout, stmt.ColumnText(0))
|
|
if err != nil {
|
|
return err
|
|
}
|
|
if result.DateHours[d] == nil {
|
|
result.DateHours[d] = make(map[int]struct{})
|
|
}
|
|
result.DateHours[d][stmt.ColumnInt(1)] = struct{}{}
|
|
return nil
|
|
}, query.EventID, query.ResponseID)
|
|
if err != nil {
|
|
return GetEventResponseResult{}, err
|
|
}
|
|
return result, nil
|
|
}
|