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
(cherry picked from commit 8b37702647755949165dc822116ed10dca5c1c2f)

3 files changed, 150 insertions, 7 deletions

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 64b185f3b2..01d441f460 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 aaf05a24cb..4b006ef0e2 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
diff --git a/docs/release-notes.rst b/docs/release-notes.rst
index 8c90a1ffc7..7d2c93132c 100755
--- a/docs/release-notes.rst
+++ b/docs/release-notes.rst
@@ -40,6 +40,15 @@ Bug Fixes
 ---------
    - `CPS-1312 <https://jira.onap.org/browse/CPS-1312>`_  CPS(/NCMP) does not have version control
 
+
+Known Limitations, Issues and Workarounds
+-----------------------------------------
+
+*System Limitations*
+
+For upgrading, CPS uses Liquibase for database upgrades. CPS/NCMP currently only supports upgrading from Liquibase changelog 11 to Liquibase changelog 16.
+This is from commit CPS-506: List all known modules and revision to CPS-1312: Default CMHandles to READY during upgrade or from ONAP release Honolulu to Kohn.
+
 Version: 3.1.4
 ==============