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