create table LDAP_BICATALOG_ID_STORE
(
CATALOG_USER_ID  raw(16)
                     references BISM_SUBJECTS(SUBJECT_ID)
                     on delete cascade,
LDAP_USER_ID     VARCHAR2(2000),
constraint LDAP_BICATALOG_ID_STORE primary key (LDAP_USER_ID,CATALOG_USER_ID)
);

drop type List;
/

create or replace type ListObject as object
(
	name varchar2(255),
	guid varchar2(2000),
	isvalid	char(1),
	alreadyexists char(1),
	sourceisoid char(1)
);
/

create or replace type List as TABLE OF ListObject;
/


create or replace package LDAP_BICATALOG_SYNC_UTILS as
function addUsersToCatalog(a_userlist in out List,a_grplist in out List,a_permission in number,a_trace in boolean) return varchar2;
procedure addList(a_list in out List,a_type in varchar2,a_addpermission in boolean, a_permission in number,a_trace in boolean);
function getUserObject (a_session in DBMS_LDAP.session, a_entry in DBMS_LDAP.message,a_nicknameattr in varchar2) return ListObject ;
function canAddThisGroup(a_guid varchar2,a_name varchar2) return boolean;
function getNickNameAttribute(my_session in DBMS_LDAP.session,a_common_container_dn in varchar2,a_trace in boolean) return varchar2;
end;
/

create or replace package body LDAP_BICATALOG_SYNC_UTILS as

function addUsersToCatalog(a_userlist in out List, a_grplist in out List,a_permission in number,a_trace in boolean)
return varchar2 
is
subname BISM_SUBJECTS.SUBJECT_NAME%TYPE;
subjecttype BISM_SUBJECTS.SUBJECT_TYPE%TYPE;
grpsubid BISM_SUBJECTS.SUBJECT_ID%TYPE;
subjectid BISM_SUBJECTS.SUBJECT_ID%TYPE;
temp LDAP_BICATALOG_ID_STORE.LDAP_USER_ID%TYPE;
begin

 -- add the specified groups and grant privileges requested
	if a_grplist is not null and a_grplist.COUNT > 0 then 
		if a_trace = true then
			DBMS_OUTPUT.PUT_LINE('===================================================');
			dbms_output.put_line ('TRACE: PERFORMING TASK: Adding groups');
		end if;
		if a_permission > 0 then
			addList(a_grplist,'g',true,a_permission,a_trace);
		else
			addList(a_grplist,'g',false,a_permission,a_trace);
		end if;
	end if;

 -- add the specified users and grant privileges requested
	if a_userlist is not null and a_userlist.count > 0 then
		if a_trace = true then
			DBMS_OUTPUT.PUT_LINE('===================================================');
			dbms_output.put_line ('TRACE: PERFORMING TASK: Adding users');
		end if;
		-- whether or not group is specified, add the users,
		-- make him part of default group and assign
		-- priviliges to default group
		addList(a_userlist,'u',true,a_permission,a_trace);
	end if;

	-- if group is not specified, then we are done
	-- user has been made part of default group (itself) already, 
	-- commit and return

	if a_grplist is null or a_grplist.COUNT = 0 then
		commit;
		-- what about assigning priviliges to default grp when no groups were specified ?
		return 'success';
	end if;

	-- if group is(are) specified, add users to specified group(s)
	if a_trace = true then
		DBMS_OUTPUT.PUT_LINE('===================================================');
		dbms_output.put_line ('TRACE: PERFORMING TASK: Adding users to groups');
	end if;

	if a_userlist is null or a_userlist.count = 0 then
		dbms_output.put_line('TRACE: Users not specified');
		return 'success';
	end if;

	FOR j IN a_userlist.FIRST..a_userlist.LAST loop

    -- if a username is null or invalid, skip over
		if a_userlist(j).name is null or a_userlist(j).isvalid = 'f' then
			goto continue_outer_loop;
		end if;
  
    -- if user already exists and was manually created, skip over
		if a_userlist(j).alreadyexists = 't' and a_userlist(j).sourceisoid = 'f' then
			dbms_output.put_line('WARNING: User '|| a_userlist(j).name ||' externally created, not adding to any groups');
			goto continue_outer_loop;
		end if;

		subjectid := null;

		begin
    -- get the subject id from the BICatalog
		select subject_id into subjectid from bism_subjects where subject_name =  a_userlist(j).name and subject_type = 'u';
		-- select LDAP_USER_ID into temp from LDAP_BICATALOG_ID_STORE where CATALOG_USER_ID = subjectid;
		-- caller must delete all relationships between this group and its users
		-- before calling here
		exception
		when others then
			goto continue_outer_loop;
		end;

		for k in a_grplist.FIRST..a_grplist.LAST loop

    -- if the group name is null or invalid, skip over
		if a_grplist(k).name is null or a_grplist(k).isvalid = 'f' then
			goto continue_inner_loop;
		end if;

    -- if the group already exists and was manually created, skip over
		if a_grplist(k).alreadyexists = 't' and a_grplist(k).sourceisoid = 'f' then
			dbms_output.put_line('WARNING: Group ' || a_grplist(k).name ||' externally created, not adding any users' );
			goto continue_inner_loop;
		end if;

		begin
    -- add the user to the group
		insert into bism_groups values (subjectid,(select subject_id from bism_subjects where subject_name =  a_grplist(k).name and subject_type = 'g'));
		-- no need to add permissions here, because its was done while adding groups

		if a_trace = true then
		DBMS_OUTPUT.PUT_LINE('TRACE: Sucessfully added user '|| a_userlist(j).name || ' to group '|| a_grplist(k).name);
		end if;
		exception
		when others then 
		if substr(SQLERRM,1,9) = 'ORA-00001' then 
		if a_trace = true then
		DBMS_OUTPUT.PUT_LINE('TRACE: User '|| a_userlist(j).name || ' is already part of group '|| a_grplist(k).name);
		end if;
		else
		DBMS_OUTPUT.PUT_LINE('WARNING: Could not add user '|| a_userlist(j).name ||' to group '|| a_grplist(k).name||' , Error = '|| SQLERRM);
		end if;
		end;
		<<continue_inner_loop>>
		null;
		end loop;
		<<continue_outer_loop>>
		null;
	end loop;	

	commit;
	if a_trace = true then
	DBMS_OUTPUT.PUT_LINE('===================================================');
	end if;
	
	return 'success';

