db-migrator

Policy DB Migrator is a set of shell scripts used to install the database tables required to run ONAP Policy Framework.

Use liquibase to generate changelog for clampacm db

liquibase

docker run --network compose_default --rm -v /home/wayne/dev/policy/clamp_db_mig/compose:/liquibase/changelog liquibase/liquibase --driver org.mariadb.jdbc.Driver --url jdbc:mariadb://mariadb:3306/clampacm --username policy_user --password policy_user generate-changelog

####################################################
##   _     _             _ _                      ##
##  | |   (_)           (_) |                     ##
##  | |    _  __ _ _   _ _| |__   __ _ ___  ___   ##
##  | |   | |/ _` | | | | | '_ \ / _` / __|/ _ \  ##
##  | |___| | (_| | |_| | | |_) | (_| \__ \  __/  ##
##  \_____/_|\__, |\__,_|_|_.__/ \__,_|___/\___|  ##
##              | |                               ##
##              |_|                               ##
##                                                ## 
##  Get documentation at docs.liquibase.com       ##
##  Get certified courses at learn.liquibase.com  ## 
##                                                ##
####################################################
Starting Liquibase at 14:45:01 (version 4.27.0 #1525 built at 2024-03-25 17:08+0000)
Liquibase Version: 4.27.0
Liquibase Open Source 4.27.0 by Liquibase
BEST PRACTICE: The changelog generated by diffChangeLog/generateChangeLog should be inspected for correctness and completeness before being deployed. Some database objects and their dependencies cannot be represented automatically, and they may need to be manually updated before being deployed.
<?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 http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-latest.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd">
    <changeSet author="liquibase (generated)" id="1713278702128-1">
        <createTable tableName="automationcomposition">
            <column name="instanceId" type="VARCHAR(255)">
                <constraints nullable="false" primaryKey="true"/>
            </column>
            <column name="compositionId" type="VARCHAR(255)"/>
            <column name="compositionTargetId" type="VARCHAR(255)"/>
            <column defaultValueComputed="NULL" name="deployState" type="TINYINT"/>
            <column name="description" type="VARCHAR(255)"/>
            <column defaultValueComputed="NULL" name="lockState" type="TINYINT"/>
            <column name="name" type="VARCHAR(255)"/>
            <column name="restarting" type="BIT"/>
            <column defaultValueComputed="NULL" name="stateChangeResult" type="TINYINT"/>
            <column name="version" type="VARCHAR(255)"/>
        </createTable>
    </changeSet>
    <changeSet author="liquibase (generated)" id="1713278702128-2">
        <createTable tableName="automationcompositiondefinition">
            <column name="compositionId" type="VARCHAR(255)">
                <constraints nullable="false" primaryKey="true"/>
            </column>
            <column name="name" type="VARCHAR(255)"/>
            <column name="restarting" type="BIT"/>
            <column name="serviceTemplate" type="MEDIUMTEXT"/>
            <column defaultValueComputed="NULL" name="state" type="TINYINT"/>
            <column defaultValueComputed="NULL" name="stateChangeResult" type="TINYINT"/>
            <column name="version" type="VARCHAR(255)"/>
        </createTable>
    </changeSet>
    <changeSet author="liquibase (generated)" id="1713278702128-3">
        <createTable tableName="automationcompositionelement">
            <column name="elementId" type="VARCHAR(255)">
                <constraints nullable="false" primaryKey="true"/>
            </column>
            <column name="definition_name" type="VARCHAR(255)"/>
            <column name="definition_version" type="VARCHAR(255)"/>
            <column defaultValueComputed="NULL" name="deployState" type="TINYINT"/>
            <column name="description" type="VARCHAR(255)"/>
            <column name="instanceId" type="VARCHAR(255)"/>
            <column defaultValueComputed="NULL" name="lockState" type="TINYINT"/>
            <column name="message" type="VARCHAR(255)"/>
            <column name="operationalState" type="VARCHAR(255)"/>
            <column name="outProperties" type="MEDIUMTEXT"/>
            <column name="participantId" type="VARCHAR(255)"/>
            <column name="properties" type="MEDIUMTEXT"/>
            <column name="restarting" type="BIT"/>
            <column name="useState" type="VARCHAR(255)"/>
        </createTable>
    </changeSet>
    <changeSet author="liquibase (generated)" id="1713278702128-4">
        <createTable tableName="nodetemplatestate">
            <column name="nodeTemplateStateId" type="VARCHAR(255)">
                <constraints nullable="false" primaryKey="true"/>
            </column>
            <column name="compositionId" type="VARCHAR(255)"/>
            <column name="message" type="VARCHAR(255)"/>
            <column name="nodeTemplate_name" type="VARCHAR(255)"/>
            <column name="nodeTemplate_version" type="VARCHAR(255)"/>
            <column name="outProperties" type="MEDIUMTEXT"/>
            <column name="participantId" type="VARCHAR(255)"/>
            <column name="restarting" type="BIT"/>
            <column defaultValueComputed="NULL" name="state" type="TINYINT"/>
        </createTable>
    </changeSet>
    <changeSet author="liquibase (generated)" id="1713278702128-5">
        <createTable tableName="participant">
            <column name="participantId" type="VARCHAR(255)">
                <constraints nullable="false" primaryKey="true"/>
            </column>
            <column name="description" type="VARCHAR(255)"/>
            <column defaultValueComputed="NULL" name="participantState" type="TINYINT"/>
        </createTable>
    </changeSet>
    <changeSet author="liquibase (generated)" id="1713278702128-6">
        <createTable tableName="participantsupportedacelements">
            <column name="id" type="VARCHAR(255)">
                <constraints nullable="false" primaryKey="true"/>
            </column>
            <column name="participantId" type="VARCHAR(255)"/>
            <column name="typeName" type="VARCHAR(255)"/>
            <column name="typeVersion" type="VARCHAR(255)"/>
        </createTable>
    </changeSet>
    <changeSet author="liquibase (generated)" id="1713278702128-7">
        <createIndex associatedWith="" indexName="ac_compositionId" tableName="automationcomposition">
            <column name="compositionId"/>
        </createIndex>
    </changeSet>
    <changeSet author="liquibase (generated)" id="1713278702128-8">
        <createIndex associatedWith="" indexName="ac_element_fk" tableName="automationcompositionelement">
            <column name="instanceId"/>
        </createIndex>
    </changeSet>
    <changeSet author="liquibase (generated)" id="1713278702128-9">
        <createIndex associatedWith="" indexName="dt_element_fk" tableName="nodetemplatestate">
            <column name="compositionId"/>
        </createIndex>
    </changeSet>
    <changeSet author="liquibase (generated)" id="1713278702128-10">
        <createIndex associatedWith="" indexName="supported_element_fk" tableName="participantsupportedacelements">
            <column name="participantId"/>
        </createIndex>
    </changeSet>
    <changeSet author="liquibase (generated)" id="1713278702128-11">
        <addForeignKeyConstraint baseColumnNames="instanceId" baseTableName="automationcompositionelement" constraintName="ac_element_fk" deferrable="false" initiallyDeferred="false" onDelete="RESTRICT" onUpdate="RESTRICT" referencedColumnNames="instanceId" referencedTableName="automationcomposition" validate="true"/>
    </changeSet>
    <changeSet author="liquibase (generated)" id="1713278702128-12">
        <addForeignKeyConstraint baseColumnNames="compositionId" baseTableName="nodetemplatestate" constraintName="dt_element_fk" deferrable="false" initiallyDeferred="false" onDelete="RESTRICT" onUpdate="RESTRICT" referencedColumnNames="compositionId" referencedTableName="automationcompositiondefinition" validate="true"/>
    </changeSet>
    <changeSet author="liquibase (generated)" id="1713278702128-13">
        <addForeignKeyConstraint baseColumnNames="participantId" baseTableName="participantsupportedacelements" constraintName="supported_element_fk" deferrable="false" initiallyDeferred="false" onDelete="RESTRICT" onUpdate="RESTRICT" referencedColumnNames="participantId" referencedTableName="participant" validate="true"/>
    </changeSet>
</databaseChangeLog>
Liquibase command 'generate-changelog' was executed successfully.

Locally convert changelog.xml to sql script

Download liquibase
Run ./liquibase update-sql --changelog-file=changelog_lb.xml

liquibase: convert xml to sql

####################################################
##   _     _             _ _                      ##
##  | |   (_)           (_) |                     ##
##  | |    _  __ _ _   _ _| |__   __ _ ___  ___   ##
##  | |   | |/ _` | | | | | '_ \ / _` / __|/ _ \  ##
##  | |___| | (_| | |_| | | |_) | (_| \__ \  __/  ##
##  \_____/_|\__, |\__,_|_|_.__/ \__,_|___/\___|  ##
##              | |                               ##
##              |_|                               ##
##                                                ## 
##  Get documentation at docs.liquibase.com       ##
##  Get certified courses at learn.liquibase.com  ## 
##                                                ##
####################################################
Starting Liquibase at 15:52:30 (version 4.27.0 #1525 built at 2024-03-25 17:08+0000)
Liquibase Version: 4.27.0
Liquibase Open Source 4.27.0 by Liquibase
[ WARN] (main) Error: 1146-42S02: Table 'clampacm.databasechangeloglock' doesn't exist
[ WARN] (main) Error: 1146-42S02: Table 'clampacm.databasechangeloglock' doesn't exist
[ WARN] (main) Error: 1146-42S02: Table 'clampacm.databasechangeloglock' doesn't exist
[ WARN] (main) Error: 1146-42S02: Table 'clampacm.databasechangelog' doesn't exist
[ WARN] (main) Error: 1146-42S02: Table 'clampacm.databasechangelog' doesn't exist
[ WARN] (main) Error: 1146-42S02: Table 'clampacm.databasechangelog' doesn't exist
--  Create Database Lock Table
CREATE TABLE clampacm.databasechangeloglock (ID INT NOT NULL, `LOCKED` TINYINT(1) NOT NULL, LOCKGRANTED datetime NULL, LOCKEDBY VARCHAR(255) NULL, CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID));

--  Initialize Database Lock Table
DELETE FROM clampacm.databasechangeloglock;

INSERT INTO clampacm.databasechangeloglock (ID, `LOCKED`) VALUES (1, 0);

--  Lock Database
UPDATE clampacm.databasechangeloglock SET `LOCKED` = 1, LOCKEDBY = 'wayne-XPS-15-9530 (10.1.149.64)', LOCKGRANTED = NOW() WHERE ID = 1 AND `LOCKED` = 0;

--  Create Database Change Log Table
CREATE TABLE clampacm.databasechangelog (ID VARCHAR(255) NOT NULL, AUTHOR VARCHAR(255) NOT NULL, FILENAME VARCHAR(255) NOT NULL, DATEEXECUTED datetime NOT NULL, ORDEREXECUTED INT NOT NULL, EXECTYPE VARCHAR(10) NOT NULL, MD5SUM VARCHAR(35) NULL, `DESCRIPTION` VARCHAR(255) NULL, COMMENTS VARCHAR(255) NULL, TAG VARCHAR(255) NULL, LIQUIBASE VARCHAR(20) NULL, CONTEXTS VARCHAR(255) NULL, LABELS VARCHAR(255) NULL, DEPLOYMENT_ID VARCHAR(10) NULL);

--  *********************************************************************
--  Update Database Script
--  *********************************************************************
--  Change Log: changelog_lb.xml
--  Ran at: 16/04/2024, 15:52
--  Against: policy_user@jdbc:mariadb://localhost/clampacm
--  Liquibase version: 4.27.0
--  *********************************************************************

--  Changeset changelog_lb.xml::1713277220206-1::liquibase (generated)
CREATE TABLE clampacm.automationcomposition (instanceId VARCHAR(255) NOT NULL, compositionId VARCHAR(255) NULL, compositionTargetId VARCHAR(255) NULL, deployState TINYINT DEFAULT NULL NULL, `description` VARCHAR(255) NULL, lockState TINYINT DEFAULT NULL NULL, name VARCHAR(255) NULL, restarting BIT NULL, stateChangeResult TINYINT DEFAULT NULL NULL, version VARCHAR(255) NULL, CONSTRAINT PK_AUTOMATIONCOMPOSITION PRIMARY KEY (instanceId));

INSERT INTO clampacm.databasechangelog (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('1713277220206-1', 'liquibase (generated)', 'changelog_lb.xml', NOW(), 1, '9:303a4e9ba6507be461f9294541082539', 'createTable tableName=automationcomposition', '', 'EXECUTED', NULL, NULL, '4.27.0', '3279150962');

--  Changeset changelog_lb.xml::1713277220206-2::liquibase (generated)
CREATE TABLE clampacm.automationcompositiondefinition (compositionId VARCHAR(255) NOT NULL, name VARCHAR(255) NULL, restarting BIT NULL, serviceTemplate MEDIUMTEXT NULL, state TINYINT DEFAULT NULL NULL, stateChangeResult TINYINT DEFAULT NULL NULL, version VARCHAR(255) NULL, CONSTRAINT PK_AUTOMATIONCOMPOSITIONDEFINITION PRIMARY KEY (compositionId));

INSERT INTO clampacm.databasechangelog (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('1713277220206-2', 'liquibase (generated)', 'changelog_lb.xml', NOW(), 2, '9:35945572686998ced7a0f701f83ac20e', 'createTable tableName=automationcompositiondefinition', '', 'EXECUTED', NULL, NULL, '4.27.0', '3279150962');

--  Changeset changelog_lb.xml::1713277220206-3::liquibase (generated)
CREATE TABLE clampacm.automationcompositionelement (elementId VARCHAR(255) NOT NULL, definition_name VARCHAR(255) NULL, definition_version VARCHAR(255) NULL, deployState TINYINT DEFAULT NULL NULL, `description` VARCHAR(255) NULL, instanceId VARCHAR(255) NULL, lockState TINYINT DEFAULT NULL NULL, message VARCHAR(255) NULL, operationalState VARCHAR(255) NULL, outProperties MEDIUMTEXT NULL, participantId VARCHAR(255) NULL, properties MEDIUMTEXT NULL, restarting BIT NULL, useState VARCHAR(255) NULL, CONSTRAINT PK_AUTOMATIONCOMPOSITIONELEMENT PRIMARY KEY (elementId));

INSERT INTO clampacm.databasechangelog (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('1713277220206-3', 'liquibase (generated)', 'changelog_lb.xml', NOW(), 3, '9:2eda015edbd0648ccabd629bceea9e05', 'createTable tableName=automationcompositionelement', '', 'EXECUTED', NULL, NULL, '4.27.0', '3279150962');

--  Changeset changelog_lb.xml::1713277220206-4::liquibase (generated)
CREATE TABLE clampacm.nodetemplatestate (nodeTemplateStateId VARCHAR(255) NOT NULL, compositionId VARCHAR(255) NULL, message VARCHAR(255) NULL, nodeTemplate_name VARCHAR(255) NULL, nodeTemplate_version VARCHAR(255) NULL, outProperties MEDIUMTEXT NULL, participantId VARCHAR(255) NULL, restarting BIT NULL, state TINYINT DEFAULT NULL NULL, CONSTRAINT PK_NODETEMPLATESTATE PRIMARY KEY (nodeTemplateStateId));

INSERT INTO clampacm.databasechangelog (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('1713277220206-4', 'liquibase (generated)', 'changelog_lb.xml', NOW(), 4, '9:1f5970adfdf753154b3f115869e07908', 'createTable tableName=nodetemplatestate', '', 'EXECUTED', NULL, NULL, '4.27.0', '3279150962');

--  Changeset changelog_lb.xml::1713277220206-5::liquibase (generated)
CREATE TABLE clampacm.participant (participantId VARCHAR(255) NOT NULL, `description` VARCHAR(255) NULL, participantState TINYINT DEFAULT NULL NULL, CONSTRAINT PK_PARTICIPANT PRIMARY KEY (participantId));

INSERT INTO clampacm.databasechangelog (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('1713277220206-5', 'liquibase (generated)', 'changelog_lb.xml', NOW(), 5, '9:7d199f0fadc4ef6aa042c32a9f011a9b', 'createTable tableName=participant', '', 'EXECUTED', NULL, NULL, '4.27.0', '3279150962');

--  Changeset changelog_lb.xml::1713277220206-6::liquibase (generated)
CREATE TABLE clampacm.participantsupportedacelements (id VARCHAR(255) NOT NULL, participantId VARCHAR(255) NULL, typeName VARCHAR(255) NULL, typeVersion VARCHAR(255) NULL, CONSTRAINT PK_PARTICIPANTSUPPORTEDACELEMENTS PRIMARY KEY (id));

INSERT INTO clampacm.databasechangelog (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('1713277220206-6', 'liquibase (generated)', 'changelog_lb.xml', NOW(), 6, '9:b0823e941181e2cff0348d879f4a5a93', 'createTable tableName=participantsupportedacelements', '', 'EXECUTED', NULL, NULL, '4.27.0', '3279150962');

--  Changeset changelog_lb.xml::1713277220206-7::liquibase (generated)
CREATE INDEX ac_compositionId ON clampacm.automationcomposition(compositionId);

INSERT INTO clampacm.databasechangelog (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('1713277220206-7', 'liquibase (generated)', 'changelog_lb.xml', NOW(), 7, '9:c481dd77bdaa7e0e5798dd8b2805927b', 'createIndex indexName=ac_compositionId, tableName=automationcomposition', '', 'EXECUTED', NULL, NULL, '4.27.0', '3279150962');

--  Changeset changelog_lb.xml::1713277220206-8::liquibase (generated)
CREATE INDEX ac_element_fk ON clampacm.automationcompositionelement(instanceId);

INSERT INTO clampacm.databasechangelog (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('1713277220206-8', 'liquibase (generated)', 'changelog_lb.xml', NOW(), 8, '9:75a7c94ba279418d1fe7b47cc782710a', 'createIndex indexName=ac_element_fk, tableName=automationcompositionelement', '', 'EXECUTED', NULL, NULL, '4.27.0', '3279150962');

--  Changeset changelog_lb.xml::1713277220206-9::liquibase (generated)
CREATE INDEX dt_element_fk ON clampacm.nodetemplatestate(compositionId);

INSERT INTO clampacm.databasechangelog (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('1713277220206-9', 'liquibase (generated)', 'changelog_lb.xml', NOW(), 9, '9:cde95681a806714a0f2f1cbe4aa50b51', 'createIndex indexName=dt_element_fk, tableName=nodetemplatestate', '', 'EXECUTED', NULL, NULL,[ WARN] (main) Error: 1146-42S02: Table 'clampacm.databasechangeloglock' doesn't exist
 '4.27.0', '3279150962');

--  Changeset changelog_lb.xml::1713277220206-10::liquibase (generated)
CREATE INDEX supported_element_fk ON clampacm.participantsupportedacelements(participantId);

INSERT INTO clampacm.databasechangelog (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('1713277220206-10', 'liquibase (generated)', 'changelog_lb.xml', NOW(), 10, '9:43da55c5c54075015cfad96c8e8ae71f', 'createIndex indexName=supported_element_fk, tableName=participantsupportedacelements', '', 'EXECUTED', NULL, NULL, '4.27.0', '3279150962');

--  Changeset changelog_lb.xml::1713277220206-11::liquibase (generated)
ALTER TABLE clampacm.automationcompositionelement ADD CONSTRAINT ac_element_fk FOREIGN KEY (instanceId) REFERENCES clampacm.automationcomposition (instanceId) ON UPDATE RESTRICT ON DELETE RESTRICT;

INSERT INTO clampacm.databasechangelog (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('1713277220206-11', 'liquibase (generated)', 'changelog_lb.xml', NOW(), 11, '9:1d6e91011a48728c6a553175775ede8b', 'addForeignKeyConstraint baseTableName=automationcompositionelement, constraintName=ac_element_fk, referencedTableName=automationcomposition', '', 'EXECUTED', NULL, NULL, '4.27.0', '3279150962');

--  Changeset changelog_lb.xml::1713277220206-12::liquibase (generated)
ALTER TABLE clampacm.nodetemplatestate ADD CONSTRAINT dt_element_fk FOREIGN KEY (compositionId) REFERENCES clampacm.automationcompositiondefinition (compositionId) ON UPDATE RESTRICT ON DELETE RESTRICT;

INSERT INTO clampacm.databasechangelog (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('1713277220206-12', 'liquibase (generated)', 'changelog_lb.xml', NOW(), 12, '9:41aae7e658c3332d850ef46bb8ffde20', 'addForeignKeyConstraint baseTableName=nodetemplatestate, constraintName=dt_element_fk, referencedTableName=automationcompositiondefinition', '', 'EXECUTED', NULL, NULL, '4.27.0', '3279150962');

--  Changeset changelog_lb.xml::1713277220206-13::liquibase (generated)
ALTER TABLE clampacm.participantsupportedacelements ADD CONSTRAINT supported_element_fk FOREIGN KEY (participantId) REFERENCES clampacm.participant (participantId) ON UPDATE RESTRICT ON DELETE RESTRICT;

INSERT INTO clampacm.databasechangelog (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('1713277220206-13', 'liquibase (generated)', 'changelog_lb.xml', NOW(), 13, '9:49ee3b90556859af9a3cd4c60969c8ab', 'addForeignKeyConstraint baseTableName=participantsupportedacelements, constraintName=supported_element_fk, referencedTableName=participant', '', 'EXECUTED', NULL, NULL, '4.27.0', '3279150962');

--  Release Database Lock
UPDATE clampacm.databasechangeloglock SET `LOCKED` = 0, LOCKEDBY = NULL, LOCKGRANTED = NULL WHERE ID = 1;

Liquibase command 'update-sql' was executed successfully.

Split into separate sql upgrade & downgrade files.

Testing Upgrade

Docker Testing

Bring up docker-compose.yml environment with the following configuration:

docker-compose.yml

version: '2'
services:
   mariadb:
      image: nexus3.onap.org:10001/mariadb:${POLICY_MARIADB_VER}
      container_name: mariadb
      hostname: mariadb
      command: ['--lower-case-table-names=1', '--wait_timeout=28800', '--default-authentication-plugin=mysql_native_password']
      env_file: ./config/db/db.conf
      volumes:
         - ./config/db:/docker-entrypoint-initdb.d:ro
         - ./config/clamp/policy-clamp-create-tables.sql:/tmp/policy-clamp-create-tables.sql
      ports:
       - "3306:3306"
   policy-db-migrator:
      image: local/policy-db-migrator:latest
      container_name: policy-db-migrator
      hostname: policy-db-migrator
      depends_on:
       - mariadb
      expose:
       - 6824
      env_file: ./config/db/db.conf
      environment:
        SQL_DB: clampacm
        SQL_HOST: mariadb
      volumes:
         - ./config/db-migrator/init.sh:/opt/app/policy/bin/db_migrator_policy_init.sh:ro
         - ./wait_for_port.sh:/opt/app/policy/bin/wait_for_port.sh:ro
      entrypoint: /opt/app/policy/bin/wait_for_port.sh
      command: [
              '-c',
                '/opt/app/policy/bin/db_migrator_policy_init.sh',
                'mariadb', '3306'
               ]

K8s/Helm testing

Run the k8s csit script to install the helm charts

When all pods are up and in running state - kubectl exec into the maridb galera pod to verify that the clampacm database has been installed successfully.

ONAP Wiki

Db-migrator changes to include ACM database upgrade/downgrade