-- *********************************************************************
-- 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.18.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.18.0.xml::tm-1.18.0::jthebault::(Checksum: 3:47ac35d6939b1e4c9d90e0ba418a4c9b)
-- Update TM database version number
UPDATE CORE_CONFIG SET VALUE = '1.18.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.18.0.xml', 'tm-1.18.0', '2.0.5', '3:47ac35d6939b1e4c9d90e0ba418a4c9b', 407);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.18.0.xml::tm-1.18.0-connection-log-table::aguilhem::(Checksum: 3:3ce3084744351755dfee9d7ea37c4d88)
-- Create Connection Attempt Log Table
CREATE TABLE CONNECTION_ATTEMPT_LOG (ATTEMPT_ID BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL, LOGIN VARCHAR(50), CONNECTION_DATE TIMESTAMP NOT NULL, SUCCESS BOOLEAN NOT NULL, CONSTRAINT PK_CONNECTION_ATTEMPT_LOG PRIMARY KEY (ATTEMPT_ID));

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('aguilhem', 'Create Connection Attempt Log 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.18.0.xml', 'tm-1.18.0-connection-log-table', '2.0.5', '3:3ce3084744351755dfee9d7ea37c4d88', 408);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.18.0.xml::tm-1.18.0-project-template-link::jlor::(Checksum: 3:d00607ab01c06922cbb7ce367e162848)
-- Adding a column to keep the link between a Project and a Project Template
ALTER TABLE PROJECT ADD TEMPLATE_ID BIGINT;

