> > I'm looking for suggestions on how to store and retrieve events for a
> > calendering system in SQlite.
> >
> > For each user there must be:
> >
> > 1) All day events on a specific day.
> > 2) All day events that are repeated over a given date range.
> > 3) All day events that are repeat each day from until canceled.
> >
> > 4) Time specific events on a specific day.
> > 5) Time specific events that repeat daily over a given date range.
> > 6) Time specific events that repeat each day from now until canceled.
> >
>
> The general way this is done, and especially if you want to be
> compatible with standards such as vcal/ical is to store events with a
> repeating rule. The rule may be something like the 1 st of each month
> or every Friday. You also store the begin date/time and end date/time
> (the end could be blank or some date far in the future).
>
>
> See http://en.wikipedia.org/wiki/ICalendar
>
> > 1) How should I be structuring the DB so to store this data sanely.
>
> The above is the easiest way if you want to do imports and exports.
>
I'm not sure what you mean....
The icalender format is rather complex, and I don't see an obvious mapping to
SQL table design. (Even for the small subset I need.)
> > 2) How should I query the DB for a list of events that are scheduled at any
> > given moment. (across all users.)
> > 3) How should I query the DB for a list of events that are scheduled in any
> > given time interval? (across all users.)
>
> Those are pretty much the same thing. The simplest way is to read in
> all events that have a begin date before the interval and an end date
> after the interval, plus all exceptions that apply to them.
>
> In terms of what happens next, my solution was for a particular date to
> calculate all entries that apply (using repeating rules and exceptions).
> One you then have the set of entries for the day you can decide how to
> proceed with them (for example sort by start time) and what to do about
> overlapping ones. Also note entries from the previous day may apply
> such as if the event started before midnight and finished afterwards on
> the calculation day.
>
Where is this calculation being done? In SQL? At the app level? How?
Is there an SQL query that can retrieve a list of events for a certain time, or
this not doable sanely in SQL?
Thank you,
AF
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
