#!/bin/sh

export ORACLE_SID=$1
LOG_DIR="/home/oracle/scripts"
INSTALL_DIR=`pwd`
TRANS_FILE=""

if [ "$ORACLE_SID" = "" ]; then
  echo "Database SID should not be blank, please try again....!"
  exit 1
fi

DB_CONS=$(sqlplus -S /nolog<<SQL1
  connect / as sysdba
  set pages 0
  set feedback off
  SELECT count(username) FROM v\$session WHERE username = 'WFS' and status <> 'KILLED';
  exit
SQL1
)

if [ "$DB_CONS" -gt 0 ]; then
  echo ""
  echo "Please shutdown CMS to close all the database connections and try again....!"
  echo ""
  exit 1
fi

DB_VERSION=$(sqlplus -S /nolog<<SQL1
  connect / as sysdba
  set pages 0
  set feedback off
  SELECT setting_value FROM WFS.SYSTEM_SETTINGS WHERE setting_type = 'RELEASE';
  exit
SQL1
)

if [ "${DB_VERSION}" = "3.2.15" ] || [ "${DB_VERSION}" = "3.2.17" ]; then 
$ORACLE_HOME/bin/sqlplus -s /nolog<<SQEOF  
   connect / as sysdba
   set serveroutput on
   SET VERIFY OFF
   begin 
      execute immediate 'DROP DIRECTORY XMLEVOLVEDIR'; 
   exception
      when others then
         null;
   end;
   /
   CREATE DIRECTORY XMLEVOLVEDIR AS '${INSTALL_DIR}';
   GRANT ALL ON DIRECTORY XMLEVOLVEDIR TO WFS;
   exit
SQEOF
fi

if [ "${DB_VERSION}" = "3.2.15" ] || [ "${DB_VERSION}" = "3.2.17" ]; then 

  if [ "${DB_VERSION}" = "3.2.15" ]; then 
    echo "Detected CMS 2.0.10.65 and running the script, please wait...!"
    TRANS_FILE="ECMFTrans.xsl"
  elif [ "${DB_VERSION}" = "3.2.17" ]; then
    echo "Detected CMS 2.0.020.19 and running the script, please wait...!"
    TRANS_FILE="copy_all.xsl"
  fi
(
$ORACLE_HOME/bin/sqlplus -s / as sysdba<<REF
   conn wfs/Wf\$1234
   spool $LOG_DIR/copy_all_xml.log append
   set serveroutput off 
   set echo off
   set head off
   select 'Dropping xml indexes.' from dual;
   DROP INDEX wfs.mdm_index_xmltype force;
   DROP INDEX wfs.ecmfspec_xmlindex force;
   DROP INDEX wfs.ecmfspec_rev_xmlindex force;
   DROP INDEX wfs.idx_xml_path_tab force;
   DROP INDEX wfs.idx_ecmfdisplaytab_ttvid force;
   BEGIN dbms_xmlindex.dropparameter('ecmfstructuredxml_idx'); END;
   /
   spool off
REF
) >> /dev/null
$ORACLE_HOME/bin/sqlplus -s /nolog<<EOF  
connect / as sysdba
set serveroutput on
SET VERIFY OFF
set echo on
spool ${LOG_DIR}/Copyevolve.log append

grant create any table, create any index, select any table, update any table, insert any table, delete any table, drop any table, alter any table, drop any index, drop any type, create any type, alter any type to WFS;

conn wfs/Wf\$1234
set serveroutput on
WHENEVER SQLERROR EXIT SQL.SQLCODE

begin
  execute immediate 'DROP TABLE "mdm_metadata_evolve"';
exception
  when others then null;
end;
/
ALTER SESSION SET RECYCLEBIN=OFF;
alter table mdm_metadata parallel;
alter table mdm_metadata_revision parallel;

DECLARE
   vcnt_deps number(6);
   verrmsg varchar2(32000);
BEGIN
   select count(*) into vcnt_deps
     from user_dependencies d, user_xml_schemas x
    where d.referenced_type = 'XML SCHEMA'
      and d.referenced_name = x.int_objname
      and x.schema_url = 'ECMFSpec.xsd'
      and name not in ('MDM_METADATA','MDM_METADATA_REVISION');
    dbms_output.put_line('DB Evolve started at '||to_char(sysdate,'mm/dd/yyyy hh24:mi:ss'));
 
   if vcnt_deps > 0 then
      dbms_xmlschema.copyevolve(
         SCHEMAURLS => XDB\$STRING_LIST_T('ECMFSpec.xsd'),
         NEWSCHEMAS => XMLSEQUENCETYPE(xmltype(xmltype(BFILENAME('XMLEVOLVEDIR', 'ECMFSpec.xsd'), NLS_CHARSET_ID('AL32UTF8')).GETCLOBVAL())),
         TRANSFORMS => XMLSEQUENCETYPE(xmltype(xmltype(BFILENAME('XMLEVOLVEDIR', '${TRANS_FILE}'), NLS_CHARSET_ID('AL32UTF8')).GETCLOBVAL())),
         PRESERVEOLDDOCS => true,
         mapTabName => 'mdm_metadata_evolve',
         force => TRUE,
         SCHEMAOWNERS => XDB\$STRING_LIST_T('WFS'),
         options => DBMS_XMLSCHEMA.COPYEVOLVE_BINARY_XML,    
         parallelDegree => 32
         );
   else 
      dbms_xmlschema.copyevolve(
         SCHEMAURLS => XDB\$STRING_LIST_T('ECMFSpec.xsd'),
         NEWSCHEMAS => XMLSEQUENCETYPE(xmltype(xmltype(BFILENAME('XMLEVOLVEDIR', 'ECMFSpec.xsd'), NLS_CHARSET_ID('AL32UTF8')).GETCLOBVAL())),
         TRANSFORMS => XMLSEQUENCETYPE(xmltype(xmltype(BFILENAME('XMLEVOLVEDIR', '${TRANS_FILE}'), NLS_CHARSET_ID('AL32UTF8')).GETCLOBVAL())),
         PRESERVEOLDDOCS => true,
         mapTabName => 'mdm_metadata_evolve',
         SCHEMAOWNERS => XDB\$STRING_LIST_T('WFS'),
         options => DBMS_XMLSCHEMA.COPYEVOLVE_BINARY_XML,    
         parallelDegree => 32 
         );
   end if;

   UPDATE WFS.SYSTEM_SETTINGS SET setting_value='${DB_VERSION}.1' WHERE setting_type = 'RELEASE';
   UPDATE WFS.SYSTEM_RELEASE  SET setting_value='${DB_VERSION}.1' WHERE setting_type = 'RELEASE';
   commit;
   dbms_output.put_line('DB Evolve completed successfully at '||to_char(sysdate,'mm/dd/yyyy hh24:mi:ss'));

EXCEPTION
   WHEN others THEN
      verrmsg := substr(sqlerrm,1,500);
      dbms_output.put_line('***Error '||SQLCODE||'-'||verrmsg);
      raise;
END;
/

alter table mdm_metadata noparallel;
alter table mdm_metadata_revision noparallel;

ALTER SESSION SET RECYCLEBIN=ON;

spool off
EOF

else
  echo "You can not run this script, contact DBA for further assistance...."
  exit 1
fi

$ORACLE_HOME/bin/sqlplus -s /nolog<<SQ1  
   connect / as sysdba
   set serveroutput on
   SET VERIFY OFF
   begin 
      execute immediate 'DROP DIRECTORY XMLEVOLVEDIR'; 
   exception
      when others then
         null;
   end;
   /
   exit
SQ1

exit
