--  *********************************************************************
--  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.10.5.xml
--  Ran at: 03/04/20 12:06
--  Against: squashtest_adm@localhost@jdbc:mysql://localhost:3306/squashtest_maint
--  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.10.5.xml::tm-1.10.5::mpagnon::(Checksum: 3:8d872fc80ee4cdeb76f181e1a76e1272)
--  Update TM database version number
UPDATE `CORE_CONFIG` SET `VALUE` = '1.10.5' WHERE STR_KEY = 'squashtest.tm.database.version';

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('mpagnon', '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.10.5.xml', 'tm-1.10.5', '2.0.5', '3:8d872fc80ee4cdeb76f181e1a76e1272', 323);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.10.5.xml::tm-1.10.5-issue-3860-1::mpagnon::(Checksum: 3:616f88306a30b38496906a242633749f)
--  Removing duplicate values
CREATE TABLE `CUF_VALUES_TO_SAVE` (`id` BIGINT NULL);

CREATE UNIQUE INDEX `idx_cuf_values_to_save` ON `CUF_VALUES_TO_SAVE`(`id`);

insert into CUF_VALUES_TO_SAVE (id)
      select min(CFV_ID)
      from CUSTOM_FIELD_VALUE cfv
      where cfv.BOUND_ENTITY_TYPE = 'TEST_STEP'
      group by cfv.BOUND_ENTITY_ID, cfv.CFB_ID;

delete from CUSTOM_FIELD_VALUE
      where BOUND_ENTITY_TYPE = 'TEST_STEP' and
      not exists (select 1 from CUF_VALUES_TO_SAVE cfvts where cfvts.id = cfv_id);

DROP INDEX `idx_cuf_values_to_save` ON `CUF_VALUES_TO_SAVE`;

DROP TABLE `CUF_VALUES_TO_SAVE`;

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('mpagnon', 'Removing duplicate values', NOW(), 'Create Table, Create Index, Custom SQL (x2), Drop Index, Drop Table', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.10.5.xml', 'tm-1.10.5-issue-3860-1', '2.0.5', '3:616f88306a30b38496906a242633749f', 324);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.10.5.xml::tm-1.10.5-issue-3860-3::mpagnon::(Checksum: 3:38c89155e896864447e3b3ddef7a31c9)
--  add missing custom field values
CREATE TABLE `STEPS_SHOULD_HAVE_CUF_VALUES` (`cuf_binding_id` BIGINT NULL, `step_id` BIGINT NULL, `default_value` VARCHAR(255) NULL, `optional` TINYINT(1) NULL);

insert into STEPS_SHOULD_HAVE_CUF_VALUES (cuf_binding_id , step_id , default_value , optional )
      select
      cfb.CFB_ID, ats.TEST_STEP_ID, cf.default_value, cf.optional
      from ACTION_TEST_STEP ats, TEST_CASE_STEPS tcs,
      TEST_CASE_LIBRARY_NODE tcln, CUSTOM_FIELD_BINDING cfb, CUSTOM_FIELD cf
      where ats.TEST_STEP_ID = tcs.STEP_ID
      and
      tcln.TCLN_ID = tcs.TEST_CASE_ID
      and tcln.PROJECT_ID = cfb.BOUND_PROJECT_ID
      and cfb.CF_ID = cf.CF_ID
      and cfb.BOUND_ENTITY = 'TEST_STEP'
      and not exists (
      select 1
      from CUSTOM_FIELD_VALUE cfv
      where cfv.CFB_ID = cfb.CFB_ID
      and cfv.BOUND_ENTITY_TYPE = 'TEST_STEP'
      and
      cfv.BOUND_ENTITY_ID = ats.TEST_STEP_ID
      );

insert into CUSTOM_FIELD_VALUE ( BOUND_ENTITY_ID, BOUND_ENTITY_TYPE, CFB_ID, VALUE)
      select sshcv.step_id ,
      'TEST_STEP', sshcv.cuf_binding_id, ''
      from STEPS_SHOULD_HAVE_CUF_VALUES sshcv
      where sshcv.optional = true;

insert into CUSTOM_FIELD_VALUE ( BOUND_ENTITY_ID, BOUND_ENTITY_TYPE, CFB_ID, VALUE)
      select sshcv.step_id ,
      'TEST_STEP', sshcv.cuf_binding_id, sshcv.default_value
      from STEPS_SHOULD_HAVE_CUF_VALUES sshcv
      where sshcv.optional
      = false;

DROP TABLE `STEPS_SHOULD_HAVE_CUF_VALUES`;

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('mpagnon', 'add missing custom field values', NOW(), 'Create Table, Custom SQL (x3), Drop Table', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.10.5.xml', 'tm-1.10.5-issue-3860-3', '2.0.5', '3:38c89155e896864447e3b3ddef7a31c9', 325);

--  Release Database Lock
--  Release Database Lock
