/*******************************/
/* SelenoDB schema version 1.0 */
/*                             */
/*      partially based        */
/*    on Ensembl schema 20     */
/*                             */
/*       Sergi Castellano      */
/*     April 1 2005, Hawaii    */
/*                             */
/*           Updated:          */
/*    October 6 2005, Cornell  */
/*  September 19 2006, Janelia */
/*                             */
/*******************************/

/*************/
/* IMPORTANT */
/*************/

/* TABLE DEPENDENCIES (REFERENTIAL INTEGRITY THROUGH FOREIGN KEYS) */
/* DO NOT ALLOW TO MODIFY TABLE ORDER IN THIS FILE */

/*******************************/
/* CREATE DB AND UPLOAD SCHEMA */
/*******************************/

/* ./selenodb_admin -a create */

/********************************/
/* DEFAULT STORAGE RANGE VALUES */
/********************************/

/* GENOME FEATURES */

/*   sequences: SMALLINT, up to 65,535 IDs */
/*       genes: SMALLINT, up to 65,535 IDs */

/* transcripts: MEDIUMINT, up to 16,777,215 IDs */
/*       exons: MEDIUMINT, up to 16,777,215 IDs */
/*       secis: MEDIUMINT, up to 16,777,215 IDs */

/* SPECIES AND PROTEIN FAMILIES */

/*   species:  SMALLINT, up to 65,535 IDs */
/*   family:  SMALLINT, up to 65,535 IDs */ 

/* DATA ANALYSIS TYPES */

/*	 analysis:   TINYINT, up to 255 IDs */

/* LENGHT OF STORED SEQUENCES */

/*  sequence: MEDIUMTEXT, 16,777,215 bytes (~ 16Mb) */

/* COORDINATES IN STORED SEQUENCES */

/*          gene_start: MEDIUMINT, up to 16,777,215 nt (to track positions in stored seq) */
/*            gene_end: MEDIUMINT, up to 16,777,215 nt (to track positions in stored seq) */
/*    transcript_start: MEDIUMINT, up to 16,777,215 nt (to track positions in stored seq) */
/*      transcript_end: MEDIUMINT, up to 16,777,215 nt (to track positions in stored seq) */
/*          exon_start: MEDIUMINT, up to 16,777,215 nt (to track positions in stored seq) */
/*            exon_end: MEDIUMINT, up to 16,777,215 nt (to track positions in stored seq) */
/*   protein_start: MEDIUMINT, up to 16,777,215 nt (to track positions in stored seq) */
/*     protein_end: MEDIUMINT, up to 16,777,215 nt (to track positions in stored seq) */
/*         secis_start: MEDIUMINT, up to 16,777,215 nt (to track positions in stored seq) */
/*           secis_end: MEDIUMINT, up to 16,777,215 nt (to track positions in stored seq) */

/* COORDINATES IN EXTERNAL SEQUENCES */

/*	sequence_ext_start: INT, up to 4294,967,295 nt (to track positions in original seq) */
/*	  sequence_ext_end: INT, up to 4294,967,295 nt (to track positions in original seq) */

/* Display width is set to a minimum of 8 digits for all types (UNASSIGNED AND ZEROFILL) */


/************************/
/* DATABASE CONVENTIONS */
/************************/

/* NULL: unknown or missing value. It means that no entry has been made. Generally they are not allowed in SelenoDB */
/*  NA: not applicable. eg: gene_start is not applicable to transcript annotation */

/* Specifying a column as not permitting null values can help maintain data integrity by ensuring that a column in a row always contains data. If null values are not allowed, the user entering data in the table must enter a value in the column or the table row cannot be accepted into the database. */

/* In a numerical NOT NULL column DEFAULT is 0 */
/* In a string NOT NULL column DEFAULT is the empty string */

/**********************/
/* UNIQUE IDENTIFIERS */
/**********************/

/* AUTO_INCREMENT by default starts at one and increases by one */
/* Numbers are NOT reused in MySQL releases 3.23 and up */
/* Only one AUTO_INCREMENT column is allowed per table */
/* Column must be NOT NULL and PRIMARY KEY (or UNIQUE) */
/* Sequence count starts at 1 */
/* Declare column UNSIGNED to have larger range of number */
/* AUTO_INCREMENT forces a UNIQUE column to accept NULL */
/* Insert NULL or 0 in AUTO_INCREMENT column record to have it replaced by next number */
/* If ZEROFILL used, do only string comparisons with IDs */

/**********************/
/* InnoDB TABLE ENGINE  */
/**********************/

/* MySQL does not support transactions, commit and rollback in MyISAM tables */
/* MySQL does locking at the table level in MyISAM tables */
/* MySQL does not support foreign keys or referential integrity in MyISAM tables */

/* MySQL does support all this and locks at the row level in InnoDB tables */
/* InnoDB tables are used in SelenoDB */


/******************/
/* RELEASE SYSTEM */
/******************/

/* A simple release system is implemented in SelenDB in a relational fashion */

/* The concept of releases, revisions and releases have different meanings in different releaseing systems. */
/* In here, they are defined as: */
/* release is a number XX.YY in which XX is the release and YY the revision */
/* by default release 1.0 is the first release of a feature, that is, release 1 and no (0) revisions */

/* Relations (implemented as tables) are classified in subject layers for clarity */

/******************************************************************************************************/
/*************************************************** META LAYER ***************************************/
/******************************************************************************************************/

/* Data about the database */

/**************/
/* INTERFACE **/
/**************/

DROP TABLE IF EXISTS interface_release;
CREATE TABLE
	interface_release
	(
	interface_release CHAR(5) NOT NULL PRIMARY KEY,  /* 1.0, 1.1,..., 2.0*/
	release_name CHAR(20) NOT NULL, /* interface release name */
	description TINYTEXT NOT NULL,
	updated TIMESTAMP NOT NULL, /* first timestamp: row update, set to NULL in imported data (auto-updates) */
	created TIMESTAMP NOT NULL  /* second timestamp: row creation, set to NULL in imported data */
	)
   ENGINE  =       InnoDB
   COMMENT =      'info about web interface releases'
	;

/**********/
/* SCHEMA */
/**********/

DROP TABLE IF EXISTS schema_release;
CREATE TABLE
	schema_release
	(
	schema_release CHAR(5) NOT NULL PRIMARY KEY, /* 1.0, 1.2, 2.0*/
	release_name CHAR(20) NOT NULL, /* schema release name */
	description TINYTEXT NOT NULL,
	updated TIMESTAMP NOT NULL, /* first timestamp: row update, set to NULL in imported data (auto-updates) */
	created TIMESTAMP NOT NULL /* second timestamp: row creation, set to NULL in imported data */
	)
   ENGINE  =       InnoDB
   COMMENT =      'info about schema releases'
	;

/**********^*/
/* DATABASE */
/************/


DROP TABLE IF EXISTS db_release;
CREATE TABLE
	db_release
	(
	db_release  CHAR(5) NOT NULL PRIMARY KEY,  /* 1.0, 1.2, 2.0*/
	release_name CHAR(20) NOT NULL, /* database release name */
	description TINYTEXT NOT NULL,
	updated TIMESTAMP NOT NULL, /* first timestamp: row update, set to NULL in imported data (auto-updates) */
	created TIMESTAMP NOT NULL /* second timestamp: row creation, set to NULL in imported data */
	)
   ENGINE  =       InnoDB
   COMMENT =      'info about database releases'
	; 

/******************************************************************************************************/
/************************************************* AUTHOR LAYER ***************************************/
/******************************************************************************************************/

/*****************/
/* AUTHORS TABLE */
/*****************/

