Re: exploiting features of pg to obtain polymorphism

On Thu, Oct 12, 2006 at 04:40:32PM +0200, Ivan Sergio Borgonovo wrote:

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

You can put a unique constraint and a serial default on the
parent table (such as a primary key). Insertion on a child
table will fail if the key in question already exists in the
base table. It may have come from another child table.
Updating the base table updates all the relevant child
tables, too. Delete will extend from base to child tables,
too. That way I'll have a unique serial across all the child
tables. I just need to take care to not use ONLY on
update/delete on the base table or to INSERT into the base
table directly (the latter isn't really harmful to the
issue, however).

> 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.
Yes.

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

> Audit tables have their own pk/fk relationships and their triggers but according to my knowledge they won't be considered unless you operate on those table directly.
> If you operate on the data tables those triggers pk/fk won't be seen.
True. But I still get the unique pks since I don't operate
on them directly. Eventually, PG will enforce those
constraints, too.

> Considering you forbid direct insert, update and delete on those tables, while pk/fk etc... are still a security net it seems that those relationship will never be really used.
True as of today.

> Later on you grant the same operations to gm-doctors. This further puzzle me
Well, all objects are owned by "gm-dbo". Our bootstrapper
does that. So I need to grant access rights to some people.
Namely those in the group gm-doctors.

> even if I've the suspect the code is not complete enough
> to implement the features
Yes. Eventually it is going to be something like Veil. Or
rather, I suppose it will *be* (as in use) Veil.

> 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.
gmAuditSchemaGenerator.py in server/bootstrap/

> 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
> idea how to implement it. The use of metadata. But
> definitively I can't see polymorphism in your use of
> inheritance.
Surely not to the extent a C++ programmer would hope for.

> Any second chance to find an OO use of inherits,
Not that I know.

> cos this seems the only OO construct of pg.
Surely not. SPs can be overloaded. Datatypes can be
extended.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346