myheats

Live heats, scoring and leaderboard for sport events
git clone https://git.in0rdr.ch/myheats.git
Log | Files | Refs | Pull requests | README | LICENSE

commit 72d2e335f0f0fc7dd60545e6026ac1fccc377433
parent 3827101ca35316504f31a8770643852546abf714
Author: Andreas Gruhler <andreas.gruhler@adfinis.com>
Date:   Fri, 14 Apr 2023 00:12:09 +0200

feat: replace rating w/ scoring

Diffstat:
MREADME.md | 24++++++++++++------------
Dschema/ratings.sql | 140-------------------------------------------------------------------------------
Aschema/scores.sql | 140+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Msrc/Leaderboard.js | 40++++++++++++++++++++--------------------
Msrc/Score.js | 32++++++++++++++++----------------
5 files changed, 188 insertions(+), 188 deletions(-)

diff --git a/README.md b/README.md @@ -21,18 +21,18 @@ The Supabase schema is stored in the `schema` folder and can be created using pl To update the schema from the current database, use (example for table `startlist`): ```bash -pg_dump -h db.aaxkgqazjhwumoljibld.supabase.co -U postgres -t 'public.startlist' --schema-only > schema/startlist.sql +pg_dump -h db.youruniquedb.supabase.co -U postgres -t 'public.startlist' --schema-only > schema/startlist.sql ``` Additionally, following views and functions are required: ```sql --- Summarize all ratings of a heat -create or replace view rating_summary as - select a.id as athlete_id, r.heat as heat_id, SUM(r.rating) as rating_summary - from ratings r - join athletes as a on a.id = r.athlete - group by a.id, r.heat +-- Summarize all scores of a heat +create or replace view score_summary as + select a.id as athlete_id, s.heat as heat_id, SUM(s.score) as score_summary + from scores s + join athletes as a on a.id = s.athlete + group by a.id, s.heat -- Create distinct set of athletes (startlist) from a set of heats create or replace function distinct_startlist(heat_ids numeric[]) @@ -72,7 +72,7 @@ create trigger on_auth_user_created for each row execute procedure public.handle_new_judge(); ``` -Lastly, enable [PostgreSQL publication](https://www.postgresql.org/docs/current/logical-replication-publication.html) for the [Supabase realtime feature for Postgres changes](https://supabase.com/docs/guides/realtime/extensions/postgres-changes): +Lastly, enable [PostgreSQL publication](https://www.postgresql.org/docs/current/logical-replication-publication.html) for the [Supabase realtime feature on Postgres changes](https://supabase.com/docs/guides/realtime/extensions/postgres-changes): ```sql begin; @@ -87,7 +87,7 @@ commit; -- add a table to the publication alter - publication supabase_realtime add table ratings; + publication supabase_realtime add table scores; ``` This is required for the leaderboard to automatically update scores when they are created or changed by judges. @@ -111,17 +111,17 @@ Sign in process: Use direct postgres database access: ```bash -psql -h db.aaxkgqazjhwumoljibld.supabase.co -U postgres +psql -h db.youruniquedb.supabase.co -U postgres ``` To import data from local csv: ```sql -\copy public.ratings from 'ratings_rows.csv' DELIMITER ',' CSV HEADER; +\copy public.scores from 'scores_rows.csv' DELIMITER ',' CSV HEADER; ``` To export data to local csv: ```sql -\copy public.ratings to 'ratings_rows.csv' DELIMITER ',' CSV HEADER; +\copy public.scores to 'scores_rows.csv' DELIMITER ',' CSV HEADER; ``` ## TODO diff --git a/schema/ratings.sql b/schema/ratings.sql @@ -1,140 +0,0 @@ --- --- PostgreSQL database dump --- - --- Dumped from database version 15.1 --- Dumped by pg_dump version 15.2 - -SET statement_timeout = 0; -SET lock_timeout = 0; -SET idle_in_transaction_session_timeout = 0; -SET client_encoding = 'UTF8'; -SET standard_conforming_strings = on; -SELECT pg_catalog.set_config('search_path', '', false); -SET check_function_bodies = false; -SET xmloption = content; -SET client_min_messages = warning; -SET row_security = off; - -SET default_tablespace = ''; - -SET default_table_access_method = heap; - --- --- Name: ratings; Type: TABLE; Schema: public; Owner: postgres --- - -CREATE TABLE public.ratings ( - id bigint NOT NULL, - created_at timestamp with time zone DEFAULT now(), - athlete bigint NOT NULL, - judge uuid NOT NULL, - rating double precision, - heat bigint NOT NULL -); - - -ALTER TABLE public.ratings OWNER TO postgres; - --- --- Name: TABLE ratings; Type: COMMENT; Schema: public; Owner: postgres --- - -COMMENT ON TABLE public.ratings IS 'List of ratings'; - - --- --- Name: rating_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres --- - -ALTER TABLE public.ratings ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY ( - SEQUENCE NAME public.rating_id_seq - START WITH 1 - INCREMENT BY 1 - NO MINVALUE - NO MAXVALUE - CACHE 1 -); - - --- --- Name: ratings ratings_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres --- - -ALTER TABLE ONLY public.ratings - ADD CONSTRAINT ratings_pkey PRIMARY KEY (athlete, judge, heat); - - --- --- Name: ratings ratings_athlete_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres --- - -ALTER TABLE ONLY public.ratings - ADD CONSTRAINT ratings_athlete_fkey FOREIGN KEY (athlete) REFERENCES public.athletes(id) ON DELETE CASCADE; - - --- --- Name: ratings ratings_heat_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres --- - -ALTER TABLE ONLY public.ratings - ADD CONSTRAINT ratings_heat_fkey FOREIGN KEY (heat) REFERENCES public.heats(id) ON DELETE CASCADE; - - --- --- Name: ratings ratings_judge_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres --- - -ALTER TABLE ONLY public.ratings - ADD CONSTRAINT ratings_judge_fkey FOREIGN KEY (judge) REFERENCES public.judges(id); - - --- --- Name: ratings Enable insert for authenticated users only; Type: POLICY; Schema: public; Owner: postgres --- - -CREATE POLICY "Enable insert for authenticated users only" ON public.ratings FOR INSERT TO authenticated WITH CHECK (true); - - --- --- Name: ratings Enable judges to update their own ratings; Type: POLICY; Schema: public; Owner: postgres --- - -CREATE POLICY "Enable judges to update their own ratings" ON public.ratings FOR UPDATE USING ((auth.uid() = judge)); - - --- --- Name: ratings Enable read access for all users; Type: POLICY; Schema: public; Owner: postgres --- - -CREATE POLICY "Enable read access for all users" ON public.ratings FOR SELECT USING (true); - - --- --- Name: ratings; Type: ROW SECURITY; Schema: public; Owner: postgres --- - -ALTER TABLE public.ratings ENABLE ROW LEVEL SECURITY; - --- --- Name: TABLE ratings; Type: ACL; Schema: public; Owner: postgres --- - -GRANT ALL ON TABLE public.ratings TO anon; -GRANT ALL ON TABLE public.ratings TO authenticated; -GRANT ALL ON TABLE public.ratings TO service_role; - - --- --- Name: SEQUENCE rating_id_seq; Type: ACL; Schema: public; Owner: postgres --- - -GRANT ALL ON SEQUENCE public.rating_id_seq TO anon; -GRANT ALL ON SEQUENCE public.rating_id_seq TO authenticated; -GRANT ALL ON SEQUENCE public.rating_id_seq TO service_role; - - --- --- PostgreSQL database dump complete --- - diff --git a/schema/scores.sql b/schema/scores.sql @@ -0,0 +1,140 @@ +-- +-- PostgreSQL database dump +-- + +-- Dumped from database version 15.1 +-- Dumped by pg_dump version 15.2 + +SET statement_timeout = 0; +SET lock_timeout = 0; +SET idle_in_transaction_session_timeout = 0; +SET client_encoding = 'UTF8'; +SET standard_conforming_strings = on; +SELECT pg_catalog.set_config('search_path', '', false); +SET check_function_bodies = false; +SET xmloption = content; +SET client_min_messages = warning; +SET row_security = off; + +SET default_tablespace = ''; + +SET default_table_access_method = heap; + +-- +-- Name: scores; Type: TABLE; Schema: public; Owner: postgres +-- + +CREATE TABLE public.scores ( + id bigint NOT NULL, + created_at timestamp with time zone DEFAULT now(), + athlete bigint NOT NULL, + judge uuid NOT NULL, + score double precision, + heat bigint NOT NULL +); + + +ALTER TABLE public.scores OWNER TO postgres; + +-- +-- Name: TABLE scores; Type: COMMENT; Schema: public; Owner: postgres +-- + +COMMENT ON TABLE public.scores IS 'List of scores'; + + +-- +-- Name: score_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres +-- + +ALTER TABLE public.scores ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY ( + SEQUENCE NAME public.score_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1 +); + + +-- +-- Name: scores scores_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY public.scores + ADD CONSTRAINT scores_pkey PRIMARY KEY (athlete, judge, heat); + + +-- +-- Name: scores scores_athlete_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY public.scores + ADD CONSTRAINT scores_athlete_fkey FOREIGN KEY (athlete) REFERENCES public.athletes(id) ON DELETE CASCADE; + + +-- +-- Name: scores scores_heat_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY public.scores + ADD CONSTRAINT scores_heat_fkey FOREIGN KEY (heat) REFERENCES public.heats(id) ON DELETE CASCADE; + + +-- +-- Name: scores scores_judge_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY public.scores + ADD CONSTRAINT scores_judge_fkey FOREIGN KEY (judge) REFERENCES public.judges(id); + + +-- +-- Name: scores Enable insert for authenticated users only; Type: POLICY; Schema: public; Owner: postgres +-- + +CREATE POLICY "Enable insert for authenticated users only" ON public.scores FOR INSERT TO authenticated WITH CHECK (true); + + +-- +-- Name: scores Enable judges to update their own scores; Type: POLICY; Schema: public; Owner: postgres +-- + +CREATE POLICY "Enable judges to update their own scores" ON public.scores FOR UPDATE USING ((auth.uid() = judge)); + + +-- +-- Name: scores Enable read access for all users; Type: POLICY; Schema: public; Owner: postgres +-- + +CREATE POLICY "Enable read access for all users" ON public.scores FOR SELECT USING (true); + + +-- +-- Name: scores; Type: ROW SECURITY; Schema: public; Owner: postgres +-- + +ALTER TABLE public.scores ENABLE ROW LEVEL SECURITY; + +-- +-- Name: TABLE scores; Type: ACL; Schema: public; Owner: postgres +-- + +GRANT ALL ON TABLE public.scores TO anon; +GRANT ALL ON TABLE public.scores TO authenticated; +GRANT ALL ON TABLE public.scores TO service_role; + + +-- +-- Name: SEQUENCE score_id_seq; Type: ACL; Schema: public; Owner: postgres +-- + +GRANT ALL ON SEQUENCE public.score_id_seq TO anon; +GRANT ALL ON SEQUENCE public.score_id_seq TO authenticated; +GRANT ALL ON SEQUENCE public.score_id_seq TO service_role; + + +-- +-- PostgreSQL database dump complete +-- + diff --git a/src/Leaderboard.js b/src/Leaderboard.js @@ -8,19 +8,19 @@ export async function getStartlistForHeats(heatIds) { return supabase.rpc('distinct_startlist', { 'heat_ids': heatIds }) } -async function getRatingForHeatAndAthlete(heatId, athleteId) { - return supabase.from('ratings').select(` +async function getScoreForHeatAndAthlete(heatId, athleteId) { + return supabase.from('scores').select(` id, athlete, judge, - rating + score `) .eq('heat', heatId) .eq('athlete', athleteId) } -async function getRatingSummary(heatIds) { - const startListWithRatings = [] +async function getScoreSummary(heatIds) { + const startListWithScores = [] const startlist = await getStartlistForHeats(heatIds) @@ -33,9 +33,9 @@ async function getRatingSummary(heatIds) { i.heats = [] for (const h of heatIds) { - const ratings = await getRatingForHeatAndAthlete(h, i.athlete) + const scores = await getScoreForHeatAndAthlete(h, i.athlete) - const summary = await supabase.from('rating_summary').select('rating_summary') + const summary = await supabase.from('score_summary').select('score_summary') .eq('heat_id', h) .eq('athlete_id', i.athlete) @@ -43,8 +43,8 @@ async function getRatingSummary(heatIds) { // add heat results of athlete to startlist entry i.heats.push({ heatId: h, - ratings: ratings.data, - summary: summary.data.length > 0 ? summary.data[0].rating_summary : 0 + scores: scores.data, + summary: summary.data.length > 0 ? summary.data[0].score_summary : 0 }) } // else don't push any heats (fail silently) @@ -57,10 +57,10 @@ async function getRatingSummary(heatIds) { i.sum = i.heats.map(h => h.summary).reduce((a, b) => a + b, 0).toFixed(1) } - startListWithRatings.push(i) + startListWithScores.push(i) } - return startListWithRatings + return startListWithScores } function rankByHeat(rankingComp) { @@ -273,27 +273,27 @@ function Leaderboard({session}) { setLoading(true) // reload entire leaderboard when heat selection is changed - const ratingSummary = await getRatingSummary(heatSelection.map(h => h.value)) - setLeaderboard(ratingSummary) + const scoreSummary = await getScoreSummary(heatSelection.map(h => h.value)) + setLeaderboard(scoreSummary) setLoading(false) })(); }, [heatSelection]); useEffect(() => { - // subscribe to ratings from judges and - const channel = supabase.channel('ratings') + // subscribe to scoring from judges and + const channel = supabase.channel('scores') channel.on( 'postgres_changes', { event: '*', - table: 'ratings', + table: 'scores', }, async (payload) => { setLoading(true) - // todo: reload only required ratings - const ratingSummary = await getRatingSummary(heatSelection.map(h => h.value)) - setLeaderboard(ratingSummary) + // todo: reload only required scores + const scoreSummary = await getScoreSummary(heatSelection.map(h => h.value)) + setLeaderboard(scoreSummary) setLoading(false) } ).subscribe() @@ -366,7 +366,7 @@ function Leaderboard({session}) { <td>{i.school}</td> {heatSelection.map(h => ( <Fragment key={h.value}> - <td className='right'>{i.heats.find(heat => heat.heatId === h.value)?.ratings?.map(r => r.rating).join(" / ")}</td> + <td className='right'>{i.heats.find(heat => heat.heatId === h.value)?.scores?.map(s => s.score).join(" / ")}</td> <td className='right'>{i.heats.find(heat => heat.heatId === h.value)?.summary}</td> </Fragment> ))} diff --git a/src/Score.js b/src/Score.js @@ -5,9 +5,9 @@ import { supabase } from './supabaseClient' const Auth = lazy(() => import('./Auth')) -async function updateRating(rating, heatId, athleteId, userId) { - await supabase.from('ratings').upsert({ - rating: rating, +async function updateScore(score, heatId, athleteId, userId) { + await supabase.from('scores').upsert({ + score: score, heat: heatId, athlete: athleteId, judge: userId @@ -19,7 +19,7 @@ function ScoringForm({session}) { const [heatSelection, setHeatSelection] = useState(0) const [athleteOpts, setAthleteOpts] = useState([]) const [athleteSelection, setAthleteSelection] = useState(0) - const [rating, setRating] = useState(0) + const [score, setScore] = useState(0) // add options to select or rank by heat const heatOpts = heats.map(h => { @@ -49,24 +49,24 @@ function ScoringForm({session}) { if (heatSelection.value === undefined || athleteSelection.value === undefined) return - // check if existing rating for heat and athlete exists - const currentRating = await supabase.from('ratings').select() + // 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.user.id) - if (rating === 0 && currentRating.data?.length > 0) { - // fallback to current rating when no rating was given - setRating(currentRating.data[0].rating) + 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 rating - updateRating(rating, + // store new score + updateScore(score, heatSelection.value, athleteSelection.value, session.user.id) } })(); - }, [heatSelection, athleteSelection, session.user.id, rating]); + }, [heatSelection, athleteSelection, session.user.id, score]); return ( <div> @@ -74,19 +74,19 @@ function ScoringForm({session}) { Heat: <Select options={heatOpts} - onChange={h => { setHeatSelection(h); setRating(0) }} + onChange={h => { setHeatSelection(h); setScore(0) }} /> Athlete: <Select options={athleteOpts} - onChange={a => { setAthleteSelection(a); setRating(0) }} + onChange={a => { setAthleteSelection(a); setScore(0) }} /> Score: <input type="number" size="5" - value={rating} - onChange={(e) => setRating(e.target.value)} + value={score} + onChange={(e) => setScore(e.target.value)} /> </div> )