diff options
Diffstat (limited to 'models.py')
| -rw-r--r-- | models.py | 202 |
1 files changed, 162 insertions, 40 deletions
@@ -1,62 +1,89 @@ import sqlite3 +# --- Workout Exercises --- -def add_exercise(conn: sqlite3.Connection, name: str, note: str | None = None) -> int: - cur = conn.execute("INSERT INTO exercises (name, note) VALUES (?, ?)", (name, note)) + +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_exercises(conn: sqlite3.Connection) -> list[sqlite3.Row]: - return conn.execute("SELECT id, name, note FROM exercises ORDER BY name").fetchall() +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_exercise(conn: sqlite3.Connection, exercise_id: int) -> sqlite3.Row | None: +def get_workout_exercise( + conn: sqlite3.Connection, exercise_id: int +) -> sqlite3.Row | None: return conn.execute( - "SELECT id, name, note FROM exercises WHERE id = ?", (exercise_id,) + "SELECT id, name, bw_relative, note FROM workout_exercises WHERE id = ?", + (exercise_id,), ).fetchone() -def update_exercise( +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_exercise(conn, exercise_id) + ex = get_workout_exercise(conn, exercise_id) if not ex: return False conn.execute( - "UPDATE exercises SET name = ?, note = ? WHERE id = ?", - (name or ex["name"], note, exercise_id), + "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_exercise(conn: sqlite3.Connection, exercise_id: int) -> None: - conn.execute("DELETE FROM exercises WHERE id = ?", (exercise_id,)) +def delete_workout_exercise(conn: sqlite3.Connection, exercise_id: int) -> None: + conn.execute("DELETE FROM workout_exercises WHERE id = ?", (exercise_id,)) conn.commit() -def create_session( +# --- Workout Sessions --- + + +def create_workout_session( conn: sqlite3.Connection, date_time: str, note: str | None = None ) -> int: cur = conn.execute( - "INSERT INTO sessions (date_time, note) VALUES (?, ?)", (date_time, note) + "INSERT INTO workout_sessions (date_time, note) VALUES (?, ?)", + (date_time, note), ) assert cur.lastrowid is not None return cur.lastrowid -def list_sessions(conn: sqlite3.Connection, limit: int = 20) -> list[sqlite3.Row]: +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 sessions s - LEFT JOIN session_exercises se ON s.id = se.session_id - LEFT JOIN exercises e ON se.exercise_id = e.id + 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 ?""", @@ -64,19 +91,20 @@ def list_sessions(conn: sqlite3.Connection, limit: int = 20) -> list[sqlite3.Row ).fetchall() -def get_session_detail( +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 sessions WHERE id = ?", (session_id,) + "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, - se.sets, se.reps, se.rpe, se.rest_time, se.lsrpe, se.note - FROM session_exercises se - JOIN exercises e ON se.exercise_id = e.id + 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,), @@ -84,49 +112,143 @@ def get_session_detail( return session, entries -def delete_session(conn: sqlite3.Connection, session_id: int) -> None: - conn.execute("DELETE FROM sessions WHERE id = ?", (session_id,)) +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_session_exercise( +def add_workout_session_exercise( conn: sqlite3.Connection, session_id: int, exercise_id: int, position: int, sets: int, - reps: int, - rpe: int | None = None, - rest_time: int | None = None, - lsrpe: int | None = None, + reps: str, + weight: str, + rest_time: int, + lsrpe: int, note: str | None = None, ) -> int: cur = conn.execute( - """INSERT INTO session_exercises - (session_id, exercise_id, position, sets, reps, rpe, rest_time, lsrpe, note) + """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, rpe, rest_time, lsrpe, note), + (session_id, exercise_id, position, sets, reps, weight, rest_time, lsrpe, note), ) assert cur.lastrowid is not None return cur.lastrowid -def save_session( - conn: sqlite3.Connection, date_time: str, note: str | None, exercises: list[dict] +def save_workout_session( + conn: sqlite3.Connection, + date_time: str, + note: str | None, + exercises: list[dict], ) -> int: - session_id = create_session(conn, date_time, note) + session_id = create_workout_session(conn, date_time, note) for i, ex in enumerate(exercises, 1): - add_session_exercise( + add_workout_session_exercise( conn, session_id, ex["exercise_id"], i, ex["sets"], ex["reps"], - ex.get("rpe"), - ex.get("rest_time"), - ex.get("lsrpe"), + 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() |