View source for Calendar:Sql Calendar Schema

= Calendar Storage Schema =

== Outstanding issues: ==

* Recurrence needs to be changed to support full iCalendar-style recurrence; we can't really get away with a subset
* Indexes need to be defined once we know what the most expensive queries

=== Data types ===

* datetimes will be stored in UTC
* how do we handle local datetimes?  the iCalendar spec has provision for timezoneless-times
* times will be stored as a datetime with a zero date
* dates will be stored as a datetime with a zero time

=== `cal_events` ===

==== Core event ====

* `oid`: unique id
* `hashid`: string hash used as key in IICal interface
* `title`
* `description` -- allow markup?
* `location`
* `categories` -- whitespace separated?
* `time_start` - datetime
* `time_end` - datetime

How do we represent all-day events?  Maybe `time_start` == `time_end` and both have 0 time components?_

* `flags` - private? allday? what else?

==== Alarms ====

* `alarm_data` - alarm data.
* `alarm_time` - datetime when alarm needs to go off

==== Recurrence ====

* `recur_type`
** null if no recurrence
** 1 - daily
** 2 - weekly (recur_data determines day(s) of the week)
** 3 - monthly (recur_data determines which day of the month)
** 4 - yearly (recur_data indicates daymonth)
** 5 - hourly (recur_data determines minute(s) of the hour)
** 6 - minute (recur_data determines second(s) of the minute)
* `recur_interval` - every N (minutes/hours/days/weeks/months/years)
* `recur_data`
** if type 2: bitmask, 0-6 are Su M Tu W Th F S
** if type 3: month number, 1-12 (0-11?)
** if type 4: month (1-12), day (1-7), as (month | (day << 8))
** if type 5: minute number 0 - 59 (64 bitmask value would be best)
** if type 6: second number 0 - 59 (64 bitmask value would be best)

* `recur_end`: NULL if forever, otherweise a datetime; we can calculate the remaining count from this
* `recur_flags`: 0x1: has exceptions (see `cal_event_recur_exceptions` table)

=== `cal_event_recur_exceptions` ===
* `event_id`: references event oid
* `skip_date`: date to skip
 
=== `cal_event_attendees` ===
* `event_id`: references event oid
* `attendee_email`: email address of attendee
* `attendee_status`: status code


=== `cal_event_attachments` ===
* `event_id`: references event oid
* `attachment_type`: mime type of attachment
* `attachment_data`: blob


== Mostafa's comments: ==
* I suggest considering floating (timezoneless) datetimes a bit more seriously since storing all values in UTC conflicts with daylight savings in cases where times are absolute ( i.e. remain the same all year like the event to get up at 7:00 for example ). The UTC value will depend on when the event was created. In other words, if datetimes are stored in UTC, we need a way to define if that value follows daylight saving changes or not. Some events might want to (international meetings) and some events might not (getting up).
:*The offset can be calculated retrospectivley, no? If this is not possible, why not store UTC and the offset, rather than the timezone's time at the moment of storage? I suppose one could store that with the timezone code, but that would seem at least as complicated as the first method. [[User:MrJones|MrJones]] 00:48, 23 Feb 2005 (PST)

*Maybe categories to be comma separated instead of whitespace separated?
*I vote for descriptions supporting markup
*all-day representation by `time_start` == `time_end` == 0 sounds logical to me
*If considering all types of recurrences, would these to be covered with the current schema:
**Durations
**Repeating on last/first/... weekday of months

I hereby apologize if I can't put much time in more in-depth thinking and/or commenting therefore being quick and brief.
The whole direction looks great though.