OOP real life example (was Re: Why is MySQL more chosen over PostgreSQL?
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. *)
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
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
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
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
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
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
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
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
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
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
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