/* ensembl100_Oracle.sql
 *
 * Host: localhost    Database: Sequence
 *--------------------------------------------------------
 * Server version	3.23.25-beta
 */


/*
 * Table structure for table 'species'
 */

CREATE TABLE species (
  species_id number(10) NOT NULL,
  nickname varchar2(40) NOT NULL,
  taxonomy_id number(10) DEFAULT '0' NOT NULL,
  CONSTRAINT PK_SPECIES_SPECIES_ID PRIMARY  KEY (species_id)
);



/*
 * Table structure for table 'externalDB'
 */

CREATE TABLE externalDB (
  externalDBId number(11) NOT NULL,
  db_name varchar2(40) NOT NULL,
  release varchar2(40),
  url_pattern varchar2(255),
  CONSTRAINT PK_EXTERNALDBID_EXTERNALDBID PRIMARY KEY (externalDBId)
);



/*
 * Table structure for table 'Xref'
 */

CREATE TABLE Xref (
  xrefId number(11) NOT NULL,
  externalDBId number(11) DEFAULT '0',
  dbprimary_id varchar2(40) NOT NULL,
  display_id varchar2(40) NOT NULL,
  version varchar2(10),
  description varchar2(255),
  CONSTRAINT PK_XREF_XREFID PRIMARY KEY (xrefId),
  CONSTRAINT FK_XREF_EXTERNALDBID FOREIGN KEY (externalDBId) REFERENCES externalDB(externalDBId)
);


/*
 * create sequence and trigger to automatically increase the id number
 */

CREATE SEQUENCE xrefId_seq
increment by 1
start with 1
maxvalue 9999999999
nocycle;

CREATE OR REPLACE TRIGGER xrefId_tri 
  BEFORE INSERT ON Xref FOR EACH ROW
BEGIN
  SELECT xrefId_seq.NEXTVAL INTO :new.xrefId FROM DUAL;
END;
/

CREATE INDEX Xref_idIdx on Xref (dbprimary_id);
CREATE INDEX Xref_idx1 on Xref (display_id);


/*
 * Table structure for table 'analysis'
 */

CREATE TABLE analysis (
  id number(10) NOT NULL,
  db varchar2(40),
  db_version varchar2(5),
  program varchar2(40) NOT NULL,
  program_version varchar2(5),
  gff_source varchar2(40),
  gff_feature varchar2(40),
  CONSTRAINT PK_ANALYSIS_ID PRIMARY KEY (id)
);



PROMPT Table structure for table 'analysisprocess';

CREATE TABLE analysisprocess (
  analysisId number(10) NOT NULL,
  created date  NOT NULL,
  logic_name varchar2(40) NOT NULL,
  db varchar2(40),
  db_version varchar2(40),
  db_file varchar2(80),
  program varchar2(40),
  program_version varchar2(40),
  program_file varchar2(40),
  parameters varchar2(80),
  module varchar2(80),
  module_version varchar2(40),
  gff_source varchar2(40),
  gff_feature varchar2(40),
  CONSTRAINT PK_ANALYSISPROCESS_ANALYSISID PRIMARY KEY (analysisId)
);

/*
CREATE SEQUENCE analysisId_seq
increment by 1
start with 1
maxvalue 999999999
nocycle;

CREATE OR REPLACE TRIGGER analysisId_tri 
 BEFORE INSERT ON analysisprocess FOR EACH ROW
BEGIN
 SELECT analysisId_seq.NEXTVAL INTO :new.analysisId FROM DUAL;
END;
/
*/

PROMPT Table structure for table 'chromosome'

CREATE TABLE chromosome (
  chromosome_id number(10) NOT NULL,
  name varchar2(40) NOT NULL,
  species_id number(11) DEFAULT '0' NOT NULL,
  id number(11) DEFAULT '0' NOT NULL,
  known_genes number(11),
  unknown_genes number(11),
  snps number(11),
  length number(11),
  CONSTRAINT PK_CHROMOSOME_CHROMOSOME_ID PRIMARY KEY (chromosome_id),
  CONSTRAINT FK_CHROMOSOME_SPECIES_ID FOREIGN KEY (species_id) REFERENCES species(species_id)
);


/*
 * Table structure for table 'clone'
 */

