Re: SQL3 UNDER - Mailing list pgsql-hackers

On Tue, 23 May 2000, Chris Bitmead wrote:
> 
> > I'll try to provide examples later.  For now, did you see the gif 
> > attachments on a earlier message of mine?  
> 
> I didn't before, but I do now.
> 
> > The UNDER and CLONES/INHERITS gif pictures
> > provide a graphical view of what I mean.  UNDER creates tree hierarchy 
> > down vertically, while INHERITS supports multiple inheritance in a 
> > lateral direction.  The UNDER trees can be under any table that is part 
> > of an INHERITS relationship.  UNDER and INHERITS work at different 
> > levels sorta.  A subtable in an UNDER hierarchy can't be in an INHERITS > clause because it is logically just part
ofits maximal supertable.  In 
 
> > other words, INHERITS can provide a relationship between different 
> > whole trees created by UNDER, by way of a maximal supertable being  
> > inherited by another maximal supertable with its own
> > UNDER tree.  Make any sense? :-)
> 
> I'm afraid not. Show me the (SQL) code :-).

=======
Tree 1
=======
CREATE TABLE maxsuper1 (ms1_id        INTEGER PRIMARY KEY,...
);

CREATE TABLE sub1a (name        VARCHAR(50);
) UNDER maxsuper1;  -- maxsuper1.ms1_id is PRIMARY KEY


=======
Tree 2
=======
CREATE TABLE maxsuper2 (ms2_id        INTEGER PRIMARY KEY...
);

CREATE TABLE sub2a (name        VARCHAR(50);...
) UNDER maxsuper2; 

=====================================
Tree 3  is visible to Tree 1 and Tree 2 via INHERIT
Tree 1 (maxsuper1) and Tree 2 (maxsuper2) can see
their own trees, AND Tree 3.
=====================================
CREATE TABLE maxsuper3 (--  inherited composite PRIMARY KEY (ms1_id, ms2_id)-- I think this might be the right thing to
do,though this example is    not the best.  Consider a TABLE row and a TABLE    col.  TABLE cell could INHERIT
(row,col). The    inherited primary key (row_id, col_id) determines a cell.    This is also rather simple.  It forces
peoplewho are going to    use multiple inheritance to really think about how the    PRIMARY KEYs are chosen and when a
composite   doesn't make sense, then they should probably not    be inherited together anyway.    ...
 
) INHERITS (maxsuper1, maxsuper2);  -- optional parens.

CREATE TABLE sub3a (name        VARCHAR(50);...
) UNDER maxsuper3;

========================================================
Example SELECTs
========================================================
SELECT * FROM maxsuper1;
Returns all rows, including into UNDER tree sub1a ...
This form will select though all UNDER related subtables.

SELECT * FROM maxsuper1*;
Returns all rows, including into UNDER tree sub1a and into child tree
maxsuper3 etc.  If any subtables are parents of children in an INHERITS
relationship, then the select also continues through those INHERITS also,
descending into childs UNDER subtables and INHERIT children if any.
This form will select through all UNDER related subtables AND all INHERITED
related children.

SELECT * FROM ONLY maxsuper1;
Returns only rows in maxsuper1, does NOT go into UNDER tree nor INHERIT
related tree maxsuper3 ...  maxsuper1 itself ONLY is selected.
This form will select from ONLY the specified table - INHERIT and UNDER related
children and subtables are ignored.

SELECT * FROM ONLY maxsuper1*;
Returns only rows in maxsuper1 and INHERIT children, but does not get rows
from any UNDER trees of maxsuper1 or its children.
This form will select through all INHERIT related children of the specified
table - all UNDER related tables are ignored.

=============================
Some Rules
=============================
1.
UNDER and INHERIT can be used in the same CREATE TABLE, but with the following
restrictions:

a.
If C is UNDER A and INHERITS (B,...), then no table of (B,...) is UNDER A.

b.
If C is UNDER B and INHERITS (A,...), then B INHERITS from no table of (A,...).

Both of these conditions prevent a situation where C tries to obtain the
same attributes two different ways.  In other words, A and B must not be
related by INHERIT or UNDER.

Yes, I'm saying that the following syntax is possible:
CREATE TABLE subtable1b2 (...
) UNDER maxsuper1 INHERITS(maxsuper2)
The inherited PRIMARY KEYs form a composite primary key.

2.
If a column is added to a parent_table or supertable, the column add must
cascade to the child_table(s) and subtable(s).  If the column add does not
cascade, then SELECT * FROM parent* and SELECT * FROM supertable, will not
work right.  When adding a column to a supertable, any subtable that is a parent
table to children via INHERIT, has to cascade the new column to its children,
which may also in turn cascade the column add further.

3.
A supertable cannot be deleted until all its subtables are deleted first, or
some syntax is used to cascade the delete (as suggested by Hannu Krosing).

4.
A parent table in an INHERIT relationship may be deleted without consequence to
its children.

5.
In the case of clashing same-name attributes in multiple inheritance from
UNDER combined with INHERIT or just INHERIT, the CREATE TABLE fails until
use of ALTER TABLE RENAME COLUMN corrects the problem.  Attribute rename will
have to cascade through child and subtables.
 ==================================================

Well, enough for now.  I hope somebody sees where I'm going here.  In previous
messages I've said that it should not be allowed to inherit from a subtable. 
My rules above now allow for that.  The combination of UNDER and INHERIT allows
for quite a bit of flexibility if enough rules and details are sorted out.

Comments?

-- 
Robert B. Easter
reaster@comptechnews.com