Clean Patient Data

Overview

This script is designed to clean up an OpenMRS 3.x database before go-live by removing patient demographic data, clinical data, and resetting various database elements. It's specifically prepared for the Mosul Go-Live (April, 2025).

Script Components

1. Safety Measures

• Temporarily disables foreign key checks to allow deletion operations
• Creates error-handling procedures to safely delete from tables without crashing on errors

2. Data Removal Operations

The script systematically removes data from the following categories:

• Patient demographic data (person, patient, identifiers, etc.)
• Clinical data (observations, encounters, visits, etc.)
• Appointment-related data
• Drug and medication information
• Allergy records
• Program enrollment data
• User data
• System logs and queues

3. Database Reset Operations

• Resets auto-increment counters for key tables
• Resets patient identifier sequence

4. Cleanup

• Removes temporary procedures created during execution
• Re-enables foreign key checks

Key Procedures

1. safe_delete: Handles errors during table deletion operations
2. safe_reset_auto_increment: Safely resets auto-increment counters
3. safe_reset_sequence: Resets the patient identifier sequence

Execution Instructions

To execute this script against an OpenMRS database running in Docker:

cat -v reset_db.sql | docker exec -i ozone-msf-mosul-mysql-1 /usr/bin/mysql -u openmrs --password='password' openmrs -v

Command Breakdown:

• cat -v cleanup.sql: Outputs the SQL script content, with the -v flag showing non-printing characters
• |: Pipes the script content to the next command
• docker exec -i ozone-msf-mosul-mysql-1: Executes a command inside the running MySQL Docker container
• /usr/bin/mysql: The MySQL client command
• -u openmrs: Specifies the MySQL username
• --password='password': Specifies the MySQL password
• openmrs: Specifies the target database
• -v: Enables verbose mode in MySQL to show detailed execution output

Important Notes

1. Backup Recommendation: Always create a database backup before running this script
2. Irreversible: The data deletion is permanent and cannot be undone
3. Purpose: This script is intended for cleaning test data before going live, not for production use
4. Customization: The script may need adjustments based on specific OpenMRS configurations and modules
5. User Removal: This script will remove all users from the system. You will need to run a separate user creation script after cleanup to add necessary user accounts back into the system.

After Execution

After running the script, the OpenMRS database will:

• Contain no patient or clinical data
• Have reset counters for new patient registrations
• Retain all system configurations and metadata
• Require new user accounts to be created

Contact Person

Joshua Nsereko (Madiro)