Re: SQL3 UNDER - Mailing list pgsql-hackers

Maybe it would help if you have two examples. One that only uses UNDER,
and one that only uses INHERITS, and explain how one or the other can
work differently.


"Robert B. Easter" wrote:
> 
> 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
partof its 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 people who 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