Re: exploiting features of pg to obtain polymorphism - Mailing list pgsql-general

Resending since it definitively seems it has been lost in outer spaces.
Sorry for duplicates if any.

On Sun, 8 Oct 2006 14:09:53 +0200
Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote:

> On Fri, Oct 06, 2006 at 11:09:29PM +0200, Ivan Sergio Borgonovo
> wrote:
>
> > Is there any good documentation, example, tutorial, pamphlet,
> > discussion... to exploit pg features to obtain "polymorphic"
> > behavior without renouncing to referential integrity?
>
> In GNUmed we use it to aggregate text fields over a
> range of child tables and for auditing:
>
>  http://cvs.savannah.gnu.org/viewcvs/gnumed/gnumed/server/sql/?root=gnumed
>
> look at gmAudit*.sql and gmclinical.sql

I finally got the time to look at the files you suggested.
They helped me to realize that if I put pk/fk in the children I won't have inheritance problems on pk/fk cos they will
bepreserved. As stupid as this observation may seem, at least it tells me what I can do. 

Anyway it doesn't solve the problem of having lists that can contain different elements with same parent and maintain
ref.integrity. 

Now back to gm code.

I see you've data tables with their pk/fk relations and triggers in one schema that inherit from audit tables in
another.

You've a function that helps to put tables that have to be audited in another table, nothing special compared with an
insertwith the exception of some extra control on input. 

Audit tables have their own pk/fk relationships and their triggers but according to my knowledge they won't be
consideredunless you operate on those table directly. 
If you operate on the data tables those triggers pk/fk won't be seen.

Considering you forbid direct insert, update and delete on those tables, while pk/fk etc... are still a security net it
seemsthat those relationship will never be really used. 

Later on you grant the same operations to gm-doctors. This further puzzle me even if I've the suspect the code is not
completeenough to implement the features I'm expecting from the framework I see. 

Finally I read:
comment on table audit.audited_tables is
    'All tables that need standard auditing must be
     recorded in this table. Audit triggers will be
     generated automatically for all tables recorded
     here.';

But I can't see anything doing this.

There is one point of contact between what I did already and what I would like to do but I still haven't had a good
ideahow to implement it. The use of metadata. But definitively I can't see polymorphism in your use of inheritance. 

One way I could eg. make lists that can contain elements of different kind with same parent could be to have a simple
methodto add the missing (not inherited) ref. integrity triggers on children. 

Specifying pk/fk again on inherited fields of children seems to come with a lot of trouble and still will put me in the
samecondition of not having polymorphism (list of elements with common parent). Furthermore this won't solve the
problemthat declaration=instantiation in sql 
While inherits seems syntax sugar and maybe a way to avoid to add metadata to feed to a stored procedure I'll have to
protectthe parent class from being directly accessed (since most of the time the behavior of parents is unwelcome). 
So the only structure there is in pg that looks OO seems not used to support polymorphism in the way I'm interested, it
seemsmainly a way to avoid typing over and over the definition of some columns and to group tables. 

Actually if I could use inherits generated metadata (pg_inherits and Co.) that will come at the cost of having
instantiatedparent tables with their "real" relation with children. 

But there is no simpler syntax than defining a table to *define a table*, and there is no way I know to define virtual
tables.

Supposing I was able to create virtual parents I could add metadata to children, feed those metadata to sp to create
triggersto maintain ref. integrity in lists that contain different elements with same parent. 

Otherwise I'll have to have an external parser that start from pseudo sql where I can specify if a table is a virtual
tableetc... or put virtual table definitions inside tables to be fed to a sp... but this definitively looks overkill. 

The example here while suggestive
http://www.varlena.com/varlena/GeneralBits/98.php
doesn't seem to have any relation with OO and the second sentence explain it all:
"In the PostgreSQL implementation, this is not true object inheritance."

Any second chance to find an OO use of inherits, cos this seems the only OO construct of pg.

Don't take this as a sparkle of a flame war (we already had 2 very long one in few weeks) on how pg is /under .*/
etc...I'm not aware of any usable/mature rdbms with reasonable OO features. 


--
Ivan Sergio Borgonovo
http://www.webthatworks.it