-- Complete Java downgrade of 11.2.0 database to 8.1.5
-- This script is the part of the downgrade that must be done once 8.1.5 is
-- running.  It loads system classes and support packages without
-- affecting user definitions.

whenever sqlerror exit;

variable test_for_1335603 varchar2(30)

/*
#######################################################################
#######################################################################
  If the following   :test_for_1335603 := 'x'   statement causes
  a PLS-00553 error, and thereby terminates the sqlplus connection,
  simply restart the script in a new sqlplus session.
  This error can occur if the script is run in the same session where
  the database was started.  To avoid this problem, always start a new
  session to run scripts after starting the database.
#######################################################################
#######################################################################
*/

execute :test_for_1335603 := 'x';

whenever sqlerror continue;

-- get rid of 9.2.0 java status tables.  Old values in these tables will
-- be confusing if the database is ever upgraded back to 9.2.0
drop table java$jvm$status;
drop table java$jvm$steps$done;

alter table AURORA$IIOP$SYSTEM$PROPERTIES rename to
  AURORA$IIOP$SYSTEM$PROPERTIES$;

-- Load all the Java classes
create or replace java system;
/

-- Java Sanity check for installation
-- If the following query returns 0, then the Java installation
-- did not suceed
select count(*) from all_objects where object_type like 'JAVA%';

-- Allow OracleDBMSOutputStream to set io streams
alter java class "oracle/aurora/rdbms/OracleDBMSOutputStream" authid definer;

-- ServiceID needs to do Inet address resolution
alter java class "oracle.aurora.jndi.sess_iiop.ServiceId" authid definer;

-- Only SYS should have execute privs on Server
revoke execute on "oracle/aurora/rdbms/Server" from public;

@@initdbj

-- Find all the error messages.  If you
-- get to this point the the Java VM is running, and there are enough classes
-- to run the longname() jsp.

select dbms_java.longname(o.name), text from error$ e,obj$ o where
 e.obj#=o.obj# and o.owner#=0 and o.type#=29;

-- There should be zero results from the above query in a correct
-- reinstallation of the Java VM

-- Create the get error package for LoadJava
create or replace package get_error$ as 
  type myrec is record (errormsg varchar(4000));
  type myrctype is ref cursor return myrec;
  function error_lines (classname varchar2) return myrctype; 
end get_error$;
/
create or replace package body get_error$ as 
  function error_lines (classname varchar2) return myrctype is 
    rc myrctype; short_name varchar2(50); n number;
  begin 
    open rc for select text from user_errors
       where name = dbms_java.shortname(classname);
    return rc; 
  end; 
end get_error$;
/
drop public synonym get_error$;
/
create public synonym get_error$ for get_error$;
/
grant execute on get_error$ to public;
/

-- Aurora ORB initialization

-- Allow the ORB classes to open socket streams
alter java class "com/visigenic/vbroker/orb/TcpSocket" authid definer;

-- The namespace is only manipulated by SYS
alter java class "oracle/aurora/server/publishing/NamespaceRDBMS" authid definer;

-- EJB Bean Generation is a system utility
alter java class "oracle.aurora.ejb.deployment.server.BeanGenerator" authid definer;

alter table AURORA$IIOP$SYSTEM$PROPERTIES$ rename to
  AURORA$IIOP$SYSTEM$PROPERTIES;

-- SQL initialization of the Aurora/ORB

--
-- The aurora$sns bindings and node tables should not be inserted,
-- deleted directly.
create or replace package dbms_orb_context as
  -- make a new object and return its node
  function mkobject (o number, s number, c varchar2, h varchar2) return number;
  -- make a new context and return its node
  function mkcontext (c number) return number;
  -- make a new reference and return its node
  function mkreference (r number) return number;
  -- bind a node to a parent
  procedure link (p number, c number, i varchar2, k varchar2, bt number);
  -- remove a node from a parent
  procedure unlink (p number, i varchar2, k varchar2);
  procedure relink (p number, i varchar2, k varchar2, nc number);
  -- helper for buiding initial context in sql.
  -- chmod operations are in java
  procedure addperm (i number, t number, s number);
end dbms_orb_context;
/

