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_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
Parameter |
Description |
---|---|
|
Specifies the language code used for web-optimised views. Expected value is a language code string. Defaults to „en“. |
|
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
Parameter |
Description |
---|---|
|
Unique identifier for the modification repository. |
|
Boolean indicating if the modification repository should be activated. |
|
Boolean indicating if the value list entries treated by reset_vl_files should be reset. |
|
Path to the template configuration file. |
|
Dictionary of variables with their values and types. Overridden by |
|
List of SQL files to be executed. Overridden by |
|
List of SQL files for activating/deactivating value list entries. Activates/deactivates based on the |
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 schemaAdding 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
).