Kalenders in PostgreSQL met PlPython3 en RRULE stage

Kalenders hebben één lastig probleem: herhalende afspraken en uitzonderingen daarop. Gelukkig is dat allemaal al uitgewerkt in iCalendar en dat is weer uitgewerkt in Python's dateutillibrary en die is weer op te roepen via PlPython.

Het klassieke probleem met kalenders zit in herhalende afspraken; hoe sla je op dat iemand een géén afspraak heeft, op basis van het feit dat hij een halfjaar geleden heeft aangegeven dat hij elke week op de donderdag een afspraak heeft maar op vakantie is van gister tot overmorgen?

Al dit ingewikkelde gedoe is al uitgewerkt en beschreven in de iCalender standaard. Er zijn uiteraard meerdere implementaties van die standaard en vandaag kies ik voorde implementatie in Python's dateutil library.

iCalendar

De iCalendar standaard stelt voor om een herhalende afspraak te defineren als een string waarin de hele definitie wordt uitgewerkt. Hoe je zo'n string precies opstelt en wat er allemaal in kan kun je terugvinden in de standaard en een van de vele RRULE-Generators, zoals https://www.textmagic.com/free-tools/rrule-generator en https://jakubroztocil.github.io/rrule/

Een simpel voorbeeld is "FREQ=WEEKLY;BYDAY=MO,TU,WE;INTERVAL=2;COUNT=5" en dat staat voor 'herhalen op weekbasis, op de maandag, dinsdag en woensdag, elke 2 weken, 5 keer doen.

Deze afspraak geldt dus op de maandag, disndag en woensdag, hij slaat telkens een week over en gebeurt 5 keer.

Let op met uitspraken als "5 keer herhalen" want dan gebeurt iets dus 6 keer;

RRULE

Python's rrule in de dateutil library kan zo'n string omzetten naar een setje timestamps waarom de afspraak gepland is.

Uit de handleiding:

code

list(rrulestr("""
 ... DTSTART:19970902T090000
 ... RRULE:FREQ=DAILY;INTERVAL=10;COUNT=5
 ... """))
 [datetime.datetime(1997, 9, 2, 9, 0),
  datetime.datetime(1997, 9, 12, 9, 0),
  datetime.datetime(1997, 9, 22, 9, 0),
  datetime.datetime(1997, 10, 2, 9, 0),
  datetime.datetime(1997, 10, 12, 9, 0)]

De clou is dus dat je niet opslaat wat de exacte datums allemaal zijn, je berekent ze realtime wanneer nodig.

PlPython

Hoe je PlPython installeert en activeert staat mooi uitgelegd in de handleiding, ik ga dat hierniet behandelen.

Het simpele voorbeeldje van hierboven ziet er in een PlPython function niet veel anders uit.
Onderstaande stored function wordt aangeroepen zonder parameters, hij geeft een TABLE terug met daarin een kolom 'date'van het TIMESTAMP type.
De datums die de rrule library aanlevert worden via een for lus doorgegeven en per stuk via yield aan de aanroeper teruggegeven.

code

CREATE FUNCTION rrule_test()
RETURNS TABLE (date timestamp)
AS $$
from dateutil.rrule import rrulestr

for d in list(rrulestr("""
    DTSTART:19970902T090000
    RRULE:FREQ=DAILY;INTERVAL=10;COUNT=5
    """)):
    yield (d)
$$ LANGUAGE plpython3u;

-- De procedure heeft RETURNS TABLE dus kan hij ook als tabel benaderd worden:
SELECT * FROM rrule_test();
        date         
---------------------
 1997-09-02 09:00:00
 1997-09-12 09:00:00
 1997-09-22 09:00:00
 1997-10-02 09:00:00
 1997-10-12 09:00:00
(5 rows)

Rulesets

Het lastigste aan herhalende afspraken zijn de uitzonderingen: Iemand werkt maandag tot en met vrijdag maar om de week heeft hij een vrijdag vrij.

In dateutil wordt gedaan met een ruleset waaraan je rules kunt toevoegen die wel of niet exclusions zijn.

In Python:

code

from dateutil.rrule import rruleset, rrule, MO, TU, WE, TH,FR, DAILY, WEEKLY
from dateutil.parser import parse

