Introduction
To prepare for RC1, I have to submit the latest database changes for this release. The objective of these changes is specifically to add the management of repeated events to a simple agenda system that doesn’t have them.
So, what’s the big deal? Well, there are two ways I was thinking repeated agenda events could be handled…
- create as many events as the repetition needs
- add repetition data to the original event to say “this is a repetitive event and it repeats this way…”
Let’s have a look at those two possible ways to do it
“Create many events” solution
The solution seems easy, after all. You just create a series of events and limit the final date to something not too far away, and there you go, you can continue using your system as usual.
There are two shortcomings to this system:
- In some cases, you will get *many* events which, if they contain a long description, can eat up your disk space fast (imagine you attach a 30K picture to the event and repeat it every week for the next 10 years, that’s 15MB of data for a simple illustrated event - and 30K is *not* much)
- How do you know which event was the first one? And so how do you keep a relationship between the “father” event and its offsprings? And so how do you delete the repeated event if you realise you made a mistake?
If point 2 can easily be avoided by adding a “parent” field to my table structure, and sticking it to each offspring, problem 1 isn’t easily solved.
“Add repetition expression to the original event” solution
If this solution removes the problem 1 from the previous idea, it introduces a few additional flaws:
- How do you tell, from a simple week view, if there is a repeated event today or not?
- How do you structure the information related to the “repetition” information?
- How do you remove one occurrence of the repetition (say one weekly event is cancelled because of the host being in hospital)?
None of these problems is solved easily…
Fortunately, there is a reference: WebCal! WebCal is *the* open-source software that deals with calendars and events of any kind, so it has to have a good solution for us, hasn’t it?
The WebCal solution
WebCal took the second idea but, probably after realising the three shortcomings, had a brilliant idea: store the repeated event’s information into a separate table.
This way of doing things, if implemented properly, ensures:
- It is easy to retrieve repeated events as it’s another table, with a much smaller amount of events (however, calculating daily events from repetition metadata might be resource-consuming)
- We can store the repetition information in the new table, with a proper way to structure this data (and to store it, retrieve it, update it)
- We can use *another* table again to store the exceptions to the repetition cycle
- There is no modification to the existing events table
In fact, one the of the most annoying elements of the second solution was that if a lot of events were recorded in the calendar, retrieval of repeated events would potentially highly suffer from other “noise” data.
Having them in a separate table will ensure we only get a few (after all, you can’t really repeat a lot more than 10 events per week, as it will fill your week already!) and our queries will be much faster to check if there is a repeated event today, or this week.
However, this will still use a lot of processing power when calculating when the events are repeated.
This is somehow helped by the fact that they restricted the types of repetition to a minimum of a day, with the following criterias:
daily, monthlyByDate, monthlyByDay, monthlyByDayR, weekly, yearly
Where monthlyByDayR means “monthly by day, restricted” to say that it can be only the second Monday of every month, for example.
We will not implement monthlyByDay nor MonthlyByDayR in this first implementation though, so for time reasons, we will only provide daily, monthlyByDate, weekly and yearly at first.
Implementing it inside Dokeos
So now we have the idea, let’s implement it inside Dokeos.
In Dokeos, we have a *very* simple calendar application. Basically, you add an event, it has a start time, an end time and a description. The table definition looks like this:
CREATE TABLE course_agenda (
id int unsigned NOT NULL auto_increment,
title varchar(200) NOT NULL,
content text,
start_date datetime NOT NULL default ‘0000-00-00 00:00:00′,
end_date datetime NOT NULL default ‘0000-00-00 00:00:00′,
PRIMARY KEY (id)
)
We also happen to have a personal agenda, which is outside of the course context. The table for this one is defined as:
CREATE TABLE personal_agenda (
id int NOT NULL auto_increment,
user int unsigned,
title text,
`text` text,
`date` datetime default NULL,
enddate datetime default NULL,
course varchar(255),
UNIQUE KEY id (id)
);
(so we have a reference to the user id and the course id here, as well as an additional description text - this structure is very wrong - it wasn’t me, I swear! - and very MySQL-dependent because of the backticks to escape fields that use reserved keywords)
Anyway, so here the idea is that we will have to duplicate the new tables as well, because a personal agenda is global, while a course agenda is restricted to the context of a course.
However, any user can see a screen with the events from his personal agenda *and* the ones from all the courses he’s subscribed to. So even if we don’t have a lot of repeated events, it is still a considerable query we have to do to get them all.
So we have to add, on both sides, two tables with the something approaching the following structure (coming from the WebCalendar code):
CREATE TABLE webcal_entry_repeats (
cal_id INT DEFAULT 0 NOT NULL,
cal_type VARCHAR(20),
cal_end INT,
cal_frequency INT DEFAULT 1,
cal_days CHAR(7),
PRIMARY KEY (cal_id)
);
CREATE TABLE webcal_entry_repeats_not (
cal_id INT NOT NULL,
cal_date INT NOT NULL,
PRIMARY KEY ( cal_id, cal_date )
);
Now we have the structure. How does the interface reflect those additional possibilities?
The insertion side
Well if we keep it simple, we will only have to provide a tickbox to say if this event is repeated or not, and a drop-down list with the three options we are offering: daily, monthlyByDate, weekly or yearly. Finally, an absolute end will have to be given, which we will ask the user to fill in.
This data will then go right to the new database table and, using the current event as a start date, will complete the insertion part of the new feature.
On the querying side though, things are going to be much more complex.
The querying side
Whenever a user goes on a calendar page, he needs to see the repeated events. If a daily view shouldn’t be too complex, a monthly view adds just a little bit to it: we have to check over a wider area.
Let’s start with a daily view. Let’s say we are querying for everything that is happening on the 21st of May. Let’s also say we are only looking at the personal agenda side.
The first thing we do is check for normal events. No worries there, we already do that.
Then, we want to check for repeated events. Because we have a separate table for these, we can easily check all the repeated events that:
- have a start date anterior, or equal to the 21st of May ($may21start = mktime(0,0,0,5,21,date(’Y'));)
- have an end date posterior, or equal to the 21st of May ($may21end = mktime(0,0,0,5,21,date(’Y'))-1;)
Once we have those (let’s say we have only one), we start with the tricky part: calculating whether the repetition occurs today or not. We will use PHP dates massively here to provide an effective date calculation.
As previously mentioned, we have only four types of repetition here, so we can find a formula for each of these types (forgetting for now about exceptions), and create a function for each:
daily
Well, it will happen today considering the conditions above are met, that’s for sure, so consider it part of today’s view. You will still have to calculate the starting and ending time of this new event though. That would look as something like this:
$time_orig_h = date('H',$orig_start);
$time_orig_m = date('i',$orig_start);
$time_orig_s = date('s',$orig_start);
$int_time = (($time_orig_h*60)+$time_orig_m)*60+$time_orig_s; //time in seconds since 00:00:00
$span = $orig_end - $orig_start; //total seconds between start and stop of original event
$current_start =$start + $int_time; //unixtimestamp start of today's event
$current_stop = $start+$int_time+$span; //unixtimestamp stop of today's event
weekly
$time_orig = date('Y/n/W/j/N/G/i/s',$event_start_time);
list($y_orig,$m_orig,$w_orig,$d_orig,$dw_orig,$h_orig,$n_orig,$s_orig) = split('/',$time_orig);
$time_now = date('Y/n/W/j/N/G/i/s',time());
list($y_now,$m_now,$w_now,$d_now,$dw_now,$h_now,$n_now,$s_now) = split('/',$time_now);
if((($y_now>$y_orig) OR (($y_now == $y_orig) && ($w_now>$w_orig))) && ($dw_orig == $dw_now))
{
$time_orig_end = date('Y/n/W/j/N/G/i/s',$event_end_time);
list($y_orig_e,$m_orig_e,$w_orig_e,$d_orig_e,$dw_orig_e,$h_orig_e,$n_orig_e,$s_orig_e) = split('/',$time_orig_end);
add_event_to_view(
$event_title,
mktime($h_orig,$n_orig,$s_orig,$m_now,$d_orig,$y_now),
mktime($h_orig_e,$n_orig_e,$s_orig_e,$m_now,$d_orig_e,$y_now)
);
}
monthlyByDate
$time_orig = date('Y/n/j/G/i/s',$event_start_time);
list($y_orig,$m_orig,$d_orig,$h_orig,$n_orig,$s_orig) = split('/',$time_orig);
$time_now = date('Y/n/j/G/i/s',time());
list($y_now,$m_now,$d_now,$h_now,$n_now,$s_now) = split('/',$time_now);
if((($y_now>$y_orig) OR (($y_now == $y_orig) && ($m_now>$m_orig))) && ($d_orig == $d_now))
{
$time_orig_end = date('Y/n/j/G/i/s',$event_end_time);
list($y_orig_e,$m_orig_e,$d_orig_e,$h_orig_e,$n_orig_e,$s_orig_e) = split('/',$time_orig_end);
add_event_to_view(
$event_title,
mktime($h_orig,$n_orig,$s_orig,$m_now,$d_orig,$y_now),
mktime($h_orig_e,$n_orig_e,$s_orig_e,$m_now,$d_orig_e,$y_now)
);
}
yearly
$time_orig = date('Y/n/j/z/G/i/s',$event_start_time);
list($y_orig,$m_orig,$d_orig,$dy_orig,$h_orig,$n_orig,$s_orig) = split('/',$time_orig);
$time_now = date('Y/n/j/z/G/i/s',time());
list($y_now,$m_now,$d_now,$dy_now,$h_now,$n_now,$s_now) = split('/',$time_now);
if((($y_now>$y_orig) OR (($y_now == $y_orig) && ($m_now>$m_orig))) && ($dy_orig == $dy_now))
{
$time_orig_end = date('Y/n/j/G/i/s',$event_end_time);
list($y_orig_e,$m_orig_e,$d_orig_e,$dy_orig_e,$h_orig_e,$n_orig_e,$s_orig_e) = split('/',$time_orig_end);
add_event_to_view(
$event_title,
mktime($h_orig,$n_orig,$s_orig,$m_now,$d_orig,$y_now),
mktime($h_orig_e,$n_orig_e,$s_orig_e,$m_now,$d_orig_e,$y_now)
);
}
For a one-day view, we will use the four formulas as switches inside a checker function, repeat_check_today(), that will either get the event start time and end time or input them directly into some kind of session array, or even print them to screen.
That’s about the same idea for week and month views, so I won’t describe them right now. I didn’t dive into the Dokeos code much, sorry about that.
Yes, OK, but hold on just a second…
I don’t know what you think about all this, but I thought it was a mind-buggingly complicated set of algorithms (and they are even simpified in this example), so I spend a few *hours* trying to make everything perfect, and then I tried it… awfully slow.
And after one more hour of thinking, I finally got back to the first solution. Well, not completely. I mixed the first, the second and the third, so what I have now is this final solution (that works, that is fast and easy to implement).
The chosen solution: a little bit of everything
In short, I add a “parent_event_id” field to the initial agenda table, so I can create any number of events that *depend* on a first parent.
Then I keep my additional tables repeat and repeat_not, to be able to store the corresponding expression that explains how the repetition works, and when there is an exception.
When inserting, I just offer the possibility to repeat the event at a certain frequency until an end date. This triggers the insertion of one parent event (nothing special, nothing in the parent_event_id field), and then I parse the frequency and
- record the repetition expression in the “agenda_repeat” table
- create as many repeated events as asked for in “agenda”, each of them having a parent_event_id of the parent event
When querying…
This means that there is very little to change to the way my application works in order to use those repeated events: they are just displayed as normal events.
If you want to make it possible to get to the parent event, you can check if the parent_event_id is not null, and then display a link to the parent event.
When editing, you actually never offer the possibility to “edit” an event “repetition-wise”, because it would be very difficult to handle. If the user has made a mistake, he can just delete the parent event and all other events will be deleted, and then he can create another one that fixes the previous problem.
If you want to edit one of the child events, you can, it is just a copy related to the parent, but then you can mention specific information about one day’s event.
When deleting, I have left it very limited so far, so you cannot delete one single child event (I am not yet handling repetition exception). You have to mention something in the event’s description to say it’s been cancelled on that specific day (this exception isn’t preserved in iCal exports though).
If you delete the parent event, all children will be deleted as well, whether they have been changed or not.
Conclusion
Although the second solution seemed clean enough, it added a lot of problems of integration with the agenda already there in Dokeos 1.8 (which is quite bad in itself, I must admit).
It also prevents the alteration of any of the event’s repetition, but considering the fact that it is a repeated event and that it has to offer export features (to iCal, for example), there must be a way to express clearly how it is repeated, so we definitely need to have that information. Clicking on one repeated item has to show the details of the original item with an updated time information, and cannot allow for edition (otherwise it’s not clear what we are editing).
There is definitely still room for improvement in the interface provided to the user, but I am quite convinced that structure put in place allow for *any* kind of change now.
If you wanna try out Dokeos 1.8.5 (in beta version at time of writing), head to http://demo.dokeos.com/
No Comments » | 
Dokeos, General, e-learning | Tagged: claroline, code analysis, Dokeos, ganesha, ilias, moodle | 
Permalink
Posted by ywarnier
