/* $Id: rdbvubld.sql,v 1.1 1998/03/26 22:00:39 yxia Exp $		*/
/* $Log: rdbvubld.sql,v $
# Revision 1.1  1998/03/26  22:00:39  yxia
# Initial revision
#
# Revision 1.1  1997/09/03  21:53:29  bgutierr
# Initial revision
#
# Revision 1.4  1997/07/09  23:42:01  aclarke
# log: OCA 2.0.2.11.0 files.
#
# Revision 1.1  1996/02/06  18:05:55  kmuniasa
# log: Initial version for d2 1.3 release.
#	*/
/* Copyright (c) 1993, 1994 by the Oracle Corporation */

/* -------------------------------------------------------------------- 
** NAME         RDBVUBLD.SQL - SQL script to create Oracle data          
**              dictionary views on RDB  database.                
**                                                                      
** MODIFIED                                                             
**   01/26/95 - Kmuniasa  Created 
**   11/18/96 - CBarrow	  Changed all_objects view and object_types table
**                        so cols appear as varchar not char 
**			  and espec. owner as varchar(30) (bug 395553)
** -------------------------------------------------------------------- */

/* 
** To use, you must use SQL*Plus.  For example:
**
** PLUS33.EXE scott/tiger@odbc:RDB61 @rdbvubld.sql
**
** where scott is the user name, tiger the password,  RDB61 the odbc
** datasource, rdbvubld.sql the sql script.
**
** BUILDS THE FOLLOWING TABLES/VIEWS FOR ORACLE TOOLS AND GRANTS 
** PUBLIC SELECT PRIV. THESE VIEWS ARE NECESSARY FOR ORACLE TOOLS.
**
**    ALL_USERS
**    ALL_OBJECTS
**    ALL_TABLES
**    ALL_VIEWS
**    ACCESSIBLE_COLUMNS
**    ALL_TAB_COLUMNS
**    R2ODTMAP
**    ALL_SYNONYMS
**    ALL_TAB_COLUMNS
**    USER_USERS
**    USER_SYNONYMS
**    ACCESSIBLE_COLUMNS
**    PRODUCT_USER_PROFILE
**    ROLE_SYS_PRIVS
**    USER_SYS_PRIVS
**    TOOL_MODULE
**    ALL_TRIGGERS
*/

SPOOL RDBVUBLD.LOG

DROP VIEW ALL_OBJECTS;
DROP VIEW ALL_TABLES;
DROP VIEW ALL_VIEWS;
DROP TABLE OBJECT_TYPES;
DROP VIEW ACCESSIBLE_COLUMNS;
DROP VIEW ALL_TAB_COLUMNS;
DROP INDEX IS2ODTMAP;
DROP TABLE  R2ODTMAP;
DROP TABLE ALL_SYNONYMS;
DROP TABLE USER_SYNONYMS;
DROP TABLE PRODUCT_USER_PROFILE;
DROP TABLE ALL_USERS;
DROP VIEW USER_USERS;
DROP TABLE ROLE_SYS_PRIVS;
DROP TABLE USER_SYS_PRIVS;
DROP TABLE TOOL_MODULE;
DROP TABLE ALL_TRIGGERS;

commit;

create table R2ODTMAP (
    RTYPE
        INTEGER,
    RNAME
        VARCHAR (20),
    ONAME
        VARCHAR (20),
    LENMASK
        INTEGER,
    NEG_LENMASK
        INTEGER,
    DATA_SCALE 
	INTEGER);

insert into R2ODTMAP values (14, 'CHAR', 'CHAR', 0, 1, NULL);
insert into R2ODTMAP values (37, 'VARCHAR', 'VARCHAR', 0, 1, NULL);
insert into R2ODTMAP values (6, 'TINYINT', 'NUMBER', 1, 0, NULL);
insert into R2ODTMAP values (7, 'SMALLINT', 'NUMBER', 1, 0, NULL);
insert into R2ODTMAP values (8, 'INTEGER', 'NUMBER', 1, 0, NULL);
insert into R2ODTMAP values (9, 'BIGINT', 'NUMBER', 1, 0, NULL);
insert into R2ODTMAP values (10, 'REAL', 'NUMBER', 1, 0, NULL);
insert into R2ODTMAP values (27, 'FLOAT', 'NUMBER', 1, 0, NULL);
insert into R2ODTMAP values (35, 'DATE', 'DATE', 0, 1, NULL);
insert into R2ODTMAP values (261, 'LIST OF BYTE VARYING', 'LONG RAW', 0, 1, NULL);

commit;

CREATE INDEX IS2ODTMAP ON R2ODTMAP (RTYPE, ONAME);

