[geeklog-devel] Calendar Plugin - Recurring Events

[Vincent Furia]

Like Joe, I had looked at adding recurring events to Geeklog in the past.
All the research I did indicated that the only efficient way to handle
recurring events to instantiate individual events. At the time I came up
with an idea for representing the events in the database. There would be two
tables, a recurring events table and an events table.

The recurring events table would be similar to the events table, but in
addition would hold information about how the events recur. It would also
contain a "begin" and "end" dates for when individual events had already
been generated. When a query is made to view events, it would check the
recurring events table to see if that date range had been generated for the
recurring events that apply to the query. If the query was outside the date
range, more events to satisfy the query would be generated. (It would be
necessary to somehow represent that all early and/or all late dates had
already been generated).

The events table would contain a foreign key or NULL to the recurring events
table so events can be associated with the "parent" recursion. Also a field
in the event table would indicate if the event was an exception, so changes
could be made to individual events that are part of recursion.

I'm not particularly attached to this implementation, but this entire
discussion on recurring events is a good one to have to better flesh out
design and implementation ideas.

Be careful depending on the iCal standard to define how recursion works in
your implementation. The iCal recursion rules are VERY flexible and fully
implementing them can be crazy difficult (not even Outlook can generate all
the types of recursions that are supported by iCal).

Vlad, I was intrigued by postgresql recurring events solution you found. I'm
not sure an entirely SQL implementation like that would work too well in
Geeklog. Mostly because MySQL (the primarily used database) doesn't have
nearly as efficient SQL functions as postgresql. I can be convinced
otherwise though if that's the route someone wants to go down...

Another difficult part of implementing recurring events is creating a GUI
that is flexible in their creation without being confusing to the user. This
is another problem with trying to implement the full recursion abilities of
the iCal standard.

There are many implementations that will work. But there are even more that
won't scale at all on a large site with lots of recurring events.

I'm looking forward to reviewing proposals for the Calendar plugin.  On a
side note, I can't review google docs proposals from work, so they'll have
to wait til tonight to be looked at (~0300 GMT).

-Vinny

On Thu, Apr 1, 2010 at 10:31 AM, Vlad Voicu <vladvoic at gmail.com> wrote:

> On Thu, Apr 1, 2010 at 4:38 PM, Samuel Leathers <sam at theleathers.net>
> wrote:
>
> >
> > 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
>
> I think this is the right way also.
>
> > 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.
>
> You can check my application, I suggested a similar solution. Only
> generating for
> 2 years ahead. I also proposed a minimal database structure in my
> application and
> a solution for exceptions.
>
> I don't think a cronjob is the best approach, but generating when a
> user reaches the
> end of the half(as I suggested) or the end of pre-generated period
> like you suggested.
> I found interesting information about this matter here: [1]
>
> > 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).
>
> I think that just a field of Remind_me and one of remind_me_value will
> suffice.
> Once a day the function will look in the database for events of tomorrow,
> if there are any events marked as remind_me and the remind_me_value is
> 1 it will send
> the emails with the .ics attached. If the user selects remind me one
> week before, the
> script will look if 7 days ahead of this day is any event that has the
> remind_me field true and the
> remind_me_value = 7. That is pretty fast I think as you only check 1
> day for events maybe 5
> times for 5 different type of remind_me_value values. x probably equal
> to 3 (remind me a day
> before 2 days before, etc) This can be done with a cronjob on a
> certain hour of a day.
>
> >
> > Just some thoughts. Wondering what everyone thinks, especially Vlad,
> > since he's writing a proposal for this plugin.
> >
>
> [1]
> http://www.justatheory.com/computers/databases/postgresql/recurring_events.html
>
> I expect an opinion also,
> Regards,
> Vlad.
> _______________________________________________
> geeklog-devel mailing list
> geeklog-devel at lists.geeklog.net
> http://eight.pairlist.net/mailman/listinfo/geeklog-devel
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://pairlist8.pair.net/pipermail/geeklog-devel/attachments/20100401/5f6c4fbe/attachment.html>