[geeklog-devel] Calendar Plugin - Recurring Events

[Samuel Leathers]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


I wanted to maybe start bouncing ideas around about how to implement
recurring events. I started by taking an ical file exported from my
google calendar to get an idea of how it stores it, thinking that might
be a good way to structure the database to make generation of an ical
file simple. Lets start a dialog about how is best to represent these
events. This is a sample event:

BEGIN:VEVENT
CREATED:20100112T184020Z
LAST-MODIFIED:20100228T063110Z
DTSTAMP:20100228T063110Z
UID:msfeaphkg5p9eedmff4ide7bjo at google.com
SUMMARY:RPTM 120
STATUS:CONFIRMED
RRULE:FREQ=WEEKLY;UNTIL=20100430T193500Z;BYDAY=MO,WE,FR
DTSTART;TZID=America/New_York:20100111T153500
DTEND;TZID=America/New_York:20100111T162500
LOCATION:Hosler 026
SEQUENCE:1
TRANSP:OPAQUE
X-MOZ-GENERATION:1
END:VEVENT

The interesting parts are RRULE, DTSTART and DTEND.

DTSTART and DTEND are the start/end times for the first event. RRULE has
a frequency rule, an optional until rule, and in this case a by day
rule. However; looking at the spec:
http://www.kanzaki.com/docs/ical/rrule.html

it can be minutely, hourly, daily, weekly, monthly and yearly. And each
has it's own set of options.

So how do we encode this in the database? Do we have a column for
frequency, until, period, days, etc...

But what about this scenario:

The first Saturday that follows the first Sunday of the month,
 forever:

  DTSTART;TZID=US-Eastern:19970913T090000
  RRULE:FREQ=MONTHLY;BYDAY=SA;BYMONTHDAY=7,8,9,10,11,12,13

How would you arrange that one in the database?

I think having a bunch of columns for recurring events could get real
ugly. My other thought was what if we mirror the ical structure in the
database like so:

columns: dtstart, dtend, rrule

And then write an ical class with a parser for displaying calendar
events. This could be slow (I don't know), but what if we augment this
ical backend in the database with a cache? Ical already supports a UID
field, so what if we cache calendar recurring events (dtstart, dtend no
rrule) in a cache table that expands recurring events for that month and
year (linked to uid of event it was built from). Then when someone views
a future month, it adds that month to the cache, or a crontab could be
set for off-peak hours to rebuild the cache for the upcoming year. The
cache could also contain "ghost" events for reminders for events. So
when someone has a remind me the day before, rather than parsing for the
event, checking if it's the day before, it queries the cache instead,
sees there is an event reminder linked to event uid of future event,
sends that reminder to all users (with an ical file including the event,
so they can import it directly into their calendar from their e-mail).

Just some thoughts. Wondering what everyone thinks, especially Vlad,
since he's writing a proposal for this plugin.

Sam
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.13 (Darwin)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iQEcBAEBAgAGBQJLtKG6AAoJEF7J+Z7XDXx/qG8IAIQIfvui4hPMM8hDSquotF5P
tONF5bxU202cbz9CraHF6VgD4Qe7ch6ACQoAU7l5gOd7OWihied5PUXrKybwspGq
KhCyCX/N3/j0L/cNpNCQMtEvd26CTWGQfo2Z7zmRIP3QOt5vvw4dWbf1TF8fhBTL
0Z+Fj6uwcELpvhvZAYK30+AAMhPpv2Hz10vm9AoWAq72ZpKwkAHNaE4ndwxxcSCM
l4UgTtcDV3pddDUdoPolRwCTZ33YM6iLKUEx0SslPpddX876e7It8rjZKnqmR4vD
c9AXTgbVkvPFCuFaZl9aZke7L3fNnvRKqF157mT8KArvS7nfaD8tbMmEkkwpkIM=
=Uz3t
-----END PGP SIGNATURE-----