diff options
| -rw-r--r-- | cps-ri/src/main/resources/changelog/db/changes/16-insert-cm-handle-state-forward.sql | 140 | ||||
| -rw-r--r-- | cps-ri/src/main/resources/changelog/db/changes/16-insert-cm-handle-state-rollback.sql | 8 |
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 |