Overview

The data stored in ProcessMaker cases can be quite sensitive, since it often contains confidential information about an organization's operations and its clients. To prevent misuse of confidential information, you may want remove all the ProcessMaker cases, while still retaining your process definitions and user lists. This procedure explains how to enter the MySQL database where ProcessMaker stores cases and remove all the cases from a workspace in ProcessMaker.

Login into MySQL

In Windows open the Command Prompt (found at Start > All Programs > Accessories > Command Prompt) and navigate to where MySQL is installed and login. For example:

Note: Be careful of this query since deleting information from CONTENT table may delete cases and tasks labels.

Removing All Cases with a Script

To remove all cases with a script, create a file with the above TRUNCATE and DELETE commands named "removeCases.sql". Then use the following command from the command line to execute the script:

mysql -u root -p password -D database < removeCases.sql

Where password is the password of the "root" user and database is the name of the wf_<workspace> database.

If needing to periodically remove all cases, this script could be executed as a cron job in Linux/UNIX or as a Scheduled Task in Windows.

Remove all Cases for a Process

To remove all the cases for a specific process requires using SQL DELETE statements which only remove entries related to that process's cases.

First, look up the unique ID for the process by going to ADMIN > Web Services before version 2.0 or ADMIN > Settings > Web Services Test in version 2.0 or later. Click on the [Test] link for the Login function and enter the username and password for the "admin" or another user with the PM_FACTORY permission in his/her role. After logging in, click on the [Test] link for theProcessList function to see a list of processes and their UIDs.

Another way to find the process UID is to look it up with the following query in the wf_<workspace> database:

SELECT CON_ID FROM CONTENT WHERE CON_CATEGORY='PRO_TITLE' AND CON_VALUE='PROCESS-TITLE';

(Replace PROCESS-TITLE with the title of the process. Remember that the query is case sensitive.)

Then use the process UID in the following SQL DELETE statements to remove all cases for a particular process:

Deleting Cancelled Cases

Use the following SQL DELETE statements to remove cancelled cases from the database:

DELETE C FROM CONTENT C JOIN APPLICATION A ON A.PRO_UID = C.CON_ID WHERE A.APP_STATUS ='CANCELLED';DELETE AD FROM APP_DOCUMENT AD JOIN APPLICATION A ON A.APP_UID = AD.APP_UID WHERE A.APP_STATUS ='CANCELLED';DELETE AE FROM APP_EVENT AE JOIN APPLICATION A ON A.APP_UID = AE.APP_UID WHERE A.APP_STATUS ='CANCELLED';DELETE AM FROM APP_MESSAGE AM JOIN APPLICATION A ON A.APP_UID = AM.APP_UID WHERE A.APP_STATUS ='CANCELLED';DELETE AO FROM APP_OWNER AO JOIN APPLICATION A ON A.APP_UID = AO.APP_UID WHERE A.APP_STATUS ='CANCELLED';DELETE AT FROM APP_THREAD AT JOIN APPLICATION A ON A.APP_UID = AT.APP_UID WHERE A.APP_STATUS ='CANCELLED';DELETE SA FROM SUB_APPLICATION SA JOIN APPLICATION A ON A.PRO_UID = SA.APP_PARENT WHERE A.APP_STATUS ='CANCELLED';DELETE AH FROM APP_HISTORY AH JOIN APPLICATION A ON A.APP_UID = AH.APP_UID WHERE A.APP_STATUS ='CANCELLED';DELETE ADEL FROM APP_DELEGATION ADEL JOIN APPLICATION A ON A.APP_UID = ADEL.APP_UID WHERE A.APP_STATUS ='CANCELLED';DELETE APP_DELAY FROM APP_DELAY WHERE APP_STATUS ='CANCELLED';DELETE APP_CACHE_VIEW FROM APP_CACHE_VIEW WHERE APP_STATUS ='CANCELLED';DELETE APPLICATION FROM APPLICATION WHERE APP_STATUS ='CANCELLED';

The above SQL statements can either be executed inside a trigger with the executeQuery() function or from inside MySQL (or a graphical interface for MySQL such as PhpMyAdmin).

Automatically Deleting Old Cases

A large number of cases in the database slows down ProcessMaker. If there is no need to keep the records of old cases, a script can be created to delete old cases in the database.

Create a file named "deleteOldCases.php" and paste the following code into it:

<?php$url="localhost:3306";//set to address and port number of MySQL server$db="wf_workflow";//set to wf_<WORKSPACE> database for your workspace$user="root";//set to user who has access to database$pass="p4ssw0rd";//set to user's password$deleteDate="-30 days";//set to a string understood by strtotime()

$sDelDate=date("Y-m-d H:i:s",strtotime($deleteDate));$cases=array();//array to hold cases$conn=mysql_connect($url,$user,$pass) or die("Error connecting to MySQL at $url.\n");mysql_select_db($db) or die("Error selecting database $db.\n");#select all cases whose status is set to "COMPLETED" or "CANCELED" and are older than the $deleteDate$query="SELECT APP_NUMBER, APP_UID FROM APPLICATION WHERE (APP_STATUS='COMPLETED' OR
APP_STATUS='CANCELED') AND APP_FINISH_DATE < '$sDelDate'";$result=mysql_query($query) or die("Error: Unable to query database.\n\tQuery:\n\t$query\n");$record=mysql_fetch_array($result, MYSQL_ASSOC);while($record!==false){$cases[$record['APP_NUMBER']]=$record['APP_UID'];$record=mysql_fetch_array($result, MYSQL_ASSOC);}$cnt=0;foreach($casesas$caseNo=>$caseId){mysql_query("DELETE FROM APP_DOCUMENT WHERE APP_UID='$caseId'");mysql_query("DELETE FROM APP_EVENT WHERE APP_UID='$caseId'");mysql_query("DELETE FROM APP_MESSAGE WHERE APP_UID='$caseId'");mysql_query("DELETE FROM APP_OWNER WHERE APP_UID='$caseId'");mysql_query("DELETE FROM APP_THREAD WHERE APP_UID='$caseId'");mysql_query("DELETE FROM SUB_APPLICATION WHERE APP_UID='$caseId'");mysql_query("DELETE FROM APP_DELAY WHERE APP_UID='$caseId'");mysql_query("DELETE FROM APP_DELEGATION WHERE APP_UID='$caseId'");mysql_query("DELETE FROM APP_CACHE_VIEW WHERE APP_UID='$caseId'");mysql_query("DELETE FROM APP_HISTORY WHERE APP_UID='$caseId'");mysql_query("DELETE FROM APPLICATION WHERE APP_UID='$caseId'") ordie("Error deleting case $caseId.\n");print"Deleted case No: $caseNo, ID: $caseId\n";$cnt++;}print"Deleted $cnt cases.\n";?>

Change the values of the $url, $db, $user and $pass variables to match your system. Set $deleteDate to a value understood by the strtotime() function, such as "-60 days","-2 months -15 days -10 hours" or "2010-12-31 12:30:00".

The script can be tested by executing it from the command line:

php -f deleteOldCases.php

In Windows, it may be necessary to include the full path to php.exe, since it isn't found in the $PATH. Enclose paths with spaces inside "" (double quotation marks) or '' (single quotation marks). For example: