metafresh logo and link to homepage

AD_Migrationscript helper functions

Insert a manual entry into AD_Migrationscript table

select migrationscript_ignore('<foldername>/<filename.sql>');

This inserts the given script into the AD_MigrationScript table, so it will be ignored on future rollouts.

Example:

select migrationscript_ignore('configuration/changingpartnerwindow.sql');

Create additional entries with different project name

select migrationscript_ignoreNewProjectName('oldProjectName', 'newProjectName');

For all AD_MigrationScript records with the given oldProjectName, this function inserts additional AD_MigrationScript records that are similar, but have newProjectName in their name and projectname columns.

Example:

select migrationscript_ignoreNewProjectName('projectnameWithTypo', 'fixedName')

If your version is older than 5.78 …

…then you first need to create the functions:

DDL for the DB-function migrationscript_ignore

DROP FUNCTION IF EXISTS public.migrationscript_ignore(character varying);
CREATE FUNCTION public.migrationscript_ignore(projectAndFileName character varying)
  RETURNS void AS
$BODY$
 INSERT INTO public.AD_MigrationScript (
	ad_client_id, ad_migrationscript_id, ad_org_id, created, createdby, description, developername, 
	isactive, name, 
	projectname, reference, releaseno, scriptroll, status, url, updated, updatedby, isapply, filename, script
 ) VALUES (
	0, nextval('ad_migrationscript_seq') , 0, now(), 100, 'Inserted by migrationscript_ignore with parameter projectAndFileName='||projectAndFileName, NULL, 
	'Y', replace (projectAndFileName,'/','->'),
    left (projectAndFileName, position('/' in projectAndFileName )-1 ), NULL, '1' , NULL, 'CO', NULL, now(), 100, 'N', right (projectAndFileName, length(projectAndFileName)-position('/' in projectAndFileName)), NULL);
$BODY$
  LANGUAGE sql VOLATILE
  COST 100;
COMMENT ON FUNCTION public.migrationscript_ignore(character varying) IS 'Inserts the given script into the AD_MigrationScript table, so it will be ignored on future rollouts.
usage example:
select migrationscript_ignore(''configuration/changingpartnerwindow.sql'');

Please keep in sync with http://docs.metasfresh.org/sql_collection/migrationscript_helper_functions.html
';

DDL for the DB-function migrationscript_ignoreNewProjectName


DROP FUNCTION IF EXISTS public.migrationscript_ignoreNewProjectName(character varying, character varying);
CREATE OR REPLACE FUNCTION public.migrationscript_ignoreNewProjectName(
	oldProjectName character varying, 
	newProjectName character varying)
  RETURNS void AS
$BODY$
 INSERT INTO public.AD_MigrationScript (
	ad_client_id, ad_migrationscript_id, ad_org_id, created, createdby, 
	description, 
	developername, 
	isactive, 
	name, 
	projectname, 
	reference, releaseno, scriptroll, status, url, updated, updatedby, isapply, 
	filename, 
	script
 )
 SELECT
	0, nextval('ad_migrationscript_seq') , 0, now(), 100, 
	'Inserted by migrationscript_ignoreChangedProject with parameters oldProjectName='||oldProjectName||'; newProjectName='||newProjectName,  -- description
	NULL, 
	'Y', 
	regexp_replace(name, '^'||oldProjectName||'->', newProjectName||'->'), -- Name
    newProjectName, 
	reference, releaseno, scriptroll, status, url, updated, updatedby, isapply, 
	fileName, -- filename
	NULL
 FROM public.ad_migrationscript
 WHERE projectname=oldProjectName
 ON CONFLICT DO NOTHING
 ;
$BODY$
  LANGUAGE sql VOLATILE
  COST 100;
  COMMENT ON FUNCTION public.migrationscript_ignoreNewProjectName(character varying, character varying) IS 'For all AD_MigrationScript records with the given oldProjectName, 
this function inserts additional AD_MigrationScript records that are similar, but have newProjectName in their name and projectname columns.

usage example:
select migrationscript_ignoreNewProjectName(''projectnameWithTypo'', ''fixedName'');

Note that it only inserts if no AD_MigrationScript with the respective new name already exists.

Please keep in sync with http://docs.metasfresh.org/sql_collection/migrationscript_helper_functions.html
';

Zur Quelldatei auf github.com