-- *********************************************************************
-- 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.7.0.xml
-- Ran at: 03/04/20 12:06
-- Against: SA@jdbc:h2:/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/incremental-upgrade-database/squash-tm
-- 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.7.0.xml::tm-1.7.0::bsiri::(Checksum: 3:e487152700beeea52eeda3275fb09eeb)
-- Update TM database version number
UPDATE CORE_CONFIG SET VALUE = '1.7.0' 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.7.0.xml', 'tm-1.7.0', '2.0.5', '3:e487152700beeea52eeda3275fb09eeb', 224);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.7.0.xml::tm-1.7.0.feat-1107-01::bsiri::(Checksum: 3:bd6474134f670ab19e5440dc42195c1f)
-- Modifying RLN_RELATIONSHIP and RLN_RELATIONSHIP_CLOSURE : requirements can now accept more requirements 
-- 		(but no folders). Cautious : there is no way to express that a a requirement can only accept requirements as children.
ALTER TABLE RLN_RELATIONSHIP DROP CONSTRAINT fk_rln_relationship_ancestor;

ALTER TABLE RLN_RELATIONSHIP DROP CONSTRAINT fk_rln_relationship_descendant;

ALTER TABLE RLN_RELATIONSHIP ADD CONSTRAINT fk_rln_relationship_ancestor FOREIGN KEY (ANCESTOR_ID) REFERENCES REQUIREMENT_LIBRARY_NODE (RLN_ID);

