DROP TABLE IF EXISTS `imaging_options`;
CREATE TABLE `imaging_options` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`description` VARCHAR(50) NOT NULL DEFAULT '',
`option_type` enum('upper_nodal','lower_nodal','extranodal','flag') DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
/* dumping data for imaging_options */
INSERT INTO `imaging_options` (`id`, `description`, `option_type`) VALUES
(1, 'cns', 'extranodal'),
(2, 'git', 'extranodal'),
(3, 'gu', 'extranodal'),
(4, 'blood', 'extranodal'),
(5, 'bone', 'extranodal'),
(6, 'liver', 'extranodal'),
(7, 'pulmonary_left', 'extranodal'),
(8, 'pulmonary_right', 'extranodal'),
(9, 'marrow', 'extranodal'),
(10, 'orbit_left', 'extranodal'),
(11, 'orbit_right', 'extranodal'),
(12, 'skin', 'extranodal'),
(13, 'salivary_gland_left', 'extranodal'),
(14, 'salivary_gland_right', 'extranodal'),
(15, 'thyroid', 'extranodal'),
(16, 'pericardium', 'extranodal'),
(17, 'muscle', 'extranodal'),
(19, 'waldeyer', 'upper_nodal'),
(20, 'neck_left', 'upper_nodal'),
(21, 'neck_right', 'upper_nodal'),
(22, 'infraclavicular_left', 'upper_nodal'),
(23, 'infraclavicular_right', 'upper_nodal'),
(24, 'axillary_left', 'upper_nodal'),
(25, 'axillary_right', 'upper_nodal'),
(26, 'arm_left', 'upper_nodal'),
(27, 'arm_right', 'upper_nodal'),
(28, 'thymus', 'upper_nodal'),
(29, 'mediastinal', 'upper_nodal'),
(30, 'hilar_left', 'upper_nodal'),
(31, 'hilar_right', 'upper_nodal'),
(32, 'para_aortic', 'lower_nodal'),
(33, 'mesenteric', 'lower_nodal'),
(34, 'iliac_left', 'lower_nodal'),
(35, 'iliac_right', 'lower_nodal'),
(36, 'inguinal_left', 'lower_nodal'),
(37, 'inguinal_right', 'lower_nodal'),
(38, 'popliteal_left', 'lower_nodal'),
(39, 'popliteal_right', 'lower_nodal'),
(40, 'spleen', 'lower_nodal');
INSERT INTO `imaging_options` (`id`, `description`, `option_type`) VALUES
(41, 'extensive', 'flag'),
(42, 'bulky', 'flag'),
(43, 'check_scan', 'flag');
/* ========================================================================== */
DROP TABLE IF EXISTS `events`;
CREATE TABLE `events` (
`id` SMALLINT(6) NOT NULL AUTO_INCREMENT,
`description` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
/* dumping data for events */
INSERT INTO `events` (`id`, `description`) VALUES
(1, 'diagnosis'),
(2, 'first_appointment'),
(3, 'palliative_care'),
(4, 'deceased');
/* ========================================================================== */
DROP TABLE IF EXISTS `patient_event`;
CREATE TABLE `patient_event` (
`patient_id` INT(11) NOT NULL DEFAULT '0',
`event_id` SMALLINT(6) NOT NULL DEFAULT '0',
`date` DATE,
PRIMARY KEY(`patient_id`,`event_id`)
)
ENGINE=InnoDB;
/* dumping data for patient_event */
INSERT INTO patient_event(patient_id, event_id, `date`)
SELECT patient_id, 1, diagnosed FROM patient_chronologies
WHERE diagnosed IS NOT NULL;
INSERT INTO patient_event(patient_id, event_id, `date`)
SELECT patient_id, 2, first_appointment FROM patient_chronologies
WHERE first_appointment IS NOT NULL;
INSERT INTO patient_event(patient_id, event_id, `date`)
SELECT patient_id, 3, palliative_care FROM patient_chronologies
WHERE palliative_care IS NOT NULL;
INSERT INTO patient_event(patient_id, event_id, `date`)
SELECT patient_id, 4, deceased FROM patient_chronologies
WHERE deceased IS NOT NULL;
/* ========================================================================== */
DROP TABLE IF EXISTS `patient_imaging_event`;
CREATE TABLE `patient_imaging_event` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`patient_id` INT(11) NOT NULL DEFAULT '0',
`dataset` SMALLINT(6) NOT NULL DEFAULT '1',
`scan_type` ENUM('ct','pet') DEFAULT NULL,
`stage` ENUM('initial','follow-up') NOT NULL DEFAULT 'initial',
`date` DATE NOT NULL DEFAULT '1901-01-01',
PRIMARY KEY (`id`),
INDEX `patient_id` (`patient_id`),
INDEX `patient_dataset_type` (`patient_id`, `dataset`, `scan_type`)
)
ENGINE=InnoDB;
/* dumping data for patient_imaging_event */
DROP TABLE IF EXISTS temp;
CREATE TEMPORARY TABLE temp(patient_id INT(11));
INSERT INTO temp(patient_id) SELECT patient_id FROM patient_staging_site;
INSERT INTO temp(patient_id) SELECT patient_id FROM patient_staging_other;
INSERT INTO temp(patient_id) SELECT patient_id FROM patient_sites_other;
INSERT INTO patient_imaging_event(patient_id, scan_type) /* others default */
SELECT DISTINCT(patient_id), 'ct' FROM temp;
/* ========================================================================== */
DROP TABLE IF EXISTS `patient_imaging_option`;
CREATE TABLE `patient_imaging_option` (
`imaging_event_id` INT(11) NOT NULL DEFAULT '0',
`imaging_option_id` SMALLINT(6) NOT NULL DEFAULT '0',
INDEX `imaging_event_id` (`imaging_event_id`)
) ENGINE=InnoDB;
/* dumping data for patient_imaging_site */
INSERT INTO patient_imaging_option(imaging_event_id, imaging_option_id)
SELECT t2.id, t1.site_id FROM patient_staging_site t1 JOIN patient_imaging_event
t2 on t1.patient_id = t2.patient_id;
INSERT INTO patient_imaging_option(imaging_event_id, imaging_option_id)
SELECT t2.id, 41 FROM patient_staging_other t1 JOIN patient_imaging_event
t2 on t1.patient_id = t2.patient_id WHERE t1.detail = 'extensive';
INSERT INTO patient_imaging_option(imaging_event_id, imaging_option_id)
SELECT t2.id, 42 FROM patient_staging_other t1 JOIN patient_imaging_event
t2 on t1.patient_id = t2.patient_id WHERE t1.detail = 'bulky';
INSERT INTO patient_imaging_option(imaging_event_id, imaging_option_id)
SELECT t2.id, 43 FROM patient_staging_other t1 JOIN patient_imaging_event
t2 on t1.patient_id = t2.patient_id WHERE t1.detail = 'check_ct';
/* ========================================================================== */
DROP TABLE IF EXISTS `patient_imaging_other`;
CREATE TABLE `patient_imaging_other` (
`imaging_event_id` INT(11) NOT NULL DEFAULT '0',
`details` VARCHAR(255) NOT NULL DEFAULT '',
INDEX `imaging_event_id` (`imaging_event_id`)
)
ENGINE=InnoDB;
/* dumping data for patient_imaging_other */
INSERT INTO patient_imaging_other(imaging_event_id, details)
SELECT t2.id, t1.details FROM patient_sites_other t1 JOIN patient_imaging_event
t2 on t1.patient_id = t2.patient_id WHERE LENGTH(details) >= 3; /* eliminate blanks */
