-- --------------------------------------------------------
-- Host:                         163.160.171.164
-- Server version:               5.0.67-log - SUSE MySQL RPM
-- Server OS:                    suse-linux-gnu
-- HeidiSQL Version:             8.1.0.4545
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

-- Dumping structure for table hmrn.antecedent_events
CREATE TABLE IF NOT EXISTS `antecedent_events` (
  `id` int(11) NOT NULL auto_increment,
  `event` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Data exporting was unselected.


-- Dumping structure for table hmrn.categories
CREATE TABLE IF NOT EXISTS `categories` (
  `id` smallint(6) NOT NULL auto_increment,
  `category` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Data exporting was unselected.


-- Dumping structure for table hmrn.category_parameter
CREATE TABLE IF NOT EXISTS `category_parameter` (
  `category_id` smallint(6) NOT NULL default '0',
  `parameter_id` smallint(6) NOT NULL default '0',
  PRIMARY KEY  (`category_id`,`parameter_id`),
  KEY `parameter_id` (`parameter_id`),
  CONSTRAINT `category_parameter_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`),
  CONSTRAINT `category_parameter_ibfk_2` FOREIGN KEY (`parameter_id`) REFERENCES `parameters` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Data exporting was unselected.


-- Dumping structure for table hmrn.defaults_and_ranges
CREATE TABLE IF NOT EXISTS `defaults_and_ranges` (
  `param_id` int(11) NOT NULL default '0',
  `min_value` decimal(2,1) NOT NULL default '0.0',
  `max_value` decimal(10,1) NOT NULL default '0.0',
  `not_stated` decimal(10,1) NOT NULL default '0.0',
  `not_done` decimal(10,1) NOT NULL default '0.0',
  PRIMARY KEY  (`param_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT;

-- Data exporting was unselected.


-- Dumping structure for table hmrn.events
CREATE TABLE IF NOT EXISTS `events` (
  `id` smallint(6) NOT NULL auto_increment,
  `description` varchar(50) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Data exporting was unselected.


-- Dumping structure for table hmrn.history
CREATE TABLE IF NOT EXISTS `history` (
  `id` int(11) NOT NULL auto_increment,
  `patient_id` int(11) NOT NULL default '0',
  `user_id` smallint(6) NOT NULL default '0',
  `action` varchar(255) NOT NULL default '',
  `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Data exporting was unselected.


-- Dumping structure for table hmrn.imaging_options
CREATE TABLE IF NOT EXISTS `imaging_options` (
  `id` smallint(6) NOT NULL auto_increment,
  `description` varchar(50) NOT NULL default '',
  `option_type` enum('upper_nodal','lower_nodal','extranodal','flag') default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Data exporting was unselected.


-- Dumping structure for table hmrn.locations
CREATE TABLE IF NOT EXISTS `locations` (
  `id` int(11) NOT NULL auto_increment,
  `location` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Data exporting was unselected.

-- Dumping structure for table hmrn.menu_options
/* // replaced with menu_items
CREATE TABLE IF NOT EXISTS `menu_options` (
  `param_id` int(11) NOT NULL default '0',
  `options` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`param_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
*/
CREATE TABLE IF NOT EXISTS `menu_items` (
	`item_id` SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
	`item_value` VARCHAR(255) NOT NULL DEFAULT '0',
	PRIMARY KEY (`item_id`),
	UNIQUE INDEX `item_value` (`item_value`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- Data exporting was unselected.

-- Dumping structure for table hmrn.parameters
CREATE TABLE IF NOT EXISTS `parameters` (
  `id` smallint(6) NOT NULL auto_increment,
  `param_name` varchar(25) NOT NULL default '',
  `field_type` enum('int','decimal','menu') NOT NULL default 'int',
  `is_active` enum('yes','no') NOT NULL default 'yes',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `param_name` (`param_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT;

-- Data exporting was unselected.

CREATE TABLE `parameter_menu_item` (
	`param_id` SMALLINT(5) UNSIGNED NOT NULL,
	`item_id` SMALLINT(5) UNSIGNED NOT NULL,
	PRIMARY KEY (`param_id`, `item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Dumping structure for table hmrn.patient_antecedent
CREATE TABLE IF NOT EXISTS `patient_antecedent` (
  `patient_id` int(11) NOT NULL default '0',
  `event_id` int(11) NOT NULL default '0',
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`patient_id`),
  KEY `event_id` (`event_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Data exporting was unselected.


-- Dumping structure for table hmrn.patient_chronologies
CREATE TABLE IF NOT EXISTS `patient_chronologies` (
  `patient_id` int(11) NOT NULL default '0',
  `diagnosed` date default NULL,
  `first_appointment` date default NULL,
  `palliative_care` date default NULL,
  `deceased` date default NULL,
  PRIMARY KEY  (`patient_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Data exporting was unselected.


-- Dumping structure for table hmrn.patient_comments
CREATE TABLE IF NOT EXISTS `patient_comments` (
  `patient_id` int(11) NOT NULL default '0',
  `comment` text NOT NULL,
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`patient_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Data exporting was unselected.


-- Dumping structure for table hmrn.patient_event
CREATE TABLE IF NOT EXISTS `patient_event` (
  `patient_id` int(11) NOT NULL default '0',
  `event_id` smallint(6) NOT NULL default '0',
  `date` date default NULL,
  PRIMARY KEY  (`patient_id`,`event_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Data exporting was unselected.


-- Dumping structure for table hmrn.patient_imaging_event
CREATE TABLE IF NOT EXISTS `patient_imaging_event` (
  `id` int(11) NOT NULL auto_increment,
  `patient_id` int(11) NOT NULL default '0',
  `dataset` smallint(6) NOT NULL default '1',
  `scan_type` enum('ct','pet') default NULL,
  `stage` enum('initial','follow-up') NOT NULL default 'initial',
  `date` date NOT NULL default '1901-01-01',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `patient_dataset_type` (`patient_id`,`dataset`,`scan_type`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Data exporting was unselected.


-- Dumping structure for table hmrn.patient_imaging_option
CREATE TABLE IF NOT EXISTS `patient_imaging_option` (
  `imaging_event_id` int(11) NOT NULL default '0',
  `imaging_option_id` smallint(6) NOT NULL default '0',
  PRIMARY KEY  (`imaging_event_id`,`imaging_option_id`),
  KEY `imaging_options` (`imaging_option_id`),
  CONSTRAINT `fk_patient_imaging_option_imaging_options` FOREIGN KEY (`imaging_option_id`) REFERENCES `imaging_options` (`id`),
  CONSTRAINT `fk_patient_imaging_option_patient_imaging_event` FOREIGN KEY (`imaging_event_id`) REFERENCES `patient_imaging_event` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Data exporting was unselected.


-- Dumping structure for table hmrn.patient_imaging_other
CREATE TABLE IF NOT EXISTS `patient_imaging_other` (
  `imaging_event_id` int(11) NOT NULL default '0',
  `details` varchar(255) NOT NULL default '',
  KEY `imaging_event_id` (`imaging_event_id`),
  CONSTRAINT `fk_patient_imaging_other_patient_imaging_event` FOREIGN KEY (`imaging_event_id`) REFERENCES `patient_imaging_event` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Data exporting was unselected.


-- Dumping structure for table hmrn.patient_mdt_dates
CREATE TABLE IF NOT EXISTS `patient_mdt_dates` (
  `id` int(11) NOT NULL auto_increment,
  `patient_id` int(11) NOT NULL default '0',
  `date` date default NULL,
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Data exporting was unselected.


-- Dumping structure for table hmrn.patient_params
CREATE TABLE IF NOT EXISTS `patient_params` (
  `patient_id` int(11) NOT NULL default '0',
  `param_id` smallint(6) NOT NULL default '0',
  `result` varchar(255) NOT NULL default '',
  `time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`patient_id`,`param_id`),
  KEY `param_id` (`param_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT;

-- Data exporting was unselected.


-- Dumping structure for table hmrn.patient_pet_score
CREATE TABLE IF NOT EXISTS `patient_pet_score` (
  `imaging_event_id` int(11) NOT NULL default '0',
  `param` enum('suv_max','deauville') NOT NULL default 'suv_max',
  `result` float default NULL,
  PRIMARY KEY  (`imaging_event_id`,`param`),
  CONSTRAINT `fk_patient_pet_scan_patient_imaging_event` FOREIGN KEY (`imaging_event_id`) REFERENCES `patient_imaging_event` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Data exporting was unselected.


-- Dumping structure for table hmrn.patient_post_code
CREATE TABLE IF NOT EXISTS `patient_post_code` (
  `patient_id` int(11) NOT NULL default '0',
  `post_code` varchar(8) default NULL,
  PRIMARY KEY  (`patient_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Data exporting was unselected.


-- Dumping structure for table hmrn.patient_prior_therapies
CREATE TABLE IF NOT EXISTS `patient_prior_therapies` (
  `id` int(11) NOT NULL auto_increment,
  `patient_id` int(11) NOT NULL default '0',
  `therapy` enum('radiotherapy','chemotherapy') default NULL,
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Data exporting was unselected.


-- Dumping structure for table hmrn.patient_referrals
CREATE TABLE IF NOT EXISTS `patient_referrals` (
  `id` int(11) NOT NULL auto_increment,
  `patient_id` int(11) NOT NULL default '0',
  `referral_date` date default NULL,
  `referral_type_id` smallint(6) NOT NULL default '0',
  `from_source_id` smallint(6) NOT NULL default '0',
  `to_source_id` smallint(6) NOT NULL default '0',
  `date_first_seen` date default NULL,
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`),
  KEY `pid` (`patient_id`),
  KEY `FK_referral_type_options` (`referral_type_id`),
  KEY `FK_referral_source_from_options` (`from_source_id`),
  KEY `FK_referral_source_to_options` (`to_source_id`),
  CONSTRAINT `FK_referral_type_options` FOREIGN KEY (`referral_type_id`) REFERENCES `referral_type_options` (`id`),
  CONSTRAINT `FK_referral_source_from_options` FOREIGN KEY (`from_source_id`) REFERENCES `referral_source_options` (`id`),
  CONSTRAINT `FK_referral_source_to_options` FOREIGN KEY (`to_source_id`) REFERENCES `referral_source_options` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Data exporting was unselected.


-- Dumping structure for table hmrn.patient_sites_other
CREATE TABLE IF NOT EXISTS `patient_sites_other` (
  `patient_id` int(11) NOT NULL default '0',
  `details` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`patient_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Data exporting was unselected.


-- Dumping structure for table hmrn.patient_staging_other
CREATE TABLE IF NOT EXISTS `patient_staging_other` (
  `patient_id` int(10) NOT NULL default '0',
  `detail` enum('bulky','extensive','check_ct') default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Data exporting was unselected.


-- Dumping structure for table hmrn.patient_staging_site
CREATE TABLE IF NOT EXISTS `patient_staging_site` (
  `patient_id` int(11) NOT NULL default '0',
  `site_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`patient_id`,`site_id`),
  KEY `site_id` (`site_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Data exporting was unselected.


-- Dumping structure for table hmrn.patient_treatment
CREATE TABLE IF NOT EXISTS `patient_treatment` (
  `id` int(11) NOT NULL auto_increment,
  `patient_id` int(11) NOT NULL default '0',
  `location_id` int(11) NOT NULL default '0',
  `tx_type_id` int(11) NOT NULL default '0',
  `tx_detail_id` int(11) default NULL,
  `start_date` date default NULL,
  `end_date` date default NULL,
  `tx_cycles` SMALLINT(6) NULL DEFAULT NULL,
  `response_id` int(11) default NULL,
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`),
  KEY `pid` (`patient_id`),
  KEY `location_id` (`location_id`),
  KEY `tx_type_id` (`tx_type_id`),
  KEY `tx_detail_id` (`tx_detail_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Data exporting was unselected.


-- Dumping structure for table hmrn.referral_source_options
CREATE TABLE IF NOT EXISTS `referral_source_options` (
  `id` smallint(6) NOT NULL auto_increment,
  `description` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Data exporting was unselected.


-- Dumping structure for table hmrn.referral_type_options
CREATE TABLE IF NOT EXISTS `referral_type_options` (
  `id` smallint(6) NOT NULL auto_increment,
  `description` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Data exporting was unselected.


-- Dumping structure for table hmrn.response_options
CREATE TABLE IF NOT EXISTS `response_options` (
  `id` int(11) NOT NULL auto_increment,
  `description` varchar(50) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Data exporting was unselected.


-- Dumping structure for table hmrn.staging_sites
CREATE TABLE IF NOT EXISTS `staging_sites` (
  `id` int(11) NOT NULL auto_increment,
  `description` varchar(255) NOT NULL default '',
  `location` enum('upper_nodal','lower_nodal','nodal','extranodal') default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Data exporting was unselected.


-- Dumping structure for table hmrn.treatment_details
CREATE TABLE IF NOT EXISTS `treatment_details` (
  `id` int(11) NOT NULL auto_increment,
  `description` varchar(255) NOT NULL default '',
  `type_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `description` (`description`,`type_id`),
  KEY `type_id` (`type_id`),
  CONSTRAINT `treatment_details_ibfk_1` FOREIGN KEY (`type_id`) REFERENCES `treatment_types` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Data exporting was unselected.


-- Dumping structure for table hmrn.treatment_types
CREATE TABLE IF NOT EXISTS `treatment_types` (
  `id` int(11) NOT NULL auto_increment,
  `description` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Data exporting was unselected.


-- Dumping structure for table hmrn._patient_id
CREATE TABLE IF NOT EXISTS `_patient_id` (
  `v3` int(11) NOT NULL default '0',
  `v4` int(11) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- Data exporting was unselected.
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