ALTER TABLE PROJECT ADD CONSTRAINT fk_project_template_id FOREIGN KEY (TEMPLATE_ID) REFERENCES PROJECT (PROJECT_ID);

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('jlor', 'Adding a column to keep the link between a Project and a Project Template', 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.18.0.xml', 'tm-1.18.0-project-template-link', '2.0.5', '3:d00607ab01c06922cbb7ce367e162848', 409);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.18.0.xml::tm-1.18.0-binding-report-to-dashboard::zyang::(Checksum: 3:2bd818310301c4e63ce1d8780b49c334)
CREATE TABLE CUSTOM_REPORT_REPORT_BINDING (CRRB_ID BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL, CRD_ID BIGINT NOT NULL, REPORT_ID BIGINT NOT NULL, ROW INT, COL INT, SIZE_X INT, SIZE_Y INT, CONSTRAINT PK_CRRB_ID PRIMARY KEY (CRRB_ID), CONSTRAINT fk_report_bindind_custom_report_dashboard_crd_id FOREIGN KEY (CRD_ID) REFERENCES CUSTOM_REPORT_DASHBOARD(CRD_ID), CONSTRAINT fk_chart_bindind_report_definition_report_id FOREIGN KEY (REPORT_ID) REFERENCES REPORT_DEFINITION(REPORT_ID));

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.18.0.xml', 'tm-1.18.0-binding-report-to-dashboard', '2.0.5', '3:2bd818310301c4e63ce1d8780b49c334', 410);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.18.0.xml::tm-1.18.0-scripted-test-case::jthebault::(Checksum: 3:1a7f2b2e849f647de429bbc7947f7d31)
-- Create the scripted execution table
CREATE TABLE SCRIPTED_TC_EXTENDER (SCRIPTED_TC_EXTENDER_ID BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL, SCRIPT LONGVARCHAR, LANGUAGE VARCHAR(50), TEST_CASE_ID BIGINT NOT NULL, CONSTRAINT PK_SCRIPTED_TC_EXTENDER PRIMARY KEY (SCRIPTED_TC_EXTENDER_ID), CONSTRAINT fk_scripted_extender_test_case FOREIGN KEY (TEST_CASE_ID) REFERENCES TEST_CASE(TCLN_ID));

CREATE INDEX scripted_tc_extender_test_case_id_idx ON SCRIPTED_TC_EXTENDER(TEST_CASE_ID);

CREATE TABLE SCRIPTED_EXECUTION_EXTENDER (SCRIPTED_EXECUTION_EXTENDER_ID BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL, LANGUAGE VARCHAR(50), SCRIPT_NAME VARCHAR(255), EXECUTION_ID BIGINT NOT NULL, CONSTRAINT PK_SCRIPTED_EXECUTION_EXTENDER PRIMARY KEY (SCRIPTED_EXECUTION_EXTENDER_ID), CONSTRAINT fk_scripted_extender_execution FOREIGN KEY (EXECUTION_ID) REFERENCES EXECUTION(EXECUTION_ID));

CREATE INDEX scripted_execution_extender_execution_id_idx ON SCRIPTED_EXECUTION_EXTENDER(EXECUTION_ID);

ALTER TABLE TEST_CASE ADD TC_KIND VARCHAR(30) DEFAULT 'STANDARD' NOT NULL;

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('jthebault', 'Create the scripted execution table', NOW(), 'Create Table, Create Index, Create Table, Create Index, Add Column', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.18.0.xml', 'tm-1.18.0-scripted-test-case', '2.0.5', '3:1a7f2b2e849f647de429bbc7947f7d31', 411);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.18.0.xml::tm-1.18.0-modification-report::zyang::(Checksum: 3:438a13a6179b54a364f0ff2e2bb6fc15)
-- Adds a summary field to report definition
ALTER TABLE REPORT_DEFINITION ADD SUMMARY VARCHAR(255) DEFAULT '' NOT NULL;

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('zyang', 'Adds a summary field to report definition', 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.18.0.xml', 'tm-1.18.0-modification-report', '2.0.5', '3:438a13a6179b54a364f0ff2e2bb6fc15', 412);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.18.0.xml::tm-1.18.0-oauth1a-consumer-1::bsiri::(Checksum: 3:e0c4d5016ade1c0d4da3bfbdf4dfe414)
-- StoredCredentials can now store either credentials, either general configuration.
--         A new column appear so we can hint what is the type of the payload.
--         Also, users can now have stored credentials (even though they can't manage them yet).
--         Only one credentials can be created for a given user and a given server (bugtracker)
ALTER TABLE STORED_CREDENTIALS ADD CONTENT_TYPE VARCHAR(4) DEFAULT 'CRED';

ALTER TABLE STORED_CREDENTIALS ADD AUTHENTICATED_USER BIGINT DEFAULT null;

ALTER TABLE STORED_CREDENTIALS ADD CONSTRAINT fk_stored_credentials_user FOREIGN KEY (AUTHENTICATED_USER) REFERENCES CORE_USER (PARTY_ID);

ALTER TABLE STORED_CREDENTIALS ADD CONSTRAINT uniq_stored_credentials_server_user UNIQUE (AUTHENTICATED_SERVER, AUTHENTICATED_USER, CONTENT_TYPE);

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'StoredCredentials can now store either credentials, either general configuration.
        A new column appear so we can hint what is the type of the payload.
        Also, users can now have stored credentials (even though they can''t manage them yet).
...', NOW(), 'Add Column, 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.18.0.xml', 'tm-1.18.0-oauth1a-consumer-1', '2.0.5', '3:e0c4d5016ade1c0d4da3bfbdf4dfe414', 413);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.18.0.xml::tm-1.18.0-oauth1a-consumer-2::bsiri::(Checksum: 3:ef39e53abfeb94877814ea110d50494b)
-- Bugtracker now declare which authentication protocol should be used upfront.
ALTER TABLE BUGTRACKER ADD AUTH_PROTOCOL VARCHAR(30) DEFAULT 'BASIC_AUTH' NOT NULL;

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'Bugtracker now declare which authentication protocol should be used upfront.', 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.18.0.xml', 'tm-1.18.0-oauth1a-consumer-2', '2.0.5', '3:ef39e53abfeb94877814ea110d50494b', 414);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.18.0.xml::tm-1.18.0-email-column-resizing::aguilhem::(Checksum: 3:5674ed3ebb19808b8a48c27799023f4a)
ALTER TABLE CORE_USER ALTER COLUMN EMAIL VARCHAR(255);

ALTER TABLE CORE_USER ALTER COLUMN  EMAIL SET DEFAULT '';

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('aguilhem', '', NOW(), 'Modify data type, Add Default Value', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.18.0.xml', 'tm-1.18.0-email-column-resizing', '2.0.5', '3:5674ed3ebb19808b8a48c27799023f4a', 415);

-- Release Database Lock
-- Release Database Lock
