--
connect &METADATAOWNER/&METADATAPASSWORD@&TNSALIAS
--
create or replace package IMAGE_PROCESSOR
as

    IMAGE_METADATA_NAMESPACE    constant VARCHAR2(128) := 'http://xmlns.oracle.com/demo/imageMetadata';
    IMAGE_METADATA_SCHEMA_LOC   constant VARCHAR2(128) := 'http://xmlns.oracle.com/demo/imageMetadata.xsd';
    IMAGE_METADATA_PREFIX_IMG   constant VARCHAR2(128) := 'xmlns:img=' || IMAGE_METADATA_NAMESPACE || '"';

    COMBINED_NAMESPACE          constant VARCHAR2(256) := XDB_NAMESPACES.RESOURCE_PREFIX_R || ' ' || XDB_NAMESPACES.EXIF_PREFIX_EXIF || ' ' || IMAGE_METADATA_PREFIX_IMG;

    IMAGE_PUBLISHING_FOLDER     constant VARCHAR2(700) := XDB_CONSTANTS.PUBLIC_FOLDER || '/allImages'; 
    
    procedure HANDLE_EVENT(payload XMLType);
    
end;
/
show errors 
--
create or replace public synonym IMAGE_PROCESSOR for IMAGE_PROCESSOR
/
--
create or replace package body IMAGE_PROCESSOR
as
--
procedure createCategoryFolder(CATEGORY_FOLDER varchar2, CATEGORY_ROOT varchar2)
as
  xdbResource xmltype;
begin

  -- Lock the Root of the Category Tree

  select res
    into xdbResource
    from resource_view
   where equals_path(res,CATEGORY_ROOT) = 1
     for update;
     
  -- xdb_utilities.mkdir(CATEGORY_FOLDER,TRUE);
  -- dbms_xdb.setAcl(CATEGORY_FOLDER,'/sys/acls/published_image_folder_acl.xml');

  XDB.XDBPM_HELPER.MKDIR(CATEGORY_FOLDER,TRUE);
  XDB.XDBPM_HELPER.SETACL(CATEGORY_FOLDER,'/sys/acls/published_image_folder_acl.xml');

end;
--
procedure createWeakLink(TARGET_RESOURCE VARCHAR2,TARGET_FOLDER VARCHAR2,FILENAME VARCHAR2)
as
begin 
  if not dbms_xdb.existsResource(TARGET_FOLDER || '/' || FILENAME) then
    -- dbms_xdb.link(TARGET_RESOURCE,TARGET_FOLDER,FILENAME,DBMS_XDB.LINK_TYPE_WEAK);
    XDB.XDBPM_HELPER.link(TARGET_RESOURCE,TARGET_FOLDER,FILENAME,DBMS_XDB.LINK_TYPE_WEAK);
  end if;
end;
--
function extractImageMetadata(TARGET_RESOURCE VARCHAR2)
return xmltype
as

  EXIF_METADATA        xmlSequenceType;
  EXIF_METADATA_COUNT  number;

  IMAGE_METADATA       xmlType;

  CONTENT              BLOB;
  CSID                 BINARY_INTEGER;  


begin

  -- 
  -- Use ORDIMAGE to extract Meta data from XMLLOB
  --  
  
  -- CONTENT := DBMS_XDB.GETCONTENTBLOB(TARGET_RESOURCE,CSID);
  
  -- select extractValue(res,'/Resource/XMLLob') 
  --   into CONTENT
  --   from RESOURCE_VIEW
  --  where equals_path(res,TARGET_RESOURCE) = 1;

  --
  -- Workaround for the fact that XDBADMIN role and ACL evalution issues when executing in the context of an AQ Notification package
  --
  
  SAVEPOINT READ_CONTENT;
  
  LOCK TABLE IMAGE_METADATA_TABLE IN EXCLUSIVE MODE;
  
  CONTENT := XDB.XDBPM_HELPER.GETCONTENTBLOB(TARGET_RESOURCE);
 
  -- dbms_output.put_line('Content Size = ' || DBMS_LOB.GETLENGTH(CONTENT));
 
  if DBMS_LOB.GETLENGTH(CONTENT) > 0 then

    EXIF_METADATA := ordsys.ordimage.getMetadata(CONTENT,'EXIF');

    -- dbms_output.put_line('Metadata Extraction Complete. Records Found =  : ' || EXIF_METADATA.COUNT());

    if EXIF_METADATA.COUNT() > 0 then
      select xmlElement
             (
               "img:imageMetadata",
               xmlAttributes
               (
                 IMAGE_METADATA_NAMESPACE as "xmlns:img",
                 XDB_NAMESPACES.EXIF_NAMESPACE as "xmlns",
                 XDB_NAMESPACES.XMLINSTANCE_NAMESPACE  as "xmlns:xsi", 
                 IMAGE_METADATA_NAMESPACE || ' ' || IMAGE_METADATA_SCHEMA_LOC as "xsi:schemaLocation"
               ),
               EXIF_METADATA(1)   
             )
        into IMAGE_METADATA
        from dual; 
    end if;    

  end if; 

  ROLLBACK TO SAVEPOINT READ_CONTENT;

  return IMAGE_METADATA;

end;
--
procedure createWeakLinks(TARGET_RESOURCE VARCHAR2, IMAGE_METADATA XMLTYPE)
as
  FILENAME       VARCHAR2(700);
  TARGET_FOLDER  VARCHAR2(700);
  
  CAMERA_MAKE    VARCHAR2(2000);
  CAMERA_MODEL   VARCHAR2(2000);
  DATE_TAKEN     VARCHAR2(64);

  NODECHECK NUMBER;  
