-- *********************************************************************
-- 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.12.3.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.12.3.xml::tm-1.12.3::bsiri::(Checksum: 3:a478ad13ba18ae010a18113c11d60f0e)
-- Update TM database version number
UPDATE CORE_CONFIG SET VALUE = '1.12.3' 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.12.3.xml', 'tm-1.12.3', '2.0.5', '3:a478ad13ba18ae010a18113c11d60f0e', 358);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.12.3.xml::tm-1.12.3-issue-5965-1-1-postgresql::bsiri::(Checksum: 3:c4a6d8c8c80fb684550109b72144ac84)
-- Fixing data in TCLN_RELATIONSHIP_CLOSURE that need to be
create table CLOSURECOPY as 
			select distinct * from TCLN_RELATIONSHIP_CLOSURE
			group by ancestor_id, descendant_id, depth
			having count(*) > 1;

delete 
			from TCLN_RELATIONSHIP_CLOSURE clos
			using CLOSURECOPY cop
			where cop.ancestor_id = clos.ancestor_id 
			and cop.descendant_id = clos.descendant_id
			and cop.depth = clos.depth;

insert into TCLN_RELATIONSHIP_CLOSURE
			select * from CLOSURECOPY;

drop table CLOSURECOPY;

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'Fixing data in TCLN_RELATIONSHIP_CLOSURE that need to be', 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.12.3.xml', 'tm-1.12.3-issue-5965-1-1-postgresql', '2.0.5', '3:c4a6d8c8c80fb684550109b72144ac84', 359);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.12.3.xml::tm-1.12.3-issue-5965-1-2-postgresql::bsiri::(Checksum: 3:2c1331057f0eaeed64133776b0b09979)
-- Fixing data in RLN_RELATIONSHIP_CLOSURE that need to be
create table CLOSURECOPY as 
			select distinct * from RLN_RELATIONSHIP_CLOSURE
			group by ancestor_id, descendant_id, depth
			having count(*) > 1;

delete 
			from RLN_RELATIONSHIP_CLOSURE clos
			using CLOSURECOPY cop
			where cop.ancestor_id = clos.ancestor_id 
			and cop.descendant_id = clos.descendant_id
			and cop.depth = clos.depth;

insert into RLN_RELATIONSHIP_CLOSURE
			select * from CLOSURECOPY;

drop table CLOSURECOPY;

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'Fixing data in RLN_RELATIONSHIP_CLOSURE that need to be', 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.12.3.xml', 'tm-1.12.3-issue-5965-1-2-postgresql', '2.0.5', '3:2c1331057f0eaeed64133776b0b09979', 360);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.12.3.xml::tm-1.12.3-issue-5965-1-3-postgresql::bsiri::(Checksum: 3:4823ddde2a7cade4eba0cb55cda305ed)
-- Fixing data in CLN_RELATIONSHIP_CLOSURE that need to be
create table CLOSURECOPY as 
			select distinct * from CLN_RELATIONSHIP_CLOSURE
			group by ancestor_id, descendant_id, depth
			having count(*) > 1;

delete 
			from CLN_RELATIONSHIP_CLOSURE clos
			using CLOSURECOPY cop
			where cop.ancestor_id = clos.ancestor_id 
			and cop.descendant_id = clos.descendant_id
			and cop.depth = clos.depth;

insert into CLN_RELATIONSHIP_CLOSURE
			select * from CLOSURECOPY;

drop table CLOSURECOPY;

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'Fixing data in CLN_RELATIONSHIP_CLOSURE that need to be', 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.12.3.xml', 'tm-1.12.3-issue-5965-1-3-postgresql', '2.0.5', '3:4823ddde2a7cade4eba0cb55cda305ed', 361);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.12.3.xml::tm-1.12.3-issue-5965-2-1::bsiri::(Checksum: 3:1fa085655cb0d45f79180ec2f08f5d7a)
-- now we can add the unique constraints on the closure tables
ALTER TABLE TCLN_RELATIONSHIP_CLOSURE ADD CONSTRAINT uniq_tcln_clos UNIQUE (ancestor_id, descendant_id);

ALTER TABLE RLN_RELATIONSHIP_CLOSURE ADD CONSTRAINT uniq_rln_clos UNIQUE (ancestor_id, descendant_id);

ALTER TABLE CLN_RELATIONSHIP_CLOSURE ADD CONSTRAINT uniq_cln_clos UNIQUE (ancestor_id, descendant_id);

INSERT INTO databasechangelog (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'now we can add the unique constraints on the closure tables', NOW(), 'Add Unique Constraint (x3)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.12.3.xml', 'tm-1.12.3-issue-5965-2-1', '2.0.5', '3:1fa085655cb0d45f79180ec2f08f5d7a', 362);

-- Release Database Lock
-- Release Database Lock
