search

qwat / qwat-data-model

TEKSI Water module (project QWAT) - PostgreSQL / postgis Datamodel
https://www.teksi.ch
23 stars 24 forks source link

Missing sequences for editable views? #322

Open haubourg opened 4 years ago

haubourg commented 4 years ago

Hi all , when using audit trigger to query the last modification date on every type I realized that we have a missing use case. For the editable views like hydrant or vw_element_installations QGIS does not evaluate the sequence, so what is logged by the audit trigger is either 'NULL' or the manual input , which is different from the definitive id.

I propose to add default values to the editable views so that QGIS can evaluate those sequences.

Something like :

ALTER VIEW qwat_od.vw_element_installation ALTER COLUMN id SET DEFAULT nextval('qwat_od.node_id_seq'); Any objection or better idea?

haubourg commented 4 years ago

In fact, the view generation is code-generated by the od_inheritance.py, so this is not trivial. At this stage, either we switch to [Pirogue](meta generation lib), or we just store plain text view definitions for the inheritance model. @lbartoletti @ponceta @3nids any opinion ?

The impact is currently that we can't use the audit system to track features created via editable views.

haubourg commented 4 years ago

In fact, this wouldn't work, because the trigger calls the fn_node_create node creation function which will give another id. We need to change the triggers to keep the id provided by QGIS if it equal to the max value of the sequence.
So it's not a trivial work.

ponceta commented 3 years ago

Interesting, I have the same problem here since qwat_od.vw_element_hydrant is not logged anymore :

image

image

IMHO QWAT should go for pirogue as soon as possible to get rid of the initial inheritance model.

@3nids is it a big work? Day(s)? Week(s)?

Pirogue enables mixed python / sql script which could be a gain in clarity.

3nids commented 3 years ago

Moving to pirogue would indeed help a lot towards code clarity and maintenance ease. I'd say we're roughly talking about 3-4 days of work, I haven't looked into the details, but it should be a good estimate.

haubourg commented 3 years ago

Interesting, I have the same problem here since qwat_od.vw_element_hydrant is not logged anymore :

This issue is only for INSERT operations, not UPDATE. @ponceta if you don't have audit trails on hydrants, you probably lost the audit trigger on this view. Maybe a local customization or an extension missing to restore the triggers?

I think this is a bug unrelated to pirogue or the legacy metaproject. Can we move the pirogue migration discussion to another issue?

ponceta commented 3 years ago

@haubourg yes, sorry, pirogue discussion is moved to https://github.com/qwat/qwat-data-model/issues/326