-- *********************************************************************
-- Update Database Script
-- *********************************************************************
-- Change Log: /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/upgrade/upgrade.changelog-up-to-1.8.0.xml
-- Ran at: 03/04/20 12:06
-- Against: SA@jdbc:h2:/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/incremental-upgrade-database/squash-tm
-- Liquibase version: 2.0.5
-- *********************************************************************

-- Lock Database
-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0.xml::tm-1.8.0::mpagnon::(Checksum: 3:d36dd82b1e8f15f61603318dd9657371)
-- Update TM database version number
UPDATE CORE_CONFIG SET VALUE = '1.8.0' WHERE STR_KEY = 'squashtest.tm.database.version';

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'Update TM database version number', NOW(), 'Update Data', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0.xml', 'tm-1.8.0', '2.0.5', '3:d36dd82b1e8f15f61603318dd9657371', 247);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0.xml::tm-1.8.0.feat-2688::mpagnon::(Checksum: 3:64dc16326c294f57452163ce0b900d51)
-- add permission READ_UNASSIGNED on Campaign library for all users but TestRunners
insert into ACL_GROUP_PERMISSION (ACL_GROUP_ID, PERMISSION_MASK, CLASS_ID)
      select ACL_GROUP.ID, 4096, ACL_CLASS.ID
      from ACL_GROUP , ACL_CLASS
      where ACL_GROUP.QUALIFIED_NAME != 'squashtest.acl.group.tm.TestRunner'
      and ACL_CLASS.CLASSNAME = 'org.squashtest.tm.domain.campaign.CampaignLibrary';

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'add permission READ_UNASSIGNED on Campaign library for all users but TestRunners', NOW(), 'Custom SQL', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0.xml', 'tm-1.8.0.feat-2688', '2.0.5', '3:64dc16326c294f57452163ce0b900d51', 248);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0.xml::tm-1.8.0.issue-1846-02::mpagnon::(Checksum: 3:5bc6788443ecb4a0235d2cace5356547)
-- Remove the DELETED_ON columns
ALTER TABLE CAMPAIGN_LIBRARY_NODE DROP COLUMN DELETED_ON;

ALTER TABLE TEST_CASE_LIBRARY_NODE DROP COLUMN DELETED_ON;

ALTER TABLE REQUIREMENT_LIBRARY_NODE DROP COLUMN DELETED_ON;

ALTER TABLE ITERATION DROP COLUMN DELETED_ON;

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'Remove the DELETED_ON columns', NOW(), 'Drop Column (x4)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0.xml', 'tm-1.8.0.issue-1846-02', '2.0.5', '3:5bc6788443ecb4a0235d2cace5356547', 249);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0.xml::tm-1.8.0.issue-3009-01::bsiri::(Checksum: 3:534184792528cc289f946a0d1b0a08e3)
-- As of 1.8.0 anyone that executes a test becomes de facto its assignee. This changeset will update the iteration 
-- 			test plan items and assign it to the last executor (if any) if it hasn't an assignee already
update ITERATION_TEST_PLAN_ITEM itpi
      set USER_ID = (select cu.PARTY_ID from CORE_USER cu where cu.LOGIN = itpi.LAST_EXECUTED_BY)
      where itpi.USER_ID IS NULL;

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'As of 1.8.0 anyone that executes a test becomes de facto its assignee. This changeset will update the iteration 
			test plan items and assign it to the last executor (if any) if it hasn''t an assignee already', NOW(), 'Custom SQL', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0.xml', 'tm-1.8.0.issue-3009-01', '2.0.5', '3:534184792528cc289f946a0d1b0a08e3', 250);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0.xml::core-1.8.0-feat-2699-00::bsiri::(Checksum: 3:c310a0691836587afa1c22c6e3b2a1a8)
-- actually remove user accounts that were deactivated
delete from CORE_TEAM_MEMBER
      where USER_ID in (
      select PARTY_ID from CORE_USER
      where ACTIVE= false
      );

delete from CORE_GROUP_MEMBER
      where PARTY_ID in (
      select PARTY_ID from CORE_USER
      where ACTIVE= false
      );

update ITERATION_TEST_PLAN_ITEM SET USER_ID = NULL WHERE USER_ID IN (SELECT PARTY_ID FROM CORE_USER WHERE active = false );

update CAMPAIGN_TEST_PLAN_ITEM SET USER_ID = NULL WHERE USER_ID IN (SELECT PARTY_ID FROM CORE_USER WHERE active =
      false );

delete from CORE_USER where active = false;

delete from CORE_PARTY where PARTY_ID not in
      (select PARTY_ID from CORE_USER)
      and PARTY_ID not in
      (select PARTY_ID from CORE_TEAM);

delete from AUTH_USER where active= false;

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'actually remove user accounts that were deactivated', NOW(), 'Custom SQL', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0.xml', 'core-1.8.0-feat-2699-00', '2.0.5', '3:c310a0691836587afa1c22c6e3b2a1a8', 251);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0.xml::core-1.8.0-feat-2699-01::bsiri::(Checksum: 3:42632085412cb18c068eb9b645f4b950)
-- the CORE_GROUP squashtest.tm.group.User is now renamed to the normalized squashtest.authz.group.tm.User
UPDATE CORE_GROUP SET QUALIFIED_NAME = 'squashtest.authz.group.tm.User' WHERE ID = 2;

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'the CORE_GROUP squashtest.tm.group.User is now renamed to the normalized squashtest.authz.group.tm.User', NOW(), 'Update Data', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0.xml', 'core-1.8.0-feat-2699-01', '2.0.5', '3:42632085412cb18c068eb9b645f4b950', 252);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0.xml::core-1.8.0-feat-2699-02::bsiri::(Checksum: 3:b836ac219073c8957e573d6c6604c6fe)
-- the CORE_GROUP squashtest.authz.tm.group.ProjectManager is deleted and the users belonging to that group are now 
-- 		demoted to squashtest.authz.group.tm.User
UPDATE CORE_GROUP_MEMBER SET GROUP_ID = '2' WHERE GROUP_ID = 3;

DELETE FROM CORE_GROUP_AUTHORITY  WHERE GROUP_ID = 3;

DELETE FROM CORE_GROUP  WHERE ID = 3;

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'the CORE_GROUP squashtest.authz.tm.group.ProjectManager is deleted and the users belonging to that group are now 
		demoted to squashtest.authz.group.tm.User', NOW(), 'Update Data, Delete Data (x2)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0.xml', 'core-1.8.0-feat-2699-02', '2.0.5', '3:b836ac219073c8957e573d6c6604c6fe', 253);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0.xml::core-1.8.0-feat-2699-03::bsiri::(Checksum: 3:d063dbd2d9a2665bfe67a7eec60b6144)
-- The table CORE_PARY_AUTHORITY stores personal authorities. As for TM 1.8.0 the users and teams now have blanket permissions 
-- 			(aka authorities) for themselves, not only via core_group membership. However, a user will still belong to one of the CORE_GROUPs and 
-- 			the personal authorities will stack with the authorities of the group they belong to.
CREATE TABLE CORE_PARTY_AUTHORITY (PARTY_ID BIGINT NOT NULL, AUTHORITY VARCHAR(50) NOT NULL, CONSTRAINT FK_PARTY_AUTHORITY_PARTY FOREIGN KEY (PARTY_ID) REFERENCES CORE_PARTY(PARTY_ID));

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'The table CORE_PARY_AUTHORITY stores personal authorities. As for TM 1.8.0 the users and teams now have blanket permissions 
			(aka authorities) for themselves, not only via core_group membership. However, a user will still belong to one of the CORE_G...', NOW(), 'Create Table', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0.xml', 'core-1.8.0-feat-2699-03', '2.0.5', '3:d063dbd2d9a2665bfe67a7eec60b6144', 254);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0.xml::core-1.8.0-feat-2699-04::bsiri::(Checksum: 3:0d0108ea67847977c9497d64786bffe4)
