Deleting data use case issues #7

[haubourg]

In the idea of finalizing the demo data sample to v1.2.1 model version, I continued the work started here #2

Removing meters via vw_element_meter

delete from qwat_od.vw_element_meter a where NOT a.geometry && (st_makebox2d(st_makePoint(559144, 143502), st_makepoint(561817, 140796)))

will throw fk constraint errors on meter_reference table

delete from qwat_od.vw_element_meter a where NOT a.geometry && (st_makebox2d(st_makePoint(559144, 143502), st_makepoint(561817, 140796))) ft_element_meter_delete seem to miss the cascading delete for meter_reference table

[haubourg]

adding DELETE FROM qwat_od.meter_reference WHERE fk_meter = OLD.id; in ft_element_meter_delete() does the trick.

[haubourg]

but.. that trigger function is generated by the meta-projects recursive scripts..

[haubourg]

SOoo, if I get it well, the solution would be to add a trigger on qwat_od.meter to delete meter_reference on delete. @3nids Can you confirm that I get it well ?

[haubourg]

Same problem occurs with deleting from vw_element_subscriber

**ERROR:  update or delete on table "subscriber" violates foreign key constraint "subscriber_reference_fk_subscriber" on table "subscriber_reference"
DETAIL:  Key (id)=(38641) is still referenced from table "subscriber_reference".
CONTEXT:  SQL statement "DELETE FROM qwat_od.subscriber WHERE id = OLD.id"
PL/pgSQL function qwat_od.ft_element_subscriber_delete() line 3 at SQL statement
********** Erreur **********

ERROR: update or delete on table "subscriber" violates foreign key constraint "subscriber_reference_fk_subscriber" on table "subscriber_reference"
État SQL :23503
Détail :Key (id)=(38641) is still referenced from table "subscriber_reference".
Contexte : SQL statement "DELETE FROM qwat_od.subscriber WHERE id = OLD.id"
PL/pgSQL function qwat_od.ft_element_subscriber_delete() line 3 at SQL statement
**

I guess that we have a general issues with reference_* logic and trigger generation process

[3nids]

Yep exactly. A delete cascade rule is the way to go.

[haubourg]

Another one on pipes: ERROR: update or delete on table "pipe" violates foreign key constraint "pipe_fk_parent" on table "pipe" DETAIL: Key (id)=(1106) is still referenced from table "pipe".

Deleting a pipe means first removing only fk_renference to it on other pipes, and then deleting it. Ther is no yet a delete trigger for pipes, should we add one?

[3nids]

PR in https://github.com/qwat/qwat-data-model/pull/158