set = rruleset()
-- Werken op alle werkdagen
set.rrule(rrule(DAILY, byweekday=(MO,TU,WE,TH,FR), count=31, dtstart=parse("19970101T090000")))
-- Elke twee weken een uitzondering op de vrijdag
set.exrule(rrule(WEEKLY, byweekday=(FR), interval=2, dtstart=parse("19970101T090000")))
for d in list(set):
  print(d.strftime("%b %d %Y %H:%M:%S %a"))

Jan 01 1997 09:00:00 Wed
Jan 02 1997 09:00:00 Thu
Jan 06 1997 09:00:00 Mon
Jan 07 1997 09:00:00 Tue
Jan 08 1997 09:00:00 Wed
Jan 09 1997 09:00:00 Thu
Jan 10 1997 09:00:00 Fri
Jan 13 1997 09:00:00 Mon
Jan 14 1997 09:00:00 Tue
Jan 15 1997 09:00:00 Wed
Jan 16 1997 09:00:00 Thu
Jan 20 1997 09:00:00 Mon
Jan 21 1997 09:00:00 Tue
Jan 22 1997 09:00:00 Wed
Jan 23 1997 09:00:00 Thu
Jan 24 1997 09:00:00 Fri
Jan 27 1997 09:00:00 Mon
Jan 28 1997 09:00:00 Tue
Jan 29 1997 09:00:00 Wed
Jan 30 1997 09:00:00 Thu
Feb 03 1997 09:00:00 Mon
Feb 04 1997 09:00:00 Tue
Feb 05 1997 09:00:00 Wed
Feb 06 1997 09:00:00 Thu
Feb 07 1997 09:00:00 Fri
Feb 10 1997 09:00:00 Mon
Feb 11 1997 09:00:00 Tue
Feb 12 1997 09:00:00 Wed

Rulesets gaan altijd over dezelfde herhalende afspraak dus als je alle datums wilt hebben van alle items in de kalender dan moet je in een lusje door de items heen; alle items ophalen, alle uitzonderingen daaraan toevoegen en voor elk item met uitzonderingen een nieuwe ruleset laten doorwerken. Dit is natuurlijk best te doen met een ORDER BY en een if/else in de lus die kijkt naar of het id van het item is veranderd, maar het kan veel mooier.

Als je in de kalender elke uitzondering in een andere kolommee geeft bij wele rule de uitzondering hoort, dan kun je darop laten groeperen door Python's groupby uit itertools die een array kan opdelen in een associative array met het pernt-kalender-item-id als key.

Omdat de function een table teruggeeft kun je de uitkomsten ook verwerken in joins, group-by, count etc.

Testcase

Voor de testcase gebruik ik de situatie van een opleidingsinstituut waar zalen en kamers worden gebruikt om studenten te ontvangen. Kamers hebben een capaciteit voor een aantal gasten, zalen hebben capaciteit voor gasten en catering.

Calendar is een aparte tabel die effctief de rrule regels bevat.
Appointments bevat de daadwerkelijk gemaakte afspraken.
Booked_users bepaalt welke users voor welke afspraken zijn ingeboekt.

code

create table users
(
    id         serial not null
        constraint users_pkey
            primary key,
    name       text,
    profession text
);

create table rooms
(
    id           integer not null
        constraint pk_rooms_id
            primary key,
    title        varchar default 100,
    max_guests   integer,
    max_catering integer
);

create table appointments
(
    id      integer not null
        constraint pk_appointments_id
            primary key,
    title   text,
    room_id integer not null
        constraint fk_appointments_rooms
            references rooms
            on update cascade on delete cascade,
    date    timestamp with time zone
);

create table booked_users
(
    appointment_id integer not null
        constraint fk_booked_users_appointments
            references appointments,
    user_id        integer not null
        constraint fk_booked_users_users
            references users,
    constraint idx_booked_users
        primary key (appointment_id, user_id)
);

-- auto-generated definition
create table calendar
(
    user_id        integer                                                not null
        constraint fk_cal_users
            references users
            on update cascade on delete cascade,
    appointment_id integer
        constraint fk_cal_appointments
            references appointments
            on update cascade on delete cascade,
    startdate      timestamp with time zone,
    enddate        timestamp with time zone,
    repetitions    integer,
    exclusion      boolean,
    description    text,
    id             integer default nextval('calendar_sequence'::regclass) not null
        constraint pk_calendar_id
            primary key
        constraint unq_calendar_id
            unique,
    parent_cal     integer                                                not null
        constraint fk_calendar_calendar
            references calendar
            on update cascade on delete cascade,
    duration       interval,
    last_date      timestamp with time zone
);

