commit 76915c567445254e11c4629c6850613abc32c6c0
parent fa695241b1f500dc04f46a40747529c073e0dc9f
Author: Andreas Gruhler <andreas.gruhler@adfinis.com>
Date: Fri, 20 Sep 2024 22:58:33 +0200
feat(dev): initdb
Diffstat:
8 files changed, 49 insertions(+), 28 deletions(-)
diff --git a/dev/README.md b/dev/README.md
@@ -12,9 +12,5 @@ To initialize the database tables, source the sql files from `../schema`.
This can be done in Adminer or with `psql`:
```
# use `example` default password to apply
-psql -h 127.0.0.1 -U postgres -p 5432 < ../schema/athletes.sql
-psql -h 127.0.0.1 -U postgres -p 5432 < ../schema/heats.sql
-psql -h 127.0.0.1 -U postgres -p 5432 < ../schema/judges.sql
-psql -h 127.0.0.1 -U postgres -p 5432 < ../schema/scores.sql
-psql -h 127.0.0.1 -U postgres -p 5432 < ../schema/startlist.sql
+cat ../schema/*.sql | psql -h 127.0.0.1 -U postgres -p 5432
```
diff --git a/dev/docker-compose.yml b/dev/docker-compose.yml
@@ -1,17 +1,23 @@
# Use postgres/example user/password credentials
-# https://hub.docker.com/_/postgres
+# - https://hub.docker.com/_/postgres
+# - https://github.com/docker-library/docs/blob/master/postgres/README.md
version: '3.9'
services:
db:
+ container_name: postgresql
image: docker.io/postgres:16-alpine
shm_size: 128mb
environment:
POSTGRES_PASSWORD: example
ports:
- 5432:5432
+ volumes:
+ - ./data/pgdata:/var/lib/postgresql/data
+ - ../schema:/docker-entrypoint-initdb.d
adminer:
+ container_name: adminer
image: docker.io/adminer:latest
restart: always
ports:
diff --git a/schema/heats.sql b/schema/01-heats.sql
diff --git a/schema/judges.sql b/schema/02-judges.sql
diff --git a/schema/scores.sql b/schema/03-scores.sql
diff --git a/schema/startlist.sql b/schema/04-startlist.sql
diff --git a/schema/99-init-db.sql b/schema/99-init-db.sql
@@ -0,0 +1,41 @@
+-- Summarize all scores of a heat
+create or replace view score_summary
+ with (security_invoker=on)
+ 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[])
+returns table(id bigint, athlete bigint, nr bigint, firstname text, lastname text,
+birthday date, school text)
+language plpgsql
+set search_path = ''
+as $$
+begin
+ return query
+ select distinct on (a.id) s.id, a.id, a.nr, a.firstname, a.lastname, a.birthday, a.school
+ from public.startlist as s
+ join public.athletes as a on s.athlete = a.id
+ where s.heat = any (heat_ids);
+end;
+$$;
+
+-- Enable PostgreSQL publication:
+-- * https://www.postgresql.org/docs/current/logical-replication-publication.html
+-- * https://github.com/porsager/postgres#realtime-subscribe
+-- Set wal_level to "logical" before creating subscriptions
+alter system set wal_level = logical;
+
+begin;
+-- Remove the myheats_realtime publication
+drop publication if exists myheats_realtime;
+
+-- Re-create the myheats_realtime publication with no tables
+create publication myheats_realtime;
+commit;
+
+-- Add a table to the publication
+alter publication myheats_realtime add table scores;
diff --git a/schema/athletes.sql b/schema/athletes.sql
@@ -1,22 +0,0 @@
-CREATE TABLE public.athletes (
- id bigint NOT NULL,
- created_at timestamp with time zone DEFAULT now(),
- nr bigint,
- firstname text NOT NULL,
- lastname text,
- birthday date,
- school text,
- CONSTRAINT athletes_firstname_check CHECK ((length(firstname) > 0))
-);
-
-ALTER TABLE public.athletes ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (
- SEQUENCE NAME public.athletes_id_seq
- START WITH 1
- INCREMENT BY 1
- NO MINVALUE
- NO MAXVALUE
- CACHE 1
-);
-
-ALTER TABLE ONLY public.athletes
- ADD CONSTRAINT athletes_pkey PRIMARY KEY (id);
