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'
]
./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
K8s/Helm testing
Run the k8s csit script to install the helm charts
wayne@wayne-XPS-15-9530:~/dev/policy/clamp_db_mig/csit$ ./run-k8s-csit.sh install Verify if Microk8s cluster is running.. MicroK8s v1.28.8 revision 6683 K8s cluster is already running ---------------------------------------- Installing Confluent kafka deployment.apps/zookeeper-deployment unchanged service/zookeeper-service unchanged deployment.apps/kafka-deployment unchanged service/kafka unchanged ---------------------------------------- Pod 'kafka-deployment-75dbc7cb9b-65g5c' in namespace 'default' is now in 'Running' state. Pod 'zookeeper-deployment-6d897c947f-fnwkt' in namespace 'default' is now in 'Running' state. All specified pods are in the 'Running' state. Exiting the function. Setting project configuration for: Unknown project supplied. Enabling all policy charts for the deployment Installing policy helm charts in the default namespace Update the latest image tags: policy: policy-api:3.1.2-SNAPSHOT policy-pap:3.1.2-SNAPSHOT policy-apex-pdp:3.1.2-SNAPSHOT policy-models-simulator:3.1.2-SNAPSHOT policy-pdpd-cl:2.1.2-SNAPSHOT policy-xacml-pdp:3.1.2-SNAPSHOT policy-distribution:3.1.2-SNAPSHOT clamp:7.1.2-SNAPSHOT policy-clamp-runtime-acm:7.1.2-SNAPSHOT policy-clamp-ac-kserve-ppnt:7.1.2-SNAPSHOT policy-clamp-ac-k8s-ppnt:7.1.2-SNAPSHOT policy-clamp-ac-pf-ppnt:7.1.2-SNAPSHOT policy-clamp-ac-http-ppnt:7.1.2-SNAPSHOT policy-clamp-ac-a1pms-ppnt:7.1.2-SNAPSHOT Saving 14 charts Deleting outdated charts W0425 08:36:04.890403 2926813 warnings.go:70] spec.template.spec.containers[0].ports[1]: duplicate port definition with spec.template.spec.containers[0].ports[0] NAME: csit-policy LAST DEPLOYED: Thu Apr 25 08:36:03 2024 NAMESPACE: default STATUS: deployed REVISION: 1 NAME: prometheus LAST DEPLOYED: Thu Apr 25 08:36:05 2024 NAMESPACE: default STATUS: deployed REVISION: 1 TEST SUITE: None Waiting for pod 'policy-apex-pdp-0' in namespace 'default' to reach 'Running' state... Waiting for pod 'policy-api-58b95c6bc-5c5nr' in namespace 'default' to reach 'Running' state... Waiting for pod 'policy-pap-6d9c5b4969-xsmmn' in namespace 'default' to reach 'Running' state... Pod 'policy-apex-pdp-0' in namespace 'default' is now in 'Running' state. Waiting for pod 'policy-api-58b95c6bc-5c5nr' in namespace 'default' to reach 'Running' state... Waiting for pod 'policy-pap-6d9c5b4969-xsmmn' in namespace 'default' to reach 'Running' state... Waiting for pod 'policy-api-58b95c6bc-5c5nr' in namespace 'default' to reach 'Running' state... Waiting for pod 'policy-pap-6d9c5b4969-xsmmn' in namespace 'default' to reach 'Running' state... Waiting for pod 'policy-api-58b95c6bc-5c5nr' in namespace 'default' to reach 'Running' state... Waiting for pod 'policy-pap-6d9c5b4969-xsmmn' in namespace 'default' to reach 'Running' state... Waiting for pod 'policy-api-58b95c6bc-5c5nr' in namespace 'default' to reach 'Running' state... Waiting for pod 'policy-pap-6d9c5b4969-xsmmn' in namespace 'default' to reach 'Running' state... Waiting for pod 'policy-api-58b95c6bc-5c5nr' in namespace 'default' to reach 'Running' state... Waiting for pod 'policy-pap-6d9c5b4969-xsmmn' in namespace 'default' to reach 'Running' state... Waiting for pod 'policy-api-58b95c6bc-5c5nr' in namespace 'default' to reach 'Running' state... Waiting for pod 'policy-pap-6d9c5b4969-xsmmn' in namespace 'default' to reach 'Running' state... Waiting for pod 'policy-api-58b95c6bc-5c5nr' in namespace 'default' to reach 'Running' state... Waiting for pod 'policy-pap-6d9c5b4969-xsmmn' in namespace 'default' to reach 'Running' state... Pod 'policy-api-58b95c6bc-5c5nr' in namespace 'default' is now in 'Running' state. Waiting for pod 'policy-pap-6d9c5b4969-xsmmn' in namespace 'default' to reach 'Running' state... Pod 'policy-pap-6d9c5b4969-xsmmn' in namespace 'default' is now in 'Running' state. All specified pods are in the 'Running' state. Exiting the function. Policy chart installation completed ------------------------------------------- No project supplied for running CSIT
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.
wayne@wayne-XPS-15-9530:~/dev/policy/clamp_db_mig/csit$ kubectl exec -it mariadb-galera-0 sh kubectl exec [POD] [COMMAND] is DEPRECATED and will be removed in a future version. Use kubectl exec [POD] -- [COMMAND] instead. Defaulted container "mariadb-galera" out of: mariadb-galera, fix-permission (init) $ mariadb -u policy-user -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 472 Server version: 10.5.8-MariaDB-log Source distribution Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> MariaDB [(none)]> MariaDB [(none)]> use migration; sReading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [migration]> show tables; +------------------------------+ | Tables_in_migration | +------------------------------+ | clampacm_schema_changelog | | metadata_versions | | policyadmin_schema_changelog | | pooling_history | | schema_versions | +------------------------------+ 5 rows in set (0.001 sec) MariaDB [migration]> select * from schema_versions; +-------------+---------+ | name | version | +-------------+---------+ | clampacm | 1400 | | policyadmin | 1300 | +-------------+---------+ 2 rows in set (0.181 sec) MariaDB [migration]> select * from clampacm_schema_changelog; +----+------------------------------------------+-----------+--------------+------------+-------------------+---------+---------------------+ | ID | script | operation | from_version | to_version | tag | success | atTime | +----+------------------------------------------+-----------+--------------+------------+-------------------+---------+---------------------+ | 1 | 0100-automationcomposition.sql | upgrade | 1300 | 1400 | 2504240736421400u | 1 | 2024-04-25 07:36:42 | | 2 | 0200-automationcompositiondefinition.sql | upgrade | 1300 | 1400 | 2504240736421400u | 1 | 2024-04-25 07:36:42 | | 3 | 0300-automationcompositionelement.sql | upgrade | 1300 | 1400 | 2504240736421400u | 1 | 2024-04-25 07:36:42 | | 4 | 0400-nodetemplatestate.sql | upgrade | 1300 | 1400 | 2504240736421400u | 1 | 2024-04-25 07:36:42 | | 5 | 0500-participant.sql | upgrade | 1300 | 1400 | 2504240736421400u | 1 | 2024-04-25 07:36:42 | | 6 | 0600-participantsupportedelements.sql | upgrade | 1300 | 1400 | 2504240736421400u | 1 | 2024-04-25 07:36:42 | | 7 | 0700-ac_compositionId_index.sql | upgrade | 1300 | 1400 | 2504240736421400u | 1 | 2024-04-25 07:36:42 | | 8 | 0800-ac_element_fk_index.sql | upgrade | 1300 | 1400 | 2504240736421400u | 1 | 2024-04-25 07:36:42 | | 9 | 0900-dt_element_fk_index.sql | upgrade | 1300 | 1400 | 2504240736421400u | 1 | 2024-04-25 07:36:42 | | 10 | 1000-supported_element_fk_index.sql | upgrade | 1300 | 1400 | 2504240736421400u | 1 | 2024-04-25 07:36:42 | | 11 | 1100-automationcompositionelement_fk.sql | upgrade | 1300 | 1400 | 2504240736421400u | 1 | 2024-04-25 07:36:42 | | 12 | 1200-nodetemplate_fk.sql | upgrade | 1300 | 1400 | 2504240736421400u | 1 | 2024-04-25 07:36:42 | | 13 | 1300-participantsupportedelements_fk.sql | upgrade | 1300 | 1400 | 2504240736421400u | 1 | 2024-04-25 07:36:43 | +----+------------------------------------------+-----------+--------------+------------+-------------------+---------+---------------------+ 13 rows in set (0.001 sec) MariaDB [migration]>