--  *********************************************************************
--  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.13.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.13.1.xml::tm-1.13.1::jthebault::(Checksum: 3:9acf48a1aeff01d0f45dabea50329634)
--  Update TM database version number
UPDATE `CORE_CONFIG` SET `VALUE` = '1.13.1' WHERE STR_KEY = 'squashtest.tm.database.version';

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('jthebault', '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.13.1.xml', 'tm-1.13.1', '2.0.5', '3:9acf48a1aeff01d0f45dabea50329634', 379);

--  Changeset /var/lib/jenkins/jobs/build-squash-tm-distros-1.21/workspace/standalone/target/maven-shared-archive-resources/tm/tm.changelog-1.13.1.xml::tm-1.13.1-ACL-Custom-Report-Library::jthebault::(Checksum: 3:9ddc45890a0ff8b5f42b3f63d61f07f6)

CREATE TEMPORARY TABLE ACL_RSE_TMP
	(
		PARTY_ID bigint,
		ACL_GROUP_ID bigint,
		OBJECT_IDENTITY_ID bigint,
		PROJECT_ID bigint,
		CRL_ID bigint,
		CRL_CLASS_ID bigint,
		CRL_OBJECT_IDENTITY_ID bigint
	);

insert into ACL_RSE_TMP (PARTY_ID,ACL_GROUP_ID,OBJECT_IDENTITY_ID,PROJECT_ID)
		select PARTY_ID,ACL_GROUP_ID,OBJECT_IDENTITY_ID,ACL_OBJECT_IDENTITY.IDENTITY
		from ACL_RESPONSIBILITY_SCOPE_ENTRY
		inner join ACL_OBJECT_IDENTITY on ACL_RESPONSIBILITY_SCOPE_ENTRY.OBJECT_IDENTITY_ID = ACL_OBJECT_IDENTITY.ID
		inner join ACL_CLASS on ACL_OBJECT_IDENTITY.CLASS_ID = ACL_CLASS.ID
		where ACL_CLASS.CLASSNAME like 'org.squashtest.tm.domain.project.Project%';

update ACL_RSE_TMP 
		set CRL_ID = (select CRL_ID from PROJECT where PROJECT.PROJECT_ID = ACL_RSE_TMP.PROJECT_ID);

update ACL_RSE_TMP 
		set CRL_CLASS_ID = (select ACL_CLASS.ID from ACL_CLASS where ACL_CLASS.CLASSNAME like 'org.squashtest.tm.domain.customreport.CustomReportLibrary');

update ACL_RSE_TMP 
		set CRL_OBJECT_IDENTITY_ID = 
			(select ACL_OBJECT_IDENTITY.ID from ACL_OBJECT_IDENTITY where ACL_OBJECT_IDENTITY.CLASS_ID = 
				(select ACL_CLASS.ID from ACL_CLASS where ACL_CLASS.CLASSNAME like 'org.squashtest.tm.domain.customreport.CustomReportLibrary')
				and ACL_OBJECT_IDENTITY.IDENTITY = ACL_RSE_TMP.CRL_ID
			);

delete from ACL_RESPONSIBILITY_SCOPE_ENTRY where ACL_RESPONSIBILITY_SCOPE_ENTRY.OBJECT_IDENTITY_ID in (select CRL_OBJECT_IDENTITY_ID from ACL_RSE_TMP);

insert into ACL_RESPONSIBILITY_SCOPE_ENTRY (PARTY_ID,ACL_GROUP_ID,OBJECT_IDENTITY_ID)
		select PARTY_ID,ACL_GROUP_ID,CRL_OBJECT_IDENTITY_ID
		from ACL_RSE_TMP;

INSERT INTO `DATABASECHANGELOG` (`AUTHOR`, `COMMENTS`, `DATEEXECUTED`, `DESCRIPTION`, `EXECTYPE`, `FILENAME`, `ID`, `LIQUIBASE`, `MD5SUM`, `ORDEREXECUTED`) VALUES ('jthebault', '', 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.13.1.xml', 'tm-1.13.1-ACL-Custom-Report-Library', '2.0.5', '3:9ddc45890a0ff8b5f42b3f63d61f07f6', 380);

--  Release Database Lock
--  Release Database Lock
