/* $Id: sqs60bld.sql,v 1.1 1998/03/26 22:01:07 yxia Exp $	 */
/* $Log: sqs60bld.sql,v $
# Revision 1.1  1998/03/26  22:01:07  yxia
# Initial revision
#
# Revision 1.1  1997/09/03  21:53:39  bgutierr
# Initial revision
#
# Revision 1.4  1997/07/09  23:42:14  aclarke
# log: OCA 2.0.2.11.0 files.
#
# Revision 1.1  1996/03/22  03:09:25  kmuniasa
# log: Initial Version
# */


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

/* -------------------------------------------------------------------- 
** NAME         SQS60BLD.SQL - SQL script to create Oracle data          
**              dictionary views on SQL Server database.                
**                                                                      
** MODIFIED 
**   aclarke     01/16/97 - Added ALL_SOURCE and ALL_ERRORS tables.
**   kmuniasa    03/21/96 - Modifed for SQL Server 6.0 based on 4.21 scripts
** -------------------------------------------------------------------- */

/* 
** SQS60BLD.SQL
**
** To use, you must use PLUS33.EXE as follows:
**
** plus33.exe scott/tiger@odbc:sqlserver:qa @sqs60bld.sql
** 
** where scott is the user, tiger the password, sqlserver the odbc datasource,
** sqs60bld.sql as the sql script, and qa  the SQL Server database name.
**
**
** 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
**    ALL_SYNONYMS
**    ALL_TAB_COLUMNS
**    ALL_SOURCE
**    ALL_ERRORS
**    USER_USERS
**    USER_SYNONYMS
**    ACCESSIBLE_COLUMNS
**    PRODUCT_USER_PROFILE
**
*/

SPOOL SQS60BLD.LOG

DROP INDEX S2ODTMAP.IS2ODTMAP;
DROP TABLE S2ODTMAP;
DROP TABLE ALL_SYNONYMS;
DROP TABLE USER_SYNONYMS;
DROP TABLE PRODUCT_USER_PROFILE;
DROP VIEW ALL_USERS;
DROP VIEW ALL_OBJECTS;
DROP VIEW ALL_TABLES;
DROP VIEW ALL_VIEWS;
DROP VIEW ALL_SOURCE;
DROP TABLE ALL_ERRORS;
DROP VIEW ALL_TAB_COLUMNS;
DROP VIEW USER_USERS;
DROP VIEW ACCESSIBLE_COLUMNS;
DROP TABLE TOOL_MODULE;
DROP TABLE ALL_TRIGGERS;


CREATE TABLE S2ODTMAP(STYPE int, SNAME varchar(13), ONAME varchar(12));
INSERT INTO S2ODTMAP VALUES( 34, 'image',     'LONG RAW' ); 
INSERT INTO S2ODTMAP VALUES( 35, 'TEXT',      'LONG'     );
INSERT INTO S2ODTMAP VALUES( 37, 'VARBINARY', 'RAW'      ); 
INSERT INTO S2ODTMAP VALUES( 38, 'INTN',      'NUMBER'   ); 
INSERT INTO S2ODTMAP VALUES( 39, 'VARCHAR',   'VARCHAR'  );
INSERT INTO S2ODTMAP VALUES( 45, 'BINARY',    'RAW'      ); 
INSERT INTO S2ODTMAP VALUES( 47, 'CHAR',      'CHAR'     ); 
INSERT INTO S2ODTMAP VALUES( 48, 'TINYINT',   'NUMBER'   ); 
INSERT INTO S2ODTMAP VALUES( 50, 'BIT',       'RAW'      ); 
INSERT INTO S2ODTMAP VALUES( 52, 'SMALLINT',  'NUMBER'   ); 
INSERT INTO S2ODTMAP VALUES( 56, 'INT',       'NUMBER'   ); 
INSERT INTO S2ODTMAP VALUES( 58, 'SMALLDATETIME','DATE'  ); 
INSERT INTO S2ODTMAP VALUES( 59, 'REAL',      'NUMBER'   ); 
INSERT INTO S2ODTMAP VALUES( 60, 'MONEY',     'NUMBER'   ); 
INSERT INTO S2ODTMAP VALUES( 61, 'DATETIME',  'DATE'     ); 
INSERT INTO S2ODTMAP VALUES( 62, 'FLOAT',     'NUMBER'   ); 
INSERT INTO S2ODTMAP VALUES(109, 'FLOATN',    'NUMBER'   ); 
INSERT INTO S2ODTMAP VALUES(110, 'MONEY',     'NUMBER'   ); 
INSERT INTO S2ODTMAP VALUES(111, 'DATETIMN',  'DATE'     ); 
INSERT INTO S2ODTMAP VALUES(122, 'SMALLMONEY','NUMBER'   ); 


CREATE INDEX IS2ODTMAP ON S2ODTMAP(STYPE, ONAME);


CREATE VIEW ALL_USERS AS
SELECT name USERNAME, uid USER_ID FROM sysusers;

