#===============================================================================
# view 'definer' user MUST exist in mysql.users or views will be inaccessible
# split into tables, views, functions & foreign_key sections
#===============================================================================
$SQL_FOR_TABLE = {
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_log => q!
CREATE TABLE `admin_log` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`action` VARCHAR(255) NOT NULL DEFAULT '',
`user_id` SMALLINT(6) NOT NULL DEFAULT '0',
`time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
INDEX `user_id` (`user_id`),
INDEX `time` (`time`)
) 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!,
anatomical_sites => q!
CREATE TABLE `anatomical_sites` (
`id` SMALLINT(6) NOT NULL AUTO_INCREMENT,
`site_name` VARCHAR(50) NOT NULL,
`snomed` VARCHAR(6) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `site_name` (`site_name`),
INDEX `snomed_code` (`snomed`)
) 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` text,
`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!,
diagnosis_lab_test => q!
CREATE TABLE `diagnosis_lab_test` (
`diagnosis_id` SMALLINT(6) NOT NULL DEFAULT '0',
`lab_test_id` SMALLINT(6) NOT NULL DEFAULT '0',
PRIMARY KEY (`diagnosis_id`, `lab_test_id`),
INDEX `diagnosis_id` (`diagnosis_id`)
)
COLLATE='latin1_swedish_ci'
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!,
draft_report_users => q!
CREATE TABLE `draft_report_users` (
`id` SMALLINT(6) 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) NOT NULL,
`last_login` TIMESTAMP NULL DEFAULT NULL,
`active` ENUM('yes','no') NOT NULL DEFAULT 'yes',
PRIMARY KEY (`id`),
UNIQUE INDEX `username` (`username`),
UNIQUE INDEX `login_id` (`first_name`, `last_name`),
UNIQUE INDEX `email` (`email`)
) 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 '',
`referral_source_id` smallint NOT NULL default '0',
`type` enum('mdt','report') default NULL,
`scope` enum('hospital','organisation','department') default NULL,
`department_id` SMALLINT(6) NULL DEFAULT NULL,
`contact_address` varchar(255) NOT NULL default '',
`status` ENUM('all','new', 'manual') NOT NULL DEFAULT 'all',
`is_active` enum('yes','no') NOT NULL default 'yes',
PRIMARY KEY (`id`),
UNIQUE INDEX `src_type_department_address`
(`referral_source_id`, `type`, `department_id`, `contact_address`),
INDEX `contact_address` (`contact_address`),
INDEX `referral_source_id` (`referral_source_id`),
INDEX `department_id` (`department_id`)
) ENGINE=InnoDB!,
error_codes => q!
CREATE TABLE `error_codes` (
`id` smallint NOT NULL auto_increment,
`code` varchar(4) 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!,
general_comms_log =>q!
CREATE TABLE `general_comms_log` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`user_id` SMALLINT(6) NOT NULL DEFAULT '0',
`details` TEXT NOT NULL,
`time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
INDEX `user_id` (`user_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_sub_category => q!
CREATE TABLE `icdo_sub_category` (
`diagnostic_category_id` smallint NOT NULL,
`icdo3` varchar(6) NOT NULL,
PRIMARY KEY (`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',
`has_labels` enum('yes','no') NOT NULL default 'no',
`auto_expand` 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_accreditation => q!
CREATE TABLE `lab_test_accreditation` (
`lab_test_id` SMALLINT(6) NOT NULL,
`accreditation_date` DATE NOT NULL,
PRIMARY KEY (`lab_test_id`)
) 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!,
ngis_clinical_indications => q!
CREATE TABLE `ngis_clinical_indications` (
`id` SMALLINT(6) NOT NULL AUTO_INCREMENT,
`code` CHAR(4) NOT NULL,
`category` VARCHAR(255) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `indication_id` (`code`),
UNIQUE INDEX `category` (`category`)
) ENGINE=InnoDB!,
ngis_diagnosis => q!
CREATE TABLE `ngis_diagnosis` (
`diagnosis_id` SMALLINT(6) NOT NULL,
`ngis_indication_id` SMALLINT(6) NOT NULL,
PRIMARY KEY (`diagnosis_id`),
INDEX `ngis_indication_id` (`ngis_indication_id`)
) ENGINE=InnoDB!,
ngis_lab_test => q!
CREATE TABLE `ngis_lab_test` (
`id` SMALLINT(6) NOT NULL AUTO_INCREMENT,
`lab_test_id` SMALLINT(6) NOT NULL,
`ngis_test_code` VARCHAR(6) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `lab_test_ngis_code` (`ngis_test_code`, `lab_test_id`),
INDEX `lab_test_id` (`lab_test_id`)
) 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_bcr_abl => q!
CREATE TABLE `patient_bcr_abl` (
`nhs_number` VARCHAR(10) NOT NULL,
`diagnosis_date` DATE NOT NULL,
`first_line_date` DATE NULL DEFAULT NULL,
`treatment` VARCHAR(50) NOT NULL,
`line_number` VARCHAR(50) NOT NULL,
`phase` VARCHAR(50) NOT NULL,
`timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`nhs_number`)
) 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!,
report_notification => q!
CREATE TABLE `report_notification` (
`id` SMALLINT(6) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) NOT NULL,
`type` ENUM('referrer','hospital','organisation') NOT NULL,
`department_id` SMALLINT(6) NULL DEFAULT NULL,
`identifier` VARCHAR(50) NOT NULL,
`status` ENUM('new','all') NOT NULL DEFAULT 'all',
`contact_address` VARCHAR(255) NOT NULL,
`is_active` ENUM('yes','no') NOT NULL DEFAULT 'yes',
`timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE INDEX `identity_address` (`name`, `contact_address`, `identifier`),
INDEX `department` (`department_id`)
)
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_draft_report => q!
CREATE TABLE `request_draft_report` (
`user_id` SMALLINT(6) NOT NULL,
`request_id` INT(11) NOT NULL,
`request_notes` TEXT NULL,
`clinical_details` TEXT NULL,
`biopsy_site` VARCHAR(255) NULL DEFAULT NULL,
`gross_description` VARCHAR(255) NULL DEFAULT NULL,
`morphology` TEXT NULL,
`comment` TEXT NULL,
`status` ENUM('new','relapse','default') NOT NULL DEFAULT 'default',
`diagnosis_id` SMALLINT(6) NOT NULL,
`secondary_diagnosis_id` SMALLINT(6) NULL DEFAULT NULL,
`specimen_quality` ENUM('good','adequate','poor') NULL DEFAULT 'adequate',
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`request_id`, `user_id`),
INDEX `request_draft_report_ibfk_1` (`user_id`),
INDEX `request_draft_report_ibfk_2` (`diagnosis_id`),
INDEX `request_draft_report_ibfk_3` (`secondary_diagnosis_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_error_code_details => q!
CREATE TABLE `request_error_code_details` (
`request_error_code_id` INT(11) NOT NULL DEFAULT '0',
`details` VARCHAR(255),
PRIMARY KEY (`request_error_code_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,
`rdw_sd` CHAR(4) NOT NULL,
`rdw_sd_f` TINYINT(1) UNSIGNED NOT NULL,
`rdw_cv` CHAR(4) NOT NULL,
`rdw_cv_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_hl7_log => q!
CREATE TABLE `request_hl7_log` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`request_id` INT(11) NOT NULL DEFAULT '0',
`service` ENUM('ICE','WinPath') NOT NULL,
`response` VARCHAR(255) NOT NULL,
`timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
INDEX `request_id` (`request_id`)
) 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_form => q!
CREATE TABLE `request_form` (
`id` VARCHAR(50) NOT NULL,
`last_name` VARCHAR(50) NOT NULL,
`first_name` VARCHAR(50) NOT NULL,
`dob` DATE NOT NULL,
`gender` ENUM('M','F','U') NOT NULL,
`nhs_number` VARCHAR(10) NOT NULL,
`patient_number` VARCHAR(50) NULL DEFAULT NULL,
`location_name` VARCHAR(50) NOT NULL,
`location_id` SMALLINT(6) NOT NULL,
`referrer` VARCHAR(50) NOT NULL,
`pds_code` VARCHAR(50) NULL DEFAULT NULL,
`user_id` SMALLINT(6) NOT NULL,
`requested_by` VARCHAR(50) NOT NULL,
`created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`imported` DATETIME NULL DEFAULT NULL,
PRIMARY KEY (`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_notification => q!
CREATE TABLE `request_notification` (
`request_id` INT(11) NOT NULL DEFAULT '0',
`datetime` DATETIME NOT NULL DEFAULT '1000-01-01 00:00:00',
PRIMARY KEY (`request_id`)
)
ENGINE=MyISAM!,
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` text NOT NULL,
`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_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',
`user_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_storage => q!
CREATE TABLE `request_storage` (
`vialId` VARCHAR(10) NOT NULL,
`request_id` INT(11) NOT NULL DEFAULT '0',
`specimen_id` SMALLINT(6) NOT NULL DEFAULT '0',
`sample` VARCHAR(25) NOT NULL,
`part_number` VARCHAR(25) NOT NULL,
`volume` SMALLINT(6) NULL DEFAULT NULL,
`concentration` SMALLINT(6) NULL DEFAULT NULL,
`source` VARCHAR(25) NOT NULL,
`method` VARCHAR(25) NULL DEFAULT NULL,
`rack_id` SMALLINT(6) NULL DEFAULT NULL,
`vial_location` VARCHAR(3) NULL DEFAULT NULL,
`comment` VARCHAR(255) NULL DEFAULT NULL,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`signed_out` TIMESTAMP NULL DEFAULT NULL,
PRIMARY KEY (`vialId`),
INDEX `specimen_id` (`specimen_id`),
INDEX `request_id` (`request_id`),
INDEX `rack_id` (`rack_id`)
) ENGINE=InnoDB!,
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(6) UNSIGNED 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_expansion => q!
CREATE TABLE `result_summary_expansion` (
`result_summary_option_id` SMALLINT(6) NOT NULL DEFAULT '0',
`description` TEXT NOT NULL,
`is_active` ENUM('yes','no') NOT NULL DEFAULT 'yes',
PRIMARY KEY (`result_summary_option_id`)
) ENGINE=InnoDB!,
result_summary_lab_test => q!
CREATE TABLE `result_summary_lab_test` (
`result_summary_id` SMALLINT(6) NOT NULL,
`lab_test_id` SMALLINT(6) NOT NULL,
PRIMARY KEY (`result_summary_id`, `lab_test_id`),
INDEX `result_summary_lab_test_ibfk2` (`lab_test_id`)
)
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!,
rfc => q!
CREATE TABLE IF NOT EXISTS `rfc` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`reason` varchar(50) NOT NULL,
`details` text NOT NULL,
`status` varchar(50) NOT NULL,
`user_id` smallint(6) NOT NULL,
`remote_id` varchar(50) DEFAULT NULL,
`created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
`updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `user_id` (`user_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!,
storage_racks => q!
CREATE TABLE `storage_racks` (
`id` smallint NOT NULL auto_increment,
`plateId` varchar(10) NOT NULL,
`storage_location` varchar(50) default NULL,
`is_active` enum('yes','no') NOT NULL default 'yes',
PRIMARY KEY (`id`),
UNIQUE KEY `plateID` (`plateID`)
) 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!,
user_registration => q!
CREATE TABLE `user_registration` (
`user_id` SMALLINT(6) NOT NULL DEFAULT 0,
`registration_number` VARCHAR(8) NOT NULL DEFAULT '',
PRIMARY KEY (`user_id`),
UNIQUE INDEX `registration_number` (`registration_number`)
) 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!,
};
#
# functions:
#
$FUNCTIONS = {
ucfirst => q!
CREATE DEFINER=`raj`@`%` FUNCTION `ucFirst`(`x` varchar(255))
RETURNS varchar(255) CHARSET latin1
LANGUAGE SQL
READS SQL DATA
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
return concat( upper(substring(x,1,1)),lower(substring(x,2)) )!,
};
#
# views after all tables created:
#
$VIEWS = {
authorised_reports_view => q!
CREATE ALGORITHM=UNDEFINED DEFINER=`raj`@`%` SQL SECURITY DEFINER
VIEW `authorised_reports_view` AS
SELECT `r`.`id`, `r`.`request_number`, `r`.`year`, date(`r`.`created_at`)
as 'reg_date', `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`,
date(`rh`.time) as 'auth_date'
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`
JOIN `request_history` `rh` on ( `rh`.`request_id` = `r`.`id`
and `rh`.`action` = 'authorised' )!,
# Genomics db only:
family_id_view => q!
CREATE ALGORITHM=UNDEFINED DEFINER=`raj`@`%` SQL SECURITY DEFINER
VIEW `family_id_view` AS
SELECT r.id as request_id, tr.result as family_id
FROM requests r
JOIN ( request_lab_test_results tr JOIN lab_tests lt
on tr.lab_test_id = lt.id and lt.test_name = 'family_id')
on tr.request_id = r.id!,
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_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_storage_view => q!
CREATE ALGORITHM=UNDEFINED DEFINER=`raj`@`%` SQL SECURITY DEFINER
VIEW `request_storage_view` AS
SELECT vialId, request_id
FROM requests r
JOIN request_storage rs on rs.request_id = r.id
UNION
SELECT result as 'vialId', request_id
FROM requests r
JOIN request_lab_test_results tr on tr.request_id = r.id
JOIN lab_tests lt on tr.lab_test_id = lt.id
and lt.test_name in ('edta1_fluidx','tumour_fluidx')!,
};
#
# 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_admin_log => q!
ALTER TABLE `admin_log`
ADD CONSTRAINT `admin_log_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`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_diagnosis_lab_test => q!
ALTER TABLE `diagnosis_lab_test`
ADD CONSTRAINT `diagnosis_lab_test_ibfk_1` FOREIGN KEY (`diagnosis_id`) REFERENCES `diagnoses` (`id`),
ADD CONSTRAINT `diagnosis_lab_test_ibfk_2` FOREIGN KEY (`lab_test_id`) REFERENCES `lab_tests` (`id`)!,
fk_email_contacts => q!
ALTER TABLE `email_contacts`
ADD CONSTRAINT `email_contacts_ibfk_1` FOREIGN KEY (`referral_source_id`) REFERENCES `referral_sources` (`id`),
ADD CONSTRAINT `email_contacts_ibfk_2` FOREIGN KEY (`department_id`) REFERENCES `hospital_departments` (`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_general_comms_log => q!
ALTER TABLE `general_comms_log`
ADD CONSTRAINT `general_comms_log_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)!,
fk_icdo_sub_category => q!
ALTER TABLE `icdo_sub_category`
ADD CONSTRAINT `icdo_sub_category_ibfk_1` FOREIGN KEY (`diagnostic_category_id`) REFERENCES `diagnostic_categories` (`id`) ON UPDATE CASCADE!,
fk_lab_test_accreditation => q!
ALTER TABLE `lab_test_accreditation`
ADD CONSTRAINT `lab_test_accreditation_ibfk_1` FOREIGN KEY (`lab_test_id`) REFERENCES `lab_tests` (`id`)!,
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_ngis_diagnosis => q!
ALTER TABLE `ngis_diagnosis`
ADD CONSTRAINT `ngis_diagnosis_ibfk_1` FOREIGN KEY (`diagnosis_id`) REFERENCES `diagnoses` (`id`),
ADD CONSTRAINT `ngis_diagnosis_ibfk_2` FOREIGN KEY (`ngis_indication_id`) REFERENCES `ngis_clinical_indications` (`id`)!,
fk_ngis_lab_test => q!
ALTER TABLE `ngis_lab_test`
ADD CONSTRAINT `ngis_lab_test_ibfk_1` FOREIGN KEY (`lab_test_id`) REFERENCES `lab_tests` (`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_user_registration => q!
ALTER TABLE `user_registration`
ADD CONSTRAINT `user_registration_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)!,
fk_report_notification => q!
ALTER TABLE `report_notification`
ADD CONSTRAINT `report_notification_ibfk_1` FOREIGN KEY (`department_id`) REFERENCES `hospital_departments` (`id`)!,
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_draft_report => q!
ALTER TABLE `request_draft_report`
ADD CONSTRAINT `request_draft_report_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE,
ADD CONSTRAINT `request_draft_report_ibfk_2` FOREIGN KEY (`diagnosis_id`) REFERENCES `diagnoses` (`id`),
ADD CONSTRAINT `request_draft_report_ibfk_3` FOREIGN KEY (`user_id`) REFERENCES `draft_report_users` (`id`),
ADD CONSTRAINT `request_draft_report_ibfk_4` FOREIGN KEY (`secondary_diagnosis_id`) REFERENCES `diagnoses` (`id`)!,
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_error_code_details => q!
ALTER TABLE `request_error_code_details`
ADD CONSTRAINT `request_error_code_details_ibfk_1` FOREIGN KEY (`request_error_code_id`)
REFERENCES `request_error_code` (`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_form => q!
ALTER TABLE `request_form`
ADD CONSTRAINT `request_form_ibfk1` FOREIGN KEY (`user_id`) REFERENCES
`users` (`id`),
ADD CONSTRAINT `request_form_ibfk2` FOREIGN KEY (`location_id`) REFERENCES
`referral_sources` (`id`)!,
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_3` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
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_storage => q!
ALTER TABLE `request_storage`
ADD CONSTRAINT `request_storage_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE,
ADD CONSTRAINT `request_storage_ibfk_2` FOREIGN KEY (`specimen_id`) REFERENCES `specimens` (`id`),
ADD CONSTRAINT `request_storage_ibfk_3` FOREIGN KEY (`rack_id`) REFERENCES `storage_racks` (`id`)!,
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_expansion => q!
ALTER TABLE `result_summary_expansion`
ADD CONSTRAINT `result_summary_expansion_ibfk_1` FOREIGN KEY (`result_summary_option_id`)
REFERENCES `result_summary_options` (`id`)!,
fk_result_summary_lab_test => q!
ALTER TABLE `result_summary_lab_test`
ADD CONSTRAINT `result_summary_lab_test_ibfk1` FOREIGN KEY (`result_summary_id`) REFERENCES `result_summary_options` (`id`),
ADD CONSTRAINT `result_summary_lab_test_ibfk2` FOREIGN KEY (`lab_test_id`) REFERENCES `lab_tests` (`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_rfc => q!
ALTER TABLE `rfc`
ADD CONSTRAINT `rfc_user_ibfk1` FOREIGN KEY (`user_id`) REFERENCES `users` (`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`);!,
};
