-- *********************************************************************
-- 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.5.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.5.0.xml::tm-1.5.0.00::mpagnon::(Checksum: 3:771816a2a8ccd3044f6b266fd3f73b69)
-- Update TM database version number
UPDATE CORE_CONFIG SET STR_KEY = 'squashtest.tm.database.version', VALUE = '1.5.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.5.0.xml', 'tm-1.5.0.00', '2.0.5', '3:771816a2a8ccd3044f6b266fd3f73b69', 187);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.5.0.xml::tm-1.5.0.feat-1596.02::bsiri::(Checksum: 3:772dac41f7a3501da93800e6abc69972)
-- dropping the temp table from changest tm-1.4.0.feat-1596.01
DROP TABLE EXECUTION_TEST_CASE_DATA;

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'dropping the temp table from changest tm-1.4.0.feat-1596.01', NOW(), 'Drop Table', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.5.0.xml', 'tm-1.5.0.feat-1596.02', '2.0.5', '3:772dac41f7a3501da93800e6abc69972', 188);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.5.0.xml::tm-1.5.0.00-issue-1838.01::mpagnon::(Checksum: 3:8a1c20d21263fa9fcec49bd647dfeaa0)
-- Update table custom-field-value so that all mandatory cuf have a value
update CUSTOM_FIELD_VALUE
			set VALUE = (
			select cf.DEFAULT_VALUE
			from CUSTOM_FIELD_BINDING cfb
			join CUSTOM_FIELD cf on cfb.CF_ID =
			cf.CF_ID
			where cfb.CFB_ID = CUSTOM_FIELD_VALUE.CFB_ID
			)
			where
			(CUSTOM_FIELD_VALUE.VALUE is null or
			LTRIM(RTRIM(CUSTOM_FIELD_VALUE.VALUE)) = '' )
			and exists (
			select *
			from CUSTOM_FIELD_BINDING cfb
			join CUSTOM_FIELD cf on cfb.CF_ID =
			cf.CF_ID
			where cfb.CFB_ID = CUSTOM_FIELD_VALUE.CFB_ID
			and cf.OPTIONAL =
			false);

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'Update table custom-field-value so that all mandatory cuf have a value', 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.5.0.xml', 'tm-1.5.0.00-issue-1838.01', '2.0.5', '3:8a1c20d21263fa9fcec49bd647dfeaa0', 189);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.5.0.xml::tm-1.5.0.00-feat-1390.03::bsiri::(Checksum: 3:c0563be4ca255d84a63edf5200f670d6)
-- adding cascade delete on the rendering locations when custom field bindings are removed (prune the orphans)
ALTER TABLE CUSTOM_FIELD_RENDERING_LOCATION DROP CONSTRAINT fk_cfb_render_loc_cfb;

ALTER TABLE CUSTOM_FIELD_RENDERING_LOCATION ADD CONSTRAINT fk_cfb_render_loc_cfb FOREIGN KEY (CFB_ID) REFERENCES CUSTOM_FIELD_BINDING (CFB_ID) ON DELETE CASCADE;

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'adding cascade delete on the rendering locations when custom field bindings are removed (prune the orphans)', 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.5.0.xml', 'tm-1.5.0.00-feat-1390.03', '2.0.5', '3:c0563be4ca255d84a63edf5200f670d6', 190);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.5.0.xml::tm-1.5.0.00-feat-1850.01::mpagnon::(Checksum: 3:474031fcb1dbc12086d9d94fccfa8567)
-- The table DENORMALIZED_FIELD_VALUE holds instances of a FIELD that are bound to instances of : 
-- 			executions, execution-steps	when they are created from test-cases or test-case-steps holding CUSTOM_FIELDS.
-- 			A "denormalized field value" is destroyed when the "denormalized field holder" instance is destroyed.
-- 						
-- 			Note that in this table the coupling between the "denormalized field value" and the "denormalized field holder" is week : it consists of a pair
-- 			of Type/ID (DENORMALIZED_FIELD_HOLDER_TYPE and DENORMALIZED_FIELD_HOLDER_ID) rather than a foreign key to the said entity, nor is true 
-- 			the opposite ( ie no FK from an execution to a denormalized field value).
CREATE TABLE DENORMALIZED_FIELD_VALUE (DFV_ID BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL, CFV_ID BIGINT, CODE VARCHAR(30) DEFAULT '' NOT NULL, DENORMALIZED_FIELD_HOLDER_ID BIGINT NOT NULL, DENORMALIZED_FIELD_HOLDER_TYPE VARCHAR(30) NOT NULL, INPUT_TYPE CHAR(30) NOT NULL, LABEL VARCHAR(255) DEFAULT '' NOT NULL, POSITION INT, VALUE VARCHAR(255), CONSTRAINT pk_denormalized_field PRIMARY KEY (DFV_ID));

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

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'The table DENORMALIZED_FIELD_VALUE holds instances of a FIELD that are bound to instances of : 
			executions, execution-steps	when they are created from test-cases or test-case-steps holding CUSTOM_FIELDS.
			A "denormalized field value" is destroyed ...', NOW(), 'Create Table, 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.5.0.xml', 'tm-1.5.0.00-feat-1850.01', '2.0.5', '3:474031fcb1dbc12086d9d94fccfa8567', 191);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.5.0.xml::tm-1.5.0.00-feat-1850.02::mpagnon::(Checksum: 3:6d7439f1e6df0371a72e9dc76e7f913e)
