Script to prepare DB profiles schema for remigration

Create backup of <tenantId>_mod_di_converter_storage
OPTIONAL | To be able to check the migration results in the future, execute:
set SEARCH_PATH = '<tenantId>_mod_di_converter_storage'; create table snapshots_old as select job_profile_id, s.get_profile_snapshot ->> 'association_id' as association_id, s.get_profile_snapshot as snapshot from (select jp.id job_profile_id, get_profile_snapshot(jp.id, 'JOB_PROFILE', 'job_profiles', jp.id::TEXT) from job_profiles jp) s;
Execute the following script. This script will remove data from the previous migration since the mod-di-converter-storage will start migration only when the “profile_wrappers” table is empty.
set SEARCH_PATH = '<tenantId>_mod_di_converter_storage'; DO $$ DECLARE r record; BEGIN RAISE NOTICE '==='; RAISE NOTICE 'Preparing mod_di_converter_storage for migration.'; DROP TABLE IF EXISTS foreign_keys; RAISE NOTICE 'Find FKeys for deletion.'; CREATE TEMP TABLE foreign_keys AS SELECT conrelid :: regclass AS table_name, conname AS foreign_key FROM pg_constraint WHERE connamespace = (SELECT current_setting('SEARCH_PATH')) :: regnamespace AND contype = 'f' AND conrelid :: regclass :: text like '%_to_%' ORDER BY conrelid :: regclass :: text, contype desc; RAISE NOTICE 'Removing FKeys:'; FOR r in SELECT table_name, foreign_key FROM foreign_keys LOOP RAISE NOTICE ' Delete FKey:: % : %', r.table_name, r.foreign_key; EXECUTE 'ALTER TABLE ' || r.table_name || ' DROP CONSTRAINT ' || r.foreign_key || ';'; END LOOP; RAISE NOTICE 'FKeys were removed.'; RAISE NOTICE 'Disable triggers.'; SET session_replication_role = replica; RAISE NOTICE 'Removing old associations.'; FOR r in SELECT distinct table_name FROM foreign_keys LOOP EXECUTE 'UPDATE ' || r.table_name || ' SET masterwrapperid=null, detailwrapperid=null;'; END LOOP; RAISE NOTICE 'Removing old wrappers.'; TRUNCATE profile_wrappers; RAISE NOTICE 'Enabling triggers back.'; SET session_replication_role = DEFAULT; RAISE NOTICE 'Creating FKeys back:'; FOR r in SELECT table_name, foreign_key FROM foreign_keys LOOP RAISE NOTICE ' Create FKey:: % : %', r.table_name, r.foreign_key; EXECUTE 'ALTER TABLE ' || r.table_name || ' ADD CONSTRAINT ' || r.foreign_key || ' FOREIGN KEY (' || left(r.foreign_key, strpos(r.foreign_key, '_') -1)|| ') REFERENCES profile_wrappers(id) ON DELETE CASCADE;'; END LOOP; DROP TABLE IF EXISTS foreign_keys; RAISE NOTICE 'DB ready for migration.'; END $$;
Execute the module update procedure.
OPTIONAL | To check the results of the migration:
set SEARCH_PATH = '<tenantId>_mod_di_converter_storage'; create table snapshots_new as select job_profile_id, s.get_profile_snapshot ->> 'association_id' as association_id, s.get_profile_snapshot as snapshot from (select jp.id job_profile_id, get_profile_snapshot(jp.id, 'JOB_PROFILE', 'job_profiles', jp.id::TEXT) from job_profiles jp) s;
set SEARCH_PATH = '<tenantId>_mod_di_converter_storage'; select o.job_profile_id, o.association_id, (o."snapshot" ->> 'detail_type') detail_type, (o."snapshot"::json#>>'{detail,0}')::jsonb ->> 'name' as detail_name, jp.jsonb ->> 'name' from snapshots_old o, job_profiles jp where o.job_profile_id = jp.id EXCEPT select n.job_profile_id, n.association_id, (n."snapshot" ->> 'detail_type') detail_type, (n."snapshot"::json#>>'{detail,0}')::jsonb ->> 'name' as detail_name, jp.jsonb ->> 'name' from snapshots_new n, job_profiles jp where n.job_profile_id = jp.id order by 1;
The query above should not return any results if the migration was successful.
Delete the service tables
set SEARCH_PATH = '<tenantId>_mod_di_converter_storage'; DROP TABLE IF EXISTS snapshots_old; DROP TABLE IF EXISTS snapshots_new;

The following scripts may help if something goes wrong

Script for creating FKeys
set SEARCH_PATH = '<tenantId>_mod_di_converter_storage'; ALTER TABLE action_to_action_profiles ADD CONSTRAINT masterwrapperid_profile_wrappers_fkey FOREIGN KEY (masterwrapperid) REFERENCES profile_wrappers(id) ON DELETE CASCADE; ALTER TABLE action_to_action_profiles ADD CONSTRAINT detailwrapperid_profile_wrappers_fkey FOREIGN KEY (detailwrapperid) REFERENCES profile_wrappers(id) ON DELETE CASCADE; ALTER TABLE action_to_mapping_profiles ADD CONSTRAINT masterwrapperid_profile_wrappers_fkey FOREIGN KEY (masterwrapperid) REFERENCES profile_wrappers(id) ON DELETE CASCADE; ALTER TABLE action_to_mapping_profiles ADD CONSTRAINT detailwrapperid_profile_wrappers_fkey FOREIGN KEY (detailwrapperid) REFERENCES profile_wrappers(id) ON DELETE CASCADE; ALTER TABLE action_to_match_profiles ADD CONSTRAINT masterwrapperid_profile_wrappers_fkey FOREIGN KEY (masterwrapperid) REFERENCES profile_wrappers(id) ON DELETE CASCADE; ALTER TABLE action_to_match_profiles ADD CONSTRAINT detailwrapperid_profile_wrappers_fkey FOREIGN KEY (detailwrapperid) REFERENCES profile_wrappers(id) ON DELETE CASCADE; ALTER TABLE job_to_action_profiles ADD CONSTRAINT masterwrapperid_profile_wrappers_fkey FOREIGN KEY (masterwrapperid) REFERENCES profile_wrappers(id) ON DELETE CASCADE; ALTER TABLE job_to_action_profiles ADD CONSTRAINT detailwrapperid_profile_wrappers_fkey FOREIGN KEY (detailwrapperid) REFERENCES profile_wrappers(id) ON DELETE CASCADE; ALTER TABLE job_to_match_profiles ADD CONSTRAINT masterwrapperid_profile_wrappers_fkey FOREIGN KEY (masterwrapperid) REFERENCES profile_wrappers(id) ON DELETE CASCADE; ALTER TABLE job_to_match_profiles ADD CONSTRAINT detailwrapperid_profile_wrappers_fkey FOREIGN KEY (detailwrapperid) REFERENCES profile_wrappers(id) ON DELETE CASCADE; ALTER TABLE match_to_action_profiles ADD CONSTRAINT masterwrapperid_profile_wrappers_fkey FOREIGN KEY (masterwrapperid) REFERENCES profile_wrappers(id) ON DELETE CASCADE; ALTER TABLE match_to_action_profiles ADD CONSTRAINT detailwrapperid_profile_wrappers_fkey FOREIGN KEY (detailwrapperid) REFERENCES profile_wrappers(id) ON DELETE CASCADE; ALTER TABLE match_to_match_profiles ADD CONSTRAINT masterwrapperid_profile_wrappers_fkey FOREIGN KEY (masterwrapperid) REFERENCES profile_wrappers(id) ON DELETE CASCADE; ALTER TABLE match_to_match_profiles ADD CONSTRAINT detailwrapperid_profile_wrappers_fkey FOREIGN KEY (detailwrapperid) REFERENCES profile_wrappers(id) ON DELETE CASCADE;
Script for removing FKeys
set SEARCH_PATH = '<tenantId>_mod_di_converter_storage'; ALTER TABLE action_to_action_profiles DROP CONSTRAINT masterwrapperid_profile_wrappers_fkey; ALTER TABLE action_to_action_profiles DROP CONSTRAINT detailwrapperid_profile_wrappers_fkey; ALTER TABLE action_to_mapping_profiles DROP CONSTRAINT masterwrapperid_profile_wrappers_fkey; ALTER TABLE action_to_mapping_profiles DROP CONSTRAINT detailwrapperid_profile_wrappers_fkey; ALTER TABLE action_to_match_profiles DROP CONSTRAINT masterwrapperid_profile_wrappers_fkey; ALTER TABLE action_to_match_profiles DROP CONSTRAINT detailwrapperid_profile_wrappers_fkey; ALTER TABLE job_to_action_profiles DROP CONSTRAINT masterwrapperid_profile_wrappers_fkey; ALTER TABLE job_to_action_profiles DROP CONSTRAINT detailwrapperid_profile_wrappers_fkey; ALTER TABLE job_to_match_profiles DROP CONSTRAINT masterwrapperid_profile_wrappers_fkey; ALTER TABLE job_to_match_profiles DROP CONSTRAINT detailwrapperid_profile_wrappers_fkey; ALTER TABLE match_to_action_profiles DROP CONSTRAINT masterwrapperid_profile_wrappers_fkey; ALTER TABLE match_to_action_profiles DROP CONSTRAINT detailwrapperid_profile_wrappers_fkey; ALTER TABLE match_to_match_profiles DROP CONSTRAINT masterwrapperid_profile_wrappers_fkey; ALTER TABLE match_to_match_profiles DROP CONSTRAINT detailwrapperid_profile_wrappers_fkey;
Script for cleaning links to old wrapperIds and old wrappers:
set SEARCH_PATH = '<tenantId>_mod_di_converter_storage'; SET session_replication_role = replica; update action_to_action_profiles set masterwrapperid=null, detailwrapperid=null; update action_to_mapping_profiles set masterwrapperid=null, detailwrapperid=null; update action_to_match_profiles set masterwrapperid=null, detailwrapperid=null; update job_to_action_profiles set masterwrapperid=null, detailwrapperid=null; update job_to_match_profiles set masterwrapperid=null, detailwrapperid=null; update match_to_action_profiles set masterwrapperid=null, detailwrapperid=null; update match_to_match_profiles set masterwrapperid=null, detailwrapperid=null; TRUNCATE profile_wrappers; SET session_replication_role = DEFAULT;