4.5. Anpassung des Datenmodells

Dieses Kapitel beschreibt die möglichen Anpassungen des Datenmodells.

4.5.1. Alterations to ordinary data

4.5.1.1. Einleitung

Das Datenmodell ist versioniert und Aktualisierungen werden mit dem Postgres Updates Manager (auch bekannt als PUM) durchgeführt. Die zur Bearbeitung der Daten erforderlichen Ansichten werden automatisch mit der Bibliothek „Pirogue <https://github.com/opengisch/pirogue>“ erzeugt.

Die Kombination von beidem ermöglicht kleine Anpassungen des Datenmodells, wie das Hinzufügen von Feldern zu bestehenden Basistabellen oder die Erweiterung von Ansichten durch das Verbinden zusätzlicher Tabellen.

Before going further, we strongly recommend to ask the community if you have any doubts about how to store any information in the database. TWW complies with SIA 405 Waste Water and VSA-DSS datamodel.

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

Sollte dennoch eine Anpassung erforderlich sein, erklären die folgenden Erläuterungen und Regeln, wie Sie das TWW-Datenmodell an Ihre spezifischen Bedürfnisse anpassen können.

4.5.1.2. Symbologie- und Beschriftungsverhalten ändern

Symbology and labelling behaviour depends on the value lists `` tww_vl.channel_usage_current`` and `` tww_vl.channel_function_hierarchic`` These two value list tables have an additional column tww_symbology_order, which is used to define the hierarchy of the symbology.

Für die Beschriftung kann die Spalte `` tww_vl.channel_function_hierarchic.tww_use_in_labels`` genutzt werden, um zu steuern, welche FunktionHierarchisch beim Generieren des Labels berücksichtigt werden.

4.5.1.3. Creation of custom tables

It is possible to add custom tables with ordinary data. They must be stored in tww_od. In order to version them, use the Postgres Updates Manager (aka PUM).

4.5.1.4. Adding fields to base tables

Instead of adding additional fields to base tables, it is advised to create a new table with a foreign key linking it to the base table using PUM.

4.5.1.5. Datamodel updates

Achtung

The process of updating the database is currenty being overhauled. The following information can therefore change before the next release

4.5.1.5.1. Adding fields and tables

Um Upgrades des Datenmodells zu ermöglichen, sollte man ein Skript bereitstellen, um sie zu erstellen, und eines, um sie zu löschen. Diese Skripte (SQL oder Python) sollten in einem bestimmten Pfad abgelegt und als pre-all.{py,sql} und post-all.{py,sql} bezeichnet werden. Dieser Pfad muss als Variable an PUM übergeben werden, wenn das Datenmodell als Delta-Verzeichnis aktualisiert wird (siehe PUM-Dokumentation).

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.

Achtung

It is discouraged to add additional fields to base tables that are defined by TEKSI Wastewater, as they can interfere with datamodel updates.

If it is necessary to add custom fields, create a separate table with a foreign key pointing to the TEKSI base table and join it to the base views.

4.5.2. Modification Framework for application schema

In order to add app modifications to TEKSI in a standardised way, TEKSI wastewater set into place an modification framework.

There are two types of modification:

Official TEKSI Wastewater modification
custom modification

The modification framework allows the following actions:

Adding additional views using sql
Adding additional triggers using sql
Activating/deactivating value list items
Joining additional tables to views

Achtung

The modification framework is not intended for alterations on the schema tww_od. Use PUM functionalities instead

4.5.2.1. Creation and loading of modifications

All modification parameters are handles in a yaml that is passed to PUM documentation on update. The the default yaml is

base_configurations:
   - lang_code: en
   - SRID: 2056

extra_definitions:
  vw_tww_reach: null
  vw_tww_wastewater_structure: null
  vw_tww_overflow: null
  vw_wastewater_structure: null
  vw_tww_infiltration_installation: null
  vw_tww_channel: null
  vw_tww_channel_maintenance: null
  vw_tww_ws_maintenance: null
  vw_tww_damage_channel: null
  vw_tww_additional_ws: null
  vw_tww_measurement_series: null
  vw_tww_log_card: null

multiple_inherintances:
  vw_maintenance: "view/multipleinheritance/vw_maintenance_event.yaml"
  vw_damage: "view/multipleinheritance/vw_damage.yaml"

simple_joins_yaml:
  vw_export_reach: "view/simplejoins/export/vw_export_reach.yaml"
  vw_export_wastewater_structure: "view/simplejoins/export/vw_export_wastewater_structure.yaml"