CREATE TABLE clone (
  internal_id number(10) NOT NULL,
  id varchar2(40) NOT NULL,
  embl_id varchar2(40) NOT NULL,
  gi number(10) NOT NULL,
  name varchar2(40) NOT NULL,
  version number(10) DEFAULT '0' NOT NULL,
  embl_version number(10) DEFAULT '0' NOT NULL,
  htg_phase number(10) DEFAULT '-1' NOT NULL,
  created date  NOT NULL,
  modified date  NOT NULL,
  stored date  NOT NULL,
  site varchar2(40) NOT NULL,
  CONSTRAINT PK_CLONE_INTERNAL_ID PRIMARY KEY (internal_id)
);

CREATE SEQUENCE clone_internal_id_seq
increment by 1
start with 1
maxvalue 999999999
nocycle;

CREATE OR REPLACE TRIGGER clone_internal_id_tri
 BEFORE INSERT ON clone FOR EACH ROW
BEGIN
 SELECT clone_internal_id_seq.NEXTVAL INTO :new.internal_id FROM DUAL;
END;
/

CREATE OR REPLACE TRIGGER clone_stored_tri 
  BEFORE INSERT ON clone FOR EACH ROW
BEGIN
  :new.stored := sysdate;
END;
/

/*
CREATE UNIQUE INDEX clone_embl on clone(embl_id,embl_version);
CREATE UNIQUE INDEX clone_id on clone(id,embl_version);
*/
CREATE INDEX clone_embl on clone(embl_id,embl_version);
CREATE INDEX clone_id on clone(id,embl_version);

CREATE INDEX clone_gi on clone(gi);
CREATE INDEX clone_name on clone(name,version);



/*
 * Table structure for table 'dna'
 */

CREATE TABLE dna (
  id number(10) NOT NULL,
  orcl_sequence clob NOT NULL,
  length number(11) NOT NULL,
  created date  NOT NULL,
  CONSTRAINT PK_DNA_ID PRIMARY KEY (id)
);

CREATE SEQUENCE dna_id_seq
increment by 1
start with 1
maxvalue 999999999
nocycle;

CREATE OR REPLACE TRIGGER dna_id_tri 
 BEFORE INSERT ON dna FOR EACH ROW
BEGIN
 SELECT dna_id_seq.NEXTVAL INTO :new.id FROM DUAL;
END;
/


/*
 * Table structure for table 'contig'
 */

CREATE TABLE contig (
  internal_id number(10) NOT NULL,
  id varchar2(40) NOT NULL,
  clone number(10) DEFAULT '0' NOT NULL,
  length number(10) DEFAULT '0' NOT NULL,
  offset number(10),
  corder number(10),
  dna number(10) DEFAULT '0' NOT NULL,
  chromosomeId number(10) DEFAULT '0' NOT NULL,
  international_id varchar2(40),
  CONSTRAINT PK_CONTIG_INTERNAL_ID PRIMARY KEY (internal_id),
  CONSTRAINT FK_CONTIG_CLONE FOREIGN KEY (clone) REFERENCES clone(internal_id),
  CONSTRAINT FK_CONTIG_DNA FOREIGN KEY (dna) REFERENCES dna(id),
  CONSTRAINT FK_CONTIG_CHROMOSOMEID FOREIGN KEY (chromosomeId) REFERENCES chromosome(chromosome_id)
);

CREATE SEQUENCE contig_internal_id_seq
increment by 1
start with 1
maxvalue 999999999
nocycle;

CREATE OR REPLACE TRIGGER contig_internal_id_tri 
 BEFORE INSERT ON contig FOR EACH ROW
BEGIN
 SELECT contig_internal_id_seq.NEXTVAL INTO :new.internal_id FROM DUAL;
END;
/

/*
CREATE UNIQUE INDEX contig_id on contig(id);
*/
CREATE INDEX contig_id on contig(id);
CREATE INDEX contig_clone on contig(clone);
CREATE INDEX contig_dna on contig(dna);
CREATE INDEX contig_length on contig(length);


/*
 * Table structure for table 'contig_landmarkMarker'
 */

CREATE TABLE contig_landmarkMarker (
  contig number(10) DEFAULT '0' NOT NULL,
  marker varchar2(40) NOT NULL,
  name varchar2(40) NOT NULL,
  orcl_start number(17) DEFAULT '0' NOT NULL,
  end number(17) DEFAULT '0' NOT NULL,
  strand number(1) DEFAULT '0' NOT NULL,
  chr_name char(20) NOT NULL,
  CONSTRAINT FK_CONTIGLANDMARKMARKER_CONTIG FOREIGN KEY (contig) REFERENCES contig(internal_id)
);

