#-------------------------------------- # USE: # The diffs are ordered by datamodel version number. #-------------------------------------- DROP PROCEDURE IF EXISTS update_user_password; DROP PROCEDURE IF EXISTS insert_patient_stub; DROP PROCEDURE IF EXISTS insert_user_stub; #---------------------------------------- # OpenMRS Datamodel version 1.1.10 # Ben Wolfe May 31st 2007 # Adding township_division, region, and # subregion attributes to patient_address # and location tables #---------------------------------------- DROP PROCEDURE IF EXISTS diff_procedure; delimiter // CREATE PROCEDURE diff_procedure (IN new_db_version VARCHAR(10)) BEGIN IF (SELECT REPLACE(property_value, '.', '0') < REPLACE(new_db_version, '.', '0') FROM global_property WHERE property = 'database_version') THEN SELECT CONCAT('Updating to ', new_db_version) AS 'Datamodel Update:' FROM dual; ALTER TABLE `person_address` ADD COLUMN `region` varchar(50) default NULL; ALTER TABLE `person_address` ADD COLUMN `subregion` varchar(50) default NULL; ALTER TABLE `person_address` ADD COLUMN `township_division` varchar(50) default NULL; ALTER TABLE `location` ADD COLUMN `region` varchar(50) default NULL; ALTER TABLE `location` ADD COLUMN `subregion` varchar(50) default NULL; ALTER TABLE `location` ADD COLUMN `township_division` varchar(50) default NULL; UPDATE `global_property` SET property_value=new_db_version WHERE property = 'database_version'; END IF; END; // delimiter ; call diff_procedure('1.1.10'); #---------------------------------------- # OpenMRS Datamodel version 1.1.11 # Ben Wolfe Dec 21st 2007 # Removing the unneeded auto increment values # on patient_id and user_id. #---------------------------------------- DROP PROCEDURE IF EXISTS diff_procedure; delimiter // CREATE PROCEDURE diff_procedure (IN new_db_version VARCHAR(10)) BEGIN IF (SELECT REPLACE(property_value, '.', '0') < REPLACE(new_db_version, '.', '0') FROM global_property WHERE property = 'database_version') THEN SELECT CONCAT('Updating to ', new_db_version) AS 'Datamodel Update:' FROM dual; ALTER TABLE `patient` MODIFY COLUMN `patient_id` int(11) NOT NULL; ALTER TABLE `users` MODIFY COLUMN `user_id` int(11) NOT NULL; UPDATE `global_property` SET property_value=new_db_version WHERE property = 'database_version'; END IF; END; // delimiter ; call diff_procedure('1.1.11'); #---------------------------------------- # OpenMRS Datamodel version 1.1.12 # Ben Wolfe Dec 27th 2007 # Adding report_schema_xml table #---------------------------------------- DROP PROCEDURE IF EXISTS diff_procedure; delimiter // CREATE PROCEDURE diff_procedure (IN new_db_version VARCHAR(10)) BEGIN IF (SELECT REPLACE(property_value, '.', '0') < REPLACE(new_db_version, '.', '0') FROM global_property WHERE property = 'database_version') THEN SELECT CONCAT('Updating to ', new_db_version) AS 'Datamodel Update:' FROM dual; CREATE TABLE `report_schema_xml` ( `report_schema_id` int(11) NOT NULL auto_increment, `name` varchar(255) NOT NULL, `description` text NOT NULL, `xml_data` text NOT NULL, PRIMARY KEY (`report_schema_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; UPDATE `global_property` SET property_value=new_db_version WHERE property = 'database_version'; END IF; END; // delimiter ; call diff_procedure('1.1.12'); #--------------------------------------------------------------------------------- #-- OpenMRS 1.2 ------------------------------------------------------ #--------------------------------------------------------------------------------- #--------------------------------------- # Update OpenMRS to 1.2.0 #--------------------------------------- DROP PROCEDURE IF EXISTS diff_procedure; delimiter // CREATE PROCEDURE diff_procedure (IN new_db_version VARCHAR(10)) BEGIN IF (SELECT REPLACE(property_value, '.', '0') < REPLACE(new_db_version, '.', '0') FROM global_property WHERE property = 'database_version') THEN SELECT CONCAT('Updating to ', new_db_version) AS 'Datamodel Update:' FROM dual; UPDATE `global_property` SET property_value=new_db_version WHERE property = 'database_version'; END IF; END; // delimiter ; call diff_procedure('1.2.0'); DROP PROCEDURE IF EXISTS diff_procedure; #---------------------------------------- # OpenMRS Datamodel version 1.2.01 # Ben Wolfe Feb 20th 2008 # Adding obs grouping rows to obs table and foreign # keying obs.obs_group_id to obs.obs_id #---------------------------------------- DROP PROCEDURE IF EXISTS diff_procedure; delimiter // CREATE PROCEDURE diff_procedure (IN new_db_version VARCHAR(10)) BEGIN IF (SELECT REPLACE(property_value, '.', '0') < REPLACE(new_db_version, '.', '0') FROM global_property WHERE property = 'database_version') THEN SELECT CONCAT('Updating to ', new_db_version) AS 'Datamodel Update:' FROM dual; SELECT 'Adding index to obs_group_id. **This could take a while on large databases.**\n\n Cancelling during this step and restarting later will not harm anything.' AS 'Current step (1/8):' FROM dual; ALTER TABLE `obs` ADD INDEX `obs_grouping_id` (`obs_group_id`); -- temporary concept id to use if we can't guess the right obs grouper concept SET @OTHER_CONCEPT_ID = (SELECT `concept_id` FROM `concept_name` where name = 'MEDICAL RECORD OBSERVATIONS' LIMIT 1); -- This creates rows in obs for parent obs groupers. It temporarily gives the obs grouping rows an obs_group_id -- Tries to use the concept_set concept that the obs is in if there is only one. If there is more than one, take from form_field parent SELECT 'Creating rows in obs table representing obs groups from current data' as 'Current step (2/8):' FROM dual; INSERT INTO `obs` (obs_group_id, concept_id, person_id, obs_datetime, encounter_id, location_id, creator, date_created) SELECT obs_group_id, COALESCE( (SELECT concept_set FROM concept_set WHERE concept_id = obs.concept_id GROUP BY concept_id HAVING count(*) = 1 ), (SELECT COALESCE(f2.concept_id, @OTHER_CONCEPT_ID ) as 'concept_id' FROM `field` f, `field` f2, `form_field` ff, `form_field` ff2, `form`, `encounter` WHERE encounter.encounter_id = obs.encounter_id AND encounter.form_id = form.form_id AND form.form_id = ff.form_id AND ff.field_id = f.field_id AND f.concept_id = obs.concept_id AND ff.parent_form_field = ff2.form_field_id AND ff2.field_id = f2.field_id LIMIT 1 ) ) as 'concept_id', person_id, obs_datetime, encounter_id, location_id, creator, date_created FROM `obs` WHERE obs_group_id IS NOT null GROUP BY obs_group_id; -- This creates a temp table mapping from grouper obs to its obs_group_id SELECT 'Fixing obs_group_id values on all obs that were grouped' as 'Current step: (3/8)' FROM dual; DROP TABLE IF EXISTS `new_obs_groups_mapping`; CREATE TABLE `new_obs_groups_mapping` ( `grouper_obs_id` int(11) NOT NULL, `obs_group_id` int(11) default NULL, PRIMARY KEY (`grouper_obs_id`), KEY `grouper` (`grouper_obs_id`), KEY `the_group_id` (`obs_group_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- This populates the previous table INSERT INTO `new_obs_groups_mapping` (grouper_obs_id, obs_group_id) SELECT max(obs_id) as grouper_obs_id, obs_group_id FROM `obs` o2 WHERE o2.obs_group_id IS NOT NULL GROUP BY o2.obs_group_id; -- This changes the obs_group_ids on the obs table to point at the obs_id of the grouper obs SELECT 'Applying temporary table values to obs table' as 'Current step: (3.5/8)' FROM dual; UPDATE `obs` o left join new_obs_groups_mapping mapping on o.obs_group_id = mapping.obs_group_id SET o.obs_group_id = mapping.grouper_obs_id WHERE o.obs_group_id IS NOT NULL; -- Get rid of our temp table DROP TABLE IF EXISTS new_obs_groups_mapping; -- Remove the obs_group_id from the obs groupers SELECT 'Fixing newly added obs grouper rows that were temporarily given an obs_group_id' as 'Current step: (4/8)' FROM dual; UPDATE `obs` o SET obs_group_id = null WHERE obs_group_id IS NOT NULL AND obs_group_id = obs_id; -- Sanity check...we shouldn't really have any obs groupers with concept_id = MEDICAL RECORD OBSERVATIONS IF (SELECT COUNT(*)<>'0' FROM obs o WHERE concept_id = @OTHER_CONCEPT_ID AND EXISTS (SELECT * FROM obs o2 WHERE o2.obs_group_id = o.obs_id)) THEN SELECT 'These obs rows pertaining to obs_groups have the been given a generic concept_id. You should find and correct with their right grouping concept_id' AS '########## WARNING! #############' FROM DUAL; SELECT * FROM obs WHERE concept_id = @OTHER_CONCEPT_ID; END IF; -- remove all bad obs grouping by setting obs_group_id to null for any obs in a solitary group and its grouper concept is not a set SELECT 'Cleaning up the obs that think they are in an obs_group but really are not.' AS 'Current step (5/8):' FROM dual; DROP TABLE IF EXISTS `single_member_obs_groups`; CREATE TABLE `single_member_obs_groups` ( `obs_id` int(11) NOT NULL, `obs_group_id` int(11) NOT NULL, PRIMARY KEY (`obs_id`), KEY `the_group_id` (`obs_group_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `single_member_obs_groups` (obs_id, obs_group_id) SELECT obs.obs_id, obs.obs_group_id FROM obs join obs obsparent on obs.obs_group_id = obsparent.obs_id join concept c on obsparent.concept_id = c.concept_id WHERE c.is_set = 0 GROUP BY obs.obs_group_id HAVING count(obs.obs_group_id) = 1; -- The actual ungrouping of the singluarly grouped obs rows UPDATE obs SET obs_group_id = null WHERE obs_id in (select obs_id from single_member_obs_groups); -- delete the obs grouper that was added for all those poser obs groups DELETE FROM obs WHERE obs_id IN (select distinct obs_group_id from `single_member_obs_groups`); -- cleanup the temp table. DROP TABLE IF EXISTS `single_member_obs_groups`; -- obs_group_id should be restricted to obs_ids SELECT 'Adding foreign key constraint from obs.obs_group_id to obs.obs_id' as 'Current step: (6/8)' FROM dual; ALTER TABLE `obs` ADD CONSTRAINT `obs_grouping_id` FOREIGN KEY (`obs_group_id`) REFERENCES `obs` (`obs_id`); -- set the voided bit on obs rows that are obs groupings if all children obs are voided SELECT 'Voiding those obs groupers that are in all-voided groups' as 'Current step: (7/8)' FROM dual; -- create a temp table to hold the obs_id of obs groupers that need to be voided DROP TABLE IF EXISTS `obs_groupers_needing_voided`; CREATE TABLE `obs_groupers_needing_voided` ( `obs_id` int(11) NOT NULL, `voided_by` int(11) default NULL, `date_voided` datetime default NULL, `void_reason` varchar(255) default NULL, PRIMARY KEY (`obs_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- This populates the previous table INSERT INTO `obs_groupers_needing_voided` (obs_id, voided_by, date_voided, void_reason) SELECT obs_group_id, voided_by, date_voided, void_reason FROM obs WHERE obs_group_id is NOT NULL AND voided = 1 GROUP BY obs_group_id, voided HAVING count(*) = (SELECT count(*) FROM obs o2 WHERE o2.obs_group_id = obs.obs_group_id); -- doing the actual change in the obs table by voiding any rows that have all voided groupedobs UPDATE obs o1 join obs_groupers_needing_voided tovoid on o1.obs_id = tovoid.obs_id SET o1.voided = 1, o1.voided_by = tovoid.voided_by, o1.date_voided = tovoid.date_voided, o1.void_reason = tovoid.void_reason; DROP TABLE IF EXISTS `obs_groupers_needing_voided`; SELECT 'Updating database version global property' as 'Current step (8/8):' FROM dual; UPDATE `global_property` SET property_value=new_db_version WHERE property = 'database_version'; END IF; END; // delimiter ; call diff_procedure('1.2.01'); #---------------------------------------- # OpenMRS Datamodel version 1.2.02 # Burke Mamlin April 6, 2008 # Adding concept_map.concept_id as FK back # to concept (ticket #216) #---------------------------------------- DROP PROCEDURE IF EXISTS diff_procedure; delimiter // CREATE PROCEDURE diff_procedure (IN new_db_version VARCHAR(10)) BEGIN IF (SELECT REPLACE(property_value, '.', '0') < REPLACE(new_db_version, '.', '0') FROM global_property WHERE property = 'database_version') THEN SELECT CONCAT('Updating to ', new_db_version) AS 'Datamodel Update:' FROM dual; ALTER TABLE `concept_map` ADD COLUMN `concept_id` int(11) NOT null default '0'; ALTER TABLE `concept_map` ADD CONSTRAINT `map_for_concept` FOREIGN KEY (`concept_id`) REFERENCES `concept` (`concept_id`); UPDATE `global_property` SET property_value=new_db_version WHERE property = 'database_version'; END IF; END; // delimiter ; call diff_procedure('1.2.02'); #---------------------------------------- # OpenMRS Datamodel version 1.2.03 # Justin Miranda April 11, 2008 # Made scheduler task start time nullable # (ticket #667). #---------------------------------------- DROP PROCEDURE IF EXISTS diff_procedure; delimiter // CREATE PROCEDURE diff_procedure (IN new_db_version VARCHAR(10)) BEGIN IF (SELECT REPLACE(property_value, '.', '0') < REPLACE(new_db_version, '.', '0') FROM global_property WHERE property = 'database_version') THEN SELECT CONCAT('Updating to ', new_db_version) AS 'Datamodel Update:' FROM dual; ALTER TABLE `scheduler_task_config` MODIFY COLUMN `start_time` DATETIME default NULL; UPDATE `global_property` SET property_value=new_db_version WHERE property = 'database_version'; END IF; END; // delimiter ; call diff_procedure('1.2.03'); #---------------------------------------- # OpenMRS Datamodel version 1.2.04 # Burke Mamlin April 14, 2008 # Upgrade default from XSLT to 1.9.5 #---------------------------------------- DROP PROCEDURE IF EXISTS diff_procedure; delimiter // CREATE PROCEDURE diff_procedure (IN new_db_version VARCHAR(10)) BEGIN IF (SELECT REPLACE(property_value, '.', '0') < REPLACE(new_db_version, '.', '0') FROM global_property WHERE property = 'database_version') THEN SELECT CONCAT('Updating to ', new_db_version) AS 'Datamodel Update:' FROM dual; -- Upgrade all instances of XSLT 1.9.4 to 1.9.5 UPDATE `form` SET xslt = '\r\n\r\n\r\n\r\n\r\n \r\n\r\nFORMENTRY\r\nAMRS.ELD\r\nHL7LISTENER\r\nAMRS.ELD\r\n \r\n \r\nAMRS.ELD.FORMID \r\n\r\n\r\n \r\n\r\n\r\n\r\n\r\n \r\n MSH|^~\\& \r\n | \r\n \r\n | \r\n \r\n | \r\n \r\n | \r\n \r\n | \r\n \r\n \r\n \r\n | \r\n |ORU^R01 \r\n | \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n |P \r\n |2.5 \r\n |1 \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n \r\n ^\r\n \r\n \r\n\r\n \r\n PID \r\n | \r\n | \r\n | \r\n \r\n \r\n \r\n \r\n \r\n 0\">\r\n ~\r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n 0\">\r\n ~\r\n \r\n \r\n \r\n \r\n \r\n \r\n | \r\n \r\n | \r\n \r\n ^ \r\n \r\n ^ \r\n \r\n | \r\n | \r\n \r\n \r\n \r\n \r\n PV1 \r\n | \r\n |O \r\n | \r\n \r\n | \r\n | \r\n | \r\n | \r\n \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n \r\n \r\n \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n \r\n \r\n \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n |V \r\n \r\n\r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n ORC \r\n |RE \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n \r\n \r\n \r\n | \r\n \r\n \r\n\r\n \r\n \r\n \r\n \r\n \r\n OBR \r\n | \r\n 1\r\n | \r\n | \r\n | \r\n \r\n \r\n\r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n OBR \r\n | \r\n \r\n | \r\n | \r\n | \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n\r\n \r\n \r\n \r\n \r\n \r\n OBR \r\n | \r\n \r\n | \r\n | \r\n | \r\n \r\n \r\n\r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n OBR \r\n | \r\n \r\n | \r\n | \r\n | \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n\r\n\r\n\r\n\r\n \r\n \r\n \r\n \r\n ^ \r\n ^ \r\n ^ \r\n \r\n ^ \r\n \r\n\r\n\r\n\r\n\r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n OBX \r\n | \r\n \r\n | \r\n \r\n \r\n NM\r\n \r\n \r\n \r\n \r\n \r\n | \r\n \r\n | \r\n \r\n | \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n 0\r\n 1\r\n \r\n \r\n \r\n \r\n \r\n \r\n | \r\n \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n\r\n\r\n\r\n\r\n \r\n \r\n \r\n \r\n\r\n\r\n\r\n\r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n\r\n\r\n\r\n\r\n \r\n \r\n \r\n \r\n\r\n\r\n' WHERE xslt = '\r\n\r\n\r\n\r\n\r\n \r\n\r\nFORMENTRY\r\nAMRS.ELD\r\nHL7LISTENER\r\nAMRS.ELD\r\n \r\n \r\nAMRS.ELD.FORMID \r\n\r\n\r\n \r\n\r\n\r\n\r\n\r\n \r\n MSH|^~\\& \r\n | \r\n \r\n | \r\n \r\n | \r\n \r\n | \r\n \r\n | \r\n \r\n \r\n \r\n | \r\n |ORU^R01 \r\n | \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n |P \r\n |2.5 \r\n |1 \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n \r\n ^\r\n \r\n \r\n\r\n \r\n PID \r\n | \r\n | \r\n | \r\n \r\n \r\n \r\n \r\n \r\n 0\">\r\n ~\r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n 0\">\r\n ~\r\n \r\n \r\n \r\n \r\n \r\n \r\n | \r\n \r\n | \r\n \r\n ^ \r\n \r\n ^ \r\n \r\n | \r\n | \r\n \r\n \r\n \r\n \r\n PV1 \r\n | \r\n |O \r\n | \r\n \r\n | \r\n | \r\n | \r\n | \r\n \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n \r\n \r\n \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n \r\n \r\n \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n |V \r\n \r\n\r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n ORC \r\n |RE \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n \r\n \r\n \r\n | \r\n \r\n \r\n\r\n \r\n \r\n \r\n \r\n OBR \r\n | \r\n 1\r\n | \r\n | \r\n | \r\n \r\n \r\n\r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n OBR \r\n | \r\n \r\n | \r\n | \r\n | \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n\r\n \r\n \r\n \r\n \r\n \r\n OBR \r\n | \r\n \r\n | \r\n | \r\n | \r\n \r\n \r\n\r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n OBR \r\n | \r\n \r\n | \r\n | \r\n | \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n\r\n\r\n\r\n\r\n \r\n \r\n \r\n \r\n ^ \r\n ^ \r\n ^ \r\n \r\n ^ \r\n \r\n\r\n\r\n\r\n\r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n OBX \r\n | \r\n \r\n | \r\n \r\n \r\n NM\r\n \r\n \r\n \r\n \r\n \r\n | \r\n \r\n | \r\n \r\n | \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n 0\r\n 1\r\n \r\n \r\n \r\n \r\n \r\n \r\n | \r\n \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n\r\n\r\n\r\n\r\n \r\n \r\n \r\n \r\n\r\n\r\n\r\n\r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n\r\n\r\n\r\n\r\n \r\n \r\n \r\n \r\n\r\n\r\n'; UPDATE `global_property` SET property_value=new_db_version WHERE property = 'database_version'; END IF; END; // delimiter ; call diff_procedure('1.2.04'); #---------------------------------------- # OpenMRS Datamodel version 1.2.05 # Ben Wolfe Dec 27th 2007 # Adding report_schema_xml table #---------------------------------------- DROP PROCEDURE IF EXISTS diff_procedure; delimiter // CREATE PROCEDURE diff_procedure (IN new_db_version VARCHAR(10)) BEGIN IF (SELECT REPLACE(property_value, '.', '0') < REPLACE(new_db_version, '.', '0') FROM global_property WHERE property = 'database_version') THEN SELECT CONCAT('Updating to ', new_db_version) AS 'Datamodel Update:' FROM dual; CREATE TABLE `report_schema_xml` ( `report_schema_id` int(11) NOT NULL auto_increment, `name` varchar(255) NOT NULL, `description` text NOT NULL, `xml_data` text NOT NULL, PRIMARY KEY (`report_schema_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; UPDATE `global_property` SET property_value=new_db_version WHERE property = 'database_version'; END IF; END; // delimiter ; call diff_procedure('1.2.05'); #--------------------------------------- # OpenMRS Datamodel version 1.2.06 # Brian McKown Mar 06 2008 # Alter report_schema table # Modify xml_data to MEDIUMTEXT #------------------------------------- DROP PROCEDURE IF EXISTS diff_procedure; delimiter // CREATE PROCEDURE diff_procedure (IN new_db_version VARCHAR(10)) BEGIN IF (SELECT REPLACE(property_value, '.', '0') < REPLACE(new_db_version, '.', '0') FROM global_property WHERE property = 'database_version') THEN SELECT CONCAT('Updating to ', new_db_version) AS 'Datamodel Update:' FROM dual; ALTER TABLE `report_schema_xml` MODIFY COLUMN `xml_data` MEDIUMTEXT CHARACTER SET utf8 NOT NULL; UPDATE `global_property` SET property_value=new_db_version WHERE property = 'database_version'; END IF; END; // delimiter ; call diff_procedure('1.2.06'); #--------------------------------------- # OpenMRS Datamodel version 1.2.07 # Brian McKown Mar 06 2008 # Alter global_property table # Modify property_value to MEDIUMTEXT #--------------------------------------- DROP PROCEDURE IF EXISTS diff_procedure; delimiter // CREATE PROCEDURE diff_procedure (IN new_db_version VARCHAR(10)) BEGIN IF (SELECT REPLACE(property_value, '.', '0') < REPLACE(new_db_version, '.', '0') FROM global_property WHERE property = 'database_version') THEN SELECT CONCAT('Updating to ', new_db_version) AS 'Datamodel Update:' FROM dual; ALTER TABLE `global_property` MODIFY COLUMN `property_value` MEDIUMTEXT CHARACTER SET utf8 DEFAULT NULL; UPDATE `global_property` SET property_value=new_db_version WHERE property = 'database_version'; END IF; END; // delimiter ; call diff_procedure('1.2.07'); #--------------------------------------- # OpenMRS Datamodel version 1.2.08 # Darius Jazayeri Mar 29 2008 # Drop the REPORT table which has never been used #--------------------------------------- DROP PROCEDURE IF EXISTS diff_procedure; delimiter // CREATE PROCEDURE diff_procedure (IN new_db_version VARCHAR(10)) BEGIN IF (SELECT REPLACE(property_value, '.', '0') < REPLACE(new_db_version, '.', '0') FROM global_property WHERE property = 'database_version') THEN SELECT CONCAT('Updating to ', new_db_version) AS 'Datamodel Update:' FROM dual; DROP TABLE `report`; UPDATE `global_property` SET property_value=new_db_version WHERE property = 'database_version'; END IF; END; // delimiter ; call diff_procedure('1.2.08'); #--------------------------------------------------------------------------------- #-- OpenMRS 1.3.0 ------------------------------------------------------ #--------------------------------------------------------------------------------- #--------------------------------------- # OpenMRS Datamodel version 1.3.0.00 # Ben Wolfe May 9 2008 # Upgrading to the 1.3.0 #--------------------------------------- DROP PROCEDURE IF EXISTS diff_procedure; delimiter // CREATE PROCEDURE diff_procedure (IN new_db_version VARCHAR(10)) BEGIN IF (SELECT REPLACE(property_value, '.', '0') < REPLACE(new_db_version, '.', '0') FROM global_property WHERE property = 'database_version') THEN SELECT CONCAT('Updating to ', new_db_version) AS 'Datamodel Update:' FROM dual; UPDATE `global_property` SET property_value=new_db_version WHERE property = 'database_version'; END IF; END; // delimiter ; call diff_procedure('1.3.0.00'); #--------------------------------------- # OpenMRS Datamodel version 1.3.0.01 # Chase Yarbrough May 19 2008 # Adding infrastructure for pluggable patient identifier validators. #--------------------------------------- DROP PROCEDURE IF EXISTS diff_procedure; delimiter // CREATE PROCEDURE diff_procedure (IN new_db_version VARCHAR(10)) BEGIN IF (SELECT REPLACE(property_value, '.', '0') < REPLACE(new_db_version, '.', '0') FROM global_property WHERE property = 'database_version') THEN SELECT CONCAT('Updating to ', new_db_version) AS 'Datamodel Update:' FROM dual; ALTER TABLE `patient_identifier_type` ADD COLUMN `validator` VARCHAR(200); UPDATE `patient_identifier_type` SET `validator`="org.openmrs.patient.impl.LuhnIdentifierValidator" WHERE check_digit=true; UPDATE `global_property` SET property_value=new_db_version WHERE property = 'database_version'; END IF; END; // delimiter ; call diff_procedure('1.3.0.01'); #---------------------------------------- # OpenMRS Datamodel version 1.3.0.02 # Darius Jazayeri March 13, 2008 # Adding modified* columns to Cohort #---------------------------------------- DROP PROCEDURE IF EXISTS diff_procedure; delimiter // CREATE PROCEDURE diff_procedure (IN new_db_version VARCHAR(10)) BEGIN IF (SELECT REPLACE(property_value, '.', '0') < REPLACE(new_db_version, '.', '0') FROM global_property WHERE property = 'database_version') THEN SELECT CONCAT('Updating to ', new_db_version) AS 'Datamodel Update:' FROM dual; ALTER TABLE `cohort` ADD COLUMN `changed_by` int(11) default NULL; ALTER TABLE `cohort` ADD COLUMN `date_changed` datetime default NULL; ALTER TABLE `cohort` ADD KEY `user_who_changed_cohort` (`changed_by`); ALTER TABLE `cohort` ADD CONSTRAINT `user_who_changed_cohort` FOREIGN KEY (`changed_by`) REFERENCES `users` (`user_id`); UPDATE `global_property` SET property_value=new_db_version WHERE property = 'database_version'; END IF; END; // delimiter ; call diff_procedure('1.3.0.02'); #---------------------------------------- # OpenMRS Datamodel version 1.3.0.03 # Mike Seaton March 31, 2008 # API-Refactoring of Program tables #---------------------------------------- DROP PROCEDURE IF EXISTS diff_procedure; delimiter // CREATE PROCEDURE diff_procedure (IN new_db_version VARCHAR(10)) BEGIN IF (SELECT REPLACE(property_value, '.', '0') < REPLACE(new_db_version, '.', '0') FROM global_property WHERE property = 'database_version') THEN SELECT CONCAT('Updating to ', new_db_version) AS 'Datamodel Update:' FROM dual; ALTER TABLE `program` ADD COLUMN `name` varchar(50); UPDATE program p SET p.name = (SELECT n.name FROM concept_name n WHERE n.concept_id = p.concept_id LIMIT 1); ALTER TABLE `program` MODIFY `name` varchar(50) NOT NULL; ALTER TABLE `program` ADD COLUMN `description` varchar(500); ALTER TABLE `program` CHANGE `voided` `retired` tinyint(1) NOT NULL default '0'; ALTER TABLE `program` DROP FOREIGN KEY `user_who_voided_program`; ALTER TABLE `program` DROP COLUMN `voided_by`; ALTER TABLE `program` DROP COLUMN `date_voided`; ALTER TABLE `program` DROP COLUMN `void_reason`; ALTER TABLE `program_workflow` CHANGE COLUMN `voided` `retired` tinyint(1) NOT NULL default '0'; ALTER TABLE `program_workflow` DROP FOREIGN KEY `workflow_voided_by`; ALTER TABLE `program_workflow` CHANGE COLUMN `voided_by` `changed_by` int(11) default NULL; ALTER TABLE `program_workflow` ADD CONSTRAINT `workflow_changed_by` FOREIGN KEY (`changed_by`) REFERENCES `users` (`user_id`); ALTER TABLE `program_workflow` CHANGE COLUMN `date_voided` `date_changed` datetime default NULL; ALTER TABLE `program_workflow` DROP COLUMN `void_reason`; ALTER TABLE `program_workflow_state` CHANGE COLUMN `voided` `retired` tinyint(1) NOT NULL default '0'; ALTER TABLE `program_workflow_state` DROP FOREIGN KEY `state_voided_by`; ALTER TABLE `program_workflow_state` CHANGE COLUMN `voided_by` `changed_by` int(11) default NULL; ALTER TABLE `program_workflow_state` ADD CONSTRAINT `state_changed_by` FOREIGN KEY (`changed_by`) REFERENCES `users` (`user_id`); ALTER TABLE `program_workflow_state` CHANGE COLUMN `date_voided` `date_changed` datetime default NULL; ALTER TABLE `program_workflow_state` DROP COLUMN `void_reason`; UPDATE `global_property` SET property_value=new_db_version WHERE property = 'database_version'; END IF; END; // delimiter ; call diff_procedure('1.3.0.03'); #---------------------------------------- # OpenMRS Datamodel version 1.3.0.04 # Ben Wolfe April 1st, 2008 # Adding retired* columns to Order Type #---------------------------------------- DROP PROCEDURE IF EXISTS diff_procedure; delimiter // CREATE PROCEDURE diff_procedure (IN new_db_version VARCHAR(10)) BEGIN IF (SELECT REPLACE(property_value, '.', '0') < REPLACE(new_db_version, '.', '0') FROM global_property WHERE property = 'database_version') THEN SELECT CONCAT('Updating to ', new_db_version) AS 'Datamodel Update:' FROM dual; ALTER TABLE `order_type` ADD COLUMN `retired` tinyint(1) NOT NULL default 0; ALTER TABLE `order_type` ADD COLUMN `retired_by` int(11) default NULL; ALTER TABLE `order_type` ADD COLUMN `date_retired` datetime default NULL; ALTER TABLE `order_type` ADD COLUMN `retire_reason` varchar(255) default NULL; ALTER TABLE `order_type` ADD KEY `user_who_retired_order_type` (`retired_by`); ALTER TABLE `order_type` ADD CONSTRAINT `user_who_retired_order_type` FOREIGN KEY (`retired_by`) REFERENCES `users` (`user_id`); ALTER TABLE `order_type` ADD INDEX `retired_status` (`retired`); UPDATE `global_property` SET property_value=new_db_version WHERE property = 'database_version'; END IF; END; // delimiter ; call diff_procedure('1.3.0.04'); #---------------------------------------- # OpenMRS Datamodel version 1.3.0.05 # Brian McKown April 4, 2008 # Adding retired* columns to Encounter Type #---------------------------------------- DROP PROCEDURE IF EXISTS diff_procedure; delimiter // CREATE PROCEDURE diff_procedure (IN new_db_version VARCHAR(10)) BEGIN IF (SELECT REPLACE(property_value, '.', '0') < REPLACE(new_db_version, '.', '0') FROM global_property WHERE property = 'database_version') THEN SELECT CONCAT('Updating to ', new_db_version) AS 'Datamodel Update:' FROM dual; ALTER TABLE `encounter_type` ADD COLUMN `retired` tinyint(1) NOT NULL default 0; ALTER TABLE `encounter_type` ADD COLUMN `retired_by` int(11) default NULL; ALTER TABLE `encounter_type` ADD COLUMN `date_retired` datetime default NULL; ALTER TABLE `encounter_type` ADD KEY `user_who_retired_encounter_type` (`retired_by`); ALTER TABLE `encounter_type` ADD CONSTRAINT `user_who_retired_encounter_type` FOREIGN KEY (`retired_by`) REFERENCES `users` (`user_id`); ALTER TABLE `encounter_type` ADD INDEX `encounter_type_retired_status` (`retired`); UPDATE `global_property` SET property_value=new_db_version WHERE property = 'database_version'; END IF; END; // delimiter ; call diff_procedure('1.3.0.05'); #---------------------------------------- # OpenMRS Datamodel version 1.3.0.06 # Ben Wolfe April 8th, 2008 # Adding retired* columns to PatientIdentifierType #---------------------------------------- DROP PROCEDURE IF EXISTS diff_procedure; delimiter // CREATE PROCEDURE diff_procedure (IN new_db_version VARCHAR(10)) BEGIN IF (SELECT REPLACE(property_value, '.', '0') < REPLACE(new_db_version, '.', '0') FROM global_property WHERE property = 'database_version') THEN SELECT CONCAT('Updating to ', new_db_version) AS 'Datamodel Update:' FROM dual; ALTER TABLE `patient_identifier_type` ADD COLUMN `retired` tinyint(1) NOT NULL default 0; ALTER TABLE `patient_identifier_type` ADD COLUMN `retired_by` int(11) default NULL; ALTER TABLE `patient_identifier_type` ADD COLUMN `date_retired` datetime default NULL; ALTER TABLE `patient_identifier_type` ADD COLUMN `retire_reason` varchar(255) default NULL; ALTER TABLE `patient_identifier_type` ADD KEY `user_who_retired_patient_identifier_type` (`retired_by`); ALTER TABLE `patient_identifier_type` ADD CONSTRAINT `user_who_retired_patient_identifier_type` FOREIGN KEY (`retired_by`) REFERENCES `users` (`user_id`); ALTER TABLE `patient_identifier_type` ADD INDEX `retired_status` (`retired`); UPDATE `global_property` SET property_value=new_db_version WHERE property = 'database_version'; END IF; END; // delimiter ; call diff_procedure('1.3.0.06'); #---------------------------------------- # OpenMRS Datamodel version 1.3.0.07 # Brian McKown April 8, 2008 # Adding retired* columns to Location # Added retire_reason col to EncounterType #---------------------------------------- DROP PROCEDURE IF EXISTS diff_procedure; delimiter // CREATE PROCEDURE diff_procedure (IN new_db_version VARCHAR(10)) BEGIN IF (SELECT REPLACE(property_value, '.', '0') < REPLACE(new_db_version, '.', '0') FROM global_property WHERE property = 'database_version') THEN SELECT CONCAT('Updating to ', new_db_version) AS 'Datamodel Update:' FROM dual; ALTER TABLE `encounter_type` ADD COLUMN `retire_reason` varchar(255) default NULL; ALTER TABLE `encounter_type` DROP INDEX `encounter_type_retired_status`, ADD INDEX `retired_status` (`retired`); ALTER TABLE `location` ADD COLUMN `retired` tinyint(1) NOT NULL default 0; ALTER TABLE `location` ADD COLUMN `retired_by` int(11) default NULL; ALTER TABLE `location` ADD COLUMN `date_retired` datetime default NULL; ALTER TABLE `location` ADD COLUMN `retire_reason` varchar(255) default NULL; ALTER TABLE `location` ADD KEY `user_who_retired_location` (`retired_by`); ALTER TABLE `location` ADD CONSTRAINT `user_who_retired_location` FOREIGN KEY (`retired_by`) REFERENCES `users` (`user_id`); ALTER TABLE `location` ADD INDEX `retired_status` (`retired`); UPDATE `global_property` SET property_value=new_db_version WHERE property = 'database_version'; END IF; END; // delimiter ; call diff_procedure('1.3.0.07'); #---------------------------------------- # OpenMRS Datamodel version 1.3.0.08 # Ben Wolfe May 16th, 2008 # Adding retired* columns to Concept #---------------------------------------- DROP PROCEDURE IF EXISTS diff_procedure; delimiter // CREATE PROCEDURE diff_procedure (IN new_db_version VARCHAR(10)) BEGIN IF (SELECT REPLACE(property_value, '.', '0') < REPLACE(new_db_version, '.', '0') FROM global_property WHERE property = 'database_version') THEN SELECT CONCAT('Updating to ', new_db_version) AS 'Datamodel Update:' FROM dual; ALTER TABLE `concept` ADD COLUMN `retired_by` int(11) default NULL; ALTER TABLE `concept` ADD COLUMN `date_retired` datetime default NULL; ALTER TABLE `concept` ADD COLUMN `retire_reason` varchar(255) default NULL; ALTER TABLE `concept` ADD KEY `user_who_retired_concept` (`retired_by`); ALTER TABLE `concept` ADD CONSTRAINT `user_who_retired_concept` FOREIGN KEY (`retired_by`) REFERENCES `users` (`user_id`); ALTER TABLE `concept_datatype` ADD COLUMN `retired` tinyint(1) NOT NULL default 0; ALTER TABLE `concept_datatype` ADD COLUMN `retired_by` int(11) default NULL; ALTER TABLE `concept_datatype` ADD COLUMN `date_retired` datetime default NULL; ALTER TABLE `concept_datatype` ADD COLUMN `retire_reason` varchar(255) default NULL; ALTER TABLE `concept_datatype` ADD KEY `user_who_retired_concept_datatype` (`retired_by`); ALTER TABLE `concept_datatype` ADD CONSTRAINT `user_who_retired_concept_datatype` FOREIGN KEY (`retired_by`) REFERENCES `users` (`user_id`); ALTER TABLE `concept_datatype` ADD INDEX `concept_datatype_retired_status` (`retired`); ALTER TABLE `concept_class` ADD COLUMN `retired` tinyint(1) NOT NULL default 0; ALTER TABLE `concept_class` ADD COLUMN `retired_by` int(11) default NULL; ALTER TABLE `concept_class` ADD COLUMN `date_retired` datetime default NULL; ALTER TABLE `concept_class` ADD COLUMN `retire_reason` varchar(255) default NULL; ALTER TABLE `concept_class` ADD KEY `user_who_retired_concept_class` (`retired_by`); ALTER TABLE `concept_class` ADD CONSTRAINT `user_who_retired_concept_class` FOREIGN KEY (`retired_by`) REFERENCES `users` (`user_id`); ALTER TABLE `concept_class` ADD INDEX `concept_class_retired_status` (`retired`); ALTER TABLE `drug` CHANGE COLUMN `voided` `retired` tinyint(1) NOT NULL default '0'; ALTER TABLE `drug` DROP FOREIGN KEY `user_who_voided_drug`; ALTER TABLE `drug` CHANGE COLUMN `voided_by` `retired_by` int(11) default NULL; ALTER TABLE `drug` ADD CONSTRAINT `drug_retired_by` FOREIGN KEY (`retired_by`) REFERENCES `users` (`user_id`); ALTER TABLE `drug` CHANGE COLUMN `date_voided` `date_retired` datetime default NULL; ALTER TABLE `drug` CHANGE COLUMN `void_reason` `retire_reason` datetime default NULL; ALTER TABLE `concept_name` ADD COLUMN `concept_name_id` int(11) UNIQUE KEY NOT NULL AUTO_INCREMENT; ALTER TABLE `concept_name` ADD INDEX `unique_concept_name_id` (`concept_id`); ALTER TABLE `concept_name` DROP PRIMARY KEY, ADD PRIMARY KEY (`concept_name_id`); UPDATE `global_property` SET property_value=new_db_version WHERE property = 'database_version'; END IF; END; // delimiter ; call diff_procedure('1.3.0.08'); #---------------------------------------- # OpenMRS Datamodel version 1.3.0.09 # Darius Jazayeri May 4, 2008 # Adding retired column to Field #---------------------------------------- DROP PROCEDURE IF EXISTS diff_procedure; delimiter // CREATE PROCEDURE diff_procedure (IN new_db_version VARCHAR(10)) BEGIN IF (SELECT REPLACE(property_value, '.', '0') < REPLACE(new_db_version, '.', '0') FROM global_property WHERE property = 'database_version') THEN SELECT CONCAT('Updating to ', new_db_version) AS 'Datamodel Update:' FROM dual; ALTER TABLE `field` ADD COLUMN `retired` tinyint(1) NOT NULL default 0; ALTER TABLE `field` ADD COLUMN `retired_by` int(11) default NULL; ALTER TABLE `field` ADD COLUMN `date_retired` datetime default NULL; ALTER TABLE `field` ADD COLUMN `retire_reason` varchar(255) default NULL; ALTER TABLE `field` ADD KEY `user_who_retired_field` (`retired_by`); ALTER TABLE `field` ADD CONSTRAINT `user_who_retired_field` FOREIGN KEY (`retired_by`) REFERENCES `users` (`user_id`); ALTER TABLE `field` ADD INDEX `field_retired_status` (`retired`); UPDATE `global_property` SET property_value=new_db_version WHERE property = 'database_version'; END IF; END; // delimiter ; call diff_procedure('1.3.0.09'); #---------------------------------------- # OpenMRS Datamodel version 1.3.0.10 # Ben Wolfe May 24, 2008 # Adding retired column to PersonAttributeType #---------------------------------------- DROP PROCEDURE IF EXISTS diff_procedure; delimiter // CREATE PROCEDURE diff_procedure (IN new_db_version VARCHAR(10)) BEGIN IF (SELECT REPLACE(property_value, '.', '0') < REPLACE(new_db_version, '.', '0') FROM global_property WHERE property = 'database_version') THEN SELECT CONCAT('Updating to ', new_db_version) AS 'Datamodel Update:' FROM dual; ALTER TABLE `person_attribute_type` ADD COLUMN `retired` tinyint(1) NOT NULL default 0; ALTER TABLE `person_attribute_type` ADD COLUMN `retired_by` int(11) default NULL; ALTER TABLE `person_attribute_type` ADD COLUMN `date_retired` datetime default NULL; ALTER TABLE `person_attribute_type` ADD COLUMN `retire_reason` varchar(255) default NULL; ALTER TABLE `person_attribute_type` ADD KEY `user_who_retired_person_attribute_type` (`retired_by`); ALTER TABLE `person_attribute_type` ADD CONSTRAINT `user_who_retired_person_attribute_type` FOREIGN KEY (`retired_by`) REFERENCES `users` (`user_id`); ALTER TABLE `person_attribute_type` ADD INDEX `person_attribute_type_retired_status` (`retired`); UPDATE `global_property` SET property_value=new_db_version WHERE property = 'database_version'; END IF; END; // delimiter ; call diff_procedure('1.3.0.10'); #---------------------------------------- # OpenMRS Datamodel version 1.3.0.11 # Ben Wolfe May 27, 2008 # Modifying concept_name table for hibernate insert quirk #---------------------------------------- DROP PROCEDURE IF EXISTS diff_procedure; delimiter // CREATE PROCEDURE diff_procedure (IN new_db_version VARCHAR(10)) BEGIN IF (SELECT REPLACE(property_value, '.', '0') < REPLACE(new_db_version, '.', '0') FROM global_property WHERE property = 'database_version') THEN SELECT CONCAT('Updating to ', new_db_version) AS 'Datamodel Update:' FROM dual; ALTER TABLE `concept_name` MODIFY COLUMN `concept_id` int(11) default NULL; UPDATE `global_property` SET property_value=new_db_version WHERE property = 'database_version'; END IF; END; // delimiter ; call diff_procedure('1.3.0.11'); #---------------------------------------- # OpenMRS Datamodel version 1.3.0.12 # Ben Wolfe June 25, 2008 # Giving 1.3 modified privileges to roles #---------------------------------------- DROP PROCEDURE IF EXISTS diff_procedure; delimiter // CREATE PROCEDURE diff_procedure (IN new_db_version VARCHAR(10)) BEGIN IF (SELECT REPLACE(property_value, '.', '0') < REPLACE(new_db_version, '.', '0') FROM global_property WHERE property = 'database_version') THEN SELECT CONCAT('Updating to ', new_db_version) AS 'Datamodel Update:' FROM dual; set FOREIGN_KEY_CHECKS = 0; insert into role_privilege (role, privilege) select distinct role, 'View Patient Programs' from role_privilege rp where privilege = 'Manage Patient Programs' and not exists (select * from role_privilege where role = rp.role and privilege = 'View Patient Programs'); insert into role_privilege (role, privilege) select distinct role, 'Edit Patient Programs' from role_privilege rp where privilege = 'Manage Patient Programs' and not exists (select * from role_privilege where role = rp.role and privilege = 'Edit Patient Programs'); update role_privilege set privilege = 'Delete Patient Programs' where privilege = 'Manage Patient Programs'; update role_privilege set privilege = 'Edit Concepts' where privilege = 'Manage Concepts'; delete from role_privilege where privilege = 'Add Concepts'; update role_privilege set privilege = 'Edit Forms' where privilege = 'Manage Forms'; delete from role_privilege where privilege = 'Add Forms'; set FOREIGN_KEY_CHECKS = 1; UPDATE `global_property` SET property_value=new_db_version WHERE property = 'database_version'; END IF; END; // delimiter ; call diff_procedure('1.3.0.12'); #---------------------------------------- # OpenMRS Datamodel version 1.3.0.13 # Ben Wolfe July 3, 2008 # Changing person.dead and person_attribute_type.searchable to tinyint #---------------------------------------- DROP PROCEDURE IF EXISTS diff_procedure; delimiter // CREATE PROCEDURE diff_procedure (IN new_db_version VARCHAR(10)) BEGIN IF (SELECT REPLACE(property_value, '.', '0') < REPLACE(new_db_version, '.', '0') FROM global_property WHERE property = 'database_version') THEN SELECT CONCAT('Updating to ', new_db_version) AS 'Datamodel Update:' FROM dual; ALTER TABLE person CHANGE COLUMN dead dead tinyint(1) NOT NULL DEFAULT '0'; ALTER TABLE person_attribute_type CHANGE COLUMN searchable searchable tinyint(1) NOT NULL DEFAULT '0'; UPDATE `global_property` SET property_value=new_db_version WHERE property = 'database_version'; END IF; END; // delimiter ; call diff_procedure('1.3.0.13'); #---------------------------------------- # OpenMRS Datamodel version 1.3.0.14 # Ben Wolfe July 24th, 2008 # Giving 1.3 modified privileges to roles #---------------------------------------- #-- temporary procedure to check and add a privilege to the Authenticated role DROP PROCEDURE IF EXISTS insert_authenticated_privilege; delimiter // CREATE PROCEDURE insert_authenticated_privilege (IN priv VARCHAR(50)) BEGIN IF NOT EXISTS (SELECT * FROM role_privilege where role = 'Authenticated' and privilege = priv) THEN insert into role_privilege (role, privilege) values ('Authenticated', priv); END IF; END; // delimiter ; DROP PROCEDURE IF EXISTS diff_procedure; delimiter // CREATE PROCEDURE diff_procedure (IN new_db_version VARCHAR(10)) BEGIN IF (SELECT REPLACE(property_value, '.', '0') < REPLACE(new_db_version, '.', '0') FROM global_property WHERE property = 'database_version') THEN SELECT CONCAT('Updating to ', new_db_version) AS 'Datamodel Update:' FROM dual; set FOREIGN_KEY_CHECKS = 0; insert into role_privilege (role, privilege) select distinct role, 'View Patient Programs' from role_privilege rp where privilege = 'Manage Patient Programs' and not exists (select * from role_privilege where role = rp.role and privilege = 'View Patient Programs'); insert into role_privilege (role, privilege) select distinct role, 'Edit Patient Programs' from role_privilege rp where privilege = 'Manage Patient Programs' and not exists (select * from role_privilege where role = rp.role and privilege = 'Edit Patient Programs'); insert into role_privilege (role, privilege) select distinct role, 'Delete Patient Programs' from role_privilege rp where privilege = 'Manage Patient Programs' and not exists (select * from role_privilege where role = rp.role and privilege = 'Delete Patient Programs'); delete from role_privilege where privilege = 'Manage Patient Programs'; #-- the 1.3.0.12 update didn't do this change correctly update role_privilege set privilege = 'Manage Concepts' where privilege = 'Edit Concepts'; update role_privilege set privilege = 'Manage Forms' where privilege = 'Edit Forms'; #-- The concept proposal privilege changed slightly insert into role_privilege (role, privilege) select distinct role, 'Add Concept Proposals' from role_privilege rp where privilege = 'Add Concept Proposal' and not exists (select * from role_privilege where role = rp.role and privilege = 'Add Concept Proposals'); insert into role_privilege (role, privilege) select distinct role, 'Edit Concept Proposals' from role_privilege rp where privilege = 'Edit Concept Proposal' and not exists (select * from role_privilege where role = rp.role and privilege = 'Edit Concept Proposals'); #-- These objects/methods now require an explicit privilege. Previously they only required authentication call insert_authenticated_privilege('View Encounter Types'); call insert_authenticated_privilege('View Locations'); call insert_authenticated_privilege('View Mime Types'); call insert_authenticated_privilege('View Identifier Types'); call insert_authenticated_privilege('View Concept Classes'); call insert_authenticated_privilege('View Concept Datatypes'); call insert_authenticated_privilege('View Privileges'); call insert_authenticated_privilege('View Roles'); call insert_authenticated_privilege('View Field Types'); call insert_authenticated_privilege('View Order Types'); call insert_authenticated_privilege('View Relationship Types'); call insert_authenticated_privilege('View Global Properties'); call insert_authenticated_privilege('View Person Attribute Types'); call insert_authenticated_privilege('View Relationships'); call insert_authenticated_privilege('View Tribes'); #-- If a role can View Patients...add the new View Patient Identifiers privilege to that role insert into role_privilege (role, privilege) select distinct role, 'View Patient Identifiers' from role_privilege rp where privilege = 'View Patients' and not exists (select * from role_privilege where role = rp.role and privilege = 'View Patient Identifiers'); # THIS IS INCORRECT, BUT DOES NOT GET FIXED UNTIL 1.3.3.0 and 1.4.0.20 #-- Convert Manage Encounter Types insert into role_privilege (role, privilege) select distinct role, 'Add Encounter Types' from role_privilege rp where privilege = 'Manage Encounter Types' and not exists (select * from role_privilege where role = rp.role and privilege = 'Add Encounter Types'); insert into role_privilege (role, privilege) select distinct role, 'Edit Encounter Types' from role_privilege rp where privilege = 'Manage Encounter Types' and not exists (select * from role_privilege where role = rp.role and privilege = 'Edit Encounter Types'); insert into role_privilege (role, privilege) select distinct role, 'Delete Encounter Types' from role_privilege rp where privilege = 'Manage Encounter Types' and not exists (select * from role_privilege where role = rp.role and privilege = 'Delete Encounter Types'); delete from role_privilege where privilege = 'Manage Encounter Types'; #-- Convert Manage Relationships privilege insert into role_privilege (role, privilege) select distinct role, 'Add Relationships' from role_privilege rp where privilege = 'Manage Relationships' and not exists (select * from role_privilege where role = rp.role and privilege = 'Add Relationships'); insert into role_privilege (role, privilege) select distinct role, 'Edit Relationships' from role_privilege rp where privilege = 'Manage Relationships' and not exists (select * from role_privilege where role = rp.role and privilege = 'Edit Relationships'); insert into role_privilege (role, privilege) select distinct role, 'Delete Relationships' from role_privilege rp where privilege = 'Manage Relationships' and not exists (select * from role_privilege where role = rp.role and privilege = 'Delete Relationships'); delete from role_privilege where privilege = 'Manage Relationships'; set FOREIGN_KEY_CHECKS = 1; UPDATE `global_property` SET property_value=new_db_version WHERE property = 'database_version'; END IF; END; // delimiter ; call diff_procedure('1.3.0.14'); DROP PROCEDURE IF EXISTS insert_authenticated_privilege; #---------------------------------------- # OpenMRS Datamodel version 1.3.0.15 # Ben Wolfe Aug 1st, 2008 # Fixing 1.3.0.14 modified authenticated role #---------------------------------------- DROP PROCEDURE IF EXISTS diff_procedure; delimiter // CREATE PROCEDURE diff_procedure (IN new_db_version VARCHAR(10)) BEGIN IF (SELECT REPLACE(property_value, '.', '0') < REPLACE(new_db_version, '.', '0') FROM global_property WHERE property = 'database_version') THEN SELECT CONCAT('Updating to ', new_db_version) AS 'Datamodel Update:' FROM dual; # Fix the incorrect privilege name from the privious update update role_privilege set privilege = 'View Relationship Types' where privilege = 'View RelationshipTypes'; UPDATE `global_property` SET property_value=new_db_version WHERE property = 'database_version'; END IF; END; // delimiter ; call diff_procedure('1.3.0.15'); #---------------------------------------- # OpenMRS Datamodel version 1.3.0.16 # Ben Wolfe Oct 6th, 2008 # Moving hl7_in_error items to hl7_in_queue that # were misprocessed due to a concept proposed bug #---------------------------------------- DROP PROCEDURE IF EXISTS diff_procedure; delimiter // CREATE PROCEDURE diff_procedure (IN new_db_version VARCHAR(10)) BEGIN IF (SELECT REPLACE(property_value, '.', '0') < REPLACE(new_db_version, '.', '0') FROM global_property WHERE property = 'database_version') THEN SELECT CONCAT('Updating to ', new_db_version) AS 'Datamodel Update:' FROM dual; insert into hl7_in_queue (hl7_source, hl7_source_key, hl7_data, date_created) (select 1, hl7_source_key, hl7_data, date_created from hl7_in_error where error_details = 'object references an unsaved transient instance - save the transient instance before flushing: org.openmrs.Encounter; nested exception is org.hibernate.TransientObjectException: object references an unsaved transient instance - save the transient instance before flushing: org.openmrs.Encounter'); delete from hl7_in_error where error_details = 'object references an unsaved transient instance - save the transient instance before flushing: org.openmrs.Encounter; nested exception is org.hibernate.TransientObjectException: object references an unsaved transient instance - save the transient instance before flushing: org.openmrs.Encounter'; UPDATE `global_property` SET property_value=new_db_version WHERE property = 'database_version'; END IF; END; // delimiter ; call diff_procedure('1.3.0.16'); #----------------------------------------------------------- # OpenMRS Datamodel version 1.3.3.0 # Darius Jazayeri Oct 17th, 2008 # # Convert from Add/Edit/Delete Encounter Types to Manage Encounter Types # (this was done incorrectly in 1.3.0.14) # This sqldiff is identical to 1.4.0.20, but is here as a backport #----------------------------------------------------------- DROP PROCEDURE IF EXISTS diff_procedure; delimiter // CREATE PROCEDURE diff_procedure (IN new_db_version VARCHAR(10)) BEGIN IF (SELECT REPLACE(property_value, '.', '0') < REPLACE(new_db_version, '.', '0') FROM global_property WHERE property = 'database_version') THEN #-- Convert Manage Encounter Types insert into role_privilege (role, privilege) select distinct role, 'Manage Encounter Types' from role_privilege rp where privilege = 'Add Encounter Types' and not exists (select * from role_privilege where role = rp.role and privilege = 'Manage Encounter Types'); insert into role_privilege (role, privilege) select distinct role, 'Manage Encounter Types' from role_privilege rp where privilege = 'Edit Encounter Types' and not exists (select * from role_privilege where role = rp.role and privilege = 'Manage Encounter Types'); insert into role_privilege (role, privilege) select distinct role, 'Manage Encounter Types' from role_privilege rp where privilege = 'Delete Encounter Types' and not exists (select * from role_privilege where role = rp.role and privilege = 'Manage Encounter Types'); delete from role_privilege where privilege = 'Add Encounter Types'; delete from role_privilege where privilege = 'Edit Encounter Types'; delete from role_privilege where privilege = 'Delete Encounter Types'; UPDATE `global_property` SET property_value=new_db_version WHERE property = 'database_version'; END IF; END; // delimiter ; call diff_procedure('1.3.3.0'); #---------------------------------------- # OpenMRS Datamodel version 1.4.0.01 # Mike Seaton July 28, 2008 # Adding accession_number to orders #---------------------------------------- DROP PROCEDURE IF EXISTS diff_procedure; delimiter // CREATE PROCEDURE diff_procedure (IN new_db_version VARCHAR(10)) BEGIN IF (SELECT REPLACE(property_value, '.', '0') < REPLACE(new_db_version, '.', '0') FROM global_property WHERE property = 'database_version') THEN SELECT CONCAT('Updating to ', new_db_version) AS 'Datamodel Update:' FROM dual; ALTER TABLE `orders` ADD COLUMN `accession_number` varchar(255) default NULL; UPDATE `global_property` SET property_value=new_db_version WHERE property = 'database_version'; END IF; END; // delimiter ; call diff_procedure('1.4.0.01'); #---------------------------------------- # OpenMRS Datamodel version 1.4.0.02 # Ben Wolfe Sep 2nd, 2008 # Upgrade default from XSLT to 1.9.6 # to enable appending obs to an encounter #---------------------------------------- DROP PROCEDURE IF EXISTS diff_procedure; delimiter // CREATE PROCEDURE diff_procedure (IN new_db_version VARCHAR(10)) BEGIN IF (SELECT REPLACE(property_value, '.', '0') < REPLACE(new_db_version, '.', '0') FROM global_property WHERE property = 'database_version') THEN SELECT CONCAT('Updating to ', new_db_version) AS 'Datamodel Update:' FROM dual; -- Upgrade all instances of XSLT 1.9.5 to 1.9.6 UPDATE `form` SET xslt = '\r\n\r\n\r\n\r\n\r\n \r\n\r\nFORMENTRY\r\nAMRS.ELD\r\nHL7LISTENER\r\nAMRS.ELD\r\n \r\n \r\nAMRS.ELD.FORMID \r\n\r\n\r\n \r\n\r\n\r\n\r\n\r\n \r\n MSH|^~\\& \r\n | \r\n \r\n | \r\n \r\n | \r\n \r\n | \r\n \r\n | \r\n \r\n \r\n \r\n | \r\n |ORU^R01 \r\n | \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n |P \r\n |2.5 \r\n |1 \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n \r\n ^\r\n \r\n \r\n\r\n \r\n PID \r\n | \r\n | \r\n | \r\n \r\n \r\n \r\n \r\n \r\n 0\">\r\n ~\r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n 0\">\r\n ~\r\n \r\n \r\n \r\n \r\n \r\n \r\n | \r\n \r\n | \r\n \r\n ^ \r\n \r\n ^ \r\n \r\n | \r\n | \r\n \r\n \r\n \r\n \r\n PV1 \r\n | \r\n \r\n |O \r\n | \r\n \r\n | \r\n | \r\n | \r\n | \r\n \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n \r\n \r\n \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n \r\n \r\n \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n |V \r\n \r\n\r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n ORC \r\n |RE \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n \r\n \r\n \r\n | \r\n \r\n \r\n\r\n \r\n \r\n \r\n \r\n \r\n OBR \r\n | \r\n 1\r\n | \r\n | \r\n | \r\n \r\n \r\n\r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n OBR \r\n | \r\n \r\n | \r\n | \r\n | \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n\r\n \r\n \r\n \r\n \r\n \r\n OBR \r\n | \r\n \r\n | \r\n | \r\n | \r\n \r\n \r\n\r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n OBR \r\n | \r\n \r\n | \r\n | \r\n | \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n\r\n\r\n\r\n\r\n \r\n \r\n \r\n \r\n ^ \r\n ^ \r\n ^ \r\n \r\n ^ \r\n \r\n\r\n\r\n\r\n\r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n OBX \r\n | \r\n \r\n | \r\n \r\n \r\n NM\r\n \r\n \r\n \r\n \r\n \r\n | \r\n \r\n | \r\n \r\n | \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n 0\r\n 1\r\n \r\n \r\n \r\n \r\n \r\n \r\n | \r\n \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n\r\n\r\n\r\n\r\n \r\n \r\n \r\n \r\n\r\n\r\n\r\n\r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n\r\n\r\n\r\n\r\n \r\n \r\n \r\n \r\n\r\n\r\n' where xslt = '\r\n\r\n\r\n\r\n\r\n \r\n\r\nFORMENTRY\r\nAMRS.ELD\r\nHL7LISTENER\r\nAMRS.ELD\r\n \r\n \r\nAMRS.ELD.FORMID \r\n\r\n\r\n \r\n\r\n\r\n\r\n\r\n \r\n MSH|^~\\& \r\n | \r\n \r\n | \r\n \r\n | \r\n \r\n | \r\n \r\n | \r\n \r\n \r\n \r\n | \r\n |ORU^R01 \r\n | \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n |P \r\n |2.5 \r\n |1 \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n \r\n ^\r\n \r\n \r\n\r\n \r\n PID \r\n | \r\n | \r\n | \r\n \r\n \r\n \r\n \r\n \r\n 0\">\r\n ~\r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n 0\">\r\n ~\r\n \r\n \r\n \r\n \r\n \r\n \r\n | \r\n \r\n | \r\n \r\n ^ \r\n \r\n ^ \r\n \r\n | \r\n | \r\n \r\n \r\n \r\n \r\n PV1 \r\n | \r\n |O \r\n | \r\n \r\n | \r\n | \r\n | \r\n | \r\n \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n \r\n \r\n \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n \r\n \r\n \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n |V \r\n \r\n\r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n ORC \r\n |RE \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n \r\n \r\n \r\n | \r\n \r\n \r\n\r\n \r\n \r\n \r\n \r\n \r\n OBR \r\n | \r\n 1\r\n | \r\n | \r\n | \r\n \r\n \r\n\r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n OBR \r\n | \r\n \r\n | \r\n | \r\n | \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n\r\n \r\n \r\n \r\n \r\n \r\n OBR \r\n | \r\n \r\n | \r\n | \r\n | \r\n \r\n \r\n\r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n OBR \r\n | \r\n \r\n | \r\n | \r\n | \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n\r\n\r\n\r\n\r\n \r\n \r\n \r\n \r\n ^ \r\n ^ \r\n ^ \r\n \r\n ^ \r\n \r\n\r\n\r\n\r\n\r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n OBX \r\n | \r\n \r\n | \r\n \r\n \r\n NM\r\n \r\n \r\n \r\n \r\n \r\n | \r\n \r\n | \r\n \r\n | \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n 0\r\n 1\r\n \r\n \r\n \r\n \r\n \r\n \r\n | \r\n \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n | \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n\r\n\r\n\r\n\r\n \r\n \r\n \r\n \r\n\r\n\r\n\r\n\r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n\r\n\r\n\r\n\r\n \r\n \r\n \r\n \r\n\r\n\r\n'; UPDATE `global_property` SET property_value=new_db_version WHERE property = 'database_version'; END IF; END; // delimiter ; call diff_procedure('1.4.0.02'); #----------------------------------------------------------- # OpenMRS Datamodel version 1.4.0.03 # Andreas Kollegger Sep 26th, 2008 # # create concept description - creates a table to hold # localizable concept descriptions. This does NOT remove # concept_name.description, which is needed for data # migrations. #----------------------------------------------------------- DROP PROCEDURE IF EXISTS diff_procedure; delimiter // CREATE PROCEDURE diff_procedure (IN new_db_version VARCHAR(10)) BEGIN IF (SELECT REPLACE(property_value, '.', '0') < REPLACE(new_db_version, '.', '0') FROM global_property WHERE property = 'database_version') THEN select 'Create table concept_description then migrate' AS '*** Step: ***', new_db_version from dual; CREATE TABLE `concept_description` ( `concept_description_id` int(11) NOT NULL AUTO_INCREMENT, `concept_id` int(11) NOT NULL default '0', `description` text NOT NULL, `locale` varchar(50) NOT NULL default '', `creator` int(11) NOT NULL default '0', `date_created` datetime NOT NULL default '0000-00-00 00:00:00', `changed_by` int(11) default NULL, `date_changed` datetime default NULL, PRIMARY KEY (`concept_description_id`), KEY `concept_being_described` (`concept_id`), KEY `user_who_created_description` (`creator`), KEY `user_who_changed_description` (`changed_by`), CONSTRAINT `description_for_concept` FOREIGN KEY (`concept_id`) REFERENCES `concept` (`concept_id`), CONSTRAINT `user_who_created_description` FOREIGN KEY (`creator`) REFERENCES `users` (`user_id`), CONSTRAINT `user_who_changed_description` FOREIGN KEY (`changed_by`) REFERENCES `users` (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `concept_description` (concept_id, description, locale, creator, date_created) SELECT concept_id, description, locale, creator, date_created from `concept_name` WHERE description<>''; select '***' AS '...done' from dual; UPDATE `global_property` SET property_value=new_db_version WHERE property = 'database_version'; END IF; END; // delimiter ; call diff_procedure('1.4.0.03'); #----------------------------------------------------------- # OpenMRS Datamodel version 1.4.0.04 # Andreas Kollegger Sep 26th, 2008 # # create concept_name_tag table - used to "tag" concept names #----------------------------------------------------------- DROP PROCEDURE IF EXISTS diff_procedure; delimiter // CREATE PROCEDURE diff_procedure (IN new_db_version VARCHAR(10)) BEGIN IF (SELECT REPLACE(property_value, '.', '0') < REPLACE(new_db_version, '.', '0') FROM global_property WHERE property = 'database_version') THEN select 'Create concept_name_tag table' AS '*** Step: ***', new_db_version from dual; CREATE TABLE `concept_name_tag` ( `concept_name_tag_id` int(11) NOT NULL auto_increment, `tag` VARCHAR(50) NOT NULL, `description` text NOT NULL, `creator` int(11) NOT NULL default '0', `date_created` datetime NOT NULL default '0000-00-00 00:00:00', `voided` tinyint(1) NOT NULL default '0', `voided_by` int(11) default NULL, `date_voided` datetime default NULL, `void_reason` varchar(255) default NULL, PRIMARY KEY (`concept_name_tag_id`), KEY `user_who_created_name_tag` (`creator`), KEY `user_who_voided_name_tag` (`voided_by`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; # add some "well-known" tags # # NOTE: common practice also employs a "preferred_<2-letter-country-code>" # to indicate that a name is preferred in a particular country. The locale # of the name is expected to be compatible. For instance the tag "preferred_GB" # could be attached to either the "en_GB" locale or just the "en" locale to # indicate a name which is preferred by english speakers in Great Britain. IF (SELECT COUNT(tag)='0' FROM concept_name_tag) THEN select 'insert well-known concept_name_tag tags' AS '*** Step: ***' from dual; ALTER TABLE `concept_name_tag` MODIFY COLUMN `concept_name_tag_id` int(11) UNIQUE KEY NOT NULL AUTO_INCREMENT; insert into `concept_name_tag` (`tag`, `description`, `creator`, `date_created`) VALUES ('default', 'name to use when nothing else is available', 1, '2007-05-01 00:00:00'), ('short', 'preferred short name for a concept', 1, '2007-05-01 00:00:00'), ('synonym', 'a different word with similar meaning', 1, '2007-05-01 00:00:00'), ('preferred', 'preferred name in English', 1, '2007-05-01 00:00:00'), ('preferred_KE', 'preferred name in Kenya', 1, '2007-06-20'), ('preferred_LS', 'preferred name in Lesotho', 1, '2007-06-20'), ('preferred_MW', 'preferred name in Malawi', 1, '2007-06-20'), ('preferred_RW', 'preferred name in Rwanda', 1, '2007-06-20'), ('preferred_SA', 'preferred name in South Africa', 1, '2007-06-20'), ('preferred_TZ', 'preferred name in Tanzania', 1, '2007-06-20'), ('preferred_UG', 'preferred name in Uganda', 1, '2007-06-20'), ('preferred_UK', 'preferred name in the United Kingdom', 1, '2007-06-20'), ('preferred_US', 'preferred name in the United States', 1, '2007-06-20'), ('preferred_ZM', 'preferred name in Zambia', 1, '2007-06-20'), ('preferred_ZW', 'preferred name in Zimbabwe', 1, '2007-06-20'); END IF; select '***' AS '...done' from dual; UPDATE `global_property` SET property_value=new_db_version WHERE property = 'database_version'; END IF; END; // delimiter ; call diff_procedure('1.4.0.04'); #----------------------------------------------------------- # OpenMRS Datamodel version 1.4.0.05 # Andreas Kollegger Sep 26th, 2008 # # update concept_name - # # add fields to concept_name table, preparing for data migrations. #----------------------------------------------------------- DROP PROCEDURE IF EXISTS diff_procedure; delimiter // CREATE PROCEDURE diff_procedure (IN new_db_version VARCHAR(10)) BEGIN IF (SELECT REPLACE(property_value, '.', '0') < REPLACE(new_db_version, '.', '0') FROM global_property WHERE property = 'database_version') THEN select 'Update concept_name table' AS '*** Step: ***', new_db_version from dual; # ALTER TABLE `concept_name` ADD COLUMN `concept_name_id` int(11) UNIQUE KEY NOT NULL AUTO_INCREMENT; # ALTER TABLE `concept_name` ADD INDEX (`concept_id`); # ALTER TABLE `concept_name` DROP PRIMARY KEY, ADD PRIMARY KEY (`concept_name_id`); ALTER TABLE `concept_name` ADD COLUMN `voided` tinyint(1) NOT NULL default '0'; ALTER TABLE `concept_name` ADD COLUMN `voided_by` int(11) default NULL; ALTER TABLE `concept_name` ADD COLUMN `date_voided` datetime default NULL; ALTER TABLE `concept_name` ADD COLUMN `void_reason` varchar(255) default NULL; ALTER TABLE `concept_name` ADD INDEX user_who_voided_name (`voided_by`); ALTER TABLE `concept_name` ADD CONSTRAINT `user_who_voided_this_name` FOREIGN KEY (`voided_by`) REFERENCES `users` (`user_id`); select '***' AS '...done' from dual; UPDATE `global_property` SET property_value=new_db_version WHERE property = 'database_version'; END IF; END; // delimiter ; call diff_procedure('1.4.0.05'); #----------------------------------------------------------- # OpenMRS Datamodel version 1.4.0.06 # Andreas Kollegger Sep 26th, 2008 # # create name_tag_map - allows many-to-many mapping from # concept_name to concept_name_tag # #----------------------------------------------------------- DROP PROCEDURE IF EXISTS diff_procedure; delimiter // CREATE PROCEDURE diff_procedure (IN new_db_version VARCHAR(10)) BEGIN IF (SELECT REPLACE(property_value, '.', '0') < REPLACE(new_db_version, '.', '0') FROM global_property WHERE property = 'database_version') THEN select 'Create concept_name_tag_map' AS '*** Step: ***', new_db_version from dual; CREATE TABLE `concept_name_tag_map` ( `concept_name_id` int(11) NOT NULL, `concept_name_tag_id` int(11) NOT NULL, KEY `map_name` (`concept_name_id`), KEY `map_name_tag` (`concept_name_tag_id`), CONSTRAINT `mapped_concept_name` FOREIGN KEY (`concept_name_id`) REFERENCES `concept_name` (`concept_name_id`), CONSTRAINT `mapped_concept_name_tag` FOREIGN KEY (`concept_name_tag_id`) REFERENCES `concept_name_tag` (`concept_name_tag_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; select '***' AS '...done' from dual; UPDATE `global_property` SET property_value=new_db_version WHERE property = 'database_version'; END IF; END; // delimiter ; call diff_procedure('1.4.0.06'); #----------------------------------------------------------- # OpenMRS Datamodel version 1.4.0.07 # Andreas Kollegger Sep 26th, 2008 # # tag preferred language - # # Tag existing concept names as preferred in language by # using an unqualified "preferred" tag. #----------------------------------------------------------- DROP PROCEDURE IF EXISTS diff_procedure; delimiter // CREATE PROCEDURE diff_procedure (IN new_db_version VARCHAR(10)) BEGIN IF (SELECT REPLACE(property_value, '.', '0') < REPLACE(new_db_version, '.', '0') FROM global_property WHERE property = 'database_version') THEN select 'Tag existing names as preferred' AS '*** Step: ***', new_db_version from dual; # tag all concept_name entries with a non-null, non-blank locale as "preferred language" SET @NAME_TAG_ID = (SELECT `concept_name_tag_id` FROM `concept_name_tag` where tag='preferred'); INSERT INTO `concept_name_tag_map` (`concept_name_id`, `concept_name_tag_id`) SELECT `concept_name_id`, @NAME_TAG_ID FROM `concept_name` WHERE locale is not null and locale<>''; select '***' AS '...done' from dual; UPDATE `global_property` SET property_value=new_db_version WHERE property = 'database_version'; END IF; END; // delimiter ; call diff_procedure('1.4.0.07'); #----------------------------------------------------------- # OpenMRS Datamodel version 1.4.0.08 # Andreas Kollegger Sep 26th, 2008 # # migrate short names - # # Migrates concept_name.short_name fields to individual # concept_name entries, tagged as short. #----------------------------------------------------------- DROP PROCEDURE IF EXISTS diff_procedure; delimiter // CREATE PROCEDURE diff_procedure (IN new_db_version VARCHAR(10)) BEGIN IF (SELECT REPLACE(property_value, '.', '0') < REPLACE(new_db_version, '.', '0') FROM global_property WHERE property = 'database_version') THEN select 'Migrate concept_name.short_names.' AS '*** Step: ***', new_db_version from dual; SET @NAME_TAG_ID = (SELECT `concept_name_tag_id` FROM `concept_name_tag` where tag='short'); # make new concept_names for the short_names. select 'create concept_names' AS '*** sub-step 1' from dual; INSERT INTO `concept_name` (short_name, description, concept_id, name, locale, creator, date_created) SELECT 'MVP-SHORT', 'deprecated', concept_id, short_name, locale, creator, date_created FROM `concept_name` LEFT JOIN `concept_name_tag_map` cntm ON cntm.concept_name_id=concept_name.concept_name_id WHERE short_name<>'' AND cntm.concept_name_tag_id<>@NAME_TAG_ID; # tag the newly created short_name entries select 'tagging new concept_names' AS 'sub-step 2' from dual; INSERT INTO `concept_name_tag_map` (`concept_name_id`, `concept_name_tag_id`) SELECT `concept_name_id`, @NAME_TAG_ID FROM `concept_name` WHERE `short_name`='MVP-SHORT'; select '***' AS '...done' from dual; UPDATE `global_property` SET property_value=new_db_version WHERE property = 'database_version'; END IF; END; // delimiter ; call diff_procedure('1.4.0.08'); #----------------------------------------------------------- # OpenMRS Datamodel version 1.4.0.09 # Andreas Kollegger Sep 26th, 2008 # # migrate synonyms # # Move concept_synonyms to new concept_name entries, # tagged as 'synonym'. # # NOTE: requires concept_name.short_name column to # exist in order to perform migration. #----------------------------------------------------------- DROP PROCEDURE IF EXISTS diff_procedure; delimiter // CREATE PROCEDURE diff_procedure (IN new_db_version VARCHAR(10)) BEGIN IF (SELECT REPLACE(property_value, '.', '0') < REPLACE(new_db_version, '.', '0') FROM global_property WHERE property = 'database_version') THEN select 'Migrate synonyms.' AS '*** Step: ***', new_db_version from dual; # make new concept_names for the synonym INSERT INTO `concept_name` (short_name, description, concept_id, name, locale, creator, date_created) SELECT 'MVP-SYNONYM', 'deprecated', cs.concept_id, cs.synonym, cs.locale, cs.creator, cs.date_created FROM `concept_synonym` cs INNER JOIN `concept` c on c.concept_id=cs.concept_id WHERE cs.synonym<>''; # tag the newly created synonym entries SET @NAME_TAG_ID = (SELECT `concept_name_tag_id` FROM `concept_name_tag` where tag='synonym'); INSERT INTO `concept_name_tag_map` (`concept_name_id`, `concept_name_tag_id`) SELECT `concept_name_id`, @NAME_TAG_ID FROM `concept_name` WHERE `short_name`='MVP-SYNONYM'; DROP table concept_synonym; select '***' AS '...done' from dual; UPDATE `global_property` SET property_value=new_db_version WHERE property = 'database_version'; END IF; END; // delimiter ; call diff_procedure('1.4.0.09'); #----------------------------------------------------------- # OpenMRS Datamodel version 1.4.0.10 # Andreas Kollegger Sep 26th, 2008 # # drop deprecated - # # drops deprecated tables and columns #----------------------------------------------------------- DROP PROCEDURE IF EXISTS diff_procedure; delimiter // CREATE PROCEDURE diff_procedure (IN new_db_version VARCHAR(10)) BEGIN IF (SELECT REPLACE(property_value, '.', '0') < REPLACE(new_db_version, '.', '0') FROM global_property WHERE property = 'database_version') THEN select 'Drop concept_name.short_name and concept_name.description' AS '*** Step: ***', new_db_version from dual; ALTER TABLE `concept_name` DROP COLUMN `short_name`; ALTER TABLE `concept_name` DROP COLUMN `description`; select '***' AS '...done' from dual; UPDATE `global_property` SET property_value=new_db_version WHERE property = 'database_version'; END IF; END; // delimiter ; call diff_procedure('1.4.0.10'); #----------------------------------------------------------- # OpenMRS Datamodel version 1.4.0.11 # Andreas Kollegger Sep 26th, 2008 # # upgrade concept proposal - # # upgrades concept_proposal table to add localization. #----------------------------------------------------------- DROP PROCEDURE IF EXISTS diff_procedure; delimiter // CREATE PROCEDURE diff_procedure (IN new_db_version VARCHAR(10)) BEGIN IF (SELECT REPLACE(property_value, '.', '0') < REPLACE(new_db_version, '.', '0') FROM global_property WHERE property = 'database_version') THEN select 'add concept_proposal.locale' AS '*** Step: ***', new_db_version from dual; ALTER TABLE `concept_proposal` ADD COLUMN `locale` varchar(50) NOT NULL default ''; select '***' AS '...done' from dual; UPDATE `global_property` SET property_value=new_db_version WHERE property = 'database_version'; END IF; END; // delimiter ; call diff_procedure('1.4.0.11'); #----------------------------------------------------------- # OpenMRS Datamodel version 1.4.0.12 # Andreas Kollegger Sep 26th, 2008 # # create concept_proposal_tag_map - # # Adds a new concept_proposal_tag_map table #----------------------------------------------------------- DROP PROCEDURE IF EXISTS diff_procedure; delimiter // CREATE PROCEDURE diff_procedure (IN new_db_version VARCHAR(10)) BEGIN IF (SELECT REPLACE(property_value, '.', '0') < REPLACE(new_db_version, '.', '0') FROM global_property WHERE property = 'database_version') THEN select 'Creating concept_proposal_tag_map table' AS '*** Step: ***', new_db_version from dual; CREATE TABLE `concept_proposal_tag_map` ( `concept_proposal_id` int(11) NOT NULL, `concept_name_tag_id` int(11) NOT NULL, KEY `map_proposal` (`concept_proposal_id`), KEY `map_name_tag` (`concept_name_tag_id`), CONSTRAINT `mapped_concept_proposal` FOREIGN KEY (`concept_proposal_id`) REFERENCES `concept_proposal` (`concept_proposal_id`), CONSTRAINT `mapped_concept_proposal_tag` FOREIGN KEY (`concept_name_tag_id`) REFERENCES `concept_name_tag` (`concept_name_tag_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; select '***' AS '...done' from dual; UPDATE `global_property` SET property_value=new_db_version WHERE property = 'database_version'; END IF; END; // delimiter ; call diff_procedure('1.4.0.12'); #----------------------------------------------------------- # OpenMRS Datamodel version 1.4.0.13 # Andreas Kollegger Sep 26th, 2008 # # update obs table - # # add concept_name_id to observation answers #----------------------------------------------------------- DROP PROCEDURE IF EXISTS diff_procedure; delimiter // CREATE PROCEDURE diff_procedure (IN new_db_version VARCHAR(10)) BEGIN IF (SELECT REPLACE(property_value, '.', '0') < REPLACE(new_db_version, '.', '0') FROM global_property WHERE property = 'database_version') THEN select 'Updating obs' AS '*** Step: ***', new_db_version from dual; ALTER TABLE `obs` ADD COLUMN `value_coded_name_id` int(11) AFTER `value_coded`; select '***' AS '...done' from dual; UPDATE `global_property` SET property_value=new_db_version WHERE property = 'database_version'; END IF; END; // delimiter ; call diff_procedure('1.4.0.13'); #----------------------------------------------------------- # OpenMRS Datamodel version 1.4.0.14 # Andreas Kollegger Sep 26th, 2008 # # constrain obs table - # # adds foreign key constraints for concept_name additions # #----------------------------------------------------------- DROP PROCEDURE IF EXISTS diff_procedure; delimiter // CREATE PROCEDURE diff_procedure (IN new_db_version VARCHAR(10)) BEGIN IF (SELECT REPLACE(property_value, '.', '0') < REPLACE(new_db_version, '.', '0') FROM global_property WHERE property = 'database_version') THEN select 'Constraining obs' AS '*** Step: ***', new_db_version from dual; ALTER TABLE `obs` ADD CONSTRAINT `obs_name_of_coded_value` FOREIGN KEY (`value_coded_name_id`) REFERENCES `concept_name` (`concept_name_id`); select '***' AS '...done' from dual; UPDATE `global_property` SET property_value=new_db_version WHERE property = 'database_version'; END IF; END; // delimiter ; call diff_procedure('1.4.0.14'); #----------------------------------------------------------- # OpenMRS Datamodel version 1.4.0.15 # Andreas Kollegger Sep 26th, 2008 # # update concept_word table # delete all concept_words - forcing a rebuild # add concept_name columns # #----------------------------------------------------------- DROP PROCEDURE IF EXISTS diff_procedure; delimiter // CREATE PROCEDURE diff_procedure (IN new_db_version VARCHAR(10)) BEGIN IF (SELECT REPLACE(property_value, '.', '0') < REPLACE(new_db_version, '.', '0') FROM global_property WHERE property = 'database_version') THEN select 'Updating concept_word table' AS '*** Step: ***', new_db_version from dual; delete from `concept_word`; ALTER TABLE `concept_word` ADD COLUMN `concept_name_id` INTEGER NOT NULL AFTER `locale`; ALTER TABLE `concept_word` ADD CONSTRAINT `word_for_name` FOREIGN KEY `word_for_name` (`concept_name_id`) REFERENCES `concept_name` (`concept_name_id`); select '***' AS '...done' from dual; UPDATE `global_property` SET property_value=new_db_version WHERE property = 'database_version'; END IF; END; // delimiter ; call diff_procedure('1.4.0.15'); # # update concept source table # DROP PROCEDURE IF EXISTS diff_procedure; delimiter // CREATE PROCEDURE diff_procedure (IN new_db_version VARCHAR(10)) BEGIN IF (SELECT REPLACE(property_value, '.', '0') < REPLACE(new_db_version, '.', '0') FROM global_property WHERE property = 'database_version') THEN select 'Updating concept_source table' AS '*** Step: ***', new_db_version from dual; # Truncating data here?? First drop column and then re-add it maybe? -bwolfe (TODO) #ALTER TABLE `concept_source` CHANGE COLUMN `voided` `voided` tinyint(1) NOT NULL default 0; ALTER TABLE `concept_source` ADD INDEX `unique_hl7_code` (`hl7_code`, `voided`); select '***' AS '...done' from dual; select 'Updating concept_map table' AS '*** Step: ***' from dual; ALTER TABLE `concept_map` DROP COLUMN `source_id`; ALTER TABLE `concept_map` ADD COLUMN `source_code` varchar(255) default NULL AFTER `source`; # ALTER TABLE `concept_map` ADD COLUMN `concept_id` int(11) NOT NULL AFTER `concept_map_id`; # ALTER TABLE `concept_map` ADD CONSTRAINT `concept_using_this_mapping` FOREIGN KEY (`concept_id`) REFERENCES `concept` (`concept_id`); select '***' AS '...done' from dual; UPDATE `global_property` SET property_value=new_db_version WHERE property = 'database_version'; END IF; END; // delimiter ; call diff_procedure('1.4.0.16'); # # update concept_name_tag to be auto increment table # DROP PROCEDURE IF EXISTS diff_procedure; delimiter // CREATE PROCEDURE diff_procedure (IN new_db_version VARCHAR(10)) BEGIN IF (SELECT REPLACE(property_value, '.', '0') < REPLACE(new_db_version, '.', '0') FROM global_property WHERE property = 'database_version') THEN ALTER TABLE `concept_name_tag` MODIFY COLUMN `concept_name_tag_id` int(11) UNIQUE KEY NOT NULL AUTO_INCREMENT; UPDATE `global_property` SET property_value=new_db_version WHERE property = 'database_version'; END IF; END; // delimiter ; call diff_procedure('1.4.0.17'); #----------------------------------------------------------- # OpenMRS Datamodel version 1.4.0.18 # Andreas Kollegger Oct 16th, 2008 # # drop concept_name_id from obs #----------------------------------------------------------- DROP PROCEDURE IF EXISTS diff_procedure; delimiter // CREATE PROCEDURE diff_procedure (IN new_db_version VARCHAR(10)) BEGIN IF (SELECT REPLACE(property_value, '.', '0') < REPLACE(new_db_version, '.', '0') FROM global_property WHERE property = 'database_version') THEN # update the db version no matter what happens so that this procedure is skipped during the next run UPDATE `global_property` SET property_value=new_db_version WHERE property = 'database_version'; select 'Dropping concept_name_id from obs table (this may fail, which is ok. If it does fail, run this file again.)' AS '*** Step: ***', new_db_version from dual; ALTER TABLE `obs` DROP FOREIGN KEY `concept_name_id`; ALTER TABLE `obs` DROP COLUMN `concept_name_id`; select '***' AS '...done' from dual; END IF; END; // delimiter ; call diff_procedure('1.4.0.18'); #----------------------------------------------------------- # OpenMRS Datamodel version 1.4.0.19 # Andreas Kollegger Oct 16th, 2008 # # a dummy update to ensure that the db version bumps up # after the previous call possibly failed #----------------------------------------------------------- DROP PROCEDURE IF EXISTS diff_procedure; delimiter // CREATE PROCEDURE diff_procedure (IN new_db_version VARCHAR(10)) BEGIN IF (SELECT REPLACE(property_value, '.', '0') < REPLACE(new_db_version, '.', '0') FROM global_property WHERE property = 'database_version') THEN UPDATE `global_property` SET property_value=new_db_version WHERE property = 'database_version'; END IF; END; // delimiter ; call diff_procedure('1.4.0.19'); #----------------------------------------------------------- # OpenMRS Datamodel version 1.4.0.20 # Darius Jazayeri Oct 17th, 2008 # # Convert from Add/Edit/Delete Encounter Types to Manage Encounter Types # (this was done incorrectly in 1.3.0.14) # This is identical to 1.3.3.0 which is there as a backport #----------------------------------------------------------- DROP PROCEDURE IF EXISTS diff_procedure; delimiter // CREATE PROCEDURE diff_procedure (IN new_db_version VARCHAR(10)) BEGIN IF (SELECT REPLACE(property_value, '.', '0') < REPLACE(new_db_version, '.', '0') FROM global_property WHERE property = 'database_version') THEN #-- Convert Manage Encounter Types insert into role_privilege (role, privilege) select distinct role, 'Manage Encounter Types' from role_privilege rp where privilege = 'Add Encounter Types' and not exists (select * from role_privilege where role = rp.role and privilege = 'Manage Encounter Types'); insert into role_privilege (role, privilege) select distinct role, 'Manage Encounter Types' from role_privilege rp where privilege = 'Edit Encounter Types' and not exists (select * from role_privilege where role = rp.role and privilege = 'Manage Encounter Types'); insert into role_privilege (role, privilege) select distinct role, 'Manage Encounter Types' from role_privilege rp where privilege = 'Delete Encounter Types' and not exists (select * from role_privilege where role = rp.role and privilege = 'Manage Encounter Types'); delete from role_privilege where privilege = 'Add Encounter Types'; delete from role_privilege where privilege = 'Edit Encounter Types'; delete from role_privilege where privilege = 'Delete Encounter Types'; UPDATE `global_property` SET property_value=new_db_version WHERE property = 'database_version'; END IF; END; // delimiter ; call diff_procedure('1.4.0.20'); #----------------------------------- # Clean up - Keep this section at the very bottom of diff script #----------------------------------- DROP PROCEDURE IF EXISTS diff_procedure;