Hello Darrin,
I recently implemented what I would consider the "hard part" of a
solution to this using Perl and Postgres. My solution handles multi-day
events and recurring events, including events that are both multi-day
and recurring. Here's an overview of how I did it:
A table called "calendar" has just one column, "date". I inserted
10,000 rows into the table, one for every day starting a couple of years
back and going _way_ into the the future. This is so that when I
construct a SELECT statement to say "show me every day in May, 2002",
I get back a row for every day, regardless of whether or not there was
an event.
A second table "events", holds my events including
an event_id, and start and end dates and times. There is one row for
each event, no matter if it recurs or is multi-day.
A third table "events_calendar" is built based on the "events" table.
In this table, a row is inserted for every day that an event occurs. So
if an event spans 3 days and occurs a total of 3 times, there are 9 rows
added to this table. For recurring events, the start and end dates and
times are adjusted to be "local" to this occurance, not the original
start date and time. In addition to the fields contained in the "events"
table, the events_calendar table also has "date" column to denote which
date is being refered to. Now with a simple SELECT statement that joins
the calendar table with the events_calendar table, I can easily build a
public view of the data with events appearing on as many dates as they
should.
On the administrative side, I have a few functions to make this work:
- a function to build the entire events_calendar table initially
- some functions to handle inserting events into events_calendar
- some funcions to handle deleting events from events_calendar
When I make an insert in the events table, I run the functions to create
the inserts for the events_calendar. When I delete from the events
table, the related rows from events_calendar table get deleted.
When updating the events table, I delete from events_calendar, and then
re-insert into it. I'm sure this piece could be done with triggers, but
I'm much better at writing Perl, so I did it that way. :)
I've been happy with this solution. I think the Perl turned out to be
fairly easy to understand and maintain, the SQL that needs to be used
ends up being fairly straightforward, and the performance is good
because the selects to view the calendar are fairly simple. The one
drawback is that sometime before 2028, I have to remember to add some
rows to the calendar table. :)
-mark
http://mark.stosberg.com/
On Tue, 20 Aug 2002, Darrin Domoney wrote:
> One of the features that I am attempting to implement in the system that I
> am building is
> the capability to schedule events (with recurrence). My question to those of
> you that are
> more experienced in postgresql is whether you would implement this
> functionality in the
> database level using triggers or at the application code level (PHP).
>
> Ideally I would like to be able to generate an event off a template
> appointment (initial appt)
> and have it schedule identical appointments hourly, daily, weekly, monthly,
> or by selectable
> range (ie: first tuesday every month). I would also like to have the
> capability to remove an
> appointment and leave others (much like using C pointers - I think)or to
> remove all
> (cascading delete).
>
> Any suggestions, etc gratefully appreciated.
>
> Darrin
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
