-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
>>> 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.... Unless your system is going to operate in total isolation from the rest of the world forever, you'll likely eventually get feature requests to provide information in a format that can be fed to or from Exchange, Apple, web services etc. The closer your schema is to what is used in the rest of the world, the easier that import/export will be when the time comes. It will also allow using standard libraries rather than having to reinvent the wheel. > 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.) One table of the events with fields you need (eg description, start and end, repeating rule). A second table with the exceptions, or depending on how much you want to normalize a table per exception type. > Where is this calculation being done? In SQL? At the app level? How? You have to do the calculation at the application level. There are no standard SQL functions for this kind of stuff. > Is there an SQL query that can retrieve a list of events for a certain time, > or this not doable sanely in SQL? The combination of multiple repeating events plus their exceptions is not something that SQL remotely supports nor could be expressed in SQL*. Quite simply you are going to have to do the hard work yourself. If you are happy with a SQLite only solution then you can use SQLite functionality to make it easier. For example you could write a custom function that returns if an event occurs on a particular day, or your could write a custom virtual table that presents the results of calculating all the events, repeats and exceptions. (*) It may be possible to express some but the expressions would get hellish - things like the last Friday of the month etc. Additionally the number of days in months and years being non-uniform makes things even more annoying. If we used some sort of metric time there might have been some hope. Roger -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.9 (GNU/Linux) iEYEARECAAYFAkoyDWYACgkQmOOfHg372QSeXQCgjCnHrqaV2GCx0SzKRDKGy2iY gV0AoJ85pJ7444hYKheRX1T0ZabjYSLD =fmLA -----END PGP SIGNATURE----- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