alter table calendar
    owner to vinny;

Vanaf hier is het een kwestie van invullen en opvragen.

Voor het invoeren van een item in de kalender gebruik ik alvast een stored function, hier gaat in de toekomst nog functionaliteit aan worden toegevoegd en zo gaat alle interactie met de kalender via functions.

code

CREATE FUNCTION calender_add(user_id int, startdate timestamptz, enddate timestamptz, repetitions int)
    RETURNS integer
AS
$$
stm = plpy.prepare("SELECT NEXTVAL('calendar_sequence') AS v")
vals = plpy.execute(stm, [])
val = vals[0]['v']

stm = plpy.prepare("INSERT INTO calendar (id, user_id, startdate, enddate, repetitions, exclusion, parent_cal) VALUES ($1, $2, $3, $4, $5, FALSE, $6)", ["INT", "INT", "TIMESTAMPTZ", "TIMESTAMPTZ", "INT", "INT"])

plpy.execute(stm, [val, user_id, startdate, enddate, repetitions, val])
plpy.info(f'Inserted rule {val}')
return val
$$ LANGUAGE plpython3u;

code

CREATE FUNCTION calender_exception(user_id int, startdate timestamptz, enddate timestamptz, repetitions int,
                                   parent_cal INT)
    RETURNS integer
AS
$$
stm = plpy.prepare("INSERT INTO calendar (user_id, startdate, enddate, repetitions, exclusion, parent_cal) VALUES ($1, $2, $3, $4, TRUE, $5) RETURNING id",["INT", "TIMESTAMPTZ", "TIMESTAMPTZ", "INT", "INT"])

res = plpy.execute(stm, [user_id, startdate, enddate, repetitions, parent_cal])
plpy.info(f'Inserted exrule {res[0]['id']}')
return res[0]['id']
$$ LANGUAGE plpython3u;

code

CREATE FUNCTION calendar(user_id int[], startdate timestamptz, enddate timestamptz)
    RETURNS TABLE
            (
                user_id        int,
                appointment_id int,
                date           timestamp,
                q              text,
                description    text
            )
AS
$$
from dateutil.rrule import rruleset, rrule, MO, SU, DAILY, WEEKLY
from dateutil.parser import parse
from dateutil.tz import gettz
from itertools import groupby

def projection(val):
    return val['parent_cal'];

s = parse(startdate)
e = parse(enddate)


stm = plpy.prepare("SELECT * FROM calendar WHERE user_id=$1 ORDER BY parent_cal NULLS FIRST, id", ["INT"])

# Fetch user-by-user
for i in user_id:
    # Fetch cal items per user.
    cal_items = plpy.execute(stm, [i])

    descr = '-'

    x_sorted = sorted(cal_items, key=projection)
    x_grouped = [list(it) for k, it in groupby(x_sorted, projection)]

    for group in x_grouped:
        set = rruleset()
        for cal in group:
            data = {}
            data['dtstart'] = parse(cal['startdate'])


            if cal['enddate'] is not None:
                data['until'] = parse(cal['enddate'])
            elif cal['repetitions'] is not None:
                data['count'] = cal['repetitions']
            else:
                data['count'] = 10

            if cal['exclusion'] == True:
                set.exrule(rrule(DAILY, **data))
            else:
                set.rrule(rrule(DAILY, **data))

            # Prepare data for yielding, that cannot be in the ruleset
            data['exclusion'] = cal['exclusion']

            if cal['exclusion'] == False:
                descr = cal['description']

        for d in list(set):
            if d >= s and d <= e:
                # plpy.info('yay')
                yield (cal['user_id'], cal['appointment_id'], d, data, descr)
$$ LANGUAGE plpython3u;

Dan wat test data om mee te spelen.

code

INSERT INTO users (id, name, profession)
VALUES (1, 'Kees Kok', 'cook'),
       (2, 'Do Cent', 'teacher'),
       (3, 'Piet Pannetje', 'cook'),
       (4, 'Gerrit Goedgeraden', 'teacher'),
       (5, 'Greetje Gast', 'guest'),
       (6, 'Kaatje Klant', 'guest'),
       (7, 'Anita Afnemer', 'guest')
