--  *********************************************************************
--  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.6.0.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.6.0.xml::tm-1.6.0::mpagnon::(Checksum: 3:44fa0971a21cf0ff8ba72bac20568984)
--  Update TM database version number
UPDATE `CORE_CONFIG` SET `VALUE` = '1.6.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/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.6.0.xml', 'tm-1.6.0', '2.0.5', '3:44fa0971a21cf0ff8ba72bac20568984', 213);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.6.0.xml::tm-1.6.0.issue-2334-01::flaurens::(Checksum: 3:3d8ec8510433cc5cbe5194b22057cb88)
--  Change type of column description in table core_team
CREATE TABLE `CORE_TEAM_TEMP` (`ID` BIGINT DEFAULT NULL NULL, `DESCRIPTION` LONGTEXT DEFAULT null NULL);

insert into CORE_TEAM_TEMP (ID, DESCRIPTION)
			select PARTY_ID, DESCRIPTION from CORE_TEAM;

ALTER TABLE `CORE_TEAM` DROP COLUMN `DESCRIPTION`;

ALTER TABLE `CORE_TEAM` ADD `DESCRIPTION` LONGTEXT DEFAULT null;

update CORE_TEAM ct
            set DESCRIPTION = (select ctt.DESCRIPTION
                               from CORE_TEAM_TEMP ctt
                               where ctt.ID = ct.PARTY_ID);

