Forum: Ruby on Rails Appt. Calendaring

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Steve R. (Guest)
on 2007-04-12 00:07
(Received via mailing list)
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....
Sent from the RubyOnRails Users mailing list archive at Nabble.com.
Ball, Donald A Jr (Library) (Guest)
on 2007-04-12 01:23
(Received via mailing list)
> 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
Steve R. (Guest)
on 2007-04-12 02:41
(Received via mailing list)
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....
Sent from the RubyOnRails Users mailing list archive at Nabble.com.
Ball, Donald A Jr (Library) (Guest)
on 2007-04-12 18:57
(Received via mailing list)
> 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
Ed Howland (Guest)
on 2007-04-12 21:35
(Received via mailing list)
On 4/12/07, Ball, Donald A Jr (Library) <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/

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
http://greenprogrammer.blogspot.com
Ball, Donald A Jr (Library) (Guest)
on 2007-04-12 21:46
(Received via mailing list)
> You might want to look at Runt. 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
Ed Howland (Guest)
on 2007-04-12 22:26
(Received via mailing list)
On 4/12/07, Ball, Donald A Jr (Library) <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
http://greenprogrammer.blogspot.com
Steve R. (Guest)
on 2007-04-13 03:51
(Received via mailing list)
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
>
> >
>
>

--
View this message in context:
http://www.nabble.com/Appt.-Calendaring-tf3561717....
Sent from the RubyOnRails Users mailing list archive at Nabble.com.
Ed Howland (Guest)
on 2007-04-13 05:40
(Received via mailing list)
On 4/12/07, s.ross <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
http://greenprogrammer.blogspot.com
This topic is locked and can not be replied to.