# request_specimen_detail =====================================================
CREATE TABLE `request_specimen_detail` (
`request_id` int NOT NULL default '0',
`description` enum('biopsy_site','gross_description','specimen_quality') default NULL,
`detail` varchar(255) NOT NULL,
PRIMARY KEY (`request_id`,`description`)
) ENGINE=InnoDB;
DROP TEMPORARY TABLE IF EXISTS temp;
CREATE TEMPORARY TABLE temp LIKE request_specimen_detail;
INSERT INTO temp (request_id, description, detail)
SELECT request_id, 'gross_description', detail FROM hilis4.request_gross_description;
INSERT INTO temp (request_id, description, detail)
SELECT request_id, 'specimen_quality', specimen_quality FROM hilis4.request_report;
INSERT INTO request_specimen_detail (request_id, description, detail)
SELECT request_id, description, detail FROM temp ORDER BY request_id;
#ALTER TABLE `request_specimen_detail`
# ADD CONSTRAINT `request_specimen_detail_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE;
# request_report_detail =====================================================
CREATE TABLE `request_report_detail` (
`request_id` int NOT NULL default '0',
`description` enum('morphology','comment','clinical_details') default NULL,
`detail` text,
PRIMARY KEY (`request_id`,`description`)
) ENGINE=InnoDB;
DROP TEMPORARY TABLE IF EXISTS temp;
CREATE TEMPORARY TABLE temp LIKE request_report_detail;
INSERT INTO temp (request_id, description, detail)
SELECT request_id, 'morphology', comment FROM hilis4.request_report WHERE comment IS NOT NULL;
INSERT INTO temp (request_id, description, detail)
SELECT request_id, 'clinical_details', clinical_details FROM hilis4.request_report WHERE clinical_details IS NOT NULL;
INSERT INTO request_report_detail (request_id, description, detail)
SELECT request_id, description, detail FROM temp ORDER BY request_id;
#ALTER TABLE `request_report_detail`
# ADD CONSTRAINT `request_report_detail_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE;
# request_report_history =====================================================
ALTER TABLE `request_report_history` ALTER `field` DROP DEFAULT;
ALTER TABLE `request_report_history` CHANGE COLUMN `field` `field` ENUM('morphology','comment') NULL AFTER `request_id`;
UPDATE request_report_history SET field = 'morphology' WHERE field = 'comment';
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;
#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;
# drop request_gross_description table; delete comment field from request_report;
