-- *********************************************************************
-- 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.11.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.11.0.xml::tm-1.11.0::flaurens::(Checksum: 3:15f03859fcb795f03494e71ae0ec15f8)
-- Update TM database version number
UPDATE CORE_CONFIG SET VALUE = '1.11.0' WHERE STR_KEY = 'squashtest.tm.database.version';

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('flaurens', '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.11.0.xml', 'tm-1.11.0', '2.0.5', '3:15f03859fcb795f03494e71ae0ec15f8', 325);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.11.0.xml::tm-1.11.0.feature-3576-1::bsiri::(Checksum: 3:76a554ce241d733597b26c4afcc1d3ec)
-- Prepares the table CUSTOM_FIELD for possible default_values of type TEXT.
--   		Also accepts now a new value for the column FIELD_TYPE : RTF (for rich text fields) - no structural changes for that though.
ALTER TABLE CUSTOM_FIELD ADD LARGE_DEFAULT_VALUE LONGVARCHAR DEFAULT null;

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'Prepares the table CUSTOM_FIELD for possible default_values of type TEXT.
  		Also accepts now a new value for the column FIELD_TYPE : RTF (for rich text fields) - no structural changes for that though.', 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.11.0.xml', 'tm-1.11.0.feature-3576-1', '2.0.5', '3:76a554ce241d733597b26c4afcc1d3ec', 326);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.11.0.xml::tm-1.11.0.feature-3576-2::bsiri::(Checksum: 3:5b69b5fb7e3727632ab4479a010bf602)
-- prepares the table CUSTOM_FIELD_VALUE for values of type CLOB, and adds support for inheritance to that end.
ALTER TABLE CUSTOM_FIELD_VALUE ADD LARGE_VALUE LONGVARCHAR DEFAULT null;

ALTER TABLE CUSTOM_FIELD_VALUE ADD FIELD_TYPE VARCHAR(3) DEFAULT 'CF' NOT NULL;

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'prepares the table CUSTOM_FIELD_VALUE for values of type CLOB, and adds support for inheritance to that end.', 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.11.0.xml', 'tm-1.11.0.feature-3576-2', '2.0.5', '3:5b69b5fb7e3727632ab4479a010bf602', 327);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.11.0.xml::tm-1.11.0.feature-3576-3::bsiri::(Checksum: 3:b3dc90cd83d541e9c0bcd38ae6707e70)
-- Prepares the table DENORMALIZED_FIELD_VALUE for values of type CLOB, and adds support for inheritance to that end.
--   		Also accepts now a new value for the column FIELD_TYPE : RTF (for rich text fields) - no structural changes for that though.
ALTER TABLE DENORMALIZED_FIELD_VALUE ADD LARGE_VALUE LONGVARCHAR DEFAULT null;

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'Prepares the table DENORMALIZED_FIELD_VALUE for values of type CLOB, and adds support for inheritance to that end.
  		Also accepts now a new value for the column FIELD_TYPE : RTF (for rich text fields) - no structural changes for that though.', 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.11.0.xml', 'tm-1.11.0.feature-3576-3', '2.0.5', '3:b3dc90cd83d541e9c0bcd38ae6707e70', 328);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.11.0.xml::tm-1.11.0.feature-3556-4::kdrifi::(Checksum: 3:d80d9ea54b2ec012b8c27e2255c8fb53)
-- Cleaning duplicates of column ROLE_USER in table core_group _authority
DELETE FROM CORE_GROUP_AUTHORITY  WHERE AUTHORITY='ROLE_USER';

DELETE FROM CORE_GROUP_AUTHORITY  WHERE GROUP_ID = 1
        and AUTHORITY='ROLE_TM_USER';

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('kdrifi', 'Cleaning duplicates of column ROLE_USER in table core_group _authority', NOW(), 'Delete Data (x2)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.11.0.xml', 'tm-1.11.0.feature-3556-4', '2.0.5', '3:d80d9ea54b2ec012b8c27e2255c8fb53', 329);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.11.0.xml::tm-1.11.0.feature-3577-1::mpagnon::(Checksum: 3:8ffea92786df4b390a435239641c2228)
-- Fix columns with CHAR(X) types to VARCHAR(X) type to avoid blank space filling by Postgresql
ALTER TABLE CUSTOM_FIELD ALTER COLUMN INPUT_TYPE VARCHAR(30);

ALTER TABLE CUSTOM_FIELD ALTER COLUMN FIELD_TYPE VARCHAR(3);

ALTER TABLE DENORMALIZED_FIELD_VALUE ALTER COLUMN FIELD_TYPE VARCHAR(3);

ALTER TABLE DENORMALIZED_FIELD_VALUE ALTER COLUMN INPUT_TYPE VARCHAR(30);

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'Fix columns with CHAR(X) types to VARCHAR(X) type to avoid blank space filling by Postgresql', NOW(), 'Modify data type (x4)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.11.0.xml', 'tm-1.11.0.feature-3577-1', '2.0.5', '3:8ffea92786df4b390a435239641c2228', 330);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.11.0.xml::tm-1.9.0.feature-3138-2::jsimon::(Checksum: 3:a57d42d8ec0b04084c9913a8e3ded8c8)
ALTER TABLE CAMPAIGN_LIBRARY_CONTENT ALTER COLUMN  CONTENT_ORDER INT NOT NULL;