DROP TABLE IF EXISTS author;
CREATE TABLE
	author
	(	
	firstname VARCHAR(30) NOT NULL,
	middlename VARCHAR(6) NOT NULL,
	lastname VARCHAR(30) NOT NULL,
	PRIMARY KEY (firstname, middlename, lastname),
	email VARCHAR(40) NOT NULL,
	updated TIMESTAMP NOT NULL, /* first timestamp: row update, set to NULL in imported data (auto-updates) */
	created TIMESTAMP NOT NULL /* second timestamp: row creation, set to NULL in imported data */
	)
   ENGINE    =       InnoDB
   COMMENT =      'contact info to selenoDB authors'
	;

/******************************************************************************************************/
/*************************************************** LINK LAYER ***************************************/
/******************************************************************************************************/

/**************************/
/* AUTHOR_INTERFACE TABLE */
/**************************/

DROP TABLE IF EXISTS author_interface_release;
CREATE TABLE
	author_interface_release
	(
	firstname VARCHAR(30) NOT NULL,
	middlename VARCHAR(6) NOT NULL,
	lastname VARCHAR(30) NOT NULL,
	interface_release CHAR(5) NOT NULL,
	PRIMARY KEY (firstname, middlename, lastname, interface_release),
	FOREIGN KEY (firstname, middlename, lastname) REFERENCES author (firstname, middlename, lastname)
	ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY (interface_release) REFERENCES interface_release (interface_release)
	ON UPDATE CASCADE ON DELETE CASCADE
	)
   ENGINE    =       InnoDB
   COMMENT =      'links authors to interface releases'
	;

/***********************/
/* AUTHOR_SCHEMA TABLE */
/***********************/

DROP TABLE IF EXISTS author_schema_release;
CREATE TABLE
	author_schema_release
	(
	firstname VARCHAR(30) NOT NULL,
	middlename VARCHAR(6) NOT NULL,
	lastname VARCHAR(30) NOT NULL,
	schema_release  CHAR(5) NOT NULL,
	PRIMARY KEY (firstname, middlename, lastname, schema_release),
	FOREIGN KEY (firstname, middlename, lastname) REFERENCES author (firstname, middlename, lastname)
	ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY (schema_release) REFERENCES schema_release (schema_release)
	ON UPDATE CASCADE ON DELETE CASCADE
	)
   ENGINE    =       InnoDB
   COMMENT =      'links authors to schema releases'
	;

/*************************/
/* AUTHOR_DATABASE TABLE */
/*************************/

DROP TABLE IF EXISTS author_db_release;
CREATE TABLE
	author_db_release
	(
	firstname VARCHAR(30) NOT NULL,
	middlename VARCHAR(6) NOT NULL,
	lastname VARCHAR(30) NOT NULL,
	db_release  CHAR(5) NOT NULL,
	PRIMARY KEY (firstname, middlename, lastname, db_release),
	FOREIGN KEY (firstname, middlename, lastname) REFERENCES author (firstname, middlename, lastname)
	ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY (db_release) REFERENCES db_release (db_release)
	ON UPDATE CASCADE ON DELETE CASCADE
	)
   ENGINE    =       InnoDB
   COMMENT =      'links authors to database releases'
	;

/*********************************************************************************************************/
/*************************************************** SPECIES LAYER ***************************************/
/*********************************************************************************************************/

/*****************************/
/* SPECIES DESCRIPTION TABLE */
/*****************************/

DROP TABLE IF EXISTS species;
CREATE TABLE
	species
	(		
	scientific_name_gen VARCHAR(60) NOT NULL,
	scientific_name_sp VARCHAR(60) NOT NULL,
	PRIMARY KEY (scientific_name_gen,scientific_name_sp),
	common_name VARCHAR(60),
	taxonomy_link VARCHAR(120),
	updated TIMESTAMP NOT NULL, /* first timestamp: row update, set to NULL in imported data (auto-updates) */
	created TIMESTAMP NOT NULL /* second timestamp: row creation, set to NULL in imported data */
	)
   ENGINE    =       InnoDB
   COMMENT =      'species table'
	;

/*********************************************************************************************************/
/************************************************** SEQUENCE LAYER ***************************************/
/*********************************************************************************************************/

/******************/
/* SEQUENCE TABLE */
/******************/

DROP TABLE IF EXISTS sequence_release;
CREATE TABLE
	sequence_release
	(
	sequence_id SMALLINT(8) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
        sequence_release VARCHAR(5) NOT NULL,
        PRIMARY KEY (sequence_id, sequence_release),
	scientific_name_gen VARCHAR(60) NOT NULL,
	scientific_name_sp VARCHAR(60) NOT NULL,
	KEY (scientific_name_gen,scientific_name_sp),
	FOREIGN KEY (scientific_name_gen,scientific_name_sp) REFERENCES species (scientific_name_gen,scientific_name_sp)
	ON UPDATE CASCADE ON DELETE CASCADE,	
	sequence MEDIUMTEXT NOT NULL, /* Sequences up to 16,777,215 nt */
	updated TIMESTAMP NOT NULL, /* first timestamp: row update, set to NULL in imported data (auto-updates) */
	created TIMESTAMP NOT NULL /* second timestamp: row creation, set to NULL in imported data */

	)
   ENGINE    =       InnoDB
   COMMENT =      'where original (sub)sequence is stored at selenoDB'
	;

/*********************************************************************************************************/
/*************************************************** FAMILY LAYER ***************************************/
/*********************************************************************************************************/

/*********************************/
/* GENE FAMILY TABLE */
/*********************************/

DROP TABLE IF EXISTS family;
CREATE TABLE
	family
	(
	family_symbol VARCHAR(10) NOT NULL PRIMARY KEY,	
	family_name VARCHAR(60) NOT NULL UNIQUE,
	description TINYTEXT NOT NULL,
	updated TIMESTAMP NOT NULL, /* first timestamp: row update, set to NULL in imported data (auto-updates) */
	created TIMESTAMP NOT NULL /* second timestamp: row creation, set to NULL in imported data */	
	)
   ENGINE    =       InnoDB
   COMMENT =      'family table'
	;

/************************************/
/* GENE SUBFAMILY TABLE */
/************************************/

/* 1-TO-N RELATIONSHIP WITH GENE TABLE */
/* PRIMARY KEY IS FOREIGN KEY IN GENE TABLE */
/* GENE TABLE IS MANDATORY (1,1) (1,N) */

/* N-TO-1 RELATIONSHIP WITH family TABLE */
/* family TABLE IS MANDATORY (1,N) (1,1) */

/* IN FAMILIES WITH NO SUBFAMILIES, subfamily_name is N/A = not applicable */

DROP TABLE IF EXISTS subfamily;
CREATE TABLE
	subfamily
	(
	subfamily_symbol VARCHAR(12) NOT NULL, /* N/A*/
	family_symbol VARCHAR(10) NOT NULL,
	subfamily_name VARCHAR(60) NOT NULL UNIQUE,
	PRIMARY KEY (subfamily_symbol),
	FOREIGN KEY (family_symbol) REFERENCES family (family_symbol)
	ON UPDATE CASCADE ON DELETE CASCADE,	
	description TINYTEXT NOT NULL,
	updated TIMESTAMP NOT NULL, /* first timestamp: row update, set to NULL in imported data (auto-updates) */
	created TIMESTAMP NOT NULL /* second timestamp: row creation, set to NULL in imported data */	
	)
   ENGINE    =       InnoDB
   COMMENT =      'subfamily table'
	;

/********************************/
/* GENE FAMILY_SYNONIMOUS TABLE */
/********************************/

