/* ############################################################################################################################## Script Name: RemoveServerNodes Description: the script is intended to delete one or multiple given ServerNodeConfigurations by ID(s) Caution: PLEASE BACKUP the whole QRS database before execute the script, in case error occurs, restore the backup, find out the data descrepancy, fix then execute again Revision history: Version Date Author Change Notes # 0.0.1 2016-11-15 Quan Sun Initial version ############################################################################################################################## */ /* Step 1. Delete records for the soft-references ############################################################################################################################## */ CREATE OR REPLACE FUNCTION nodes_to_delete() RETURNS TABLE (id uuid) AS $BODY$ SELECT "ID" FROM "ServerNodeConfigurations" WHERE "ID" in ( '107fd2d0-8e3b-4ef5-9fea-da08fb83c643' , '307e52a3-b3f2-439d-b197-ef70606a1ea4' , '32a97f2f-80c2-45fb-9575-f7072b5e65e8' , '8992ad1c-5ac6-44bc-9a30-2c179845afbb' , '96b07644-1124-4388-84a1-3c5abbd0e1c7' , 'a153e1aa-bf68-49f2-889e-418b0019bde2' , 'a3c24703-6c45-4ad2-b636-a0da1f593dd6' , 'c7dde2a4-24c1-41c6-902f-fa6e6ec57e5f' , 'ad25b083-d639-4cdc-86bb-0f42afe1efb7') AND "IsCentral" = false; $BODY$ LANGUAGE sql; DELETE FROM "LicenseAnalyzerAccessUsages" WHERE upper("ServerNodeConfigurationId") IN (SELECT upper(cast(id as varchar(50))) FROM nodes_to_delete()); DELETE FROM "LicenseProfessionalAccessUsages" WHERE upper("ServerNodeConfigurationId") IN (SELECT upper(cast(id as varchar(50))) FROM nodes_to_delete()); DELETE FROM "LicenseLoginAccessUsages" WHERE upper("ServerNodeConfigurationId") IN (SELECT upper(cast(id as varchar(50))) FROM nodes_to_delete()); DELETE FROM "LicenseLoginAccessUsageSessions" WHERE upper("ServerNodeConfigurationId") IN (SELECT upper(cast(id as varchar(50))) FROM nodes_to_delete()); DELETE FROM "LicenseUserAccessUsages" WHERE upper("ServerNodeConfigurationId") IN (SELECT upper(cast(id as varchar(50))) FROM nodes_to_delete()); DELETE FROM "ServerNodeConfigurationTags" WHERE "ServerNodeConfiguration_ID" IN (SELECT id FROM nodes_to_delete()); DELETE FROM "ExecutionSessions" WHERE "ExecutingNode_ID" IN (SELECT id FROM nodes_to_delete()); DELETE FROM "VirtualProxyConfigServerNodeConfigurations" WHERE "ServerNodeConfiguration_ID" IN (SELECT id FROM nodes_to_delete()); /* Step 2. Prepare for deletion: Alter foreign keys to Casacade Delete ############################################################################################################################## */ CREATE TABLE temp_foreign_key ( constraint_name VARCHAR, table_name VARCHAR, column_name VARCHAR, ref_table_name VARCHAR, ref_column_name VARCHAR ); INSERT INTO temp_foreign_key (constraint_name, table_name, column_name, ref_table_name, ref_column_name) SELECT fk.constraint_name, child.table_name, child.column_name, parent.table_name, parent.column_name FROM information_schema.referential_constraints fk JOIN information_schema.key_column_usage AS child ON fk.constraint_name = child.constraint_name JOIN information_schema.key_column_usage AS parent ON fk.unique_constraint_name = parent.constraint_name WHERE fk.constraint_schema = 'public' AND child.position_in_unique_constraint = parent.ordinal_position AND fk.delete_rule = 'NO ACTION'; -- Step 2.2 Create a function the replace foreign keys with new on DELETE option CREATE OR REPLACE FUNCTION replace_foreign_key (new_option VARCHAR) RETURNS void AS $BODY$ DECLARE fks CURSOR FOR SELECT * FROM temp_foreign_key; BEGIN FOR rec IN fks LOOP EXECUTE 'alter table "' || rec.table_name || '" ' || 'drop constraint "' || rec.constraint_name || '" ,' || 'add constraint "' || rec.constraint_name || '" FOREIGN KEY ("' || rec.column_name || '") REFERENCES "' || rec.ref_table_name || '" ("' || rec.ref_column_name || '") ' || new_option || ';' ; END LOOP; END; $BODY$ LANGUAGE plpgsql; -- Step 2.3 execute the function to replace all foreign keys with CASCADE on Delete SELECT * FROM replace_foreign_key('on delete cascade'); /* Step 3. Delete nodes ############################################################################################################################## */ DELETE FROM "ServerNodeConfigurations" WHERE "ID" IN (SELECT id FROM nodes_to_delete()); /* Step 4. Resume foreign keys to No Action on Delete ############################################################################################################################## */ SELECT * FROM replace_foreign_key(''); /* Step 5. Drop temp objects ############################################################################################################################## */ DROP FUNCTION nodes_to_delete(); DROP FUNCTION replace_foreign_key(new_option varchar); DROP TABLE temp_foreign_key;