65 lines
1.7 KiB
Python
65 lines
1.7 KiB
Python
"""SQLite schema and connection management. JSON-blob storage per PLAN.md 1d."""
|
|
|
|
from __future__ import annotations
|
|
|
|
import sqlite3
|
|
from pathlib import Path
|
|
|
|
SCHEMA = """
|
|
CREATE TABLE IF NOT EXISTS routes (
|
|
id TEXT PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
source TEXT NOT NULL,
|
|
data JSON NOT NULL,
|
|
trail_guide_id TEXT REFERENCES trail_guides(id) ON DELETE SET NULL,
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
CREATE TABLE IF NOT EXISTS trail_guides (
|
|
id TEXT PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
source_url TEXT,
|
|
pdf_path TEXT,
|
|
data JSON NOT NULL,
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
CREATE TABLE IF NOT EXISTS assets (
|
|
id TEXT PRIMARY KEY,
|
|
route_id TEXT NOT NULL REFERENCES routes(id) ON DELETE CASCADE,
|
|
generator TEXT NOT NULL,
|
|
data JSON NOT NULL,
|
|
created_at TEXT NOT NULL
|
|
);
|
|
CREATE TABLE IF NOT EXISTS weather_cache (
|
|
route_id TEXT PRIMARY KEY REFERENCES routes(id) ON DELETE CASCADE,
|
|
data JSON NOT NULL,
|
|
fetched_at TEXT NOT NULL
|
|
);
|
|
CREATE TABLE IF NOT EXISTS offline_map_packs (
|
|
id TEXT PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
bbox JSON NOT NULL,
|
|
min_zoom INTEGER NOT NULL,
|
|
max_zoom INTEGER NOT NULL,
|
|
layers JSON NOT NULL,
|
|
status TEXT NOT NULL,
|
|
path TEXT,
|
|
size_bytes INTEGER DEFAULT 0,
|
|
tile_count INTEGER DEFAULT 0,
|
|
data JSON NOT NULL,
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
"""
|
|
|
|
|
|
def connect(db_path: str) -> sqlite3.Connection:
|
|
Path(db_path).parent.mkdir(parents=True, exist_ok=True)
|
|
conn = sqlite3.connect(db_path, check_same_thread=False)
|
|
conn.row_factory = sqlite3.Row
|
|
conn.execute("PRAGMA journal_mode=WAL")
|
|
conn.execute("PRAGMA foreign_keys=ON")
|
|
conn.executescript(SCHEMA)
|
|
return conn
|