;
INSERT INTO rooms (id, title, max_guests, max_catering)
VALUES (1, 'Kamer 1', 4, 0),
       (2, 'Kamer 2', 4, 0),
       (3, 'Kamer 3', 4, 0),
       (4, 'Kamer 4', 8, 0),
       (5, 'Grote Zaal', 16, 2),
       (6, 'Grotere Zaal', 32, 4),
       (7, 'Hele Grote Zaal', 16, 2)
;
INSERT INTO appointments (id, title, room_id, date)
VALUES (1, 'Inleiding Python', 1, '20200103T090000'),
       (2, 'PostgreSQL congres', 7, '20200104T090000'),
       (3, 'Inleiding sukerspinnen', 5, '20200105T090000'),
       (4, 'Kennismaking', 6, '20200106T090000')
;


-- Kees Kok is beschikbaar van 2020-01-01 t/m 2020-01-15
SELECT calender_add(1, '20200101T090000'::timestamptz, '20200115T000000'::timestamptz, null);

-- Do Cent is beschikbaar van 2020-01-01 t/m 2020-01-15
SELECT calender_add(2, '20200101T090000'::timestamptz, '20200115T000000'::timestamptz, null);

-- Do Cent en Kees Kok zijn geboekt voor appointment 1, dus dat worden uitzonderingen in de kalender.
SELECT calender_exception(1, '20200103T090000'::timestamptz, null, 1, 1);
SELECT calender_exception(2, '20200103T090000'::timestamptz, null, 1, 2);

--Kees kok is op vakantie van 10 tot 13
SELECT calender_exception(1, '20200109T090000'::timestamptz, '20200113T000000'::timestamptz, null, 1);

-- Piet Pannetje en Gerrit Goedgeraden doen ook mee.
SELECT calender_add(3, '20200101T090000'::timestamptz, '20200110T000000'::timestamptz, null);
SELECT calender_add(4, '20200103T090000'::timestamptz, '20200111T000000'::timestamptz, null);

code

-- Maak een lijst van datums per kamer.
SELECT d, rooms.title
FROM rooms,
     GENERATE_SERIES('20200101T090000', '20200114T090000', INTERVAL '1 DAY') as D;

           d            |      title
------------------------+-----------------
 2020-01-01 09:00:00+01 | Kamer 1
 2020-01-01 09:00:00+01 | Kamer 2
 2020-01-01 09:00:00+01 | Kamer 3
 2020-01-01 09:00:00+01 | Kamer 4
 2020-01-01 09:00:00+01 | Grote Zaal
 2020-01-01 09:00:00+01 | Grotere Zaal
 2020-01-01 09:00:00+01 | Hele Grote Zaal
 2020-01-02 09:00:00+01 | Kamer 1
 2020-01-02 09:00:00+01 | Kamer 2
 2020-01-02 09:00:00+01 | Kamer 3
 2020-01-02 09:00:00+01 | Kamer 4
 2020-01-02 09:00:00+01 | Grote Zaal
 2020-01-02 09:00:00+01 | Grotere Zaal
 2020-01-02 09:00:00+01 | Hele Grote Zaal
 2020-01-03 09:00:00+01 | Kamer 1
 2020-01-03 09:00:00+01 | Kamer 2
 2020-01-03 09:00:00+01 | Kamer 3
 2020-01-03 09:00:00+01 | Kamer 4
...

code

-- Voeg de beschikbaarheid toe

SELECT d,
       rooms.title AS roomtitle,
       rooms.id,
       ARRAY_TO_STRING(ARRAY_AGG(CASE WHEN users.profession='cook' THEN users.name ELSE NULL END ORDER BY f.user_id), ', ') AS available_cooks,
       ARRAY_TO_STRING(ARRAY_AGG(CASE WHEN users.profession='teacher' THEN users.name ELSE NULL END ORDER BY f.user_id), ', ') AS available_teachers
FROM GENERATE_SERIES('20200101T090000', '20200114T090000', INTERVAL '1 DAY') as d
CROSS JOIN
     rooms
         LEFT JOIN calendar(ARRAY [1,2,3,4], '20200101T090000'::timestamptz,
                         '20200120T190000'::timestamptz) as f(user_id, appointment_id, date, data, description)
                   ON d = date
         LEFT JOIN users ON users.id = user_id
GROUP BY d, rooms, rooms.title, rooms.id;

           d            | roomtitle       | id |     available_cooks     | available_teachers
