---------------------------------------------
--PL/SQL routing and destination package
--author L.Abadie
--v1.0
------------------------------------
create or replace package body UpdateCnsTab_pck
is
-----------------------------------------------------------
--Package which aims at updating the information of the cns tables
-----------------------------------------------------------
----------------------------------------------------------
-- repli_update_limit corresponds to the max. nb of rows which can be updated per sec due to replication. the default is 0 means there is no replication
----------------------------------------------------------
--CREATE or replace TYPE element_list IS TABLE OF NUMBER; 
 

function UpdateColCnsfileMetadata(root_name varchar2, new_groupname_gid varchar2, new_groupname_acl varchar2,repli_update_limit number default 0) return varchar2  is
	fileid_list element_list;
	i number:=1;
	new_gid number;
	new_gid_acl number;
	max_rows_update number:=10000;
	res_query varchar2(4000);
	new_acl_value varchar2(3900);
	fileid_notupdated varchar2(3900):='';
	root_fileid number:=0;
	current_fileid number:=0;
	resquery varchar2(4000);
	time_before number;
	begin
		new_gid:=UpdateCnsTab_pck.GetGID(new_groupname_gid);
		if new_gid> 0 then
			new_gid_acl:=UpdateCnsTab_pck.GetGID(new_groupname_acl);
		else
			res_query:='ERROR:new_groupname_gid does not exist';
			return res_query;
		end if;
		dbms_output.put_line('after getGID');

		if new_gid_acl> 0 then
			root_fileid:=UpdateCnsTab_pck.GetFileIDByTreeAddress(root_name);
			dbms_output.put_line('after GetFileIDByTreeAddress');

		else
			res_query:='ERROR : new_groupname_acl does not exist';
			return res_query;
		end if;
		if root_fileid>0 then
		dbms_output.put_line('after GetAllDirectoriesUnder');
		time_before := DBMS_UTILITY.GET_TIME;

		resquery:=UpdateCnsTab_pck.UpdateColCnsfileMetadataForDir(root_fileid,new_gid, new_gid_acl, repli_update_limit);
		DBMS_OUTPUT.PUT_LINE ('time execution to update the direc :'|| (DBMS_UTILITY.GET_TIME-time_before)/100 ||' and value of resuqye '||resquery);	
		
		if (instr(resquery,'Successful update')>0) then
			time_before := DBMS_UTILITY.GET_TIME;
			resquery:=UpdateCnsTab_pck.UpdateColCnsfileMetadataForFil(root_fileid,new_gid, new_gid_acl,repli_update_limit);
			DBMS_OUTPUT.PUT_LINE ('time execution to create createnodetab scd '|| (DBMS_UTILITY.GET_TIME-time_before)/100);	
		else
			res_query:=resquery;
			return res_query;

		end if;
		else
			resquery:='Error : Invalid root_name given';
		end if;
		return resquery;
	
	end UpdateColCnsfileMetadata;

function UpdateColCnsfileMetadataForDir(root_fileid number, new_gid number, new_gid_acl number, repli_update_limit number default 0) return varchar2  is
	fileid_list element_list;
	i number:=1;
	max_rows_update number:=10000;
	res_query varchar2(4000);
	new_acl_value varchar2(3900);
	fileid_notupdated varchar2(3900):='ERROR: could not update the rows';
	current_fileid number:=0;
	time_before number;
	time_after number;
	waiting_time number;
	update_rate number;
	begin
		fileid_list:=GetAllDirectoriesUnder(root_fileid);
		if repli_update_limit>0 then
			update_rate:=ceil(max_rows_update/repli_update_limit);	
		end if;
		dbms_output.put_line('after GetAllDirectoriesUnder ' ||fileid_list(1));

		if fileid_list(1)=-1 then
			if fileid_list(2)=-1 then
				res_query:='ERROR : root_name does not exist';
			else
				res_query:='ERROR : Ora'||fileid_list(2);
			end if;
			return res_query;
		else
			dbms_output.put_line('before the loop :'||fileid_list.count);
			time_before:=DBMS_UTILITY.GET_TIME;
			while (i <= fileid_list.count) loop
				current_fileid:=fileid_list(i);
				new_acl_value:=UpdateCnsTab_pck.EncodeACLForDirectory(new_gid,new_gid_acl,current_fileid);
				
				if instr(new_acl_value,'ERROR')=0 then
					execute immediate 'update cns_file_metadata t set t.gid=:1, t.acl=:2 where t.fileid=:3' using new_gid,new_acl_value, current_fileid;
				else
					fileid_notupdated:=fileid_notupdated||current_fileid||'|';
				end if;
			
				if mod(i,max_rows_update)=0 then
					commit;
					if repli_update_limit>0 then
						time_after:=DBMS_UTILITY.GET_TIME;
						waiting_time:=update_rate-(time_after-time_before)/100;
						if waiting_time>0 then
						dbms_lock.sleep(waiting_time); 
						end if;
						time_before:=DBMS_UTILITY.GET_TIME;
					end if;
				end if;
				i:=i+1;
			
			end loop;
			DBMS_OUTPUT.PUT_LINE ('time execution to update the direc :'|| (DBMS_UTILITY.GET_TIME-time_before)/100);	

			dbms_output.put_line('after the loop '||fileid_notupdated);

			commit;
			-- need to slow down the updates because of the replication 20 rows/sec at most, that is 500 sec to process the 10,000
			if repli_update_limit>0 then
				waiting_time:=ceil(mod(fileid_list.count,max_rows_update)/repli_update_limit); 
				if waiting_time>0 then
					dbms_lock.sleep(waiting_time); 
				end if;
			end if;
			
			if length(fileid_notupdated)<38 then
				res_query:='NO_ERROR: Successful update';
			else
				res_query:=fileid_notupdated;
			end if;
		
			return res_query;
		end if;
	
	end UpdateColCnsfileMetadataForDir;
 
