6.2. Customize local DB models

Since QWAT 1.3.2, customization process and extension have been formalised.

An extension is a core data model customization that is part of a dedicated repository. It can be activated at any time, using its own initialization script. An extension can follows the core data model life cycle, which means that fixing or improving a core extension will require a QWAT update, but can have an own life cycle. Currently, the only existing extension is the SIRE export extension. Note that it extends the value lists and is modified the qwat core right now.

A local customization will follow exactly the same developpement rules as an extension, except that it must be stored in a separate repository, apart from QWAT core directories. local customizations must be first initialized manually and then applied in QWAT migration process using upgrade_db.sh specific option to add additional directories.

Both extension and customizations require the following :

  • a init script, adding the necessary informations to the model

  • a set of update scripts used when upgrading QWAT core data model :

    • a pre-all.py script wich will eventually drop all additionnal views, trigger function or any dependencies locking the data model upgrade

    • a set of SQL files prefixed with a version number inlined with QWAT core data model versions (ex: 1.3.1.001_local_Pully_script)

    • a post-all.py script restoring the necessary dependencies once the core model and the extension/customization have been applied

A full example is provided in the .build/customizations/sigip folder or the SIRE extension.

6.2.1. Customizing the data model

This part concerns the modification of the data model to add specific objects, on top on QWAT core data model. While there is full freedom for using PostGIS and PostgreSQL capabilities, here are the conventions and good practices for the QWAT project.

Allowed customization for QWAT are :

  • Adding a custom field

  • Adding a custom table

No change of the core data model is allowed (field names, types, constraints…), or you should ask for the QWAT data model to be modified centrally.

6.2.2. Adding a field

If you want to add a field to one of the core data model tables, follow these steps

  • Store all modifications as separate SQL scripts. NEVER modify directly your database structure.

  • Any added field or table must have the prefix usr_

  • keep in mind that most views are generated by the initialization script. The field will be automatically added to the views if you use the initialization process and include pre and post file to drop and recreate the views.

  • The simplest approach is to keep one unique SQL script applied after all core delta upgrade files. Currently (1.3.0) the file must named like vx.x.x and version number must be higher than latest core data model.

Example : adding a paint color information to hydrants

  • 1 : add the attribute with pgadmin (you could also directly type SQL to modify the model)

  • 2 : add the following SQL equivalent instruction to a usr_model.sql file

1ALTER TABLE qwat_od.hydrant ADD COLUMN usr_color integer;

  • 3 : add the following SQL instruction to a usr_model_drop.sql file

1 ALTER TABLE qwat_od.hydrant DROP COLUMN usr_color ;

6.2.3. Adding a table

Custom tables go to specific schemas prefixed with usr_ . If you want to have additional custom tables, you should first ensure that such a custom schema exists.

If you want to add a table follow these steps

  • Create a custom schema prefixed with usr_

  • Add your table in this schema (table name is free)

  • You should write corresponding diff code against the core data model (see example below)

  • You could write corresponding delete diff code against the core data model (see example below)

Example : adding color informations to hydrants

  • 1 : add the usr_cityservices schema, and the hydrant_paint table with pgadmin (you could also directly type SQL to modify the model)

  • 2 : add the following SQL equivalent instruction to a usr_model.sql file

1 CREATE SCHEMA usr_cityservices;
2 CREATE TABLE usr_cityservices.hydrant_paint (
3 id serial
4 , fk_hydrant integer
5 , color varchar
6 , paint_date timestamp
7);
8 ALTER TABLE usr_cityservices.hydrant_paint ADD CONSTRAINT hydrant_fk FOREIGN KEY (fk_hydrant) REFERENCES qwat_od.hydrant(id) MATCH FULL;

  • 3 : add the following SQL instruction to a usr_model_drop.sql file

1 ALTER TABLE usr_cityservices.hydrant_paint DROP CONSTRAINT hydrant_fk;
2 DROP TABLE usr_cityservices.hydrant_paint;
3 DROP SCHEMA usr_cityservices;

6.3. Create an extension

Extensions have dedicated repositories like SIRE Extension. To create an extension you have to make a new repository from the template and follow the procedure.