-- we have now to set the role ROLE_TM_PROJECT_MANAGER to the corresponding users
insert into CORE_PARTY_AUTHORITY(PARTY_ID, AUTHORITY)
      select distinct cu.PARTY_ID, 'ROLE_TM_PROJECT_MANAGER' from CORE_USER cu
      where cu.PARTY_ID in (
      select cu1.PARTY_ID from CORE_USER cu1
      inner join ACL_RESPONSIBILITY_SCOPE_ENTRY arse1 on arse1.PARTY_ID = cu1.PARTY_ID
      where arse1.ACL_GROUP_ID = 5
      )
      or cu.PARTY_ID in (
      select cu2.PARTY_ID from CORE_USER cu2
      inner join CORE_TEAM_MEMBER ctm on ctm.USER_ID = cu2.PARTY_ID
      inner join ACL_RESPONSIBILITY_SCOPE_ENTRY arse2 on arse2.PARTY_ID = ctm.TEAM_ID
      where arse2.ACL_GROUP_ID = 5
      );

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'we have now to set the role ROLE_TM_PROJECT_MANAGER to the corresponding users', NOW(), 'Custom SQL', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0.xml', 'core-1.8.0-feat-2699-04', '2.0.5', '3:0d0108ea67847977c9497d64786bffe4', 255);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0.xml::tm-1.8.0.issue-2899-1::bsiri::(Checksum: 3:c08bdd96581f7333744b200024cb7bd1)
-- Clean up the database from dead entities that slipped through the deletion routines (mysql, postgresql)
-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0.xml::tm-1.8.0.issue-2899-2::bsiri::(Checksum: 3:b367799cc274a2162f41b386eec0131b)
-- Clean up the database from dead entities that slipped through the deletion routines (h2). Note :
-- 			There is no way to perform multi table delete so we must delete the tables one by one. 
-- 			
-- 			Since h2 doesn't support multi table delete we must delete the entries one table after the other
-- 			and this will cost quite a bit.
SET REFERENTIAL_INTEGRITY FALSE;

BEGIN TRANSACTION;

delete from RESOURCE
      where RES_ID not in(
      select RES_ID from REQUIREMENT_FOLDER UNION
      select RES_ID from REQUIREMENT_VERSION
      );

delete from SIMPLE_RESOURCE
      where RES_ID not in(
      select RES_ID from RESOURCE
      );

delete from ATTACHMENT_LIST
      where ATTACHMENT_LIST_ID not in (
      select ATTACHMENT_LIST_ID from ACTION_TEST_STEP UNION
      select ATTACHMENT_LIST_ID from CAMPAIGN_LIBRARY UNION
      select ATTACHMENT_LIST_ID from CAMPAIGN_LIBRARY_NODE UNION
      select ATTACHMENT_LIST_ID from EXECUTION UNION
      select ATTACHMENT_LIST_ID from EXECUTION_STEP UNION
      select ATTACHMENT_LIST_ID from ITERATION UNION
      select ATTACHMENT_LIST_ID from PROJECT UNION
      select ATTACHMENT_LIST_ID from REQUIREMENT_LIBRARY UNION
      select ATTACHMENT_LIST_ID from RESOURCE UNION
      select ATTACHMENT_LIST_ID from TEST_CASE_LIBRARY UNION
      select ATTACHMENT_LIST_ID from TEST_CASE_LIBRARY_NODE UNION
      select ATTACHMENT_LIST_ID from TEST_SUITE
      );

delete from ATTACHMENT
      where ATTACHMENT_LIST_ID not in (
      select ATTACHMENT_LIST_ID from ATTACHMENT_LIST
      );

delete from ATTACHMENT_CONTENT
      where ATTACHMENT_CONTENT_ID not in (
      select CONTENT_ID from ATTACHMENT
      );

COMMIT;

SET REFERENTIAL_INTEGRITY TRUE;

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'Clean up the database from dead entities that slipped through the deletion routines (h2). Note :
			There is no way to perform multi table delete so we must delete the tables one by one. 
			
			Since h2 doesn''t support multi table delete we must delet...', NOW(), 'Custom SQL', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0.xml', 'tm-1.8.0.issue-2899-2', '2.0.5', '3:b367799cc274a2162f41b386eec0131b', 256);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0.xml::tm-1.8.0.issue-2899-2-postgresql::mpagnon::(Checksum: 3:96d81e788ecc03b8906c7533dc52ebbf)
-- Clean up the database from dead entities that slipped through the deletion routines (postgresql).
-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-1-init.xml::tm-1.8.0.issue-2933-init-01::mpagnon::(Checksum: 3:5f5f93e2109620791d01dff6c9dee582)
-- create table to store if table has orphan to delete
CREATE TABLE ENTITIES_TO_DELETE (NAME VARCHAR(30), ID BIGINT);

CREATE TABLE IS_TO_DELETE (NAME VARCHAR(30) NOT NULL, TOTAL INT, CONSTRAINT PK_IS_TO_DELETE PRIMARY KEY (NAME));

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'create table to store if table has orphan to delete', NOW(), 'Create Table (x2)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-1-init.xml', 'tm-1.8.0.issue-2933-init-01', '2.0.5', '3:5f5f93e2109620791d01dff6c9dee582', 257);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-2-requirements.xml::tm-1.8.0.issue-2933-req-01::mpagnon::(Checksum: 3:e8ab015587cb566a91588a461710d125)
-- store info if exist requirement nodes to delete
insert into ENTITIES_TO_DELETE
			(NAME, ID)
			select 'RLN', rln.RLN_ID
			from REQUIREMENT_LIBRARY_NODE rln
			where not exists (
			select 1 from RLN_RELATIONSHIP  where RLN_RELATIONSHIP.DESCENDANT_ID = rln.RLN_ID UNION
			select 1 from REQUIREMENT_LIBRARY_CONTENT where REQUIREMENT_LIBRARY_CONTENT.CONTENT_ID = rln.RLN_ID 
			);

insert into IS_TO_DELETE
			(NAME, TOTAL)
			select 'RLN', count(1)
			from
			ENTITIES_TO_DELETE where NAME = 'RLN';

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'store info if exist requirement nodes to delete', NOW(), 'Custom SQL (x2)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-2-requirements.xml', 'tm-1.8.0.issue-2933-req-01', '2.0.5', '3:e8ab015587cb566a91588a461710d125', 258);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-2-requirements.xml::tm-1.8.0.issue-2933-req-02::mpagnon::(Checksum: 3:0d6e34003b6593e42f2a0ff76dfd6583)
-- save if requirement_version_coverage to delete
insert into ENTITIES_TO_DELETE
			(NAME, ID)
			select distinct 'RLN',
			rlnrc.DESCENDANT_ID
			from RLN_RELATIONSHIP_CLOSURE rlnrc,
			ENTITIES_TO_DELETE etd
			where etd.NAME = 'RLN'
			and etd.ID =
			rlnrc.ANCESTOR_ID;

insert into ENTITIES_TO_DELETE
			(NAME, ID)
			select 'RESOURCE',
			rv.RES_ID
			from REQUIREMENT_VERSION rv , ENTITIES_TO_DELETE etd
			where
			etd.ID = rv.REQUIREMENT_ID
			and etd.NAME = 'RLN';

insert into ENTITIES_TO_DELETE
			(NAME, ID)
			select 'RESOURCE', rf.RES_ID
			from REQUIREMENT_FOLDER rf, ENTITIES_TO_DELETE etd
			where etd.ID = rf.RLN_ID
			and etd.NAME = 'RLN';

insert into ENTITIES_TO_DELETE
			(NAME, ID)
			select
			'REQUIREMENT_VERSION_COVERAGE', rvc.REQUIREMENT_VERSION_COVERAGE_ID
			from REQUIREMENT_VERSION_COVERAGE rvc, ENTITIES_TO_DELETE etd
			where
			rvc.VERIFIED_REQ_VERSION_ID = etd.ID
			and etd.NAME = 'RESOURCE';

insert into IS_TO_DELETE
			(NAME, TOTAL)
			select
			'REQUIREMENT_VERSION_COVERAGE', count(1)
			from ENTITIES_TO_DELETE where
			NAME = 'REQUIREMENT_VERSION_COVERAGE';

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'save if requirement_version_coverage to delete', NOW(), 'Custom SQL (x4)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-2-requirements.xml', 'tm-1.8.0.issue-2933-req-02', '2.0.5', '3:0d6e34003b6593e42f2a0ff76dfd6583', 259);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-2-requirements.xml::tm-1.8.0.issue-2933-req-03::mpagnon::(Checksum: 3:02dc5c7fd977fc7953192327742f6a75)
-- delete requirement_version_coverages
delete from VERIFYING_STEPS
			where REQUIREMENT_VERSION_COVERAGE_ID
			in
			(
			select ID from ENTITIES_TO_DELETE
			where NAME =
			'REQUIREMENT_VERSION_COVERAGE'
			);

