Thread: Event recurrence - in database or in application code ????

Event recurrence - in database or in application code ????

From
"Darrin Domoney"
Date:
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



Re: Event recurrence - in database or in application code ????

From
Mark Stosberg
Date:
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
>




Re: Event recurrence - in database or in application code ????

From
Robert Treat
Date:
On Tue, 2002-08-20 at 22:15, Mark Stosberg wrote:
> 
> Hello Darrin,
> 
<snip>
> 
> 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. :)
> 

You need to add rows as well as re-populate a bunch of info for
recurring dates that are not listed forward right?

>   -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).
> >
<snip>
> >
> > Any suggestions, etc gratefully appreciated.
> >

I would strongly recommend you look at the "WebCalendar" project on
sourceforge. 

Robert Treat





Re: Event recurrence - in database or in application code ????

From
Mark Stosberg
Date:
On 21 Aug 2002, Robert Treat wrote:

> On Tue, 2002-08-20 at 22:15, Mark Stosberg wrote:
> >
> > Hello Darrin,
> >
> <snip>
> >
> > 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. :)
> >
>
> You need to add rows as well as re-populate a bunch of info for
> recurring dates that are not listed forward right?

Perhaps this will answer your question Robert-- one point I didn't
mention before is that I don't allow events events to recur forever,
they have end after some finite number of times. You could add a
birthday and tell it to repeat it once a year for the next 100 years for
example. I wouldn't have to go and add rows for these later though-- the
rows needed for the next 100 years would already be generated in the
events_calendar table.
The only thing that "expires" with my solution is the dates in the
calendar table. I could make the dates run for the next 100 years just
as easy as 28 years, I just figured the system would probably get a significant
revamp sometime in the next quarter century.  :)
 -mark

http://mark.stosberg.com/