News:

Want to get involved in developing SMF, then why not lend a hand on our github!

Main Menu

Repeating Events for Calendar

Started by andershz, April 26, 2012, 04:23:30 AM

Previous topic - Next topic

andershz

I have started a bit of brain storming regarding a possible mod for repeating events in the calendar.

Some things to consider:

  • What recurrence patterns to support
  • How to store the pattern in the database
  • How to match a pattern against a given date

1: Current idea is to base it on the patterns in Microsoft Outlook. See attachment 1.

2: To keep compatibility with calendar blocks in portals etc I don't want to touch the standard calendar database table.
Instead the plan is to use a separate table for the patterns, where an id field points to the main calendar event.
The pattern will know which event it belongs to, but the event will have no knowledge of any pattern connected to it.
Apart from the id field I think the following fields might be used: start date, end date, number of repeats, the interval (every nth day/week/month), and a bitmask.
End date and/or number of repeats may be empty, if both are empty the event repeats forever.
If the user enters the number of repeats rather than the end date it's probably best to calculate the end date and store it in the database when the pattern is created.
The number of repeats should be stored as well, to be able to edit the pattern later.
The bit mask could be a 64 bit unsigned bigint , it appears all supported databases (mysql, postgresql and sqlite) has this data type.
PHP may not necessarily support 64 bit integers, (and never unsigned integers), so some special care is needed there.
The bit mask could look like attachment 2.

3:To match a given date against a pattern the first step would be a coarse filter with a SQL query something like:
SELECT id_event, start, interval, mask & 3 as ly_type FROM patterns WHERE start <= [curdate] AND end >= [curdate] AND mask & [curmask] = [curmask];
The curmask value is calculated for each date to indicate the month, day within month, day within week etc.
Then start, interval and ly_type would be used to further calculate if there is a match.

Any comments?
Note that this is just some initial thoughts, actual code is quite far away and may never be a reality.

stefann

This is a great idea, it's another project I've had in mind that I wanted to get going that may not happen either, but this is a good base to start from

I'd suggest a database layout similar to the following, which seems to be what you've done already, with the first 3 columns in your bit mask

frequency   daily/weekly/monthly/yearly
offset      first/second/third/fourth/last
day      day/weekday/weekend/sunday/monday/tuesday/wednesday/thursday/friday/saturday OR 1-31
spacing      every n $freqs OR month(n) for freq:yearly
start      date
occurrences   n OR NULL for specific date
end      date OR NULL for no end


Some things that should probably be considered
1) which of the "day" settings need to allow multiple selections
2) whether a bit mask is the most efficient way to do this for processing as well as storage, and if there's an easy way to replicate mysql's ENUM & SET functionality to other supported databases
3) how it could be possible to delete or reschedule a single event that belongs to a recurring pattern (eg if it falls on a public holiday etc)

I'd be happy to help out with this project, but my time is limited and unpredictable, and this isn't a major priority for my sites yet
full time lurker on freenode #smf

Arantor

Quotewhether a bit mask is the most efficient way to do this for processing as well as storage

In MySQL, the answer is no, it is not particularly efficient because MySQL doesn't know it's being used as a bitmask and as such no index will be able to handle that value - so every query has to examine every row.

Quoteand if there's an easy way to replicate mysql's ENUM & SET functionality to other supported databases

Or the mod could just 'works on MySQL only'. The population using either PostgreSQL or SQLite is surprisingly small.

Advertisement: