-- *********************************************************************
-- 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.8.2.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.8.2.xml::tm-1.8.2::flaurens::(Checksum: 3:a80c0bc8924350b49417c17f22984eae)
-- Update TM database version number
UPDATE CORE_CONFIG SET VALUE = '1.8.2' 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.8.2.xml', 'tm-1.8.2', '2.0.5', '3:a80c0bc8924350b49417c17f22984eae', 292);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.2.xml::tm-1.8.2.feature-3120-01::flaurens::(Checksum: 3:0544e35ce7de06b104c3ec004ecf6f5a)
-- Adding denormalized field option table for editable denormalized list valued cufs
CREATE TABLE DENORMALIZED_FIELD_OPTION (DFV_ID BIGINT NOT NULL, LABEL VARCHAR(255) NOT NULL, POSITION INT NOT NULL, CODE VARCHAR(30) NOT NULL, CONSTRAINT fk_df_option_dfv FOREIGN KEY (DFV_ID) REFERENCES DENORMALIZED_FIELD_VALUE(DFV_ID));

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('flaurens', 'Adding denormalized field option table for editable denormalized list valued cufs', 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.8.2.xml', 'tm-1.8.2.feature-3120-01', '2.0.5', '3:0544e35ce7de06b104c3ec004ecf6f5a', 293);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.2.xml::tm-1.8.2.feature-3120-02::flaurens::(Checksum: 3:50583ee81c3b72676fb0b6e0654a67d8)
-- Old list valued cufs are editable as simple text cufs
UPDATE DENORMALIZED_FIELD_VALUE SET INPUT_TYPE = 'PLAIN_TEXT' WHERE INPUT_TYPE = 'DROPDOWN_LIST';

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('flaurens', 'Old list valued cufs are editable as simple text cufs', 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.8.2.xml', 'tm-1.8.2.feature-3120-02', '2.0.5', '3:50583ee81c3b72676fb0b6e0654a67d8', 294);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.2.xml::tm-1.8.2.feature-3120-03::flaurens::(Checksum: 3:5fef6b2dc7deda02148330646469a994)
-- Adding a field type column to denormalized field value table
ALTER TABLE DENORMALIZED_FIELD_VALUE ADD FIELD_TYPE CHAR(3) DEFAULT 'CF' NOT NULL;

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('flaurens', 'Adding a field type column to denormalized field value 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.8.2.xml', 'tm-1.8.2.feature-3120-03', '2.0.5', '3:5fef6b2dc7deda02148330646469a994', 295);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.2.xml::tm-1.8.2-feat-3127-01::bsiri::(Checksum: 3:871537e3ea13f4520d42ebc1983cb96f)
-- As of now X_LIBRARY_PLUGINS are merged in the single table LIBRARY_PLUGIN_BINDING
CREATE TABLE LIBRARY_PLUGIN_BINDING (PLUGIN_BINDING_ID BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL, LIBRARY_ID BIGINT, LIBRARY_TYPE CHAR(1) NOT NULL, PLUGIN_ID VARCHAR(50) NOT NULL, CONSTRAINT PK_LIBRARY_PLUGIN_BINDING PRIMARY KEY (PLUGIN_BINDING_ID));

ALTER TABLE LIBRARY_PLUGIN_BINDING ADD CONSTRAINT uniq_plugin_binding UNIQUE (LIBRARY_ID, LIBRARY_TYPE, PLUGIN_ID);

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'As of now X_LIBRARY_PLUGINS are merged in the single table LIBRARY_PLUGIN_BINDING', 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.8.2.xml', 'tm-1.8.2-feat-3127-01', '2.0.5', '3:871537e3ea13f4520d42ebc1983cb96f', 296);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.2.xml::tm-1.8.2-feat-3127-02::bsiri::(Checksum: 3:85c9aaac4c23f3931279ac88f3da3bef)
-- create the table LIBRARY_PLUGIN_PROPERTY
CREATE TABLE LIBRARY_PLUGIN_BINDING_PROPERTY (PLUGIN_BINDING_ID BIGINT NOT NULL, PLUGIN_BINDING_KEY VARCHAR(30) NOT NULL, PLUGIN_BINDING_VALUE VARCHAR(255) NOT NULL, CONSTRAINT fk_libplugin_property_libplugin FOREIGN KEY (PLUGIN_BINDING_ID) REFERENCES LIBRARY_PLUGIN_BINDING(PLUGIN_BINDING_ID));

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'create the table LIBRARY_PLUGIN_PROPERTY', 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.8.2.xml', 'tm-1.8.2-feat-3127-02', '2.0.5', '3:85c9aaac4c23f3931279ac88f3da3bef', 297);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.2.xml::tm-1.8.2-feat-3127-03::bsiri::(Checksum: 3:3aefb0b60a5938f3ffc301a3a33c4e79)
-- drop the old tables
DROP TABLE TEST_CASE_LIBRARY_PLUGINS;

DROP TABLE REQUIREMENT_LIBRARY_PLUGINS;

DROP TABLE CAMPAIGN_LIBRARY_PLUGINS;

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'drop the old tables', NOW(), 'Drop Table (x3)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.8.2.xml', 'tm-1.8.2-feat-3127-03', '2.0.5', '3:3aefb0b60a5938f3ffc301a3a33c4e79', 298);

-- Release Database Lock
-- Release Database Lock
