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 64b185f..01d441f 100644 (file)
--- 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 aaf05a2..4b006ef 100644 (file)
--- 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