Repository navigation
Expand file tree
/
Copy pathhistory.py
More file actions
390 lines (346 loc) · 14.3 KB
/
Copy pathhistory.py
File metadata and controls
390 lines (346 loc) · 14.3 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
#!/usr/bin/env python3
"""
dump3411 / history.py
Optional persistent detection log backed by SQLite. Hooks the same Tracker
``on_change`` callback MQTT uses; each fire stores a row of the imperial
snapshot dict (lat / lon / alt / gs / track / operator location / self_id /
…) so a flight path can be reconstructed after the in-memory tracker has
TTL-evicted the drone.
**Opt-in.** Disabled unless ``--history-db`` (or ``HISTORY_DB``) is
configured. Pi SD cards aren't infinite, so we don't write by default.
Design:
- WAL-mode SQLite. One write connection guarded by a Lock; HTTP readers
open per-request connections (WAL lets readers not block writes).
- Per-drone debounce (default 1 s) so a 5 Hz broadcaster is one row/sec.
Latest-wins: the *next* write after the window closes uses whatever
row state the tracker currently has, which mirrors the snapshot the
JSON feed and MQTT consumers also see.
- Cleanup thread (default every 60 s) prunes rows older than the
retention window and then trims to the size cap. Whichever cuts first.
- No expire hook. When the tracker TTL-evicts a drone, history rows
persist — that's the entire point.
Schema (v1):
detections(
id INTEGER PRIMARY KEY AUTOINCREMENT,
ts REAL NOT NULL, -- epoch seconds, wall clock
uas_id TEXT NOT NULL,
id_type TEXT,
ua_type TEXT,
lat REAL,
lon REAL,
alt_geom_ft REAL,
agl_ft REAL,
gs REAL, -- knots
track REAL, -- degrees
geom_rate REAL, -- ft/min
rssi REAL,
rid_source TEXT,
self_id TEXT,
op_lat REAL,
op_lon REAL,
operator_id TEXT
)
idx_uas_ts ON (uas_id, ts)
idx_ts ON (ts)
PRAGMA user_version = 1.
"""
import logging
import os
import sqlite3
import threading
import time
from typing import Optional
log = logging.getLogger("dump3411.history")
SCHEMA_VERSION = 1
_SCHEMA_SQL = """
CREATE TABLE IF NOT EXISTS detections (
id INTEGER PRIMARY KEY AUTOINCREMENT,
ts REAL NOT NULL,
uas_id TEXT NOT NULL,
id_type TEXT,
ua_type TEXT,
lat REAL,
lon REAL,
alt_geom_ft REAL,
agl_ft REAL,
gs REAL,
track REAL,
geom_rate REAL,
rssi REAL,
rid_source TEXT,
self_id TEXT,
op_lat REAL,
op_lon REAL,
operator_id TEXT
);
CREATE INDEX IF NOT EXISTS idx_uas_ts ON detections(uas_id, ts);
CREATE INDEX IF NOT EXISTS idx_ts ON detections(ts);
"""
_INSERT_SQL = """
INSERT INTO detections
(ts, uas_id, id_type, ua_type, lat, lon, alt_geom_ft, agl_ft, gs, track,
geom_rate, rssi, rid_source, self_id, op_lat, op_lon, operator_id)
VALUES
(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
"""
class HistoryWriter:
"""SQLite-backed persistent detection log.
Construct with the destination path; call :meth:`start` to launch the
cleanup thread; pass :meth:`on_drone_change` into ``Tracker.set_callbacks``
or chain it alongside other consumers in the orchestrator.
"""
CLEANUP_INTERVAL_S = 60.0
def __init__(self, db_path: str,
max_mb: float = 100.0,
retention_days: float = 30.0,
debounce_s: float = 1.0,
recent_days: float = 7.0):
self._path = db_path
self._max_bytes = int(max_mb * 1024 * 1024)
self._retention_s = float(retention_days) * 86400.0
self._debounce_s = float(debounce_s)
# Default window for /history/recent.json + the dashboard's Recent table.
# Independent of retention — retention bounds the DB, this bounds the view.
self.recent_seconds = float(recent_days) * 86400.0
# One write connection guarded by a lock; readers use per-request conns.
os.makedirs(os.path.dirname(os.path.abspath(db_path)) or ".", exist_ok=True)
self._conn = sqlite3.connect(db_path, check_same_thread=False,
isolation_level=None)
self._conn.execute("PRAGMA journal_mode=WAL")
self._conn.execute("PRAGMA synchronous=NORMAL") # WAL-safe, faster on SD
self._conn.executescript(_SCHEMA_SQL)
self._conn.execute(f"PRAGMA user_version = {SCHEMA_VERSION}")
self._write_lock = threading.Lock()
self._last_write: dict[str, float] = {} # uas_id -> monotonic
# stats() feeds /status, which the dashboard polls every 1.5 s, but its
# COUNT queries scan the table: about 1 s per call on a Pi Zero at 240k
# rows. A minute-old row count is fine for a status tile.
self._stats_lock = threading.Lock()
self._stats_cache: Optional[dict] = None
self._stats_at = float("-inf") # monotonic
self._stop = threading.Event()
self._cleanup_thread: Optional[threading.Thread] = None
log.info(
"history: %s (max=%.0f MB, retention=%.1f d, debounce=%.1f s, recent=%.1f d)",
db_path, max_mb, retention_days, debounce_s, recent_days,
)
# -- lifecycle -----------------------------------------------------------
def start(self) -> None:
self._cleanup_thread = threading.Thread(
target=self._cleanup_loop, daemon=True, name="history-cleanup",
)
self._cleanup_thread.start()
def stop(self) -> None:
self._stop.set()
try:
with self._write_lock:
self._conn.close()
except Exception:
pass
# -- Tracker callback ----------------------------------------------------
def on_drone_change(self, uas_id: str, row: dict) -> None:
"""Called from radio threads via Tracker.on_change. Must be fast.
Latest-wins per drone with a configurable debounce so SD-card writes
stay bounded (default 1 s = max one row per drone per second).
"""
now_mono = time.monotonic()
last = self._last_write.get(uas_id, 0.0)
if (now_mono - last) < self._debounce_s:
return
self._last_write[uas_id] = now_mono
op = row.get("operator") or {}
params = (
time.time(),
uas_id,
row.get("id_type"),
row.get("ua_type"),
row.get("lat"),
row.get("lon"),
row.get("alt_geom_ft"),
row.get("agl_ft"),
row.get("gs"),
row.get("track"),
row.get("geom_rate"),
row.get("rssi"),
row.get("rid_source"),
row.get("self_id"),
op.get("lat"),
op.get("lon"),
op.get("id"),
)
try:
with self._write_lock:
self._conn.execute(_INSERT_SQL, params)
except Exception:
log.exception("history insert failed for %s", uas_id)
# -- Cleanup -------------------------------------------------------------
def _cleanup_loop(self) -> None:
while not self._stop.wait(self.CLEANUP_INTERVAL_S):
try:
self._prune_age()
self._prune_size()
except Exception:
log.exception("history cleanup failed")
def _prune_age(self) -> None:
cutoff = time.time() - self._retention_s
with self._write_lock:
cur = self._conn.execute(
"DELETE FROM detections WHERE ts < ?", (cutoff,))
removed = cur.rowcount
if removed:
log.info("history: pruned %d rows older than %.1f d",
removed, self._retention_s / 86400.0)
def _prune_size(self) -> None:
try:
size = os.path.getsize(self._path)
except OSError:
return
if size <= self._max_bytes:
return
# Over the size cap. Delete the oldest 10% of rows; VACUUM is heavy on
# SD card, so let SQLite's freelist reuse pages and we'll check again
# on the next tick.
with self._write_lock:
(total,) = self._conn.execute(
"SELECT COUNT(*) FROM detections").fetchone()
to_remove = max(1, total // 10)
self._conn.execute(
"DELETE FROM detections WHERE id IN "
"(SELECT id FROM detections ORDER BY ts ASC LIMIT ?)",
(to_remove,))
log.info("history: over size cap (%.1f MB > %.1f MB); pruned %d oldest rows",
size / 1048576, self._max_bytes / 1048576, to_remove)
# -- Read API (called from HTTP handler threads) ------------------------
def _read_conn(self) -> sqlite3.Connection:
"""Open a fresh read-only connection. WAL lets these run without
blocking the writer."""
c = sqlite3.connect(f"file:{self._path}?mode=ro", uri=True,
check_same_thread=False)
c.row_factory = sqlite3.Row
return c
def query_track(self, uas_id: str,
since: Optional[float] = None,
until: Optional[float] = None,
limit: int = 10000) -> list[dict]:
"""Return ordered position points for one drone within an optional
time window. Filters out rows with no lat/lon so the result is a
clean track suitable for plotting."""
q = ("SELECT ts, lat, lon, alt_geom_ft, agl_ft, gs, track, geom_rate, "
"rssi, rid_source FROM detections "
"WHERE uas_id = ? AND lat IS NOT NULL AND lon IS NOT NULL")
params: list = [uas_id]
if since is not None:
q += " AND ts >= ?"; params.append(since)
if until is not None:
q += " AND ts <= ?"; params.append(until)
q += " ORDER BY ts ASC LIMIT ?"
params.append(limit)
c = self._read_conn()
try:
rows = c.execute(q, params).fetchall()
return [dict(r) for r in rows]
finally:
c.close()
def query_operator(self, uas_id: str) -> Optional[dict]:
"""Return the most recent non-null operator position seen for this
drone, or None if we never saw one."""
c = self._read_conn()
try:
row = c.execute(
"SELECT ts, op_lat, op_lon, operator_id FROM detections "
"WHERE uas_id = ? AND op_lat IS NOT NULL AND op_lon IS NOT NULL "
"ORDER BY ts DESC LIMIT 1",
(uas_id,),
).fetchone()
if row is None:
return None
return {"ts": row["ts"], "lat": row["op_lat"], "lon": row["op_lon"],
"id": row["operator_id"]}
finally:
c.close()
def query_recent_drones(self, since: Optional[float] = None,
limit: int = 50) -> list[dict]:
"""Summary rows for drones seen within the time window, most recently
seen first. Used by ``/history/recent.json`` for the dashboard's
Recent detections section. ``since`` defaults to ``self.recent_seconds``
ago (7 days unless overridden via --history-recent-days).
Each row carries first_seen / last_seen / messages plus the latest
id_type / ua_type / self_id seen for the drone (since identity
fields are write-once on the wire but `self_id` can roll over the
course of a long flight).
"""
if since is None:
since = time.time() - self.recent_seconds
c = self._read_conn()
try:
rows = c.execute(
"""
SELECT
d.uas_id,
MIN(d.ts) AS first_seen,
MAX(d.ts) AS last_seen,
COUNT(*) AS messages,
(SELECT id_type FROM detections
WHERE uas_id = d.uas_id ORDER BY ts DESC LIMIT 1) AS id_type,
(SELECT ua_type FROM detections
WHERE uas_id = d.uas_id ORDER BY ts DESC LIMIT 1) AS ua_type,
(SELECT self_id FROM detections
WHERE uas_id = d.uas_id ORDER BY ts DESC LIMIT 1) AS self_id
FROM detections d
WHERE d.ts >= ?
GROUP BY d.uas_id
ORDER BY last_seen DESC
LIMIT ?
""",
(since, limit),
).fetchall()
return [dict(r) for r in rows]
finally:
c.close()
def query_drone_meta(self, uas_id: str) -> Optional[dict]:
"""Return latest id_type / ua_type / self_id seen for this drone."""
c = self._read_conn()
try:
row = c.execute(
"SELECT id_type, ua_type, self_id FROM detections "
"WHERE uas_id = ? ORDER BY ts DESC LIMIT 1",
(uas_id,),
).fetchone()
return dict(row) if row else None
finally:
c.close()
STATS_TTL_S = 60.0
def stats(self) -> dict:
"""For /status: rows, distinct drones, file size. Cached for STATS_TTL_S.
The lock also makes concurrent /status requests wait for one refresh
rather than each running the scans.
"""
with self._stats_lock:
now = time.monotonic()
if self._stats_cache is None or now - self._stats_at >= self.STATS_TTL_S:
self._stats_cache = self._compute_stats()
self._stats_at = now
return dict(self._stats_cache)
def _compute_stats(self) -> dict:
try:
size_bytes = os.path.getsize(self._path)
except OSError:
size_bytes = 0
c = self._read_conn()
try:
(rows,) = c.execute("SELECT COUNT(*) FROM detections").fetchone()
(drones,) = c.execute(
"SELECT COUNT(DISTINCT uas_id) FROM detections").fetchone()
# Separate queries: SQLite answers a lone MIN() or MAX() from
# idx_ts in O(log n), but MIN(ts), MAX(ts) together is a full scan.
(earliest,) = c.execute("SELECT MIN(ts) FROM detections").fetchone()
(latest,) = c.execute("SELECT MAX(ts) FROM detections").fetchone()
finally:
c.close()
return {
"rows": rows,
"drones": drones,
"size_bytes": size_bytes,
"earliest_ts": earliest,
"latest_ts": latest,
}