3.2 - 3.8 - Manually Deleting ProcessMaker Cases

Please rate how useful you found this document:

Contents: [hide]

Overview
Log into MySQL
Remove All Cases from a Workspace
Removing All Cases with a Script
Removing Cases of a Process
Deleting Specific Cases Using SQL
Deleting Cancelled Cases
Automatically Deleting Old Cases
Configuring Automatic Execution

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 delete all ProcessMaker cases while still retaining your process definitions and user lists. This page explains how to enter the MySQL database where ProcessMaker stores cases and remove all the cases from a workspace in ProcessMaker.

Log into MySQL

In Windows, open the Command Prompt (found at Start > All Programs > Accessories > Command Prompt) and navigate to where MySQL is installed and log in. 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 All Cases from a Workspace

Once inside MySQL, view the available databases with the command:

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 that store cases:

TRUNCATE APPLICATION;

TRUNCATE APP_ASSIGN_SELF_SERVICE_VALUE;

TRUNCATE APP_CACHE_VIEW;

TRUNCATE APP_DELAY;

TRUNCATE APP_DELEGATION;

TRUNCATE APP_DOCUMENT;

TRUNCATE APP_EVENT;

TRUNCATE APP_FOLDER;

TRUNCATE APP_HISTORY;

TRUNCATE APP_MESSAGE;

TRUNCATE APP_NOTES;

TRUNCATE APP_OWNER;

TRUNCATE APP_SOLR_QUEUE;

TRUNCATE APP_THREAD;

TRUNCATE LIST_CANCELED;

TRUNCATE LIST_COMPLETED;

TRUNCATE LIST_INBOX;

TRUNCATE LIST_MY_INBOX;

TRUNCATE LIST_PARTICIPATED_HISTORY;

TRUNCATE LIST_PARTICIPATED_LAST;

TRUNCATE LIST_PAUSED;

TRUNCATE LIST_UNASSIGNED;

TRUNCATE LIST_UNASSIGNED_GROUP;

TRUNCATE MESSAGE_APPLICATION;

TRUNCATE SUB_APPLICATION;

DELETE FROM CONTENT WHERE CON_CATEGORY LIKE 'APP_%';

DELETE FROM SEQUENCES WHERE SEQ_NAME = 'APP_NUMBER';

Reset the APP_SEQUENCE table, these operation helps to reset the Case number and Web Entries:

UPDATE APP_SEQUENCE SET ID = 0;

Exit MySQL:

quit;

After using the SQL code, go to the path where ProcessMaker is installed:

LINUX/UNIX

cd /opt/processmaker

Windows

cd INSTALL-DIRECTORY\processmaker

Finally, run the migrate-new-cases-lists ProcessMaker command to complete the entire process:

LINUX/UNIX

./processmaker migrate-new-cases-lists

Windows

PHP-DIRECTORY\php.exe -f processmaker migrate-new-cases-lists

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

Removing All Cases with a Script

To remove all cases from a workspace with a script, create a file named "removeCases.sql" that contains the above TRUNCATE and DELETE commands. 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.

To periodically remove all cases, this script can be executed as a cron job in Linux/UNIX or as a Scheduled Task in Windows.

Removing Cases of a Process

Note: As of version 3.0.1.8, a new feature has been added that allows the user to delete all cases of a process from the Designer menu.

Once inside MySQL, view the available databases:

SHOW DATABASES;

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

USE wf_workflow;

Find the ID of the process whose cases will be removed from the workspace in the bpmn_project table. To obtain the ID of the process using the process name, execute the following query:

SELECT PRJ_UID

FROM bpmn_project

WHERE PRJ_NAME =  'Process Name'

