Skip to content

Instantly share code, notes, and snippets.

@jkatz
Created February 16, 2023 16:45
Show Gist options
  • Save jkatz/5655c10da1a4c8691094e951ea07b036 to your computer and use it in GitHub Desktop.
Save jkatz/5655c10da1a4c8691094e951ea07b036 to your computer and use it in GitHub Desktop.
-- 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
/**
* Schema
*/
CREATE TABLE public.room (
id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE public.availability_rule (
id int GENERATED BY DEFAULT AS IDENTITY 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 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 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 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 INDEX calendar_room_id_calendar_date_idx
ON calendar (room_id, calendar_date);
/**
* Helper functions and Triggers
*/
/**
* ROOM: Need to create an initial calendar for all the data in the room indicating all
* the times that everything is closed
*/
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
AS $$
INSERT INTO public.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;
$$ LANGUAGE SQL;
/**
* 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)
AS $$
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)
$$ LANGUAGE SQL STABLE;
/**
* 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)
AS $$
SELECT *
FROM (
SELECT
cal.status, unnest(tstzmultirange(cal.calendar_range) - COALESCE(tstzmultirange(unavailability.unavailable_range), tstzmultirange())) calendar_range
FROM public.calendar_generate_available($1, $2) cal
LEFT OUTER JOIN public.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 public.unavailability
WHERE
unavailability.room_id = $1 AND
unavailability.unavailable_range && $2
) c
ORDER BY lower(c.calendar_range);
$$ LANGUAGE SQL STABLE;
/**
* 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
AS $$
WITH delete_calendar AS (
DELETE FROM public.calendar
WHERE
room_id = $1 AND
calendar_date = $2
)
INSERT INTO public.calendar (room_id, status, calendar_date, calendar_range)
SELECT $1, c.status, $2, c.calendar_range
FROM public.calendar_generate_calendar($1, tstzrange($2, $2 + 1)) c
$$ LANGUAGE SQL;
/** 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