--  *********************************************************************
--  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.14.1.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.14.1.xml::tm-1.14.1::bsiri::(Checksum: 3:6e42a1e9b71a995a5210b003b44cbaf7)
--  Update TM database version number
UPDATE `CORE_CONFIG` SET `VALUE` = '1.14.1' WHERE STR_KEY = 'squashtest.tm.database.version';

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('bsiri', '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.14.1.xml', 'tm-1.14.1', '2.0.5', '3:6e42a1e9b71a995a5210b003b44cbaf7', 391);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.14.1.xml::tm-1.14.1-issue-6340-1::bsiri::(Checksum: 3:60da0cf14443db9fb651212663612c9d)
--  For the purpose of the cleanup to come, we must activate on delete cascade 
--              between DENORMALIZED_FIELD_OPTION and DENORMALIZED_FIELD_VALUE
ALTER TABLE `DENORMALIZED_FIELD_OPTION` DROP FOREIGN KEY `fk_df_option_dfv`;

ALTER TABLE `DENORMALIZED_FIELD_OPTION` ADD CONSTRAINT `fk_df_option_dfv` FOREIGN KEY (`DFV_ID`) REFERENCES `DENORMALIZED_FIELD_VALUE` (`DFV_ID`) ON DELETE CASCADE;

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('bsiri', 'For the purpose of the cleanup to come, we must activate on delete cascade 
            between DENORMALIZED_FIELD_OPTION and DENORMALIZED_FIELD_VALUE', NOW(), 'Drop Foreign Key Constraint, Add Foreign Key Constraint', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.14.1.xml', 'tm-1.14.1-issue-6340-1', '2.0.5', '3:60da0cf14443db9fb651212663612c9d', 392);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.14.1.xml::tm-1.14.1-issue-6340-2b::bsiri::(Checksum: 3:8672e08b0999da0c207d8b0724a96825)
--  Fixing the extraneous custom field values (for test cases) and denormalized field values (for executions)
--            for MySQL
create temporary table DFV_DELETE as 
        (
                SELECT MAX( DFV_ID ) as DFV_ID
                FROM DENORMALIZED_FIELD_VALUE
                WHERE DENORMALIZED_FIELD_HOLDER_TYPE = 'EXECUTION'
                GROUP BY DENORMALIZED_FIELD_HOLDER_ID, DENORMALIZED_FIELD_HOLDER_TYPE, CODE
        );

delete from DENORMALIZED_FIELD_VALUE
        where DENORMALIZED_FIELD_HOLDER_TYPE = 'EXECUTION' 
        and DFV_ID not in (
                select DFV_ID from DFV_DELETE
        );

create temporary table CFV_DELETE as 
        (
                SELECT MAX( CFV_ID ) as CFV_ID
                FROM CUSTOM_FIELD_VALUE
                WHERE BOUND_ENTITY_TYPE = 'TEST_CASE'
                GROUP BY BOUND_ENTITY_ID, BOUND_ENTITY_TYPE, CFB_ID
        );

delete from CUSTOM_FIELD_VALUE
        where BOUND_ENTITY_TYPE = 'TEST_CASE'
        and CFV_ID not in (
                select CFV_ID from CFV_DELETE
        );

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('bsiri', 'Fixing the extraneous custom field values (for test cases) and denormalized field values (for executions)
          for MySQL', 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.14.1.xml', 'tm-1.14.1-issue-6340-2b', '2.0.5', '3:8672e08b0999da0c207d8b0724a96825', 393);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.14.1.xml::tm-1.14.1-issue-6340-3::bsiri::(Checksum: 3:0f662c7dbe45ee5f2f8c55e544cdfc2a)
--  enforcing uniticy on the custom field values and denormalized field values - 
--            if later on there are more double insert let it fail fast
ALTER TABLE `DENORMALIZED_FIELD_VALUE` ADD CONSTRAINT `u_dfv_id_type_code` UNIQUE (`DENORMALIZED_FIELD_HOLDER_ID`, `DENORMALIZED_FIELD_HOLDER_TYPE`, `CODE`);

ALTER TABLE `CUSTOM_FIELD_VALUE` ADD CONSTRAINT `u_cfv_id_type_binding` UNIQUE (`BOUND_ENTITY_ID`, `BOUND_ENTITY_TYPE`, `CFB_ID`);

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('bsiri', 'enforcing uniticy on the custom field values and denormalized field values - 
          if later on there are more double insert let it fail fast', NOW(), 'Add Unique Constraint (x2)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.14.1.xml', 'tm-1.14.1-issue-6340-3', '2.0.5', '3:0f662c7dbe45ee5f2f8c55e544cdfc2a', 394);

--  Release Database Lock
--  Release Database Lock
