##############
### SQL script to create schema for EST_SSR database
##############

create database est_ssr;
use est_ssr;


SET FOREIGN_KEY_CHECKS = 0

drop table if exists `blast_res_hvgissr_bacpacpseudo`;

CREATE TABLE `blast_res_hvgissr_bacpacpseudo` (
  `Qacc` varchar(40) NOT NULL default '',
  `Sacc` varchar(25) NOT NULL default '',
  `perc_identity` decimal(5,2) NOT NULL default '0.00',
  `aln_len` mediumint(8) unsigned NOT NULL default '0',
  `mismatch` smallint(5) unsigned NOT NULL default '0',
  `gap_openings` smallint(5) unsigned NOT NULL default '0',
  `Qstart` mediumint(8) unsigned NOT NULL default '0',
  `Qend` mediumint(8) unsigned NOT NULL default '0',
  `Sstart` mediumint(8) unsigned NOT NULL default '0',
  `Send` mediumint(8) unsigned NOT NULL default '0',
  `E_val` varchar(10) NOT NULL default '',
  `bit_score` double NOT NULL default '0',
  `Repeated_HSP` tinyint(1) unsigned NOT NULL default '0',
  `HSP_id` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`HSP_id`)
) TYPE=MyISAM;


drop table if exists `blast_res_osgi_chr1pseudo`;

CREATE TABLE `blast_res_osgi_chr1pseudo` (
  `Qacc` varchar(40) NOT NULL default '',
  `Sacc` varchar(25) NOT NULL default '',
  `perc_identity` decimal(5,2) NOT NULL default '0.00',
  `aln_len` mediumint(8) unsigned NOT NULL default '0',
  `mismatch` smallint(5) unsigned NOT NULL default '0',
  `gap_openings` smallint(5) unsigned NOT NULL default '0',
  `Qstart` mediumint(8) unsigned NOT NULL default '0',
  `Qend` mediumint(8) unsigned NOT NULL default '0',
  `Sstart` mediumint(8) unsigned NOT NULL default '0',
  `Send` mediumint(8) unsigned NOT NULL default '0',
  `E_val` varchar(10) NOT NULL default '',
  `bit_score` double NOT NULL default '0',
  `Repeated_HSP` tinyint(1) unsigned NOT NULL default '0',
  `HSP_id` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`HSP_id`)
) TYPE=MyISAM COMMENT='All osgi vs rice chr 1 to estimate gene density';


drop table if exists `blast_res_osgissr_bacpacpseudo`;

CREATE TABLE `blast_res_osgissr_bacpacpseudo` (
  `Qacc` varchar(40) NOT NULL default '',
  `Sacc` varchar(25) NOT NULL default '',
  `perc_identity` decimal(5,2) NOT NULL default '0.00',
  `aln_len` mediumint(8) unsigned NOT NULL default '0',
  `mismatch` smallint(5) unsigned NOT NULL default '0',
  `gap_openings` smallint(5) unsigned NOT NULL default '0',
  `Qstart` mediumint(8) unsigned NOT NULL default '0',
  `Qend` mediumint(8) unsigned NOT NULL default '0',
  `Sstart` mediumint(8) unsigned NOT NULL default '0',
  `Send` mediumint(8) unsigned NOT NULL default '0',
  `E_val` varchar(10) NOT NULL default '',
  `bit_score` double NOT NULL default '0',
  `Repeated_HSP` tinyint(1) unsigned NOT NULL default '0',
  `HSP_id` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`HSP_id`)
) TYPE=MyISAM;


drop table if exists `blast_res_tagissr_bacpacpseudo`;

CREATE TABLE `blast_res_tagissr_bacpacpseudo` (
  `Qacc` varchar(40) NOT NULL default '',
  `Sacc` varchar(25) NOT NULL default '',
  `perc_identity` decimal(5,2) NOT NULL default '0.00',
  `aln_len` mediumint(8) unsigned NOT NULL default '0',
  `mismatch` smallint(5) unsigned NOT NULL default '0',
  `gap_openings` smallint(5) unsigned NOT NULL default '0',
  `Qstart` mediumint(8) unsigned NOT NULL default '0',
  `Qend` mediumint(8) unsigned NOT NULL default '0',
  `Sstart` mediumint(8) unsigned NOT NULL default '0',
  `Send` mediumint(8) unsigned NOT NULL default '0',
  `E_val` varchar(10) NOT NULL default '',
  `bit_score` double NOT NULL default '0',
  `Repeated_HSP` tinyint(1) unsigned NOT NULL default '0',
  `HSP_id` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`HSP_id`)
) TYPE=MyISAM;


