-- *********************************************************************
-- 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.17.0.xml
-- Ran at: 03/04/20 12:06
-- Against: liquibase@jdbc:postgresql://localhost:5432/squash_dist_cur
-- 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.17.0.xml::tm-1.17.0::jthebault::(Checksum: 3:0c43f78c001b54232be6fb7d5aa1169e)
-- Update TM database version number
UPDATE CORE_CONFIG SET VALUE = '1.17.0' WHERE STR_KEY = 'squashtest.tm.database.version';

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('jthebault', '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.17.0.xml', 'tm-1.17.0', '2.0.5', '3:0c43f78c001b54232be6fb7d5aa1169e', 405);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.17.0.xml::tm-1.17.0-feature-6921::zyang::(Checksum: 3:d9f5ee0070ebbd5f0d2a702f2b90d274)
-- Adds a status field to campaign and iteration
ALTER TABLE CAMPAIGN ADD CAMPAIGN_STATUS VARCHAR(30) NOT NULL DEFAULT 'UNDEFINED';

ALTER TABLE ITERATION ADD ITERATION_STATUS VARCHAR(30) NOT NULL DEFAULT 'UNDEFINED';

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('zyang', 'Adds a status field to campaign and iteration', NOW(), 'Add Column (x2)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.17.0.xml', 'tm-1.17.0-feature-6921', '2.0.5', '3:d9f5ee0070ebbd5f0d2a702f2b90d274', 406);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.17.0.xml::tm-1.17.0::zyang::(Checksum: 3:855bbf520bbd7b0b22e94edcf382656a)
CREATE TABLE REPORT_DEFINITION (REPORT_ID bigserial NOT NULL, NAME VARCHAR(255), DESCRIPTION TEXT, PLUGIN_NAMESPACE VARCHAR(255), PARAMETERS TEXT, USER_ID BIGINT NOT NULL, PROJECT_ID BIGINT NOT NULL, CREATED_BY VARCHAR(50) NOT NULL, CREATED_ON TIMESTAMP WITH TIME ZONE NOT NULL, LAST_MODIFIED_BY VARCHAR(50) DEFAULT null, LAST_MODIFIED_ON TIMESTAMP WITH TIME ZONE DEFAULT null, CONSTRAINT PK_REPORT_DEFINITION PRIMARY KEY (REPORT_ID), CONSTRAINT fk_project_report_definition_project_id FOREIGN KEY (PROJECT_ID) REFERENCES PROJECT(PROJECT_ID), CONSTRAINT fk_report_owner FOREIGN KEY (USER_ID) REFERENCES CORE_USER(PARTY_ID));

COMMENT ON COLUMN REPORT_DEFINITION.REPORT_ID IS 'autogenerated ID';

COMMENT ON COLUMN REPORT_DEFINITION.NAME IS 'The name of this chart';

COMMENT ON COLUMN REPORT_DEFINITION.DESCRIPTION IS 'additional informations about this report';

COMMENT ON COLUMN REPORT_DEFINITION.PLUGIN_NAMESPACE IS 'The namespace of the report plugin';

COMMENT ON COLUMN REPORT_DEFINITION.PARAMETERS IS 'parameters to generate the report';

COMMENT ON COLUMN REPORT_DEFINITION.USER_ID IS 'foreign key to the owner of this report definition';