CREATE INDEX contig_marker on contig_landmarkMarker(marker);
CREATE INDEX contig_chr on contig_landmarkMarker(chr_name);
CREATE INDEX contigIdx on contig_landmarkMarker(contig);


/*
 * Table structure for table 'contig_landmarker1'
 */

CREATE TABLE contig_landmarker1 (
  contig number(10) DEFAULT '0' NOT NULL,
  marker varchar2(40) NOT NULL,
  name varchar2(40) NOT NULL,
  orcl_start number(17) DEFAULT '0' NOT NULL,
  end number(17) DEFAULT '0' NOT NULL,
  strand number(1) DEFAULT '0' NOT NULL,
  chr_name varchar2(20) NOT NULL,
  CONSTRAINT FK_CONTIGLANDMARKER1_CONTIG FOREIGN KEY (contig) REFERENCES contig(internal_id)
);

CREATE INDEX contig1_chr on contig_landmarker1(chr_name);



/*
 * Table structure for table 'current_status'
 */

CREATE TABLE current_status (
  jobId number(10) DEFAULT '0' NOT NULL,
  status varchar2(40) NOT NULL,
  CONSTRAINT PK_CURRENTSTATUS_JOBID PRIMARY KEY (jobId)
);

CREATE INDEX status_index on current_status(status);


/*
 * Table structure for table 'exon'
 */

CREATE TABLE exon (
  id varchar2(40) NOT NULL,
  contig number(10) DEFAULT '0' NOT NULL,
  version number(10) DEFAULT '1' NOT NULL,
  created date  NOT NULL,
  modified date  NOT NULL,
  stored date  NOT NULL,
  seq_start number(10) DEFAULT '0' NOT NULL,
  seq_end number(10) DEFAULT '0' NOT NULL,
  strand number(2) DEFAULT '0' NOT NULL,
  phase number(11) DEFAULT '0' NOT NULL,
  end_phase number(11) DEFAULT '0' NOT NULL,
  sticky_rank number(10) DEFAULT '1' NOT NULL,
  CONSTRAINT PK_EXON_ID PRIMARY KEY (id),
  CONSTRAINT FK_EXON_CONTIG FOREIGN KEY (contig) REFERENCES contig(internal_id)
);

CREATE OR REPLACE TRIGGER exon_stored_tri 
  BEFORE INSERT OR UPDATE ON exon FOR EACH ROW
BEGIN
  :new.stored := sysdate;
END;
/

CREATE INDEX exon_id_contig on exon(id,contig);
CREATE INDEX exon_contig on exon(contig);


/*
 * Table structure for table 'gene'
 */

CREATE TABLE gene (
  id varchar2(40) NOT NULL,
  version number(10) DEFAULT '1' NOT NULL,
  created date  NOT NULL,
  modified date  NOT NULL,
  stored date  NOT NULL,
  analysisId number(11),
  CONSTRAINT PK_GENE_ID PRIMARY KEY (id),
  CONSTRAINT FK_GENE_ANALYSISID FOREIGN KEY (analysisId) REFERENCES analysis(id)
);

CREATE OR REPLACE TRIGGER gene_stored_tri 
  BEFORE INSERT ON gene FOR EACH ROW
BEGIN
  :new.stored := sysdate;
END;
/


/*
 * Table structure for table 'translation'
 */

CREATE TABLE translation (
  id varchar2(40) NOT NULL,
  version number(10) DEFAULT '1' NOT NULL,
  seq_start number(10) DEFAULT '0' NOT NULL,
  start_exon varchar2(40) NOT NULL,
  seq_end number(10) DEFAULT '0' NOT NULL,
  end_exon varchar2(40) NOT NULL,
  CONSTRAINT PK_TRANSLATION_ID PRIMARY KEY (id)
);



/*
 * Table structure for table 'transcript'
 */

CREATE TABLE transcript (
  id varchar2(40) NOT NULL,
  version number(10) DEFAULT '1' NOT NULL,
  gene varchar2(40) NOT NULL,
  translation varchar2(40) NOT NULL,
  CONSTRAINT PK_TRANSCRIPT_ID PRIMARY KEY (id),
  CONSTRAINT FK_TRANSCRIPT_TRANSLATION FOREIGN KEY (translation) REFERENCES translation(id) deferrable,
  CONSTRAINT FK_TRANSCRIPT_GENE FOREIGN KEY (gene) REFERENCES gene(id) deferrable
);

