Re: SQL3 UNDER
От | Chris Bitmead |
---|---|
Тема | Re: SQL3 UNDER |
Дата | |
Msg-id | 392B176E.84F3E01D@nimrod.itg.telecom.com.au обсуждение исходный текст |
Ответ на | SQL3 UNDER (Chris Bitmead <chris@bitmead.com>) |
Список | pgsql-hackers |
"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
В списке pgsql-hackers по дате отправления: