[icinga-checkins] icinga.org: icingaweb2-module-director/master: schema/mysql: migrate all remaining assignments

git at icinga.org git at icinga.org
Tue Oct 25 04:39:59 CEST 2016


Module: icingaweb2-module-director
Branch: master
Commit: 3ff6fd5211da6876ad9ad355c535b4743dac965c
URL:    https://git.icinga.org/?p=icingaweb2-module-director.git;a=commit;h=3ff6fd5211da6876ad9ad355c535b4743dac965c

Author: Thomas Gelf <thomas at gelf.net>
Date:   Mon Oct 24 00:35:20 2016 +0000

schema/mysql: migrate all remaining assignments

---

 schema/mysql-migrations/upgrade_120.sql |  139 ++++++++++++++++++++++++++++++-
 1 file changed, 138 insertions(+), 1 deletion(-)

diff --git a/schema/mysql-migrations/upgrade_120.sql b/schema/mysql-migrations/upgrade_120.sql
index ddde90f..7fca281 100644
--- a/schema/mysql-migrations/upgrade_120.sql
+++ b/schema/mysql-migrations/upgrade_120.sql
@@ -40,7 +40,144 @@ UPDATE icinga_service s JOIN (
 
 ) flat_assign ON s.id = flat_assign.service_id SET s.assign_filter = flat_assign.filter_string;
 
-DROP TABLE icigna_service_assignment;
+DROP TABLE icinga_service_assignment;
+
+ALTER TABLE icinga_service_set ADD COLUMN assign_filter TEXT;
+
+UPDATE icinga_service_set s JOIN (
+
+    SELECT
+        service_set_id,
+          CASE WHEN COUNT(*) = 0 THEN NULL
+               WHEN COUNT(*) = 1 THEN sa.filter_string
+               ELSE GROUP_CONCAT(sa.filter_string SEPARATOR '&') END AS filter_string
+    FROM (
+        SELECT
+          sa_not.service_set_id,
+          CASE WHEN COUNT(*) = 0 THEN NULL
+               WHEN COUNT(*) = 1 THEN sa_not.filter_string
+               ELSE '(' || GROUP_CONCAT(sa_not.filter_string SEPARATOR '&') || ')' END AS filter_string
+          FROM ( SELECT
+            sa.service_set_id,
+            '!' || sa.filter_string AS filter_string
+            FROM icinga_service_set_assignment sa
+            WHERE assign_type = 'ignore'
+          ) sa_not
+          GROUP BY service_set_id
+
+        UNION ALL
+
+        SELECT
+          sa_yes.service_set_id,
+          CASE WHEN COUNT(*) = 0 THEN NULL
+               WHEN COUNT(*) = 1 THEN sa_yes.filter_string
+               ELSE '(' || GROUP_CONCAT(sa_yes.filter_string SEPARATOR '|') || ')' END AS filter_string
+          FROM ( SELECT
+            sa.service_set_id,
+            sa.filter_string AS filter_string
+            FROM icinga_service_set_assignment sa
+            WHERE assign_type = 'assign'
+          ) sa_yes
+          GROUP BY service_set_id
+
+    ) sa GROUP BY service_set_id
+
+) flat_assign ON s.id = flat_assign.service_set_id SET s.assign_filter = flat_assign.filter_string;
+
+DROP TABLE icinga_service_set_assignment;
+
+
+ALTER TABLE icinga_notification ADD COLUMN assign_filter TEXT;
+
+UPDATE icinga_notification s JOIN (
+
+    SELECT
+        notification_id,
+          CASE WHEN COUNT(*) = 0 THEN NULL
+               WHEN COUNT(*) = 1 THEN sa.filter_string
+               ELSE GROUP_CONCAT(sa.filter_string SEPARATOR '&') END AS filter_string
+    FROM (
+        SELECT
+          sa_not.notification_id,
+          CASE WHEN COUNT(*) = 0 THEN NULL
+               WHEN COUNT(*) = 1 THEN sa_not.filter_string
+               ELSE '(' || GROUP_CONCAT(sa_not.filter_string SEPARATOR '&') || ')' END AS filter_string
+          FROM ( SELECT
+            sa.notification_id,
+            '!' || sa.filter_string AS filter_string
+            FROM icinga_notification_assignment sa
+            WHERE assign_type = 'ignore'
+          ) sa_not
+          GROUP BY notification_id
+
+        UNION ALL
+
+        SELECT
+          sa_yes.notification_id,
+          CASE WHEN COUNT(*) = 0 THEN NULL
+               WHEN COUNT(*) = 1 THEN sa_yes.filter_string
+               ELSE '(' || GROUP_CONCAT(sa_yes.filter_string SEPARATOR '|') || ')' END AS filter_string
+          FROM ( SELECT
+            sa.notification_id,
+            sa.filter_string AS filter_string
+            FROM icinga_notification_assignment sa
+            WHERE assign_type = 'assign'
+          ) sa_yes
+          GROUP BY notification_id
+
+    ) sa GROUP BY notification_id
+
+) flat_assign ON s.id = flat_assign.notification_id SET s.assign_filter = flat_assign.filter_string;
+
+DROP TABLE icinga_notification_assignment;
+
+ALTER TABLE icinga_hostgroup ADD COLUMN assign_filter TEXT;
+
+UPDATE icinga_hostgroup s JOIN (
+
+    SELECT
+        hostgroup_id,
+          CASE WHEN COUNT(*) = 0 THEN NULL
+               WHEN COUNT(*) = 1 THEN sa.filter_string
+               ELSE GROUP_CONCAT(sa.filter_string SEPARATOR '&') END AS filter_string
+    FROM (
+        SELECT
+          sa_not.hostgroup_id,
+          CASE WHEN COUNT(*) = 0 THEN NULL
+               WHEN COUNT(*) = 1 THEN sa_not.filter_string
+               ELSE '(' || GROUP_CONCAT(sa_not.filter_string SEPARATOR '&') || ')' END AS filter_string
+          FROM ( SELECT
+            sa.hostgroup_id,
+            '!' || sa.filter_string AS filter_string
+            FROM icinga_hostgroup_assignment sa
+            WHERE assign_type = 'ignore'
+          ) sa_not
+          GROUP BY hostgroup_id
+
+        UNION ALL
+
+        SELECT
+          sa_yes.hostgroup_id,
+          CASE WHEN COUNT(*) = 0 THEN NULL
+               WHEN COUNT(*) = 1 THEN sa_yes.filter_string
+               ELSE '(' || GROUP_CONCAT(sa_yes.filter_string SEPARATOR '|') || ')' END AS filter_string
+          FROM ( SELECT
+            sa.hostgroup_id,
+            sa.filter_string AS filter_string
+            FROM icinga_hostgroup_assignment sa
+            WHERE assign_type = 'assign'
+          ) sa_yes
+          GROUP BY hostgroup_id
+
+    ) sa GROUP BY hostgroup_id
+
+) flat_assign ON s.id = flat_assign.hostgroup_id SET s.assign_filter = flat_assign.filter_string;
+
+DROP TABLE icinga_hostgroup_assignment;
+
+
+ALTER TABLE icinga_servicegroup ADD COLUMN assign_filter TEXT;
+
 
 INSERT INTO director_schema_migration
   (schema_version, migration_time)



More information about the icinga-checkins mailing list