From b837d549ff028ec2282a399e152f2137f2714ac4 Mon Sep 17 00:00:00 2001 From: Jeff Bradberry Date: Wed, 8 May 2024 14:09:34 -0400 Subject: 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. --- tools/scripts/ig-hotfix/foreignkeys.sql | 48 ++++++++++++++++++++++++--------- 1 file changed, 35 insertions(+), 13 deletions(-) (limited to 'tools/scripts/ig-hotfix') 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; -- cgit v1.2.3