--  *********************************************************************
--  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.21.0.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.21.0.xml::tm-1.21.0::jprioux::(Checksum: 3:3de25266fed570b489278e0a0ef2a4e9)
--  Update TM database version number
UPDATE `CORE_CONFIG` SET `VALUE` = '1.21.0' WHERE STR_KEY = 'squashtest.tm.database.version';

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('jprioux', '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.21.0.xml', 'tm-1.21.0', '2.0.5', '3:3de25266fed570b489278e0a0ef2a4e9', 481);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.21.0.xml::tm-1.21.0-TM-244-remote-automation-request-extender::jlor::(Checksum: 3:8dc9c229686aa18dc95cebf28a023310)
--  Create new table for RemoteAutomationRequestExtender
CREATE TABLE `REMOTE_AUTOMATION_REQUEST_EXTENDER` (`REMOTE_AUTOMATION_REQUEST_EXTENDER_ID` BIGINT AUTO_INCREMENT NOT NULL, `AUTOMATION_REQUEST_ID` BIGINT NOT NULL, `SERVER_ID` BIGINT NULL, `REMOTE_STATUS` VARCHAR(255) NULL, `REMOTE_ISSUE_KEY` VARCHAR(255) NOT NULL, `REMOTE_REQUEST_URL` VARCHAR(300) NULL, `REMOTE_ASSIGNED_TO` VARCHAR(300) NOT NULL, CONSTRAINT `PK_REMOTE_AUTOMATION_REQUEST_EXTENDER` PRIMARY KEY (`REMOTE_AUTOMATION_REQUEST_EXTENDER_ID`), CONSTRAINT `fk_remote_automation_request_extender_automation_request_id` FOREIGN KEY (`AUTOMATION_REQUEST_ID`) REFERENCES AUTOMATION_REQUEST (AUTOMATION_REQUEST_ID), CONSTRAINT `fk_remote_automation_request_extender_server_id` FOREIGN KEY (`SERVER_ID`) REFERENCES BUGTRACKER (BUGTRACKER_ID));

CREATE INDEX `idx_fk_remote_automation_request_extender_automation_request_id` ON `REMOTE_AUTOMATION_REQUEST_EXTENDER`(`AUTOMATION_REQUEST_ID`);

CREATE INDEX `idx_fk_remote_automation_request_extender_server_id` ON `REMOTE_AUTOMATION_REQUEST_EXTENDER`(`SERVER_ID`);

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('jlor', 'Create new table for RemoteAutomationRequestExtender', NOW(), 'Create Table, Create Index (x2)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.21.0.xml', 'tm-1.21.0-TM-244-remote-automation-request-extender', '2.0.5', '3:8dc9c229686aa18dc95cebf28a023310', 482);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.21.0.xml::tm-1.21.0-TM-248-automation-workflow-type::jlor::(Checksum: 3:db8a1bb56c55f31ba9457217ce21c542)
--  Modify column ALLOW_AUTOMATION_WORKFLOW into AUTOMATION_WORKFLOW_TYPE
ALTER TABLE `PROJECT` ADD `AUTOMATION_WORKFLOW_TYPE` VARCHAR(25) NOT NULL DEFAULT 'NONE';

