Split the foreign key sql script into an 'into' and 'from' portion

Also, make use of up-front defined arrays of the tables involved, for
ease of editing in the future.

1 files changed, 35 insertions, 13 deletions

diff --git a/tools/scripts/ig-hotfix/foreignkeys.sql b/tools/scripts/ig-hotfix/foreignkeys.sql
index b5c5389e61..e0816c4bd8 100644
--- a/tools/scripts/ig-hotfix/foreignkeys.sql
+++ b/tools/scripts/ig-hotfix/foreignkeys.sql
@@ -1,13 +1,35 @@
-SELECT DISTINCT
-    tc.table_name, 
-    ccu.table_name AS foreign_table_name
-FROM information_schema.table_constraints AS tc 
-JOIN information_schema.constraint_column_usage AS ccu
-    ON ccu.constraint_name = tc.constraint_name
-WHERE tc.constraint_type = 'FOREIGN KEY'
-    AND
-        (tc.table_name IN ('main_instance', 'main_instancegroup', 'main_instancegroup_instances')
-         AND ccu.table_name NOT IN ('main_instance', 'main_instancegroup', 'main_instancegroup_instances'))
-        OR
-        (ccu.table_name IN ('main_instance', 'main_instancegroup', 'main_instancegroup_instances')
-         AND tc.table_name NOT IN ('main_instance', 'main_instancegroup', 'main_instancegroup_instances'));
+DO $$
+DECLARE
+    topology text[] := ARRAY['main_instance', 'main_instancegroup', 'main_instancegroup_instances'];
+    excluded text[] := ARRAY['main_instance', 'main_instancegroup', 'main_instancegroup_instances', 'main_organizationinstancegroupmembership', 'main_unifiedjobtemplateinstancegroupmembership', 'main_inventoryinstancegroupmembership'];
+BEGIN
+    CREATE TABLE tmp_fk_from AS (
+        SELECT DISTINCT
+            tc.table_name, 
+            ccu.table_name AS foreign_table_name
+        FROM information_schema.table_constraints AS tc 
+        JOIN information_schema.constraint_column_usage AS ccu
+            ON ccu.constraint_name = tc.constraint_name
+        WHERE tc.constraint_type = 'FOREIGN KEY'
+            AND tc.table_name = ANY (excluded)
+            AND NOT ccu.table_name = ANY (topology)
+    );
+
+    CREATE TABLE tmp_fk_into AS (
+        SELECT DISTINCT
+            tc.table_name, 
+            ccu.table_name AS foreign_table_name
+        FROM information_schema.table_constraints AS tc 
+        JOIN information_schema.constraint_column_usage AS ccu
+            ON ccu.constraint_name = tc.constraint_name
+        WHERE tc.constraint_type = 'FOREIGN KEY'
+            AND ccu.table_name = ANY (excluded)
+            AND NOT tc.table_name = ANY (topology)
+    );
+END $$;
+
+SELECT * FROM tmp_fk_from;
+SELECT * FROM tmp_fk_into;
+
+DROP TABLE tmp_fk_from;
+DROP TABLE tmp_fk_into;