# request_specimen_detail =====================================================
DROP TABLE IF EXISTS request_specimen_detail;
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') NOT NULL DEFAULT 'adequate',
PRIMARY KEY (`request_id`)
) ENGINE=InnoDB;
INSERT INTO request_specimen_detail (request_id, gross_description, specimen_quality)
SELECT t1.request_id, t2.detail, t1.specimen_quality
FROM hilis4.request_report t1 LEFT JOIN hilis4.request_gross_description t2
on t1.request_id = t2.request_id ORDER BY t1.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 =====================================================
DROP TABLE IF EXISTS request_report_detail;
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`),
INDEX `request_report_ibfk_2` (`diagnosis_id`)
) ENGINE=InnoDB;
INSERT INTO request_report_detail (request_id, clinical_details, comment,
status, diagnosis_id, created_at, updated_at)
SELECT request_id, clinical_details, comment, status, diagnosis_id, created_at,
updated_at FROM request_report 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,
# ADD CONSTRAINT `request_report_detail_ibfk_2` FOREIGN KEY (`diagnosis_id`)
REFERENCES `diagnoses` (`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_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`)
) ENGINE=InnoDB;
RENAME TABLE `request_report` TO `_request_report`;
RENAME TABLE `request_gross_description` TO `_request_gross_description`;
#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;
# create request_report_view
# remove default from hilis4.request_specimen_detail.specimen_quality
# delete DB class RequestSecondaryDiagnosis & RequestReport
# change all request_report to request_report_view in local.sql