update PROJECT
      set AUTOMATION_WORKFLOW_TYPE =
      case
      when ALLOW_AUTOMATION_WORKFLOW = true then 'NATIVE'
      else 'NONE'
      end;

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('jlor', 'Modify column ALLOW_AUTOMATION_WORKFLOW into AUTOMATION_WORKFLOW_TYPE', 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.21.0.xml', 'tm-1.21.0-TM-248-automation-workflow-type', '2.0.5', '3:db8a1bb56c55f31ba9457217ce21c542', 483);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.21.0.xml::tm-1.21.0-TM-248-plugin-type::amk::(Checksum: 3:a8abe06a93bad7ac3cfb96da0a4c8996)
--  add new column PLUGIN_TYPE into LIBRARY_PLUGIN_BINDING
ALTER TABLE `LIBRARY_PLUGIN_BINDING` ADD `PLUGIN_TYPE` VARCHAR(25);

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('amk', 'add new column PLUGIN_TYPE into LIBRARY_PLUGIN_BINDING', 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.21.0.xml', 'tm-1.21.0-TM-248-plugin-type', '2.0.5', '3:a8abe06a93bad7ac3cfb96da0a4c8996', 484);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.21.0.xml::tm-1.21.0-TM-211-active::amk::(Checksum: 3:6593378e5c157e88a01677406ab8b96c)
--  add new column ACTIVE into LIBRARY_PLUGIN_BINDING
ALTER TABLE `LIBRARY_PLUGIN_BINDING` ADD `ACTIVE` TINYINT(1) DEFAULT 1;

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('amk', 'add new column ACTIVE into LIBRARY_PLUGIN_BINDING', 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.21.0.xml', 'tm-1.21.0-TM-211-active', '2.0.5', '3:6593378e5c157e88a01677406ab8b96c', 485);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.21.0.xml::tm-1.21.0-TM-649-sync-enable::api::(Checksum: 3:5e0a2ca525eb084a42d1283a70cc8bf1)
--  add new column Sync_Enable into REMOTE_SYNCHONISATION
ALTER TABLE `REMOTE_SYNCHRONISATION` ADD `SYNC_ENABLE` TINYINT(1) NOT NULL DEFAULT 1;

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('api', 'add new column Sync_Enable into REMOTE_SYNCHONISATION', 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.21.0.xml', 'tm-1.21.0-TM-649-sync-enable', '2.0.5', '3:5e0a2ca525eb084a42d1283a70cc8bf1', 486);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.21.0.xml::tm-1.21.0-TM-211-last-sync-date::amk::(Checksum: 3:524769522739ee35799e2ab2bff89c54)
--  add new column LAST-SYNC-DATE into REMOTE_AUTOMATION_REQUEST_EXTENDER
ALTER TABLE `REMOTE_AUTOMATION_REQUEST_EXTENDER` ADD `LAST_SYNC_DATE` DATETIME DEFAULT null;

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('amk', 'add new column LAST-SYNC-DATE into REMOTE_AUTOMATION_REQUEST_EXTENDER', 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.21.0.xml', 'tm-1.21.0-TM-211-last-sync-date', '2.0.5', '3:524769522739ee35799e2ab2bff89c54', 487);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.21.0.xml::tm-1.21.0-TM-725-add-committer-mail-in-scm-server::jlor::(Checksum: 3:d14262f9fcaa9ca9142c9e0f8ff05628)
--  Add column COMMITTER_MAIL in SCM_SERVER
ALTER TABLE `SCM_SERVER` ADD `COMMITTER_MAIL` VARCHAR(255) NOT NULL DEFAULT '';

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('jlor', 'Add column COMMITTER_MAIL in SCM_SERVER', 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.21.0.xml', 'tm-1.21.0-TM-725-add-committer-mail-in-scm-server', '2.0.5', '3:d14262f9fcaa9ca9142c9e0f8ff05628', 488);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.21.0.xml::tm-1.21.0-TM-713-add-axis_and_measure-to-execution_is_auto::abo::(Checksum: 3:9b88c6309933383f5446526b1a2623e5)
INSERT INTO CHART_COLUMN_ROLE (CHART_COLUMN_ID, ROLE) values ((SELECT QUERY_COLUMN_ID FROM QUERY_COLUMN_PROTOTYPE WHERE QUERY_COLUMN_PROTOTYPE.LABEL = 'EXECUTION_ISAUTO'), 'MEASURE'),
      ((SELECT QUERY_COLUMN_ID FROM QUERY_COLUMN_PROTOTYPE WHERE QUERY_COLUMN_PROTOTYPE.LABEL = 'EXECUTION_ISAUTO'), 'AXIS');

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('abo', '', 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.21.0.xml', 'tm-1.21.0-TM-713-add-axis_and_measure-to-execution_is_auto', '2.0.5', '3:9b88c6309933383f5446526b1a2623e5', 489);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.21.0.xml::tm-1.21.0-TM-211-sent-value-for-sync::amk::(Checksum: 3:c35d46bb8ab7fe59bccae017807cf6b6)
--  add new column SENT_VALUE_FOR_SYNC into REMOTE_AUTOMATION_REQUEST_EXTENDER
ALTER TABLE `REMOTE_AUTOMATION_REQUEST_EXTENDER` ADD `SENT_VALUE_FOR_SYNC` LONGTEXT DEFAULT null;

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('amk', 'add new column SENT_VALUE_FOR_SYNC into REMOTE_AUTOMATION_REQUEST_EXTENDER', 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.21.0.xml', 'tm-1.21.0-TM-211-sent-value-for-sync', '2.0.5', '3:c35d46bb8ab7fe59bccae017807cf6b6', 490);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.21.0.xml::tm-1.21.0-TM-916-918-synchronizable-issue-status::amk::(Checksum: 3:27e3ce65341d7b7624b5d8b7db75c1d6)
--  add new column SYNCHRONIZABLE_ISSUE_STATUS into REMOTE_AUTOMATION_REQUEST_EXTENDER
ALTER TABLE `REMOTE_AUTOMATION_REQUEST_EXTENDER` ADD `SYNCHRONIZABLE_ISSUE_STATUS` VARCHAR(25);

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('amk', 'add new column SYNCHRONIZABLE_ISSUE_STATUS into REMOTE_AUTOMATION_REQUEST_EXTENDER', 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.21.0.xml', 'tm-1.21.0-TM-916-918-synchronizable-issue-status', '2.0.5', '3:27e3ce65341d7b7624b5d8b7db75c1d6', 491);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.21.0.xml::tm-1.21.0-TM-875-last-sync-date-squash::amk::(Checksum: 3:2d4677a94faf63e4cefd653582757c6e)
--  add new column LAST-SYNC-DATE into REMOTE_AUTOMATION_REQUEST_EXTENDER
ALTER TABLE `REMOTE_AUTOMATION_REQUEST_EXTENDER` ADD `LAST_SYNC_DATE_SQUASH` DATETIME DEFAULT null;

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('amk', 'add new column LAST-SYNC-DATE into REMOTE_AUTOMATION_REQUEST_EXTENDER', 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.21.0.xml', 'tm-1.21.0-TM-875-last-sync-date-squash', '2.0.5', '3:2d4677a94faf63e4cefd653582757c6e', 492);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.21.0.xml::tm-1.21.0-TM-846.1-mysql::jlor::(Checksum: 3:7df0ee73b1348229349c0fb058c00444)
--  MySQL - Add unique constraint on order column for iterations in campaign

create table CAMPAIGN_ITERATION_TMP
          (select
            CAMPAIGN_ID,
            ITERATION_ID,
            min(ITERATION_ORDER) ITERATION_ORDER
          from CAMPAIGN_ITERATION
          group by CAMPAIGN_ID, ITERATION_ID);

create index idx1 on CAMPAIGN_ITERATION_TMP(CAMPAIGN_ID, ITERATION_ID);

update CAMPAIGN_ITERATION_TMP t1,
          (select
            CAMPAIGN_ID,
            ITERATION_ID,
            (select count(*)
            from CAMPAIGN_ITERATION_TMP b
            where a.CAMPAIGN_ID = b.CAMPAIGN_ID
            and a.ITERATION_ORDER >= b.ITERATION_ORDER) line
          from CAMPAIGN_ITERATION_TMP a) t2
        set ITERATION_ORDER = t2.line - 1
        where t1.CAMPAIGN_ID = t2.CAMPAIGN_ID
        and t1.ITERATION_ID = t2.ITERATION_ID;

truncate table CAMPAIGN_ITERATION;

insert into CAMPAIGN_ITERATION
        select
          a.CAMPAIGN_ID,
          a.ITERATION_ID,
          a.ITERATION_ORDER + 1 - (select count(*)
                              from CAMPAIGN_ITERATION_TMP b
                              where a.CAMPAIGN_ID = b.CAMPAIGN_ID
                              and a.ITERATION_ORDER = b.ITERATION_ORDER
                              and a.ITERATION_ID <= b.ITERATION_ID
                              group by
                                b.CAMPAIGN_ID,
                                b.ITERATION_ORDER) ITERATION_ORDER
          from CAMPAIGN_ITERATION_TMP a;

drop table CAMPAIGN_ITERATION_TMP;

ALTER TABLE `CAMPAIGN_ITERATION` ADD UNIQUE (`CAMPAIGN_ID`, `ITERATION_ORDER`);

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('jlor', 'MySQL - Add unique constraint on order column for iterations in campaign', NOW(), 'Custom SQL, 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.21.0.xml', 'tm-1.21.0-TM-846.1-mysql', '2.0.5', '3:7df0ee73b1348229349c0fb058c00444', 493);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.21.0.xml::tm-1.21.0-TM-846.2-mysql::jlor::(Checksum: 3:71b5aded53d97e1e25094e8772ae6717)
--  MySQL - Add unique constraint on order column for executions in itpi

create table ITEM_TEST_PLAN_EXECUTION_TMP
          (select
            ITEM_TEST_PLAN_ID,
            EXECUTION_ID,
            min(EXECUTION_ORDER) EXECUTION_ORDER
          from ITEM_TEST_PLAN_EXECUTION
          group by ITEM_TEST_PLAN_ID, EXECUTION_ID);

create index idx1 on ITEM_TEST_PLAN_EXECUTION_TMP(ITEM_TEST_PLAN_ID, EXECUTION_ID);

update ITEM_TEST_PLAN_EXECUTION_TMP t1,
          (select
            ITEM_TEST_PLAN_ID,
            EXECUTION_ID,
            (select count(*)
            from ITEM_TEST_PLAN_EXECUTION_TMP b
            where a.ITEM_TEST_PLAN_ID = b.ITEM_TEST_PLAN_ID
            and a.EXECUTION_ORDER >= b.EXECUTION_ORDER) line
          from ITEM_TEST_PLAN_EXECUTION_TMP a) t2
        set EXECUTION_ORDER = t2.line - 1
        where t1.ITEM_TEST_PLAN_ID = t2.ITEM_TEST_PLAN_ID
        and t1.EXECUTION_ID = t2.EXECUTION_ID;

truncate table ITEM_TEST_PLAN_EXECUTION;

insert into ITEM_TEST_PLAN_EXECUTION
        select
          a.ITEM_TEST_PLAN_ID,
          a.EXECUTION_ID,
          a.EXECUTION_ORDER + 1 - (select count(*)
                              from ITEM_TEST_PLAN_EXECUTION_TMP b
                              where a.ITEM_TEST_PLAN_ID = b.ITEM_TEST_PLAN_ID
                              and a.EXECUTION_ORDER = b.EXECUTION_ORDER
                              and a.EXECUTION_ID <= b.EXECUTION_ID
                              group by
                                b.ITEM_TEST_PLAN_ID,
                                b.EXECUTION_ORDER) EXECUTION_ORDER
          from ITEM_TEST_PLAN_EXECUTION_TMP a;

drop table ITEM_TEST_PLAN_EXECUTION_TMP;

ALTER TABLE `ITEM_TEST_PLAN_EXECUTION` ADD UNIQUE (`ITEM_TEST_PLAN_ID`, `EXECUTION_ORDER`);

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('jlor', 'MySQL - Add unique constraint on order column for executions in itpi', NOW(), 'Custom SQL, 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.21.0.xml', 'tm-1.21.0-TM-846.2-mysql', '2.0.5', '3:71b5aded53d97e1e25094e8772ae6717', 494);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.21.0.xml::tm-1.21.0-TM-846.3-mysql::jlor::(Checksum: 3:0af358d7522b85c1c6c9e78afefcc11e)
--  MySQL - Add unique constraint on order column for steps in execution

create table EXECUTION_EXECUTION_STEPS_TMP
          (select
            EXECUTION_ID,
            EXECUTION_STEP_ID,
            min(EXECUTION_STEP_ORDER) STEP_ORDER
          from EXECUTION_EXECUTION_STEPS
          group by EXECUTION_ID, EXECUTION_STEP_ID);

create index idx1 on EXECUTION_EXECUTION_STEPS_TMP(EXECUTION_ID, EXECUTION_STEP_ID);

update EXECUTION_EXECUTION_STEPS_TMP t1,
          (select
            EXECUTION_ID,
            EXECUTION_STEP_ID,
            (select count(*)
            from EXECUTION_EXECUTION_STEPS_TMP b
            where a.EXECUTION_ID = b.EXECUTION_ID
            and a.STEP_ORDER >= b.STEP_ORDER) line
          from EXECUTION_EXECUTION_STEPS_TMP a) t2
        set STEP_ORDER = t2.line - 1
        where t1.EXECUTION_ID = t2.EXECUTION_ID
        and t1.EXECUTION_STEP_ID = t2.EXECUTION_STEP_ID;

truncate table EXECUTION_EXECUTION_STEPS;

insert into EXECUTION_EXECUTION_STEPS
        select
          a.EXECUTION_ID,
          a.EXECUTION_STEP_ID,
          a.STEP_ORDER + 1 - (select count(*)
                              from EXECUTION_EXECUTION_STEPS_TMP b
                              where a.EXECUTION_ID = b.EXECUTION_ID
                              and a.STEP_ORDER = b.STEP_ORDER
                              and a.EXECUTION_STEP_ID <= b.EXECUTION_STEP_ID
                              group by
                                b.EXECUTION_ID,
                                b.STEP_ORDER) STEP_ORDER
          from EXECUTION_EXECUTION_STEPS_TMP a;

drop table EXECUTION_EXECUTION_STEPS_TMP;

ALTER TABLE `EXECUTION_EXECUTION_STEPS` ADD UNIQUE (`EXECUTION_ID`, `EXECUTION_STEP_ORDER`);

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('jlor', 'MySQL - Add unique constraint on order column for steps in execution', NOW(), 'Custom SQL, 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.21.0.xml', 'tm-1.21.0-TM-846.3-mysql', '2.0.5', '3:0af358d7522b85c1c6c9e78afefcc11e', 495);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.21.0.xml::tm-1.21.0-TM-996-modify-entity-reference-id-type::jprioux::(Checksum: 3:491b75f0d8e7a01f40349242dc8d7a42)
--  Modify ENTITY_REFERENCE_ID data type in CHART_SCOPE
ALTER TABLE `CHART_SCOPE` MODIFY `ENTITY_REFERENCE_ID` BIGINT;

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('jprioux', 'Modify ENTITY_REFERENCE_ID data type in CHART_SCOPE', NOW(), 'Modify data type', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.21.0.xml', 'tm-1.21.0-TM-996-modify-entity-reference-id-type', '2.0.5', '3:491b75f0d8e7a01f40349242dc8d7a42', 496);

--  Release Database Lock
--  Release Database Lock
