Thread: Time varying referential integrity
Hello. I have time-varying relation variables similar to the ones described in this Rick Snodgrass article: http://www.informix.com.ua/articles/tempref/tempref.htm An example: CREATE TABLE departments ( department bigint primary key not null, name text not null, start_date timestamp not null default now(), end_date timestamp ); CREATE TABLE projects ( project bigint primary key not null, department bigint not null, name text not null, start_date timestamp not null default now(), end_date timestamp ); I also have views which query only the active rows: CREATE VIEW active_departments AS SELECT * FROM departments WHERE end_date IS NULL; The behavior, which I currently achieve laboriously through hand-written triggers and partial indexes, is: 1) Insertion of an active project requires the existence of an active department 2) Deactivation of an active department will cascade with a deactivation of the associated active projects where "active" means any tuple whose end_date is NULL I'm thinking of modifying the backend to achieve these results declaratively, rather than the manner I'm using now. For a small number of relation variables, custom triggers aren't that bad. However, in the hundreds it becomes a bit of a bear. In addition, I'd like the deactivation of a tuple value in a temporal relation to optionally result in its deletion, if the deactivation failed to cause the cascading deactivation of other tuples in referencing non-temporal relations. If I did write up something to achieve this by modifying the backend, would it have any chance of being accepted? Or, given the above design and requirements, is there a way I could achieve the appropriate effects declaratively that I've missed? Mike Mascari mascarm@mascari.com
Mike Mascari wrote: > Hello. > > I have time-varying relation variables similar to the ones described > in this Rick Snodgrass article: > > http://www.informix.com.ua/articles/tempref/tempref.htm I should also point out that apparently there is apparently a SQL/Temporal section to SQL 3, but I only have old '93 BNF so I haven't a clue as to what it says... http://www.dbpd.com/vault/9810snod.html Mike Mascari mascarm@mascari.com
Mike Mascari wrote: > I should also point out that apparently there is apparently a > SQL/Temporal section to SQL 3, but I only have old '93 BNF so I haven't > a clue as to what it says... FWIW, you can grab a copy of a late draft of SQL 2003 here: http://www.wiscorp.com/SQLStandards.html Joe
Joe Conway wrote: > Mike Mascari wrote: > >> I should also point out that apparently there is apparently a >> SQL/Temporal section to SQL 3, but I only have old '93 BNF so I >> haven't a clue as to what it says... > > > FWIW, you can grab a copy of a late draft of SQL 2003 here: > http://www.wiscorp.com/SQLStandards.html Well, that's a great link and I grabbed the ZIP file for it, but it appears to be missing SQL/Temporal. It appears to have: 01-Framework 02-Foundation 03-CLI 04-PSM 09-MED 10-OLB 11-Schemata 13-JRT but not 07-Temporal. Is that right? From a cursory search on temporal support, it appears that there should be a 07-SQL/Temporal group. Mike Mascari mascarm@mascari.com
Mike Mascari wrote: > but not 07-Temporal. Is that right? From a cursory search on temporal > support, it appears that there should be a 07-SQL/Temporal group. Your correct -- I didn't realize that it was its own spec. A little googling found me this though: http://www.jtc1sc32.org/sc32/jtc1sc32.nsf/Attachments/5A5731749C30132388256A5B0044557A/$FILE/32N0651.PDF Joe
Joe Conway wrote: > Mike Mascari wrote: > >> but not 07-Temporal. Is that right? From a cursory search on temporal >> support, it appears that there should be a 07-SQL/Temporal group. > > > Your correct -- I didn't realize that it was its own spec. A little > googling found me this though: > http://www.jtc1sc32.org/sc32/jtc1sc32.nsf/Attachments/5A5731749C30132388256A5B0044557A/$FILE/32N0651.PDF > Great. Thanks, Joe. It's kind of ironic that a SQL working draft is archived as a Lotus Notes document. I'll have to have a nice read-through though. The various SIGMOD Snodgrass articles and TimeCenter publications lead me to believe that temporal support in SQL database management systems could really make life simple. Hopefully an incremental approach for PostgreSQL is possible, because my home-grown temporal RI system is turning into a bit of a nightmare... Thanks again, Mike Mascari mascarm@mascari.com
Another thing to think about when using start and stop times like this is an RTREE index over time. Think of timestamps as points on a line. Intersects, contains within all apply. Obviously this does not necessarily solve the RI problem, but you may find that this does what you need in a practical sense. --elein On Sat, Dec 27, 2003 at 09:21:27PM -0500, Mike Mascari wrote: > Joe Conway wrote: > > >Mike Mascari wrote: > > > >>but not 07-Temporal. Is that right? From a cursory search on temporal > >>support, it appears that there should be a 07-SQL/Temporal group. > > > > > >Your correct -- I didn't realize that it was its own spec. A little > >googling found me this though: > >http://www.jtc1sc32.org/sc32/jtc1sc32.nsf/Attachments/5A5731749C30132388256A5B0044557A/$FILE/32N0651.PDF > > > > Great. Thanks, Joe. It's kind of ironic that a SQL working draft is > archived as a Lotus Notes document. I'll have to have a nice > read-through though. The various SIGMOD Snodgrass articles and > TimeCenter publications lead me to believe that temporal support in SQL > database management systems could really make life simple. Hopefully an > incremental approach for PostgreSQL is possible, because my home-grown > temporal RI system is turning into a bit of a nightmare... > > Thanks again, > > Mike Mascari > mascarm@mascari.com > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
On Dec 27, 2003, at 8:06 PM, Joe Conway wrote: > Mike Mascari wrote: >> but not 07-Temporal. Is that right? From a cursory search on temporal >> support, it appears that there should be a 07-SQL/Temporal group. > > Your correct -- I didn't realize that it was its own spec. A little > googling found me this though: > http://www.jtc1sc32.org/sc32/jtc1sc32.nsf/Attachments/ > 5A5731749C30132388256A5B0044557A/$FILE/32N0651.PDF This document is dated 2001-05-29. For some more recent information on the Temporal-SQL proposal, you can check out Hugh Darwen & C.J. Date's "Overview and Analysis of TSQL2" (draft dated 2003-09-3) <http://www.hughdarwen.freeola.com/TheThirdManifesto.web/OnTSQL2.pdf> Hugh Darwen, of IBM UK, has been an active contributor to SQL standards, in particular regarding TSQL. According to this "Overview", "[T]ime ran out toward the end of 2001, when─since no vendor had made any such move, and the committee had therefore done no further work on the project to develop Part 7 of the standard─ISO's own bylaws led to that project being canceled altogether. At the time of writing, therefore, the working draft document mentioned above ("Part 7: SQL/Temporal") is in limbo." Darwen & Date's "Overview" refers to a book they've written, together with Nikos A. Lorentzos, called "Temporal Data and the Relational Model" (ISBN 1-55860-855-9) proposing a method of working discrete point intervals in general (including temporal data in particular). Hugh Darwen's summary of the book is available at <http://www.hughdarwen.freeola.com/TheThirdManifesto.web/ TemporalData.pdf> So right now, it appears the field is pretty open for temporal implementations. It doesn't look like there's much movement on the ISO front right now. It's potentially an opportunity for PostgreSQL to implement something pretty cutting edge. I'm interested in this area, but have no C coding skills. Any recommendations for getting started on C? I've also thought there might be some crossover between discrete-point-interval support and work done for GIS, but I haven't looked at this very closely yet. Michael Glaesemann grzm myrealbox com