Now, delete all the content in the tables that store the cases of the process specified (replace the 32 x's with the process ID obtained before):

DELETE FROM CONTENT WHERE CON_ID IN (SELECT APP_UID FROM APPLICATION WHERE PRO_UID='xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');

DELETE FROM APP_DOCUMENT WHERE APP_UID IN (SELECT APP_UID FROM APPLICATION WHERE PRO_UID='xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');

DELETE FROM APP_EVENT WHERE APP_UID IN (SELECT APP_UID FROM APPLICATION WHERE PRO_UID='xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');

DELETE FROM APP_MESSAGE WHERE APP_UID IN (SELECT APP_UID FROM APPLICATION WHERE PRO_UID='xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');

DELETE FROM APP_OWNER WHERE APP_UID IN (SELECT APP_UID FROM APPLICATION WHERE PRO_UID='xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');

DELETE FROM APP_THREAD WHERE APP_UID IN (SELECT APP_UID FROM APPLICATION WHERE PRO_UID='xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');

DELETE FROM SUB_APPLICATION WHERE APP_PARENT IN (SELECT APP_UID FROM APPLICATION WHERE PRO_UID='xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');

DELETE FROM APP_DELAY WHERE PRO_UID IN (SELECT APP_UID FROM APPLICATION WHERE PRO_UID='xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');

DELETE FROM APP_DELEGATION WHERE APP_UID IN (SELECT APP_UID FROM APPLICATION WHERE PRO_UID='xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');

DELETE FROM APP_CACHE_VIEW WHERE APP_UID IN (SELECT APP_UID FROM APPLICATION WHERE PRO_UID='xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');

DELETE FROM APP_HISTORY WHERE APP_UID IN (SELECT APP_UID FROM APPLICATION WHERE PRO_UID='xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');

DELETE FROM LIST_CANCELED WHERE APP_UID IN (SELECT APP_UID FROM APPLICATION WHERE PRO_UID='xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');

DELETE FROM LIST_COMPLETED WHERE APP_UID IN (SELECT APP_UID FROM APPLICATION WHERE PRO_UID='xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');

DELETE FROM LIST_INBOX WHERE APP_UID IN (SELECT APP_UID FROM APPLICATION WHERE PRO_UID='xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');

DELETE FROM LIST_MY_INBOX WHERE APP_UID IN (SELECT APP_UID FROM APPLICATION WHERE PRO_UID='xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');

DELETE FROM LIST_PARTICIPATED_HISTORY WHERE APP_UID IN (SELECT APP_UID FROM APPLICATION WHERE PRO_UID='xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');

DELETE FROM LIST_PARTICIPATED_LAST WHERE APP_UID IN (SELECT APP_UID FROM APPLICATION WHERE PRO_UID='xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');

DELETE FROM LIST_PAUSED WHERE APP_UID IN (SELECT APP_UID FROM APPLICATION WHERE PRO_UID='xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');

DELETE FROM LIST_UNASSIGNED WHERE APP_UID IN (SELECT APP_UID FROM APPLICATION WHERE PRO_UID='xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');

DELETE FROM APPLICATION WHERE PRO_UID='xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx';

Exit MySQL:

quit;

After using the SQL code, go to the path where ProcessMaker is installed:

LINUX/UNIX

cd /opt/processmaker

Windows

cd INSTALL-DIRECTORY\processmaker

Finally, run the migrate-new-cases-lists ProcessMaker command to complete the entire process:

LINUX/UNIX

./processmaker migrate-new-cases-lists

Windows

PHP-DIRECTORY\php.exe -f processmaker migrate-new-cases-lists

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

Deleting Specific Cases Using SQL

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

DELETE FROM APP_DOCUMENT WHERE APP_DOCUMENT.APP_UID IN (SELECT APP_UID FROM APPLICATION WHERE APPLICATION.APP_NUMBER IN (1, 2, 3));

DELETE FROM APP_EVENT WHERE APP_EVENT.APP_UID IN (SELECT APP_UID FROM APPLICATION WHERE APPLICATION.APP_NUMBER IN (1, 2, 3));

DELETE FROM APP_MESSAGE WHERE APP_MESSAGE.APP_UID IN (SELECT APP_UID FROM APPLICATION WHERE APPLICATION.APP_NUMBER IN (1, 2, 3));

DELETE FROM APP_OWNER WHERE APP_OWNER.APP_UID IN (SELECT APP_UID FROM APPLICATION WHERE APPLICATION.APP_NUMBER IN (1, 2, 3));

DELETE FROM APP_THREAD WHERE APP_THREAD.APP_UID IN (SELECT APP_UID FROM APPLICATION WHERE APPLICATION.APP_NUMBER IN (1, 2, 3));

DELETE FROM SUB_APPLICATION WHERE SUB_APPLICATION.APP_UID IN (SELECT APP_UID FROM APPLICATION WHERE APPLICATION.APP_NUMBER IN (1, 2, 3));

DELETE FROM APP_DELAY WHERE APP_DELAY.APP_UID IN (SELECT APP_UID FROM APPLICATION WHERE APPLICATION.APP_NUMBER IN (1, 2, 3));

DELETE FROM APP_DELEGATION WHERE APP_DELEGATION.APP_UID IN (SELECT APP_UID FROM APPLICATION WHERE APPLICATION.APP_NUMBER IN (1, 2, 3));

DELETE FROM APP_CACHE_VIEW WHERE APP_CACHE_VIEW.APP_UID IN (SELECT APP_UID FROM APPLICATION WHERE APPLICATION.APP_NUMBER IN (1, 2, 3));

DELETE FROM APP_HISTORY WHERE APP_HISTORY.APP_UID IN (SELECT APP_UID FROM APPLICATION WHERE APPLICATION.APP_NUMBER IN (1, 2, 3));

DELETE FROM APPLICATION WHERE APP_NUMBER IN (1, 2, 3);

Exit MySQL:

quit;

After running the SQL commands, go to the path where ProcessMaker is installed:

LINUX/UNIX

cd /opt/processmaker

Windows

cd INSTALL-DIRECTORY\processmaker

Finally, run the migrate-new-cases-lists ProcessMaker command to complete the entire process:

LINUX/UNIX

./processmaker migrate-new-cases-lists

Windows

PHP-DIRECTORY\php.exe -f processmaker migrate-new-cases-lists

Deleting Cancelled Cases

Use the following SQL DELETE statements to remove canceled 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 APT FROM APP_THREAD APT JOIN APPLICATION A ON A.APP_UID = APT.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';

Automatically Deleting Old Cases

Having 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 from 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 "2016-02-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\php\php.exe" -f "C:\deleteOldCases.php"

Configuring 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 executes the /root/deleteOldCases.php script once a day at midnight:

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

In Windows, the following command also executes 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\php\php.exe' -f 'C:\deleteOldCases.php'"