Converting database structure models to Liquibase ChangeSets

🌐 This document is available in both English and Ukrainian. Use the language toggle in the top right corner to switch between versions.

1. Functional scenarios

  • Create Diff Document based on DataModelSnapshot database (current version and version changes) in .json format according to the requirements.

  • Create Xml liquibase Changesets based on the generated Diff Document.

2. Out of scope

  • Delete operation for AccessRule

3. Technology stack

Technology / Library Version License Documentation Description

Guava

31.1-jre

Apache License 2.0

Documentation

Guava — Google library for working with collections in Java.

4. Diff calculator

The Diff calculator component based on the DataModelSnapshot creates Diff Document. Since the DataModelSnapshot has a structure based on Map, the Guava library is used to calculate the difference.

The calculation of difference between two states of database structure on the test data takes 30 msec.

4.1. Example of generated Diff Document

{
   "tablesToAdd":{
      "application_type":{
         "name":"application_type",
         "remarks":"Application type guide",
         "columns":{
            "name":{
               "name":"name",
               "remarks":"Application type",
               "type":"text",
               "notNullFlag":true,
               "tableName":"application_type"
            },
            "application_type_id":{
               "name":"application_type_id",
               "remarks":"Application type identifier",
               "type":"uuid",
               "defaultValue":"uuid_generate_v4()",
               "notNullFlag":true,
               "tableName":"application_type"
            },
            "constant_code":{
               "name":"constant_code",
               "remarks":"Character constant",
               "type":"text",
               "notNullFlag":true,
               "tableName":"application_type"
            }
         },
         "primaryKey":{
            "name":"pk_application_type_id",
            "columns":[
               {
                  "name":"application_type_id",
                  "sorting":"ASC"
               }
            ],
            "tableName":"application_type"
         },
         "uniqueConstraints":{
            "application_type_name_key":{
               "name":"application_type_name_key",
               "columns":[
                  {
                     "name":"name",
                     "sorting":"ASC"
                  }
               ],
               "tableName":"application_type"
            }
         }
      }
   },
   "tablesToEdit":{
      "staff":{
         "attributesToEdit":{
            "remarks":"Personnel structure"
         },
         "columnsToAdd":{
            "researches":{
               "name":"researches",
               "remarks":"Array of study identifiers",
               "type":"_uuid",
               "notNullFlag":false,
               "tableName":"staff"
            }
         },
         "columnsToEdit":{
            "education":{
               "remarks":"Education, profession",
               "defaultValue":"Law faculty"
            }
         },
         "foreignKeysToAdd":{
            "fk_staff_status":{
               "name":"fk_staff_status",
               "targetTable":"staff_status",
               "columnPairs":[
                  {
                     "sourceColumnName":"staff_status_id",
                     "targetColumnName":"staff_status_id"
                  }
               ],
               "sourceTable":"staff"
            },
            "fk_staff_laboratory_new":{
               "name":"fk_staff_laboratory_new",
               "targetTable":"laboratory",
               "columnPairs":[
                  {
                     "sourceColumnName":"laboratory_id",
                     "targetColumnName":"laboratory_id"
                  }
               ],
               "sourceTable":"staff"
            }
         },
         "foreignKeysToDelete":{
            "fk_staff_laboratory":{
               "name":"fk_staff_laboratory",
               "targetTable":"laboratory",
               "columnPairs":[
                  {
                     "sourceColumnName":"laboratory_id",
                     "targetColumnName":"laboratory_id"
                  }
               ],
               "sourceTable":"staff"
            }
         },
         "indicesToAdd":{
            "ix_staff_staff_status__staff_status_id":{
               "name":"ix_staff_staff_status__staff_status_id",
               "columns":[
                  {
                     "name":"staff_status_id",
                     "sorting":"ASC"
                  }
               ],
               "tableName":"staff"
            },
            "ix_staff_laboratory__laboratory_id_new":{
               "name":"ix_staff_laboratory__laboratory_id_new",
               "columns":[
                  {
                     "name":"laboratory_id",
                     "sorting":"ASC"
                  }
               ],
               "tableName":"staff"
            }
         },
         "indicesToDelete":{
            "ix_staff_laboratory__laboratory_id":{
               "name":"ix_staff_laboratory__laboratory_id",
               "columns":[
                  {
                     "name":"laboratory_id",
                     "sorting":"ASC"
                  }
               ],
               "tableName":"staff"
            }
         }
      }
   },
   "accessRulesToAdd":{
      "1":{
         "permissionId":"1",
         "roleName":"isAuthenticated",
         "objectName":"laboratory",
         "columnName":"edrpou",
         "operation":"SELECT"
      }
   }
}

