3.0 - 3.1 - Manually Deleting ProcessMaker Cases

Please rate how useful you found this document:

Contents: [hide]

Overview
Login 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 for 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 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 all Cases from a Workspace

Once inside MySQL, examine 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 which 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';

UPDATE USERS SET USR_TOTAL_INBOX = 0, USR_TOTAL_DRAFT = 0, USR_TOTAL_CANCELLED = 0, USR_TOTAL_PARTICIPATED = 0, USR_TOTAL_PAUSED = 0, USR_TOTAL_COMPLETED = 0, USR_TOTAL_UNASSIGNED = 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 login 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 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.

Removing Cases of a Process

Note: From version 3.0.1.8 on, a new feature has been added to delete all cases of a process from the Designer menu.

Once inside MySQL, examine 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;

Check the ID of the process whose cases will be removed in the workspace in the bpmn_project table. To obtain the ID of the process by name execute the following query:

SELECT PRJ_UID

FROM bpmn_project

WHERE PRJ_NAME =  'Process Name'

Now, delete all the content in the tables which store cases of the process specified (replace the 32 x's by 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 of this query since deleting information from CONTENT table may delete cases and tasks labels.

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);

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 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';

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 "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 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\php\php.exe' -f 'C:\deleteOldCases.php'"