function UpdateColCnsfileMetadataForFil(root_fileid number, new_gid number, new_gid_acl number, repli_update_limit number default 0) return varchar2  is
	fileid_list element_list;
	i number:=1;
	max_rows_update number:=10000;
	res_query varchar2(4000);
	new_acl_value varchar2(3900);
	fileid_notupdated varchar2(3900):='ERROR: could not update the rows';
	current_fileid number:=0;
	execution_time number;
	waiting_time number;
	time_before number;
	time_after number;
	update_rate number;
	begin

		fileid_list:=GetAllFilesUnder(root_fileid);
		if repli_update_limit>0 then
			update_rate:=ceil(max_rows_update/repli_update_limit);	
		end if;
		
		dbms_output.put_line('after GetAllfilesUnder :'||fileid_list.count);

		if fileid_list(1)=-1 then
			if fileid_list(2)=-1 then
				res_query:='ERROR : root_name does not exist';
			else
				res_query:='ERROR : Ora'||fileid_list(2);
			end if;
			return res_query;
		else
			--dbms_output.put_line('before the loop :'||fileid_list(5));
			execution_time:= DBMS_UTILITY.GET_TIME;
			time_before:= DBMS_UTILITY.GET_TIME;

			while (i <= fileid_list.count) loop
				current_fileid:=fileid_list(i);
				new_acl_value:=UpdateCnsTab_pck.EncodeACLForFile(new_gid,new_gid_acl,current_fileid);
				
				if instr(new_acl_value,'ERROR')=0 then
					execute immediate 'update cns_file_metadata t set t.gid=:1, t.acl=:2 where t.fileid=:3' using new_gid,new_acl_value, current_fileid;
				else
					fileid_notupdated:=fileid_notupdated||current_fileid||'|';
				end if;
			
				if mod(i,max_rows_update)=0 then
					commit;
					if repli_update_limit>0 then
						time_after:=DBMS_UTILITY.GET_TIME;
						waiting_time:=update_rate-(time_after-time_before)/100;
						if waiting_time>0 then
							dbms_lock.sleep(waiting_time); 
						end if;
						time_before:=DBMS_UTILITY.GET_TIME;
					end if;
				end if;
				i:=i+1;
				--if i>30000 then
				--	i:=i+3000000;
				--end if;
			end loop;
			DBMS_OUTPUT.PUT_LINE ('time execution to update the files :'|| (DBMS_UTILITY.GET_TIME-execution_time)/100);	

				commit;
				-- need to slow down the updates because of the replication 20 rows/sec at most, that is 500 sec to process the 10,000 : we set the speed at 16rows/sec by security
				if repli_update_limit>0 then
					waiting_time:=ceil(mod(fileid_list.count,max_rows_update)/repli_update_limit); 
					if waiting_time>0 then
					dbms_lock.sleep(waiting_time); 
					end if;
				end if;
			
			if length(fileid_notupdated)<38 then
				res_query:='NO_ERROR: Successful update';
			else
				res_query:=fileid_notupdated;
			end if;
		
			return res_query;
		end if;
	
	end UpdateColCnsfileMetadataForFil;

function GetGID(groupname varchar2) return number is
	gid_val number:=-1;
	begin
		execute immediate 'select gid from cns_groupinfo where groupname=:1' into gid_val using groupname;
		return gid_val;
	exception
		when NO_DATA_FOUND then
			gid_val:=-1;
			return gid_val;
		when OTHERS then
			gid_val:=sqlcode;
			return gid_val;
end GetGID;
 