-- Create table for denormalized field rendering location
CREATE TABLE DENORMALIZED_FIELD_RENDERING_LOCATION (DFV_ID BIGINT NOT NULL, RENDERING_LOCATION VARCHAR(30) NOT NULL);

ALTER TABLE DENORMALIZED_FIELD_RENDERING_LOCATION ADD CONSTRAINT fk_dfb_render_loc_dfb FOREIGN KEY (DFV_ID) REFERENCES DENORMALIZED_FIELD_VALUE (DFV_ID) ON DELETE CASCADE;

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'Create table for denormalized field rendering location', NOW(), 'Create Table, 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.5.0.xml', 'tm-1.5.0.00-feat-1850.02', '2.0.5', '3:6d7439f1e6df0371a72e9dc76e7f913e', 192);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.5.0.xml::tm-1.5.0-feat-821::gfouquet::(Checksum: 3:eec7880021f82a34d47341ee4df5da78)
-- Change class names in ACL system
UPDATE ACL_CLASS SET CLASSNAME = 'org.squashtest.tm.domain.project.Project' WHERE CLASSNAME = 'org.squashtest.csp.tm.domain.project.Project';

UPDATE ACL_CLASS SET CLASSNAME = 'org.squashtest.tm.domain.requirement.RequirementLibrary' WHERE CLASSNAME = 'org.squashtest.csp.tm.domain.requirement.RequirementLibrary';

UPDATE ACL_CLASS SET CLASSNAME = 'org.squashtest.tm.domain.testcase.TestCaseLibrary' WHERE CLASSNAME = 'org.squashtest.csp.tm.domain.testcase.TestCaseLibrary';

UPDATE ACL_CLASS SET CLASSNAME = 'org.squashtest.tm.domain.campaign.CampaignLibrary' WHERE CLASSNAME = 'org.squashtest.csp.tm.domain.campaign.CampaignLibrary';

UPDATE ACL_CLASS SET CLASSNAME = 'org.squashtest.tm.domain.project.ProjectTemplate' WHERE CLASSNAME = 'org.squashtest.csp.tm.domain.project.ProjectTemplate';

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('gfouquet', 'Change class names in ACL system', NOW(), 'Update Data (x5)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.5.0.xml', 'tm-1.5.0-feat-821', '2.0.5', '3:eec7880021f82a34d47341ee4df5da78', 193);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.5.0.xml::tm-1.5.0.00-feat-1864.01::mpagnon::(Checksum: 3:17ff51de01f9ae0d7d952b07493eb882)
-- Create tables CORE_PARTY, CORE_TEAM
CREATE TABLE CORE_PARTY (PARTY_ID BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL, CONSTRAINT pk_party PRIMARY KEY (PARTY_ID));

