Overview
Displaying lists of cases under the HOME menu can be slow when there are a large number of cases in the database, because ProcessMaker has query five different tables to display a list of cases. In order to speed up the display of case lists, ProcessMaker version 2.0 has added the wf_<workspace>.APP_CACHE_VIEW table to the database to contain all the information needed when querying case lists. In rare circumstances, the data contained in the wf_<workspace>.APP_CACHE_VIEW table can become out of sync with the information found in the APPLICATION, APP_DELEGATION, APP_THREAD, USERS and CONTENT tables. The Case List Case Builder can be used to rebuild the APP_CACHE_VIEW table from information found in these tables.
The Case List Case Builder also provides an option to change the location of the MySQL database and its user, who is used to setup new workspaces in ProcessMaker.
Rebuilding the Cases List Cache
If the information displayed in the list of cases under the HOME menu is not correct or is out of date, the information in the wf_<workspace>.APP_CACHE_VIEW table in the database has probably gotten out of sync and needs to be rebuilt. To rebuild the table, login to ProcessMaker as the "admin" user (or any user who has the PM_SETUP_ADVANCE permission in his/her role). Then, go to HOME > Settings > Cases List Cache Builder.
To rebuild the contents of the wf_<workspace>.APP_CACHE_VIEW table, click on the Build Cache button. When the table has finished being rebuilt, a message box will appear:
Structure of the APP_CACHE_VIEW table
The wf_<workspace>.APP_CACHE_VIEW table provides handy information about cases, which can be useful to consult in SQL queries.
Structure of the APP_CACHE_VIEW table:
| Field Name | Type | Source Table | Description |
|---|---|---|---|
| APP_UID | varchar(32) | APP_DELEGATION | The unique ID of the case |
| DEL_INDEX | int(11) | APP_DELEGATION | The delegation index of the case. This index is a sequential value that is generated for each task as it is worked on in the case. Default value: 0 |
| APP_NUMBER | int(11) | APPLICATION | The sequential integer identifying the case. Default value: 0 |
| APP_UID | varchar(32) | APPLICATION | The current status of the case. The possible values for this field are: "DRAFT", "TO_DO", "COMPLETED", "PAUSED" and "CANCELLED". |
| USR_UID | varchar(32) | APP_DELEGATION | The unique ID of the user who was last assigned to work on a task in the case. Note that if the case has parallel tasks or open tasks in multiple threads, then this field has the user for the last task to be assigned to a user. |
| TAS_UID | varchar(32) | APP_DELEGATION | The unique ID of the current task for the case. Note that if the case has parallel tasks or open tasks in multiple threads, then this field has the last task to be created for the case. |
| PRO_UID | varchar(32) | APP_DELEGATION | The unique ID of the process which is being run by the case. |
| DEL_DELEGATE_DATE | datetime | APP_DELEGATION | The date and time when a user was assigned to the current task for the case. Note that if the case has parallel tasks or open tasks in multiple threads, then this field has the date and time when the most recent task was assigned to a user. |
| DEL_TASK_DUE_DATE | datetime | APP_DELEGATION | The date and time when the current task is due (scheduled to be completed), which is based upon the task properties and could also be based upon a calendar. Note that if the case has parallel tasks or open tasks in multiple threads, then this field has the due date for the most recent task to be created in the case. |
| DEL_FINISH_DATE | datetime | APP_DELEGATION | The date and time when the current task or most recent task was completed. If the task hasn't yet been completed then this field is set to NULL. Note that if the case has parallel tasks or open tasks in multiple threads, then this field has the finish date for the most recent task to be created in the case. |
| DEL_THREAD_STATUS | varchar(32) | APP_DELEGATION | The state of the current task in the case. The possible values for this field are: "OPEN" and "CLOSED". Default Value: "OPEN" |
| APP_THREAD_STATUS | varchar(32) | APP_THREAD | The thread status of the case, indicating if the thread is opened or closed. Threads are used to identify different branches in the process map. The possible values for this field are: "OPEN" and "CLOSED". |
| APP_TITLE | varchar(255) | CONTENT (according to the current system language) | The current title of the case, which by default is "#case-number", but it can be configured in the task properties. |
| APP_TAS_TITLE | varchar(255) | CONTENT (according to the current system language) | The title of the current task (or most recent task) in the case. |
| APP_CURRENT_USER | varchar(128) | +USR_FIRSTNAMEfields from USERStable | The full name of the user assigned to the current task (or most recent task) in the case. |
| APP_DEL_PREVIOUS_USER | varchar(128) | +USR_FIRSTNAMEfields from USERStable | The full name of the user assigned to the previous task in the case. |
| DEL_PRIORITY | varchar(32) | APP_DELEGATION | The numeric priority given to the current task (or most recent task) in the case. Possible values: 1 (very low), 2 (low), 3 (normal), 4 (high), 5 (very high). Default value: 3 |
| DEL_DURATION | double | APP_DELEGATION | The time which has elapsed since the current (or most recent) task was first opened until it was finished. |
| DEL_QUEUE_DURATION | double | APP_DELEGATION | The time that passed between the delegation (assignment) of the current task (or most recent task) to a user and when the case was first opened for that task. |
| DEL_STARTED | tinyint(4) | APP_DELEGATION | Boolean flag indicating whether the delegation (current or most recent task) was started or not. Default value: 0 |
| DEL_FINISHED | tinyint(4) | APP_DELEGATION | Boolean flag indicating whether the delegation (current or most recent task) has finished or not. Default value: 0 |
| DEL_DELAYED | tinyint(4) | APP_DELEGATION | Boolean flag indicating whether the delegation (current or most recent task) has been delayed (paused) or not. Default value: 0 |
| APP_CREATE_DATE | datetime | APPLICATION | The date and time when the case was created. |
| APP_FINISH_DATE | datetime | APPLICATION | The date and time when the case was finished. If not yet finished, then set to NULL. |
| APP_UPDATE_DATE | datetime | APPLICATION | The date and time when the data for the case was last updated. |
| APP_OVERDUE_PERCENTAGE | double | APP_DELEGATION | The percentage of time that it took the delegation (current or most recent task) to be closed in comparison to the due date. |
Note: The DEL_DURATION, DEL_DELAY_DURATION, DEL_QUEUE_DURATION, DEL_STARTED, DEL_FINISHED and DEL_DELAYED fields are calculated each time the cron.php script is executed by the system. If the cron.php is no properly configured to be executed as a cron job in Linux/UNIX or as a Scheduled Task in Windows, then the value of these fields will remain zero.
Changing the MySQL Location or its "root" user
In ProcessMaker version 2.0 and later, the location of the MySQL database which ProcessMaker uses can be changed inside the interface. In addition, the username and password of the MySQL user with superglobal privileges to setup ProcessMaker databases can also be changed. In most cases, this is the "root" user, but ProcessMaker can be configured to use another MySQL user. If using version 1.X, see these instructions.
After moving the database to a new location, changing the MySQL user who has privileges to setup the ProcessMaker databases or changing the password of that user, login to ProcessMaker as the "admin" user (or any user who has the PM_SETUP_ADVANCE permission in his/her role). Then, go to HOME > Settings > Cases List Cache Builder. In the section, click on the down arrow button to expand that section.
Fill out the information about how to connect to the MySQL database:
- Host: The IP address where the MySQL database is located. If located on the same machine as ProcessMaker, then enter "localhost". If MySQL runs on a port other than the standard port 3306, then also include that port number in the address.
- User: Enter "root" or the username of another MySQL user with superglobal privileges to create new databases. If ProcessMaker is installed on a different machine than the MySQL database, make sure that this user has rights to login remotely from the ProcessMaker database. These rights are granted in the mysql.user table.
- Password: Enter the password of the "root" user or another MySQL user with superglobal privileges to create new databases.
Then click on to Setup Password to save the changes. This will update the encrypted database information stored in
Updating the db.php configuration file
After updating the MySQL user and location in the graphical interface, use a plain text editor (such as Notepad, gedit or vim) to edit the file INSTALL-DIRECTORY/shared/sites/WORKSPACE/db.php for each workspace, which by default is located at:
Linux/UNIX:
/opt/processmaker/shared/sites/workflow/db.php
Windows Vista and later:
C:\Users\USERNAME\AppData\Roaming\ProcessMaker-X_X_X\processmaker\shared\sites\workflow\db.php
If changing the location of the MySQL server, then set the IP address or domain name (and port number if not using the default 3306 port for MySQL) for 'DB_HOST', 'DB_RBAC_HOST' and 'DB_REPORT_HOST'. If changing the name of the MySQL databases, then change the 'DB_NAME', 'DB_RBAC_NAME' and 'DB_REPORT_NAME'. Do NOT manually change the 'DB_PASS', 'DB_RBAC_PASS' and 'DB_REPORT_PASS', since these hashed values need to be automatically generated by ProcessMaker by clicking on the Setup Password button under Admin > Settings > Cases List Cache Builder.
// Processmaker configuration
define ('DB_ADAPTER', 'mysql' );
define ('DB_HOST', 'localhost' );
define ('DB_NAME', 'wf_workflow30185' );
define ('DB_USER', 'wf_workflow30185' );
define ('DB_PASS', 'vb1x3gpw678r' );
define ('DB_RBAC_HOST', 'localhost' );
define ('DB_RBAC_NAME', 'wf_workflow30185' );
define ('DB_RBAC_USER', 'wf_workflow30185' );
define ('DB_RBAC_PASS', 'vb1x3gpw678r' );
define ('DB_REPORT_HOST', 'localhost' );
define ('DB_REPORT_NAME', 'wf_workflow30185' );
define ('DB_REPORT_USER', 'wf_workflow30185' );
define ('DB_REPORT_PASS', 'vb1x3gpw678r' );