create or replace package body dbms_orb_context as
  -- make a new object and return its node
  function mkobject (o number, s number, c varchar2, h varchar2) return number 
  is
    inode number;
  begin
    select aurora$sns$node_number$.nextval into inode from dual;
    insert into aurora$sns$inode$ 
        (node, type, refcount, creation_ts, last_modified, owner,
         class, schema, helper)
      values (inode, 0, 0, sysdate, sysdate, o, c, s, h);
    return inode;
  end;

  -- make a new context and return its node
  function mkcontext (c number) return number is
    inode number;
  begin
    select aurora$sns$node_number$.nextval into inode from dual;
    insert into aurora$sns$inode$ (node, type, refcount, creation_ts,
                                   last_modified, owner)
      values (inode, 1, 0, sysdate, sysdate, c);
    return inode;
  end;

  -- make a new reference and return its node
  function mkreference (r number) return number is
    inode number;
  begin
    select aurora$sns$node_number$.nextval into inode from dual;
    insert into aurora$sns$inode$ (node, type, refcount, creation_ts,
                                   last_modified, owner)
      values (inode, 3, 0, sysdate, sysdate, r);
    return inode;
  end;

  -- bind a node to a parent
  procedure link (p number, c number, i varchar2, k varchar2, bt number) is
  begin
    -- update the refcount first, as we should be idempotent on
    -- ref managment.
    update aurora$sns$inode$ set refcount = refcount + 1 where node = c;
    insert into aurora$sns$bindings$ (parent, child, id, kind, binding_type)
      values (p, c, i, k, bt);
  end;

  -- remove a node from a parent
  procedure unlink (p number, i varchar2, k varchar2) is
    c number;
  begin
    -- gets the child and lock the rows in bindings$ and inode$ tables
    select b.child into c from aurora$sns$bindings$ b, aurora$sns$inode$ n
      where b.parent = p and b.id = i and b.kind = k and
            n.node = b.child for update;
    -- delete the parent -> child link
    delete from aurora$sns$bindings$ 
      where parent = p and id = i and kind = k;
    -- update the child ref count
    update aurora$sns$inode$ set refcount = refcount - 1 where node = c;
    -- delete the child if refcount is 0
    delete from aurora$sns$inode$ where refcount = 0 and node = c;
    -- if child was deleted also delete its entries in $permissions
    if sql%rowcount > 0 then -- true if the node was deleted in $inode
      delete from aurora$sns$permissions$ where node = c;
      delete from aurora$sns$attributes$ where node = c;
      delete from aurora$sns$refaddr$ where node = c;
    end if;
  exception
    when others then -- catches the case of deleting an unknown binding
      if sql%notfound then
        null;
      end if;
  end;

  -- relink an entry
  procedure relink (p number, i varchar2, k varchar2, nc number) is
    c number;
  begin
    -- gets the child and lock the rows in bindings$ and inode$ tables
    select b.child into c from aurora$sns$bindings$ b, aurora$sns$inode$ n
      where b.parent = p and b.id = i and b.kind = k and
            n.node = b.child for update;
    -- update the child ref count
    update aurora$sns$inode$ set refcount = refcount - 1 where node = c;
    -- update the binding
    update aurora$sns$bindings$ set child = nc where parent = p and id = i and kind = k;
    -- ref managment.
    update aurora$sns$inode$ set refcount = refcount + 1 where node = nc;
    -- delete the child if refcount is 0
    delete from aurora$sns$inode$ where refcount = 0 and node = c;
    -- if child was deleted also delete its entries in $permissions
    if sql%rowcount > 0 then -- true if the node was deleted in $inode
      delete from aurora$sns$permissions$ where node = c;
      delete from aurora$sns$attributes$ where node = c;
      delete from aurora$sns$refaddr$ where node = c;
    end if;
  exception
    when others then -- catches the case of deleting an unknown binding
      if sql%notfound then
        null;
      end if;
  end;

  -- since permissions are lists, the java code that
  -- manipulates this deletes all the permissions, and
  -- adds the new ones since permissions are gc'd with their
  -- associated inode, this should really be done with
  -- some sort of constraint or procedure, but I don't know
  -- how to pass arrays to sql from java. 
  -- helper function for building initial context
  procedure addperm (i number, t number, s number) is
  begin
    insert into aurora$sns$permissions$ (node, type, schema) values (i, t, s);
  end;
end dbms_orb_context;
/

-- SQLJ initialization
@@initsqlj