summaryrefslogtreecommitdiffstats
path: root/cps-ri/src/main/resources/changelog/db
diff options
context:
space:
mode:
authorlukegleeson <luke.gleeson@est.tech>2022-10-11 17:32:38 +0100
committerlukegleeson <luke.gleeson@est.tech>2022-10-12 10:00:56 +0100
commit8b37702647755949165dc822116ed10dca5c1c2f (patch)
tree49a1a426f2b3044e508285d5346c37cbb500e827 /cps-ri/src/main/resources/changelog/db
parent63093a3a85d2444e54de5db17101810b2e66e21e (diff)
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
Diffstat (limited to 'cps-ri/src/main/resources/changelog/db')
-rw-r--r--cps-ri/src/main/resources/changelog/db/changes/16-insert-cm-handle-state-forward.sql140
-rw-r--r--cps-ri/src/main/resources/changelog/db/changes/16-insert-cm-handle-state-rollback.sql8
2 files changed, 141 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