Developer Guide: Custom datamodel extensions

[cymed]

My suggestions concerning custom database extensions are the following:

Schemas

TEKSI datamodels are organized with different schemas simplifying comprehension, rights management and development.

[...]

• txx_custom : custom being any extension name (ex. tww_ag6496) of the actual datamodel for any extension need

Tables

[...]
If a custom database extension introduces new tables with ordinary data, these must be marked with the prefix of the database extension. The beforementioned rules for Tables apply.

Field naming conventions

to define

TEKSI distinguishes the following field names:

1. ordinary data field: fields that are present in the underlying interlis model
   no prefix
2. DB calculated field: fields not present in the interlis model that are used to shortcut topological relations or to prepare labels. (i.e. tww_od.wastewater_structure._usage_current). Updated by triggers.
   prefix: '_'
3. Configuration field: fields that are not updated by triggers, but that can be used by the db admin to customize the db behaviour. (i.e. tww_vl.channel_function_hierarchic.cfg_order_fct_hierarchic)
   suggested prefix: 'cfg_'
4. official model extension: Official TEKSI model extension to maintain existing functionalities, (i.e. aquifier handling for TEKSI wastewater)
   suggested prefix: 'txx_'
5. Custom field: user-specific database extensions not included in the official TEKSI base model.
   suggested prefix: 'custom_', custom being any extension name (ex. tdw_sire -> sire) for any extension need

[...]

Loading of database extensions

In order to facilitate loading database extensions, there is a default solution implemented in https://github.com/teksi/wastewater (introduced in Pull Request 300
In *datamodel\extensions* there is a prepared python script that can be called from the shell using python datamodel\extensions\extension_manager.py --pg_service %myservice% --srid 2056 --drop-schema --extension_name demo.
In *datamodel\extensions* there is a yaml holding the key informations for creation (id, directory name, schema name and variables) for the extension. The code then sorts all the files in the directory defined in the yaml and executes all .sql and .py scripts in that folder.

Value list

[...]

TEKSI wastewater: These value lists are defined in the VSA-DSS datamodel. Do not change!

If new Columns in form custom database extensions require new value lists, these are added into txx_vl in analogy to the regular value lists. The naming of the new value lists includes the extension prefix, i.e. a value list extension for tww_od.wastewater_node.ag96_is_gateway is handled in tww_vl.wastewater_node_ag96_is_gateway. Make sure to inherit from txx_vl.value_list_base

Adding entries to existing value lists

Value list extensions from custom database extensions must be provided with a new code that is not already contained in txx_sys.dictionary_od_field.attribute_id. The index must be unique across all value lists. In addition, each value list extension must be mapped with an original value via the vsacode (todo: is vsacode the correct naming here). Unused values from the base model are not deleted, but the attribute active is set to FALSE instead. See the example from tww_ag6496 below:

-- add new values
INSERT INTO tww_vl.manhole_function (code,vsacode,value_de,value_en,active) VALUES
(1999968,2742,'Schlammfang','sludge_trap',true)		-- Schlammsammler
ON CONFLICT (code) DO UPDATE SET 
  vsacode = EXCLUDED.vsacode
, value_de = EXCLUDED.value_de
, value_en = EXCLUDED.value_en
, active = EXCLUDED.active
;

-- deactivate values that should not be used
UPDATE tww_vl.manhole_function SET active = false WHERE code = ANY(Array[
  8828	-- Entwaesserungsrinne_mit_Schlammsack
, 8601	-- Fettabscheider
, 8702  -- Behandlungsanlage  
]);

[...]

Relational mapping

When creating a database extension, there might arise the necessity to define 1:1 attribute mappings for import. These value lists are handled by inheriting from a base value list. In the following example, we want to map both tww_od.manhole.function values 8736 (manhole) and 9302 (combined_manhole) to the value "Kontrollschacht" in german. Import values "Kontrollschacht" are to be mapped to 8736.

First we create the value list base tables for import and export

CREATE TABLE IF NOT EXISTS tww_vl.value_list_extname_import_rel_base
(
    code integer NOT NULL,
    value_de character varying(100) COLLATE pg_catalog."default",
    CONSTRAINT pkey_value_list_extname_import_rel_base_code PRIMARY KEY (code)
)
TABLESPACE pg_default;

CREATE TABLE IF NOT EXISTS tww_vl.value_list_extname_export_rel_base
(
    code integer NOT NULL,
    value_de character varying(100) COLLATE pg_catalog."default",
    CONSTRAINT pkey_value_list_extname_export_rel_base_code PRIMARY KEY (code)
)
TABLESPACE pg_default;

