/* =============================================================================== */ /* Generated by KeepTool Database Reverse Engineer (c) KeepTool GmbH 2006 */ /* SCHEMA: PDW_EXPRESS_STAGE@PDW.IPK-GATERSLEBEN.DE */ /* DATE : 11.10.2007 */ /* =============================================================================== */ /* xxx please change connect string */ /* CONNECT SYS/@PDW.IPK-GATERSLEBEN.DE */ /* =============================================================================== */ /* Create User */ /* =============================================================================== */ -- DROP USER PDW_EXPRESS_STAGE CASCADE; CREATE USER PDW_EXPRESS_STAGE IDENTIFIED BY /* xxx please change password */ / ALTER USER PDW_EXPRESS_STAGE QUOTA UNLIMITED ON PDW_EXPRESS_STAGE / /* =============================================================================== */ /* Roles */ /* =============================================================================== */ CREATE ROLE CKR_REPOS_MANAGER / /* =============================================================================== */ /* Grant System and Role Privileges */ /* =============================================================================== */ GRANT CKR_REPOS_MANAGER TO PDW_EXPRESS_STAGE / GRANT CONNECT TO PDW_EXPRESS_STAGE / GRANT CREATE ANY CONTEXT TO PDW_EXPRESS_STAGE / GRANT CREATE CLUSTER TO PDW_EXPRESS_STAGE / GRANT CREATE DATABASE LINK TO PDW_EXPRESS_STAGE / GRANT CREATE DIMENSION TO PDW_EXPRESS_STAGE / GRANT CREATE INDEXTYPE TO PDW_EXPRESS_STAGE / GRANT CREATE LIBRARY TO PDW_EXPRESS_STAGE / GRANT CREATE MATERIALIZED VIEW TO PDW_EXPRESS_STAGE / GRANT CREATE OPERATOR TO PDW_EXPRESS_STAGE / GRANT CREATE PROCEDURE TO PDW_EXPRESS_STAGE / GRANT CREATE SEQUENCE TO PDW_EXPRESS_STAGE / GRANT CREATE SYNONYM TO PDW_EXPRESS_STAGE / GRANT CREATE TABLE TO PDW_EXPRESS_STAGE / GRANT CREATE TRIGGER TO PDW_EXPRESS_STAGE / GRANT CREATE TYPE TO PDW_EXPRESS_STAGE / GRANT CREATE VIEW TO PDW_EXPRESS_STAGE / /* =============================================================================== */ /* Consumer Group Privileges */ /* =============================================================================== */ /* =============================================================================== */ /* Grants received from other users */ /* =============================================================================== */ /* xxx please change connect string */ /* CONNECT CTXSYS/@PDW.IPK-GATERSLEBEN.DE */ GRANT EXECUTE ON CTXSYS.CTX_DDL TO PDW_EXPRESS_STAGE / GRANT EXECUTE ON CTXSYS.CTX_DOC TO PDW_EXPRESS_STAGE / /* xxx please change connect string */ /* CONNECT PDW_DATA_TEST/@PDW.IPK-GATERSLEBEN.DE */ GRANT REFERENCES ON PDW_DATA_TEST.T210_SEQUENCE TO PDW_EXPRESS_STAGE / GRANT SELECT ON PDW_DATA_TEST.T210_SEQUENCE TO PDW_EXPRESS_STAGE WITH GRANT OPTION / GRANT SELECT ON PDW_DATA_TEST.T211_SEQ_SCL TO PDW_EXPRESS_STAGE / GRANT SELECT ON PDW_DATA_TEST.T212_SEQ_CLASS TO PDW_EXPRESS_STAGE / GRANT SELECT ON PDW_DATA_TEST.T219_SEQ_CULT_ORG TO PDW_EXPRESS_STAGE / GRANT REFERENCES ON PDW_DATA_TEST.T222_SEQ2SEQ TO PDW_EXPRESS_STAGE / GRANT SELECT ON PDW_DATA_TEST.T222_SEQ2SEQ TO PDW_EXPRESS_STAGE WITH GRANT OPTION / GRANT SELECT ON PDW_DATA_TEST.T223_RELATION_TYPE TO PDW_EXPRESS_STAGE / GRANT SELECT ON PDW_DATA_TEST.T252_ORGANISM TO PDW_EXPRESS_STAGE / GRANT SELECT ON PDW_DATA_TEST.T270_FILE TO PDW_EXPRESS_STAGE / GRANT SELECT ON PDW_DATA_TEST.T290_SIMILARITY_BLAST TO PDW_EXPRESS_STAGE / /* xxx please change connect string */ /* CONNECT REPOS_MANAGER/@PDW.IPK-GATERSLEBEN.DE */ GRANT EXECUTE ON REPOS_MANAGER.CKMIGFUN TO PDW_EXPRESS_STAGE / GRANT SELECT ON REPOS_MANAGER."I$RM_ELEMENT_TYPES" TO PDW_EXPRESS_STAGE / GRANT EXECUTE ON REPOS_MANAGER.JR_SYSTEM_UTIL TO PDW_EXPRESS_STAGE / GRANT EXECUTE ON REPOS_MANAGER.JR_UTIL TO PDW_EXPRESS_STAGE / GRANT EXECUTE ON REPOS_MANAGER.SDD_ACC_PRIV_LIST TO PDW_EXPRESS_STAGE / GRANT SELECT ON REPOS_MANAGER.SDD_DEPENDENCY_TYPES TO PDW_EXPRESS_STAGE / GRANT SELECT ON REPOS_MANAGER.SDD_GLOBAL_NAMES TO PDW_EXPRESS_STAGE / GRANT EXECUTE ON REPOS_MANAGER.SDD_SYS_PRIV_LIST TO PDW_EXPRESS_STAGE / /* xxx please change connect string */ /* CONNECT SYS/@PDW.IPK-GATERSLEBEN.DE */ GRANT EXECUTE ON SYS.DBMS_RLS TO PDW_EXPRESS_STAGE / GRANT EXECUTE ON SYS.DEFAULT_CONSUMER_GROUP TO PDW_EXPRESS_STAGE / GRANT EXECUTE ON SYS.LOW_GROUP TO PDW_EXPRESS_STAGE / /* xxx please change connect string */ /* CONNECT PDW_EXPRESS_STAGE/@PDW.IPK-GATERSLEBEN.DE */ /* =============================================================================== */ /* Synonyms */ /* =============================================================================== */ /* =============================================================================== */ /* Tables */ /* =============================================================================== */ CREATE TABLE EDITING_SESSION_T ( ESE_ID NUMBER (38,0) NOT NULL, ESE_IMPORTDATE DATE DEFAULT sysdate NOT NULL, ESE_SUCCESSFUL CHAR (1) DEFAULT 'n' NOT NULL, ESE_DESC VARCHAR2 (4000), ESE_SOURCE_DESC VARCHAR2 (4000), ESE_USERNAME VARCHAR2 (400), ESE_RPL_ID_EXECUTION NUMBER (38,0) ) NOCACHE NOPARALLEL LOGGING / CREATE TABLE EXTERNDB_T ( EDB_ID NUMBER (38,0) NOT NULL, EDB_NAME VARCHAR2 (400) NOT NULL, EDB_URL VARCHAR2 (4000) NOT NULL, EDB_DESC VARCHAR2 (4000), EDB_SHORTNAME VARCHAR2 (40) ) NOCACHE NOPARALLEL LOGGING / CREATE TABLE OBJECTS_T ( OBJ_ID NUMBER (38,0) NOT NULL, OBJ_OJT_ID NUMBER (38,0) NOT NULL, OBJ_ESE_ID NUMBER (38,0) NOT NULL ) NOCACHE NOPARALLEL LOGGING / CREATE TABLE OBJECT_TYPES_T ( OJT_ID NUMBER (38,0) NOT NULL, OJT_TCT_ID NUMBER (38,0) NOT NULL, OJT_NAME VARCHAR2 (40) NOT NULL, OJT_ABBREVIATION VARCHAR2 (3) NOT NULL, OJT_DESC VARCHAR2 (4000) ) NOCACHE NOPARALLEL LOGGING / CREATE TABLE OBJ_FILES_T ( OFI_ID NUMBER (38,0) NOT NULL, OFI_OBJ_ID_FIL NUMBER (38,0) NOT NULL, OFI_FILE BFILE NOT NULL ) NOCACHE NOPARALLEL LOGGING / CREATE TABLE OBJ_PROPERTIES_T ( OPR_ID NUMBER (38,0) NOT NULL, OPR_TRM_ID NUMBER (38,0) NOT NULL, OPR_OBJ_ID NUMBER (38,0) NOT NULL, OPR_OPR_ID NUMBER (38,0), OPR_ESE_ID NUMBER (38,0) NOT NULL, OPR_ESE_ID_OBSOLETE NUMBER (38,0) ) NOCACHE NOPARALLEL LOGGING / CREATE TABLE PERMISSIONS_T ( OBJ_OBJ_ID NUMBER (38,0) NOT NULL, URS_URS_ID NUMBER (38,0) NOT NULL, ESE_ESE_ID NUMBER (38,0) NOT NULL ) NOCACHE NOPARALLEL LOGGING / CREATE TABLE REPLACEMENTS_T ( RPL_ID NUMBER (38,0) NOT NULL, RPL_AUTHOR VARCHAR2 (400) NOT NULL, RPL_DESC VARCHAR2 (4000), RPL_CREATIONDATE DATE DEFAULT sysdate NOT NULL ) NOCACHE NOPARALLEL LOGGING / CREATE TABLE RESULT_DATA_T ( RSD_ID NUMBER (38,0) NOT NULL, RSD_OBJ_ID_RST NUMBER (38,0) NOT NULL, RSD_OBJ_ID NUMBER (38,0) NOT NULL, RSD_VALUE NUMBER (38,18) NOT NULL ) NOCACHE NOPARALLEL LOGGING / CREATE TABLE TERMS_T ( TRM_ID NUMBER (38,0) NOT NULL, TRM_EDB_ID NUMBER (38,0) DEFAULT 0 NOT NULL, TRM_TCT_ID NUMBER (38,0) NOT NULL, TRM_VALUE VARCHAR2 (4000) NOT NULL, TRM_DESC VARCHAR2 (4000), TRM_EDBACC VARCHAR2 (100) DEFAULT 'unreferenced' NOT NULL, TRM_IMPORTDATE DATE DEFAULT sysdate NOT NULL, TRM_ESE_ID NUMBER (38,0) NOT NULL ) NOCACHE NOPARALLEL LOGGING / CREATE TABLE TRL_TYPE_T ( RTY_ID NUMBER (38,0) NOT NULL, RTY_NAME VARCHAR2 (40) NOT NULL, RTY_DESC VARCHAR2 (4000), RTY_SHORT VARCHAR2 (3) NOT NULL ) NOCACHE NOPARALLEL LOGGING / CREATE TABLE TRM_CATEGORIES_T ( TCT_ID NUMBER (38,0) NOT NULL, TCT_TCT_ID NUMBER (38,0) DEFAULT 0, TCT_NAME VARCHAR2 (400) NOT NULL, TCT_DESC VARCHAR2 (4000) NOT NULL, TCT_MANY NUMBER (1,0) DEFAULT 0 NOT NULL, TCT_NOTNULL NUMBER (1,0) DEFAULT 0 NOT NULL ) NOCACHE NOPARALLEL LOGGING / CREATE TABLE TRM_REPLACE_T ( TRL_ID NUMBER (38,0) NOT NULL, TRL_TRM_ID_SOURCE NUMBER (38,0) NOT NULL, TRL_DESC VARCHAR2 (4000), TRL_AUTHOR VARCHAR2 (400), TRL_RPL_ID NUMBER (38,0) NOT NULL, TRL_RTY_ID NUMBER (38,0) NOT NULL ) NOCACHE NOPARALLEL LOGGING / CREATE TABLE TRM_TRL_T ( TRM_TRM_ID NUMBER (38,0) NOT NULL, TRL_TRL_ID NUMBER (38,0) NOT NULL, TRM_ORDER NUMBER (2,0) DEFAULT 0 ) NOCACHE NOPARALLEL LOGGING / CREATE TABLE USERS_T ( URS_ID NUMBER (38,0) NOT NULL, URS_NAME VARCHAR2 (40) NOT NULL ) NOCACHE NOPARALLEL LOGGING / /* =============================================================================== */ /* Sequences */ /* =============================================================================== */ CREATE SEQUENCE EDITING_SESSION_SEQ START WITH 0 INCREMENT BY 1 MAXVALUE 1E27 MINVALUE 0 NOCACHE NOCYCLE NOORDER / CREATE SEQUENCE EXTERNDB_SEQ START WITH 0 INCREMENT BY 1 MAXVALUE 1E27 MINVALUE 0 NOCACHE NOCYCLE NOORDER / CREATE SEQUENCE OBJECTFILE_SEQ START WITH 0 INCREMENT BY 1 MAXVALUE 1E27 MINVALUE 0 NOCACHE NOCYCLE NOORDER / CREATE SEQUENCE OBJECTTYPE_SEQ START WITH 0 INCREMENT BY 1 MAXVALUE 1E27 MINVALUE 0 NOCACHE NOCYCLE NOORDER / CREATE SEQUENCE OBJECT_SEQ START WITH 0 INCREMENT BY 1 MAXVALUE 1E27 MINVALUE 0 NOCACHE NOCYCLE NOORDER / CREATE SEQUENCE OBJ_PROPERTIES_SEQ START WITH 0 INCREMENT BY 1 MAXVALUE 1E27 MINVALUE 0 NOCACHE NOCYCLE NOORDER / CREATE SEQUENCE RESULTDATA_SEQ START WITH 0 INCREMENT BY 1 MAXVALUE 1E27 MINVALUE 0 NOCACHE NOCYCLE NOORDER / CREATE SEQUENCE TERMS_SEQ START WITH 0 INCREMENT BY 1 MAXVALUE 1E27 MINVALUE 0 NOCACHE NOCYCLE NOORDER / CREATE SEQUENCE TRM_CATERGORIES_SEQ START WITH 0 INCREMENT BY 1 MAXVALUE 1E27 MINVALUE 0 NOCACHE NOCYCLE NOORDER / CREATE SEQUENCE TRM_REPLACE_SEQ START WITH 0 INCREMENT BY 1 MAXVALUE 1E27 MINVALUE 0 NOCACHE NOCYCLE NOORDER / CREATE SEQUENCE USER_SEQ START WITH 0 INCREMENT BY 1 MAXVALUE 1E27 MINVALUE 0 NOCACHE NOCYCLE NOORDER / /* =============================================================================== */ /* Views */ /* =============================================================================== */ CREATE OR REPLACE FORCE VIEW ALL_OBJECTS_ADD_V ( OBJ_ID, OJT_NAME, TCT_NAME, TRM_VALUE, EDB_NAME, TRM_EDBACC, OPR_ISOBSOLETE, TRM_ID, TCT_ID, OBJ_ESE_ID, OJT_ID ) AS ( SELECT OBJ_ID ,ojt_name ,TCT_NAME ,TRM_VALUE ,edb_name ,TRM_EDBACC ,nvl2(opr_ese_id_obsolete,1,0) as OPR_ISOBSOLETE , trm_id , tct_id , obj_Ese_id , ojt_id FROM OBJECTS_T ,terms_t , OBJ_PROPERTIES_T , OBJECT_TYPES_T , TRM_CATEGORIES_T ,EXTERNDB_T WHERE obj_id=opr_obj_id AND trm_id = opr_trm_id AND ojt_id = obj_ojt_id AND tct_id = trm_tct_id AND edb_id(+) = trm_edb_id ) UNION ALL ( SELECT obj_id , 'RESULTSETS' AS OJT_NAME , 'VALUE_COUNT' AS TCT_NAME , COUNT (*)||'' AS TRM_VALUE , NULL AS EDB_NAME , NULL AS TRM_EDBACC , 0 AS OPR_ISOBSOLETE , NULL AS TRM_ID , NULL AS TCT_ID , obj_Ese_id , 5 ojt_id FROM OBJECTS_T left outer JOIN RESULT_DATA_T ON obj_id = rsd_obj_id_rst WHERE obj_ojt_id = 5 -- ResultSet GROUP BY obj_id ,obj_Ese_id ) / CREATE OR REPLACE FORCE VIEW ALL_OBJECTS_V ( OBJ_ID, OJT_NAME, TCT_NAME, TRM_VALUE, EDB_NAME, TRM_EDBACC, OPR_ISOBSOLETE, TRM_ID, TCT_ID, OBJ_ESE_ID, OJT_ID, OPR_ESE_ID_OBSOLETE ) AS SELECT OBJ_ID ,ojt_name ,TCT_NAME ,TRM_VALUE , edb_name ,TRM_EDBACC ,nvl2(OPR_ESE_ID_OBSOLETE,1,0) as OPR_ISOBSOLETE , trm_id , tct_id , obj_Ese_id , ojt_id , OPR_ESE_ID_OBSOLETE FROM OBJECTS_T ,terms_t , OBJ_PROPERTIES_T , OBJECT_TYPES_T , TRM_CATEGORIES_T ,EXTERNDB_T WHERE obj_id=opr_obj_id AND trm_id = opr_trm_id AND ojt_id = obj_ojt_id AND tct_id = trm_tct_id AND edb_id(+) = trm_edb_id / CREATE OR REPLACE FORCE VIEW ALL_TERMS_V ( TCT_NAME, TRM_VALUE, EDB_NAME, TRM_EDBACC, TRM_ID, TCT_ID, EDB_ID, TRM_ESE_ID ) AS SELECT --ojt_name TCT_NAME ,TRM_VALUE , edb_name ,TRM_EDBACC , trm_id , tct_id -- , ojt_id , edb_id , trm_ese_id FROM terms_t join TRM_CATEGORIES_T on tct_id = trm_tct_id --join OBJECT_TYPES_T on ojt_id = ojt_tct_id left outer join EXTERNDB_T on edb_id = trm_edb_id / CREATE OR REPLACE FORCE VIEW ERR_FOREIGNKEYS_V ( TRM_ESE_ID, TCT_NAME, FKVALUE ) AS select trm_ese_id, tct_name, fkValue from ( SELECT trm_ese_id ,t1.tct_name --, t1.tct_id --, substr(t1.tct_name,5,3) AS abbr ,ojt_id , to_number(trm_value) as fkValue FROM TRM_CATEGORIES_T t1 JOIN TRM_CATEGORIES_T t2 ON t1.tct_tct_id = t2.tct_id JOIN OBJECT_TYPES_T ON ojt_abbreviation = substr(t1.tct_name,5,3) JOIN terms_t ON trm_tct_id = t1.tct_id WHERE t2.tct_name='FOREIGNKEYS' ) setFK left outer join ( select obj_ojt_id, obj_id from OBJECTS_T ) existsFK on setFK.ojt_id = existsFK.obj_ojt_id and setFK.fkValue = existsFK.obj_id where existsFK.obj_id is null / CREATE OR REPLACE FORCE VIEW ERR_NOTNULL_V ( OJT_NAME, OJT_ID, OBJ_ID, TCT_ID, TCT_NAME, OBJ_ESE_ID ) AS with notNullTct as ( select tct_id, tct_name, regexp_substr(substr(thepath,3),'[[:alnum:]]+') as mainCharacteristicsID,tct_notnull from ( select level thelevel,tct_id, tct_name, sys_connect_by_path(tct_id,'/') thepath,tct_notnull from TRM_CATEGORIES_T start with tct_id =0 connect by prior tct_id = tct_tct_id and tct_id <> 62 and tct_id <> 66 ) ) select "OJT_NAME","OJT_ID","OBJ_ID","TCT_ID","TCT_NAME","OBJ_ESE_ID" from ( select ojt_name, ojt_id, obj_id, tct_id , tct_name, obj_ese_id from notNullTct join OBJECT_TYPES_T on MAINCHARACTERISTICSID = ojt_tct_id join objects_t on ojt_id = obj_ojt_id where tct_notnull = 1 minus select ojt_name, obj_ojt_id, obj_id, trm_tct_id , tct_name, obj_ese_id from objects_t join OBJ_PROPERTIES_T on obj_id = opr_obj_id join OBJECT_TYPES_T on obj_ojt_id = ojt_id join terms_t on trm_id = opr_trm_id join TRM_CATEGORIES_T on tct_id = trm_tct_id ) / CREATE OR REPLACE FORCE VIEW ERR_NOTUNIQUEIDENTIFIERS_V ( TRM_ID, TRM_EDB_ID, TRM_TCT_ID, TRM_VALUE, TRM_DESC, TRM_EDBACC, TRM_IMPORTDATE, TRM_ESE_ID ) AS SELECT "TRM_ID","TRM_EDB_ID","TRM_TCT_ID","TRM_VALUE","TRM_DESC","TRM_EDBACC","TRM_IMPORTDATE","TRM_ESE_ID" FROM terms_t trm WHERE trm_tct_id = (select tct_id from TRM_CATEGORIES_T where tct_name='OBJ_Identifier' ) and trm.rowid NOT IN ( SELECT MIN (rowid) FROM terms_t trmIn WHERE trm_tct_id = (select tct_id from TRM_CATEGORIES_T where tct_name='OBJ_Identifier' ) group by trm_value) / CREATE OR REPLACE FORCE VIEW SEQUENCE_MAPPING_V ( BIOSEQUENCE, MATCHSEQUENCE, TREE_LEVEL ) AS select A1.t210_seq_id biosequence, A2.t210_seq_id matchsequence, tree_level from ( select regexp_substr(sys_connect_by_path(t222_t210_id_2, '/'), '\d+') tree_root, t222_t210_id_1 tree_node, level tree_level from pdw_data_test.t222_seq2seq where connect_by_iscycle =0 start with t222_t210_id_2 in (select t210_id from pdw_data_test.t210_sequence join terms_t on t210_seq_id = trm_value) connect by nocycle prior t222_t210_id_1 = t222_t210_id_2 ) B join pdw_data_test.T210_SEQUENCE A1 on A1.t210_id = B.tree_root join pdw_data_test.T210_SEQUENCE A2 on A2.t210_id = B.tree_node / CREATE OR REPLACE FORCE VIEW USR_PUBLIC_ARRAYS_V ( ARY_ID, ARY_IDENTIFIER, ARY_NAME, ARY_DESCRIPTION, ARRAYTECHNOLOGY_ID, ARRAYMANUFACTURER_ID ) AS SELECT OBJ_ID AS ARY_ID ,MAX(decode(TCT_NAME,'OBJ_Identifier',EDB_NAME||':'|| TRM_VALUE,NULL)) AS ARY_Identifier ,MAX(decode(TCT_NAME,'OBJ_Name',TRM_VALUE,NULL)) AS ARY_Name ,MAX(decode(TCT_NAME,'OBJ_Description',TRM_VALUE,NULL)) AS ARY_Description ,MAX(decode(TCT_NAME,'ArrayTechnology',TRM_ID,NULL)) AS ArrayTechnology_ID ,MAX(decode(TCT_NAME,'ArrayManufacturer',TRM_ID,NULL)) AS ArrayManufacturer_ID FROM PDW_EXPRESS_STAGE.ALL_OBJECTS_V JOIN PERMISSIONS_T on obj_obj_id=obj_id JOIN USERS_T on urs_id=urs_urs_id WHERE OJT_NAME = 'ARRAYS' AND OPR_ESE_ID_OBSOLETE IS NULL AND urs_name='public' GROUP BY obj_id / CREATE OR REPLACE FORCE VIEW USR_PUBLIC_BIOMATERIALS_V ( MAT_ID, MAT_IDENTIFIER, MAT_NAME, MAT_DESCRIPTION ) AS SELECT OBJ_ID AS MAT_ID ,MAX(decode(TCT_NAME,'OBJ_Identifier',EDB_NAME||':'|| TRM_VALUE,NULL)) AS MAT_Identifier ,MAX(decode(TCT_NAME,'OBJ_Name',TRM_VALUE,NULL)) AS MAT_Name ,MAX(decode(TCT_NAME,'OBJ_Description',TRM_VALUE,NULL)) AS MAT_Description FROM PDW_EXPRESS_STAGE.ALL_OBJECTS_V JOIN PERMISSIONS_T on obj_obj_id=obj_id JOIN USERS_T on urs_id=urs_urs_id WHERE OJT_NAME = 'BIOMATERIALS' AND OPR_ESE_ID_OBSOLETE IS NULL AND urs_name='public' GROUP BY obj_id / CREATE OR REPLACE FORCE VIEW USR_PUBLIC_BIOSEQUENCES_V ( SEQ_ID, SEQ_IDENTIFIER, SEQ_NAME, SEQ_DESCRIPTION, STRANDTYPE_ID, CONTROLTYPE_ID, BIOSEQUENCETYPE_ID, SEQ_MART_ID, SEQ_MART_ORIGIN ) AS SELECT OBJ_ID AS SEQ_ID ,MAX(decode(TCT_NAME,'OBJ_Identifier',EDB_NAME||':'|| TRM_VALUE,NULL)) AS SEQ_Identifier ,MAX(decode(TCT_NAME,'OBJ_Name',TRM_VALUE,NULL)) AS SEQ_Name ,MAX(decode(TCT_NAME,'OBJ_Description',TRM_VALUE,NULL)) AS SEQ_Description ,MAX(decode(TCT_NAME,'StrandType',TRM_ID,NULL)) AS StrandType_ID ,MAX(decode(TCT_NAME,'ControlType',TRM_ID,NULL)) AS ControlType_ID ,MAX(decode(TCT_NAME,'BioSequenceType',TRM_ID,NULL)) AS BioSequenceType_ID ,MAX(decode(TCT_NAME,'OBJ_Identifier',TRM_value, NULL)) AS SEQ_MART_ID ,MAX(decode(TCT_NAME,'OBJ_IDENTIFIER',EDB_NAME, NULL)) AS SEQ_MART_ORIGIN FROM PDW_EXPRESS_STAGE.ALL_OBJECTS_V JOIN PERMISSIONS_T on obj_obj_id=obj_id JOIN USERS_T on urs_id=urs_urs_id WHERE OJT_NAME = 'BIOSEQUENCES' AND OPR_ESE_ID_OBSOLETE IS NULL AND urs_name='public' GROUP BY obj_id / CREATE OR REPLACE FORCE VIEW USR_PUBLIC_EXPERIMENTS_V ( EXP_ID, EXP_IDENTIFIER, EXP_NAME, EXP_DESCRIPTION, EXPERIMENTER_ID, EXPERIMENTDESIGNTYPE_ID, DATASOURCE_ID, QUALITYCONTROLDESCRIPTION_ID, LABORATORY_ID ) AS SELECT OBJ_ID AS EXP_ID ,MAX(decode(TCT_NAME,'OBJ_Identifier',EDB_NAME||':'|| TRM_VALUE,NULL)) AS EXP_Identifier ,MAX(decode(TCT_NAME,'OBJ_Name',TRM_VALUE,NULL)) AS EXP_Name ,MAX(decode(TCT_NAME,'OBJ_Description',TRM_VALUE,NULL)) AS EXP_Description ,MAX(decode(TCT_NAME,'Experimenter',TRM_ID,NULL)) AS Experimenter_ID ,MAX(decode(TCT_NAME,'ExperimentDesignType',TRM_ID,NULL)) AS ExperimentDesignType_ID ,MAX(decode(TCT_NAME,'DataSource',TRM_ID,NULL)) AS DataSource_ID ,MAX(decode(TCT_NAME,'QualityControlDescription',TRM_ID,NULL)) AS QualityControlDescription_ID ,MAX(decode(TCT_NAME,'Laboratory',TRM_ID,NULL)) AS Laboratory_ID FROM PDW_EXPRESS_STAGE.ALL_OBJECTS_V JOIN PERMISSIONS_T on obj_obj_id=obj_id JOIN USERS_T on urs_id=urs_urs_id WHERE OJT_NAME = 'EXPERIMENTS' AND OPR_ESE_ID_OBSOLETE IS NULL AND urs_name='public' GROUP BY obj_id / CREATE OR REPLACE FORCE VIEW USR_PUBLIC_EXTERNDB_V ( EDB_ID, EDB_NAME, EDB_URL, EDB_DESC, EDB_SHORTNAME ) AS SELECT EDB_ID , EDB_NAME , EDB_URL , EDB_DESC , EDB_SHORTNAME FROM PDW_EXPRESS_STAGE.EXTERNDB_T join terms_t on edb_id=trm_edb_id join obj_properties_t on opr_trm_id=trm_id join objects_t on obj_id=opr_obj_id JOIN PERMISSIONS_T on obj_obj_id=obj_id JOIN USERS_T on urs_id=urs_urs_id WHERE URS_NAME = 'public' / CREATE OR REPLACE FORCE VIEW USR_PUBLIC_FILES_V ( FIL_ID, FIL_IDENTIFIER, FIL_NAME, FIL_DESCRIPTION, FILENAME_ID ) AS SELECT OBJ_ID AS FIL_ID ,MAX(decode(TCT_NAME,'OBJ_Identifier',EDB_NAME||':'|| TRM_VALUE,NULL)) AS FIL_Identifier ,MAX(decode(TCT_NAME,'OBJ_Name',TRM_VALUE,NULL)) AS FIL_Name ,MAX(decode(TCT_NAME,'OBJ_Description',TRM_VALUE,NULL)) AS FIL_Description ,MAX(decode(TCT_NAME,'FileName',TRM_ID,NULL)) AS FileName_ID FROM PDW_EXPRESS_STAGE.ALL_OBJECTS_V JOIN PERMISSIONS_T on obj_obj_id=obj_id JOIN USERS_T on urs_id=urs_urs_id WHERE OJT_NAME = 'FILES' AND OPR_ESE_ID_OBSOLETE IS NULL AND urs_name='public' GROUP BY obj_id / CREATE OR REPLACE FORCE VIEW USR_PUBLIC_HYBRIDISATIONS_V ( HYB_ID, HYB_IDENTIFIER, HYB_NAME, HYB_DESCRIPTION, HYB_EXP_ID, HYB_ARY_ID, REPLICATEDESCRIPTIONTYPE_ID, REPLICATEINDEX ) AS SELECT OBJ_ID AS HYB_ID ,MAX(decode(TCT_NAME,'OBJ_Identifier',EDB_NAME||':'|| TRM_VALUE,NULL)) AS HYB_Identifier ,MAX(decode(TCT_NAME,'OBJ_Name',TRM_VALUE,NULL)) AS HYB_Name ,MAX(decode(TCT_NAME,'OBJ_Description',TRM_VALUE,NULL)) AS HYB_Description ,MAX(decode(TCT_NAME,'HYB_EXP_ID',to_number(TRM_VALUE),NULL)) AS HYB_EXP_ID ,MAX(decode(TCT_NAME,'HYB_ARY_ID',to_number(TRM_VALUE),NULL)) AS HYB_ARY_ID ,MAX(decode(TCT_NAME,'ReplicateDescriptionType',TRM_ID,NULL)) AS ReplicateDescriptionType_ID ,MAX(decode(TCT_NAME,'ReplicateIndex',TRM_VALUE,NULL)) AS ReplicateIndex FROM PDW_EXPRESS_STAGE.ALL_OBJECTS_V JOIN PERMISSIONS_T on obj_obj_id=obj_id JOIN USERS_T on urs_id=urs_urs_id WHERE OJT_NAME = 'HYBRIDISATIONS' AND OPR_ESE_ID_OBSOLETE IS NULL AND urs_name='public' GROUP BY obj_id / CREATE OR REPLACE FORCE VIEW USR_PUBLIC_MAT_TRM_V ( TRM_TRM_ID, MAT_MAT_ID ) AS SELECT opr_trm_id as trm_trm_id , opr_obj_id as mat_mat_id FROM PDW_EXPRESS_STAGE.OBJ_PROPERTIES_T JOIN PDW_EXPRESS_STAGE.terms_t ON trm_id=opr_trm_id JOIN PDW_EXPRESS_STAGE.TRM_CATEGORIES_T ON tct_id = trm_tct_id JOIN PERMISSIONS_T on obj_obj_id=opr_obj_id JOIN USERS_T on urs_id=urs_urs_id WHERE OPR_ese_id_OBSOLETE is null AND urs_name='public' AND tct_id IN ( SELECT tct_id FROM PDW_EXPRESS_STAGE.TRM_CATEGORIES_T WHERE LEVEL >1 CONNECT BY PRIOR tct_id=tct_tct_id START WITH tct_id = ( SELECT ojt_tct_id FROM PDW_EXPRESS_STAGE.OBJECT_TYPES_T WHERE ojt_name='BIOMATERIALS' ) ) / CREATE OR REPLACE FORCE VIEW USR_PUBLIC_RESULTSETS_V ( RST_ID, RST_IDENTIFIER, RST_NAME, RST_DESCRIPTION, RST_FIL_ID, RST_MAT_ID, RST_HYB_ID, SCALE_ID, RESULTSETTYPE_ID, LABELCOMPOUND_ID, NORMALIZATIONDESCTYPE_ID ) AS SELECT OBJ_ID AS RST_ID ,MAX(decode(TCT_NAME,'OBJ_Identifier',EDB_NAME||':'|| TRM_VALUE,NULL)) AS RST_Identifier ,MAX(decode(TCT_NAME,'OBJ_Name',TRM_VALUE,NULL)) AS RST_Name ,MAX(decode(TCT_NAME,'OBJ_Description',TRM_VALUE,NULL)) AS RST_Description ,MAX(decode(TCT_NAME,'RST_FIL_ID',to_number(TRM_VALUE),NULL)) AS RST_FIL_ID ,MAX(decode(TCT_NAME,'RST_MAT_ID',to_number(TRM_VALUE),NULL)) AS RST_MAT_ID ,MAX(decode(TCT_NAME,'RST_HYB_ID',to_number(TRM_VALUE),NULL)) AS RST_HYB_ID ,MAX(decode(TCT_NAME,'Scale',TRM_ID,NULL)) AS Scale_ID ,MAX(decode(TCT_NAME,'ResultSetType',TRM_ID,NULL)) AS ResultSetType_ID ,MAX(decode(TCT_NAME,'LabelCompound',TRM_ID,NULL)) AS LabelCompound_ID ,MAX(decode(TCT_NAME,'NormalizationDescriptionType',TRM_ID,NULL)) AS NormalizationDescType_ID FROM PDW_EXPRESS_STAGE.ALL_OBJECTS_V JOIN PERMISSIONS_T on obj_obj_id=obj_id JOIN USERS_T on urs_id=urs_urs_id WHERE OJT_NAME = 'RESULTSETS' AND OPR_ESE_ID_OBSOLETE IS NULL AND urs_name='public' GROUP BY obj_id / CREATE OR REPLACE FORCE VIEW USR_PUBLIC_RST_RST_V ( RST_RST_ID_CHILD, RST_RST_ID_PARENT ) AS SELECT opr_obj_id as rst_rst_id_child ,trm_value as rst_rst_id_PARENT FROM PDW_EXPRESS_STAGE.OBJ_PROPERTIES_T JOIN PDW_EXPRESS_STAGE.terms_t ON trm_id=opr_trm_id JOIN PDW_EXPRESS_STAGE.TRM_CATEGORIES_T ON tct_id = trm_tct_id JOIN PERMISSIONS_T on obj_obj_id=opr_obj_id JOIN USERS_T on urs_id=urs_urs_id WHERE OPR_ese_id_OBSOLETE is null AND urs_name='public' AND tct_id IN ( SELECT tct_id FROM PDW_EXPRESS_STAGE.TRM_CATEGORIES_T WHERE tct_name='RST_RST_ID_PARENT' ) / CREATE OR REPLACE FORCE VIEW USR_PUBLIC_TERMS_V ( TRM_ID, TRM_EDB_ID, TRM_TCT_ID, TRM_VALUE, TRM_DESC, TRM_EDBACC, TCT_NAME, EDB_NAME ) AS SELECT TRM_ID , TRM_EDB_ID , TRM_TCT_ID , TRM_VALUE , TRM_DESC , TRM_EDBACC ,tct_name ,edb_name FROM PDW_EXPRESS_STAGE.terms_t join PDW_EXPRESS_STAGE.TRM_CATEGORIES_T on tct_id =trm_tct_id left outer join PDW_EXPRESS_STAGE.EXTERNDB_T on edb_id = trm_edb_id join obj_properties_T on trm_id=opr_trm_id JOIN PERMISSIONS_T on obj_obj_id=opr_obj_id JOIN USERS_T on urs_id=urs_urs_id where -- only Term wich are not obsolete exists ( select 1 from PDW_EXPRESS_STAGE.OBJ_PROPERTIES_T where opr_trm_id = trm_id and opr_ese_id_obsolete is null) -- remove all ForeignKeys and ObjectAttributes and trm_tct_id not in( SELECT tct_id FROM PDW_EXPRESS_STAGE.TRM_CATEGORIES_T WHERE tct_tct_id =( SELECT tct_id FROM PDW_EXPRESS_STAGE.TRM_CATEGORIES_T WHERE tct_name='FOREIGNKEYS' ) UNION ALL ( SELECT tct_id FROM PDW_EXPRESS_STAGE.TRM_CATEGORIES_T WHERE tct_tct_id =( SELECT tct_id FROM PDW_EXPRESS_STAGE.TRM_CATEGORIES_T WHERE tct_name='OBJECT_ATTR' ) ) ) AND urs_name='public' / CREATE OR REPLACE FORCE VIEW USR_PUBLIC_TRM_CATEGORIES_V ( TCT_ID, TCT_TCT_ID, TCT_NAME, TCT_DESC ) AS SELECT TCT_ID , TCT_TCT_ID , TCT_NAME , TCT_DESC FROM PDW_EXPRESS_STAGE.TRM_CATEGORIES_T where tct_id not in( SELECT tct_id FROM PDW_EXPRESS_STAGE.TRM_CATEGORIES_T WHERE tct_tct_id =( SELECT tct_id FROM PDW_EXPRESS_STAGE.TRM_CATEGORIES_T WHERE tct_name='FOREIGNKEYS' ) UNION ALL ( SELECT tct_id FROM PDW_EXPRESS_STAGE.TRM_CATEGORIES_T WHERE tct_tct_id =( SELECT tct_id FROM PDW_EXPRESS_STAGE.TRM_CATEGORIES_T WHERE tct_name='OBJECT_ATTR' ) ) ) / /* =============================================================================== */ /* Comments */ /* =============================================================================== */ COMMENT ON COLUMN TRM_REPLACE_T.TRL_AUTHOR IS 'deprecated' / COMMENT ON COLUMN TRM_REPLACE_T.TRL_DESC IS 'deprecated ' / /* =============================================================================== */ /* Indexes */ /* =============================================================================== */ CREATE INDEX EXTERNDB_T_IDX ON EXTERNDB_T ( EDB_ID , EDB_NAME ) LOGGING / CREATE INDEX IDX_OBJECTS_T2 ON OBJECTS_T ( OBJ_ID , OBJ_OJT_ID , OBJ_ESE_ID ) LOGGING / CREATE INDEX IDX_OBJECT_T1 ON OBJECTS_T ( OBJ_OJT_ID ) LOGGING / CREATE INDEX OBJECTS_T_IDX ON OBJECTS_T ( OBJ_ESE_ID ) LOGGING / CREATE INDEX IDX_OBJECT_TYPES_T1 ON OBJECT_TYPES_T ( OJT_TCT_ID ) LOGGING / CREATE INDEX OBJECT_TYPES_T_IDX ON OBJECT_TYPES_T ( OJT_ID , OJT_NAME ) LOGGING / CREATE INDEX IDX_OBJ_FILE1 ON OBJ_FILES_T ( OFI_OBJ_ID_FIL ) LOGGING / CREATE INDEX IDX_OBJ_PROPERTIES_T2 ON OBJ_PROPERTIES_T ( OPR_OBJ_ID ) LOGGING / CREATE INDEX IDX_OBJ_PROPERTIES_T4 ON OBJ_PROPERTIES_T ( OPR_OPR_ID ) LOGGING / CREATE INDEX IDX_OBJ_PROPERTIES_T6 ON OBJ_PROPERTIES_T ( OPR_ESE_ID ) LOGGING / CREATE INDEX IDX_OBJ_PROPERTIES_T7 ON OBJ_PROPERTIES_T ( OPR_ESE_ID_OBSOLETE ) LOGGING / CREATE INDEX IDX_OBJ_PROPERTIES_T8 ON OBJ_PROPERTIES_T ( OPR_TRM_ID ) LOGGING / CREATE INDEX IDX_PERMISSIONS_T1 ON PERMISSIONS_T ( URS_URS_ID ) LOGGING / CREATE INDEX IDX_PERMISSIONS_T2 ON PERMISSIONS_T ( ESE_ESE_ID ) LOGGING / CREATE INDEX IDX_RESULT_DATA_T1 ON RESULT_DATA_T ( RSD_OBJ_ID ) LOGGING / CREATE INDEX IDX_RESULT_DATA_T2 ON RESULT_DATA_T ( RSD_OBJ_ID_RST ) LOGGING / CREATE INDEX IDX_RESULT_DATA_T3 ON RESULT_DATA_T ( RSD_ID , RSD_OBJ_ID_RST ) LOGGING / CREATE INDEX IDX_RESULT_DATA_T5 ON RESULT_DATA_T ( RSD_OBJ_ID_RST , RSD_OBJ_ID ) LOGGING / CREATE INDEX RESULT_DATA_T_IDX6 ON RESULT_DATA_T ( RSD_OBJ_ID , RSD_OBJ_ID_RST , RSD_VALUE ) LOGGING / CREATE INDEX IDX_TERMS_T1 ON TERMS_T ( TRM_TCT_ID ) LOGGING / CREATE INDEX IDX_TERMS_T2 ON TERMS_T ( TRM_EDB_ID ) LOGGING / CREATE INDEX IDX_TERMS_T3 ON TERMS_T ( TRM_VALUE ) LOGGING / CREATE INDEX IDX_TERMS_T4 ON TERMS_T ( TRM_ESE_ID ) LOGGING / CREATE INDEX TERMS_T_IDX ON TERMS_T ( TRM_ID , TRM_EDB_ID , TRM_TCT_ID , TRM_VALUE , TRM_EDBACC ) LOGGING / CREATE INDEX IDX_TRM_CATEGORIES_T1 ON TRM_CATEGORIES_T ( TCT_TCT_ID ) LOGGING / CREATE INDEX TRM_CATEGORIES_T_IDX ON TRM_CATEGORIES_T ( TCT_ID , TCT_NAME ) LOGGING / CREATE INDEX IDX_TRM_REPLACE_T1 ON TRM_REPLACE_T ( TRL_TRM_ID_SOURCE ) LOGGING / CREATE INDEX IDX_TRM_TRL_T1 ON TRM_TRL_T ( TRL_TRL_ID ) LOGGING / /* =============================================================================== */ /* Primary Key and Unique Constraints */ /* =============================================================================== */ ALTER TABLE EDITING_SESSION_T ADD ( CONSTRAINT EDITING_SESSION_T_UQ UNIQUE ( ESE_RPL_ID_EXECUTION ) NOT DEFERRABLE INITIALLY IMMEDIATE USING INDEX ENABLE ) / ALTER TABLE EDITING_SESSION_T ADD ( PRIMARY KEY ( ESE_ID ) NOT DEFERRABLE INITIALLY IMMEDIATE USING INDEX ENABLE ) / ALTER TABLE EXTERNDB_T ADD ( CONSTRAINT EXTERNDB_T_UQ UNIQUE ( EDB_NAME ) NOT DEFERRABLE INITIALLY IMMEDIATE USING INDEX ENABLE ) / ALTER TABLE EXTERNDB_T ADD ( CONSTRAINT EXTERNDB_T_UQ2 UNIQUE ( EDB_SHORTNAME ) NOT DEFERRABLE INITIALLY IMMEDIATE USING INDEX ENABLE ) / ALTER TABLE EXTERNDB_T ADD ( PRIMARY KEY ( EDB_ID ) NOT DEFERRABLE INITIALLY IMMEDIATE USING INDEX ENABLE ) / ALTER TABLE OBJECTS_T ADD ( PRIMARY KEY ( OBJ_ID ) NOT DEFERRABLE INITIALLY IMMEDIATE USING INDEX ENABLE ) / ALTER TABLE OBJECT_TYPES_T ADD ( CONSTRAINT OBJECT_TYPES_T_UQ UNIQUE ( OJT_NAME ) NOT DEFERRABLE INITIALLY IMMEDIATE USING INDEX ENABLE ) / ALTER TABLE OBJECT_TYPES_T ADD ( CONSTRAINT OBJECT_TYPES_T_UQ2 UNIQUE ( OJT_ABBREVIATION ) NOT DEFERRABLE INITIALLY IMMEDIATE USING INDEX ENABLE ) / ALTER TABLE OBJECT_TYPES_T ADD ( PRIMARY KEY ( OJT_ID ) NOT DEFERRABLE INITIALLY IMMEDIATE USING INDEX ENABLE ) / ALTER TABLE OBJ_FILES_T ADD ( PRIMARY KEY ( OFI_ID ) NOT DEFERRABLE INITIALLY IMMEDIATE USING INDEX ENABLE ) / ALTER TABLE OBJ_PROPERTIES_T ADD ( CONSTRAINT OBJ_PROPERTIES_T_UQ UNIQUE ( OPR_OBJ_ID, OPR_TRM_ID ) NOT DEFERRABLE INITIALLY IMMEDIATE DISABLE NOVALIDATE ) / ALTER TABLE OBJ_PROPERTIES_T ADD ( PRIMARY KEY ( OPR_ID ) NOT DEFERRABLE INITIALLY IMMEDIATE USING INDEX ENABLE ) / ALTER TABLE PERMISSIONS_T ADD ( PRIMARY KEY ( OBJ_OBJ_ID, URS_URS_ID ) NOT DEFERRABLE INITIALLY IMMEDIATE USING INDEX ENABLE ) / ALTER TABLE REPLACEMENTS_T ADD ( PRIMARY KEY ( RPL_ID ) NOT DEFERRABLE INITIALLY IMMEDIATE USING INDEX ENABLE ) / ALTER TABLE RESULT_DATA_T ADD ( CONSTRAINT IDX_RESULT_DATA_T4 UNIQUE ( RSD_ID, RSD_OBJ_ID ) DEFERRABLE INITIALLY DEFERRED USING INDEX ENABLE ) / ALTER TABLE RESULT_DATA_T ADD ( PRIMARY KEY ( RSD_ID ) NOT DEFERRABLE INITIALLY IMMEDIATE USING INDEX ENABLE ) / ALTER TABLE TERMS_T ADD ( PRIMARY KEY ( TRM_ID ) NOT DEFERRABLE INITIALLY IMMEDIATE USING INDEX ENABLE ) / ALTER TABLE TERMS_T ADD ( CONSTRAINT TERMS_T_UQ UNIQUE ( TRM_VALUE, TRM_EDB_ID, TRM_TCT_ID ) NOT DEFERRABLE INITIALLY IMMEDIATE USING INDEX ENABLE ) / ALTER TABLE TRL_TYPE_T ADD ( PRIMARY KEY ( RTY_ID ) NOT DEFERRABLE INITIALLY IMMEDIATE USING INDEX ENABLE ) / ALTER TABLE TRL_TYPE_T ADD ( CONSTRAINT TRL_TYPE_UQ UNIQUE ( RTY_NAME ) NOT DEFERRABLE INITIALLY IMMEDIATE USING INDEX ENABLE ) / ALTER TABLE TRM_CATEGORIES_T ADD ( PRIMARY KEY ( TCT_ID ) NOT DEFERRABLE INITIALLY IMMEDIATE USING INDEX ENABLE ) / ALTER TABLE TRM_CATEGORIES_T ADD ( CONSTRAINT TRM_CATEGORIES_T_UQ UNIQUE ( TCT_NAME ) NOT DEFERRABLE INITIALLY IMMEDIATE USING INDEX ENABLE ) / ALTER TABLE TRM_CATEGORIES_T ADD ( CONSTRAINT UN_TRM_CATEGORIES_1 UNIQUE ( TCT_NAME, TCT_TCT_ID ) NOT DEFERRABLE INITIALLY IMMEDIATE USING INDEX ENABLE ) / ALTER TABLE TRM_REPLACE_T ADD ( PRIMARY KEY ( TRL_ID ) NOT DEFERRABLE INITIALLY IMMEDIATE USING INDEX ENABLE ) / ALTER TABLE TRM_REPLACE_T ADD ( CONSTRAINT TRM_REPLACE_T_UQ UNIQUE ( TRL_TRM_ID_SOURCE, TRL_RPL_ID ) NOT DEFERRABLE INITIALLY IMMEDIATE USING INDEX ENABLE ) / ALTER TABLE TRM_TRL_T ADD ( PRIMARY KEY ( TRM_TRM_ID, TRL_TRL_ID ) NOT DEFERRABLE INITIALLY IMMEDIATE USING INDEX ENABLE ) / ALTER TABLE USERS_T ADD ( PRIMARY KEY ( URS_ID ) NOT DEFERRABLE INITIALLY IMMEDIATE USING INDEX ENABLE ) / /* =============================================================================== */ /* Foreign Key Constraints */ /* =============================================================================== */ ALTER TABLE EDITING_SESSION_T ADD ( CONSTRAINT EDITING_SESSION_T_REPLACEME_FK FOREIGN KEY ( ESE_RPL_ID_EXECUTION ) REFERENCES REPLACEMENTS_T ( RPL_ID ) ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE ENABLE ) / ALTER TABLE OBJECTS_T ADD ( CONSTRAINT OBJECTS_T_EDITING_SESSION_T_FK FOREIGN KEY ( OBJ_ESE_ID ) REFERENCES EDITING_SESSION_T ( ESE_ID ) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED ENABLE ) / ALTER TABLE OBJECTS_T ADD ( FOREIGN KEY ( OBJ_OJT_ID ) REFERENCES OBJECT_TYPES_T ( OJT_ID ) NOT DEFERRABLE INITIALLY IMMEDIATE ENABLE ) / ALTER TABLE OBJECT_TYPES_T ADD ( FOREIGN KEY ( OJT_TCT_ID ) REFERENCES TRM_CATEGORIES_T ( TCT_ID ) NOT DEFERRABLE INITIALLY IMMEDIATE ENABLE ) / ALTER TABLE OBJ_FILES_T ADD ( FOREIGN KEY ( OFI_OBJ_ID_FIL ) REFERENCES OBJECTS_T ( OBJ_ID ) NOT DEFERRABLE INITIALLY IMMEDIATE ENABLE ) / ALTER TABLE OBJ_PROPERTIES_T ADD ( CONSTRAINT OBJ_PROPERTIES_T_EDITING_SE_FK FOREIGN KEY ( OPR_ESE_ID ) REFERENCES EDITING_SESSION_T ( ESE_ID ) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED ENABLE ) / ALTER TABLE OBJ_PROPERTIES_T ADD ( CONSTRAINT OPR_T_ESE_FK_OBSOLOETE FOREIGN KEY ( OPR_ESE_ID_OBSOLETE ) REFERENCES EDITING_SESSION_T ( ESE_ID ) ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE ENABLE ) / ALTER TABLE OBJ_PROPERTIES_T ADD ( FOREIGN KEY ( OPR_TRM_ID ) REFERENCES TERMS_T ( TRM_ID ) ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE ENABLE ) / ALTER TABLE OBJ_PROPERTIES_T ADD ( FOREIGN KEY ( OPR_OBJ_ID ) REFERENCES OBJECTS_T ( OBJ_ID ) ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE ENABLE ) / ALTER TABLE OBJ_PROPERTIES_T ADD ( FOREIGN KEY ( OPR_OPR_ID ) REFERENCES OBJ_PROPERTIES_T ( OPR_ID ) NOT DEFERRABLE INITIALLY IMMEDIATE ENABLE ) / ALTER TABLE PERMISSIONS_T ADD ( FOREIGN KEY ( OBJ_OBJ_ID ) REFERENCES OBJECTS_T ( OBJ_ID ) ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE ENABLE ) / ALTER TABLE PERMISSIONS_T ADD ( FOREIGN KEY ( URS_URS_ID ) REFERENCES USERS_T ( URS_ID ) NOT DEFERRABLE INITIALLY IMMEDIATE ENABLE ) / ALTER TABLE PERMISSIONS_T ADD ( FOREIGN KEY ( ESE_ESE_ID ) REFERENCES EDITING_SESSION_T ( ESE_ID ) ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE ENABLE ) / ALTER TABLE RESULT_DATA_T ADD ( FOREIGN KEY ( RSD_OBJ_ID ) REFERENCES OBJECTS_T ( OBJ_ID ) DEFERRABLE INITIALLY DEFERRED ENABLE ) / ALTER TABLE RESULT_DATA_T ADD ( FOREIGN KEY ( RSD_OBJ_ID_RST ) REFERENCES OBJECTS_T ( OBJ_ID ) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED ENABLE ) / ALTER TABLE TERMS_T ADD ( FOREIGN KEY ( TRM_TCT_ID ) REFERENCES TRM_CATEGORIES_T ( TCT_ID ) ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE ENABLE ) / ALTER TABLE TERMS_T ADD ( FOREIGN KEY ( TRM_EDB_ID ) REFERENCES EXTERNDB_T ( EDB_ID ) NOT DEFERRABLE INITIALLY IMMEDIATE ENABLE ) / ALTER TABLE TERMS_T ADD ( CONSTRAINT TERMS_T_EDITING_SESSION_T_FK FOREIGN KEY ( TRM_ESE_ID ) REFERENCES EDITING_SESSION_T ( ESE_ID ) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED ENABLE ) / ALTER TABLE TRM_CATEGORIES_T ADD ( FOREIGN KEY ( TCT_TCT_ID ) REFERENCES TRM_CATEGORIES_T ( TCT_ID ) ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE ENABLE ) / ALTER TABLE TRM_REPLACE_T ADD ( FOREIGN KEY ( TRL_TRM_ID_SOURCE ) REFERENCES TERMS_T ( TRM_ID ) ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE ENABLE ) / ALTER TABLE TRM_REPLACE_T ADD ( CONSTRAINT TRM_REPLACE_T_REPLACE_T_FK FOREIGN KEY ( TRL_RPL_ID ) REFERENCES REPLACEMENTS_T ( RPL_ID ) ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE ENABLE ) / ALTER TABLE TRM_REPLACE_T ADD ( CONSTRAINT TRM_REPLACE_T_TRL_TYPE_FK FOREIGN KEY ( TRL_RTY_ID ) REFERENCES TRL_TYPE_T ( RTY_ID ) NOT DEFERRABLE INITIALLY IMMEDIATE ENABLE ) / ALTER TABLE TRM_TRL_T ADD ( FOREIGN KEY ( TRM_TRM_ID ) REFERENCES TERMS_T ( TRM_ID ) NOT DEFERRABLE INITIALLY IMMEDIATE ENABLE ) / ALTER TABLE TRM_TRL_T ADD ( FOREIGN KEY ( TRL_TRL_ID ) REFERENCES TRM_REPLACE_T ( TRL_ID ) ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE ENABLE ) / /* =============================================================================== */ /* Check Constraints */ /* =============================================================================== */ ALTER TABLE EDITING_SESSION_T ADD ( CONSTRAINT CC_EDITING_SESSION_T_SUCCESS CHECK ( ese_successful ='n' or ese_successful='y' ) NOT DEFERRABLE INITIALLY IMMEDIATE ENABLE ) / ALTER TABLE TERMS_T ADD ( CONSTRAINT CH_TERMS_1 CHECK ( (trm_edbacc is null and trm_edb_id is null) or (trm_edbacc is not null and trm_edb_id is not null) ) NOT DEFERRABLE INITIALLY IMMEDIATE DISABLE NOVALIDATE ) / ALTER TABLE TRM_CATEGORIES_T ADD ( CHECK ( TCT_MANY = 1 or TCT_MANY =0 ) NOT DEFERRABLE INITIALLY IMMEDIATE ENABLE ) / ALTER TABLE TRM_CATEGORIES_T ADD ( CHECK ( TCT_NOTNULL = 1 or TCT_NOTNULL =0 ) NOT DEFERRABLE INITIALLY IMMEDIATE ENABLE ) / /* =============================================================================== */ /* Packages */ /* =============================================================================== */ CREATE OR REPLACE PACKAGE Curator AS procedure executeReplacement (p_rpl_id number, p_ese_id number); END; / CREATE OR REPLACE PACKAGE pe_build AS procedure rebuildAllViews; --------------------------------------------------------- -- drops all VIEWS with name OBJECT_NAME like'CV_%_V' ---------------------------------------------------------- procedure dropCVViews; ------------------------------------------------------ -- create VIEWS for terms_t -- Name Syntax: CV___V ------------------------------------------------------ procedure createCVViews; -------------------------------------------------- -- calls first dropCVViews, then createCVViews -------------------------------------------------- procedure rebuildCVViews; ------------------------------------------------------ -- create VIEWS for OBJECTS_T -- Name Syntax: OBJ__V ------------------------------------------------------ procedure createOBJViews; procedure dropOBJViews ; procedure rebuildOBJViews; function getMVQuery(p_ojt_name varchar2, p_plain int default 0) return clob; END; / CREATE OR REPLACE PACKAGE pe_constraints AS /* * Mostly there are two kinds of procedures: * - Fist (without parameter) checks whole database * - Seconds checks daten of the given editing session */ ----------------------------------------- -- Checks all contraints listed below ----------------------------------------- procedure checkAllConstraints; procedure checkConstraints(p_ese_id number); ---------------------------------------- -- Checks the defined ForeignKeys ----------------------------------------- procedure checkAllForeignKeys; procedure checkForeignKeys (p_ese_id number); ------------------------------------------- -- Conrtrols the not null contraints -- defined in TRM_CATEGORIES --------------------------------------------- procedure checkAllNotNullConstraints; procedure checkNotNullConstraints (p_ese_id number); ------------------------------------------- -- Checks column RESULT_DATA_T.RSD_OBJ_ID -- Controls that there is only one -- object_type referencd within a resultset ------------------------------------------- procedure checkOneOjtPerResultSet(p_ese_id number); procedure checkOneOjtPerResultSet; ------------------------------------------ -- Checks that there are no duplicate -- identiferes. Identifiers are equals if -- TRM_VALUE and TRM_EDB_ID are equals ------------------------------------------ procedure checkUniqueIdentifiers(p_ese_id number); procedure checkAllUniqueIdentifiers(p_ese_id number); --------------------------------------------- -- Checks that a identifier is used only one time ---------------------------------------------- procedure checkAllUniqueIdentifierRef; END; / CREATE OR REPLACE PACKAGE PE_UTIL AS ----------------------------- -- Deletes data of session. -- Uses separate Statements to -- avoid using of on 'delete cascade', -- because its to slow ----------------------------- procedure deleteEditingSession(p_ese_id number); END; / /* =============================================================================== */ /* Package Bodies */ /* =============================================================================== */ CREATE OR REPLACE PACKAGE BODY Curator AS procedure executeReplacement (p_rpl_id number, p_ese_id number) as -- It is possible to store source term without target within -- Flag avoids that Terms without target are set to obsolete v_targetsFound number :=0; v_opr_parent number := null; -- If ==1 then Entries in OBJ_PROPERTIES_T will be grouped using self reference v_groupingEnable int :=1; -- If ==1 then source relationship will be set to obsolete v_substitutionEnable int :=0; begin -- get All Source Terms of replacement for rec_Trl in ( select TRL_ID, TRL_TRM_ID_SOURCE, RTY_SHORT from TRM_REPLACE_T join TRL_TYPE_T on rty_id=trl_rty_id where TRM_REPLACE_T.TRL_RPL_ID = p_rpl_id ) loop select case substr(rec_Trl.RTY_SHORT,2,2) when 'G' then 1 else 0 end into v_groupingEnable from dual; select case substr(rec_Trl.RTY_SHORT,1,1) when 'R' then 1 else 0 end into v_substitutionEnable from dual; -- get all Objects which have a relationship to rec_Trl.TRL_TRM_ID_SOURCE for rec_Obj in ( select opr_obj_id from OBJ_PROPERTIES_T where opr_trm_id = rec_Trl.TRL_TRM_ID_SOURCE) loop v_opr_parent:=null; -- discard parent Id, because we have a new Object -- for each Object set new relationships to target term(s) for rec_Target in (select TRM_TRM_ID from TRM_TRL_T where TRL_TRL_ID = rec_Trl.TRL_ID) loop begin --DBMS_OUTPUT.put_line('insert into OBJ_PROPERTIES_T (opr_trm_id, opr_ese_id, opr_obj_id,opr_opr_id) values ('||rec_Target.TRM_TRM_ID||','||p_ese_id||','||rec_Obj.opr_obj_id||',' ||v_opr_parent||')'); insert into OBJ_PROPERTIES_T (opr_trm_id, opr_ese_id, opr_obj_id,opr_opr_id) values (rec_Target.TRM_TRM_ID,p_ese_id,rec_Obj.opr_obj_id, v_opr_parent); exception when others then -- if sqlcode = -00001 then --DBMS_OUTPUT.put_line('doppelt:' || rec_Target.TRM_TRM_ID || ' ' || p_ese_id || ' ' || rec_Obj.opr_obj_id || ' ' || v_opr_parent); -- TODO get opr id of duplicate -- null; -- else --raise_application_error(2200,sqlerrm);--sqlcode DBMS_OUTPUT.put_line('error'); raise; -- end if; end; if v_groupingEnable =1 then -- current opr_id will be parent of the next SELECT OBJ_PROPERTIES_SEQ.currval into v_opr_parent FROM DUAL; end if; v_targetsFound:=1; --note that there was a targetTerm end loop; end loop; -- Set relationship to TRL_TRM_ID_SOURCE to obsolete if v_targetsFound = 1 and v_substitutionEnable =1 then update OBJ_PROPERTIES_T set opr_ese_id_obsolete = p_ese_id where opr_trm_id = rec_Trl.TRL_TRM_ID_SOURCE; end if; v_targetsFound:=0; end loop; end; END; / CREATE OR REPLACE PACKAGE BODY pe_build AS procedure rebuildAllViews as begin rebuildOBJViews; rebuildCVViews; end; ------------------------------------------------------ -- create VIEWS for terms_t ------------------------------------------------------ procedure createCVViews as v_stmt varchar2(32767); v_counter number :=0; BEGIN FOR cur_ojt IN (SELECT OJT_ID , OJT_TCT_ID , OJT_NAME , OJT_ABBREVIATION FROM OBJECT_TYPES_T) loop for cur_tct in ( select tct_id, tct_name from TRM_CATEGORIES_t where level >1 connect by prior tct_id = tct_tct_id start with TCT_ID =cur_ojt.OJT_TCT_ID order by TCT_NAME ) loop v_stmt := 'create view CV_'|| cur_ojt.OJT_ABBREVIATION ||'_'||substr(cur_tct.tct_name,0,21) ||'_V as ' || ' SELECT' || ' TRM_ID' || ' ,TRM_VALUE' || ' ,edb_name ' || ' FROM' || ' terms_t' || ' , EXTERNDB_T' || ' WHERE' || ' trm_tct_id = ' || cur_tct.tct_id || ' and edb_id(+) = trm_edb_id'; dbms_output.put_line(v_stmt); execute immediate v_stmt; v_counter:=v_counter+1; end loop;--cur_tct END loop; --cur_ojt DBMS_Output.PUT_LINE(v_counter || ' CV_ views created.'); END; --createCVViews -------------------------------------------------- -- calls first dropCVViews, then createCVViews -------------------------------------------------- procedure rebuildCVViews as begin dropCVViews; createCVViews; end; --rebuildCVViews -------------------------------------------------- -- calls first dropOBJViews, then createOBJViews -------------------------------------------------- procedure rebuildOBJViews as begin dropOBJViews; createOBJViews; end; --rebuildOBJViews procedure createOBJViews as v_stmt varchar (32767); v_query varchar (32767); v_counter number :=0; begin for ojt_cur in ( select ojt_id,ojt_name from OBJECT_TYPES_T ) loop v_query := 'SELECT ' || ' OBJ_ID ' || ' ,TCT_NAME ' || ' , TRM_VALUE ' || ' , nvl2(OPR_ESE_ID_OBSOLETE,1,0) as OPR_ISOBSOLETE' || ' , TRM_ID ' || ' ,TCT_ID ' || ' ,TRM_EDB_ID ' || ' ,OBJ_ESE_ID ' || 'FROM OBJECTS_T ' || 'JOIN OBJ_PROPERTIES_T ON obj_id = opr_obj_id ' || 'JOIN OBJECT_TYPES_T ON ojt_id = obj_ojt_id ' || 'JOIN terms_T ON trm_id = opr_trm_id ' || 'JOIN TRM_CATEGORIES_T ON tct_id = trm_tct_id ' || 'WHERE ojt_id = ' || ojt_cur.ojt_id || 'ORDER BY OBJ_ID'; v_stmt := 'Create view OBJ_'|| ojt_cur.ojt_name ||'_V as '|| v_query; dbms_output.put_line(v_stmt); execute immediate v_stmt; v_counter :=v_counter+1; end loop; DBMS_Output.PUT_LINE(v_counter || ' OBJ_ views created.'); end; --------------------------------------------------------- -- drops all VIEWS with name OBJECT_NAME like'OBJ_%_V' ---------------------------------------------------------- procedure dropOBJViews as v_stmt varchar2(32767); v_counter number :=0; BEGIN for cur_OBJECT_NAME in ( select OBJECT_NAME from user_objects where OBJECT_TYPE='VIEW' and OBJECT_NAME like'OBJ_%_V') loop execute immediate 'drop view ' || cur_OBJECT_NAME.OBJECT_NAME; v_counter:=v_counter+1 ; end loop; DBMS_Output.PUT_LINE(v_counter || ' views dropped.'); end; --dropOBJViews --------------------------------------------------------- -- drops all VIEWS with name OBJECT_NAME like'CV_%_V' ---------------------------------------------------------- procedure dropCVViews as v_stmt varchar2(32767); v_counter number :=0; BEGIN for cur_OBJECT_NAME in ( select OBJECT_NAME from user_objects where OBJECT_TYPE='VIEW' and OBJECT_NAME like'CV_%_V') loop execute immediate 'drop view ' || cur_OBJECT_NAME.OBJECT_NAME; v_counter:=v_counter+1 ; end loop; DBMS_Output.PUT_LINE(v_counter || ' views dropped.'); end; --dropCVViews --------------------------------------------------------- -- returns query for materialized views ---------------------------------------------------------- function getMVQuery(p_ojt_name varchar2, p_plain int default 0) return clob as v_value varchar2(50); v_ddl clob; v_ojt_abbreviation object_types_t.ojt_abbreviation%TYPE; v_colHeader varchar2(100); BEGIN SELECT ojt_abbreviation into v_ojt_abbreviation FROM object_types_t WHERE ojt_name=p_ojt_name ; v_ddl:='SELECT OBJ_ID as ' || v_ojt_abbreviation || '_ID ' ; -- v_ddl:='SELECT OBJ_ID as ID ' ; FOR cur_cat IN ( ( SELECT TCT_NAME , TCT_MANY , 'OBJECT_ATTR' as parentCategory FROM TRM_CATEGORIES_T WHERE tct_tct_id =( SELECT tct_id FROM TRM_CATEGORIES_T WHERE tct_name='OBJECT_ATTR') )UNION ALL ( SELECT TCT_NAME , TCT_MANY , 'FOREIGNKEYS' as parentCategory FROM TRM_CATEGORIES_T WHERE tct_tct_id =( SELECT tct_id FROM TRM_CATEGORIES_T WHERE tct_name='FOREIGNKEYS' ) AND substr(tct_name,0,3) = v_ojt_abbreviation ) UNION ALL ( SELECT TCT_NAME , TCT_MANY , 'Characteristics' as parentCategory FROM TRM_CATEGORIES_T WHERE LEVEL =2 and p_ojt_name<>'BIOMATERIALS' CONNECT BY PRIOR tct_id=tct_tct_id START WITH tct_id = ( SELECT ojt_tct_id FROM OBJECT_TYPES_T WHERE ojt_name=p_ojt_name ) ) ) loop -- ignore many to many relationship if cur_cat.tct_many = 0 then -- Define ColumnHeader if substr(cur_cat.tct_name,0,4) = 'OBJ_' then -- v_colHeader:= substr(cur_cat.tct_name,5); v_colHeader:= v_ojt_abbreviation ||substr(cur_cat.tct_name,4); elsif cur_cat.tct_name='ReplicateIndex' then v_colHeader:='ReplicateIndex'; elsif p_plain =0 and cur_cat.tct_name='NormalizationDescriptionType' then v_colHeader:='NormalizationDescType_ID'; elsif p_plain =0 and cur_cat.parentCategory ='Characteristics' then v_colHeader:= cur_cat.tct_name || '_ID'; else v_colHeader:= cur_cat.tct_name; end if; -- Define ColumnValue if cur_cat.tct_name = 'OBJ_Identifier' then v_value:= 'EDB_NAME||'':''|| TRM_VALUE'; elsif cur_cat.tct_name='ReplicateIndex' then v_value := 'TRM_VALUE'; elsif cur_cat.parentCategory = 'FOREIGNKEYS' then v_value := 'to_number(TRM_VALUE)'; elsif p_plain =0 and cur_cat.parentCategory = 'Characteristics' then v_value :='TRM_ID'; else v_value := 'TRM_VALUE'; end if; v_ddl := v_ddl || ' ,max(decode(TCT_NAME,''' || cur_cat.tct_name || ''',' || v_value || ',null)) as ' || v_colHeader; end if; END loop; v_ddl:=v_ddl || ' from ALL_OBJECTS_V '; v_ddl:=v_ddl || ' where OJT_NAME = '''|| p_OJT_NAME ||''' '; v_ddl:=v_ddl || ' and OPR_ESE_ID_OBSOLETE is null'; v_ddl:=v_ddl || ' group by obj_id'; return v_ddl; -- dbms_output.put_line(v_ddl); END; END; / CREATE OR REPLACE PACKAGE BODY pe_constraints AS procedure checkAllForeignKeys as begin checkForeignKeys(-1); end; procedure checkForeignKeys (p_ese_id number)as v_itemCount integer; -- number items which not exists begin for fkCat in ( -- All ForeignKeyCatogories with Abbreviation of referenced Object SELECT t1.tct_name ,t1.tct_id, substr(t1.tct_name,5,3) as abbr FROM TRM_CATEGORIES_T t1 join TRM_CATEGORIES_T t2 on t1.tct_tct_id = t2.tct_id where t2.tct_name='FOREIGNKEYS') loop DBMS_output.put('Testing ForeignKey '|| fkCat.tct_name ||'... '); begin select count (*) into v_itemCount from ( -- All FK Values select trm_value from terms_t where trm_tct_id=fkCat.tct_id and (trm_ese_id = p_ese_id or p_ese_id = -1) minus -- Existing Object IDs select to_char(obj_id ) from objects_t join OBJECT_TYPES_T on ojt_id =obj_ojt_id where ojt_abbreviation=fkCat.abbr ); if v_itemCount = 0 then DBMS_output.put_line('ok.'); else DBMS_output.put_line('failed. ('||v_itemCount||' objects not found).'); raise_application_error(-20000,v_itemCount|| ' value(s) for foreignkey '|| fkCat.tct_name ||' do not exists.'); end if; end; end loop; end; procedure checkAllNotNullConstraints as begin checkNotNullConstraints(-1); end; procedure checkNotNullConstraints (p_ese_id number) as -- p_ese_id number(38):=-1; v_helpCount number; v_exception number :=0; begin -- get all OBJECT_TYPES of Session for ojt_cur in ( SELECT DISTINCT ojt_id ,ojt_name,ojt_abbreviation,ojt_tct_id FROM Objects_t JOIN object_types_t ON ojt_id =obj_ojt_id WHERE obj_ese_id = p_ese_id or p_ese_id=-1 ) loop -- get all NOT NULL Categories of current Object Type for tct_cur in ( SELECT tct_id,TCT_NAME FROM TRM_CATEGORIES_T wHERE LEVEL > 1 and tct_notnull = 1 AND substr(TCT_NAME,1,3 ) =ojt_cur.ojt_abbreviation CONNECT BY PRIOR tct_id=tct_tct_id START WITH tct_id = ( SELECT tct_id FROM TRM_CATEGORIES_T WHERE tct_name='FOREIGNKEYS' ) union SELECT tct_id,TCT_NAME FROM TRM_CATEGORIES_T wHERE LEVEL > 1 and tct_notnull = 1 CONNECT BY PRIOR tct_id=tct_tct_id START WITH tct_id in ( SELECT tct_id FROM TRM_CATEGORIES_T WHERE tct_id=ojt_cur.ojt_tct_id or tct_name='OBJECT_ATTR' ) ) loop dbms_output.put('Checking Not Null '||ojt_cur.ojt_name ||'.'|| tct_cur.tct_name ||'...'); -- check if Keys are not null select count (*) into v_helpCount from ( SELECT distinct obj_id FROM objects_t WHERE obj_ojt_id = ojt_cur.ojt_id AND ( obj_ese_id = p_ese_id or p_ese_id =-1) minus SELECT distinct opr_obj_id FROM TRM_CATEGORIES_T JOIN terms_t ON tct_id =trm_tct_id JOIN OBJ_PROPERTIES_T ON trm_id=opr_trm_id WHERE tct_id = tct_cur.tct_id AND OPR_ESE_ID_OBSOLETE IS NULL ); if (v_helpCount =0) then dbms_output.put_line('ok.'); else v_exception:=1; dbms_output.put_line('failed. Violations:'|| v_helpCount); end if; end loop;--tct_cur end loop;--ojt_cur if v_exception=1 then raise_application_error(-20001,'Not NULL Contraints violated.'); end if; end; procedure checkOneOjtPerResultSet(p_ese_id number) as v_errCnt integer:=0; begin dbms_output.PUT('Checking that there are no different Objects_Types within one ResultSet. ...'); select count(*) into v_errCnt from ( select rsd_obj_id_rst,count(distinct obj_ojt_id) as ct,obj_ese_id from RESULT_DATA_T join objects_t obj_seq on obj_seq.obj_id = rsd_obj_id join objects_t obj_rst on obj_rst.obj_id = rsd_obj_id_rst where obj_rst.obj_ese_id =p_ese_id or p_ese_id=-1 group by rsd_obj_id_rst,obj_ese_id having count(distinct obj_ojt_id)>1 ); if v_errCnt>0 then dbms_output.PUT_LINE('failed.'); raise_application_error(-20002,'Different Objects_Types within one ResultSet are not allowed.'); else dbms_output.PUT_LINE('ok.'); end if; end; procedure checkOneOjtPerResultSet as begin checkOneOjtPerResultSet(-1); end; procedure checkAllConstraints as begin checkAllForeignKeys(); checkAllNotNullConstraints(); checkOneOjtPerResultSet(); checkAllUniqueIdentifierRef(); end; procedure checkConstraints(p_ese_id number) as begin checkForeignKeys(p_ese_id); checkNotNullConstraints(p_ese_id); checkOneOjtPerResultSet(p_ese_id); checkAllUniqueIdentifierRef(); end; procedure checkUniqueIdentifiers(p_ese_id number) as begin checkAllUniqueIdentifiers(-1); end; procedure checkAllUniqueIdentifiers(p_ese_id number) as v_count number; begin dbms_output.PUT_LINE('Checking for unique OBJ_Identifier.'); SELECT count(trm_value) into v_count FROM terms_t trm WHERE trm_tct_id = (select tct_id from TRM_CATEGORIES_T where tct_name='OBJ_Identifier' ) and (trm_ese_id = p_ese_id or p_ese_id = -1) and trm.rowid NOT IN ( SELECT MIN (rowid) FROM terms_t trmIn WHERE trm_tct_id = (select tct_id from TRM_CATEGORIES_T where tct_name='OBJ_Identifier' ) group by trm_value ); if v_count>0 then dbms_output.PUT_LINE('failed.'); raise_application_error(-20002,'OBJ_Identifier are not unique. See View ERR_NotUniqueIdentifers_V for dedails.'); else dbms_output.PUT_LINE('ok.'); end if; end; procedure checkAllUniqueIdentifierRef as v_count number; begin dbms_output.PUT_LINE('Checking for unique OBJ_Identifier Refererence.'); SELECT --opr_trm_id, count(opr_obj_id ) into v_count FROM OBJ_PROPERTIES_T where opr_trm_id in ( SELECT trm_id FROM TERMS_T WHERE TRM_TCT_ID = ( SELECT TCT_ID FROM TRM_CATEGORIES_T WHERE tct_name ='OBJ_Identifier')) group by opr_trm_id having count(opr_obj_id ) > 1; if v_count>0 then dbms_output.PUT_LINE('failed.'); raise_application_error(-20002,'OBJ_Identifier Refererence is not unique.'); else dbms_output.PUT_LINE('ok.'); end if; end; END; / CREATE OR REPLACE PACKAGE BODY PE_UTIL AS procedure deleteEditingSession(p_ese_id number) as begin dbms_output.PUT(to_char(sysdate,'HH:MM:SS') || ' Deleting OBJ_PROPERTIES_T ...'); delete from OBJ_PROPERTIES_T where opr_ese_id = p_ese_id ; dbms_output.PUT_LINE(' done.'); dbms_output.PUT_line(to_char(sysdate,'HH:MM:SS') || ' Deleting RESULT_DATA_T ...'); for all_rst in ( SELECT obj_id FROM OBJECTS_T WHERE obj_ojt_id = 5 AND obj_ese_id = p_ese_id) loop --dbms_output.PUT_line(to_char(sysdate,'HH:MM:SS') || ' Deleting where RESULT_DATA_T.rsd_obj_id_rst = ' || all_rst.obj_id); DELETE FROM RESULT_DATA_T WHERE rsd_obj_id_rst = all_rst.obj_id; end loop; dbms_output.PUT_LINE(' done.'); dbms_output.PUT(to_char(sysdate,'HH:MM:SS') || ' Deleting OBJECTS_T ...'); delete from OBJECTS_T where obj_ese_id = p_ese_id; dbms_output.PUT_LINE(' done.'); dbms_output.PUT(to_char(sysdate,'HH:MM:SS') || ' Deleting terms_t ...'); delete from terms_t where trm_ese_id = p_ese_id; dbms_output.PUT_LINE(' done.'); dbms_output.PUT(to_char(sysdate,'HH:MM:SS') || ' Deleting EDITING_SESSION_T ...'); delete from EDITING_SESSION_T where ese_id = p_ese_id; dbms_output.PUT_LINE(' done.'); end; END; / /* =============================================================================== */ /* Triggers */ /* =============================================================================== */ CREATE OR REPLACE TRIGGER Trigger_EDITING_SESSION_T_1 BEFORE INSERT ON EDITING_SESSION_T FOR EACH ROW BEGIN SELECT EDITING_SESSION_SEQ.NEXTVAL INTO :NEW.ESE_ID FROM DUAL; END; / CREATE OR REPLACE TRIGGER BI_EXTERNDB_T BEFORE INSERT ON EXTERNDB_T FOR EACH ROW BEGIN if :NEW.EDB_ID is null then SELECT EXTERNDB_SEQ.NEXTVAL INTO :NEW.EDB_ID FROM DUAL; end if; END; / CREATE OR REPLACE TRIGGER BI_OBJECT_T BEFORE INSERT ON OBJECTS_T FOR EACH ROW BEGIN SELECT OBJECT_SEQ.NEXTVAL INTO :NEW.OBJ_ID FROM DUAL; END; / CREATE OR REPLACE TRIGGER BI_OBJECT_TYPES_T BEFORE INSERT ON OBJECT_TYPES_T FOR EACH ROW BEGIN if :NEW.OJT_ID is null then SELECT OBJECTTYPE_SEQ.NEXTVAL INTO :NEW.OJT_ID FROM DUAL; end if; END; / CREATE OR REPLACE TRIGGER OBJ_FILE_BIR BEFORE INSERT ON OBJ_FILES_T REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW declare v_isfile integer; BEGIN SELECT 1 into v_isfile FROM OBJECT_TYPES_T JOIN OBJECTS_T ON obj_ojt_id = ojt_id WHERE obj_id = :new.OFI_OBJ_ID_FIL and OJT_ABBREVIATION ='FIL'; exception when NO_DATA_FOUND then RAISE_APPLICATION_ERROR(-20200,'OFI_OBJ_ID_FIL must be of OBJECT_TYPE File'); END; / CREATE OR REPLACE TRIGGER Trigger_OBJ_FILE_1 BEFORE INSERT ON OBJ_FILES_T FOR EACH ROW BEGIN SELECT OBJECTFILE_SEQ.NEXTVAL INTO :NEW.OFI_ID FROM DUAL; END; / CREATE OR REPLACE TRIGGER BI_OBJ_PROPERTIES_T BEFORE INSERT ON OBJ_PROPERTIES_T FOR EACH ROW BEGIN SELECT OBJ_PROPERTIES_SEQ.NEXTVAL INTO :NEW.OPR_ID FROM DUAL; END; / CREATE OR REPLACE TRIGGER BI_REPLACE_T BEFORE INSERT ON REPLACEMENTS_T FOR EACH ROW BEGIN SELECT TRM_REPLACE_SEQ.NEXTVAL INTO :NEW.RPL_ID FROM DUAL; END; / CREATE OR REPLACE TRIGGER BI_RESULT_DATA_T BEFORE INSERT ON RESULT_DATA_T FOR EACH ROW BEGIN SELECT RESULTDATA_SEQ.NEXTVAL INTO :NEW.RSD_ID FROM DUAL; END; / CREATE OR REPLACE TRIGGER RESULT_DATA_T_BIR BEFORE INSERT ON RESULT_DATA_T REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW declare v_isfile integer; BEGIN SELECT 1 into v_isfile FROM OBJECT_TYPES_T JOIN OBJECTS_T ON obj_ojt_id = ojt_id WHERE obj_id = :new.RSD_OBJ_ID_RST and OJT_ABBREVIATION ='RST'; exception when NO_DATA_FOUND then RAISE_APPLICATION_ERROR(-20200,'RSD_OBJ_ID_RST must be of OBJECT_TYPE ResultSet'); END; / CREATE OR REPLACE TRIGGER BI_TERMS_T BEFORE INSERT ON TERMS_T FOR EACH ROW BEGIN if :NEW.TRM_ID is null then SELECT TERMS_SEQ.NEXTVAL INTO :NEW.TRM_ID FROM DUAL; end if; END; / CREATE OR REPLACE TRIGGER TERMS_T_BIUR BEFORE INSERT OR UPDATE ON TERMS_T REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW BEGIN if :new.trm_edb_id is null then :new.trm_edb_id :=0; end if; END; / CREATE OR REPLACE TRIGGER BI_TRL_TYPE BEFORE INSERT ON TRL_TYPE_T FOR EACH ROW BEGIN SELECT TRM_REPLACE_SEQ.NEXTVAL INTO :NEW.RTY_ID FROM DUAL; END; / CREATE OR REPLACE TRIGGER BI_TRM_CATEGORIES_T BEFORE INSERT ON TRM_CATEGORIES_T FOR EACH ROW BEGIN if :NEW.TCT_ID is null then SELECT TRM_CATERGORIES_SEQ.NEXTVAL INTO :NEW.TCT_ID FROM DUAL; end if; END; / CREATE OR REPLACE TRIGGER Trigger_TRM_REPLACE_T_1 BEFORE INSERT ON TRM_REPLACE_T FOR EACH ROW BEGIN SELECT TRM_REPLACE_SEQ.NEXTVAL INTO :NEW.TRL_ID FROM DUAL; END; / CREATE OR REPLACE TRIGGER Trigger_USERS_T_1 BEFORE INSERT ON USERS_T FOR EACH ROW BEGIN SELECT USER_SEQ.NEXTVAL INTO :NEW.URS_ID FROM DUAL; END; / /* =============================================================================== */ /* Synonyms */ /* =============================================================================== */ CREATE SYNONYM "RM$REPOSITORIES" FOR REPOS_MANAGER."RM$REPOSITORIES" / CREATE SYNONYM TESTSYS FOR PDW_EXPRESS_STAGE.OBJ_ARRAYS_V /