exception
 when others then 
   DBMS_OUTPUT.PUT_LINE('ERROR: Errors occured in synchronizing');
   DBMS_OUTPUT.PUT_LINE('ERROR Message : ' || SQLERRM);
   return SQLERRM;
end;	

procedure addList(a_list in out List,a_type in varchar2,a_addpermission in boolean, a_permission in number,a_trace in boolean)
is
subname BISM_SUBJECTS.SUBJECT_NAME%TYPE;
subjecttype BISM_SUBJECTS.SUBJECT_TYPE%TYPE;
gensubid BISM_SUBJECTS.SUBJECT_ID%TYPE;
subjectid BISM_SUBJECTS.SUBJECT_ID%TYPE;
subid LDAP_BICATALOG_ID_STORE.CATALOG_USER_ID%TYPE;
itexists boolean := false;
subexists boolean := false;
begin

if a_list is null or a_list.count = 0 then
return ;
end if;


-- add the list of entries to the database
for i in a_list.FIRST..a_list.LAST loop

  -- make sure that the subject name does not exceed 64 bytes, 
  -- BICatalog schema has this restriction
	if lengthb(a_list(i).name) > 64 then
		-- BISM_SUBJECTS table can only accept subject_names upto 64 bytes
		-- skip over this entry and log a warning
		dbms_output.put_line('WARNING : '|| a_list(i).name ||' is greater than 64, ignoring it');
		a_list(i).isvalid := 'f';
		goto continue_loop;
	else
		a_list(i).isvalid := 't';
	end if;

  -- GUID is not null when the entry comes from LDAP (OID)
	if a_list(i).guid is not null and a_list(i).name is not null then

	begin
	-- has this subject (grp or user) been imported from OID previously ?
	select catalog_user_id into subid from LDAP_BICATALOG_ID_STORE where ldap_user_id = a_list(i).guid;
	a_list(i).alreadyexists := 't';
	a_list(i).sourceisoid := 't';
	-- has name changed ?
	select subject_name into subname from bism_subjects where subject_id = subid;
	if subname <> a_list(i).name then
  -- if name has changed, update the name
	update bism_subjects set subject_name = a_list(i).name where subject_id = subid;
	if a_trace = true then
	dbms_output.put_line('TRACE: Subject name changed old name  ='|| subname ||' new name = '|| a_list(i).name ||' updated');
	end if;
	else
	if a_trace = true then
	dbms_output.put_line('TRACE: Entry '||a_list(i).name ||' already exists in BICatalog');
	end if;
	end if;
	exception
	when no_data_found then
		-- this user has not been imported from OID previously

		itexists := false;
		-- see if this user has been added manually to the catalog
		-- users added manually will not have entries in LDAP_BICATALOG_ID_STORE
		-- if subject (user or grp) with this name already exists, skip over !!
		begin
		select subject_name into subname from bism_subjects where subject_name=a_list(i).name;
		a_list(i).alreadyexists := 't';
		a_list(i).sourceisoid := 'f';
		itexists := true;
		exception
		when others then
		a_list(i).alreadyexists := 'f';
		a_list(i).sourceisoid := 'f';
		end;
		begin

		if itexists = false then
		gensubid := bism_utils.get_guid;
		insert into bism_subjects values (gensubid,a_list(i).name,a_type);
		-- if the subject is a user, then make this user part of itself
		-- to support groups in catalog
		if a_type = 'u' then
		insert into bism_groups values (gensubid,gensubid);
		end if;
    -- if privileges have to be granted, do so on the root folder
		if a_addpermission = true then
		insert into bism_permissions values(gensubid,'31',a_permission);
		end if;
		insert into LDAP_BICATALOG_ID_STORE values (gensubid,a_list(i).guid);
		if a_trace = true then
		dbms_output.put_line('TRACE: Successfully created entry '|| a_list(i).name ||' in BICatalog');
		end if;
		else
		dbms_output.put_line('TRACE: Subject name ' || a_list(i).name ||' already exists in BICatalog');
		end if;

		exception
		when others then
		DBMS_OUTPUT.PUT_LINE('WARNING: Could not add entry '|| a_list(i).name ||' length = '|| length(a_list(i).guid) ||', Error = '|| SQLERRM);
		end;
	end;
	elsif a_list(i).guid is null and a_list(i).name is not null then
	-- when there is no guid for this entry, just look up bism_subjects
	-- if group names are hardcoded in scripts, then they wont have guid's

	begin
	subexists := false;
	select SUBJECT_NAME,SUBJECT_ID  into subname,subid from bism_subjects where subject_name = a_list(i).name and subject_type = a_type;
	subexists := true;
	a_list(i).alreadyexists := 't';
	select catalog_user_id into subid from LDAP_BICATALOG_ID_STORE where ldap_user_id = 'NULL' and catalog_user_id=subid;
	a_list(i).sourceisoid := 't';
	dbms_output.put_line('WARNING: Entry '||a_list(i).name ||' already exists in BICatalog');
	exception
	when no_data_found then
		begin
		a_list(i).sourceisoid := 'f';
		if 	subexists = false then
		a_list(i).alreadyexists := 'f';
		else
		a_list(i).alreadyexists := 't';
		end if;

    if subexists = false then 
		gensubid := bism_utils.get_guid;
    -- insert the subject
		insert into bism_subjects values (gensubid,a_list(i).name,a_type);
    -- insert the entry in the tracking table so that we know
    -- that the subject originally came from OID
		insert into LDAP_BICATALOG_ID_STORE values (gensubid,'NULL');
		a_list(i).sourceisoid := 't';
		-- if the subject is a user, then make this user part of itself
		-- to support groups in catalog
		if a_type = 'u' then
		insert into bism_groups values (gensubid,gensubid);
		end if;

    -- grant any privileges on root folder
		if a_addpermission = true then 
		insert into bism_permissions values(gensubid,'31',a_permission);	
		end if;
		if a_trace = true then
		dbms_output.put_line('TRACE: Successfully created entry '|| a_list(i).name ||' in BICatalog');		
		end if;
    end if;
		exception 
		when others then 
		DBMS_OUTPUT.PUT_LINE('WARNING: Could not add entry '|| a_list(i).name ||', Error = '|| SQLERRM);
		end;
	end;
	
	end if;
	<<continue_loop>>
	null; 

