Spaces:
Running
Running
File size: 9,966 Bytes
557ee65 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 | import sqlite3
import os
import logging
logger = logging.getLogger(__name__)
class Database:
"""Base class for handling SQLite database connections."""
def __init__(self, db_path: str):
self.db_path = db_path
self._ensure_dir()
self._memory_conn = None
if self.db_path == ":memory:":
self._memory_conn = sqlite3.connect(self.db_path)
self._memory_conn.row_factory = sqlite3.Row
def _ensure_dir(self):
if self.db_path == ":memory:":
return
db_dir = os.path.dirname(self.db_path)
if db_dir and not os.path.exists(db_dir):
os.makedirs(db_dir, exist_ok=True)
def get_connection(self) -> sqlite3.Connection:
if self._memory_conn:
return self._memory_conn
conn = sqlite3.connect(self.db_path)
conn.row_factory = sqlite3.Row
return conn
def initialize_schema(self):
"""Initializes all database tables."""
with self.get_connection() as conn:
# Runner Profile
conn.execute(
"""
CREATE TABLE IF NOT EXISTS runner_profiles (
runner_id TEXT PRIMARY KEY,
runner_display_name TEXT,
age INTEGER,
sex TEXT,
experience_level TEXT,
baseline_weekly_km REAL,
gender TEXT,
injury_history_notes TEXT,
created_at TEXT,
updated_at TEXT
)
"""
)
# Individual Runs
conn.execute(
"""
CREATE TABLE IF NOT EXISTS runs (
id TEXT PRIMARY KEY,
runner_id TEXT NOT NULL,
start_time TEXT NOT NULL,
total_distance_m REAL,
total_time_sec REAL,
avg_hr REAL,
max_hr REAL,
avg_pace_sec_per_km REAL,
source_file TEXT,
json_data TEXT
)
"""
)
# Weekly Snapshots
conn.execute(
"""
CREATE TABLE IF NOT EXISTS weekly_snapshots (
id TEXT PRIMARY KEY,
runner_id TEXT NOT NULL,
week_start_date TEXT NOT NULL UNIQUE,
total_distance_km REAL,
avg_pace_sec_per_km REAL,
avg_hr REAL,
max_hr REAL,
total_time_sec REAL,
run_count INTEGER,
consistency_score REAL,
performance_brief TEXT,
performance_focus TEXT,
brief_generated_at TEXT,
brief_source_hash TEXT,
structure_status TEXT,
created_at TEXT
)
"""
)
# Trend Snapshots
conn.execute(
"""
CREATE TABLE IF NOT EXISTS trend_snapshots (
id TEXT PRIMARY KEY,
runner_id TEXT NOT NULL,
week_start_date TEXT NOT NULL,
comparison_type TEXT NOT NULL,
reference_week_start_date TEXT,
distance_delta_km REAL,
avg_pace_delta_s_per_km REAL,
avg_hr_delta REAL,
runs_count_delta INTEGER,
consistency_delta REAL,
engine_version TEXT NOT NULL,
computed_at TEXT NOT NULL,
UNIQUE(runner_id, week_start_date, comparison_type)
)
"""
)
# Goals
conn.execute(
"""
CREATE TABLE IF NOT EXISTS goals (
id TEXT PRIMARY KEY,
runner_id TEXT NOT NULL,
type TEXT NOT NULL,
target_value REAL NOT NULL,
unit TEXT NOT NULL,
target_date TEXT,
status TEXT NOT NULL,
created_at TEXT NOT NULL,
achieved_at TEXT
)
"""
)
conn.commit()
# Analyses
conn.execute(
"""
CREATE TABLE IF NOT EXISTS analyses (
id TEXT PRIMARY KEY,
created_at TEXT NOT NULL,
source_files TEXT,
formats TEXT,
run_summary TEXT,
run_timeseries TEXT,
insights_json TEXT,
plan_json TEXT,
route_json TEXT
)
"""
)
conn.commit()
# Planned Sessions
conn.execute(
"""
CREATE TABLE IF NOT EXISTS planned_sessions (
id TEXT PRIMARY KEY,
runner_id TEXT NOT NULL,
week_start_date TEXT NOT NULL,
session_type TEXT NOT NULL,
planned_date TEXT NOT NULL,
target_distance_km REAL NOT NULL,
completed_run_id TEXT,
created_at TEXT NOT NULL
)
"""
)
conn.commit()
# Runs
conn.execute(
"""
CREATE TABLE IF NOT EXISTS runs (
id TEXT PRIMARY KEY,
runner_id TEXT NOT NULL,
start_time TEXT NOT NULL,
total_distance_m REAL,
total_time_sec REAL,
avg_hr REAL,
max_hr REAL,
avg_pace_sec_per_km REAL,
source_file TEXT,
json_data TEXT
)
"""
)
conn.commit()
# Run migrations for existing tables (DB Tables updated)
self._migrate_schema()
def _migrate_schema(self):
"""Adds missing columns to existing tables."""
with self.get_connection() as conn:
# Runner Profiles migrations
cursor = conn.execute("PRAGMA table_info(runner_profiles)")
columns = [row["name"] for row in cursor.fetchall()]
if "gender" not in columns:
conn.execute("ALTER TABLE runner_profiles ADD COLUMN gender TEXT")
if "baseline_weekly_km" not in columns:
conn.execute("ALTER TABLE runner_profiles ADD COLUMN baseline_weekly_km REAL")
# Weekly Snapshots migrations
cursor = conn.execute("PRAGMA table_info(weekly_snapshots)")
columns = [row["name"] for row in cursor.fetchall()]
if "runner_id" not in columns:
# SQLite doesn't support ADD COLUMN with NOT NULL without default or being empty.
# Since this is a migration, we'll allow it as nullable first or provide a default.
conn.execute("ALTER TABLE weekly_snapshots ADD COLUMN runner_id TEXT")
if "performance_brief" not in columns:
conn.execute("ALTER TABLE weekly_snapshots ADD COLUMN performance_brief TEXT")
if "performance_focus" not in columns:
conn.execute("ALTER TABLE weekly_snapshots ADD COLUMN performance_focus TEXT")
if "brief_generated_at" not in columns:
conn.execute("ALTER TABLE weekly_snapshots ADD COLUMN brief_generated_at TEXT")
if "brief_source_hash" not in columns:
conn.execute("ALTER TABLE weekly_snapshots ADD COLUMN brief_source_hash TEXT")
if "structure_status" not in columns:
conn.execute("ALTER TABLE weekly_snapshots ADD COLUMN structure_status TEXT")
# Analyses Table migrations (Legacy cleanup)
cursor = conn.execute("PRAGMA table_info(analyses)")
columns = [row["name"] for row in cursor.fetchall()]
renames = {
"source_files_json": "source_files",
"formats_json": "formats",
"run_summary_json": "run_summary",
"run_timeseries_json": "run_timeseries",
}
for old_col, new_col in renames.items():
if old_col in columns and new_col not in columns:
logger.info(f"Migrating column {old_col} to {new_col} in analyses table")
conn.execute(f"ALTER TABLE analyses RENAME COLUMN {old_col} TO {new_col}")
# Goals Table
conn.execute(
"""
CREATE TABLE IF NOT EXISTS goals (
id TEXT PRIMARY KEY,
runner_id TEXT NOT NULL,
type TEXT NOT NULL,
target_value REAL NOT NULL,
unit TEXT NOT NULL,
target_date TEXT,
status TEXT NOT NULL,
created_at TEXT NOT NULL,
achieved_at TEXT
)
"""
)
# Planned Sessions Table
conn.execute(
"""
CREATE TABLE IF NOT EXISTS planned_sessions (
id TEXT PRIMARY KEY,
runner_id TEXT NOT NULL,
week_start_date TEXT NOT NULL,
session_type TEXT NOT NULL,
planned_date TEXT NOT NULL,
target_distance_km REAL NOT NULL,
completed_run_id TEXT,
created_at TEXT NOT NULL
)
"""
)
conn.commit()
|