------------------------+-----------------+----+-------------------------+--------------------
 2020-01-01 09:00:00+01 | Kamer 1         |  1 | Kees Kok, Piet Pannetje | Do Cent
 2020-01-01 09:00:00+01 | Kamer 2         |  2 | Kees Kok, Piet Pannetje | Do Cent
 2020-01-01 09:00:00+01 | Kamer 3         |  3 | Kees Kok, Piet Pannetje | Do Cent
 2020-01-01 09:00:00+01 | Kamer 4         |  4 | Kees Kok, Piet Pannetje | Do Cent
 2020-01-01 09:00:00+01 | Grote Zaal      |  5 | Kees Kok, Piet Pannetje | Do Cent
 2020-01-01 09:00:00+01 | Grotere Zaal    |  6 | Kees Kok, Piet Pannetje | Do Cent
 2020-01-01 09:00:00+01 | Hele Grote Zaal |  7 | Kees Kok, Piet Pannetje | Do Cent
 2020-01-02 09:00:00+01 | Kamer 1         |  1 | Kees Kok, Piet Pannetje | Do Cent
 2020-01-02 09:00:00+01 | Kamer 2         |  2 | Kees Kok, Piet Pannetje | Do Cent
 2020-01-02 09:00:00+01 | Kamer 3         |  3 | Kees Kok, Piet Pannetje | Do Cent
 2020-01-02 09:00:00+01 | Kamer 4         |  4 | Kees Kok, Piet Pannetje | Do Cent
 2020-01-02 09:00:00+01 | Grote Zaal      |  5 | Kees Kok, Piet Pannetje | Do Cent
 2020-01-02 09:00:00+01 | Grotere Zaal    |  6 | Kees Kok, Piet Pannetje | Do Cent
 2020-01-02 09:00:00+01 | Hele Grote Zaal |  7 | Kees Kok, Piet Pannetje | Do Cent
 2020-01-03 09:00:00+01 | Kamer 1         |  1 | Kees Kok, Piet Pannetje | Do Cent, Gerrit Goedgeraden
 2020-01-03 09:00:00+01 | Kamer 2         |  2 | Kees Kok, Piet Pannetje | Do Cent, Gerrit Goedgeraden
 2020-01-03 09:00:00+01 | Kamer 3         |  3 | Kees Kok, Piet Pannetje | Do Cent, Gerrit Goedgeraden
 2020-01-03 09:00:00+01 | Kamer 4         |  4 | Kees Kok, Piet Pannetje | Do Cent, Gerrit Goedgeraden
 2020-01-03 09:00:00+01 | Grote Zaal      |  5 | Kees Kok, Piet Pannetje | Do Cent, Gerrit Goedgeraden
 2020-01-03 09:00:00+01 | Grotere Zaal    |  6 | Kees Kok, Piet Pannetje | Do Cent, Gerrit Goedgeraden
 2020-01-03 09:00:00+01 | Hele Grote Zaal |  7 | Kees Kok, Piet Pannetje | Do Cent, Gerrit Goedgeraden

code

-- Beetje opschonen
WItH cal AS (
    SELECT d,
           rooms.title   AS roomtitle,
           rooms.id,
           ARRAY_TO_STRING(
                   ARRAY_AGG(CASE WHEN users.profession = 'cook' THEN users.name ELSE NULL END ORDER BY f.user_id),
                   ', ') AS available_cooks,
           ARRAY_TO_STRING(
                   ARRAY_AGG(CASE WHEN users.profession = 'teacher' THEN users.name ELSE NULL END ORDER BY f.user_id),
                   ', ') AS available_teachers
    FROM GENERATE_SERIES('20200101T090000', '20200114T090000', INTERVAL '1 DAY') as d
             CROSS JOIN
         rooms
             LEFT JOIN calendar(ARRAY [1,2,3,4], '20200101T090000'::timestamptz,
                             '20200120T190000'::timestamptz) as f(user_id, appointment_id, date, data, description)
                       ON d = date
             LEFT JOIN users ON users.id = user_id
    GROUP BY d, rooms, rooms.title, rooms.id)
SELECT to_char(d, 'YYYY-mm-dd'), cal.roomtitle, available_teachers, available_cooks, appointments.title
FROM cal
         LEFT JOIN appointments ON cal.id = appointments.room_id AND cal.d = appointments.date;


  to_char   |    roomtitle    |     available_teachers      |     available_cooks     |         title
