-- ------------------------------------------------------------------------------------------------------

-- "Ensemble" is the proprietary property of The Regents of the University of California ("The Regents.")
-- 
-- Copyright (c) 2005-10 The Regents of the University of California, Davis campus. All Rights Reserved.
--
-- Redistribution and use in source and binary forms, with or without modification, are permitted by 
-- nonprofit, research institutions for research use only, provided the conditions in the included
-- licence agreement are met. 
--
-- Refer to the file "ensemble_license.txt" for the license agreement,
-- located in the top level directory of this distribution.

-- ------------------------------------------------------------------------------------------------------
-- Ensemble Schema v. 1.6.0
-- May 25, 2010


-- phpMyAdmin SQL Dump
-- version 2.11.5.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: May 25, 2010 at 01:22 PM
-- Server version: 5.0.77
-- PHP Version: 5.1.6

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

CREATE DATABASE IF NOT EXISTS ensemble;
USE ensemble;

--
-- Database: `ensemble_dev`
--

-- --------------------------------------------------------

--
-- Table structure for table `attribute`
--

CREATE TABLE IF NOT EXISTS `attribute` (
  `attribute_id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(50) NOT NULL default '',
  `class` enum('genre','stim_set','parent_category','subject_group','theme') NOT NULL default 'stim_set',
  PRIMARY KEY  (`attribute_id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `attribute_x_attribute`
--

CREATE TABLE IF NOT EXISTS `attribute_x_attribute` (
  `attribute_id_child` int(11) NOT NULL default '0',
  `attribute_id_parent` int(11) NOT NULL default '0',
  `parent_name` varchar(128) default NULL,
  PRIMARY KEY  (`attribute_id_child`,`attribute_id_parent`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `subject_x_attribute`
--

CREATE TABLE IF NOT EXISTS `subject_x_attribute` (
  `subject_id` varchar(10) NOT NULL default '0',
  `attribute_id` int(11) NOT NULL default '0',
  `attribute_value_double` double default NULL,
  `attribute_value_text` text,
  PRIMARY KEY  (`subject_id`,`attribute_id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `data_format`
--

CREATE TABLE IF NOT EXISTS `data_format` (
  `data_format_id` int(10) unsigned NOT NULL auto_increment,
  `type` varchar(15) NOT NULL default '',
  `enum_values` text,
  PRIMARY KEY  (`data_format_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 PACK_KEYS=0;

INSERT INTO `data_format` (`data_format_id`, `type`, `enum_values`) VALUES
(1, 'varchar', NULL),
(2, 'text', NULL),
(3, 'int16', NULL),
(4, 'int32', NULL),
(5, 'int64', NULL),
(6, 'double', NULL),
(7, 'date', NULL),
(8, 'time', NULL),
(9, 'year', NULL),
(10, 'null', NULL);


-- --------------------------------------------------------

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

CREATE TABLE IF NOT EXISTS `experiment` (
  `experiment_id` int(10) unsigned NOT NULL auto_increment,
  `start_date` date default NULL,
  `experiment_title` varchar(50) NOT NULL default '',
  `experiment_description` text,
  `response_table` varchar(50) NOT NULL,
  `irb_id` varchar(30) default NULL,
  `end_date` date default NULL,
  `language` varchar(30) NOT NULL default 'english',
  `play_question_audio` enum('T','F') NOT NULL default 'F',
  `encrypted_response_table` enum('T','F') NOT NULL default 'F',
  `params` text,
  `locked` enum('T','F') NOT NULL default 'F',
  PRIMARY KEY  (`experiment_id`),
  UNIQUE KEY `experiment_title` (`experiment_title`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 PACK_KEYS=0;

-- --------------------------------------------------------

--
-- Table structure for table `experiment_x_form`
--

CREATE TABLE IF NOT EXISTS `experiment_x_form` (
  `experiment_id` int(10) unsigned NOT NULL default '0',
  `form_id` int(10) unsigned NOT NULL default '0',
  `form_order` int(10) unsigned NOT NULL default '0',
  `form_handler` varchar(50) default NULL,
  `goto` int(11) default NULL,
  `repeat` int(11) default NULL,
  `condition` text,
  `condition_matlab` text,
  `stimulus_matlab` text,
  `break_loop_button` enum('F','T') NOT NULL default 'F',
  `break_loop_button_text` varchar(50) default NULL,
  PRIMARY KEY  (`experiment_id`,`form_id`,`form_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `experiment_x_stimulus`
--

CREATE TABLE IF NOT EXISTS `experiment_x_stimulus` (
  `experiment_id` int(10) unsigned NOT NULL default '0',
  `stimulus_id` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`experiment_id`,`stimulus_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `form`
--

CREATE TABLE IF NOT EXISTS `form` (
  `form_id` int(10) unsigned NOT NULL auto_increment,
  `form_name` varchar(50) NOT NULL default '',
  `form_category` enum('general background','musical background','experiment specific','standard question','multi_stimulus','tarp','session_control') default NULL,
  `header` text,
  `footer` text,
  `header_audio_path` varchar(50) default NULL,
  `footer_audio_path` varchar(50) default NULL,
  `version` float default NULL,
  `locked` enum('T','F') NOT NULL default 'F',
  `condition` text,
  `condition_matlab` varchar(40) default NULL,
  `visit_once` enum('T','F') NOT NULL default 'F',
  PRIMARY KEY  (`form_id`),
  UNIQUE KEY `form_name` (`form_name`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 PACK_KEYS=0;


INSERT INTO `form` (`form_id`, `form_name`, `form_category`, `header`, `footer`, `version`, `locked`, `condition`, `condition_matlab`, `visit_once`) VALUES
(1, 'start_session_info_v1p0', 'general background', 'Please click the "Next >>>" button to begin.', NULL, 1, 'T', NULL, NULL, 'F'),
(2, 'end_session', 'general background', 'This concludes the experiment.<br>Thank you for your participation!', NULL, 1, 'F', NULL, NULL, 'F'),
(3, 'subject_id', 'general background', NULL, NULL, 1, 'F', NULL, NULL, 'F');

-- --------------------------------------------------------

--
-- Table structure for table `form_x_question`
--

CREATE TABLE IF NOT EXISTS `form_x_question` (
  `form_id` int(10) unsigned NOT NULL default '0',
  `question_id` int(10) unsigned NOT NULL default '0',
  `question_iteration` tinyint(3) unsigned NOT NULL default '1',
  `form_question_num` smallint(5) unsigned NOT NULL default '0',
  PRIMARY KEY  (`form_id`,`question_iteration`,`form_question_num`,`question_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `form_x_trial`
--

CREATE TABLE IF NOT EXISTS `form_x_trial` (
  `ft_id` int(10) unsigned NOT NULL auto_increment,
  `form_id` int(10) unsigned NOT NULL default '0',
  `trial_id` int(10) unsigned NOT NULL default '0',
  `trial_order` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`ft_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 PACK_KEYS=0;

-- --------------------------------------------------------

--
-- Table structure for table `question`
--

CREATE TABLE IF NOT EXISTS `question` (
  `question_id` int(10) unsigned NOT NULL auto_increment,
  `question_text` text,
  `question_category` enum('general background','musical background','experiment specific','tarp') default NULL,
  `heading_format` enum('single_quest','multi-title','multi-question') NOT NULL default 'single_quest',
  `locked` enum('T','F') NOT NULL default 'F',
  PRIMARY KEY  (`question_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 PACK_KEYS=0;

-- --------------------------------------------------------

--
-- Table structure for table `question_x_data_format`
--

CREATE TABLE IF NOT EXISTS `question_x_data_format` (
  `qdf_id` int(10) unsigned NOT NULL auto_increment,
  `question_id` int(10) unsigned NOT NULL default '0',
  `subquestion` tinyint(3) unsigned NOT NULL default '1',
  `answer_format_id` int(10) unsigned NOT NULL default '10',
  `heading` text,
  `range` varchar(30) default NULL,
  `default` varchar(30) default NULL,
  `html_field_type` enum('text','textarea','menu','radiogroup','checkbox','multimenu') default NULL,
  `audio_path` varchar(50) default NULL,
  `required` enum('F','T') NOT NULL default 'T',
  PRIMARY KEY  (`qdf_id`),
  KEY `answer_format_id` (`answer_format_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 PACK_KEYS=0;

-- --------------------------------------------------------

--
-- Table structure for table `session`
--

CREATE TABLE IF NOT EXISTS `session` (
  `session_id` int(10) unsigned NOT NULL auto_increment,
  `date_time` datetime default NULL,
  `end_datetime` datetime default NULL,
  `experiment_id` int(10) unsigned default NULL,
  `ticket_id` int(10) unsigned default NULL,
  `subject_id` varchar(50) default NULL,
  `php_session_id` varchar(70) default NULL,
  PRIMARY KEY  (`session_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 PACK_KEYS=0;

-- --------------------------------------------------------

--
-- Table structure for table `stimulus`
--

CREATE TABLE IF NOT EXISTS `stimulus` (
  `stimulus_id` int(10) unsigned NOT NULL auto_increment,
  `name` text NOT NULL,
  `description` varchar(30) NOT NULL default '',
  `playlist` varchar(50) default NULL,
  `artist` text,
  `album` text,
  `genre` text,
  `file_format` enum('aif','wav','snd','mp3','txt','mov') default NULL,
  `size` int(11) default NULL,
  `duration` time default NULL,
  `year` year(4) default NULL,
  `compression_bit_rate` int(11) default NULL,
  `sample_rate` int(11) default NULL,
  `sample_size` tinyint(3) unsigned default NULL,
  `channels` tinyint(3) unsigned default NULL,
  `width` int(11) NOT NULL default '0',
  `height` int(11) NOT NULL default '0',
  `location` text NOT NULL,
  PRIMARY KEY  (`stimulus_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 PACK_KEYS=0;

-- --------------------------------------------------------

--
-- Table structure for table `stimulus_x_attribute`
--

CREATE TABLE IF NOT EXISTS `stimulus_x_attribute` (
  `stimulus_id` int(11) NOT NULL default '0',
  `attribute_id` int(11) NOT NULL default '0',
  `attribute_value_double` double default NULL,
  `attribute_value_text` text,
  PRIMARY KEY  (`stimulus_id`,`attribute_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `subject`
--

CREATE TABLE IF NOT EXISTS `subject` (
  `subject_id` varchar(10) NOT NULL default '',
  `date_entered` date default NULL,
  `name_last` blob,
  `name_first` blob,
  `name_middle` blob,
  `name_suffix` blob,
  `passphrase` blob,
  `security_questions` text,
  `security_responses` blob,
  `email` blob,
  `phone1` blob,
  `phone2` blob,
  `address1` blob,
  `address2` blob,
  `address3` blob,
  `city` blob,
  `county` blob,
  `state` blob,
  `postal_code` blob,
  `gender` enum('F','M') default NULL,
  `ethnic_category` enum('HL','not_HL','unknown') default NULL,
  `racial_category` set('Unknown','AmInd','Asian','NatHaw','Black','Cauc','More','Other') default NULL,
  `dob` blob,
  `notes` text,
  PRIMARY KEY  (`subject_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `ticket`
--

CREATE TABLE IF NOT EXISTS `ticket` (
  `ticket_id` int(10) unsigned NOT NULL auto_increment,
  `ticket_code` char(32) NOT NULL default '',
  `experiment_id` int(10) unsigned NOT NULL default '0',
  `type` enum('user','master') NOT NULL default 'user',
  `used` enum('F','T') NOT NULL default 'F',
  `expiration_datetime` datetime default NULL,
  `session_id` int(10) unsigned default NULL,
  `assigned` enum('F','T') NOT NULL default 'F',
  PRIMARY KEY  (`ticket_id`),
  UNIQUE KEY `ticket_code` (`ticket_code`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `trial`
--

CREATE TABLE IF NOT EXISTS `trial` (
  `trial_id` int(10) unsigned NOT NULL auto_increment,
  `stimulus_id1` int(11) default NULL,
  `stimulus_id2` int(11) default NULL,
  `question_id` int(10) unsigned default NULL,
  `data_format_id` int(11) default NULL,
  `correct_response_enum` int(11) default NULL,
  `correct_response_text` varchar(50) default NULL,
  PRIMARY KEY  (`trial_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 PACK_KEYS=0;

-- --------------------------------------------------------

--
-- Table structure for table `trial_x_attribute`
--

CREATE TABLE IF NOT EXISTS `trial_x_attribute` (
  `trial_id` int(11) NOT NULL default '0',
  `attribute_id` int(11) NOT NULL default '0',
  `attribute_value_double` double default NULL,
  `attribute_value_text` text,
  PRIMARY KEY  (`trial_id`,`attribute_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