CREATE TABLE CORE_TEAM (PARTY_ID BIGINT NOT NULL, NAME VARCHAR(50) NOT NULL, DESCRIPTION VARCHAR(255), CREATED_BY VARCHAR(50) NOT NULL, CREATED_ON TIMESTAMP NOT NULL, LAST_MODIFIED_ON TIMESTAMP DEFAULT null, LAST_MODIFIED_BY VARCHAR(50) DEFAULT null, CONSTRAINT pk_team PRIMARY KEY (PARTY_ID), CONSTRAINT fk_team_party FOREIGN KEY (PARTY_ID) REFERENCES CORE_PARTY(PARTY_ID));

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'Create tables CORE_PARTY, CORE_TEAM', NOW(), 'Create Table (x2)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.5.0.xml', 'tm-1.5.0.00-feat-1864.01', '2.0.5', '3:17ff51de01f9ae0d7d952b07493eb882', 194);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.5.0.xml::tm-1.5.0.00-feat-1864.02::mpagnon::(Checksum: 3:7ce1a4865ebee7c34cb6c05bf46bac63)
-- change name of USER_ID into PARTY_ID in CORE_USER, ACL_RESPONSIBILITY_SCOPE_ENTRY and CORE_GROUP_MEMBER. We need to drop FKs on user first.
ALTER TABLE CAMPAIGN_TEST_PLAN_ITEM DROP CONSTRAINT fk_camp_tp_user;

ALTER TABLE ITERATION_TEST_PLAN_ITEM DROP CONSTRAINT fk_itertestplan_user;

ALTER TABLE ACL_RESPONSIBILITY_SCOPE_ENTRY DROP CONSTRAINT FK_RESP_SCOPE_USER;

ALTER TABLE CORE_GROUP_MEMBER DROP CONSTRAINT FK_GRP_MEMBER_USR;

ALTER TABLE CORE_USER ALTER COLUMN ID RENAME TO PARTY_ID;

ALTER TABLE ACL_RESPONSIBILITY_SCOPE_ENTRY ALTER COLUMN USER_ID RENAME TO PARTY_ID;

ALTER TABLE CORE_GROUP_MEMBER ALTER COLUMN USER_ID RENAME TO PARTY_ID;

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'change name of USER_ID into PARTY_ID in CORE_USER, ACL_RESPONSIBILITY_SCOPE_ENTRY and CORE_GROUP_MEMBER. We need to drop FKs on user first.', NOW(), 'Drop Foreign Key Constraint (x4), Rename Column (x3)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.5.0.xml', 'tm-1.5.0.00-feat-1864.02', '2.0.5', '3:7ce1a4865ebee7c34cb6c05bf46bac63', 195);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.5.0.xml::tm-1.5.0.00-feat-1864.04::mpagnon::(Checksum: 3:a9a4dabaa3984599d89f8dd17f56397a)
-- Create a PARTY for each CORE_USER
insert into CORE_PARTY(PARTY_ID)
			select PARTY_ID from CORE_USER;

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'Create a PARTY for each CORE_USER', 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.5.0.xml', 'tm-1.5.0.00-feat-1864.04', '2.0.5', '3:a9a4dabaa3984599d89f8dd17f56397a', 196);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.5.0.xml::tm-1.5.0.00-feat-1864.05::mpagnon::(Checksum: 3:a0f6b56302dcf5de63c9bc4e0070bb26)
-- Replace reference on user with reference on party
ALTER TABLE ACL_RESPONSIBILITY_SCOPE_ENTRY ADD CONSTRAINT fk_arse_party FOREIGN KEY (PARTY_ID) REFERENCES CORE_PARTY (PARTY_ID);

ALTER TABLE CORE_GROUP_MEMBER ADD CONSTRAINT fk_group_member_party FOREIGN KEY (PARTY_ID) REFERENCES CORE_PARTY (PARTY_ID);

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'Replace reference on user with reference on party', NOW(), 'Add Foreign Key Constraint (x2)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.5.0.xml', 'tm-1.5.0.00-feat-1864.05', '2.0.5', '3:a0f6b56302dcf5de63c9bc4e0070bb26', 197);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.5.0.xml::tm-1.5.0.00-feat-1864.06::mpagnon::(Checksum: 3:be8ac6424bf708bce926f9a5422926f8)
-- Remove autoIncrement on core_user pk and add fk on Party table
ALTER TABLE CORE_USER DROP PRIMARY KEY;

