-- *********************************************************************
-- 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: liquibase@jdbc:postgresql://localhost:5432/squash_dist_cur
-- 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 CONSTRAINT 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-2a::bsiri::(Checksum: 3:2727be10754e0526889eb7bee67b6c9f)
-- Fixing the extraneous custom field values (for test cases) and denormalized field values (for executions)
--           for Postgresql.
--           Note about the ordering (in the partition instruction) : I've empirically found that it must be ascending 
--           for table DENORMALIZED_FIELD_VALUE and descending for CUSTOm_FIELD_VALUE because of
delete from DENORMALIZED_FIELD_VALUE
        where DFV_ID in (
            select DFV_ID from (
                select DFV_ID, ROW_NUMBER() over (partition by DENORMALIZED_FIELD_HOLDER_ID, DENORMALIZED_FIELD_HOLDER_TYPE, CODE order by DFV_ID) 
                as RNUM 
                from DENORMALIZED_FIELD_VALUE
            ) TODELETE
            where TODELETE.RNUM > 1
        );

delete from CUSTOM_FIELD_VALUE
        where CFV_ID in (
            select CFV_ID from (
                select CFV_ID, ROW_NUMBER() over (partition by BOUND_ENTITY_ID, BOUND_ENTITY_TYPE, CFB_ID order by CFV_ID desc) 
                as RNUM 
                from CUSTOM_FIELD_VALUE
            ) TODELETE
            where TODELETE.RNUM > 1	
        );

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 Postgresql.
          Note about the ordering (in the partition instruction) : I''ve empirically found that it must be ascending 
   ...', 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-2a', '2.0.5', '3:2727be10754e0526889eb7bee67b6c9f', 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