------------+-----------------+-----------------------------+-------------------------+------------------------
 2020-01-01 | Kamer 1         | Do Cent                     | Kees Kok, Piet Pannetje |
 2020-01-01 | Kamer 2         | Do Cent                     | Kees Kok, Piet Pannetje |
 2020-01-01 | Kamer 3         | Do Cent                     | Kees Kok, Piet Pannetje |
 2020-01-01 | Kamer 4         | Do Cent                     | Kees Kok, Piet Pannetje |
 2020-01-01 | Grote Zaal      | Do Cent                     | Kees Kok, Piet Pannetje |
 2020-01-01 | Grotere Zaal    | Do Cent                     | Kees Kok, Piet Pannetje |
 2020-01-01 | Hele Grote Zaal | Do Cent                     | Kees Kok, Piet Pannetje |
 2020-01-02 | Kamer 1         | Do Cent                     | Kees Kok, Piet Pannetje |
 2020-01-02 | Kamer 2         | Do Cent                     | Kees Kok, Piet Pannetje |
 2020-01-02 | Kamer 3         | Do Cent                     | Kees Kok, Piet Pannetje |
 2020-01-02 | Kamer 4         | Do Cent                     | Kees Kok, Piet Pannetje |
 2020-01-02 | Grote Zaal      | Do Cent                     | Kees Kok, Piet Pannetje |
 2020-01-02 | Grotere Zaal    | Do Cent                     | Kees Kok, Piet Pannetje |
 2020-01-02 | Hele Grote Zaal | Do Cent                     | Kees Kok, Piet Pannetje |
 2020-01-03 | Kamer 1         | Do Cent, Gerrit Goedgeraden | Kees Kok, Piet Pannetje | Inleiding Python
 2020-01-03 | Kamer 2         | Do Cent, Gerrit Goedgeraden | Kees Kok, Piet Pannetje |
 2020-01-03 | Kamer 3         | Do Cent, Gerrit Goedgeraden | Kees Kok, Piet Pannetje |
 2020-01-03 | Kamer 4         | Do Cent, Gerrit Goedgeraden | Kees Kok, Piet Pannetje |
 2020-01-03 | Grote Zaal      | Do Cent, Gerrit Goedgeraden | Kees Kok, Piet Pannetje |
 2020-01-03 | Grotere Zaal    | Do Cent, Gerrit Goedgeraden | Kees Kok, Piet Pannetje |
 2020-01-03 | Hele Grote Zaal | Do Cent, Gerrit Goedgeraden | Kees Kok, Piet Pannetje |
 2020-01-04 | Kamer 1         | Do Cent, Gerrit Goedgeraden | Kees Kok, Piet Pannetje |
 2020-01-04 | Kamer 2         | Do Cent, Gerrit Goedgeraden | Kees Kok, Piet Pannetje |
 2020-01-04 | Kamer 3         | Do Cent, Gerrit Goedgeraden | Kees Kok, Piet Pannetje |
 2020-01-04 | Kamer 4         | Do Cent, Gerrit Goedgeraden | Kees Kok, Piet Pannetje |
 2020-01-04 | Grote Zaal      | Do Cent, Gerrit Goedgeraden | Kees Kok, Piet Pannetje |
 2020-01-04 | Grotere Zaal    | Do Cent, Gerrit Goedgeraden | Kees Kok, Piet Pannetje |
 2020-01-04 | Hele Grote Zaal | Do Cent, Gerrit Goedgeraden | Kees Kok, Piet Pannetje | PostgreSQL congres
 2020-01-05 | Kamer 1         | Do Cent, Gerrit Goedgeraden | Kees Kok, Piet Pannetje |
 2020-01-05 | Kamer 2         | Do Cent, Gerrit Goedgeraden | Kees Kok, Piet Pannetje |
 2020-01-05 | Kamer 3         | Do Cent, Gerrit Goedgeraden | Kees Kok, Piet Pannetje |
 2020-01-05 | Kamer 4         | Do Cent, Gerrit Goedgeraden | Kees Kok, Piet Pannetje |
 2020-01-05 | Grote Zaal      | Do Cent, Gerrit Goedgeraden | Kees Kok, Piet Pannetje | Inleiding sukerspinnen
 2020-01-05 | Grotere Zaal    | Do Cent, Gerrit Goedgeraden | Kees Kok, Piet Pannetje |
 2020-01-05 | Hele Grote Zaal | Do Cent, Gerrit Goedgeraden | Kees Kok, Piet Pannetje |
 2020-01-06 | Kamer 1         | Do Cent, Gerrit Goedgeraden | Kees Kok, Piet Pannetje |
 2020-01-06 | Kamer 2         | Do Cent, Gerrit Goedgeraden | Kees Kok, Piet Pannetje |
 2020-01-06 | Kamer 3         | Do Cent, Gerrit Goedgeraden | Kees Kok, Piet Pannetje |
 2020-01-06 | Kamer 4         | Do Cent, Gerrit Goedgeraden | Kees Kok, Piet Pannetje |
 2020-01-06 | Grote Zaal      | Do Cent, Gerrit Goedgeraden | Kees Kok, Piet Pannetje |
 2020-01-06 | Grotere Zaal    | Do Cent, Gerrit Goedgeraden | Kees Kok, Piet Pannetje | Kennismaking
 2020-01-06 | Hele Grote Zaal | Do Cent, Gerrit Goedgeraden | Kees Kok, Piet Pannetje |

