-- NEW MAGE TABLES FOR CGH AND AFFY DATA.

CREATE TABLE BIOSEQUENCE_DESCRIPTION (
       BIOSEQUENCE_DESCRIPTION_ID NUMBER NOT NULL,
       DESCRIPTION_TEXT     VARCHAR2(1024) NULL,
       DESCRIPTION_URI      VARCHAR2(255) NULL,
       BIOSEQUENCE_ID       NUMBER NULL,
       PRIMARY KEY (BIOSEQUENCE_DESCRIPTION_ID)  );

CREATE TABLE DESCRIPTION_ONTOLOGYENTRY (
       DESCRIPTION_ONTOLOGYENTRY_ID NUMBER NOT NULL,
       ONTOLOGYENTRY_CATEGORY VARCHAR2(50) NULL,
       ONTOLOGYENTRY_VALUE  VARCHAR2(255) NULL,
       ONTOLOGYENTRY_DESCRIPTION VARCHAR2(1025) NULL,
       BIOSEQUENCE_DESCRIPTION_ID NUMBER NULL,
       PRIMARY KEY (DESCRIPTION_ONTOLOGYENTRY_ID)  );

CREATE TABLE MAGE_BIOSEQ_COMPOS_SEQ (
       BIOSEQUENCE_ID       NUMBER NOT NULL,
       MAGE_COMPOSITE_SEQUENCE_ID NUMBER NOT NULL,
       PRIMARY KEY (BIOSEQUENCE_ID, MAGE_COMPOSITE_SEQUENCE_ID)  );

CREATE TABLE MAGE_COMPOSITE_SEQUENCE (
       MAGE_COMPOSITE_SEQUENCE_ID NUMBER NOT NULL,
       DESIGN_ELEMENT_GROUP_ID NUMBER NULL,
       COMPOSITE_SEQUENCE_NAME VARCHAR2(1024) NULL,
       COMPOSITE_SEQUENCE_DESCRIPTION VARCHAR2(1024) NULL,
       PRIMARY KEY (MAGE_COMPOSITE_SEQUENCE_ID)  );

CREATE TABLE MAGE_FEATURE (
       MAGE_FEATURE_ID      NUMBER NOT NULL,
       MAGE_ZONE_ID         NUMBER NULL,
       FEATURE_GROUP_ID     NUMBER NULL,
       DISTANCE_X           NUMBER NULL,
       DISTANCE_Y           NUMBER NULL,
       DISTANCE_UNIT        VARCHAR2(50) NULL,
       FEATURE_X            NUMBER NULL,
       FEATURE_Y            NUMBER NULL,
       ARRAY_DESIGN_ID      NUMBER NULL,
       PRIMARY KEY (MAGE_FEATURE_ID)  );

CREATE TABLE MAGE_FEATURE_REPORTER (
       MAGE_FEATURE_REPORTER_ID NUMBER NOT NULL,
       MAGE_REPORTER_ID     NUMBER NULL,
       MAGE_FEATURE_ID      NUMBER NULL,
       PRIMARY KEY (MAGE_FEATURE_REPORTER_ID)  );

CREATE TABLE MAGE_REPORTER (
       MAGE_REPORTER_ID     NUMBER NOT NULL,
       REPORTER_NAME        VARCHAR2(1024) NULL,
       WARNING_TYPE         VARCHAR2(1024) NULL,
       REPORTER_DESCRIPTION VARCHAR2(1024) NULL,
       PRIMARY KEY (MAGE_REPORTER_ID)  );

CREATE TABLE MAGE_REPORTER_BIOSEQUENCE (
       MAGE_REPORTER_ID     NUMBER NOT NULL,
       BIOSEQUENCE_ID       NUMBER NOT NULL,
       PRIMARY KEY (MAGE_REPORTER_ID, BIOSEQUENCE_ID)  );

CREATE TABLE MAGE_ZONE (
       MAGE_ZONE_ID         NUMBER NOT NULL,
       ZONE_X               NUMBER NULL,
       ZONE_Y               NUMBER NULL,
       ARRAY_DESIGN_ID      NUMBER NULL,
       PRIMARY KEY (MAGE_ZONE_ID)  );

--

ALTER TABLE BIOASSAY_DATUM ADD  (MAGE_REPORTER_ID NUMBER);
ALTER TABLE QUANTITATION_TYPE ADD (MESUREMENT_TYPE_CODE  CHAR(1) NULL);
ALTER TABLE BIOSEQUENCE ADD (IS_APPROXIMATE_LENGTH  NUMBER NULL,
                             SPECIES_ID             NUMBER NULL);
--

ALTER TABLE BIOASSAY_DATUM ADD  ( FOREIGN KEY (MAGE_REPORTER_ID)
  REFERENCES MAGE_REPORTER ) ;

