-- *********************************************************************
-- 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.13.3.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.13.3.xml::tm-1.13.3::bsiri::(Checksum: 3:4dae33cd26a63e47b996bb8e65546fcc)
-- Update TM database version number
UPDATE CORE_CONFIG SET VALUE = '1.13.3' WHERE STR_KEY = 'squashtest.tm.database.version';

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'Update TM database version number', NOW(), 'Update Data', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.13.3.xml', 'tm-1.13.3', '2.0.5', '3:4dae33cd26a63e47b996bb8e65546fcc', 374);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.13.3.xml::tm-1.13.3-feature-5265-1::bsiri::(Checksum: 3:15cde7fa51c55f82ab8795f0be8f2b7d)
-- making the capacity of plugin configuration items larger
ALTER TABLE LIBRARY_PLUGIN_BINDING_PROPERTY ALTER COLUMN PLUGIN_BINDING_VALUE LONGVARCHAR;

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'making the capacity of plugin configuration items larger', NOW(), 'Modify data type', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.13.3.xml', 'tm-1.13.3-feature-5265-1', '2.0.5', '3:15cde7fa51c55f82ab8795f0be8f2b7d', 375);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.13.3.xml::tm-1.13.3-feature-5265-2::bsiri::(Checksum: 3:88e5cd27228c2f58998992a673248c86)
-- reworking table REQUIREMENT_SYNC_EXTENDER
ALTER TABLE REQUIREMENT_SYNC_EXTENDER ADD REMOTE_FILTER_NAME VARCHAR(150) NOT NULL;

ALTER TABLE REQUIREMENT_SYNC_EXTENDER DROP CONSTRAINT fk_sync_extender_bugtracker;

ALTER TABLE REQUIREMENT_SYNC_EXTENDER ALTER COLUMN BUGTRACKER_ID RENAME TO SERVER_ID;

ALTER TABLE REQUIREMENT_SYNC_EXTENDER ADD CONSTRAINT fk_sync_extender_server FOREIGN KEY (SERVER_ID) REFERENCES BUGTRACKER (BUGTRACKER_ID) ON DELETE CASCADE;

ALTER TABLE REQUIREMENT_SYNC_EXTENDER DROP CONSTRAINT fk_sync_extender_requirement;

ALTER TABLE REQUIREMENT_SYNC_EXTENDER ADD CONSTRAINT fk_sync_extender_requirement FOREIGN KEY (REQUIREMENT_ID) REFERENCES REQUIREMENT (RLN_ID) ON DELETE CASCADE;

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'reworking table REQUIREMENT_SYNC_EXTENDER', NOW(), 'Add Column, Drop Foreign Key Constraint, Rename Column, Add Foreign Key Constraint, Drop Foreign Key Constraint, Add Foreign Key Constraint', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.13.3.xml', 'tm-1.13.3-feature-5265-2', '2.0.5', '3:88e5cd27228c2f58998992a673248c86', 376);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.13.3.xml::tm-1.13.3-feature-5265-3::bsiri::(Checksum: 3:b9ff7b5a87f9095565612e0ce7ce4ee7)
-- adding useful indexes
CREATE INDEX idx_sync_remote_req_id ON REQUIREMENT_SYNC_EXTENDER(REMOTE_REQ_ID);

CREATE INDEX idx_sync_remote_projfilter ON REQUIREMENT_SYNC_EXTENDER(REMOTE_PROJECT_ID, REMOTE_FILTER_NAME);

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'adding useful indexes', NOW(), '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.13.3.xml', 'tm-1.13.3-feature-5265-3', '2.0.5', '3:b9ff7b5a87f9095565612e0ce7ce4ee7', 377);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.13.3.xml::tm-1.13.3-feature-5265-4::bsiri::(Checksum: 3:a594dba7d082bd207d56bffc7d4b8bf8)
-- This view maps a requirement library node to its main resource. Some would say 
-- 			it is redundant with what can be found in tables REQUIREMENT or REQUIREMENT_FOLDER
-- 			but seriously it makes simple things likes getting the name of a generic 
-- 			REQUIREMENT_LIBRARY_NODE much simpler.
CREATE VIEW RLN_RESOURCE AS select fold.rln_id as rln_id, fold.res_id as res_id
			from REQUIREMENT_FOLDER fold 
			UNION
			select req.rln_id as rln_id, req.current_version_id as res_id
			from REQUIREMENT req;

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'This view maps a requirement library node to its main resource. Some would say 
			it is redundant with what can be found in tables REQUIREMENT or REQUIREMENT_FOLDER
			but seriously it makes simple things likes getting the name of a generic 
			REQUIR...', NOW(), 'Create View', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.13.3.xml', 'tm-1.13.3-feature-5265-4', '2.0.5', '3:a594dba7d082bd207d56bffc7d4b8bf8', 378);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.13.3.xml::tm-1.13.3-feature-5265-5::bsiri::(Checksum: 3:612961ea115fe333cd6e2b995580f7ce)
-- new requirement audit trail tables for synchronization events
CREATE TABLE SYNC_REQUIREMENT_CREATION (EVENT_ID BIGINT NOT NULL, SOURCE VARCHAR(255) NOT NULL, CONSTRAINT PK_SYNC_CREATE PRIMARY KEY (EVENT_ID), CONSTRAINT fk_sync_create_audit FOREIGN KEY (EVENT_ID) REFERENCES REQUIREMENT_AUDIT_EVENT(EVENT_ID));

CREATE TABLE SYNC_REQUIREMENT_UPDATE (EVENT_ID BIGINT NOT NULL, SOURCE VARCHAR(255) NOT NULL, CONSTRAINT PK_SYNC_UPDATE PRIMARY KEY (EVENT_ID), CONSTRAINT fk_sync_update_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', 'new requirement audit trail tables for synchronization events', NOW(), 'Create Table (x2)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.13.3.xml', 'tm-1.13.3-feature-5265-5', '2.0.5', '3:612961ea115fe333cd6e2b995580f7ce', 379);

-- Release Database Lock
-- Release Database Lock
