94 lines
2.3 KiB
Go
94 lines
2.3 KiB
Go
 
|
// Package store persists confirmed fall events to a local SQLite database.
|
||
|
|
// The database is local, untracked evidence: it records the camera host, port
|
||
|
|
// and channel but never the password.
|
||
|
|
package store
|
||
|
|
|
||
|
|
import (
|
||
|
|
"database/sql"
|
||
|
|
"fmt"
|
||
|
|
|
||
|
|
_ "github.com/mattn/go-sqlite3"
|
||
|
|
)
|
||
|
|
|
||
|
|
// Record is one confirmed fall event row.
|
||
|
|
type Record struct {
|
||
|
|
EventID string
|
||
|
|
TrackID string
|
||
|
|
SourceID string
|
||
|
|
CameraHost string
|
||
|
|
CameraPort int
|
||
|
|
CameraChannel string
|
||
|
|
ConfigVersion string
|
||
|
|
State string
|
||
|
|
LatencySeconds float64
|
||
|
|
ScreenshotPath string
|
||
|
|
ConfirmedAtUTC string
|
||
|
|
}
|
||
|
|
|
||
|
|
type Store struct {
|
||
|
|
db *sql.DB
|
||
|
|
}
|
||
|
|
|
||
|
|
const schema = `
|
||
|
|
CREATE TABLE IF NOT EXISTS fall_events (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
event_id TEXT NOT NULL,
|
||
|
|
track_id TEXT,
|
||
|
|
source_id TEXT,
|
||
|
|
camera_host TEXT,
|
||
|
|
camera_port INTEGER,
|
||
|
|
camera_channel TEXT,
|
||
|
|
config_version TEXT,
|
||
|
|
state TEXT,
|
||
|
|
latency_seconds REAL,
|
||
|
|
screenshot_path TEXT,
|
||
|
|
confirmed_at_utc TEXT
|
||
|
|
);`
|
||
|
|
|
||
|
|
// Open opens (creating if needed) the SQLite database and ensures the schema.
|
||
|
|
func Open(path string) (*Store, error) {
|
||
|
|
db, err := sql.Open("sqlite3", path)
|
||
|
|
if err != nil {
|
||
|
|
return nil, fmt.Errorf("open fall database: %w", err)
|
||
|
|
}
|
||
|
|
if _, err := db.Exec(schema); err != nil {
|
||
|
|
_ = db.Close()
|
||
|
|
return nil, fmt.Errorf("create fall table: %w", err)
|
||
|
|
}
|
||
|
|
return &Store{db: db}, nil
|
||
|
|
}
|
||
|
|
|
||
|
|
// Save inserts one confirmed fall event.
|
||
|
|
func (store *Store) Save(record Record) error {
|
||
|
|
_, err := store.db.Exec(
|
||
|
|
`INSERT INTO fall_events
|
||
|
|
(event_id, track_id, source_id, camera_host, camera_port, camera_channel,
|
||
|
|
config_version, state, latency_seconds, screenshot_path, confirmed_at_utc)
|
||
|
|
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
|
||
|
|
record.EventID, record.TrackID, record.SourceID,
|
||
|
|
record.CameraHost, record.CameraPort, record.CameraChannel,
|
||
|
|
record.ConfigVersion, record.State, record.LatencySeconds,
|
||
|
|
record.ScreenshotPath, record.ConfirmedAtUTC,
|
||
|
|
)
|
||
|
|
if err != nil {
|
||
|
|
return fmt.Errorf("insert fall event: %w", err)
|
||
|
|
}
|
||
|
|
return nil
|
||
|
|
}
|
||
|
|
|
||
|
|
// Count returns the number of stored events (used by tests).
|
||
|
|
func (store *Store) Count() (int, error) {
|
||
|
|
var count int
|
||
|
|
if err := store.db.QueryRow(`SELECT COUNT(*) FROM fall_events`).Scan(&count); err != nil {
|
||
|
|
return 0, err
|
||
|
|
}
|
||
|
|
return count, nil
|
||
|
|
}
|
||
|
|
|
||
|
|
func (store *Store) Close() error {
|
||
|
|
if store == nil || store.db == nil {
|
||
|
|
return nil
|
||
|
|
}
|
||
|
|
return store.db.Close()
|
||
|
|
}
|