// pushdevice.go remembers WHICH browsers a person turned push on in,
// and nothing more (RFC 0011 §6). The subscription itself lives in
// notify as subscriber credentials — quarry stores no endpoint, no
// keys, and could not send a push if it wanted to. What is here is the
// one thing notify cannot answer for a preferences page: a list a
// person recognizes, so they can turn one off.
package oltp
import (
"crypto/sha256"
"encoding/hex"
"time"
)
const pushDeviceSchema = `
CREATE TABLE IF NOT EXISTS push_device (
account TEXT NOT NULL,
endpoint_hash TEXT NOT NULL, -- sha-256 of the push endpoint, never the endpoint
ua TEXT NOT NULL,
at TEXT NOT NULL,
-- The endpoint itself, held ONLY while a remote delete is owed and
-- cleared the moment one is confirmed. See MarkPushDevicePendingDelete.
pending_endpoint TEXT NOT NULL DEFAULT '',
PRIMARY KEY (account, endpoint_hash)
);`
// pushDeviceMigrations brings an existing forge's table up: an
// ADD COLUMN, not a rebuild, because the rows are already right.
var pushDeviceMigrations = []string{
`ALTER TABLE push_device ADD COLUMN pending_endpoint TEXT NOT NULL DEFAULT ''`,
}
// PushDevice is one browser a person enabled push in.
type PushDevice struct {
EndpointHash string
UA string
At string
// PendingDelete is a device somebody asked to remove that notify
// has not confirmed dropping. The row is still here on purpose.
PendingDelete bool
}
// PushEndpointHash is the identity a device row is keyed by. A push
// endpoint is a bearer capability — anyone holding it can make
// somebody's phone buzz — so quarry keeps only its digest, which is
// enough to match, delete and de-duplicate and useless to a reader of
// the database.
func PushEndpointHash(endpoint string) string {
sum := sha256.Sum256([]byte(endpoint))
return hex.EncodeToString(sum[:])
}
// AddPushDevice records (or refreshes) one device for an account.
// Idempotent on the endpoint: a page that re-subscribes on every load
// must not grow the list.
func (d *DB) AddPushDevice(account, endpoint, ua string) error {
if account == "" || endpoint == "" {
return nil
}
_, err := d.w.Exec(`INSERT INTO push_device (account, endpoint_hash, ua, at)
VALUES (?, ?, ?, ?)
ON CONFLICT (account, endpoint_hash) DO UPDATE SET
ua = excluded.ua, at = excluded.at, pending_endpoint = ''`,
account, PushEndpointHash(endpoint), ua, time.Now().UTC().Format(time.RFC3339Nano))
return err
}
// DeletePushDevice forgets one device. Removing one that is not there
// is success — the end state asked for is what already holds.
func (d *DB) DeletePushDevice(account, endpoint string) error {
if account == "" || endpoint == "" {
return nil
}
_, err := d.w.Exec(`DELETE FROM push_device WHERE account = ? AND endpoint_hash = ?`,
account, PushEndpointHash(endpoint))
return err
}
// DeletePushDeviceByHash forgets one device by its endpoint hash, for
// when notify has already deleted the subscription and reports it by
// the same hash.
func (d *DB) DeletePushDeviceByHash(account, hash string) error {
if account == "" || hash == "" {
return nil
}
_, err := d.w.Exec(`DELETE FROM push_device WHERE account = ? AND endpoint_hash = ?`, account, hash)
return err
}
// PushDevices lists an account's devices, newest first.
func (d *DB) PushDevices(account string) ([]PushDevice, error) {
if account == "" {
return nil, nil
}
rows, err := d.db.Query(`SELECT endpoint_hash, ua, at, pending_endpoint <> '' FROM push_device
WHERE account = ? ORDER BY at DESC`, account)
if err != nil {
return nil, err
}
defer rows.Close()
var out []PushDevice
for rows.Next() {
var p PushDevice
if err := rows.Scan(&p.EndpointHash, &p.UA, &p.At, &p.PendingDelete); err != nil {
return nil, err
}
out = append(out, p)
}
return out, rows.Err()
}
// MarkPushDevicePendingDelete keeps a device row that notify has NOT
// confirmed dropping, and remembers the endpoint so the delete can be
// retried — a hash cannot be handed back to notify.
//
// This is the one place quarry holds a push endpoint, and it holds it
// for the shortest window the job allows: it is written only when a
// delete is owed, and cleared by DeletePushDevice (the row goes) or by
// AddPushDevice (the device is wanted again). The alternative is
// forgetting a device notify still pushes to, which leaves somebody
// with a buzzing phone and nothing in the interface to stop it.
func (d *DB) MarkPushDevicePendingDelete(account, endpoint string) error {
if account == "" || endpoint == "" {
return nil
}
_, err := d.w.Exec(`UPDATE push_device SET pending_endpoint = ?
WHERE account = ? AND endpoint_hash = ?`,
endpoint, account, PushEndpointHash(endpoint))
return err
}
// PendingPushDeletes lists the endpoints whose removal notify has not
// confirmed, newest first — what the next successful call retries.
func (d *DB) PendingPushDeletes(account string) ([]string, error) {
if account == "" {
return nil, nil
}
rows, err := d.db.Query(`SELECT pending_endpoint FROM push_device
WHERE account = ? AND pending_endpoint <> '' ORDER BY at DESC`, account)
if err != nil {
return nil, err
}
defer rows.Close()
var out []string
for rows.Next() {
var e string
if err := rows.Scan(&e); err != nil {
return nil, err
}
out = append(out, e)
}
return out, rows.Err()
}