XFEEDToolBox

"""Rebuild a market cache from recovered JSON, using intact station/snapshot tables."""
import datetime
import json
import pathlib
import sqlite3
import sys

sys.stdout.reconfigure(encoding="utf-8")
root = pathlib.Path(sys.argv[1]).resolve()
target = root / (sys.argv[2] if len(sys.argv)>2 else "validated-markets.db")
if target.exists():
    raise RuntimeError("Refusing to replace an existing result")
source = sqlite3.connect((root / "markets.db").as_uri() + "?mode=ro", uri=True)
recovered = sqlite3.connect((root / "recovered.db").as_uri() + "?mode=ro", uri=True)
db = sqlite3.connect(target.as_uri(),uri=True)
db.execute("PRAGMA synchronous=FULL")
schema = source.execute("SELECT sql FROM sqlite_schema WHERE type='table' AND name IN ('stations','snapshots','quotes') ORDER BY name").fetchall()
for (sql,) in schema:
    db.execute(sql)
db.execute("ATTACH DATABASE ? AS salvage", ((root / "recovered.db").as_uri() + "?mode=ro",))
db.executescript("""
INSERT OR IGNORE INTO main.stations
SELECT id,name,system,x,y,z,json FROM (
 SELECT id,name,system,x,y,z,json FROM salvage.stations
 UNION ALL SELECT id,c1,c2,c3,c4,c5,c6 FROM salvage.lost_and_found WHERE nfield=7
) WHERE typeof(id)='integer' AND id>0 AND typeof(name)='text' AND length(name)>0
 AND typeof(system)='text' AND length(system)>0
 AND CASE WHEN json_valid(json) THEN json_extract(json,'$.marketId')=id
  AND json_extract(json,'$.name')=name AND json_extract(json,'$.systemName')=system ELSE 0 END;
INSERT OR IGNORE INTO main.snapshots SELECT station,timestamp,source FROM salvage.snapshots
WHERE typeof(station)='integer' AND station>0 AND typeof(timestamp)='integer' AND typeof(source)='integer' AND source BETWEEN 0 AND 4;
CREATE TEMP VIEW candidates AS
SELECT station,commodity,name,buy,sell,supply,demand,timestamp,source,json FROM salvage.quotes
UNION ALL
SELECT c0,c1,c2,c3,c4,c5,c6,c7,c8,c9 FROM salvage.lost_and_found WHERE nfield=10;
CREATE TEMP TABLE valid_candidates AS
SELECT q.* FROM candidates q
JOIN main.stations st ON st.id=q.station
JOIN main.snapshots sn ON sn.station=q.station AND sn.timestamp=q.timestamp AND sn.source=q.source
WHERE typeof(q.station)='integer' AND typeof(q.commodity)='text' AND length(q.commodity)>0
 AND typeof(q.name)='text' AND typeof(q.buy)='integer' AND q.buy>=0
 AND typeof(q.sell)='integer' AND q.sell>=0 AND typeof(q.supply)='integer' AND q.supply>=0
 AND typeof(q.demand)='integer' AND q.demand>=0 AND typeof(q.json)='text'
 AND CASE WHEN json_valid(q.json) THEN
  json_extract(q.json,'$.marketId')=q.station AND json_extract(q.json,'$.commodity')=q.commodity
  AND json_extract(q.json,'$.displayName')=q.name AND json_extract(q.json,'$.buyPrice')=q.buy
  AND json_extract(q.json,'$.sellPrice')=q.sell AND json_extract(q.json,'$.supply')=q.supply
  AND json_extract(q.json,'$.demand')=q.demand
  AND abs(unixepoch(json_extract(q.json,'$.collectedAt'),'subsec')*1000-q.timestamp)<1
  AND json_extract(q.json,'$.source')=CASE q.source WHEN 0 THEN 'Demonstration' WHEN 1 THEN 'Capture'
    WHEN 2 THEN 'Spansh' WHEN 3 THEN 'Eddn' WHEN 4 THEN 'Journal' END
 ELSE 0 END;
INSERT OR IGNORE INTO main.quotes SELECT * FROM valid_candidates;
CREATE INDEX main.idx_quotes_commodity ON quotes(commodity);
CREATE INDEX main.idx_quotes_time ON quotes(timestamp DESC);
CREATE INDEX main.idx_stations_system ON stations(system);
""")
report = {"utc": datetime.datetime.now(datetime.timezone.utc).isoformat(), "integrity": db.execute("PRAGMA main.integrity_check").fetchall(),
          "stations": db.execute("SELECT COUNT(*) FROM stations").fetchone()[0],
          "snapshots": db.execute("SELECT COUNT(*) FROM snapshots").fetchone()[0],
          "quotes": db.execute("SELECT COUNT(*) FROM quotes").fetchone()[0],
          "stations_with_quotes": db.execute("SELECT COUNT(DISTINCT station) FROM quotes").fetchone()[0],
          "recovery_verified_against_recovered_snapshot_and_embedded_json": True,
          "lossless_recovery_claimed": False}
db.close()
(root / "validation-report.json").write_text(json.dumps(report, ensure_ascii=False, indent=2), encoding="utf-8")
print(json.dumps(report, ensure_ascii=False), flush=True)