DROP TABLE IF EXISTS family_synonimous;
CREATE TABLE
	family_synonimous
	(
	family_symbol_syn Varchar(10) NOT NULL,
	family_name_syn VARCHAR(60) NOT NULL,	
	PRIMARY KEY (family_symbol_syn, family_name_syn),
	family_symbol VARCHAR(10) NOT NULL,
	FOREIGN KEY (family_symbol) REFERENCES family (family_symbol)
	ON UPDATE CASCADE ON DELETE CASCADE,	
	updated TIMESTAMP NOT NULL, /* first timestamp: row update, set to NULL in imported data (auto-updates) */
	created TIMESTAMP NOT NULL /* second timestamp: row creation, set to NULL in imported data */	
	)
   ENGINE    =       InnoDB
   COMMENT =      'family synonimous symbols table'
	;

/***********************************/
/* GENE SUBFAMILY_SYNONIMOUS TABLE */
/***********************************/

DROP TABLE IF EXISTS subfamily_synonimous;
CREATE TABLE
	subfamily_synonimous
	(
	subfamily_symbol_syn VARCHAR(12) NOT NULL,
	subfamily_name_syn VARCHAR(60) NOT NULL,
	PRIMARY KEY (subfamily_symbol_syn, subfamily_name_syn),	
	subfamily_symbol VARCHAR(12) NOT NULL,
	FOREIGN KEY (subfamily_symbol) REFERENCES subfamily (subfamily_symbol)
	ON UPDATE CASCADE ON DELETE CASCADE,	
	updated TIMESTAMP NOT NULL,/* first timestamp: row update, set to NULL in imported data (auto-updates) */
	created TIMESTAMP NOT NULL/* second timestamp: row creation, set to NULL in imported data */	
	)
   ENGINE    =       InnoDB
   COMMENT =      'family synonimous symbols table'
	;

/*********************************************************************************************************/
/********************************************** ANALYSIS LAYER *******************************************/
/*********************************************************************************************************/

/******************************/
/* ANALYSIS DESCRIPTION TABLE */
/******************************/

/* Analysis of exons, transcripts, protein and SECIS */

DROP TABLE IF EXISTS analysis;
CREATE TABLE
	analysis
	(	
	program VARCHAR(60) NOT NULL,
	program_release VARCHAR(30) NOT NULL,
	PRIMARY KEY (program, program_release), 	
	program_type ENUM ('N/A','Ab initio gene prediction','Comparative gene prediction','Protein homology','Transcript homology','Conceptual protein','External database annotation','Thermodynamic pattern-based SECIS prediction','Comparative SECIS prediction','Matrix pattern-based promoter prediction','Comparative promoter prediction','Promoter prediction'),
	description TINYTEXT,
	updated TIMESTAMP NOT NULL, /* first timestamp: row update, set to NULL in imported data (auto-updates) */
	created TIMESTAMP NOT NULL /* second timestamp: row creation, set to NULL in imported data */
	)
   ENGINE    =       InnoDB
   COMMENT =      'analysis description table'
	;

/*********************************************************************************************************/
/********************************************** RESIDUE LAYER *******************************************/
/*********************************************************************************************************/

/******************************/
/* RESIDUE TABLE */
/******************************/

DROP TABLE IF EXISTS residue_validation;
CREATE TABLE
	residue_validation
	(	
	amino_acid CHAR(3) NOT NULL PRIMARY KEY,
	amino_acid_name VARCHAR(15),
        amino_acid_symbol CHAR(1),
	updated TIMESTAMP NOT NULL, /* first timestamp: row update, set to NULL in imported data (auto-updates) */
	created TIMESTAMP NOT NULL /* second timestamp: row creation, set to NULL in imported data */
	)
   ENGINE    =       InnoDB
   COMMENT =      'residue validation table'
	;

/*********************************************************************************************************/
/************************************FEATURE RELEASE ANNOTATION LAYER ************************************/
/*********************************************************************************************************/

/*********************************/
/* EXON RELEASE ANNOTATION TABLE */
/*********************************/

DROP TABLE IF EXISTS exon_release;
CREATE TABLE
	exon_release
	(
	exon_id MEDIUMINT(8) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
	exon_release VARCHAR(5) NOT NULL,
	PRIMARY KEY (exon_id, exon_release),
	program VARCHAR(60) NOT NULL,
	program_release VARCHAR(30) NOT NULL,
	KEY (program, program_release),
	FOREIGN KEY (program, program_release) REFERENCES analysis (program, program_release)
	ON UPDATE CASCADE ON DELETE CASCADE,
	seq_start MEDIUMINT(8) UNSIGNED ZEROFILL,
	seq_end MEDIUMINT(8) UNSIGNED ZEROFILL,
	updated TIMESTAMP NOT NULL, /* first timestamp: row update, set to NULL in imported data (auto-updates) */
	created TIMESTAMP NOT NULL /* second timestamp: row creation, set to NULL in imported data */
	)
   ENGINE    =       InnoDB
   COMMENT =      'exon release table'
	;

/*********************************/
/* INTRON RELEASE ANNOTATION TABLE */
/*********************************/

/* Instead of creating this table on the fly from the exon_release table */
/* we store it so that intron_id can be a foreign key when annotating other */
/* features on the introns, eg. description or splicing enhancers */

DROP TABLE IF EXISTS intron_release;
CREATE TABLE
	intron_release
	(
	intron_id MEDIUMINT(8) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
	intron_release VARCHAR(5) NOT NULL,
	PRIMARY KEY (intron_id, intron_release),
	seq_start MEDIUMINT(8) UNSIGNED ZEROFILL,
	seq_end MEDIUMINT(8) UNSIGNED ZEROFILL,
	updated TIMESTAMP NOT NULL, /* first timestamp: row update, set to NULL in imported data (auto-updates) */
	created TIMESTAMP NOT NULL /* second timestamp: row creation, set to NULL in imported data */
	)
   ENGINE    =       InnoDB
   COMMENT =      'intron release table'
	;


/*********************************/
/* GENE RELEASE ANNOTATION TABLE */
/*********************************/

/* Sequence may need someday to be linked to transcript instead of gene */

DROP TABLE IF EXISTS gene_release;
CREATE TABLE
	gene_release
	(
	gene_id SMALLINT(8) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
	gene_release VARCHAR(5) NOT NULL,
	PRIMARY KEY (gene_id, gene_release),
	sequence_id SMALLINT(8) UNSIGNED ZEROFILL, 
        sequence_release VARCHAR(5) NOT NULL,
	KEY (sequence_id, sequence_release),
	FOREIGN KEY (sequence_id, sequence_release) REFERENCES sequence_release (sequence_id, sequence_release)
	ON UPDATE CASCADE ON DELETE CASCADE,
	subfamily_symbol VARCHAR(12) NOT NULL,
	family_symbol VARCHAR(10) NOT NULL,
	KEY (subfamily_symbol, family_symbol),
	FOREIGN KEY (subfamily_symbol) REFERENCES subfamily (subfamily_symbol) 
	ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY (family_symbol) REFERENCES family (family_symbol) 
	ON UPDATE CASCADE ON DELETE CASCADE,
	strand ENUM('Forward','Reverse','N/A'),
	updated TIMESTAMP NOT NULL, /* first timesta mp: row update, set to NULL in imported data (auto-updates) */
	created TIMESTAMP NOT NULL /* second timestamp: row creation, set to NULL in imported data */
	)
   ENGINE    =       InnoDB
   COMMENT =      'gene release table'
	;

/*************************************/
/* PROMOTER RELEASE ANNOTATION TABLE */
/*************************************/