drop table if exists `blast_summary_hvgissr_bacpacpseudo`;

CREATE TABLE `blast_summary_hvgissr_bacpacpseudo` (
  `Qacc` varchar(25) NOT NULL default '',
  `Sacc` varchar(25) NOT NULL default '',
  `gen_perc_identity` decimal(5,2) NOT NULL default '0.00',
  `aln_len_sum` mediumint(8) unsigned NOT NULL default '0',
  `QspanStart` mediumint(8) unsigned NOT NULL default '0',
  `QspanEnd` mediumint(8) unsigned NOT NULL default '0',
  `BPspanStart` mediumint(8) unsigned NOT NULL default '0',
  `BPspanEnd` mediumint(8) unsigned NOT NULL default '0',
  `E_val` varchar(10) NOT NULL default '',
  `sum_bit_score` double NOT NULL default '0',
  `HSP_count` tinyint(3) unsigned NOT NULL default '0',
  PRIMARY KEY  (`Qacc`,`Sacc`),
  KEY `Sacc` (`Sacc`)
) TYPE=MyISAM COMMENT='RepHSPs filtered out with blast_result_HSP_rep_check.pl ';


drop table if exists `blast_summary_osgi_chr1`;

CREATE TABLE `blast_summary_osgi_chr1` (
  `Qacc` varchar(40) NOT NULL default '',
  `Sacc` varchar(25) NOT NULL default '',
  `gen_perc_identity` decimal(5,2) NOT NULL default '0.00',
  `aln_len_sum` mediumint(8) unsigned NOT NULL default '0',
  `QspanStart` mediumint(8) unsigned NOT NULL default '0',
  `QspanEnd` mediumint(8) unsigned NOT NULL default '0',
  `BPspanStart` mediumint(8) unsigned NOT NULL default '0',
  `BPspanEnd` mediumint(8) unsigned NOT NULL default '0',
  `E_val` varchar(10) NOT NULL default '',
  `sum_bit_score` double NOT NULL default '0',
  `HSP_count` tinyint(3) unsigned NOT NULL default '0',
  PRIMARY KEY  (`Qacc`,`Sacc`),
  KEY `Sacc` (`Sacc`)
) TYPE=MyISAM;


drop table if exists `blast_summary_osgissr_bacpacpseudo`;

CREATE TABLE `blast_summary_osgissr_bacpacpseudo` (
  `Qacc` varchar(25) NOT NULL default '',
  `Sacc` varchar(25) NOT NULL default '',
  `gen_perc_identity` decimal(5,2) NOT NULL default '0.00',
  `aln_len_sum` mediumint(8) unsigned NOT NULL default '0',
  `QspanStart` mediumint(8) unsigned NOT NULL default '0',
  `QspanEnd` mediumint(8) unsigned NOT NULL default '0',
  `BPspanStart` mediumint(8) unsigned NOT NULL default '0',
  `BPspanEnd` mediumint(8) unsigned NOT NULL default '0',
  `E_val` varchar(10) NOT NULL default '',
  `sum_bit_score` double NOT NULL default '0',
  `HSP_count` tinyint(3) unsigned NOT NULL default '0',
  PRIMARY KEY  (`Qacc`,`Sacc`),
  KEY `Sacc` (`Sacc`)
) TYPE=MyISAM COMMENT='RepHSPs filtered out with blast_result_HSP_rep_check.pl ';


drop table if exists `blast_summary_tagissr_bacpacpseudo`;

CREATE TABLE `blast_summary_tagissr_bacpacpseudo` (
  `Qacc` varchar(25) NOT NULL default '',
  `Sacc` varchar(25) NOT NULL default '',
  `gen_perc_identity` decimal(5,2) NOT NULL default '0.00',
  `aln_len_sum` mediumint(8) unsigned NOT NULL default '0',
  `QspanStart` mediumint(8) unsigned NOT NULL default '0',
  `QspanEnd` mediumint(8) unsigned NOT NULL default '0',
  `BPspanStart` mediumint(8) unsigned NOT NULL default '0',
  `BPspanEnd` mediumint(8) unsigned NOT NULL default '0',
  `E_val` varchar(10) NOT NULL default '',
  `sum_bit_score` double NOT NULL default '0',
  `HSP_count` tinyint(3) unsigned NOT NULL default '0',
  PRIMARY KEY  (`Qacc`,`Sacc`),
  KEY `Sacc` (`Sacc`)
) TYPE=MyISAM COMMENT='RepHSPs filtered out with blast_result_HSP_rep_check.pl ';


