Thread

1. OOP real life example (was Re: Why is MySQL more chosen over PostgreSQL?

   Daniel Lyons <fusion@nmt.edu> — 2002-08-09T10:57:16Z

   I'm a little out of my league on this list, but I thought I might discuss a
   problem I'm having since it seems to have come up here recently.  I have read
   the archives w/ respect to the inheritance mechanism, the problems with it,
   etc.  But I still think it would be a useful thing to have in PostgreSQL, even
   if the current implementation... leaves something to be desired.  I
   particularly think it could be useful for a problem I'm working on now.
   
   I'm working on a largish Python-based voting system for my college.  The
   backend is to be PostgreSQL because of referential integrity, stored
   procedures, etc.--the thousand good reasons you guys already know.
   
   During the analysis phase we've noticed that there are really two kinds of
   "questions" if you will, "position questions" and "binary questions."  Things
   like "Secretary" are position questions, and have a list of candidates
   associated with them, and things like "Should the SA do such-and-such" are
   binary questions.
   
   Up at the top of the tree (kind of) is the election, which is an entity that
   among other things contains a few sets of questions.  Each set is for a
   different population (graduates don't get to pick undergrad senators, for
   example).  Each question set refers to one or more questions, which can be one
   of the two types I was just talking about.
   
   The problem is that position questions are going to be referred to by the
   candidate position table (a table that links candidates and positions in a
   many-to-many way).  The candidate position table therefore will be referencing
   a primary key which the position question table inherits from the question
   table.
   
   So my initial thinking is that this is a profound problem.  But after a little
   more thought, I can make the question_id field of the question table be a
   SERIAL type and the primary key.  That way, when I insert rows into either
   the position question or the binary question table, it will be picking the
   values out of the same sequence.  I won't have actual primary key integrity
   checking, but I'm fairly safe in assuming that it won't be a problem.
   
   Then my second thought was, perhaps I could write some sort of CHECK procedure
   which would verify integrity by hand between the two tables.  Or perhaps I
   could manually state that the primary key was the question_id field when
   creating both the child tables.  I'm really not sure if any of these
   approaches will work, or which one is best to do.
   
   So now that I hear there is a way to get from an object-relational solution to
   a solution using views, I'd like to know how to do it in general or perhaps
   with my particular problem.  I'm a big fan of OOP, as are the other people
   working with me on this project, so I would (personally) rather work around
   the existing inheritance mechanism than implement a solution I probably won't
   understand later using views, though I'd like to know it also... what is your
   advice?
   
   -- 
   Daniel
   
   (* Obscenity is a crutch for inarticulate motherfuckers. *)
   
   

2. Re: OOP real life example (was Re: Why is MySQL more chosen

   Curt Sampson <cjs@cynic.net> — 2002-08-12T02:46:06Z

   > So my initial thinking is that this is a profound problem.  But after a little
   > more thought, I can make the question_id field of the question table be a
   > SERIAL type and the primary key.  That way, when I insert rows into either
   > the position question or the binary question table, it will be picking the
   > values out of the same sequence.  I won't have actual primary key integrity
   > checking, but I'm fairly safe in assuming that it won't be a problem.
   >
   > Then my second thought was, perhaps I could write some sort of CHECK procedure
   > which would verify integrity by hand between the two tables.  Or perhaps I
   > could manually state that the primary key was the question_id field when
   > creating both the child tables.  I'm really not sure if any of these
   > approaches will work, or which one is best to do.
   >
   > So now that I hear there is a way to get from an object-relational solution to
   > a solution using views, I'd like to know how to do it in general or perhaps
   > with my particular problem.
   
   The problem is, table inheritance is just syntatic sugar for creating
   separate tables, and a view that does a UNION SELECT on them all
   together, projecting only the common columns.
   
   You want to go the other way around, with a setup like this.
   
       table question contains:
   	question id - a unique identifier for each question
   	question_type -  binary or position
   	common attributes of binary and position questions
   
       table binary_question_data contains:
   	question id - references question table
   	attributes belonging only to binary questions
   
       table position_question_data contains:
   	question id - references question table
   	attributes belonging only to position questions
   
   If you need frequently to select just binary or just position
   questions, you can create a pair of views to deal with them, along
   the lines of
   
       CREATE VIEW binary_question AS
   	SELECT question.question_id, ...
   	FROM question, binary_question_data
   	WHERE question.question_id = binary_question.question_id
   	    AND question.question_type = 'B'
   
   Now you have two data integrity guarantees that you didn't have
   with table inheritance: two different questions cannot have the
   same question_id, and a question can never be both a position
   question and a binary question.
   
   > I'm a big fan of OOP, as are the other people working with me on this
   > project,
   
   As am I. That's why I use, for example, Java and Ruby rather than
   C and perl.
   
   > so I would (personally) rather work around the existing inheritance
   > mechanism
   
   Well, an inheritance mechanism alone does not OO make. Please don't
   think that table inheritance is OO; it's not.
   
   > than implement a solution I probably won't understand later
   > using views, though I'd like to know it also... what is your advice?
   
   The implementation with views is standard, very basic relational
   stuff.  Primary keys, foreign keys, and joins. If you do not
   understand it, I would strongly encouarge you to study it until
   you do, because you are going to be using this stuff all the time
   if you use databases.
   
   cjs
   -- 
   Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
       Don't you know, in this new Dark Age, we're all light.  --XTC
   
   
   

3. Re: OOP real life example (was Re: Why is MySQL more

   Hannu Krosing <hannu@tm.ee> — 2002-08-12T05:08:03Z

   On Mon, 2002-08-12 at 11:38, Mario Weilguni wrote:
   > Am Montag, 12. August 2002 08:02 schrieb Don Baccus:
   > > Curt Sampson wrote:
   > > > On Sun, 11 Aug 2002, Don Baccus wrote:
   > > >>I've been wanting to point out that SQL views are really, when
   > > >>scrutinized, "just syntactic sugar" ...
   > > >
   > > > Oh? Ok, please translate the following into equivalant SQL that
   > > > does not use a view:
   > > >
   > > >     CREATE TABLE t1 (key serial, value1 text, value2 text);
   > > >     CREATE VIEW v1 AS SELECT key, value1 FROM t1;
   > > >     GRANT SELECT ON v1 TO sorin;
   > >
   > > Granulize GRANT to the table column level.   Then GRANT "SELECT" perms
   > > for the user on every column from the two tables that happen to be
   > > included in the view.
   > >
   > > Yes, it's awkward.   So are the VIEW-based replacements for PG's type
   > > extensibility features.
   > 
   > But this is not a replacement for a view, isn't it? With a view I can do this:
   > create view v1 as select name, salary from workers where type <> 'MANAGEMENT';
   > 
   > with column permissions I must give access to all workers salary including the management, but not with a view.
   
   I guess that bare-bones replacement of CREATE VIEW with CREATE TABLE and
   CREATE RULE ... ON SELECT DO INSTEAD ... would have exaclty the same
   semantics as CREATE VIEW, including the ability to GRANT .
   
   so the no-view-syntactic-sugar equivalent would be
   
   CREATE TABLE v1 AS SELECT * FROM t1 WHERE false;
   CREATE RULE v1ins AS
       ON SELECT TO tv1
       DO INSTEAD
       SELECT t1."key",
              t1.value2
         FROM t1
        WHERE (t1."type" <> 'MANAGEMENT'::text);
   GRANT SELECT ON v1 TO sorin;
   
   Actually it seems that GRANT is also syntactic sugar for rules and the
   above could be replaced with 
   
   CREATE RULE v1ins AS
       ON SELECT TO tv1
       DO INSTEAD
       SELECT t1."key",
              t1.value2
         FROM t1
        WHERE (t1."type" <> 'MANAGEMENT'::text)
          AND CURRENT_USER IN ( SELECT username
                                  FROM grantees
                                 WHERE tablename = 'v1'
                                   AND command = 'select' )
   INSERT INTO GRANTEES(tablename,command,username)
                 VALUES('v1','select','sorin');
   
   ----------------
   Hannu
   
   
   

4. Re: OOP real life example (was Re: Why is MySQL more

   Hannu Krosing <hannu@tm.ee> — 2002-08-12T05:29:21Z

   On Mon, 2002-08-12 at 11:52, Curt Sampson wrote:
   > On Sun, 11 Aug 2002, Don Baccus wrote:
   > 
   > > Obviously it would require extending SQL, but since you in part argue
   > > that SQL sucks in regard to the relational model this shouldn't matter,
   > > right?
   > 
   > Well, if we're going to go so far as to get rid of SQL, we can go all
   > the way with the D&D thing, and VIEWs will no longer be syntatic sugar
   > because views and tables will be the same thing. (I'll leave you how
   > specify physical storage as an exercise for the reader. :-))
   > 
   > But anyway, I have no particularly huge objection to syntatic sugar
   > alone. I do have objections to it when it's not saving much typing. (It
   > is in this case, but that could be fixed with better automatic support
   > of view updates.)
   > 
   > But my real objection is when it makes things more confusing, rather
   > than less, which I think is definitely happening here.
   
   What makes things more confusing is poor understanding of a feature, not
   the feature itself. 
   
   > I've never
   > seen a rigourous explanation of our model of table inheritance,
   > nor any model that was more obviously correct than another. And
   > the parallel drawn with inheritance in OO languages is a false
   > parallel that adds to the confusion.
   
   Are you saying that inheritance in SQL is something fundamentally
   different than inheritance in OO languages ?
   
   > (For example, the distinction
   > between types and instances of types is critical in OO theory. What are
   > the TI equivalants of this?)
   
   If by TI you mean type instance then the equivalent of of an instance is
   a relation (i.e. one row in an (inherited) table).
   
   > All this is borne out by the regular questions one sees about
   > inheritance in the mailing lists. I'll admit a good part of it is
   > due to the broken implementation of inheritance, but all of the
   > problems I've ever seen are easily solved with very simple relational
   > solutions.
   
   All _simple_ inheritance problems are easily solved by simple relational
   solutions. The general problem of much more typing and debugging, less
   clues for optimiser etc. are not solved by _simple_ relational
   solutions.
   
   > Maybe the inheritance thing is causing people to turn off the relational
   > parts of their brain or something.
    
   Of maybe people are diversifying, using inheritance for is-a
   relationships and relational model for has-a relationships.
   
   ---------------
   Hannu
   
   
   

5. Re: OOP real life example (was Re: Why is MySQL more chosen

   Don Baccus <dhogaza@pacifier.com> — 2002-08-12T05:31:40Z

   Curt Sampson wrote:
   
   > The problem is, table inheritance is just syntatic sugar for creating
   > separate tables, and a view that does a UNION SELECT on them all
   > together, projecting only the common columns.
   
   I've been wanting to point out that SQL views are really, when 
   scrutinized, "just syntactic sugar" ...
   
   
   
   -- 
   Don Baccus
   Portland, OR
   http://donb.photo.net, http://birdnotes.net, http://openacs.org
   
   
   

6. Re: OOP real life example (was Re: Why is MySQL more chosen

   Curt Sampson <cjs@cynic.net> — 2002-08-12T05:51:34Z

   On Sun, 11 Aug 2002, Don Baccus wrote:
   
   > I've been wanting to point out that SQL views are really, when
   > scrutinized, "just syntactic sugar" ...
   
   Oh? Ok, please translate the following into equivalant SQL that
   does not use a view:
   
       CREATE TABLE t1 (key serial, value1 text, value2 text);
       CREATE VIEW v1 AS SELECT key, value1 FROM t1;
       GRANT SELECT ON v1 TO sorin;
   
   cjs
   -- 
   Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
       Don't you know, in this new Dark Age, we're all light.  --XTC
   
   
   

7. Re: OOP real life example (was Re: Why is MySQL more chosen

   Don Baccus <dhogaza@pacifier.com> — 2002-08-12T06:02:48Z

   Curt Sampson wrote:
   > On Sun, 11 Aug 2002, Don Baccus wrote:
   > 
   > 
   >>I've been wanting to point out that SQL views are really, when
   >>scrutinized, "just syntactic sugar" ...
   > 
   > 
   > Oh? Ok, please translate the following into equivalant SQL that
   > does not use a view:
   > 
   >     CREATE TABLE t1 (key serial, value1 text, value2 text);
   >     CREATE VIEW v1 AS SELECT key, value1 FROM t1;
   >     GRANT SELECT ON v1 TO sorin;
   
   Granulize GRANT to the table column level.   Then GRANT "SELECT" perms 
   for the user on every column from the two tables that happen to be 
   included in the view.
   
   Yes, it's awkward.   So are the VIEW-based replacements for PG's type 
   extensibility features.
   
   
   -- 
   Don Baccus
   Portland, OR
   http://donb.photo.net, http://birdnotes.net, http://openacs.org
   
   
   

8. Re: OOP real life example (was Re: Why is MySQL more chosen

   Curt Sampson <cjs@cynic.net> — 2002-08-12T06:08:26Z

   On Sun, 11 Aug 2002, Don Baccus wrote:
   
   > > Oh? Ok, please translate the following into equivalant SQL that
   > > does not use a view:
   > > ...
   > Granulize GRANT to the table column level.
   
   Can you please show me the code for that? After all, I showed you
   all of my code when doing equivalants.
   
   Or are you saying that it's syntactic sugar only in some imaginary
   version of postgres that does not exist?
   
   cjs
   -- 
   Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
       Don't you know, in this new Dark Age, we're all light.  --XTC
   
   
   

9. Re: OOP real life example (was Re: Why is MySQL more chosen

   Tom Lane <tgl@sss.pgh.pa.us> — 2002-08-12T06:13:08Z

   Curt Sampson <cjs@cynic.net> writes:
   > On Sun, 11 Aug 2002, Don Baccus wrote:
   >> Granulize GRANT to the table column level.
   
   > Can you please show me the code for that?
   
   It's required by the SQL spec.  PG hasn't got it, but the spec is
   perfectly clear about how it should be done.
   
   I think this is really a bit irrelevant to the thread topic, though.
   
   			regards, tom lane
   
   

10. Re: OOP real life example (was Re: Why is MySQL more chosen

    Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2002-08-12T06:14:52Z

    > > > Oh? Ok, please translate the following into equivalant SQL that
    > > > does not use a view:
    > > > ...
    > > Granulize GRANT to the table column level.
    >
    > Can you please show me the code for that? After all, I showed you
    > all of my code when doing equivalants.
    >
    > Or are you saying that it's syntactic sugar only in some imaginary
    > version of postgres that does not exist?
    
    MySQL has column permissions and I _think_ the sql standard has them as
    well.
    
    Chris
    
    
    

11. Re: OOP real life example (was Re: Why is MySQL more chosen

    Don Baccus <dhogaza@pacifier.com> — 2002-08-12T06:26:26Z

    Curt Sampson wrote:
    > On Sun, 11 Aug 2002, Don Baccus wrote:
    > 
    > 
    >>>Oh? Ok, please translate the following into equivalant SQL that
    >>>does not use a view:
    >>>...
    >>
    >>Granulize GRANT to the table column level.
    > 
    > 
    > Can you please show me the code for that? After all, I showed you
    > all of my code when doing equivalants.
    
    Obviously it would require extending SQL, but since you in part argue 
    that SQL sucks in regard to the relational model this shouldn't matter, 
    right?   You're arguing the superiority of the relational model as 
    described by D&D over other models, non-relational SQL (which all agree 
    has weaknesses) and most likely God.
    
    So don't flip-flop between the "oh, SQL sucks think about the relational 
    model" and "SQL doesn't support that".  Pick one or the other.  Argue 
    SQL or D&D/relational model.
    
    It's not hard to propose *extensions* to SQL that would allow granting 
    of perms on a column rather than table level.
    
    > Or are you saying that it's syntactic sugar only in some imaginary
    > version of postgres that does not exist?
    
    Sort of like the idealized relational model that isn't implemented by 
    SQL nor PG, but yet you reference again and again when it suits you to 
    ignore the shortcomings of SQL92?
    
    Sure.
    
    Sorry, for a moment I thought you were interested in a meaningful 
    discussion rather than a dick-waving contest but I was wrong.
    
    I give up.  Your right hand waves your dick more frequently and with 
    much more vigor than mine.  This has nothing to do with with anything I 
    care about, though.
    
    
    -- 
    Don Baccus
    Portland, OR
    http://donb.photo.net, http://birdnotes.net, http://openacs.org
    
    
    

12. Re: OOP real life example (was Re: Why is MySQL more chosen

    Don Baccus <dhogaza@pacifier.com> — 2002-08-12T06:30:20Z

    Tom Lane wrote:
    > Curt Sampson <cjs@cynic.net> writes:
    > 
    >>On Sun, 11 Aug 2002, Don Baccus wrote:
    >>
    >>>Granulize GRANT to the table column level.
    >>
    > 
    >>Can you please show me the code for that?
    > 
    > 
    > It's required by the SQL spec.  PG hasn't got it, but the spec is
    > perfectly clear about how it should be done.
    > 
    > I think this is really a bit irrelevant to the thread topic, though.
    
    As far as the last goes, not really.  Curtis argues from false premises, 
    and this is one.  If it were the only false premise he argues from, 
    sure, I'd agree it's irrelevant but sadly Curtis argues from false 
    premises by default.
    
    -- 
    Don Baccus
    Portland, OR
    http://donb.photo.net, http://birdnotes.net, http://openacs.org
    
    
    

13. Re: OOP real life example (was Re: Why is MySQL more chosen

    Mario Weilguni <mweilguni@sime.com> — 2002-08-12T06:38:21Z

    Am Montag, 12. August 2002 08:02 schrieb Don Baccus:
    > Curt Sampson wrote:
    > > On Sun, 11 Aug 2002, Don Baccus wrote:
    > >>I've been wanting to point out that SQL views are really, when
    > >>scrutinized, "just syntactic sugar" ...
    > >
    > > Oh? Ok, please translate the following into equivalant SQL that
    > > does not use a view:
    > >
    > >     CREATE TABLE t1 (key serial, value1 text, value2 text);
    > >     CREATE VIEW v1 AS SELECT key, value1 FROM t1;
    > >     GRANT SELECT ON v1 TO sorin;
    >
    > Granulize GRANT to the table column level.   Then GRANT "SELECT" perms
    > for the user on every column from the two tables that happen to be
    > included in the view.
    >
    > Yes, it's awkward.   So are the VIEW-based replacements for PG's type
    > extensibility features.
    
    But this is not a replacement for a view, isn't it? With a view I can do this:
    create view v1 as select name, salary from workers where type <> 'MANAGEMENT';
    
    with column permissions I must give access to all workers salary including the management, but not with a view.
    
    best regards,
      mario weilguni
    
    

14. Re: OOP real life example (was Re: Why is MySQL more chosen

    Curt Sampson <cjs@cynic.net> — 2002-08-12T06:52:09Z

    On Sun, 11 Aug 2002, Don Baccus wrote:
    
    > Obviously it would require extending SQL, but since you in part argue
    > that SQL sucks in regard to the relational model this shouldn't matter,
    > right?
    
    Well, if we're going to go so far as to get rid of SQL, we can go all
    the way with the D&D thing, and VIEWs will no longer be syntatic sugar
    because views and tables will be the same thing. (I'll leave you how
    specify physical storage as an exercise for the reader. :-))
    
    But anyway, I have no particularly huge objection to syntatic sugar
    alone. I do have objections to it when it's not saving much typing. (It
    is in this case, but that could be fixed with better automatic support
    of view updates.)
    
    But my real objection is when it makes things more confusing, rather
    than less, which I think is definitely happening here. I've never
    seen a rigourous explanation of our model of table inheritance,
    nor any model that was more obviously correct than another. And
    the parallel drawn with inheritance in OO languages is a false
    parallel that adds to the confusion. (For example, the distinction
    between types and instances of types is critical in OO theory. What are
    the TI equivalants of this?)
    
    All this is borne out by the regular questions one sees about
    inheritance in the mailing lists. I'll admit a good part of it is
    due to the broken implementation of inheritance, but all of the
    problems I've ever seen are easily solved with very simple relational
    solutions. Maybe the inheritance thing is causing people to turn
    off the relational parts of their brain or something.
    
    > I give up.  Your right hand waves your dick more frequently and with
    > much more vigor than mine.
    
    First you ask for more meaningful discussion. Then you make comments
    like this. Hello?
    
    If you really don't intend to stop completely with the insulting comments,
    let me know and I can killfile you and we'll be done with this.
    
    cjs
    -- 
    Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
        Don't you know, in this new Dark Age, we're all light.  --XTC
    
    
    

15. Re: OOP real life example (was Re: Why is MySQL more

    Hannu Krosing <hannu@tm.ee> — 2002-08-12T07:11:53Z

    On Mon, 2002-08-12 at 13:14, Curt Sampson wrote:
    > On 12 Aug 2002, Hannu Krosing wrote:
    > 
    > > Are you saying that inheritance in SQL is something fundamentally
    > > different than inheritance in OO languages ?
    > 
    > Yes.
    > 
    
    ...
    
    > So is an instance a relation (a set of tuples) or a tuple?
    
    An instance is a tuple. The relation is the Class. The relation header
    is the class definition.
    
    > If the former, consider the following argument.
    > 
    > In an object oriented program I can have a class C, and a subclass C'
    > that inherits from C. Now, in any situation that calls for an instance
    > of C, I can instead use an instance of C'. This is polymorphism.
    > 
    > Now, if an instance is equivalant to tuple, and a relation inherits from
    > another relation, I'd guess that a relation is equivalant to a class.
    
    Yes.
    
    > But given relation R' inheriting from relation R, does that mean that I
    > can use a tuple from R' anywhere I could use a tuple from R? No, obviously
    > not, as the two tuples have a different number of attributes, to start with.
    
    The classes C and C' also have different number of 'attributes', but
    what matters, is that C' has all the attributes that C has, so you can
    use an instance of C' everywhere an instance of C is needed. The same is
    true of table inheritance - tuple from R' has all the attributes that a
    tuple from R has.
    
    ...
    
    > > All _simple_ inheritance problems are easily solved by simple relational
    > > solutions. The general problem of much more typing and debugging, less
    > > clues for optimiser etc. are not solved by _simple_ relational
    > > solutions.
    > 
    > Can you please give me two or three examples of problems that are
    > not solved by simple relational solutions, and how table inheritance
    > solves them?
    
    From implementors POW:
    
    Updatable VIEWs 
    
    The subset of 'views' that inheritance creates are updatable by default
    with no additional effort from the programmer. It is ready for
    inheritance because it is inherently more difficult to solve the view
    updatability problem for a general case than for the limited set of
    views used by inheritance.
    
    
    > > Of maybe people are diversifying, using inheritance for is-a
    > > relationships and relational model for has-a relationships.
    > 
    > Well, it seems to me that the relational model better supports the is-a
    > relationship. With the relational model, I can specify a column in a
    > table that specifies what that particular entity is, and that can be set
    > to one and only one value.
    
    When using inheritance both of these (defining and setting) are done
    automatically.
    
    > With the table inheritance model, how are we
    > ensuring that, if tables R' and R'' both inherit from R, when a tuple
    > is in R' relating to another tuple in R (or is that the same tuple),
    > there's not also such a relation between a tuple in R'' and R?
    
    In OOR _model_ we define a constraint. 
    
    In postgreSQL we first fix the constraints spanning inheritance trees
    problem and then define a constraint ;)
    
    -------------
    Hannu
    
    
    

16. Re: OOP real life example (was Re: Why is MySQL more chosen

    Curt Sampson <cjs@cynic.net> — 2002-08-12T08:14:02Z

    On 12 Aug 2002, Hannu Krosing wrote:
    
    > Are you saying that inheritance in SQL is something fundamentally
    > different than inheritance in OO languages ?
    
    Yes.
    
    > > (For example, the distinction
    > > between types and instances of types is critical in OO theory. What are
    > > the TI equivalants of this?)
    >
    > If by TI you mean type instance....
    
    Sorry, I shouldn't have abbreviated this. By "TI" I meant "table
    inheritance."
    
    > then the equivalent of of an instance is
    > a relation (i.e. one row in an (inherited) table).
    
    As I understand it, one row in a table, inherited or not, is a
    tuple, not a relation. The definitions I'm familar with are Date's:
    a relation is a header, describing the types of attributes within
    the tuple, and a set of tuples conforming to that header, and a
    relvar is a variable that holds such a relation. (His definitions
    seem to be the ones in common use--Korth/Silberschatz agree with
    him, though they don't use the relvar concept AFIK.)
    
    So is an instance a relation (a set of tuples) or a tuple?
    
    If the former, consider the following argument.
    
    In an object oriented program I can have a class C, and a subclass C'
    that inherits from C. Now, in any situation that calls for an instance
    of C, I can instead use an instance of C'. This is polymorphism.
    
    Now, if an instance is equivalant to tuple, and a relation inherits from
    another relation, I'd guess that a relation is equivalant to a class.
    But given relation R' inheriting from relation R, does that mean that I
    can use a tuple from R' anywhere I could use a tuple from R? No, obviously
    not, as the two tuples have a different number of attributes, to start with.
    So this analogy is now breaking down.
    
    I suppose I could try to work out here if you really mean that
    (using the strict Date sense of the terms here) the relvars are
    classes, and the relations that they hold are instances. But that
    seems to get a bit sticky too. I think it's better if I wait at
    this point for you to provide some further clarification. Would
    you mind doing so? Specifically, what is the equivalant of a class,
    and what is the equivalant of an instance? What are the consequences
    of this, if you know them?
    
    > All _simple_ inheritance problems are easily solved by simple relational
    > solutions. The general problem of much more typing and debugging, less
    > clues for optimiser etc. are not solved by _simple_ relational
    > solutions.
    
    Can you please give me two or three examples of problems that are
    not solved by simple relational solutions, and how table inheritance
    solves them?
    
    > Of maybe people are diversifying, using inheritance for is-a
    > relationships and relational model for has-a relationships.
    
    Well, it seems to me that the relational model better supports the is-a
    relationship. With the relational model, I can specify a column in a
    table that specifies what that particular entity is, and that can be set
    to one and only one value. With the table inheritance model, how are we
    ensuring that, if tables R' and R'' both inherit from R, when a tuple
    is in R' relating to another tuple in R (or is that the same tuple),
    there's not also such a relation between a tuple in R'' and R?
    
    cjs
    -- 
    Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
        Don't you know, in this new Dark Age, we're all light.  --XTC
    
    
    

17. Re: OOP real life example (was Re: Why is MySQL more

    Greg Copeland <greg@copelandconsulting.net> — 2002-08-12T14:00:17Z

    On Mon, 2002-08-12 at 00:29, Hannu Krosing wrote:
    > On Mon, 2002-08-12 at 11:52, Curt Sampson wrote:
    > > On Sun, 11 Aug 2002, Don Baccus wrote:
    [snip]
    > > But anyway, I have no particularly huge objection to syntatic sugar
    > > alone. I do have objections to it when it's not saving much typing. (It
    > > is in this case, but that could be fixed with better automatic support
    > > of view updates.)
    > > 
    > > But my real objection is when it makes things more confusing, rather
    > > than less, which I think is definitely happening here.
    > 
    > What makes things more confusing is poor understanding of a feature, not
    > the feature itself. 
    
    Agreed.  Just because a feature may not be well understood by the masses
    doesn't mean the feature is worthless.
    
    > 
    > > I've never
    > > seen a rigourous explanation of our model of table inheritance,
    > > nor any model that was more obviously correct than another. And
    > > the parallel drawn with inheritance in OO languages is a false
    > > parallel that adds to the confusion.
    > 
    > Are you saying that inheritance in SQL is something fundamentally
    > different than inheritance in OO languages ?
    > 
    
    Hmmm...there might be.  Curt raises in interesting point below.  Do keep
    in mind that I believe he's specifically referring to table inheritance
    and not the broad scope of "language wide" inheritance.
    
    
    > > (For example, the distinction
    > > between types and instances of types is critical in OO theory. What are
    > > the TI equivalants of this?)
    > 
    > If by TI you mean type instance then the equivalent of of an instance is
    > a relation (i.e. one row in an (inherited) table).
    > 
    
    Look a little deeper here.  In other OO implementations, I can define a
    class (say class a) which has no instances (abstract base class). 
    Furthermore, I can take this case and use it for building blocks
    (assuming multiple inheritance is allowed in this world) by combining
    with other classes (z inherits from a, b, c; whereby classes a, b, c
    still do not have an actual instance).  I can create an instance of my
    newly inherited class (z).
    
    Seems to me that there is some distinction between types (classes) and
    and type instances (instance of a specific class) as it pertains to it's
    usability.
    
    How exactly would you create an abstract base class for table type?
    
    I'm still trying to put my brain around exactly what the implications
    are here, but I *think* this is what curt was trying to stress.  Curt,
    feel free to correct me as needed.
    
    
    > > All this is borne out by the regular questions one sees about
    > > inheritance in the mailing lists. I'll admit a good part of it is
    > > due to the broken implementation of inheritance, but all of the
    > > problems I've ever seen are easily solved with very simple relational
    > > solutions.
    > 
    > All _simple_ inheritance problems are easily solved by simple relational
    > solutions. The general problem of much more typing and debugging, less
    > clues for optimiser etc. are not solved by _simple_ relational
    > solutions.
    
    I agree with Hannu here.  Curt's comment seems like lip service.  Worth
    noting too, even if it were not for the issues pointed out by Hannu
    here, Curt's statement certainly does nothing to invalidate the concept
    of table inheritance.  After all, most camps are happy when there are
    multiple means to an end.  Just because it can be done via method-x,
    doesn't invalid method-y.  The inverse is probably true too.  ;)
    
    > 
    > > Maybe the inheritance thing is causing people to turn off the relational
    > > parts of their brain or something.
    >  
    > Of maybe people are diversifying, using inheritance for is-a
    > relationships and relational model for has-a relationships.
    
    That's an interesting point.
    
    Greg
    
    

18. Re: OOP real life example (was Re: Why is MySQL more

    Oliver Elphick <olly@lfix.co.uk> — 2002-08-12T15:39:42Z

    On Mon, 2002-08-12 at 15:00, Greg Copeland wrote:
    ...
    > Look a little deeper here.  In other OO implementations, I can define a
    > class (say class a) which has no instances (abstract base class). 
    > Furthermore, I can take this case and use it for building blocks
    > (assuming multiple inheritance is allowed in this world) by combining
    > with other classes (z inherits from a, b, c; whereby classes a, b, c
    > still do not have an actual instance).  I can create an instance of my
    > newly inherited class (z).
    > 
    > Seems to me that there is some distinction between types (classes) and
    > and type instances (instance of a specific class) as it pertains to it's
    > usability.
    > 
    > How exactly would you create an abstract base class for table type?
    
    CREATE TABLE abstract_base (
       cols ...,
       CONSTRAINT "No data allowed in table abstract_base!" CHECK (1 = 0)
    )
    
    This assumes that the constraint is not inherited or can be removed in
    child tables.
    
    -- 
    Oliver Elphick                                Oliver.Elphick@lfix.co.uk
    Isle of Wight, UK                            
    http://www.lfix.co.uk/oliver
    GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                     ========================================
         "And he spake a parable unto them to this end, that men
          ought always to pray, and not to faint."       
                                                 Luke 18:1 
    
    
    

19. Re: OOP real life example (was Re: Why is MySQL more

    Greg Copeland <greg@copelandconsulting.net> — 2002-08-12T16:30:12Z

    On Mon, 2002-08-12 at 10:39, Oliver Elphick wrote:
    > On Mon, 2002-08-12 at 15:00, Greg Copeland wrote:
    > > How exactly would you create an abstract base class for table type?
    > 
    > CREATE TABLE abstract_base (
    >    cols ...,
    >    CONSTRAINT "No data allowed in table abstract_base!" CHECK (1 = 0)
    > )
    > 
    > This assumes that the constraint is not inherited or can be removed in
    > child tables.
    > 
    
    Why would I assume that constraints would not be inherited?  Seems as a
    general rule of thumb, you'd want the constraints to be inherited.  Am I
    missing something?
    
    Also, if I remove the constraint on the child table, doesn't that really
    mean I'm removing the constraint on the parent table?  That would seem
    to violate the whole reason of having constraints.  If a constraint is
    placed in an ABC and we find that we later need to remove it for EVERY
    derived class, doesn't that imply it shouldn't of been in there to begin
    with?  After all, in this case, we're saying that each and every derived
    class needs to overload or drop a specific constraint.  That strikes me
    as being rather obtuse.
    
    That, in it self, I find rather interesting.  Is there any papers or
    books which offers explanation of how constraints should handled for
    table inheritance?
    
    Greg
    
    

20. Re: OOP real life example (was Re: Why is MySQL more

    Oliver Elphick <olly@lfix.co.uk> — 2002-08-12T17:46:14Z

    On Mon, 2002-08-12 at 17:30, Greg Copeland wrote:
    > On Mon, 2002-08-12 at 10:39, Oliver Elphick wrote:
    > > On Mon, 2002-08-12 at 15:00, Greg Copeland wrote:
    > > > How exactly would you create an abstract base class for table type?
    > > 
    > > CREATE TABLE abstract_base (
    > >    cols ...,
    > >    CONSTRAINT "No data allowed in table abstract_base!" CHECK (1 = 0)
    > > )
    > > 
    > > This assumes that the constraint is not inherited or can be removed in
    > > child tables.
    > > 
    > 
    > Why would I assume that constraints would not be inherited?  Seems as a
    > general rule of thumb, you'd want the constraints to be inherited.  Am I
    > missing something?
    
    You are right, but I was stuck trying to think of a constraint that
    would restrict the abstract base but not its descendants.  Instead of
    CHECK (1 = 0), I think we can use a function that checks whether the
    current table is the abstract base and returns false if it is.  That
    would be validly heritable.  (CHECK (tableoid != 12345678))
     
    > Also, if I remove the constraint on the child table, doesn't that really
    > mean I'm removing the constraint on the parent table?  That would seem
    > to violate the whole reason of having constraints.  If a constraint is
    > placed in an ABC and we find that we later need to remove it for EVERY
    > derived class, doesn't that imply it shouldn't of been in there to begin
    > with?  After all, in this case, we're saying that each and every derived
    > class needs to overload or drop a specific constraint.  That strikes me
    > as being rather obtuse.
    
    Yes, it would be clumsy, and I think you are correct that constraints
    should not be removable.
    
    The inheritance model I am familiar with is that of Eiffel, where
    constraints are additive down the hierarchy.  That is, an invariant on
    the base class applies in its descendants along with any invariants
    added by the descendant or intermediate classes.  That language has the
    concept of a deferred class, which is the parallel of the abstract base
    table we are discussing.  A deferred class cannot be directly
    instantiated.  To do the same in the table hierarchy would require a
    keyword to designate a table as an abstract table (CREATE ABSTRACT TABLE
    xxx ...?).  In the absence of that, a constraint based on the table
    identity will have to do.
    
    -- 
    Oliver Elphick                                Oliver.Elphick@lfix.co.uk
    Isle of Wight, UK                            
    http://www.lfix.co.uk/oliver
    GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                     ========================================
         "And he spake a parable unto them to this end, that men
          ought always to pray, and not to faint."       
                                                 Luke 18:1 
    
    
    

21. Re: OOP real life example (was Re: Why is MySQL more

    Curt Sampson <cjs@cynic.net> — 2002-08-13T01:34:48Z

    Ok, big bundled up reply here to various people.
    
    > From: Greg Copeland <greg@CopelandConsulting.Net>
    >
    > > What makes things more confusing is poor understanding of a feature, not
    > > the feature itself.
    >
    > Agreed.  Just because a feature may not be well understood by the masses
    > doesn't mean the feature is worthless.
    
    Yeah, but if it's not understood by fairly smart people familiar
    with both relational theory and OO programming? If the feature is
    confusing because it appears to be something it's not, that's a
    feature problem, not a problem with the people trying to understand
    it. Maybe all that's necessary to fix it is a terminology change,
    but even so....
    
    > Hmmm...there might be.  Curt raises in interesting point below.  Do keep
    > in mind that I believe he's specifically referring to table inheritance
    > and not the broad scope of "language wide" inheritance.
    
    Yes.
    
    > > All _simple_ inheritance problems are easily solved by simple relational
    > > solutions. The general problem of much more typing and debugging, less
    > > clues for optimiser etc. are not solved by _simple_ relational
    > > solutions.
    >
    > I agree with Hannu here.  Curt's comment seems like lip service.
    
    Well, as I said: examples please. Quite frankly, between the lack
    of a clear model of table inheritance (Hannu seems to have one,
    but this needs to be written up in unambiguous form and put into
    the postgres manual) and the bugs in the postgres implementation
    of table inheritance, I've found the relational model much easier
    to use for solving problems.
    
    > From: Oliver Elphick <olly@lfix.co.uk>
    >
    > On Mon, 2002-08-12 at 15:00, Greg Copeland wrote:
    > > How exactly would you create an abstract base class for table type?
    >
    > CREATE TABLE abstract_base (
    >    cols ...,
    >    CONSTRAINT "No data allowed in table abstract_base!" CHECK (1 = 0)
    > )
    >
    > This assumes that the constraint is not inherited or can be removed in
    > child tables.
    
    Are we then assuming that tuples in the child tables do not appear
    in the base table? That's more or less what I'd assumed when I
    originally heard about table inheritance (after all, instantiating
    a child object does not automatically instantiate a separate copy
    of the parent object), but the SQL standard, postgres, and I believe other
    systems make the exact opposite assumption.
    
    If the child table tuples do appear in the parent, you've now got
    a situation analogous to the current postgres situation where a
    constraint on the parent table is an outright lie. (I'm thinking
    of the UNIQUE constraint which guarantees that all values in a
    column will be unique--and then they aren't.) I consider breaking
    the relational model this badly a completely unacceptable cost no
    matter what additional functionality you're wanting to add, and I
    expect that most other people do, too.
    
    > From: Greg Copeland <greg@CopelandConsulting.Net>
    >
    > That, in it self, I find rather interesting.  Is there any papers or
    > books which offers explanation of how constraints should handled for
    > table inheritance?
    
    Here again, I'd love to hear about some references, too. I see a
    lot of people saying they like table inheritance; I don't see anyone
    (except maybe Hannu) who seems to have a clear idea of how it should
    work.
    
    cjs
    -- 
    Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
        Don't you know, in this new Dark Age, we're all light.  --XTC
    
    
    

22. Re: OOP real life example (was Re: Why is MySQL more

    Don Baccus <dhogaza@pacifier.com> — 2002-08-13T01:48:20Z

    Curt Sampson wrote:
    
    > ... the bugs in the postgres implementation
    > of table inheritance, I've found the relational model much easier
    > to use for solving problems.
    
    No one has argued that the shortcomings (not bugs, really, just things 
    left out) makes the current implementation of very limited utility.  As 
    I mention this is exactly why we choose not to use it at OpenACS.
    
    On the other hand at least we took the time to understand how it 
    actually does work before criticizing it.
    
    It's a pity, as I pointed out the reduction in joins alone would really 
    be great.
    
    
    -- 
    Don Baccus
    Portland, OR
    http://donb.photo.net, http://birdnotes.net, http://openacs.org
    
    
    

23. Re: OOP real life example (was Re: Why is MySQL more

    Greg Copeland <greg@copelandconsulting.net> — 2002-08-13T02:33:19Z

    On Mon, 2002-08-12 at 20:34, Curt Sampson wrote:
    > Ok, big bundled up reply here to various people.
    > 
    > > From: Greg Copeland <greg@CopelandConsulting.Net>
    > >
    > > > What makes things more confusing is poor understanding of a feature, not
    > > > the feature itself.
    > >
    > > Agreed.  Just because a feature may not be well understood by the masses
    > > doesn't mean the feature is worthless.
    > 
    > Yeah, but if it's not understood by fairly smart people familiar
    > with both relational theory and OO programming? If the feature is
    > confusing because it appears to be something it's not, that's a
    > feature problem, not a problem with the people trying to understand
    > it. Maybe all that's necessary to fix it is a terminology change,
    > but even so....
    
    You're constantly confusing Postgres' implementation with a "desired"
    implementation.  Below, I think, is the effort to figure out exactly
    what a "desired implementation" really is.
    
    If a feature is partially implemented, of course it's going to be
    confusing to use.
    
    Let's please stop beating this horse Curt.  At this point, I think the
    horse is floating upside down in a pond somewhere...yep...and the
    buzzards are coming.
    
    Please.  Beating people with a stick isn't suddenly going to make
    everyone share your view point.
    
    > > > All _simple_ inheritance problems are easily solved by simple relational
    > > > solutions. The general problem of much more typing and debugging, less
    > > > clues for optimiser etc. are not solved by _simple_ relational
    > > > solutions.
    > >
    > > I agree with Hannu here.  Curt's comment seems like lip service.
    > 
    > Well, as I said: examples please. Quite frankly, between the lack
    > of a clear model of table inheritance (Hannu seems to have one,
    > but this needs to be written up in unambiguous form and put into
    > the postgres manual) and the bugs in the postgres implementation
    > of table inheritance, I've found the relational model much easier
    > to use for solving problems.
    
    If you're so keen on examples, please provide one that justifies such a
    boastful statement.  Hannu has done a pretty fair job of beating ya back
    every time.  Personally, in this case, I don't really need examples are
    it's pretty obvious a braggart statement full of bias.  So second
    thought, perhaps we can let this one alone.
    
    I do agree that it looks like Hannu is doing a fairly good job of
    providing some constructive direction here.  Hannu, please keep up the
    good work.  ;)
    
    > 
    > > From: Oliver Elphick <olly@lfix.co.uk>
    > >
    > > On Mon, 2002-08-12 at 15:00, Greg Copeland wrote:
    > > > How exactly would you create an abstract base class for table type?
    > >
    > > CREATE TABLE abstract_base (
    > >    cols ...,
    > >    CONSTRAINT "No data allowed in table abstract_base!" CHECK (1 = 0)
    > > )
    > >
    > > This assumes that the constraint is not inherited or can be removed in
    > > child tables.
    > 
    > Are we then assuming that tuples in the child tables do not appear
    > in the base table? That's more or less what I'd assumed when I
    > originally heard about table inheritance (after all, instantiating
    > a child object does not automatically instantiate a separate copy
    > of the parent object), but the SQL standard, postgres, and I believe other
    > systems make the exact opposite assumption.
    
    That's actually my exact assumption...that is, that tuples in the parent
    did not exist in the child.  Is that not true?  Can you point me to any
    references?
    
    > 
    > If the child table tuples do appear in the parent, you've now got
    > a situation analogous to the current postgres situation where a
    > constraint on the parent table is an outright lie. (I'm thinking
    > of the UNIQUE constraint which guarantees that all values in a
    [snip]
    
    I knew that there are *implementation* issues with postgres that causes
    problems with constraints, etc...I didn't realize that was the reason.
    
    > 
    > > From: Greg Copeland <greg@CopelandConsulting.Net>
    > >
    > > That, in it self, I find rather interesting.  Is there any papers or
    > > books which offers explanation of how constraints should handled for
    > > table inheritance?
    > 
    > Here again, I'd love to hear about some references, too. I see a
    > lot of people saying they like table inheritance; I don't see anyone
    > (except maybe Hannu) who seems to have a clear idea of how it should
    > work.
    
    Well, you seem to be making references to "...SQL standard, postgres,
    and I believe other systems...".  I was counting on you or someone else
    to point us to existing references.  I'm fairly sure we can manage to
    wade through it to walk a sane and fruitful path...it would just be a
    less bumpier road if we all spoke the same OO'ish dialect and shared a
    common knowledge base that we can all agree on for starters.  So, you
    got anything to share here???  ;)
    
    
    Greg
    
    

24. Re: OOP real life example (was Re: Why is MySQL more

    Curt Sampson <cjs@cynic.net> — 2002-08-13T03:57:07Z

    On Mon, 12 Aug 2002, Don Baccus wrote:
    
    > It's a pity, as I pointed out the reduction in joins alone would really
    > be great.
    
    So implement the same thing relationally, and get your reduction
    in joins.  There are tricks, discussed on this very list in the
    last few days, that would let you do what you need.
    
    cjs
    -- 
    Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
        Don't you know, in this new Dark Age, we're all light.  --XTC
    
    
    

25. Re: OOP real life example (was Re: Why is MySQL more

    Hannu Krosing <hannu@tm.ee> — 2002-08-13T04:19:56Z

    On Tue, 2002-08-13 at 10:16, Curt Sampson wrote:
    > On 12 Aug 2002, Greg Copeland wrote:
    ...
    
    > > Are we then assuming that tuples in the child tables do not appear
    > > > in the base table? That's more or less what I'd assumed when I
    > > > originally heard about table inheritance (after all, instantiating
    > > > a child object does not automatically instantiate a separate copy
    > > > of the parent object),
    
    Tuples in the child table "appear" in parent table when you do a plain
    SELECT, as thei IMHO should, because you _do_ want to get all kinds of
    animals when doing select from animals.
    
    They do not appear in parent table when you do 
    
    SELECT .. FROM ONLY parent_table
    
    It used to be the opposite (one needed to do "SELECT .. FROM 
    parent_table* "  to get tuples from inherited tables as well ) but it
    was changed because SQL99 mandated that inherited tables should be
    included by default. That's for SQL99's "CREATE TABLE (...) UNDER
    another_table" kind of single inheritance.
    
    > > > but the SQL standard, postgres, and I believe other
    > > > systems make the exact opposite assumption.
    > >
    > > That's actually my exact assumption...that is, that tuples in the parent
    > > did not exist in the child.
    > 
    > Sorry, by "opposite assumption," I meant these two opposites:
    
    There are two main orthogonal ways of mapping inheritance to relational
    model.
    
    >     1. Tuples in child tables appear in the parent table.
    
    That's the way you implemented the samples in the beginning of this
    thread, i.e. keep the common part in one table and extend by stitching
    columns fron child tables to the "side" using foreign keys. 
    
    This makes it easy to enforce primary keys and uniqueness, but grows
    ugly quite fast if you have deep inhweritance hierarchies  - if you have
    inheritance 5 levels deep, you need 4 joins to get a tuple from the
    last-descendant table.
    
    It also makes automatic updating ov views a pain to do.
    
    >     2. Tuples in child tables do not appear in the parent table.
    
    This is how postgres implements it - make a new table for each inherited
    table and do UNION join when doing a SELECT .
    
    This makes it hard to implement uniqueness and primary keys, but easy to
    do updates and inserts.
    
    > Take your pick, keeping in mind that the sources I know of (Appendix E of _The
    > Third Manifesto_, _Database Systems Concepts_ (ISTR), the SQL standard and
    > postgres currently all assume #1.
    
    I would like yet another implementation, more in line with SQL99's
    single inheritance, where all inherited tables would be stored in the
    same pagefile (so that you can put a unique index on them and it would
    "just work" because TIDs all point into the same file). Fast access to
    some single table ONLY could be done using partial indexes on tableoid.
    
    This can't be mapped directly on SQL92 kind of relational model, but can
    more or less be mimicked by setting the new fields to NULL for tuples
    belonging to parent relation.
    
    > If we find the one we pick is unworkable, we can always go back
    > and try the other.
    > 
    > > > If the child table tuples do appear in the parent, you've now got
    > > > a situation analogous to the current postgres situation where a
    > > > constraint on the parent table is an outright lie. (I'm thinking
    > > > of the UNIQUE constraint which guarantees that all values in a
    > > [snip]
    > >
    > > I knew that there are *implementation* issues with postgres that causes
    > > problems with constraints, etc...I didn't realize that was the reason.
    > 
    > Well, assuming we are mapping inheritance back into relational stuff
    > behind the scenes (which it appears to me we are doing now), we can just
    > map back to the relation method I demonstrated earlier of doing what
    > someone wanted to do with table inheritance (child tables contain only
    > foreign key and child-specific data; parent table contains primary key
    > and all parent data) and that will fix the implementation problem.
    
    The main problems I pointed out above: 
    
    1. hard-to-implement UPDATE rules, theoretically possible is not good
    enough for real use ;)
    
    2. too much joining for deep inheritance hierarchies .
    
    > Or people have proposed other things, such as cross-table constraints,
    > to try to do this.
    > 
    > > Well, you seem to be making references to "...SQL standard, postgres,
    > > and I believe other systems...".  I was counting on you or someone else
    > > to point us to existing references.
    > 
    > Well, counting on me is not good, since the whole reason I started this
    > was because I found the issue confusing in part due to the lack of any
    > obvious standards here that I could find. :-) But here's what I do have:
    > 
    >     Date, Darwen, _Foundation for Future Database Systems, The
    >     Third Manefesto (Second Edition)_. Appendex E.
    > 
    >     Silberschatz, Korth, Sudarshan, _Database Systems Concepts
    >     (Fourth Edition)_. I think it's around chapter 9. (My copy is
    >     at home right now.)
    > 
    >     SQL Standard. I don't have it handy. Anyone? Anyone? Bueller?
    
    I got mine from
    
    http://www.sqlstandards.org/SC32/WG3/Progression_Documents/FCD/fcd2found.pdf
    
    Quite hard to read, as standard in general tend to be ;)
    
    I also have PDF's with a large [FINAL] stamp on them, which I cant
    remember where I got (but I posted the link here a few months ago)
    
    >     Postgres. Known broken implementation, but we can at least poke
    >     stuff into it and see what it does.
    > 
    > In addition, OO programming gets mentioned ocassionally. I don't
    > think that table inheritance is anything related
    
    IMHO table inheritance is a natural relational extension to type
    inheritance - if you create a subtype that is-a parent type (bird is an
    animal), you also want to be able to treat it as such in queries - i.e.
    be able select all animals, and not have to manually make the connection
    between OO (type inheritance) and Relational
    (INSERT/UPDATE/SELECT/DELETE) worlds.
    
    > (and I've spent
    > a lot of time in the last couple of years developing methods to
    > make my OO programs and relational databases play nice with each
    > other),
    
    So have the database guys, adding OO stuff to databases and all ;)
    
    > but it might help to have some idea of what people to do
    > connect the two, in case some people think that they are or should
    > be connected. You can start by checking out this page for a few
    > ways of creating objects from database information:
    > 
    >     http://www.martinfowler.com/isa/inheritanceMappers.html
    
    I'll try to check it out .
    
    

26. Re: OOP real life example (was Re: Why is MySQL more

    Curt Sampson <cjs@cynic.net> — 2002-08-13T05:16:24Z

    On 12 Aug 2002, Greg Copeland wrote:
    
    > You're constantly confusing Postgres' implementation with a "desired"
    > implementation.
    
    No. I'm still trying to figure out what the desired implementation
    actually is. This is documented nowhere.
    
    > If you're so keen on examples, please provide one that justifies such a
    > boastful statement.
    
    You appear to be saying I should provide an example that proves there
    exists no table inheritance configuration that cannot easily be done
    with a relational implementation. That's not possible to do, sorry.
    
    I will revise my opinion the instant someone shows me something that I
    can't do relationally, or is easy to implement with inheritance, and
    difficult with relational methods. Now you know what you need to do, and
    if you have no example, we can drop the whole thing. But I am honestly
    interested to see just what it is that makes table inheritance so great.
    
    > > Are we then assuming that tuples in the child tables do not appear
    > > in the base table? That's more or less what I'd assumed when I
    > > originally heard about table inheritance (after all, instantiating
    > > a child object does not automatically instantiate a separate copy
    > > of the parent object), but the SQL standard, postgres, and I believe other
    > > systems make the exact opposite assumption.
    >
    > That's actually my exact assumption...that is, that tuples in the parent
    > did not exist in the child.
    
    Sorry, by "opposite assumption," I meant these two opposites:
    
        1. Tuples in child tables appear in the parent table.
    
        2. Tuples in child tables do not appear in the parent table.
    
    Take your pick, keeping in mind that the sources I know of (Appendix E of _The
    Third Manifesto_, _Database Systems Concepts_ (ISTR), the SQL standard and
    postgres currently all assume #1.
    
    If we find the one we pick is unworkable, we can always go back
    and try the other.
    
    > > If the child table tuples do appear in the parent, you've now got
    > > a situation analogous to the current postgres situation where a
    > > constraint on the parent table is an outright lie. (I'm thinking
    > > of the UNIQUE constraint which guarantees that all values in a
    > [snip]
    >
    > I knew that there are *implementation* issues with postgres that causes
    > problems with constraints, etc...I didn't realize that was the reason.
    
    Well, assuming we are mapping inheritance back into relational stuff
    behind the scenes (which it appears to me we are doing now), we can just
    map back to the relation method I demonstrated earlier of doing what
    someone wanted to do with table inheritance (child tables contain only
    foreign key and child-specific data; parent table contains primary key
    and all parent data) and that will fix the implementation problem.
    
    Or people have proposed other things, such as cross-table constraints,
    to try to do this.
    
    > Well, you seem to be making references to "...SQL standard, postgres,
    > and I believe other systems...".  I was counting on you or someone else
    > to point us to existing references.
    
    Well, counting on me is not good, since the whole reason I started this
    was because I found the issue confusing in part due to the lack of any
    obvious standards here that I could find. :-) But here's what I do have:
    
        Date, Darwen, _Foundation for Future Database Systems, The
        Third Manefesto (Second Edition)_. Appendex E.
    
        Silberschatz, Korth, Sudarshan, _Database Systems Concepts
        (Fourth Edition)_. I think it's around chapter 9. (My copy is
        at home right now.)
    
        SQL Standard. I don't have it handy. Anyone? Anyone? Bueller?
    
        Postgres. Known broken implementation, but we can at least poke
        stuff into it and see what it does.
    
    In addition, OO programming gets mentioned ocassionally. I don't
    think that table inheritance is anything related (and I've spent
    a lot of time in the last couple of years developing methods to
    make my OO programs and relational databases play nice with each
    other), but it might help to have some idea of what people to do
    connect the two, in case some people think that they are or should
    be connected. You can start by checking out this page for a few
    ways of creating objects from database information:
    
        http://www.martinfowler.com/isa/inheritanceMappers.html
    
    cjs
    -- 
    Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
        Don't you know, in this new Dark Age, we're all light.  --XTC
    
    
    

27. Re: OOP real life example (was Re: Why is MySQL more

    Greg Copeland <greg@copelandconsulting.net> — 2002-08-13T06:05:14Z

    On Tue, 2002-08-13 at 00:16, Curt Sampson wrote:
    > I will revise my opinion the instant someone shows me something that I
    > can't do relationally, or is easy to implement with inheritance, and
    > difficult with relational methods. Now you know what you need to do, and
    > if you have no example, we can drop the whole thing. But I am honestly
    > interested to see just what it is that makes table inheritance so great.
    
    I think here-in is the first problem.  You seem to insist that the world
    can only allow for one or the other and that the two approaches are
    mutually exclusive.  I tends to think that there is room for both.  One
    would also seem to allow that they can actually be complimentary
    (referring to Hannu's recent is-a & has-a inheritance comments).
    
    Can we let go of x is better than y and just concentrate on how y can be
    made better without regard for x?
    
    After it's all said and done, who knows, everyone might agree that table
    inheritance is just a plain, bad idea.
    
    > >
    > > I knew that there are *implementation* issues with postgres that causes
    > > problems with constraints, etc...I didn't realize that was the reason.
    > 
    > Well, assuming we are mapping inheritance back into relational stuff
    > behind the scenes (which it appears to me we are doing now), we can just
    > map back to the relation method I demonstrated earlier of doing what
    > someone wanted to do with table inheritance (child tables contain only
    > foreign key and child-specific data; parent table contains primary key
    > and all parent data) and that will fix the implementation problem.
    
    This is what I imagined the preferred solution would be, however, I'm
    also assuming it would be the more complex to implement *properly*.  
    
    > 
    > Or people have proposed other things, such as cross-table constraints,
    > to try to do this.
    
    Ya, I was kicking this idea around in my head tonight.  Didn't get far
    on it.  So I should look for postings in the archive about this specific
    implementation?
    
    > 
    > > Well, you seem to be making references to "...SQL standard, postgres,
    > > and I believe other systems...".  I was counting on you or someone else
    > > to point us to existing references.
    > 
    > Well, counting on me is not good, since the whole reason I started this
    > was because I found the issue confusing in part due to the lack of any
    > obvious standards here that I could find. :-) But here's what I do have:
    > 
    >     Date, Darwen, _Foundation for Future Database Systems, The
    >     Third Manefesto (Second Edition)_. Appendex E.
    
    Is this a book or a paper.  I have a paper that I've been reading
    (ack...very, very dry) by these guys of the same name.
    
    > 
    >     Silberschatz, Korth, Sudarshan, _Database Systems Concepts
    >     (Fourth Edition)_. I think it's around chapter 9. (My copy is
    >     at home right now.)
    > 
    >     SQL Standard. I don't have it handy. Anyone? Anyone? Bueller?
    
    So the SQL standard does address table inheritance?  Not that this means
    I feel that they've done the right thing...but what did the
    specification have to say on the subject?  Any online references?
    
    > 
    >     Postgres. Known broken implementation, but we can at least poke
    >     stuff into it and see what it does.
    > 
    > In addition, OO programming gets mentioned ocassionally. I don't
    > think that table inheritance is anything related (and I've spent
    
    Yes.  I think I'm starting to buy into that too, however, I'm not sure
    that it has to mean that no value is within.  In other words, I'm still
    on the fence on a) table inheritance really makes much "OO" sense and b)
    even if it does or does not, is there value in any form of it's
    implementation (whatever the end result looks like) .
    
    > a lot of time in the last couple of years developing methods to
    > make my OO programs and relational databases play nice with each
    > other), but it might help to have some idea of what people to do
    > connect the two, in case some people think that they are or should
    > be connected. You can start by checking out this page for a few
    > ways of creating objects from database information:
    > 
    >     http://www.martinfowler.com/isa/inheritanceMappers.html
    > 
    
    Thanks.  Funny, I was reading that just the other day.  ;)
    
    Greg
    
    

28. Re: OOP real life example (was Re: Why is MySQL more

    Curt Sampson <cjs@cynic.net> — 2002-08-13T06:14:46Z

    On 13 Aug 2002, Greg Copeland wrote:
    
    > On Tue, 2002-08-13 at 00:16, Curt Sampson wrote:
    > > I will revise my opinion the instant someone shows me something that I
    > > can't do relationally, or is easy to implement with inheritance, and
    > > difficult with relational methods. Now you know what you need to do, and
    > > if you have no example, we can drop the whole thing. But I am honestly
    > > interested to see just what it is that makes table inheritance so great.
    >
    > I think here-in is the first problem.  You seem to insist that the world
    > can only allow for one or the other and that the two approaches are
    > mutually exclusive.
    
    No, I don't.
    
        1. If it changes the rules, as it were, that is breaks other
        parts of the system, it should go. This is the current state
        of the postgres implementation. I'm guessing it's not the state
        of the desired implementation, once we figure out what that is.
    
        2. If it's just syntactic sugar, that's livable, so long as
        it's quite obvious what it's syntatic sugar for. (In the current
        case, it's not.) It's even good if it saves a lot of effort.
    
        3. If it actually allows you to do something you cannot otherwise
        do, or allows you to do something very difficult with much
        greater ease, it's a good thing and it should stay.
    
    > > Well, assuming we are mapping inheritance back into relational stuff
    > > behind the scenes (which it appears to me we are doing now), we can just
    > > map back to the relation method I demonstrated earlier of doing what
    > > someone wanted to do with table inheritance (child tables contain only
    > > foreign key and child-specific data; parent table contains primary key
    > > and all parent data) and that will fix the implementation problem.
    >
    > This is what I imagined the preferred solution would be, however, I'm
    > also assuming it would be the more complex to implement *properly*.
    
    I don't think so. Both systems are currently, AFICT, pretty simple
    mappings onto the relational system. Once we get the exact details of
    table inheritance behaviour hammered out, I will gladly provide the
    mapping it's possible to create it.
    
    > >     Date, Darwen, _Foundation for Future Database Systems, The
    > >     Third Manefesto (Second Edition)_. Appendex E.
    >
    > Is this a book or a paper.  I have a paper that I've been reading
    > (ack...very, very dry) by these guys of the same name.
    
    It's a book. Apparently the paper is, in comparison, much more lively.
    :-) But I find the book good in that, at the very least, it shows the
    level to which you have to go to come up with a theoretically solid
    basis for something you want to implement.
    
    > So the SQL standard does address table inheritance?
    
    Yes.
    
    > Not that this means I feel that they've done the right thing...but
    > what did the specification have to say on the subject? Any online
    > references?
    
    I don't have a copy of the spec handy, and have not had time to go and
    dig one up. All I got from it was out of the two book references I gave.
    
    cjs
    -- 
    Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
        Don't you know, in this new Dark Age, we're all light.  --XTC
    
    
    

29. Re: OOP real life example (was Re: Why is MySQL more

    Don Baccus <dhogaza@pacifier.com> — 2002-08-13T13:48:28Z

    Greg Copeland wrote:
    > On Tue, 2002-08-13 at 00:16, Curt Sampson wrote:
    > 
    >>I will revise my opinion the instant someone shows me something that I
    >>can't do relationally, or is easy to implement with inheritance, and
    >>difficult with relational methods.
    
    The traditional view approach requires unnecessary joins, and there's no 
    getting around it.
    
    And yes I know he's not reading my mail and no, don't bother repeating 
    this to him, he'll just continue to ignore the point.
    
    -- 
    Don Baccus
    Portland, OR
    http://donb.photo.net, http://birdnotes.net, http://openacs.org
    
    
    

30. Re: OOP real life example (was Re: Why is MySQL more

    Hannu Krosing <hannu@tm.ee> — 2002-08-13T17:28:18Z

    On Tue, 2002-08-13 at 18:48, Don Baccus wrote:
    > Greg Copeland wrote:
    > > On Tue, 2002-08-13 at 00:16, Curt Sampson wrote:
    > > 
    ...
    > 
    > And yes I know he's not reading my mail and no, don't bother repeating 
    > this to him, he'll just continue to ignore the point.
    
    I suspect that he will still read your (partial) comments in replies to
    your mails and has to look the originals up in archives in case he gets
    interested in what the other guys respond to ;)
    
    -----------------
    Hannu