-- *********************************************************************
-- 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.5.1.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.5.1.xml::tm-1.5.1::mpagnon::(Checksum: 3:fbb40866e9b269ec1b80b00d1c53f45a)
-- Update TM database version number
UPDATE CORE_CONFIG SET STR_KEY = 'squashtest.tm.database.version', VALUE = '1.5.1' 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.5.1.xml', 'tm-1.5.1', '2.0.5', '3:fbb40866e9b269ec1b80b00d1c53f45a', 210);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.5.1.xml::tm-1.5.0.issue-2261-01::mpagnon,bsiri::(Checksum: 3:87b6ca96153ba3bbe35ffd0891b70653)
-- First we create help table that will help us match the iteration test plan orders with the test-suite's test plan items.
-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.5.1.xml::tm-1.5.0.issue-2261-02-postgresql::mpagnon,bsiri::(Checksum: 3:ae502a919be31d44b91ceff5c97fe8ab)
-- enforce the security on that column
-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.5.1.xml::tm-1.5.0.issue-2261-02::mpagnon,bsiri::(Checksum: 3:3740e86f3d5046ef904a254c4b16b400)
-- First we create help table that will help us match the iteration test plan orders with the test-suite's test plan items.
-- ------------------------------------------------------------------------------
      -- create the temporary tables
      -- ------------------------------------------------------------------------------
      
      create table TEMP_DISCONTINUOUS_SUITE_ITEM(ITEM_ID BIGINT NOT NULL, SUITE_ID BIGINT NOT NULL);

create table TEMP_CONTINUOUS_SUITE_ITEM(ITEM_ID BIGINT NOT NULL, SUITE_ID BIGINT NOT NULL, TP_ORDER INT NOT NULL, DUMMY BIGINT NOT NULL);

-- ------------------------------------------------------------------------------
      -- gather the data that need migration
      -- ------------------------------------------------------------------------------
            
      
      insert into TEMP_DISCONTINUOUS_SUITE_ITEM (SUITE_ID, ITEM_ID) 
      select tstpitem.SUITE_ID, tstpitem.TPI_ID 
      from TEST_SUITE_TEST_PLAN_ITEM tstpitem 
      where
        tstpitem.SUITE_ID in ( 
          select distinct tstpi2.suite_id 
          from TEST_SUITE_TEST_PLAN_ITEM tstpi2
          where tstpi2.TEST_PLAN_ORDER is null
        );

-- ------------------------------------------------------------------------------
      -- Code for h2 database to find continuous orders for test suite test plan.
      -- Then we compute continuous orders for test-suite test plan items
      -- ------------------------------------------------------------------------------
      
      set @rn=-1;

set @prevsuite=0;

insert into TEMP_CONTINUOUS_SUITE_ITEM ( TP_ORDER, SUITE_ID, ITEM_ID,
      DUMMY)
      select casewhen(@prevsuite = SUITE_ID, set(@rn, @rn+1),
      set(@rn, 0)) TP_ORDER, SUITE_ID, ITEM_ID, set(@prevsuite, SUITE_ID)
      from
      TEMP_DISCONTINUOUS_SUITE_ITEM tdsi
      order by SUITE_ID, TP_ORDER;

-- ------------------------------------------------------------------------------
      -- And finally we set the right orders in the concerned table without forgetting to delete the help tables.
      -- ------------------------------------------------------------------------------
      
      update TEST_SUITE_TEST_PLAN_ITEM
      set TEST_PLAN_ORDER = 
        (select TEMP_CONTINUOUS_SUITE_ITEM.TP_ORDER
        from  TEMP_CONTINUOUS_SUITE_ITEM
        where TEMP_CONTINUOUS_SUITE_ITEM.SUITE_ID = TEST_SUITE_TEST_PLAN_ITEM.SUITE_ID
        and TEST_SUITE_TEST_PLAN_ITEM.TPI_ID = TEMP_CONTINUOUS_SUITE_ITEM.ITEM_ID)
      where 
        SUITE_ID in (
          select distinct tcsi2.SUITE_ID from TEMP_CONTINUOUS_SUITE_ITEM tcsi2
        );

-- ------------------------------------------------------------------------------
      -- drop the temp tables
      -- ------------------------------------------------------------------------------
      
      drop table TEMP_DISCONTINUOUS_SUITE_ITEM;

drop table TEMP_CONTINUOUS_SUITE_ITEM;

-- ------------------------------------------------------------------------------
      -- enforce the security on that column
      -- ------------------------------------------------------------------------------
      
      alter table TEST_SUITE_TEST_PLAN_ITEM alter TEST_PLAN_ORDER INT NOT NULL;

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon,bsiri', 'First we create help table that will help us match the iteration test plan orders with the test-suite''s test plan items.', 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.5.1.xml', 'tm-1.5.0.issue-2261-02', '2.0.5', '3:3740e86f3d5046ef904a254c4b16b400', 211);

-- Release Database Lock
-- Release Database Lock