CREATE INDEX transcript_gene on transcript(gene);



/*
 * Table structure for table 'exon_transcript'
 */

CREATE TABLE exon_transcript (
  exon varchar2(40) NOT NULL,
  transcript varchar2(40) NOT NULL,
  rank number(10) DEFAULT '0' NOT NULL,
  CONSTRAINT PK_EXONTRANSCRIPT_ALL PRIMARY KEY (exon,transcript,rank),
  CONSTRAINT FK_EXONTRANSCRIPT_EXON FOREIGN KEY (exon) REFERENCES exon(id) deferrable,
  CONSTRAINT FK_EXONTRANSCRIPT_TRANSCRIPT FOREIGN KEY (transcript) REFERENCES transcript(id) deferrable 
);

CREATE INDEX transcriptIdx on exon_transcript(transcript);
CREATE INDEX exonIdx on exon_transcript(exon);



/*
 * Table structure for table 'externalSynonym'
 */

CREATE TABLE externalSynonym (
  xrefId number(11) DEFAULT '0' NOT NULL,
  orcl_synonym varchar2(40) NOT NULL,
  CONSTRAINT PK_EXTERNALSYNONYM_ALL PRIMARY KEY (xrefId,orcl_synonym),
  CONSTRAINT FK_EXTERNALSYNONYM_XREFID FOREIGN KEY (xrefId) REFERENCES xref(xrefId)
);

CREATE INDEX nameIdx on externalSynonym (orcl_synonym);


/*
 * Table structure for table 'feature'
 */

CREATE TABLE feature (
  id number(10) NOT NULL,
  contig number(10) DEFAULT '0' NOT NULL,
  seq_start number(10) DEFAULT '0' NOT NULL,
  seq_end number(10) DEFAULT '0' NOT NULL,
  score number(16,4) DEFAULT '0.0000' NOT NULL,
  strand number(1) DEFAULT '1' NOT NULL,
  analysis number(10) DEFAULT '0' NOT NULL,
  name varchar2(40),
  hstart number(11) DEFAULT '0' NOT NULL,
  hend number(11) DEFAULT '0' NOT NULL,
  hid varchar2(40) NOT NULL,
  evalue varchar2(10),
  perc_id number(10),
  phase number(1),
  end_phase number(1),
  CONSTRAINT PK_FEATURE_ID PRIMARY KEY (id),
  CONSTRAINT FK_FEATURE_CONTIG FOREIGN KEY (contig) REFERENCES contig(internal_id),
  CONSTRAINT FK_FEATURE_ANALYSIS FOREIGN KEY (analysis) REFERENCES analysis(id)
);

/*
CREATE SEQUENCE feature_id_seq
increment by 1
start with 1
maxvalue 999999999
nocycle;

CREATE OR REPLACE TRIGGER feature_id_tri 
 BEFORE INSERT ON feature FOR EACH ROW
BEGIN
 SELECT feature_id_seq.NEXTVAL INTO :new.id FROM DUAL;
END;
/
*/

CREATE INDEX feature_overlap on feature(id,contig,seq_start,seq_end,analysis);
CREATE INDEX feature_contig on feature(contig);
CREATE INDEX feature_hid on feature(hid);
CREATE INDEX feature_analysis on feature(analysis);


/*
 * Table structure for table 'fset'
 */

CREATE TABLE fset (
  id number(10) NOT NULL,
  score number(16,4) DEFAULT '0.0000' NOT NULL,
  CONSTRAINT PK_FSET_ID PRIMARY KEY (id)
);

/*
CREATE SEQUENCE fset_id_seq
increment by 1
start with 1
maxvalue 999999999
nocycle;

CREATE OR REPLACE TRIGGER fset_id_tri 
 BEFORE INSERT ON fset FOR EACH ROW
BEGIN
 SELECT fset_id_seq.NEXTVAL INTO :new.id FROM DUAL;
END;
/
*/

/*
 * Table structure for table 'fset_feature'
 */

