Work in progress
Prerequisites
- Agree Schema changes (ie. create proposal page for the required schema change e.g. with proposed yang file)
Example: CPS-677: Support 'public' Cm Handle Properties
Build Docker for Postgres
These steps are applied first to load the already existing change-logs within CPS.
To start an Postgres database instance using docker issue the following command
docker run --name postgres -p 5432:5432 -d -e POSTGRES_DB=cpsdb -e POSTGRES_USER=cps -e POSTGRES_PASSWORD=cps postgres:13.2-alpine
Applying Pending Changes
To apply any changes made to the changelog within CPS, run the following command from the cps-ri directory
mvn compile org.liquibase:liquibase-maven-plugin:4.3.1:update -Dliquibase.url=jdbc:postgresql://localhost:5432/cpsdb -Dliquibase.username=cps -Dliquibase.password=cps -Dliquibase.changeLogFile=src/main/resources/changelog/changelog-master.yaml
Change-Log Order
The change-log will then be updated running the following yaml files in order, based on the changelog-master.yaml file within the cps-ri->src->main->resources->changelog→db.changes directory.
Anytime a new change-log YAML is added, it will need to be included in this file for the changes to be applied within CPS
Adding New Tables
To add new tables to tables within CPS using the Liquibase change-log go to the 01-createCPSTables.yaml file.
Under the databaseChangeLog object each change-set is defined with a unique id (Similar to how an array is defined within YAML).
Here the columns are also defined, along with the constraints of a specific column.
To add a new table, under the latest change-set add a new change-set, incrementing the ID by one.
The id of the change-set is based on the change-log YAML file number, and the change-set order. Eg: 1-1, 1-2, 2-1, 2-2, 3-1, 3-2 etc.
- changeSet: id: 1-38 author: cps
Define a changes object, all new edits will go under this section. Create a new createTable object, and under here the table name can be defined along with the column information/constraints as stated above.
Table once created within SqlDeveloper
Adding/Editing Columns
Once the table is created, columns can be added and edited using a similar syntax to above.
Foreign Key Contraints.
To add foreign key constraints, within the 01-createCPSTables.yaml file create a new change-set and increment the change-set id as done above.
In the following example, I have created a second test table, which will contain a foreign key shared with the primary key of the first test_table.
From the table above we have created a column of type integer called test_table_pk. This is a reference to the primary key within the first test table created above, which will be our foreign key in this table.
Below is the syntax within YAML for creating a foreign key relation between both tables, the base table and column being the table we are creating the constraint on, and the referenced table and column being the table which contains the PK (id) column we are creating our FK relationship with.
test table with fk model.
Rename Column
To rename a column use the following syntax, specifying the table and the oldColumnName, along with the new Column Name.
There is currently no YAML file within CPS specifically for renaming table columns, this example is done under the presumption that a new file has been created for renaming columns as seen by the updated id from the example.
Add New Column
To add a column use the following syntax.
Load Data
The data for each table is defined using the CSV files located within cps-ri->resources->changelog->db.changes->data→dmi.
renamed_column|new_column1|new_column2 test_table_column_name|columnValue|3
Then each table has a specific YAML change-set which loads the CSV using the following syntax.
test table with data
Loading data for Yang Resource
To load a yang resource, create a new yang resource csv file to load the data (as outlined above) in the following format 'yang_resource_@*date-of-creation*'
Before adding the checksum in the file above, first generate the checksum within the yang_resource table following the step below
Generating Checksum For New Yang Resource
First copy the content from the CSV file above and create a YANG file locally with it with the format 'dmi-registry@*date-of-creation*'.
Run the following command to insert a new yang resource into the yang_resource table with the checksum generated.
CPS Application must be deployed from before running this command.
curl --location --request POST 'http://localhost:8080/cps/api/v1/dataspaces/*dataspacename*/schema-sets' --header 'Authorization: Basic Y3BzdXNlcjpjcHNyMGNrcyE=' --form 'file=*PATH-TO-YANG-FILE-ON-LOCAL*' --form 'schema-set-name='*NAME-OF-SCEHMA-SET-DEFINED-IN-YANG_RESOURCE*'
Add the generated checksum into the yang resource csv above.
Once this is done, then add the change-set for yang resource you have created to load the data into the liquibase DB.
Yang Resource Schema Set
Data will also need to be inserted into the yang_resource_schema_set table to create a mapping between the new yang resource and the necessary schema set.
Create a csv to define the data in the format of 'schema_set_yang_resources@*date-of-creation*'
Then add the change-set to the relevent change-log YAML file
Rollback
As seen above, the rollback tag is included within the change-set in the case that and updates made need to be reverted.
Types of Rollback
To run the rollback to revert a specified number of change-sets sequentially run the following command with the number of change-sets you want to run back.
mvn compile org.liquibase:liquibase-maven-plugin:4.3.1:rollback -Dliquibase.rollbackCount=*number-of-change-sets-to-rollback* -Dliquibase.url=jdbc:postgresql://localhost:5432/cpsdb -Dliquibase.username=cps -Dliquibase.password=cps -Dliquibase.changeLogFile=src/main/resources/changelog/changelog-master.yaml
To run the rollback to revert all changes to a database that were made after a specific tag
mvn compile org.liquibase:liquibase-maven-plugin:4.3.1:rollback -Dliquibase.rollbackTag=*liquibase-tag-to-rollback-to* -Dliquibase.url=jdbc:postgresql://localhost:5432/cpsdb -Dliquibase.username=cps -Dliquibase.password=cps -Dliquibase.changeLogFile=src/main/resources/changelog/changelog-master.yaml
To run the rollback to revert all changes back to a specific date run.
mvn compile org.liquibase:liquibase-maven-plugin:4.3.1:rollback -Dliquibase.rollbackDate=*date-to-rollback-to* -Dliquibase.url=jdbc:postgresql://localhost:5432/cpsdb -Dliquibase.username=cps -Dliquibase.password=cps -Dliquibase.changeLogFile=src/main/resources/changelog/changelog-master.yaml
Potential Issues
If the following issue arises:
Caused by: liquibase.exception.ValidationFailedException: Validation Failed:
Issue the following command to clear checksums:
mvn compile org.liquibase:liquibase-maven-plugin:4.3.1:clearCheckSums -Dliquibase.url=jdbc:postgresql://localhost:5432/cpsdb -Dliquibase.username=cps -Dliquibase.password=cps