------------------------------------------------------------------------
-- $Header: $
--
-- Copyright (c) Oracle Corporation 2000. All Rights Reserved.
--  
-- FILE         
--      trigger.sql: A sample trigger using DBMS_LDAP
--
-- DESCRIPTION 
--
--      This SQL file creates a database table called 'EMP'
--      and creates a trigger on it called LDAP_EMP which will
--      synchronize all changes happening to the table with an
--      LDAP server.
--      The changes to the database table are reflected/replicated
--      to the LDAP directory using the DBMS_LDAP package.
--
--      This script assumes the following:
--       LDAP server hostname: NULL (local host)
--       LDAP server portnumber: 389
--       Directory container for employee records: o=acme, dc=com
--       Username/Password for Directory Updates: cn=orcladmin/welcome
-- 
--      The aforementioned variables could be customized for different 
--      environments by changing the appropriate variables in the code below.
--         
--      Table Definition:
--      Employee Details(Columns) in Database Table(EMP):
--      ------------------------------------------------------
--      EMP_ID       - Number     
--      FIRST_NAME   - Varchar2
--      LAST_NAME    - Varchar2
--      MANAGER_ID   - Number
--      PHONE_NUMBER - Varchar2
--      MOBILE       - Varchar2
--      ROOM_NUMBER  - Varchar2
--      TITLE        - Varchar2
--    
--      LDAP Schema Definition & mapping to relational schema EMP:
--      Corresponding Data representation in LDAP directory:
--      ----------------------------------------------------
--        DN - [cn=<FIRST_NAME LAST_NAME>, o=acme, dc=com]
--        cn              - <FIRST_NAME LAST_NAME>
--        sn              - <LAST_NAME>
--        givenname       - <FIRST_NAME>
--        manager         - <DN>
--        telephonenumber - <PHONE_NUMBER>
--        mobile          - <MOBILE>
--        employeeNumber  - <EMP_ID>
--        userpassword    - <FIRST_NAME>
--        objectclass     - person
--                          organizationalperson
--                          inetOrgPerson
--                          top
--
--
--
--
--  MODIFIED   (MM/DD/YY)
--  rbollu	07/21/00 - created
---------------------------------------------------------

-- Creating EMP table

PROMPT Dropping Table EMP ..

drop table EMP;


PROMPT Creating Table EMP ..

CREATE TABLE EMP (
   EMP_ID    NUMBER,     -- Employee Number
   FIRST_NAME  VARCHAR2(256), -- First Name
   LAST_NAME  VARCHAR2(256), -- Last Name
   MANAGER_ID  NUMBER,     -- Manager Number
   PHONE_NUMBER VARCHAR2(256), -- Telephone Number
   MOBILE   VARCHAR2(256), -- Mobile Number
   ROOM_NUMBER  VARCHAR2(256), -- Room Number
   TITLE   VARCHAR2(256)  -- Title in the company
 );


-- Creating Trigger LDAP_EMP

PROMPT Creating Trigger LDAP_EMP ..

CREATE OR REPLACE TRIGGER LDAP_EMP
AFTER INSERT OR DELETE OR UPDATE ON EMP
FOR EACH ROW

DECLARE 
 retval   PLS_INTEGER; 
 emp_session  DBMS_LDAP.session;
 emp_dn   VARCHAR2(256);
 emp_rdn   VARCHAR2(256);
 emp_array  DBMS_LDAP.MOD_ARRAY;
 emp_vals  DBMS_LDAP.STRING_COLLECTION ;
 ldap_host  VARCHAR2(256);
 ldap_port  VARCHAR2(256);
 ldap_user  VARCHAR2(256);
 ldap_passwd  VARCHAR2(256);
 ldap_base  VARCHAR2(256);
