# Appt. Calendaring **URL:** \ **Category:** Rails\ **Created:** [April 11, 2007, 10:07pm UTC](https://www.ruby-forum.com/t/appt-calendaring/91726 "2007-04-11T22:07:43Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![cwd](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/da6949/32.png) [@cwd](https://www.ruby-forum.com/u/cwd)\ **Post date:** [April 11, 2007, 10:07pm UTC](https://www.ruby-forum.com/t/appt-calendaring/91726/1 "2007-04-11T22:07:43Z")
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 View this message in context: [http://www.nabble.com/Appt.-Calendaring-tf3561717.html#a9947388](http://www.nabble.com/Appt.-Calendaring-tf3561717.html#a9947388) Sent from the RubyOnRails Users mailing list archive at [Nabble.com](http://Nabble.com). ---
**Author:** ![cwd](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/da6949/32.png) [@cwd](https://www.ruby-forum.com/u/cwd)\ **Post date:** [April 11, 2007, 11:23pm UTC](https://www.ruby-forum.com/t/appt-calendaring/91726/2 "2007-04-11T23:23:51Z")
> 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](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 ---
**Author:** ![cwd](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/da6949/32.png) [@cwd](https://www.ruby-forum.com/u/cwd)\ **Post date:** [April 12, 2007, 12:41am UTC](https://www.ruby-forum.com/t/appt-calendaring/91726/3 "2007-04-12T00:41:57Z")
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: > > Anyone doing something like this want to share some insights? > > recurrences - patterns by which events recur > > doesn’t seem to support that; I’ve filed an enhancement request for this > > e.id AS event\_id > (r.monthweek IS NULL OR > LEFT OUTER JOIN occurrences o ON o.event\_id = e.id AND o.library\_date\_id > (r.id IS NOT NULL OR o.id IS NOT NULL) > > > – View this message in context: [http://www.nabble.com/Appt.-Calendaring-tf3561717.html#a9949859](http://www.nabble.com/Appt.-Calendaring-tf3561717.html#a9949859) Sent from the RubyOnRails Users mailing list archive at [Nabble.com](http://Nabble.com). ---
**Author:** ![cwd](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/da6949/32.png) [@cwd](https://www.ruby-forum.com/u/cwd)\ **Post date:** [April 12, 2007, 7:35pm UTC](https://www.ruby-forum.com/t/appt-calendaring/91726/4 "2007-04-12T19:35:49Z")
On 4/12/07, Ball, Donald A Jr (Library) [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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 You might want to look at Runt. [http://runt.rubyforge.org/](http://runt.rubyforge.org/) Runt is Ruby Temporal Expressions, based on the pattern Temporal Expressions by Martin F… (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 > > – Ed Howland > **[Green Programming](http://greenprogrammer.blogspot.com)** > > Environmentally safe code ---
**Author:** ![cwd](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/da6949/32.png) [@cwd](https://www.ruby-forum.com/u/cwd)\ **Post date:** [April 12, 2007, 7:46pm UTC](https://www.ruby-forum.com/t/appt-calendaring/91726/5 "2007-04-12T19:46:51Z")
> You might want to look at Runt. [http://runt.rubyforge.org/](http://runt.rubyforge.org/) > > Runt is Ruby Temporal Expressions, based on the pattern > Temporal Expressions by Martin F… (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 ---
**Author:** ![cwd](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/da6949/32.png) [@cwd](https://www.ruby-forum.com/u/cwd)\ **Post date:** [April 12, 2007, 8:26pm UTC](https://www.ruby-forum.com/t/appt-calendaring/91726/6 "2007-04-12T20:26:55Z")
On 4/12/07, Ball, Donald A Jr (Library) [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > 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 C.s 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. – Ed Howland > **[Green Programming](http://greenprogrammer.blogspot.com)** > > Environmentally safe code ---
**Author:** ![cwd](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/da6949/32.png) [@cwd](https://www.ruby-forum.com/u/cwd)\ **Post date:** [April 12, 2007, 4:57pm UTC](https://www.ruby-forum.com/t/appt-calendaring/91726/7 "2007-04-12T16:57:29Z")
> 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 ---
**Author:** ![cwd](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/da6949/32.png) [@cwd](https://www.ruby-forum.com/u/cwd)\ **Post date:** [April 13, 2007, 1:51am UTC](https://www.ruby-forum.com/t/appt-calendaring/91726/8 "2007-04-13T01:51:28Z")
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: > > > > Now, I am considering using this for a class in May and rapping a > > – > Ed Howland > [http://greenprogrammer.blogspot.com](http://greenprogrammer.blogspot.com) > > > – View this message in context: [http://www.nabble.com/Appt.-Calendaring-tf3561717.html#a9970664](http://www.nabble.com/Appt.-Calendaring-tf3561717.html#a9970664) Sent from the RubyOnRails Users mailing list archive at [Nabble.com](http://Nabble.com). ---
**Author:** ![cwd](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/da6949/32.png) [@cwd](https://www.ruby-forum.com/u/cwd)\ **Post date:** [April 13, 2007, 3:40am UTC](https://www.ruby-forum.com/t/appt-calendaring/91726/9 "2007-04-13T03:40:36Z")
On 4/12/07, s.ross [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > 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? 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 – Ed Howland > **[Green Programming](http://greenprogrammer.blogspot.com)** > > Environmentally safe code