delete from REQUIREMENT_VERSION_COVERAGE
			where
			REQUIREMENT_VERSION_COVERAGE_ID in
			(
			select ID from ENTITIES_TO_DELETE
			where NAME =
			'REQUIREMENT_VERSION_COVERAGE'
			);

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'delete requirement_version_coverages', NOW(), 'Custom SQL (x2)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-2-requirements.xml', 'tm-1.8.0.issue-2933-req-03', '2.0.5', '3:02dc5c7fd977fc7953192327742f6a75', 260);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-2-requirements.xml::tm-1.8.0.issue-2933-req-04::mpagnon::(Checksum: 3:e9bacb50f47c2d1c9380b10beca358ee)
-- delete resources
insert into ENTITIES_TO_DELETE
			(NAME, ID)
			select
			'CUSTOM_FIELD_VALUE' , cfv.CFV_ID
			from CUSTOM_FIELD_VALUE cfv,
			ENTITIES_TO_DELETE etd
			where etd.ID = cfv.BOUND_ENTITY_ID
			and etd.NAME = 'RESOURCE'
			and cfv.BOUND_ENTITY_TYPE = 'REQUIREMENT_VERSION';

insert into ENTITIES_TO_DELETE
			(NAME, ID)
			select 'ATTACHMENT_LIST',
			r.ATTACHMENT_LIST_ID
			from RESOURCE r , ENTITIES_TO_DELETE etd
			where
			etd.ID = r.RES_ID
			and etd.NAME = 'RESOURCE';

insert into ENTITIES_TO_DELETE
			(NAME, ID)
			select
			'REQUIREMENT_AUDIT_EVENT', rae.EVENT_ID
			from REQUIREMENT_AUDIT_EVENT
			rae, ENTITIES_TO_DELETE etd
			where etd.NAME = 'RESOURCE'
			and etd.ID =
			rae.REQ_VERSION_ID;

delete from REQUIREMENT_LARGE_PROPERTY_CHANGE
			where EVENT_ID in
			(select ID from ENTITIES_TO_DELETE where NAME =
			'REQUIREMENT_AUDIT_EVENT');

delete from REQUIREMENT_CREATION
			where exists (select 1 from	ENTITIES_TO_DELETE 
			where NAME = 'REQUIREMENT_AUDIT_EVENT'
			and ENTITIES_TO_DELETE.ID  = REQUIREMENT_CREATION.EVENT_ID
			);

delete from REQUIREMENT_PROPERTY_CHANGE
			where exists (select 1 
			from ENTITIES_TO_DELETE where NAME = 'REQUIREMENT_AUDIT_EVENT' and REQUIREMENT_PROPERTY_CHANGE.EVENT_ID = ENTITIES_TO_DELETE.ID );

delete from REQUIREMENT_AUDIT_EVENT
			where exists (select 1
			from ENTITIES_TO_DELETE where NAME = 'REQUIREMENT_AUDIT_EVENT' and ID = REQUIREMENT_AUDIT_EVENT.EVENT_ID);

update REQUIREMENT 
			set CURRENT_VERSION_ID = null
			where exists  (select 1
			from ENTITIES_TO_DELETE where NAME = 'RLN' and ID = REQUIREMENT.RLN_ID);

delete from REQUIREMENT_VERSION
			where exists (select 1 from
			ENTITIES_TO_DELETE where NAME = 'RESOURCE' and ID = REQUIREMENT_VERSION.RES_ID);

delete from RLN_RELATIONSHIP
			where exists (select 1
			from
			ENTITIES_TO_DELETE where NAME = 'RLN' and ID = RLN_RELATIONSHIP.DESCENDANT_ID);

delete from RLN_RELATIONSHIP_CLOSURE
			where  exists
			(select 1 from
			ENTITIES_TO_DELETE where NAME = 'RLN' and RLN_RELATIONSHIP_CLOSURE.DESCENDANT_ID = ID);

delete from REQUIREMENT_FOLDER
			where  exists (select 1 from
			ENTITIES_TO_DELETE where NAME = 'RLN' and ID = REQUIREMENT_FOLDER.RLN_ID);

delete from REQUIREMENT
			where  exists (select 1 from
			ENTITIES_TO_DELETE where NAME = 'RLN' and ID = REQUIREMENT.RLN_ID);

delete from REQUIREMENT_LIBRARY_NODE
			where exists (select 1
			from
			ENTITIES_TO_DELETE where NAME = 'RLN' and ID = REQUIREMENT_LIBRARY_NODE.RLN_ID);

delete from SIMPLE_RESOURCE
			where exists (select 1 from
			ENTITIES_TO_DELETE where NAME = 'RESOURCE' and ID = SIMPLE_RESOURCE.RES_ID);

delete from RESOURCE
			where exists (select ID from
			ENTITIES_TO_DELETE where NAME =
			'RESOURCE' and RESOURCE.RES_ID = ID);

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'delete resources', NOW(), 'Custom SQL (x16)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-2-requirements.xml', 'tm-1.8.0.issue-2933-req-04', '2.0.5', '3:e9bacb50f47c2d1c9380b10beca358ee', 261);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-3-testcases.xml::tm-1.8.0.issue-2933-tc-01::mpagnon::(Checksum: 3:53f5e1419f4239b180415746d6048fd8)
-- store info if exist test case folders to delete
insert into ENTITIES_TO_DELETE
			(NAME, ID)
			select 'TEST_CASE_FOLDER', folder.TCLN_ID
			from TEST_CASE_FOLDER folder
			where not exists (
				select 1  from TCLN_RELATIONSHIP where DESCENDANT_ID = folder.TCLN_ID UNION
				select 1  from TEST_CASE_LIBRARY_CONTENT where CONTENT_ID = folder.TCLN_ID
			);

insert into IS_TO_DELETE
			(NAME, TOTAL)
			select 'TEST_CASE_FOLDER', count(1)
			from ENTITIES_TO_DELETE
			where NAME = 'TEST_CASE_FOLDER';

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'store info if exist test case folders to delete', NOW(), 'Custom SQL (x2)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-3-testcases.xml', 'tm-1.8.0.issue-2933-tc-01', '2.0.5', '3:53f5e1419f4239b180415746d6048fd8', 262);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-3-testcases.xml::tm-1.8.0.issue-2933-tc-02::mpagnon::(Checksum: 3:acc49e3bde10bb3a992febbef5c5b14a)
-- save ids of attachment_lists
insert into ENTITIES_TO_DELETE
			(NAME, ID)
			select distinct 'TEST_CASE_FOLDER', folder.TCLN_ID
			from TEST_CASE_FOLDER folder, TCLN_RELATIONSHIP_CLOSURE tclnrc, ENTITIES_TO_DELETE etd
			where etd.NAME = 'TEST_CASE_FOLDER'
			and etd.ID = tclnrc.ANCESTOR_ID
			and	tclnrc.DESCENDANT_ID = folder.TCLN_ID;

insert into ENTITIES_TO_DELETE
			(NAME, ID)
			select distinct 'TEST_CASE', testcase.TCLN_ID
			from TEST_CASE testcase
			where not exists (
				select 1  from TCLN_RELATIONSHIP where DESCENDANT_ID = testcase.TCLN_ID UNION
				select 1  from TEST_CASE_LIBRARY_CONTENT where CONTENT_ID = testcase.TCLN_ID
			);

insert into ENTITIES_TO_DELETE
			(NAME, ID)
			select distinct 'TEST_CASE', testcase.TCLN_ID
			from TEST_CASE testcase, TCLN_RELATIONSHIP_CLOSURE tclnrc, ENTITIES_TO_DELETE etd
			where etd.NAME = 'TEST_CASE_FOLDER'
			and etd.ID = tclnrc.ANCESTOR_ID
			and tclnrc.DESCENDANT_ID = testcase.TCLN_ID;

insert into IS_TO_DELETE
			(NAME, TOTAL)
			select 'TEST_CASE', count(1)
			from ENTITIES_TO_DELETE etd
			where etd.NAME = 'TEST_CASE';

insert into ENTITIES_TO_DELETE
			(NAME, ID)
			select 'ATTACHMENT_LIST', tcln.ATTACHMENT_LIST_ID
			from TEST_CASE_LIBRARY_NODE tcln , ENTITIES_TO_DELETE etd
			where tcln.TCLN_ID = etd.ID
			and etd.NAME in ('TEST_CASE_FOLDER', 'TEST_CASE');

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'save ids of attachment_lists', NOW(), 'Custom SQL (x4)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-3-testcases.xml', 'tm-1.8.0.issue-2933-tc-02', '2.0.5', '3:acc49e3bde10bb3a992febbef5c5b14a', 263);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-3-testcases.xml::tm-1.8.0.issue-2933-tc-03::mpagnon::(Checksum: 3:66e73ac42dd7c9216ae7ceaab4f7c3c0)
-- set tcln_id to null on iteration_test_plan_item
delete from ENTITIES_TO_DELETE
			where NAME = 'REQUIREMENT_VERSION_COVERAGE';

