Re: SQL3 UNDER - Mailing list pgsql-hackers

Well, you've laid out a whole lot of rules here. I understand what those
rules are, but I don't see the logical purpose for having such a set of
rules.

It appears you've got two separate inheritance mechanisms that interact
in strange ways. Which inheritance scheme that gets activated depends on
whether you use tablename or tablename*. Why not invent a few more
inheritance mechanisms, then you can have tablename% and tablename&,
then there can be some more rules for how they interact?  I don't
understand why you want to have these kinds of semantics. Does it have
precedent in some programming language?

"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