commit 83606fe52734bd3bf066fed145f84650c15add54
parent 925dfdeb7eb4d2cfe2ed305791a20af4287a692e
Author: Andreas Gruhler <andreas.gruhler@adfinis.com>
Date: Sun, 22 Sep 2024 12:47:06 +0200
feat: replace supabase with postgresql
Diffstat:
10 files changed, 1104 insertions(+), 203 deletions(-)
diff --git a/src/api/db.cjs b/src/api/db.cjs
@@ -15,7 +15,11 @@ require('dotenv').config({
// will use psql environment variables
// https://github.com/porsager/postgres?tab=readme-ov-file#environmental-variables
-const sql = pg()
+const sql = pg({
+ publications: [
+ 'scores',
+ ]
+})
require('dotenv').config({
// Configure common config files and modes
@@ -94,6 +98,313 @@ async function getUser(email) {
}
}
+async function exportHeatsToCSV() {
+ try {
+ const heats = await sql`
+ copy (select created_at,
+ name,
+ location,
+ planned_start
+ from heats)
+ to 'heat_rows.csv'
+ delimiter ','
+ csv header
+ `
+ return heats
+ } catch (error) {
+ console.log('Error occurred in exportHeatsToCSV:', error);
+ throw error.errors[0]
+ }
+}
+
+async function exportAthletesToCSV() {
+ try {
+ const athletes = await sql`
+ copy (select created_at,
+ nr,
+ firstname,
+ lastname,
+ birthday,
+ school
+ from athletes)
+ to 'athlete_rows.csv'
+ delimiter ','
+ csv header
+ `
+ return athletes
+ } catch (error) {
+ console.log('Error occurred in exportAthletesToCSV:', error);
+ throw error.errors[0]
+ }
+}
+
+async function allHeats() {
+ try {
+ const heats = await sql`
+ select * from heats
+ `
+ return heats
+ } catch (error) {
+ console.log('Error occurred in allHeats:', error);
+ throw error.errors[0]
+ }
+}
+
+async function getHeat(heatId) {
+ try {
+ const heat = await sql`
+ select * from heats
+ where id = ${heatId}
+ `
+ return heat
+ } catch (error) {
+ console.log('Error occurred in getHeat:', error);
+ throw error.errors[0]
+ }
+}
+
+async function allAthletes() {
+ try {
+ const athletes = await sql`
+ select * from athletes
+ `
+ return athletes
+ } catch (error) {
+ console.log('Error occurred in allHeats:', error);
+ throw error.errors[0]
+ }
+}
+
+async function addAthleteToHeat(athlete, heat) {
+ try {
+ const startlist = await sql`
+ insert into startlist (
+ athlete,
+ heat
+ )
+ values (
+ ${athlete},
+ ${heat}
+ )
+ `
+ return startlist
+ } catch (error) {
+ console.log('Error occurred in addAthleteToHeat:', error);
+ throw error
+ }
+}
+
+async function addAthlete(nr, firstname, lastname, birthday, school) {
+ try {
+ const athlete = await sql`
+ insert into athletes (
+ nr,
+ firstname,
+ lastname,
+ birthday,
+ school
+ )
+ values (
+ ${nr},
+ ${firstname},
+ ${lastname},
+ ${birthday},
+ ${school}
+ )
+ `
+ return athlete
+ } catch (error) {
+ console.log('Error occurred in addAthlete:', error);
+ throw error
+ }
+}
+
+async function removeAthlete(id) {
+ try {
+ const athlete = await sql`
+ delete from athletes where id = ${id}
+ `
+ return athlete
+ } catch (error) {
+ console.log('Error occurred in removeAthlete:', error);
+ throw error
+ }
+}
+
+async function removeAthleteFromHeat(startlistId) {
+ try {
+ const startlist = await sql`
+ delete from startlist
+ where id = ${startlistId}
+ `
+ return startlist
+ } catch (error) {
+ console.log('Error occurred in removeAthleteFromHeat:', error);
+ throw error
+ }
+}
+
+async function removeAthlete(athleteId) {
+ try {
+ const athlete = await sql`
+ delete from athletes
+ where id = ${athleteId}
+ `
+ return athlete
+ } catch (error) {
+ console.log('Error occurred in removeAthlete:', error);
+ throw error.errors[0]
+ }
+}
+
+async function newHeat(name, heatLocation, plannedStart) {
+ try {
+ const heat = await sql`
+ insert into heats (
+ name,
+ location,
+ planned_start
+ )
+ values (
+ ${name},
+ ${heatLocation},
+ ${plannedStart}
+ )
+ `
+ return heat
+ } catch (error) {
+ console.log('Error occurred in newHeat:', error);
+ throw error.errors[0]
+ }
+}
+
+async function removeHeat(heatId) {
+ try {
+ const heat = await sql`
+ delete from heats where id = ${heatId}
+ `
+ return heat
+ } catch (error) {
+ console.log('Error occurred in removeHeat:', error);
+ throw error
+ }
+}
+
+async function distinctStartlist(heatIds) {
+ try {
+ const startlist = await sql`
+ select distinct_startlist(${heatIds})
+ `
+ return startlist
+ } catch (error) {
+ console.log('Error occurred in distinctStartlist:', error);
+ throw error.errors[0]
+ }
+}
+
+async function startlistWithAthletes(heatId) {
+ try {
+ // left outer join, to fetch heats with no athletes
+ // https://www.postgresql.org/docs/current/tutorial-join.html
+ const startlist = await sql`
+ select
+ s.id as startlist_id,
+ s.heat,
+ s.athlete,
+ a.id as athlete_id,
+ a.nr,
+ a.firstname,
+ a.lastname,
+ a.birthday,
+ a.school
+ from startlist as s
+ left outer join athletes as a
+ on s.athlete = a.id
+ where s.heat = ${heatId}
+ `
+ return startlist
+ } catch (error) {
+ console.log('Error occurred in startlistWithAthletes:', error);
+ throw error.errors[0]
+ }
+}
+
+async function scoreForHeatAndAthlete(heat, athlete) {
+ try {
+ const score = await sql`
+ select
+ id,
+ athlete,
+ judge,
+ score
+ from scores where heat = ${heat} and athlete = ${athlete}
+ `
+ return score
+ } catch (error) {
+ console.log('Error occurred in scoreForHeatAndAthlete:', error);
+ throw error.errors[0]
+ }
+}
+
+async function scoreSummaryForHeatAndAthlete(heat, athlete) {
+ try {
+ const summary = await sql`
+ select score_summary
+ from score_summary where heat = ${heat} and athlete = ${athlete}
+ `
+ return summary
+ } catch (error) {
+ console.log('Error occurred in scoreSummaryForHeatAndAthlete:', error);
+ throw error.errors[0]
+ }
+}
+
+async function getScore(heat, athlete, judge) {
+ try {
+ const scores = await sql`
+ select * from scores
+ where heat = ${heat} and athlete = ${athlete} and judge = ${judge}
+ `
+ return scores
+ } catch (error) {
+ console.log('Error occurred in getScore:', error);
+ throw error.errors[0]
+ }
+}
+
+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}
+ `
+ return scores
+ } catch (error) {
+ console.log('Error occurred in setScore:', error);
+ throw error.errors[0]
+ }
+}
+
+async function getScores() {
+ try {
+ const { unsubscribe } = await sql.subscribe(
+ '*:scores',
+ (row, { command, relation, key, old }) => {
+ // Callback function for each row change
+ // tell about new event row over eg. websockets or do something else
+ console.log("!! SCORES CHANGED:", row)
+ return row
+ },
+ () => {
+ // Callback on initial connect and potential reconnects
+ }
+ )
+ } catch (error) {
+ console.log('Error occurred in getScores:', error);
+ throw error.errors[0]
+ }
+}
module.exports = {
sql,
@@ -101,4 +412,22 @@ module.exports = {
lookupToken,
saveToken,
getUser,
+ allHeats,
+ getHeat,
+ allAthletes,
+ newHeat,
+ removeHeat,
+ distinctStartlist,
+ startlistWithAthletes,
+ scoreForHeatAndAthlete,
+ scoreSummaryForHeatAndAthlete,
+ getScore,
+ setScore,
+ getScores,
+ addAthleteToHeat,
+ addAthlete,
+ removeAthlete,
+ removeAthleteFromHeat,
+ exportHeatsToCSV,
+ exportAthletesToCSV,
}
diff --git a/src/api/server.cjs b/src/api/server.cjs
@@ -57,6 +57,23 @@ const paths = [
'/v1/auth/verify',
'/v1/echo',
'/v1/auth/requestMagicLink',
+ '/v1/leaderboard/allHeats',
+ '/v1/leaderboard/allAthletes',
+ '/v1/leaderboard/newHeat',
+ '/v1/leaderboard/getHeat',
+ '/v1/leaderboard/removeHeat',
+ '/v1/leaderboard/distinctStartlist',
+ '/v1/leaderboard/startlistWithAthletes',
+ '/v1/leaderboard/scoreForHeatAndAthlete',
+ '/v1/leaderboard/scoreSummaryForHeatAndAthlete',
+ '/v1/leaderboard/getScore',
+ '/v1/leaderboard/setScore',
+ '/v1/leaderboard/addAthleteToHeat',
+ '/v1/leaderboard/removeAthleteFromHeat',
+ '/v1/leaderboard/exportHeatsToCSV',
+ '/v1/leaderboard/exportAthletesToCSV',
+ '/v1/leaderboard/addAthlete',
+ '/v1/leaderboard/removeAthlete',
]
console.log("Backend API:", api_uri);
@@ -89,7 +106,10 @@ server.on('request', async (req, res) => {
res.setHeader('Access-Control-Allow-Methods', 'GET, POST, OPTIONS');
res.setHeader('Access-Control-Allow-Headers', 'Content-Type');
- if (req.method === 'GET') {
+ // cors pre-flight request uses options method
+ if (req.method === 'OPTIONS') {
+ res.end()
+ } else if (req.method === 'GET') {
if (url.pathname === '/v1/healthz') {
res.end(JSON.stringify({
message: 'egg fried rice 🍚 fuiyooh!',
@@ -130,6 +150,48 @@ server.on('request', async (req, res) => {
} catch (error) {
serverError(res, error);
}
+ } else if (url.pathname === '/v1/leaderboard/allHeats') {
+ try {
+ const heats = await db.allHeats()
+
+ res.end(JSON.stringify({
+ message: 'All heats',
+ data: heats,
+ }));
+ } catch(error) {
+ serverError(res, error);
+ }
+ } else if (url.pathname === '/v1/leaderboard/allAthletes') {
+ try {
+ const athletes = await db.allAthletes()
+
+ res.end(JSON.stringify({
+ message: 'All athletes',
+ data: athletes,
+ }));
+ } catch(error) {
+ serverError(res, error);
+ }
+ } else if (url.pathname === '/v1/leaderboard/exportHeatsToCSV') {
+ try {
+ const heats = await db.exportHeatsToCSV()
+
+ res.setHeader('Content-Type', 'text/csv');
+ res.setHeader("Content-Disposition", "attachment;filename=myfilename.csv");
+ res.end(heats);
+ } catch(error) {
+ serverError(res, error);
+ }
+ } else if (url.pathname === '/v1/leaderboard/exportAthletesToCSV') {
+ try {
+ const athletes = await db.exportAthletesToCSV()
+
+ res.setHeader('Content-Type', 'text/csv');
+ res.setHeader("Content-Disposition", "attachment;filename=myfilename.csv");
+ res.end(athletes);
+ } catch(error) {
+ serverError(res, error);
+ }
} else {
const pathExists = paths.find((i) => i === url.pathname);
if (pathExists) {
@@ -139,9 +201,6 @@ server.on('request', async (req, res) => {
notFound(res, url.pathname);
}
}
- // cors pre-flight request uses options method
- } else if (req.method === 'OPTIONS') {
- res.end()
} else if (req.method === 'POST') {
if (url.pathname === '/v1/echo') {
let body = [];
@@ -202,6 +261,353 @@ server.on('request', async (req, res) => {
serverError(res, error);
}
})
+ } else if (url.pathname === '/v1/leaderboard/distinctStartlist') {
+ let body = [];
+ req.on('data', chunk => {
+ body.push(chunk);
+ }).on('end', async () => {
+ const b = Buffer.concat(body);
+ try {
+ if (b.length < 1) {
+ throw new Error("Empty request body")
+ }
+ input = JSON.parse(b);
+ console.log(' distinctStartlist request with headIds:', input.heat_ids);
+
+ const startlist = await db.distinctStartlist(input.heat_ids);
+
+ if (startlist.length < 1) {
+ throw new Error("Startlist not found")
+ }
+ res.end(JSON.stringify({
+ message: 'Distinct startlist for multiple heats',
+ data: startlist[0],
+ }));
+ } catch(error) {
+ serverError(res, error);
+ }
+ })
+ } else if (url.pathname === '/v1/leaderboard/startlistWithAthletes') {
+ let body = [];
+ req.on('data', chunk => {
+ body.push(chunk);
+ }).on('end', async () => {
+ const b = Buffer.concat(body);
+ try {
+ if (b.length < 1) {
+ throw new Error("Empty request body")
+ }
+ input = JSON.parse(b);
+ console.log(' startlistWithAthletes request with headId:', input.heat_id);
+
+ const startlist = await db.startlistWithAthletes(input.heat_id);
+
+ if (startlist.length < 1) {
+ throw new Error("No athletes for this startlist")
+ }
+ res.end(JSON.stringify({
+ message: 'Startlist with athletes for heat',
+ data: startlist,
+ }));
+ } catch(error) {
+ serverError(res, error);
+ }
+ })
+ } else if (url.pathname === '/v1/leaderboard/scoreForHeatAndAthlete') {
+ let body = [];
+ req.on('data', chunk => {
+ body.push(chunk);
+ }).on('end', async () => {
+ const b = Buffer.concat(body);
+ try {
+ if (b.length < 1) {
+ throw new Error("Empty request body")
+ }
+ input = JSON.parse(b);
+ console.log(' scoreForHeatAndAthlete request with heat and athlete:',
+ input.heat, input.athlete);
+
+ const score = await db.scoreForHeatAndAthlete(
+ input.heat,
+ input.athlete
+ )
+
+ if (score.length < 1) {
+ throw new Error("Score not found")
+ }
+ res.end(JSON.stringify({
+ message: 'Score for heat and athlete',
+ data: score[0],
+ }));
+ } catch(error) {
+ serverError(res, error);
+ }
+ })
+ } else if (url.pathname === '/v1/leaderboard/scoreSummaryForHeatAndAthlete') {
+ let body = [];
+ req.on('data', chunk => {
+ body.push(chunk);
+ }).on('end', async () => {
+ const b = Buffer.concat(body);
+ try {
+ if (b.length < 1) {
+ throw new Error("Empty request body")
+ }
+ input = JSON.parse(b);
+ console.log(' scoreSummaryForHeatAndAthlete request with heat and athlete:',
+ input.heat, input.athlete);
+
+ const summary = await db.scoreSummaryForHeatAndAthlete(
+ input.heat,
+ input.athlete
+ )
+
+ if (summary.length < 1) {
+ throw new Error("Score summary not found")
+ }
+ res.end(JSON.stringify({
+ message: 'Score summary for heat and athlete',
+ data: summary[0],
+ }));
+ } catch(error) {
+ serverError(res, error);
+ }
+ })
+ } else if (url.pathname === '/v1/leaderboard/getScore') {
+ let body = [];
+ req.on('data', chunk => {
+ body.push(chunk);
+ }).on('end', async () => {
+ const b = Buffer.concat(body);
+ try {
+ if (b.length < 1) {
+ throw new Error("Empty request body")
+ }
+ input = JSON.parse(b);
+ console.log(' GetScore request for:', input);
+
+ const scores = await db.getScore(
+ input.heat,
+ input.athete,
+ input.judge
+ );
+ if (scores.length < 1) {
+ throw new Error("Score not found")
+ }
+
+ res.end(JSON.stringify({
+ message: 'Requested score for heat, user and judge',
+ data: scores[0],
+ }));
+ } catch (error) {
+ serverError(res, error);
+ }
+ })
+ } else if (url.pathname === '/v1/leaderboard/setScore') {
+ let body = [];
+ req.on('data', chunk => {
+ body.push(chunk);
+ }).on('end', async () => {
+ const b = Buffer.concat(body);
+ try {
+ if (b.length < 1) {
+ throw new Error("Empty request body")
+ }
+ input = JSON.parse(b);
+ console.log(' SetScore request for:', input);
+
+ const scores = await db.setScore(
+ input.heat,
+ input.athete,
+ 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],
+ }));
+ } catch (error) {
+ serverError(res, error);
+ }
+ })
+ } else if (url.pathname === '/v1/leaderboard/newHeat') {
+ let body = [];
+ req.on('data', chunk => {
+ body.push(chunk);
+ }).on('end', async () => {
+ const b = Buffer.concat(body);
+ try {
+ if (b.length < 1) {
+ throw new Error("Empty request body")
+ }
+ input = JSON.parse(b);
+ console.log(' newHeat request for:', input);
+
+ const heats = await db.newHeat(
+ input.name,
+ input.location,
+ input.planned_start,
+ );
+ if (heats.length < 1) {
+ throw new Error("Heat not created")
+ }
+
+ res.end(JSON.stringify({
+ message: 'New heat',
+ data: heats[0],
+ }));
+ } catch (error) {
+ serverError(res, error);
+ }
+ })
+ } else if (url.pathname === '/v1/leaderboard/getHeat') {
+ let body = [];
+ req.on('data', chunk => {
+ body.push(chunk);
+ }).on('end', async () => {
+ const b = Buffer.concat(body);
+ try {
+ if (b.length < 1) {
+ throw new Error("Empty request body")
+ }
+ input = JSON.parse(b);
+ console.log(' getHeat request for:', input);
+
+ const heats = await db.getHeat(
+ input.heat_id,
+ );
+ if (heats.length < 1) {
+ throw new Error("Heat not created")
+ }
+
+ res.end(JSON.stringify({
+ message: 'New heat',
+ data: heats[0],
+ }));
+ } catch (error) {
+ serverError(res, error);
+ }
+ })
+ } else if (url.pathname === '/v1/leaderboard/removeHeat') {
+ let body = [];
+ req.on('data', chunk => {
+ body.push(chunk);
+ }).on('end', async () => {
+ const b = Buffer.concat(body);
+ try {
+ if (b.length < 1) {
+ throw new Error("Empty request body")
+ }
+ input = JSON.parse(b);
+ console.log(' removeHeat request for:', input);
+
+ await db.removeHeat(input.heat_id)
+ res.end(JSON.stringify({
+ message: 'Heat removed',
+ }));
+ } catch (error) {
+ serverError(res, error);
+ }
+ })
+ } else if (url.pathname === '/v1/leaderboard/addAthleteToHeat') {
+ let body = [];
+ req.on('data', chunk => {
+ body.push(chunk);
+ }).on('end', async () => {
+ const b = Buffer.concat(body);
+ try {
+ if (b.length < 1) {
+ throw new Error("Empty request body")
+ }
+ input = JSON.parse(b);
+ console.log(' addAthleteToHeat request for:', input);
+
+ await db.addAthleteToHeat(
+ input.athlete,
+ input.heat,
+ );
+
+ res.end(JSON.stringify({
+ message: 'Athlete added to startlist'
+ }));
+ } catch (error) {
+ serverError(res, error);
+ }
+ })
+ } else if (url.pathname === '/v1/leaderboard/removeAthleteFromHeat') {
+ let body = [];
+ req.on('data', chunk => {
+ body.push(chunk);
+ }).on('end', async () => {
+ const b = Buffer.concat(body);
+ try {
+ if (b.length < 1) {
+ throw new Error("Empty request body")
+ }
+ input = JSON.parse(b);
+ console.log(' removeAthleteFromHeat request for:', input);
+
+ await db.removeAthleteFromHeat(input.startlist_id)
+ res.end(JSON.stringify({
+ message: 'Athlete removed from startlist'
+ }));
+ } catch (error) {
+ serverError(res, error);
+ }
+ })
+ } else if (url.pathname === '/v1/leaderboard/addAthlete') {
+ let body = [];
+ req.on('data', chunk => {
+ body.push(chunk);
+ }).on('end', async () => {
+ const b = Buffer.concat(body);
+ try {
+ if (b.length < 1) {
+ throw new Error("Empty request body")
+ }
+ input = JSON.parse(b);
+ console.log(' addAthlete request for:', input);
+
+ await db.addAthlete(
+ input.nr,
+ input.firstname,
+ input.lastname,
+ input.birthday,
+ input.school,
+ );
+
+ res.end(JSON.stringify({
+ message: 'Athlete created'
+ }));
+ } catch (error) {
+ serverError(res, error);
+ }
+ })
+ } else if (url.pathname === '/v1/leaderboard/removeAthlete') {
+ let body = [];
+ req.on('data', chunk => {
+ body.push(chunk);
+ }).on('end', async () => {
+ const b = Buffer.concat(body);
+ try {
+ if (b.length < 1) {
+ throw new Error("Empty request body")
+ }
+ input = JSON.parse(b);
+ console.log(' removeAthlete request for:', input);
+
+ await db.removeAthlete(input.athlete_id)
+ res.end(JSON.stringify({
+ message: 'Athlete removed'
+ }));
+ } catch (error) {
+ serverError(res, error);
+ }
+ })
} else {
const pathExists = paths.find((i) => i === url.pathname);
if (pathExists) {
@@ -251,8 +657,35 @@ const clients = new Set();
// Listen for websocket connections
wss1.on('connection', function connection(sock) {
- sock.on('message', function message(data) {
- console.log(' Uncle roger hears: %s', data);
+ sock.on('message', async function message(m) {
+ try {
+ msg = JSON.parse(m)
+ console.log(' Uncle roger hears: %s', msg);
+
+ if (msg.method === 'getStartListForHeats') {
+ const startlist = await db.getStartlistForHeats(msg.data.heatIds)
+ sock.send(JSON.stringify({
+ "data": startlist,
+ }));
+ } else if (msg.method === 'getScoreForHeatAndAthlete') {
+ const score = await db.getScoreForHeatAndAthlete(msg.data.heatId, msg.data.athleteId)
+ sock.send(JSON.stringify({
+ "data": score,
+ }));
+ } else if (msg.method === 'getHeats') {
+ const heats = await db.getHeats()
+ sock.send(JSON.stringify({
+ "data": heats,
+ }));
+ } else if (msg.method === 'getScores') {
+ const scores = await db.getScores()
+ sock.send(JSON.stringify({
+ "data": scores,
+ }));
+ }
+ } catch (error) {
+ console.log('x Error: %s', error.message);
+ }
});
sock.on('error', console.error);
@@ -268,8 +701,8 @@ wss1.on('connection', function connection(sock) {
console.log(`~ Received a new websocket connection`);
console.log(` ${sock} connected`);
- sock.send('🎵 Streaming leaderboard live data..');
- sock.close(1000, '< Websocket rice party done 👋 uncle roger disowns niece/nephew');
+ //sock.send('🎵 Streaming leaderboard live data..');
+ //sock.close(1000, '< Websocket rice party done 👋 uncle roger disowns niece/nephew');
});
// Listen to upgrade event
diff --git a/src/frontend/App.jsx b/src/frontend/App.jsx
@@ -1,7 +1,6 @@
import './App.css'
import { Suspense, lazy, useState, useEffect, Fragment } from 'react'
import { BrowserRouter as Router, Routes, Route, Outlet, Link, NavLink } from 'react-router-dom'
-import { supabase } from './supabaseClient'
import { CookiesProvider, useCookies } from 'react-cookie'
const Score = lazy(() => import('./Score'))
diff --git a/src/frontend/Athletes.jsx b/src/frontend/Athletes.jsx
@@ -1,9 +1,10 @@
import { lazy, useEffect, useState } from 'react'
-import { supabase } from './supabaseClient'
import { exportAthletesToCSV } from './utils'
-
const Auth = lazy(() => import('./Auth'))
+const api_uri = import.meta.env.VITE_API_URI ? import.meta.env.VITE_API_URI: 'http://127.0.0.1'
+const api_port = import.meta.env.VITE_API_PORT ? import.meta.env.VITE_API_PORT: '8000'
+
async function addAthlete(e) {
e.preventDefault()
@@ -12,21 +13,22 @@ async function addAthlete(e) {
const formJson = Object.fromEntries(formData.entries());
// create new athlete
- const { error } = await supabase
- .from('athletes')
- .insert({
- nr: formJson.nr,
- firstname: formJson.firstname,
- lastname: formJson.lastname,
- birthday: formJson.birthday ? formJson.birthday : null,
- school: formJson.school
- })
-
- if (error === null) {
- window.location.reload()
- } else {
+ const res = await fetch(`${api_uri}:${api_port}/v1/leaderboard/addAthlete`, {
+ method: 'POST',
+ headers: {'Content-Type': 'application/json'},
+ body: JSON.stringify({
+ "nr": formJson.nr,
+ "firstname": formJson.firstname,
+ "lastname": formJson.lastname,
+ "birthday": formJson.birthday ? formJson.birthday : null,
+ "school": formJson.school
+ }),
+ })
+ const { data, error } = await res.json()
+ if (error) {
alert('Failed to create new athlete: ' + error.message)
}
+ window.location.reload()
}
async function deleteAthlete(e, athleteId, athleteFirstName, athleteLastName) {
@@ -34,10 +36,17 @@ async function deleteAthlete(e, athleteId, athleteFirstName, athleteLastName) {
const athleteName = athleteFirstName + (athleteLastName ? ' ' + athleteLastName : '')
if (window.confirm('Do you really want to delete athlete "' + athleteName + '"?')) {
- await supabase
- .from('athletes')
- .delete()
- .eq('id', athleteId)
+ const res = await fetch(`${api_uri}:${api_port}/v1/leaderboard/removeAthlete`, {
+ method: 'POST',
+ headers: {'Content-Type': 'application/json'},
+ body: JSON.stringify({
+ "athlete_id": athleteId,
+ }),
+ })
+ const { data, error } = await res.json()
+ if (error) {
+ alert('Failed to delete athlete: ' + error.message)
+ }
window.location.reload()
}
}
@@ -60,9 +69,13 @@ function AthleteForm({session}) {
useEffect(() => {
(async () => {
setLoading(true)
- const athleteList = await supabase.from('athletes').select()
- if (athleteList.error === null)
- setAthletes(athleteList.data)
+ const res = await fetch(`${api_uri}:${api_port}/v1/leaderboard/allAthletes`)
+ const { data, error } = await res.json()
+ if (error) {
+ console.log(error)
+ } else {
+ setAthletes(data)
+ }
setLoading(false)
})();
}, [])
diff --git a/src/frontend/Heats.jsx b/src/frontend/Heats.jsx
@@ -1,10 +1,30 @@
import { lazy, useEffect, useState } from 'react'
import { generatePath, Link } from 'react-router-dom'
-import { supabase } from './supabaseClient'
import { exportHeatsToCSV } from './utils'
+const api_uri = import.meta.env.VITE_API_URI ? import.meta.env.VITE_API_URI: 'http://127.0.0.1'
+const api_port = import.meta.env.VITE_API_PORT ? import.meta.env.VITE_API_PORT: '8000'
+
const Auth = lazy(() => import('./Auth'))
+export async function addNewHeat(name, heatLocation, plannedStart) {
+ try {
+ const res = await fetch(`${api_uri}:${api_port}/v1/leaderboard/newHeat`, {
+ method: 'POST',
+ headers: {'Content-Type': 'application/json'},
+ body: JSON.stringify({
+ "name": name,
+ "location": heatLocation,
+ "planned_start": plannedStart
+ }),
+ })
+ const { data, error } = await res.json()
+ return data
+ } catch (error) {
+ throw(error)
+ }
+}
+
async function addHeat(e) {
e.preventDefault()
@@ -13,32 +33,41 @@ async function addHeat(e) {
const formJson = Object.fromEntries(formData.entries());
// create new heat
- const { error } = await supabase
- .from('heats')
- .insert({
- name: formJson.name,
- location: formJson.location,
- // planned_start is an empty string if unset
- // https://developer.mozilla.org/en-US/docs/Web/HTML/Element/input/time
- planned_start: formJson.planned_start === '' ? null : formJson.planned_start
- })
-
- if (error === null) {
+ try {
+ const heat = await addNewHeat(
+ formJson.name,
+ formJson.location,
+ // planned_start is an empty string if unset
+ // https://developer.mozilla.org/en-US/docs/Web/HTML/Element/input/time
+ formJson.planned_start === '' ? null : formJson.planned_start
+ )
window.location.reload()
- } else {
- alert('Failed to create new heat: ' + error.message)
+ } catch (error) {
+ console.error('Failed to create new heat: ' + error.message)
}
}
+export async function removeHeat(heatId) {
+ const res = await fetch(`${api_uri}:${api_port}/v1/leaderboard/removeHeat`, {
+ method: 'POST',
+ headers: {'Content-Type': 'application/json'},
+ body: JSON.stringify({
+ "heat_id": heatId
+ }),
+ })
+ return await res.json()
+}
+
async function deleteHeat(e, heatId, heatName) {
e.preventDefault()
if (window.confirm('Do you really want to delete heat "' + heatName + '"?')) {
- await supabase
- .from('heats')
- .delete()
- .eq('id', heatId)
- window.location.reload()
+ const { data, error } = await removeHeat(heatId)
+ if (error === undefined) {
+ window.location.reload()
+ } else {
+ console.error(error)
+ }
}
}
@@ -60,9 +89,12 @@ function HeatForm({session}) {
useEffect(() => {
(async () => {
setLoading(true)
- const heatList = await supabase.from('heats').select()
- if (heatList.error === null)
- setHeats(heatList.data)
+ const res = await fetch(`${api_uri}:${api_port}/v1/leaderboard/allHeats`)
+ const { data, error } = await res.json()
+ if (error) {
+ console.error(error)
+ }
+ setHeats(data)
setLoading(false)
})();
}, [])
diff --git a/src/frontend/Leaderboard.jsx b/src/frontend/Leaderboard.jsx
@@ -1,29 +1,84 @@
-import { supabase } from './supabaseClient'
import { exportLeaderboardToCSV, rankByHeat, getScores } from './utils'
import { Fragment, useEffect, useState, useRef } from 'react'
import Select from 'react-select'
+import { addNewHeat } from './Heats'
+
+const api_uri = import.meta.env.VITE_API_URI ? import.meta.env.VITE_API_URI: 'http://127.0.0.1'
+const api_port = import.meta.env.VITE_API_PORT ? import.meta.env.VITE_API_PORT: '8000'
+
+// use a socket for the real-time leaderboard data
+let socket = new WebSocket("ws://127.0.0.1:8000/v1/leaderboard");
+
+export async function addAthleteToHeat(athlete, heat) {
+ const res = await fetch(`${api_uri}:${api_port}/v1/leaderboard/addAthleteToHeat`, {
+ method: 'POST',
+ headers: {'Content-Type': 'application/json'},
+ body: JSON.stringify({
+ "athlete": athlete,
+ "heat": heat,
+ }),
+ })
+ const { data, error } = await res.json()
+ if (error) {
+ throw error
+ }
+ return data
+}
export async function getStartlistForHeats(heatIds) {
- return supabase.rpc('distinct_startlist', { 'heat_ids': heatIds })
+ const res = await fetch(`${api_uri}:${api_port}/v1/leaderboard/distinctStartlist`, {
+ method: 'POST',
+ headers: {'Content-Type': 'application/json'},
+ body: JSON.stringify({
+ "heat_ids": heatIds,
+ }),
+ })
+ const { data, error } = await res.json()
+ if (error) {
+ throw error
+ }
+ return data
}
async function getScoreForHeatAndAthlete(heatId, athleteId) {
- return supabase.from('scores').select(`
- id,
- athlete,
- judge,
- score
- `)
- .eq('heat', heatId)
- .eq('athlete', athleteId)
+ const res = await fetch(`${api_uri}:${api_port}/v1/leaderboard/scoreForHeatAndAthlete`, {
+ method: 'POST',
+ headers: {'Content-Type': 'application/json'},
+ body: JSON.stringify({
+ "heat": heatId,
+ "athlete": athleteId,
+ }),
+ })
+ const { data, error } = await res.json()
+ if (error) {
+ throw error
+ }
+ return data
+}
+
+async function getScoreSummaryForHeatAndAthlete(heatId, athleteId) {
+ const res = await fetch(`${api_uri}:${api_port}/v1/leaderboard/scoreSummaryForHeatAndAthlete`, {
+ method: 'POST',
+ headers: {'Content-Type': 'application/json'},
+ body: JSON.stringify({
+ "heat": heatId,
+ "athlete": athleteId,
+ }),
+ })
+ const { data, error } = await res.json()
+ if (error) {
+ throw error
+ }
+ return data
}
async function getScoreSummary(heatIds) {
const startListWithScores = []
- const startlist = await getStartlistForHeats(heatIds)
-
- if (startlist.error !== null) {
+ try {
+ const startlist = await getStartlistForHeats(heatIds)
+ } catch (error) {
+ console.error(error)
// fail silently & return empty startlist in case of errors
return []
}
@@ -32,21 +87,20 @@ async function getScoreSummary(heatIds) {
i.heats = []
for (const h of heatIds) {
- const scores = await getScoreForHeatAndAthlete(h, i.athlete)
-
- const summary = await supabase.from('score_summary').select('score_summary')
- .eq('heat_id', h)
- .eq('athlete_id', i.athlete)
+ try {
+ const scores = await getScoreForHeatAndAthlete(h, i.athlete)
+ const summary = await getScoreSummaryForHeatAndAthlete(h, i.athlete)
- if (summary.error === null) {
// add heat results of athlete to startlist entry
i.heats.push({
heatId: h,
scores: scores.data,
summary: summary.data.length > 0 ? summary.data[0].score_summary : 0
})
+ } catch (error) {
+ // else don't push any heats (fail silently)
+ console.error(error)
}
- // else don't push any heats (fail silently)
// find best/worst heat
i.bestHeat = Math.max(...i.heats.map(h => h.summary))
@@ -75,28 +129,20 @@ async function newHeatFromLeaderboard(e, {leaderboard, rankingComp, selectHeatRe
const formJson = Object.fromEntries(formData.entries());
// create new heat
- const { data, error } = await supabase
- .from('heats')
- .insert({
- name: formJson.name,
- location: formJson.location,
- // planned_start is an empty string if unset
- // https://developer.mozilla.org/en-US/docs/Web/HTML/Element/input/time
- planned_start: formJson.planned_start === '' ? null : formJson.planned_start
- })
- .select()
-
- if (error !== null) {
- alert(error.message)
- return
- }
+ const heat = await addNewHeat(
+ formJson.name,
+ formJson.location,
+ formJson.planned_start === '' ? null : formJson.planned_start
+ )
const sortedBoard = leaderboard.sort(rankByHeat(rankingComp))
for (let i = 0; i < formJson.size && i < sortedBoard.length; i++ ) {
// add top N athletes from current leaderboard to new heat
- await supabase
- .from('startlist')
- .insert({ heat: data[0].id, athlete: sortedBoard[i].athlete })
+ try {
+ await addAthleteToHeat(sortedBoard[i].athlete, data[0].id)
+ } catch (error) {
+ console.error(error)
+ }
}
// clear values in selects to refresh list of heats
@@ -233,8 +279,12 @@ function Leaderboard({session}) {
setLoading(true)
// load initial list of heats
- const heatList = await supabase.from('heats').select()
- setHeats(heatList.data)
+ const res = await fetch(`${api_uri}:${api_port}/v1/leaderboard/allHeats`)
+ const { data, error } = await res.json()
+ if (error) {
+ console.error(error)
+ }
+ setHeats(data)
setLoading(false)
})();
}, []);
@@ -250,30 +300,25 @@ function Leaderboard({session}) {
})();
}, [heatSelection]);
- useEffect(() => {
- // subscribe to scoring from judges and
- const channel = supabase.channel('scores')
- channel.on(
- 'postgres_changes',
- {
- event: '*',
- table: 'scores',
- },
- async (payload) => {
- setLoading(true)
-
- // todo: reload only required scores
- const scoreSummary = await getScoreSummary(heatSelection.map(h => h.value))
- setLeaderboard(scoreSummary)
- setLoading(false)
- }
- ).subscribe()
-
- // remove subscription
- return function cleanup() {
- supabase.removeChannel(channel)
- }
- }, [heatSelection]);
+// useEffect(() => {
+// (async() => {
+// // subscribe to scoring from judges and
+// const scores = await socket.send(JSON.stringify({
+// method: "getScores",
+// }))
+// console.log("DEBUG: Scores", scores)
+// // todo: reload only required scores
+// const scoreSummary = await getScoreSummary(heatSelection.map(h => h.value))
+// setLeaderboard(scoreSummary)
+// setLoading(false)
+//
+// // remove subscription
+// return function cleanup() {
+// // todo: remove subscription on API server
+// //supabase.removeChannel(channel)
+// }
+// })();
+// }, [heatSelection]);
return (
<div>
diff --git a/src/frontend/Score.jsx b/src/frontend/Score.jsx
@@ -1,17 +1,44 @@
import { lazy, useEffect, useState } from 'react'
import { getStartlistForHeats } from './Leaderboard'
+const Auth = lazy(() => import('./Auth'))
import Select from 'react-select'
-import { supabase } from './supabaseClient'
-const Auth = lazy(() => import('./Auth'))
+const api_uri = import.meta.env.VITE_API_URI ? import.meta.env.VITE_API_URI: 'http://127.0.0.1'
+const api_port = import.meta.env.VITE_API_PORT ? import.meta.env.VITE_API_PORT: '8000'
+
+async function getScore(heatId, athleteId, userId) {
+ const res = await fetch(`${api_uri}:${api_port}/v1/leaderboard/getScore`, {
+ method: 'POST',
+ headers: {'Content-Type': 'application/json'},
+ body: JSON.stringify({
+ "heat": heatIds,
+ "ahtlete": athleteId,
+ "judge": userId,
+ }),
+ })
+ const { data, error } = await res.json()
+ if (error) {
+ throw error
+ }
+ return data
+}
async function updateScore(score, heatId, athleteId, userId) {
- await supabase.from('scores').upsert({
- score: score,
- heat: heatId,
- athlete: athleteId,
- judge: userId
+ const res = await fetch(`${api_uri}:${api_port}/v1/leaderboard/setScore`, {
+ method: 'POST',
+ headers: {'Content-Type': 'application/json'},
+ body: JSON.stringify({
+ "score": score,
+ "heat": heatIds,
+ "ahtlete": athleteId,
+ "judge": userId,
+ }),
})
+ const { data, error } = await res.json()
+ if (error) {
+ throw error
+ }
+ return data
}
function ScoringForm({session}) {
@@ -33,17 +60,22 @@ function ScoringForm({session}) {
useEffect(() => {
(async () => {
setLoading(true)
- const heatList = await supabase.from('heats').select()
- setHeats(heatList.data)
+ const res = await fetch(`${api_uri}:${api_port}/v1/leaderboard/allHeats`)
+ const { data, error } = await res.json()
+ if (error) {
+ console.error(error)
+ }
+ setHeats(data)
const startlist = await getStartlistForHeats([heatSelection.value])
+ console.log(startlist.distinct_startlist)
if (startlist.error) {
setLoading(false)
return
}
- setAthleteOpts(startlist.data.map(s => {
+ setAthleteOpts(startlist.map(s => {
return {
value: s.athlete,
label: s.nr + " " + s.firstname + " " + (s.lastname ? s.lastname : "")
@@ -56,10 +88,11 @@ function ScoringForm({session}) {
}
// check if existing score for heat and athlete exists
- const currentScore = await supabase.from('scores').select()
- .eq('heat', heatSelection.value)
- .eq('athlete', athleteSelection.value)
- .eq('judge', session.id)
+ const currentScore = await getScore(
+ heatSelection.value,
+ athleteSelection.value,
+ session.id
+ )
if (score === 0 && currentScore.data?.length > 0) {
// fallback to current score when no new scoring took place
diff --git a/src/frontend/Startlist.jsx b/src/frontend/Startlist.jsx
@@ -1,20 +1,20 @@
import { lazy, useEffect, useState } from 'react'
import { useParams } from 'react-router-dom'
+const Auth = lazy(() => import('./Auth'))
+import { addAthleteToHeat } from './Leaderboard'
import Select from 'react-select'
-import { supabase } from './supabaseClient'
-const Auth = lazy(() => import('./Auth'))
+const api_uri = import.meta.env.VITE_API_URI ? import.meta.env.VITE_API_URI: 'http://127.0.0.1'
+const api_port = import.meta.env.VITE_API_PORT ? import.meta.env.VITE_API_PORT: '8000'
-async function addAthleteToHeat(e, selectedAthlete, heatId) {
+async function addtoHeat(e, athlete, heatId) {
e.preventDefault()
-
- const { error } = await supabase.from('startlist').upsert({
- athlete: selectedAthlete.value,
- heat: heatId
- })
-
- if (error === null)
+ try {
+ await addAthleteToHeat(athlete.value, heatId)
window.location.reload()
+ } catch(error) {
+ console.error(error)
+ }
}
async function removeAthleteFromHeat(e, startlistId, athleteFirstName, athleteLastName, heatName) {
@@ -22,14 +22,51 @@ async function removeAthleteFromHeat(e, startlistId, athleteFirstName, athleteLa
const athleteName = athleteFirstName + (athleteLastName ? ' ' + athleteLastName : '')
if (window.confirm('Do you really want to remove athlete "' + athleteName + '" from heat "' + heatName + '"?')) {
- await supabase
- .from('startlist')
- .delete()
- .eq('id', startlistId)
+ const res = await fetch(`${api_uri}:${api_port}/v1/leaderboard/removeAthleteFromHeat`, {
+ method: 'POST',
+ headers: {'Content-Type': 'application/json'},
+ body: JSON.stringify({
+ "startlist_id": startlistId,
+ }),
+ })
+ const { data, error } = await res.json()
+ if (error) {
+ console.error(error)
+ }
window.location.reload()
}
}
+async function startlistWithAthletes(heatId) {
+ const res = await fetch(`${api_uri}:${api_port}/v1/leaderboard/startlistWithAthletes`, {
+ method: 'POST',
+ headers: {'Content-Type': 'application/json'},
+ body: JSON.stringify({
+ "heat_id": heatId,
+ }),
+ })
+ const { data, error } = await res.json()
+ if (error) {
+ throw error
+ }
+ return data
+}
+
+async function getHeat(heatId) {
+ const res = await fetch(`${api_uri}:${api_port}/v1/leaderboard/getHeat`, {
+ method: 'POST',
+ headers: {'Content-Type': 'application/json'},
+ body: JSON.stringify({
+ "heat_id": heatId,
+ }),
+ })
+ const { data, error } = await res.json()
+ if (error) {
+ throw error
+ }
+ return data
+}
+
function StartlistForm({heatId}) {
const [heatName, setheatName] = useState("")
const [heatLocation, setheatLocation] = useState("")
@@ -44,40 +81,24 @@ function StartlistForm({heatId}) {
(async () => {
setLoading(true)
- const startlistData = await supabase
- .from('startlist')
- .select(`
- id,
- heat,
- athlete (
- id,
- nr,
- firstname,
- lastname,
- birthday,
- school
- )
- `)
- .eq('heat', heatId)
-
- if (startlistData.error === null)
- setStartlist(startlistData.data)
+ try {
+ const startlist = await startlistWithAthletes(heatId)
+ setStartlist(startlist)
- const heatData = await supabase
- .from('heats')
- .select('name, location, planned_start')
- .eq('id', heatId)
- .single()
-
- if (heatData.error === null) {
- setheatName(heatData.data.name)
- setheatLocation(heatData.data.location)
- setheatStart(heatData.data.planned_start)
+ const heat = await getHeat(heatId)
+ setheatName(heat.name)
+ setheatLocation(heat.location)
+ setheatStart(heat.planned_start)
+ } catch (error) {
+ console.error(error)
}
- const athleteList = await supabase.from('athletes').select()
- if (athleteList.error === null) {
- let options = athleteList.data.map(a => {
+ const res = await fetch(`${api_uri}:${api_port}/v1/leaderboard/allAthletes`)
+ const { data, error } = await res.json()
+ if (error) {
+ console.error(error)
+ } else {
+ let options = data.map(a => {
return {
value: a.id,
label: a.nr + " " + a.firstname + " " + (a.lastname ? a.lastname : "")
@@ -85,7 +106,6 @@ function StartlistForm({heatId}) {
})
setAthleteOpts(options)
}
-
setLoading(false)
})();
}, [heatId])
@@ -113,17 +133,17 @@ function StartlistForm({heatId}) {
</thead>
<tbody>
{startlist.map(i => (
- <tr key={i.id}>
- <td data-title='Start Nr.' className='right'>{i.athlete.nr}</td>
- <td data-title='Firstname'>{i.athlete.firstname}</td>
- <td data-title='Lastname'>{i.athlete.lastname}</td>
- <td data-title='Birthday'>{i.athlete.birthday}</td>
- <td data-title='School'>{i.athlete.school}</td>
+ <tr key={i.startlist_id}>
+ <td data-title='Start Nr.' className='right'>{i.nr}</td>
+ <td data-title='Firstname'>{i.firstname}</td>
+ <td data-title='Lastname'>{i.lastname}</td>
+ <td data-title='Birthday'>{i.birthday}</td>
+ <td data-title='School'>{i.school}</td>
<td><button onClick={e => removeAthleteFromHeat(
e,
- i.id,
- i.athlete.firstname,
- i.athlete.lastname,
+ i.startlist_id,
+ i.firstname,
+ i.lastname,
heatName
)}>– del</button></td>
</tr>
@@ -136,7 +156,7 @@ function StartlistForm({heatId}) {
/>
</td>
<td>
- <button onClick={(e) => addAthleteToHeat(e, selectedAthlete, heatId)}>+ add</button>
+ <button onClick={(e) => addtoHeat(e, selectedAthlete, heatId)}>+ add</button>
</td>
</tr>
</tbody>
diff --git a/src/frontend/supabaseClient.js b/src/frontend/supabaseClient.js
@@ -1,6 +0,0 @@
-import { createClient } from '@supabase/supabase-js'
-
-const supabaseUrl = import.meta.env.VITE_APP_SUPABASE_URL
-const supabaseKey = import.meta.env.VITE_APP_SUPABASE_KEY
-
-export const supabase = createClient(supabaseUrl, supabaseKey)
diff --git a/src/frontend/utils.js b/src/frontend/utils.js
@@ -1,12 +1,14 @@
-import { supabase } from './supabaseClient'
+const api_uri = import.meta.env.VITE_API_URI ? import.meta.env.VITE_API_URI: 'http://127.0.0.1'
+const api_port = import.meta.env.VITE_API_PORT ? import.meta.env.VITE_API_PORT: '8000'
export const exportHeatsToCSV = async function(e) {
e.preventDefault()
- const { data, error } = await supabase
- .from('heats')
- .select('created_at,name,location,planned_start')
- .csv()
+ const res = await fetch(`${api_uri}:${api_port}/v1/leaderboard/exportHeatsToCSV`)
+ const { data, error } = await res.text()
+ if (error) {
+ console.error(error)
+ }
if (error !== null) {
alert(error.message)
@@ -22,10 +24,11 @@ export const exportHeatsToCSV = async function(e) {
export const exportAthletesToCSV = async function(e) {
e.preventDefault()
- const { data, error } = await supabase
- .from('athletes')
- .select('created_at,nr,firstname,lastname,birthday,school')
- .csv()
+ const res = await fetch(`${api_uri}:${api_port}/v1/leaderboard/exportAthletesToCSV`)
+ const { data, error } = await res.text()
+ if (error) {
+ console.error(error)
+ }
if (error !== null) {
alert(error.message)