DROP TABLE `CORE_TEAM_TEMP`;

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('flaurens', 'Change type of column description in table core_team', NOW(), 'Create Table, Custom SQL, Drop Column, Add Column, Custom SQL, Drop Table', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.6.0.xml', 'tm-1.6.0.issue-2334-01', '2.0.5', '3:3d8ec8510433cc5cbe5194b22057cb88', 214);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.6.0.xml::tm-1.6.0.feat-1101-01::mpagnon::(Checksum: 3:a6572d9cd64b76b53fdafb14a169b44b)
--  Add table to handle test cases PARAMETERS
CREATE TABLE `PARAMETER` (`PARAM_ID` BIGINT AUTO_INCREMENT NOT NULL, `NAME` VARCHAR(255) DEFAULT '' NOT NULL, `TEST_CASE_ID` BIGINT NOT NULL, `DESCRIPTION` LONGTEXT NOT NULL, CONSTRAINT `pk_parameter` PRIMARY KEY (`PARAM_ID`), CONSTRAINT `fk_parameter_test_case` FOREIGN KEY (`TEST_CASE_ID`) REFERENCES TEST_CASE(TCLN_ID));

ALTER TABLE `PARAMETER` ADD UNIQUE (`NAME`, `TEST_CASE_ID`);

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('mpagnon', 'Add table to handle test cases PARAMETERS', NOW(), 'Create Table, Add Unique Constraint', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.6.0.xml', 'tm-1.6.0.feat-1101-01', '2.0.5', '3:a6572d9cd64b76b53fdafb14a169b44b', 215);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.6.0.xml::tm-1.6.0.feat-1101-02::mpagnon::(Checksum: 3:7988c3f7d4bed8d82dd21398694377fe)
--  Add tables to handle test cases DATASETS
CREATE TABLE `DATASET` (`DATASET_ID` BIGINT AUTO_INCREMENT NOT NULL, `NAME` VARCHAR(255) DEFAULT '' NOT NULL, `TEST_CASE_ID` BIGINT NOT NULL, CONSTRAINT `pk_dataset` PRIMARY KEY (`DATASET_ID`), CONSTRAINT `fk_dataset_test_case` FOREIGN KEY (`TEST_CASE_ID`) REFERENCES TEST_CASE(TCLN_ID));

ALTER TABLE `DATASET` ADD UNIQUE (`NAME`, `TEST_CASE_ID`);

CREATE TABLE `DATASET_PARAM_VALUE` (`DATASET_PARAM_VALUE_ID` BIGINT AUTO_INCREMENT NOT NULL, `DATASET_ID` BIGINT NOT NULL, `PARAM_ID` BIGINT NOT NULL, `PARAM_VALUE` VARCHAR(255) DEFAULT '' NOT NULL, CONSTRAINT `pk_dataset_param_value` PRIMARY KEY (`DATASET_PARAM_VALUE_ID`), CONSTRAINT `fk_dataset_param_value_param` FOREIGN KEY (`PARAM_ID`) REFERENCES PARAMETER(PARAM_ID), CONSTRAINT `fk_dataset_param_value_dataset` FOREIGN KEY (`DATASET_ID`) REFERENCES DATASET(DATASET_ID));

ALTER TABLE `DATASET_PARAM_VALUE` ADD UNIQUE (`DATASET_ID`, `PARAM_ID`);

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('mpagnon', 'Add tables to handle test cases DATASETS', NOW(), 'Create Table, Add Unique Constraint, Create Table, Add Unique Constraint', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.6.0.xml', 'tm-1.6.0.feat-1101-02', '2.0.5', '3:7988c3f7d4bed8d82dd21398694377fe', 216);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.6.0.xml::tm-1.6.0.feat-1128-01::mpagnon::(Checksum: 3:c20286a7b7084452c09438ddd7ad8d66)
--  Add column to bind a test plan item to a DATASET
ALTER TABLE `ITERATION_TEST_PLAN_ITEM` ADD `DATASET_ID` BIGINT;

ALTER TABLE `ITERATION_TEST_PLAN_ITEM` ADD CONSTRAINT `fk_iteration_test_plan_item_dataset` FOREIGN KEY (`DATASET_ID`) REFERENCES `DATASET` (`DATASET_ID`);

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('mpagnon', 'Add column to bind a test plan item to a DATASET', NOW(), 'Add Column', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.6.0.xml', 'tm-1.6.0.feat-1128-01', '2.0.5', '3:c20286a7b7084452c09438ddd7ad8d66', 217);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.6.0.xml::tm-1.6.0.issue-2203-01::flaurens::(Checksum: 3:8674d5e47646be66f97168fd484e04ad)
--  remove inactive users from all teams
delete from CORE_TEAM_MEMBER
			where USER_ID in (select cu.PARTY_ID 
			                  from CORE_USER cu
			                  where cu.ACTIVE = false);

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('flaurens', 'remove inactive users from all teams', NOW(), 'Custom SQL', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.6.0.xml', 'tm-1.6.0.issue-2203-01', '2.0.5', '3:8674d5e47646be66f97168fd484e04ad', 218);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.6.0.xml::tm-1.6.0.issue-2203-02::flaurens::(Checksum: 3:47577c8868b9cc23188e8a20458f561c)
--  remove core rights of inactive users
delete from CORE_GROUP_MEMBER
			where PARTY_ID in (select cu.PARTY_ID 
			                   from CORE_USER cu
			                   where cu.ACTIVE = false);

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('flaurens', 'remove core rights of inactive users', NOW(), 'Custom SQL', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.6.0.xml', 'tm-1.6.0.issue-2203-02', '2.0.5', '3:47577c8868b9cc23188e8a20458f561c', 219);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.6.0.xml::tm-1.6.0.issue-2203-03::flaurens::(Checksum: 3:b33c46fc1b66cf2f55cd314f40ed4f8e)
--  remove acl rights of inactive users
delete from ACL_RESPONSIBILITY_SCOPE_ENTRY
			where PARTY_ID in (select cu.PARTY_ID 
			                   from CORE_USER cu
			                   where cu.ACTIVE = false);

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('flaurens', 'remove acl rights of inactive users', NOW(), 'Custom SQL', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.6.0.xml', 'tm-1.6.0.issue-2203-03', '2.0.5', '3:b33c46fc1b66cf2f55cd314f40ed4f8e', 220);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.6.0.xml::tm-1.6.0.issue-2454-01::bsiri::(Checksum: 3:2ea9a08fbaa337f4288ac6f9367a0efb)
--  cleaning test suite test plan ordering : some collections have non-contiguous elements. This 
--  		is due to issue #2454, because of shortcomings in the code in charge of deleting test cases and dependent 
--  		entities.
--  			It looks pretty much like tm-1.5.0.issue-2261-01 (in the 1.5.1 changelog), except we're not dealing with order = null.
--  			
--  			Note : this script might already have been executed if at some point Squash 1.5.2 was used.
-- ------------------------------------------------------------------------------
			-- create the temporary tables
			-- ------------------------------------------------------------------------------
			
			create table TEMP_DISCONTINUOUS_SUITE_ITEM(ITEM_ID BIGINT NOT NULL, SUITE_ID BIGINT NOT NULL);

create table TEMP_CONTINUOUS_SUITE_ITEM(ITEM_ID BIGINT NOT NULL, SUITE_ID BIGINT NOT NULL, TP_ORDER INT NOT NULL, DUMMY BIGINT NOT NULL);

-- ------------------------------------------------------------------------------
			-- gather the data that need migration
			-- ------------------------------------------------------------------------------
						
			insert into TEMP_DISCONTINUOUS_SUITE_ITEM (SUITE_ID, ITEM_ID) 
			select tstpitem.suite_id, tstpitem.tpi_id 
			from TEST_SUITE_TEST_PLAN_ITEM tstpitem
			order by tstpitem.suite_id,  tstpitem.test_plan_order;

-- ------------------------------------------------------------------------------
			-- Code for mysql database to find continuous orders for test suite test plan.
			-- Then we compute continuous orders for test-suite test plan items
			-- ------------------------------------------------------------------------------
			
			insert into TEMP_CONTINUOUS_SUITE_ITEM ( TP_ORDER, SUITE_ID, ITEM_ID, DUMMY)
			select if(@prevsuite=SUITE_ID, 
					  @rownum:=@rownum+1, 
				      @rownum:=0)
			TP_ORDER, SUITE_ID, ITEM_ID, (@prevsuite:=SUITE_ID)
			from
			TEMP_DISCONTINUOUS_SUITE_ITEM tdsi, (SELECT @rownum:=0) r, (SELECT @prevsuite:=0) s
			order by SUITE_ID, TP_ORDER;

-- ------------------------------------------------------------------------------
			-- And finally we set the right orders in the concerned table without forgetting to delete the help tables.
			-- ------------------------------------------------------------------------------
			
			update TEST_SUITE_TEST_PLAN_ITEM
			set TEST_PLAN_ORDER = 
				(select TEMP_CONTINUOUS_SUITE_ITEM.TP_ORDER
				from  TEMP_CONTINUOUS_SUITE_ITEM
				where TEMP_CONTINUOUS_SUITE_ITEM.SUITE_ID = TEST_SUITE_TEST_PLAN_ITEM.SUITE_ID
				and TEST_SUITE_TEST_PLAN_ITEM.TPI_ID = TEMP_CONTINUOUS_SUITE_ITEM.ITEM_ID)
			where 
				SUITE_ID in (
					select distinct tcsi2.SUITE_ID from TEMP_CONTINUOUS_SUITE_ITEM tcsi2
				);

-- ------------------------------------------------------------------------------
			-- drop the temp tables
			-- ------------------------------------------------------------------------------
			
			drop table TEMP_DISCONTINUOUS_SUITE_ITEM;

drop table TEMP_CONTINUOUS_SUITE_ITEM;

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('bsiri', 'cleaning test suite test plan ordering : some collections have non-contiguous elements. This 
		is due to issue #2454, because of shortcomings in the code in charge of deleting test cases and dependent 
		entities.
			It looks pretty much like tm-1.5.0...', NOW(), 'Custom SQL', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.6.0.xml', 'tm-1.6.0.issue-2454-01', '2.0.5', '3:2ea9a08fbaa337f4288ac6f9367a0efb', 221);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.6.0.xml::tm-1.6.0.issue-2454-02::bsiri::(Checksum: 3:758587cd787b305a0c669ef26dda62fb)
--  cleaning test suite test plan ordering : some collections have non-contiguous elements. This 
--  		is due to issue #2454, because of shortcomings in the code in charge of deleting test cases and dependent 
--  		entities.
--  			It looks pretty much like tm-1.5.0.issue-2261-01 (in the 1.5.1 changelog), except we're not dealing with order = null.
--  			
--  			Note : this script might already have been executed if at some point Squash 1.5.2 was used.
--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.6.0.xml::tm-1.6.0.issue-2454-02-postgresql::mpagnon::(Checksum: 3:bcb771c3465600909297d53a60ba9e43)
--  cleaning test suite test plan ordering : some collections have non-contiguous elements. This 
--  		is due to issue #2454, because of shortcomings in the code in charge of deleting test cases and dependent 
--  		entities.
--  			It looks pretty much like tm-1.5.0.issue-2261-01 (in the 1.5.1 changelog), except we're not dealing with order = null.
--  			
--  			Note : this script might already have been executed if at some point Squash 1.5.2 was used.
--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.6.0.xml::tm-1.6.0.issue-2474-01::bsiri::(Checksum: 3:707eb09e44214f4a6564dbcc2595bdff)
--  fixes the action test steps having no custom fields when copied from one project to another. Works for both mysql and h2.
insert into CUSTOM_FIELD_VALUE(bound_entity_id, bound_entity_type, cfb_id, value)
			select ats.test_step_id, 'TEST_STEP',  cfb.cfb_id, cf.default_value
			from ACTION_TEST_STEP ats
			inner join TEST_CASE_STEPS tcs on ats.test_step_id = tcs.step_id
			inner join TEST_CASE_LIBRARY_NODE tcln on tcs.test_case_id = tcln.tcln_id
			inner join CUSTOM_FIELD_BINDING cfb on tcln.project_id = cfb.bound_project_id
			inner join CUSTOM_FIELD cf on cfb.cf_id = cf.cf_id
			where cfb.bound_entity = 'TEST_STEP'
			and ats.test_step_id not in (
				select cfv2.bound_entity_id
				from CUSTOM_FIELD_VALUE cfv2
				inner join CUSTOM_FIELD_BINDING cfb2 on cfv2.cfb_id = cfb2.cfb_id
				where cfb2.bound_entity = 'TEST_STEP'
				and cfb2.bound_project_id = cfb.bound_project_id
			);

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('bsiri', 'fixes the action test steps having no custom fields when copied from one project to another. Works for both mysql and h2.', NOW(), 'Custom SQL', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.6.0.xml', 'tm-1.6.0.issue-2474-01', '2.0.5', '3:707eb09e44214f4a6564dbcc2595bdff', 222);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.6.0.xml::tm-1.6.0.issue-2485-01::flaurens::(Checksum: 3:b4c6769abca7e076b2b7c15e8a2e0cca)
--  replaces fk-constraint which wrongly cascades the deletion of denormalized field values
ALTER TABLE `DENORMALIZED_FIELD_VALUE` DROP FOREIGN KEY `fk_dfv_cfv_binding`;

ALTER TABLE `DENORMALIZED_FIELD_VALUE` ADD CONSTRAINT `fk_dfv_cfv_binding` FOREIGN KEY (`CFV_ID`) REFERENCES `CUSTOM_FIELD_VALUE` (`CFV_ID`) ON DELETE SET NULL;

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('flaurens', 'replaces fk-constraint which wrongly cascades the deletion of denormalized field values', NOW(), 'Drop Foreign Key Constraint, Add Foreign Key Constraint', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.6.0.xml', 'tm-1.6.0.issue-2485-01', '2.0.5', '3:b4c6769abca7e076b2b7c15e8a2e0cca', 223);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.6.0.xml::tm-1.6.0.issue-2536-01::flaurens::(Checksum: 3:bfc71563d410979038de2087efc95be1)
--  giving testrunners reading rights on projects
INSERT INTO `ACL_GROUP_PERMISSION` (`ACL_GROUP_ID`, `CLASS_ID`, `PERMISSION_MASK`) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.TestRunner'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.tm.domain.project.Project'), '1');

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('flaurens', 'giving testrunners reading rights on projects', NOW(), 'Insert Row', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.6.0.xml', 'tm-1.6.0.issue-2536-01', '2.0.5', '3:bfc71563d410979038de2087efc95be1', 224);

--  Release Database Lock
--  Release Database Lock
