59 lines
2.9 KiB
Python
59 lines
2.9 KiB
Python
import sqlite3
|
|
from sfm.database import SeismoDb
|
|
def _cols(db):
|
|
with sqlite3.connect(db.db_path) as c:
|
|
return {r[1] for r in c.execute("PRAGMA table_info(events)")}
|
|
def test_fresh_db_has_reviewed_real(tmp_path):
|
|
assert "reviewed_real" in _cols(SeismoDb(tmp_path/"s.db"))
|
|
def test_existing_db_migrates_reviewed_real(tmp_path):
|
|
p = tmp_path/"s.db"; db = SeismoDb(p)
|
|
with sqlite3.connect(p) as c:
|
|
c.execute("ALTER TABLE events DROP COLUMN reviewed_real")
|
|
assert "reviewed_real" not in _cols(db) # dropped (read via existing handle/connection)
|
|
SeismoDb(p) # re-open migrates
|
|
assert "reviewed_real" in _cols(SeismoDb(p))
|
|
|
|
def test_legacy_pre_migration1_db_rebuild_does_not_crash(tmp_path):
|
|
# Genuinely legacy (pre-Migration-1) events table: no UNIQUE(serial, timestamp)
|
|
# (still on the old UNIQUE(serial, waveform_key)) and columns only through
|
|
# false_trigger/created_at — i.e. none of the columns added later by the
|
|
# Migration-1b ADD COLUMN loop (blastware_filename, device_family,
|
|
# tran_zc_freq, shape_*, reviewed_real, ...). Opening this DB triggers the
|
|
# Migration-1 rebuild (`events_old` -> `events` via positional
|
|
# `INSERT OR IGNORE INTO events SELECT * FROM events_old`), which requires
|
|
# the rebuild's CREATE TABLE to have exactly the legacy column count.
|
|
p = tmp_path / "legacy.db"
|
|
with sqlite3.connect(p) as c:
|
|
c.execute("""
|
|
CREATE TABLE events (
|
|
id TEXT PRIMARY KEY,
|
|
serial TEXT NOT NULL,
|
|
waveform_key TEXT NOT NULL,
|
|
session_id TEXT,
|
|
timestamp TEXT,
|
|
tran_ppv REAL,
|
|
vert_ppv REAL,
|
|
long_ppv REAL,
|
|
peak_vector_sum REAL,
|
|
mic_ppv REAL,
|
|
project TEXT,
|
|
client TEXT,
|
|
operator TEXT,
|
|
sensor_location TEXT,
|
|
sample_rate INTEGER,
|
|
record_type TEXT,
|
|
false_trigger INTEGER NOT NULL DEFAULT 0,
|
|
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
|
|
UNIQUE(serial, waveform_key)
|
|
)
|
|
""")
|
|
c.execute(
|
|
"INSERT INTO events (id, serial, waveform_key, timestamp) VALUES (?, ?, ?, ?)",
|
|
("evt-1", "BE11529", "01110000", "2026-01-01T00:00:00"),
|
|
)
|
|
# Pre-fix this raised: OperationalError: table events has 19 columns but
|
|
# 18 values were supplied (reviewed_real had leaked into the rebuild's
|
|
# CREATE TABLE, but events_old — and the positional SELECT * — only had 18).
|
|
db = SeismoDb(p)
|
|
assert "reviewed_real" in _cols(db)
|