end loop;
commit;
exception
when others then
DBMS_OUTPUT.PUT_LINE('ERROR: error occured in addList '|| SQLERRM);
end;

function getUserObject (a_session in DBMS_LDAP.session, a_entry in DBMS_LDAP.message,a_nicknameattr in varchar2) 
return ListObject 
is
 my_attr_name 	VARCHAR2(256);
 my_ber_elmt  	DBMS_LDAP.ber_element;
 userObject 	ListObject := ListObject(null,null,'f','f','f');
 my_vals 		DBMS_LDAP.STRING_COLLECTION ;
 attr_index   	PLS_INTEGER;
begin

-- given an entry of type inetorgperson, this routine retrives the user attributes 
-- a_nicknameattr and orclguid and returns to caller
-- if a_nicknameattr attribute (typically mapped to uid) has multiple values
-- it uses the first one and ignores the rest

if a_entry is null or a_session is null then
return null;
end if;

begin	
 my_attr_name := DBMS_LDAP.first_attribute(a_session,a_entry, my_ber_elmt);
   attr_index := 1;
   while my_attr_name IS NOT NULL loop 
    my_vals := DBMS_LDAP.get_values (a_session, a_entry,my_attr_name);
      if my_vals.COUNT > 0 then
       FOR i in my_vals.FIRST..my_vals.LAST loop
        if i = 0 then 
          -- nickname attribute will be defined in OID (see common conmtainer object)
          if my_attr_name = a_nicknameattr then  
            -- use nickname attribute for username
            userObject.name := SUBSTR(my_vals(i),1,length(my_vals(i))); 
          elsif my_attr_name = 'orclguid' then 
            -- ORCLGUID is needed to keep track of entries that originate in OID
            userObject.guid := SUBSTR(my_vals(i),1,length(my_vals(i)));   		
          end if;		
        else	
          DBMS_OUTPUT.PUT_LINE('WARNING : ignoring '||my_attr_name || ' : ' ||SUBSTR(my_vals(i),1,200));	  
        end if;
       end loop;
      end if;
     my_attr_name := DBMS_LDAP.next_attribute(a_session,a_entry,my_ber_elmt);
     attr_index := attr_index+1;
   end loop;  -- end attribute loop
   return userObject;
