From: lukegleeson <luke.gleeson@est.tech>
Date: Tue, 11 Oct 2022 16:32:38 +0000 (+0100)
Subject: Default CMHandles to READY during upgrade
X-Git-Tag: 3.2.1~88^2
X-Git-Url: https://gerrit.onap.org/r/gitweb?p=cps.git;a=commitdiff_plain;h=8b37702647755949165dc822116ed10dca5c1c2f

Default CMHandles to READY during upgrade

Allows upgrade from liquibase changelog 11 to 16
Sets CMHandles which do not have a state to state READY

Issue-ID: CPS-1312
Signed-off-by: lukegleeson <luke.gleeson@est.tech>
Change-Id: I6b6b05ba6fad3d174e43e9c385cbc1f9f4e4e5e5
---

diff --git a/cps-ri/src/main/resources/changelog/db/changes/16-insert-cm-handle-state-forward.sql b/cps-ri/src/main/resources/changelog/db/changes/16-insert-cm-handle-state-forward.sql
index 64b185f3b..01d441f46 100644
--- a/cps-ri/src/main/resources/changelog/db/changes/16-insert-cm-handle-state-forward.sql
+++ b/cps-ri/src/main/resources/changelog/db/changes/16-insert-cm-handle-state-forward.sql
@@ -1,3 +1,137 @@
-create view cmHandles as select * from fragment where xpath ~* '^/dmi-registry/cm-handles\[@id=''[\w\-]+''\]$';
-insert into fragment(xpath, attributes, anchor_id, parent_id, dataspace_id, schema_node_id) select concat(xpath, '/state'), to_jsonb(concat('{"cm-handle-state": "ADVISED", "last-update-time": "', to_char(now(), 'YYYY-MM-DD"T"HH24:MI:SS.MSTZHTZM'), '"}')::json), anchor_id, id, dataspace_id, schema_node_id from cmHandles;
-drop view cmHandles;
\ No newline at end of file
+INSERT INTO
+	fragment(
+		xpath,
+		attributes,
+		anchor_id,
+		parent_id,
+		dataspace_id,
+		schema_node_id
+	)
+SELECT
+	concat(cmHandles.xpath, '/state') AS xpath,
+	to_jsonb(
+		concat(
+			'{"cm-handle-state": "READY", "last-update-time": "',
+			to_char(
+				now(),
+				'YYYY-MM-DD"T"HH24:MI:SS.MSTZHTZM'
+			),
+			'", "data-sync-enabled": false}'
+		) :: json
+	) AS attributes,
+	cmHandles.anchor_id,
+	cmHandles.id,
+	cmHandles.dataspace_id,
+	cmHandles.schema_node_id
+FROM
+	(
+		SELECT
+			id,
+			xpath,
+			anchor_id,
+			dataspace_id,
+			schema_node_id
+		FROM
+			fragment
+		WHERE
+			xpath ~* '^/dmi-registry/cm-handles\[@id=''[\w\-]+''\]$'
+			AND xpath NOT IN (
+				SELECT
+					SUBSTRING(
+						xpath
+						FROM
+							'^/dmi-registry/cm-handles\[@id=''[\w\-]+''\]'
+					)
+				FROM
+					fragment
+				WHERE
+					xpath ~* '^/dmi-registry/cm-handles\[@id=''[\w\-]+''\]/state$'
+			)
+	) AS cmHandles;
+INSERT INTO
+	fragment(
+		xpath,
+		attributes,
+		anchor_id,
+		parent_id,
+		dataspace_id,
+		schema_node_id
+	)
+SELECT
+	concat(cmHandlesStates.xpath, '/datastores'),
+	to_jsonb('{}' :: json),
+	cmHandlesStates.anchor_id,
+	cmHandlesStates.id,
+	cmHandlesStates.dataspace_id,
+	cmHandlesStates.schema_node_id
+FROM
+	(
+		SELECT
+			id,
+			xpath,
+			anchor_id,
+			dataspace_id,
+			schema_node_id
+		FROM
+			fragment
+		WHERE
+			xpath ~* '^/dmi-registry/cm-handles\[@id=''[\w\-]+''\]/state$'
+			AND xpath NOT IN (
+				SELECT
+					SUBSTRING(
+						xpath
+						FROM
+							'^/dmi-registry/cm-handles\[@id=''[\w\-]+''\]/state'
+					)
+				FROM
+					fragment
+				WHERE
+					xpath ~* '^/dmi-registry/cm-handles\[@id=''[\w\-]+''\]/state/datastores$'
+			)
+	) AS cmHandlesStates;
+INSERT INTO
+	fragment(
+		xpath,
+		attributes,
+		anchor_id,
+		parent_id,
+		dataspace_id,
+		schema_node_id
+	)
+SELECT
+	concat(
+		cmHandlesDatastores.xpath,
+		'/operational'
+	),
+	to_jsonb(
+		concat('{"sync-state": "NONE_REQUESTED"}') :: json
+	),
+	cmHandlesDatastores.anchor_id,
+	cmHandlesDatastores.id,
+	cmHandlesDatastores.dataspace_id,
+	cmHandlesDatastores.schema_node_id
+FROM
+	(
+		SELECT
+			id,
+			xpath,
+			anchor_id,
+			dataspace_id,
+			schema_node_id
+		FROM
+			fragment
+		WHERE
+			xpath ~* '^/dmi-registry/cm-handles\[@id=''[\w\-]+''\]/state/datastores$'
+			AND xpath NOT IN (
+				SELECT
+					SUBSTRING(
+						xpath
+						FROM
+							'^/dmi-registry/cm-handles\[@id=''[\w\-]+''\]/state/datastores'
+					)
+				FROM
+					fragment
+				WHERE
+					xpath ~* '^/dmi-registry/cm-handles\[@id=''[\w\-]+''\]/state/datastores/operational$'
+			)
+	) AS cmHandlesDatastores;
\ No newline at end of file
diff --git a/cps-ri/src/main/resources/changelog/db/changes/16-insert-cm-handle-state-rollback.sql b/cps-ri/src/main/resources/changelog/db/changes/16-insert-cm-handle-state-rollback.sql
index aaf05a24c..4b006ef0e 100644
--- a/cps-ri/src/main/resources/changelog/db/changes/16-insert-cm-handle-state-rollback.sql
+++ b/cps-ri/src/main/resources/changelog/db/changes/16-insert-cm-handle-state-rollback.sql
@@ -1,4 +1,4 @@
-delete from fragment where xpath ~* '^/dmi-registry/cm-handles\[@id=''[\w\-]+''\]/state/lock-reason$';
-delete from fragment where xpath ~* '^/dmi-registry/cm-handles\[@id=''[\w\-]+''\]/state/datastores/operational$';
-delete from fragment where xpath ~* '^/dmi-registry/cm-handles\[@id=''[\w\-]+''\]/state/datastores$';
-delete from fragment where xpath ~* '^/dmi-registry/cm-handles\[@id=''[\w\-]+''\]/state$';
\ No newline at end of file
+DELETE FROM fragment WHERE xpath ~* '^/dmi-registry/cm-handles\[@id=''[\w\-]+''\]/state/lock-reason$';
+DELETE FROM fragment WHERE xpath ~* '^/dmi-registry/cm-handles\[@id=''[\w\-]+''\]/state/datastores/operational$';
+DELETE FROM fragment WHERE xpath ~* '^/dmi-registry/cm-handles\[@id=''[\w\-]+''\]/state/datastores$';
+DELETE FROM fragment WHERE xpath ~* '^/dmi-registry/cm-handles\[@id=''[\w\-]+''\]/state$';
\ No newline at end of file