CREATE TABLE fset_feature (
  feature number(10) DEFAULT '0' NOT NULL,
  fset number(10) DEFAULT '0' NOT NULL,
  rank number(11) DEFAULT '0' NOT NULL,
  CONSTRAINT PK_FSETFEATURE_ALL PRIMARY KEY (feature,fset,rank),
  CONSTRAINT FK_FSETFEATURE_FSET FOREIGN KEY (fset) REFERENCES fset(id)
);

CREATE INDEX fsetIdx on fset_feature(fset);



/*
 * Table structure for table 'gene_description'
 */

CREATE TABLE gene_description (
  gene_id varchar2(40) NOT NULL,
  description varchar2(4000),
  CONSTRAINT PK_GENEDESCRIPTION_GENE_ID PRIMARY KEY (gene_id)
);


/*
 * Table structure for table 'genetype'
 */

CREATE TABLE genetype (
  gene_id varchar2(40) NOT NULL,
  type varchar2(40) NOT NULL,
  CONSTRAINT PK_GENETYPE_ALL PRIMARY KEY (gene_id,type)
);


/*
 * Table structure for table 'interpro'
 */

CREATE TABLE interpro (
  interpro_ac varchar2(40) NOT NULL,
  id varchar2(40) NOT NULL,
  CONSTRAINT PK_INTERPRO_INTERPRO_AC PRIMARY KEY (interpro_ac)
);

CREATE INDEX interpro_ac_id on interpro(interpro_ac,id);
CREATE INDEX interpro_id_ac on interpro(id,interpro_ac);


PROMPT Table structure for table 'interpro_description'

CREATE TABLE interpro_description (
  interpro_ac varchar2(40) NOT NULL,
  description varchar2(255),
  short_description varchar(255),
  CONSTRAINT PK_INTERPRODESCRIPTION PRIMARY KEY (interpro_ac)
);



/*
 * Table structure for table 'karyotype'
 */

CREATE TABLE karyotype (
  chr_name varchar2(40) NOT NULL,
  chr_start number(10) DEFAULT '0' NOT NULL,
  chr_end number(10) DEFAULT '0' NOT NULL,
  band varchar2(40) NOT NULL,
  stain varchar2(40) NOT NULL,
  CONSTRAINT PK_KARYOTYPE_MULTI PRIMARY KEY (chr_name,band)
);


/*
 * Table structure for table 'map_density'
 */

CREATE TABLE map_density (
  chromosome_id number(10) DEFAULT '0' NOT NULL,
  chr_start number(10) DEFAULT '0' NOT NULL,
  chr_end number(10) DEFAULT '0' NOT NULL,
  type varchar2(20) NOT NULL,
  value number(10) DEFAULT '0' NOT NULL,
  CONSTRAINT PK_MAPDENSITY_MULTI PRIMARY KEY (chromosome_id,chr_start,chr_end,type)
);


/*
 * Table structure for table 'meta'
 */

CREATE TABLE meta (
  meta_id number(11) NOT NULL,
  meta_key varchar( 40 ) not null,
  meta_value varchar( 255 ) not null,
  CONSTRAINT PK_META_META_ID PRIMARY KEY( meta_id )
);

CREATE SEQUENCE meta_id_seq
increment by 1
start with 1
maxvalue 999999999
nocycle;

CREATE OR REPLACE TRIGGER meta_id_tri 
 BEFORE INSERT ON meta FOR EACH ROW
BEGIN
 SELECT meta_id_seq.NEXTVAL INTO :new.meta_id FROM DUAL;
END;
/

CREATE INDEX meta_key_index on meta (meta_key);
CREATE INDEX meta_value_index on meta (meta_value);



/*
 * Table structure for table 'objectXref'
 */

CREATE TABLE objectXref (
  ensembl_id varchar2(40) NOT NULL,
  ensembl_object_type varchar2(80) DEFAULT 'RawContig' NOT NULL,
  xrefId number(11) DEFAULT '0' NOT NULL,
  CONSTRAINT PK_OBJECTXREF_ALL PRIMARY KEY (ensembl_object_type,ensembl_id,xrefId),
  CONSTRAINT FK_OBJECTXREF_XREFID FOREIGN KEY (xrefId) REFERENCES xref(xrefId)
);

CREATE INDEX  xrefIdx on objectXref (xrefId,ensembl_object_type,ensembl_id);



/*
 * Table structure for table 'protein_feature'
 */