BEGIN

 retval       := -1;
 -- Please customize the following variables as needed
 ldap_host  := NULL;
 ldap_port  := '389';
 ldap_user  := 'cn=orcladmin';
 ldap_passwd:= 'welcome';
 ldap_base  := 'o=acme,dc=com';
 -- end of customizable settings 

 DBMS_OUTPUT.PUT('Trigger [LDAP_EMP]: Replicating changes ');
 DBMS_OUTPUT.PUT_LINE('to directory .. ');
 DBMS_OUTPUT.PUT_LINE(RPAD('LDAP Host ',25,' ') || ': ' || ldap_host);
 DBMS_OUTPUT.PUT_LINE(RPAD('LDAP Port ',25,' ') || ': ' || ldap_port);

 -- Choosing exceptions to be raised by DBMS_LDAP library.
 DBMS_LDAP.USE_EXCEPTION := TRUE;

 -- Initialize ldap library and get session handle.
 emp_session := DBMS_LDAP.init(ldap_host,ldap_port);

 DBMS_OUTPUT.PUT_LINE (RPAD('Ldap session ',25,' ')  || ': ' ||
     RAWTOHEX(SUBSTR(emp_session,1,8)) ||
     '(returned from init)');
        
 -- Bind to the directory
 retval := DBMS_LDAP.simple_bind_s(emp_session,
       ldap_user,ldap_passwd);

    DBMS_OUTPUT.PUT_LINE(RPAD('simple_bind_s Returns ',25,' ') || ': ' 
          || TO_CHAR(retval));
 
 -- Process New Entry in the database

 IF INSERTING THEN

  -- Create and setup attribute array for the New entry
  emp_array := DBMS_LDAP.create_mod_array(14);

  -- RDN to be - cn="FIRST_NAME LAST_NAME"

  emp_vals(1) := :new.FIRST_NAME || ' ' || :new.LAST_NAME;

  DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD,
            'cn',emp_vals);

  emp_vals(1) := :new.LAST_NAME;

  DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD,
            'sn',emp_vals);

  emp_vals(1) := :new.FIRST_NAME;

  DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD,
            'givenname',emp_vals);

  emp_vals(1) := 'top';
  emp_vals(2) := 'person';
  emp_vals(3) := 'organizationalPerson';
  emp_vals(4) := 'inetOrgPerson';

  DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD,
            'objectclass',emp_vals);

  emp_vals.DELETE;
  emp_vals(1) := :new.PHONE_NUMBER;

  DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD,
            'telephonenumber',emp_vals);

  emp_vals(1) := :new.MOBILE;

  DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD,
            'mobile',emp_vals);

  emp_vals(1) := :new.ROOM_NUMBER;

  DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD,
            'roomNumber',emp_vals);

  emp_vals(1) := :new.TITLE;

  DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD,
            'title',emp_vals);

  emp_vals(1) := :new.EMP_ID;

  DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD,
            'employeeNumber',emp_vals);

  emp_vals(1) := :new.FIRST_NAME;

  DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD,
            'userpassword',emp_vals);
   
  -- DN for Entry to be Added under 'ldap_base' [o=acme, dc=com]
 
  emp_dn := 'cn=' || :new.FIRST_NAME || ' ' || 
   :new.LAST_NAME || ', ' || ldap_base ;
  DBMS_OUTPUT.PUT_LINE(RPAD('Adding Entry for DN ',25,' ') || ': [' 
           || emp_dn || ']');

  -- Add new Entry to ldap directory
  retval := DBMS_LDAP.add_s(emp_session,emp_dn,emp_array);
     DBMS_OUTPUT.PUT_LINE(RPAD('add_s Returns ',25,' ')  || ': '
           || TO_CHAR(retval));

  -- Free attribute array (emp_array)
  DBMS_LDAP.free_mod_array(emp_array);

 END IF; -- INSERTING

 -- Process Entry deletion in database 

 IF DELETING THEN

  -- DN for Entry to be deleted under 'ldap_base' [o=acme, dc=com]

  emp_dn := 'cn=' || :old.FIRST_NAME || ' ' || 
   :old.LAST_NAME || ', ' || ldap_base ;
  DBMS_OUTPUT.PUT_LINE(RPAD('Deleting Entry for DN ',25,' ') || 
         ': [' || emp_dn || ']');

  -- Delete entry in ldap directory
  retval := DBMS_LDAP.delete_s(emp_session,emp_dn);
     DBMS_OUTPUT.PUT_LINE(RPAD('delete_s Returns ',25,' ') || ': ' ||
           TO_CHAR(retval));

 END IF; -- DELETING

 -- Process updated Entry in database

 IF UPDATING THEN


  -- Since two Table columns(in this case) constitue a RDN
  -- check for any changes and update RDN in ldap directory
  -- before updating any other attributes of the Entry.

  IF :old.FIRST_NAME <> :new.FIRST_NAME OR
     :old.LAST_NAME  <> :new.LAST_NAME THEN

   emp_dn := 'cn=' || :old.FIRST_NAME || ' ' || 
    :old.LAST_NAME || ', ' || ldap_base; 

   emp_rdn := 'cn=' || :new.FIRST_NAME || ' ' || :new.LAST_NAME;

   DBMS_OUTPUT.PUT_LINE(RPAD('Renaming OLD DN ',25,' ') ||
           ': [' || emp_dn || ']'); 
   DBMS_OUTPUT.PUT_LINE(RPAD(' => NEW RDN ',25,' ') || 
           ': [' || emp_rdn || ']' );
   retval := DBMS_LDAP.modrdn2_s(emp_session,emp_dn,emp_rdn,
             DBMS_LDAP.MOD_DELETE);
   DBMS_OUTPUT.PUT_LINE(RPAD('modrdn2_s Returns ',25,' ') || ': ' ||
             TO_CHAR(retval));
  END IF;

  -- DN for Entry to be updated under 'ldap_base' [o=acme, dc=com]

  emp_dn := 'cn=' || :new.FIRST_NAME || ' ' || 
   :new.LAST_NAME || ', ' || ldap_base;

  DBMS_OUTPUT.PUT_LINE(RPAD('Updating Entry for DN ',25,' ') || 
            ': [' || emp_dn || ']');

  -- Create and setup attribute array(emp_array) for updated entry
  emp_array := DBMS_LDAP.create_mod_array(7);

  emp_vals(1) := :new.LAST_NAME;

  DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,
            'sn',emp_vals);

  emp_vals(1) := :new.FIRST_NAME;

  DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,
            'givenname',emp_vals);

  emp_vals(1) := :new.PHONE_NUMBER;

  DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,
            'telephonenumber',emp_vals);

  emp_vals(1) := :new.MOBILE;

  DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,
            'mobile',emp_vals);

  emp_vals(1) := :new.ROOM_NUMBER;

  DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,
            'roomNumber',emp_vals);

  emp_vals(1) := :new.TITLE;

  DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,
            'title',emp_vals);

  emp_vals(1) := :new.EMP_ID;

  DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,
            'employeeNumber',emp_vals);

  -- Modify entry in ldap directory
  retval := DBMS_LDAP.modify_s(emp_session,emp_dn,emp_array);

     DBMS_OUTPUT.PUT_LINE(RPAD('modify_s Returns ',25,' ') || ': ' ||
            TO_CHAR(retval));

  -- Free attribute array (emp_array)
  DBMS_LDAP.free_mod_array(emp_array);

 END IF; -- UPDATING

 -- Unbind from ldap directory
 retval := DBMS_LDAP.unbind_s(emp_session);

 DBMS_OUTPUT.PUT_LINE(RPAD('unbind_res Returns ',25,' ') ||  ': ' ||
           TO_CHAR(retval));

 DBMS_OUTPUT.PUT_LINE('Directory operation Successful .. exiting');

 -- Handle Exceptions
 EXCEPTION
  WHEN OTHERS THEN
  -- TODO : should the trigger call unbind at this point ??
  --   what if the exception was raised from unbind itself ??

   DBMS_OUTPUT.PUT_LINE(' Error code    : ' || TO_CHAR(SQLCODE));
   DBMS_OUTPUT.PUT_LINE(' Error Message : ' || SQLERRM);
   DBMS_OUTPUT.PUT_LINE(' Exception encountered .. exiting');

END;
/
