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
|
import sqlite3
import os
DB_PATH = os.path.join(os.path.dirname(os.path.abspath(__file__)), "egometrics.db")
SCHEMA = """
CREATE TABLE IF NOT EXISTS exercises (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL UNIQUE COLLATE NOCASE,
note TEXT
);
CREATE TABLE IF NOT EXISTS sessions (
id INTEGER PRIMARY KEY AUTOINCREMENT,
date_time TEXT NOT NULL,
note TEXT
);
CREATE TABLE IF NOT EXISTS session_exercises (
id INTEGER PRIMARY KEY AUTOINCREMENT,
session_id INTEGER NOT NULL,
exercise_id INTEGER NOT NULL,
position INTEGER NOT NULL,
sets INTEGER,
reps INTEGER,
rpe INTEGER,
rest_time INTEGER,
lsrpe INTEGER,
note TEXT,
FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE,
FOREIGN KEY (exercise_id) REFERENCES exercises(id) ON DELETE RESTRICT
);
CREATE INDEX IF NOT EXISTS idx_session_exercises_session
ON session_exercises(session_id);
CREATE INDEX IF NOT EXISTS idx_sessions_date
ON sessions(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()
|
