Liren Large Software Subsidy 5/05.iso/a/a522/6.ddi/SRW_ICEN.SQL

Text File | 1990-12-05 | 15.3 KB | 418 lines

rem
rem $Header: srw_icen.sql,v 1.1.11.3 90/05/09 14:02:40 dsimson Exp $srw_icen.sql
rem

rem  +---------------------------------------------------------------------+
rem  |  SRW_ICEN.SQL  -- INSTALLATION SCRIPT, CENTRALIZED TABLES           |
rem  |                                                                     |
rem  |  This script installs the SQL*ReportWriter tables in a central      |
rem  |  location, (SYSTEM) and builds views and public synonyms that       |
rem  |  allow reports to be shared, yet guarantee security of the report   |
rem  |  definitions.                                                       |
rem  +---------------------------------------------------------------------+

set termout off;
drop index srw_cluster_id;
drop table srw__report;
drop table srw__ste;
drop table srw__query;
drop table srw__fkey;
drop table srw__group;
drop table srw__field;
drop table srw__summary;
drop table srw__text;
drop table srw__text_long;
drop table srw__param;
drop cluster srw_cluster;
set termout on;

rem  +--------------------------------------------+
rem  |  SRW_GRANT:  Access grants to other users  |
rem  +--------------------------------------------+

set termout off;
drop view srw_grant;
drop public synonym srw_grant;
delete from system.product_access where product = 'SQL*ReportWriter';
set termout on;

create view srw_grant as
  select
    product,           /* 'SQL*ReportWriter' */
    owner,             /* Owner */
    numeric_id appid,  /* Report ID */
    grantee            /* User to whom granted access */
  from
    system.product_access
    where product = 'SQL*ReportWriter'
      and owner   = user
  with check option;

create public synonym srw_grant for system.srw_grant;

rem  +---------------------------------------------------------------+
rem  |  SRW_GRANTED:  View on profile of reports you have access to  |
rem  +---------------------------------------------------------------+

set termout off;
drop view srw_granted;
drop public synonym srw_granted;
set termout on;

create view srw_granted as
  select
    numeric_id appid,  /* Report ID */
    grantee            /* User to whom granted access */
  from system.product_access
  where product   = 'SQL*ReportWriter'
    and (upper(grantee) = 'PUBLIC' or user like upper(grantee));

create public synonym srw_granted for system.srw_granted;

rem  +-----------------------------------------+
rem  |  SRW_REPORT:  Report-level information  |
rem  +-----------------------------------------+

set termout off;
drop view srw_report;
drop sequence srw_next_appid;
drop public synonym srw_report;
drop public synonym srw_next_appid;
set termout on;

create table srw__report
(
  appid             decimal(9) not null,  /* Report ID */
  next_itemid       decimal(9) not null,  /* Next Available Object ID */
  report_name       char(80),             /* Report Name */
  page_height       decimal(3),           /* Page Height */
  page_width        decimal(3),           /* Page Width */
  left_margin       decimal(3),           /* Left Margin */
  right_margin      decimal(3),           /* Right Margin */
  top_margin        decimal(3),           /* Top Margin */
  bottom_margin     decimal(3),           /* Bottom Margin */
  version           decimal(5),           /* SRW Version when created */
  modified_version  decimal(5),           /* SRW Version last modified */
  comments          long,                 /* Comments */
  owner             char(30),             /* Current Owner */
  modifier          char(30),             /* Last Modifier */
  create_date       date,                 /* Date Created */
  modified_date     date,                 /* Date last Modified */
  param_title       char(80),             /* Title for parameter form */
  param_hint        char(80),             /* Hint line for param form */
  param_status      char(50)              /* Status line for param form */
);

create unique index srw_report_id on srw__report (appid);
create unique index srw_name_id on srw__report (owner, report_name);

create view srw_report as
  select * from system.srw__report where owner = user or appid in
  (select appid from srw_granted)
  with check option;

create public synonym srw_report for system.srw_report;

create sequence srw_next_appid
  start with 100
  increment by 1
  nomaxvalue nocycle nocache;

create public synonym srw_next_appid for system.srw_next_appid;

rem  +--------------------------+
rem  |  SRW_STE:  Symbol Table  |
rem  +--------------------------+

set termout off;
drop view srw_ste;
drop public synonym srw_ste;
set termout on;

create table srw__ste
(
  owner   char(30),             /* Owner */
  appid   decimal(9) not null,  /* Report ID */
  itemid  decimal(9) not null,  /* Object ID */
  type    decimal(2),           /* Object Type */
  name    char(80)              /* Object Name */
);

create unique index srw_ste_id on srw__ste (appid, itemid, type);