CREATE TABLE protein_feature (
  id number(10) NOT NULL,
  translation varchar2(40) NOT NULL,
  seq_start number(10) DEFAULT '0' NOT NULL,
  seq_end number(10) DEFAULT '0' NOT NULL,
  analysis number(10) DEFAULT '0' NOT NULL,
  hstart number(10) DEFAULT '0' NOT NULL,
  hend number(10) DEFAULT '0' NOT NULL,
  hid varchar2(40) NOT NULL,
  score number(16,4) DEFAULT '0.0000' NOT NULL,
  evalue number(16,4),
  perc_id number(10),
  CONSTRAINT PK_PROTEINFEATURE_ID PRIMARY KEY (id),
  CONSTRAINT FK_PROTEINFEATURE_ANALYSIS FOREIGN KEY (analysis) REFERENCES analysis(id),
  CONSTRAINT FK_PROTEINFEATURE_TRANSLATION FOREIGN KEY (translation) REFERENCES translation(id) deferrable
);

CREATE SEQUENCE protein_feature_id_seq
increment by 1
start with 1
maxvalue 999999999
nocycle;

CREATE OR REPLACE TRIGGER protein_feature_id_tri 
 BEFORE INSERT ON protein_feature FOR EACH ROW
BEGIN
 SELECT protein_feature_id_seq.NEXTVAL INTO :new.id FROM DUAL;
END;
/

CREATE INDEX protein_feature_translation on protein_feature(translation);
CREATE INDEX protein_feature_hid on protein_feature(hid,translation);
CREATE INDEX protein_feature_translation_2 on protein_feature(translation,hid);
 


/*
 * Table structure for table 'repeat_feature'
 */

CREATE TABLE repeat_feature (
  id number(10) NOT NULL,
  contig number(10) DEFAULT '0' NOT NULL,
  seq_start number(10) DEFAULT '0' NOT NULL,
  seq_end number(10) DEFAULT '0' NOT NULL,
  score number(16,4) DEFAULT '0.0000' NOT NULL,
  strand number(1) DEFAULT '1' NOT NULL,
  analysis number(10) DEFAULT '0' NOT NULL,
  hstart number(11) DEFAULT '0' NOT NULL,
  hend number(11) DEFAULT '0' NOT NULL,
  hid varchar2(40) NOT NULL,
  CONSTRAINT PK_REPEATFEATURE_ID PRIMARY KEY (id),
  CONSTRAINT FK_REPEATFEATURE_CONTIG FOREIGN KEY (contig) REFERENCES contig(internal_id)
);

CREATE SEQUENCE repeat_feature_id_seq
increment by 1
start with 1
maxvalue 999999999
nocycle;

CREATE OR REPLACE TRIGGER repeat_feature_id_tri 
 BEFORE INSERT ON repeat_feature FOR EACH ROW
BEGIN
 SELECT repeat_feature_id_seq.NEXTVAL INTO :new.id FROM DUAL;
END;
/

CREATE INDEX repeat_feature_overlap on repeat_feature(id,contig,seq_start,seq_end,analysis);
CREATE INDEX repeat_feature_contig on repeat_feature(contig);
CREATE INDEX repeat_feature_hid on repeat_feature(hid);



PROMPT Table structure for table 'static_golden_path';

CREATE TABLE static_golden_path (
  fpcctg_name varchar2(20) NOT NULL,
  chr_name varchar2(20) NOT NULL,
  raw_id number(10) DEFAULT '0' NOT NULL,
  chr_start number(10) DEFAULT '0' NOT NULL,
  chr_end number(10) DEFAULT '0' NOT NULL,
  fpcctg_start number(10) DEFAULT '0' NOT NULL,
  fpcctg_end number(10) DEFAULT '0' NOT NULL,
  raw_start number(10) DEFAULT '0' NOT NULL,
  raw_end number(10) DEFAULT '0' NOT NULL,
  raw_ori number(2) DEFAULT '0' NOT NULL,
  type varchar2(20) NOT NULL,
  CONSTRAINT PK_STATICGOLDENPATH_MULTI PRIMARY KEY (raw_id,type),
  CONSTRAINT FK_STATICGOLDENPATH_CONTIG FOREIGN KEY (raw_id) REFERENCES contig(internal_id)
);

CREATE INDEX fpcctg_name on static_golden_path(fpcctg_name);
CREATE INDEX chr_name on static_golden_path(chr_name);
 

/*
 * Table structure for table 'supporting_feature'
 */

