--**************************************************
--** SQl script for creating CLARITY tables       **
--**  clarity.sql                                 **
--**************************************************


--**  table for storing phylogenetic tree         **


CREATE DATABASE clarity;

USE clarity;

CREATE TABLE timecourse_data (
  id INTEGER NOT NULL AUTO_INCREMENT,
  datafile_path VARCHAR(100) NOT NULL,
  creation_date VARCHAR(45) NOT NULL,
  association_file VARCHAR(100) NOT NULL,
  patient_id INTEGER,
  PRIMARY KEY (id)
);


--**  table for storing gantt charts              **
CREATE TABLE gantt_chart (
  id INTEGER NOT NULL AUTO_INCREMENT,
  timecourse_data_id INTEGER DEFAUlT '0',
  term_name VARCHAR(200),
  sequence VARCHAR(45) NOT NULL,
  PRIMARY KEY(id)
);


--**  table for storing numerical data            **
CREATE TABLE numerical_data (
       id INTEGER NOT NULL AUTO_INCREMENT,
       timecourse_data_id INTEGER,
       term_name VARCHAR(100),
       window_start FLOAT,
       window_end FLOAT,
       data FLOAT,
       probe_ids TEXT,
       PRIMARY KEY(id)
);


--**  table for storing phylogenetic tree         **
CREATE TABLE tree (
	id INTEGER NOT NULL AUTO_INCREMENT,
	timecourse_data_id INTEGER DEFAULT '0',
	node_left INTEGER DEFAULT '0',
	node_right INTEGER DEFAULT '0',
	is_root INTEGER  DEFAULT '0',
	consensus_id INTEGER DEFAULT '0',
	PRIMARY KEY(id)
);

--**  table for storing consensus sequences      **
--**        for phylogenetic tree                **

CREATE TABLE consensus (
       id INTEGER NOT NULL AUTO_INCREMENT,
       tree_id INTEGER DEFAULT '0',
       term_name VARCHAR(100) NOT NULL,
       sequence VARCHAR(45) NOT NULL,
       PRIMARY KEY(id)
);
