-- *********************************************************************
-- 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.15.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.15.0.xml::tm-1.15.0::jthebault::(Checksum: 3:491b8b7cb413e48bffcb92dc9104f1ce)
-- Update TM database version number
UPDATE CORE_CONFIG SET VALUE = '1.15.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.15.0.xml', 'tm-1.15.0', '2.0.5', '3:491b8b7cb413e48bffcb92dc9104f1ce', 395);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.15.0.xml::tm-1.15.0-feature-6365::jthebault::(Checksum: 3:f4b28600fd2a783b6589696a146d2f5e)
-- column for numeric cufs
ALTER TABLE CUSTOM_FIELD ADD NUMERIC_DEFAULT_VALUE DECIMAL(20,5);

COMMENT ON COLUMN CUSTOM_FIELD.NUMERIC_DEFAULT_VALUE IS 'a column to persist numeric cuf';

ALTER TABLE CUSTOM_FIELD_VALUE ADD NUMERIC_VALUE DECIMAL(20,5);

COMMENT ON COLUMN CUSTOM_FIELD_VALUE.NUMERIC_VALUE IS 'a column to persist numeric cuf';

ALTER TABLE DENORMALIZED_FIELD_VALUE ADD NUMERIC_VALUE DECIMAL(20,5);

COMMENT ON COLUMN DENORMALIZED_FIELD_VALUE.NUMERIC_VALUE IS 'a column to persist denormalized numeric cuf';

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('jthebault', 'column for numeric cufs', NOW(), 'Add Column (x3)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.15.0.xml', 'tm-1.15.0-feature-6365', '2.0.5', '3:f4b28600fd2a783b6589696a146d2f5e', 396);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.15.0.xml::tm-1.15.0-cuf-custom-report-1::jthebault::(Checksum: 3:d19c45da1f21f1cd26ebc1211d464884)
-- Denormalize the cuf ID inside the cuf values, so we don't have to make two more joins in custom reports
CREATE TABLE TEMP_CUF_ID (CFV_ID BIGINT, CF_ID BIGINT);

CREATE INDEX idx_tmp_cuf_cuf_id ON TEMP_CUF_ID(CF_ID);

CREATE INDEX idx_tmp_cuf_cfv_id ON TEMP_CUF_ID(CFV_ID);

ALTER TABLE CUSTOM_FIELD_VALUE ADD CF_ID BIGINT;

COMMENT ON COLUMN CUSTOM_FIELD_VALUE.CF_ID IS 'dernomalize the custom field id for custom report queries';

CREATE INDEX idx_cfv_custom_field_id ON CUSTOM_FIELD_VALUE(CF_ID);

insert into TEMP_CUF_ID (CFV_ID,CF_ID)
        select cfv.CFV_ID, cfb.CF_ID
        from CUSTOM_FIELD_VALUE cfv
        join CUSTOM_FIELD_BINDING cfb on cfv.CFB_ID = cfb.CFB_ID;

update CUSTOM_FIELD_VALUE
      set CF_ID = (
        select TEMP_CUF_ID.CF_ID
        from TEMP_CUF_ID
        where TEMP_CUF_ID.CFV_ID = CUSTOM_FIELD_VALUE.CFV_ID
      );

DROP TABLE TEMP_CUF_ID;

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('jthebault', 'Denormalize the cuf ID inside the cuf values, so we don''t have to make two more joins in custom reports', NOW(), 'Create Table, Create Index (x2), Add Column, Create Index, Custom SQL, Drop Table', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.15.0.xml', 'tm-1.15.0-cuf-custom-report-1', '2.0.5', '3:d19c45da1f21f1cd26ebc1211d464884', 397);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.15.0.xml::tm-1.15.0-cuf-custom-report-2::jthebault::(Checksum: 3:b69a3b17b516ce363a8e562380f16664)
-- columns prototypes for custom fields bound to execution.
insert into CHART_COLUMN_PROTOTYPE(CHART_COLUMN_ID,COLUMN_TYPE, BUSINESS, LABEL, ENTITY_TYPE, ENTITY_ROLE, DATA_TYPE, ATTRIBUTE_NAME, SUBQUERY_ID)
      values
      (95,'CUF', TRUE, 'REQUIREMENT_VERSION_CUF_TEXT', 'REQUIREMENT_VERSION', 'CUSTOM_FIELD', 'STRING', 'value', null),
      (96,'CUF', TRUE, 'REQUIREMENT_VERSION_CUF_CHECKBOX', 'REQUIREMENT_VERSION', 'CUSTOM_FIELD', 'BOOLEAN_AS_STRING', 'value', null),
      (97,'CUF', TRUE, 'REQUIREMENT_VERSION_CUF_LIST', 'REQUIREMENT_VERSION', 'CUSTOM_FIELD', 'LIST', 'value', null),
      (98,'CUF', TRUE, 'REQUIREMENT_VERSION_CUF_DATE', 'REQUIREMENT_VERSION', 'CUSTOM_FIELD', 'DATE_AS_STRING', 'value', null),
      (99,'CUF', TRUE, 'REQUIREMENT_VERSION_CUF_TAG', 'REQUIREMENT_VERSION', 'CUSTOM_FIELD', 'TAG', 'value', null),
      (100,'CUF', TRUE, 'REQUIREMENT_VERSION_CUF_NUMERIC', 'REQUIREMENT_VERSION', 'CUSTOM_FIELD', 'NUMERIC', 'value', null);

insert into CHART_COLUMN_ROLE(CHART_COLUMN_ID, ROLE)
      values
      (95, 'MEASURE'), (95, 'AXIS'), (95, 'FILTER'),
      (96, 'MEASURE'), (96, 'AXIS'), (96, 'FILTER'),
      (97, 'MEASURE'), (97, 'AXIS'), (97, 'FILTER'),
      (98, 'MEASURE'), (98, 'AXIS'), (98, 'FILTER'),
      (99, 'MEASURE'), (99, 'AXIS'), (99, 'FILTER'),
      (100, 'MEASURE'), (100, 'AXIS'), (100, 'FILTER');

insert into CHART_COLUMN_PROTOTYPE(CHART_COLUMN_ID,COLUMN_TYPE, BUSINESS, LABEL, ENTITY_TYPE, ENTITY_ROLE, DATA_TYPE, ATTRIBUTE_NAME, SUBQUERY_ID)
      values
      (101,'CUF', TRUE, 'TEST_CASE_CUF_TEXT', 'TEST_CASE', 'CUSTOM_FIELD', 'STRING', 'value', null),
      (102,'CUF', TRUE, 'TEST_CASE_CUF_CHECKBOX', 'TEST_CASE', 'CUSTOM_FIELD', 'BOOLEAN_AS_STRING', 'value', null),
      (103,'CUF', TRUE, 'TEST_CASE_CUF_LIST', 'TEST_CASE', 'CUSTOM_FIELD', 'LIST', 'value', null),
      (104,'CUF', TRUE, 'TEST_CASE_CUF_DATE', 'TEST_CASE', 'CUSTOM_FIELD', 'DATE_AS_STRING', 'value', null),
      (105,'CUF', TRUE, 'TEST_CASE_CUF_TAG', 'TEST_CASE', 'CUSTOM_FIELD', 'TAG', 'value', null),
      (106,'CUF', TRUE, 'TEST_CASE_CUF_NUMERIC', 'TEST_CASE', 'CUSTOM_FIELD', 'NUMERIC', 'value', null);

insert into CHART_COLUMN_ROLE(CHART_COLUMN_ID, ROLE)
      values
      (101, 'MEASURE'), (101, 'AXIS'), (101, 'FILTER'),
      (102, 'MEASURE'), (102, 'AXIS'), (102, 'FILTER'),
      (103, 'MEASURE'), (103, 'AXIS'), (103, 'FILTER'),
      (104, 'MEASURE'), (104, 'AXIS'), (104, 'FILTER'),
      (105, 'MEASURE'), (105, 'AXIS'), (105, 'FILTER'),
      (106, 'MEASURE'), (106, 'AXIS'), (106, 'FILTER');

insert into CHART_COLUMN_PROTOTYPE(CHART_COLUMN_ID,COLUMN_TYPE, BUSINESS, LABEL, ENTITY_TYPE, ENTITY_ROLE, DATA_TYPE, ATTRIBUTE_NAME, SUBQUERY_ID)
      values
      (107,'CUF', TRUE, 'CAMPAIGN_CUF_TEXT', 'CAMPAIGN', 'CUSTOM_FIELD', 'STRING', 'value', null),
      (108,'CUF', TRUE, 'CAMPAIGN_CUF_CHECKBOX', 'CAMPAIGN', 'CUSTOM_FIELD', 'BOOLEAN_AS_STRING', 'value', null),
      (109,'CUF', TRUE, 'CAMPAIGN_CUF_LIST', 'CAMPAIGN', 'CUSTOM_FIELD', 'LIST', 'value', null),
      (110,'CUF', TRUE, 'CAMPAIGN_CUF_DATE', 'CAMPAIGN', 'CUSTOM_FIELD', 'DATE_AS_STRING', 'value', null),
      (111,'CUF', TRUE, 'CAMPAIGN_CUF_TAG', 'CAMPAIGN', 'CUSTOM_FIELD', 'TAG', 'value', null),
      (112,'CUF', TRUE, 'CAMPAIGN_CUF_NUMERIC', 'CAMPAIGN', 'CUSTOM_FIELD', 'NUMERIC', 'value', null);

insert into CHART_COLUMN_ROLE(CHART_COLUMN_ID, ROLE)
      values
      (107, 'MEASURE'), (107, 'AXIS'), (107, 'FILTER'),
      (108, 'MEASURE'), (108, 'AXIS'), (108, 'FILTER'),
      (109, 'MEASURE'), (109, 'AXIS'), (109, 'FILTER'),
      (110, 'MEASURE'), (110, 'AXIS'), (110, 'FILTER'),
      (111, 'MEASURE'), (111, 'AXIS'), (111, 'FILTER'),
      (112, 'MEASURE'), (112, 'AXIS'), (112, 'FILTER');

insert into CHART_COLUMN_PROTOTYPE(CHART_COLUMN_ID,COLUMN_TYPE, BUSINESS, LABEL, ENTITY_TYPE, ENTITY_ROLE, DATA_TYPE, ATTRIBUTE_NAME, SUBQUERY_ID)
      values
      (113,'CUF', TRUE, 'ITERATION_CUF_TEXT', 'ITERATION', 'CUSTOM_FIELD', 'STRING', 'value', null),
      (114,'CUF', TRUE, 'ITERATION_CUF_CHECKBOX', 'ITERATION', 'CUSTOM_FIELD', 'BOOLEAN_AS_STRING', 'value', null),
      (115,'CUF', TRUE, 'ITERATION_CUF_LIST', 'ITERATION', 'CUSTOM_FIELD', 'LIST', 'value', null),
      (116,'CUF', TRUE, 'ITERATION_CUF_DATE', 'ITERATION', 'CUSTOM_FIELD', 'DATE_AS_STRING', 'value', null),
      (117,'CUF', TRUE, 'ITERATION_CUF_TAG', 'ITERATION', 'CUSTOM_FIELD', 'TAG', 'value', null),
      (118,'CUF', TRUE, 'ITERATION_CUF_NUMERIC', 'ITERATION', 'CUSTOM_FIELD', 'NUMERIC', 'value', null);

insert into CHART_COLUMN_ROLE(CHART_COLUMN_ID, ROLE)
      values
      (113, 'MEASURE'), (113, 'AXIS'), (113, 'FILTER'),
      (114, 'MEASURE'), (114, 'AXIS'), (114, 'FILTER'),
      (115, 'MEASURE'), (115, 'AXIS'), (115, 'FILTER'),
      (116, 'MEASURE'), (116, 'AXIS'), (116, 'FILTER'),
      (117, 'MEASURE'), (117, 'AXIS'), (117, 'FILTER'),
      (118, 'MEASURE'), (118, 'AXIS'), (118, 'FILTER');

insert into CHART_COLUMN_PROTOTYPE(CHART_COLUMN_ID,COLUMN_TYPE, BUSINESS, LABEL, ENTITY_TYPE, ENTITY_ROLE, DATA_TYPE, ATTRIBUTE_NAME, SUBQUERY_ID)
      values
      (119,'CUF', TRUE, 'EXECUTION_CUF_TEXT', 'EXECUTION', 'CUSTOM_FIELD', 'STRING', 'value', null),
      (120,'CUF', TRUE, 'EXECUTION_CUF_CHECKBOX', 'EXECUTION', 'CUSTOM_FIELD', 'BOOLEAN_AS_STRING', 'value', null),
      (121,'CUF', TRUE, 'EXECUTION_CUF_LIST', 'EXECUTION', 'CUSTOM_FIELD', 'LIST', 'value', null),
      (122,'CUF', TRUE, 'EXECUTION_CUF_DATE', 'EXECUTION', 'CUSTOM_FIELD', 'DATE_AS_STRING', 'value', null),
      (123,'CUF', TRUE, 'EXECUTION_CUF_TAG', 'EXECUTION', 'CUSTOM_FIELD', 'TAG', 'value', null),
      (124,'CUF', TRUE, 'EXECUTION_CUF_NUMERIC', 'EXECUTION', 'CUSTOM_FIELD', 'NUMERIC', 'value', null);

insert into CHART_COLUMN_ROLE(CHART_COLUMN_ID, ROLE)
      values
      (119, 'MEASURE'), (119, 'AXIS'), (119, 'FILTER'),
      (120, 'MEASURE'), (120, 'AXIS'), (120, 'FILTER'),
      (121, 'MEASURE'), (121, 'AXIS'), (121, 'FILTER'),
      (122, 'MEASURE'), (122, 'AXIS'), (122, 'FILTER'),
      (123, 'MEASURE'), (123, 'AXIS'), (123, 'FILTER'),
      (124, 'MEASURE'), (124, 'AXIS'), (124, 'FILTER');

ALTER TABLE CHART_MEASURE_COLUMN ADD CUF_ID BIGINT;

COMMENT ON COLUMN CHART_MEASURE_COLUMN.CUF_ID IS 'a column to persist cuf id relative to this measure';

ALTER TABLE CHART_AXIS_COLUMN ADD CUF_ID BIGINT;

COMMENT ON COLUMN CHART_AXIS_COLUMN.CUF_ID IS 'a column to persist cuf id relative to this axis';

ALTER TABLE CHART_FILTER ADD CUF_ID BIGINT;

COMMENT ON COLUMN CHART_FILTER.CUF_ID IS 'a column to persist cuf id relative to this filter';

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('jthebault', 'columns prototypes for custom fields bound to execution.', NOW(), 'Custom SQL (x10), Add Column (x3)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.15.0.xml', 'tm-1.15.0-cuf-custom-report-2', '2.0.5', '3:b69a3b17b516ce363a8e562380f16664', 398);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.15.0.xml::tm-1.15.0-feat-5417::jthebault::(Checksum: 3:eb7d59c34555f8f8a4aaa25dfdda5091)
ALTER TABLE CHART_DEFINITION ADD SCOPE_TYPE VARCHAR(20);

COMMENT ON COLUMN CHART_DEFINITION.SCOPE_TYPE IS 'a column to persist the scope type';

update CHART_DEFINITION
      set SCOPE_TYPE = 'PROJECTS'
      where CHART_ID in
      (select CHART_ID from CHART_SCOPE where ENTITY_REFERENCE_TYPE like 'PROJECT');

update CHART_DEFINITION
      set SCOPE_TYPE = 'CUSTOM'
      where SCOPE_TYPE IS NULL;

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('jthebault', '', NOW(), 'Add Column, Custom SQL', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.15.0.xml', 'tm-1.15.0-feat-5417', '2.0.5', '3:eb7d59c34555f8f8a4aaa25dfdda5091', 399);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.15.0.xml::tm-1.15.0-feat-5417-2::jthebault::(Checksum: 3:b302c88e1251e2a3ccf4a50c0c4ed15c)
update PARTY_PREFERENCE
      set PREFERENCE_VALUE = 'default'
      where PREFERENCE_VALUE = 'message' and PREFERENCE_KEY = 'squash.core.home.content';

update PARTY_PREFERENCE
      set PREFERENCE_KEY = 'squash.core.dashboard.content.home'
      where PREFERENCE_KEY = 'squash.core.home.content';

update PARTY_PREFERENCE
      set PREFERENCE_KEY = 'squash.core.favorite.dashboard.home'
      where PREFERENCE_KEY = 'squash.core.favorite.dashboard';

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('jthebault', '', NOW(), 'Custom SQL', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.15.0.xml', 'tm-1.15.0-feat-5417-2', '2.0.5', '3:b302c88e1251e2a3ccf4a50c0c4ed15c', 400);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.15.0.xml::tm-1.15.0-feat-5417-3::jthebault::(Checksum: 3:f9a41fbfcfe55bba82018583a5552582)
insert into CHART_COLUMN_PROTOTYPE(CHART_COLUMN_ID,COLUMN_TYPE, BUSINESS, LABEL, ENTITY_TYPE, ENTITY_ROLE, DATA_TYPE, ATTRIBUTE_NAME, SUBQUERY_ID)
      values
      (125,'ATTRIBUTE', FALSE, 'CAMPAIGN_MILESTONE_ID', 'MILESTONE', 'CAMPAIGN_MILESTONE', 'NUMERIC', 'id', null);

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('jthebault', '', NOW(), 'Custom SQL', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.15.0.xml', 'tm-1.15.0-feat-5417-3', '2.0.5', '3:f9a41fbfcfe55bba82018583a5552582', 401);

-- Release Database Lock
-- Release Database Lock
