rem  ---------------------------------------------------------------------
rem  Copyright (c) Oracle Corporation 1991.  All Rights Reserved, Worldwide.
rem
rem  NAME: rosbild.sql
rem
rem  DESCRIPTION:
rem
rem  This script installs the Resource Object Store schema in a clients
rem  database (usually USER) and builds the tables and views needed
rem  by ROS for database access.
rem

rem  +-------------------------------------------------------------------+
rem  |  ROS DB SCHEMA - base tables, views, clusters, sequences, indexes |
rem  +-------------------------------------------------------------------+

rem  +------------------------------------------------+
rem  |  ROSSTRUCTS - ROS c-structure classes          |
rem  |      each row maps to a Persistent Bind Desc   |
rem  |      header defintion                          |
rem  +------------------------------------------------+

create view ROSSTRUCTS  as
 select owner psstrcownerP, table_name psstrcnameP, -1 psssid,
 max(column_id) psnflds , '' pscmnt
  from ALL_TAB_COLUMNS
  group by owner, table_name;

rem  +------------------------------------------------+
rem  |  ROSATTRIBS - ROS c-structure class attributes |
rem  |     each row maps to a Persistent Bind Desc    |
rem  |     field Definition (rosDefn)                 |
rem  +------------------------------------------------+

create view ROSATTRIBS  as
select owner pdstrcownerP, table_name pdstrcnameP, column_id pdrelpos,
 column_name pdfnameP,
 data_type||'('||TO_CHAR(data_length)||','
        ||TO_CHAR(data_scale)||')'pdoratype,
 decode
   ( data_type,
        'CHAR',
         decode
           (data_length,1,'rosTYPE_TEXTCHAR',
                decode
                  (substr(column_name,length(column_name)-1,2),
                        '_P','rosTYPE_TEXTP',
                        'rosTYPE_TEXTARR'
                  )
           ),
        'NUMBER',
         decode
           (data_scale,0,'rosTYPE_SB4','rosTYPE_FLOAT'
           ),
        'DATE','rosTYPE_DATEP',
        'RAW', 'rosTYPE_BINARYP',
        'LONG','rosTYPE_LTEXTP',
        'LONG RAW','rosTYPE_LBINARYP',
        'VARCHAR2',
        decode
           (data_length,1,'rosTYPE_TEXTCHAR',
           decode
              (substr(column_name,length(column_name)-1,2),
                    '_A','rosTYPE_TEXTARR',
              decode
                 (substr(column_name,length(column_name)-1,2),
                       '_P','rosTYPE_TEXTP',
                       'rosTYPE_VARCHARP'
                 )
              )
           ),
        'OTHER'
   ) pdrosusertype,
 decode
   (data_type,
        'CHAR',
        decode
        (data_length,1,24576,
                decode
                  (substr(column_name,length(column_name)-1,2),
                        '_P',28672,
                        26624
                  )
        ),
        'NUMBER',decode(data_scale,0,16384,18432),
        'DATE',34816,
        'RAW', 30720,
        'LONG',28672,
        'LONG RAW',43008,
        'VARCHAR2',
        decode
           (data_length,1,24576,
           decode
              (substr(column_name,length(column_name)-1,2),
                    '_A',26624,
              decode
                 (substr(column_name,length(column_name)-1,2),
                       '_P',28672,
                       32768
                 )
              )
           ),
        0
   )pdtype,
 data_length pdmaxlen,0 pdmaskw,0 pdflagw
 from ALL_TAB_COLUMNS;

rem  +------------------------------------------------+
rem  |  ROSSTRINGS - ROS table for all ROS uiStrings  |
rem  |     each row contains 1 ROS uiString. owner is |
rem  |     USER, modid is equivalent to docID,        |
rem  |     stringID identifies string inst in module  |
rem  +------------------------------------------------+

