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
|
import sqlite3
# --- Workout Exercises ---
def add_workout_exercise(
conn: sqlite3.Connection,
name: str,
bw_relative: bool = False,
note: str | None = None,
) -> int:
cur = conn.execute(
"INSERT INTO workout_exercises (name, bw_relative, note) VALUES (?, ?, ?)",
(name, int(bw_relative), note),
)
conn.commit()
assert cur.lastrowid is not None
return cur.lastrowid
def list_workout_exercises(conn: sqlite3.Connection) -> list[sqlite3.Row]:
return conn.execute(
"SELECT id, name, bw_relative, note FROM workout_exercises ORDER BY name"
).fetchall()
def get_workout_exercise(
conn: sqlite3.Connection, exercise_id: int
) -> sqlite3.Row | None:
return conn.execute(
"SELECT id, name, bw_relative, note FROM workout_exercises WHERE id = ?",
(exercise_id,),
).fetchone()
def update_workout_exercise(
conn: sqlite3.Connection,
exercise_id: int,
name: str | None = None,
bw_relative: bool | None = None,
note: str | None = None,
) -> bool:
ex = get_workout_exercise(conn, exercise_id)
if not ex:
return False
conn.execute(
"UPDATE workout_exercises SET name = ?, bw_relative = ?, note = ? WHERE id = ?",
(
name or ex["name"],
int(bw_relative) if bw_relative is not None else ex["bw_relative"],
note,
exercise_id,
),
)
conn.commit()
return True
def delete_workout_exercise(conn: sqlite3.Connection, exercise_id: int) -> None:
conn.execute("DELETE FROM workout_exercises WHERE id = ?", (exercise_id,))
conn.commit()
# --- Workout Sessions ---
def create_workout_session(
conn: sqlite3.Connection, date_time: str, note: str | None = None
) -> int:
cur = conn.execute(
"INSERT INTO workout_sessions (date_time, note) VALUES (?, ?)",
(date_time, note),
)
assert cur.lastrowid is not None
return cur.lastrowid
def list_workout_sessions(
conn: sqlite3.Connection, limit: int = 20
) -> list[sqlite3.Row]:
return conn.execute(
"""SELECT s.id, s.date_time, s.note,
GROUP_CONCAT(e.name, ', ') as exercises
FROM workout_sessions s
LEFT JOIN workout_session_exercises se ON s.id = se.session_id
LEFT JOIN workout_exercises e ON se.exercise_id = e.id
GROUP BY s.id
ORDER BY s.date_time DESC
LIMIT ?""",
(limit,),
).fetchall()
def get_workout_session_detail(
conn: sqlite3.Connection, session_id: int
) -> tuple[sqlite3.Row | None, list[sqlite3.Row]]:
session = conn.execute(
"SELECT id, date_time, note FROM workout_sessions WHERE id = ?", (session_id,)
).fetchone()
if not session:
return None, []
entries = conn.execute(
"""SELECT se.id, se.position, e.name as exercise_name,
e.bw_relative,
se.sets, se.reps, se.weight, se.rest_time, se.lsrpe, se.note
FROM workout_session_exercises se
JOIN workout_exercises e ON se.exercise_id = e.id
WHERE se.session_id = ?
ORDER BY se.position""",
(session_id,),
).fetchall()
return session, entries
def delete_workout_session(conn: sqlite3.Connection, session_id: int) -> None:
conn.execute("DELETE FROM workout_sessions WHERE id = ?", (session_id,))
conn.commit()
def add_workout_session_exercise(
conn: sqlite3.Connection,
session_id: int,
exercise_id: int,
position: int,
sets: int,
reps: str,
weight: str,
rest_time: int,
lsrpe: int,
note: str | None = None,
) -> int:
cur = conn.execute(
"""INSERT INTO workout_session_exercises
(session_id, exercise_id, position, sets, reps, weight, rest_time, lsrpe, note)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)""",
(session_id, exercise_id, position, sets, reps, weight, rest_time, lsrpe, note),
)
assert cur.lastrowid is not None
return cur.lastrowid
def save_workout_session(
conn: sqlite3.Connection,
date_time: str,
note: str | None,
exercises: list[dict],
) -> int:
session_id = create_workout_session(conn, date_time, note)
for i, ex in enumerate(exercises, 1):
add_workout_session_exercise(
conn,
session_id,
ex["exercise_id"],
i,
ex["sets"],
ex["reps"],
ex["weight"],
ex["rest_time"],
ex["lsrpe"],
ex.get("note"),
)
conn.commit()
return session_id
# --- Workout Templates ---
def create_workout_template(conn: sqlite3.Connection, name: str) -> int:
cur = conn.execute("INSERT INTO workout_templates (name) VALUES (?)", (name,))
conn.commit()
assert cur.lastrowid is not None
return cur.lastrowid
def list_workout_templates(conn: sqlite3.Connection) -> list[sqlite3.Row]:
return conn.execute("""SELECT t.id, t.name,
GROUP_CONCAT(e.name, ', ') as exercises
FROM workout_templates t
LEFT JOIN workout_template_exercises te ON t.id = te.template_id
LEFT JOIN workout_exercises e ON te.exercise_id = e.id
GROUP BY t.id
ORDER BY t.name""").fetchall()
def get_workout_template(
conn: sqlite3.Connection, template_id: int
) -> sqlite3.Row | None:
return conn.execute(
"SELECT id, name FROM workout_templates WHERE id = ?", (template_id,)
).fetchone()
def get_workout_template_detail(
conn: sqlite3.Connection, template_id: int
) -> tuple[sqlite3.Row | None, list[sqlite3.Row]]:
template = get_workout_template(conn, template_id)
if not template:
return None, []
entries = conn.execute(
"""SELECT te.id, te.position, e.id as exercise_id, e.name as exercise_name,
e.bw_relative,
te.sets, te.reps, te.lsrpe, te.rest_time, te.note
FROM workout_template_exercises te
JOIN workout_exercises e ON te.exercise_id = e.id
WHERE te.template_id = ?
ORDER BY te.position""",
(template_id,),
).fetchall()
return template, entries
def update_workout_template_name(
conn: sqlite3.Connection, template_id: int, name: str
) -> bool:
t = get_workout_template(conn, template_id)
if not t:
return False
conn.execute(
"UPDATE workout_templates SET name = ? WHERE id = ?", (name, template_id)
)
conn.commit()
return True
def delete_workout_template(conn: sqlite3.Connection, template_id: int) -> None:
conn.execute("DELETE FROM workout_templates WHERE id = ?", (template_id,))
conn.commit()
def save_workout_template_exercises(
conn: sqlite3.Connection, template_id: int, exercises: list[dict]
) -> None:
"""Replace all exercises in a template with the given list."""
conn.execute(
"DELETE FROM workout_template_exercises WHERE template_id = ?", (template_id,)
)
for i, ex in enumerate(exercises, 1):
conn.execute(
"""INSERT INTO workout_template_exercises
(template_id, exercise_id, position, sets, reps, lsrpe, rest_time, note)
VALUES (?, ?, ?, ?, ?, ?, ?, ?)""",
(
template_id,
ex["exercise_id"],
i,
ex["sets"],
ex["reps"],
ex["lsrpe"],
ex["rest_time"],
ex.get("note"),
),
)
conn.commit()
|
