Code & data model audit

Datamodel introspection

schema qwat_dr:

schema qwat_od

schema qwat_vl

schema qwat_sys

Because the tool used to generate the previous diagrams (postgresql_autodoc) cannot represent inherited relationships Here is how to find them in sql :

SELECT parnsp.nspname AS par_schemaname
    , parcla.relname AS par_tablename
    , chlnsp.nspname AS chl_schemaname
    , chlcla.relname AS chl_tablename
 FROM pg_catalog.pg_inherits
 JOIN pg_catalog.pg_class AS chlcla ON (chlcla.oid = inhrelid)
 JOIN pg_catalog.pg_namespace AS chlnsp ON (chlnsp.oid = chlcla.relnamespace)
 JOIN pg_catalog.pg_class AS parcla ON (parcla.oid = inhparent)
 JOIN pg_catalog.pg_namespace AS parnsp ON (parnsp.oid = parcla.relnamespace)

it shows that only qwat_vl.value_list_base is used as a parent table of 37 children

Network elements

It appears that there is no true inheritance for network elements. There are just id shared between tables.

Although the comment on network_element table says that it inherits from node, there's not foreign key for that, which seems dangerous for model integrity.

Furthermore since all is handled by nested triggers it's very difficult to debug and follow what happen in database.

Example of all statements triggered when we try to insert a new valve in the model (from qgis):

└── insert into vw_element_valve
    └── trigger ft_element_valve_insert
        ├── insert into vw_node_element
        │   └── trigger ft_node_element_insert
        │       ├── insert into network_element
        │       └── fn_node_create
        │           └── insert into node
        └── insert into valve
            └── trigger ft_value_node_set_type
                └── fn_node_set_type
                    ├── delete from node
                    └── update node