--  *********************************************************************
--  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.1.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.1.0.xml::tm-1.1.0.01::gfouquet::(Checksum: 3:7b812bb021cbb1145febc9bad5624b79)
--  Corrects status to BLOCKED in EXECUTION table
UPDATE `EXECUTION` SET `EXECUTION_STATUS` = 'BLOCKED' WHERE EXECUTION_STATUS='BLOQUED';

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('gfouquet', 'Corrects status to BLOCKED in EXECUTION table', NOW(), 'Update Data', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml', 'tm-1.1.0.01', '2.0.5', '3:7b812bb021cbb1145febc9bad5624b79', 78);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml::tm-1.1.0.02::gfouquet::(Checksum: 3:e0a5f002e81988762e6b1b2d329f84b1)
--  Corrects status to BLOCKED in EXECUTION_STEP table
UPDATE `EXECUTION_STEP` SET `EXECUTION_STATUS` = 'BLOCKED' WHERE EXECUTION_STATUS='BLOQUED';

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('gfouquet', 'Corrects status to BLOCKED in EXECUTION_STEP table', NOW(), 'Update Data', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml', 'tm-1.1.0.02', '2.0.5', '3:e0a5f002e81988762e6b1b2d329f84b1', 79);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml::tm-1.1.0.03::gfouquet::(Checksum: 3:8ed2c90c46fdd119a126dfce9311bf50)
--  Corrects status to BLOCKED in EXECUTION_STEP table
UPDATE `ITERATION_TEST_PLAN_ITEM` SET `EXECUTION_STATUS` = 'BLOCKED' WHERE EXECUTION_STATUS='BLOQUED';

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('gfouquet', 'Corrects status to BLOCKED in EXECUTION_STEP table', NOW(), 'Update Data', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml', 'tm-1.1.0.03', '2.0.5', '3:8ed2c90c46fdd119a126dfce9311bf50', 80);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml::tm-1.1.0.04::gfouquet::(Checksum: 3:602af0823f8965c71e6f8ba05124a2cd)
--  Adds an importance to test cases
ALTER TABLE `TEST_CASE` ADD `IMPORTANCE` VARCHAR(20) NOT NULL DEFAULT 'LOW';

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('gfouquet', 'Adds an importance to test cases', NOW(), 'Add Column', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml', 'tm-1.1.0.04', '2.0.5', '3:602af0823f8965c71e6f8ba05124a2cd', 81);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml::tm-1.1.0.05::gfouquet::(Checksum: 3:d600b40e7b3fb8526cd8184109d2dfbe)
--  Adds a auto compute importance property to test cases
ALTER TABLE `TEST_CASE` ADD `IMPORTANCE_AUTO` TINYINT(1) NOT NULL DEFAULT 0;

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('gfouquet', 'Adds a auto compute importance property to test cases', NOW(), 'Add Column', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml', 'tm-1.1.0.05', '2.0.5', '3:d600b40e7b3fb8526cd8184109d2dfbe', 82);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml::tm-1.1.0.06::gfouquet::(Checksum: 3:2e544b51db99e2b730f9bac869c54861)
--  Adds a TM database version number
INSERT INTO `CORE_CONFIG` (`STR_KEY`, `VALUE`) VALUES ('squashtest.tm.database.version', '1.1.0');

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('gfouquet', 'Adds a TM database version number', NOW(), 'Insert Row', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml', 'tm-1.1.0.06', '2.0.5', '3:2e544b51db99e2b730f9bac869c54861', 83);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml::tm-1.1.0.07::gfouquet::(Checksum: 3:7aeec9343a5b8e86f987d291a5edb778)
--  A tree node organizes resources. These are stored in the RESOURCE table
CREATE TABLE `RESOURCE` (`RES_ID` BIGINT AUTO_INCREMENT NOT NULL, `NAME` VARCHAR(255) NOT NULL, `DESCRIPTION` LONGTEXT DEFAULT null NULL, `CREATED_BY` VARCHAR(50) NOT NULL, `CREATED_ON` DATETIME NOT NULL, `LAST_MODIFIED_BY` VARCHAR(50) DEFAULT null NULL, `LAST_MODIFIED_ON` DATETIME DEFAULT null NULL, CONSTRAINT `PK_RESOURCE` PRIMARY KEY (`RES_ID`));

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('gfouquet', 'A tree node organizes resources. These are stored in the RESOURCE table', NOW(), 'Create Table', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml', 'tm-1.1.0.07', '2.0.5', '3:7aeec9343a5b8e86f987d291a5edb778', 84);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml::tm-1.1.0.08::gfouquet::(Checksum: 3:7969f710831387cc73bc194aa34d1b8c)
--  SIMPLE_RESOURCE stores "concrete" resources with a name and a description
CREATE TABLE `SIMPLE_RESOURCE` (`RES_ID` BIGINT NOT NULL, CONSTRAINT `pk_simple_resource` PRIMARY KEY (`RES_ID`), CONSTRAINT `fk_resource` FOREIGN KEY (`RES_ID`) REFERENCES RESOURCE(RES_ID));

ALTER TABLE `REQUIREMENT_FOLDER` ADD `RES_ID` BIGINT;

ALTER TABLE `REQUIREMENT_FOLDER` ADD CONSTRAINT `fk_req_folder_simple_resource` FOREIGN KEY (`RES_ID`) REFERENCES `SIMPLE_RESOURCE` (`RES_ID`);

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('gfouquet', 'SIMPLE_RESOURCE stores "concrete" resources with a name and a description', NOW(), 'Create Table, Add Column', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml', 'tm-1.1.0.08', '2.0.5', '3:7969f710831387cc73bc194aa34d1b8c', 85);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml::tm-1.1.0.09::gfouquet::(Checksum: 3:8eef6e42740a9a0de705a27dcbfa9ba8)
--  Moves requirement folder data into SIMPLE_RESOURCE table
insert into RESOURCE (RES_ID, NAME, DESCRIPTION, CREATED_BY, CREATED_ON, LAST_MODIFIED_BY, LAST_MODIFIED_ON) 
			select f.RLN_ID, NAME, DESCRIPTION, CREATED_BY, CREATED_ON, LAST_MODIFIED_BY, LAST_MODIFIED_ON
			from REQUIREMENT_LIBRARY_NODE n
			inner join REQUIREMENT_FOLDER f on n.RLN_Id = f.RLN_ID
			where n.DELETED_ON is null;

insert into SIMPLE_RESOURCE (RES_ID) 
			select res.RES_ID
			from REQUIREMENT_LIBRARY_NODE n
			inner join REQUIREMENT_FOLDER f on n.RLN_Id = f.RLN_ID
			inner join RESOURCE res on res.RES_ID = f.RLN_ID
			where n.DELETED_ON is null;

update REQUIREMENT_FOLDER 
			set RES_ID = RLN_ID
			where exists (select 1 from REQUIREMENT_LIBRARY_NODE where REQUIREMENT_LIBRARY_NODE.RLN_ID = REQUIREMENT_FOLDER.RLN_ID and DELETED_ON is null);

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('gfouquet', 'Moves requirement folder data into SIMPLE_RESOURCE table', NOW(), 'Custom SQL (x3)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml', 'tm-1.1.0.09', '2.0.5', '3:8eef6e42740a9a0de705a27dcbfa9ba8', 86);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml::tm-1.1.0.10::gfouquet::(Checksum: 3:11e8ae33c5eb2ee02e6046721193047f)
--  Creates REQUIREMENT_VERSION table in which we store the versions of requirements
CREATE TABLE `REQUIREMENT_VERSION` (`RES_ID` BIGINT NOT NULL, `REQUIREMENT_ID` BIGINT NULL, `REFERENCE` VARCHAR(20) NULL, `VERSION_NUMBER` INT DEFAULT 1 NULL, `CRITICALITY` VARCHAR(50) DEFAULT 'UNDEFINED' NOT NULL, `REQUIREMENT_STATUS` VARCHAR(50) DEFAULT 'WORK_IN_PROGRESS' NOT NULL, `ATTACHMENT_LIST_ID` BIGINT NOT NULL, CONSTRAINT `pk_requirement_version` PRIMARY KEY (`RES_ID`), CONSTRAINT `fk_requirement_version_attachment_list` FOREIGN KEY (`ATTACHMENT_LIST_ID`) REFERENCES ATTACHMENT_LIST(ATTACHMENT_LIST_ID), CONSTRAINT `fk_req_version_resource` FOREIGN KEY (`RES_ID`) REFERENCES RESOURCE(RES_ID), CONSTRAINT `fk_requirement_version_requirement` FOREIGN KEY (`REQUIREMENT_ID`) REFERENCES REQUIREMENT(RLN_ID));

ALTER TABLE `REQUIREMENT_VERSION` ADD CONSTRAINT `cu_version_of_requirement` UNIQUE (`REQUIREMENT_ID`, `VERSION_NUMBER`);

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('gfouquet', 'Creates REQUIREMENT_VERSION table in which we store the versions of requirements', NOW(), 'Create Table, Add Unique Constraint', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml', 'tm-1.1.0.10', '2.0.5', '3:11e8ae33c5eb2ee02e6046721193047f', 87);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml::tm-1.1.0.11::gfouquet::(Checksum: 3:0baecf978673f11a9f8dd9999fed2cd1)
--  Migrates requirement data into REQUIREMENT_VERSION table
insert into RESOURCE 
				(RES_ID, NAME, DESCRIPTION, CREATED_BY, CREATED_ON, LAST_MODIFIED_BY, LAST_MODIFIED_ON) 
			select r.RLN_ID, NAME, DESCRIPTION, CREATED_BY, CREATED_ON, LAST_MODIFIED_BY, LAST_MODIFIED_ON
			from REQUIREMENT_LIBRARY_NODE n
			inner join REQUIREMENT r on n.RLN_Id = r.RLN_ID
			where n.DELETED_ON is null;

insert into REQUIREMENT_VERSION 
				(RES_ID, REQUIREMENT_ID, REFERENCE, CRITICALITY, ATTACHMENT_LIST_ID) 
			select res.RES_ID, r.RLN_ID, REFERENCE, CRITICALITY, ATTACHMENT_LIST_ID
			from REQUIREMENT_LIBRARY_NODE n
			inner join REQUIREMENT r on n.RLN_Id = r.RLN_ID
			inner join RESOURCE res on res.RES_ID = r.RLN_ID
			where n.DELETED_ON is null;

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('gfouquet', 'Migrates requirement data into REQUIREMENT_VERSION table', NOW(), 'Custom SQL (x2)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml', 'tm-1.1.0.11', '2.0.5', '3:0baecf978673f11a9f8dd9999fed2cd1', 88);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml::tm-1.1.0.12::gfouquet::(Checksum: 3:4a2bca6beb7751e2c4a5f50b1ecb0c2b)
--  Adds the 'latest version' relationship between REQUIREMENT and REQUIREMENT_VERSION tables
ALTER TABLE `REQUIREMENT` ADD `CURRENT_VERSION_ID` BIGINT;

ALTER TABLE `REQUIREMENT` ADD CONSTRAINT `fk_requirement_current_version` FOREIGN KEY (`CURRENT_VERSION_ID`) REFERENCES `REQUIREMENT_VERSION` (`RES_ID`);

update REQUIREMENT r
			set CURRENT_VERSION_ID = (
				select RES_ID 
				from REQUIREMENT_VERSION rv 
				where rv.REQUIREMENT_ID = r.RLN_ID
			);

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('gfouquet', 'Adds the ''latest version'' relationship between REQUIREMENT and REQUIREMENT_VERSION tables', NOW(), 'Add Column, Custom SQL', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml', 'tm-1.1.0.12', '2.0.5', '3:4a2bca6beb7751e2c4a5f50b1ecb0c2b', 89);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml::tm-1.1.0.13::gfouquet::(Checksum: 3:f808950f1318acd85849691d30ef7418)
--  Test Cases now cover Requirements versions. We create a new association table and populate it from TEST_CASE_REQUIREMENT_LINK
CREATE TABLE `TEST_CASE_VERIFIED_REQUIREMENT_VERSION` (`VERIFIED_REQ_VERSION_ID` BIGINT NULL, `VERIFYING_TEST_CASE_ID` BIGINT NULL, CONSTRAINT `fk_verifying_test_case` FOREIGN KEY (`VERIFYING_TEST_CASE_ID`) REFERENCES TEST_CASE(TCLN_ID), CONSTRAINT `fk_verified_req_version` FOREIGN KEY (`VERIFIED_REQ_VERSION_ID`) REFERENCES REQUIREMENT_VERSION(RES_ID));

insert into TEST_CASE_VERIFIED_REQUIREMENT_VERSION (VERIFYING_TEST_CASE_ID, VERIFIED_REQ_VERSION_ID)
			select TEST_CASE_ID, RES_ID
			from TEST_CASE_REQUIREMENT_LINK tcrl
			inner join REQUIREMENT_VERSION rv on rv.REQUIREMENT_ID = tcrl.REQUIREMENT_ID;

ALTER TABLE `TEST_CASE_VERIFIED_REQUIREMENT_VERSION` MODIFY `VERIFIED_REQ_VERSION_ID` BIGINT NOT NULL;

ALTER TABLE `TEST_CASE_VERIFIED_REQUIREMENT_VERSION` MODIFY `VERIFYING_TEST_CASE_ID` BIGINT NOT NULL;

ALTER TABLE `TEST_CASE_VERIFIED_REQUIREMENT_VERSION` 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 ('gfouquet', 'Test Cases now cover Requirements versions. We create a new association table and populate it from TEST_CASE_REQUIREMENT_LINK', NOW(), 'Create Table, Custom SQL, 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.1.0.xml', 'tm-1.1.0.13', '2.0.5', '3:f808950f1318acd85849691d30ef7418', 90);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml::tm-1.1.0.14::gfouquet::(Checksum: 3:434950a4c673124b5466920aa1d43907)
--  Trashes the now useless TEST_CASE_REQUIREMENT_LINK table
ALTER TABLE `TEST_CASE_REQUIREMENT_LINK` DROP FOREIGN KEY `fk_tc_req_tc`;

ALTER TABLE `TEST_CASE_REQUIREMENT_LINK` DROP FOREIGN KEY `fk_tc_req_req`;

DROP TABLE `TEST_CASE_REQUIREMENT_LINK`;

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('gfouquet', 'Trashes the now useless TEST_CASE_REQUIREMENT_LINK table', NOW(), 'Drop Foreign Key Constraint (x2), Drop Table', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml', 'tm-1.1.0.14', '2.0.5', '3:434950a4c673124b5466920aa1d43907', 91);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml::tm-1.1.0.15::bsiri::(Checksum: 3:df9ca9a019aead911d53118c13fe27b7)
--  This table is the main table of the event logging system, in other words the history of a business entity. As for Squash TM
--  		version 1.1.0 and until further notice, we log here requirement modifications only. It may grows to log other entities in the future, ie test cases.
--  		Note : the application will fill this table itself, since we don't want triggers in the database.
CREATE TABLE `REQUIREMENT_AUDIT_EVENT` (`EVENT_ID` BIGINT AUTO_INCREMENT NOT NULL, `REQ_VERSION_ID` BIGINT NOT NULL, `EVENT_DATE` DATETIME NOT NULL, `AUTHOR` VARCHAR(255) NOT NULL, CONSTRAINT `PK_REQUIREMENT_AUDIT_EVENT` PRIMARY KEY (`EVENT_ID`), CONSTRAINT `fk_audit_event_requirement_version` FOREIGN KEY (`REQ_VERSION_ID`) REFERENCES REQUIREMENT_VERSION(RES_ID));

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('bsiri', 'This table is the main table of the event logging system, in other words the history of a business entity. As for Squash TM
		version 1.1.0 and until further notice, we log here requirement modifications only. It may grows to log other entities in the ...', NOW(), 'Create Table', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml', 'tm-1.1.0.15', '2.0.5', '3:df9ca9a019aead911d53118c13fe27b7', 92);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml::tm-1.1.0.16::bsiri::(Checksum: 3:f7781e8a8128679b692a576e954b946d)
--  It is a child table of REQUIREMENT_AUDIT_EVENT. This table exists for the sole purpose of qualifying a given event in a creation event, as such its content is empty (the sheer presence
--  		of an entry in this table is sufficient)
CREATE TABLE `REQUIREMENT_CREATION` (`EVENT_ID` BIGINT NOT NULL, CONSTRAINT `PK_CREATION_EVENT` PRIMARY KEY (`EVENT_ID`), CONSTRAINT `fk_create_audit` FOREIGN KEY (`EVENT_ID`) REFERENCES REQUIREMENT_AUDIT_EVENT(EVENT_ID));

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('bsiri', 'It is a child table of REQUIREMENT_AUDIT_EVENT. This table exists for the sole purpose of qualifying a given event in a creation event, as such its content is empty (the sheer presence
		of an entry in this table is sufficient)', NOW(), 'Create Table', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml', 'tm-1.1.0.16', '2.0.5', '3:f7781e8a8128679b692a576e954b946d', 93);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml::tm-1.1.0.17::bsiri::(Checksum: 3:04d14fac6434ead391f617b4f49e303a)
--  This is a child table of REQUIREMENT_AUDIT_EVENT. This table will log updates on the target entity. Not every operations will be logged. 
--  			Note 1 : As for version 1.1.0 and until further notice for the sake of simplicity we log both the former and the new value. It may change in the future.
--  			Note 2 : For technical reasons this table exists in two flavors. The twin table is REQUIREMENT_LARGE_PROPERTY_CHANGE. This table will log standard attributes, 
--  			while the other one will log blobs.
CREATE TABLE `REQUIREMENT_PROPERTY_CHANGE` (`EVENT_ID` BIGINT NOT NULL, `OLD_VALUE` VARCHAR(100) NULL, `NEW_VALUE` VARCHAR(100) NULL, `PROPERTY_NAME` VARCHAR(100) NULL, CONSTRAINT `PK_PPT_CHANGE` PRIMARY KEY (`EVENT_ID`), CONSTRAINT `fk_PPT_change_audit` FOREIGN KEY (`EVENT_ID`) REFERENCES REQUIREMENT_AUDIT_EVENT(EVENT_ID));

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('bsiri', 'This is a child table of REQUIREMENT_AUDIT_EVENT. This table will log updates on the target entity. Not every operations will be logged. 
			Note 1 : As for version 1.1.0 and until further notice for the sake of simplicity we log both the former and th...', NOW(), 'Create Table', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml', 'tm-1.1.0.17', '2.0.5', '3:04d14fac6434ead391f617b4f49e303a', 94);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml::tm-1.1.0.18::bsiri::(Checksum: 3:f7bcd79666e2e120c0a26260a1028380)
--  This is a child table of REQUIREMENT_AUDIT_EVENT. This table will log updates on the target entity. Not every operations will be logged. 
--  			Note 1 : As for version 1.1.0 and until further notice for the sake of simplicity we log both the former and the new value. It may change in the future.
--  			Note 2 : For technical reasons this table exists in two flavors. The twin table is REQUIREMENT_PROPERTY_CHANGE. This table will log blobs, 
--  			while the other one will log standard attributes.
CREATE TABLE `REQUIREMENT_LARGE_PROPERTY_CHANGE` (`EVENT_ID` BIGINT NOT NULL, `OLD_VALUE` LONGTEXT DEFAULT null NULL, `NEW_VALUE` LONGTEXT DEFAULT null NULL, `PROPERTY_NAME` VARCHAR(100) NULL, CONSTRAINT `PK_LARGE_PPT_CHANGE` PRIMARY KEY (`EVENT_ID`), CONSTRAINT `fk_lPPT_change_audit` FOREIGN KEY (`EVENT_ID`) REFERENCES REQUIREMENT_AUDIT_EVENT(EVENT_ID));

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('bsiri', 'This is a child table of REQUIREMENT_AUDIT_EVENT. This table will log updates on the target entity. Not every operations will be logged. 
			Note 1 : As for version 1.1.0 and until further notice for the sake of simplicity we log both the former and th...', NOW(), 'Create Table', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml', 'tm-1.1.0.18', '2.0.5', '3:f7bcd79666e2e120c0a26260a1028380', 95);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml::tm-1.1.0.19::bsiri::(Checksum: 3:9a53e8e6e08eb787dbab66e10b85cfe9)
--  Will immediately update the tables with the create_events of existing requirements.
insert into REQUIREMENT_AUDIT_EVENT (REQ_VERSION_ID, EVENT_DATE, AUTHOR) 
			select rv.RES_ID, CREATED_ON, CREATED_BY  
			from REQUIREMENT_VERSION rv 
			inner join RESOURCE r on r.RES_ID = rv.RES_ID;

insert into REQUIREMENT_CREATION (event_id) select event_id from REQUIREMENT_AUDIT_EVENT;

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('bsiri', 'Will immediately update the tables with the create_events of existing requirements.', NOW(), 'Custom SQL (x2)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml', 'tm-1.1.0.19', '2.0.5', '3:9a53e8e6e08eb787dbab66e10b85cfe9', 96);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml::tm-1.1.0.20::gfouquet::(Checksum: 3:bee2aa78c17ce9f26bdc145f4d2faa69)
--  Remove obsolete columns from REQUIREMENT and REQUIREMENT_LIBRARY_NODE table. 
--  			Data is now in REQUIREMENT_VERSION and REQUIREMENT_FOLDER tables.
ALTER TABLE `REQUIREMENT` DROP COLUMN `REFERENCE`;

ALTER TABLE `REQUIREMENT` DROP COLUMN `CRITICALITY`;

ALTER TABLE `REQUIREMENT` DROP FOREIGN KEY `fk_requirement_attachment_list`;

ALTER TABLE `REQUIREMENT` DROP COLUMN `ATTACHMENT_LIST_ID`;

ALTER TABLE `REQUIREMENT_LIBRARY_NODE` DROP COLUMN `NAME`;

ALTER TABLE `REQUIREMENT_LIBRARY_NODE` DROP COLUMN `DESCRIPTION`;

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('gfouquet', 'Remove obsolete columns from REQUIREMENT and REQUIREMENT_LIBRARY_NODE table. 
			Data is now in REQUIREMENT_VERSION and REQUIREMENT_FOLDER tables.', NOW(), 'Drop Column (x2), Drop Foreign Key Constraint, Drop Column (x3)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml', 'tm-1.1.0.20', '2.0.5', '3:bee2aa78c17ce9f26bdc145f4d2faa69', 97);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml::tm-1.1.0.21::fogaillard::(Checksum: 3:749fd7e10d0f1275176e6e5379316cb8)
--  Add Prerequisite collumn to the test_case table
ALTER TABLE `TEST_CASE` ADD `PREREQUISITE` LONGTEXT DEFAULT null;

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('fogaillard', 'Add Prerequisite collumn to the test_case table', NOW(), 'Add Column', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml', 'tm-1.1.0.21', '2.0.5', '3:749fd7e10d0f1275176e6e5379316cb8', 98);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml::tm-1.1.0.22::bsiri::(Checksum: 3:f21b8cdf96f5a52a837b8a843b3c0dfd)
--  Add the table TEST_SUITE. A TEST_SUITE is basically a view on a subset of ITERATION_TEST_PLAN_ITEMs.
CREATE TABLE `TEST_SUITE` (`ID` BIGINT AUTO_INCREMENT NOT NULL, `NAME` VARCHAR(100) NULL, `DESCRIPTION` LONGTEXT DEFAULT null NULL, `ATTACHMENT_LIST_ID` BIGINT NOT NULL, `CREATED_BY` VARCHAR(50) NOT NULL, `CREATED_ON` DATETIME NOT NULL, `LAST_MODIFIED_BY` VARCHAR(50) DEFAULT null NULL, `LAST_MODIFIED_ON` DATETIME DEFAULT null NULL, CONSTRAINT `PK_TEST_SUITE` PRIMARY KEY (`ID`), CONSTRAINT `fk_test_suite_attachment_list` FOREIGN KEY (`ATTACHMENT_LIST_ID`) REFERENCES ATTACHMENT_LIST(ATTACHMENT_LIST_ID));

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('bsiri', 'Add the table TEST_SUITE. A TEST_SUITE is basically a view on a subset of ITERATION_TEST_PLAN_ITEMs.', NOW(), 'Create Table', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml', 'tm-1.1.0.22', '2.0.5', '3:f21b8cdf96f5a52a837b8a843b3c0dfd', 99);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml::tm-1.1.0.23::bsiri::(Checksum: 3:cc2c3ce46094fc05b76f34154fea809c)
--  Association table between ITERATION and TEST_SUITE. Like in many other places here, the purpose of this association table is not to model a n-n cardinality between ITERATION and 
--  			TEST_SUITE, but 1-n. We thus gain performance at the expense of the model quality.
CREATE TABLE `ITERATION_TEST_SUITE` (`ITERATION_ID` BIGINT NOT NULL, `TEST_SUITE_ID` BIGINT NOT NULL, CONSTRAINT `fk_iteration_test_suite_iteration` FOREIGN KEY (`ITERATION_ID`) REFERENCES ITERATION(ITERATION_ID), CONSTRAINT `fk_iteration_test_suite_suite` FOREIGN KEY (`TEST_SUITE_ID`) REFERENCES TEST_SUITE(ID), UNIQUE (`TEST_SUITE_ID`));

CREATE INDEX `dx_iteration_test_suite_iteration` ON `ITERATION_TEST_SUITE`(`ITERATION_ID`);

CREATE INDEX `dx_iteration_test_suite_suite` ON `ITERATION_TEST_SUITE`(`TEST_SUITE_ID`);

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('bsiri', 'Association table between ITERATION and TEST_SUITE. Like in many other places here, the purpose of this association table is not to model a n-n cardinality between ITERATION and 
			TEST_SUITE, but 1-n. We thus gain performance at the expense of the mo...', NOW(), 'Create Table, Create Index (x2)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml', 'tm-1.1.0.23', '2.0.5', '3:cc2c3ce46094fc05b76f34154fea809c', 100);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml::tm-1.1.0.24::bsiri::(Checksum: 3:7b4ae4519e1c0fa1c18c75f27c75c8a8)
--  Hereby an ITERATION_TEST_PLAN_ITEM shalt reference 0 or 1 TEST_SUITE
ALTER TABLE `ITERATION_TEST_PLAN_ITEM` ADD `TEST_SUITE` BIGINT DEFAULT null;

ALTER TABLE `ITERATION_TEST_PLAN_ITEM` ADD CONSTRAINT `fk_itpi_testsuite` FOREIGN KEY (`TEST_SUITE`) REFERENCES `TEST_SUITE` (`ID`);

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('bsiri', 'Hereby an ITERATION_TEST_PLAN_ITEM shalt reference 0 or 1 TEST_SUITE', NOW(), 'Add Column', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml', 'tm-1.1.0.24', '2.0.5', '3:7b4ae4519e1c0fa1c18c75f27c75c8a8', 101);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml::tm-1.1.0.25::fogaillard::(Checksum: 3:507c15d0675f77b3a197c7d7f607f682)
--  Add Prerequisite collumn to the execution table
ALTER TABLE `EXECUTION` ADD `PREREQUISITE` LONGTEXT DEFAULT null;

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('fogaillard', 'Add Prerequisite collumn to the execution table', NOW(), 'Add Column', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.1.0.xml', 'tm-1.1.0.25', '2.0.5', '3:507c15d0675f77b3a197c7d7f607f682', 102);

--  Release Database Lock
--  Release Database Lock
