Rem
Rem
Rem apxldimg.sql
Rem
Rem Copyright (c) 2004,2009, Oracle Corporation.  All rights reserved.
Rem
Rem    NAME
Rem      apxldimg.sql - Application Express Load Images
Rem
Rem    DESCRIPTION
Rem      This script should be run as SYS and takes one argument, the path
Rem      to the directory where the Application Express 3.2.1 patch exists
Rem      on the filesystem.  e.g., @apxldimg /tmp/patch
Rem
Rem
Rem    MODIFIED   (MM/DD/YYYY)
Rem     jkallman   05/26/2009 - Created
Rem     jkallman   05/27/2009 - Correct image path, add exception block for folder already present in XDB
Rem     jkallman   07/20/2009 - Load all as encoded in AL32UTF8
Rem     jkallman   07/22/2009 - Correct path for creation of APEX_IMAGES directory
Rem

timing start "Load Images"

begin
    execute immediate 'drop directory APEX_IMAGES';
exception when others then
    null;
end;
/

begin
    execute immediate 'drop directory APEX_PATCH_DIR';
exception when others then
    null;
end;
/


create directory APEX_IMAGES as '&1/images';
create directory APEX_PATCH_DIR as '&1/patches/3.2.1';

set serveroutput on

declare
    file_list                varchar2(30)  default 'imagelist_321_patch.xml';
    upload_directory_name    varchar2(30)  default 'APEX_IMAGES';
    imagelist_directory_name varchar2(30)  default 'APEX_PATCH_DIR';
    ro_anonymous_acl         varchar2(80)  := '/sys/acls/ro_anonymous_acl.xml';    
    repository_folder_path   varchar2(30);
    pathseparator            varchar2(1)   := '/';

    directory_path      varchar2(256);

    target_folder_path  varchar2(256);
    target_file_path    varchar2(256);
    target_file_name    varchar2(256);

    resource_path       varchar2(256);

    filelist_xml        xmltype := xmltype(bfilename(imagelist_directory_name,file_list),nls_charset_id('AL32UTF8'));
    content_bfile       bfile;

    result              boolean;

    filelist_dom    dbms_xmldom.domdocument;
    files_nl        dbms_xmldom.domnodelist;
    directory_nl    dbms_xmldom.domnodelist;
    filename_nl     dbms_xmldom.domnodelist;
    files_node      dbms_xmldom.domnode;
    directory_node  dbms_xmldom.domnode;
    file_node       dbms_xmldom.domnode;
    text_node       dbms_xmldom.domnode;

begin

  if wwv_flow_utilities.db_version_is_at_least('11') then
    repository_folder_path := '/images/';
  else
    repository_folder_path := '/i/';
  end if;
  
  --
  -- Remove the /doc directory, as it will be recreated as part of this loading process
  --
  begin
      dbms_xdb.deleteresource( repository_folder_path || 'doc', dbms_xdb.delete_recursive_force );
      commit;
  exception
      when others then
          if sqlcode <> -31001 then
              raise;
          end if;
  end;
  


  -- create the set of new folders in the xdb repository

  filelist_dom := dbms_xmldom.newdomdocument(filelist_xml);

  directory_nl := dbms_xmldom.getelementsbytagname(filelist_dom,'directory');

  for i in 0 .. (dbms_xmldom.getlength(directory_nl) - 1) loop
    directory_node := dbms_xmldom.item(directory_nl,i);
    text_node      := dbms_xmldom.getfirstchild(directory_node);
    directory_path := dbms_xmldom.getnodevalue(text_node);
    directory_path := repository_folder_path || directory_path;
    begin
        result          := dbms_xdb.createfolder(directory_path);
    exception
        when others then
            if sqlcode <> -31003 then
                raise;
            end if;
    end;
  end loop;

    -- load the resources into the xml db repository

  files_nl           := dbms_xmldom.getelementsbytagname(filelist_dom,'files');
  files_node         := dbms_xmldom.item(files_nl,0);

  filename_nl := dbms_xmldom.getelementsbytagname(filelist_dom,'file');

  for i in 0 .. (dbms_xmldom.getlength(filename_nl) - 1) loop
    file_node          := dbms_xmldom.item(filename_nl,i);

    text_node          := dbms_xmldom.getfirstchild(file_node);

    target_file_path   := dbms_xmldom.getnodevalue(text_node);
    target_file_name   := substr(target_file_path,instr(target_file_path,pathseparator,-1)+1);
    target_folder_path := substr(target_file_path,1,instr(target_file_path,pathseparator,-1));
    target_folder_path := substr(target_folder_path,instr(target_folder_path,pathseparator));
    target_folder_path := substr(target_folder_path,1,length(target_folder_path)-1);
    resource_path := repository_folder_path || target_folder_path || '/' || target_file_name;

    begin
        content_bfile := bfilename(upload_directory_name,target_file_path);
        --
        begin
            dbms_xdb.deleteresource( resource_path, dbms_xdb.delete_force );
        exception
            when others then
                if sqlcode <> -31001 then
                    raise;
                end if;
        end;
    
        result := dbms_xdb.createresource(resource_path,content_bfile,nls_charset_id('AL32UTF8'));
        --
        if wwv_flow_utilities.db_version_is_at_least('11') then
            dbms_xdb.setACL(resource_path, ro_anonymous_acl);            
        end if;    
    exception when others then
        dbms_output.put_line('file not found: '||target_file_path);
    end;
    --
    if dbms_lob.fileisopen( content_bfile ) = 1 then
        dbms_lob.close( content_bfile );
    end if;

  end loop;


end;
/

commit;

timing stop

drop directory APEX_IMAGES;

drop directory APEX_PATCH_DIR;