DROP TABLE IF EXISTS promoter_release;
CREATE TABLE
	promoter_release
	(
	promoter_id MEDIUMINT(8) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
	promoter_release VARCHAR(5) NOT NULL,
	PRIMARY KEY (promoter_id, promoter_release),
	program VARCHAR(60) NOT NULL,
	program_release VARCHAR(30) NOT NULL,
	KEY (program, program_release),
	FOREIGN KEY (program, program_release) REFERENCES analysis (program, program_release)
	ON UPDATE CASCADE ON DELETE NO ACTION,
	seq_start MEDIUMINT(8) UNSIGNED ZEROFILL NOT NULL,
	seq_end MEDIUMINT(8) UNSIGNED ZEROFILL NOT NULL,
	updated TIMESTAMP NOT NULL, /* first timestamp: row update, set to NULL in imported data (auto-updates) */
	created TIMESTAMP NOT NULL /* second timestamp: row creation, set to NULL in imported data */
	)
   ENGINE    =       InnoDB
   COMMENT =      'promoter release table'
	;

/***************************************/
/* TRANSCRIPT RELEASE ANNOTATION TABLE */
/***************************************/

DROP TABLE IF EXISTS transcript_release;
CREATE TABLE
	transcript_release
	(
	transcript_id MEDIUMINT(8) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
	transcript_release CHAR(5) NOT NULL,
	PRIMARY KEY (transcript_id, transcript_release),
	gene_id SMALLINT(8) UNSIGNED ZEROFILL NOT NULL,
	gene_release VARCHAR(5) NOT NULL,	
	KEY (gene_id, gene_release),
	FOREIGN KEY (gene_id, gene_release) REFERENCES gene_release (gene_id, gene_release)
	ON UPDATE CASCADE ON DELETE CASCADE,
	promoter_id MEDIUMINT(8) UNSIGNED ZEROFILL NOT NULL,
	promoter_release VARCHAR(5) NOT NULL,
	KEY (promoter_id, promoter_release),
	FOREIGN KEY (promoter_id, promoter_release) REFERENCES promoter_release (promoter_id, promoter_release)
	ON UPDATE CASCADE ON DELETE NO ACTION,
	updated TIMESTAMP NOT NULL, /* first timestamp: row update, set to NULL in imported data (auto-updates) */
	created TIMESTAMP NOT NULL /* second timestamp: row creation, set to NULL in imported data */
	)
   ENGINE    =       InnoDB
   COMMENT =      'transcript release table'
	; 

/****************************************/
/* PROTEIN RELEASE ANNOTATION TABLE */
/****************************************/

/* We store exon and seq. position of start and end of protein */
/* We can obtain conceptual protein using both this and */
/* transcript_release tables */

DROP TABLE IF EXISTS protein_release;
CREATE TABLE
	protein_release
	(
	protein_id MEDIUMINT(8) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
	protein_release VARCHAR(5) NOT NULL,
	PRIMARY KEY (protein_id, protein_release),
	program VARCHAR(60) NOT NULL,
	program_release VARCHAR(30) NOT NULL,
	KEY (program, program_release),
	FOREIGN KEY (program, program_release) REFERENCES analysis (program, program_release)
	ON UPDATE CASCADE ON DELETE CASCADE,
	seq_start MEDIUMINT(8) UNSIGNED ZEROFILL NOT NULL, /* First ATG nt, so A position*/
	start_exon_id MEDIUMINT(8) UNSIGNED ZEROFILL NOT NULL,
	start_exon_release VARCHAR(5) NOT NULL,
	KEY (start_exon_id, start_exon_release),
	FOREIGN KEY (start_exon_id, start_exon_release) REFERENCES exon_release (exon_id, exon_release)
	ON UPDATE CASCADE ON DELETE CASCADE,
	seq_end MEDIUMINT(8) UNSIGNED ZEROFILL NOT NULL, /* LAST STOP nt */
	end_exon_id MEDIUMINT(8) UNSIGNED ZEROFILL NOT NULL,
	end_exon_release VARCHAR(5) NOT NULL,
	KEY (end_exon_id, end_exon_release),
	FOREIGN KEY (end_exon_id, end_exon_release) REFERENCES exon_release (exon_id, exon_release)
	ON UPDATE CASCADE ON DELETE CASCADE,
	amino_acid ENUM('Sec','Cys','Arg','Trp','N/A'), /* tells whether this is a Sec or Cys homolog */	
	updated TIMESTAMP NOT NULL, /* first timestamp: row update, set to NULL in imported data (auto-updates) */
	created TIMESTAMP NOT NULL /* second timestamp: row creation, set to NULL in imported data */
	)
   ENGINE    =       InnoDB
   COMMENT =      'protein release table'
	;

/**********************************/
/* SECIS RELEASE ANNOTATION TABLE */
/**********************************/

/* We store exon and seq. position of start and end of secis */
/* We can obtain the whole secis seq.  using both this and */
/* transcript_release tables */

DROP TABLE IF EXISTS secis_release;
CREATE TABLE
	secis_release
	(
	secis_id MEDIUMINT(8) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
	secis_release VARCHAR(5) NOT NULL,
	PRIMARY KEY (secis_id, secis_release),
	program VARCHAR(60) NOT NULL,
	program_release VARCHAR(30) NOT NULL,
	KEY (program, program_release),
	FOREIGN KEY (program, program_release) REFERENCES analysis (program, program_release)
	ON UPDATE CASCADE ON DELETE CASCADE,
	seq_start MEDIUMINT(8) UNSIGNED ZEROFILL NOT NULL,
	start_exon_id MEDIUMINT(8) UNSIGNED ZEROFILL NOT NULL,
	start_exon_release VARCHAR(5) NOT NULL,
	KEY (start_exon_id, start_exon_release),
	FOREIGN KEY (start_exon_id, start_exon_release) REFERENCES exon_release (exon_id, exon_release)
	ON UPDATE CASCADE ON DELETE CASCADE,
	seq_end MEDIUMINT(8) UNSIGNED ZEROFILL NOT NULL,
	end_exon_id MEDIUMINT(8) UNSIGNED ZEROFILL NOT NULL,
	end_exon_release VARCHAR(5) NOT NULL,
	KEY (end_exon_id, end_exon_release),
	FOREIGN KEY (end_exon_id, end_exon_release) REFERENCES exon_release (exon_id, exon_release)
	ON UPDATE CASCADE ON DELETE CASCADE,
	updated TIMESTAMP NOT NULL, /* first timestamp: row update, set to NULL in imported data (auto-updates) */
	created TIMESTAMP NOT NULL /* second timestamp: row creation, set to NULL in imported data */
	)
   ENGINE    =       InnoDB
   COMMENT =      'secis release table'
	;

/************************************/
/* RESIDUE RELEASE ANNOTATION TABLE */
/************************************/

/* We give an id to the codon so we can establish direct homology between features */
/* Indirect homology is established through the gene and is less precise */

/* It needs a homology_id and homology_release */

DROP TABLE IF EXISTS residue_release;
CREATE TABLE
	residue_release
	(
	residue_id MEDIUMINT(8) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
	residue_release VARCHAR(5) NOT NULL,
	PRIMARY KEY (residue_id, residue_release),
	seq_start MEDIUMINT(8) UNSIGNED ZEROFILL NOT NULL, /* First nt in codon */
	start_exon_id MEDIUMINT(8) UNSIGNED ZEROFILL NOT NULL,
	start_exon_release VARCHAR(5) NOT NULL,
	KEY (start_exon_id, start_exon_release),
	FOREIGN KEY (start_exon_id, start_exon_release) REFERENCES exon_release (exon_id, exon_release)
	ON UPDATE CASCADE ON DELETE CASCADE,
	seq_end MEDIUMINT(8) UNSIGNED ZEROFILL NOT NULL, /* Last nt in codon */
	end_exon_id MEDIUMINT(8) UNSIGNED ZEROFILL NOT NULL,
	end_exon_release VARCHAR(5) NOT NULL,
	KEY (end_exon_id, end_exon_release),
	FOREIGN KEY (end_exon_id, end_exon_release) REFERENCES exon_release (exon_id, exon_release)
	ON UPDATE CASCADE ON DELETE CASCADE,
	amino_acid CHAR(3) NOT NULL,
        KEY (amino_acid),
        FOREIGN KEY (amino_acid) REFERENCES residue_validation (amino_acid)
        ON UPDATE CASCADE ON DELETE CASCADE,	
	updated TIMESTAMP NOT NULL, /* first timestamp: row update, set to NULL in imported data (auto-updates) */
	created TIMESTAMP NOT NULL /* second timestamp: row creation, set to NULL in imported data */
	)
   ENGINE    =       InnoDB
   COMMENT =      'residue release table'
	;