insert into ENTITIES_TO_DELETE
			(NAME, ID)
			select 'REQUIREMENT_VERSION_COVERAGE', rvc.REQUIREMENT_VERSION_COVERAGE_ID
			from REQUIREMENT_VERSION_COVERAGE rvc, ENTITIES_TO_DELETE etd
			where rvc.VERIFYING_TEST_CASE_ID = etd.ID
			and etd.NAME = 'TEST_CASE';

insert into ENTITIES_TO_DELETE
			(NAME, ID)
			select 'CUSTOM_FIELD_VALUE', cfv.CFV_ID
			from CUSTOM_FIELD_VALUE cfv, ENTITIES_TO_DELETE etd
			where etd.ID = cfv.BOUND_ENTITY_ID
			and cfv.BOUND_ENTITY_TYPE = 'TEST_CASE'
			and etd.NAME = 'TEST_CASE';

insert into ENTITIES_TO_DELETE
			(NAME, ID)
			select 'CTPI_CAMPAIGN' , ctpi.CAMPAIGN_ID
			from CAMPAIGN_TEST_PLAN_ITEM ctpi, ENTITIES_TO_DELETE etd
			where etd.ID = ctpi.TEST_CASE_ID
			and etd.NAME = 'TEST_CASE';

insert into ENTITIES_TO_DELETE
			(NAME, ID)
			select 'PARAMETER', param.PARAM_ID
			from PARAMETER param, ENTITIES_TO_DELETE etd
			where etd.ID = param.TEST_CASE_ID
			and etd.NAME = 'TEST_CASE';

insert into ENTITIES_TO_DELETE
			(NAME, ID)
			select 'DATASET', ds.DATASET_ID
			from DATASET ds, ENTITIES_TO_DELETE etd
			where etd.ID = ds.TEST_CASE_ID
			and etd.NAME = 'TEST_CASE';

insert into ENTITIES_TO_DELETE
			(NAME, ID)
			select 'AUTOMATED_TEST', tc.TA_TEST
			from TEST_CASE tc, ENTITIES_TO_DELETE etd
			where etd.ID = tc.TCLN_ID
			and etd.NAME = 'TEST_CASE'
			and tc.TA_TEST is not null;

insert into ENTITIES_TO_DELETE
			(NAME, ID)
			select 'TEST_STEP', tcs.STEP_ID
			from TEST_CASE_STEPS tcs, ENTITIES_TO_DELETE etd
			where etd.ID = tcs.TEST_CASE_ID
			and etd.NAME = 'TEST_CASE';

insert into ENTITIES_TO_DELETE
			(NAME, ID)
			select 'CALLING_STEP', cts.TEST_STEP_ID
			from CALL_TEST_STEP cts, ENTITIES_TO_DELETE etd
			where etd.ID = cts.CALLED_TEST_CASE_ID
			and etd.NAME = 'TEST_CASE';

insert into IS_TO_DELETE
			(NAME, TOTAL)
			select 'CALLING_STEP', count(1)
			from ENTITIES_TO_DELETE etd
			where etd.NAME = 'CALLING_STEP';

update EXECUTION
			set TCLN_ID = null
			where exists (select 1
			from ENTITIES_TO_DELETE where NAME = 'TEST_CASE' and ID = EXECUTION.TCLN_ID );

update ITERATION_TEST_PLAN_ITEM
			set TCLN_ID = null
			where exists (select ID from ENTITIES_TO_DELETE where NAME = 'TEST_CASE' and ID = ITERATION_TEST_PLAN_ITEM.TCLN_ID);

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'set tcln_id to null on iteration_test_plan_item', NOW(), 'Custom SQL (x12)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-3-testcases.xml', 'tm-1.8.0.issue-2933-tc-03', '2.0.5', '3:66e73ac42dd7c9216ae7ceaab4f7c3c0', 264);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-3-testcases.xml::tm-1.8.0.issue-2933-tc-04::mpagnon::(Checksum: 3:5a5f06de3f28534e9e445251c7a3f729)
-- delete verifying_steps
delete from VERIFYING_STEPS
			where  exists (
				select 1 from ENTITIES_TO_DELETE where NAME = 'REQUIREMENT_VERSION_COVERAGE'
				and REQUIREMENT_VERSION_COVERAGE_ID = ID
			);

delete from REQUIREMENT_VERSION_COVERAGE
			where  exists (
				select 1 from ENTITIES_TO_DELETE where NAME = 'REQUIREMENT_VERSION_COVERAGE'
				and REQUIREMENT_VERSION_COVERAGE.REQUIREMENT_VERSION_COVERAGE_ID = ID
			);

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'delete verifying_steps', NOW(), 'Custom SQL (x2)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-3-testcases.xml', 'tm-1.8.0.issue-2933-tc-04', '2.0.5', '3:5a5f06de3f28534e9e445251c7a3f729', 265);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-3-testcases.xml::tm-1.8.0.issue-2933-tc-05-mysql::mpagnon::(Checksum: 3:e199852226e7027637bb894a1be5bec1)
-- reorder test plan of campaign where deletion of campaign_test_plan_item occurred
-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-3-testcases.xml::tm-1.8.0.issue-2933-tc-05-h2::mpagnon::(Checksum: 3:1886e40bbe3b6e70e9a1335c8c176251)
-- reorder test plan of campaign where deletion of campaign_test_plan_item occurred
delete from CAMPAIGN_TEST_PLAN_ITEM
			where exists (select 1 from ENTITIES_TO_DELETE where NAME = 'TEST_CASE' and CAMPAIGN_TEST_PLAN_ITEM.TEST_CASE_ID = ID);

create table CAMPAIGN_TEST_PLAN_ITEM_TEMP
			(
			CAMPAIGN_ID
			BIGINT NOT NULL,
			CTPI_ID BIGINT NOT NULL,
			TEST_PLAN_ORDER INT NOT NULL
			);

set @rn=-1;

insert into CAMPAIGN_TEST_PLAN_ITEM_TEMP
			(CAMPAIGN_ID, CTPI_ID, TEST_PLAN_ORDER)
			select CAMPAIGN_ID, CTPI_ID, set(@rn, @rn+1) as globindex
			from CAMPAIGN_TEST_PLAN_ITEM
			where exists (select 1 from ENTITIES_TO_DELETE where NAME = 'CTPI_CAMPAIGN' and ID = CAMPAIGN_TEST_PLAN_ITEM.CAMPAIGN_ID)
			group by CAMPAIGN_ID, CTPI_ID, globindex
			order by TEST_PLAN_ORDER;

create table CAMPAIGN_TEST_PLAN_ITEM_TEMP2 
			(
			CAMPAIGN_ID
			BIGINT NOT NULL,
			CTPI_ID BIGINT NOT NULL,
			TEST_PLAN_ORDER INT NOT NULL
			);

insert into CAMPAIGN_TEST_PLAN_ITEM_TEMP2
			select * from CAMPAIGN_TEST_PLAN_ITEM_TEMP;

update CAMPAIGN_TEST_PLAN_ITEM ctpi
			set TEST_PLAN_ORDER = (
				select count( ctpit2.CTPI_ID )
				from CAMPAIGN_TEST_PLAN_ITEM_TEMP ctpit1,CAMPAIGN_TEST_PLAN_ITEM_TEMP2 ctpit2
				where ctpit1.CTPI_ID = ctpi.CTPI_ID
				and ctpit2.CAMPAIGN_ID = ctpit1.CAMPAIGN_ID
				and ctpit1.TEST_PLAN_ORDER > ctpit2.TEST_PLAN_ORDER
			)
			where ctpi.CTPI_ID in (select DISTINCT ctpi3.CTPI_ID from CAMPAIGN_TEST_PLAN_ITEM_TEMP ctpi3);

drop table CAMPAIGN_TEST_PLAN_ITEM_TEMP;

drop table CAMPAIGN_TEST_PLAN_ITEM_TEMP2;

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'reorder test plan of campaign where deletion of campaign_test_plan_item occurred', NOW(), 'Custom SQL (x2)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-3-testcases.xml', 'tm-1.8.0.issue-2933-tc-05-h2', '2.0.5', '3:1886e40bbe3b6e70e9a1335c8c176251', 266);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-3-testcases.xml::tm-1.8.0.issue-2933-tc-05-postgre::mpagnon::(Checksum: 3:62b50f1f2ee42ce3690a9bfbd995c249)
-- reorder test plan of campaign where deletion of campaign_test_plan_item occurred
-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-3-testcases.xml::tm-1.8.0.issue-2933-tc-06::mpagnon::(Checksum: 3:44cd15243248d8d68959cf6442294ed8)
-- delete dataset
update ITERATION_TEST_PLAN_ITEM 
			set DATASET_ID = null
			where exists (select 1 from ENTITIES_TO_DELETE where NAME ='DATASET' and ID = ITERATION_TEST_PLAN_ITEM.DATASET_ID);

