4.4. Personalización del modelo de datos

Este capítulo describe la posible personalización del modelo de datos.

4.4.1. Introducción

The data model is versioned and updates are achieved using Postgres Updates Manager (aka PUM). The views required to edit the data are automatically generated using Pirogue library.

La combinación de ambos permite introducir pequeñas personalizaciones del modelo de datos, como agregar campos a las bases de las tablas existentes o ampliar las vistas uniendo tablas adicionales.

Antes de continuar, le recomendamos que consulte a la comunidad si tiene alguna duda sobre cómo almacenar cualquier información en la base de datos. QGEP cumple con Aguas residuales SIA 405 y modelo de datos VSA-DSS.

If QGEP data model is not able yet to hold some data, please let us know what kind and how it should be handled. QGEP data model is a standard proof adaptative model and could follow your needs.

Si aún se requiere una personalización, las siguientes explicaciones y reglas explican cómo adaptar el modelo de datos QGEP a sus necesidades específicas.

4.4.2. Agregando campos

Está permitido agregar campos adicionales a las tablas simplemente nombrándolas como usr_….

If added in base tables, these additional fields will be automatically added to the views, being an editable field. The views can be re-generated any time using the following command:

./view/create_views.py --pg_service pg_qgep --srid 2056

Nota

When running data model upgrades using PUM, it will be required to use the additional option --exclude-field-pattern 'usr_%'

4.4.3. Uniendo tablas adicionales

It is possible to join additional tables to the two main views (vw_qgep_wastewater_structure and vw_qgep_reach). This is done by using a YAML definition file for each view and defining a list of joined tables. Fields of these tables will be joined as read-only fields as they are discarded in edit triggers. For joining a table to qgep_od.wastewater_structure, here is an example:

joins:
  my_extra_join:
    table: qgep_od.my_table
    alias: mt
    prefix: mt_
    skip_columns:
      - field_1
      - field_2
    remap_columns:
      field_3: my_renamed_field
    join_on: mt.fk_ws = ws.obj_id

table y join_on son claves obligatorias, todas las demás son opcionales.

Este archivo YAML se debe proporcionar como una ruta de archivo al ejecutar el script:

./view/create_views.py --pg_service pg_qgep --srid 2056 --qgep_wastewater_structure_extra /path_to/extra_ws.yaml

And similarly for vw_qgep_reach view, by specifying qgep_reach_extra variable to the corresponding YAML file path.

4.4.4. Creando vistas personalizadas

Es posible crear vistas personalizadas y agregarlas al modelo de datos.

In order to allow upgrades of the data model, one should provide a script to create them and one to delete them. These scripts (SQL or Python) should be placed in a specific path and name them as pre-all.{py,sql} and post-all.{py,sql}. This path shall be given as argument to PUM when upgrading the data model as a delta directory (see PUM documentation).

If one wants to have these views automatically updated when fields are added the data model, Pirogue can be used to dynamically generate the views. You can take example of the creation of the views in the data model.