create table ROSSTRINGS
  ( owner     varchar(30)  not null,
    modid     decimal(10)  not null,
    groupid   decimal(10)  not null,
    stringid  decimal(10)  not null,
    lfid      decimal(10)  not null,
    len       decimal(10)  not null,
    cs        decimal(10)  not null,
    str       long raw
  );

create index IROSSTRINGS on ROSSTRINGS (owner, modid, groupid, stringid);

rem  +------------------------------------------------+
rem  |  ROSLF - ROS tables, clusters,and indexes for  |
rem  |     each row contains 1 ROS uiString. owner is |
rem  |     USER, modid is equivalent to docID,        |
rem  |     stringID identifies string inst in module  |
rem  +------------------------------------------------+

rem  +------------------------------------------------+
rem  |  ROSLFDESC - ROS table for storing long binary |
rem  |     fields. each row contains a logical "page" |
rem  |     of the long data ordered by a lpageid      |
rem  |     sequence number                            |
rem  +------------------------------------------------+

create table ROSLFDESC
  ( owner    varchar(30),
    modid    decimal(10),
    id       decimal(10) not null,
    lpageid  decimal(10) not null,
    lflen    decimal(10)         ,
    n        decimal(10)         ,
    datalen  decimal(10) not null,
    data     long raw
  )
  storage
  (
    initial 100K
    next    100K
  );

rem  +------------------------------------------------+
rem  |  ROSTFDESC - ROS table for storing long text   |
rem  |     fields. each row contains a logical "page" |
rem  |     of the long data ordered by a lpageid      |
rem  |     sequence number                            |
rem  +------------------------------------------------+

create table ROSTFDESC
  ( owner    varchar(30),
    modid    decimal(10),
    id       decimal(10) not null,
    lpageid  decimal(10) not null,
    lflen    decimal(10)         ,
    n        decimal(10)         ,
    datalen  decimal(10) not null,
    data     long
  )
  storage
  (
    initial 100K
    next    100K
  );

create index IROSLFDESC on ROSLFDESC(MODID,ID,LPAGEID);
create index IROSTFDESC on ROSTFDESC(MODID,ID,LPAGEID);

create sequence ROSLFDESC_ID maxvalue 1073741824;
create sequence ROSTFDESC_ID maxvalue 1073741824;

create table ROSOBJMAP
  ( appid    decimal(10)   not null,
    mapkey   varchar(255)  not null,
    docID    decimal(10)   not null,
    objID    decimal(10)   not null,
    mapcmt   varchar(255),
    lfid     decimal(10)
  );

create table ROSSEQUENCES
  ( owner    varchar(30),
    modid    decimal(10),
    seqname  varchar(30) not null,
    min_val  decimal(10),
    max_val  decimal(10),
    inc_by   decimal(10),
    currval  decimal(10),
    nextval  decimal(10),
    cachesize decimal(10)
  );

insert into ROSSEQUENCES
 (owner, modid, seqname, min_val, max_val, inc_by, currval, nextval, cachesize)
 values (user, 1, 'ROSLFDESC_ID',1,1073741824,1,1,2,20);

insert into ROSSEQUENCES
 (owner, modid, seqname, min_val, max_val, inc_by, currval, nextval, cachesize)
 values (user, 1, 'ROSTFDESC_ID',1,1073741824,1,1,2,20);

create public synonym ROSSTRUCTS for system.ROSSTRUCTS;
create public synonym ROSATTRIBS for system.ROSATTRIBS;
create public synonym ROSSTRINGS for system.ROSSTRINGS;
create public synonym ROSLFDESC for system.ROSLFDESC;
create public synonym ROSTFDESC for system.ROSTFDESC;
create public synonym ROSOBJMAP for system.ROSOBJMAP;
create public synonym ROSSEQUENCES for system.ROSSEQUENCES;
create public synonym ROSLFDESC_ID for system.ROSLFDESC_ID;
create public synonym ROSTFDESC_ID for system.ROSTFDESC_ID;

commit;

