security-admin/db/mysql/patches/008-removeTrailingSlash.sql

-- Licensed to the Apache Software Foundation (ASF) under one or more
-- contributor license agreements.  See the NOTICE file distributed with
-- this work for additional information regarding copyright ownership.
-- The ASF licenses this file to You under the Apache License, Version 2.0
-- (the "License"); you may not use this file except in compliance with
-- the License.  You may obtain a copy of the License at
--
--     http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

DROP PROCEDURE if exists getUpdatedResourceName $$
CREATE PROCEDURE `getUpdatedResourceName`(IN resName varchar(4000), IN resId bigint, IN assetId bigint, IN policyName varchar(1000), OUT updatedResName varchar(4000)) 
BEGIN

DECLARE noOfCommas int;

DECLARE resource varchar(1000);
DECLARE count int default 0;
DECLARE proceedLoop boolean default true;

-- TrxLog fields
DECLARE createTime datetime;
DECLARE addedById bigint;
DECLARE classType int;
DECLARE objId bigint;
DECLARE parentObjId bigint;
DECLARE parentObjClsType int;
DECLARE parentObjName varchar(1024);
DECLARE objName varchar(1024);
DECLARE attrName varchar(255);
DECLARE prevVal varchar(1024);
DECLARE newVal varchar(1024);
DECLARE trxId varchar(1024);
DECLARE act varchar(255);
DECLARE sessType varchar(30);
DECLARE transId varchar(50);
DECLARE assetTypeInt int;
DECLARE assetType varchar(30);
set updatedResName='';
set noOfCommas = (LENGTH(resName)-LENGTH(REPLACE (resName, ',', '')));
set assetTypeInt = (select asset_type from x_asset where id = assetId);

	if (assetTypeInt = 1) then
		set assetType = 'HDFS';
	elseif  (assetTypeInt = 2) then
		set assetType = 'HBase';
	elseif ((assetTypeInt = 3)) then
		set assetType = 'Hive';
	elseif (assetTypeInt = 4) then
		set assetType = 'XAAGENT';
	elseif (assetTypeInt = 5) then
		set assetType = 'Knox';
	elseif (assetTypeInt = 6) then
		set assetType = 'Storm';
	end if;

WHILE proceedLoop DO
	set count = count +1;
	
	if (count > noOfCommas) then
		set resource = SUBSTRING_INDEX(resName, ',', -1);
		set proceedLoop = false;
	else 
		set resource = REPLACE(SUBSTRING(SUBSTRING_INDEX(resName, ',', count), 
						LENGTH(SUBSTRING_INDEX(resName, ',', count -1)) + 1), ',', '');
	end if;

	if (LENGTH(resource) > 1 && SUBSTRING(resource, -(LENGTH(resource)-(LENGTH(resource)-1))) = '/') then 
		set resource = SUBSTRING(resource, 1, (LENGTH(resource)-1));
	else
		set resource = resource;
	end if;

	if(updatedResName != '') then
		set updatedResName = CONCAT(updatedResName, ',', resource);
	else
		set updatedResName = resource;
	end if;
	
END WHILE;

if (updatedResName != resName) then
-- Generating Trx Log if value has been updated.

set createTime = utc_timestamp();
set addedById = 1;
set classType = 1001;
set objId = resId;
set parentObjId = assetId;
set parentObjClsType = 1000;
set objName = updatedResName;
set attrName = 'Resource Path';
set prevVal = resName;
set newVal = updatedResName;
set act = 'update';
set sessType = 'DB Script';

set parentObjName = (select asset_name from x_asset where id = assetId);
set transId = concat(DATE_FORMAT(utc_timestamp(), '%Y%m%d%H%i%s'), '_', rand());

insert into x_trx_log (create_time, update_time, added_by_id, upd_by_id, 
	class_type, object_id, parent_object_id, parent_object_class_type, 
	parent_object_name, object_name, attr_name, prev_val, new_val, `action`, 
	trx_id, sess_type) 
	values(createTime, createTime, addedById, addedById, classType, objId, 
	parentObjId, parentObjClsType, parentObjName, objName, attrName, prevVal, 
	newVal, act, transId, sessType);

insert into x_trx_log (create_time, update_time, added_by_id, upd_by_id, 
	class_type, object_id, parent_object_id, parent_object_class_type, 
	parent_object_name, object_name, attr_name, prev_val, new_val, `action`, 
	trx_id, sess_type) 
	values(createTime, createTime, addedById, addedById, classType, objId, 
	parentObjId, parentObjClsType, parentObjName, objName, 'Policy Name', policyName, 
	policyName, act, transId, sessType);

insert into x_trx_log (create_time, update_time, added_by_id, upd_by_id, 
	class_type, object_id, parent_object_id, parent_object_class_type, 
	parent_object_name, object_name, attr_name, new_val, `action`, 
	trx_id, sess_type) 
	values(createTime, createTime, addedById, addedById, classType, objId, 
	parentObjId, parentObjClsType, parentObjName, objName, 'Repository Type', 
	assetType, act, transId, sessType);

end if;


END $$
DELIMITER $$

DROP PROCEDURE if exists removeTrailingSlash $$
CREATE PROCEDURE `removeTrailingSlash`()
BEGIN

DECLARE done INT;
DECLARE resId bigint;
DECLARE assetId bigint;
DECLARE policyName varchar(1000);
DECLARE resourceName varchar(4000);
DECLARE trimmedResourceName varchar(4000);

DECLARE resourceNames CURSOR FOR 
	select res.res_name, res.id, res.asset_id, res.policy_name from x_resource res, x_asset asset
		where asset.asset_type = 1 and asset.id = res.asset_id;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN resourceNames;

	SET done = 0;
	iLoop : LOOP
	FETCH resourceNames into resourceName, resId, assetId, policyName;

	if(done = 1) then
		LEAVE iLoop;
	end if;

	call getUpdatedResourceName(resourceName, resId, assetId, policyName, trimmedResourceName);
	update x_resource res set res_name = trimmedResourceName where id=resId and res_name=resourceName;

	END LOOP;

CLOSE resourceNames;

END $$
DELIMITER ;
call removeTrailingSlash();

DROP PROCEDURE if exists getUpdatedResourceName;
DROP PROCEDURE if exists removeTrailingSlash;