--
-- Host: localhost    Database: segex_dev_2015
-- ------------------------------------------------------


--
-- Table structure for table `GeneGO`
--

CREATE TABLE `GeneGO` (
  `gid` int(10) unsigned NOT NULL,
  `go_acc` int(7) unsigned zerofill NOT NULL,
  PRIMARY KEY  (`gid`,`go_acc`),
  KEY `gid` (`gid`),
  KEY `go_acc` (`go_acc`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Table structure for table `ProbeGene`
--

CREATE TABLE `ProbeGene` (
  `rid` int(10) unsigned NOT NULL,
  `gid` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`rid`,`gid`),
  KEY `rid` (`rid`),
  KEY `gid` (`gid`),
  CONSTRAINT `ProbeGene_ibfk_1` FOREIGN KEY (`rid`) REFERENCES `probe` (`rid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `ProjectStudy`
--

CREATE TABLE `ProjectStudy` (
  `prid` int(10) unsigned NOT NULL,
  `stid` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`prid`,`stid`),
  KEY `prid` (`prid`),
  KEY `stid` (`stid`),
  CONSTRAINT `ProjectStudy_ibfk_1` FOREIGN KEY (`prid`) REFERENCES `project` (`prid`) ON DELETE CASCADE,
  CONSTRAINT `ProjectStudy_ibfk_2` FOREIGN KEY (`stid`) REFERENCES `study` (`stid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `Sample1Experiment`
--

CREATE TABLE `Sample1Experiment` (
  `smid` int(10) unsigned NOT NULL,
  `eid` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`smid`,`eid`),
  KEY `smid` (`smid`),
  KEY `eid` (`eid`),
  CONSTRAINT `sample1experiment_ibfk_1` FOREIGN KEY (`eid`) REFERENCES `experiment` (`eid`) ON DELETE CASCADE,
  CONSTRAINT `sample1experiment_ibfk_2` FOREIGN KEY (`smid`) REFERENCES `sample` (`smid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `SampleExperiment`
--

CREATE TABLE `SampleExperiment` (
  `smid` int(10) unsigned NOT NULL,
  `eid` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`smid`,`eid`),
  KEY `eid` (`eid`),
  KEY `smid` (`smid`),
  CONSTRAINT `sampleexperiment_ibfk_1` FOREIGN KEY (`eid`) REFERENCES `experiment` (`eid`) ON DELETE CASCADE,
  CONSTRAINT `sampleexperiment_ibfk_2` FOREIGN KEY (`smid`) REFERENCES `sample` (`smid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `StudyExperiment`
--

CREATE TABLE `StudyExperiment` (
  `stid` int(10) unsigned NOT NULL,
  `eid` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`stid`,`eid`),
  KEY `eid` (`eid`),
  KEY `stid` (`stid`),
  CONSTRAINT `studyexperiment_ibfk_1` FOREIGN KEY (`eid`) REFERENCES `experiment` (`eid`) ON DELETE CASCADE,
  CONSTRAINT `studyexperiment_ibfk_2` FOREIGN KEY (`stid`) REFERENCES `study` (`stid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `analysis`
--

CREATE TABLE `analysis` (
  `yid` int(10) unsigned NOT NULL auto_increment,
  `qid` int(10) unsigned NOT NULL,
  `aid` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`yid`),
  KEY `analysis_ibfk_1` (`qid`),
  KEY `analysis_ibfk_2` (`aid`),
  CONSTRAINT `analysis_ibfk_2` FOREIGN KEY (`aid`) REFERENCES `analysis_method` (`aid`),
  CONSTRAINT `analysis_ibfk_1` FOREIGN KEY (`qid`) REFERENCES `quant_method` (`qid`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

--
-- Table structure for table `analysis_method`
--

CREATE TABLE `analysis_method` (
  `aid` int(10) unsigned NOT NULL auto_increment,
  `aname` varchar(64) NOT NULL,
  PRIMARY KEY  (`aid`),
  UNIQUE KEY `aname` (`aname`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

--
-- Table structure for table `experiment`
--

CREATE TABLE `experiment` (
  `eid` int(10) unsigned NOT NULL auto_increment,
  `public` tinyint(1) NOT NULL default '0',
  `sample1` varchar(255) NOT NULL default '',
  `sample2` varchar(255) NOT NULL default '',
  `ExperimentDescription` varchar(1023) NOT NULL default '',
  `AdditionalInformation` varchar(1023) NOT NULL default '',
  `AdditionalInformation2` varchar(1023) NOT NULL default '',
  `pid` int(10) unsigned NOT NULL,
  `PValFlag` tinyint(1) unsigned NOT NULL default '0',
  `s1id` int(10) unsigned default NULL,
  `s2id` int(10) unsigned default NULL,
  `FCFlag` tinyint(1) unsigned default NULL,
  PRIMARY KEY  (`eid`),
  KEY `pid` (`pid`),
  CONSTRAINT `experiment_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `platform` (`pid`)
) ENGINE=InnoDB AUTO_INCREMENT=212 DEFAULT CHARSET=latin1;

--
-- Table structure for table `gene`
--

CREATE TABLE `gene` (
  `gid` int(10) unsigned NOT NULL auto_increment,
  `sid` int(10) unsigned NOT NULL,
  `gtype` tinyint(3) unsigned NOT NULL default '0',
  `gsymbol` char(32) NOT NULL,
  `gname` varchar(1022) default NULL,
  `gdesc` varchar(2044) default NULL,
  PRIMARY KEY  (`gid`),
  UNIQUE KEY `sid_gsymbol` (`sid`,`gsymbol`),
  KEY `sid` (`sid`),
  FULLTEXT KEY `full` (`gsymbol`,`gname`,`gdesc`)
) ENGINE=MyISAM AUTO_INCREMENT=190582 DEFAULT CHARSET=latin1;

--
-- Table structure for table `go_term`
--

CREATE TABLE `go_term` (
  `go_acc` int(7) unsigned zerofill NOT NULL,
  `go_term_type` varchar(55) NOT NULL,
  `go_name` varchar(255) NOT NULL default '',
  `go_term_definition` text,
  `go_term_id` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`go_term_id`),
  UNIQUE KEY `go_acc` (`go_acc`),
  FULLTEXT KEY `full` (`go_name`,`go_term_definition`),
  FULLTEXT KEY `names` (`go_name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Table structure for table `library_prep`
--

CREATE TABLE `library_prep` (
  `lpid` int(10) unsigned NOT NULL auto_increment,
  `lpname` varchar(64) NOT NULL,
  PRIMARY KEY  (`lpid`),
  UNIQUE KEY `lpname` (`lpname`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

--
-- Table structure for table `locus`
--

CREATE TABLE `locus` (
  `rid` int(10) unsigned NOT NULL,
  `sid` int(10) unsigned NOT NULL,
  `chr` varchar(127) NOT NULL,
  `zinterval` geometry NOT NULL,
  SPATIAL KEY `zinterval` (`zinterval`),
  KEY `rid` (`rid`),
  KEY `sid_chr` (`sid`,`chr`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Table structure for table `platform`
--

CREATE TABLE `platform` (
  `pid` int(10) unsigned NOT NULL auto_increment,
  `pname` varchar(120) NOT NULL,
  `def_p_cutoff` double default NULL,
  `def_f_cutoff` double default NULL,
  `sid` int(10) unsigned default NULL,
  PRIMARY KEY  (`pid`),
  UNIQUE KEY `pname` (`pname`),
  KEY `sid` (`sid`),
  CONSTRAINT `platform_ibfk_1` FOREIGN KEY (`sid`) REFERENCES `species` (`sid`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1;

--
-- Table structure for table `probe`
--

CREATE TABLE `probe` (
  `rid` int(10) unsigned NOT NULL auto_increment,
  `reporter` char(18) NOT NULL,
  `probe_sequence` varchar(100) default NULL,
  `pid` int(10) unsigned NOT NULL,
  `probe_comment` varchar(2047) default NULL,
  PRIMARY KEY  (`rid`),
  UNIQUE KEY `pid_reporter` (`pid`,`reporter`),
  KEY `pid` (`pid`),
  CONSTRAINT `probe_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `platform` (`pid`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=436158 DEFAULT CHARSET=latin1;

--
-- Table structure for table `project`
--

CREATE TABLE `project` (
  `prid` int(10) unsigned NOT NULL auto_increment,
  `prname` varchar(255) NOT NULL default '',
  `prdesc` varchar(1023) NOT NULL default '',
  PRIMARY KEY  (`prid`),
  UNIQUE KEY `prname` (`prname`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

--
-- Table structure for table `quant_method`
--

CREATE TABLE `quant_method` (
  `qid` int(10) unsigned NOT NULL auto_increment,
  `qname` varchar(64) NOT NULL,
  PRIMARY KEY  (`qid`),
  UNIQUE KEY `qname` (`qname`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

--
-- Table structure for table `response`
--

CREATE TABLE `response` (
  `eid` int(10) unsigned NOT NULL,
  `rid` int(10) unsigned NOT NULL,
  `ratio` double default NULL,
  `foldchange` double default NULL,
  `intensity1` double default NULL,
  `intensity2` double default NULL,
  `pvalue1` double default NULL,
  `pvalue2` double default NULL,
  `pvalue3` double default NULL,
  `pvalue4` double default NULL,
  `yid` int(10) unsigned default NULL,
  PRIMARY KEY  (`eid`,`rid`),
  KEY `rid` (`rid`),
  KEY `eid` (`eid`),
  CONSTRAINT `response_ibfk_1` FOREIGN KEY (`eid`) REFERENCES `experiment` (`eid`) ON DELETE CASCADE,
  CONSTRAINT `response_ibfk_2` FOREIGN KEY (`rid`) REFERENCES `probe` (`rid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `sample`
--

CREATE TABLE `sample` (
  `smid` int(10) unsigned NOT NULL auto_increment,
  `smdesc` varchar(64) NOT NULL,
  `lpid` int(10) unsigned default NULL,
  `rsid` int(10) unsigned default NULL,
  `tid` int(10) unsigned default NULL,
  PRIMARY KEY  (`smid`),
  KEY `sample_ibfk_1` (`lpid`),
  KEY `sample_ibfk_2` (`rsid`),
  KEY `sample_ibfk_3` (`tid`),
  CONSTRAINT `sample_ibfk_1` FOREIGN KEY (`lpid`) REFERENCES `library_prep` (`lpid`),
  CONSTRAINT `sample_ibfk_2` FOREIGN KEY (`rsid`) REFERENCES `source` (`rsid`),
  CONSTRAINT `sample_ibfk_3` FOREIGN KEY (`tid`) REFERENCES `tissue` (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=316 DEFAULT CHARSET=latin1;

--
-- Temporary table structure for view `sample1`
--

/*!50001 DROP VIEW IF EXISTS `sample1`*/;
/*!50001 CREATE TABLE `sample1` (
  `smid1` int(10) unsigned,
  `smdesc1` varchar(64),
  `lpname1` varchar(64),
  `rsname1` varchar(64),
  `tname1` varchar(64)
) ENGINE=MyISAM */;

--
-- Temporary table structure for view `sample2`
--

/*!50001 DROP VIEW IF EXISTS `sample2`*/;
/*!50001 CREATE TABLE `sample2` (
  `smid2` int(10) unsigned,
  `smdesc2` varchar(64),
  `lpname2` varchar(64),
  `rsname2` varchar(64),
  `tname2` varchar(64)
) ENGINE=MyISAM */;

--
-- Temporary table structure for view `sample_view`
--

/*!50001 DROP VIEW IF EXISTS `sample_view`*/;
/*!50001 CREATE TABLE `sample_view` (
  `smid` int(10) unsigned,
  `smdesc` varchar(64),
  `lpname` varchar(64),
  `rsname` varchar(64),
  `tname` varchar(64)
) ENGINE=MyISAM */;

--
-- Table structure for table `sessions`
--

CREATE TABLE `sessions` (
  `id` char(32) NOT NULL,
  `a_session` text NOT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `source`
--

CREATE TABLE `source` (
  `rsid` int(10) unsigned NOT NULL auto_increment,
  `rsname` varchar(64) NOT NULL,
  PRIMARY KEY  (`rsid`),
  UNIQUE KEY `sname` (`rsname`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

--
-- Table structure for table `species`
--

CREATE TABLE `species` (
  `sid` int(10) unsigned NOT NULL auto_increment,
  `sname` varchar(64) NOT NULL,
  `slatin` varchar(64) default NULL,
  `sncbi` varchar(64) NOT NULL,
  `sversion` varchar(24) default NULL,
  PRIMARY KEY  (`sid`),
  UNIQUE KEY `sname` (`sname`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

--
-- Table structure for table `study`
--

CREATE TABLE `study` (
  `stid` int(10) unsigned NOT NULL auto_increment,
  `description` varchar(255) NOT NULL default '',
  `pubmed` varchar(255) NOT NULL default '',
  `pid` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`stid`),
  UNIQUE KEY `pid_description` (`pid`,`description`),
  KEY `pid` (`pid`),
  CONSTRAINT `study_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `platform` (`pid`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=43 DEFAULT CHARSET=latin1;

--
-- Table structure for table `tissue`
--

CREATE TABLE `tissue` (
  `tid` int(10) unsigned NOT NULL auto_increment,
  `tname` varchar(64) NOT NULL,
  PRIMARY KEY  (`tid`),
  UNIQUE KEY `tname` (`tname`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `uid` int(10) unsigned NOT NULL auto_increment,
  `uname` varchar(60) NOT NULL default '',
  `pwd` char(40) default NULL,
  `email` varchar(127) NOT NULL default '',
  `full_name` varchar(255) NOT NULL default '',
  `address` varchar(255) default NULL,
  `phone` varchar(127) default NULL,
  `level` enum('nogrants','readonly','user','admin') NOT NULL default 'nogrants',
  `email_confirmed` tinyint(1) default '0',
  `udate` timestamp NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`uid`),
  UNIQUE KEY `uname` (`uname`)
) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=latin1;

--
-- Dumping routines for database 'segex_dev_2015'
--
DELIMITER ;;
/*!50003 DROP FUNCTION IF EXISTS `format_locus` */;;
/*!50003 SET SESSION SQL_MODE=""*/;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 FUNCTION `format_locus`(chr varchar(127), zinterval geometry) RETURNS varchar(255) CHARSET latin1
    NO SQL
    DETERMINISTIC
BEGIN
DECLARE tuple_content varchar(127);
SET tuple_content = SUBSTRING_INDEX(SUBSTRING_INDEX(AsText(zinterval), '(', -1),')',1);
RETURN CONCAT('chr', chr, ':', FORMAT(SUBSTRING_INDEX(TRIM(SUBSTRING_INDEX(tuple_content, ',', 1)), ' ', -1), 0), '-', FORMAT(SUBSTRING_INDEX(TRIM(SUBSTRING_INDEX(tuple_content, ',', -1)), ' ', -1), 0));
    END */;;
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
DELIMITER ;

--
-- Final view structure for view `sample1`
--

/*!50001 DROP TABLE `sample1`*/;
/*!50001 DROP VIEW IF EXISTS `sample1`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `sample1` AS select `s`.`smid` AS `smid1`,`s`.`smdesc` AS `smdesc1`,`lp`.`lpname` AS `lpname1`,`sr`.`rsname` AS `rsname1`,`t`.`tname` AS `tname1` from (((`sample` `s` join `library_prep` `lp` on((`s`.`lpid` = `lp`.`lpid`))) join `source` `sr` on((`s`.`rsid` = `sr`.`rsid`))) join `tissue` `t` on((`s`.`tid` = `t`.`tid`))) */;

--
-- Final view structure for view `sample2`
--

/*!50001 DROP TABLE `sample2`*/;
/*!50001 DROP VIEW IF EXISTS `sample2`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `sample2` AS select `s`.`smid` AS `smid2`,`s`.`smdesc` AS `smdesc2`,`lp`.`lpname` AS `lpname2`,`sr`.`rsname` AS `rsname2`,`t`.`tname` AS `tname2` from (((`sample` `s` join `library_prep` `lp` on((`s`.`lpid` = `lp`.`lpid`))) join `source` `sr` on((`s`.`rsid` = `sr`.`rsid`))) join `tissue` `t` on((`s`.`tid` = `t`.`tid`))) */;

--
-- Final view structure for view `sample_view`
--

/*!50001 DROP TABLE `sample_view`*/;
/*!50001 DROP VIEW IF EXISTS `sample_view`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `sample_view` AS select `s`.`smid` AS `smid`,`s`.`smdesc` AS `smdesc`,`lp`.`lpname` AS `lpname`,`sr`.`rsname` AS `rsname`,`t`.`tname` AS `tname` from (((`sample` `s` join `library_prep` `lp` on((`s`.`lpid` = `lp`.`lpid`))) join `source` `sr` on((`s`.`rsid` = `sr`.`rsid`))) join `tissue` `t` on((`s`.`tid` = `t`.`tid`))) */;


