-- updated 040520 DDH
-- 29Nov2004 NL changed sequenceemblfeature.remark from varchar to text to avoid truncation
-- 29Nov2004 NL changed sequenceemblfeature.location from varchar(50) to varchar(255) to avoid truncation
-- 29Nov2004 NL changed sequence.tracefile from varchar(50) to varchar(100) to avoid truncation
-- 29Nov2004 NL changed sequence.title from varchar to text to avoid truncation
-- 5Jul2005 DEM Added table sequencetextsequence.
-- 22nov05 dem Added tables sequencecontig, sequencelength.
--             Added column sequence.locus
-- 27nov05 dem Removed column sequence.locus.  It's 1:1 with locus.sequence.


-- Peptide: sequence->protein->peptide
-- Sequence: sequence->dna
-- Gene: sequence->genesequence->gene
-- Gene_product: sequence->geneproductsequence->geneproduct
-- Allele: sequence->genesequence->allelegene->allele
-- Database: sequence->sequenceexternaldb
-- Locus: sequence->locus
-- Contigset: removed
-- Contig_members: removed
-- Contig: removed
-- Singleton_in: removed
-- DNA[1]: removed
-- Sex: removed
-- Date: removed
-- Best_DNA: removed
-- Best_Pep: removed 26Aug2004/NL: un-removed: see sequence table.
-- Method: removed
-- Pick_me_to_call: removed
-- Alignment: removed

-- EST sequencing direction?

create table sequence (
	id integer auto_increment primary key,
	name varchar(255),
	-- DNA[0]
	dnaid integer,
	-- Other_name
	altname varchar(100),
	-- Title
	title text,
	-- Corresponding_protein
	correspondingprotein_proteinid integer,
	-- Source
	source_sequenceid integer,
	-- Germplasm
	germplasmid integer,
	-- Gene_class
	geneclassid integer,
	-- Tracefile
	tracefile varchar(100),
	-- DNA_library
	libraryid integer,
	-- Codon_start (1|2|3)
	codonframe integer,
        -- Best_Pep[0]
        bestpep_proteinid integer,
        -- Best_Pep[1]
        bestpepscore float,
        -- Best_Pep[2]
        bestpepevalue varchar(10)
) ENGINE=InnoDB;
create index name_index on sequence (name);
create index dnaid_index on sequence (dnaid);
create index correspondingprotein_proteinid_index on sequence (correspondingprotein_proteinid);
create index altname_index on sequence (altname);
create index title_index on sequence (title(255));
create index source_sequenceid_index on sequence (source_sequenceid);
create index germplasmid_index on sequence (germplasmid);
create index geneclassid_index on sequence (geneclassid);
create index libraryid_index on sequence (libraryid);
create index bestpep_proteinid_index on sequence (bestpep_proteinid);

create table sequencespecies (
        id integer auto_increment primary key,
	sequenceid integer,
	-- Species
	speciesid integer
) ENGINE=InnoDB;
create index sequenceid_index on sequencespecies (sequenceid);
create index speciesid_index on sequencespecies (speciesid);

create table sequencetype (
	id integer auto_increment primary key,
	sequenceid integer,
	-- cDNA_EST, Contig_Phrap
	type varchar(25)
) ENGINE=InnoDB;
create index sequenceid_index on sequencetype (sequenceid);
create index type_index on sequencetype (type);

create table sequencereference (
        id integer auto_increment primary key,
	sequenceid integer,
	-- Reference
	referenceid integer
) ENGINE=InnoDB;
create index sequenceid_index on sequencereference (sequenceid);
create index referenceid_index on sequencereference (referenceid);

create table sequenceprobe (
        id integer auto_increment primary key,
	sequenceid integer,
	-- Probe
	probeid integer
) ENGINE=InnoDB;
create index sequenceid_index on sequenceprobe (sequenceid);
create index probeid_index on sequenceprobe (probeid);

