--  *********************************************************************
--  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: 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.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', 357);

--  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-mysql::bsiri::(Checksum: 3:b5e2b417ddc9f5ac3f18a1946f582128)
--  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
			having count(*) > 1;

delete clos
			from TCLN_RELATIONSHIP_CLOSURE clos
			inner join CLOSURECOPY cop on (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-mysql', '2.0.5', '3:b5e2b417ddc9f5ac3f18a1946f582128', 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-2-mysql::bsiri::(Checksum: 3:d6f949ec07648049e85127c14f635662)
--  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
			having count(*) > 1;

delete clos
			from RLN_RELATIONSHIP_CLOSURE clos
			inner join CLOSURECOPY cop on (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-mysql', '2.0.5', '3:d6f949ec07648049e85127c14f635662', 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-3-mysql::bsiri::(Checksum: 3:b7db9b7931aade4b8d07c8ee78b556cc)
--  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
			having count(*) > 1;

delete clos
			from CLN_RELATIONSHIP_CLOSURE clos
			inner join CLOSURECOPY cop on (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-mysql', '2.0.5', '3:b7db9b7931aade4b8d07c8ee78b556cc', 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-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', 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-3-1::bsiri::(Checksum: 3:e0c23a49e1d7ac3071312cc3538b3398)
--  redefining some triggers for table TCLN_RELATIONHIP and TEST_CASE_LIBRARY_NODE
drop trigger if exists ins_tcln_after;

create trigger ins_tcln_after after insert on
			TEST_CASE_LIBRARY_NODE
			for each row insert ignore into
			TCLN_RELATIONSHIP_CLOSURE values (new.tcln_id, new.tcln_id, 0);

drop trigger if exists attach_tcln_after;

create trigger attach_tcln_after after insert on
			TCLN_RELATIONSHIP
			for each row insert ignore into TCLN_RELATIONSHIP_CLOSURE
			select c1.ancestor_id, c2.descendant_id, c1.depth+c2.depth+1
			from
			TCLN_RELATIONSHIP_CLOSURE c1
			cross join TCLN_RELATIONSHIP_CLOSURE c2
			where c1.descendant_id = new.ancestor_id
			and c2.ancestor_id =
			new.descendant_id;

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('bsiri', 'redefining some triggers for table TCLN_RELATIONHIP and TEST_CASE_LIBRARY_NODE', NOW(), 'Custom SQL (x2)', '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-3-1', '2.0.5', '3:e0c23a49e1d7ac3071312cc3538b3398', 362);

--  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-3-2::bsiri::(Checksum: 3:33d882d20a61e3d727d8650ab9b3e4b4)
--  redefining some triggers for table RLN_RELATIONHIP and REQUIREMENT_LIBRARY_NODE
drop trigger if exists ins_rln_after;

create trigger ins_rln_after after insert on
			REQUIREMENT_LIBRARY_NODE
			for each row insert ignore into
			RLN_RELATIONSHIP_CLOSURE values (new.rln_id, new.rln_id, 0);

drop trigger if exists attach_rln_after;

create trigger attach_rln_after after insert on RLN_RELATIONSHIP
			for each row insert ignore into RLN_RELATIONSHIP_CLOSURE
			select
			c1.ancestor_id, c2.descendant_id, c1.depth+c2.depth+1
			from
			RLN_RELATIONSHIP_CLOSURE c1
			cross join RLN_RELATIONSHIP_CLOSURE c2
			where c1.descendant_id = new.ancestor_id
			and c2.ancestor_id =
			new.descendant_id;

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('bsiri', 'redefining some triggers for table RLN_RELATIONHIP and REQUIREMENT_LIBRARY_NODE', NOW(), 'Custom SQL (x2)', '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-3-2', '2.0.5', '3:33d882d20a61e3d727d8650ab9b3e4b4', 363);

--  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-3-3::bsiri::(Checksum: 3:e94b8838f84428a4fe71d0f50034fb89)
--  redefining some triggers for table CLN_RELATIONHIP and CAMPAIGN_LIBRARY_NODE
drop trigger if exists ins_cln_after;

create trigger ins_cln_after after insert on
			CAMPAIGN_LIBRARY_NODE
			for each row insert ignore into
			CLN_RELATIONSHIP_CLOSURE values (new.cln_id, new.cln_id, 0);

drop trigger if exists attach_cln_after;

create trigger attach_cln_after after insert on CLN_RELATIONSHIP
			for each row insert ignore into CLN_RELATIONSHIP_CLOSURE
			select
			c1.ancestor_id, c2.descendant_id, c1.depth+c2.depth+1
			from
			CLN_RELATIONSHIP_CLOSURE c1
			cross join CLN_RELATIONSHIP_CLOSURE c2
			where c1.descendant_id = new.ancestor_id
			and c2.ancestor_id = new.descendant_id;

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('bsiri', 'redefining some triggers for table CLN_RELATIONHIP and CAMPAIGN_LIBRARY_NODE', NOW(), 'Custom SQL (x2)', '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-3-3', '2.0.5', '3:e94b8838f84428a4fe71d0f50034fb89', 364);

--  Release Database Lock
--  Release Database Lock
