Subject: SQL3 UNDER

From: Chris Bitmead <[email-masked]>
Date: Mon, 22 May 2000 23:17:20 +1000

For those interested I've extended the patch to support the SQL3 UNDER
syntax...

ftp://ftp.tech.com.au/pub/diff.x

---

From: "Robert B. Easter" <[email-masked]>
Date: Tue, 23 May 2000 02:16:52 -0400

Mr. Bitmead,

I read your patch (but have not applied it, or tested it exactly).  It says
that UNDER supports multiple inheritance as a Postgres language extension.  How
about instead, implementing UNDER exactly to the SQL3 spec?  For multiple
inheritance, why not just suggest the use of INHERITS, which is
already a Postgres language extension for multiple inheritance.  UNDER covers
the tree/hierarchy situation, so make it only to SQL3 standards. 
INHERIT fits the clone/copy/inherits situation that, like I've
said before, is like starting a new tree.  You could constrain INHERITS to only
accept tables that are maximal supertables.  INHERITS probably should not
accept a subtable that is within an UNDER tree - that would add much
complication if allowed.  I know that I said that maybe INHERITS should strive
to become UNDER in a prior message.  But now I can see exactly how they
complement each other: each provides a different type of inheritance scheme.

(sorry if I seem to keep saying the same things over and over ... again..)
In UNDER, the tables are connected into a tree like one big table with
extensions to it (the subtables).  The subtables are dependent on the
supertables so that the supertables cannot be dropped until you drop all its
subtables first.  My impression of the subtable-supertable relationship is
that, again, the subtable stores only the subrow it declares.  The subrow it
inherited from its supertable is just a link.  When inserting, the subtable
stores the subrow it declared, then it accesses its supertable
and inserts the inherited subrow.  The subtable is incomplete without accessing
its supertable for the inherited subrow.  When you add a column to a superclass,
there should be no need to also add a column to the subclass.  The subclass
doesn't store it, but should just begin accepting the new attribute of its
superclass.  Isn't this how the SQL3 spec works (I'll have to read it more)?

INHERITS should accept only maximal supertables or tables that are not
part of an UNDER tree.  The child table could be independent of the parent
table.  The parent table could be dropped without consequence to the child
table since it inherits a copy of all its parent's attributes.  While the
parent exists, it would maintain information about all of its children
tables so that it can select down into them (in common attributes only).  The
child maintains no linkage to the parent - its inserts etc only affect itself. 
This contrasts with UNDER, where a subtable does maintain a link to its
supertable in order to cascade inserts etc to the supertable for the subrow it
inherited.

I hope my comments are helpful! :)

-- 
Robert B. Easter
[email-masked]

---

From: Hannu Krosing <[email-masked]>
Date: Tue, 23 May 2000 09:40:42 +0300

Chris Bitmead wrote:
> 
> "Robert B. Easter" wrote:
> What you have just described for the behaviour of UNDER (as opposed to
> implementation) is just how INHERITS works now. i.e. you can't destroy
> the parent unless there are no children.

We could supply DROP TABLE parent CASCADE; syntax to destroy bot parent and
all 
inherited tables.

---------------------
Hannu

---

From: Chris Bitmead <[email-masked]>
Date: Tue, 23 May 2000 17:03:10 +1000

> complement each other: each provides a different type of inheritance scheme.

As far as I'm concerned, current postgres INHERIT, is exactly the same
semantics as UNDER (apart from multiple inheritance). That being the
case, the only point of retaining INHERIT is legacy. Encouraging use of
a completely different syntax just for multiple inheritance seems
foolish.

If you think the semantics are different provide a specific example
(including SQL) of how you think their behaviour is different - that is
how you think UNDER should work differently to current INHERIT.

> superclass.  Isn't this how the SQL3 spec works (I'll have to read it more)?

That kinda sounds like how SQL3 describes the model. But it doesn't mean
we have to implement it that way to provide the same behaviour. And in
fact we don't, and I don't think we should either.

What you have just described for the behaviour of UNDER (as opposed to
implementation) is just how INHERITS works now. i.e. you can't destroy
the parent unless there are no children. While I think the ability to
destroy a parent would be a good feature (for evolving a schema for
example), it hardly amounts to a whole new model. The time to decide you
want the ability to destroy a parent, is when you've decided to destroy
a parent, not when you created a child. As far as I'm concerned, the
more wierd and wonderful ways to evolve the schema without destroying
data the better.