create table ALL_USERS (
    USERNAME
        VARCHAR (20),
    USER_ID
        SMALLINT);

/*
** At OCA 1.0.13 the type col was CHAR(5) which avoided getting
** all tables/views displayed as Packages in the Obj Navigator !
** We now no longer include types 2 and 3 in the table
*/

create table object_types (
	flag integer,
	type varchar(12));
insert into object_types values (0, 'TABLE');
insert into object_types values (1, 'VIEW');

/*
** insert into object_types values (2, 'PACKAGE');
** insert into object_types values (3, 'PROCEDURE');
*/

create view ALL_OBJECTS
    (OWNER,
     OBJECT_NAME,
     OBJECT_TYPE,
     CREATED, 
     STATUS) as
    select TRIM(SUBSTRING(USER from 1 for 30)),
    TRIM(substring(C1.RDB$RELATION_NAME from 1 for 30)), 
    C2.TYPE, CURRENT_DATE, 'VALID'
    from RDB$RELATIONS C1, OBJECT_TYPES C2
    where ( (c1.RDB$FLAGS = C2.flag  
            or  (c1.RDB$FLAGS-(CAST(c1.RDB$FLAGS/2 - 0.5 as INT)* 2) = c2.flag) )
    and   C1.RDB$SYSTEM_FLAG = 0);
   
create view ALL_TABLES (OWNER, TABLE_NAME) as
    select TRIM(substring(USER from 1 for 30)), 
    TRIM(substring(C1.RDB$RELATION_NAME from 1 for 30)) 
    from RDB$RELATIONS C1 
    where RDB$SYSTEM_FLAG = 0 
    and (RDB$FLAGS = 0 OR  (RDB$FLAGS - (CAST(RDB$FLAGS/2  - 0.5  as INT) * 2) = 0));  

 create view ALL_VIEWS (OWNER, VIEW_NAME) as
     select TRIM(substring(USER from 1 for 30)), 
     TRIM(substring(C1.RDB$RELATION_NAME from 1 for 30)) 
     from RDB$RELATIONS C1 
     where RDB$SYSTEM_FLAG = 0 
     and  ( RDB$FLAGS - (CAST(RDB$FLAGS/2  - 0.5  as INT) * 2) = 1); 

create view ALL_TAB_COLUMNS
    (OWNER,
     TABLE_NAME,
     COLUMN_NAME,
     DATA_TYPE,
     DATA_LENGTH,
     COLUMN_ID,
     DATA_PRECISION,
     DATA_SCALE,
     NULLABLE,
     DATA_DEFAULT) as
    select
    USER,
    TRIM(SUBSTRING(C1.RDB$RELATION_NAME FROM 1 FOR 30)),
    TRIM(SUBSTRING(C2.RDB$FIELD_NAME FROM 1 FOR 30)),
        C3.ONAME,
    ((22 * C3.LENMASK) + (C2.RDB$FIELD_LENGTH * C3.NEG_LENMASK)),
        C2.RDB$FIELD_ID,
        C2.RDB$FIELD_LENGTH,
        C3.DATA_SCALE,
    'Y',
        C4.RDB$DEFAULT_VALUE
    from RDB$RELATIONS C1, RDB$FIELD_VERSIONS C2, R2ODTMAP C3, 
    RDB$RELATION_FIELDS C4
    where ((((C1.RDB$RELATION_ID = C2.RDB$RELATION_ID)
        and (C1.RDB$RELATION_NAME = C4.RDB$RELATION_NAME))
        and (C2.RDB$FIELD_ID = C4.RDB$FIELD_ID))
        and (C2.RDB$FIELD_TYPE = C3.RTYPE));

create view ACCESSIBLE_COLUMNS
    (OWNER,
     TABLE_NAME,
     COLUMN_NAME,
     DATA_TYPE,
     DATA_LENGTH,
     COLUMN_ID,
     DATA_PRECISION,
     DATA_SCALE,
     NULLABLE,
     DATA_DEFAULT) as
    select
    USER,
    TRIM(SUBSTRING(C1.RDB$RELATION_NAME FROM 1 FOR 30)),
    TRIM(SUBSTRING(C2.RDB$FIELD_NAME FROM 1 FOR 30)),
    C3.ONAME,
    ((22 * C3.LENMASK) + (C2.RDB$FIELD_LENGTH * C3.NEG_LENMASK)),
        C2.RDB$FIELD_ID,
        C2.RDB$FIELD_LENGTH,
        C2.RDB$FIELD_SCALE,
    'Y',
        C4.RDB$DEFAULT_VALUE
    from RDB$RELATIONS C1, RDB$FIELD_VERSIONS C2, R2ODTMAP C3, 
    RDB$RELATION_FIELDS C4
    where ((((C1.RDB$RELATION_ID = C2.RDB$RELATION_ID)
        and (C1.RDB$RELATION_NAME = C4.RDB$RELATION_NAME))
        and (C2.RDB$FIELD_ID = C4.RDB$FIELD_ID))
        and (C2.RDB$FIELD_TYPE = C3.RTYPE));

