-- *********************************************************************
-- 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.1.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.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-1.21/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-1.21/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-1.21/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-1.21/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-1.21/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-1.21/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) DEFAULT 'LOW' NOT NULL;

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-1.21/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-1.21/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 BOOLEAN DEFAULT FALSE NOT NULL;

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-1.21/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-1.21/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-1.21/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-1.21/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 GENERATED BY DEFAULT AS IDENTITY NOT NULL, NAME VARCHAR(255) NOT NULL, DESCRIPTION LONGVARCHAR DEFAULT null, CREATED_BY VARCHAR(50) NOT NULL, CREATED_ON TIMESTAMP NOT NULL, LAST_MODIFIED_BY VARCHAR(50) DEFAULT null, LAST_MODIFIED_ON TIMESTAMP DEFAULT 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-1.21/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-1.21/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-1.21/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-1.21/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-1.21/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-1.21/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, REFERENCE VARCHAR(20), VERSION_NUMBER INT DEFAULT 1, 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_req_version_resource FOREIGN KEY (RES_ID) REFERENCES RESOURCE(RES_ID), CONSTRAINT fk_requirement_version_requirement FOREIGN KEY (REQUIREMENT_ID) REFERENCES REQUIREMENT(RLN_ID), CONSTRAINT fk_requirement_version_attachment_list FOREIGN KEY (ATTACHMENT_LIST_ID) REFERENCES ATTACHMENT_LIST(ATTACHMENT_LIST_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-1.21/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-1.21/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-1.21/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-1.21/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-1.21/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-1.21/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, VERIFYING_TEST_CASE_ID BIGINT, 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 ALTER COLUMN  VERIFIED_REQ_VERSION_ID BIGINT NOT NULL;

ALTER TABLE TEST_CASE_VERIFIED_REQUIREMENT_VERSION ALTER COLUMN  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-1.21/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-1.21/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 CONSTRAINT fk_tc_req_tc;

ALTER TABLE TEST_CASE_REQUIREMENT_LINK DROP CONSTRAINT 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-1.21/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-1.21/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 GENERATED BY DEFAULT AS IDENTITY NOT NULL, REQ_VERSION_ID BIGINT NOT NULL, EVENT_DATE TIMESTAMP 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-1.21/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-1.21/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-1.21/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-1.21/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), NEW_VALUE VARCHAR(100), PROPERTY_NAME VARCHAR(100), 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-1.21/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-1.21/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 LONGVARCHAR DEFAULT null, NEW_VALUE LONGVARCHAR DEFAULT null, PROPERTY_NAME VARCHAR(100), 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-1.21/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-1.21/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-1.21/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-1.21/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 CONSTRAINT 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-1.21/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-1.21/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 LONGVARCHAR 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-1.21/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-1.21/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 GENERATED BY DEFAULT AS IDENTITY NOT NULL, NAME VARCHAR(100), DESCRIPTION LONGVARCHAR DEFAULT null, ATTACHMENT_LIST_ID BIGINT NOT NULL, CREATED_BY VARCHAR(50) NOT NULL, CREATED_ON TIMESTAMP NOT NULL, LAST_MODIFIED_BY VARCHAR(50) DEFAULT null, LAST_MODIFIED_ON TIMESTAMP DEFAULT 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-1.21/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-1.21/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-1.21/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-1.21/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-1.21/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-1.21/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 LONGVARCHAR 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-1.21/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
