On Thursday 05 May 2005 15:46, Flavio S. Glock wrote: > CREATE VIEW MY_RECURRENCE_28403 ( N ) AS > SELECT ( N + INTERVAL '7 MONTH' ) FROM DT_YEAR; > CREATE VIEW MY_RECURRENCE_83554 ( N ) AS > SELECT ( N + INTERVAL '5 DAY' ) FROM MY_RECURRENCE_28403; > CREATE VIEW MY_RECURRENCE ( N ) AS > SELECT ( N ) FROM MY_RECURRENCE_83554;
Hmmm... Took me some time to get it. It's really imaginative, congratulations. > You are responsible for maintaining the "DT_YEAR" table - it may be as > big as needed. That's one nugget that I don't like. Your solution gets close, but it really doesn't store an infinite set. It's a way to select subsets of what's already there. I've been thinking that it just wasn't possible to store infinite sets, and that's one more indication of that, although it gets really close. In fact, your solution is a way to move the set logic from the app to the SQL server. I don't think it's a good idea. You might as well have the SQL server call a procedure using DateTime::Sets directly, it would be the same computation. Nothing is gained, except that the logic is stored unserialized, and IMHO it's more appropriate to have the application server, not the DB server, do the application logic. > > Is it possible to do a > > query such as "which sets intersect with today?" on the SQL > > data resulting from that serialization? > > Yes, this is just a normal table. Well, not really. Suppose I want to store N sets. That would create N views (not counting the sub-views). Now that the sets are stores, I want to know "which sets intersect with today?" That's equivalent to "which views contain a row that intersects with today?" I don't think that's possible to know without iterating on the view names. Here's my way of doing it. I'm sure I won't be telling you anything new, I'm just writing because maybe others might enjoy it. I like to think my method is pragmatic. I deal with finite and infinite sets differently. I store the recursion rule as an ICal string in a column and in another column I use a bool to indicate whether the set is finite or infinite. If it's finite, I expand the recursion to all its occurrences in a table of occurrences. Easy. If it's infinite, then there is simply nothing else to do. It's up to the application to select all infinite sets, parse the rule and do the calculation. As others mentioned, I cache occurrences of infinite recursions in a separate table, so the calculation needs not be done every time. This was an efficient way of doing it because: - I postulate that it's not possible to store infinite sets in an SQL database. You may get close, but you'll never really have it. The best you can achieve is some sort of cache. - In most applications, there are not many infinite sets to deal with. I noticed this when using a calendaring application. If I had many infinite sets, then my calendar would be much too *crowded* for me to use it. I can't imagine someone needing more than, let's say, a hundred infinite sets *in his whole lifetime*. Throw in a cache and you don't need to worry about performance.