create view USER_USERS
    (USERNAME,
     USER_ID,
     CONNECT_PRIV,
     RESOURCE_PRIV,
     DBA_PRIV) as
    select
    USER,
    1,
    1,
    1,
    0
    from RDB$RELATIONS C1;

create table ALL_SYNONYMS (
    OWNER
        VARCHAR (30)
        constraint ALL_SYNONYMS_OWNER_NOT_NULL
            not null
            deferrable,
    SYNONYM_NAME
        VARCHAR (30)
        constraint ALL_SYNONYMS_NOT_NULL1
            not null
            deferrable,
    TABLE_OWNER
        VARCHAR (30),
    TABLE_NAME
        VARCHAR (30)
        constraint ALL_SYNONYMS_NOT_NULL2
            not null
            deferrable,
    DB_LINK
        VARCHAR (128));

create table USER_SYNONYMS (
    SYNONYM_NAME
        VARCHAR (30)
        constraint USER_SYNONYMS_NOT_NULL1
            not null
            deferrable,
    TABLE_OWNER
        VARCHAR (30),
    TABLE_NAME
        VARCHAR (30)
        constraint USER_SYNONYMS_NOT_NULL2
            not null
            deferrable,
    DB_LINK
        VARCHAR (128));

create table PRODUCT_USER_PROFILE (
    PRODUCT
        VARCHAR (30),
    USERID
        VARCHAR (30),
    ATTRIBUTE
        VARCHAR (240),
    SCOPE
        VARCHAR (240),
    NUMERIC_VALUE
        INTEGER,
    CHAR_VALUE
        VARCHAR (240),
    DATE_VALUE
        DATE VMS,
    LONG_VALUE
        INTEGER);
 
create table ROLE_SYS_PRIVS (
    ROLE
        VARCHAR (30),
    PRIVILEGE
        VARCHAR (40),
    ADMIN_OPTION
        VARCHAR (3));
 
create table USER_SYS_PRIVS (
    USERNAME
        VARCHAR (30),
    PRIVILEGE
        VARCHAR (40),
    ADMIN_OPTION
        VARCHAR (3));

create table TOOL_MODULE (
	OWNER VARchar(31),
	MODTYPE VARchar(31),
	MODNAME VARchar(31));

create table ALL_TRIGGERS
(
 OWNER      VARCHAR(30) NOT NULL,
 TRIGGER_NAME  VARCHAR(30) NOT NULL,
 TRIGGER_TYPE  VARCHAR(16),
 TRIGGERING_EVENT  VARCHAR(26),
 TABLE_OWNER  VARCHAR(30) NOT NULL,
 TABLE_NAME   VARCHAR(30) NOT NULL,
 REFERENCING_NAMES   VARCHAR(87),
 WHEN_CLAUSE  VARCHAR(2000),
 STATUS      VARCHAR(8),
 DESCRIPTION   VARCHAR(2000),
 TRIGGER_BODY  list of byte varying
);

commit;

GRANT SELECT ON R2ODTMAP     TO PUBLIC;
GRANT SELECT ON ALL_USERS    TO PUBLIC;
GRANT SELECT ON ALL_OBJECTS  TO PUBLIC;
GRANT SELECT ON ALL_TABLES   TO PUBLIC;
GRANT SELECT ON ALL_VIEWS    TO PUBLIC;
GRANT SELECT ON ALL_SYNONYMS TO PUBLIC;
GRANT SELECT ON ALL_TAB_COLUMNS TO PUBLIC;
GRANT SELECT ON ACCESSIBLE_COLUMNS TO PUBLIC;
GRANT SELECT ON USER_USERS TO PUBLIC;
GRANT SELECT ON USER_SYNONYMS TO PUBLIC;
GRANT SELECT ON PRODUCT_USER_PROFILE TO PUBLIC;
GRANT SELECT ON USER_SYS_PRIVS TO PUBLIC;
GRANT SELECT ON ROLE_SYS_PRIVS TO PUBLIC;
GRANT SELECT on TOOL_MODULE to PUBLIC;
GRANT SELECT on ALL_TRIGGERS to PUBLIC;

commit;

SPOOL OFF