create view srw_ste as
  select * from system.srw__ste where owner = user or appid in
  (select appid from srw_granted)
  with check option;

create public synonym srw_ste for system.srw_ste;

rem  +---------------------------------+
rem  |  SRW_QUERY:  Select Statements  |
rem  +---------------------------------+

set termout off;
drop view  srw_query;
drop public synonym srw_query;
set termout on;

create table srw__query
(
  owner            char(30),             /* Owner */
  appid            decimal(9) not null,  /* Report ID */
  itemid           decimal(9) not null,  /* Query ID */
  parentid         decimal(9),           /* Parent Query ID */
  matrix_parentid  decimal(9),           /* Parent Query 2 ID */
  query_order      decimal(2),           /* Order on Screen */
  query            long                  /* SELECT Statement Text */
);

create unique index srw_query_id on srw__query (appid, itemid);

create view srw_query as
  select * from system.srw__query where owner = user or appid in
  (select appid from srw_granted)
  with check option;

create public synonym srw_query for system.srw_query;

rem  +-----------------------------------------------+
rem  |  SRW_FKEY:  Parent-child query relationships  |
rem  +-----------------------------------------------+

set termout off;
drop view srw_fkey;
drop public synonym srw_fkey;
set termout on;

create table srw__fkey
(
 owner          char(30),             /* Owner */
 appid          decimal(9) not null,  /* Report ID */
 itemid         decimal(9) not null,  /* Query ID */
 parentid       decimal(9),           /* Parent Query ID */
 local_tlpos    decimal(3),           /* Select List Position in Query */
 foreign_tlpos  decimal(3)            /* Select List Position in Parent */
);

create index srw_fkey_id on srw__fkey (appid, itemid, parentid);

create view srw_fkey as
  select * from system.srw__fkey where owner = user or appid in
  (select appid from srw_granted)
  with check option;

create public synonym srw_fkey for system.srw_fkey;

rem  +-------------------------------------------+
rem  |  SRW_GROUP:  Groups and their attributes  |
rem  +-------------------------------------------+

set termout off;
drop view srw_group;
drop public synonym srw_group;
set termout on;

create table srw__group
(
  owner          char(30),             /* Owner */
  appid          decimal(9) not null,  /* Report ID */
  itemid         decimal(9) not null,  /* Group ID */
  queryid        decimal(9),           /* Associated Query ID */
  page_break     decimal(1),           /* Page Break Style */
  group_order    decimal(2),           /* Order on Screen */
  repetition     decimal(1),           /* Repetition Direction */
  lines_before   decimal(3),           /* Lines Before Group */
  spaces_before  decimal(3),           /* Spaces Before Group */
  inter_row      decimal(3),           /* Spacing Between Records */
  inter_field    decimal(3),           /* Spacing Between Fields */
  field_hilite   decimal(2),           /* Field Highlight Style */
  label_hilite   decimal(2),           /* Label Highlight Style */
  relative_pos   decimal(1),           /* Relative Position Below Parent  */
  fields_across  decimal(3),           /* Maximum Fields Across */
  multi_panel    char(1),              /* Span Multiple Panels or Not */
  matrix_flag    char(1),              /* Matrix Group Indicator */
  locate_labels  decimal(1)            /* Label Placement */
);

create unique index srw_group_id on srw__group (appid, itemid);

create view srw_group as
  select * from system.srw__group where owner = user or appid in
  (select appid from srw_granted)
  with check option;

create public synonym srw_group for system.srw_group;

rem  +-------------------------------------------+
rem  |  SRW_FIELD:  Fields and their attributes  |
rem  +-------------------------------------------+

set termout off;
drop view srw_field;
drop public synonym srw_field;
set termout on;

create table srw__field
(
  owner            char(30),             /* Owner */
  appid            decimal(9) not null,  /* Report ID */
  itemid           decimal(9) not null,  /* Field ID */
  groupid          decimal(9) not null,  /* Owning Group */
  source_query     decimal(9),           /* Query of Source Column */
  target_position  decimal(3),           /* Select List Position in Query */
  compute          char(240),            /* Computing Procedure */
  heading          char(240),            /* Field Label */
  skip             char(1),              /* Skip/Print */
  field_order      decimal(3),           /* Order on Screen */
  format_mask      char(40),             /* Display Format */
  width            decimal(3),           /* Physical Field Width */
  datatype         decimal(1),           /* Datatype of Field */
  operator         decimal(2),           /* Computed Field Function */
  reset_group      decimal(9),           /* Group Where Computation Resets */
  reprint          char(1),              /* Display on All Panels of Group */
  relative_pos     decimal(1),           /* Relative Position to Previous Field */
  lines_before     decimal(3),           /* Lines Before Field */
  spaces_before    decimal(3),           /* Spaces Before Field */
  alignment        decimal(1)            /* Justification of Value within Field */
);