/*********************************************/
/* FEATURE RESIDUE RELEASE NOT PRESENT TABLE */
/*********************************************/

DROP TABLE IF EXISTS feature_release_not_present;
CREATE TABLE
	feature_release_not_present
	(
        scientific_name_gen VARCHAR(60) NOT NULL,
	scientific_name_sp VARCHAR(60) NOT NULL,
        family_symbol VARCHAR(10) NOT NULL,
        PRIMARY KEY (scientific_name_gen, scientific_name_sp, family_symboL),
	FOREIGN KEY (scientific_name_gen,scientific_name_sp) REFERENCES species (scientific_name_gen,scientific_name_sp)
	ON UPDATE CASCADE ON DELETE CASCADE,
        FOREIGN KEY (family_symbol) REFERENCES family (family_symbol) 
	ON UPDATE CASCADE ON DELETE CASCADE,
	updated TIMESTAMP NOT NULL, /* first timestamp: row update, set to NULL in imported data (auto-updates) */
	created TIMESTAMP NOT NULL /* second timestamp: row creation, set to NULL in imported data */
	)
   ENGINE    =       InnoDB
   COMMENT =      'feature release not present table'
	;



/******************************************************************************************************/
/*************************************************** LINK LAYER ***************************************/
/******************************************************************************************************/

/*****************************/
/* AUTHOR_EXON_RELEASE TABLE */
/*****************************/

DROP TABLE IF EXISTS author_exon_release;
CREATE TABLE
	author_exon_release
	(
	firstname VARCHAR(30) NOT NULL,
	middlename VARCHAR(6) NOT NULL,
	lastname VARCHAR(30) NOT NULL,
	exon_id MEDIUMINT(8) UNSIGNED ZEROFILL NOT NULL,
	exon_release VARCHAR(5) NOT NULL,
	PRIMARY KEY (firstname, middlename, lastname, exon_id, exon_release),
	FOREIGN KEY (firstname, middlename, lastname) REFERENCES author (firstname, middlename, lastname)
	ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY (exon_id, exon_release) REFERENCES exon_release (exon_id, exon_release)
	ON UPDATE CASCADE ON DELETE CASCADE
	)
   ENGINE    =       InnoDB
   COMMENT =      'links authors to exon releases'
        ;

/***********************************/
/* AUTHOR_TRANSCRIPT_RELEASE TABLE */
/***********************************/

DROP TABLE IF EXISTS author_transcript_release;
CREATE TABLE
	author_transcript_release
	(
	firstname VARCHAR(30) NOT NULL,
	middlename VARCHAR(6) NOT NULL,
	lastname VARCHAR(30) NOT NULL,
	transcript_id MEDIUMINT(8) UNSIGNED ZEROFILL NOT NULL,
	transcript_release CHAR(5) NOT NULL,
	PRIMARY KEY (firstname, middlename, lastname, transcript_id, transcript_release),
	FOREIGN KEY (firstname, middlename, lastname) REFERENCES author (firstname, middlename, lastname)
	ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY (transcript_id, transcript_release) REFERENCES transcript_release (transcript_id, transcript_release)
	ON UPDATE CASCADE ON DELETE CASCADE
	)
   ENGINE    =       InnoDB
   COMMENT =      'links authors to transcript releases'
        ;
 
/******************************/
/* AUTHOR_SECIS_RELEASE TABLE */
/******************************/

DROP TABLE IF EXISTS author_secis_release;
CREATE TABLE
	author_secis_release
	(
	firstname VARCHAR(30) NOT NULL,
	middlename VARCHAR(6) NOT NULL,
	lastname VARCHAR(30) NOT NULL,
	secis_id MEDIUMINT(8) UNSIGNED ZEROFILL NOT NULL,
	secis_release  VARCHAR(5) NOT NULL,
	PRIMARY KEY (firstname, middlename, lastname, secis_id, secis_release),
	FOREIGN KEY (firstname, middlename, lastname) REFERENCES author (firstname, middlename, lastname)
	ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY (secis_id, secis_release) REFERENCES secis_release (secis_id, secis_release)
	ON UPDATE CASCADE ON DELETE CASCADE
	)
   ENGINE    =       InnoDB
   COMMENT =      'links authors to SECIS releases'
        ;

/*********************************/
/* AUTHOR_PROMOTER_RELEASE TABLE */
/*********************************/

DROP TABLE IF EXISTS author_promoter_release;
CREATE TABLE
	author_promoter_release
	(
	firstname VARCHAR(30) NOT NULL,
	middlename VARCHAR(6) NOT NULL,
	lastname VARCHAR(30) NOT NULL,
	promoter_id MEDIUMINT(8) UNSIGNED ZEROFILL NOT NULL,
	promoter_release  VARCHAR(5) NOT NULL,
	PRIMARY KEY (firstname, middlename, lastname, promoter_id, promoter_release),
	FOREIGN KEY (firstname, middlename, lastname) REFERENCES author (firstname, middlename, lastname)
	ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY (promoter_id, promoter_release) REFERENCES promoter_release (promoter_id, promoter_release)
	ON UPDATE CASCADE ON DELETE CASCADE
	)
   ENGINE    =       InnoDB
   COMMENT =      'links authors to promoter releases'
	;



        
/******************************************************************************************************/
/*************************************************** LINK LAYER ***************************************/
/******************************************************************************************************/

/*******************************************/
/* EXON RELEASE - TRANSCRIPT RELEASE TABLE */
/*******************************************/

DROP TABLE IF EXISTS exon_transcript;
CREATE TABLE
	exon_release_transcript_release
	(
	exon_id MEDIUMINT(8) UNSIGNED ZEROFILL NOT NULL,
	exon_release VARCHAR(5) NOT NULL,
	transcript_id MEDIUMINT(8) UNSIGNED ZEROFILL NOT NULL,
	transcript_release CHAR(5) NOT NULL,
	PRIMARY KEY (exon_id, exon_release, transcript_id, transcript_release),
	FOREIGN KEY (exon_id, exon_release) REFERENCES exon_release (exon_id, exon_release)
	ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY (transcript_id, transcript_release) REFERENCES transcript_release (transcript_id, transcript_release)
	ON UPDATE CASCADE ON DELETE CASCADE
	)
   ENGINE    =       InnoDB
   COMMENT =      'exon release - transcript release table'
	;

/*******************************************/
/* INTRON RELEASE - TRANSCRIPT RELEASE TABLE */
/*******************************************/

 DROP TABLE IF EXISTS intron_transcript;
CREATE TABLE
	intron_release_transcript_release
	(
	intron_id MEDIUMINT(8) UNSIGNED ZEROFILL NOT NULL,
	intron_release VARCHAR(5) NOT NULL,
	transcript_id MEDIUMINT(8) UNSIGNED ZEROFILL NOT NULL,
	transcript_release CHAR(5) NOT NULL,
	PRIMARY KEY (intron_id, intron_release, transcript_id, transcript_release),
	FOREIGN KEY (intron_id, intron_release) REFERENCES intron_release (intron_id, intron_release)
	ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY (transcript_id, transcript_release) REFERENCES transcript_release (transcript_id, transcript_release)
	ON UPDATE CASCADE ON DELETE CASCADE
	)
   ENGINE    =       InnoDB
   COMMENT =      'intron release - transcript release table'
	;