CREATE TABLE supporting_feature (
  id number(10) NOT NULL,
  exon varchar2(40) NOT NULL,
  seq_start number(10) DEFAULT '0' NOT NULL,
  seq_end number(10) DEFAULT '0' NOT NULL,
  score number(10) DEFAULT '0' NOT NULL,
  strand number(1) DEFAULT '1' NOT NULL,
  analysis number(10) DEFAULT '0' NOT NULL,
  name varchar2(40) NOT NULL,
  hstart number(11) DEFAULT '0' NOT NULL,
  hend number(11) DEFAULT '0' NOT NULL,
  hid varchar2(40) NOT NULL,
  evalue number(16,4),
  perc_id number(10),
  phase number(1),
  end_phase number(1),
  CONSTRAINT PK_SUPPORTINGFEATURE_ID PRIMARY KEY (id),
  CONSTRAINT FK_SUPPORTINGFEATURE_ANALYSIS FOREIGN KEY (analysis) REFERENCES analysis(id),
  CONSTRAINT FK_SUPPORTING_FEATURE_EXON FOREIGN KEY (exon) REFERENCES exon(id)
);

CREATE SEQUENCE supporting_feature_id_seq
increment by 1
start with 1
maxvalue 999999999
nocycle;

CREATE OR REPLACE TRIGGER supporting_feature_id_tri 
 BEFORE INSERT ON supporting_feature FOR EACH ROW
BEGIN
 SELECT supporting_feature_id_seq.NEXTVAL INTO :new.id FROM DUAL;
END;
/

CREATE INDEX supporting_feature_overlap on supporting_feature(id,seq_start,seq_end,analysis);
CREATE INDEX supporting_feature_id_exon on supporting_feature(id,exon);
CREATE INDEX supporting_feature_exon on supporting_feature(exon);
CREATE INDEX supporting_feature_analysis on supporting_feature(analysis);
CREATE INDEX supporting_feature_hid on supporting_feature(hid);
CREATE INDEX supporting_feature_name on supporting_feature(name);  



/*
 * Table structure for table 'sequencing_site'
 */
CREATE TABLE sequencing_site (
  id 		varchar2(20) NOT NULL,
  fullname	varchar2(80) NOT NULL,
  address	varchar2(160) NOT NULL,
  home_page     varchar2(80) NOT NULL,
  director	varchar2(80),
  CONSTRAINT PK_SEQUENCINGSITE_ID PRIMARY KEY (id)
);



CREATE TABLE marker_clone (
  id number(10) NOT NULL ,
  clone varchar2(40)  NOT NULL,
  clone_version number(10),
  clone_start number(10) DEFAULT '0' NOT NULL,
  clone_end number(10) DEFAULT '0' NOT NULL,
  strand number(1) DEFAULT '1' NOT NULL,
  score number(16,4) DEFAULT '0.0000' NOT NULL,
  analysis number(10) DEFAULT '600' NOT NULL,
  program_name varchar2(40),
  marker_start number(11) DEFAULT '0' NOT NULL,
  marker_end number(11) DEFAULT '0' NOT NULL,
  marker varchar2(40) DEFAULT '' NOT NULL,
  marker_accession varchar2(40),
  evalue varchar2(10),
  perc_id number(10),
  PRIMARY KEY (id)
);

create index marker_clone_marker on marker_clone(marker);
create index marker_clone_clone on marker_clone(clone);
create index marker_clone_accession on marker_clone(marker_accession);

CREATE TABLE genbank_translation (
    transcript_id 	varchar2(40) DEFAULT '' NOT NULL,
    genbank_id 		varchar2(40) DEFAULT '' NOT NULL,
    translation 	varchar2(4000) ,
    PRIMARY KEY(transcript_id)
) NOLOGGING ;

CREATE TABLE translation_ok (
    transcript_id 	varchar2(40) DEFAULT '' NOT NULL,
    ok			NUMBER(1)    DEFAULT 0  NOT NULL,
    PRIMARY KEY(transcript_id)
) NOLOGGING ;
COMMENT ON COLUMN  translation_ok.ok IS '2=ok, 3=bad';
CREATE TABLE helpview (
  kw varchar2(40) DEFAULT '' NOT NULL,
  title varchar2(255) DEFAULT '' NOT NULL,
  content varchar2(4000) NOT NULL,
  piece   number(3) NOT NULL,
  PRIMARY KEY (kw,piece)
);

