####################### # Don't forget indexes ####################### ------------------------------------------------------------- ------------------------------------------------------------- dn1; 22/11/2004 Scerario for setting up a new live GTR tract Note that to setup a new tract the operator requires the passwords for tracking@CAS entman@GTR gtadmin@GTR Note that this relies on having representative tract data on TGTR All the queries are to be executed on GTR. ------------------------------------------------------------- ------------------------------------------------------------- ########################################################################## # Create a new role which will be assigned to the application access user # This will be called *_B_ROLE # Note that "*" should not begin with a number eg. 2D_BARCODE. ########################################################################## *Execute as user entman* create role EGAG_MOUSE_B_ROLE not identified; ################################################################################ Setup 2 new users. 1 user will be for application access on the tract; this will be called *_B_API ################################################################################ *DDL generated from TOAD* *Execute as user entman* CREATE USER "EGAG_MOUSE_B_API" IDENTIFIED BY "EGAG_MOUSE_B_API" DEFAULT TABLESPACE DATA_LARGE1 TEMPORARY TABLESPACE TEMP_01 QUOTA UNLIMITED ON DATA_LARGE1 QUOTA UNLIMITED ON TEMP_01; GRANT "CONNECT" TO "EGAG_MOUSE_B_API"; GRANT "EGAG_MOUSE_B_ROLE" TO "EGAG_MOUSE_B_API"; ALTER USER "EGAG_MOUSE_B_API" DEFAULT ROLE "CONNECT","EGAG_MOUSE_B_ROLE"; ####################################################################### 1 user will be the schema owner for the tract; this will be called *_B ####################################################################### *DDL generated from TOAD* *Execute as user entman* CREATE USER "EGAG_MOUSE_B" IDENTIFIED BY "EGAG_MOUSE_B" DEFAULT TABLESPACE DATA_LARGE1 TEMPORARY TABLESPACE TEMP_01 QUOTA UNLIMITED ON DATA_LARGE1 QUOTA UNLIMITED ON TEMP_01; GRANT "CONNECT" TO "EGAG_MOUSE_B"; GRANT "RESOURCE" TO "EGAG_MOUSE_B"; ALTER USER "EGAG_MOUSE_B" DEFAULT ROLE "CONNECT","RESOURCE"; ## CREATE USER "EGAG_MOUSE_ADMIN" IDENTIFIED BY "EGAG_MOUSE_ADMIN" DEFAULT TABLESPACE DATA_LARGE1 TEMPORARY TABLESPACE TEMP_01 QUOTA UNLIMITED ON DATA_LARGE1 QUOTA UNLIMITED ON TEMP_01; GRANT "CONNECT" TO "EGAG_MOUSE_ADMIN"; GRANT "RESOURCE" TO "EGAG_MOUSE_ADMIN"; ALTER USER "EGAG_MOUSE_B" DEFAULT ROLE "CONNECT","RESOURCE"; ## ######################################################################## # Grant reference privileges on core GTADMIN tables to new schema owner ######################################################################## *DDL generated from TOAD* *Execute as user entman* GRANT REFERENCES ON GTADMIN.ATTRIBUTE_DICT TO EGAG_MOUSE_B; GRANT REFERENCES ON GTADMIN.CONTENTS_INFO_DICT TO EGAG_MOUSE_B; GRANT REFERENCES ON GTADMIN.CONTENTS_INFO_REASON TO EGAG_MOUSE_B; GRANT REFERENCES ON GTADMIN.ENTITY_CONTENTS_TYPE TO EGAG_MOUSE_B; GRANT REFERENCES ON GTADMIN.ENTITY_TYPE TO EGAG_MOUSE_B; GRANT REFERENCES ON GTADMIN.LOCAL_TEAM_PERSON_ROLE TO EGAG_MOUSE_B; ############################################################### # Set up the tract objects under the new schema owner. # Assuming Tablespaces are setup with local extent management. ############################################################### TOAD -> DBA -> Compare Schemas; to check tract setup/generate DDL Reference source Connection GTADMIN@GTR.WORLD Schema EPIGENOMICS_B Comparison source Connection GTADMIN@GTR.WORLD Schema EGAG_MOUSE_B *Execute as user tract with Default tablespace DATA_LARGE1* CREATE TABLE EGAG_MOUSE_B.PHYSICAL_ENTITY ( ID_ENTITY NUMBER(38) NOT NULL, ID_ENTITY_TYPE NUMBER(38) NOT NULL ) LOGGING NOCACHE NOPARALLEL; COMMENT ON TABLE EGAG_MOUSE_B.PHYSICAL_ENTITY IS 'Physical entities (usually barcoded); Use a different sequence for each entity_type'; COMMENT ON COLUMN EGAG_MOUSE_B.PHYSICAL_ENTITY.ID_ENTITY IS 'Internal identity for an entity (this is not a barcode)'; COMMENT ON COLUMN EGAG_MOUSE_B.PHYSICAL_ENTITY.ID_ENTITY_TYPE IS 'The type of entity represented'; CREATE TABLE EGAG_MOUSE_B.ENTITY_CONTENTS ( ID_ENTITY_CONTENT NUMBER(38) NOT NULL, ID_ENTITY NUMBER(38) NOT NULL, ID_ENTITY_CONTENTS_TYPE NUMBER(38) DEFAULT 1 NOT NULL ) LOGGING NOCACHE NOPARALLEL; COMMENT ON TABLE EGAG_MOUSE_B.ENTITY_CONTENTS IS 'The contents of an entity (eg. wells, tubes)'; COMMENT ON COLUMN EGAG_MOUSE_B.ENTITY_CONTENTS.ID_ENTITY_CONTENT IS 'Unique identifier of a content member of an entity'; COMMENT ON COLUMN EGAG_MOUSE_B.ENTITY_CONTENTS.ID_ENTITY IS 'Belongs to which entity ?'; CREATE TABLE EGAG_MOUSE_B.CONTENTS_INFO ( ID_ENTITY_CONTENT NUMBER(38) NOT NULL, ID_CONTENT NUMBER(38) NOT NULL, ID_CONTENT_TYPE NUMBER(38) NOT NULL, ID_REASON NUMBER(38) NOT NULL, ID_CONTENTS_INFO NUMBER(38) NOT NULL ) LOGGING NOCACHE NOPARALLEL; COMMENT ON TABLE EGAG_MOUSE_B.CONTENTS_INFO IS 'Information attached to the contents of an entity'; COMMENT ON COLUMN EGAG_MOUSE_B.CONTENTS_INFO.ID_ENTITY_CONTENT IS 'The entity content in question'; COMMENT ON COLUMN EGAG_MOUSE_B.CONTENTS_INFO.ID_CONTENT IS 'An identifier'; COMMENT ON COLUMN EGAG_MOUSE_B.CONTENTS_INFO.ID_CONTENT_TYPE IS 'A link to where the identifer and hence information can be found (via contents_info_dict)'; COMMENT ON COLUMN EGAG_MOUSE_B.CONTENTS_INFO.ID_REASON IS 'A link is made to information for a reason (eg. contents of the well (a seqobject), well/experiment requires something, well arrayed from)'; CREATE TABLE EGAG_MOUSE_B.ATTRIBUTE ( ID_ATTRIBUTE NUMBER(38) NOT NULL, ID_ATTRIBUTE_TYPE NUMBER(38) NOT NULL, VALUE VARCHAR2(240 BYTE) NOT NULL, ISCURRENT NUMBER(1) DEFAULT 1 NOT NULL, ID_ROLE NUMBER(38) NOT NULL, INIT_DATE DATE DEFAULT sysdate NOT NULL, END_DATE DATE, ID_SESSION NUMBER(38), PROGRAM VARCHAR2(30 BYTE), REMARK VARCHAR2(1000 BYTE), ID_CONTENTS_INFO NUMBER(38) NOT NULL ) LOGGING NOCACHE NOPARALLEL; COMMENT ON TABLE EGAG_MOUSE_B.ATTRIBUTE IS 'Records attributes which can be linked to entities via contents_info entries'; COMMENT ON COLUMN EGAG_MOUSE_B.ATTRIBUTE.ID_ATTRIBUTE IS 'Unique identifier'; COMMENT ON COLUMN EGAG_MOUSE_B.ATTRIBUTE.ID_ATTRIBUTE_TYPE IS 'The type of attribute in question'; COMMENT ON COLUMN EGAG_MOUSE_B.ATTRIBUTE.VALUE IS 'The value for this instance'; COMMENT ON COLUMN EGAG_MOUSE_B.ATTRIBUTE.ISCURRENT IS 'Is this a current entry ?'; COMMENT ON COLUMN EGAG_MOUSE_B.ATTRIBUTE.ID_ROLE IS 'The user adding the attribute'; COMMENT ON COLUMN EGAG_MOUSE_B.ATTRIBUTE.INIT_DATE IS 'The data the attribute was added'; COMMENT ON COLUMN EGAG_MOUSE_B.ATTRIBUTE.END_DATE IS 'The date at which the attribute is no longer relevant'; COMMENT ON COLUMN EGAG_MOUSE_B.ATTRIBUTE.ID_SESSION IS 'session identifier'; COMMENT ON COLUMN EGAG_MOUSE_B.ATTRIBUTE.PROGRAM IS 'The program adding the attribute'; COMMENT ON COLUMN EGAG_MOUSE_B.ATTRIBUTE.REMARK IS 'Optional free text remark'; *Execute as user tract with Default tablespace INDEX_LARGE1* ALTER USER EGAG_MOUSE_B DEFAULT TABLESPACE INDEX_LARGE1; ALTER TABLE EGAG_MOUSE_B.PHYSICAL_ENTITY ADD CONSTRAINT ENT_PK PRIMARY KEY (ID_ENTITY); ALTER TABLE EGAG_MOUSE_B.PHYSICAL_ENTITY ADD CONSTRAINT ENT_ENTY_FK FOREIGN KEY (ID_ENTITY_TYPE) REFERENCES GTADMIN.ENTITY_TYPE (ID_ENTITY_TYPE); ALTER TABLE EGAG_MOUSE_B.ENTITY_CONTENTS ADD CONSTRAINT ENCON_PK PRIMARY KEY (ID_ENTITY_CONTENT); ALTER TABLE EGAG_MOUSE_B.ENTITY_CONTENTS ADD CONSTRAINT ENCON_ECT_FK FOREIGN KEY (ID_ENTITY_CONTENTS_TYPE) REFERENCES GTADMIN.ENTITY_CONTENTS_TYPE (ID_ENTITY_CONTENTS_TYPE); ALTER TABLE EGAG_MOUSE_B.ENTITY_CONTENTS ADD CONSTRAINT ENCON_ENT_FK FOREIGN KEY (ID_ENTITY) REFERENCES EGAG_MOUSE_B.PHYSICAL_ENTITY (ID_ENTITY); ALTER TABLE EGAG_MOUSE_B.CONTENTS_INFO ADD CONSTRAINT CONS_PK PRIMARY KEY (ID_CONTENTS_INFO); ALTER TABLE EGAG_MOUSE_B.CONTENTS_INFO ADD CONSTRAINT CONS_UK UNIQUE (ID_ENTITY_CONTENT, ID_CONTENT, ID_CONTENT_TYPE); ALTER TABLE EGAG_MOUSE_B.CONTENTS_INFO ADD CONSTRAINT CONS_CID_FK FOREIGN KEY (ID_CONTENT_TYPE) REFERENCES GTADMIN.CONTENTS_INFO_DICT (ID_CONTENT_TYPE); ALTER TABLE EGAG_MOUSE_B.CONTENTS_INFO ADD CONSTRAINT CONS_CIR_FK FOREIGN KEY (ID_REASON) REFERENCES GTADMIN.CONTENTS_INFO_REASON (ID_REASON); ALTER TABLE EGAG_MOUSE_B.CONTENTS_INFO ADD CONSTRAINT CONS_ENCON_FK FOREIGN KEY (ID_ENTITY_CONTENT) REFERENCES EGAG_MOUSE_B.ENTITY_CONTENTS (ID_ENTITY_CONTENT); ALTER TABLE EGAG_MOUSE_B.ATTRIBUTE ADD CONSTRAINT AVCON_1082478976_ISCUR_001 CHECK (ISCURRENT IN (0, 1)); ALTER TABLE EGAG_MOUSE_B.ATTRIBUTE ADD CONSTRAINT ENAT_PK PRIMARY KEY (ID_ATTRIBUTE); ALTER TABLE EGAG_MOUSE_B.ATTRIBUTE ADD CONSTRAINT ENAT_AD_FK FOREIGN KEY (ID_ATTRIBUTE_TYPE) REFERENCES GTADMIN.ATTRIBUTE_DICT (ID_ATTRIBUTE_TYPE); ALTER TABLE EGAG_MOUSE_B.ATTRIBUTE ADD CONSTRAINT ENAT_CONS_FK FOREIGN KEY (ID_CONTENTS_INFO) REFERENCES EGAG_MOUSE_B.CONTENTS_INFO (ID_CONTENTS_INFO); ALTER TABLE EGAG_MOUSE_B.ATTRIBUTE ADD CONSTRAINT ENAT_TPE_FK FOREIGN KEY (ID_ROLE) REFERENCES GTADMIN.LOCAL_TEAM_PERSON_ROLE (ID_ROLE); ##################################################################################### # Grant insert, update, select privileges on new tract tables to relevant tract role ##################################################################################### GRANT INSERT, SELECT, UPDATE ON EGAG_MOUSE_B.ENTITY_CONTENTS TO EGAG_MOUSE_B_ROLE; GRANT INSERT, SELECT, UPDATE ON EGAG_MOUSE_B.CONTENTS_INFO TO EGAG_MOUSE_B_ROLE; GRANT INSERT, SELECT, UPDATE ON EGAG_MOUSE_B.PHYSICAL_ENTITY TO EGAG_MOUSE_B_ROLE; GRANT INSERT, SELECT, UPDATE ON EGAG_MOUSE_B.ATTRIBUTE TO EGAG_MOUSE_B_ROLE; #################################################################################### # Set up private synonyms so that the new application access user has access to the # relevant new tract owner #################################################################################### TOAD -> DBA -> Compare Schemas; to check tract setup/generate DDL Reference source Connection GTADMIN@GTR.WORLD Schema EPIGENOMICS_B_API Comparison source Connection GTADMIN@GTR.WORLD Schema EGAG_MOUSE_B_API *Execute as entman* CREATE SYNONYM EGAG_MOUSE_B_API.ATTRIBUTE FOR EGAG_MOUSE_B.ATTRIBUTE; CREATE SYNONYM EGAG_MOUSE_B_API.CONTENTS_INFO FOR EGAG_MOUSE_B.CONTENTS_INFO; CREATE SYNONYM EGAG_MOUSE_B_API.ENTITY_CONTENTS FOR EGAG_MOUSE_B.ENTITY_CONTENTS; CREATE SYNONYM EGAG_MOUSE_B_API.PHYSICAL_ENTITY FOR EGAG_MOUSE_B.PHYSICAL_ENTITY; ######################################################################## # Set up new tract specific sequences required ######################################################################## ######################################################################## # Set up new tract specific indexes for the new tract owner in question ######################################################################## *Execute as entman* CREATE INDEX EGAG_MOUSE_B.ATTRIBUTE_I_ID_ATTRIBUTE_TYPE ON EGAG_MOUSE_B.ATTRIBUTE (ID_ATTRIBUTE_TYPE) NOLOGGING NOPARALLEL; CREATE INDEX EGAG_MOUSE_B.ATTRIBUTE_I_ID_CONTENTS_INFO ON EGAG_MOUSE_B.ATTRIBUTE (ID_CONTENTS_INFO) NOLOGGING NOPARALLEL; CREATE INDEX EGAG_MOUSE_B.ATTRIBUTE_I_ID_ROLE ON EGAG_MOUSE_B.ATTRIBUTE (ID_ROLE) NOLOGGING NOPARALLEL; CREATE INDEX EGAG_MOUSE_B.CONTENTS_INFO_I_ID_CONTENT_TYP ON EGAG_MOUSE_B.CONTENTS_INFO (ID_CONTENT_TYPE) NOLOGGING NOPARALLEL; CREATE INDEX EGAG_MOUSE_B.CONTENTS_INFO_I_ID_ENTITY_CONT ON EGAG_MOUSE_B.CONTENTS_INFO (ID_ENTITY_CONTENT) NOLOGGING NOPARALLEL; CREATE INDEX EGAG_MOUSE_B.CONTENTS_INFO_I_ID_REASON ON EGAG_MOUSE_B.CONTENTS_INFO (ID_REASON) NOLOGGING NOPARALLEL; CREATE INDEX EGAG_MOUSE_B.ENTITY_CONTENTS_I_ID_ENTITY ON EGAG_MOUSE_B.ENTITY_CONTENTS (ID_ENTITY) NOLOGGING NOPARALLEL; CREATE INDEX EGAG_MOUSE_B.ENTITY_CONTENTS_I_ID_ENTITY_CO ON EGAG_MOUSE_B.ENTITY_CONTENTS (ID_ENTITY_CONTENTS_TYPE) NOLOGGING NOPARALLEL; CREATE INDEX EGAG_MOUSE_B.PHYSICAL_ENTITY_I_ID_ENTITY_TY ON EGAG_MOUSE_B.PHYSICAL_ENTITY (ID_ENTITY_TYPE) NOLOGGING NOPARALLEL; ##RESET THE USER DEFAULT TABLESPACE BACK TO DATA_LARGE1 ALTER USER EGAG_MOUSE_B DEFAULT TABLESPACE DATA_LARGE1; # # # # ################################## # END OF TRACT STRUCTURE SETUP # ################################## # # # # ######################################################################## ##CHECK NEW TRACTS TO ENSURE YOU CAN SEE THE NECESSARY DICTIONARY DATA. ######################################################################## select * from contents_info_dict; select * from contents_info_reason; select * from entity_contents_type; select * from attribute_dict; select * from entity_type; ############################################################### # Isolate and create missing entries from shared dictionaries. # Use the test tract on TGTR for a comparison. ############################################################### ####################################################################### Need a db_link from GTR to TGTR which can see shared dictionaries plus specific tract entries; create as user GTADMIN@GTR, If not already present. # CREATE DATABASE LINK "GT_ALL" CONNECT TO "GT_ALL_TRACTS" # IDENTIFIED BY "GT_ALL_TRACTS" # USING 'TGTR.WORLD'; ####################################################################### ########################################################### ########################################################### ##ADD IN DICTIIONARY DATA FOR THE TRACT FROM TGTR ---> GTR ########################################################### ########################################################### ################################################################################## # The following will show which entity_types, barcode_prefixes and sequences are # required ## RUN THE COMMANDS ON GTR. ## ONLY RUN THE COMMANDS THAT ARE NOT HASHED OUT, THESE WILL GENERATE THE ## RELEVANT INSERT STATEMENTS FOR THE DATA REQUIRED FROM THE DEVELOPMENT TRACTS ## ON TGTR THAT ARE NOT PRESENT IN GTR. ################################################################################## column ID_PREFIX FORMAT a10; column DESCRIPTION FORMAT a30; column USE_SEQUENCE FORMAT a20; column DAVE_COMMENT FORMAT a20; // to check TGTR AND RETURN VALUES THAT EXIST FOR A SPECIFIED TRACT ON TGTR BUT NOT ON GTR. select 'INSERT INTO ENTITY_TYPE (ID_ENTITY_TYPE,DESCRIPTION,ID_PREFIX,USE_SEQUENCE,CODE) VALUES ('||id_entity_type||',"'||description||'","'||id_prefix||'","'||use_sequence||'","'||code||'");' from entity_type@GT_ALL where id_entity_type in (select distinct id_entity_type from EGAG_B.physical_entity@GT_ALL WHERE ID_ENTITY_TYPE NOT IN (SELECT ID_ENTITY_TYPE FROM ENTITY_TYPE)) order by id_entity_type; #//GTR # #select * from entity_type where id_entity_type in ( # select distinct id_entity_type from EGAG_B.physical_entity@GT_ALL #)order by id_entity_type; ##################################################### # Checking barcode_prefix setup between GTR and TGTR ##################################################### set linesize 1000; column INSTANCE format a10; column DB_LINK format a10; column PK_COLUMN format a10; column ORA_USER format a10; //TGTR barcode_prefix setup (snapshot local_barcode_prefix@TGTR cones from barcode_prefix@TCAS) // RETURN VALUES THAT EXIST FOR A SPECIFIED TRACT ON TGTR BUT NOT ON GTR. select distinct 'INSERT INTO BARCODE_PREFIX(ID_PREFIX,TABLE_NAME,DESCRIPTION,ID_LABEL,INSTANCE,PK_COLUMN,DB_LINK,ORA_USER) VALUES ("'||id_prefix||'","'||table_name||'","'||description||'",'||id_label||',"'||instance||'","'||pk_column||'","'||db_link||'","'||ora_user||'");' from barcode_prefix@GT_ALL where id_prefix in ( select id_prefix from entity_type@GT_ALL where id_entity_type in (select distinct id_entity_type from EGAG_B.physical_entity@GT_ALL WHERE ID_ENTITY_TYPE NOT IN (SELECT ID_ENTITY_TYPE FROM ENTITY_TYPE))); #//GTR barcode_prefix setup (snapshot local_barcode_prefix@GTR comes from barcode_prefix@CAS) # #select distinct * from barcode_prefix where id_prefix in ( #select id_prefix from entity_type@GT_ALL where id_entity_type in ( # select distinct id_entity_type from EGAG_B.physical_entity@GT_ALL #)); ##################################### # New barcode_prefix entries for GTR ##################################### As user tracking@CAS insert into barcode_prefix (ID_PREFIX, TABLE_NAME, DESCRIPTION, ID_LABEL, INSTANCE, PK_COLUMN, DB_LINK, ORA_USER) values ('TP', 'EGAG_MOUSE_B.ATTRIBUTE', 'A tilepath/cloneset plate', 6, 'GTR', 'VALUE', 'GTR.WORLD', 'EGAG_MOUSE_B'); Refresh gtadmin@GTR refresh group 'TRACKING_GROUP' ################################ # New entity_type set up on GTR ################################ as user gtadmin@GTR insert into entity_type (ID_ENTITY_TYPE, DESCRIPTION, ID_PREFIX, USE_SEQUENCE) values (56, 'TilePath plate', 'TP', 'SEQ_ENTITY_TYPE_56'); ################################################## # New sequence setup on GTR # # rem consistency between sequence naming ################################################## #SEQ_ENTITY_EP ----> becomes SEQ_ENTITY_TYPE_50 #SEQ_ENTITY_EP ----> becomes SEQ_ENTITY_TYPE_50 #SEQ_ENTITY_EC ----> becomes SEQ_ENTITY_TYPE_52 #SEQ_ENTITY_ER ----> becomes SEQ_ENTITY_TYPE_53 #SEQ_ENTITY_EQ ----> becomes SEQ_ENTITY_TYPE_54 # #as user gtadmin@GTR # #update entity_type set USE_SEQUENCE = 'SEQ_ENTITY_TYPE_50' where id_entity_type = 50; CREATE SEQUENCE GTADMIN.SEQ_ENTITY_TYPE_56 START WITH 1 INCREMENT BY 1 MINVALUE 0 NOCACHE NOCYCLE NOORDER; public synonyms created for above sequences via toad; GRANT SELECT ON GTADMIN.SEQ_ENTITY_TYPE_56 TO PUBLIC; ######################################### # New entity_contents_type set up on GTR ######################################### // TGTR RETURN VALUES THAT EXIST FOR A SPECIFIED TRACT ON TGTR BUT NOT ON GTR. select 'INSERT INTO ENTITY_CONTENTS_TYPE (ID_ENTITY_CONTENTS_TYPE,DESCRIPTION,CODE) VALUES ('||id_entity_contents_type||',"'||description||'","'||code||'");' from entity_contents_type@GT_ALL where id_entity_contents_type in ( select distinct id_entity_contents_type from EGAG_B.entity_contents@GT_ALL WHERE ID_ENTITY_CONTENTS_TYPE NOT IN (SELECT ID_ENTITY_CONTENTS_TYPE FROM ENTITY_CONTENTS_TYPE)); #// GTR # #select * from entity_contents_type where id_entity_contents_type in ( #select distinct id_entity_contents_type from EGAG_B.entity_contents@GT_ALL #); ############################## # INFO_CONNECT setup ############################## # #column DB_LINK format a20; #column R_DBI format a20; #column RW_DBI format a20; // TO CHECK TGTR RETURN VALUES THAT EXIST FOR A SPECIFIED TRACT ON TGTR BUT NOT ON GTR select 'INSERT INTO INFO_CONNECT (DB_LINK,R_DBI,RW_DBI) VALUES ("'||db_link||'","'||r_dbi||'","'||rw_dbi||'");' from INFO_CONNECT@GT_ALL where db_link in (select db_link from contents_info_dict@GT_ALL where id_content_type in (select distinct id_content_type from EGAG_B.contents_info@GT_ALL WHERE ID_CONTENT_TYPE NOT IN (SELECT ID_CONTENT_TYPE FROM CONTENTS_INFO_DICT))); #// GTR #select * from INFO_CONNECT; #select * from INFO_CONNECT where db_link in (select db_link from contents_info_dict where id_content_type in (select distinct id_content_type from EGAG_B.contents_info@GT_ALL)); #as user gtadmin@GTR #insert into info_connect (DB_LINK, R_DBI, RW_DBI) values ('SNP.WORLD', 'snpreport', 'snp'); ################################ # New entity_status_dict set up on GTR ################################ as user gtadmin@GTR //TO CHECK TGTR RETURN VALUES THAT EXIST FOR A SPECIFIED TRACT ON TGTR BUT NOT ON GTR. ##do not use for now #select 'INSERT INTO ENTITY_STATUS_DICT (ID_STATUS,DESCRIPTION,ID_ENTITY_TYPE,MEANING) VALUES ('||id_status||',"'||description||'",'||id_entity_type||',"'||meaning||'");' from entity_status_dict@GT_ALL where id_entity_type in ( #select distinct id_entity_type #from EGAG_B.physical_entity@GT_ALL #WHERE ID_ENTITY_TYPE NOT IN (SELECT ID_ENTITY_TYPE FROM ENTITY_STATUS_DICT) #) order by id_status; #THIS MIGHT BE A BETTER STATEMENT TO USE. select 'INSERT INTO ENTITY_STATUS_DICT (ID_STATUS,DESCRIPTION,ID_ENTITY_TYPE,MEANING) VALUES ('||id_status||',"'||description||'",'||id_entity_type||',"'||meaning||'");' from entity_status_dict@GT_ALL where id_entity_type in ( select distinct id_entity_type from EGAG_B.physical_entity@GT_ALL ) and ID_STATUS NOT IN (select id_status from entity_status_dict) order by id_status; #//GTR # #select * from entity_status_dict where id_entity_type in ( # select distinct id_entity_type from EGAG_B.physical_entity@GT_ALL #)order by id_status; ############################################# # New contents_info set up on GTR # # This will give an indication of BIO table # dependencies ############################################# column TABLE_NAME format a25; column PK_COLUMN format a25; column DB_LINK format a25; // TGTR RETURN VALUES THAT EXIST FOR A SPECIFIED TRACT ON TGTR BUT NOT ON GTR. select 'INSERT INTO CONTENTS_INFO_DICT (ID_CONTENT_TYPE,TABLE_NAME,PK_COLUMN,DB_LINK,CODE) VALUES ('||id_content_type||',"'||table_name||'","'||pk_column||'","'||db_link||'","'||code||'");' from contents_info_dict@GT_ALL where id_content_type in ( select distinct id_content_type from EGAG_B.contents_info@GT_ALL WHERE ID_CONTENT_TYPE NOT IN (SELECT ID_CONTENT_TYPE FROM CONTENTS_INFO_DICT)) order by id_content_type; #// GTR # #select * from contents_info_dict where id_content_type in ( #select distinct id_content_type from EGAG_B.contents_info@GT_ALL #) order by id_content_type; As user gtadmin@GTR ############################################# # New contents_info_reason set up on GTR # ############################################# column DAVE_COMMENT format a35; // TGTR RETURN VALUES THAT EXIST FOR A SPECIFIED TRACT ON TGTR BUT NOT ON GTR. select 'INSERT INTO CONTENTS_INFO_REASON (ID_REASON,DESCRIPTION,CODE) VALUES ('||id_reason||',"'||description||'","'||code||'");' from contents_info_reason@GT_ALL where id_reason in ( select distinct id_reason from EGAG_B.contents_info@GT_ALL WHERE ID_REASON NOT IN (SELECT ID_REASON FROM CONTENTS_INFO_REASON) ) order by id_reason; #// GTR # #select * from contents_info_reason where id_reason in ( #select distinct id_reason from EGAG_B.contents_info@GT_ALL #) order by id_reason; ################################################################ # New attribute_dict set up on GTR # # ! Note ! New entries for attribute_dict@GTR may require # depended upon entries to be inserted into contents_info_dict ################################################################ column DESCRIPTION format a20; column DATATYPE format a20; column DAVE_COMMENT format a20; // TGTR RETURN VALUES THAT EXIST FOR A SPECIFIED TRACT ON TGTR BUT NOT ON GTR. select 'INSERT INTO ATTRIBUTE_DICT (ID_ATTRIBUTE_TYPE,DESCRIPTION,DATATYPE,CODE) VALUES ('||id_attribute_type||',"'||description||'","'||datatype||'","'||code||'");' from attribute_dict@GT_ALL where id_attribute_type in (select distinct id_attribute_type from EGAG_B.attribute@GT_ALL WHERE ID_ATTRIBUTE_TYPE NOT IN (SELECT ID_ATTRIBUTE_TYPE FROM ATTRIBUTE_DICT)) order by id_attribute_type; #// GTR # #select * from attribute_dict where id_attribute_type in ( #select distinct id_attribute_type from EGAG_B.attribute@GT_ALL #) order by id_attribute_type; As user gtadmin@GTR insert into attribute_dict (ID_ATTRIBUTE_TYPE, DESCRIPTION, DATATYPE, CODE) values (32, 'Gel band size', 'VARCHAR2(38)','GEL_SIZE'); ################################################################# # Setting up the relevant processes for the tract KVA TODO # Note that this will have to be done as each tract is made live ################################################################# Find what the relevant process structure is on TGTR to copy. run locally process_insert.pl -pid ############################################################################################### # Bio table setup; installed under GTADMIN@GTR OR INSTALLED ON DIFFERENT DATABASE # SET UP SYNONYMS, GRANTS ETC. ############################################################################################### ALTER TABLE PCR_ENZYME ADD ( CONSTRAINT PEE_PK PRIMARY KEY (ID_DICT) USING INDEX TABLESPACE INDEX_01 PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 64K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 10 FREELISTS 1 FREELIST GROUPS 1 )); public synonym created via toad ALTER TABLE PCR_ENZYME ADD CONSTRAINT PEE_UK UNIQUE (DESCRIPTION) ENABLE VALIDATE; GRANT SELECT ON PCR_ENZYME TO READ_ONLY; insert into pcr_enzyme (select * from pcr_enzyme@TSNP);