COMMENT ON COLUMN REPORT_DEFINITION.PROJECT_ID IS 'foreign key to the projet';

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('zyang', '', 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.17.0.xml', 'tm-1.17.0', '2.0.5', '3:855bbf520bbd7b0b22e94edcf382656a', 407);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.17.0.xml::tm-1.17.0-feature-6921::cholc::(Checksum: 3:2ed5996d28258aaa745f8c90b2966702)
-- Adds a execution status field to the test suites
ALTER TABLE TEST_SUITE ADD EXECUTION_STATUS VARCHAR(255) DEFAULT 'READY';

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('cholc', 'Adds a execution status field to the test suites', 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.17.0.xml', 'tm-1.17.0-feature-6921', '2.0.5', '3:2ed5996d28258aaa745f8c90b2966702', 408);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.17.0.xml::tm-1.17.0-feature-sqtm-167::bsiri::(Checksum: 3:7a1442f1f4141a444ad8136edfbff065)
-- Adding the ability to use application-level credentials that allows Squash interact with a bugtracker on its own
--       (ie instead of requiring the current user to authenticate).
CREATE TABLE STORED_CREDENTIALS (CREDENTIAL_ID bigserial NOT NULL, ENC_VERSION SMALLINT, ENC_CREDENTIALS TEXT, AUTHENTICATED_SERVER BIGINT, CONSTRAINT PK_STORED_CREDENTIALS PRIMARY KEY (CREDENTIAL_ID), CONSTRAINT fk_stored_credentials_authenticated_server FOREIGN KEY (AUTHENTICATED_SERVER) REFERENCES BUGTRACKER(BUGTRACKER_ID) ON DELETE CASCADE);

COMMENT ON COLUMN STORED_CREDENTIALS.CREDENTIAL_ID IS 'primary key';

COMMENT ON COLUMN STORED_CREDENTIALS.ENC_VERSION IS 'the version of encryption service used. Possibly the value will remain to 1 forever.';

COMMENT ON COLUMN STORED_CREDENTIALS.ENC_CREDENTIALS IS 'the credentials as an encrypted string';

COMMENT ON COLUMN STORED_CREDENTIALS.AUTHENTICATED_SERVER IS 'foreign key to the server to authenticate';

ALTER TABLE BUGTRACKER ADD AUTH_POLICY VARCHAR(30) DEFAULT 'USER';

COMMENT ON COLUMN BUGTRACKER.AUTH_POLICY IS 'the policy that decides who needs to authenticate';

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'Adding the ability to use application-level credentials that allows Squash interact with a bugtracker on its own
      (ie instead of requiring the current user to authenticate).', 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.17.0.xml', 'tm-1.17.0-feature-sqtm-167', '2.0.5', '3:7a1442f1f4141a444ad8136edfbff065', 409);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.17.0.xml::tm-1.17.0-jira-sync-01::jthebault::(Checksum: 3:1dc00e1430aa55d44450f5f234cca0cc)
-- Add a remote synchronisation table in squash tm.
CREATE TABLE REMOTE_SYNCHRONISATION (REMOTE_SYNCHRONISATION_ID bigserial NOT NULL, PROJECT_ID BIGINT NOT NULL, SERVER_ID BIGINT NOT NULL, REMOTE_SYNCHRONISATION_NAME VARCHAR(255) NOT NULL, KIND VARCHAR(50) NOT NULL, REMOTE_SELECT_TYPE VARCHAR(50), REMOTE_SELECT_VALUE VARCHAR(1000), LAST_SUCCESSFUL_SYNC_DATE TIMESTAMP WITH TIME ZONE, LAST_SYNC_DATE TIMESTAMP WITH TIME ZONE, LAST_SYNC_STATUS VARCHAR(20), SYNC_STATUS VARCHAR(20), REMOTE_SYNCHRONISATION_OPTIONS TEXT, CONSTRAINT PK_REMOTE_SYNCHRONISATION PRIMARY KEY (REMOTE_SYNCHRONISATION_ID), CONSTRAINT fk_remote_sync__project__project_id FOREIGN KEY (PROJECT_ID) REFERENCES PROJECT(PROJECT_ID), CONSTRAINT fk_remote_sync__bugtracker__bugtracker_id FOREIGN KEY (SERVER_ID) REFERENCES BUGTRACKER(BUGTRACKER_ID));

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('jthebault', 'Add a remote synchronisation table in squash tm.', 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.17.0.xml', 'tm-1.17.0-jira-sync-01', '2.0.5', '3:1dc00e1430aa55d44450f5f234cca0cc', 410);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.17.0.xml::tm-1.17.0-jira-sync-02::jthebault::(Checksum: 3:52aadd9c26924da0405ef10ffddbc6af)
-- Add a remote folder synchronisation table in squash tm.
CREATE TABLE REQUIREMENT_FOLDER_SYNC_EXTENDER (RF_SYNC_EXTENDER_ID bigserial NOT NULL, REMOTE_SYNCHRONISATION_ID BIGINT NOT NULL, REMOTE_FOLDER_ID VARCHAR(255), REMOTE_FOLDER_STATUS VARCHAR(50), REQUIREMENT_FOLDER_ID BIGINT NOT NULL, TYPE VARCHAR(30) NOT NULL, CONSTRAINT PK_REQUIREMENT_FOLDER_SYNC_EXTENDER PRIMARY KEY (RF_SYNC_EXTENDER_ID), CONSTRAINT fk_req_folder_sync_extender__remote_sync_remote_sync_id FOREIGN KEY (REMOTE_SYNCHRONISATION_ID) REFERENCES REMOTE_SYNCHRONISATION(REMOTE_SYNCHRONISATION_ID), CONSTRAINT fk_req_folder_sync_extender__req_folder__rln_id FOREIGN KEY (REQUIREMENT_FOLDER_ID) REFERENCES REQUIREMENT_FOLDER(RLN_ID), UNIQUE (REQUIREMENT_FOLDER_ID));

COMMENT ON COLUMN REQUIREMENT_FOLDER_SYNC_EXTENDER.REMOTE_SYNCHRONISATION_ID IS 'Only one master folder should be targeted by a given synchronisation';

COMMENT ON COLUMN REQUIREMENT_FOLDER_SYNC_EXTENDER.REQUIREMENT_FOLDER_ID IS 'Only one extender for any folder in Squash TM';

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('jthebault', 'Add a remote folder synchronisation table in squash tm.', 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.17.0.xml', 'tm-1.17.0-jira-sync-02', '2.0.5', '3:52aadd9c26924da0405ef10ffddbc6af', 411);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.17.0.xml::tm-1.17.0-jira-sync-03::jthebault::(Checksum: 3:8a68ee95a2fb1a2575e7a2e9b3571eed)
ALTER TABLE REQUIREMENT_SYNC_EXTENDER ALTER COLUMN  REMOTE_FILTER_NAME DROP NOT NULL;

ALTER TABLE REQUIREMENT_SYNC_EXTENDER ADD REMOTE_SYNCHRONISATION_ID BIGINT;

ALTER TABLE REQUIREMENT_SYNC_EXTENDER ADD CONSTRAINT fk_req_sync_extender__remote_sync_remote_sync_id FOREIGN KEY (REMOTE_SYNCHRONISATION_ID) REFERENCES REMOTE_SYNCHRONISATION (REMOTE_SYNCHRONISATION_ID);

ALTER TABLE REQUIREMENT_SYNC_EXTENDER ADD REMOTE_LAST_UPDATED TIMESTAMP WITH TIME ZONE;

ALTER TABLE REQUIREMENT_SYNC_EXTENDER ADD REMOTE_PARENT_ID VARCHAR(30);

CREATE INDEX req_sync_extender_remote_parent_id_idx ON REQUIREMENT_SYNC_EXTENDER(REMOTE_PARENT_ID);

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('jthebault', '', NOW(), 'Drop Not-Null Constraint, Add Column, Create Index', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.17.0.xml', 'tm-1.17.0-jira-sync-03', '2.0.5', '3:8a68ee95a2fb1a2575e7a2e9b3571eed', 412);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.17.0.xml::tm-1.17.0-Issue-6967::jthebault::(Checksum: 3:5a27c86957398164065ff12fb0bfee1e)
CREATE INDEX tcln_name_idx ON TEST_CASE_LIBRARY_NODE(NAME);

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('jthebault', '', NOW(), 'Create Index', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.17.0.xml', 'tm-1.17.0-Issue-6967', '2.0.5', '3:5a27c86957398164065ff12fb0bfee1e', 413);

-- Release Database Lock
-- Release Database Lock
