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 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 go 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 in the Home tab 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.
Rebuild the table by logging in to ProcessMaker as the "admin" user (or any user who has the PM_SETUP_ADVANCE permission in his/her role). Then, go to Admin > Settings > Cases List Cache Builder.
Rebuild the contents of the wf_<workspace>.APP_CACHE_VIEW table by clicking 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 |
| DEL_LAST_INDEX | int(11) | APP_DELEGATION | The last delegation index in the case. |
| APP_NUMBER | int(11) | APPLICATION | The sequential integer identifying the case. Default value: 0 |
| APP_STATUS | varchar(32) | APPLICATION | The case status. |
| 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. |
| PREVIOUS_USR_UID | varchar(32) | APP_DELEGATION | The unique ID of the previous user assigned to work on a case. |
| 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_INIT_DATE | datetime | APP_DELEGATION | The initial date and time when a user was assigned to a 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_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_RISK_DATE | datetime | APP_DELEGATION | The date and time when the current task is at risk (about to expire). This is based upon the task properties and could also be bases upon a calendar. |
| 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_PRO_TITLE | varchar(255) | CONTENT (according to the current system language) | The current title of the process. |
| 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.
Considerations before Rebuilding
Please take note that in ProcessMaker version 3.0 the Cases List Cache must be rebuild in the language in which the ProcessMaker installation was done (English generally).
Otherwise, some issues may happen. For example, if the main installation works in English and a user executes the rebuilding process of the APP_CACHE_VIEW in a different language, actions performed by triggers or others won't be able to find the right values in the CONTENT table.
To fix this known issue, the value given by rebuilding the cache ("es-ES" if was done in Spanish) will have to be changed to "en" manually in the MySQL triggers.
Changing the MySQL Location or its "root" user
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 admin but ProcessMaker can be configured to use another MySQL user.
After moving the database to a new location, change the MySQL user who has privileges to setup the ProcessMaker databases or change 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 Admin > Settings > Cases List Cache Builder. In the section, click on the down arrow button to expand the "Setup Mysql Root Password".
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 (such as admin) 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.
- Security Code: Enter the captcha showed in the image above to be able to setup de new mysql new password and authenticate the user.
Then click on to Setup Password button 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' );
After editing the above file, then save it for the new database location to be used.
