-- *********************************************************************
-- 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.6.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.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-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.6.0.xml', 'tm-1.6.0', '2.0.5', '3:44fa0971a21cf0ff8ba72bac20568984', 212);

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

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/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 GENERATED BY DEFAULT AS IDENTITY NOT NULL, NAME VARCHAR(255) DEFAULT '' NOT NULL, TEST_CASE_ID BIGINT NOT NULL, DESCRIPTION LONGVARCHAR 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-1.21/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', 214);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/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 GENERATED BY DEFAULT AS IDENTITY 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 GENERATED BY DEFAULT AS IDENTITY 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-1.21/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', 215);

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

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

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

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

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/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.
-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/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.
-- ------------------------------------------------------------------------------
			-- 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 h2 database to find continuous orders for test suite test plan.
			-- Then we compute continuous orders for test-suite test plan items
			-- ------------------------------------------------------------------------------
			
			set @rn=-1;

set @prevsuite=0;

insert into TEMP_CONTINUOUS_SUITE_ITEM ( TP_ORDER, SUITE_ID, ITEM_ID,
			DUMMY)
			select casewhen(@prevsuite = SUITE_ID, set(@rn, @rn+1),
			set(@rn, 0)) TP_ORDER, SUITE_ID, ITEM_ID, set(@prevsuite, SUITE_ID)
			from
			TEMP_DISCONTINUOUS_SUITE_ITEM tdsi
			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-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.6.0.xml', 'tm-1.6.0.issue-2454-02', '2.0.5', '3:758587cd787b305a0c669ef26dda62fb', 220);

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

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

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

-- Release Database Lock
-- Release Database Lock