function GetFileIDByTreeAddress(full_directory_path varchar2) return number is
path_directory varchar2(4000):=full_directory_path;
path_directory_temp varchar2(4000):=full_directory_path;
stop number:=0;
index_path number:=0;
index_path_old number:=0;
i number:=1;
index_begin number;
index_end number;
current_fileid number:=-1;
previous_fileid number;
current_node varchar2(231);
	begin
	index_path:=instr(path_directory,'/',1,i);
	if index_path=1 then
		path_directory_temp:=substr(path_directory,2,length(path_directory));
	end if;
		while (stop=0) loop
			index_path:=instr(path_directory_temp,'/',1,i);
			if index_path=0 or index_path=length(path_directory_temp) then
				stop:=1;
			else
				index_begin:=index_path_old+1;
				index_end:=index_path-index_path_old-1;
				current_node:=substr(path_directory_temp,index_begin,index_end);
				dbms_output.put_line('value of '||current_node);

				if i=1 then
					execute immediate 'select fileid from cns_file_metadata where name=:1' into current_fileid using current_node;
				else
					execute immediate 'select fileid from cns_file_metadata where name=:1 and parent_fileid=:2' into current_fileid using current_node, previous_fileid;
				end if;
				previous_fileid:=current_fileid;
				index_path_old:=index_path;
				
				i:=i+1;
			end if;
			
		end loop;
		--dbms_output.put_line('value of '||index_begin);
		if index_path=0 then
			current_node:=substr(path_directory_temp,index_path_old+1);
		else
			current_node:=substr(path_directory_temp,index_path_old+1,index_path-index_path_old-1);
		end if;
		execute immediate 'select fileid from cns_file_metadata where name=:1 and parent_fileid=:2' into current_fileid using current_node,  previous_fileid;
		return current_fileid;
		exception
		when NO_DATA_FOUND then
			current_fileid:=-1;
			return current_fileid;
		when OTHERS then
			current_fileid:=sqlcode;
			return current_fileid;

	
end GetFileIDByTreeAddress;

function GetAllDirectoriesUnder(root_fileid number) return element_list  is
	directory_upper number:=16895;
	directory_lower number:=16384;
	fileid_list element_list:=element_list(-1);
	begin
		execute immediate 'select fileid from cns_file_metadata where filemode between :2 and :3 start with fileid=:1 connect by PRIOR fileid=parent_fileid ' bulk collect into fileid_list using directory_lower, directory_upper, root_fileid;
		dbms_output.put_line('value of '||fileid_list(1)||' and '||fileid_list.last);
		return fileid_list;
	exception
		when NO_DATA_FOUND then
			fileid_list(2):=-1;
			return fileid_list;
		when OTHERS then
			fileid_list(2):=sqlcode;
			return fileid_list;
end GetAllDirectoriesUnder;

function GetAllFilesUnder(root_fileid number) return element_list  is

	directory_upper number:=33279;
	directory_lower number:=32768;
	fileid_list element_list:=element_list(-1);
	begin
		execute immediate 'select fileid from cns_file_metadata where filemode between :2 and :3 start with fileid=:1 connect by PRIOR fileid=parent_fileid ' bulk collect into fileid_list using directory_lower, directory_upper, root_fileid;
		return fileid_list;
	exception
		when NO_DATA_FOUND then
			fileid_list(2):=-1;
			return fileid_list;
		when OTHERS then
			fileid_list(2):=sqlcode;
			return fileid_list;
end GetAllFilesUnder;


function EncodeACLForDirectory( new_gid number, new_acl_gid number, fileid number) return varchar2 is
	
	current_owner number;
	new_acl varchar2(3900);
	
	begin

		execute immediate 'select owner_uid from cns_file_metadata where fileid=:1' into current_owner using fileid;
		new_acl:='A7'||current_owner||',C7'||new_gid||',D7'||new_acl_gid||',E70,F50,a7'||current_owner||',c7'||new_gid||',d7'||new_acl_gid||',e70,f50';

	return new_acl;
	exception
	when NO_DATA_FOUND then
		new_acl:='ERROR_NO_DATA_FOUND';
		return new_acl;
	when OTHERS then
		new_acl:='ERROR_'||sqlerrm;
		return new_acl;
	end EncodeACLForDirectory;

function EncodeACLForFile( new_gid number, new_acl_gid number, fileid number) return varchar2 is
	
	current_owner number;
	new_acl varchar2(3900);
	
	begin

		execute immediate 'select owner_uid from cns_file_metadata where fileid=:1' into current_owner using fileid;
		new_acl:='A6'||current_owner||',C6'||new_gid||',D6'||new_acl_gid||',E70,F40';

	return new_acl;
	exception
	when NO_DATA_FOUND then
		new_acl:='ERROR_NO_DATA_FOUND';
		return new_acl;
	when OTHERS then
		new_acl:='ERROR_'||sqlerrm;
		return new_acl;
	end EncodeACLForFile;

end UpdateCnsTab_pck;
/