From 61aaf0930456b86d8cbc177385ff4805a024d4ca Mon Sep 17 00:00:00 2001
From: Christian Weiske <cweiske@cweiske.de>
Date: Wed, 5 Feb 2020 16:06:08 +0100
Subject: [PATCH]

---
 1-add.sql    | 20 ++++++++++++++++++++
 2-remove.sql | 20 ++++++++++++++++++++
 2 files changed, 40 insertions(+)
 create mode 100644 1-add.sql
 create mode 100644 2-remove.sql

diff --git a/1-add.sql b/1-add.sql
new file mode 100644
index 0000000..126b4cc
--- /dev/null
+++ b/1-add.sql
@@ -0,0 +1,20 @@
+UPDATE node_data
+JOIN nodes ON nodes.id = node_data.node_id
+SET external = "{}"
+WHERE
+  node_data.external IS NULL
+  AND nodes.available_for_rent = 1;
+
+UPDATE node_data
+JOIN nodes ON nodes.id = node_data.node_id
+SET external = JSON_SET(external, '$.export_portals', JSON_ARRAY())
+WHERE
+  NOT JSON_CONTAINS_PATH(external, 'all', '$.export_portals')
+  AND nodes.available_for_rent = 1;
+
+UPDATE node_data
+JOIN nodes ON nodes.id = node_data.node_id
+SET external = JSON_ARRAY_APPEND(external, '$.export_portals', 'hpm-api')
+WHERE
+  NOT JSON_CONTAINS(external, '["hpm-api"]', '$.export_portals')
+  AND nodes.available_for_rent = 1;
diff --git a/2-remove.sql b/2-remove.sql
new file mode 100644
index 0000000..0f73a5a
--- /dev/null
+++ b/2-remove.sql
@@ -0,0 +1,20 @@
+UPDATE node_data
+JOIN nodes ON nodes.id = node_data.node_id
+SET external = JSON_REMOVE(external, JSON_UNQUOTE(JSON_SEARCH(external, 'one', 'hpm-api', NULL, '$.export_portals')))
+WHERE
+  JSON_CONTAINS(external, '["hpm-api"]', '$.export_portals')
+  AND nodes.available_for_rent = 1;
+
+UPDATE node_data
+JOIN nodes ON nodes.id = node_data.node_id
+SET external = JSON_REMOVE(external, '$.export_portals')
+WHERE
+  external->'$.export_portals' = JSON_ARRAY()
+  AND nodes.available_for_rent = 1;
+
+UPDATE node_data
+JOIN nodes ON nodes.id = node_data.node_id
+SET external = NULL
+WHERE
+  external = JSON_OBJECT()
+  AND nodes.available_for_rent = 1;
-- 
2.30.2