commit c7e44fcae0065fb38b68fc3faae5d8c6e7789de8
parent 97e3e66db2986079b2a5f309f854585b85349cfc
Author: Andreas Gruhler <andreas.gruhler@adfinis.com>
Date: Sun, 15 Sep 2024 22:31:03 +0200
feat(magic): adapt db schema
The Supabase user uuid is migrated to a normal bigint id starting at 1.
The (Supabase) user table is deprecated and we only use 1 table `judges` for now
to keep it very simple (everyone that authenticates to the admin section is a
"judge").
Diffstat:
| M | schema/athletes.sql | | | 96 | ------------------------------------------------------------------------------- |
| M | schema/heats.sql | | | 96 | ------------------------------------------------------------------------------- |
| M | schema/judges.sql | | | 81 | ++++++++++++------------------------------------------------------------------- |
| M | schema/scores.sql | | | 113 | +------------------------------------------------------------------------------ |
| M | schema/startlist.sql | | | 106 | ------------------------------------------------------------------------------- |
5 files changed, 13 insertions(+), 479 deletions(-)
diff --git a/schema/athletes.sql b/schema/athletes.sql
@@ -1,29 +1,3 @@
---
--- PostgreSQL database dump
---
-
--- Dumped from database version 15.1
--- Dumped by pg_dump version 15.3
-
-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: athletes; Type: TABLE; Schema: public; Owner: postgres
---
-
CREATE TABLE public.athletes (
id bigint NOT NULL,
created_at timestamp with time zone DEFAULT now(),
@@ -35,20 +9,6 @@ CREATE TABLE public.athletes (
CONSTRAINT athletes_firstname_check CHECK ((length(firstname) > 0))
);
-
-ALTER TABLE public.athletes OWNER TO postgres;
-
---
--- Name: TABLE athletes; Type: COMMENT; Schema: public; Owner: postgres
---
-
-COMMENT ON TABLE public.athletes IS 'Startlist with athletes';
-
-
---
--- Name: athletes_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
---
-
ALTER TABLE public.athletes ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (
SEQUENCE NAME public.athletes_id_seq
START WITH 1
@@ -58,61 +18,5 @@ ALTER TABLE public.athletes ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY
CACHE 1
);
-
---
--- Name: athletes athletes_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
---
-
ALTER TABLE ONLY public.athletes
ADD CONSTRAINT athletes_pkey PRIMARY KEY (id);
-
-
---
--- Name: athletes Enable delete for authenticated users only; Type: POLICY; Schema: public; Owner: postgres
---
-
-CREATE POLICY "Enable delete for authenticated users only" ON public.athletes FOR DELETE TO authenticated USING (true);
-
-
---
--- Name: athletes Enable insert for authenticated users only; Type: POLICY; Schema: public; Owner: postgres
---
-
-CREATE POLICY "Enable insert for authenticated users only" ON public.athletes FOR INSERT TO authenticated WITH CHECK (true);
-
-
---
--- Name: athletes Enable read access for all users; Type: POLICY; Schema: public; Owner: postgres
---
-
-CREATE POLICY "Enable read access for all users" ON public.athletes FOR SELECT USING (true);
-
-
---
--- Name: athletes; Type: ROW SECURITY; Schema: public; Owner: postgres
---
-
-ALTER TABLE public.athletes ENABLE ROW LEVEL SECURITY;
-
---
--- Name: TABLE athletes; Type: ACL; Schema: public; Owner: postgres
---
-
-GRANT ALL ON TABLE public.athletes TO anon;
-GRANT ALL ON TABLE public.athletes TO authenticated;
-GRANT ALL ON TABLE public.athletes TO service_role;
-
-
---
--- Name: SEQUENCE athletes_id_seq; Type: ACL; Schema: public; Owner: postgres
---
-
-GRANT ALL ON SEQUENCE public.athletes_id_seq TO anon;
-GRANT ALL ON SEQUENCE public.athletes_id_seq TO authenticated;
-GRANT ALL ON SEQUENCE public.athletes_id_seq TO service_role;
-
-
---
--- PostgreSQL database dump complete
---
-
diff --git a/schema/heats.sql b/schema/heats.sql
@@ -1,29 +1,3 @@
---
--- PostgreSQL database dump
---
-
--- Dumped from database version 15.1
--- Dumped by pg_dump version 15.3
-
-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: heats; Type: TABLE; Schema: public; Owner: postgres
---
-
CREATE TABLE public.heats (
id bigint NOT NULL,
created_at timestamp with time zone DEFAULT now(),
@@ -33,20 +7,6 @@ CREATE TABLE public.heats (
CONSTRAINT heats_name_check CHECK ((length(name) > 0))
);
-
-ALTER TABLE public.heats OWNER TO postgres;
-
---
--- Name: TABLE heats; Type: COMMENT; Schema: public; Owner: postgres
---
-
-COMMENT ON TABLE public.heats IS 'List of heats or contest runs';
-
-
---
--- Name: heats_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
---
-
ALTER TABLE public.heats ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (
SEQUENCE NAME public.heats_id_seq
START WITH 1
@@ -56,61 +16,5 @@ ALTER TABLE public.heats ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (
CACHE 1
);
-
---
--- Name: heats heats_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
---
-
ALTER TABLE ONLY public.heats
ADD CONSTRAINT heats_pkey PRIMARY KEY (id);
-
-
---
--- Name: heats Enable delete for authenticated users only; Type: POLICY; Schema: public; Owner: postgres
---
-
-CREATE POLICY "Enable delete for authenticated users only" ON public.heats FOR DELETE TO authenticated USING (true);
-
-
---
--- Name: heats Enable insert for authenticated users only; Type: POLICY; Schema: public; Owner: postgres
---
-
-CREATE POLICY "Enable insert for authenticated users only" ON public.heats FOR INSERT TO authenticated WITH CHECK (true);
-
-
---
--- Name: heats Enable read access for all users; Type: POLICY; Schema: public; Owner: postgres
---
-
-CREATE POLICY "Enable read access for all users" ON public.heats FOR SELECT USING (true);
-
-
---
--- Name: heats; Type: ROW SECURITY; Schema: public; Owner: postgres
---
-
-ALTER TABLE public.heats ENABLE ROW LEVEL SECURITY;
-
---
--- Name: TABLE heats; Type: ACL; Schema: public; Owner: postgres
---
-
-GRANT ALL ON TABLE public.heats TO anon;
-GRANT ALL ON TABLE public.heats TO authenticated;
-GRANT ALL ON TABLE public.heats TO service_role;
-
-
---
--- Name: SEQUENCE heats_id_seq; Type: ACL; Schema: public; Owner: postgres
---
-
-GRANT ALL ON SEQUENCE public.heats_id_seq TO anon;
-GRANT ALL ON SEQUENCE public.heats_id_seq TO authenticated;
-GRANT ALL ON SEQUENCE public.heats_id_seq TO service_role;
-
-
---
--- PostgreSQL database dump complete
---
-
diff --git a/schema/judges.sql b/schema/judges.sql
@@ -1,78 +1,21 @@
---
--- PostgreSQL database dump
---
-
--- Dumped from database version 15.1
--- Dumped by pg_dump version 15.3
-
-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: judges; Type: TABLE; Schema: public; Owner: postgres
---
-
CREATE TABLE public.judges (
- id uuid NOT NULL,
+ id bigint NOT NULL,
created_at timestamp with time zone DEFAULT now(),
+ email text,
+ token text,
+ expires_at timestamp with time zone DEFAULT now(),
firstname text,
lastname text
);
-
-ALTER TABLE public.judges OWNER TO postgres;
-
---
--- Name: TABLE judges; Type: COMMENT; Schema: public; Owner: postgres
---
-
-COMMENT ON TABLE public.judges IS 'List of judges';
-
-
---
--- Name: judges judges_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
---
+ALTER TABLE public.judges ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (
+ SEQUENCE NAME public.judges_id_seq
+ START WITH 1
+ INCREMENT BY 1
+ NO MINVALUE
+ NO MAXVALUE
+ CACHE 1
+);
ALTER TABLE ONLY public.judges
ADD CONSTRAINT judges_pkey PRIMARY KEY (id);
-
-
---
--- Name: judges judges_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
---
-
-ALTER TABLE ONLY public.judges
- ADD CONSTRAINT judges_id_fkey FOREIGN KEY (id) REFERENCES auth.users(id) ON DELETE CASCADE;
-
-
---
--- Name: judges; Type: ROW SECURITY; Schema: public; Owner: postgres
---
-
-ALTER TABLE public.judges ENABLE ROW LEVEL SECURITY;
-
---
--- Name: TABLE judges; Type: ACL; Schema: public; Owner: postgres
---
-
-GRANT ALL ON TABLE public.judges TO anon;
-GRANT ALL ON TABLE public.judges TO authenticated;
-GRANT ALL ON TABLE public.judges TO service_role;
-
-
---
--- PostgreSQL database dump complete
---
-
diff --git a/schema/scores.sql b/schema/scores.sql
@@ -1,52 +1,12 @@
---
--- PostgreSQL database dump
---
-
--- Dumped from database version 15.1
--- Dumped by pg_dump version 15.3
-
-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,
+ judge bigint 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
@@ -56,85 +16,14 @@ ALTER TABLE public.scores ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (
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) ON DELETE CASCADE;
-
-
---
--- 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/schema/startlist.sql b/schema/startlist.sql
@@ -1,29 +1,3 @@
---
--- PostgreSQL database dump
---
-
--- Dumped from database version 15.1
--- Dumped by pg_dump version 15.3
-
-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: startlist; Type: TABLE; Schema: public; Owner: postgres
---
-
CREATE TABLE public.startlist (
id bigint NOT NULL,
created_at timestamp with time zone DEFAULT now(),
@@ -31,20 +5,6 @@ CREATE TABLE public.startlist (
athlete bigint NOT NULL
);
-
-ALTER TABLE public.startlist OWNER TO postgres;
-
---
--- Name: TABLE startlist; Type: COMMENT; Schema: public; Owner: postgres
---
-
-COMMENT ON TABLE public.startlist IS 'List of athletes for a given heat or run';
-
-
---
--- Name: startlist_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
---
-
ALTER TABLE public.startlist ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (
SEQUENCE NAME public.startlist_id_seq
START WITH 1
@@ -54,77 +14,11 @@ ALTER TABLE public.startlist ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTIT
CACHE 1
);
-
---
--- Name: startlist startlist_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
---
-
ALTER TABLE ONLY public.startlist
ADD CONSTRAINT startlist_pkey PRIMARY KEY (heat, athlete);
-
---
--- Name: startlist startlist_athlete_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
---
-
ALTER TABLE ONLY public.startlist
ADD CONSTRAINT startlist_athlete_fkey FOREIGN KEY (athlete) REFERENCES public.athletes(id) ON DELETE CASCADE;
-
---
--- Name: startlist startlist_heat_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
---
-
ALTER TABLE ONLY public.startlist
ADD CONSTRAINT startlist_heat_fkey FOREIGN KEY (heat) REFERENCES public.heats(id) ON DELETE CASCADE;
-
-
---
--- Name: startlist Enable delete for authenticated users only; Type: POLICY; Schema: public; Owner: postgres
---
-
-CREATE POLICY "Enable delete for authenticated users only" ON public.startlist FOR DELETE TO authenticated USING (true);
-
-
---
--- Name: startlist Enable insert for authenticated users only; Type: POLICY; Schema: public; Owner: postgres
---
-
-CREATE POLICY "Enable insert for authenticated users only" ON public.startlist FOR INSERT TO authenticated WITH CHECK (true);
-
-
---
--- Name: startlist Enable read access for all users; Type: POLICY; Schema: public; Owner: postgres
---
-
-CREATE POLICY "Enable read access for all users" ON public.startlist FOR SELECT USING (true);
-
-
---
--- Name: startlist; Type: ROW SECURITY; Schema: public; Owner: postgres
---
-
-ALTER TABLE public.startlist ENABLE ROW LEVEL SECURITY;
-
---
--- Name: TABLE startlist; Type: ACL; Schema: public; Owner: postgres
---
-
-GRANT ALL ON TABLE public.startlist TO anon;
-GRANT ALL ON TABLE public.startlist TO authenticated;
-GRANT ALL ON TABLE public.startlist TO service_role;
-
-
---
--- Name: SEQUENCE startlist_id_seq; Type: ACL; Schema: public; Owner: postgres
---
-
-GRANT ALL ON SEQUENCE public.startlist_id_seq TO anon;
-GRANT ALL ON SEQUENCE public.startlist_id_seq TO authenticated;
-GRANT ALL ON SEQUENCE public.startlist_id_seq TO service_role;
-
-
---
--- PostgreSQL database dump complete
---
-
