Re: [gnome-db] Triggers support

[Vivien Malerba <malerba gnome-db org>]

On Mon, Dec 16, 2002 at 11:46:38AM +0100, Jean-Michel POURE wrote:
> Le Lundi 16 Décembre 2002 09:52, Vivien Malerba a écrit :
> > If you are talking about a schema for triggers (I haven't followed the
> > whole discussion so forgive me if I'm out of subject), can you also
> > think about how to get information on foreign keys and referential
> > integrity rules?
> 
> In PostgreSQL, both triggers and foreign keys are implemented ***internaly*** 
> using rules. But, for data integrity questions, it is not recommanded to 
> alter PostgreSQL schema ***externaly*** using the rule system.
> 
> Basically, the difference between a rule and a trigger is that a rule is able 
> to re-write an SQL query before it is executed in order to alter its syntax. 
> In other words, a rules is able to modify the syntax and execute actions, 
> whereas a trigger can only execute actions. A trigger is a subset of a rule.
> 
> http://www.postgresql.org/idocs/index.php?sql-createrule.html
> 

Thanks for the explanations!

> > I know that within Postgres these two notions are implemented using
> > triggers, but I don't know if it is the same for other DBMS. Maybe it is
> > easier to have a schema request for triggers and another for foreign
> > keys and referential integrity rules, may be not...
> 
> http://www.freebsddiary.org/postgresql-dropping-constraints.php describes some 
> illegal schema access in 7.2. But the information for > 7.3 is interesting.
> 
> PostgreSQL rules, triggers and foreign keys should be considered different 
> objects in libgda.

You mean they should be reported to the libgda user from different
schema name; I agree with you. Could you propose some list output the
libgda API would return when the list of triggers, foreign keys and
rules.

Here is my idea about it:

Foreign keys:
-------------
returned columns are, for each foreign key:
- foreign key identifier (FKID)
- table1.field which is a foreign key
- table2.field which is the primary key (for table2)
- action on update
- action on delete
- other referential integrity attributes

This means for example that a foreign key composed of two fields will
appear here as two lines with the same FKID.

Note: the primary key is obtained using the GDA_CONNECTION_SCHEMA_FIELDS
schema query which, for any field, returns TRUE or FALSE depending on if
the field is part of q primary key or not. The associated limitation is
that libgda will not report correctly the primary keys if there are
several different primary keys (composed or not).

Triggers and rules:
-------------------
I don't have any precise idea here...

Thanks,

Vivien