Versions Compared

Version Old Version 5 New Version Current
Changes made by

Aliaksandr Fedasiuk

Olamide Kolawole

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

  1. Create backup of <tenantId>_mod_di_converter_storage

  2. OPTIONAL | To be able to check the migration results in the future, execute:

    Code Block
    languagesql
    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;

  3. Execute the following script. This script will remove data from the previous migration since the module mod-di-converter-storage will start migration only when the “profile_wrappers” table is empty.

    Code Block
    languagesql
    set SEARCH_PATH = '<tenantId>_mod_di_converter_storage'; 
DO $$ 
DECLARE 
	r record;
BEGIN 
	RAISE NOTICE '===';
	RAISE NOTICE 'Preparing mod_di_converter_storage tofor migration.';
	DROP TABLE IF EXISTS foreign_keys;
	
	RAISE NOTICE 'Find FKeys for delitiondeletion.';
	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 'DiableDisable triggers.';
	SET session_replication_role = replica;
	
	RAISE NOTICE 'RemovindRemoving 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 $$;

  4. Execute the module update procedure.

  5. OPTIONAL | To check the results of the migration:

    Code Block
    languagesql
    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;
    Code Block
    languagesql
    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.

  6. Delete the service tables

    Code Block
    languagesql
    set SEARCH_PATH = '<tenantId>_mod_di_converter_storage';
DROP TABLE IF EXISTS snapshots_old;
DROP TABLE IF EXISTS snapshots_new;

...