cps-ri/src/main/resources/changelog/db/changes/16-insert-cm-handle-state-forward.sql

   1 INSERT INTO
   2         fragment(
   3                 xpath,
   4                 attributes,
   5                 anchor_id,
   6                 parent_id,
   7                 dataspace_id,
   8                 schema_node_id
   9         )
  10 SELECT
  11         concat(cmHandles.xpath, '/state') AS xpath,
  12         to_jsonb(
  13                 concat(
  14                         '{"cm-handle-state": "READY", "last-update-time": "',
  15                         to_char(
  16                                 now(),
  17                                 'YYYY-MM-DD"T"HH24:MI:SS.MSTZHTZM'
  18                         ),
  19                         '", "data-sync-enabled": false}'
  20                 ) :: json
  21         ) AS attributes,
  22         cmHandles.anchor_id,
  23         cmHandles.id,
  24         cmHandles.dataspace_id,
  25         cmHandles.schema_node_id
  26 FROM
  27         (
  28                 SELECT
  29                         id,
  30                         xpath,
  31                         anchor_id,
  32                         dataspace_id,
  33                         schema_node_id
  34                 FROM
  35                         fragment
  36                 WHERE
  37                         xpath ~* '^/dmi-registry/cm-handles\[@id=''[\w\-]+''\]$'
  38                         AND xpath NOT IN (
  39                                 SELECT
  40                                         SUBSTRING(
  41                                                 xpath
  42                                                 FROM
  43                                                         '^/dmi-registry/cm-handles\[@id=''[\w\-]+''\]'
  44                                         )
  45                                 FROM
  46                                         fragment
  47                                 WHERE
  48                                         xpath ~* '^/dmi-registry/cm-handles\[@id=''[\w\-]+''\]/state$'
  49                         )
  50         ) AS cmHandles;
  51 INSERT INTO
  52         fragment(
  53                 xpath,
  54                 attributes,
  55                 anchor_id,
  56                 parent_id,
  57                 dataspace_id,
  58                 schema_node_id
  59         )
  60 SELECT
  61         concat(cmHandlesStates.xpath, '/datastores'),
  62         to_jsonb('{}' :: json),
  63         cmHandlesStates.anchor_id,
  64         cmHandlesStates.id,
  65         cmHandlesStates.dataspace_id,
  66         cmHandlesStates.schema_node_id
  67 FROM
  68         (
  69                 SELECT
  70                         id,
  71                         xpath,
  72                         anchor_id,
  73                         dataspace_id,
  74                         schema_node_id
  75                 FROM
  76                         fragment
  77                 WHERE
  78                         xpath ~* '^/dmi-registry/cm-handles\[@id=''[\w\-]+''\]/state$'
  79                         AND xpath NOT IN (
  80                                 SELECT
  81                                         SUBSTRING(
  82                                                 xpath
  83                                                 FROM
  84                                                         '^/dmi-registry/cm-handles\[@id=''[\w\-]+''\]/state'
  85                                         )
  86                                 FROM
  87                                         fragment
  88                                 WHERE
  89                                         xpath ~* '^/dmi-registry/cm-handles\[@id=''[\w\-]+''\]/state/datastores$'
  90                         )
  91         ) AS cmHandlesStates;
  92 INSERT INTO
  93         fragment(
  94                 xpath,
  95                 attributes,
  96                 anchor_id,
  97                 parent_id,
  98                 dataspace_id,
  99                 schema_node_id
 100         )
 101 SELECT
 102         concat(
 103                 cmHandlesDatastores.xpath,
 104                 '/operational'
 105         ),
 106         to_jsonb(
 107                 concat('{"sync-state": "NONE_REQUESTED"}') :: json
 108         ),
 109         cmHandlesDatastores.anchor_id,
 110         cmHandlesDatastores.id,
 111         cmHandlesDatastores.dataspace_id,
 112         cmHandlesDatastores.schema_node_id
 113 FROM
 114         (
 115                 SELECT
 116                         id,
 117                         xpath,
 118                         anchor_id,
 119                         dataspace_id,
 120                         schema_node_id
 121                 FROM
 122                         fragment
 123                 WHERE
 124                         xpath ~* '^/dmi-registry/cm-handles\[@id=''[\w\-]+''\]/state/datastores$'
 125                         AND xpath NOT IN (
 126                                 SELECT
 127                                         SUBSTRING(
 128                                                 xpath
 129                                                 FROM
 130                                                         '^/dmi-registry/cm-handles\[@id=''[\w\-]+''\]/state/datastores'
 131                                         )
 132                                 FROM
 133                                         fragment
 134                                 WHERE
 135                                         xpath ~* '^/dmi-registry/cm-handles\[@id=''[\w\-]+''\]/state/datastores/operational$'
 136                         )
 137         ) AS cmHandlesDatastores;