# conventions:
# <table_name>_id is primary internal id (usually autogenerated)

# author Ewan Birney 
# comments to bioperl - bioperl-l@bioperl.org

# database have bioentries. That is about it.
# we do not store different versions of a database as different dbids
# (there is no concept of versions of database). There is a concept of
# versions of entries. Versions of databases deserve their own table and
# join to bioentry table for tracking with versions of entries 


CREATE TABLE biodatabase (
  biodatabase_id int(10) unsigned NOT NULL PRIMARY KEY auto_increment,
  name        varchar(40) NOT NULL
);
CREATE INDEX biodatabaseidx1 on biodatabase(name);

# we could insist that taxa are NCBI taxa id, but on reflection I made this
# an optional extra line, as many flat file formats do not have the NCBI id

# full lineage is : delimited string starting with species.

# no organelle/sub species

CREATE TABLE taxa (
  taxa_id   int(10) unsigned NOT NULL PRIMARY KEY auto_increment,
  full_lineage mediumtext NOT NULL,
  common_name varchar(255) NOT NULL,
  ncbi_taxa_id int(10)
  
);
CREATE INDEX taxancbi ON taxa(ncbi_taxa_id);
CREATE INDEX taxaname ON taxa(common_name);


# any controlled vocab term, everything from full ontology
# terms eg GO IDs to the various keys allowed as qualifiers
#
# this replaces the table "seqfeature_qualifier"
CREATE TABLE ontology_term (
       ontology_term_id int(10) unsigned NOT NULL PRIMARY KEY auto_increment,
       term_name        char(255),
       term_definition  mediumtext
);
CREATE INDEX otn ON ontology_term(term_name);

# we can be a bioentry without a biosequence, but not visa-versa
# most things are going to be keyed off bioentry_id

# accession is the stable id, display_id is a potentially volatile,
# human readable name.


CREATE TABLE bioentry (
  bioentry_id  int(10) unsigned NOT NULL PRIMARY KEY auto_increment,
  biodatabase_id  int(10) NOT NULL,
  display_id   varchar(40) NOT NULL,
  accession    varchar(40) NOT NULL,
  entry_version int(10), 
  division     varchar(3) NOT NULL,
  UNIQUE (biodatabase_id,accession,entry_version, division),
  FOREIGN KEY (biodatabase_id) REFERENCES biodatabase(biodatabase_id)
);
CREATE INDEX bioentrydbid ON bioentry(biodatabase_id);
CREATE INDEX bioentrydid  ON bioentry(display_id);
CREATE INDEX bioentryacc  ON bioentry(accession);


# not all entries have a taxa, but many do.
# one bioentry only has one taxa! (weirdo chimerias are not handled. tough)

CREATE TABLE bioentry_taxa (
  bioentry_id int(10)  NOT NULL,
  taxa_id     int(10)  NOT NULL,
  FOREIGN KEY (bioentry_id) REFERENCES bioentry(bioentry_id),
  PRIMARY KEY(bioentry_id)
);
CREATE INDEX bioentrytax  ON bioentry_taxa(taxa_id);

# some bioentries will have a sequence
# biosequence because sequence is sometimes 
# a reserved word
# removed not null for seq_version; cjm

CREATE TABLE biosequence (
  biosequence_id  int(10) unsigned NOT NULL PRIMARY KEY auto_increment,
  bioentry_id     int(10) NOT NULL,
  seq_version     int(6), 
  seq_length      int(10), 
  biosequence_str mediumtext,
  molecule        varchar(10),
  FOREIGN KEY (bioentry_id) REFERENCES bioentry(bioentry_id),
  UNIQUE(bioentry_id)
);
CREATE INDEX biosequenceeid  ON biosequence(bioentry_id);

