generated from CubeCraft-Creations/Tracehound
58 lines
1.9 KiB
SQL
58 lines
1.9 KiB
SQL
-- RemoteRig Database Schema (SQLite)
|
|
-- Migration: 001_create_tables
|
|
|
|
-- Cameras table: registry of all GoPro cameras
|
|
CREATE TABLE IF NOT EXISTS cameras (
|
|
camera_id TEXT PRIMARY KEY,
|
|
friendly_name TEXT NOT NULL,
|
|
mac_address TEXT UNIQUE,
|
|
created_at DATETIME NOT NULL DEFAULT (datetime('now')),
|
|
updated_at DATETIME NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_cameras_mac ON cameras(mac_address);
|
|
|
|
-- Status logs: every poll from an ESP8266 node
|
|
CREATE TABLE IF NOT EXISTS status_logs (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
camera_id TEXT NOT NULL REFERENCES cameras(camera_id),
|
|
recorded_at DATETIME NOT NULL DEFAULT (datetime('now')),
|
|
battery_pct INTEGER,
|
|
video_remaining_sec INTEGER,
|
|
recording_state INTEGER NOT NULL DEFAULT 0,
|
|
mode TEXT,
|
|
resolution TEXT,
|
|
fps INTEGER,
|
|
online INTEGER NOT NULL DEFAULT 1,
|
|
raw_battery_pct REAL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_status_logs_camera_time
|
|
ON status_logs(camera_id, recorded_at DESC);
|
|
|
|
-- Recording events: explicit start/stop events
|
|
CREATE TABLE IF NOT EXISTS recording_events (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
camera_id TEXT NOT NULL REFERENCES cameras(camera_id),
|
|
started_at DATETIME NOT NULL,
|
|
stopped_at DATETIME,
|
|
reason TEXT,
|
|
duration INTEGER
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_recording_events_camera_time
|
|
ON recording_events(camera_id, started_at DESC);
|
|
|
|
-- Settings: system-wide and per-camera config
|
|
CREATE TABLE IF NOT EXISTS settings (
|
|
key TEXT PRIMARY KEY,
|
|
value TEXT NOT NULL,
|
|
updated_at DATETIME NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
|
|
-- Seed: default poll interval and thresholds
|
|
INSERT INTO settings (key, value) VALUES
|
|
('poll_interval_sec', '30'),
|
|
('low_battery_threshold', '15'),
|
|
('low_storage_alert_sec', '300');
|