I need to incorporate appointment calendaring into my app, including recurring events (like "every other Thursday" or "annually on July 17th"). Before I get into this, I want to make sure I'm not reinventing the wheel cuz while the one-shot appointments are straightforward, synthesizing appointments from recurrent events is more work.
Anyone doing something like this want to share some insights?
Thanks
I need to incorporate appointment calendaring into my app, including recurring events (like "every other Thursday" or "annually on July 17th"). Before I get into this, I want to make sure I'm not reinventing the wheel cuz while the one-shot appointments are straightforward, synthesizing appointments from recurrent events is more work.
Anyone doing something like this want to share some insights?
Sure, I just implemented something like this. I was completely at sea as to how to do it efficiently until I hit upon this solution - create a table of dates, or a stored procedure which enumerates dates given a range, against which you can join. I used the former since I needed to flag our holidays as special dates anyway. I ended up with four tables:
events - event metadata library_dates - date enumeration occurrences - specific dates on which events occur recurrences - patterns by which events recur
I then created a view titled "events_library_dates" which uses the data in these tables to generate what looks to rails like a normal HABTM join table for events and dates. There are one minor gotcha I've run across with this approach so far - when I destroy an event, AR tries to destroy the rows in the "join table", which of course it cannot, but I tagged the HABTM relation with a noop sql statement in the :delete_sql option and everything's hunky dory again.
(It would be better to tag the relation itself as read-only, but AR doesn't seem to support that; I've filed an enhancement request for this at http://dev.rubyonrails.org/ticket/8045 just for grins, I'm sure the ticket sit there gathering dust unless and until I'm able to scratch my own itch.)
Anyway, that's the gist. For the record, my recurrences table has three columns:
monthday weekday monthweek
so it only handles cases like "the 23rd of every month", "every wednesday", "3rd wednesday of the month", and "last wednesday of the month". Expanding it to accomodate annually recurring events or whathaveyou should be relatively straightforward.
The events_library_dates view looks like:
CREATE VIEW events_library_dates AS SELECT ld.id AS library_date_id, e.id AS event_id -- select all dates FROM library_dates ld -- multiply by all events LEFT OUTER JOIN events e ON 1=1 -- decorate with recurrences LEFT OUTER JOIN recurrences r ON r.event_id = e.id AND ((r.monthday IS NOT NULL AND DATEPART(dd, ld.start_date) = r.monthday) OR (r.monthday IS NULL AND r.weekday IS NOT NULL AND DATEPART(dw, ld.start_date) = r.weekday AND (r.monthweek IS NULL OR (r.monthweek > 1 AND DATEPART(dd, ld.start_date) BETWEEN 7*(r.monthweek-1) + 1 AND 7*(r.monthweek-1) + 7) OR (r.monthweek = -1 AND DATEPART(dd, ld.start_date) BETWEEN DATEPART(dd, DATEADD(dd,-1,DATEADD(mm,DATEDIFF(mm,0,ld.start_date)+1,0)))-6 AND DATEPART(dd, DATEADD(dd,-1,DATEADD(mm,DATEDIFF(mm,0,ld.start_date)+1,0)))) ) )) -- decorate with occurrences LEFT OUTER JOIN occurrences o ON o.event_id = e.id AND o.library_date_id = ld.id -- remove holidays WHERE ld.holiday = 0 AND -- and dates before the start date, if any (e.start_date IS NULL OR (e.start_date IS NOT NULL AND ld.start_date >= e.start_date)) AND -- and dates after the end date, if any (e.end_date IS NULL OR (e.end_date is NOT NULL AND ld.start_date <= e.end_date)) AND -- and dates with no events (r.id IS NOT NULL OR o.id IS NOT NULL)
the date functions are sqlserver-specific, but analogues should exist for whatever database you're using.
The view logic is a bit hairy, but it performs well and is neatly encapsulated. I'm curious if other folks think this is a decent approach or if I'm overlooking something.
- donald
Wow! Thanks. I'll look at this approach. Do you suppose has_many :through would alleviate the problem you where having with deleting rows you wanted to preserve?
--steve
Ball, Donald A Jr (Library) wrote:
Wow! Thanks. I'll look at this approach. Do you suppose has_many :through would alleviate the problem you where having with deleting rows you wanted to preserve?
I doubt it; I don't in fact see how a HMT relationship would fit in here.
- donald
> Wow! Thanks. I'll look at this approach. Do you suppose > has_many :through would alleviate the problem you where > having with deleting rows you wanted to preserve?
I doubt it; I don't in fact see how a HMT relationship would fit in here.
- donald
You might want to look at Runt. http://runt.rubyforge.org/
Runt is Ruby Temporal Expressions, based on the pattern Temporal Expressions by Martin Fowler. (See links off their website.) This library handles many common things like everu last Monday in April AND every fourth (or last) Tuesday of any month through the use of set expressions.
Now, I am considering using this for a class in May and rapping a Rails app around it. You want to stay away from storing actual infinitely recurring dates in a table. But you can store a Runt expression and then when the calendar is viewed, it would show up.
Ed
You might want to look at Runt. http://runt.rubyforge.org/
Runt is Ruby Temporal Expressions, based on the pattern Temporal Expressions by Martin Fowler. (See links off their website.) This library handles many common things like everu last Monday in April AND every fourth (or last) Tuesday of any month through the use of set expressions.
That's a really nifty library, thanks for the pointer.
Now, I am considering using this for a class in May and rapping a Rails app around it. You want to stay away from storing actual infinitely recurring dates in a table. But you can store a Runt expression and then when the calendar is viewed, it would show up.
A downside of storing recurrences as runt expressions is that you have to interpret them in the application layer, which is slow and prohibits alternate use of the database. The only real downside I foresee with doing the recurrence logic in a database view is the need to have either a table of enumerated potential dates, which must of course be maintained over time, or a stored procedure which can generate the same.
Do you see other reasons to stay away from storing recurring date patterns in a table?
- donald
A downside of storing recurrences as runt expressions is that you have to interpret them in the application layer, which is slow and prohibits alternate use of the database. The only real downside I foresee with doing the recurrence logic in a database view is the need to have either a table of enumerated potential dates, which must of course be maintained over time, or a stored procedure which can generate the same.
The maintained over time was the reason. Your point is valid about alternate use of the table. Was not an issue in my case.
Do you see other reasons to stay away from storing recurring date patterns in a table?
I actually want to store real events in the table, not potential ones. So if there is a meeting that occurs on the first Monday of the month at 9:00 a.m., it isn't real until I see it or change it somehow. I checked Google Calendars for the year 2025 and all my recurring events were there. I am sure they didn't store an infinite # of events for every user.
If I store a discrete event in the table, that is fine. The two views of the table overlay each other. If I modify a recurring event, then I store the difference (delta) in the database. If it is just a change in details, like time or place, then it becomes a discrete event*. This works a little like the Time Zone database in Unix. Remember the DST change this year?
This problem is a like infinite series problems in CS. Or sparse arrays, lazy evaluation etc. Maybe the calendar is partially virtual. You don't actually process it until you need it. Handles email reminders because that is an external event that triggers a view of the calendar for the upcoming day.
You are right that it is an application layer (with possible performance issues.) In my case the app layer was all I needed.
Ed
* The discrete events are the topmost layer.
This looks very promising. I don't need recurring appointments etched in the database -- I'd be surprised if iCal or Outlook do this. So, to create a calendar, I envision this kind of thing:
=> create a series of time slots (say 48) that represent a day => sift fixed appointments for a given user for today and slot them into these buckets => select runt_events that belong to User A and screen them to see if today is a member of these events => with screened runt_events, allocate them out to the buckets as well, one at a time
Does this make sense?
Ed Howland wrote:
Totally.
That is similar to how I plan to do it for my class. In my case, I am treating the slots (i call them openings) as a single field (initially non-existant) As each event is saved (or if it is a runt virtual event,) it divides the opening into two parts, the part before the event and the part after. My users can only create a new event on an existing opening. It will either take up the entire opening or divide it into 3 parts: the part before the event, the event and the part after the event. Either the part before or after or both can be eliminated. I think, though I've not proven it yet, that Runt's DateRange class will suffice.
This is real sketchy at the moment, but that is the direction I am contemplating. Anyway, this seems to eliminate any collisions between runt recurring events and new events generated. The user will have to edit/delete the runt generated event for that day to create a new opening. When that happens, the runt event becomes a discrete event and overlays any Runt one. (Thats the part I haven't worked out yet.)
In the end you need storage for runt expressions, and discrete events. I think.
Ed
Keep up to date with Rails on Twitter and This Week in Rails
Policies: Conduct, License, Maintenance, Security, Trademarks