create unique index srw_field_id on srw__field (appid, itemid);

create view srw_field as
  select * from system.srw__field where owner = user or appid in
  (select appid from srw_granted)
  with check option;

create public synonym srw_field for system.srw_field;

rem  +------------------------------------------------+
rem  |  SRW_SUMMARY:  Summaries and their attributes  |
rem  +------------------------------------------------+

set termout off;
drop view srw_summary;
drop public synonym srw_summary;
set termout on;

create table srw__summary
(
  owner        char(30),             /* Owner */
  appid        decimal(9) not null,  /* Report ID */
  itemid       decimal(9) not null,  /* Summary ID */
  fieldid      decimal(9) not null,  /* Field to Summarize */
  sum_order    decimal(3),           /* Order on Screen */
  operator     decimal(2),           /* Summary Operator */
  width        decimal(3),           /* Summary Field Width */
  datatype     decimal(1),           /* Summary Datatype */
  format_mask  char(40),             /* Display Format */
  print_at     decimal(9),           /* Group Where Summary Appears */
  reset_at     decimal(9)            /* Group Where Summary Resets */
);

create unique index srw_summary_id on srw__summary (appid, itemid);

create view srw_summary as
  select * from system.srw__summary where owner = user or appid in
  (select appid from srw_granted)
  with check option;

create public synonym srw_summary for system.srw_summary;

rem  +-------------------------------+
rem  |  SRW_TEXT:  Text information  |
rem  +-------------------------------+

set termout off;
drop view srw_text;
drop public synonym srw_text;
set termout on;

create table srw__text
(
  owner          char(30),             /* Owner */
  appid          decimal(9) not null,  /* Report ID */
  itemid         decimal(9) not null,  /* Owning Object ID */
  type           decimal(2),           /* Text Type */
  dirty          char(1),              /* Edited or Default State */
  relative_pos   decimal(1),           /* Relative Position to Previous Text */
  lines_before   decimal(3),           /* Lines Before Text */
  spaces_before  decimal(3),           /* Spaces Before Text */
  repeat         char(1),              /* Repeat Text on Page Overflow */
  justification  decimal(1),           /* Justification of Text within Area */
  frequency      decimal(9),           /* Appear with which Group (Col Hdng) */
  width          number(3)             /* Width if contains variable fields */
);

create unique index srw_text_id on srw__text (appid, itemid, type);

create view srw_text as
  select * from system.srw__text  where owner = user or appid in
  (select appid from srw_granted)
  with check option;

create public synonym srw_text for system.srw_text;

rem  +-------------------------------+
rem  |  SRW_TEXT_LONG:  Text chunks  |
rem  +-------------------------------+

set termout off;
drop view srw_text_long;
drop public synonym srw_text_long;
set termout on;

create table srw__text_long
(
  owner   char(30),             /* Owner */
  appid   decimal(9) not null,  /* Report ID */
  itemid  decimal(9) not null,  /* Owning Object ID */
  type    decimal(2),           /* Text Type */
  panel   decimal(2),           /* Panel Number */
  text    long                  /* Text Chunk */
);

create unique index srw_id_text_long
  on srw__text_long (appid, itemid, type, panel);

create view srw_text_long as
  select * from system.srw__text_long where owner = user or appid in
  (select appid from srw_granted)
  with check option;

create public synonym srw_text_long for system.srw_text_long;

rem  +-----------------------------------------------+
rem  |  SRW_PARAM:  Parameters and their attributes  |
rem  +-----------------------------------------------+

set termout off;
drop view srw_param;
drop public synonym srw_param;
set termout on;

create table srw__param
(
  owner          char(30),             /* Owner */
  appid          decimal(9) not null,  /* Report ID */
  itemid         decimal(9) not null,  /* Parameter ID */
  param_order    decimal(3),           /* Order on Screen */
  datatype       decimal(1),           /* Parameter Datatype */
  width          decimal(3),           /* Parameter Width */
  default_value  char(240),            /* Initial value */
  label          char(240),            /* Parameter Label */
  param_type     decimal(2),           /* Query or Text Parameter */
  input_mask     char(40),             /* Input Format Mask */
  output_mask    char(40),             /* Output Format Mask */
  skip           char(1)               /* Don't show on parameter form */
);

create unique index srw_param_id on srw__param (appid, itemid);

create view srw_param as
  select * from system.srw__param where owner = user or appid in
  (select appid from srw_granted)
  with check option;

create public synonym srw_param for system.srw_param;