
CREATE TABLE NS2ODTMAP(
	NSTYPE CHAR(18), 
	ONAME char(12), 
	L_FACTOR SMALLINT,
	INV_FACTOR SMALLINT)

INSERT INTO NS2ODTMAP VALUES('LARGEINT', 'NUMBER', 1, 0)
INSERT INTO NS2ODTMAP VALUES('LARGEINT SIGNED', 'NUMBER', 1, 0)
INSERT INTO NS2ODTMAP VALUES('INTEGER', 'NUMBER', 1, 0)
INSERT INTO NS2ODTMAP VALUES('INTEGER SIGNED', 'NUMBER', 1, 0)
INSERT INTO NS2ODTMAP VALUES('INTEGER UNSIGNED', 'NUMBER', 1, 0)
INSERT INTO NS2ODTMAP VALUES('SMALLINT', 'NUMBER', 1, 0)
INSERT INTO NS2ODTMAP VALUES('SMALLINT SIGNED', 'NUMBER', 1, 0)
INSERT INTO NS2ODTMAP VALUES('SMALLINT UNSIGNED', 'NUMBER', 1, 0)
INSERT INTO NS2ODTMAP VALUES('FLOAT', 'NUMBER', 1, 0)
INSERT INTO NS2ODTMAP VALUES('REAL', 'NUMBER', 1, 0)
INSERT INTO NS2ODTMAP VALUES('DOUBLE PRECISION', 'NUMBER', 1, 0)
INSERT INTO NS2ODTMAP VALUES('CHAR', 'CHAR', 0, 1)
INSERT INTO NS2ODTMAP VALUES('VARCHAR', 'VARCHAR',0, 1)
INSERT INTO NS2ODTMAP VALUES('NUMERIC', 'NUMBER', 1, 0)
INSERT INTO NS2ODTMAP VALUES('NUMERIC SIGNED', 'NUMBER', 1, 0)
INSERT INTO NS2ODTMAP VALUES('NUMERIC UNSIGNED', 'NUMBER', 1, 0)
INSERT INTO NS2ODTMAP VALUES('DECIMAL', 'NUMBER', 1, 0)
INSERT INTO NS2ODTMAP VALUES('DECIMAL SIGNED', 'NUMBER', 1, 0)
INSERT INTO NS2ODTMAP VALUES('DECIMAL UNSIGNED', 'NUMBER', 1, 0)
INSERT INTO NS2ODTMAP VALUES('DATETIME', 'DATE', 0, 1)

CREATE INDEX INS2ODTMAP ON NS2ODTMAP(NSTYPE, ONAME)


CREATE VIEW ALL_USERS(USERNAME, USER_ID) AS
	SELECT NAME, UID 
	FROM SYSUSERS

CREATE TABLE OBJECT_TYPES (ID CHAR(1), TYPE CHAR(20))
INSERT INTO OBJECT_TYPES VALUES ('U', 'TABLE')
INSERT INTO OBJECT_TYPES VALUES ('V', 'VIEW')
INSERT INTO OBJECT_TYPES VALUES ('P', 'PROCEDURE')

CREATE VIEW ALL_OBJECTS (
	OWNER,
	OBJECT_NAME,
	OBJECT_TYPE, 
	CREATED, 
	STATUS
	) AS
	SELECT Z.T_UNAME, Z.T_OBJNAME, O.TYPE, 
		T.CREATETIME, 'VALID'
 	FROM ZNUOBJ Z, OBJECT_TYPES O, TABLES T
 	WHERE Z.N_OBJNAME = T.TABLENAME AND
  		Z.T_OBJTYPE = O.ID

CREATE VIEW ALL_TABLES (
	OWNER, 
	TABLE_NAME
	) AS
	SELECT T_UNAME, T_OBJNAME
  	FROM ZNUOBJ  
 	WHERE T_OBJTYPE = 'U'
 

CREATE VIEW ALL_VIEWS (
	OWNER, 
	VIEW_NAME
	) AS
	SELECT  T_UNAME, T_OBJNAME
 	 FROM ZNUOBJ
 	 WHERE T_OBJTYPE = 'V'

CREATE VIEW ALL_TAB_COLUMNS (
	OWNER, 
	USERNAME, 
	TABLE_NAME, 
	COLUMN_NAME, 
	DATA_TYPE, 
	DATA_LENGTH,
	COLUMN_ID, 
	DATA_PRECISION, 
	DATA_SCALE, 
	NULLABLE, 
	DATA_DEFAULT
	) AS
	SELECT ZO.T_UNAME, USER, ZO.T_OBJNAME , 
       	CO.COLNAME, DTMAP.ONAME,  
      	(22 * DTMAP.L_FACTOR + CO.COLSIZE * DTMAP.INV_FACTOR), 
      	CO.COLNUMBER,
       	CO.COLSIZE,
     	 CO.SCALE ,
       	"" , 
       	-1 
  	FROM ZNUOBJ ZO, COLUMNS CO, NS2ODTMAP DTMAP
 	WHERE 
       		ZO.N_OBJNAME = CO.TABLENAME AND
       		CO.DATATYPE  = DTMAP.NSTYPE
      
     

CREATE VIEW ACCESSIBLE_COLUMNS AS
SELECT * FROM ALL_TAB_COLUMNS

CREATE VIEW USER_USERS (
	USERNAME, 
	USER_ID, 
	CONNECT_PRIV, 
	RESOURCE_PRIV, 
	DBA_PRIV) AS
	SELECT NAME, UID, 1, 1, 0
  	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	date,
   LONG_VALUE	int
)

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


