Info

title	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

...

Split into separate sql upgrade & downgrade files.

Testing Upgrade

Docker Testing

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

...

Code Block

language	bash

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

Code Block

language	bash

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.

Code Block

language	bash

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

Version	Old Version 2	New Version Current
Changes made by	Wayne Dunican	Wayne Dunican
Saved on	Apr 17, 2024	Apr 25, 2024

Versions Compared

Key

Use liquibase to generate changelog for clampacm db

Split into separate sql upgrade & downgrade files.

Testing Upgrade

Docker Testing

K8s/Helm testing

Content Comparison

Versions Compared

Key

Use liquibase to generate changelog for clampacm db

Split into separate sql upgrade & downgrade files.

Testing Upgrade

Docker Testing

K8s/Helm testing