delete from DATASET_PARAM_VALUE
			where exists (select 1 from ENTITIES_TO_DELETE where NAME = 'DATASET' and DATASET_PARAM_VALUE.DATASET_ID = ID);

delete from DATASET
			where exists (select 1 from ENTITIES_TO_DELETE where NAME = 'DATASET' and ID = DATASET.DATASET_ID);

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'delete dataset', NOW(), 'Custom SQL (x3)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-3-testcases.xml', 'tm-1.8.0.issue-2933-tc-06', '2.0.5', '3:44cd15243248d8d68959cf6442294ed8', 267);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-3-testcases.xml::tm-1.8.0.issue-2933-tc-07::mpagnon::(Checksum: 3:c96f4e07c33d9dedd49e9493bee7457a)
-- delete parameters
delete from DATASET_PARAM_VALUE
			where exists (select 1 from ENTITIES_TO_DELETE where NAME = 'PARAMETER' and ID = DATASET_PARAM_VALUE.PARAM_ID);

delete from PARAMETER
			where exists (select 1 from ENTITIES_TO_DELETE where NAME = 'PARAMETER' and PARAMETER.PARAM_ID = ID);

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'delete parameters', NOW(), 'Custom SQL (x2)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-3-testcases.xml', 'tm-1.8.0.issue-2933-tc-07', '2.0.5', '3:c96f4e07c33d9dedd49e9493bee7457a', 268);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-3-testcases.xml::tm-1.8.0.issue-2933-tc-08::mpagnon::(Checksum: 3:5d7b4b09ad19afc303f91f63c5f2e6e8)
-- delete automated_suite where not exist automated_execution_extender linked to it
delete from AUTOMATED_EXECUTION_EXTENDER
			where exists (select 1 from ENTITIES_TO_DELETE where NAME = 'AUTOMATED_TEST' and AUTOMATED_EXECUTION_EXTENDER.TEST_ID = ID);

delete from AUTOMATED_SUITE
			where not exists (select 1 from AUTOMATED_EXECUTION_EXTENDER where AUTOMATED_EXECUTION_EXTENDER.SUITE_ID = AUTOMATED_SUITE.SUITE_ID);

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'delete automated_suite where not exist automated_execution_extender linked to it', NOW(), 'Custom SQL (x2)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-3-testcases.xml', 'tm-1.8.0.issue-2933-tc-08', '2.0.5', '3:5d7b4b09ad19afc303f91f63c5f2e6e8', 269);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-3-testcases.xml::tm-1.8.0.issue-2933-tc-09::mpagnon::(Checksum: 3:23f2a28b59019cf35c1e1e613b86660b)
-- delete test step
insert into ENTITIES_TO_DELETE
			(NAME, ID)
			select 'CUSTOM_FIELD_VALUE', cfv.CFV_ID
			from CUSTOM_FIELD_VALUE cfv, ENTITIES_TO_DELETE etd
			where etd.ID = cfv.BOUND_ENTITY_ID
			and cfv.BOUND_ENTITY_TYPE = 'TEST_STEP'
			and etd.NAME = 'TEST_STEP';

update EXECUTION_STEP
			set TEST_STEP_ID = null
			where exists (select 1 from ENTITIES_TO_DELETE where NAME = 'TEST_STEP' and ID = EXECUTION_STEP.TEST_STEP_ID);

insert into ENTITIES_TO_DELETE
			(NAME, ID)
			select 'ATTACHMENT_LIST', ats.ATTACHMENT_LIST_ID
			from ACTION_TEST_STEP ats, ENTITIES_TO_DELETE etd
			where ats.TEST_STEP_ID = etd.ID
			and etd.NAME = 'TEST_STEP';

delete from TEST_CASE_STEPS
			where exists (select 1 from ENTITIES_TO_DELETE where NAME = 'TEST_CASE' and TEST_CASE_STEPS.TEST_CASE_ID = ID);

delete from CALL_TEST_STEP
			where exists (select 1 from ENTITIES_TO_DELETE where NAME = 'TEST_STEP' and CALL_TEST_STEP.TEST_STEP_ID = ID);

delete from ACTION_TEST_STEP
			where exists (select 1 from ENTITIES_TO_DELETE where NAME = 'TEST_STEP' and ACTION_TEST_STEP.TEST_STEP_ID = ID);

delete from TEST_STEP
			where exists (select 1 from ENTITIES_TO_DELETE where NAME = 'TEST_STEP' and TEST_STEP.TEST_STEP_ID = ID);

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'delete test step', NOW(), 'Custom SQL (x7)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-3-testcases.xml', 'tm-1.8.0.issue-2933-tc-09', '2.0.5', '3:23f2a28b59019cf35c1e1e613b86660b', 270);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-3-testcases.xml::tm-1.8.0.issue-2933-tc-10::mpagnon::(Checksum: 3:e1b0955efa6766bb6e68ce8eead210e6)
-- delete test_step and call_test_step
insert into ENTITIES_TO_DELETE
			(NAME, ID)
			select 'CALLING_TEST_CASE' , tcs.TEST_CASE_ID
			from TEST_CASE_STEPS tcs ,	ENTITIES_TO_DELETE etd
			where tcs.STEP_ID = etd.ID
			and etd.NAME =	'CALLING_STEP';

update EXECUTION_STEP
			set TEST_STEP_ID = null
			where TEST_STEP_ID in
			(select ID from ENTITIES_TO_DELETE where NAME = 'CALLING_STEP');

delete from CALL_TEST_STEP
			where exists (select 1 from ENTITIES_TO_DELETE where NAME = 'CALLING_STEP' and ID = CALL_TEST_STEP.TEST_STEP_ID);

delete from TEST_CASE_STEPS
			where exists (select 1 from ENTITIES_TO_DELETE where NAME = 'CALLING_STEP' and ID = TEST_CASE_STEPS.STEP_ID);

delete from TEST_STEP
			where exists (select 1 from ENTITIES_TO_DELETE where NAME = 'CALLING_STEP' and ID = TEST_STEP.TEST_STEP_ID);

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'delete test_step and call_test_step', NOW(), 'Custom SQL (x3)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-3-testcases.xml', 'tm-1.8.0.issue-2933-tc-10', '2.0.5', '3:e1b0955efa6766bb6e68ce8eead210e6', 271);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-3-testcases.xml::tm-1.8.0.issue-2933-tc-11-mysql::mpagnon::(Checksum: 3:c107a1841aa97998cb407493f3185cb4)
-- reorder steps of calling test case
-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-3-testcases.xml::tm-1.8.0.issue-2933-tc-11-h2::mpagnon::(Checksum: 3:1c1ec977d2a0275b1e3eddf2a9284572)
-- reorder steps of calling test case
create table TEST_CASE_STEPS_TEMP(
			TEST_CASE_ID BIGINT NOT NULL,
			STEP_ID BIGINT NOT NULL,
			STEP_ORDER INT NOT NULL
			);

set @rn=-1;

insert into TEST_CASE_STEPS_TEMP
			(TEST_CASE_ID, STEP_ID, STEP_ORDER)
			select TEST_CASE_ID, STEP_ID, set(@rn, @rn+1) as globindex
			from TEST_CASE_STEPS
			where exists (select 1 from ENTITIES_TO_DELETE where NAME = 'CALLING_TEST_CASE' and TEST_CASE_STEPS.TEST_CASE_ID = ID)
			group by TEST_CASE_ID, STEP_ID, globindex
			order by STEP_ORDER;

create table TEST_CASE_STEPS_TEMP2 (
			TEST_CASE_ID BIGINT NOT NULL,
			STEP_ID BIGINT NOT NULL,
			STEP_ORDER INT NOT NULL
			);

insert into TEST_CASE_STEPS_TEMP2
			select * from
			TEST_CASE_STEPS_TEMP;

update TEST_CASE_STEPS tcs
			set STEP_ORDER = (
				select count( tcst2.STEP_ID )
				from TEST_CASE_STEPS_TEMP tcst1,TEST_CASE_STEPS_TEMP2 tcst2
				where tcst1.STEP_ID = tcs.STEP_ID
				and tcst2.TEST_CASE_ID = tcst1.TEST_CASE_ID
				and tcst1.STEP_ORDER > tcst2.STEP_ORDER
			)
 			where tcs.STEP_ID in (select DISTINCT tcst3.STEP_ID from TEST_CASE_STEPS_TEMP tcst3);

drop table TEST_CASE_STEPS_TEMP;

