rem
rem  $Header: /s/ros/992/spam/precious/files/ACTIVE/toolbild.sql,v 1.2 1998/04/20 19:16:02 spoonen Exp $
rem
rem  Copyright (c) Oracle Corporation 1991.  All Rights Reserved, Worldwide.
rem
rem  toolbild.sql
rem
rem  This script installs the shared module storage tables in a
rem  central location (usually SYSTEM) and builds views and public
rem  synonyms that allow modules to be shared, yet guarantee security
rem  of the module definitions.
rem
rem  dlai       08/10/93   tools_accessible view now allow wildcards for
rem                        matching user-names.
create table tool__access
(
  product  varchar(30),     /* Product Name                */
  owner    varchar(30),     /* Module Owner                */
  modid    decimal(10),     /* Module ID                   */
  grantee  varchar(30)      /* User to whom access granted */
);

create index tool_access_idx on tool__access
(
  owner, modid
);

create index tool_accessible_idx on tool__access
(
  product, grantee, modid
);

create view tool_access as
  select * from tool__access
  where owner = user
  with check option;

rem create view tool_accessible as   /* This will only work in V7 */
rem  select * from tool__access
rem  where grantee = 'PUBLIC' or grantee = user
rem    or grantee in (select role from enabled_roles);

create view tool_accessible as   /* This will work if the one above failed */
  select * from tool__access
  where grantee = 'PUBLIC' or user like grantee;

create public synonym tool_access for system.tool_access;
create public synonym tool_accessible for system.tool_accessible;

rem  +--------------------------------------+
rem  |  TOOL_LIBRARY -  attached libraries  |
rem  +--------------------------------------+
rem
rem  Two indices are created.  TOOL_LIBRARY_IDX is used when the module is
rem  edited.


create table tool__library
(
  product  varchar(30),     /* Product Name                 */
  owner    varchar(30),     /* Module Owner                 */
  modid    decimal(10),     /* Module ID                    */
  libname  varchar(255),    /* Name of the library attached */
  state    decimal(10)      /* Current state of the library */
);

create index tool_library_idx on tool__library
(
  product, libname, modid
);

create view tool_library as
  select * from tool__library
  where owner = user or modid in (select modid from tool_accessible)
  with check option;

create public synonym tool_library for system.tool_library;

rem  +---------------------------------------------+
rem  |  TOOL_MODID - Module ID generator sequence  |
rem  +---------------------------------------------+


create sequence tool_modid
  increment by  1
  start with    100
  maxvalue      4294967295
  nocycle
  nocache;

create public synonym tool_modid for system.tool_modid;

rem  +-------------------------------------------+
rem  |  TOOL_MODULE - module header information  |
rem  +-------------------------------------------*/


create table tool__module
(
  product     varchar(30),   /* Product Name                      */
  modtype     varchar(30),   /* Module Type                       */
  modid       decimal(10),   /* Module ID                         */
  next_itemid decimal(10),   /* Next available internal ID        */
  owner       varchar(30),   /* Module Owner                      */
  modname     varchar(30),   /* Module Name                       */
  creator     varchar(30),   /* User who created the module       */
  create_date date,          /* Creation Date                     */
  create_ver  decimal(10),   /* Product Version created with      */
  modifier    varchar(30),   /* User who last modified the module */
  mod_date    date,          /* Modification Date                 */
  mod_ver     decimal(10),   /* Product version modified with     */
  copyright   varchar(250),  /* Module Copyright Notice           */
  req_role    varchar(30),   /* Role required to run this module  */
  tcs_ver     decimal(10),   /* Tool Module version               */
  vgs_ver     decimal(10),   /* VGS version                       */
  de_ver      decimal(10),   /* DE version                        */
  ros_ver     decimal(10)    /* ROS version                       */
);

create index tool_modtype_idx on tool__module
(
  product, modtype
);

create unique index tool_module_idx on tool__module
(
  owner, modname, modtype
);

create view tool_module as
  select * from tool__module
  where owner = user or modid in (select modid from tool_accessible)
  with check option;

create public synonym tool_module for system.tool_module;

rem  +--------------------------------------------+
rem  |  TOOL_LTEXT - Shared long text data table  |
rem  +--------------------------------------------*/


create table tool__ltext
(
  owner     varchar(30),   /* Module Owner                */
  modid     decimal(10),   /* Module ID                   */
  id        decimal(10),   /* Long Field ID               */
  lpageid   decimal(10),   /* (backward compatibility?)   */
  lflen     decimal(10),   /* (backward compatibility?)   */
  n         decimal(5),    /* Long Field Sequence Number  */
  datalen   decimal(10),   /* Long Field Length           */
  data      long           /* Long Text Chunk             */
)
storage
(
  initial 100K
  next    100K
);

create index tool_ltext_idx1 on tool__ltext
(
  owner, modid
);

create unique index tool_ltext_idx2 on tool__ltext
(
  id, lpageid
);

create view tool_ltext as
  select * from tool__ltext
  where owner = user or modid in (select modid from tool_accessible)
  with check option;

create public synonym tool_ltext for system.tool_ltext;