drop table if exists `hvgi_est_ssr`;

CREATE TABLE `hvgi_est_ssr` (
  `SeqID` varchar(40) NOT NULL default '',
  `SSRtype` enum('mononucleotide','dinucleotide','trinucleotide','tetranucleotide','pentanucleotide') NOT NULL default 'mononucleotide',
  `start` mediumint(8) unsigned NOT NULL default '0',
  `end` mediumint(8) unsigned NOT NULL default '0',
  `len` smallint(5) unsigned NOT NULL default '0',
  `score` tinyint(3) unsigned NOT NULL default '0',
  `motif` varchar(5) default NULL,
  `sequence` varchar(250) default NULL,
  `id` mediumint(8) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`id`),
  KEY `SeqID` (`SeqID`)
) TYPE=MyISAM;


drop table if exists `irgp_gssr`;

CREATE TABLE `irgp_gssr` (
  `SeqID` varchar(25) NOT NULL default '',
  `SSRtype` enum('mononucleotide','dinucleotide','trinucleotide','tetranucleotide','pentanucleotide') NOT NULL default 'mononucleotide',
  `start` mediumint(8) unsigned NOT NULL default '0',
  `end` mediumint(8) unsigned NOT NULL default '0',
  `len` smallint(5) unsigned NOT NULL default '0',
  `score` tinyint(3) unsigned NOT NULL default '0',
  `motif` varchar(5) default NULL,
  `sequence` varchar(250) default NULL,
  `id` mediumint(8) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`id`),
  KEY `SeqID` (`SeqID`)
) TYPE=MyISAM COMMENT='Some Manual cleaning of DATA performed';


drop table if exists `osgi_est_ssr`;

CREATE TABLE `osgi_est_ssr` (
  `SeqID` varchar(40) NOT NULL default '',
  `SSRtype` enum('mononucleotide','dinucleotide','trinucleotide','tetranucleotide','pentanucleotide') NOT NULL default 'mononucleotide',
  `start` mediumint(8) unsigned NOT NULL default '0',
  `end` mediumint(8) unsigned NOT NULL default '0',
  `len` smallint(5) unsigned NOT NULL default '0',
  `score` tinyint(3) unsigned NOT NULL default '0',
  `motif` varchar(5) default NULL,
  `sequence` varchar(250) default NULL,
  `id` mediumint(8) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`id`),
  KEY `SeqID` (`SeqID`)
) TYPE=MyISAM COMMENT='filtered SO  SSR > 12 only, with score >= 75';


drop table if exists `pcr_primer_set`;

CREATE TABLE `pcr_primer_set` (
  `gene_index_name` enum('osgi_est_ssr','hvgi_est_ssr','tagi_est_ssr') NOT NULL default 'osgi_est_ssr',
  `est_ssr_id` mediumint(8) unsigned NOT NULL default '0',
  `primer_set_rank` tinyint(1) unsigned NOT NULL default '0',
  `forw_primer_seq` varchar(30) NOT NULL default '',
  `forw_primer_start` mediumint(8) unsigned NOT NULL default '0',
  `forw_primer_length` tinyint(3) unsigned NOT NULL default '0',
  `forw_primer_tm` decimal(10,2) NOT NULL default '0.00',
  `forw_primer_GC` decimal(3,1) NOT NULL default '0.0',
  `rev_primer_seq` varchar(30) NOT NULL default '',
  `rev_primer_start` mediumint(8) unsigned NOT NULL default '0',
  `rev_primer_length` tinyint(3) unsigned NOT NULL default '0',
  `rev_primer_tm` decimal(10,2) NOT NULL default '0.00',
  `rev_primer_GC` decimal(3,1) NOT NULL default '0.0',
  `expected_product_size` smallint(4) unsigned NOT NULL default '0',
  `id` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `ESTSSRprimerset_idx` (`gene_index_name`,`est_ssr_id`,`primer_set_rank`)
) TYPE=MyISAM;


drop table if exists `redundancy_check`;

CREATE TABLE `redundancy_check` (
  `IRGP_gSSRid` mediumint(8) unsigned NOT NULL default '0',
  `SeqID` varchar(25) NOT NULL default '',
  `InTilingPath` tinyint(1) unsigned NOT NULL default '0',
  `InOverlap` tinyint(1) unsigned NOT NULL default '0',
  PRIMARY KEY  (`IRGP_gSSRid`)
) TYPE=MyISAM COMMENT='Clear AND Update this table with   ProcessBACoverlap_SSR.pl';


