myheats

commit 3c894b5f5cdac4b986641f149b6e21d372d4bb26
parent b3e36f8afb19ea026609a65e890c6594b60b8dac
Author: Andreas Gruhler <andreas.gruhler@adfinis.com>
Date:   Mon, 23 Sep 2024 22:05:22 +0200

fix(postgresql): upsert of scores

Diffstat:
M
src/api/db.cjs
 | 
20
++++++++++++++++----
M
src/api/server.cjs
 | 
14
++++++--------
M
src/frontend/Score.jsx
 | 
42
++++++++++++++++++++++++------------------

3 files changed, 46 insertions(+), 30 deletions(-)
diff --git a/src/api/db.cjs b/src/api/db.cjs
@@ -293,7 +293,15 @@ async function removeHeat(heatId) {
 async function distinctStartlist(heatIds) {
   try {
     const startlist = await sql`
-      select distinct_startlist(${heatIds})
+      select
+        id,
+        athlete,
+        nr,
+        firstname,
+        lastname,
+        birthday,
+        school
+      from distinct_startlist(${heatIds}) as athlete
     `
     return startlist
   } catch (error) {
@@ -372,12 +380,16 @@ async function getScore(heat, athlete, judge) {
   }
 }
 
+// "upsert" score (https://www.postgresql.org/docs/current/sql-insert.html)
+// For ON CONFLICT DO UPDATE, a conflict_target must be provided.
 async function setScore(heat, athlete, judge, score) {
   try {
     const scores = await sql`
-      update public.scores set
-        score = ${score},
-      where heat = ${heat} and athlete = ${athlete} and judge = ${judge}
+      insert into public.scores as s (athlete, judge, score, heat)
+        values (${athlete}, ${judge}, ${score}, ${heat})
+      on conflict (athlete, judge, heat) do update
+      set score = ${score}
+      where s.heat = ${heat} and s.athlete = ${athlete} and s.judge = ${judge}
     `
     return scores
   } catch (error) {
diff --git a/src/api/server.cjs b/src/api/server.cjs
@@ -280,8 +280,8 @@ server.on('request', async (req, res) => {
             throw new Error("Startlist not found")
           }
           res.end(JSON.stringify({
-            message: 'Distinct startlist for multiple heats',
-            data: startlist[0],
+            message: 'Distinct athletes for multiple heats',
+            data: startlist,
           }));
         } catch(error) {
           serverError(res, error);
@@ -388,9 +388,10 @@ server.on('request', async (req, res) => {
 
           const scores = await db.getScore(
                   input.heat,
-                  input.athete,
+                  input.athlete,
                   input.judge
           );
+
           if (scores.length < 1) {
             throw new Error("Score not found")
           }
@@ -418,17 +419,14 @@ server.on('request', async (req, res) => {
 
           const scores = await db.setScore(
                   input.heat,
-                  input.athete,
+                  input.athlete,
                   input.judge,
                   input.score,
           );
-          if (scores.length < 1) {
-            throw new Error("Score not updated")
-          }
 
           res.end(JSON.stringify({
             message: 'Score update for heat, user and judge',
-            data: scores[0],
+            data: scores,
           }));
         } catch (error) {
           serverError(res, error);
diff --git a/src/frontend/Score.jsx b/src/frontend/Score.jsx
@@ -11,8 +11,8 @@ async function getScore(heatId, athleteId, userId) {
     method: 'POST',
     headers: {'Content-Type': 'application/json'},
     body: JSON.stringify({
-      "heat": heatIds,
-      "ahtlete": athleteId,
+      "heat": heatId,
+      "athlete": athleteId,
       "judge": userId,
     }),
   })
@@ -29,8 +29,8 @@ async function updateScore(score, heatId, athleteId, userId) {
     headers: {'Content-Type': 'application/json'},
     body: JSON.stringify({
       "score": score,
-      "heat": heatIds,
-      "ahtlete": athleteId,
+      "heat": heatId,
+      "athlete": athleteId,
       "judge": userId,
     }),
   })
@@ -68,7 +68,6 @@ function ScoringForm({session}) {
       setHeats(data)
 
       const startlist = await getStartlistForHeats([heatSelection.value])
-      console.log(startlist.distinct_startlist)
 
       if (startlist.error) {
         setLoading(false)
@@ -88,25 +87,32 @@ function ScoringForm({session}) {
       }
 
       // check if existing score for heat and athlete exists
-      const currentScore = await getScore(
-              heatSelection.value,
-              athleteSelection.value,
-              session.id
-      )
+      try {
+        const currentScore = await getScore(
+                heatSelection.value,
+                athleteSelection.value,
+                session.auth.id
+        )
+        if (score === 0) {
+          // fallback to current score when no new scoring took place
+          setScore(currentScore.score)
+        }
+      } catch (error) {
+        console.error(error)
+      }
 
-      if (score === 0 && currentScore.data?.length > 0) {
-        // fallback to current score when no new scoring took place
-        setScore(currentScore.data[0].score)
-      } else {
-        // store new score
-        updateScore(score,
+      // store new score
+      try {
+        await updateScore(score,
           heatSelection.value,
           athleteSelection.value,
-          session.id)
+          session.auth.id)
+      } catch (error) {
+        console.error(error)
       }
       setLoading(false)
     })();
-  }, [heatSelection, athleteSelection, session.id, score]);
+  }, [heatSelection, athleteSelection, session.auth.id, score]);
 
   return (
     <div>