# new table
CREATE TABLE dbxref (
        dbxref_id  int(10) unsigned NOT NULL PRIMARY KEY auto_increment,
        dbname                  varchar(40) NOT NULL,
        accession               varchar(40) NOT NULL,
        UNIQUE(dbname, accession)
);
CREATE INDEX dbxrefdbn  ON dbxref(dbname);
CREATE INDEX dbxrefacc  ON dbxref(accession);

# new table
# for roundtripping embl/genbank, we need to have the "optional ID"
# for the dbxref.
#
# another use of this table could be for storing
# descriptive text for a dbxref. for example, we may want to
# know stuff about the interpro accessions we store (without
# importing all of interpro), so we can attach the text
# description as a synonym
#
CREATE TABLE dbxref_qualifier_value (
       dbxref_qualifier_value_id  int(10) unsigned NOT NULL PRIMARY KEY auto_increment,
       dbxref_id               int(10) NOT NULL,
       FOREIGN KEY (dbxref_id) REFERENCES dbxref(dbxref_id),
       ontology_term_id  int(10) unsigned NOT NULL,
       FOREIGN KEY(ontology_term_id) REFERENCES ontology_term(ontology_term_id),
       qualifier_value             mediumtext
);
CREATE INDEX dqv1  ON dbxref_qualifier_value(dbxref_id);
CREATE INDEX dqv2  ON dbxref_qualifier_value(ontology_term_id);

# Direct links. It is tempting to do this
# from bioentry_id to bioentry_id. But that wont work
# during updates of one database - we will have to edit
# this table each time. Better to do the join through accession
# and db each time. Should be almost as cheap

# note: changed to use new dbxref table
CREATE TABLE bioentry_direct_links (
       bio_dblink_id           int(10) unsigned NOT NULL PRIMARY KEY auto_increment,
       source_bioentry_id      int(10) NOT NULL,
       dbxref_id               int(10) NOT NULL,
       FOREIGN KEY (source_bioentry_id) REFERENCES bioentry(bioentry_id),
       FOREIGN KEY (dbxref_id) REFERENCES dbxref(dbxref_id)
);
CREATE INDEX bdl1  ON bioentry_direct_links(source_bioentry_id);
CREATE INDEX bdl2  ON bioentry_direct_links(dbxref_id);

#We can have multiple references per bioentry, but one reference
#can also be used for the same bioentry.

CREATE TABLE reference (
  reference_id       int(10) unsigned NOT NULL PRIMARY KEY auto_increment,
  reference_location mediumtext NOT NULL,
  reference_title    mediumtext,
  reference_authors  mediumtext NOT NULL,
  reference_medline  int(10)
);

CREATE INDEX medlineidx ON reference(reference_medline);

CREATE TABLE bioentry_reference (
  bioentry_id int(10) unsigned NOT NULL,
  reference_id int(10) unsigned NOT NULL,
  reference_start    int(10),
  reference_end      int(10),
  reference_rank int(5) unsigned NOT NULL,

  PRIMARY KEY(bioentry_id,reference_id,reference_rank),
  FOREIGN KEY(bioentry_id) REFERENCES bioentry(bioentry_id),
  FOREIGN KEY(reference_id) REFERENCES reference(reference_id)
);
CREATE INDEX reference_rank_idx ON bioentry_reference(reference_rank);
CREATE INDEX reference_rank_idx2 ON bioentry_reference(bioentry_id);
CREATE INDEX reference_rank_idx3 ON bioentry_reference(reference_id);
CREATE INDEX reference_rank_idx4 ON bioentry_reference(reference_rank);
CREATE INDEX reference_rank_idx5 ON bioentry_reference(bioentry_id, reference_rank);


# We can have multiple comments per seqentry, and
# comments can have embedded '\n' characters

CREATE TABLE comment (
  comment_id  int(10) unsigned NOT NULL PRIMARY KEY auto_increment,
  bioentry_id    int(10) NOT NULL,
  comment_text   mediumtext NOT NULL,
  comment_rank   int(5) NOT NULL,
  FOREIGN KEY(bioentry_id) REFERENCES bioentry(bioentry_id)
);
CREATE INDEX cmtidx1 ON comment(bioentry_id);

