/* Copyright (c) 1997 by the Oracle Corporation */

/* -------------------------------------------------------------- 
** NAME     INGVUBLD.SQL - SQL script to create Oracle-style data          
**          dictionary views against INGRES.                
**                                                                      
** --------------------------------------------------------------
**
** To run this script, please use SQL*Plus with syntax similar to :-
**
** plus33w.exe scott/tiger@odbc:ING64 @ingvubld.sql 
** 
** where scott is the user name, tiger the password, ING64 the ODBC
** datasource, ingvubld.sql the sql script.
**
** This script should be run by the DBA (or database owner) against
** each Ingres database which is to be used for Developer/2000 
** application DEVELOPMENT.  It is NOT necessary for RUNNING finished 
** applications. It creates a certain number of views and a minimal 
** number of tables.  All the table names start "d2k_" so that they 
** may easily be distinguished from other (application) tables.
**
*/

SPOOL INGVUBLD.LOG

/*
** Drop tables and views in reverse order of creation.  Under Ingres,
** If you drop an item that has other items depending on it, the depending items
** are also dropped.
*/
drop view ALL_TRIGGERS; 
drop view TOOL_MODULE;
drop view USER_SYS_PRIVS;
drop view ROLE_SYS_PRIVS;
drop view PRODUCT_USER_PROFILE;
drop view USER_SYNONYMS;
drop view ALL_SYNONYMS;
drop view ACCESSIBLE_COLUMNS;
drop view ALL_TAB_COLUMNS;
drop view ALL_VIEWS;
drop view ALL_TABLES;
drop view ALL_ERRORS;
drop view ALL_SOURCE;
drop view ALL_OBJECTS;
drop view USER_USERS;
drop view ALL_USERS;
drop table d2k_object_types;
drop table d2k_empty_table;
drop table d2k_dtmap;

commit;

/* Ingres to Oracle data type mapping */
create table d2k_dtmap (
    ingname        varchar(20),
    oraname        varchar(9),
    isnumber       smallint
);

insert into d2k_dtmap values ('C',        'CHAR',    0);
insert into d2k_dtmap values ('CHAR',     'CHAR',    0);
insert into d2k_dtmap values ('CHARACTER','CHAR',    0);
insert into d2k_dtmap values ('VARCHAR',  'VARCHAR', 0);
insert into d2k_dtmap values ('TEXT',     'VARCHAR', 0);
insert into d2k_dtmap values ('INTEGER1', 'NUMBER',  1);
insert into d2k_dtmap values ('SMALLING', 'NUMBER',  1);
insert into d2k_dtmap values ('INTEGER2', 'NUMBER',  1);
insert into d2k_dtmap values ('INTEGER',  'NUMBER',  1);
insert into d2k_dtmap values ('INT',      'NUMBER',  1);
insert into d2k_dtmap values ('INTEGER4', 'NUMBER',  1);
insert into d2k_dtmap values ('FLOAT4',   'NUMBER',  1);
insert into d2k_dtmap values ('REAL',     'NUMBER',  1);
insert into d2k_dtmap values ('FLOAT',    'NUMBER',  1);
insert into d2k_dtmap values ('FLOAT8',   'NUMBER',  1);
insert into d2k_dtmap values ('DOUBLE PRECISION','NUMBER',1);
insert into d2k_dtmap values ('DATE',     'DATE',    0);
insert into d2k_dtmap values ('MONEY',    'NUMBER',  1);

commit;

/* Create one empty table as basis for all empty views 
   to avoid having to create lots of empty tables */

create table d2k_empty_table (
    intcol    integer,
    datecol   date,
    vc3col    VARCHAR(3),
    vc8col    VARCHAR(8),
    vc12col   VARCHAR(12),
    vc16col   VARCHAR(16),
    vc26col   VARCHAR(26),
    vc87col   VARCHAR(87),
    vc30col   VARCHAR(30),
    vc40col   VARCHAR(40),
    vc100col  VARCHAR(100),
    vc128col  VARCHAR(128),
    vc240col  VARCHAR(240)
);

/* Ingres Table_type to Oracle object_type map */
create table d2k_object_types (
	typevalue char(1),
	typename varchar(12));
insert into d2k_object_types values ('T', 'TABLE');
insert into d2k_object_types values ('V', 'VIEW');

commit;

create view ALL_USERS as
SELECT trim(left(user_name, 30)) as USERNAME,
       0 as USER_ID
  from iidbconstants;

create view USER_USERS as
SELECT USERNAME,
    1 as CONNECT_PRIV, 1 as RESOURCE_PRIV, 0 as DBA_PRIV
    from all_users;

/* iitables gives tables, views and indexes, with table_type
   'T', 'V' or 'I' respectively */

create view ALL_OBJECTS AS
SELECT trim(left(table_owner, 30)) as OWNER,
       trim(left(table_name, 30)) as OBJECT_NAME, 
       ot.typename as OBJECT_TYPE, 
       date(create_date) as CREATED,
       'VALID' as STATUS
  FROM iitables, d2k_object_types ot
  WHERE table_type = ot.typevalue
  AND table_type != 'I'
