161 lines
7.2 KiB
Python
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
|