PHP+CalDav+ICS Room Timeslot algorithm?

Question

I am currently working on a booking site. I am using PHP Baikal CalDav Server (modified it). The ical file generated by Baikal stores the VCALENDAR (each event) file in a single column as medium blob in mysql, when generating it via Mozilla Thunderbird or iCal. Furthermore my bookings on my site are stored as VCALENDAR file, too.

If I want to check if a room is available, I have to fetch every single VCALENDAR file matching to the room_id and extract information out of VCALENDAR. I don´t know if this operation is too costly. I try to "clean" the database from VCALENDARS are already used and not recurrent.

I have to open the room_id opening hours VCALENDAR -> extract data with Sabre process

For Loop through Exception VCALENDAR -> extract data with Sabre process

For Loop through Baikal VCALENDAR -> extract data with Sabre process

For Loop through Bookings VCALENDAR -> extract data with Sabre process

-> Process All -> Return Result, if room is free in the given time.

Are their any smart algorithms, that check for the room availability in a given timeslot, when having the ics files (opening hours, exceptions, bookings, baikal reservations) ?

Thanks!

Answer 1

Query SabreDAV directly. It's highly optimized for time-based queries. If you do a calendar-query REPORT HTTP request, and filter for a start and end-time, it will return only events that overlap with that time-range.

You can even ask it to grab all events in that timerange, and turn it into a VFREEBUSY report, which is basically a simplified VCALENDAR that lists exactly which timeslots are open and not open.

You can find more information about these reports in the CalDAV specification