rem  +---------------------------------------------------+
rem  |  TOOL_LTEXT_ID - Long Text ID generator sequence  |
rem  +---------------------------------------------------+


create sequence tool_ltext_id
  increment by  1
  start with    1
  maxvalue      1073741824
  nocycle
  nocache;

create public synonym tool_ltext_id for system.tool_ltext_id;

rem  +------------------------------------------+
rem  |  TOOL_LRAW - Shared long raw data table  |
rem  +------------------------------------------*/


create table tool__lraw
(
  owner     varchar(30),   /* Module Owner                */
  modid     decimal(10),   /* Module ID                   */
  id        decimal(10),   /* Long Field ID               */
  lpageid   decimal(10),   /* (backward compatibility?)   */
  lflen     decimal(10),   /* (backward compatibility?)   */
  n         decimal(5),    /* Long Field Sequence Number  */
  datalen   decimal(10),   /* Long Field Length           */
  data      long raw       /* Long Raw Data Chunk         */
)
storage
(
  initial 100K
  next    100K
);

create index tool_lraw_idx1 on tool__lraw
(
  owner, modid
);

create index tool_lraw_idx2 on tool__lraw
(
  id, lpageid
);

create view tool_lraw as
  select * from tool__lraw
  where owner = user or modid in (select modid from tool_accessible)
  with check option;

create public synonym tool_lraw for system.tool_lraw;

rem  +-------------------------------------------------+
rem  |  TOOL_LRAW_ID - Long Raw ID generator sequence  |
rem  +-------------------------------------------------+


create sequence tool_lraw_id
  increment by  1
  start with    1
  maxvalue      1073741824
  nocycle
  nocache;

create public synonym tool_lraw_id for system.tool_lraw_id;

rem  +--------------------------------------------+
rem  |  TOOL_PLSQL - Standard PL/SQL code tables  |
rem  +--------------------------------------------*/


create table tool__plsql
(
  owner      varchar(30),   /* Module Owner                     */
  modid      decimal(10),   /* Module ID                        */
  itemid     decimal(10),   /* Item (program unit) ID           */
  object_id  decimal(10),   /* Associated Object ID (optional)  */
  type       decimal(4),    /* Type (optional, product-defined) */
  name       varchar(30),   /* Program Unit name                */
  plslfid_st decimal(10),   /* PL/SQL Source Text long field ID */
  plslfid_ep decimal(10),   /* PL/SQL Encoded Progunit long field ID */
  state      decimal(10)    /* Current state of the program unit */
);

create unique index tool_plsql_idx on tool__plsql
(
  owner, modid, itemid
);

create view tool_plsql as
  select * from tool__plsql
  where owner = user or modid in (select modid from tool_accessible)
  with check option;

create public synonym tool_plsql for system.tool_plsql;

rem  +-----------------------------------------------------+
rem  |  TOOL_COMMENT - Standard Comment/Annotation Tables  |
rem  +-----------------------------------------------------+


create table tool__comment
(
  owner     varchar(30),   /* Module Owner                          */
  modid     decimal(10),   /* Module ID                             */
  itemid    decimal(10),   /* Item (comment) ID                     */
  object_id decimal(10),   /* ID of commented object                */
  cmtlfid_s decimal(10),   /* Comment long field ID (sound comment) */
  cmtlfid_t decimal(10),   /* Comment long field ID (text comment)  */
  cmttext   long,          /* Comment text   (see note below)       */
  cmtlen    decimal(9)     /* Comment Length (see note below)       */
);

rem - NOTE: The columns cmttext and cmtlen exist for backward
rem -       compatibility with SQL*FORMS. Please stop using them
rem -       and store your comments in the long text table instead.
rem -       The long field id of the entry in the long text table
rem -       should then be stored in the itemid column.
rem -
rem -       Cmttext and Cmtlen will be removed from the definition
rem -       of this table by CA 1.0.5.

create unique index tool_comment_idx on tool__comment
(
  owner, modid, itemid
);

create view tool_comment as
  select * from tool__comment
  where owner = user or modid in (select modid from tool_accessible)
  with check option;

create public synonym tool_comment for system.tool_comment;

rem  +---------------------------------------------+
rem  |  TOOL_DEPEND - Dependency Management Table  |
rem  +---------------------------------------------+


create table tool__depend
(
  owner      varchar(30),   /* Module Owner                      */
  modid      decimal(10),   /* Module ID                         */
  itemid     decimal(10),   /* Item ID (optional)                */
  dep_modid  decimal(10),   /* Module Dependent on               */
  dep_itemid decimal(10),   /* Item Dependent on (optional)      */
  dep_type   varchar(250),  /* Dependency Type (product-defined) */
  depchanged varchar(1)     /* Dependent-object-has-changed flag */
);

create view tool_depend as
  select * from tool__depend
  where owner = user or modid in (select modid from tool_accessible)
  with check option;

create view tool_dependent as
  select * from tool__depend
  where dep_modid in (select modid from tool_module);

create public synonym tool_depend for system.tool_depend;
create public synonym tool_dependent for system.tool_dependent;

