Migrations

Introduction

The CDP4-COMET-Web Service has a migration engine that allows it to update the database with new sql scripts that are applied on top of the initial one. The purpose of this page is to describe how it works and what the developer shall do to create other additional scripts to add features or fix bugs.

Runtime

The system checks migrations at the startup of the Web server. It searches for migrations in the database ("SiteDirectory"."MigrationManagement") and runs migrations that are unknown. It updates the MigrationManagement table when migrating is finished, so we can be sure that a specific migration only runs once.

Scripts location and convention

The scripts shall be added within the solution under CDP4Orm.MigrationScript as Embedded resources. The content of the file shall be compliant with postgresql version 9.6 and may or may not contain string that are replaced by the migration-script handler before execution (for instance the partition name).

Script naming convention

A specific naming schema is used for migration scripts. Hereafter is the regex describing what the system expects for the migration script name:

^(?<applicableDomain>[a-zA-Z]+)(_(?<migrationClass>[a-zA-Z]+))?_(?<date>\d{8})_(?<version>\d_\d_\d_\d)_(?<name>.*\.sql)$

• applicableDomain defines the kind of partition that the script is applied on. Supported values are SiteDirectory, EngineeringModel, Iteration and All. The engine is responsible for executing scripts on all applicable partitions. If there are N Iterations partitions, the script shall be executed on all of them if the script targets "Iteration" partitions.
• migrationClass is optional and defines what migration handler executes the script. The different handlers are described below. By default if the field is missing, the GenericMigration handler is used.
• date The date the script was added
• version indicates the version of the web-service the script was included and is important as the engine executes them in order from the oldest to the latest. The version shall be unique.
• name is just a readable name that may indicate what the script is about.

example:

1. All_20180919_1_1_0_1_Add-Delete-Triggers.sql
2. All_NonThingTableMigrationTemplate_20180919_1_1_0_2_Add-Delete-Triggers.sql
3. All_ThingAuditTableMigrationTemplate_20231030_8_0_0_0_Add_Audit_Iid_Index.sql

1. is a script applied on all partitions and was included on version 1.1.0 of the web-service and is the first script of that version. The GenericMigration handler takes care of the execution.
2. is a script applied on all partitions and was included on version 1.1.0 of the web-service and is the second script of that version. The NonThingTableMigration handler takes care of executing that script.
3. is a script applied on all partitions and was included on version 8.0.0 of the web-service and is the first script of that version. The ThingAuditTableMigration handler takes care of executing that script.

Migration-scripts Handlers

GenericMigration

The GenericMigration handler just takes the script and executes it after it has replaced potential "SchemaName_Replace" in the script by the actual partition name that the script is applied on.

NonThingTableMigration

This handler takes the script as input and applies it on all classes representing a 10-25 class (except for the top Thing table) within a partition. Besides the "SchemaName_Replace" that is replaced by the actual partition, "TABLE_REPLACE" is used by the handler so that it replaces that string pattern by the name of a table

ThingAuditTableMigration

This handler takes the script as input and applies it on all *_Audit tables for classes representing a 10-25 class within a partition. Besides the "SchemaName_Replace" that is replaced by the actual partition, "TABLE_REPLACE" is used by the handler so that it replaces that string pattern by the name of a table

Extra handler

Any new handler may be created and shall derive from the MigrationBase class. If you require a new Handler that will handle updates on all classes representing a 10-25 class, you can derive the new class from ThingTableMigrationBase and override the abstract methods according to your needs.