Skip to end of metadata
Go to start of metadata

You are viewing an old version of this content. View the current version.

Compare with Current View Version History

Version 1 Next »

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 

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'
               ]




./start-compose.sh policy-db-migrator
policy-db-migrator
Configuring docker compose...
Starting policy-db-migrator application
[+] Running 3/3
 ✔ Network compose_default       Created                                                                                                                                                                                                                                             0.2s 
 ✔ Container mariadb             Started                                                                                                                                                                                                                                             0.0s 
 ✔ Container policy-db-migrator  Started  


docker logs policy-db-migrator 
Waiting for mariadb port 3306...
nc: connect to mariadb (172.21.0.2) port 3306 (tcp) failed: Connection refused
nc: connect to mariadb (172.21.0.2) port 3306 (tcp) failed: Connection refused
nc: connect to mariadb (172.21.0.2) port 3306 (tcp) failed: Connection refused
nc: connect to mariadb (172.21.0.2) port 3306 (tcp) failed: Connection refused
nc: connect to mariadb (172.21.0.2) port 3306 (tcp) failed: Connection refused
Connection to mariadb (172.21.0.2) 3306 port [tcp/mysql] succeeded!
11 blocks
Preparing upgrade release version: 1400
Done
name	version
clampacm	0
clampacm: upgrade available: 0 -> 1400
upgrade: 0 -> 1400

> upgrade 0100-automationcomposition.sql
--------------
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))
--------------


> upgrade 0200-automationcompositiondefinition.sql
--------------
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))
--------------


> upgrade 0300-automationcompositionelement.sql
--------------
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))
--------------


> upgrade 0400-nodetemplatestate.sql
--------------
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))
--------------


> upgrade 0500-participant.sql
--------------
CREATE TABLE clampacm.participant (participantId VARCHAR(255) NOT NULL, `description` VARCHAR(255) NULL, participantState TINYINT DEFAULT NULL NULL, CONSTRAINT PK_PARTICIPANT PRIMARY KEY (participantId))
--------------


> upgrade 0600-participantsupportedelements.sql
--------------
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))
--------------


> upgrade 0700-ac_compositionId_index.sql
--------------
CREATE INDEX ac_compositionId ON clampacm.automationcomposition(compositionId)
--------------


> upgrade 0800-ac_element_fk_index.sql
--------------
CREATE INDEX ac_element_fk ON clampacm.automationcompositionelement(instanceId)
--------------


> upgrade 0900-dt_element_fk_index.sql
--------------
CREATE INDEX dt_element_fk ON clampacm.nodetemplatestate(compositionId)
--------------


> upgrade 1000-supported_element_fk_index.sql
--------------
CREATE INDEX supported_element_fk ON clampacm.participantsupportedacelements(participantId)
--------------


> upgrade 1100-automationcompositionelement_fk.sql
--------------
ALTER TABLE clampacm.automationcompositionelement ADD CONSTRAINT ac_element_fk FOREIGN KEY (instanceId) REFERENCES clampacm.automationcomposition (instanceId) ON UPDATE RESTRICT ON DELETE RESTRICT
--------------


> upgrade 1200-nodetemplate_fk.sql
--------------
ALTER TABLE clampacm.nodetemplatestate ADD CONSTRAINT dt_element_fk FOREIGN KEY (compositionId) REFERENCES clampacm.automationcompositiondefinition (compositionId) ON UPDATE RESTRICT ON DELETE RESTRICT
--------------


> upgrade 1300-participantsupportedelements_fk.sql
--------------
ALTER TABLE clampacm.participantsupportedacelements ADD CONSTRAINT supported_element_fk FOREIGN KEY (participantId) REFERENCES clampacm.participant (participantId) ON UPDATE RESTRICT ON DELETE RESTRICT
--------------

clampacm: OK: upgrade (1400)
name	version
clampacm	1400
ID	script	operation	from_version	to_version	tag	success	atTime
1	0100-automationcomposition.sql	upgrade	1300	1400	1604241610431400u	1	2024-04-16 16:10:43
2	0200-automationcompositiondefinition.sql	upgrade	1300	1400	1604241610431400u	1	2024-04-16 16:10:43
3	0300-automationcompositionelement.sql	upgrade	1300	1400	1604241610431400u	1	2024-04-16 16:10:43
4	0400-nodetemplatestate.sql	upgrade	1300	1400	1604241610431400u	1	2024-04-16 16:10:43
5	0500-participant.sql	upgrade	1300	1400	1604241610431400u	1	2024-04-16 16:10:43
6	0600-participantsupportedelements.sql	upgrade	1300	1400	1604241610431400u	1	2024-04-16 16:10:43
7	0700-ac_compositionId_index.sql	upgrade	1300	1400	1604241610431400u	1	2024-04-16 16:10:43
8	0800-ac_element_fk_index.sql	upgrade	1300	1400	1604241610431400u	1	2024-04-16 16:10:44
9	0900-dt_element_fk_index.sql	upgrade	1300	1400	1604241610431400u	1	2024-04-16 16:10:44
10	1000-supported_element_fk_index.sql	upgrade	1300	1400	1604241610431400u	1	2024-04-16 16:10:44
11	1100-automationcompositionelement_fk.sql	upgrade	1300	1400	1604241610431400u	1	2024-04-16 16:10:44
12	1200-nodetemplate_fk.sql	upgrade	1300	1400	1604241610431400u	1	2024-04-16 16:10:44
13	1300-participantsupportedelements_fk.sql	upgrade	1300	1400	1604241610431400u	1	2024-04-16 16:10:44
clampacm: OK @ 1400


Testing downgrade


  • No labels