ALTER TABLE BIOSEQUENCE_DBXREF
       ADD  ( FOREIGN KEY (DATABASE_REFERENCE_ID)
                             REFERENCES DATABASE_REFERENCE ) ;
ALTER TABLE BIOSEQUENCE_DESCRIPTION
       ADD  ( FOREIGN KEY (BIOSEQUENCE_ID)
                             REFERENCES BIOSEQUENCE ) ;
ALTER TABLE DESCRIPTION_ONTOLOGYENTRY
       ADD  ( FOREIGN KEY (BIOSEQUENCE_DESCRIPTION_ID)
                             REFERENCES BIOSEQUENCE_DESCRIPTION ) ;

ALTER TABLE MAGE_BIOSEQ_COMPOS_SEQ 
  ADD  ( FOREIGN KEY (MAGE_COMPOSITE_SEQUENCE_ID)
  REFERENCES MAGE_COMPOSITE_SEQUENCE ) ;

ALTER TABLE MAGE_BIOSEQ_COMPOS_SEQ ADD  ( FOREIGN KEY (BIOSEQUENCE_ID)
  REFERENCES BIOSEQUENCE ) ;


ALTER TABLE MAGE_FEATURE ADD  ( FOREIGN KEY (MAGE_ZONE_ID)
  REFERENCES MAGE_ZONE ) ;

ALTER TABLE MAGE_FEATURE_REPORTER ADD  ( FOREIGN KEY (MAGE_REPORTER_ID)
  REFERENCES MAGE_REPORTER ) ;

ALTER TABLE MAGE_FEATURE_REPORTER ADD  ( FOREIGN KEY (MAGE_FEATURE_ID)
  REFERENCES MAGE_FEATURE ) ;

ALTER TABLE MAGE_REPORTER_BIOSEQUENCE ADD  ( FOREIGN KEY (BIOSEQUENCE_ID)
  REFERENCES BIOSEQUENCE ) ;

ALTER TABLE MAGE_REPORTER_BIOSEQUENCE ADD  ( FOREIGN KEY (MAGE_REPORTER_ID)
  REFERENCES MAGE_REPORTER ) ;

ALTER TABLE MAGE_ZONE ADD  ( FOREIGN KEY (ARRAY_DESIGN_ID)
  REFERENCES ARRAY_DESIGN ) ;



CREATE INDEX  M_FEAT_REP_M_FEAT_ID ON MAGE_FEATURE_REPORTER(MAGE_FEATURE_ID);
CREATE INDEX  M_FEAT_REP_M_REP_ID ON MAGE_FEATURE_REPORTER(MAGE_REPORTER_ID);

CREATE DATABASE LINK cgapop CONNECT TO WEB IDENTIFIED BY READONLY
USING  'LPGPROD';

create table sage_tag2clu 
as select sagebest_tag2clu.tag, sagebest_tag2clu.cluster_number
from cgap.sagebest_tag2clu@cgapop;

create index sage_tag2clu_tag on sage_tag2clu(tag);
CREATE INDEX S_BIOASSAY_EXP_ID ON SAGE_BIOASSAY(experiment_id);
CREATE INDEX S_TAG_SEQ ON SAGE_BIOASSAY_DATUM (tag_sequence);

alter table database add (database_name varchar2(255));
UPDATE DATABASE SET DATABASE_NAME = 'GENBANK'
WHERE  DATABASE_URI = 'http://www.ncbi.nlm.nih.gov/Genbank/';

insert into database (database_id, database_uri, database_name)
values (2, 'http://www.ncbi.nlm.nih.gov/Genbank/', 'GENBANK');

insert into database (database_id, database_uri, database_name)
values (3, 'http://www.tigr.org/', 'TIGR');

commit;

insert into sample_types (sample_type_id, sample_type_name) 
values (4, 'mRNA');
insert into sample_types (sample_type_id, sample_type_name) 
values (5, 'Amplifed RNA');
insert into sample_types (sample_type_id, sample_type_name) 
values (6, 'Total RNA');
insert into sample_types (sample_type_id, sample_type_name) 
values (7, 'DNA (CGH)');

insert into platform_types (platform_type_id, platform_type_name) 
values (11, 'cDNA - Spotted');
insert into platform_types (platform_type_id, platform_type_name) 
values (12, 'Long Oligo - Spotted');
insert into platform_types (platform_type_id, platform_type_name) 
values (13, 'BAC - Spotted');

update dc_sequences set last_number = last_number + 1
where  sequence_name  = 'software_id'
and    seq_table_name = 'software';

commit;

insert into software (software_id, software_type, software_name)
select last_number, 'Image Processing', 'UCSF SPOT'
from   dc_sequences
where  sequence_name  = 'software_id'
and    seq_table_name = 'software';

commit;



--
--  remember to run dc_sequences update script.
--

prompt remember to run dc_sequences update script.
