1 files changed, 137 insertions, 3 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 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