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
|
import sqlite3
import os
DB_PATH = os.path.join(os.path.dirname(os.path.abspath(__file__)), "egometrics.db")
SCHEMA = """
CREATE TABLE IF NOT EXISTS workout_exercises (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL UNIQUE COLLATE NOCASE,
bw_relative BOOLEAN NOT NULL DEFAULT 0,
note TEXT
);
CREATE TABLE IF NOT EXISTS workout_sessions (
id INTEGER PRIMARY KEY AUTOINCREMENT,
date_time TEXT NOT NULL,
note TEXT
);
CREATE TABLE IF NOT EXISTS workout_session_exercises (
id INTEGER PRIMARY KEY AUTOINCREMENT,
session_id INTEGER NOT NULL,
exercise_id INTEGER NOT NULL,
position INTEGER NOT NULL,
sets INTEGER NOT NULL,
reps TEXT NOT NULL,
weight TEXT NOT NULL,
rest_time TEXT,
lsrpe INTEGER NOT NULL,
note TEXT,
FOREIGN KEY (session_id) REFERENCES workout_sessions(id) ON DELETE CASCADE,
FOREIGN KEY (exercise_id) REFERENCES workout_exercises(id) ON DELETE RESTRICT
);
CREATE TABLE IF NOT EXISTS workout_templates (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL UNIQUE COLLATE NOCASE
);
CREATE TABLE IF NOT EXISTS workout_template_exercises (
id INTEGER PRIMARY KEY AUTOINCREMENT,
template_id INTEGER NOT NULL,
exercise_id INTEGER NOT NULL,
position INTEGER NOT NULL,
sets INTEGER NOT NULL,
reps INTEGER NOT NULL,
lsrpe INTEGER NOT NULL,
rest_time INTEGER NOT NULL,
note TEXT,
FOREIGN KEY (template_id) REFERENCES workout_templates(id) ON DELETE CASCADE,
FOREIGN KEY (exercise_id) REFERENCES workout_exercises(id) ON DELETE RESTRICT
);
CREATE INDEX IF NOT EXISTS idx_workout_session_exercises_session
ON workout_session_exercises(session_id);
CREATE INDEX IF NOT EXISTS idx_workout_sessions_date
ON workout_sessions(date_time);
CREATE INDEX IF NOT EXISTS idx_workout_template_exercises_template
ON workout_template_exercises(template_id);
CREATE TABLE IF NOT EXISTS weight_logs (
id INTEGER PRIMARY KEY AUTOINCREMENT,
date_time TEXT NOT NULL,
weight REAL NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_weight_logs_date
ON weight_logs(date_time);
"""
def get_connection() -> sqlite3.Connection:
conn = sqlite3.connect(DB_PATH)
conn.row_factory = sqlite3.Row
conn.execute("PRAGMA foreign_keys = ON")
return conn
def init_db() -> None:
conn = get_connection()
conn.executescript(SCHEMA)
conn.close()
|
