From 1341b07020302aea7cad109e09f360e87acbc425 Mon Sep 17 00:00:00 2001
From: Jakub Czajka <jczajka@google.com>
Date: Mon, 25 Dec 2023 10:28:59 +0100
Subject: [PATCH] Store records as references to workouts_exercises instead of
 copying values.

---
 create.sql | 28 ++++++++++++++++------------
 insert.sh  | 24 +++++++++++-------------
 record.sh  | 11 ++++++++---
 3 files changed, 35 insertions(+), 28 deletions(-)

diff --git a/create.sql b/create.sql
index 6034678..7a7828c 100644
--- a/create.sql
+++ b/create.sql
@@ -17,18 +17,6 @@ CREATE TABLE IF NOT EXISTS workouts (
     duration_min INTEGER NOT NULL
 );
 
-CREATE TABLE IF NOT EXISTS gyms_records (
-    gym_id INTEGER REFERENCES gyms (id),
-    exercise_id INTEGER REFERENCES exercises (id),
-    value VARCHAR(64) NOT NULL,
-    PRIMARY KEY (gym_id, exercise_id)
-);
-
-CREATE TABLE IF NOT EXISTS records (
-    exercise_id INTEGER PRIMARY KEY REFERENCES exercises (id),
-    value VARCHAR(64) NOT NULL
-);
-
 CREATE TABLE IF NOT EXISTS workouts_gyms (
     workout_id INTEGER PRIMARY KEY REFERENCES workouts (id),
     gym_id INTEGER REFERENCES gyms (id)
@@ -41,6 +29,22 @@ CREATE TABLE IF NOT EXISTS workouts_exercises (
     PRIMARY KEY (workout_id, exercise_id)
 );
 
+CREATE TABLE IF NOT EXISTS gyms_records (
+    gym_id INTEGER REFERENCES gyms (id),
+    exercise_id INTEGER REFERENCES exercises (id),
+    workout_id INTEGER,
+    PRIMARY KEY (gym_id, exercise_id),
+    FOREIGN KEY (workout_id, exercise_id)
+    REFERENCES workouts_exercises (workout_id, exercise_id)
+);
+
+CREATE TABLE IF NOT EXISTS records (
+    exercise_id INTEGER PRIMARY KEY REFERENCES exercises (id),
+    workout_id INTEGER,
+    FOREIGN KEY (workout_id, exercise_id)
+    REFERENCES workouts_exercises (workout_id, exercise_id)
+);
+
 DO $$
 BEGIN
     IF NOT EXISTS (SELECT * FROM pg_user WHERE usename = 'gym_tracker')
diff --git a/insert.sh b/insert.sh
index 626c234..aba0fac 100755
--- a/insert.sh
+++ b/insert.sh
@@ -92,43 +92,41 @@ case ${RECORD_TYPE} in
 
     echo "${request_body}&" | while IFS='=' read -r -d '&' key val
     do
-      # $exercise, $exercise_id and $value retain their values between
-      # iterations.
+      # $exercise and $exercise_id retain their values between iterations.
       [ -n "${val}" ] && case ${key} in
         *_local_record)
           query_db "\
-          INSERT INTO gyms_records (gym_id, exercise_id, value)
-          VALUES ('${gym_id}', '${exercise_id}', '${value}')
+          INSERT INTO gyms_records (gym_id, exercise_id, workout_id)
+          VALUES ('${gym_id}', '${exercise_id}', '${id}')
           ON CONFLICT (gym_id, exercise_id)
-          DO UPDATE SET value='${value}'"
+          DO UPDATE SET workout_id=${id}"
 
           echo "Recorded new record for '${exercise}' (id=${exercise_id}) in\
- ${gym} (id=${gym_id}) (value=${value})."
+ ${gym} (id=${gym_id}) (workout_id=${id})."
         ;;
         *_global_record)
           query_db "\
-          INSERT INTO records (exercise_id, value)
-          VALUES ('${exercise_id}', '${value}')
+          INSERT INTO records (exercise_id, workout_id)
+          VALUES ('${exercise_id}', '${id}')
           ON CONFLICT (exercise_id)
-          DO UPDATE SET value='${value}'"
+          DO UPDATE SET workout_id=${id}"
 
           echo "Recorded new global record for '${exercise}'\
- (id=${exercise_id}) (value=${value})."
+ (id=${exercise_id}) (workout_id=${id})."
         ;;
         [0-9]*)
           exercise_id="${key}"
-          value="${val}"
 
           query_db "\
           INSERT INTO workouts_exercises (workout_id, exercise_id, value)
-          VALUES ('${id}', '${exercise_id}', '${value}')"
+          VALUES ('${id}', '${exercise_id}', '${val}')"
 
           exercise=$(query_db "\
           SELECT name
           FROM exercises
           WHERE id='${exercise_id}'")
           echo "Recorded exercise '${exercise}' (id=${exercise_id}) for workout\
- (id=${id}) (value=${value})."
+ (id=${id}) (value=${val})."
         ;;
       esac
     done
diff --git a/record.sh b/record.sh
index 0f520a9..641920f 100755
--- a/record.sh
+++ b/record.sh
@@ -7,16 +7,21 @@
 exercises=$(/usr/bin/psql --user=gym_tracker  --dbname="${GYM_DB}" \
   --tuples-only --no-align --command="\
 WITH per_gym_records AS (
-  SELECT exercise_id, value
+  SELECT gyms_records.exercise_id, workouts_exercises.value
   FROM gyms_records INNER JOIN gyms
     ON gyms_records.gym_id = gyms.id
+  INNER JOIN workouts_exercises
+    ON gyms_records.exercise_id = workouts_exercises.exercise_id
+   AND gyms_records.workout_id = workouts_exercises.workout_id
   WHERE name = '${GYM}'
 ), records_all_exercises AS (
   SELECT *
   FROM per_gym_records
   UNION
-  SELECT *
-  FROM records
+  SELECT records.exercise_id, workouts_exercises.value
+  FROM records INNER JOIN workouts_exercises
+    ON records.exercise_id = workouts_exercises.exercise_id
+   AND records.workout_id = workouts_exercises.workout_id
   WHERE NOT EXISTS (
     SELECT * FROM
     per_gym_records
-- 
2.39.5