# request_specimen_detail =====================================================
CREATE TABLE `request_specimen_detail` (
`request_id` int NOT NULL default '0',
`description` enum('biopsy_site','gross_description') default NULL,
`detail` text,
PRIMARY KEY (`request_id`,`description`)
) ENGINE=InnoDB;
#ALTER TABLE `request_specimen_detail`
# ADD CONSTRAINT `request_specimen_detail_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE;
INSERT INTO request_specimen_detail (request_id, description, detail)
SELECT request_id, 'gross_description', detail FROM hilis4.request_gross_description;
# 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;
CREATE TEMPORARY TABLE temp LIKE request_report_detail;
#ALTER TABLE `request_report_detail`
# ADD CONSTRAINT `request_report_detail_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE;
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;
# 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_additional_diagnosis` (
`request_id` int NOT NULL default '0',
`diagnosis_id` smallint NOT NULL,
`time` timestamp NOT NULL default '0000-00-00 00:00:00' on update CURRENT_TIMESTAMP,
PRIMARY KEY (`request_id`,`diagnosis_id`)
) ENGINE=InnoDB;
#ALTER TABLE `request_additional_diagnosis`
# ADD CONSTRAINT `request_additional_diagnosis_ibfk_2` FOREIGN KEY (`diagnosis_id`) REFERENCES `diagnoses` (`id`),
# ADD CONSTRAINT `request_additional_diagnosis_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE;
# drop request_gross_description table; delete comment field from request_report;
