-- *********************************************************************
-- 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.10.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.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-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.10.0.xml', 'tm-1.10.0', '2.0.5', '3:3ad9494d3bedeb8a0c6496ba0d60d083', 310);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/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 ALTER COLUMN  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-1.21/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', 311);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/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-1.21/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', 312);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/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 LONGVARCHAR;

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

ALTER TABLE TEST_AUTOMATION_SERVER ADD CREATED_ON TIMESTAMP;

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

ALTER TABLE TEST_AUTOMATION_SERVER ADD LAST_MODIFIED_ON TIMESTAMP DEFAULT null;

ALTER TABLE TEST_AUTOMATION_SERVER ADD MANUAL_SLAVE_SELECTION BOOLEAN DEFAULT FALSE NOT NULL;

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 ALTER COLUMN  NAME VARCHAR(50) NOT NULL;

ALTER TABLE TEST_AUTOMATION_SERVER ADD UNIQUE (NAME);

ALTER TABLE TEST_AUTOMATION_SERVER ALTER COLUMN  CREATED_ON TIMESTAMP 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-1.21/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', 313);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/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 GENERATED BY DEFAULT AS IDENTITY 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_tm_project FOREIGN KEY (TM_PROJECT_ID) REFERENCES PROJECT(PROJECT_ID), CONSTRAINT fk_ta_project_ta_server FOREIGN KEY (SERVER_ID) REFERENCES TEST_AUTOMATION_SERVER(SERVER_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 CONSTRAINT fk_automated_test_project;

DROP TABLE TEST_AUTOMATION_PROJECT;

ALTER TABLE PROTO_TEST_AUTOMATION_PROJECT RENAME TO 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-1.21/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', 314);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/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-1.21/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', 315);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/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-1.21/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', 316);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/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-1.21/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', 317);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/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-1.21/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', 318);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/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
-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/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
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
	      );

set @rn=-1;

set @prevsuite=0;

insert into TEMP_EXECUTION_EXTENDER_ORDER ( EXECUTION_EXTENDER_ORDER, SUITE_ID, EXTENDER_ID, DUMMY)
			select casewhen(@prevsuite = SUITE_ID, set(@rn, @rn+1),
			set(@rn, 0)) EXECUTION_EXTENDER_ORDER, SUITE_ID, EXTENDER_ID, set(@prevsuite, SUITE_ID)
			from ( 
	      	select cr.SUITE_ID, cr.EXTENDER_ID
	      	from AUTOMATED_EXECUTION_EXTENDER cr
	        order by cr.SUITE_ID, cr.EXTENDER_ID
	       	);

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

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-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.10.0.xml', 'tm-1.10.0.feature-3481-9', '2.0.5', '3:1823888e43c13a948e083334f63d7298', 319);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/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