/**************************************************/
/* PROTEIN RELEASE - TRANSCRIPT RELEASE TABLE */
/**************************************************/

/* One transcript can have more than one protein */
/* One protein can have more than one transcript */
/* Therefore, one transcript can pickup same exon */
/* with different proteins */

DROP TABLE IF EXISTS protein_release_transcript_release;
CREATE TABLE
	protein_release_transcript_release
	(
	protein_id MEDIUMINT(8) UNSIGNED ZEROFILL NOT NULL,
	protein_release VARCHAR(5) NOT NULL,
	transcript_id MEDIUMINT(8) UNSIGNED ZEROFILL NOT NULL,
	transcript_release CHAR(5) NOT NULL,
	PRIMARY KEY (protein_id, protein_release, transcript_id, transcript_release),
	FOREIGN KEY (protein_id, protein_release) REFERENCES protein_release (protein_id, protein_release)
	ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY (transcript_id, transcript_release) REFERENCES transcript_release (transcript_id, transcript_release)
	ON UPDATE CASCADE ON DELETE CASCADE
	)
   ENGINE    =       InnoDB
   COMMENT =      'protein release - transcript release table'
	;

/*******************************************/
/* SECIS RELEASE - TRANSCRIPT RELEASE TABLE */
/*******************************************/

/* One transcript can have more than one SECIS */
/* One SECIS can be in  more than one transcript */
/* Therefore, one transcript can pickup same exon */
/* but use different SECIS (it may be in coding region in some transcript) */

DROP TABLE IF EXISTS secis_release_transcript_release;
CREATE TABLE
	secis_release_transcript_release
	(
	secis_id MEDIUMINT(8) UNSIGNED ZEROFILL NOT NULL,
	secis_release  VARCHAR(5) NOT NULL,
	transcript_id MEDIUMINT(8) UNSIGNED ZEROFILL NOT NULL,
	transcript_release CHAR(5) NOT NULL,
	PRIMARY KEY (secis_id, secis_release, transcript_id, transcript_release),
	FOREIGN KEY (secis_id, secis_release) REFERENCES secis_release (secis_id, secis_release)
	ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY (transcript_id, transcript_release) REFERENCES transcript_release (transcript_id, transcript_release)
	ON UPDATE CASCADE ON DELETE CASCADE	
	)
   ENGINE    =       InnoDB
   COMMENT =      'secis release - transcript release table'
	;


/***********************************************/
/* RESIDUE RELEASE - PROTEIN RELEASE TABLE */
/***********************************************/

DROP TABLE IF EXISTS residue_release_protein_release;
CREATE TABLE
	residue_release_protein_release
	(
	residue_id MEDIUMINT(8) UNSIGNED ZEROFILL NOT NULL,
	residue_release  VARCHAR(5) NOT NULL,
	protein_id MEDIUMINT(8) UNSIGNED ZEROFILL NOT NULL,
	protein_release CHAR(5) NOT NULL,
	PRIMARY KEY (residue_id, residue_release, protein_id, protein_release),
	FOREIGN KEY (residue_id, residue_release) REFERENCES residue_release (residue_id, residue_release)
	ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY (protein_id, protein_release) REFERENCES protein_release (protein_id, protein_release)
	ON UPDATE CASCADE ON DELETE CASCADE	
	)
   ENGINE    =       InnoDB
   COMMENT =      'residue release - protein release table'
	;


/******************************************************************************************************/
/*********************************************** EXTERNAL REFERENCE LAYER *****************************/
/******************************************************************************************************/

/* WE WILL GIVE ONLY ONE EXTERNAL REFERENCE */

/* SELENOPROTEINS ARE NOT NECESSARLY WELL ANNOTATED */
/* IN THE EXTERNAL REFERENCE */

/* WE'LL PROVIDE ONE OF THESE REFERENCES */
/* ENSEMBL, GENBANK GENE, SPECIALIZED DATABASE OR NONE */

/*************************************/
/* SEQUENCE EXTERNAL REFERENCE TABLE */
/*************************************/

/* 1-TO-1 RELATIONSHIP WITH sequence TABLE */
/* SHARES PRIMARY KEY WITH sequence TABLE */
/* Sequence TABLE IS MANDATORY (1,1) (1,1) */

/* If genomic, sequence is a subregion of the original sequence */
/* sequence_ext_start and sequence_ext_end are relative to the external sequence */

DROP TABLE IF EXISTS sequence_release_ext_ref;
CREATE TABLE
	sequence_release_ext_ref
	(
	sequence_id SMALLINT(8) UNSIGNED ZEROFILL NOT NULL,
        sequence_release VARCHAR(5) NOT NULL,
        PRIMARY KEY (sequence_id, sequence_release),	
	FOREIGN KEY (sequence_id, sequence_release) REFERENCES sequence_release (sequence_id, sequence_release)
	ON UPDATE CASCADE ON DELETE CASCADE,
	sequence_type ENUM('Genome -assembly-','Genome -traces-','Transcript -mRNA/cDNA-','Transcript -EST-','Protein -complete-','Protein -fragment-') NOT NULL,
	sequence_ext_start INT(8) UNSIGNED ZEROFILL NOT NULL,
	sequence_ext_end INT(8) UNSIGNED ZEROFILL NOT NULL,
	sequence_ext_id VARCHAR(20) NOT NULL,
	db  VARCHAR(20) NOT NULL,
	db_link  VARCHAR(60) NOT NULL,
	sequence_ext_link VARCHAR(120) NOT NULL,
	updated TIMESTAMP NOT NULL,/* first timestamp: row update, set to NULL in imported data (auto-updates) */
	created TIMESTAMP NOT NULL/* second timestamp: row creation, set to NULL in imported data */	
	)
   ENGINE    =       InnoDB
   COMMENT =      'sequence external refeence'
	;

/***********************************************/
/* TRANSCRIPT RELEASE EXTERNAL REFERENCE TABLE */
/***********************************************/

/* WE'LL PROVIDE ONE OF THESE REFERENCES */
/* ENSEMBL, REFSEQ, TIGR, GENBANK EST OR NONE */

DROP TABLE IF EXISTS transcript_release_ext_ref;
CREATE TABLE
	transcript_release_ext_ref
	(
	transcript_id MEDIUMINT(8) UNSIGNED ZEROFILL NOT NULL,
	transcript_release CHAR(5) NOT NULL,
	PRIMARY KEY (transcript_id, transcript_release),
	FOREIGN KEY (transcript_id, transcript_release) REFERENCES transcript_release (transcript_id, transcript_release)
	ON UPDATE CASCADE ON DELETE CASCADE,
	annotation ENUM('Same','Different'), /* Same or different annotation in selenodb */
	transcript_release_ext_id VARCHAR(20) NOT NULL,
	db  VARCHAR(20) NOT NULL,
	db_link  VARCHAR(60) NOT NULL,
	transcript_release_ext_link VARCHAR(120) NOT NULL,
	updated TIMESTAMP NOT NULL, /* first timestamp: row update, set to NULL in imported data (auto-updates) */
	created TIMESTAMP NOT NULL /* second timestamp: row creation, set to NULL in imported data */	
	)
   ENGINE    =       InnoDB
   COMMENT =      'transcript external reference'
	;

/*****************************************/
/* GENE RELEASE EXTERNAL REFERENCE TABLE */
/*****************************************/