drop table TEST_CASE_STEPS_TEMP2;

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'reorder steps of calling test case', NOW(), 'Custom SQL', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-3-testcases.xml', 'tm-1.8.0.issue-2933-tc-11-h2', '2.0.5', '3:1c1ec977d2a0275b1e3eddf2a9284572', 272);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-3-testcases.xml::tm-1.8.0.issue-2933-tc-11-postgresql::mpagnon::(Checksum: 3:5e49f7af783fa1f8d72d4a11b5feb318)
-- reorder steps of calling test case
-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-3-testcases.xml::tm-1.8.0.issue-2933-tc-12::mpagnon::(Checksum: 3:16a031e3efa034fdbfadc9bbb9e8bfd4)
-- delete test_case_library_node
delete from TCLN_RELATIONSHIP_CLOSURE 
			where exists (select 1 from ENTITIES_TO_DELETE where NAME = 'TEST_CASE' and ID = TCLN_RELATIONSHIP_CLOSURE.DESCENDANT_ID);

delete from TCLN_RELATIONSHIP
			where exists (select 1 from ENTITIES_TO_DELETE where NAME = 'TEST_CASE' and ID = TCLN_RELATIONSHIP.DESCENDANT_ID);

delete from TEST_CASE
			where exists (select 1 from ENTITIES_TO_DELETE where NAME = 'TEST_CASE' and ID = TEST_CASE.TCLN_ID);

delete from TEST_CASE_LIBRARY_NODE 
			where exists (select 1 from ENTITIES_TO_DELETE where NAME = 'TEST_CASE' and ID = TEST_CASE_LIBRARY_NODE.TCLN_ID);

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'delete test_case_library_node', NOW(), 'Custom SQL (x4)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-3-testcases.xml', 'tm-1.8.0.issue-2933-tc-12', '2.0.5', '3:16a031e3efa034fdbfadc9bbb9e8bfd4', 273);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-3-testcases.xml::tm-1.8.0.issue-2933-tc-13::mpagnon::(Checksum: 3:3f48324c44f4a33aea2c696f4514818c)
-- delete test_case_library_node
delete from TCLN_RELATIONSHIP_CLOSURE 
			where exists (select 1 from ENTITIES_TO_DELETE where NAME = 'TEST_CASE_FOLDER' and ID = TCLN_RELATIONSHIP_CLOSURE.DESCENDANT_ID);

delete from TCLN_RELATIONSHIP
			where exists (select 1 from ENTITIES_TO_DELETE where NAME = 'TEST_CASE_FOLDER' and ID = TCLN_RELATIONSHIP.DESCENDANT_ID);

delete from TEST_CASE_FOLDER
			where exists (select 1 from ENTITIES_TO_DELETE where NAME = 'TEST_CASE_FOLDER' and ID = TEST_CASE_FOLDER.TCLN_ID);

delete from TEST_CASE_LIBRARY_NODE 
			where exists (select 1 from ENTITIES_TO_DELETE where NAME = 'TEST_CASE_FOLDER' and ID = TEST_CASE_LIBRARY_NODE.TCLN_ID);

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'delete test_case_library_node', NOW(), 'Custom SQL (x4)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-3-testcases.xml', 'tm-1.8.0.issue-2933-tc-13', '2.0.5', '3:3f48324c44f4a33aea2c696f4514818c', 274);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-4-campaigns.xml::tm-1.8.0.issue-2933-camp-01::mpagnon::(Checksum: 3:c6c91afa7c21dc23771e00cd7c9efcab)
-- store info if exist campaign folders to delete
insert into ENTITIES_TO_DELETE
			(NAME, ID)
			select 'CAMPAIGN_FOLDER',
			folder.CLN_ID
			from CAMPAIGN_FOLDER folder
			where not exists (
			select 1  from CLN_RELATIONSHIP where DESCENDANT_ID = folder.CLN_ID  UNION
			select 1  from CAMPAIGN_LIBRARY_CONTENT where CONTENT_ID = folder.CLN_ID 
			);

insert into IS_TO_DELETE
			(NAME, TOTAL)
			select 'CAMPAIGN_FOLDER',
			count(1)
			from ENTITIES_TO_DELETE
			where NAME = 'CAMPAIGN_FOLDER';

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'store info if exist campaign folders to delete', NOW(), 'Custom SQL (x2)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-4-campaigns.xml', 'tm-1.8.0.issue-2933-camp-01', '2.0.5', '3:c6c91afa7c21dc23771e00cd7c9efcab', 275);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-4-campaigns.xml::tm-1.8.0.issue-2933-camp-02::mpagnon::(Checksum: 3:8c0b9f05931f1720391d7d31544c0cc1)
-- save if campaign to delete exists
insert into ENTITIES_TO_DELETE
			(NAME, ID)
			select 'CAMPAIGN_FOLDER', cf.CLN_ID
			from CAMPAIGN_FOLDER cf, CLN_RELATIONSHIP_CLOSURE crc, ENTITIES_TO_DELETE etd
			where cf.CLN_ID = crc.DESCENDANT_ID
			and crc.ANCESTOR_ID = etd.ID
			and etd.NAME = 'CAMPAIGN_FOLDER';

insert into ENTITIES_TO_DELETE
			(NAME, ID)
			select 'CAMPAIGN', c.CLN_ID
			from CAMPAIGN c, CLN_RELATIONSHIP_CLOSURE crc, ENTITIES_TO_DELETE etd
			where c.CLN_ID = crc.DESCENDANT_ID
			and crc.ANCESTOR_ID = etd.ID
			and etd.NAME = 'CAMPAIGN_FOLDER';

insert into ENTITIES_TO_DELETE
			(NAME, ID)
			select 'ATTACHMENT_LIST', cln.ATTACHMENT_LIST_ID
			from CAMPAIGN_LIBRARY_NODE cln, ENTITIES_TO_DELETE etd
			where cln.CLN_ID = etd.ID
			and etd.NAME in ('CAMPAIGN_FOLDER', 'CAMPAIGN');

insert into IS_TO_DELETE
			(NAME, TOTAL)
			select 'CAMPAIGN', count(1)
			from ENTITIES_TO_DELETE etd
			where etd.NAME = 'CAMPAIGN';

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'save if campaign to delete exists', NOW(), 'Custom SQL (x3)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-4-campaigns.xml', 'tm-1.8.0.issue-2933-camp-02', '2.0.5', '3:8c0b9f05931f1720391d7d31544c0cc1', 276);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-4-campaigns.xml::tm-1.8.0.issue-2933-camp-03::mpagnon::(Checksum: 3:56c274bc0f84834141883b15dfd32f7a)
-- save iteration to delete property
insert into ENTITIES_TO_DELETE
			(NAME, ID)
			select 'CUSTOM_FIELD_VALUE', cfv.CFV_ID
			from CUSTOM_FIELD_VALUE cfv, ENTITIES_TO_DELETE etd
			where cfv.BOUND_ENTITY_TYPE = 'CAMPAIGN'
			and cfv.BOUND_ENTITY_ID = etd.ID
			and etd.NAME = 'CAMPAIGN';

delete from CAMPAIGN_TEST_PLAN_ITEM
			where exists (select 1 from ENTITIES_TO_DELETE where NAME = 'CAMPAIGN' and ID = CAMPAIGN_TEST_PLAN_ITEM.CAMPAIGN_ID);

insert into ENTITIES_TO_DELETE
			(NAME, ID)
			select 'ITERATION', ci.ITERATION_ID
			from CAMPAIGN_ITERATION ci, ENTITIES_TO_DELETE etd
			where etd.NAME = 'CAMPAIGN'
			and ci.CAMPAIGN_ID = etd.ID;

insert into IS_TO_DELETE
			(NAME, TOTAL)
			select 'ITERATION', count(1)
			from ENTITIES_TO_DELETE etd
			where etd.NAME = 'ITERATION';

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'save iteration to delete property', NOW(), 'Custom SQL (x4)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-4-campaigns.xml', 'tm-1.8.0.issue-2933-camp-03', '2.0.5', '3:56c274bc0f84834141883b15dfd32f7a', 277);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-4-campaigns.xml::tm-1.8.0.issue-2933-camp-04::mpagnon::(Checksum: 3:98fe59bdbbb250426894f4993ebe6be4)
-- save if itpi to delete property
insert into ENTITIES_TO_DELETE
			(NAME, ID)
			select 'ATTACHMENT_LIST', it.ATTACHMENT_LIST_ID
			from ITERATION it, ENTITIES_TO_DELETE etd
			where etd.NAME = 'ITERATION'
			and etd.ID = it.ITERATION_ID;

