MySQL stored procedure to upgrade tribe

I wrote a MySQL stored procedure to upgrade the existing patient tribe values to a person attribute type.  This method is not going to be used, so it will not be checked in. So I am putting it here so that somebody may get some use out of it.

— define the procedure
DELIMITER $$

DROP PROCEDURE IF EXISTS `openmrs`.`convert_tribe`$$
CREATE PROCEDURE `openmrs`.`convert_tribe` ()
BEGIN
DECLARE currentAutoCommit BOOLEAN;

DECLARE tribeTypeId INT(11); — tribe person attribute type id

— fetched values of patients with tribe are stored in the following variables
DECLARE patientId INT(11);
DECLARE tribeId INT(11);

DECLARE noMoreRows INT(11) DEFAULT 0;

DECLARE patientsWithTribe CURSOR FOR
SELECT patient_id, tribe FROM patient WHERE tribe IS NOT NULL;

DECLARE CONTINUE HANDLER FOR NOT FOUND
BEGIN
SET noMoreRows = 1;
END;

— disable auto commit
SELECT @@autocommit INTO currentAutoCommit;
SET autocommit = 0;

START TRANSACTION;

— create the tribe person attribute type
INSERT INTO person_attribute_type
(name, description, format, creator, date_created)
VALUES (‘Tribe’, ‘Tribe of the person’, ‘org.openmrs.module.tribe.Tribe’, 1, NOW());
— get the created tribe person attribute type id
SELECT person_attribute_type_id into tribeTypeId FROM person_attribute_type WHERE name = ‘Tribe’;

— read patient tribe values and add new person attributes
OPEN patientsWithTribe;
cursorLoop : LOOP
FETCH patientsWithTribe INTO patientId, tribeId;
IF noMoreRows = 1 THEN
LEAVE cursorLoop;
END IF;

INSERT INTO person_attribute (person_id, value, person_attribute_type_id, creator, date_created)
VALUES (patientId, tribeId, tribeTypeId, 1, now());
END LOOP cursorLoop;

CLOSE patientsWithTribe;

— remove tribe column
ALTER TABLE patient DROP FOREIGN KEY belongs_to_tribe;
ALTER TABLE patient DROP COLUMN tribe;

— commit everything and restore auto commit value
COMMIT;
SET autocommit = currentAutoCommit;
END$$

DELIMITER ;

— run the procedure
CALL convert_tribe;

— drop the procedure
DROP PROCEDURE convert_tribe;

Explore posts in the same categories: OpenMRS

Tags: MySQL, SQL, stored procedure

You can comment below, or link to this permanent URL from your own site.