Files

161 lines
7.2 KiB
Python

"""CRUD stores over the SQLite JSON-blob tables."""
from __future__ import annotations
import json
import sqlite3
import threading
from typing import Optional
from app.models.assets import Asset
from app.models.map_pack import MapPack
from app.models.route import TrekkerRoute, utcnow_iso
from app.models.trail_guide import TrailGuide
class Store:
"""All-table store. Serializes writes with a lock (SQLite, multi-thread app)."""
def __init__(self, conn: sqlite3.Connection):
self.conn = conn
self.lock = threading.Lock()
# -- routes ------------------------------------------------------------
def save_route(self, route: TrekkerRoute) -> TrekkerRoute:
route.updated_at = utcnow_iso()
with self.lock, self.conn:
self.conn.execute(
"""INSERT INTO routes (id, name, source, data, trail_guide_id, created_at, updated_at)
VALUES (?,?,?,?,?,?,?)
ON CONFLICT(id) DO UPDATE SET
name=excluded.name, source=excluded.source, data=excluded.data,
trail_guide_id=excluded.trail_guide_id, updated_at=excluded.updated_at""",
(route.id, route.name, route.source, route.model_dump_json(),
route.trail_guide_id, route.created_at, route.updated_at),
)
return route
def get_route(self, route_id: str) -> Optional[TrekkerRoute]:
row = self.conn.execute("SELECT data FROM routes WHERE id=?", (route_id,)).fetchone()
return TrekkerRoute.model_validate_json(row["data"]) if row else None
def list_routes(self) -> list[TrekkerRoute]:
rows = self.conn.execute("SELECT data FROM routes ORDER BY created_at DESC").fetchall()
return [TrekkerRoute.model_validate_json(r["data"]) for r in rows]
def delete_route(self, route_id: str) -> bool:
with self.lock, self.conn:
cur = self.conn.execute("DELETE FROM routes WHERE id=?", (route_id,))
return cur.rowcount > 0
def link_trail_guide(self, route_id: str, guide_id: Optional[str]) -> Optional[TrekkerRoute]:
route = self.get_route(route_id)
if route is None:
return None
if guide_id is not None and self.get_trail_guide(guide_id) is None:
raise KeyError(f"trail guide {guide_id} not found")
route.trail_guide_id = guide_id
return self.save_route(route)
# -- trail guides --------------------------------------------------------
def save_trail_guide(self, guide: TrailGuide) -> TrailGuide:
guide.updated_at = utcnow_iso()
with self.lock, self.conn:
self.conn.execute(
"""INSERT INTO trail_guides (id, name, source_url, pdf_path, data, created_at, updated_at)
VALUES (?,?,?,?,?,?,?)
ON CONFLICT(id) DO UPDATE SET
name=excluded.name, source_url=excluded.source_url, pdf_path=excluded.pdf_path,
data=excluded.data, updated_at=excluded.updated_at""",
(guide.id, guide.name, guide.source_url, guide.pdf_path,
guide.model_dump_json(), guide.created_at, guide.updated_at),
)
return guide
def get_trail_guide(self, guide_id: str) -> Optional[TrailGuide]:
row = self.conn.execute("SELECT data FROM trail_guides WHERE id=?", (guide_id,)).fetchone()
return TrailGuide.model_validate_json(row["data"]) if row else None
def list_trail_guides(self) -> list[TrailGuide]:
rows = self.conn.execute("SELECT data FROM trail_guides ORDER BY created_at DESC").fetchall()
return [TrailGuide.model_validate_json(r["data"]) for r in rows]
def delete_trail_guide(self, guide_id: str) -> bool:
with self.lock, self.conn:
cur = self.conn.execute("DELETE FROM trail_guides WHERE id=?", (guide_id,))
return cur.rowcount > 0
# -- assets ---------------------------------------------------------------
def save_asset(self, asset: Asset) -> Asset:
with self.lock, self.conn:
self.conn.execute(
"""INSERT INTO assets (id, route_id, generator, data, created_at)
VALUES (?,?,?,?,?)
ON CONFLICT(id) DO UPDATE SET data=excluded.data""",
(asset.id, asset.route_id, asset.generator,
asset.model_dump_json(), asset.created_at),
)
return asset
def list_assets(self, route_id: str) -> list[Asset]:
rows = self.conn.execute(
"SELECT data FROM assets WHERE route_id=? ORDER BY created_at DESC", (route_id,)
).fetchall()
return [Asset.model_validate_json(r["data"]) for r in rows]
# -- weather cache ----------------------------------------------------------
def save_weather(self, route_id: str, data: dict) -> None:
with self.lock, self.conn:
self.conn.execute(
"""INSERT INTO weather_cache (route_id, data, fetched_at) VALUES (?,?,?)
ON CONFLICT(route_id) DO UPDATE SET data=excluded.data, fetched_at=excluded.fetched_at""",
(route_id, json.dumps(data), utcnow_iso()),
)
def get_weather(self, route_id: str) -> Optional[dict]:
row = self.conn.execute(
"SELECT data, fetched_at FROM weather_cache WHERE route_id=?", (route_id,)
).fetchone()
if not row:
return None
data = json.loads(row["data"])
data["fetched_at"] = row["fetched_at"]
return data
# -- offline map packs -----------------------------------------------------
def save_map_pack(self, pack: MapPack) -> MapPack:
pack.updated_at = utcnow_iso()
with self.lock, self.conn:
self.conn.execute(
"""INSERT INTO offline_map_packs
(id, name, bbox, min_zoom, max_zoom, layers, status, path,
size_bytes, tile_count, data, created_at, updated_at)
VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)
ON CONFLICT(id) DO UPDATE SET
name=excluded.name, status=excluded.status, path=excluded.path,
size_bytes=excluded.size_bytes, tile_count=excluded.tile_count,
data=excluded.data, updated_at=excluded.updated_at""",
(pack.id, pack.name, json.dumps(pack.bbox), pack.min_zoom, pack.max_zoom,
json.dumps(pack.layers), pack.status, pack.path, pack.size_bytes,
pack.tile_count, pack.model_dump_json(), pack.created_at, pack.updated_at),
)
return pack
def get_map_pack(self, pack_id: str) -> Optional[MapPack]:
row = self.conn.execute("SELECT data FROM offline_map_packs WHERE id=?", (pack_id,)).fetchone()
return MapPack.model_validate_json(row["data"]) if row else None
def list_map_packs(self) -> list[MapPack]:
rows = self.conn.execute("SELECT data FROM offline_map_packs ORDER BY created_at DESC").fetchall()
return [MapPack.model_validate_json(r["data"]) for r in rows]
def delete_map_pack(self, pack_id: str) -> bool:
with self.lock, self.conn:
cur = self.conn.execute("DELETE FROM offline_map_packs WHERE id=?", (pack_id,))
return cur.rowcount > 0