-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Allen Fowler wrote: > I'm looking for suggestions on how to store and retrieve events for a > calendering system in SQlite.
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). Separately from that you store a list of exceptions. Each exception points to an id of an event, the date/time of the exception and then describes the exception. Options are cancelling the event (eg a public holiday) or overriding it such as with a new time, description or participants. 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. > 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. When letting users edit events you'll need to ask if they are changing one repeating instance (ie create an exception) or the core repeating event. This approach stores the minimum amount of data and makes you do the (tedious) calculations. You could do a space vs time tradeoff by having a table with the calculations already done for each day although you'll have to be very careful to ensure it stays in sync. Roger -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.9 (GNU/Linux) iEYEARECAAYFAkox7mkACgkQmOOfHg372QT/lgCgrz61537JtBYsuezJYAmfYhEY dhQAn2Ud9GXlVFIzmg0XlA4I6+wF5na7 =7b37 -----END PGP SIGNATURE----- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