code

-- Hang de booked tabel er aan om te zien wie er geboekt is per afspraak.

WItH booked AS (SELECT booked_users.appointment_id,
                       ARRAY_TO_STRING(
                               ARRAY_AGG(name), ',') AS booked_names
                FROM booked_users
                         INNER JOIN users u on booked_users.user_id = u.id
                GROUP BY appointment_id),
     cal AS (
         SELECT d,
                rooms.title   AS roomtitle,
                rooms.id,
                ARRAY_TO_STRING(
                        ARRAY_AGG(CASE WHEN users.profession = 'cook' THEN users.name ELSE NULL END ORDER BY f.user_id),
                        ', ') AS available_cooks,
                ARRAY_TO_STRING(
                        ARRAY_AGG(CASE WHEN users.profession = 'teacher' THEN users.name ELSE NULL END
                                  ORDER BY f.user_id),
                        ', ') AS available_teachers
         FROM GENERATE_SERIES('20200101T090000', '20200114T090000', INTERVAL '1 DAY') as d
                  CROSS JOIN
              rooms
                  LEFT JOIN calendar(ARRAY [1,2,3,4], '20200101T090000'::timestamptz,
                                  '20200120T190000'::timestamptz) as f(user_id, appointment_id, date, data, description)
                            ON d = date
                  LEFT JOIN users ON users.id = user_id
         GROUP BY d, rooms, rooms.title, rooms.id)
SELECT to_char(d, 'YYYY-mm-dd'),
       cal.roomtitle,
       available_teachers,
       available_cooks,
       appointments.title,
       booked.booked_names
FROM cal
         LEFT JOIN appointments ON cal.id = appointments.room_id AND cal.d = appointments.date
         LEFT JOIN booked ON booked.appointment_id = appointments.id;

  to_char   |    roomtitle    |     available_teachers      |     available_cooks     |         title          |   booked_names