UNION
SELECT trim(left(procedure_owner, 30)) as OWNER,
       trim(left(procedure_name, 30)) as OBJECT_NAME,
       'PROCEDURE' as OBJECT_TYPE,
       date(create_date) as CREATED,
       'VALID' as STATUS
  FROM iiprocedures;
   
create view ALL_SOURCE as
SELECT trim(left(procedure_owner, 30)) as OWNER, 
       trim(left(procedure_name, 30)) as NAME,
       'PROCEDURE' as TYPE,
       text_sequence as LINE, 
       text_segment as TEXT
  FROM iiprocedures;

create view ALL_ERRORS as
SELECT vc30col  as OWNER,
       vc30col  as NAME,
       vc12col  as TYPE,
       intcol   as SEQUENCE,
       intcol   as LINE,
       intcol   as POSITION,
       vc240col as TEXT
  FROM d2k_empty_table 
  WHERE 1=2;

create view ALL_TABLES as
SELECT trim(left(table_owner, 30)) as OWNER,
       trim(left(table_name, 30)) as TABLE_NAME
  FROM iitables
  WHERE table_type = 'T';
  
create view ALL_VIEWS as
SELECT trim(left(table_owner, 30)) as OWNER,
       trim(left(table_name, 30))  as VIEW_NAME
  FROM iiviews;
  
/* NOTE: we do a join with the d2k_dtmap table to return only 
         Oracle (i.e. PL/SQL) types and to make columns 
         mapping to NUMBER always return 22 for DATA_LENGTH
         (because this is what Oracle does).
*/

create view ALL_TAB_COLUMNS as
SELECT trim(left(c.table_owner, 30)) as OWNER,
       trim(left(c.table_name, 30)) as TABLE_NAME,
       trim(left(c.column_name, 30)) as COLUMN_NAME,
       map.oraname      as DATA_TYPE,
       ((22 * map.isnumber) + 
        (c.column_length * (1-map.isnumber))) as DATA_LENGTH,
       c.column_sequence as COLUMN_ID,
       c.column_length   as DATA_PRECISION,
       c.column_scale    as DATA_SCALE,
       c.column_nulls    as NULLABLE,
       trim(NULL)      as DATA_DEFAULT
  FROM iicolumns c, d2k_dtmap map
  WHERE varchar(uppercase(c.column_datatype)) = map.ingname;

create view ACCESSIBLE_COLUMNS as
SELECT * FROM ALL_TAB_COLUMNS;

create view ALL_SYNONYMS as
SELECT vc30col as OWNER,
       vc30col as SYNONYM_NAME,
       vc30col as TABLE_OWNER,
       vc30col as TABLE_NAME,
       vc128col as DB_LINK
  FROM d2k_empty_table 
  WHERE 1=2;

create view USER_SYNONYMS as
SELECT SYNONYM_NAME,
       TABLE_OWNER,
       TABLE_NAME,
       DB_LINK
  FROM ALL_SYNONYMS;

create view PRODUCT_USER_PROFILE as
SELECT vc30col  as PRODUCT,
       vc30col  as USERID,
       vc240col as ATTRIBUTE,
       vc240col as SCOPE,
       intcol   as NUMERIC_VALUE,
       vc240col as CHAR_VALUE,
       datecol  as DATE_VALUE,
       vc240col as LONG_VALUE
  FROM d2k_empty_table 
  WHERE 1=2;

create view ROLE_SYS_PRIVS as
SELECT vc30col  as ROLE,
       vc40col  as PRIVILEGE,
       vc3col   as ADMIN_OPTION
  FROM d2k_empty_table 
  WHERE 1=2;
 
create view USER_SYS_PRIVS as
SELECT vc30col  as USERNAME,
       vc40col  as PRIVILEGE,
       vc3col   as ADMIN_OPTION
  FROM d2k_empty_table 
  WHERE 1=2;

create view TOOL_MODULE as
SELECT vc30col as OWNER,
       vc30col as MODTYPE,
       vc30col as MODNAME
  FROM d2k_empty_table
  WHERE 1=2;

create view ALL_TRIGGERS as 
SELECT vc30col as OWNER,
       vc30col as TRIGGER_NAME,
       vc16col as TRIGGER_TYPE,
       vc26col as TRIGGERING_EVENT,
       vc30col as TABLE_OWNER,
       vc30col as TABLE_NAME,
       vc87col as REFERENCING_NAMES,
      vc128col as WHEN_CLAUSE,
        vc8col as STATUS,
      vc128col as DESCRIPTION,
      vc128col as TRIGGER_BODY
  FROM d2k_empty_table
  WHERE 1=2;

commit;

/* For Ingres, the current user must be the DBA of the
   current database for the following to work.  Comment
   out the following statements if that is not the case. */

GRANT SELECT ON d2k_dtmap        TO PUBLIC;
GRANT SELECT ON d2k_object_types TO PUBLIC;
GRANT SELECT ON d2k_empty_table  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


