Thread: SQL3 UNDER
For those interested I've extended the patch to support the SQL3 UNDER syntax... ftp://ftp.tech.com.au/pub/diff.x
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! :) On Mon, 22 May 2000, Chris Bitmead wrote: > For those interested I've extended the patch to support the SQL3 UNDER > syntax... > > ftp://ftp.tech.com.au/pub/diff.x -- Robert B. Easter reaster@comptechnews.com
"Robert B. Easter" wrote: > 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. 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. > (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)? 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. > 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. 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.
Chris Bitmead wrote: > > "Robert B. Easter" wrote: > > 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. > > 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
On Tue, 23 May 2000, Chris Bitmead wrote: > 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. > 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 reaster@comptechnews.com
> 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 itsmaximal 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 :-).
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 ofits 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 peoplewho 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
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
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
On Tue, 23 May 2000, Chris Bitmead wrote: > 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? 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.
"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 > see programming language precedents being so relevent. 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.
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. 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 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 -- Robert B. Easter reaster@comptechnews.com
"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. > > 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; This is what INHERITS currently is meant for. > CREATE TABLE machine ( > ); > CREATE TABLE poweredmachine ( > ) 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
"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 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 > -- > Robert B. Easter > reaster@comptechnews.com
Peter Eisentraut wrote:
>
> On Tue, 23 May 2000, Chris Bitmead wrote:
>
> > As far as I'm concerned, current postgres INHERIT, is exactly the same
> > semantics as UNDER (apart from multiple inheritance).
>
> Agreed, but note that according to the final SQL99 standard the UNDER
> clause comes before the originally defined column list, which does make
> sense because that's how the columns end up.
Are you sure? It actually looks to me like you can have the UNDER before
or after. What sense do you make of that? (Note the <table element
list> occuring before and after the <subtable clause>.
<table definition> ::= CREATE [ <table scope> ] TABLE <table name> <table contents
source> [ ON COMMIT <table commit action> ROWS ]
<table contents source> ::= <table element list> | OF <user-defined type>
[ <subtable clause> ] [ <table element list> ] <subtable clause> ::= UNDER
<supertableclause>
> Chris Bitmead wrote: > >Peter Eisentraut wrote: > > Agreed, but note that according to the final SQL99 standard the UNDER > > clause comes before the originally defined column list, which does make > > sense because that's how the columns end up. > Are you sure? It actually looks to me like you can have the UNDER before > or after. What sense do you make of that? (Note the <table element > list> occuring before and after the <subtable clause>. > <table definition> ::= > CREATE [ <table scope> ] TABLE <table name> > <table contents source> > [ ON COMMIT <table commit action> ROWS ] > > <table contents source> ::= > <table element list> > | OF <user-defined type> > [ <subtable clause> ] > [ <table element list> ] > <subtable clause> ::= > UNDER <supertable clause> Actually, from this I'd say Peter was right unless I'm horribly misreading the grammar piece provided, <table element list> doesn't come both before and after <subtable clause> in the <table contents source>, it is either alone, or part of the OF...<table element list> with the | breaking the two options.
You're right. I'll have to look at making changes. Stephan Szabo wrote: > > > Chris Bitmead wrote: > > >Peter Eisentraut wrote: > > > Agreed, but note that according to the final SQL99 standard the UNDER > > > clause comes before the originally defined column list, which does make > > > sense because that's how the columns end up. > > Are you sure? It actually looks to me like you can have the UNDER before > > or after. What sense do you make of that? (Note the <table element > > list> occuring before and after the <subtable clause>. > > <table definition> ::= > > CREATE [ <table scope> ] TABLE <table name> > > <table contents source> > > [ ON COMMIT <table commit action> ROWS ] > > > > <table contents source> ::= > > <table element list> > > | OF <user-defined type> > > [ <subtable clause> ] > > [ <table element list> ] > > <subtable clause> ::= > > UNDER <supertable clause> > > Actually, from this I'd say Peter was right unless I'm horribly misreading > the > grammar piece provided, <table element list> doesn't come both before and > after <subtable clause> in the <table contents source>, it is either alone, > or part of the OF...<table element list> with the | breaking the two > options.