modification_repositories:
  - id: ci
    active: false
    template: modification_repos/ci-extension/config.yaml
  - id: agxx
    active: false
    reset_vl: false
    template: modification_repos/tww2ag6496/config.yaml
  - id: demo
    active: false
    reset_vl: false
    variables:
      myVariable:
        value: "Hello World"
        type: literal
      myNumber:
        value: 0
        type: number
    sql_files:
      - file: /datamodel/app/modification_repos/dummy/000_demo.sql
    reset_vl_files:
      - file: /datamodel/app/modification_repos/dummy/vl_management.sql

The yaml handles the following parameters:

4.5.2.1.1. Base Configurations

Base Configurations handle variables that are handled over the entire project. Those parameters are exposed in TMMT as well, but when passing a yaml definition, they are overridden

Base Configurations
Parameter	Description
`lang_code`	Specifies the language code used for web-optimised views. Expected value is a language code string. Defaults to „en“.
`SRID`	Specifies the Spatial Reference System Identifier. Expected value is an integer representing the SRID, Defaults to 2056.

4.5.2.1.2. Extra Definitions

Extra definitions allow adding additional fields to custom views. The parameter defines the path to the corresponding yaml definition. The yaml is structured as follows:

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

table and join_on are mandatory keys, all the others are optional. read_only defaults to true. remap_columns is used on insert and update, remap_columns_select on Select. Entries that are in skip_columns but listed in remap_columns are not skipped on insert and update. It is expected that mt.fk_ws has a ON DELETE CASCADE`` foreign key constraint.

4.5.2.1.3. Multiple Inheritances

Multiple Inheritances are used to aggregate all subtypes of a superclass into one view. The parameter defines the path to the corresponding yaml definition. The yaml is structured as follows:

table: tww_od.overflow
view_name: vw_tww_overflow
view_schema: tww_app
allow_type_change: True
allow_parent_only: false
pkey_default_value: True

additional_columns:
  geometry: n1.situation3d_geometry
  my_column: nt.foobar
additional_joins: >
  LEFT JOIN tww_od.wastewater_node n1 ON overflow.fk_wastewater_node::text = n1.obj_id::text
  LEFT JOIN tww_od.my_table mt ON overflow.obj_id::text = mt.baz::text

joins:
  leapingweir:
        table: tww_od.leapingweir

  prank_weir:
        table: tww_od.prank_weir

  pump:
        table: tww_od.pump

4.5.2.1.4. Simple Joins YAML

The Simple Join YAML allows altering the path from which the join definition is loaded. The parameter defines the path to the corresponding yaml definition. The yaml is structured as follows:

view_name: vw_export_wastewater_structure
view_schema: tww_app
table: tww_app.vw_tww_wastewater_structure
pkey: obj_id

joins:
  cover_shape:
        table: tww_vl.cover_cover_shape
        fkey: co_shape
        prefix: cover_shape_
[...]

4.5.2.1.5. Modification Repositories

Modification Repositories
Parameter	Description
`id`	Unique identifier for the modification repository.
`active`	Boolean indicating if the modification repository should be activated.
`reset_vl`	Boolean indicating if the value list entries treated by reset_vl_files should be reset.
`template`	Path to the template configuration file.
`variables`	Dictionary of variables with their values and types. Overridden by `template`
`sql_files`	List of SQL files to be executed. Overridden by `template`
`reset_vl_files`	List of SQL files for activating/deactivating value list entries. Activates/deactivates based on the `active` setting. Overridden by `template`

4.5.2.1.6. Modification Repository Templates

A modification repository template allows to predefine the values of the following repository flags:

variables
sql_files
reset_vl_files

Additionally, it allows setting values for Extra Definitions, MultipleInheritances and SimpleJoin YAML if the app_modification yaml does not define a value.

4.5.2.2. Limitations for sql scripts

The sql scripts must only be used for the following purposes:

Adding additional views to the application schema
Adding additional INSTEAD OF triggers to the application schema
Adding additional triggers to populate od tables that are not part of the VSA-DSS datamodel
Activating/deactivating items to value lists

Note that the sql must not be used to create new tables in tww_od. Use PUM for these cases.

Please note that these scripts are re-run on every datamodel update. They must therefore be written in such a way that existing data does not interfere with them (i.e. using CREATE OR REPLACE VIEW or ON CONFLICT DO NOTHING).