DROP TABLE IF EXISTS gene_release_ext_ref;
CREATE TABLE
	gene_release_ext_ref
	(
	gene_id SMALLINT(8) UNSIGNED ZEROFILL NOT NULL,
	gene_release VARCHAR(5) NOT NULL,	
	PRIMARY KEY (gene_id, gene_release),
	FOREIGN KEY (gene_id, gene_release) REFERENCES gene_release (gene_id, gene_release)
	ON UPDATE CASCADE ON DELETE CASCADE,
	annotation ENUM('Same','Different'),
	gene_release_ext_id VARCHAR(20) NOT NULL,
	db  VARCHAR(20) NOT NULL,
	db_link  VARCHAR(60) NOT NULL,
	gene_release_ext_link VARCHAR(120) NOT NULL,
	updated TIMESTAMP NOT NULL,/* first timestamp: row update, set to NULL in imported data (auto-updates) */
	created TIMESTAMP NOT NULL/* second timestamp: row creation, set to NULL in imported data */	
	)
   ENGINE    =       InnoDB
   COMMENT =      'gene external reference'
	;

/*****************************************/
/* EXON RELEASE EXTERNAL REFERENCE TABLE */
/*****************************************/

DROP TABLE IF EXISTS exon_release_ext_ref;
CREATE TABLE
	exon_release_ext_ref
	(
	exon_id MEDIUMINT(8) UNSIGNED ZEROFILL NOT NULL,
	exon_release VARCHAR(5) NOT NULL,	
	PRIMARY KEY (exon_id, exon_release),
	FOREIGN KEY (exon_id, exon_release) REFERENCES exon_release (exon_id, exon_release)
	ON UPDATE CASCADE ON DELETE CASCADE,
	annotation ENUM('Same','Different'),
	exon_release_ext_id VARCHAR(20) NOT NULL,
	db  VARCHAR(20) NOT NULL,
	db_link  VARCHAR(60) NOT NULL,
	exon_release_ext_link VARCHAR(120) NOT NULL,
	updated TIMESTAMP NOT NULL,/* first timestamp: row update, set to NULL in imported data (auto-updates) */
	created TIMESTAMP NOT NULL/* second timestamp: row creation, set to NULL in imported data */	
	)
   ENGINE    =       InnoDB
   COMMENT =      'exon external reference'
	;

/*****************************************/
/* INTRON RELEASE EXTERNAL REFERENCE TABLE */
/*****************************************/

DROP TABLE IF EXISTS intron_release_ext_ref;
CREATE TABLE
	intron_release_ext_ref
	(
	intron_id MEDIUMINT(8) UNSIGNED ZEROFILL NOT NULL,
	intron_release VARCHAR(5) NOT NULL,	
	PRIMARY KEY (intron_id, intron_release),
	FOREIGN KEY (intron_id, intron_release) REFERENCES intron_release (intron_id, intron_release)
	ON UPDATE CASCADE ON DELETE CASCADE,
	annotation ENUM('Same','Different'),
	intron_release_ext_id VARCHAR(20) NOT NULL,
	db  VARCHAR(20) NOT NULL,
	db_link  VARCHAR(60) NOT NULL,
	intron_release_ext_link VARCHAR(120) NOT NULL,
	updated TIMESTAMP NOT NULL,/* first timestamp: row update, set to NULL in imported data (auto-updates) */
	created TIMESTAMP NOT NULL/* second timestamp: row creation, set to NULL in imported data */	
	)
   ENGINE    =       InnoDB
   COMMENT =      'intron external reference'
	;

/***********************************************/
/* PROTEIN RELEASE EXTERNAL REFERENCE TABLE */
/***********************************************/

/* WE'LL PROVIDE ONE OF THESE REFERENCES */
/* ENSEMBL, UNIPROT, GENBANK PROTEIN OR NONE */

DROP TABLE IF EXISTS protein_release_ext_ref;
CREATE TABLE
	protein_release_ext_ref
	(
	protein_id MEDIUMINT(8) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
	protein_release VARCHAR(5) NOT NULL,
	PRIMARY KEY (protein_id, protein_release),
	FOREIGN KEY (protein_id, protein_release ) REFERENCES protein_release (protein_id, protein_release)
	ON UPDATE CASCADE ON DELETE CASCADE,
	annotation ENUM('Same','Different'),
	protein_release_ext_id VARCHAR(20) NOT NULL,
	db  VARCHAR(20) NOT NULL,
	db_link  VARCHAR(60) NOT NULL,
	protein_release_ext_link VARCHAR(120) NOT NULL,
	updated TIMESTAMP NOT NULL,/* first timestamp: row update, set to NULL in imported data (auto-updates) */
	created TIMESTAMP NOT NULL/* second timestamp: row creation, set to NULL in imported data */	
	)
   ENGINE    =       InnoDB
   COMMENT =      'protein external reference'
	;

/*****************************************/
/* SECIS RELEASE EXTERNAL REFERENCE TABLE */
/*****************************************/

DROP TABLE IF EXISTS secis_release_ext_ref;
CREATE TABLE
	secis_release_ext_ref
	(
	secis_id MEDIUMINT(8) UNSIGNED ZEROFILL NOT NULL,
	secis_release VARCHAR(5) NOT NULL,	
	PRIMARY KEY (secis_id, secis_release),
	FOREIGN KEY (secis_id, secis_release) REFERENCES secis_release (secis_id, secis_release)
	ON UPDATE CASCADE ON DELETE CASCADE,
	annotation ENUM('Same','Different'),
	secis_release_ext_id VARCHAR(20) NOT NULL,
	db  VARCHAR(20) NOT NULL,
	db_link  VARCHAR(60) NOT NULL,
	secis_release_ext_link VARCHAR(120) NOT NULL,
	updated TIMESTAMP NOT NULL,/* first timestamp: row update, set to NULL in imported data (auto-updates) */
	created TIMESTAMP NOT NULL/* second timestamp: row creation, set to NULL in imported data */	
	)
   ENGINE    =       InnoDB
   COMMENT =      'secis external reference'
	;

/*********************************************/
/* PROMOTER RELEASE EXTERNAL REFERENCE TABLE */
/*********************************************/

DROP TABLE IF EXISTS promoter_release_ext_ref;
CREATE TABLE
	promoter_release_ext_ref
	(
	promoter_id MEDIUMINT(8) UNSIGNED ZEROFILL NOT NULL,
	promoter_release VARCHAR(5) NOT NULL,	
	PRIMARY KEY (promoter_id, promoter_release),
	FOREIGN KEY (promoter_id, promoter_release) REFERENCES promoter_release (promoter_id, promoter_release)
	ON UPDATE CASCADE ON DELETE CASCADE,
	annotation ENUM('Same','Different'),
	promoter_release_ext_id VARCHAR(20) NOT NULL,
	db  VARCHAR(20) NOT NULL,
	db_link  VARCHAR(60) NOT NULL,
	promoter_release_ext_link VARCHAR(120) NOT NULL,
	updated TIMESTAMP NOT NULL,/* first timestamp: row update, set to NULL in imported data (auto-updates) */
	created TIMESTAMP NOT NULL/* second timestamp: row creation, set to NULL in imported data */	
	)
   ENGINE    =       InnoDB
   COMMENT =      'promoter external reference'
	;

/******************************************************************************************************/
/******************************** FEATURE RELEASE DESCRIPTION LAYER ***********************************/
/******************************************************************************************************/

/* These tables are to be modified by other researchers to add functional info */


/****************************/
/* EXON RELEASE DESCRIPTION */
/****************************/

DROP TABLE IF EXISTS exon_release_description;
CREATE TABLE
	exon_release_description
	(
	exon_id MEDIUMINT(8) UNSIGNED ZEROFILL NOT NULL,
	exon_release VARCHAR(5) NOT NULL,	
	PRIMARY KEY (exon_id, exon_release),
	FOREIGN KEY (exon_id, exon_release) REFERENCES exon_release (exon_id, exon_release)
	ON UPDATE CASCADE ON DELETE CASCADE,
	description TEXT NOT NULL,       /* length, maybe frame and reminder */
	function TEXT NOT NULL,          /* general domain function if mapped */
	updated TIMESTAMP NOT NULL, /* first timestamp: row update, set to NULL in imported data (auto-updates) */ 
	created TIMESTAMP NOT NULL /* second timestamp: row creation, set to NULL in imported data */
	)
   ENGINE    =       InnoDB
   COMMENT =      'exon description table'
	; 