From these base tables we inherit the setup of the specialised tables

--create the table
CREATE TABLE IF NOT EXISTS tww_vl.manhole_function_import_rel_extname
(CONSTRAINT pkey_manhole_function_import_rel_extname_code PRIMARY KEY (code)
INHERITS (tww_vl.value_list_extname_import_rel_base)
TABLESPACE pg_default;

-- Copy the base
INSERT INTO tww_vl.manhole_function_import_rel_extname(code,value_de)
SELECT code,value_de FROM tww_vl.manhole_function WHERE active
ON CONFLICT (code) DO UPDATE SET 
  value_de = EXCLUDED.value_de;

-- alter 1:1 updated value_de
INSERT INTO tww_vl.manhole_function_import_rel_extname(code,value_de) VALUES
(8736,'Kontrollschacht') 
ON CONFLICT (code) DO UPDATE SET 
  value_de = EXCLUDED.value_de;

For the export mapping, we use

--create the table
CREATE TABLE IF NOT EXISTS tww_vl.manhole_function_export_rel_extname
(CONSTRAINT pkey_manhole_function_export_rel_extname_code PRIMARY KEY (code)
INHERITS (tww_vl.value_list_extname_export_rel_base)
TABLESPACE pg_default;

-- Copy the base
INSERT INTO tww_vl.manhole_function_export_rel_extname(code,value_de)
SELECT code,value_de FROM tww_vl.manhole_function WHERE active
ON CONFLICT (code) DO UPDATE SET 
  value_de = EXCLUDED.value_de;

-- alter 1:1 updated value_de
INSERT INTO tww_vl.manhole_function_export_rel_extname(code,value_de) VALUES
(8736,'Kontrollschacht') ,
(9302 ,'Kontrollschacht') 
ON CONFLICT (code) DO UPDATE SET 
  value_de = EXCLUDED.value_de;

Views

ALL official views referencing txx_od are created in the txx_app schema.
Scripts are stored in the datamodel/view folder. The corresponding scripts should be named accordingly (.sql,.yaml,.py).

Naming convention :

• subclass views that integrate the superclass attributes are named vw_subclass. (example: vw_maintenance)
• superclass views (superclass views that integrate all subclass attributes with prefixes to the attributes of the subclass) should be named vw_txx_superclass (example: vw_tww_maintenance_event).
• dito big views that integrate other classes like the main views vw_tww_reach and vw_tww_wastewater_structure that JOIN together a series of related classes.
• other class views are named vw_class (if ever needed).
• Official Extension views are prefixed with their extension name (example : swmm_vw_conduits)
• Custom Extension views must be stored in the application schema of the extension txx_custom (Note: This separation from tww_app facilitates setting up specific views for interlis exports, where txx_custom can serve as a txx_od substitute. Also, txx_app is re-created on datamodel updates)

Warning :
We strongly discourage users to create views referencing other views. This can lead to overly complicated upgrades when dealing with datamodel alterations (postgreSQL locks).

Functions

Functions of custom database extensions are stored in the application schema of the extension txx_custom

Triggers

Trigger functions of custom database extensions are stored in the application schema of the extension txx_custom

[sjib]

@ponceta Please review - then I can integrate in https://github.com/teksi/Home/wiki/TEKSI-Developer-Guide

[ponceta]

Schemas

• we actually don't need anymore txx_custom as we can easily set
  txx_od.custom_object_class
  txx_od.custom_value_list
  txx_app.vw_custom_editable_view

If we face a situation which enforce this I would be happy to add a custom schema.

[cymed]

I would prefer using txx_custom for the following reasons:

• Handling od tables in a separate schema facilitates dropping an extension you don't need it anymore
• Some models (i.e. AG-64/AG-96) require setting system variables that need writing rights for a tww-user role (i.e. defining whether a user is working on a GEP or cadastral data). I don't want this information in the tww-sys schema to separate rights on a schema level
• Sometimes it is smarter to use backwards matching than extending value lists. I.e when an extension models requires alterations in usage_current are used, adding vl extensions destroys many pre-defined options in usage. In this case it is smarter to set a different bwrel for certain values on the export schema. These are model-dependent and should be separated from tww_vl
• Some information might be lost in the semantic translation from other models to VSA DSS, i.e. if you have values of usage_current on a node with no reach connection in a interlis import. I dislike keeping the quarantine data in od, as it shouldn't be altered manually.

[cymed]

@sjib @ponceta additions from tww2ag6496