ALTER TABLE CORE_USER ADD CONSTRAINT pk_core_user PRIMARY KEY (PARTY_ID);

ALTER TABLE CORE_USER ADD CONSTRAINT fk_core_user_party FOREIGN KEY (PARTY_ID) REFERENCES CORE_PARTY (PARTY_ID);

ALTER TABLE CAMPAIGN_TEST_PLAN_ITEM ADD CONSTRAINT fk_camp_tp_user FOREIGN KEY (USER_ID) REFERENCES CORE_USER (PARTY_ID);

ALTER TABLE ITERATION_TEST_PLAN_ITEM ADD CONSTRAINT fk_itertestplan_user FOREIGN KEY (USER_ID) REFERENCES CORE_USER (PARTY_ID);

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'Remove autoIncrement on core_user pk and add fk on Party table', NOW(), 'Drop Primary Key, Add Primary Key, Add Foreign Key Constraint (x3)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.5.0.xml', 'tm-1.5.0.00-feat-1864.06', '2.0.5', '3:be8ac6424bf708bce926f9a5422926f8', 198);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.5.0.xml::tm-1.5.0.00-feat-1864.03::mpagnon::(Checksum: 3:ac46d1c0c320671c71d759a31ef46cdf)
-- create table team member
CREATE TABLE CORE_TEAM_MEMBER (TEAM_ID BIGINT NOT NULL, USER_ID BIGINT NOT NULL, CONSTRAINT fk_team_member_user FOREIGN KEY (USER_ID) REFERENCES CORE_USER(PARTY_ID), CONSTRAINT fk_team_member_team FOREIGN KEY (TEAM_ID) REFERENCES CORE_TEAM(PARTY_ID));