/****************************/
/* INTRON RELEASE DESCRIPTION */
/****************************/

 DROP TABLE IF EXISTS intron_release_description;
CREATE TABLE
	intron_release_description
	(
	intron_id MEDIUMINT(8) UNSIGNED ZEROFILL NOT NULL,
	intron_release VARCHAR(5) NOT NULL,	
	PRIMARY KEY (intron_id, intron_release),
	FOREIGN KEY (intron_id, intron_release) REFERENCES intron_release (intron_id, intron_release)
	ON UPDATE CASCADE ON DELETE CASCADE,
	description TEXT NOT NULL,       /* length */
	function TEXT NOT NULL,          /* general enhancing/supressor function */
	updated TIMESTAMP NOT NULL, /* first timestamp: row update, set to NULL in imported data (auto-updates) */ 
	created TIMESTAMP NOT NULL /* second timestamp: row creation, set to NULL in imported data */
	)
   ENGINE    =       InnoDB
   COMMENT =      'intron description table'
	; 

/****************************/
/* GENE RELEASE DESCRIPTION */
/****************************/

DROP TABLE IF EXISTS gene_release_description;
CREATE TABLE
	gene_release_description
	(
	gene_id SMALLINT(8) UNSIGNED ZEROFILL NOT NULL,
	gene_release VARCHAR(5) NOT NULL,	
	PRIMARY KEY (gene_id, gene_release),
	FOREIGN KEY (gene_id, gene_release) REFERENCES gene_release (gene_id, gene_release)
	ON UPDATE CASCADE ON DELETE CASCADE,
	description TEXT NOT NULL,       /* how many transcripts, SECIS type... */
	function TEXT NOT NULL,          /* general function */
	updated TIMESTAMP NOT NULL, /* first timestamp: row update, set to NULL in imported data (auto-updates) */ 
	created TIMESTAMP NOT NULL /* second timestamp: row creation, set to NULL in imported data */
	)
   ENGINE    =       InnoDB
   COMMENT =      'gene description table'
	; 

/**********************************/
/* TRANSCRIPT RELEASE DESCRIPTION */
/**********************************/

DROP TABLE IF EXISTS transcript_release_description;
CREATE TABLE
	transcript_release_description
	(
	transcript_id MEDIUMINT(8) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
	transcript_release CHAR(5) NOT NULL,
	PRIMARY KEY (transcript_id, transcript_release),
	FOREIGN KEY (transcript_id, transcript_release) REFERENCES transcript_release (transcript_id, transcript_release)
	ON UPDATE CASCADE ON DELETE CASCADE,
	description TEXT NOT NULL,                /* how many exons, SECIS in exon ?... */
	function TEXT NOT NULL,                   /* specific (if any) transcript function */
	updated TIMESTAMP NOT NULL, /* first timestamp: row update, set to NULL in imported data (auto-updates) */
	created TIMESTAMP NOT NULL /* second timestamp: row creation, set to NULL in imported data */
	)
   ENGINE    =       InnoDB
   COMMENT =      'transcript description table'
	; 

/***********************************/
/* PROTEIN RELEASE DESCRIPTION */
/***********************************/

DROP TABLE IF EXISTS protein_release_description;
CREATE TABLE
	protein_release_description
	(
	protein_id MEDIUMINT(8) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
	protein_release VARCHAR(5) NOT NULL,
	PRIMARY KEY (protein_id, protein_release),
	FOREIGN KEY (protein_id, protein_release) REFERENCES protein_release (protein_id, protein_release)
	ON UPDATE CASCADE ON DELETE CASCADE,
	description TEXT NOT NULL,
	function TEXT NOT NULL, /* specific function */
	updated TIMESTAMP NOT NULL, /* first timestamp: row update, set to NULL in imported data (auto-updates) */
	created TIMESTAMP NOT NULL /* second timestamp: row creation, set to NULL in imported data */
	)
   ENGINE    =       InnoDB
   COMMENT =      'protein description table'
	; 

/*****************************/
/* SECIS RELEASE DESCRIPTION */
/*****************************/

/* It should be able to hold prokaryotic and eukaryotic SECIS */

DROP TABLE IF EXISTS secis_release_description;
CREATE TABLE
	secis_release_description
	(
	secis_id MEDIUMINT(8) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
	secis_release  VARCHAR(5) NOT NULL,
	PRIMARY KEY (secis_id, secis_release),
	FOREIGN KEY (secis_id, secis_release) REFERENCES secis_release (secis_id, secis_release)
	ON UPDATE CASCADE ON DELETE CASCADE,	
	description TEXT NOT NULL,
	type ENUM('1','2','To be added','N/A'),
	function TEXT NOT NULL,
	updated TIMESTAMP NOT NULL, /* first timestamp: row update, set to NULL in imported data (auto-updates) */
	created TIMESTAMP NOT NULL /* second timestamp: row creation, set to NULL in imported data */
	)
   ENGINE    =       InnoDB
   COMMENT =      'SECIS description table'
	; 

/**********************************/
/* PROMOTER RELEASE DESCRIPTION */
/**********************************/

DROP TABLE IF EXISTS promoter_release_description;
CREATE TABLE
	promoter_release_description
	(
	promoter_id MEDIUMINT(8) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
	promoter_release CHAR(5) NOT NULL,
	PRIMARY KEY (promoter_id, promoter_release),
	FOREIGN KEY (promoter_id, promoter_release) REFERENCES promoter_release (promoter_id, promoter_release)
	ON UPDATE CASCADE ON DELETE CASCADE,
	description TEXT NOT NULL,                /* how many exons, SECIS in exon ?... */
	function TEXT NOT NULL,                   /* specific (if any) promoter function */
	updated TIMESTAMP NOT NULL, /* first timestamp: row update, set to NULL in imported data (auto-updates) */
	created TIMESTAMP NOT NULL /* second timestamp: row creation, set to NULL in imported data */
	)
   ENGINE    =       InnoDB
   COMMENT =      'promoter description table'
	; 

/**********************************/
/* RESIDUE RELEASE DESCRIPTION */
/**********************************/

DROP TABLE IF EXISTS residue_release_description;
CREATE TABLE
	residue_release_description
	(
	residue_id MEDIUMINT(8) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
	residue_release CHAR(5) NOT NULL,
	PRIMARY KEY (residue_id, residue_release),
	FOREIGN KEY (residue_id, residue_release) REFERENCES residue_release (residue_id, residue_release)
	ON UPDATE CASCADE ON DELETE CASCADE,
	description TEXT NOT NULL,                /*  */
	function TEXT NOT NULL,                   /* specific (if any) residue function */
	updated TIMESTAMP NOT NULL, /* first timestamp: row update, set to NULL in imported data (auto-updates) */
	created TIMESTAMP NOT NULL /* second timestamp: row creation, set to NULL in imported data */
	)
   ENGINE    =       InnoDB
   COMMENT =      'promoter description table'
	; 

/******************************************************************************************************/
/******************************************** EXPRESSION LAYER ****************************************/
/******************************************************************************************************/

/*
DROP TABLE IF EXISTS transcript_expression;
CREATE TABLE
	transcript_expression
	(
	promoter_release_id
	updated TIMESTAMP NOT NULL, /* first timestamp: row update, set to NULL in imported data (auto-updates) 
	created TIMESTAMP NOT NULL /* second timestamp: row creation, set to NULL in imported data 
	)
   ENGINE    =       InnoDB
   COMMENT =      'expression table'
	; 
*/

/* NOT YET IMPLEMENTED */