exception
when others then
return null;
end;

end;

function canAddThisGroup(a_guid varchar2,a_name varchar2)
return boolean
is
v_subid LDAP_BICATALOG_ID_STORE.CATALOG_USER_ID%TYPE;
begin

if a_name is null then
return false;
end if;

-- this routine cheks for the following
-- 1. has this group been originally imported from OID, if so go ahead and update
-- 2. if it has been manually created, skip over, log a message
	begin
		-- has it come from OID
		select CATALOG_USER_ID into v_subid from LDAP_BICATALOG_ID_STORE where LDAP_USER_ID = a_guid;
		-- since this group already exists in the catalog and something about this
		-- group has changed, lets remove users belonging to this group in catalog
		-- so that caller will add new users to it
		delete from bism_groups where group_id = v_subid;
		return true;
	exception
	when no_data_found then
		-- this group does not exist LDAP_BICATALOG_ID_STORE, but it may exist in BISM_SUBJECTS
		-- if it has been manually created
			begin
				select subject_id into v_subid from bism_subjects where subject_name = a_name;
				dbms_output.put_line('WARNING: Group '|| a_name || ' already exists in the BI Catalog and has been manually created, skipping over..');
				return false;
			exception
			when no_data_found then
				return true;
			when others then
				dbms_output.put_line('WARNING: Skipping over group '|| a_name||' error is '|| SQLERRM);
				return false;
			end;
	when others then
		dbms_output.put_line('WARNING: Skipping over group '|| a_name||' error is '|| SQLERRM);
  		return false;
	end;
