--  *********************************************************************
--  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.10.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.10.0.xml::tm-1.10.0::bsiri::(Checksum: 3:3ad9494d3bedeb8a0c6496ba0d60d083)
--  Update TM database version number
UPDATE `CORE_CONFIG` SET `VALUE` = '1.10.0' WHERE STR_KEY = 'squashtest.tm.database.version';

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('bsiri', '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.10.0.xml', 'tm-1.10.0', '2.0.5', '3:3ad9494d3bedeb8a0c6496ba0d60d083', 311);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.10.0.xml::tm-1.10.0.feature-3481-1::bsiri::(Checksum: 3:59e2894568fa1bd5fff1948666ea333e)
--  before the cleanup, we need to relax some constraints
ALTER TABLE `AUTOMATED_EXECUTION_EXTENDER` MODIFY `TEST_ID` BIGINT NULL;

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('bsiri', 'before the cleanup, we need to relax some constraints', NOW(), 'Drop Not-Null Constraint', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.10.0.xml', 'tm-1.10.0.feature-3481-1', '2.0.5', '3:59e2894568fa1bd5fff1948666ea333e', 312);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.10.0.xml::tm-1.10.0.feature-3481-2::bsiri::(Checksum: 3:8bcc80e133df026105b743b08a0a5ccc)
--  cleaning up unused entities
create temporary table UNUSED_TA_PROJECT as 
  			(
  				select taproj.project_id as project_id
  				from TEST_AUTOMATION_PROJECT taproj 
  				where taproj.project_id not in (
  					select ta_project_id from TM_TA_PROJECTS
  				)
  			);

update AUTOMATED_EXECUTION_EXTENDER extender
  			set result_url = null,
  				test_id = null
  			where extender.test_id in (
  				select tatest.test_id 
  				from  AUTOMATED_TEST tatest 
  				inner join UNUSED_TA_PROJECT unused on tatest.project_id = unused.project_id 
  			);

update TEST_CASE tc 
  			set ta_test = null
  			where tc.ta_test in (
  				select tatest.test_id 
  				from AUTOMATED_TEST tatest 
  				inner join UNUSED_TA_PROJECT unused on tatest.project_id = unused.project_id 
  			);

delete from AUTOMATED_TEST  
			where project_id in (
				select unused.project_id from UNUSED_TA_PROJECT unused
			);

delete from TEST_AUTOMATION_PROJECT 
  			where project_id in (
  				select p.project_id from UNUSED_TA_PROJECT p
  			);

delete from TEST_AUTOMATION_SERVER 
  			where server_id not in (
  				select distinct taproj.server_id 
  				from TM_TA_PROJECTS binding 
  				inner join TEST_AUTOMATION_PROJECT taproj on binding.ta_project_id = taproj.project_id 
  			);

drop table UNUSED_TA_PROJECT;

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('bsiri', 'cleaning up unused entities', NOW(), 'Custom SQL (x7)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.10.0.xml', 'tm-1.10.0.feature-3481-2', '2.0.5', '3:8bcc80e133df026105b743b08a0a5ccc', 313);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.10.0.xml::tm-1.10.0.feature-3481-3::bsiri::(Checksum: 3:656ef2247742ad5390b1dbd308c132b9)
--  The test automation servers are now 'auditable' (dates and authors of creation/modification are recorded) and has 
--  		several other attributes
ALTER TABLE `TEST_AUTOMATION_SERVER` ADD `NAME` VARCHAR(50);

ALTER TABLE `TEST_AUTOMATION_SERVER` ADD `DESCRIPTION` LONGTEXT;

ALTER TABLE `TEST_AUTOMATION_SERVER` ADD `CREATED_BY` VARCHAR(50) DEFAULT '(nobody)';

ALTER TABLE `TEST_AUTOMATION_SERVER` ADD `CREATED_ON` DATETIME;

ALTER TABLE `TEST_AUTOMATION_SERVER` ADD `LAST_MODIFIED_BY` VARCHAR(50) DEFAULT null;

ALTER TABLE `TEST_AUTOMATION_SERVER` ADD `LAST_MODIFIED_ON` DATETIME DEFAULT null;

ALTER TABLE `TEST_AUTOMATION_SERVER` ADD `MANUAL_SLAVE_SELECTION` TINYINT(1) NOT NULL DEFAULT 0;

update TEST_AUTOMATION_SERVER
			set name = concat_ws('-', base_url, server_id);

update TEST_AUTOMATION_SERVER
			set CREATED_ON = NOW();

ALTER TABLE `TEST_AUTOMATION_SERVER` MODIFY `NAME` VARCHAR(50) NOT NULL;

ALTER TABLE `TEST_AUTOMATION_SERVER` ADD UNIQUE (`NAME`);

ALTER TABLE `TEST_AUTOMATION_SERVER` MODIFY `CREATED_ON` DATETIME NOT NULL;

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('bsiri', 'The test automation servers are now ''auditable'' (dates and authors of creation/modification are recorded) and has 
		several other attributes', NOW(), 'Add Column, Custom SQL (x2), Add Not-Null Constraint, Add Unique Constraint, Add Not-Null Constraint', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.10.0.xml', 'tm-1.10.0.feature-3481-3', '2.0.5', '3:656ef2247742ad5390b1dbd308c132b9', 314);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.10.0.xml::tm-1.10.0.feature-3481-4::bsiri::(Checksum: 3:d9951c7511173bf2418ccc3918d3cbe6)
--  The associative table TM_TA_PROJECTS is totally re-purposed as TEST_AUTOMATION_PROJECT 
--  			(and the former table is ditched).
CREATE TABLE `PROTO_TEST_AUTOMATION_PROJECT` (`TA_PROJECT_ID` BIGINT AUTO_INCREMENT NOT NULL, `TM_PROJECT_ID` BIGINT NOT NULL, `LABEL` VARCHAR(50) NOT NULL, `REMOTE_NAME` VARCHAR(50) NOT NULL, `SERVER_ID` BIGINT NOT NULL, `EXECUTION_ENVIRONMENTS` VARCHAR(255) DEFAULT '' NOT NULL, `FORMER_ID` BIGINT NOT NULL, CONSTRAINT `PK_PROTO_TEST_AUTOMATION_PROJECT` PRIMARY KEY (`TA_PROJECT_ID`), CONSTRAINT `fk_ta_project_ta_server` FOREIGN KEY (`SERVER_ID`) REFERENCES TEST_AUTOMATION_SERVER(SERVER_ID), CONSTRAINT `fk_ta_project_tm_project` FOREIGN KEY (`TM_PROJECT_ID`) REFERENCES PROJECT(PROJECT_ID));

insert into PROTO_TEST_AUTOMATION_PROJECT(TM_PROJECT_ID, LABEL, REMOTE_NAME, SERVER_ID, FORMER_ID) 
			select binding.tm_project_id, 
				   concat_ws('-', proj.name, binding.tm_project_id, binding.ta_project_id),
				   proj.name,
				   proj.server_id, 
				   proj.project_id
			from TM_TA_PROJECTS binding
			inner join TEST_AUTOMATION_PROJECT proj on binding.ta_project_id = proj.project_id;

DROP TABLE `TM_TA_PROJECTS`;

ALTER TABLE `AUTOMATED_TEST` DROP FOREIGN KEY `fk_automated_test_project`;

DROP TABLE `TEST_AUTOMATION_PROJECT`;

ALTER TABLE `PROTO_TEST_AUTOMATION_PROJECT` RENAME `TEST_AUTOMATION_PROJECT`;

update AUTOMATED_TEST  
			set PROJECT_ID = (
				select tap.TA_PROJECT_ID 
				from TEST_AUTOMATION_PROJECT tap
                  inner join TEST_CASE_LIBRARY_NODE tcln on tcln.PROJECT_ID = tap.TM_PROJECT_ID
                  inner join TEST_CASE tc on tc.TCLN_ID = tcln.TCLN_ID
				where PROJECT_ID = tap.FORMER_ID
				  and TEST_ID = tc.TA_TEST
				group by tc.TA_TEST, tap.TA_PROJECT_ID 
			);

ALTER TABLE `TEST_AUTOMATION_PROJECT` DROP COLUMN `FORMER_ID`;

ALTER TABLE `AUTOMATED_TEST` ADD CONSTRAINT `fk_automated_test_project` FOREIGN KEY (`PROJECT_ID`) REFERENCES `TEST_AUTOMATION_PROJECT` (`TA_PROJECT_ID`);

ALTER TABLE `TEST_AUTOMATION_PROJECT` ADD CONSTRAINT `uniq_tm_ta_project_job` UNIQUE (`TM_PROJECT_ID`, `REMOTE_NAME`, `SERVER_ID`);

ALTER TABLE `TEST_AUTOMATION_PROJECT` ADD CONSTRAINT `uniq_tm_ta_project_label` UNIQUE (`TM_PROJECT_ID`, `LABEL`);

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('bsiri', 'The associative table TM_TA_PROJECTS is totally re-purposed as TEST_AUTOMATION_PROJECT 
			(and the former table is ditched).', NOW(), 'Create Table, Custom SQL, Drop Table, Drop Foreign Key Constraint, Drop Table, Rename Table, Custom SQL, Drop Column, Add Foreign Key Constraint, Add Unique Constraint (x2)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.10.0.xml', 'tm-1.10.0.feature-3481-4', '2.0.5', '3:d9951c7511173bf2418ccc3918d3cbe6', 315);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.10.0.xml::tm-1.10.0.feature-3481-5::bsiri::(Checksum: 3:616a59d39628998dd7528bcabb3d0eb6)
--  A TM project is now hard-bound to a TA server
ALTER TABLE `PROJECT` ADD `TA_SERVER_ID` BIGINT;

ALTER TABLE `PROJECT` ADD CONSTRAINT `tm_project_ta_server` FOREIGN KEY (`TA_SERVER_ID`) REFERENCES `TEST_AUTOMATION_SERVER` (`SERVER_ID`);

ALTER TABLE `PROJECT` DROP COLUMN `TEST_AUTOMATION_ENABLED`;

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('bsiri', 'A TM project is now hard-bound to a TA server', NOW(), 'Add Column, Drop Column', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.10.0.xml', 'tm-1.10.0.feature-3481-5', '2.0.5', '3:616a59d39628998dd7528bcabb3d0eb6', 316);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.10.0.xml::tm-1.10.0.feature-3481-6::bsiri::(Checksum: 3:42bb203e9e2c144c5f898a60cebc018d)
--  migrating existing data to the new schema
update PROJECT 
			set ta_server_id = (
				select max(server.server_id) 
				from TEST_AUTOMATION_SERVER server 
				inner join TEST_AUTOMATION_PROJECT taproj on server.server_id = taproj.server_id
				where taproj.tm_project_id = project_id
			);

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('bsiri', 'migrating existing data to the new schema', NOW(), 'Custom SQL', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.10.0.xml', 'tm-1.10.0.feature-3481-6', '2.0.5', '3:42bb203e9e2c144c5f898a60cebc018d', 317);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.10.0.xml::tm-1.10.0.feature-3481-6::gfouquet::(Checksum: 3:8d4f03a4e80ae529bf67a2028ff9d008)
ALTER TABLE `AUTOMATED_EXECUTION_EXTENDER` ADD `NODE_NAME` VARCHAR(50) DEFAULT '';

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('gfouquet', '', NOW(), 'Add Column', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.10.0.xml', 'tm-1.10.0.feature-3481-6', '2.0.5', '3:8d4f03a4e80ae529bf67a2028ff9d008', 318);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.10.0.xml::tm-1.10.0.feature-3481-7::flaurens::(Checksum: 3:54b38dcbdbe6823bfda9c18d4df42597)
ALTER TABLE `AUTOMATED_EXECUTION_EXTENDER` ADD `EXECUTION_EXTENDER_ORDER` BIGINT;

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('flaurens', '', NOW(), 'Add Column', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.10.0.xml', 'tm-1.10.0.feature-3481-7', '2.0.5', '3:54b38dcbdbe6823bfda9c18d4df42597', 319);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.10.0.xml::tm-1.10.0.feature-3481-8::flaurens::(Checksum: 3:cdb6aacd59519443b1ff399c56853178)
--  Creating default order which is identical with the alphabetical order
create table TEMP_EXECUTION_EXTENDER_ORDER(
	        EXECUTION_EXTENDER_ORDER BIGINT NOT NULL, 
	        SUITE_ID BIGINT NOT NULL, 
	        EXTENDER_ID BIGINT NOT NULL, 
	        DUMMY BIGINT NOT NULL
	      );

insert into TEMP_EXECUTION_EXTENDER_ORDER (EXECUTION_EXTENDER_ORDER, SUITE_ID, EXTENDER_ID, DUMMY)
	      select 
	        if(@prevtcs=SUITE_ID, @rownum:=@rownum+1, @rownum:=0) as RANK,
	        clnr.SUITE_ID, 
	        clnr.EXTENDER_ID, 
	        (@prevtcs:=SUITE_ID)
	      from ( 
	      	select cr.SUITE_ID, cr.EXTENDER_ID
	      	from AUTOMATED_EXECUTION_EXTENDER cr
	        order by cr.SUITE_ID, cr.EXTENDER_ID
	       ) as clnr,
	        (SELECT @rownum:=0) r, 
	        (SELECT @prevtcs:=0) s;

update AUTOMATED_EXECUTION_EXTENDER
	      set EXECUTION_EXTENDER_ORDER = (
	        select TEMP_EXECUTION_EXTENDER_ORDER.EXECUTION_EXTENDER_ORDER
	        from TEMP_EXECUTION_EXTENDER_ORDER
	        where TEMP_EXECUTION_EXTENDER_ORDER.SUITE_ID = AUTOMATED_EXECUTION_EXTENDER.SUITE_ID
	          and TEMP_EXECUTION_EXTENDER_ORDER.EXTENDER_ID = AUTOMATED_EXECUTION_EXTENDER.EXTENDER_ID
	      );

drop table TEMP_EXECUTION_EXTENDER_ORDER;

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('flaurens', 'Creating default order which is identical with the alphabetical order', NOW(), 'Custom SQL', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.10.0.xml', 'tm-1.10.0.feature-3481-8', '2.0.5', '3:cdb6aacd59519443b1ff399c56853178', 320);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.10.0.xml::tm-1.10.0.feature-3481-9::flaurens::(Checksum: 3:1823888e43c13a948e083334f63d7298)
--  Creating default order which is identical with the alphabetical order
--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.10.0.xml::tm-1.10.0.feature-3481-postgresql::mpagnon::(Checksum: 3:d392e5a0756e8dd888c9b174fd00fba6)
--  Creating default order which is identical with the alphabetical order
--  Release Database Lock
--  Release Database Lock
