--  *********************************************************************
--  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.5.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.5.1.xml::tm-1.5.1::mpagnon::(Checksum: 3:fbb40866e9b269ec1b80b00d1c53f45a)
--  Update TM database version number
UPDATE `CORE_CONFIG` SET `STR_KEY` = 'squashtest.tm.database.version', `VALUE` = '1.5.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.5.1.xml', 'tm-1.5.1', '2.0.5', '3:fbb40866e9b269ec1b80b00d1c53f45a', 211);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.5.1.xml::tm-1.5.0.issue-2261-01::mpagnon,bsiri::(Checksum: 3:87b6ca96153ba3bbe35ffd0891b70653)
--  First we create help table that will help us match the iteration test plan orders with the test-suite's test plan items.
-- ------------------------------------------------------------------------------
			-- 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 
			where
				tstpitem.SUITE_ID in ( 
					select distinct tstpi2.suite_id 
					from TEST_SUITE_TEST_PLAN_ITEM tstpi2
					where tstpi2.TEST_PLAN_ORDER is null
				);

-- ------------------------------------------------------------------------------
			-- 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;

-- ------------------------------------------------------------------------------
			-- enforce the security on that column
			-- ------------------------------------------------------------------------------
			
			alter table TEST_SUITE_TEST_PLAN_ITEM modify TEST_PLAN_ORDER INT NOT NULL;

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('mpagnon,bsiri', 'First we create help table that will help us match the iteration test plan orders with the test-suite''s test plan items.', NOW(), 'Custom SQL', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.5.1.xml', 'tm-1.5.0.issue-2261-01', '2.0.5', '3:87b6ca96153ba3bbe35ffd0891b70653', 212);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.5.1.xml::tm-1.5.0.issue-2261-02-postgresql::mpagnon,bsiri::(Checksum: 3:ae502a919be31d44b91ceff5c97fe8ab)
--  enforce the security on that column
--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.5.1.xml::tm-1.5.0.issue-2261-02::mpagnon,bsiri::(Checksum: 3:3740e86f3d5046ef904a254c4b16b400)
--  First we create help table that will help us match the iteration test plan orders with the test-suite's test plan items.
--  Release Database Lock
--  Release Database Lock