ALTER TABLE CLN_RELATIONSHIP ALTER COLUMN  CONTENT_ORDER INT NOT NULL;

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('jsimon', '', NOW(), 'Add Not-Null Constraint (x2)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.11.0.xml', 'tm-1.9.0.feature-3138-2', '2.0.5', '3:a57d42d8ec0b04084c9913a8e3ded8c8', 331);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.11.0.xml::tm-1.9.0.feature-3138-4::jsimon::(Checksum: 3:c88ef675a0232a59c1db71d28f5b7e1b)
ALTER TABLE RLN_RELATIONSHIP ALTER COLUMN  CONTENT_ORDER INT NOT NULL;

ALTER TABLE REQUIREMENT_LIBRARY_CONTENT ALTER COLUMN  CONTENT_ORDER INT NOT NULL;

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('jsimon', '', NOW(), 'Add Not-Null Constraint (x2)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.11.0.xml', 'tm-1.9.0.feature-3138-4', '2.0.5', '3:c88ef675a0232a59c1db71d28f5b7e1b', 332);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.11.0.xml::tm-1.9.0.feature-3138-6::jsimon::(Checksum: 3:b5f2d2aca472ea6d775e34406cdef2c6)
ALTER TABLE TCLN_RELATIONSHIP ALTER COLUMN  CONTENT_ORDER INT NOT NULL;

ALTER TABLE TEST_CASE_LIBRARY_CONTENT ALTER COLUMN  CONTENT_ORDER INT NOT NULL;

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('jsimon', '', NOW(), 'Add Not-Null Constraint (x2)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.11.0.xml', 'tm-1.9.0.feature-3138-6', '2.0.5', '3:b5f2d2aca472ea6d775e34406cdef2c6', 333);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.11.0.xml::tm-1.11.0.feature-3693-1::bsiri::(Checksum: 3:e03b49c622195764377e3a0f1df4c3df)
-- Adding support for call-step level management of datasets
ALTER TABLE CALL_TEST_STEP ADD CALLED_DATASET BIGINT DEFAULT null;

ALTER TABLE CALL_TEST_STEP ADD DELEGATE_PARAMETER_VALUES BOOLEAN DEFAULT FALSE NOT NULL;

ALTER TABLE CALL_TEST_STEP ADD CONSTRAINT fk_call_step_dataset FOREIGN KEY (CALLED_DATASET) REFERENCES DATASET (DATASET_ID) ON DELETE SET NULL;

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'Adding support for call-step level management of datasets', NOW(), 'Add Column, 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.11.0.xml', 'tm-1.11.0.feature-3693-1', '2.0.5', '3:e03b49c622195764377e3a0f1df4c3df', 334);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.11.0.xml::tm-1.11.0.feature-3693-2::bsiri::(Checksum: 3:f584c9f6658d1d4cc49caa0ab18ce9de)
-- For back compatibility, the call steps migrating from an existing DB will now be set to delegate the parameters
UPDATE CALL_TEST_STEP SET DELEGATE_PARAMETER_VALUES = TRUE;

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'For back compatibility, the call steps migrating from an existing DB will now be set to delegate the parameters', 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.11.0.xml', 'tm-1.11.0.feature-3693-2', '2.0.5', '3:f584c9f6658d1d4cc49caa0ab18ce9de', 335);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.11.0.xml::tm-1.11.0.feature-3700-1::bsiri::(Checksum: 3:add390e7eec45f884a462042b1536218)
-- Items of a campaign test plan can now reference Datasets
ALTER TABLE CAMPAIGN_TEST_PLAN_ITEM ADD DATASET_ID BIGINT;

ALTER TABLE CAMPAIGN_TEST_PLAN_ITEM ADD CONSTRAINT fk_camp_test_plan_item_dataset FOREIGN KEY (DATASET_ID) REFERENCES DATASET (DATASET_ID);

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'Items of a campaign test plan can now reference Datasets', 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.11.0.xml', 'tm-1.11.0.feature-3700-1', '2.0.5', '3:add390e7eec45f884a462042b1536218', 336);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.11.0.xml::tm-1.11.0.feature-3701-1::bsiri::(Checksum: 3:7dbee782206c249e6d9efd793924d7e0)
-- An execution now remembers which dataset was used (or not). That new column encodes three states : 
-- 				"" -> no dataset used,
-- 				somelabel -> the label of the dataset used,
-- 				null -> the test case had no parameters anyway
ALTER TABLE EXECUTION ADD DATASET_LABEL VARCHAR(255);

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'An execution now remembers which dataset was used (or not). That new column encodes three states : 
				"" -> no dataset used,
				somelabel -> the label of the dataset used,
				null -> the test case had no parameters anyway', 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.11.0.xml', 'tm-1.11.0.feature-3701-1', '2.0.5', '3:7dbee782206c249e6d9efd793924d7e0', 337);

-- Release Database Lock
-- Release Database Lock