TODO: A Json scheme of the Diff Document description must be implemented.

5. LiquibaseDataModelSerializer

The LiquibaseDataModelSerializer component based on Diff Document generates Liquibase XML-Changelog using the following algorithm:

  • Get Liquibase ChangeLog.

  • Form Liquibase Changes based on Diff Document.

  • Add Liquibase Changes to the corresponding Liquibase ChangeSet or create a new one.

  • Liquibase based on updated Liquibase ChangeLog generates Liquibase XML-Changelog.

5.1. Example of generated Liquibase XML-Changelog

<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
                   xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
                   xmlns:pro="http://www.liquibase.org/xml/ns/pro"
                   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                   xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext
                   https://nexus-public-mdtu-ddm-edp-cicd.apps.cicd2.mdtu-ddm.projects.epam.com/repository/extensions/com/epam/digital/data/platform/liquibase-ext-schema/latest/liquibase-ext-schema-latest.xsd
                   http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-4.6.xsd
                   http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.6.xsd">

    <changeSet author="system" id="id" objectQuotingStrategy="LEGACY">
        <createTable ext:isObject="false" remarks="Application type guide" tableName="application_type">
            <column name="name" remarks="Application type" type="text">
                <constraints nullable="false"/>
            </column>
            <column defaultValue="uuid_generate_v4()" name="application_type_id" remarks="Application type identifier" type="uuid">
                <constraints nullable="false" primaryKey="true" primaryKeyName="pk_application_type_id"/>
            </column>
            <column name="constant_code" remarks="Character constant" type="text">
                <constraints nullable="false"/>
            </column>
        </createTable>
        <addUniqueConstraint columnNames="name" constraintName="application_type_name_key" tableName="application_type"/>
        <setTableRemarks remarks="Personnel structure" tableName="staff"/>
        <addColumn tableName="staff">
            <column name="researches" remarks="Array of study identifiers" type="_uuid"/>
        </addColumn>
        <setColumnRemarks columnName="education" remarks="Education, profession" tableName="staff"/>
        <dropDefaultValue columnName="education" tableName="staff"/>
        <addDefaultValue columnName="education" defaultValue="Law faculty" tableName="staff"/>
        <dropForeignKeyConstraint baseTableName="staff" constraintName="fk_staff_laboratory"/>
        <addForeignKeyConstraint baseColumnNames="staff_status_id" baseTableName="staff" constraintName="fk_staff_status" referencedColumnNames="staff_status_id" referencedTableName="staff_status"/>
        <addForeignKeyConstraint baseColumnNames="laboratory_id" baseTableName="staff" constraintName="fk_staff_laboratory_new" referencedColumnNames="laboratory_id" referencedTableName="laboratory"/>
        <dropIndex indexName="ix_staff_laboratory__laboratory_id" tableName="staff"/>
        <createIndex indexName="ix_staff_staff_status__staff_status_id" tableName="staff">
            <column descending="false" name="staff_status_id"/>
        </createIndex>
        <createIndex indexName="ix_staff_laboratory__laboratory_id_new" tableName="staff">
            <column descending="false" name="laboratory_id"/>
        </createIndex>
        <ext:rbac>
            <ext:role name="isAuthenticated">
                <ext:table name="laboratory">
                    <ext:column name="edrpou" read="true"/>
                </ext:table>
            </ext:role>
        </ext:rbac>
    </changeSet>
</databaseChangeLog>