rem 
rem $Header: catdefer.sql 7010300.1 94/02/24 18:24:12 snataraj Generic<base> $ 
rem 
Rem  Copyright (c) 1992 by Oracle Corporation 
Rem    NAME
Rem      catdefer.sql - catalog of deferred rpc queues
Rem    DESCRIPTION
Rem      catalog of deferred rpc queues
Rem      This file contains sql which creates the base tables
Rem      used to store deferred remote procedure calls for used in
Rem      transaction replication.
Rem      Tables:
Rem         defError
Rem         defTranDest
Rem         defCallDest
Rem         defDefaultDest
Rem         defTran
Rem         defCall
Rem         defSchedule
Rem    RETURNS
Rem 
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem    MODIFIED   (MM/DD/YY)
Rem     rjenkins   01/17/94 -  changing jq to job
Rem     rjenkins   12/17/93 -  creating job queue
Rem     dsdaniel   10/28/93 -  deferred rpc dblink security
Rem                         -  also removed table drops, since shouldnt
Rem                         -  loose data on upgrade
Rem     dsdaniel   10/26/93 -  merge changes from branch 1.1.400.1
Rem     dsdaniel   10/10/93 -  Creation from dbmsdefr
rem create base tables

DROP VIEW deferror;
DROP VIEW deferrcount;
DROP VIEW deftrandest;
DROP VIEW defcalldest;
DROP VIEW defcall;
DROP VIEW deftran;

--  create a table for deferred transactions. This has one row for 
--  each transaction.
--  transaction id size is taken from gendef.h 
CREATE TABLE def$_tran (
  deferred_tran_id VARCHAR2(22),  -- transaction id
  deferred_tran_db VARCHAR2(128), -- creation or copying node
    CONSTRAINT dfrpc$_tran_primary
      PRIMARY KEY(deferred_tran_id, deferred_tran_db),
  origin_tran_id   VARCHAR2(22),  -- original tid (if copied)
  origin_tran_db   VARCHAR2(128), -- origial  node
  origin_user_id   NUMBER,        -- userid deferring tran
  origin_user      VARCHAR2(30),  -- user deferring tran (used at remote)
  delivery_order   NUMBER,        -- order to deliver to destinations
  destination_list CHAR(1),       -- R = RepSchema, D = def$_calldest
  start_time       DATE,          -- time original tid started
  commit_comment VARCHAR2(50));   -- commit comment
/
-- table which defines the execution order for defered transactions

--  create the call table.  One row for each deferred call.
CREATE TABLE def$_call  (
  callno            NUMBER,         -- UID of call, orders calls in transaction
  deferred_tran_db VARCHAR2(128),   -- origin
    CONSTRAINT def$_calls_primary
      PRIMARY KEY(callno, buffer_number, deferred_tran_db),
  deferred_tran_id  VARCHAR2(22),   -- transaction id
    CONSTRAINT def$_calls_prnt
       FOREIGN KEY(deferred_tran_id,  deferred_tran_db)
       REFERENCES def$_tran(deferred_tran_id, deferred_tran_db)
       ON DELETE CASCADE,
  schemaname    VARCHAR2(30),      -- schema name
  packagename   VARCHAR2(30),      -- package name
  procname      VARCHAR2(30),      -- procedue name
  argcount      NUMBER,            -- # of args 
  buffer_number NUMBER,            -- parameters buffer number 
  parm_buffer   LONG RAW);         -- parameters buffer    
/
--  create the  table that identifies a call to be executed	
--  at a remote node. One row for each callsXnode.
--  The projection of this table by node is the queue of transactions for 
--  a node.
CREATE TABLE def$_calldest(
  callno           NUMBER,        -- call id 
  deferred_tran_id VARCHAR2(22),  -- deferred transaction
  deferred_tran_db VARCHAR2(128), -- deferred transaction
  dummy_buffer_number NUMBER DEFAULT 1, -- for forign key reference
    CONSTRAINT def$_calldest_call
      FOREIGN KEY(callno, dummy_buffer_number,  deferred_tran_db)
      REFERENCES def$_call(callno, buffer_number, deferred_tran_db),
  dblink           VARCHAR2(128)  -- dblink to destination
    CONSTRAINT def$_call_dblink_null NOT NULL);
/                    
-- create the table that is the queue of transacitons for a destination
CREATE TABLE def$_trandest(
  deferred_tran_id VARCHAR2(22),  -- deferred transaction
  deferred_tran_db VARCHAR2(128), -- deferred transaction
    CONSTRAINT def$_trandest_tran
      FOREIGN KEY(deferred_tran_id, deferred_tran_db)
      REFERENCES def$_tran(deferred_tran_id, deferred_tran_db),
  dblink           VARCHAR2(128)  -- dblink to destination
    CONSTRAINT def$_trandest_dblink_null NOT NULL);
/

--  create the table where the exceptions get logged. One row for each
--  transactionXorigin_node when the execution of the transaction at 
--  this  node encountered  an error.
CREATE TABLE def$_error(
  deferred_tran_db VARCHAR2(128),  -- node origination/copying txn
  deferred_tran_id VARCHAR2(22),   -- transaction id
    CONSTRAINT def$_error_tran
      FOREIGN KEY(deferred_tran_id, deferred_tran_db)
      REFERENCES def$_tran(deferred_tran_id, deferred_tran_db),
  callno           NUMBER,           -- UID of call
-- forign key constrain on call not used.  Users can create error transacitons
-- with null or invalid callno - it is for documenation only
--  dummy_buffer_number NUMBER DEFAULT 1,
--    CONSTRAINT def$_error_call
--      FOREIGN KEY(callno, dummy_buffer_number, deferred_tran_db)
--      REFERENCES def$_call(callno, buffer_number, deferred_tran_db),
   destination     VARCHAR2(128),  -- dblink transaciton destined to
     CONSTRAINT def$_error_primary
        PRIMARY KEY(deferred_tran_id, deferred_tran_db, destination),
   error_time      DATE,           -- time at which 
                                   -- conflit occured
   error_number    NUMBER,         -- error 
                                   -- number reported
   error_msg       VARCHAR2(200)); -- error message
/
CREATE VIEW deferror AS
  SELECT deferred_tran_db, deferred_tran_id, callno, destination, 
         error_time, error_number, error_msg 
    FROM def$_error;
/
DROP PUBLIC SYNONYM deferror;
CREATE PUBLIC SYNONYM deferror for deferror;
CREATE VIEW deferrcount AS
  SELECT count(1) errcount, destination 
    FROM deferror GROUP BY destination;
/
DROP PUBLIC SYNONYM deferrcount;
CREATE PUBLIC SYNONYM deferrcount for deferrcount;
GRANT SELECT ON deferrcount TO PUBLIC;
/
CREATE VIEW deftrandest AS
  SELECT * from def$_trandest;
/
DROP PUBLIC SYNONYM deftrandest;
CREATE PUBLIC SYNONYM deftrandest FOR deftrandest;
CREATE VIEW defcalldest AS
  SELECT  callno, deferred_tran_id, deferred_tran_db, dblink
    FROM def$_calldest
/
DROP PUBLIC SYNONYM defcalldest;
CREATE PUBLIC SYNONYM defcalldest for defcalldest;
CREATE VIEW defcall AS
  SELECT  callno, deferred_tran_db, deferred_tran_id, schemaname, packagename,
          procname, argcount 
    FROM def$_call
    WHERE buffer_number = 1;
/
DROP PUBLIC SYNONYM defcall;
CREATE PUBLIC SYNONYM defcall FOR defcall;
CREATE VIEW deftran AS
  SELECT deferred_tran_id, deferred_tran_db, origin_tran_id, origin_tran_db,
         NVL(u.name,t.origin_user) origin_user, delivery_order, 
         destination_list, start_time, commit_comment
    FROM def$_tran t, sys.user$ u
    WHERE u.user# (+)= t.origin_user_id;
/
DROP PUBLIC SYNONYM deftran;
CREATE PUBLIC SYNONYM deftran FOR deftran;
--  Create table of default nodes for replication targets
--  this table is managed by calls in dbms_defer_sys
DROP TABLE def$_defaultdest;
DROP VIEW  defdefaultdest;
CREATE TABLE def$_defaultdest (
  dblink VARCHAR2(128)  -- dblink 
    CONSTRAINT def$_defalutdest_primary
    PRIMARY KEY);
/
CREATE VIEW defdefaultdest AS
  SELECT * from def$_defaultdest;
/
DROP PUBLIC SYNONYM defdefaultdest;
CREATE PUBLIC SYNONYM defdefaultdest for defdefaultdest;

DROP TABLE def$_schedule;
DROP VIEW defschedule;
CREATE TABLE def$_schedule(
  dblink         VARCHAR2(128)  -- queue name
    CONSTRAINT def$_schedule_primary PRIMARY KEY,
  job            NUMBER,        -- number of job which does the push
  alert_name     VARCHAR2(30),  -- unused
  last_txn_count NUMBER,        -- number of transacitons executed lat push
  last_error     NUMBER,        -- sqlcode from last push
  last_msg       VARCHAR2(255));-- error message from last push
/

CREATE VIEW defschedule  AS
  SELECT s.dblink, s.job, j.interval, next_date, j.last_date
    FROM def$_schedule s, sys.job$ j where s.job = j.job(+);
/
DROP PUBLIC SYNONYM defschedule;
CREATE PUBLIC SYNONYM defschedule FOR defschedule;