CREATE VIEW ALL_OBJECTS AS
SELECT SU.name OWNER, SO.name OBJECT_NAME, 
       SUBSTRING('TABLE',  1,5 * (1-ABS(SIGN(ASCII(SO.type) - ASCII('U'))))) +
       SUBSTRING('VIEW',   1,4 * (1-ABS(SIGN(ASCII(SO.type) - ASCII('V'))))) + 
       SUBSTRING('PROCEDURE',   1,9 * (1-ABS(SIGN(ASCII(SO.type) - ASCII('P'))))) 
       OBJECT_TYPE, 
       SO.crdate CREATED, 'VALID' STATUS
  FROM sysusers SU, sysobjects SO
 WHERE SU.uid = SO.uid;


CREATE VIEW ALL_SOURCE as
SELECT SU.name OWNER, SO.name NAME,
       SUBSTRING('TABLE',  1,5 * (1-ABS(SIGN(ASCII(SO.type) - ASCII('U'))))) +
       SUBSTRING('VIEW',   1,4 * (1-ABS(SIGN(ASCII(SO.type) - ASCII('V'))))) + 
       SUBSTRING('PROCEDURE',   1,9 * (1-ABS(SIGN(ASCII(SO.type) - ASCII('P'))))) 
       TYPE, 1 LINE, SC.TEXT TEXT
  FROM sysusers SU, sysobjects SO, syscomments SC
 WHERE SU.uid = SO.uid
   AND SO.id  = SC.id;

CREATE TABLE ALL_ERRORS (
   OWNER VARCHAR(30) NOT NULL,
   NAME  VARCHAR(30) NOT NULL,
   TYPE  VARCHAR(12) NULL,
   SEQUENCE INT NOT NULL,
   LINE INT NOT NULL,
   POSITION INT NOT NULL,
   TEXT VARCHAR(255) NOT NULL
   );

CREATE VIEW ALL_TABLES AS
SELECT SU.name OWNER, SO.name TABLE_NAME
  FROM sysusers SU, sysobjects SO
 WHERE SU.uid  = SO.uid 
   AND SO.type = 'U';


CREATE VIEW ALL_VIEWS AS
SELECT SU.name OWNER, SO.name VIEW_NAME
  FROM sysusers SU, sysobjects SO
 WHERE SU.uid  = SO.uid
   AND SO.type = 'V';


/*
** Note: ALL_TAB_COLUMNS will return all column names in uppercase
**       because PL/SQL only supports case insensitive and expects
**       the columns names to be in uppercase to verify columns 
**       within PL/SQL.  
*/

CREATE VIEW ALL_TAB_COLUMNS AS
SELECT SU.name OWNER, SO.name TABLE_NAME, 
       UPPER(SC.name) COLUMN_NAME, S2ODTMAP.ONAME DATA_TYPE,
       (22 * SIGN(CHARINDEX('NUMBER',S2ODTMAP.ONAME))) + 
       (SC.length * (1-SIGN(CHARINDEX('NUMBER',S2ODTMAP.ONAME)))) DATA_LENGTH,
       SC.colid COLUMN_ID,
       SC.length DATA_PRECISION,
       NULL DATA_SCALE,
       'Y' NULLABLE, 
       SC.cdefault DATA_DEFAULT
  FROM sysusers SU, sysobjects SO, syscolumns SC, S2ODTMAP
 WHERE 
       SU.uid  = SO.uid AND
       SO.id   = SC.id  AND
       SC.type = S2ODTMAP.STYPE;


CREATE VIEW ACCESSIBLE_COLUMNS AS
SELECT * FROM ALL_TAB_COLUMNS;


CREATE VIEW USER_USERS AS
SELECT name USERNAME, uid USER_ID, 1 CONNECT_PRIV, 1 RESOURCE_PRIV, 0 DBA_PRIV
  FROM sysusers;


CREATE TABLE ALL_SYNONYMS (
   OWNER          char(30) NOT NULL,
   SYNONYM_NAME   char(30) NOT NULL,
   TABLE_OWNER    char(30),
   TABLE_NAME     char(30) NOT NULL,
   DB_LINK        char(128) );

CREATE TABLE USER_SYNONYMS (
   SYNONYM_NAME   char(30) NOT NULL,
   TABLE_OWNER    char(30),
   TABLE_NAME     char(30) NOT NULL,
   DB_LINK        char(128) );


create table PRODUCT_USER_PROFILE
(
   PRODUCT		char(30),
   USERID		char(30),
   ATTRIBUTE	char(240),
   SCOPE		char(240),
   NUMERIC_VALUE	int,
   CHAR_VALUE	char(240),
   DATE_VALUE	datetime,
   LONG_VALUE	int
);

create table TOOL_MODULE 
(
  OWNER	char(30),
  MODTYPE char(30),
  MODNAME char(30)
);

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  text,
 STATUS      VARCHAR(8),
 DESCRIPTION   text,
 TRIGGER_BODY  image
);



/*
** Grant permission to public on all views
*/
GRANT SELECT ON S2ODTMAP     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_SOURCE   TO PUBLIC;
GRANT SELECT ON ALL_ERRORS   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 TOOL_MODULE TO PUBLIC;
GRANT SELECT ON ALL_TRIGGERS TO PUBLIC;

SPOOL OFF
