Thread: SQL99 - Nested Tables
I was interested as to if there were plans to develop SQL99 nested tables. I know with AJAX(tm) starting to grow in popularity that the XML features of SQL2003 would prove useful for EnterpriseDB. I have developed applications using Microsoft SQL Server since 1999. I have only been programming in C for about a year. I was curious as to what I might could do to contribute towards nested table support. I personally think it's needed, and am willing to do whatever I need to to make it happen. My first step was to get the latest source and try and understand it some. Darren Alcorn
Darren, > I was interested as to if there were plans to develop SQL99 nested > tables. I know with AJAX(tm) starting to grow in popularity that the > XML features of SQL2003 would prove useful for EnterpriseDB. I realize that nested tables are in SQL99, but so is SQLJ and a few other really dumb ideas. As Joe Celko (member of the SQL92 committee) put it, "we were a committee, and a committee never met a feature it didn't like." Can you make a use-case for nested tables? It would need to be a fairly strong one, given that they basically violate the relational model. If what you're really interested in is XML data support, then I'd suggest focusing on that instead. Of course, that's just my opinion. -- Josh Berkus Aglio Database Solutions San Francisco
On Thu, Jul 07, 2005 at 09:52:44AM -0700, Josh Berkus wrote: > Darren, > > > I was interested as to if there were plans to develop SQL99 nested > > tables. I know with AJAX(tm) starting to grow in popularity that > > the XML features of SQL2003 would prove useful for EnterpriseDB. > > I realize that nested tables are in SQL99, but so is SQLJ and a few > other really dumb ideas. As Joe Celko (member of the SQL92 > committee) put it, "we were a committee, and a committee never met a > feature it didn't like." > > Can you make a use-case for nested tables? Now that the time travel option is gone, this is a way of implementing it. > It would need to be a fairly strong one, given that they basically > violate the relational model. If what you're really interested in > is XML data support, then I'd suggest focusing on that instead. XML data support would be best if coordinated. There are several different projects right now, and one more would be bad. > Of course, that's just my opinion. I think it would be kinda nice if a column type could be anything, including SETOF some_composite_type. If I recall correctly, Illustra had this, and it was a pretty cool feature :) It might also help implement the SQL:2003 standard for UNNEST, MULTISET, FUSION, COLLECT and INTERSECT. In re: the relational model, that's already broken, although Date's excellent new book from O'Reilly encourages people to implement Tutorial D. :) Cheers, D -- David Fetter david@fetter.org http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote!
I was mainly interested because of the simplicity it seems to add for implementing an application using the database. While those accustomed to writing SQL queries using joins and keys might prefer it for many understandable reasons, there is something to be said for multidimensional data structures. It would be like if you _had_ to have multiple arrays to store information in C instead of using a multidimensional array.
I'm open to debate on the subject as I'd love to be convinced that Oracle is wrong. I think the XML features are important and I'd be more suited writing something more straight forward versus re-inventing the wheel. I brought it up for debate, because I thought it was something that should be thought about.
Darren Alcorn
On 7/7/05, Josh Berkus <josh@agliodbs.com> wrote:
Darren,
> I was interested as to if there were plans to develop SQL99 nested
> tables. I know with AJAX(tm) starting to grow in popularity that the
> XML features of SQL2003 would prove useful for EnterpriseDB.
I realize that nested tables are in SQL99, but so is SQLJ and a few other
really dumb ideas. As Joe Celko (member of the SQL92 committee) put it, "we
were a committee, and a committee never met a feature it didn't like."
Can you make a use-case for nested tables? It would need to be a fairly
strong one, given that they basically violate the relational model. If what
you're really interested in is XML data support, then I'd suggest focusing on
that instead.
Of course, that's just my opinion.
--
Josh Berkus
Aglio Database Solutions
San Francisco
Darren, > I was mainly interested because of the simplicity it seems to add for > implementing an application using the database. While those accustomed > to writing SQL queries using joins and keys might prefer it for many > understandable reasons, there is something to be said for > multidimensional data structures. It would be like if you _had_ to have > multiple arrays to store information in C instead of using a > multidimensional array. I'm open to debate on the subject as I'd love to > be convinced that Oracle is wrong. Ooops. Our discussion somehow got shifted off list. Suffice it to say that not everyone agrees with me. > I think the XML features are > important and I'd be more suited writing something more straight forward > versus re-inventing the wheel. I brought it up for debate, because I > thought it was something that should be thought about. Yes, I just don't see how nested tables relate to XML. -- --Josh Josh Berkus Aglio Database Solutions San Francisco
On Thu, Jul 07, 2005 at 12:53:14PM -0700, Josh Berkus wrote: > Darren, > > > I was mainly interested because of the simplicity it seems to add for > > implementing an application using the database. While those accustomed > > to writing SQL queries using joins and keys might prefer it for many > > understandable reasons, there is something to be said for > > multidimensional data structures. It would be like if you _had_ to have > > multiple arrays to store information in C instead of using a > > multidimensional array. I'm open to debate on the subject as I'd love to > > be convinced that Oracle is wrong. > > Ooops. Our discussion somehow got shifted off list. Suffice it to say > that not everyone agrees with me. Where "not everyone" includes one C. J. Date ;) > > I think the XML features are important and I'd be more suited > > writing something more straight forward versus re-inventing the > > wheel. I brought it up for debate, because I thought it was > > something that should be thought about. > > Yes, I just don't see how nested tables relate to XML. To me, they don't relate directly, as tables (nested or otherwise) have no intrinsic row ordering, where XML does. Nested tables is a Good Thing(TM) though :) Cheers, D -- David Fetter david@fetter.org http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote!
David, >Nested tables is a > Good Thing(TM) though :) Hmmm. I don't buy that they are an a priori Good Thing. What are they good for? I can't think of a single occasion in my 12-year database career where I found myself wanting one. Seems to me that, SQL standard or not, nested tables are just a jumbo-sized foot gun for new DBAs. And it's not like they'll be trivial to implement. -- --Josh Josh Berkus Aglio Database Solutions San Francisco
On Thu, Jul 07, 2005 at 05:14:27PM -0700, Josh Berkus wrote: > David, > > >Nested tables is a Good Thing(TM) though :) > > Hmmm. I don't buy that they are an a priori Good Thing. What are > they good for? I can't think of a single occasion in my 12-year > database career where I found myself wanting one. If they'd been available when you started, you might find them indispensable. :) > Seems to me that, SQL standard or not, nested tables are just a > jumbo-sized foot gun for new DBAs. There are quite a few things in PostgreSQL than have already been implemented which can serve this function. Ask a new DBA what she expects table inheritance to do or not to do, for example. > And it's not like they'll be trivial to implement. Probably not, but implementing them will very likely open the door to implementing other parts of the SQL standard that really are fun. :) Cheers, D -- David Fetter david@fetter.org http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote!
On Wed, 6 Jul 2005, Darren Alcorn wrote: > I was interested as to if there were plans to develop SQL99 nested > tables. Could you give an example of SQL99 nested tables? It might help us who don't know what the term stand for understand the issue. I've browsed through (bur not fully read) sql99 more then once in my life and I don't recall any nested tables. -- /Dennis Björklund
Here is a link that has a description. There is also a lot of examples (of syntax as well) on Oracle's website. http://www-db.stanford.edu/~ullman/fcdb/oracle/or-objects.html#nested Darren On Jul 8, 2005, at 9:58 AM, Dennis Bjorklund wrote: > On Wed, 6 Jul 2005, Darren Alcorn wrote: > > >> I was interested as to if there were plans to develop SQL99 nested >> tables. >> > > Could you give an example of SQL99 nested tables? It might help us who > don't know what the term stand for understand the issue. I've browsed > through (bur not fully read) sql99 more then once in my life and I > don't > recall any nested tables. > > -- > /Dennis Björklund > >
The way I understand Nested Tables and Object Relational Databases, they basically are a layer on top of any old RDBMS that adds ease for the user. I personally believe in normalization theory I just don't like implementing it to avoid JOIN syntax. How difficult would it be to implement (for those more familiar with the code) to write such a "layer." It could always be a patch until I can persuade more people that it's a good idea. Darren On Jul 7, 2005, at 7:09 PM, David Fetter wrote: > On Thu, Jul 07, 2005 at 12:53:14PM -0700, Josh Berkus wrote: > >> Darren, >> >> >>> I was mainly interested because of the simplicity it seems to add >>> for >>> implementing an application using the database. While those >>> accustomed >>> to writing SQL queries using joins and keys might prefer it for many >>> understandable reasons, there is something to be said for >>> multidimensional data structures. It would be like if you _had_ >>> to have >>> multiple arrays to store information in C instead of using a >>> multidimensional array. I'm open to debate on the subject as I'd >>> love to >>> be convinced that Oracle is wrong. >>> >> >> Ooops. Our discussion somehow got shifted off list. Suffice it >> to say >> that not everyone agrees with me. >> > > Where "not everyone" includes one C. J. Date ;) > > >>> I think the XML features are important and I'd be more suited >>> writing something more straight forward versus re-inventing the >>> wheel. I brought it up for debate, because I thought it was >>> something that should be thought about. >>> >> >> Yes, I just don't see how nested tables relate to XML. >> > > To me, they don't relate directly, as tables (nested or otherwise) > have no intrinsic row ordering, where XML does. Nested tables is a > Good Thing(TM) though :) > > Cheers, > D > -- > David Fetter david@fetter.org http://fetter.org/ > phone: +1 510 893 6100 mobile: +1 415 235 3778 > > Remember to vote! >
On Fri, Jul 08, 2005 at 10:03:57 -0400, Darren Alcorn <dalcorn@gmail.com> wrote: > Here is a link that has a description. There is also a lot of > examples (of syntax as well) on Oracle's website. > > http://www-db.stanford.edu/~ullman/fcdb/oracle/or-objects.html#nested So they are permitting sets as a data type and then proceed to use a misleading example (polygons are ordered sets of points). Postgres already provides mechanisms to do the same thing using custom datatypes. What it looks like they provide in addition is efficient(?) relational (i.e. set) operators and foreign key constraints.
On 7/7/05, Darren Alcorn <dalcorn@gmail.com> wrote: > I was interested as to if there were plans to develop SQL99 nested > tables. I know with AJAX(tm) starting to grow in popularity that the > XML features of SQL2003 would prove useful for EnterpriseDB. > Although this discussion seems to be heading more towards Nested Tables, I would like to express that EnterpriseDB is very interested in seeing XML features of SQL2003 specs added to PostgreSQL. We would love to sponsor anybody possessing the pertinent skillset and interested in working on these bi-directional mapping features. This, of course, includes folks already working on any XML-oriented projects for PostgreSQL. If you are interested, drop a private e-mail to Denis Lussier (luss@enterprisedb.com). Affan Bin Salman, Lead Database Architect EnterpriseDB Corporation. mailto: affan@enterprisedb.com blog: http://affans.blogspot.com
On 7/7/05, David Fetter <david@fetter.org> wrote: > > It would need to be a fairly strong one, given that they basically > > violate the relational model. If what you're really interested in > > is XML data support, then I'd suggest focusing on that instead. > > XML data support would be best if coordinated. There are several > different projects right now, and one more would be bad. > David, Can you enumerate or refer to these *apparently* uncoordianted/disparate XML data support projects? I would also appreciate any progress/status information on 'em, if possible. Thanks, Affan Bin Salman, Lead Database Architect EnterpriseDB Corporation. mailto: affan@enterprisedb.com blog: http://affans.blogspot.com
On Wed, 13 Jul 2005, Affan Salman wrote: > On 7/7/05, Darren Alcorn <dalcorn@gmail.com> wrote: >> I was interested as to if there were plans to develop SQL99 nested >> tables. I know with AJAX(tm) starting to grow in popularity that the >> XML features of SQL2003 would prove useful for EnterpriseDB. >> > > Although this discussion seems to be heading more towards Nested > Tables, I would like to express that EnterpriseDB is very interested > in seeing XML features of SQL2003 specs added to PostgreSQL. > > We would love to sponsor anybody possessing the pertinent skillset and > interested in working on these bi-directional mapping features. This, > of course, includes folks already working on any XML-oriented projects > for PostgreSQL. If you are interested, drop a private e-mail to Denis > Lussier (luss@enterprisedb.com). Now, after we have GiST with concurrent and recovery support I see no obstacle to implement native XML support using GiST. A long time ago I thought about ltree + hstore hybrid to work with XML, but unfortunately, we were busy. > > Affan Bin Salman, > Lead Database Architect > EnterpriseDB Corporation. > > mailto: affan@enterprisedb.com > blog: http://affans.blogspot.com > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > Regards, Oleg _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83