Please rate how useful you found this document: 
Average: 2.3 (3 votes)

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:

cd "C:\Program Files\ProcessMaker\mysql\bin" mysql -u root -p Enter the root password

In Linux or UNIX, open a terminal and enter:

mysql -u root -p Enter the root password

Remove Cases from a Workspace

Once inside MySQL, examine the available databases:

SHOW DATABASES;

Switch to the wf_<workspace> database. The default workspace is "workflow", which is probably what you are using unless you created additional workspaces.

USE wf_workflow;

Delete all the content in the tables which store cases:

TRUNCATE TABLE APPLICATION;
 TRUNCATE TABLE APP_DELAY;
 TRUNCATE TABLE APP_DELEGATION;
 TRUNCATE TABLE APP_DOCUMENT;
 TRUNCATE TABLE APP_MESSAGE;
 TRUNCATE TABLE APP_OWNER;
 TRUNCATE TABLE APP_THREAD;
 TRUNCATE TABLE SUB_APPLICATION;
 TRUNCATE TABLE APP_EVENT;
 TRUNCATE TABLE APP_CACHE_VIEW;     --(If using PM 1.8 and later)
 TRUNCATE TABLE APP_HISTORY;        --(If using PM 1.8 and later)
 TRUNCATE TABLE APP_FOLDER;         --(If using PM 1.8 and later)
 TRUNCATE TABLE SEQUENCES;          --(if using PM 2.5 and later)
 

Exit MySQL:

quit;

Now when you login into ProcessMaker, all the cases should be gone.

Remove all cases

Use the following query if you want to remove all cases from your ProcessMaker installation.

TRUNCATE TABLE APPLICATION;
 TRUNCATE TABLE APP_DELAY;
 TRUNCATE TABLE APP_DELEGATION;
 TRUNCATE TABLE APP_DOCUMENT;
 TRUNCATE TABLE APP_MESSAGE;
 TRUNCATE TABLE APP_OWNER;
 TRUNCATE TABLE APP_THREAD;
 TRUNCATE TABLE SUB_APPLICATION;
 TRUNCATE TABLE APP_EVENT;
 TRUNCATE TABLE APP_CACHE_VIEW;     --(If using PM 1.8 and later)
 TRUNCATE TABLE APP_HISTORY;        --(If using PM 1.8 and later)
 TRUNCATE TABLE APP_FOLDER;         --(If using PM 1.8 and later)
 TRUNCATE TABLE SEQUENCES;          --(if using PM 2.5 and later)  
 DELETE FROM CONTENT WHERE CON_CATEGORY LIKE 'APP_%';
 

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:

DELETE FROM CONTENT WHERE CON_ID IN (SELECT APP_UID FROM APPLICATION WHERE PRO_UID='XXXXXXXXXXXXXXX');
DELETE FROM APP_DOCUMENT WHERE APP_UID IN (SELECT APP_UID FROM APPLICATION WHERE PRO_UID='XXXXXXXXXXXXXXX');
DELETE FROM APP_EVENT WHERE APP_UID IN (SELECT APP_UID FROM APPLICATION WHERE PRO_UID='XXXXXXXXXXXXXXX');
DELETE FROM APP_MESSAGE WHERE APP_UID IN (SELECT APP_UID FROM APPLICATION WHERE PRO_UID='XXXXXXXXXXXXXXX');
DELETE FROM APP_OWNER WHERE APP_UID IN (SELECT APP_UID FROM APPLICATION WHERE PRO_UID='XXXXXXXXXXXXXXX');
DELETE FROM APP_THREAD WHERE APP_UID IN (SELECT APP_UID FROM APPLICATION WHERE PRO_UID='XXXXXXXXXXXXXXX');
DELETE FROM SUB_APPLICATION WHERE APP_PARENT IN (SELECT APP_UID FROM APPLICATION WHERE PRO_UID='XXXXXXXXXXXXXXX');  
DELETE FROM APP_DELAY WHERE PRO_UID IN (SELECT APP_UID FROM APPLICATION WHERE PRO_UID='XXXXXXXXXXXXXXX');
DELETE FROM APP_DELEGATION WHERE APP_UID IN (SELECT APP_UID FROM APPLICATION WHERE PRO_UID='XXXXXXXXXXXXXXX');
DELETE FROM APP_CACHE_VIEW WHERE APP_UID IN (SELECT APP_UID FROM APPLICATION WHERE PRO_UID='XXXXXXXXXXXXXXX');
DELETE FROM APP_HISTORY WHERE APP_UID IN (SELECT APP_UID FROM APPLICATION WHERE PRO_UID='XXXXXXXXXXXXXXX');
DELETE FROM APPLICATION WHERE PRO_UID='XXXXXXXXXXXXXXX';