insert into ENTITIES_TO_DELETE
			(NAME, ID)
			select 'CUSTOM_FIELD_VALUE', cfv.CFV_ID
			from CUSTOM_FIELD_VALUE cfv, ENTITIES_TO_DELETE etd
			where etd.NAME = 'ITERATION'
			and etd.ID = cfv.BOUND_ENTITY_ID
			and cfv.BOUND_ENTITY_TYPE = 'ITERATION';

insert into ENTITIES_TO_DELETE
			(NAME, ID)
			select 'ITPI', itpl.ITEM_TEST_PLAN_ID
			from ITEM_TEST_PLAN_LIST itpl, ENTITIES_TO_DELETE etd
			where etd.NAME = 'ITERATION'
			and etd.ID = itpl.ITERATION_ID;

insert into ENTITIES_TO_DELETE
			(NAME, ID)
			select 'TEST_SUITE', its.TEST_SUITE_ID
			from ITERATION_TEST_SUITE its, ENTITIES_TO_DELETE etd
			where etd.NAME = 'ITERATION'
			and etd.ID = its.ITERATION_ID;

insert into IS_TO_DELETE
			(NAME, TOTAL)
			select 'TEST_SUITE', count(1)
			from ENTITIES_TO_DELETE etd
			where etd.NAME = 'TEST_SUITE';

insert into IS_TO_DELETE
			(NAME, TOTAL)
			select 'ITPI', count(1)
			from ENTITIES_TO_DELETE etd
			where etd.NAME = 'ITPI';

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'save if itpi to delete property', NOW(), 'Custom SQL (x6)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-4-campaigns.xml', 'tm-1.8.0.issue-2933-camp-04', '2.0.5', '3:98fe59bdbbb250426894f4993ebe6be4', 278);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-4-campaigns.xml::tm-1.8.0.issue-2933-camp-05::mpagnon::(Checksum: 3:d7695175e3249ff3d8a705725f99d1de)
-- save execution to delete property
insert into ENTITIES_TO_DELETE
			(NAME, ID)
			select 'EXECUTION' , itpe.EXECUTION_ID
			from ITEM_TEST_PLAN_EXECUTION itpe, ENTITIES_TO_DELETE etd
			where etd.ID = itpe.ITEM_TEST_PLAN_ID
			and etd.NAME = 'ITPI';

insert into IS_TO_DELETE
			(NAME, TOTAL)
			select 'EXECUTION', count(1)
			from ENTITIES_TO_DELETE etd
			where etd.NAME = 'EXECUTION';

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'save execution to delete property', NOW(), 'Custom SQL (x2)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-4-campaigns.xml', 'tm-1.8.0.issue-2933-camp-05', '2.0.5', '3:d7695175e3249ff3d8a705725f99d1de', 279);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-4-campaigns.xml::tm-1.8.0.issue-2933-camp-06::mpagnon::(Checksum: 3:e96a23d8010294712425e7871d266718)
-- save if execution step to delete
insert into ENTITIES_TO_DELETE
			(NAME, ID)
			select 'ATTACHMENT_LIST', e.ATTACHMENT_LIST_ID
			from EXECUTION e , ENTITIES_TO_DELETE etd
			where etd.NAME = 'EXECUTION'
			and e.EXECUTION_ID = etd.ID;

insert into ENTITIES_TO_DELETE
			(NAME, ID)
			select 'ISSUE_LIST', e.ISSUE_LIST_ID
			from EXECUTION e , ENTITIES_TO_DELETE etd
			where etd.NAME = 'EXECUTION'
			and e.EXECUTION_ID = etd.ID;

insert into ENTITIES_TO_DELETE
			(NAME, ID)
			select 'AUTOMATED_EXECUTION_EXTENDER', aee.EXTENDER_ID
			from AUTOMATED_EXECUTION_EXTENDER aee, ENTITIES_TO_DELETE etd
			where etd.NAME = 'EXECUTION'
			and etd.ID = aee.MASTER_EXECUTION_ID;

insert into ENTITIES_TO_DELETE
			(NAME, ID)
			select 'EXECUTION_STEP', ees.EXECUTION_STEP_ID
			from EXECUTION_EXECUTION_STEPS ees, ENTITIES_TO_DELETE etd
			where etd.NAME = 'EXECUTION'
			and etd.ID = ees.EXECUTION_ID;

insert into IS_TO_DELETE
			(NAME, TOTAL)
			select 'EXECUTION_STEP', count(1)
			from ENTITIES_TO_DELETE etd
			where etd.NAME = 'EXECUTION_STEP';

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'save if execution step to delete', NOW(), 'Custom SQL (x5)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-4-campaigns.xml', 'tm-1.8.0.issue-2933-camp-06', '2.0.5', '3:e96a23d8010294712425e7871d266718', 280);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-4-campaigns.xml::tm-1.8.0.issue-2933-camp-07::mpagnon::(Checksum: 3:9f09d41eff2b6496374b6308596ab9f0)
-- delete automated_suite where not exist automated_execution_extender linked to it
delete from AUTOMATED_EXECUTION_EXTENDER
			where exists (select 1 from ENTITIES_TO_DELETE where NAME = 'AUTOMATED_EXECUTION_EXTENDER' and ID = AUTOMATED_EXECUTION_EXTENDER.EXTENDER_ID);

delete from AUTOMATED_SUITE
			where not exists (select 1 from AUTOMATED_EXECUTION_EXTENDER where AUTOMATED_EXECUTION_EXTENDER.SUITE_ID = AUTOMATED_SUITE.SUITE_ID);

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'delete automated_suite where not exist automated_execution_extender linked to it', NOW(), 'Custom SQL (x2)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-4-campaigns.xml', 'tm-1.8.0.issue-2933-camp-07', '2.0.5', '3:9f09d41eff2b6496374b6308596ab9f0', 281);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-4-campaigns.xml::tm-1.8.0.issue-2933-camp-08::mpagnon::(Checksum: 3:a6db7a5d93feeee8506a3d97119d85be)
-- delete execution steps
insert into ENTITIES_TO_DELETE
			(NAME, ID)
			select 'ISSUE_LIST', es.ISSUE_LIST_ID
			from EXECUTION_STEP es, ENTITIES_TO_DELETE etd
			where etd.NAME = 'EXECUTION_STEP'
			and etd.ID = es.EXECUTION_STEP_ID;

insert into ENTITIES_TO_DELETE
			(NAME, ID)
			select 'ATTACHMENT_LIST', es.ATTACHMENT_LIST_ID
			from EXECUTION_STEP es, ENTITIES_TO_DELETE etd
			where etd.NAME = 'EXECUTION_STEP'
			and etd.ID = es.EXECUTION_STEP_ID;

delete from EXECUTION_EXECUTION_STEPS
			where  exists  (select 1 from ENTITIES_TO_DELETE where NAME = 'EXECUTION' and ID = EXECUTION_EXECUTION_STEPS.EXECUTION_ID);

delete from EXECUTION_STEP
			where  exists (select 1 from ENTITIES_TO_DELETE where NAME = 'EXECUTION_STEP' and ID = EXECUTION_STEP.EXECUTION_STEP_ID);

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'delete execution steps', NOW(), 'Custom SQL (x3)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-4-campaigns.xml', 'tm-1.8.0.issue-2933-camp-08', '2.0.5', '3:a6db7a5d93feeee8506a3d97119d85be', 282);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-4-campaigns.xml::tm-1.8.0.issue-2933-camp-09::mpagnon::(Checksum: 3:9d678fc02fa6228bc6f21e71a47d10e5)
-- delete issue_lists
delete from ITEM_TEST_PLAN_EXECUTION
			where  exists (select 1 from ENTITIES_TO_DELETE where NAME = 'ITPI' and ID = ITEM_TEST_PLAN_EXECUTION.ITEM_TEST_PLAN_ID);

delete from EXECUTION 
			where  exists (select 1 from ENTITIES_TO_DELETE where NAME = 'EXECUTION' and ID = EXECUTION.EXECUTION_ID);

delete from ISSUE
			where  exists (select 1 from ENTITIES_TO_DELETE where NAME = 'ISSUE_LIST' and ID = ISSUE.ISSUE_LIST_ID);

delete from ISSUE_LIST
			where  exists (select 1 from ENTITIES_TO_DELETE where NAME = 'ISSUE_LIST' and ID = ISSUE_LIST.ISSUE_LIST_ID);

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'delete issue_lists', NOW(), 'Custom SQL (x3)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-4-campaigns.xml', 'tm-1.8.0.issue-2933-camp-09', '2.0.5', '3:9d678fc02fa6228bc6f21e71a47d10e5', 283);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-4-campaigns.xml::tm-1.8.0.issue-2933-camp-10::mpagnon::(Checksum: 3:623237b523a6a2d6d2d771383ef7cb79)
-- delete iteration_test_plan_item
delete from ITEM_TEST_PLAN_LIST
			where exists (select 1 from ENTITIES_TO_DELETE where NAME ='ITERATION' and ID = ITEM_TEST_PLAN_LIST.ITERATION_ID);

