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_UIDvarchar(32)APP_DELEGATIONThe unique ID of the case
DEL_INDEXint(11)APP_DELEGATIONThe 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_INDEXint(11)APP_DELEGATIONThe last delegation index in the case.
APP_NUMBERint(11)APPLICATIONThe sequential integer identifying the case. Default value: 0
APP_STATUSvarchar(32)APPLICATIONThe case status.
USR_UIDvarchar(32)APP_DELEGATIONThe 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_UIDvarchar(32)APP_DELEGATIONThe unique ID of the previous user assigned to work on a case.
TAS_UIDvarchar(32)APP_DELEGATIONThe 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_UIDvarchar(32)APP_DELEGATIONThe unique ID of the process which is being run by the case.
DEL_DELEGATE_DATEdatetimeAPP_DELEGATIONThe 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_DATEdatetimeAPP_DELEGATIONThe initial date and time when a user was assigned to a case.
DEL_FINISH_DATEdatetimeAPP_DELEGATIONThe 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_DATEdatetimeAPP_DELEGATIONThe 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_DATEdatetimeAPP_DELEGATIONThe 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_STATUSvarchar(32)APP_DELEGATIONThe state of the current task in the case. The possible values for this field are: "OPEN" and "CLOSED". Default Value: "OPEN"
APP_THREAD_STATUSvarchar(32)APP_THREADThe 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_TITLEvarchar(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_TITLEvarchar(255)CONTENT (according to the current system language)The current title of the process.
APP_TAS_TITLEvarchar(255)CONTENT (according to the current system language)The title of the current task (or most recent task) in the case.
APP_CURRENT_USERvarchar(128)+USR_FIRSTNAMEfields from USERStableThe full name of the user assigned to the current task (or most recent task) in the case.
APP_DEL_PREVIOUS_USERvarchar(128)+USR_FIRSTNAMEfields from USERStableThe full name of the user assigned to the previous task in the case.
DEL_PRIORITYvarchar(32)APP_DELEGATIONThe 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_DURATIONdoubleAPP_DELEGATIONThe time which has elapsed since the current (or most recent) task was first opened until it was finished.
DEL_QUEUE_DURATIONdoubleAPP_DELEGATIONThe 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_STARTEDtinyint(4)APP_DELEGATIONBoolean flag indicating whether the delegation (current or most recent task) was started or not. Default value: 0
DEL_FINISHEDtinyint(4)APP_DELEGATIONBoolean flag indicating whether the delegation (current or most recent task) has finished or not. Default value: 0
DEL_DELAYEDtinyint(4)APP_DELEGATIONBoolean flag indicating whether the delegation (current or most recent task) has been delayed (paused) or not. Default value: 0
APP_CREATE_DATEdatetimeAPPLICATIONThe date and time when the case was created.
APP_FINISH_DATEdatetimeAPPLICATIONThe date and time when the case was finished. If not yet finished, then set to NULL.
APP_UPDATE_DATEdatetimeAPPLICATIONThe date and time when the data for the case was last updated.
APP_OVERDUE_PERCENTAGEdoubleAPP_DELEGATIONThe 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 /workflow/engine/config/paths_installed.php file.

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.

<?php
// 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.