Last active
February 22, 2023 02:22
-
-
Save jkatz/5c34bf1e401b3376dfe8e627fcd30af3 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- This is based on the code from | |
-- https://raw.githubusercontent.com/CrunchyData/postgres-realtime-demo/main/examples/demo/demo1.sql | |
-- which was originally released under the Apache 2.0 License | |
-- https://github.com/CrunchyData/postgres-realtime-demo/blob/main/LICENSE | |
CREATE TABLE public.room ( | |
id int GENERATED BY DEFAULT AS IDENTITY (INCREMENT 2 START WITH 1) PRIMARY KEY, | |
name text NOT NULL | |
); | |
CREATE TABLE public.availability_rule ( | |
id int GENERATED BY DEFAULT AS IDENTITY (INCREMENT 2 START WITH 1) PRIMARY KEY, | |
room_id int NOT NULL REFERENCES public.room (id) ON DELETE CASCADE, | |
days_of_week int[] NOT NULL, | |
start_time time NOT NULL, | |
end_time time NOT NULL, | |
generate_weeks_into_future int NOT NULL DEFAULT 52 | |
); | |
CREATE TABLE public.availability ( | |
id int GENERATED BY DEFAULT AS IDENTITY (INCREMENT 2 START WITH 1) PRIMARY KEY, | |
room_id int NOT NULL REFERENCES public.room (id) ON DELETE CASCADE, | |
availability_rule_id int NOT NULL REFERENCES public.availability_rule (id) ON DELETE CASCADE, | |
available_date date NOT NULL, | |
available_range tstzrange NOT NULL | |
); | |
CREATE INDEX availability_available_range_gist_idx | |
ON availability | |
USING gist(available_range); | |
CREATE TABLE public.unavailability ( | |
id int GENERATED BY DEFAULT AS IDENTITY (INCREMENT 2 START WITH 1) PRIMARY KEY, | |
room_id int NOT NULL REFERENCES public.room (id) ON DELETE CASCADE, | |
unavailable_date date NOT NULL, | |
unavailable_range tstzrange NOT NULL | |
); | |
CREATE INDEX unavailability_unavailable_range_gist_idx | |
ON unavailability | |
USING gist(unavailable_range); | |
CREATE TABLE public.calendar ( | |
id int GENERATED BY DEFAULT AS IDENTITY (INCREMENT 2 START WITH 1) PRIMARY KEY, | |
room_id int NOT NULL REFERENCES public.room (id) ON DELETE CASCADE, | |
status text NOT NULL, | |
calendar_date date NOT NULL, | |
calendar_range tstzrange NOT NULL | |
); | |
CREATE OR REPLACE FUNCTION public.room_insert() | |
RETURNS trigger | |
AS $$ | |
BEGIN | |
IF TG_OP = 'INSERT' THEN | |
INSERT INTO public.calendar ( | |
room_id, | |
status, | |
calendar_date, | |
calendar_range | |
) | |
SELECT | |
NEW.id, 'closed', calendar_date, tstzrange(calendar_date, calendar_date + '1 day'::interval) | |
FROM generate_series( | |
date_trunc('week', CURRENT_DATE), | |
date_trunc('week', CURRENT_DATE + '52 weeks'::interval), | |
'1 day'::interval | |
) calendar_date; | |
END IF; | |
RETURN NEW; | |
END; | |
$$ LANGUAGE plpgsql; | |
/** | |
* Only need to fire trigger inserting a room as UPDATEs do not affect the timings and DELETEs | |
* are cascaded | |
*/ | |
CREATE TRIGGER room_insert | |
AFTER INSERT ON public.room | |
FOR EACH ROW | |
EXECUTE PROCEDURE public.room_insert(); | |
/** | |
* AVAILABILITY RULE: Ensure that updates to general availability | |
*/ | |
/** Helper: Bulk create availability rules; day_of_week ~ isodow (Mon: 1 - Sat: 7) */ | |
CREATE OR REPLACE FUNCTION public.availability_rule_bulk_insert(availability_rule availability_rule, day_of_week int) | |
RETURNS void | |
LANGUAGE SQL | |
BEGIN ATOMIC | |
INSERT INTO availability ( | |
room_id, | |
availability_rule_id, | |
available_date, | |
available_range | |
) | |
SELECT | |
$1.room_id, | |
$1.id, | |
available_date::date + $2 - 1, | |
tstzrange( | |
/** start of range */ | |
(available_date::date + $2 - 1) + $1.start_time, | |
/** end of range */ | |
/** check if there is a time wraparound, if so, increment by a day */ | |
CASE $1.end_time <= $1.start_time | |
WHEN TRUE THEN (available_date::date + $2) + $1.end_time | |
ELSE (available_date::date + $2 - 1) + $1.end_time | |
END | |
) | |
FROM | |
generate_series( | |
date_trunc('week', CURRENT_DATE), | |
date_trunc('week', CURRENT_DATE) + ($1.generate_weeks_into_future::text || ' weeks')::interval, | |
'1 week'::interval | |
) available_date; | |
END; | |
/** | |
* availability_rule trigger function | |
*/ | |
CREATE OR REPLACE FUNCTION public.availability_rule_manage() | |
RETURNS trigger | |
AS $trigger$ | |
DECLARE | |
day_of_week int; | |
BEGIN | |
IF TG_OP = 'INSERT' THEN | |
/** Loop over the days of the week */ | |
FOREACH day_of_week IN ARRAY NEW.days_of_week | |
LOOP | |
PERFORM public.availability_rule_bulk_insert(NEW, day_of_week); | |
END LOOP; | |
ELSIF TG_OP = 'UPDATE' THEN | |
/** Update is tricky if the days_of_week has changed */ | |
IF OLD.days_of_week IS DISTINCT FROM NEW.days_of_week THEN | |
/** NAIVE: We will delete everything and re-insert */ | |
DELETE FROM public.availability | |
WHERE availability_rule_id = NEW.id; | |
/** insertion */ | |
FOREACH day_of_week IN ARRAY NEW.days_of_week | |
LOOP | |
PERFORM public.availability_rule_bulk_insert(NEW, day_of_week); | |
END LOOP; | |
ELSE | |
/** Otherwise, just modify the start/end time ranges */ | |
UPDATE public.availability | |
SET | |
available_range = tstzrange( | |
/** start of range */ | |
available_date + NEW.start_time, | |
/** end of range */ | |
/** check if there is a time wraparound, if so, increment by a day */ | |
CASE NEW.end_time <= NEW.start_time | |
WHEN TRUE THEN (available_date + 1) + NEW.end_time | |
ELSE available_date + NEW.end_time | |
END | |
) | |
WHERE availability_rule_id = NEW.id; | |
END IF; | |
END IF; | |
RETURN NEW; | |
END; | |
$trigger$ | |
LANGUAGE plpgsql; | |
/** availability_rule trigger only fires on insert or update as DELETE is cascaded */ | |
CREATE TRIGGER availability_rule_insert_or_update | |
AFTER INSERT OR UPDATE ON public.availability_rule | |
FOR EACH ROW | |
EXECUTE PROCEDURE public.availability_rule_manage(); | |
/** AVAILABILITY, UNAVAILABILITY, and CALENDAR */ | |
/** Helper function: generate the available chunks of time within a block of time for a day within a calendar */ | |
CREATE OR REPLACE FUNCTION public.calendar_generate_available(room_id int, calendar_range tstzrange) | |
RETURNS TABLE(status text, calendar_range tstzrange) | |
LANGUAGE SQL STABLE | |
BEGIN ATOMIC | |
SELECT * | |
FROM | |
( | |
SELECT | |
'closed', | |
unnest(tstzmultirange($2) - COALESCE(range_agg(availability.available_range), tstzmultirange())) AS calendar_range | |
FROM public.room | |
LEFT OUTER JOIN public.availability ON | |
availability.room_id = room.id AND | |
availability.available_range && $2 | |
WHERE | |
room.id = $1 | |
GROUP BY room.id | |
HAVING room.id IS NOT NULL | |
UNION | |
SELECT | |
'available' AS status, | |
availability.available_range AS calendar_range | |
FROM public.availability | |
WHERE | |
availability.room_id = $1 AND | |
availability.available_range && $2 | |
) c | |
ORDER BY lower(c.calendar_range); | |
END; | |
/** | |
* Helper function: combine the closed and available chunks of time with the unavailable chunks | |
* of time to output the final calendar for the given `calendar_range` | |
*/ | |
CREATE OR REPLACE FUNCTION public.calendar_generate_calendar(room_id int, calendar_range tstzrange) | |
RETURNS TABLE (status text, calendar_range tstzrange) | |
LANGUAGE SQL STABLE | |
BEGIN ATOMIC | |
SELECT * | |
FROM ( | |
SELECT | |
cal.status, unnest(tstzmultirange(cal.calendar_range) - COALESCE(tstzmultirange(unavailability.unavailable_range), tstzmultirange())) calendar_range | |
FROM calendar_generate_available($1, $2) cal | |
LEFT OUTER JOIN unavailability ON | |
unavailability.room_id = $1 AND | |
unavailability.unavailable_range && $2 AND | |
cal.calendar_range && unavailability.unavailable_range | |
UNION | |
SELECT 'unavailable' AS status, unavailable_range AS calendar_range | |
FROM unavailability | |
WHERE | |
unavailability.room_id = $1 AND | |
unavailability.unavailable_range && $2 | |
) c | |
ORDER BY lower(c.calendar_range); | |
END; | |
/** | |
* Helper function: substitute the data within the `calendar`; this can be used | |
* for all updates that occur on `availability` and `unavailability` | |
*/ | |
CREATE OR REPLACE FUNCTION public.calendar_manage(room_id int, calendar_date date) | |
RETURNS void | |
LANGUAGE SQL | |
BEGIN ATOMIC | |
WITH delete_calendar AS ( | |
DELETE FROM calendar | |
WHERE | |
room_id = $1 AND | |
calendar_date = $2 | |
) | |
INSERT INTO calendar (room_id, status, calendar_date, calendar_range) | |
SELECT $1, c.status, $2, c.calendar_range | |
FROM calendar_generate_calendar($1, tstzrange($2, $2 + 1)) c; | |
END; | |
/** Now, the trigger functions for availability and unavailability */ | |
CREATE OR REPLACE FUNCTION public.availability_manage() | |
RETURNS trigger | |
AS $trigger$ | |
BEGIN | |
IF TG_OP = 'DELETE' THEN | |
PERFORM public.calendar_manage(OLD.room_id, OLD.available_date); | |
RETURN OLD; | |
END IF; | |
PERFORM public.calendar_manage(NEW.room_id, NEW.available_date); | |
RETURN NEW; | |
END; | |
$trigger$ | |
LANGUAGE plpgsql; | |
CREATE OR REPLACE FUNCTION public.unavailability_manage() | |
RETURNS trigger | |
AS $trigger$ | |
BEGIN | |
IF TG_OP = 'DELETE' THEN | |
PERFORM public.calendar_manage(OLD.room_id, OLD.unavailable_date); | |
RETURN OLD; | |
END IF; | |
PERFORM public.calendar_manage(NEW.room_id, NEW.unavailable_date); | |
RETURN NEW; | |
END; | |
$trigger$ | |
LANGUAGE plpgsql; | |
/** And the triggers, applied to everything */ | |
CREATE TRIGGER availability_manage | |
AFTER INSERT OR UPDATE OR DELETE ON public.availability | |
FOR EACH ROW | |
EXECUTE PROCEDURE public.availability_manage(); | |
CREATE TRIGGER unavailability_manage | |
AFTER INSERT OR UPDATE OR DELETE ON public.unavailability | |
FOR EACH ROW | |
EXECUTE PROCEDURE public.unavailability_manage(); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- This is based on the code from | |
-- https://raw.githubusercontent.com/CrunchyData/postgres-realtime-demo/main/examples/demo/demo1.sql | |
-- which was originally released under the Apache 2.0 License | |
-- https://github.com/CrunchyData/postgres-realtime-demo/blob/main/LICENSE | |
CREATE TABLE public.room ( | |
id int GENERATED BY DEFAULT AS IDENTITY (INCREMENT 2 START WITH 2) PRIMARY KEY, | |
name text NOT NULL | |
); | |
CREATE TABLE public.availability_rule ( | |
id int GENERATED BY DEFAULT AS IDENTITY (INCREMENT 2 START WITH 2) PRIMARY KEY, | |
room_id int NOT NULL REFERENCES public.room (id) ON DELETE CASCADE, | |
days_of_week int[] NOT NULL, | |
start_time time NOT NULL, | |
end_time time NOT NULL, | |
generate_weeks_into_future int NOT NULL DEFAULT 52 | |
); | |
CREATE TABLE public.availability ( | |
id int GENERATED BY DEFAULT AS IDENTITY (INCREMENT 2 START WITH 2) PRIMARY KEY, | |
room_id int NOT NULL REFERENCES public.room (id) ON DELETE CASCADE, | |
availability_rule_id int NOT NULL REFERENCES public.availability_rule (id) ON DELETE CASCADE, | |
available_date date NOT NULL, | |
available_range tstzrange NOT NULL | |
); | |
CREATE INDEX availability_available_range_gist_idx | |
ON availability | |
USING gist(available_range); | |
CREATE TABLE public.unavailability ( | |
id int GENERATED BY DEFAULT AS IDENTITY (INCREMENT 2 START WITH 2) PRIMARY KEY, | |
room_id int NOT NULL REFERENCES public.room (id) ON DELETE CASCADE, | |
unavailable_date date NOT NULL, | |
unavailable_range tstzrange NOT NULL | |
); | |
CREATE INDEX unavailability_unavailable_range_gist_idx | |
ON unavailability | |
USING gist(unavailable_range); | |
CREATE TABLE public.calendar ( | |
id int GENERATED BY DEFAULT AS IDENTITY (INCREMENT 2 START WITH 2) PRIMARY KEY, | |
room_id int NOT NULL REFERENCES public.room (id) ON DELETE CASCADE, | |
status text NOT NULL, | |
calendar_date date NOT NULL, | |
calendar_range tstzrange NOT NULL | |
); | |
CREATE OR REPLACE FUNCTION public.room_insert() | |
RETURNS trigger | |
AS $$ | |
BEGIN | |
IF TG_OP = 'INSERT' THEN | |
INSERT INTO public.calendar ( | |
room_id, | |
status, | |
calendar_date, | |
calendar_range | |
) | |
SELECT | |
NEW.id, 'closed', calendar_date, tstzrange(calendar_date, calendar_date + '1 day'::interval) | |
FROM generate_series( | |
date_trunc('week', CURRENT_DATE), | |
date_trunc('week', CURRENT_DATE + '52 weeks'::interval), | |
'1 day'::interval | |
) calendar_date; | |
END IF; | |
RETURN NEW; | |
END; | |
$$ LANGUAGE plpgsql; | |
/** | |
* Only need to fire trigger inserting a room as UPDATEs do not affect the timings and DELETEs | |
* are cascaded | |
*/ | |
CREATE TRIGGER room_insert | |
AFTER INSERT ON public.room | |
FOR EACH ROW | |
EXECUTE PROCEDURE public.room_insert(); | |
/** | |
* AVAILABILITY RULE: Ensure that updates to general availability | |
*/ | |
/** Helper: Bulk create availability rules; day_of_week ~ isodow (Mon: 1 - Sat: 7) */ | |
CREATE OR REPLACE FUNCTION public.availability_rule_bulk_insert(availability_rule availability_rule, day_of_week int) | |
RETURNS void | |
LANGUAGE SQL | |
BEGIN ATOMIC | |
INSERT INTO availability ( | |
room_id, | |
availability_rule_id, | |
available_date, | |
available_range | |
) | |
SELECT | |
$1.room_id, | |
$1.id, | |
available_date::date + $2 - 1, | |
tstzrange( | |
/** start of range */ | |
(available_date::date + $2 - 1) + $1.start_time, | |
/** end of range */ | |
/** check if there is a time wraparound, if so, increment by a day */ | |
CASE $1.end_time <= $1.start_time | |
WHEN TRUE THEN (available_date::date + $2) + $1.end_time | |
ELSE (available_date::date + $2 - 1) + $1.end_time | |
END | |
) | |
FROM | |
generate_series( | |
date_trunc('week', CURRENT_DATE), | |
date_trunc('week', CURRENT_DATE) + ($1.generate_weeks_into_future::text || ' weeks')::interval, | |
'1 week'::interval | |
) available_date; | |
END; | |
/** | |
* availability_rule trigger function | |
*/ | |
CREATE OR REPLACE FUNCTION public.availability_rule_manage() | |
RETURNS trigger | |
AS $trigger$ | |
DECLARE | |
day_of_week int; | |
BEGIN | |
IF TG_OP = 'INSERT' THEN | |
/** Loop over the days of the week */ | |
FOREACH day_of_week IN ARRAY NEW.days_of_week | |
LOOP | |
PERFORM public.availability_rule_bulk_insert(NEW, day_of_week); | |
END LOOP; | |
ELSIF TG_OP = 'UPDATE' THEN | |
/** Update is tricky if the days_of_week has changed */ | |
IF OLD.days_of_week IS DISTINCT FROM NEW.days_of_week THEN | |
/** NAIVE: We will delete everything and re-insert */ | |
DELETE FROM public.availability | |
WHERE availability_rule_id = NEW.id; | |
/** insertion */ | |
FOREACH day_of_week IN ARRAY NEW.days_of_week | |
LOOP | |
PERFORM public.availability_rule_bulk_insert(NEW, day_of_week); | |
END LOOP; | |
ELSE | |
/** Otherwise, just modify the start/end time ranges */ | |
UPDATE public.availability | |
SET | |
available_range = tstzrange( | |
/** start of range */ | |
available_date + NEW.start_time, | |
/** end of range */ | |
/** check if there is a time wraparound, if so, increment by a day */ | |
CASE NEW.end_time <= NEW.start_time | |
WHEN TRUE THEN (available_date + 1) + NEW.end_time | |
ELSE available_date + NEW.end_time | |
END | |
) | |
WHERE availability_rule_id = NEW.id; | |
END IF; | |
END IF; | |
RETURN NEW; | |
END; | |
$trigger$ | |
LANGUAGE plpgsql; | |
/** availability_rule trigger only fires on insert or update as DELETE is cascaded */ | |
CREATE TRIGGER availability_rule_insert_or_update | |
AFTER INSERT OR UPDATE ON public.availability_rule | |
FOR EACH ROW | |
EXECUTE PROCEDURE public.availability_rule_manage(); | |
/** AVAILABILITY, UNAVAILABILITY, and CALENDAR */ | |
/** Helper function: generate the available chunks of time within a block of time for a day within a calendar */ | |
CREATE OR REPLACE FUNCTION public.calendar_generate_available(room_id int, calendar_range tstzrange) | |
RETURNS TABLE(status text, calendar_range tstzrange) | |
LANGUAGE SQL STABLE | |
BEGIN ATOMIC | |
SELECT * | |
FROM | |
( | |
SELECT | |
'closed', | |
unnest(tstzmultirange($2) - COALESCE(range_agg(availability.available_range), tstzmultirange())) AS calendar_range | |
FROM public.room | |
LEFT OUTER JOIN public.availability ON | |
availability.room_id = room.id AND | |
availability.available_range && $2 | |
WHERE | |
room.id = $1 | |
GROUP BY room.id | |
HAVING room.id IS NOT NULL | |
UNION | |
SELECT | |
'available' AS status, | |
availability.available_range AS calendar_range | |
FROM public.availability | |
WHERE | |
availability.room_id = $1 AND | |
availability.available_range && $2 | |
) c | |
ORDER BY lower(c.calendar_range); | |
END; | |
/** | |
* Helper function: combine the closed and available chunks of time with the unavailable chunks | |
* of time to output the final calendar for the given `calendar_range` | |
*/ | |
CREATE OR REPLACE FUNCTION public.calendar_generate_calendar(room_id int, calendar_range tstzrange) | |
RETURNS TABLE (status text, calendar_range tstzrange) | |
LANGUAGE SQL STABLE | |
BEGIN ATOMIC | |
SELECT * | |
FROM ( | |
SELECT | |
cal.status, unnest(tstzmultirange(cal.calendar_range) - COALESCE(tstzmultirange(unavailability.unavailable_range), tstzmultirange())) calendar_range | |
FROM calendar_generate_available($1, $2) cal | |
LEFT OUTER JOIN unavailability ON | |
unavailability.room_id = $1 AND | |
unavailability.unavailable_range && $2 AND | |
cal.calendar_range && unavailability.unavailable_range | |
UNION | |
SELECT 'unavailable' AS status, unavailable_range AS calendar_range | |
FROM unavailability | |
WHERE | |
unavailability.room_id = $1 AND | |
unavailability.unavailable_range && $2 | |
) c | |
ORDER BY lower(c.calendar_range); | |
END; | |
/** | |
* Helper function: substitute the data within the `calendar`; this can be used | |
* for all updates that occur on `availability` and `unavailability` | |
*/ | |
CREATE OR REPLACE FUNCTION public.calendar_manage(room_id int, calendar_date date) | |
RETURNS void | |
LANGUAGE SQL | |
BEGIN ATOMIC | |
WITH delete_calendar AS ( | |
DELETE FROM calendar | |
WHERE | |
room_id = $1 AND | |
calendar_date = $2 | |
) | |
INSERT INTO calendar (room_id, status, calendar_date, calendar_range) | |
SELECT $1, c.status, $2, c.calendar_range | |
FROM calendar_generate_calendar($1, tstzrange($2, $2 + 1)) c; | |
END; | |
/** Now, the trigger functions for availability and unavailability */ | |
CREATE OR REPLACE FUNCTION public.availability_manage() | |
RETURNS trigger | |
AS $trigger$ | |
BEGIN | |
IF TG_OP = 'DELETE' THEN | |
PERFORM public.calendar_manage(OLD.room_id, OLD.available_date); | |
RETURN OLD; | |
END IF; | |
PERFORM public.calendar_manage(NEW.room_id, NEW.available_date); | |
RETURN NEW; | |
END; | |
$trigger$ | |
LANGUAGE plpgsql; | |
CREATE OR REPLACE FUNCTION public.unavailability_manage() | |
RETURNS trigger | |
AS $trigger$ | |
BEGIN | |
IF TG_OP = 'DELETE' THEN | |
PERFORM public.calendar_manage(OLD.room_id, OLD.unavailable_date); | |
RETURN OLD; | |
END IF; | |
PERFORM public.calendar_manage(NEW.room_id, NEW.unavailable_date); | |
RETURN NEW; | |
END; | |
$trigger$ | |
LANGUAGE plpgsql; | |
/** And the triggers, applied to everything */ | |
CREATE TRIGGER availability_manage | |
AFTER INSERT OR UPDATE OR DELETE ON public.availability | |
FOR EACH ROW | |
EXECUTE PROCEDURE public.availability_manage(); | |
CREATE TRIGGER unavailability_manage | |
AFTER INSERT OR UPDATE OR DELETE ON public.unavailability | |
FOR EACH ROW | |
EXECUTE PROCEDURE public.unavailability_manage(); | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment