Rem
Rem Copyright (c) 2000 by Oracle Corporation
Rem

----------------------------------------------------------------------------
-- SEQUENCES
----------------------------------------------------------------------------

create sequence wb_rt_notification_seq start with 1 increment by 1 order;
create sequence wb_rt_node_seq start with 1 increment by 1 order;

----------------------------------------------------------------------------
-- TEMPORARY LOB TABLE
----------------------------------------------------------------------------

create global temporary table wb_rt_temporary_clobs
(
  client_id number(22),
  lob clob
)
on commit delete rows;

----------------------------------------------------------------------------
-- NLS MESSAGES
----------------------------------------------------------------------------

create table wb_rt_nls_messages
(
  message_id  number(10),
  language_id varchar2(3),
  message varchar2(512)
);

----------------------------------------------------------------------------
-- CONTEXT ATTRIBUTES
----------------------------------------------------------------------------

create table wb_rt_binary_context_attrs
(
  audit_id number(22) not null,
  attr_name varchar2(64) not null,
  attr_value blob not null,
  constraint bca_pk primary key (audit_id, attr_name) using index tablespace %tndex
);

create table wb_rt_textual_context_attrs
(
  audit_id number(22) not null,
  attr_name varchar2(64) not null,
  attr_value clob not null,
  constraint bta_pk primary key (audit_id, attr_name) using index tablespace %tndex
);

----------------------------------------------------------------------------
-- SERVICE CONTROL FLAGS
----------------------------------------------------------------------------

create table wb_rt_service_control_flags
(
  service_stop_pending number(1) not null,
  active_node number(10) null
);

insert into wb_rt_service_control_flags
(
  service_stop_pending,
  active_node
)
values
(
  0,
  null
);

commit;

----------------------------------------------------------------------------
-- SERVICE NODES
----------------------------------------------------------------------------
          
create table wb_rt_service_nodes
(
  node_id number(22) not null,
  instance_number number(10) not null,
  connect_spec varchar2(4000) null,
  enabled number(1) not null,
  runtime_version varchar2(4000) null,
  server_side_home varchar2(4000) null,
  constraint rn_pk primary key (node_id) using index tablespace %tndex,
  constraint rn_uk unique (instance_number) using index tablespace %tndex
);

----------------------------------------------------------------------------
-- SERVICE JOB LOG
----------------------------------------------------------------------------

create table wb_rt_service_job_logs
(
  time_stamp date not null,
  session_id varchar2(32) not null,
  message varchar2(4000) not null
);
    
----------------------------------------------------------------------------
-- NOTIFICATION CONTROL TABLES
----------------------------------------------------------------------------

create table wb_rt_notification_streams
(
  stream_id number(22) not null,
  message_timeout number(10) null,
  constraint ns_pk primary key (stream_id) using index tablespace %tndex
);

create table wb_rt_notification_components
(
  component_id number(22) not null,
  stream_id number(22) not null,
  event_type number(8) not null, 
  audit_id number(22) not null,
  constraint nc_pk primary key (component_id) using index tablespace %tndex,
  constraint nc_uk unique (stream_id, event_type, audit_id) using index tablespace %tndex,
  constraint nc_fk_ns foreign key (stream_id) 
    references wb_rt_notification_streams (stream_id) on delete cascade 
);

create index wb_rt_idx_nc_ns on wb_rt_notification_components (stream_id) tablespace %tndex;

create table wb_rt_notification_acks
(
  audit_id number(22) not null,
  stream_id number(22) not null,
  ack_type number(8) not null,
  ack_pending number(1) not null,
  constraint no_pk primary key (audit_id) using index tablespace %tndex,
  constraint no_fk_ns foreign key (stream_id) 
    references wb_rt_notification_streams (stream_id) on delete cascade 
);

create index wb_rt_idx_na_ns on wb_rt_notification_acks (stream_id) tablespace %tndex;

----------------------------------------------------------------------------
-- NOTIFICATION QUEUE
----------------------------------------------------------------------------

create sequence wb_rt_notify_priority_seq maxvalue 2000000000 cycle;

create or replace type wb_rt_notify_t
is object
(
  event_type number(8),
  audit_id number(22),
  focus_id number(22),
  end_of_chain number(1)
);
/

begin
  dbms_aqadm.create_queue_table (
    queue_table => 'WB_RT_NOTIFY_QUEUE_TAB',
    queue_payload_type => 'WB_RT_NOTIFY_T',
    sort_list => 'PRIORITY');
end;
/

begin
  dbms_aqadm.create_queue (
    queue_name => 'WB_RT_NOTIFY_QUEUE',
    queue_table => 'WB_RT_NOTIFY_QUEUE_TAB');
end;
/

----------------------------------------------------------------------------
-- ACKNOWLEDGEMENT QUEUE
----------------------------------------------------------------------------

create or replace type wb_rt_acknowledge_t
is object
(
  command number(8)
);
/

begin
  dbms_aqadm.create_queue_table (
    queue_table => 'WB_RT_ACK_QUEUE_TAB',
    queue_payload_type => 'WB_RT_ACKNOWLEDGE_T');
end;
/

begin
  dbms_aqadm.create_queue (
    queue_name => 'WB_RT_ACK_QUEUE',
    queue_table => 'WB_RT_ACK_QUEUE_TAB');
end;
/


----------------------------------------------------------------------------
-- SERVICE REQUEST QUEUE
----------------------------------------------------------------------------

create or replace type wb_rt_service_request_t
is object
(
  request_type number(8),
  focus_id number(22)
);
/

begin
  dbms_aqadm.create_queue_table (
    queue_table => 'WB_RT_SERVICE_QUEUE_TAB',
    queue_payload_type => 'WB_RT_SERVICE_REQUEST_T');
end;
/

begin
  dbms_aqadm.create_queue (
    queue_name => 'WB_RT_SERVICE_QUEUE',
    queue_table => 'WB_RT_SERVICE_QUEUE_TAB');
end;
/