delete from TEST_SUITE_TEST_PLAN_ITEM
			where exists (select 1 from ENTITIES_TO_DELETE where NAME = 'TEST_SUITE' and ID = TEST_SUITE_TEST_PLAN_ITEM.SUITE_ID);

delete from ITERATION_TEST_PLAN_ITEM
			where  exists (select 1 from ENTITIES_TO_DELETE where NAME = 'ITPI' and ID = ITERATION_TEST_PLAN_ITEM.ITEM_TEST_PLAN_ID);

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'delete iteration_test_plan_item', NOW(), 'Custom SQL (x3)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-4-campaigns.xml', 'tm-1.8.0.issue-2933-camp-10', '2.0.5', '3:623237b523a6a2d6d2d771383ef7cb79', 284);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-4-campaigns.xml::tm-1.8.0.issue-2933-camp-11::mpagnon::(Checksum: 3:0d7b15110756f345ad7657126192bed6)
-- delete test_suites
insert into ENTITIES_TO_DELETE
			(NAME, ID)
			select 'ATTACHMENT_LIST', ts.ATTACHMENT_LIST_ID
			from TEST_SUITE ts, ENTITIES_TO_DELETE etd
			where etd.NAME = 'TEST_SUITE'
			and etd.ID = ts.ID;

insert into ENTITIES_TO_DELETE
			(NAME, ID)
			select 'CUSTOM_FIELD_VALUE', cfv.CFV_ID
			from CUSTOM_FIELD_VALUE cfv, ENTITIES_TO_DELETE etd
			where etd.NAME = 'TEST_SUITE'
			and etd.ID = cfv.BOUND_ENTITY_ID
			and cfv.BOUND_ENTITY_TYPE = 'TEST_SUITE';

delete from ITERATION_TEST_SUITE
			where  exists (select 1 from ENTITIES_TO_DELETE where NAME = 'ITERATION' and ID = ITERATION_TEST_SUITE.ITERATION_ID);

delete from TEST_SUITE
			where exists (select 1 from ENTITIES_TO_DELETE where NAME = 'TEST_SUITE' and ENTITIES_TO_DELETE.ID = TEST_SUITE.ID);

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'delete test_suites', NOW(), 'Custom SQL (x4)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-4-campaigns.xml', 'tm-1.8.0.issue-2933-camp-11', '2.0.5', '3:0d7b15110756f345ad7657126192bed6', 285);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-4-campaigns.xml::tm-1.8.0.issue-2933-camp-12::mpagnon::(Checksum: 3:10d09256e84d19637fcc34315eddc4f6)
-- delete iterations
delete from CAMPAIGN_ITERATION
			where exists (select 1 from ENTITIES_TO_DELETE where NAME = 'CAMPAIGN' and ID = CAMPAIGN_ITERATION.CAMPAIGN_ID);

delete from ITERATION
			where exists (select 1 from ENTITIES_TO_DELETE where NAME = 'ITERATION' and ID = ITERATION.ITERATION_ID);

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'delete iterations', NOW(), 'Custom SQL (x2)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-4-campaigns.xml', 'tm-1.8.0.issue-2933-camp-12', '2.0.5', '3:10d09256e84d19637fcc34315eddc4f6', 286);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-4-campaigns.xml::tm-1.8.0.issue-2933-camp-13::mpagnon::(Checksum: 3:80316977c3c6e0fa9ab9c87d2a65a50a)
-- delete campaign_library_nodes
delete from CLN_RELATIONSHIP
			where exists (select 1 from ENTITIES_TO_DELETE where NAME = 'CAMPAIGN' and ID = CLN_RELATIONSHIP.DESCENDANT_ID);

delete from CLN_RELATIONSHIP_CLOSURE
			where  exists (select 1 from ENTITIES_TO_DELETE where NAME = 'CAMPAIGN' and ID = CLN_RELATIONSHIP_CLOSURE.DESCENDANT_ID);

delete from CAMPAIGN
			where  exists (select 1 from ENTITIES_TO_DELETE where NAME = 'CAMPAIGN' and ID = CAMPAIGN.CLN_ID);

delete from CAMPAIGN_LIBRARY_NODE
			where exists (select 1 from ENTITIES_TO_DELETE where NAME = 'CAMPAIGN' and ID = CAMPAIGN_LIBRARY_NODE.CLN_ID);

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'delete campaign_library_nodes', NOW(), 'Custom SQL (x4)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-4-campaigns.xml', 'tm-1.8.0.issue-2933-camp-13', '2.0.5', '3:80316977c3c6e0fa9ab9c87d2a65a50a', 287);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-4-campaigns.xml::tm-1.8.0.issue-2933-camp-14::mpagnon::(Checksum: 3:a1d18aa168b966f85e3a23721e735db1)
-- delete campaign_library_nodes
delete from CLN_RELATIONSHIP
			where  exists (select 1 from ENTITIES_TO_DELETE where NAME = 'CAMPAIGN_FOLDER' and ID = CLN_RELATIONSHIP.DESCENDANT_ID);

delete from CLN_RELATIONSHIP_CLOSURE
			where  exists (select 1 from ENTITIES_TO_DELETE where NAME = 'CAMPAIGN_FOLDER' and ID = CLN_RELATIONSHIP_CLOSURE.DESCENDANT_ID);

delete from CAMPAIGN_FOLDER
			where  exists (select 1 from ENTITIES_TO_DELETE where NAME = 'CAMPAIGN_FOLDER' and ID = CAMPAIGN_FOLDER.CLN_ID);

delete from CAMPAIGN_LIBRARY_NODE
			where  exists (select 1 from ENTITIES_TO_DELETE where NAME = 'CAMPAIGN_FOLDER' and ID = CLN_ID);

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'delete campaign_library_nodes', NOW(), 'Custom SQL (x4)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-4-campaigns.xml', 'tm-1.8.0.issue-2933-camp-14', '2.0.5', '3:a1d18aa168b966f85e3a23721e735db1', 288);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-5-end.xml::tm-1.8.0.issue-2933-101::mpagnon::(Checksum: 3:68003823417d31dca77d2e3e5ae5b88d)
-- delete attachment_list
insert into ENTITIES_TO_DELETE
			(NAME, ID)
			select
			'ATTACHMENT_CONTENT', attach.CONTENT_ID
			from ATTACHMENT attach,
			ENTITIES_TO_DELETE etd
			where etd.NAME = 'ATTACHMENT_LIST'
			and etd.ID =
			attach.ATTACHMENT_LIST_ID;

delete from ATTACHMENT
			where ATTACHMENT_LIST_ID in (select ID from
			ENTITIES_TO_DELETE where NAME = 'ATTACHMENT_LIST');

delete from ATTACHMENT_CONTENT
			where ATTACHMENT_CONTENT_ID in
			(select ID from ENTITIES_TO_DELETE where NAME = 'ATTACHMENT_CONTENT');

delete from ATTACHMENT_LIST
			where ATTACHMENT_LIST_ID in (select ID
			from ENTITIES_TO_DELETE where NAME = 'ATTACHMENT_LIST');

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'delete attachment_list', NOW(), 'Custom SQL (x4)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-5-end.xml', 'tm-1.8.0.issue-2933-101', '2.0.5', '3:68003823417d31dca77d2e3e5ae5b88d', 289);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-5-end.xml::tm-1.8.0.issue-2933-end-01::mpagnon::(Checksum: 3:99805bb79ef2124a76e379b7c3fe0569)
-- delete cuf values
delete from CUSTOM_FIELD_VALUE
			where CFV_ID in (select ID from
			ENTITIES_TO_DELETE where NAME =
			'CUSTOM_FIELD_VALUE');

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'delete cuf values', NOW(), 'Custom SQL', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-5-end.xml', 'tm-1.8.0.issue-2933-end-01', '2.0.5', '3:99805bb79ef2124a76e379b7c3fe0569', 290);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-5-end.xml::tm-1.8.0.issue-2933-end-02::mpagnon::(Checksum: 3:135636cf00693c00e667b02603862bf2)
-- delete storage tables
DROP TABLE ENTITIES_TO_DELETE;

DROP TABLE IS_TO_DELETE;

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'delete storage tables', NOW(), 'Drop Table (x2)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.0-issue-2933-5-end.xml', 'tm-1.8.0.issue-2933-end-02', '2.0.5', '3:135636cf00693c00e667b02603862bf2', 291);

-- Release Database Lock
-- Release Database Lock