# separate description table separate to save on space when we
# do not store descriptions

# this table replaces the old
#  bioentry_description and bioentry_keywords tables
CREATE TABLE bioentry_qualifier_value (
   bioentry_id       int(10) unsigned NOT NULL,
   FOREIGN KEY(bioentry_id) REFERENCES bioentry(bioentry_id),
   ontology_term_id  int(10) unsigned NOT NULL,
   FOREIGN KEY(ontology_term_id) REFERENCES ontology_term(ontology_term_id),
   qualifier_value             mediumtext
);
CREATE INDEX bqv1 ON bioentry_qualifier_value(bioentry_id);
CREATE INDEX bqv2 ON bioentry_qualifier_value(ontology_term_id);
CREATE INDEX bqv3 ON bioentry_qualifier_value(bioentry_id, ontology_term_id);

# feature table. We cleanly handle
#   - simple locations
#   - split locations
#   - split locations on remote sequences

# The fuzzies are not handled yet



CREATE TABLE seqfeature_source (
       seqfeature_source_id int(10) unsigned NOT NULL PRIMARY KEY auto_increment,
       source_name varchar(255) NOT NULL
);

CREATE TABLE seqfeature (
   seqfeature_id int(10) unsigned NOT NULL PRIMARY KEY auto_increment,
   bioentry_id   int(10) NOT NULL,
   seqfeature_key_id     int(10),
   seqfeature_source_id  int(10),
   seqfeature_rank int(5),
  FOREIGN KEY (seqfeature_key_id) REFERENCES ontology_term(ontology_term_id),
  FOREIGN KEY (seqfeature_source_id) REFERENCES seqfeature_source(seqfeature_source_id),
  FOREIGN KEY (bioentry_id) REFERENCES bioentry(bioentry_id)
);
CREATE INDEX sf1 ON seqfeature(seqfeature_key_id);
CREATE INDEX sf2 ON seqfeature(seqfeature_source_id);
CREATE INDEX sf3 ON seqfeature(bioentry_id);

# seqfeatures can be arranged in containment hierarchies.
# one can imagine storing other relationships between features,
# in this case the ontology_term_id can be used to type the relationship
CREATE TABLE seqfeature_relationship (
   seqfeature_relationship_id int(10) unsigned NOT NULL PRIMARY KEY auto_increment,
   parent_seqfeature_id int(10) NOT NULL,
   child_seqfeature_id int(10) NOT NULL,
   relationship_type_id int(10) NOT NULL,
   relationship_rank int(5),
   UNIQUE(parent_seqfeature_id, child_seqfeature_id, relationship_type_id),
   FOREIGN KEY (relationship_type_id) REFERENCES ontology_term(ontology_term_id),
   FOREIGN KEY (parent_seqfeature_id) REFERENCES seqfeature(seqfeature_id),
   FOREIGN KEY (child_seqfeature_id) REFERENCES seqfeature(seqfeature_id)
);
CREATE INDEX sfr1 ON seqfeature_relationship(relationship_type_id);
CREATE INDEX sfr2 ON seqfeature_relationship(parent_seqfeature_id);
CREATE INDEX sfr3 ON seqfeature_relationship(child_seqfeature_id);

CREATE TABLE seqfeature_qualifier_value (
   seqfeature_id int(10) NOT NULL,
   ontology_term_id int(10) NOT NULL,
   qualifier_rank int(5) NOT NULL,
   qualifier_value  mediumtext NOT NULL,
   FOREIGN KEY (ontology_term_id) REFERENCES ontology_term(ontology_term_id),
   FOREIGN KEY (seqfeature_id) REFERENCES seqfeature(seqfeature_id),
   PRIMARY KEY(seqfeature_id,ontology_term_id,qualifier_rank)
);
CREATE INDEX sqv1 ON seqfeature_qualifier_value(ontology_term_id);
CREATE INDEX sqv3 ON seqfeature_qualifier_value(seqfeature_id);
   