------------+-----------------+-----------------------------+-------------------------+------------------------+------------------
 2020-01-01 | Kamer 1         | Do Cent                     | Kees Kok, Piet Pannetje |                        |
 2020-01-01 | Kamer 2         | Do Cent                     | Kees Kok, Piet Pannetje |                        |
 2020-01-01 | Kamer 3         | Do Cent                     | Kees Kok, Piet Pannetje |                        |
 2020-01-01 | Kamer 4         | Do Cent                     | Kees Kok, Piet Pannetje |                        |
 2020-01-01 | Grote Zaal      | Do Cent                     | Kees Kok, Piet Pannetje |                        |
 2020-01-01 | Grotere Zaal    | Do Cent                     | Kees Kok, Piet Pannetje |                        |
 2020-01-01 | Hele Grote Zaal | Do Cent                     | Kees Kok, Piet Pannetje |                        |
 2020-01-02 | Kamer 1         | Do Cent                     | Kees Kok, Piet Pannetje |                        |
 2020-01-02 | Kamer 2         | Do Cent                     | Kees Kok, Piet Pannetje |                        |
 2020-01-02 | Kamer 3         | Do Cent                     | Kees Kok, Piet Pannetje |                        |
 2020-01-02 | Kamer 4         | Do Cent                     | Kees Kok, Piet Pannetje |                        |
 2020-01-02 | Grote Zaal      | Do Cent                     | Kees Kok, Piet Pannetje |                        |
 2020-01-02 | Grotere Zaal    | Do Cent                     | Kees Kok, Piet Pannetje |                        |
 2020-01-02 | Hele Grote Zaal | Do Cent                     | Kees Kok, Piet Pannetje |                        |
 2020-01-03 | Kamer 1         | Do Cent, Gerrit Goedgeraden | Kees Kok, Piet Pannetje | Inleiding Python       | Kees Kok,Do Cent
 2020-01-03 | Kamer 2         | Do Cent, Gerrit Goedgeraden | Kees Kok, Piet Pannetje |                        |
 2020-01-03 | Kamer 3         | Do Cent, Gerrit Goedgeraden | Kees Kok, Piet Pannetje |                        |
 2020-01-03 | Kamer 4         | Do Cent, Gerrit Goedgeraden | Kees Kok, Piet Pannetje |                        |
 2020-01-03 | Grote Zaal      | Do Cent, Gerrit Goedgeraden | Kees Kok, Piet Pannetje |                        |
 2020-01-03 | Grotere Zaal    | Do Cent, Gerrit Goedgeraden | Kees Kok, Piet Pannetje |                        |
 2020-01-03 | Hele Grote Zaal | Do Cent, Gerrit Goedgeraden | Kees Kok, Piet Pannetje |                        |
 2020-01-04 | Kamer 1         | Do Cent, Gerrit Goedgeraden | Kees Kok, Piet Pannetje |                        |
 2020-01-04 | Kamer 2         | Do Cent, Gerrit Goedgeraden | Kees Kok, Piet Pannetje |                        |

Prangende vragen

Q: Is dat niet heel erg inefficient? Wat als je een database hebt met 10k leden die allemaal 20 afspraken per dag hebben? Als je dan een jaaroverzicht maakt krijg je 73 miljoen rijen terug.
A: Klopt helemaal, als je zulke overzichten moet maken dan zul je hier nog eens goed over moeten nadenken. Maar de meeste kalenders tonen gegevens van een groep gebruikers en dat zijn er misschien 100 in extremis en al de meeste gebruikers hebben per dag hooguit een handjevol afspraken. Een loodgietersbedrijf met 250 werknemers die allemaal vier klussen per werkdag doen levert een jaaroverzicht van 260k op, dat is wel te doen.

Q: Waarom Python en niet iets anders, PlPgSQL bijvoorbeeld?
A: Python is sterk in dit soort zaken, de library wordt onderhouden door de Python groep zelf en PlPython is standaard beschikbaar in elke PostgreSQL installatie.

Q: Waarom niet gewoon een Python script om alles te doen, waarom nou weer de database erbij betrekken?
A1: Omdat afspraken in een kalender een verzameling datums is en databases zijn gemaakt om met verzamelingen te werken.
A2: Omdat de functionaliteit nu gebruikt kan worden door elke app of API die met de database verbindt, het hoeft niet voor elke app apart te worden geschreven

Q: Hoe ga je bugs in PlPython code fixen?
A1: Dat valt buiten de scope van dit artikel, maar het is Python dus je kunt b.v. interface maken in PlPython die alleen weet hoe hij data van PostgreSQL moet doorgeven aan een custom library (PIP is goed in versiebeheer) en de resultaten kan teruggeven aan PostgrSQL. De library kun je in je IDE ontwikkelen tot je een ons weegt.
A2: PlPython bevat een setje commando's om data te laten loggen naar het standaard log van PostgreSQL, zie de utility functions.

Q: Hoe doe je versiebeheer van stored functions?
A: Ook dit valt buiten de scope, maar hiervoor heb je in PostgreSQL bijvoorbeeld schema's. Doe maar even googlen.

Concluse tot nu toe

Het werkt.

Wat nu?

De booked_users tabel kan natuurlijk worden samengevoegd met de kalender want elke boeking is een uitzondering op een beschikbaarheidsrule, waarom twee records aanhouden als één volstaat.

Er zal wel een optimalisatie moeten worden toegevoegd om uit de kalender records te kunnen overslaan als ze nog moeten beginnen, of al afgelopen zijn. Alleen de lopende afsprakenseries zijn van belang voor het overzicht dat je maakt.