Removing All Cases with a Trigger

To remove all cases of a Process create a Trigger and copy the following line:

executeQuery("DELETE FROM APPLICATION WHERE PRO_UID = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'");

Where XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX is the unique ID of the process.

Deleting Specific Cases Using SQL

Use the SQL code below to remove specific cases based upon their case number. This example deletes cases whose case numbers are 1, 2, and 3.

DELETE FROM APP_DOCUMENT USING APP_DOCUMENT INNER JOIN APPLICATION WHERE APPLICATION.APP_NUMBER IN (1, 2, 3) AND APPLICATION.APP_UID = APP_DOCUMENT.APP_UID;
DELETE FROM APP_EVENT USING APP_EVENT INNER JOIN APPLICATION WHERE APPLICATION.APP_NUMBER IN (1, 2, 3) AND APPLICATION.APP_UID = APP_EVENT.APP_UID;
DELETE FROM APP_MESSAGE USING APP_MESSAGE INNER JOIN APPLICATION WHERE APPLICATION.APP_NUMBER IN (1, 2, 3) AND APPLICATION.APP_UID = APP_MESSAGE.APP_UID;
DELETE FROM APP_OWNER USING APP_OWNER INNER JOIN APPLICATION WHERE APPLICATION.APP_NUMBER IN (1, 2, 3) AND APPLICATION.APP_UID = APP_OWNER.APP_UID;
DELETE FROM APP_THREAD USING APP_THREAD INNER JOIN APPLICATION WHERE APPLICATION.APP_NUMBER IN (1, 2, 3) AND APPLICATION.APP_UID = APP_THREAD.APP_UID;
DELETE FROM SUB_APPLICATION USING SUB_APPLICATION INNER JOIN APPLICATION WHERE APPLICATION.APP_NUMBER IN (1, 2, 3) AND APPLICATION.APP_UID = SUB_APPLICATION.APP_UID;
DELETE FROM APP_DELAY USING APP_DELAY INNER JOIN APPLICATION WHERE APPLICATION.APP_NUMBER IN (1, 2, 3) AND APPLICATION.APP_UID = APP_DELAY.APP_UID;
DELETE FROM APP_DELEGATION USING APP_DELEGATION INNER JOIN APPLICATION WHERE APPLICATION.APP_NUMBER IN (1, 2, 3) AND APPLICATION.APP_UID = APP_DELEGATION.APP_UID;
DELETE FROM APP_CACHE_VIEW USING APP_CACHE_VIEW INNER JOIN APPLICATION WHERE APPLICATION.APP_NUMBER IN (1, 2, 3) AND APPLICATION.APP_UID = APP_CACHE_VIEW.APP_UID;
DELETE FROM APP_HISTORY USING APP_HISTORY INNER JOIN APPLICATION WHERE APPLICATION.APP_NUMBER IN (1, 2, 3) AND APPLICATION.APP_UID = APP_HISTORY.APP_UID;
DELETE FROM APPLICATION WHERE APP_NUMBER IN (1, 2, 3);

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 ($cases as $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'") or
      die("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:

"C:\Program Files\ProcessMaker-2_0_37\php\php.exe" -f "C:\Documents and Settings\Bob\My Documents\deleteOldCases.php"

Configuring for Automatic Execution

Once sure that the script works, set it to execute periodically as a cron job in Linux/UNIX or as a Scheduled Task in Windows.

Examples:

In Linux/UNIX, the following line in /etc/crontab would execute the /root/deleteOldCases.php script once a day at midnight:

* 0 * * * root php -f /root/deleteOldCases.php

In Windows, the following command would also execute the C:\Documents and Settings\Bob\My Documents\deleteOldCases.php script once a day at midnight:

SCHTASKS /CREATE /SC DAILY /ST 12:00 /TN delete_old_cases ^ /TR "'C:\Program Files\ProcessMaker-2_0_37\php\php.exe' -f 'C:\Documents and Settings\Bob\My Documents\deleteOldCases.php'"