drop table if exists `ssrtables were cleaned`;

CREATE TABLE `ssrtables were cleaned` (
  `empty` int(11) NOT NULL default '0'
) TYPE=MyISAM COMMENT='cleaned from NNN type SSRs and lne < 12 and other things';


drop table if exists `tagi_est_ssr`;

CREATE TABLE `tagi_est_ssr` (
  `SeqID` varchar(40) NOT NULL default '',
  `SSRtype` enum('mononucleotide','dinucleotide','trinucleotide','tetranucleotide','pentanucleotide') NOT NULL default 'mononucleotide',
  `start` mediumint(8) unsigned NOT NULL default '0',
  `end` mediumint(8) unsigned NOT NULL default '0',
  `len` smallint(5) unsigned NOT NULL default '0',
  `score` tinyint(3) unsigned NOT NULL default '0',
  `motif` varchar(5) default NULL,
  `sequence` varchar(250) default NULL,
  `id` mediumint(8) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`id`),
  KEY `SeqID` (`SeqID`)
) TYPE=MyISAM;


drop table if exists `temp1`;

CREATE TABLE `temp1` (
  `Qacc` varchar(40) NOT NULL default '0',
  `max_sum_bit_score` double NOT NULL default '0',
  PRIMARY KEY  (`Qacc`)
) TYPE=HEAP;


drop table if exists `tmp_best_hvgissr_rgphits`;

CREATE TABLE `tmp_best_hvgissr_rgphits` (
  `Qacc` varchar(25) NOT NULL default '',
  `Sacc` varchar(25) NOT NULL default '',
  `gen_perc_identity` decimal(5,2) NOT NULL default '0.00',
  `aln_len_sum` mediumint(8) unsigned NOT NULL default '0',
  `QspanStart` mediumint(8) unsigned NOT NULL default '0',
  `QspanEnd` mediumint(8) unsigned NOT NULL default '0',
  `BPspanStart` mediumint(8) unsigned NOT NULL default '0',
  `BPspanEnd` mediumint(8) unsigned NOT NULL default '0',
  `E_val` varchar(10) NOT NULL default '',
  `sum_bit_score` double NOT NULL default '0',
  `HSP_count` tinyint(3) unsigned NOT NULL default '0',
  PRIMARY KEY  (`Qacc`,`Sacc`),
  KEY `Sacc` (`Sacc`)
) TYPE=MyISAM;


drop table if exists `tmp_best_osgissr_rgphits`;

CREATE TABLE `tmp_best_osgissr_rgphits` (
  `Qacc` varchar(25) NOT NULL default '',
  `Sacc` varchar(25) NOT NULL default '',
  `gen_perc_identity` decimal(5,2) NOT NULL default '0.00',
  `aln_len_sum` mediumint(8) unsigned NOT NULL default '0',
  `QspanStart` mediumint(8) unsigned NOT NULL default '0',
  `QspanEnd` mediumint(8) unsigned NOT NULL default '0',
  `BPspanStart` mediumint(8) unsigned NOT NULL default '0',
  `BPspanEnd` mediumint(8) unsigned NOT NULL default '0',
  `E_val` varchar(10) NOT NULL default '',
  `sum_bit_score` double NOT NULL default '0',
  `HSP_count` tinyint(3) unsigned NOT NULL default '0',
  PRIMARY KEY  (`Qacc`,`Sacc`),
  KEY `Sacc` (`Sacc`)
) TYPE=MyISAM;


drop table if exists `tmp_best_tagissr_rgphits`;

CREATE TABLE `tmp_best_tagissr_rgphits` (
  `Qacc` varchar(25) NOT NULL default '',
  `Sacc` varchar(25) NOT NULL default '',
  `gen_perc_identity` decimal(5,2) NOT NULL default '0.00',
  `aln_len_sum` mediumint(8) unsigned NOT NULL default '0',
  `QspanStart` mediumint(8) unsigned NOT NULL default '0',
  `QspanEnd` mediumint(8) unsigned NOT NULL default '0',
  `BPspanStart` mediumint(8) unsigned NOT NULL default '0',
  `BPspanEnd` mediumint(8) unsigned NOT NULL default '0',
  `E_val` varchar(10) NOT NULL default '',
  `sum_bit_score` double NOT NULL default '0',
  `HSP_count` tinyint(3) unsigned NOT NULL default '0',
  PRIMARY KEY  (`Qacc`,`Sacc`),
  KEY `Sacc` (`Sacc`)
) TYPE=MyISAM;


SET FOREIGN_KEY_CHECKS = 1