# basically we model everything as potentially having
# any number of locations, ie, a split location. SimpleLocations
# just have one location. We need to have a location id so for remote
# split locations we can specify the start/end point

# please do not try to model complex assemblies with this thing. It wont
# work. Check out the ensembl schema for this.

# we allow nulls for start/end - this is useful for fuzzies as
# standard range queries will not be included
CREATE TABLE seqfeature_location (
   seqfeature_location_id int(10) unsigned NOT NULL PRIMARY KEY auto_increment,
   seqfeature_id          int(10) NOT NULL,
   seq_start              int(10),
   seq_end                int(10),
   seq_strand             int(1)  NOT NULL,
   location_rank          int(5)  NOT NULL,
  FOREIGN KEY (seqfeature_id) REFERENCES seqfeature(seqfeature_id)
);
CREATE INDEX sfl1 ON seqfeature_location(seqfeature_id);
CREATE INDEX sfl2 ON seqfeature_location(seq_start);
CREATE INDEX sfl3 ON seqfeature_location(seq_end);

# for remote locations, this is the join to make.
# beware - in the object layer it has to make a double SQL query to figure out
# whether this is remote location or not

# like DR links, we do not link directly to a bioentry_id - we have to do
# this run-time

CREATE TABLE remote_seqfeature_name (
       seqfeature_location_id int(10) unsigned NOT NULL PRIMARY KEY,
       accession varchar(40) NOT NULL,
       version   int(10) NOT NULL,
  FOREIGN KEY (seqfeature_location_id) REFERENCES seqfeature_location(seqfeature_location_id)
);
CREATE INDEX rsfn1 ON remote_seqfeature_name(seqfeature_location_id);

# location qualifiers - mainly intended for fuzzies but anything
# can go in here
# some controlled vocab terms have slots;
# fuzzies could be modeled as min_start(5), max_start(5)
# 
# there is no restriction on extending the fuzzy ontology
# for your own nefarious aims, although the bio* apis will
# most likely ignore these
CREATE TABLE location_qualifier_value (
   seqfeature_location_id int(10) unsigned NOT NULL,
   ontology_term_id int(10) NOT NULL,
   qualifier_value  char(255) NOT NULL,
   qualifier_int_value int(10),
  FOREIGN KEY (seqfeature_location_id) REFERENCES seqfeature_location(seqfeature_location_id),
  FOREIGN KEY (ontology_term_id) REFERENCES ontology_term(ontology_term_id)
);
CREATE INDEX lqv1 ON location_qualifier_value(seqfeature_location_id);
CREATE INDEX lqv2 ON location_qualifier_value(ontology_term_id);

# pre-make the fuzzy ontology
INSERT INTO ontology_term (term_name) VALUES ('min_start');
INSERT INTO ontology_term (term_name) VALUES ('min_end');
INSERT INTO ontology_term (term_name) VALUES ('max_start');
INSERT INTO ontology_term (term_name) VALUES ('max_end');
INSERT INTO ontology_term (term_name) VALUES ('unknown_start');
INSERT INTO ontology_term (term_name) VALUES ('unknown_end');
INSERT INTO ontology_term (term_name) VALUES ('end_pos_type');
INSERT INTO ontology_term (term_name) VALUES ('start_pos_type');
INSERT INTO ontology_term (term_name) VALUES ('location_type');
# coordinate policies?

#
# this is a tiny table to allow a cach'ing corba server to
# persistently store aspects of the root server - so when/if
# the server gets reaped it can reconnect
#

CREATE TABLE cache_corba_support (
       biodatabase_id    int(10) unsigned NOT NULL PRIMARY KEY,  
       http_ior_string   varchar(255),
       direct_ior_string varchar(255)
       );