create table sequenceexons (
	id integer auto_increment primary key,
	sequenceid integer,
	-- Source_Exons[0]
	begin integer,
	-- Source_Exons[1]
	end integer
) ENGINE=InnoDB;
create index sequenceid_index on sequenceexons (sequenceid);

create table sequencesubsequence (
	id integer auto_increment primary key,
	sequenceid integer,
	-- Subsequence[0]
	subsequence_sequenceid integer,
	-- Subsequence[1]
	begin integer,
	-- Subsequence[2]
	end integer
) ENGINE=InnoDB;
create index sequenceid_index on sequencesubsequence (sequenceid);
create index subsequence_sequenceid_index on sequencesubsequence (subsequence_sequenceid);

create table sequenceexternaldb (
	id integer auto_increment primary key,
	sequenceid integer,
	-- External_DB[0]
	name varchar(100),
	-- External_DB[1]
	accession varchar(100),
	url varchar(100),
	-- DB_remark, EC_number
	remark varchar(255)
) ENGINE=InnoDB;
create index sequenceid_index on sequenceexternaldb (sequenceid);

create table sequenceblasthits (
	id integer auto_increment primary key,
	sequenceid integer,
	-- Blast_hits
	blasttype varchar(25),
	dbname varchar(100),
	dbversion varchar(100),
	-- DNA_homol, Pep_homol
	accession varchar(25),
	title varchar(255),
	evalue float,
	score integer,
	querybegin integer,
	queryend integer,
	subjectbegin integer,
	subjectend integer
) ENGINE=InnoDB;
create index sequenceid_index on sequenceblasthits (sequenceid);
create index accession_index on sequenceblasthits (accession);
create index title_index on sequenceblasthits (title);

create table sequenceemblfeature (
	id integer auto_increment primary key,
	sequenceid integer,
	-- EMBL_feature
	feature varchar(50),
	begin integer,
	end integer,
	remark text,
	location varchar(255)
) ENGINE=InnoDB;
create index sequenceid_index on sequenceemblfeature (sequenceid);
create index feature_index on sequenceemblfeature (feature);

create table sequenceremark (
        id integer auto_increment primary key,
	sequenceid integer,
	-- Remark, Keyword, Clone, Alignment
	-- Cultivar, Chromosome, Clone_lib
	-- Tissue, Dev_stage, Strain
	type varchar(50),
	remark text
) ENGINE=InnoDB;
create index sequenceid_index on sequenceremark (sequenceid);
create index type_index on sequenceremark (type);

create table sequencedatasource (
        id integer auto_increment primary key,
	sequenceid integer,
	-- Data_source
	colleagueid integer,
	date varchar(75)
) ENGINE=InnoDB;
create index sequenceid_index on sequencedatasource (sequenceid);
create index colleagueid_index on sequencedatasource (colleagueid);

create table sequencetextsequence (
        id integer auto_increment primary key,
	sequenceid integer,
	-- Structure Sequence.  Free text, not FASTA format.  At most two
	-- per sequence record.  Example MWG17.
	textsequence mediumtext
) ENGINE=InnoDB;
create index sequenceid_index on sequencetextsequence (sequenceid);

-- dem 21nov05
-- Add Contig and Contigset for Sequence.

-- Contig_members: sequencecontig->sequence

-- table sequencecontig
-- Many-to-many, though a sequence should be in only one contig per contigset.
create table sequencecontig (
        id integer auto_increment primary key,
        -- id of the individual sequence
        sequenceid integer,
        -- sequence id of the contig
        contig_sequenceid integer,
        -- Should this be in the sequence table instead?
        contigsetid integer
) ENGINE=InnoDB;
create index sequenceid_index on sequencecontig (sequenceid);
create index contigsequenceid_index on sequencecontig (contig_sequenceid);

create table sequencelength (
	id integer auto_increment primary key,
	sequenceid integer,
	-- Length.  Can be multivalued.  Unit like "KB", "Bands".
	length integer,
	unit varchar(25)
) ENGINE=InnoDB;
create index sequenceid_index on sequencelength (sequenceid);