-- *********************************************************************
-- Update Database Script
-- *********************************************************************
-- Change Log: /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/upgrade/upgrade.changelog-up-to-1.10.5.xml
-- Ran at: 04/03/15 11:45
-- Against: SA@jdbc:h2:/var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/incremental-upgrade-database/squash-tm
-- Liquibase version: 2.0.5
-- *********************************************************************

-- Lock Database
-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/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/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.10.5.xml', 'tm-1.10.5', '2.0.5', '3:8d872fc80ee4cdeb76f181e1a76e1272', 322);

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

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;

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/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:4713bc5ed9bad537b8050a3ebc9109d7', 323);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.10.5.xml::tm-1.10.5-issue-3860-3::mpagnon::(Checksum: 3:7b9afad7915d8bbda1670201ba2e9c8c)
-- add missing custom field values
CREATE TABLE steps_should_have_cuf_values (cuf_binding_id BIGINT, step_id BIGINT, default_value VARCHAR(255), optional BOOLEAN);

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/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:7b9afad7915d8bbda1670201ba2e9c8c', 324);

-- Release Database Lock
-- Release Database Lock
