Starting with version 3.0.0 of tia Core Content Server, the database (both protocol and metadata) is managed by Flyway. Flyway is a framework that allows the management of databases using scripts and records the used ones. This allows applications to self-manage the database even when changing structure, migrating data or performing complex SQL-based operations.
In contrast to using JPA (Java/Jakarta Persistence API) to manage the database, using Flyway requires SQL scripts for each database supported. tia Core ships with scripts for H2, MSSQL server and Oracle.
Flyway concept and naming patterns
The database migrations are done using SQL-scripts which are applied and the usage is recorded in a Flyway history table in the database. Upon each start, the history table is compared to the existing scripts and if scripts are missing, the migrations will be applied.
We can use two types of files: versioned migrations and repeatable migrations.
Version migrations have the pattern V<Version>__<description>.sql. The capital letter V is important, lowercase will not be recognized. The version can either be in the from 2_9_7 or 2.9.7 both are identical for Flyway. The description can be any text, spaces must be replaced by lowercase stash and should not be too long. Those migrations are executed in the versioning order. Any already applied file must not be changed.
Repeatable migrations have the pattern R__<description>.sql. Both the capital letter R and the restrictions for the description apply as for the versioned migrations. The repeatable migrations are executed after all versioned migrations are applied. In contrast to versioned, the repeatable migrations can be changed once applied. Every time the content changes, the files are re-executed. Those migrations can be used for example, when the filling of a field changes and the existing values need to be adapted to the new value.
Customizing
The database services are managed for each repository, therefore each database-based service in the repository.cfg has an optional parameter scripts. The default value is classpath:sql/[meta|metav2|prot]/migration/[h2|sqlserver|oracle] depending on the service and the database vendor.
When using custom scripts, either additional or complete replacement, the vendors must not be mixed, i.e. the locations specified must point to a directory containing the scripts for only one vendor.
Adding additional scripts
In the case additional scripts are required, the scripts parameter can be used to provide them.
This example configures the protocol service additional scripts for oracle in the location /data/custom/kgs/protocol:
<repo>.protocolservice.database.scripts=classpath:sql/prot/migration/oracle,filesystem:/data/custom/kgs/protocol
In order to avoid conflicts with the versioned migrations contained by default, only repeatable migrations should be used. Otherwise it may happen, that the version number of the custom script duplicates the version number of a new script provided by kgs. Leaving a larger gap, e.g. kgs scripts end at V4_0_0 and the custom script starts at V90_0_0, may block kgs scripts later added with V5 and so on. The order of the locations does not matter.
Adding support for non-default vendors
When adding custom support for a database vendor, before each update it is required to check if there are changes which need to be added to those scripts.
In case a database vendor not supported by default is needed, the scripts parameter can also be used. In addition to the JDBC-Driver some vendors also require an additional Flyway dependency (e.g. mysql). The list of supported databases can be found here: https://documentation.red-gate.com/fd/supported-databases-184127454.html .
For example, using a custom vendor for metaservice database v2, and the scripts are located in /kgs/data/sql/vendor
<repo>.metaservice.databasev2.scripts=filesystem:/kgs/data/sql/vendor
The jar files for both the driver and the flyway dependency can then be placed in a lib folder, say /kgs/lib, and then added to the application, depending on the deployment, using the parameter loader.path, e.g. for container deployments LOADER_PATH=/kgs/lib.