Re: SQL3 UNDER

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 part of 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(at)comptechnews(dot)com