363 lines
11 KiB
Go
363 lines
11 KiB
Go
package store
|
|
|
|
import (
|
|
"context"
|
|
"database/sql"
|
|
"errors"
|
|
"time"
|
|
|
|
_ "modernc.org/sqlite"
|
|
)
|
|
|
|
const schema = `
|
|
create table if not exists users (
|
|
id text primary key,
|
|
name text not null,
|
|
usos_token blob,
|
|
usos_secret blob,
|
|
feed_hash text unique,
|
|
feed_token blob,
|
|
archive integer not null default 0,
|
|
backfilled integer not null default 0,
|
|
synced_at integer,
|
|
sync_error text not null default '',
|
|
created_at integer not null
|
|
);
|
|
create table if not exists sessions (
|
|
token_hash text primary key,
|
|
user_id text not null references users (id) on delete cascade,
|
|
expires_at integer not null
|
|
);
|
|
create table if not exists events (
|
|
user_id text not null references users (id) on delete cascade,
|
|
uid text not null,
|
|
starts_at integer not null,
|
|
ends_at integer not null,
|
|
title text not null,
|
|
kind text not null,
|
|
grp text not null,
|
|
room text not null,
|
|
building text not null,
|
|
address text not null,
|
|
lecturers text not null,
|
|
url text not null,
|
|
updated_at integer not null,
|
|
primary key (user_id, uid)
|
|
);
|
|
create index if not exists events_by_start on events (user_id, starts_at);
|
|
create table if not exists lecturers (
|
|
id text primary key,
|
|
name text not null
|
|
);
|
|
create table if not exists buildings (
|
|
id text primary key,
|
|
name text not null,
|
|
address text not null
|
|
);
|
|
`
|
|
|
|
var ErrNotFound = errors.New("not found")
|
|
|
|
type Store struct {
|
|
db *sql.DB
|
|
}
|
|
|
|
type User struct {
|
|
ID string
|
|
Name string
|
|
USOSToken []byte
|
|
USOSSecret []byte
|
|
FeedToken []byte
|
|
Archive bool
|
|
Backfilled bool
|
|
SyncedAt time.Time
|
|
SyncError string
|
|
}
|
|
|
|
func (u User) Connected() bool {
|
|
return len(u.USOSToken) > 0 && len(u.USOSSecret) > 0
|
|
}
|
|
|
|
type Event struct {
|
|
UID string
|
|
Start time.Time
|
|
End time.Time
|
|
Title string
|
|
Kind string
|
|
Group string
|
|
Room string
|
|
Building string
|
|
Address string
|
|
Lecturers string
|
|
URL string
|
|
UpdatedAt time.Time
|
|
}
|
|
|
|
type Building struct {
|
|
Name string
|
|
Address string
|
|
}
|
|
|
|
func Open(path string) (*Store, error) {
|
|
dsn := "file:" + path + "?_pragma=journal_mode(WAL)&_pragma=busy_timeout(5000)&_pragma=synchronous(NORMAL)&_pragma=foreign_keys(1)"
|
|
db, err := sql.Open("sqlite", dsn)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
db.SetMaxOpenConns(1)
|
|
if _, err := db.Exec(schema); err != nil {
|
|
return nil, err
|
|
}
|
|
return &Store{db: db}, nil
|
|
}
|
|
|
|
func (s *Store) Close() error {
|
|
return s.db.Close()
|
|
}
|
|
|
|
const userColumns = `id, name, usos_token, usos_secret, feed_token, archive, backfilled, coalesce(synced_at, 0), sync_error`
|
|
|
|
func scanUser(row *sql.Row) (User, error) {
|
|
var (
|
|
user User
|
|
synced int64
|
|
)
|
|
err := row.Scan(&user.ID, &user.Name, &user.USOSToken, &user.USOSSecret, &user.FeedToken, &user.Archive, &user.Backfilled, &synced, &user.SyncError)
|
|
if errors.Is(err, sql.ErrNoRows) {
|
|
return user, ErrNotFound
|
|
}
|
|
if synced > 0 {
|
|
user.SyncedAt = time.Unix(synced, 0)
|
|
}
|
|
return user, err
|
|
}
|
|
|
|
func (s *Store) User(ctx context.Context, id string) (User, error) {
|
|
return scanUser(s.db.QueryRowContext(ctx, `select `+userColumns+` from users where id = ?`, id))
|
|
}
|
|
|
|
func (s *Store) UserByFeed(ctx context.Context, feedHash string) (User, error) {
|
|
return scanUser(s.db.QueryRowContext(ctx, `select `+userColumns+` from users where feed_hash = ?`, feedHash))
|
|
}
|
|
|
|
func (s *Store) Connect(ctx context.Context, id, name string, token, secret []byte) error {
|
|
_, err := s.db.ExecContext(ctx, `
|
|
insert into users (id, name, usos_token, usos_secret, created_at) values (?, ?, ?, ?, ?)
|
|
on conflict (id) do update set name = excluded.name, usos_token = excluded.usos_token,
|
|
usos_secret = excluded.usos_secret, sync_error = ''`,
|
|
id, name, token, secret, time.Now().Unix())
|
|
return err
|
|
}
|
|
|
|
func (s *Store) SetFeed(ctx context.Context, id, feedHash string, sealed []byte) error {
|
|
var hash any
|
|
if feedHash != "" {
|
|
hash = feedHash
|
|
}
|
|
_, err := s.db.ExecContext(ctx, `update users set feed_hash = ?, feed_token = ? where id = ?`, hash, sealed, id)
|
|
return err
|
|
}
|
|
|
|
func (s *Store) SetArchive(ctx context.Context, id string, on bool) error {
|
|
_, err := s.db.ExecContext(ctx, `update users set archive = ?, backfilled = 0 where id = ?`, on, id)
|
|
return err
|
|
}
|
|
|
|
func (s *Store) SetBackfilled(ctx context.Context, id string) error {
|
|
_, err := s.db.ExecContext(ctx, `update users set backfilled = 1 where id = ?`, id)
|
|
return err
|
|
}
|
|
|
|
func (s *Store) SetSynced(ctx context.Context, id string, at time.Time, failure string) error {
|
|
_, err := s.db.ExecContext(ctx, `update users set synced_at = ?, sync_error = ? where id = ?`, at.Unix(), failure, id)
|
|
return err
|
|
}
|
|
|
|
func (s *Store) DropTokens(ctx context.Context, id, failure string) error {
|
|
_, err := s.db.ExecContext(ctx, `update users set usos_token = null, usos_secret = null, sync_error = ? where id = ?`, failure, id)
|
|
return err
|
|
}
|
|
|
|
func (s *Store) Disconnect(ctx context.Context, id string) error {
|
|
tx, err := s.db.BeginTx(ctx, nil)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
defer tx.Rollback()
|
|
if _, err := tx.ExecContext(ctx, `
|
|
update users set usos_token = null, usos_secret = null, feed_hash = null, feed_token = null, sync_error = ''
|
|
where id = ?`, id); err != nil {
|
|
return err
|
|
}
|
|
if _, err := tx.ExecContext(ctx, `delete from sessions where user_id = ?`, id); err != nil {
|
|
return err
|
|
}
|
|
return tx.Commit()
|
|
}
|
|
|
|
func (s *Store) DeleteUser(ctx context.Context, id string) error {
|
|
_, err := s.db.ExecContext(ctx, `delete from users where id = ?`, id)
|
|
return err
|
|
}
|
|
|
|
func (s *Store) DueUsers(ctx context.Context, syncedBefore time.Time) ([]string, error) {
|
|
rows, err := s.db.QueryContext(ctx, `
|
|
select id from users
|
|
where usos_token is not null and (synced_at is null or synced_at < ?)
|
|
order by coalesce(synced_at, 0)`, syncedBefore.Unix())
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
var ids []string
|
|
for rows.Next() {
|
|
var id string
|
|
if err := rows.Scan(&id); err != nil {
|
|
return nil, err
|
|
}
|
|
ids = append(ids, id)
|
|
}
|
|
return ids, rows.Err()
|
|
}
|
|
|
|
func (s *Store) CreateSession(ctx context.Context, tokenHash, userID string, expires time.Time) error {
|
|
if _, err := s.db.ExecContext(ctx, `delete from sessions where expires_at < ?`, time.Now().Unix()); err != nil {
|
|
return err
|
|
}
|
|
_, err := s.db.ExecContext(ctx, `insert into sessions (token_hash, user_id, expires_at) values (?, ?, ?)`, tokenHash, userID, expires.Unix())
|
|
return err
|
|
}
|
|
|
|
func (s *Store) SessionUser(ctx context.Context, tokenHash string) (User, error) {
|
|
return scanUser(s.db.QueryRowContext(ctx, `
|
|
select `+userColumns+` from users
|
|
where id = (select user_id from sessions where token_hash = ? and expires_at > ?)`, tokenHash, time.Now().Unix()))
|
|
}
|
|
|
|
func (s *Store) ReplaceWindow(ctx context.Context, userID string, from, to time.Time, events []Event) error {
|
|
tx, err := s.db.BeginTx(ctx, nil)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
defer tx.Rollback()
|
|
stale, err := windowUIDs(ctx, tx, userID, from, to)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
now := time.Now().Unix()
|
|
for _, event := range events {
|
|
if _, err := tx.ExecContext(ctx, `
|
|
insert into events (user_id, uid, starts_at, ends_at, title, kind, grp, room, building, address, lecturers, url, updated_at)
|
|
values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
|
|
on conflict (user_id, uid) do update set
|
|
starts_at = excluded.starts_at, ends_at = excluded.ends_at, title = excluded.title, kind = excluded.kind,
|
|
grp = excluded.grp, room = excluded.room, building = excluded.building, address = excluded.address,
|
|
lecturers = excluded.lecturers, url = excluded.url, updated_at = excluded.updated_at
|
|
where (starts_at, ends_at, title, kind, grp, room, building, address, lecturers, url) is not
|
|
(excluded.starts_at, excluded.ends_at, excluded.title, excluded.kind, excluded.grp, excluded.room,
|
|
excluded.building, excluded.address, excluded.lecturers, excluded.url)`,
|
|
userID, event.UID, event.Start.Unix(), event.End.Unix(), event.Title, event.Kind, event.Group, event.Room,
|
|
event.Building, event.Address, event.Lecturers, event.URL, now); err != nil {
|
|
return err
|
|
}
|
|
delete(stale, event.UID)
|
|
}
|
|
for uid := range stale {
|
|
if _, err := tx.ExecContext(ctx, `delete from events where user_id = ? and uid = ?`, userID, uid); err != nil {
|
|
return err
|
|
}
|
|
}
|
|
return tx.Commit()
|
|
}
|
|
|
|
func windowUIDs(ctx context.Context, tx *sql.Tx, userID string, from, to time.Time) (map[string]struct{}, error) {
|
|
rows, err := tx.QueryContext(ctx, `select uid from events where user_id = ? and starts_at >= ? and starts_at < ?`, userID, from.Unix(), to.Unix())
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
uids := map[string]struct{}{}
|
|
for rows.Next() {
|
|
var uid string
|
|
if err := rows.Scan(&uid); err != nil {
|
|
return nil, err
|
|
}
|
|
uids[uid] = struct{}{}
|
|
}
|
|
return uids, rows.Err()
|
|
}
|
|
|
|
func (s *Store) DeleteEventsBefore(ctx context.Context, userID string, before time.Time) error {
|
|
_, err := s.db.ExecContext(ctx, `delete from events where user_id = ? and starts_at < ?`, userID, before.Unix())
|
|
return err
|
|
}
|
|
|
|
func (s *Store) Events(ctx context.Context, userID string) ([]Event, error) {
|
|
rows, err := s.db.QueryContext(ctx, `
|
|
select uid, starts_at, ends_at, title, kind, grp, room, building, address, lecturers, url, updated_at
|
|
from events where user_id = ? order by starts_at, uid`, userID)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
var events []Event
|
|
for rows.Next() {
|
|
var (
|
|
event Event
|
|
start, end, updated int64
|
|
)
|
|
if err := rows.Scan(&event.UID, &start, &end, &event.Title, &event.Kind, &event.Group, &event.Room,
|
|
&event.Building, &event.Address, &event.Lecturers, &event.URL, &updated); err != nil {
|
|
return nil, err
|
|
}
|
|
event.Start, event.End, event.UpdatedAt = time.Unix(start, 0), time.Unix(end, 0), time.Unix(updated, 0)
|
|
events = append(events, event)
|
|
}
|
|
return events, rows.Err()
|
|
}
|
|
|
|
func (s *Store) EventSpan(ctx context.Context, userID string) (count int, first time.Time, err error) {
|
|
var earliest int64
|
|
err = s.db.QueryRowContext(ctx, `select count(*), coalesce(min(starts_at), 0) from events where user_id = ?`, userID).Scan(&count, &earliest)
|
|
if earliest > 0 {
|
|
first = time.Unix(earliest, 0)
|
|
}
|
|
return count, first, err
|
|
}
|
|
|
|
func (s *Store) LecturerNames(ctx context.Context, ids []string) (map[string]string, error) {
|
|
names := map[string]string{}
|
|
for _, id := range ids {
|
|
var name string
|
|
err := s.db.QueryRowContext(ctx, `select name from lecturers where id = ?`, id).Scan(&name)
|
|
if errors.Is(err, sql.ErrNoRows) {
|
|
continue
|
|
}
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
names[id] = name
|
|
}
|
|
return names, nil
|
|
}
|
|
|
|
func (s *Store) SaveLecturers(ctx context.Context, names map[string]string) error {
|
|
for id, name := range names {
|
|
if _, err := s.db.ExecContext(ctx, `insert or replace into lecturers (id, name) values (?, ?)`, id, name); err != nil {
|
|
return err
|
|
}
|
|
}
|
|
return nil
|
|
}
|
|
|
|
func (s *Store) Building(ctx context.Context, id string) (Building, bool) {
|
|
var building Building
|
|
err := s.db.QueryRowContext(ctx, `select name, address from buildings where id = ?`, id).Scan(&building.Name, &building.Address)
|
|
return building, err == nil
|
|
}
|
|
|
|
func (s *Store) SaveBuilding(ctx context.Context, id string, building Building) error {
|
|
_, err := s.db.ExecContext(ctx, `insert or replace into buildings (id, name, address) values (?, ?, ?)`, id, building.Name, building.Address)
|
|
return err
|
|
}
|