--  *********************************************************************
--  Update Database Script
--  *********************************************************************
--  Change Log: /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/upgrade/upgrade.changelog-up-to-1.7.1.xml
--  Ran at: 04/03/15 11:45
--  Against: squashtest_adm@localhost@jdbc:mysql://localhost:3306/squashtest_maint
--  Liquibase version: 2.0.5
--  *********************************************************************

--  Lock Database
--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.7.1.xml::tm-1.7.1::mpagnon::(Checksum: 3:704687745c9c0f218a642929b1e30e3f)
--  Update TM database version number
UPDATE `CORE_CONFIG` SET `VALUE` = '1.7.1' 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/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.7.1.xml', 'tm-1.7.1', '2.0.5', '3:704687745c9c0f218a642929b1e30e3f', 234);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.7.1.xml::tm-1.7.1.issue-2873-01::mpagnon::(Checksum: 3:68b5f0e453857633365c9710e5a0ae24)
--  create table to store the id of entities to delete
CREATE TABLE `ENTITIES_TO_DELETE` (`ENTITY_NAME` VARCHAR(30) NULL, `ENTITY_ID` BIGINT NULL);

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('mpagnon', 'create table to store the id of entities to delete', NOW(), 'Create Table', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.7.1.xml', 'tm-1.7.1.issue-2873-01', '2.0.5', '3:68b5f0e453857633365c9710e5a0ae24', 235);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.7.1.xml::tm-1.7.1.issue-2873-02::mpagnon::(Checksum: 3:bb934c8baa9b8a4ba21766365b8820f3)
--  Store the ids of the ghost executions
insert into ENTITIES_TO_DELETE (ENTITY_NAME, ENTITY_ID)
			select distinct 'EXECUTION', E.EXECUTION_ID
			from EXECUTION E left outer join ITEM_TEST_PLAN_EXECUTION ITPE
			on E.EXECUTION_ID = ITPE.EXECUTION_ID
			where ITPE.EXECUTION_ID is null;

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('mpagnon', 'Store the ids of the ghost executions', NOW(), 'Custom SQL', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.7.1.xml', 'tm-1.7.1.issue-2873-02', '2.0.5', '3:bb934c8baa9b8a4ba21766365b8820f3', 236);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.7.1.xml::tm-1.7.1.issue-2873-03::mpagnon::(Checksum: 3:8ddf17c0a207e5990e601785e165d470)
--  Store the ids of the execution steps
insert into ENTITIES_TO_DELETE (ENTITY_NAME, ENTITY_ID)
			select
			'EXECUTION_STEP', ES.EXECUTION_STEP_ID
			from EXECUTION_STEP ES, EXECUTION_EXECUTION_STEPS EES, ENTITIES_TO_DELETE ETD
			where ES.EXECUTION_STEP_ID = EES.EXECUTION_STEP_ID
			and EES.EXECUTION_ID =	ETD.ENTITY_ID
			and ETD.ENTITY_NAME = 'EXECUTION';

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('mpagnon', 'Store the ids of the execution steps', NOW(), 'Custom SQL', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.7.1.xml', 'tm-1.7.1.issue-2873-03', '2.0.5', '3:8ddf17c0a207e5990e601785e165d470', 237);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.7.1.xml::tm-1.7.1.issue-2873-04::mpagnon::(Checksum: 3:0daeae5e4dd689bcf981b8f5262d0109)
--  Store the ids of the issue lists to delete
insert into ENTITIES_TO_DELETE (ENTITY_NAME, ENTITY_ID)
			select 'ISSUE_LIST', E.ISSUE_LIST_ID
			from EXECUTION E,  ENTITIES_TO_DELETE ETD
			where E.EXECUTION_ID = ETD.ENTITY_ID
			and ETD.ENTITY_NAME = 'EXECUTION';

insert into ENTITIES_TO_DELETE (ENTITY_NAME, ENTITY_ID)
			select 'ISSUE_LIST', ES.ISSUE_LIST_ID
			from   EXECUTION_STEP ES,  ENTITIES_TO_DELETE ETD2
			where ES.EXECUTION_STEP_ID = ETD2.ENTITY_ID
			and	ETD2.ENTITY_NAME = 'EXECUTION_STEP';

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('mpagnon', 'Store the ids of the issue lists to delete', NOW(), 'Custom SQL (x2)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.7.1.xml', 'tm-1.7.1.issue-2873-04', '2.0.5', '3:0daeae5e4dd689bcf981b8f5262d0109', 238);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.7.1.xml::tm-1.7.1.issue-2873-05::mpagnon::(Checksum: 3:62636bc8eb17d19b1dd725e251e898cb)
--  Store the ids of the issue lists to delete
insert into ENTITIES_TO_DELETE (ENTITY_NAME, ENTITY_ID)
			select 'ATTACHMENT_LIST', E.ATTACHMENT_LIST_ID
			from EXECUTION E,  ENTITIES_TO_DELETE ETD
			where  E.EXECUTION_ID = ETD.ENTITY_ID
			and ETD.ENTITY_NAME = 'EXECUTION';

insert into ENTITIES_TO_DELETE (ENTITY_NAME, ENTITY_ID)
			select 'ATTACHMENT_LIST', ES.ATTACHMENT_LIST_ID
			from  EXECUTION_STEP ES,  ENTITIES_TO_DELETE ETD2
			where  ES.EXECUTION_STEP_ID = ETD2.ENTITY_ID
			and	ETD2.ENTITY_NAME = 'EXECUTION_STEP';

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('mpagnon', 'Store the ids of the issue lists to delete', NOW(), 'Custom SQL (x2)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.7.1.xml', 'tm-1.7.1.issue-2873-05', '2.0.5', '3:62636bc8eb17d19b1dd725e251e898cb', 239);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.7.1.xml::tm-1.7.1.issue-2873-06::mpagnon::(Checksum: 3:a800251d0d6a914edc45934a77ddf1ed)
--  DELETE Issues of execution and execution steps to delete
DELETE FROM `ISSUE`  WHERE exists (
				select 1
				from ENTITIES_TO_DELETE
				where ENTITY_NAME = 'ISSUE_LIST'
				and ENTITY_ID = ISSUE_LIST_ID
				);

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('mpagnon', 'DELETE Issues of execution and execution steps to delete', NOW(), 'Delete Data', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.7.1.xml', 'tm-1.7.1.issue-2873-06', '2.0.5', '3:a800251d0d6a914edc45934a77ddf1ed', 240);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.7.1.xml::tm-1.7.1.issue-2873-07::mpagnon::(Checksum: 3:8876a68af7b32012273a65351485fd5c)
--  DELETE Automated execution extender of executions to delete
DELETE FROM `AUTOMATED_EXECUTION_EXTENDER`  WHERE exists (
				select	1 
				from ENTITIES_TO_DELETE
				where ENTITY_NAME = 'EXECUTION'
				and ENTITY_ID = MASTER_EXECUTION_ID);

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('mpagnon', 'DELETE Automated execution extender of executions to delete', NOW(), 'Delete Data', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.7.1.xml', 'tm-1.7.1.issue-2873-07', '2.0.5', '3:8876a68af7b32012273a65351485fd5c', 241);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.7.1.xml::tm-1.7.1.issue-2873-08::mpagnon::(Checksum: 3:900bc764b6e513995c6afe9a19979f02)
--  DELETE automated suites that are not linked to any automated execution extender
DELETE FROM `AUTOMATED_SUITE`  WHERE not exists (
				select 1
				from AUTOMATED_EXECUTION_EXTENDER
				where AUTOMATED_SUITE.SUITE_ID = AUTOMATED_EXECUTION_EXTENDER.SUITE_ID);

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('mpagnon', 'DELETE automated suites that are not linked to any automated execution extender', NOW(), 'Delete Data', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.7.1.xml', 'tm-1.7.1.issue-2873-08', '2.0.5', '3:900bc764b6e513995c6afe9a19979f02', 242);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.7.1.xml::tm-1.7.1.issue-2873-09::mpagnon::(Checksum: 3:186698b62dfabaeb568577fffd561506)
--  Delete execution steps of ghost executions
DELETE FROM `EXECUTION_EXECUTION_STEPS`  WHERE exists (
				select 1 
				from ENTITIES_TO_DELETE
				where ENTITY_NAME = 'EXECUTION_STEP'
				and ENTITY_ID = EXECUTION_STEP_ID);

DELETE FROM `EXECUTION_STEP`  WHERE exists (
				select 1 
				from ENTITIES_TO_DELETE
				where ENTITY_NAME = 'EXECUTION_STEP'
				and EXECUTION_STEP_ID = ENTITY_ID);

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('mpagnon', 'Delete execution steps of ghost executions', NOW(), 'Delete Data (x2)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.7.1.xml', 'tm-1.7.1.issue-2873-09', '2.0.5', '3:186698b62dfabaeb568577fffd561506', 243);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.7.1.xml::tm-1.7.1.issue-2873-10::mpagnon::(Checksum: 3:47d07b4d4a0877813ee39b635393da38)
--  Delete ghost executions
DELETE FROM `EXECUTION`  WHERE exists (
				select 1 
				from ENTITIES_TO_DELETE
				where ENTITY_NAME = 'EXECUTION'
				and EXECUTION_ID = ENTITY_ID);

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('mpagnon', 'Delete ghost executions', NOW(), 'Delete Data', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.7.1.xml', 'tm-1.7.1.issue-2873-10', '2.0.5', '3:47d07b4d4a0877813ee39b635393da38', 244);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.7.1.xml::tm-1.7.1.issue-2873-11::mpagnon::(Checksum: 3:22899dd09e8779cb77785618956a214c)
--  DELETE Attachments of execution and execution steps to delete
DELETE FROM `ATTACHMENT`  WHERE exists (
				select 1
				from ENTITIES_TO_DELETE
				where ENTITY_NAME = 'ATTACHMENT_LIST'
				and ENTITY_ID = ATTACHMENT_LIST_ID
				);

DELETE FROM `ATTACHMENT_CONTENT`  WHERE not exists (
				select 1
				from ATTACHMENT
				where CONTENT_ID = ATTACHMENT_CONTENT_ID
				);

DELETE FROM `ATTACHMENT_LIST`  WHERE exists (
				select 1
				from ENTITIES_TO_DELETE
				where ENTITY_NAME = 'ATTACHMENT_LIST'
				and ATTACHMENT_LIST_ID = ENTITY_ID
				);

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('mpagnon', 'DELETE Attachments of execution and execution steps to delete', NOW(), 'Delete Data (x3)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.7.1.xml', 'tm-1.7.1.issue-2873-11', '2.0.5', '3:22899dd09e8779cb77785618956a214c', 245);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.7.1.xml::tm-1.7.1.issue-2873-12::mpagnon::(Checksum: 3:a2d86fa017d0bd1fe52873006638c208)
--  DELETE issue lists of executions and execution-steps to delete
DELETE FROM `ISSUE_LIST`  WHERE exists (
				select 1 
				from ENTITIES_TO_DELETE
				where ENTITY_NAME ='ISSUE_LIST'
				and ENTITY_ID = ISSUE_LIST_ID);

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('mpagnon', 'DELETE issue lists of executions and execution-steps to delete', NOW(), 'Delete Data', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.7.1.xml', 'tm-1.7.1.issue-2873-12', '2.0.5', '3:a2d86fa017d0bd1fe52873006638c208', 246);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.7.1.xml::tm-1.7.1.issue-2873-13::mpagnon::(Checksum: 3:a223a65d8dc3d320d65403a98819cc88)
--  Drop table used to store the ids of the entities to delete
DROP TABLE `ENTITIES_TO_DELETE`;

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('mpagnon', 'Drop table used to store the ids of the entities to delete', NOW(), 'Drop Table', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.7.1.xml', 'tm-1.7.1.issue-2873-13', '2.0.5', '3:a223a65d8dc3d320d65403a98819cc88', 247);

--  Release Database Lock
--  Release Database Lock