In general, I think you concentrating too hard on implementation as
opposed to semantics. Try to crystalise the semantics first, then
implementation can be chosen on performance.

---

From: "Robert B. Easter" <[email-masked]>
Date: Tue, 23 May 2000 03:37:48 -0400

> how you think UNDER should work differently to current INHERIT.

I'll try to provide examples later.  For now, did you see the gif attachments
on a earlier message of mine?  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? :-)

-- 
Robert B. Easter
[email-masked]

---

From: Chris Bitmead <[email-masked]>
Date: Tue, 23 May 2000 19:16:56 +1000

> 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

I'm afraid not. Show me the (SQL) code :-).

---

From: "Robert B. Easter" <[email-masked]>
Date: Tue, 23 May 2000 05:48:05 -0400

> 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
[email-masked]

---

From: Chris Bitmead <[email-masked]>
Date: Tue, 23 May 2000 22:15:25 +1000

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?

---

From: Chris Bitmead <[email-masked]>
Date: Tue, 23 May 2000 22:28:51 +1000

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.

---

From: "Robert B. Easter" <[email-masked]>
Date: Tue, 23 May 2000 08:45:23 -0400

> precedent in some programming language?

A database is capable of more flexibility than a programming language with
regard to how it can store objects.  A database it not constrained by
hardcoded runtime and compilation dependencies like objects in a programming
language. Changing the data structure of a program means reprogramming then
restarting the program.  If made right, a database can evolve its classes
without ever going offline.  I think there are some differences and so I don't
see programming language precedents being so relevent.

I'm just proposing things to see if we don't over look some possibilities. 
Under my ideas here, UNDER can be implemented more like to spec (maybe
exactly).  INHERIT can pickup the Postgres extensions until a standard
covers it too.

---

From: "Robert B. Easter" <[email-masked]>
Date: Tue, 23 May 2000 09:01:52 -0400

> and one that only uses INHERITS, and explain how one or the other can

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
[email-masked]

---

From: Chris Bitmead <[email-masked]>
Date: Tue, 23 May 2000 23:51:11 +1000

"Robert B. Easter" wrote:

> A database is capable of more flexibility than a programming language 
> with regard to how it can store objects.  A database it not constrained 
> by hardcoded runtime and compilation dependencies like objects in a 
> programming language. Changing the data structure of a program means 
> reprogramming then restarting the program.  

Well, my favoured language is lisp which can actually change its
structures, even its code and polymorphic rules at runtime.

> If made right, a database can evolve its classes without ever going 
> offline.  I think there are some differences and so I don't

Ok, programming languages aren't a precedent. Is there another database
as precedent? Give me something to work with here.

> I'm just proposing things to see if we don't over look some 
> possibilities. Under my ideas here, UNDER can be implemented more like 
> to spec (maybe exactly).  INHERIT can pickup the Postgres extensions 
> until a standard covers it too.

It sounds to me you're worried about the implementation rather than the
spec. IF someone were to bother implementing that layout it should
probably just be an option - not affecting semantics. CREATE TABLE
foo(...) UNDER bar LAYOUT IS HIERARCHIAL or LAYOUT IS SINGULAR. That
would complicate the code a  lot though. Personally I think if it was
implemented the way the spec implies it would create an extra join for
every inheritance declaration. Avoiding that is the whole reason to have
an object database. If you don't care about another join for every
inheritance you may as well use a pure relational database with a mapper
library like persistance because you're not gaining a whole lot. On the
other hand with the current implementation (which is pretty much how
every ODBMS and ORBMS I've ever seen works), there is very little
downside. If you implement a single index that indexes subclasses then
both index scans and sequential scans will be pretty near optimal with
no joins. Compare against who knows how many joins if you split it up.
The only minor downside is you maybe lift a little more data off the
disk IF you happen to be doing a projection of super-class attributes.
But an ODMG interface would hardly ever do that anyway.

---

From: Hannu Krosing <[email-masked]>
Date: Tue, 23 May 2000 19:14:08 +0300

> ) UNDER nuclearpowersource;

This is what INHERITS currently is meant for.

> ) INHERITS(powersource) UNDER machine ;

Why not just

 CREATE TABLE poweredmachine (
    machine_powersource    powersource
 )  UNDER machine ;

This should probably allow to insert any powersource as machine_powersource.

-------
Hannu

---

From: Chris Bitmead <[email-masked]>
Date: Wed, 24 May 2000 09:42:38 +1000

> > and one that only uses INHERITS, and explain how one or the other can

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?