SQL99 - Nested Tables

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

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 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!

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!

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:

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 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