end;

function getNickNameAttribute(my_session in DBMS_LDAP.session,a_common_container_dn in varchar2, a_trace in boolean)
return varchar2
is
 retval       PLS_INTEGER;
 my_attrs     DBMS_LDAP.string_collection;
 ret_values   DBMS_LDAP.string_collection;
 my_message   DBMS_LDAP.message;
 my_entry     DBMS_LDAP.message;
 entry_index  PLS_INTEGER;
 my_attr_name VARCHAR2(256);
 my_ber_elmt  DBMS_LDAP.ber_element;
 attr_index   PLS_INTEGER;
 i            PLS_INTEGER;
 my_vals      DBMS_LDAP.STRING_COLLECTION ;
 filter       VARCHAR2(128);
 nicknameattribute varchar2(255) := null;
 v_retval PLS_INTEGER;
begin

-- a valid session handle is needed
if my_session is null then
return null;
end if;

-- a dn for common container is needed, DIT varies wtih organization and 
-- common container distinguished name varies with it
if a_common_container_dn is null then
return null;
end if;

-- no need to specify the filter, when using DN of the object
filter :='(objectclass=*)';
-- the attribute we are looking for is called orclcommonnicknameattribute
-- and it is an attribute of Commoin Container object
my_attrs(1) := 'orclcommonnicknameattribute';

-- perform the search on LDAP with specified criteria
-- common container dn must be defined for this to work
 retval := DBMS_LDAP.search_s(my_session,a_common_container_dn, 
                              DBMS_LDAP.SCOPE_SUBTREE,
                              filter,
                              my_attrs,
                              0,
                              my_message);

 my_entry := DBMS_LDAP.first_entry(my_session, my_message);
 entry_index := 1;

	v_retval := DBMS_LDAP.count_entries(my_session, my_message);
  if a_trace = true then 
    dbms_output.put_line('TRACE: Looking for value of orclcommonnicknameattribute');
    dbms_output.put_line(RPAD('TRACE: Number of entries for nicknameattribute ',25,' ') || ': ' || TO_CHAR(v_retval));
  end if;

 while my_entry IS NOT NULL loop 
  
 my_attr_name := DBMS_LDAP.first_attribute(my_session,my_entry, my_ber_elmt);
   attr_index := 1;
   while my_attr_name IS NOT NULL loop

     my_vals := DBMS_LDAP.get_values (my_session, my_entry,my_attr_name);
     if my_vals.COUNT > 0 then
       FOR i in my_vals.FIRST..my_vals.LAST loop
        nicknameattribute := SUBSTR(my_vals(i),1,100);         
        if a_trace = true then
          DBMS_OUTPUT.PUT_LINE('orclcommonnicknameattribute is mapped to '|| nicknameattribute );
        end if;
       end loop;
     end if;
     my_attr_name := DBMS_LDAP.next_attribute(my_session,my_entry,my_ber_elmt);
     attr_index := attr_index+1;
   end loop;  -- end attribute loop   
   my_entry := DBMS_LDAP.next_entry(my_session, my_entry);
   entry_index := entry_index+1;
 end loop;

 retval := DBMS_LDAP.msgfree(my_message);
 return nicknameattribute;

exception
when others then
DBMS_OUTPUT.PUT_LINE('Error occurred in getNickNameAttribute ' || SQLERRM);
DBMS_OUTPUT.PUT_LINE('Make sure that orclnicknameattribute of entry dn='|| a_common_container_dn ||' is mapped to an unique attribute');
return null;
end;

end;
/ 

