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

Started by Daniel Lyonsover 24 years ago106 messageshackers
Jump to latest
#1Daniel Lyons
fusion@nmt.edu

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. *)

#2Curt Sampson
cjs@cynic.net
In reply to: Daniel Lyons (#1)
Re: OOP real life example (was Re: Why is MySQL more chosen

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

#3Hannu Krosing
hannu@tm.ee
In reply to: Mario Weilguni (#13)
Re: OOP real life example (was Re: Why is MySQL more

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

#4Hannu Krosing
hannu@tm.ee
In reply to: Hannu Krosing (#3)
Re: OOP real life example (was Re: Why is MySQL more

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

#5Don Baccus
dhogaza@pacifier.com
In reply to: Curt Sampson (#2)
Re: OOP real life example (was Re: Why is MySQL more chosen

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

#6Curt Sampson
cjs@cynic.net
In reply to: Don Baccus (#5)
Re: OOP real life example (was Re: Why is MySQL more chosen

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

#7Don Baccus
dhogaza@pacifier.com
In reply to: Curt Sampson (#6)
Re: OOP real life example (was Re: Why is MySQL more chosen

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

#8Curt Sampson
cjs@cynic.net
In reply to: Don Baccus (#7)
Re: OOP real life example (was Re: Why is MySQL more chosen

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

#9Tom Lane
tgl@sss.pgh.pa.us
In reply to: Curt Sampson (#8)
Re: OOP real life example (was Re: Why is MySQL more chosen

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

#10Christopher Kings-Lynne
chriskl@familyhealth.com.au
In reply to: Curt Sampson (#8)
Re: OOP real life example (was Re: Why is MySQL more chosen

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

#11Don Baccus
dhogaza@pacifier.com
In reply to: Curt Sampson (#8)
Re: OOP real life example (was Re: Why is MySQL more chosen

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

#12Don Baccus
dhogaza@pacifier.com
In reply to: Curt Sampson (#8)
Re: OOP real life example (was Re: Why is MySQL more chosen

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

#13Mario Weilguni
mweilguni@sime.com
In reply to: Don Baccus (#7)
Re: OOP real life example (was Re: Why is MySQL more chosen

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

#14Curt Sampson
cjs@cynic.net
In reply to: Don Baccus (#11)
Re: OOP real life example (was Re: Why is MySQL more chosen

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

#15Hannu Krosing
hannu@tm.ee
In reply to: Hannu Krosing (#4)
Re: OOP real life example (was Re: Why is MySQL more

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

#16Curt Sampson
cjs@cynic.net
In reply to: Hannu Krosing (#4)
Re: OOP real life example (was Re: Why is MySQL more chosen

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

#17Greg Copeland
greg@CopelandConsulting.Net
In reply to: Hannu Krosing (#4)
Re: OOP real life example (was Re: Why is MySQL more

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

#18Oliver Elphick
olly@lfix.co.uk
In reply to: Greg Copeland (#17)
Re: OOP real life example (was Re: Why is MySQL more

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

#19Greg Copeland
greg@CopelandConsulting.Net
In reply to: Oliver Elphick (#18)
Re: OOP real life example (was Re: Why is MySQL more

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

#20Oliver Elphick
olly@lfix.co.uk
In reply to: Greg Copeland (#19)
Re: OOP real life example (was Re: Why is MySQL more

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

#21Curt Sampson
cjs@cynic.net
In reply to: Greg Copeland (#19)
#22Don Baccus
dhogaza@pacifier.com
In reply to: Curt Sampson (#21)
#23Greg Copeland
greg@CopelandConsulting.Net
In reply to: Curt Sampson (#21)
#24Curt Sampson
cjs@cynic.net
In reply to: Don Baccus (#22)
#25Hannu Krosing
hannu@tm.ee
In reply to: Curt Sampson (#24)
#26Curt Sampson
cjs@cynic.net
In reply to: Greg Copeland (#23)
#27Curt Sampson
cjs@cynic.net
In reply to: Curt Sampson (#26)
#28Greg Copeland
greg@CopelandConsulting.Net
In reply to: Curt Sampson (#27)
#29Greg Copeland
greg@CopelandConsulting.Net
In reply to: Curt Sampson (#26)
#30Curt Sampson
cjs@cynic.net
In reply to: Greg Copeland (#29)
#31Don Baccus
dhogaza@pacifier.com
In reply to: Curt Sampson (#26)
#32Lamar Owen
lamar.owen@wgcr.org
In reply to: Greg Copeland (#28)
#33Hannu Krosing
hannu@tm.ee
In reply to: Don Baccus (#31)
#34Curt Sampson
cjs@cynic.net
In reply to: Lamar Owen (#32)
#35Bruce Momjian
pgman@candle.pha.pa.us
In reply to: Curt Sampson (#34)
#36Don Baccus
dhogaza@pacifier.com
In reply to: Bruce Momjian (#35)
#37Bruce Momjian
pgman@candle.pha.pa.us
In reply to: Don Baccus (#36)
#38Hannu Krosing
hannu@tm.ee
In reply to: Christopher Kings-Lynne (#42)
#39Hannu Krosing
hannu@tm.ee
In reply to: Curt Sampson (#34)
#40Hannu Krosing
hannu@tm.ee
In reply to: Hannu Krosing (#39)
#41Curt Sampson
cjs@cynic.net
In reply to: Bruce Momjian (#37)
#42Christopher Kings-Lynne
chriskl@familyhealth.com.au
In reply to: Curt Sampson (#41)
#43Bruce Momjian
pgman@candle.pha.pa.us
In reply to: Curt Sampson (#41)
#44Curt Sampson
cjs@cynic.net
In reply to: Christopher Kings-Lynne (#42)
#45Bruce Momjian
pgman@candle.pha.pa.us
In reply to: Christopher Kings-Lynne (#42)
#46Christopher Kings-Lynne
chriskl@familyhealth.com.au
In reply to: Bruce Momjian (#45)
#47Curt Sampson
cjs@cynic.net
In reply to: Bruce Momjian (#43)
#48Don Baccus
dhogaza@pacifier.com
In reply to: Curt Sampson (#34)
#49Don Baccus
dhogaza@pacifier.com
In reply to: Bruce Momjian (#45)
#50Noname
ngpg@grymmjack.com
In reply to: Curt Sampson (#41)
#51Tom Lane
tgl@sss.pgh.pa.us
In reply to: Christopher Kings-Lynne (#46)
#52Greg Copeland
greg@CopelandConsulting.Net
In reply to: Bruce Momjian (#43)
#53Greg Copeland
greg@CopelandConsulting.Net
In reply to: Tom Lane (#51)
#54Greg Copeland
greg@CopelandConsulting.Net
In reply to: Curt Sampson (#44)
#55Tom Lane
tgl@sss.pgh.pa.us
In reply to: Hannu Krosing (#40)
#56Ross J. Reedstrom
reedstrm@rice.edu
In reply to: Greg Copeland (#54)
#57Joe Conway
mail@joeconway.com
In reply to: Curt Sampson (#44)
#58Greg Copeland
greg@CopelandConsulting.Net
In reply to: Ross J. Reedstrom (#56)
#59Lamar Owen
lamar.owen@wgcr.org
In reply to: Curt Sampson (#34)
#60Tom Lane
tgl@sss.pgh.pa.us
In reply to: Greg Copeland (#54)
#61Rod Taylor
rbt@zort.ca
In reply to: Ross J. Reedstrom (#56)
#62Curt Sampson
cjs@cynic.net
In reply to: Tom Lane (#60)
#63Tom Lane
tgl@sss.pgh.pa.us
In reply to: Curt Sampson (#62)
#64Curt Sampson
cjs@cynic.net
In reply to: Tom Lane (#63)
#65Zeugswetter Andreas SB SD
ZeugswetterA@spardat.at
In reply to: Curt Sampson (#64)
#66Curt Sampson
cjs@cynic.net
In reply to: Zeugswetter Andreas SB SD (#65)
#67Zeugswetter Andreas SB SD
ZeugswetterA@spardat.at
In reply to: Curt Sampson (#66)
#68Curt Sampson
cjs@cynic.net
In reply to: Zeugswetter Andreas SB SD (#67)
#69Zeugswetter Andreas SB SD
ZeugswetterA@spardat.at
In reply to: Curt Sampson (#68)
#70Curt Sampson
cjs@cynic.net
In reply to: Zeugswetter Andreas SB SD (#69)
#71Oliver Elphick
olly@lfix.co.uk
In reply to: Curt Sampson (#70)
#72Greg Copeland
greg@CopelandConsulting.Net
In reply to: Curt Sampson (#70)
#73Peter Gulutzan
71022.733@compuserve.com
In reply to: Greg Copeland (#72)
#74Bruce Momjian
pgman@candle.pha.pa.us
In reply to: Peter Gulutzan (#73)
#75Curt Sampson
cjs@cynic.net
In reply to: Bruce Momjian (#74)
#76Curt Sampson
cjs@cynic.net
In reply to: Curt Sampson (#75)
#77Hannu Krosing
hannu@tm.ee
In reply to: Curt Sampson (#75)
#78Curt Sampson
cjs@cynic.net
In reply to: Hannu Krosing (#77)
#79Jeff Davis
list-pgsql-hackers@empires.org
In reply to: Hannu Krosing (#77)
#80Hannu Krosing
hannu@tm.ee
In reply to: Curt Sampson (#76)
#81Curt Sampson
cjs@cynic.net
In reply to: Jeff Davis (#79)
#82Curt Sampson
cjs@cynic.net
In reply to: Curt Sampson (#81)
#83Hannu Krosing
hannu@tm.ee
In reply to: Curt Sampson (#78)
#84Jeff Davis
list-pgsql-hackers@empires.org
In reply to: Curt Sampson (#81)
#85Hannu Krosing
hannu@tm.ee
In reply to: Curt Sampson (#82)
#86Hannu Krosing
hannu@tm.ee
In reply to: Jeff Davis (#84)
#87Tom Lane
tgl@sss.pgh.pa.us
In reply to: Hannu Krosing (#86)
#88Hannu Krosing
hannu@tm.ee
In reply to: Tom Lane (#87)
#89Ross J. Reedstrom
reedstrm@rice.edu
In reply to: Tom Lane (#87)
#90Hannu Krosing
hannu@tm.ee
In reply to: Curt Sampson (#82)
#91Greg Copeland
greg@CopelandConsulting.Net
In reply to: Hannu Krosing (#85)
#92Curt Sampson
cjs@cynic.net
In reply to: Hannu Krosing (#85)
#93Curt Sampson
cjs@cynic.net
In reply to: Greg Copeland (#91)
#94Curt Sampson
cjs@cynic.net
In reply to: Jeff Davis (#84)
#95Curt Sampson
cjs@cynic.net
In reply to: Curt Sampson (#94)
#96Hannu Krosing
hannu@tm.ee
In reply to: Curt Sampson (#92)
#97Hannu Krosing
hannu@tm.ee
In reply to: Curt Sampson (#95)
#98Greg Copeland
greg@CopelandConsulting.Net
In reply to: Hannu Krosing (#90)
#99Greg Copeland
greg@CopelandConsulting.Net
In reply to: Hannu Krosing (#97)
#100Chris Browne
cbbrowne@cbbrowne.com
In reply to: Hannu Krosing (#96)
#101Greg Copeland
greg@CopelandConsulting.Net
In reply to: Chris Browne (#100)
#102Chris Browne
cbbrowne@cbbrowne.com
In reply to: Greg Copeland (#101)
#103elein
elein@norcov.com
In reply to: Greg Copeland (#101)
#104Greg Copeland
greg@CopelandConsulting.Net
In reply to: Chris Browne (#102)
#105Oliver Elphick
olly@lfix.co.uk
In reply to: elein (#103)
#106elein
elein@norcov.com
In reply to: Oliver Elphick (#105)