#this script creates the data tables for PolyA Cleavage Site & 3' UTR Database (PACdb)

#aligned_seqs

CREATE TABLE `aligned_seqs` (
  `seq_id` int(11) NOT NULL default '0',
  `seq_type` enum('cds','cdna','est') NOT NULL default 'cds',
  `organism_id` varchar(12) NOT NULL default '',
  PRIMARY KEY  (`seq_id`,`seq_type`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1



#bugs

CREATE TABLE `bugs` (
  `bug_id` int(11) unsigned NOT NULL auto_increment,
  `reported_by` varchar(50) NOT NULL default '',
  `last_updated` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `location` text NOT NULL,
  `db_name` enum('atpacdb','pacdb') NOT NULL default 'atpacdb',
  `description` text NOT NULL,
  `status` enum('reported','assigned','fixed') NOT NULL default 'reported',
  `assigned_to` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`bug_id`)
) ENGINE=MyISAM AUTO_INCREMENT=20 DEFAULT CHARSET=latin1



#cluster_members

CREATE TABLE `cluster_members` (
  `cluster_id` int(11) unsigned NOT NULL default '0',
  `polya_site_id` int(11) NOT NULL default '0',
  `organism_id` varchar(12) NOT NULL default '',
  PRIMARY KEY  (`cluster_id`,`polya_site_id`),
  KEY `polya_site_id` (`polya_site_id`),
  KEY `cluster_id` (`cluster_id`),
  KEY `organism_id` (`organism_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1



#determination_methods

CREATE TABLE `determination_methods` (
  `determination_id` int(6) NOT NULL default '0',
  `description` varchar(255) NOT NULL default '',
  `type` enum('computational','experimental','unknown') default NULL,
  PRIMARY KEY  (`determination_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1



#genes

CREATE TABLE `genes` (
  `gene_id` int(11) NOT NULL default '0',
  `gene_name` varchar(255) NOT NULL default '',
  `cds_start` int(11) NOT NULL default '0',
  `cds_stop` int(11) NOT NULL default '0',
  `molecule` varchar(30) NOT NULL default '',
  `sense` enum('','+','-') NOT NULL default '',
  `organism_id` varchar(12) NOT NULL default '',
  PRIMARY KEY  (`gene_id`),
  KEY `gene_name` (`gene_name`),
  KEY `organism_id` (`organism_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1



#go_ontologies

CREATE TABLE `go_ontologies` (
  `go_id` varchar(15) NOT NULL default '',
  `go_aspect` char(2) NOT NULL default '',
  `go_annotation` varchar(255) NOT NULL default '',
  `go_definition` text NOT NULL,
  PRIMARY KEY  (`go_id`),
  KEY `go_id` (`go_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1



#id_lookup

CREATE TABLE `id_lookup` (
  `internal_id` int(11) NOT NULL default '0',
  `id_type` varchar(25) NOT NULL default 'accession',
  `seq_type` enum('gene','est','cds','cdna','protein','transcript') NOT NULL,
  `external_id` varchar(25) NOT NULL default '',
  PRIMARY KEY  (`internal_id`,`id_type`,`external_id`),
  KEY `internal_id` (`internal_id`),
  KEY `external_id` (`external_id`),
  KEY `seq_type` (`seq_type`),
  KEY `id_type` (`id_type`),
  KEY `internal_id__external_id` (`internal_id`,`external_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1



#links

CREATE TABLE `links` (
  `link_for` varchar(30) NOT NULL default '',
  `id_type` varchar(20) NOT NULL default '',
  `url` blob NOT NULL,
  `link_to` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`link_for`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1



#mccarrey_eddy

CREATE TABLE `mccarrey_eddy` (
  `seq_id` int(11) NOT NULL default '0',
  `cell_type` enum('pachytene','round','A spermatogonia','B spermatogonia','sertoli','peritubular','spermatocyte','preleptotene','primitive A spermatogonia','leptotene and zygotene') NOT NULL default 'pachytene',
  `accession` varchar(10) NOT NULL default '',
  PRIMARY KEY  (`seq_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1



#multi_stop_genes

CREATE TABLE `multi_stop_genes` (
  `gene` varchar(255) NOT NULL default '',
  `organism_id` varchar(12) NOT NULL default '',
  PRIMARY KEY  (`gene`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1



#organisms

CREATE TABLE `organisms` (
  `organism_id` varchar(12) NOT NULL default '',
  `organism_name` varchar(50) NOT NULL default '',
  `common_name` varchar(40) NOT NULL default '',
  `default_id_type` varchar(25) NOT NULL default 'none',
  `default_prot_id_type` varchar(25) NOT NULL default 'none',
  `genome_blastdb` varchar(20) NOT NULL default 'none',
  `blat_flatfiles` varchar(20) NOT NULL default 'none',
  `sg_experiment_id` smallint(5) unsigned default NULL,
  `genome_browser_db` varchar(10) default NULL,
  `conserved_regions` enum('N','Y') NOT NULL default 'N',
  PRIMARY KEY  (`organism_id`),
  KEY `organism_id` (`organism_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1



#pacdb_summary

CREATE TABLE `pacdb_summary` (
  `organism_id` varchar(12) NOT NULL default '',
  `organism_name` varchar(50) NOT NULL default '',
  `pac_site_count` int(11) NOT NULL default '0',
  `genes_count` int(11) NOT NULL default '0',
  `common_name` varchar(40) NOT NULL default '',
  `ests_count` int(11) NOT NULL default '0',
  `one_site_genes` int(11) NOT NULL default '0',
  `multi_site_genes` int(11) NOT NULL default '0',
  `singleton_ests` int(11) NOT NULL default '0',
  `unmapped_sites` int(11) NOT NULL default '0',
  `genes_with_internal_sites` int(11) NOT NULL default '0',
  `cdna_count` int(11) NOT NULL default '0',
  `cds_count` int(11) NOT NULL default '0',
  `internal_polya_sites` int(11) NOT NULL default '0',
  PRIMARY KEY  (`organism_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1



#polya_sites

CREATE TABLE `polya_sites` (
  `polya_site_id` int(11) NOT NULL default '0',
  `organism_id` varchar(12) NOT NULL default '',
  `gene_id` int(11) NOT NULL default '0',
  `molecule` varchar(30) NOT NULL default '',
  `sense` enum('','+','-') NOT NULL default '',
  `coordinate` int(11) NOT NULL default '0',
  `utr_length` int(11) default NULL,
  `upstream_seq` varchar(200) NOT NULL default '',
  `downstream_seq` varchar(200) NOT NULL default '',
  `determination_id` int(6) NOT NULL default '0',
  PRIMARY KEY  (`polya_site_id`),
  KEY `gene_id` (`gene_id`),
  KEY `organism_id` (`organism_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1



#requests

CREATE TABLE `requests` (
  `request_id` int(11) NOT NULL auto_increment,
  `query` blob NOT NULL,
  `return_type` varchar(10) NOT NULL default '',
  `output_format` varchar(5) NOT NULL default '',
  `perpage` char(3) NOT NULL default '25',
  `interface` varchar(10) NOT NULL default 'pacdb',
  `finished` blob NOT NULL,
  `request_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `ip_address` varchar(20) NOT NULL default '',
  PRIMARY KEY  (`request_id`)
) ENGINE=MyISAM AUTO_INCREMENT=6357 DEFAULT CHARSET=latin1



#seq_hits

CREATE TABLE `seq_hits` (
  `seq_id` int(11) NOT NULL default '0',
  `polya_site_id` int(11) NOT NULL default '0',
  `properties` set('A1','A2','repeats','polytail1','polytail2','lib-wide contam','vector','rev. complement','#ESThits1','#ESThits2','R1','R2','est_error1','est_error2','est_insert','multi-hit','dust','orimethod1','orimethod2','oriprediction','annot2','annot1','rsource') default NULL,
  `sg_run` smallint(5) unsigned NOT NULL default '0',
  `sg_queryid` int(10) unsigned NOT NULL default '0',
  `sg_subjectid` int(10) unsigned NOT NULL default '0',
  `sg_hitnumber` tinyint(3) unsigned NOT NULL default '0',
  PRIMARY KEY  (`seq_id`,`polya_site_id`),
  KEY `sg_run` (`sg_run`,`sg_queryid`,`sg_subjectid`,`sg_hitnumber`),
  KEY `polya_site_id` (`polya_site_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1



#site_clusters

CREATE TABLE `site_clusters` (
  `cluster_id` int(11) unsigned NOT NULL auto_increment,
  `polya_site_id` int(11) NOT NULL default '0',
  `multiplicity` smallint(5) unsigned NOT NULL default '0',
  `clustered_properties` set('A1','A2','repeats','polytail1','polytail2','lib-wide contam','vector','rev. complement','#ESThits1','#ESThits2','R1','R2','est_error1','est_error2','est_insert','multi-hit','dust','orimethod1','orimethod2','oriprediction','annot2','annot1','rsource') default NULL,
  `confidence_level` enum('Very Low','Low','Medium','High','Very High') default NULL,
  `cluster_window` tinyint(3) unsigned NOT NULL default '0',
  `organism_id` varchar(12) NOT NULL default '',
  PRIMARY KEY  (`cluster_id`),
  KEY `polya_site_id` (`polya_site_id`),
  KEY `organism_id` (`organism_id`),
  KEY `polya_site_id_2` (`polya_site_id`)
) ENGINE=MyISAM AUTO_INCREMENT=2071628 DEFAULT CHARSET=latin1



#siteselector

CREATE TABLE `siteselector` (
  `ss_id` int(11) unsigned NOT NULL auto_increment,
  `polya_site_id` int(11) default NULL,
  `gene_id` int(11) NOT NULL,
  `cds_stop` int(11) NOT NULL,
  `multiplicity` smallint(5) unsigned default NULL,
  `annotation_agreement` enum('Y','N') default NULL,
  `pas` enum('Y','V','N') default NULL,
  `ip_re` enum('Y','N') default NULL,
  `multiplicity_threshold` enum('Y','N') default NULL,
  `keep_throw` enum('K','T') default NULL,
  `utr_length` int(11) default NULL,
  `organism_id` varchar(12) NOT NULL,
  PRIMARY KEY  (`ss_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1