begin
  select CAMERA_MAKE, CAMERA_MODEL, DATE_TAKEN
    into CAMERA_MAKE, CAMERA_MODEL, DATE_TAKEN
    from xmltable
         (
           xmlnamespaces
           (
--           IMAGE_METADATA_NAMESPACE as "img",
--           XDB_NAMESPACES.EXIF_NAMESPACE as "exif"    
             'http://xmlns.oracle.com/demo/imageMetadata' as "img",
             'http://xmlns.oracle.com/ord/meta/exif' as "exif"
           ),
           '/img:imageMetadata/exif:exifMetadata'
           passing IMAGE_METADATA
           columns
           CAMERA_MAKE  varchar2(128) path 'exif:TiffIfd/exif:Make/text()',
           CAMERA_MODEL varchar2(128) path 'exif:TiffIfd/exif:Model/text()',
	   DATE_TAKEN   varchar2(128) path 'exif:ExifIfd/exif:DateTimeOriginal/text()'
	 );

  FILENAME := SUBSTR(TARGET_RESOURCE,INSTR(TARGET_RESOURCE,'/',-1)+1);  
	 
  if (CAMERA_MAKE is not NULL and CAMERA_MODEL is not NULL) then
    TARGET_FOLDER := IMAGE_PUBLISHING_FOLDER || '/camera/' || CAMERA_MAKE ||  '/' || CAMERA_MODEL;
    createCategoryFolder(TARGET_FOLDER, IMAGE_PUBLISHING_FOLDER);
    createWeakLink(TARGET_RESOURCE,TARGET_FOLDER,FILENAME);
  end if;
     
  if (DATE_TAKEN is not NULL) then
    TARGET_FOLDER := IMAGE_PUBLISHING_FOLDER|| '/dateTaken/' || SUBSTR(DATE_TAKEN,1,4)  || '/' || SUBSTR(DATE_TAKEN,6,2);
    createCategoryFolder(TARGET_FOLDER, IMAGE_PUBLISHING_FOLDER);
    createWeakLink(TARGET_RESOURCE,TARGET_FOLDER,FILENAME);
  end if;    
  
  -- dbms_xdb.setAcl(TARGET_RESOURCE,'/sys/acls/published_image_acl.xml');
  XDB.XDBPM_HELPER.SETACL(TARGET_RESOURCE,'/sys/acls/published_image_acl.xml');

exception
  when no_data_found then
    null;
end;
--
procedure insertImageMetdata(TARGET_RESOURCE VARCHAR2)
as
  XMLREF ref XMLType;
  SQL_ERROR_CODE NUMBER;
  SQL_ERROR_MSG  VARCHAR2(4000);

  imageMetadata XMLType;

begin
  --
  -- Insert ORDIMAGE metadata into IMAGE_METADATA_TABLE and return REF.
  --

  imageMetadata := extractImageMetadata(TARGET_RESOURCE);

  if (imageMetadata is not null) then

    -- dbms_output.put_line('Processing Metadata');

    -- 
    -- dbms_xdb.appendResourceMetadata(TARGET_RESOURCE,imageMetadata);
    -- 
    
    --
    -- Workaround for the fact that XDBADMIN role and ACL evalution issues when executing in the context of an AQ Notification package
    --

    XDB.XDBPM_HELPER.APPENDRESOURCEMETADATA(TARGET_RESOURCE,imageMetadata);

    dbms_output.put_line('Creating Links.');

    createWeakLinks(TARGET_RESOURCE,imageMetadata);

    dbms_output.put_line('Links Created.');

  end if;
  
end;
--
procedure HANDLE_EVENT(payload XMLType)
as
  RESID raw(16);
  TARGET_RESOURCE varchar2(700);
  EVENT_TYPE pls_integer;

  STACK_TRACE xmltype;
  res boolean;
begin
  EVENT_TYPE := payload.extract('/re:ResourceEvent/re:EventType/text()',XDB_NAMESPACES.RESOURCE_EVENT_PREFIX_RE).getNumberVal();
  TARGET_RESOURCE := payload.extract('/re:ResourceEvent/re:resourcePath/text()',XDB_NAMESPACES.RESOURCE_EVENT_PREFIX_RE).getStringVal();
    
  -- dbms_output.put_line('Event = ' || EVENT_TYPE || '. Path = ' || TARGET_RESOURCE);
   
  if (EVENT_TYPE = DBMS_XEVENT.POST_CREATE_EVENT)  then
    insertImageMetdata(TARGET_RESOURCE);
  end if;

  commit;
exception
  when others then
    select xmlElement
           (
             "ImageProcessingError",
             xmlAttributes(USER as "User",SYSTIMESTAMP as "Timestamp"),
             xmlElement
             (
               "ErrorStack",
               xmlCDATA(DBMS_UTILITY.FORMAT_ERROR_STACK())
             ),
             xmlElement
             (
               "BackTrace",
               xmlCDATA(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE())
             ),
             payload
           )
      into STACK_TRACE
      from DUAL;
    rollback; 
    insert into IMAGE_METADATA_ERROR_TABLE values (STACK_TRACE);
    commit;
end;   
--
end;
/
show errors
--
GRANT EXECUTE on IMAGE_PROCESSOR to public;
/
disconnect
--
