Re: SQL3 UNDER

"Robert B. Easter" wrote:
>
> On Tue, 23 May 2000, Chris Bitmead wrote:
> > 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.

Yes but how does a pure UNDER example actually work different to a pure
INHERITS example? You've created various tables below (combining INHERIT
and UNDER unfortunately), but how will the INHERITS hierarchies and
UNDER hierarchies actually work differently in practice?

>
> Which one (or both) that you use depends on the relationship the two entities
> have. If you need multiple inheritance, your choice is clear: INHERITS. UNDER
> will not do multiple inheritance.
> UNDER is the choice when the idea is of EXTENDing a class into more
> specific types of subclasses. INHERIT is the choice when the idea is like
> parent and child or olddesign and newdesign where olddesign may disappear
> without any problem.
>
> What follows are some rough examples. There could be some errors. I'd like to
> see someone elses examples too. I know there are possibilities for very good
> examples.
>
> CREATE TABLE powersource (
> );
> CREATE TABLE nuclearpowersource (
> ) UNDER powersource;
> CREATE fissionpowersource (
> ) UNDER nuclearpowersource;
> CREATE fusionpowersource (
> ) UNDER nuclearpowersource;
>
> CREATE TABLE machine (
> );
> CREATE TABLE poweredmachine (
> ) INHERITS(powersource) UNDER machine ;
>
> CREATE TABLE wheel (
> );
> CREATE TABLE tire (
> ) UNDER wheel;
> CREATE TABLE knobbedtire (
> ) UNDER tire;
> CREATE TABLE smoothtire (
> ) UNDER tire;
>
> CREATE TABLE transportmode (
> );
> CREATE TABLE wheeltransport (
> ) INHERITS(tire) UNDER transportmode
> CREATE TABLE foottransport (
> ) UNDER transportmode;
>
> CREATE TABLE engine (
> ) INHERITS(poweredmachine);
> CREATE TABLE jetengine (
> ) UNDER engine;
> CREATE TABLE PISTONENGINE (
> ) UNDER engine;
> CREATE TABLE electricengine (
> ) UNDER engine;
>
> CREATE TABLE lifeform (
> species INTEGER PRIMARY KEY,
> brain INTEGER
> );
> CREATE TABLE human (
> ) UNDER lifeform;
>
> CREATE TABLE autotransportmachine (
> ) INHERITS (transportmode) UNDER poweredmachine
>
> CREATE TABLE cyborg (
> ) INHERITS(autotransportmachine) UNDER human;
>
> CREATE TABLE entity (
> ) INHERITS (cyborg);
>
> ============================================
>
> >
> >
> > "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
> --
> Robert B. Easter
> reaster(at)comptechnews(dot)com