ALTER TABLE RLN_RELATIONSHIP ADD CONSTRAINT fk_rln_relationship_descendant FOREIGN KEY (DESCENDANT_ID) REFERENCES REQUIREMENT_LIBRARY_NODE (RLN_ID);

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('bsiri', 'Modifying RLN_RELATIONSHIP and RLN_RELATIONSHIP_CLOSURE : requirements can now accept more requirements 
		(but no folders). Cautious : there is no way to express that a a requirement can only accept requirements as children.', NOW(), 'Drop Foreign Key Constraint (x2), Add Foreign Key Constraint (x2)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.7.0.xml', 'tm-1.7.0.feat-1107-01', '2.0.5', '3:bd6474134f670ab19e5440dc42195c1f', 225);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.7.0.xml::tm-1.7.0.feat-1090-01::flaurens::(Checksum: 3:6e48a7ae6a4c474adb23e075dcba2b7d)
-- adding entries for indexing dates in table core_config
INSERT INTO CORE_CONFIG (STR_KEY) VALUES ('lastindexing.requirement.date');

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('flaurens', 'adding entries for indexing dates in table core_config', NOW(), 'Insert Row', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.7.0.xml', 'tm-1.7.0.feat-1090-01', '2.0.5', '3:6e48a7ae6a4c474adb23e075dcba2b7d', 226);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.7.0.xml::tm-1.7.0.feat-1090-02::flaurens::(Checksum: 3:f3cb7f953543a17b4d1c154c7e442401)
-- adding entries for indexing dates in table core_config
INSERT INTO CORE_CONFIG (STR_KEY) VALUES ('lastindexing.testcase.date');

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('flaurens', 'adding entries for indexing dates in table core_config', NOW(), 'Insert Row', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.7.0.xml', 'tm-1.7.0.feat-1090-02', '2.0.5', '3:f3cb7f953543a17b4d1c154c7e442401', 227);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.7.0.xml::tm-1.7.0.feat-1090-03::flaurens::(Checksum: 3:45eb510ab2fbd7f639ecddbe84464f9f)
-- adding entries for indexing dates in table core_config
INSERT INTO CORE_CONFIG (STR_KEY) VALUES ('lastindexing.campaign.date');

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('flaurens', 'adding entries for indexing dates in table core_config', NOW(), 'Insert Row', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.7.0.xml', 'tm-1.7.0.feat-1090-03', '2.0.5', '3:45eb510ab2fbd7f639ecddbe84464f9f', 228);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.7.0.xml::tm-1.7.0.feat-1090-04::flaurens::(Checksum: 3:9fb520a42c5abd1166267f706b8b9b77)
-- adding entries for indexing versions in table core_config
INSERT INTO CORE_CONFIG (STR_KEY) VALUES ('lastindexing.requirement.version');

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('flaurens', 'adding entries for indexing versions in table core_config', NOW(), 'Insert Row', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.7.0.xml', 'tm-1.7.0.feat-1090-04', '2.0.5', '3:9fb520a42c5abd1166267f706b8b9b77', 229);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.7.0.xml::tm-1.7.0.feat-1090-05::flaurens::(Checksum: 3:238b90d96db5cf853377733356cadff2)
-- adding entries for indexing versions in table core_config
INSERT INTO CORE_CONFIG (STR_KEY) VALUES ('lastindexing.testcase.version');

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('flaurens', 'adding entries for indexing versions in table core_config', NOW(), 'Insert Row', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.7.0.xml', 'tm-1.7.0.feat-1090-05', '2.0.5', '3:238b90d96db5cf853377733356cadff2', 230);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.7.0.xml::tm-1.7.0.feat-1090-06::flaurens::(Checksum: 3:b9e59a82fea7f6e9f96a88b51283f2b4)
-- adding entries for indexing versions in table core_config
INSERT INTO CORE_CONFIG (STR_KEY) VALUES ('lastindexing.campaign.version');

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('flaurens', 'adding entries for indexing versions in table core_config', NOW(), 'Insert Row', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.7.0.xml', 'tm-1.7.0.feat-1090-06', '2.0.5', '3:b9e59a82fea7f6e9f96a88b51283f2b4', 231);

-- Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.7.0.xml::tm-1.7.0.feat-2648::mpagnon::(Checksum: 3:695514959b39640bf03440a5fbb091e1)
-- add permission ATTACH to Validator on Test-case library
INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.ProjectManager'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.tm.domain.campaign.CampaignLibrary'), '2048');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.TestRunner'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.tm.domain.requirement.RequirementLibrary'), '64');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.TestRunner'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.tm.domain.testcase.TestCaseLibrary'), '64');

DELETE FROM ACL_GROUP_PERMISSION  WHERE ACL_GROUP_ID = (select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.AdvanceTester')
				and PERMISSION_MASK = 2
				and CLASS_ID = (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.tm.domain.campaign.CampaignLibrary');

DELETE FROM ACL_GROUP_PERMISSION  WHERE ACL_GROUP_ID = (select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.AdvanceTester')
				and PERMISSION_MASK = 4
				and CLASS_ID = (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.tm.domain.campaign.CampaignLibrary');

DELETE FROM ACL_GROUP_PERMISSION  WHERE ACL_GROUP_ID = (select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.AdvanceTester')
				and PERMISSION_MASK = 8
				and CLASS_ID = (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.tm.domain.campaign.CampaignLibrary');

DELETE FROM ACL_GROUP_PERMISSION  WHERE ACL_GROUP_ID = (select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.AdvanceTester')
				and PERMISSION_MASK = 256
				and CLASS_ID = (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.tm.domain.campaign.CampaignLibrary');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.AdvanceTester'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.tm.domain.requirement.RequirementLibrary'), '64');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.AdvanceTester'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.tm.domain.requirement.RequirementLibrary'), '256');

DELETE FROM ACL_GROUP_PERMISSION  WHERE ACL_GROUP_ID = (select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.AdvanceTester')
				and PERMISSION_MASK = 1024
				and CLASS_ID = (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.tm.domain.testcase.TestCaseLibrary');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.AdvanceTester'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.tm.domain.testcase.TestCaseLibrary'), '256');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.Validator'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.tm.domain.requirement.RequirementLibrary'), '2');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.Validator'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.tm.domain.requirement.RequirementLibrary'), '1024');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.Validator'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.tm.domain.testcase.TestCaseLibrary'), '2');

INSERT INTO ACL_GROUP_PERMISSION (ACL_GROUP_ID, CLASS_ID, PERMISSION_MASK) VALUES ((select ID from ACL_GROUP where QUALIFIED_NAME = 'squashtest.acl.group.tm.Validator'), (select ID from ACL_CLASS where CLASSNAME = 'org.squashtest.tm.domain.testcase.TestCaseLibrary'), '1024');

INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mpagnon', 'add permission ATTACH to Validator on Test-case library', NOW(), 'Insert Row (x3), Delete Data (x4), Insert Row (x2), Delete Data, Insert Row (x5)', 'EXECUTED', '/var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.7.0.xml', 'tm-1.7.0.feat-2648', '2.0.5', '3:695514959b39640bf03440a5fbb091e1', 232);

-- Release Database Lock
-- Release Database Lock
