--  *********************************************************************
--  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.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.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/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/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/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/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/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/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 FOREIGN KEY `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/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/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 AUTO_INCREMENT NOT NULL, `CFV_ID` BIGINT NULL, `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 NULL, `VALUE` VARCHAR(255) NULL, 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/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/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/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/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/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/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 AUTO_INCREMENT 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) NULL, `CREATED_BY` VARCHAR(50) NOT NULL, `CREATED_ON` DATETIME NOT NULL, `LAST_MODIFIED_ON` DATETIME DEFAULT null NULL, `LAST_MODIFIED_BY` VARCHAR(50) DEFAULT null 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/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/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 FOREIGN KEY `fk_camp_tp_user`;

ALTER TABLE `ITERATION_TEST_PLAN_ITEM` DROP FOREIGN KEY `fk_itertestplan_user`;

ALTER TABLE `ACL_RESPONSIBILITY_SCOPE_ENTRY` DROP FOREIGN KEY `FK_RESP_SCOPE_USER`;

ALTER TABLE `CORE_GROUP_MEMBER` DROP FOREIGN KEY `FK_GRP_MEMBER_USR`;

ALTER TABLE `CORE_USER` CHANGE `ID` `PARTY_ID` BIGINT;

ALTER TABLE `ACL_RESPONSIBILITY_SCOPE_ENTRY` CHANGE `USER_ID` `PARTY_ID` BIGINT;

ALTER TABLE `CORE_GROUP_MEMBER` CHANGE `USER_ID` `PARTY_ID` BIGINT;

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/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/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/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/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/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/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 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/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/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/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/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/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/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 FOREIGN KEY `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/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/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/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/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/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/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 FOREIGN KEY `fk_verified_req_version`;

ALTER TABLE `TEST_CASE_VERIFIED_REQUIREMENT_VERSION` DROP FOREIGN KEY `fk_verifying_test_case`;

ALTER TABLE `TEST_CASE_VERIFIED_REQUIREMENT_VERSION` DROP KEY `uc_tc_verifies_req_version`;

CREATE TABLE `REQUIREMENT_VERSION_COVERAGE` (`REQUIREMENT_VERSION_COVERAGE_ID` BIGINT AUTO_INCREMENT NOT NULL, `VERIFIED_REQ_VERSION_ID` BIGINT NULL, `VERIFYING_TEST_CASE_ID` BIGINT NULL, 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` MODIFY `VERIFIED_REQ_VERSION_ID` BIGINT NOT NULL;

ALTER TABLE `REQUIREMENT_VERSION_COVERAGE` MODIFY `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/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/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/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/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/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/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_action_test_step` FOREIGN KEY (`TEST_STEP_ID`) REFERENCES ACTION_TEST_STEP(TEST_STEP_ID), CONSTRAINT `fk_verifying_step_requirement_version_coverage` FOREIGN KEY (`REQUIREMENT_VERSION_COVERAGE_ID`) REFERENCES REQUIREMENT_VERSION_COVERAGE(REQUIREMENT_VERSION_COVERAGE_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/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/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.
ALTER TABLE TEST_CASE_LIBRARY_NODE MODIFY DESCRIPTION longtext;

ALTER TABLE CAMPAIGN_LIBRARY_NODE MODIFY DESCRIPTION longtext;

ALTER TABLE ITERATION MODIFY DESCRIPTION longtext;

ALTER TABLE TEST_SUITE MODIFY DESCRIPTION longtext;

ALTER TABLE PROJECT MODIFY DESCRIPTION longtext;

ALTER TABLE TEST_CASE MODIFY PREREQUISITE longtext;

ALTER TABLE RESOURCE MODIFY DESCRIPTION longtext;

ALTER TABLE REQUIREMENT_LARGE_PROPERTY_CHANGE MODIFY OLD_VALUE longtext;

ALTER TABLE REQUIREMENT_LARGE_PROPERTY_CHANGE MODIFY NEW_VALUE longtext;

ALTER TABLE EXECUTION MODIFY DESCRIPTION longtext;

ALTER TABLE EXECUTION MODIFY PREREQUISITE longtext;

ALTER TABLE EXECUTION_STEP MODIFY ACTION longtext;

ALTER TABLE EXECUTION_STEP MODIFY EXPECTED_RESULT longtext;

ALTER TABLE EXECUTION_STEP MODIFY COMMENT longtext;

ALTER TABLE ACTION_TEST_STEP MODIFY ACTION longtext;

ALTER TABLE ACTION_TEST_STEP MODIFY EXPECTED_RESULT longtext;

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('bsiri', '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. 
...', NOW(), 'Custom SQL', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.5.0.xml', 'tm-1.5.0.00-feat-1966.01', '2.0.5', '3:c1bbbb48d574fd555457e24fed64876d', 208);

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

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/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` MODIFY `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` MODIFY `PROPERTY_NAME` VARCHAR(100) NOT NULL;

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

ALTER TABLE `EXECUTION` MODIFY `REFERENCE` VARCHAR(50) NOT NULL;

ALTER TABLE `EXECUTION` ALTER `REFERENCE` SET DEFAULT '';

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

ALTER TABLE `EXECUTION` MODIFY `PREREQUISITE` LONGTEXT NOT NULL;

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

ALTER TABLE `REQUIREMENT_VERSION` MODIFY `REFERENCE` VARCHAR(50) NOT NULL;

ALTER TABLE `REQUIREMENT_VERSION` ALTER `REFERENCE` SET DEFAULT '';

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

ALTER TABLE `TEST_CASE` MODIFY `PREREQUISITE` LONGTEXT NOT NULL;

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

ALTER TABLE `TEST_CASE` MODIFY `REFERENCE` VARCHAR(50) NOT NULL;

ALTER TABLE `TEST_CASE` ALTER `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/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', 210);

--  Release Database Lock
--  Release Database Lock
