$SQL_FOR_TABLE = {
# _lab_tests_map => q!
# CREATE TABLE `_lab_test_map` (
# `id` tinyint NOT NULL auto_increment,
# `TestName` varchar(255) default NULL,
# PRIMARY KEY (`id`)
# ) ENGINE=MyISAM!,
additional_options => q!
CREATE TABLE `additional_options` (
`id` smallint NOT NULL auto_increment,
`option_name` varchar(255) default NULL,
`option_label` varchar(255) default NULL,
`is_active` enum('yes','no') NOT NULL default 'yes',
PRIMARY KEY (`id`),
UNIQUE KEY `option_label` (`option_label`),
UNIQUE KEY `option_name` (`option_name`)
) ENGINE=InnoDB!,
admin_messages => q!
CREATE TABLE `admin_messages` (
`id` INT(11) NOT NULL auto_increment,
`message` TEXT NOT NULL,
`valid_until` date default NULL,
`user_group` enum('internal','external','all') NOT NULL default 'all',
`time` TIMESTAMP NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB!,
audit_request_categories => q!
CREATE TABLE `audit_request_categories` (
`id` smallint NOT NULL auto_increment,
`description` varchar(255) NOT NULL default '',
`active` enum('yes','no') NOT NULL default 'yes',
PRIMARY KEY (`id`),
UNIQUE KEY `description` (`description`)
) ENGINE=InnoDB!,
audit_request_options => q!
CREATE TABLE `audit_request_options` (
`id` smallint NOT NULL auto_increment,
`description` varchar(255) NOT NULL default '',
`category_id` smallint NOT NULL default '0',
`active` enum('yes','no') NOT NULL default 'yes',
PRIMARY KEY (`id`),
UNIQUE KEY `description` (`description`),
KEY `category_id` (`category_id`)
) ENGINE=InnoDB!,
clinical_trials => q!
CREATE TABLE `clinical_trials` (
`id` smallint NOT NULL auto_increment,
`trial_name` varchar(50) default NULL,
`active` enum('yes','no') NOT NULL default 'yes',
PRIMARY KEY (`id`),
UNIQUE KEY `trial_name` (`trial_name`)
) ENGINE=InnoDB!,
consent_options => q!
CREATE TABLE `consent_options` (
`id` smallint NOT NULL auto_increment,
`consent_name` varchar(255) NOT NULL default '',
`consent_label` varchar(255) NOT NULL default '',
`is_active` enum('yes','no') NOT NULL default 'yes',
PRIMARY KEY (`id`),
UNIQUE KEY `consent_name` (`consent_name`),
UNIQUE KEY `consent_label` (`consent_label`)
) ENGINE=InnoDB!,
context_warning_diagnosis => q!
CREATE TABLE `context_warning_diagnosis` (
`id` smallint NOT NULL auto_increment,
`diagnosis_id` smallint NOT NULL default '0',
`context_warning_id` smallint NOT NULL default '0',
PRIMARY KEY (`id`),
UNIQUE KEY (`diagnosis_id`),
KEY `context_warning_id` (`context_warning_id`)
) ENGINE=InnoDB!,
deleted_requests => q!
CREATE TABLE `deleted_requests` (
`id` int(11) NOT NULL auto_increment,
`request_id` int(11) NOT NULL default '0',
`request_number` smallint(6) NOT NULL default '0',
`year` year(4) NOT NULL default '0000',
`action` varchar(255) NOT NULL default '',
`user_id` smallint(6) NOT NULL default '0',
`time` datetime default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB!,
diagnosis_change_options => q!
CREATE TABLE `diagnosis_change_options` (
`id` smallint NOT NULL auto_increment,
`option_name` varchar(25) NOT NULL,
`description` varchar(255) NOT NULL,
`is_active` enum('yes','no') NOT NULL default 'yes',
PRIMARY KEY (`id`),
UNIQUE KEY `option_name` (`option_name`)
) ENGINE=InnoDB!,
diagnosis_context_warnings => q!
CREATE TABLE `diagnosis_context_warnings` (
`id` smallint NOT NULL auto_increment,
`description` varchar(255) NOT NULL default '',
`is_active` enum('yes','no') default 'yes',
PRIMARY KEY (`id`),
UNIQUE KEY `description` (`description`)
) ENGINE=InnoDB!,
diagnoses => q!
CREATE TABLE `diagnoses` (
`id` smallint NOT NULL auto_increment,
`name` varchar(255) default NULL,
`icdo3` varchar(6) default NULL,
`diagnostic_category_id` smallint default NULL,
`active` enum('yes','no') default 'yes',
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`),
KEY `icdo3` (`icdo3`)
) ENGINE=InnoDB!,
diagnostic_categories => q!
CREATE TABLE `diagnostic_categories` (
`id` smallint NOT NULL auto_increment,
`description` varchar(255) default NULL,
`category_type` enum('main','sub') default NULL,
`active` enum('yes','no') default 'yes',
PRIMARY KEY (`id`),
UNIQUE KEY `description` (`description`)
) ENGINE=InnoDB!,
email_addresses => q!
CREATE TABLE `email_addresses` (
`address` varchar(255) NOT NULL default '',
PRIMARY KEY (`address`)
) ENGINE=InnoDB!,
email_contacts => q!
CREATE TABLE `email_contacts` (
`id` smallint NOT NULL auto_increment,
`display_name` varchar(255) NOT NULL default '',
`scope` enum('hospital','organisation','department') default NULL,
`referral_source_id` smallint NOT NULL default '0',
`type` enum('mdt','report') default NULL,
`contact_address` varchar(255) NOT NULL default '',
`is_active` enum('yes','no') NOT NULL default 'yes',
PRIMARY KEY (`id`),
UNIQUE KEY `contact_type` (`referral_source_id`,`type`,`contact_address`),
KEY `referral_source_id` (`referral_source_id`),
KEY `contact_address` (`contact_address`)
) ENGINE=InnoDB!,
error_codes => q!
CREATE TABLE `error_codes` (
`id` smallint NOT NULL auto_increment,
`code` varchar(2) default NULL,
`description` varchar(255) default NULL,
`is_unique` enum('yes','no') NOT NULL default 'yes',
`active` enum('yes','no') NOT NULL default 'yes',
PRIMARY KEY (`id`),
UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB!,
error_code_assignment => q!
CREATE TABLE `error_code_assignment` (
`type` enum('patient','new_request','request_edit','errors') default NULL,
`error_code_id` smallint NOT NULL default '0',
UNIQUE KEY `error_code_type` (`type`,`error_code_id`),
KEY `error_code_id` (`error_code_id`)
) ENGINE=InnoDB!,
hospital_departments => q!
CREATE TABLE `hospital_departments` (
`id` smallint NOT NULL default '0',
`display_name` varchar(50) NOT NULL default '',
PRIMARY KEY (`id`),
UNIQUE KEY `display_name` (`display_name`(20))
) ENGINE=InnoDB!,
icdo_category => q!
CREATE TABLE `icdo_category` (
`diagnostic_category_id` smallint NOT NULL,
`icdo3` varchar(6) NOT NULL,
PRIMARY KEY (`diagnostic_category_id`, `icdo3`)
) ENGINE=InnoDB!,
lab_section_sample_type => q!
CREATE TABLE `lab_section_sample_type` (
`lab_section_id` smallint NOT NULL default '0',
`sample_type_id` smallint NOT NULL default '0',
PRIMARY KEY (`sample_type_id`, `lab_section_id`)
) ENGINE=InnoDB!,
lab_section_status_option => q!
CREATE TABLE `lab_section_status_option` (
`lab_section_id` smallint(6) NOT NULL default '0',
`status_option_id` smallint(6) NOT NULL default '0',
`position` SMALLINT(6) NULL,
PRIMARY KEY (`lab_section_id`,`status_option_id`),
KEY `status_option_id` (`status_option_id`),
UNIQUE KEY `section_position` (`lab_section_id`, `position`)
) ENGINE=InnoDB!,
lab_sections => q!
CREATE TABLE `lab_sections` (
`id` smallint NOT NULL auto_increment,
`section_name` varchar(255) NOT NULL,
`has_result_summary` enum('yes','no') NOT NULL default 'no',
`has_section_notes` enum('yes','no') NOT NULL default 'no',
`has_test_sign_out` enum('yes','no') NOT NULL default 'no',
`has_foreign_id` enum('yes','no') NOT NULL default 'no',
`has_results_import` enum('yes','no') NOT NULL default 'no',
`is_active` enum('yes','no') NOT NULL default 'yes',
PRIMARY KEY (`id`),
UNIQUE KEY `section_name` (`section_name`)
) ENGINE=InnoDB!,
lab_test_data_type => q!
CREATE TABLE `lab_test_data_type` (
`lab_test_id` smallint(6) NOT NULL default '0',
`data_type_id` smallint(6) NOT NULL default '0',
`is_active` enum('yes','no') NOT NULL default 'yes',
PRIMARY KEY (`lab_test_id`),
KEY `data_type_id` (`data_type_id`)
) ENGINE=InnoDB!,
lab_test_result_data_types => q!
CREATE TABLE `lab_test_result_data_types` (
`id` smallint NOT NULL auto_increment,
`description` varchar(25) NOT NULL default '',
`is_active` enum('yes','no') NOT NULL default 'yes',
PRIMARY KEY (`id`),
UNIQUE KEY `description` (`description`)
) ENGINE=InnoDB!,
lab_test_result_options => q!
CREATE TABLE `lab_test_result_options` (
`id` smallint NOT NULL auto_increment,
`data_type_id` smallint NOT NULL default '0',
`value` varchar(30) NOT NULL default '',
`is_active` enum('yes','no') NOT NULL default 'yes',
PRIMARY KEY (`id`),
UNIQUE KEY `data_type_value` (`data_type_id`,`value`)
) ENGINE=InnoDB!,
lab_test_sample_type => q!
CREATE TABLE `lab_test_sample_type` (
`lab_test_id` SMALLINT(6) NOT NULL DEFAULT '0',
`sample_type_id` SMALLINT(6) NOT NULL DEFAULT '0',
PRIMARY KEY (`sample_type_id`, `lab_test_id`),
INDEX `lab_test_id` (`lab_test_id`)
)
ENGINE=InnoDB!,
lab_test_status_options => q!
CREATE TABLE `lab_test_status_options` (
`id` smallint NOT NULL auto_increment,
`description` varchar(255) NOT NULL default '',
`is_editable` enum('yes','no') NOT NULL default 'yes',
`is_active` enum('yes','no') NOT NULL default 'yes',
PRIMARY KEY (`id`),
UNIQUE KEY `description` (`description`)
) ENGINE=InnoDB!,
lab_tests => q!
CREATE TABLE `lab_tests` (
`id` smallint NOT NULL auto_increment,
`test_name` varchar(25) NOT NULL,
`field_label` varchar(25) NOT NULL,
`lab_section_id` smallint NOT NULL default '0',
`test_type` enum('test','panel') default NULL,
`has_results` enum('yes','no') default NULL,
`is_active` enum('yes','no') NOT NULL default 'yes',
PRIMARY KEY (`id`),
UNIQUE KEY `label_section_type` (`lab_section_id`,`test_type`,`field_label`),
UNIQUE KEY `name_section_type` (`lab_section_id`,`test_type`,`test_name`),
KEY `lab_section_id` (`lab_section_id`)
) ENGINE=InnoDB!,
linked_lab_test => q!
CREATE TABLE `linked_lab_test` (
`parent_test_id` SMALLINT(6) NOT NULL DEFAULT '0',
`linked_test_id` SMALLINT(6) NOT NULL DEFAULT '0',
PRIMARY KEY (`parent_test_id`, `linked_test_id`),
INDEX `linked_lab_test` (`linked_test_id`)
) ENGINE=InnoDB!,
local_network_locations => q!
CREATE TABLE `local_network_locations` (
`parent_id` smallint(6) NOT NULL default '0',
PRIMARY KEY (`parent_id`)
) ENGINE=InnoDB!,
logins => q!
CREATE TABLE `logins` (
`id` int NOT NULL auto_increment,
`user_id` smallint NOT NULL default '0',
`address` varchar(15) default NULL,
`browser` varchar(255) default NULL,
`session_id` char(32) default NULL,
`time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB!,
new_national_code => q!
CREATE TABLE `new_national_code` (
`national_code` VARCHAR(8) NOT NULL,
`type` ENUM('referrer','location') NOT NULL,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE INDEX `national_code` (`national_code`)
) ENGINE=InnoDB!,
panel_lab_test => q!
CREATE TABLE `panel_lab_test` (
`panel_test_id` SMALLINT(6) NOT NULL DEFAULT '0',
`lab_test_id` SMALLINT(6) NOT NULL DEFAULT '0',
PRIMARY KEY (`panel_test_id`, `lab_test_id`),
INDEX `lab_test_id` (`lab_test_id`)
) ENGINE=InnoDB!,
parent_organisations => q!
CREATE TABLE `parent_organisations` (
`id` smallint NOT NULL auto_increment,
`parent_code` varchar(6) NOT NULL default '',
`description` varchar(255) NOT NULL default '',
`referral_type_id` smallint NOT NULL default '0',
PRIMARY KEY (`id`),
UNIQUE KEY `description` (`description`),
UNIQUE KEY `parent_code` (`parent_code`)
) ENGINE=InnoDB!,
patient_case => q!
CREATE TABLE `patient_case` (
`id` int NOT NULL auto_increment,
`patient_id` int NOT NULL default '0',
`referral_source_id` smallint NOT NULL default '0',
`unit_number` varchar(255) NOT NULL default 'UNKNOWN',
`time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `case` (`patient_id`,`unit_number`,`referral_source_id`),
KEY `patient_id` (`patient_id`),
KEY `referral_source_id` (`referral_source_id`)
) ENGINE=InnoDB!,
patient_demographics => q!
CREATE TABLE `patient_demographics` (
`patient_id` int(11) NOT NULL default '0',
`address` varchar(255) default NULL,
`post_code` varchar(8) default NULL,
`contact_number` varchar(15) default NULL,
`gp_id` int(11) NOT NULL default '0',
`practice_id` smallint(6) NOT NULL default '0',
`status` enum('alive','dead') NOT NULL default 'alive',
`dod` date default NULL,
`time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`patient_id`),
KEY `practice_id` (`practice_id`)
) ENGINE=InnoDB!,
patient_demographic_history => q!
CREATE TABLE `patient_demographic_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!,
patient_edits => q!
CREATE TABLE `patient_edits` (
`id` int NOT NULL auto_increment,
`patient_id` int NOT NULL default '0',
`last_name` varchar(50) default NULL,
`first_name` varchar(50) default NULL,
`middle_name` varchar(50) default NULL,
`dob` date default NULL,
`gender` enum('M','F','U') default 'U',
`nhs_number` varchar(10) default NULL,
`user_id` smallint NOT NULL,
`error_code_id` smallint NOT NULL,
`time` timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`),
KEY `patient_id` (`patient_id`)
) ENGINE=InnoDB!,
patient_notes => q!
CREATE TABLE `patient_notes` (
`patient_id` INT(11) NOT NULL DEFAULT '0',
`detail` TEXT NOT NULL,
PRIMARY KEY (`patient_id`)
) ENGINE=InnoDB!,
patient_trial => q!
CREATE TABLE `patient_trial` (
`patient_id` int NOT NULL default '0',
`trial_id` smallint NOT NULL default '0',
`trial_number` varchar(255) default NULL,
PRIMARY KEY (`patient_id`,`trial_id`),
KEY `trial_id` (`trial_id`)
) ENGINE=InnoDB!,
patients => q!
CREATE TABLE `patients` (
`id` int NOT NULL auto_increment,
`last_name` varchar(50) default NULL,
`first_name` varchar(50) default NULL,
`middle_name` varchar(50) default NULL,
`dob` date default NULL,
`gender` enum('M','F','U') default 'U',
`nhs_number` varchar(10) default NULL,
`created_at` timestamp NOT NULL default '0000-00-00 00:00:00',
`updated_at` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `name` (`last_name`,`first_name`),
UNIQUE KEY `nhs_number` (`nhs_number`)
) ENGINE=InnoDB!,
phone_log_options => q!
CREATE TABLE `phone_log_options` (
`id` smallint NOT NULL auto_increment,
`description` varchar(255) NOT NULL default '',
`is_active` enum('yes','no') NOT NULL default 'yes',
PRIMARY KEY (`id`),
UNIQUE KEY `option` (`description`)
) ENGINE=InnoDB!,
pre_registration => q!
CREATE TABLE `pre_registration` (
`id` int NOT NULL auto_increment,
`labno` varchar(8) NOT NULL default '',
`surname` varchar(25) default NULL,
`is_screened` enum('yes','no') NOT NULL default 'no',
`time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `labno` (`labno`)
) ENGINE=InnoDB!,
pre_registration_lab_test => q!
CREATE TABLE `pre_registration_lab_test` (
`id` int NOT NULL auto_increment,
`reg_specimen_id` int NOT NULL default '0',
`lab_test_id` smallint NOT NULL default '0',
PRIMARY KEY (`id`),
UNIQUE KEY `reg_specimen_id` (`reg_specimen_id`,`lab_test_id`),
KEY `lab_test_id` (`lab_test_id`)
) ENGINE=InnoDB!,
pre_registration_specimen => q!
CREATE TABLE `pre_registration_specimen` (
`id` int(11) NOT NULL auto_increment,
`pre_reg_id` int(11) NOT NULL default '0',
`specimen_code` char(2) NOT NULL default '0',
PRIMARY KEY (`id`),
UNIQUE KEY `pre_reg_specimen` (`pre_reg_id`,`specimen_code`)
) ENGINE=InnoDB!,
referral_sources => q!
CREATE TABLE `referral_sources` (
`id` smallint NOT NULL auto_increment,
`display_name` varchar(255) NOT NULL default '',
`organisation_code` varchar(6) NOT NULL default '',
`parent_organisation_id` smallint NOT NULL default '0',
`referral_type_id` smallint NOT NULL default '0',
`is_active` enum('yes','no') NOT NULL default 'yes',
PRIMARY KEY (`id`),
UNIQUE KEY `organisation_code` (`organisation_code`),
UNIQUE KEY `display_name` (`display_name`),
KEY `referral_type_id` (`referral_type_id`),
KEY `parent_organisation_id` (`parent_organisation_id`)
) ENGINE=InnoDB!,
referral_types => q!
CREATE TABLE `referral_types` (
`id` smallint NOT NULL auto_increment,
`description` varchar(255) NOT NULL default '',
`prefix` varchar(2) default NULL,
`default_unknown` varchar(8) NOT NULL default '',
PRIMARY KEY (`id`),
UNIQUE KEY `description` (`description`(255))
) ENGINE=InnoDB!,
referrer_department => q!
CREATE TABLE `referrer_department` (
`id` int NOT NULL auto_increment,
`referrer_id` int NOT NULL default '0',
`parent_organisation_id` smallint NOT NULL default '0',
`hospital_department_code` smallint NOT NULL default '0',
`is_active` enum('yes','no') NOT NULL default 'yes',
PRIMARY KEY (`id`),
UNIQUE KEY `referrer_parent_organisation` (`referrer_id`,`parent_organisation_id`),
KEY `parent_organisation_id` (`parent_organisation_id`),
KEY `hospital_department_code` (`hospital_department_code`)
) ENGINE=InnoDB!,
referrers => q!
CREATE TABLE `referrers` (
`id` int NOT NULL auto_increment,
`name` varchar(255) NOT NULL default '',
`national_code` varchar(8) NOT NULL default '',
`referral_type_id` smallint NOT NULL default '0',
`active` enum('yes','no') NOT NULL default 'yes',
PRIMARY KEY (`id`),
KEY `name` (`name`),
UNIQUE KEY `national_code` (`national_code`)
) ENGINE=InnoDB!,
request_audit => q!
CREATE TABLE `request_audit` (
`request_id` int NOT NULL default '0',
`audit_request_option_id` smallint NOT NULL default '0',
PRIMARY KEY (`request_id`,`audit_request_option_id`),
KEY `audit_request_option_id` (`audit_request_option_id`)
) ENGINE=InnoDB!,
request_authorisation_diagnosis => q!
CREATE TABLE `request_authorisation_diagnosis` (
`request_id` int NOT NULL default '0',
`report_diagnosis_id` smallint NOT NULL default '0',
`auth_diagnosis_id` smallint NOT NULL default '0',
`user_id` smallint NOT NULL default '0',
`time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`request_id`),
KEY `report_diagnosis_id` (`report_diagnosis_id`),
KEY `auth_diagnosis_id` (`auth_diagnosis_id`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB!,
request_consent => q!
CREATE TABLE `request_consent` (
`request_id` int NOT NULL default '0',
`consent_id` smallint NOT NULL default '0',
`status` enum('yes','no') NOT NULL default 'no',
PRIMARY KEY (`request_id`,`consent_id`),
KEY `consent_id` (`consent_id`)
) ENGINE=InnoDB!,
request_diagnosis_history => q!
CREATE TABLE `request_diagnosis_history` (
`id` int NOT NULL auto_increment,
`request_id` int NOT NULL,
`diagnosis_id` smallint NOT NULL,
`option_id` smallint NOT NULL,
`user_id` smallint NOT NULL,
`time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB!,
request_dispatch_log => q!
CREATE TABLE `request_dispatch_log` (
`id` int NOT NULL auto_increment,
`request_id` int NOT NULL default '0',
`recipient` varchar(255) NOT NULL,
`time` timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `request_id` (`request_id`)
) ENGINE=InnoDB!,
request_error_code => q!
CREATE TABLE `request_error_code` (
`id` int NOT NULL auto_increment,
`request_id` int NOT NULL default '0',
`error_code_id` smallint NOT NULL default '0',
`user_id` smallint NOT NULL default '0',
`time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `error_code_id` (`error_code_id`),
KEY `request_id` (`request_id`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB!,
request_external_ref => q!
CREATE TABLE `request_external_ref` (
`request_id` int NOT NULL default '0',
`external_reference` varchar(255) NOT NULL default '',
`status` varchar(255) default NULL,
PRIMARY KEY (`request_id`)
) ENGINE=InnoDB!,
request_haematology => q!
CREATE TABLE `request_haematology` (
`lab_number` CHAR(8) NOT NULL,
`status` ENUM('verified','default') NOT NULL DEFAULT 'default',
`pda` VARCHAR(6) NOT NULL,
`wbc` CHAR(4) NOT NULL,
`wbc_f` TINYINT(1) UNSIGNED NOT NULL,
`rbc` CHAR(4) NOT NULL,
`rbc_f` TINYINT(1) UNSIGNED NOT NULL,
`hb` CHAR(4) NOT NULL,
`hb_f` TINYINT(1) UNSIGNED NOT NULL,
`hct` CHAR(4) NOT NULL,
`hct_f` TINYINT(1) UNSIGNED NOT NULL,
`mcv` CHAR(4) NOT NULL,
`mcv_f` TINYINT(1) UNSIGNED NOT NULL,
`mch` CHAR(4) NOT NULL,
`mch_f` TINYINT(1) UNSIGNED NOT NULL,
`mchc` CHAR(4) NOT NULL,
`mchc_f` TINYINT(1) UNSIGNED NOT NULL,
`plt` CHAR(4) NOT NULL,
`plt_f` TINYINT(1) UNSIGNED NOT NULL,
`lymph_percent` CHAR(4) NOT NULL,
`lymph_percent_f` TINYINT(1) UNSIGNED NOT NULL,
`mixed_percent` CHAR(4) NOT NULL,
`mixed_percent_f` TINYINT(1) UNSIGNED NOT NULL,
`neutr_percent` CHAR(4) NOT NULL,
`neutr_percent_f` TINYINT(1) UNSIGNED NOT NULL,
`lymph` CHAR(4) NOT NULL,
`lymph_f` TINYINT(1) UNSIGNED NOT NULL,
`mixed` CHAR(4) NOT NULL,
`mixed_f` TINYINT(1) UNSIGNED NOT NULL,
`neutr` CHAR(4) NOT NULL,
`neutr_f` TINYINT(1) UNSIGNED NOT NULL,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`lab_number`)
) ENGINE=InnoDB!,
request_history => q!
CREATE TABLE `request_history` (
`id` int NOT NULL auto_increment,
`request_id` int NOT NULL default '0',
`action` varchar(255) NOT NULL default '',
`user_id` smallint NOT NULL default '0',
`time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `request_id` (`request_id`),
KEY `action` (`action`),
KEY `user_id` (`user_id`),
KEY `time` (`time`)
) ENGINE=InnoDB!,
request_initial_screen => q!
CREATE TABLE `request_initial_screen` (
`request_id` int NOT NULL default '0',
`screen_id` smallint NOT NULL default '0',
PRIMARY KEY (`request_id`),
KEY `screen_id` (`screen_id`)
) ENGINE=InnoDB!,
request_general_notes => q!
CREATE TABLE `request_general_notes` (
`request_id` int NOT NULL default '0',
`detail` text,
PRIMARY KEY (`request_id`)
) ENGINE=InnoDB!,
request_lab_section_foreign_id => q!
CREATE TABLE `request_lab_section_foreign_id` (
`request_id` int(11) NOT NULL default '0',
`lab_section_id` smallint(6) NOT NULL default '0',
`foreign_id` varchar(25) NOT NULL default '',
`time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`request_id`,`lab_section_id`),
KEY `lab_section_id` (`lab_section_id`)
) ENGINE=InnoDB!,
request_lab_section_notes => q!
CREATE TABLE `request_lab_section_notes` (
`request_id` int NOT NULL default '0',
`lab_section_id` smallint NOT NULL default '0',
`details` text,
`time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`request_id`,`lab_section_id`),
KEY `lab_section_id` (`lab_section_id`)
) ENGINE=InnoDB!,
request_lab_test_history => q!
CREATE TABLE `request_lab_test_history` (
`id` int NOT NULL auto_increment,
`request_id` int NOT NULL default '0',
`action` varchar(255) NOT NULL default '',
`user_id` smallint NOT NULL default '0',
`time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `request_id` (`request_id`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB!,
request_lab_test_results => q!
CREATE TABLE `request_lab_test_results` (
`id` int NOT NULL auto_increment,
`request_id` int NOT NULL default '0',
`lab_test_id` smallint NOT NULL default '0',
`result` varchar(255) NOT NULL default '',
`time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `request_lab_test` (`request_id`,`lab_test_id`),
KEY `lab_test_id` (`lab_test_id`)
) ENGINE=InnoDB!,
request_lab_test_status => q!
CREATE TABLE `request_lab_test_status` (
`id` int(11) NOT NULL auto_increment,
`request_id` int(11) NOT NULL default '0',
`lab_test_id` smallint(6) NOT NULL default '0',
`status_option_id` smallint(6) NOT NULL default '0',
`user_id` smallint NOT NULL default '0',
`time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `request_lab_test` (`request_id`,`lab_test_id`),
KEY `lab_test_id` (`lab_test_id`),
KEY `user_id` (`user_id`),
KEY `status_option_id` (`status_option_id`)
) ENGINE=InnoDB!,
request_option => q!
CREATE TABLE `request_option` (
`request_id` int NOT NULL default '0',
`option_id` smallint NOT NULL default '0',
PRIMARY KEY (`request_id`,`option_id`),
KEY `option_id` (`option_id`)
) ENGINE=InnoDB!,
request_phone_log => q!
CREATE TABLE `request_phone_log` (
`id` int NOT NULL auto_increment,
`request_id` int NOT NULL default '0',
`user_id` smallint NOT NULL default '0',
`status` enum('inbound','outbound') default NULL,
`contact` varchar(255) NOT NULL default '',
`details` varchar(255) NOT NULL default '',
`time` timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `request_id` (`request_id`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB!,
request_print_log => q!
CREATE TABLE `request_print_log` (
`id` int NOT NULL auto_increment,
`request_id` int NOT NULL default '0',
`user_id` smallint NOT NULL default '0',
`time` timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `request_id` (`request_id`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB!,
request_report_patient_view => q!
CREATE ALGORITHM=UNDEFINED DEFINER=`raj`@`%` SQL SECURITY DEFINER
VIEW `request_report_patient_view` AS
SELECT `r`.`id`, `r`.`request_number`, `r`.`year`, `r`.`created_at`,
`p`.`last_name`, `p`.`first_name`, `p`.`dob`, `pc`.`unit_number`,
((DATE_FORMAT(`r`.`created_at`,'%Y') - DATE_FORMAT(`p`.`dob`,'%Y'))
- (DATE_FORMAT(`r`.`created_at`,'00-%m-%d')
< DATE_FORMAT(`p`.`dob`,'00-%m-%d'))) AS `age`,
`p`.`nhs_number`, `rs`.`display_name` AS `location`,
`rs`.`organisation_code`, `po`.`parent_code`,
`d`.`name` AS `diagnosis`, `d`.`icdo3`, `rr`.`status`,
CONCAT_WS('/', r.request_number, r.year - 2000) as 'hmds_ref'
FROM `requests` `r`
JOIN `patient_case` `pc` on `r`.`patient_case_id` = `pc`.`id`
JOIN `referral_sources` `rs` on `pc`.`referral_source_id` = `rs`.`id`
JOIN `parent_organisations` `po` on `rs`.`parent_organisation_id`
= `po`.`id`
JOIN `patients` `p` on `pc`.`patient_id` = `p`.`id`
JOIN `request_report_detail` `rr` on `rr`.`request_id` = `r`.`id`
JOIN `diagnoses` `d` on `rr`.`diagnosis_id` = `d`.`id`!,
# replaced with request_report_view
# request_report => q!
# CREATE TABLE `request_report` (
# `request_id` int NOT NULL,
# `comment` text,
# `clinical_details` text,
# `status` enum('new','relapse','default') NOT NULL default 'default',
# `specimen_quality` enum('good','adequate','poor') NOT NULL default 'adequate',
# `diagnosis_id` smallint NOT NULL,
# `created_at` timestamp NOT NULL default '0000-00-00 00:00:00',
# `updated_at` timestamp NOT NULL default '0000-00-00 00:00:00' on update CURRENT_TIMESTAMP,
# PRIMARY KEY (`request_id`)
# ) ENGINE=InnoDB!,
request_report_view => q!
CREATE ALGORITHM=UNDEFINED DEFINER=`raj`@`%` SQL SECURITY DEFINER
VIEW `request_report_view` AS
SELECT t1.request_id, t1.clinical_details, t1.morphology, t1.`comment`,
t1.`status`, t1.diagnosis_id, t2.gross_description, t2.biopsy_site,
t2.specimen_quality, t2.specimen_date, t1.created_at, t1.updated_at
FROM request_report_detail t1 JOIN request_specimen_detail t2
on `t1`.`request_id` = `t2`.`request_id`!,
request_report_detail => q!
CREATE TABLE `request_report_detail` (
`request_id` INT(11) NOT NULL,
`clinical_details` TEXT NULL,
`morphology` TEXT NULL,
`comment` TEXT NULL,
`status` ENUM('new','relapse','default') NOT NULL DEFAULT 'default',
`diagnosis_id` SMALLINT(6) NOT NULL,
`created_at` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
`updated_at` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`request_id`)
) ENGINE=InnoDB!,
request_report_history => q!
CREATE TABLE `request_report_history` (
`id` int NOT NULL auto_increment,
`request_id` int NOT NULL default '0',
`field` ENUM('morphology','comment') default NULL,
`content` text,
`user_id` smallint NOT NULL default '0',
`time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `request_id` (`request_id`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB!,
request_result_summaries => q!
CREATE TABLE `request_result_summaries` (
`request_id` int NOT NULL default '0',
`lab_section_id` smallint NOT NULL default '0',
`results_summary` text,
`time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`request_id`,`lab_section_id`),
KEY `lab_section_id` (`lab_section_id`)
) ENGINE=InnoDB!,
request_secondary_diagnosis => q!
CREATE TABLE `request_secondary_diagnosis` (
`request_id` int NOT NULL default '0',
`secondary_diagnosis_id` smallint NOT NULL,
`time` timestamp NOT NULL default '0000-00-00 00:00:00' on update CURRENT_TIMESTAMP,
PRIMARY KEY (`request_id`,`secondary_diagnosis_id`)
) ENGINE=InnoDB!,
request_specimen => q!
CREATE TABLE `request_specimen` (
`request_id` int NOT NULL default '0',
`specimen_id` smallint NOT NULL default '0',
PRIMARY KEY (`request_id`,`specimen_id`),
KEY `specimen_id` (`specimen_id`)
) ENGINE=InnoDB!,
request_status_view => q!
CREATE ALGORITHM=UNDEFINED DEFINER=`raj`@`%` SQL SECURITY DEFINER
VIEW `request_status_view` AS
SELECT
`rh`.`request_id` AS `request_id`,
(CASE WHEN (`rh`.`action` = 'registered') THEN 1 ELSE `so`.`id` END)
AS `status_option_id`,
`rh`.`action`,
`u`.`username`,
`rh`.`time` AS `time`
FROM `request_history` `rh` JOIN users u ON rh.user_id = u.id
LEFT JOIN `status_options` `so` ON `rh`.`action` = `so`.`description`
WHERE `rh`.`action` IN ('registered','screened','reported','authorised')
!,
request_specimen_detail => q!
CREATE TABLE `request_specimen_detail` (
`request_id` INT(11) NOT NULL DEFAULT '0',
`gross_description` TEXT NULL,
`biopsy_site` TEXT NULL,
`specimen_quality` ENUM('good','adequate','poor') NULL,
`specimen_date` DATETIME NULL,
PRIMARY KEY (`request_id`)
) ENGINE=InnoDB!,
request_trial => q!
CREATE TABLE `request_trial` (
`request_id` int NOT NULL default '0',
`trial_id` smallint NOT NULL default '0',
PRIMARY KEY (`request_id`),
KEY `trial_id` (`trial_id`)
) ENGINE=InnoDB!,
request_view_log => q!
CREATE TABLE `request_view_log` (
`id` int NOT NULL auto_increment,
`request_id` int NOT NULL default '0',
`user_id` smallint NOT NULL default '0',
`ip_address` varchar(255) NOT NULL,
`time` timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `request_id` (`request_id`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB!,
requests => q!
CREATE TABLE `requests` (
`id` int NOT NULL auto_increment,
`request_number` smallint NOT NULL default '0',
`year` year(4) NOT NULL default '0000',
`patient_case_id` int NOT NULL default '0',
`referrer_department_id` int NOT NULL default '0',
`status_option_id` smallint NOT NULL default '1',
`created_at` timestamp NOT NULL default '0000-00-00 00:00:00',
`updated_at` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `lab_no` (`request_number`,`year`),
KEY `patient_case_id` (`patient_case_id`),
KEY `referrer_department_id` (`referrer_department_id`),
KEY `status_option_id` (`status_option_id`)
) ENGINE=InnoDB!,
report_error_codes => q!
CREATE TABLE `report_error_codes` (
`id` smallint NOT NULL auto_increment,
`code` varchar(2) default NULL,
`description` varchar(255) default NULL,
`active` enum('yes','no') NOT NULL default 'yes',
PRIMARY KEY (`id`),
UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB!,
result_summary_options => q!
CREATE TABLE `result_summary_options` (
`id` smallint NOT NULL auto_increment,
`description` varchar(255) NOT NULL default '',
`lab_section_id` smallint NOT NULL default '0',
`is_active` enum('yes','no') NOT NULL default 'yes',
PRIMARY KEY (`id`),
UNIQUE KEY `lab_section_description` (`description`,`lab_section_id`),
KEY `lab_section_id` (`lab_section_id`)
) ENGINE=InnoDB!,
sample_types => q!
CREATE TABLE `sample_types` (
`id` smallint NOT NULL auto_increment,
`specimen_type` VARCHAR(50) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
UNIQUE KEY `specimen_type` (`specimen_type`)
) ENGINE=InnoDB!,
screen_category => q!
CREATE TABLE `screen_category` (
`id` smallint(6) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`is_active` enum('yes','no') NOT NULL default 'yes',
PRIMARY KEY (`id`)
) ENGINE=InnoDB!,
screen_lab_test_detail => q!
CREATE TABLE `screen_lab_test_detail` (
`screen_id` smallint NOT NULL default '0',
`lab_section_id` smallint NOT NULL default '0',
`test_details` varchar(255) NOT NULL default '',
PRIMARY KEY (`screen_id`,`lab_section_id`),
KEY `lab_section_id` (`lab_section_id`)
) ENGINE=InnoDB!,
screen_lab_test => q!
CREATE TABLE `screen_lab_test` (
`screen_id` smallint NOT NULL default '0',
`lab_test_id` smallint NOT NULL default '0',
PRIMARY KEY (`screen_id`,`lab_test_id`),
KEY `lab_test_id` (`lab_test_id`)
) ENGINE=InnoDB!,
screens => q!
CREATE TABLE `screens` (
`id` smallint NOT NULL auto_increment,
`description` varchar(50) default NULL,
`category_id` smallint NOT NULL default '0',
`active` enum('yes','no') default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `category_description` (`category_id`, `description`)
) ENGINE=InnoDB!,
sessions => q!
CREATE TABLE `sessions` (
`id` varchar(32) NOT NULL default '',
`userid` varchar(255) default NULL,
`a_session` text NOT NULL,
`time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
UNIQUE KEY `id` (`id`),
UNIQUE KEY `userid` (`userid`)
) ENGINE=InnoDB!,
specimen_lab_test => q!
CREATE TABLE `specimen_lab_test` (
`specimen_id` smallint NOT NULL default '0',
`lab_test_id` smallint NOT NULL default '0',
PRIMARY KEY (`specimen_id`,`lab_test_id`),
KEY `lab_test_id` (`lab_test_id`)
) ENGINE=InnoDB!,
specimen_sample_type => q!
CREATE TABLE `specimen_sample_type` (
`specimen_id` smallint NOT NULL default '0',
`sample_type_id` smallint NOT NULL default '0',
PRIMARY KEY (`sample_type_id`, `specimen_id`)
) ENGINE=InnoDB!,
specimens => q!
CREATE TABLE `specimens` (
`id` smallint NOT NULL auto_increment,
`sample_code` varchar(4) default NULL,
`description` varchar(40) default NULL,
`active` enum('yes','no') NOT NULL default 'yes',
PRIMARY KEY (`id`),
UNIQUE KEY `sample_code` (`sample_code`)
) ENGINE=InnoDB!,
status_options => q!
CREATE TABLE `status_options` (
`id` smallint NOT NULL auto_increment,
`description` varchar(255) NOT NULL default '',
`is_editable` enum('yes','no') NOT NULL default 'yes',
`is_active` enum('yes','no') NOT NULL default 'yes',
PRIMARY KEY (`id`),
UNIQUE KEY `description` (`description`)
) ENGINE=InnoDB!,
user_functions => q!
CREATE TABLE `user_functions` (
`id` smallint NOT NULL auto_increment,
`function_name` varchar(20) default NULL,
`function_detail` varchar(255) default NULL,
`active` enum('yes','no') NOT NULL default 'yes',
PRIMARY KEY (`id`),
UNIQUE KEY `function_name` (`function_name`),
UNIQUE KEY `function_detail` (`function_detail`)
) ENGINE=InnoDB!,
user_group_function => q!
CREATE TABLE `user_group_function` (
`group_id` smallint NOT NULL,
`function_id` smallint NOT NULL,
PRIMARY KEY (`group_id`,`function_id`),
KEY `function` (`function_id`)
) ENGINE=InnoDB!,
user_groups => q!
CREATE TABLE `user_groups` (
`id` smallint NOT NULL auto_increment,
`group_name` varchar(10) default NULL,
`group_label` varchar(30) default NULL,
`group_detail` varchar(255) default NULL,
`active` enum('yes','no') NOT NULL default 'yes',
PRIMARY KEY (`id`),
UNIQUE KEY `group_name` (`group_name`),
UNIQUE KEY `group_label` (`group_label`)
) ENGINE=InnoDB!,
user_locations => q!
CREATE TABLE `user_locations` (
`id` smallint NOT NULL auto_increment,
`location_name` varchar(50) NOT NULL default '',
`region_code` varchar(6) default NULL,
`active` enum('yes','no') default 'yes',
PRIMARY KEY (`id`),
KEY `region_code` (`region_code`),
UNIQUE KEY `location_name` (`location_name`)
) ENGINE=InnoDB!,
user_message => q!
CREATE TABLE `user_message` (
`id` INT(11) NOT NULL auto_increment,
`recipient_id` SMALLINT(6) NOT NULL default '0',
`sender_id` SMALLINT(6) NOT NULL default '0',
`message` TEXT NOT NULL,
`received` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`acknowledged` TIMESTAMP NULL DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `recipient_id` (`recipient_id`),
KEY `sender_id` (`sender_id`)
) ENGINE=InnoDB!,
user_permission => q!
CREATE TABLE `user_permission` (
`user_id` smallint NOT NULL NOT NULL auto_increment,
`function_id` smallint NOT NULL default '0',
PRIMARY KEY (`user_id`,`function_id`),
KEY `function_id` (`function_id`)
) ENGINE=InnoDB!,
users => q!
CREATE TABLE `users` (
`id` smallint NOT NULL auto_increment,
`username` varchar(50) NOT NULL default 'username',
`first_name` varchar(50) NOT NULL default 'first_name',
`last_name` varchar(50) NOT NULL default 'last_name',
`password` varchar(32) NOT NULL default 'password',
`email` varchar(50) default NULL,
`user_location_id` smallint NOT NULL default '0',
`designation` varchar(255) NOT NULL default 'designation',
`group_id` smallint NOT NULL default '0',
`last_login` timestamp NOT NULL default current_timestamp,
`active` enum('yes','no') NOT NULL default 'yes',
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`),
UNIQUE KEY `login_id` (`first_name`, `last_name`),
UNIQUE KEY `email` (`email`),
KEY `group_id` (`group_id`),
KEY `user_location_id` (`user_location_id`)
) ENGINE=InnoDB!,
};
# _user_locations => q!
# CREATE TABLE `user_locations` (
# `id` int(11) unsigned NOT NULL auto_increment,
# `location_name` varchar(50) NOT NULL default '',
# `region_code` varchar(12) default NULL,
# `email_contact` varchar(50) NOT NULL default '',
# `active` enum('yes','no') NOT NULL default 'yes',
# PRIMARY KEY (`id`),
# UNIQUE KEY `location_name` (`location_name`)
# ) ENGINE=InnoDB!,
#
# Foreign keys for tables
#
$FOREIGN_KEYS = {
fk_audit_request_options => q!
ALTER TABLE `audit_request_options`
ADD CONSTRAINT `audit_request_options_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `audit_request_categories` (`id`)!,
fk_context_warning_diagnosis => q!
ALTER TABLE `context_warning_diagnosis`
ADD CONSTRAINT `context_warning_diagnosis_ibfk_1` FOREIGN KEY (`diagnosis_id`) REFERENCES `diagnoses` (`id`),
ADD CONSTRAINT `context_warning_diagnosis_ibfk_2` FOREIGN KEY (`context_warning_id`) REFERENCES `diagnosis_context_warnings` (`id`)!,
fk_deleted_requests => q!
ALTER TABLE `deleted_requests`
ADD CONSTRAINT `deleted_requests_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)!,
fk_diagnoses => q!
ALTER TABLE `diagnoses`
ADD CONSTRAINT `diagnoses_ibfk_1` FOREIGN KEY (`diagnostic_category_id`) REFERENCES `diagnostic_categories` (`id`)!,
fk_email_contacts => q!
ALTER TABLE `email_contacts`
ADD CONSTRAINT `email_contacts_ibfk_1` FOREIGN KEY (`referral_source_id`) REFERENCES `referral_sources` (`id`)!,
fk_error_code_assignment => q!
ALTER TABLE `error_code_assignment`
ADD CONSTRAINT `error_code_config_ibfk_1` FOREIGN KEY (`error_code_id`) REFERENCES `error_codes` (`id`)!,
fk_icdo_category => q!
ALTER TABLE `icdo_category`
ADD CONSTRAINT `icdo_category_ibfk_1` FOREIGN KEY (`diagnostic_category_id`) REFERENCES `diagnostic_categories` (`id`) ON UPDATE CASCADE!,
fk_lab_test_data_type => q!
ALTER TABLE `lab_test_data_type`
ADD CONSTRAINT `lab_test_data_type_ibfk_2` FOREIGN KEY (`lab_test_id`) REFERENCES `lab_tests` (`id`),
ADD CONSTRAINT `lab_test_data_type_ibfk_1` FOREIGN KEY (`data_type_id`) REFERENCES `lab_test_result_data_types` (`id`)!,
fk_lab_test_result_options => q!
ALTER TABLE `lab_test_result_options`
ADD CONSTRAINT `lab_test_result_options_ibfk_1` FOREIGN KEY (`data_type_id`) REFERENCES `lab_test_result_data_types` (`id`)!,
fk_lab_section_sample_type => q!
ALTER TABLE `lab_section_sample_type`
ADD CONSTRAINT `lab_section_sample_type_ibfk_1` FOREIGN KEY (`lab_section_id`) REFERENCES `lab_sections` (`id`),
ADD CONSTRAINT `lab_section_sample_type_ibfk_2` FOREIGN KEY (`sample_type_id`) REFERENCES `sample_types` (`id`)!,
fk_lab_section_status_option => q!
ALTER TABLE `lab_section_status_option`
ADD CONSTRAINT `lab_section_status_option_ibfk_1` FOREIGN KEY (`lab_section_id`) REFERENCES `lab_sections` (`id`),
ADD CONSTRAINT `lab_section_status_option_ibfk_2` FOREIGN KEY (`status_option_id`) REFERENCES `lab_test_status_options` (`id`)!,
fk_lab_test_sample_type => q!
ALTER TABLE `lab_test_sample_type`
ADD CONSTRAINT `lab_test_sample_type_ibfk_1` FOREIGN KEY (`lab_test_id`) REFERENCES `lab_tests` (`id`),
ADD CONSTRAINT `lab_test_sample_type_ibfk_2` FOREIGN KEY (`sample_type_id`) REFERENCES `sample_types` (`id`)!,
fk_linked_lab_test => q!
ALTER TABLE `linked_lab_test`
ADD CONSTRAINT `linked_lab_test_ibfk_1` FOREIGN KEY (`parent_test_id`) REFERENCES `lab_tests` (`id`),
ADD CONSTRAINT `linked_lab_test_ibfk_2` FOREIGN KEY (`linked_test_id`) REFERENCES `lab_tests` (`id`)!,
fk_lab_tests => q!
ALTER TABLE `lab_tests`
ADD CONSTRAINT `lab_tests_ibfk_1` FOREIGN KEY (`lab_section_id`) REFERENCES `lab_sections` (`id`);!,
fk_local_network_locations => q!
ALTER TABLE `local_network_locations`
ADD CONSTRAINT `local_network_locations_ibfk_1` FOREIGN KEY(`parent_id`) REFERENCES `parent_organisations` (`id`);!,
fk_logins => q!
ALTER TABLE `logins`
ADD CONSTRAINT `logins_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);!,
fk_panel_lab_test => q!
ALTER TABLE `panel_lab_test`
ADD CONSTRAINT `panel_lab_test_ibfk_1` FOREIGN KEY (`panel_test_id`) REFERENCES `lab_tests` (`id`),
ADD CONSTRAINT `panel_lab_test_ibfk_2` FOREIGN KEY (`lab_test_id`) REFERENCES `lab_tests` (`id`)!,
fk_parent_organisations => q!
ALTER TABLE `parent_organisations`
ADD CONSTRAINT `parent_organisations_ibfk_1` FOREIGN KEY (`referral_type_id`) REFERENCES `referral_types` (`id`)!,
fk_patient_case => q!
ALTER TABLE `patient_case`
ADD CONSTRAINT `patient_case_ibfk_1` FOREIGN KEY (`referral_source_id`)
REFERENCES `referral_sources` (`id`),
ADD CONSTRAINT `patient_case_ibfk_2` FOREIGN KEY (`patient_id`) REFERENCES `patients` (`id`) ON DELETE CASCADE!,
fk_patient_notes => q!
ALTER TABLE `patient_notes`
ADD CONSTRAINT `patient_notes_ibfk_1` FOREIGN KEY (`patient_id`) REFERENCES `patients` (`id`)!,
fk_patient_demographics => q!
ALTER TABLE `patient_demographics`
ADD CONSTRAINT `patient_demographics_ibfk_3` FOREIGN KEY (`gp_id`) REFERENCES `referrers` (`id`),
ADD CONSTRAINT `patient_demographics_ibfk_2` FOREIGN KEY (`practice_id`) REFERENCES `referral_sources` (`id`),
ADD CONSTRAINT `patient_demographics_ibfk_1` FOREIGN KEY (`patient_id`) REFERENCES `patients` (`id`)!,
fk_patient_demographic_history => q!
ALTER TABLE `patient_demographic_history`
ADD CONSTRAINT `patient_demographic_history_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
ADD CONSTRAINT `patient_demographic_history_ibfk_1` FOREIGN KEY (`patient_id`) REFERENCES `patients` (`id`)!,
fk_patient_edits => q!
ALTER TABLE `patient_edits`
ADD CONSTRAINT `patient_edits_ibfk_3` FOREIGN KEY (`error_code_id`) REFERENCES `error_codes` (`id`),
ADD CONSTRAINT `patient_edits_ibfk_2` FOREIGN KEY (`patient_id`) REFERENCES `patients` (`id`),
ADD CONSTRAINT `patient_edits_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)!,
fk_patient_trial => q!
ALTER TABLE `patient_trial`
ADD CONSTRAINT `patient_trial_ibfk_2` FOREIGN KEY (`trial_id`) REFERENCES `clinical_trials` (`id`),
ADD CONSTRAINT `patient_trial_ibfk_1` FOREIGN KEY (`patient_id`) REFERENCES `patients` (`id`)!,
fk_pre_registration_lab_test => q!
ALTER TABLE `pre_registration_lab_test`
ADD CONSTRAINT `pre_registration_lab_test_ibfk_2` FOREIGN KEY (`lab_test_id`) REFERENCES `lab_tests` (`id`),
ADD CONSTRAINT `pre_registration_lab_test_ibfk_1` FOREIGN KEY (`reg_specimen_id`)
REFERENCES `pre_registration_specimen` (`id`) ON DELETE CASCADE!,
fk_pre_registration_specimen => q!
ALTER TABLE `pre_registration_specimen`
ADD CONSTRAINT `pre_registration_specimen_ibfk_1` FOREIGN KEY (`pre_reg_id`)
REFERENCES `pre_registration` (`id`) ON DELETE CASCADE!,
fk_referrers => q!
ALTER TABLE `referrers`
ADD CONSTRAINT `referrers_ibfk_1` FOREIGN KEY (`referral_type_id`) REFERENCES `referral_types` (`id`)!,
fk_referral_sources => q!
ALTER TABLE `referral_sources`
ADD CONSTRAINT `referral_sources_ibfk_1` FOREIGN KEY (`referral_type_id`) REFERENCES `referral_types` (`id`),
ADD CONSTRAINT `referral_sources_ibfk_2` FOREIGN KEY (`parent_organisation_id`) REFERENCES `parent_organisations` (`id`)!,
fk_referrer_department => q!
ALTER TABLE `referrer_department`
ADD CONSTRAINT `referrer_department_ibfk_3` FOREIGN KEY (`hospital_department_code`) REFERENCES `hospital_departments` (`id`),
ADD CONSTRAINT `referrer_department_ibfk_1` FOREIGN KEY (`referrer_id`) REFERENCES `referrers` (`id`),
ADD CONSTRAINT `referrer_department_ibfk_2` FOREIGN KEY (`parent_organisation_id`) REFERENCES `parent_organisations` (`id`)!,
fk_report_error_codes => q!
ALTER TABLE `report_error_codes`
ADD CONSTRAINT `report_error_codes_ibfk_1` FOREIGN KEY (`code`) REFERENCES `error_codes` (`code`)!,
fk_request_audit => q!
ALTER TABLE `request_audit`
ADD CONSTRAINT `request_audit_ibfk_2` FOREIGN KEY (`audit_request_option_id`) REFERENCES `audit_request_options` (`id`),
ADD CONSTRAINT `request_audit_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE!,
fk_request_authorisation_diagnosis => q!
ALTER TABLE `request_authorisation_diagnosis`
ADD CONSTRAINT `request_authorisation_diagnosis_ibfk_4` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
ADD CONSTRAINT `request_authorisation_diagnosis_ibfk_3` FOREIGN KEY (`report_diagnosis_id`) REFERENCES `diagnoses` (`id`),
ADD CONSTRAINT `request_authorisation_diagnosis_ibfk_2` FOREIGN KEY (`auth_diagnosis_id`) REFERENCES `diagnoses` (`id`),
ADD CONSTRAINT `request_authorisation_diagnosis_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE!,
fk_request_consent => q!
ALTER TABLE `request_consent`
ADD CONSTRAINT `request_consent_ibfk_2` FOREIGN KEY (`consent_id`) REFERENCES `consent_options` (`id`),
ADD CONSTRAINT `request_consent_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE!,
fk_request_diagnosis_history => q!
ALTER TABLE `request_diagnosis_history`
ADD CONSTRAINT `request_diagnosis_history_ibfk_4` FOREIGN KEY (`option_id`) REFERENCES `diagnosis_change_options` (`id`),
ADD CONSTRAINT `request_diagnosis_history_ibfk_3` FOREIGN KEY (`diagnosis_id`) REFERENCES `diagnoses` (`id`),
ADD CONSTRAINT `request_diagnosis_history_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
ADD CONSTRAINT `request_diagnosis_history_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE!,
fk_request_dispatch_log => q!
ALTER TABLE `request_dispatch_log`
ADD CONSTRAINT `request_dispatch_log_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE!,
fk_request_error_code => q!
ALTER TABLE `request_error_code`
ADD CONSTRAINT `request_error_code_ibfk_3` FOREIGN KEY (`error_code_id`) REFERENCES `error_codes` (`id`),
ADD CONSTRAINT `request_error_code_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
ADD CONSTRAINT `request_error_code_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE!,
fk_request_external_ref => q!
ALTER TABLE `request_external_ref`
ADD CONSTRAINT `request_external_ref_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE!,
fk_request_general_notes => q!
ALTER TABLE `request_general_notes`
ADD CONSTRAINT `request_general_notes_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE!,
fk_request_history => q!
ALTER TABLE `request_history`
ADD CONSTRAINT `request_history_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE,
ADD CONSTRAINT `request_history_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)!,
fk_request_initial_screen => q!
ALTER TABLE `request_initial_screen`
ADD CONSTRAINT `request_initial_screen_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE,
ADD CONSTRAINT `request_initial_screen_ibfk_2` FOREIGN KEY (`screen_id`) REFERENCES `screens` (`id`)!,
fk_request_lab_section_foreign_id => q!
ALTER TABLE `request_lab_section_foreign_id`
ADD CONSTRAINT `request_lab_section_foreign_id_ibfk_2` FOREIGN KEY (`lab_section_id`) REFERENCES `lab_sections` (`id`),
ADD CONSTRAINT `request_section_foreign_id_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE!,
fk_request_lab_section_notes => q!
ALTER TABLE `request_lab_section_notes`
ADD CONSTRAINT `request_lab_section_notes_ibfk_2` FOREIGN KEY (`lab_section_id`) REFERENCES `lab_sections` (`id`),
ADD CONSTRAINT `request_section_notes_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE!,
fk_request_lab_test_history => q!
ALTER TABLE `request_lab_test_history`
ADD CONSTRAINT `request_lab_test_history_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
ADD CONSTRAINT `request_lab_test_history_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE!,
fk_request_lab_test_results => q!
ALTER TABLE `request_lab_test_results`
ADD CONSTRAINT `request_lab_test_results_ibfk_2` FOREIGN KEY (`lab_test_id`) REFERENCES `lab_tests` (`id`),
ADD CONSTRAINT `request_lab_test_results_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE!,
fk_request_lab_test_status => q!
ALTER TABLE `request_lab_test_status`
ADD CONSTRAINT `request_lab_test_status_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE,
ADD CONSTRAINT `request_lab_test_status_ibfk_2` FOREIGN KEY (`lab_test_id`) REFERENCES `lab_tests` (`id`),
ADD CONSTRAINT `request_lab_test_status_ibfk_3` FOREIGN KEY (`status_option_id`) REFERENCES `lab_test_status_options` (`id`),
ADD CONSTRAINT `request_lab_test_status_ibfk_4` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)!,
fk_request_option => q!
ALTER TABLE `request_option`
ADD CONSTRAINT `request_option_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE,
ADD CONSTRAINT `request_option_ibfk_2` FOREIGN KEY (`option_id`) REFERENCES `additional_options` (`id`)!,
fk_request_phone_log => q!
ALTER TABLE `request_phone_log`
ADD CONSTRAINT `request_phone_log_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE,
ADD CONSTRAINT `request_phone_log_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)!,
fk_request_print_log => q!
ALTER TABLE `request_print_log`
ADD CONSTRAINT `request_print_log_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
ADD CONSTRAINT `request_print_log_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE!,
fk_request_report_detail => q!
ALTER TABLE `request_report_detail`
ADD CONSTRAINT `request_report_detail_ibfk_2` FOREIGN KEY (`diagnosis_id`) REFERENCES `diagnoses` (`id`),
ADD CONSTRAINT `request_report_detail_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE!,
fk_request_report_history => q!
ALTER TABLE `request_report_history`
ADD CONSTRAINT `request_report_history_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
ADD CONSTRAINT `request_report_history_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE!,
fk_request_result_summaries => q!
ALTER TABLE `request_result_summaries`
ADD CONSTRAINT `request_result_summaries_ibfk_2` FOREIGN KEY (`lab_section_id`) REFERENCES `lab_sections` (`id`),
ADD CONSTRAINT `request_result_summaries_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE!,
fk_request_secondary_diagnosis => q!
ALTER TABLE `request_secondary_diagnosis`
ADD CONSTRAINT `request_secondary_diagnosis_ibfk_2` FOREIGN KEY (`secondary_diagnosis_id`) REFERENCES `diagnoses` (`id`),
ADD CONSTRAINT `request_secondary_diagnosis_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE!,
fk_request_specimen => q!
ALTER TABLE `request_specimen`
ADD CONSTRAINT `request_specimen_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE,
ADD CONSTRAINT `request_specimen_ibfk_2` FOREIGN KEY (`specimen_id`) REFERENCES `specimens` (`id`)!,
fk_request_specimen_detail => q!
ALTER TABLE `request_specimen_detail`
ADD CONSTRAINT `request_specimen_detail_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE!,
fk_request_trial => q!
ALTER TABLE `request_trial`
ADD CONSTRAINT `request_trial_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE,
ADD CONSTRAINT `request_trial_ibfk_2` FOREIGN KEY (`trial_id`) REFERENCES `clinical_trials` (`id`)!,
fk_request_view_log => q!
ALTER TABLE `request_view_log`
ADD CONSTRAINT `request_view_log_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
ADD CONSTRAINT `request_view_log_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE!,
fk_requests => q!
ALTER TABLE `requests`
ADD CONSTRAINT `requests_ibfk_3` FOREIGN KEY (`status_option_id`) REFERENCES `status_options` (`id`),
ADD CONSTRAINT `requests_ibfk_1` FOREIGN KEY (`referrer_department_id`) REFERENCES `referrer_department` (`id`),
ADD CONSTRAINT `requests_ibfk_2` FOREIGN KEY (`patient_case_id`) REFERENCES `patient_case` (`id`)!,
fk_result_summary_options => q!
ALTER TABLE `result_summary_options`
ADD CONSTRAINT `result_summary_options_ibfk_1` FOREIGN KEY (`lab_section_id`) REFERENCES `lab_sections` (`id`)!,
fk_screen_lab_test => q!
ALTER TABLE `screen_lab_test`
ADD CONSTRAINT `screen_lab_test_ibfk_1` FOREIGN KEY (`screen_id`) REFERENCES `screens` (`id`),
ADD CONSTRAINT `screen_lab_test_ibfk_2` FOREIGN KEY (`lab_test_id`) REFERENCES `lab_tests` (`id`);!,
fk_screen_lab_test_detail => q!
ALTER TABLE `screen_lab_test_detail`
ADD CONSTRAINT `screen_lab_test_detail_ibfk_1` FOREIGN KEY (`screen_id`) REFERENCES `screens` (`id`),
ADD CONSTRAINT `screen_lab_test_detail_ibfk_2` FOREIGN KEY (`lab_section_id`) REFERENCES `lab_sections` (`id`);!,
fk_screens => q!
ALTER TABLE `screens`
ADD CONSTRAINT `screens_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `screen_category` (`id`);!,
fk_specimen_lab_test => q!
ALTER TABLE `specimen_lab_test`
ADD CONSTRAINT `specimen_lab_test_ibfk_2` FOREIGN KEY (`lab_test_id`) REFERENCES `lab_tests` (`id`),
ADD CONSTRAINT `specimen_lab_test_ibfk_1` FOREIGN KEY (`specimen_id`) REFERENCES `specimens` (`id`)!,
fk_specimen_sample_type => q!
ALTER TABLE `specimen_sample_type`
ADD CONSTRAINT `specimen_sample_type_ibfk_1` FOREIGN KEY (`specimen_id`) REFERENCES `specimens` (`id`),
ADD CONSTRAINT `specimen_sample_type_ibfk_2` FOREIGN KEY (`sample_type_id`) REFERENCES `sample_types` (`id`)!,
fk_user_group_function => q!
ALTER TABLE `user_group_function`
ADD CONSTRAINT `user_group_function_ibfk_2` FOREIGN KEY (`group_id`) REFERENCES `user_groups` (`id`),
ADD CONSTRAINT `user_group_function_ibfk_1` FOREIGN KEY (`function_id`) REFERENCES `user_functions` (`id`)!,
# script cannot create this FK, but can delete it & can be created manually - WFT???
# fk_user_message => q!
# ALTER TABLE `user_message`
# ADD CONSTRAINT `user_message_ibfk2` FOREIGN KEY (`recipient_id`) REFERENCES `users` (`id`),
# ADD CONSTRAINT `user_message_ibfk1` FOREIGN KEY (`sender_id`) REFERENCES `users` (`id`)!,
fk_user_locations => q!
ALTER TABLE `user_locations`
ADD CONSTRAINT `user_location_ibfk_1` FOREIGN KEY (`region_code`) REFERENCES `parent_organisations` (`parent_code`)!,
fk_user_permission => q!
ALTER TABLE `user_permission`
ADD CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`function_id`)
REFERENCES `user_functions` (`id`),
ADD CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);!,
fk_users => q!
ALTER TABLE `users`
ADD CONSTRAINT `users_ibfk_1` FOREIGN KEY (`group_id`) REFERENCES `user_groups` (`id`),
ADD CONSTRAINT `users_ibfk_2` FOREIGN KEY (`user_location_id`) REFERENCES `user_locations` (`id`);!,
};