CREATE UNIQUE INDEX DX_TEAM_MEMBER ON CORE_TEAM_MEMBER(TEAM_ID, USER_ID);

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'create table team member', NOW(), 'Create Table, Create Index', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.5.0.xml', 'tm-1.5.0.00-feat-1864.03', '2.0.5', '3:ac46d1c0c320671c71d759a31ef46cdf', 199);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.5.0.xml::tm-1.5.0.00-feat-1984.01::flaurens::(Checksum: 3:12d840fe62edf955008ba632c6aca4b2)
-- create table item_test_plan_test_suite
CREATE TABLE TEST_SUITE_TEST_PLAN_ITEM (TPI_ID BIGINT NOT NULL, SUITE_ID BIGINT NOT NULL, CONSTRAINT fk_test_suite_test_plan_item_test_plan_item FOREIGN KEY (TPI_ID) REFERENCES ITERATION_TEST_PLAN_ITEM(ITEM_TEST_PLAN_ID), CONSTRAINT fk_test_suite_test_plan_item_test_suite FOREIGN KEY (SUITE_ID) REFERENCES TEST_SUITE(ID));

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('flaurens', 'create table item_test_plan_test_suite', NOW(), 'Create Table', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.5.0.xml', 'tm-1.5.0.00-feat-1984.01', '2.0.5', '3:12d840fe62edf955008ba632c6aca4b2', 200);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.5.0.xml::tm-1.5.0.00-feat-1984.02::flaurens::(Checksum: 3:c7159fa321fd066634bed9777be2d7f9)
-- migrate data to table test_suite_test_plan_item
insert into TEST_SUITE_TEST_PLAN_ITEM (TPI_ID, SUITE_ID)
			select ITEM_TEST_PLAN_ID, TEST_SUITE from ITERATION_TEST_PLAN_ITEM
			where TEST_SUITE IS NOT NULL;

ALTER TABLE ITERATION_TEST_PLAN_ITEM DROP CONSTRAINT fk_itpi_testsuite;

ALTER TABLE ITERATION_TEST_PLAN_ITEM DROP COLUMN TEST_SUITE;

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('flaurens', 'migrate data to table test_suite_test_plan_item', NOW(), 'Custom SQL, Drop Foreign Key Constraint, Drop Column', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.5.0.xml', 'tm-1.5.0.00-feat-1984.02', '2.0.5', '3:c7159fa321fd066634bed9777be2d7f9', 201);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.5.0.xml::tm-1.5.0.00-feat-1902.01::bsiri::(Checksum: 3:1cecce2e50a491cae71fab04421659ac)
-- creating the tables and references for the workspace plugins
CREATE TABLE TEST_CASE_LIBRARY_PLUGINS (LIBRARY_ID BIGINT NOT NULL, PLUGIN_ID VARCHAR(100) NOT NULL, CONSTRAINT fk_plugin_tclib FOREIGN KEY (LIBRARY_ID) REFERENCES TEST_CASE_LIBRARY(TCL_ID));

CREATE TABLE REQUIREMENT_LIBRARY_PLUGINS (LIBRARY_ID BIGINT NOT NULL, PLUGIN_ID VARCHAR(100) NOT NULL, CONSTRAINT fk_plugin_rlib FOREIGN KEY (LIBRARY_ID) REFERENCES REQUIREMENT_LIBRARY(RL_ID));

CREATE TABLE CAMPAIGN_LIBRARY_PLUGINS (LIBRARY_ID BIGINT NOT NULL, PLUGIN_ID VARCHAR(100) NOT NULL, CONSTRAINT fk_plugin_clib FOREIGN KEY (LIBRARY_ID) REFERENCES CAMPAIGN_LIBRARY(CL_ID));

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'creating the tables and references for the workspace plugins', NOW(), 'Create Table (x3)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.5.0.xml', 'tm-1.5.0.00-feat-1902.01', '2.0.5', '3:1cecce2e50a491cae71fab04421659ac', 202);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.5.0.xml::tm-1.5.0.00-feat-1984.03::flaurens::(Checksum: 3:c97b3fea29e22fe2612c276f71ad62ed)
-- add column for ordering to table TEST_SUITE_TEST_PLAN_ITEM
ALTER TABLE TEST_SUITE_TEST_PLAN_ITEM ADD TEST_PLAN_ORDER INT;

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('flaurens', 'add column for ordering to table TEST_SUITE_TEST_PLAN_ITEM', 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.5.0.xml', 'tm-1.5.0.00-feat-1984.03', '2.0.5', '3:c97b3fea29e22fe2612c276f71ad62ed', 203);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.5.0.xml::tm-1.5.0.00-feat-2003.01::mpagnon::(Checksum: 3:da18fce611d98ef6ccef23d1daa0f901)
-- The new table is called  "REQUIREMENT_VERSION_COVERAGE"
ALTER TABLE TEST_CASE_VERIFIED_REQUIREMENT_VERSION DROP CONSTRAINT fk_verified_req_version;

ALTER TABLE TEST_CASE_VERIFIED_REQUIREMENT_VERSION DROP CONSTRAINT fk_verifying_test_case;

ALTER TABLE TEST_CASE_VERIFIED_REQUIREMENT_VERSION DROP CONSTRAINT uc_tc_verifies_req_version;

CREATE TABLE REQUIREMENT_VERSION_COVERAGE (REQUIREMENT_VERSION_COVERAGE_ID BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL, VERIFIED_REQ_VERSION_ID BIGINT, VERIFYING_TEST_CASE_ID BIGINT, CONSTRAINT pk_requirement_version_coverage PRIMARY KEY (REQUIREMENT_VERSION_COVERAGE_ID), CONSTRAINT fk_verified_req_version FOREIGN KEY (VERIFIED_REQ_VERSION_ID) REFERENCES REQUIREMENT_VERSION(RES_ID), CONSTRAINT fk_verifying_test_case FOREIGN KEY (VERIFYING_TEST_CASE_ID) REFERENCES TEST_CASE(TCLN_ID));

ALTER TABLE REQUIREMENT_VERSION_COVERAGE ALTER COLUMN  VERIFIED_REQ_VERSION_ID BIGINT NOT NULL;

ALTER TABLE REQUIREMENT_VERSION_COVERAGE ALTER COLUMN  VERIFYING_TEST_CASE_ID BIGINT NOT NULL;

ALTER TABLE REQUIREMENT_VERSION_COVERAGE ADD CONSTRAINT uc_tc_verifies_req_version UNIQUE (VERIFIED_REQ_VERSION_ID, VERIFYING_TEST_CASE_ID);

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'The new table is called  "REQUIREMENT_VERSION_COVERAGE"', NOW(), 'Drop Foreign Key Constraint (x2), Drop Unique Constraint, Create Table, Add Not-Null Constraint (x2), 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.5.0.xml', 'tm-1.5.0.00-feat-2003.01', '2.0.5', '3:da18fce611d98ef6ccef23d1daa0f901', 204);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.5.0.xml::tm-1.5.0.00-feat-2003.02::mpagnon::(Checksum: 3:eb25425ba209ef8c10037b4fbd1a13b7)
-- We migrate datas from TEST_CASE_VERIFIED_REQUIREMENT_VERSION to REQUIREMENT_VERSION_COVERAGE.
Insert into REQUIREMENT_VERSION_COVERAGE(VERIFIED_REQ_VERSION_ID, VERIFYING_TEST_CASE_ID)
			select VERIFIED_REQ_VERSION_ID, VERIFYING_TEST_CASE_ID from TEST_CASE_VERIFIED_REQUIREMENT_VERSION;

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'We migrate datas from TEST_CASE_VERIFIED_REQUIREMENT_VERSION to REQUIREMENT_VERSION_COVERAGE.', 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.5.0.xml', 'tm-1.5.0.00-feat-2003.02', '2.0.5', '3:eb25425ba209ef8c10037b4fbd1a13b7', 205);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.5.0.xml::tm-1.5.0.00-feat-2003.03::mpagnon::(Checksum: 3:19e90e356af9c11c040321085d046b44)
-- We drop the old TEST_CASE_VERIFIED_REQUIREMENT_VERSION table.
DROP TABLE TEST_CASE_VERIFIED_REQUIREMENT_VERSION;

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'We drop the old TEST_CASE_VERIFIED_REQUIREMENT_VERSION table.', NOW(), 'Drop Table', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.5.0.xml', 'tm-1.5.0.00-feat-2003.03', '2.0.5', '3:19e90e356af9c11c040321085d046b44', 206);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.5.0.xml::tm-1.5.0.00-feat-2003.04::mpagnon::(Checksum: 3:ea32093b375bc67eb08eb8af84f4c2d4)
-- Let's add a table VERIFYING_STEPS to hold the steps that are concerned by the requirement coverage
CREATE TABLE VERIFYING_STEPS (REQUIREMENT_VERSION_COVERAGE_ID BIGINT NOT NULL, TEST_STEP_ID BIGINT NOT NULL, CONSTRAINT fk_verifying_step_requirement_version_coverage FOREIGN KEY (REQUIREMENT_VERSION_COVERAGE_ID) REFERENCES REQUIREMENT_VERSION_COVERAGE(REQUIREMENT_VERSION_COVERAGE_ID), CONSTRAINT fk_verifying_step_action_test_step FOREIGN KEY (TEST_STEP_ID) REFERENCES ACTION_TEST_STEP(TEST_STEP_ID));

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'Let''s add a table VERIFYING_STEPS to hold the steps that are concerned by the requirement coverage', NOW(), 'Create Table', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.5.0.xml', 'tm-1.5.0.00-feat-2003.04', '2.0.5', '3:ea32093b375bc67eb08eb8af84f4c2d4', 207);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.5.0.xml::tm-1.5.0.00-feat-1966.01::bsiri::(Checksum: 3:c1bbbb48d574fd555457e24fed64876d)
-- fixing mysql text data type for Squash schema version 1.4.x and before (see issue 1966). Dev note : it would have been best to use
-- 		the tag <modifyColumn> instead of raw sql, because the xml could have been efficiently parsed by the dbdoc generator. 
-- 		However the dbdoc does not tell which datatype the columns anyway (for now), so let us say that the following raw sql is just fine.
-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.5.0.xml::tm-1.5.0.00-issue-2061.01::mpagnon::(Checksum: 3:60cf25d777ae63777f14959720352984)
-- Because imported test-cases did not have custom-field-values we need to fix the database.
insert into CUSTOM_FIELD_VALUE (BOUND_ENTITY_ID, BOUND_ENTITY_TYPE, CFB_ID, VALUE)
			select tc.TCLN_ID, 'TEST_CASE', binding.CFB_ID, cuf.DEFAULT_VALUE
			from TEST_CASE tc
			join TEST_CASE_LIBRARY_NODE tcln ON tc.TCLN_ID = tcln.TCLN_ID
			join PROJECT project ON tcln.PROJECT_ID = project.PROJECT_ID
			join CUSTOM_FIELD_BINDING binding ON binding.BOUND_PROJECT_ID = project.PROJECT_ID
			join CUSTOM_FIELD cuf ON binding.CF_ID = cuf.CF_ID
			where not exists (
				select * from CUSTOM_FIELD_VALUE val 
				where val.BOUND_ENTITY_ID = tc.TCLN_ID 
				and val.BOUND_ENTITY_TYPE='TEST_CASE' 
				and val.CFB_ID = binding.CFB_ID
			);

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'Because imported test-cases did not have custom-field-values we need to fix the database.', 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.5.0.xml', 'tm-1.5.0.00-issue-2061.01', '2.0.5', '3:60cf25d777ae63777f14959720352984', 208);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.5.0.xml::tm-1.5.0.00-issue-2146.01::mpagnon::(Checksum: 3:20c02e1368dc82ca88d244545b622cc7)
-- also fix for [Issue 1895]
UPDATE REQUIREMENT_PROPERTY_CHANGE
			SET PROPERTY_NAME = ''
			WHERE PROPERTY_NAME is null;

ALTER TABLE REQUIREMENT_PROPERTY_CHANGE ALTER COLUMN  PROPERTY_NAME VARCHAR(100) NOT NULL;

UPDATE REQUIREMENT_LARGE_PROPERTY_CHANGE
			SET PROPERTY_NAME = ''
			WHERE PROPERTY_NAME is null;

ALTER TABLE REQUIREMENT_LARGE_PROPERTY_CHANGE ALTER COLUMN  PROPERTY_NAME VARCHAR(100) NOT NULL;

UPDATE EXECUTION
			SET REFERENCE = ''
			WHERE REFERENCE is null;

ALTER TABLE EXECUTION ALTER COLUMN  REFERENCE VARCHAR(50) NOT NULL;

ALTER TABLE EXECUTION ALTER COLUMN  REFERENCE SET DEFAULT '';

UPDATE EXECUTION
			SET PREREQUISITE = ''
			WHERE PREREQUISITE is null;

ALTER TABLE EXECUTION ALTER COLUMN  PREREQUISITE LONGVARCHAR NOT NULL;

UPDATE REQUIREMENT_VERSION
			SET REFERENCE = ''
			WHERE REFERENCE is null;

ALTER TABLE REQUIREMENT_VERSION ALTER COLUMN  REFERENCE VARCHAR(50) NOT NULL;

ALTER TABLE REQUIREMENT_VERSION ALTER COLUMN  REFERENCE SET DEFAULT '';

UPDATE TEST_CASE
			SET PREREQUISITE= ''
			WHERE PREREQUISITE is null;

ALTER TABLE TEST_CASE ALTER COLUMN  PREREQUISITE LONGVARCHAR NOT NULL;

UPDATE TEST_CASE
			SET REFERENCE = ''
			WHERE REFERENCE is null;

ALTER TABLE TEST_CASE ALTER COLUMN  REFERENCE VARCHAR(50) NOT NULL;

ALTER TABLE TEST_CASE ALTER COLUMN  REFERENCE SET DEFAULT '';

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'also fix for [Issue 1895]', NOW(), 'Custom SQL, Add Not-Null Constraint, Custom SQL, Add Not-Null Constraint, Custom SQL, Add Not-Null Constraint, Add Default Value, Custom SQL, Add Not-Null Constraint, Custom SQL, Add Not-Null Constraint, Add Default Value, Custom SQL, Add Not-Null Cons...', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.5.0.xml', 'tm-1.5.0.00-issue-2146.01', '2.0.5', '3:20c02e1368dc82ca88d244545b622cc7', 209);

-- Release Database Lock
-- Release Database Lock
