All the information which ProcessMaker stores is held in one MySQL database for each workspace, which are generally named wf_<workspace>. This information can be easily looked up and even modified with MySQL or a graphical interface such as PhpMyAdmin to access MySQL databases. It can also be consulted from within ProcessMaker using the executeQuery() function in triggers.

Note: By default, MySQL tables are not case sensitive in Windows, but they are case sensitive in Linux/UNIX. See the MySQL documentation. The ProcessMaker table and field names are in lowercase in Windows installations, but they are in UPPERCASE in Linux/UNIX systems. When creating SQL queries to access the ProcessMaker databases, it is recommended to spell the table and field names in UPPERCASE, since those SQL queries can be used in both Windows and Linux systems. For example, refer to USERS.USR_USERNAME, rather than users.usr_username or Users.Usr_Username.

From the Command Line

The ProcessMaker database can be accessed from the command line of your operating system with mysql (or from your web browser with mysqladmin). Enter MySQL as the root user (or whichever user was configured to the MySQL databases for ProcessMaker). If you have forgotten this password as well, see these instructions to reset the root password.

Login into MySQL

In Windows open the Command Prompt (found at Start > All Programs > Accessories > Command Prompt) and enter:

cd "C:\Program Files\ProcessMaker\mysql\bin" (the path depends on where MySQL was installed) mysql -u root -p Enter the MySQL root password
In GNU/Linux or UNIX, open a terminal and enter:
mysql -u root -p Enter the MySQL root password
(If logged in as the root user in Red Hat/Fedora, it generally isn't necessary to use the -p option and enter the MySQL root password.)

Examining the databases

Once inside MySQL, examine the available databases:

mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | performance_schema | | mysql | | test | | wf_workflow | +--------------------+ 5 rows in set (0.00 sec)

Switch to a particular database:

mysql> use wf_workflow; Database changed

See all the tables in a database:

mysql> show tables; +-------------------------------+ | Tables_in_wf_workflow | +-------------------------------+ | abe_configuration | | abe_requests | | abe_responses | | additional_tables | | addons_manager | | addons_store | | app_assign_self_service_value | | app_cache_view | | app_delay | | app_delegation | | app_document | | app_event | | app_folder | | app_history | | app_message | | app_notes | | app_owner | | app_solr_queue | | app_thread | | app_timeout_action_executed | | application | | bpmn_activity | | bpmn_artifact | | bpmn_bound | | bpmn_data | | bpmn_diagram | | bpmn_documentation | | bpmn_event | | bpmn_extension | | bpmn_flow | | bpmn_gateway | | bpmn_lane | | bpmn_laneset | | bpmn_participant | | bpmn_process | | bpmn_project | | calendar_assignments | | calendar_business_hours | | calendar_definition | | calendar_holidays | | case_consolidated | | case_scheduler | | case_tracker | | case_tracker_object | | catalog | | configuration | | content | | dashboard | | dashboard_das_ind | | dashboard_indicator | | dashlet | | dashlet_instance | | db_source | | department | | dim_time_complete | | dim_time_delegate | | dynaform | | element_task_relation | | email_event | | email_server | | event | | field_condition | | fields | | gateway | | gmail_relabeling | | group_user | | groupwf | | holiday | | input_document | | iso_country | | iso_location | | iso_subdivision | | language | | lexico | | license_manager | | list_canceled | | list_completed | | list_inbox | | list_my_inbox | | list_participated_history | | list_paused | | list_unassigned | | list_unassigned_group | | log_cases_scheduler | | login_log | | message_application | | message_event_definition | | message_event_relation | | message_type | | message_type_variable | | notification_device | | oauth_access_tokens | | oauth_authorization_codes | | oauth_clients | | oauth_refresh_tokens | | oauth_scopes | | object_permission | | output_document | | pmoauth_user_access_tokens | | pro_reporting | | process | | process_category | | process_files | | process_owner | | process_user | | process_variables | | rbac_authentication_source | | rbac_permissions | | rbac_roles | | rbac_roles_permissions | | rbac_systems | | rbac_users | | rbac_users_roles | | report_table | | report_var | | route | | script_task | | sequences | | session | | session_storage | | shadow_table | | stage | | step | | step_supervisor | | step_trigger | | sub_application | | sub_process | | swimlanes_elements | | task | | task_user | | timer_event | | translation | | triggers | | users | | users_properties | | usr_reporting | | web_entry | | web_entry_event | +-------------------------------+ 138 rows in set (0.00 sec)

Examine the structure of a table:

mysql> desc users; +-------------------------+--------------+------+-----+-------------------+-----+ | Field | Type | Null | Key | Default |Extra| +-------------------------+--------------+------+-----+-------------------+-----+ | USR_UID | varchar(32) | NO | PRI | | | | USR_USERNAME | varchar(100) | NO | | | | | USR_PASSWORD | varchar(32) | NO | | | | | USR_FIRSTNAME | varchar(50) | NO | | | | | USR_LASTNAME | varchar(50) | NO | | | | | USR_EMAIL | varchar(100) | NO | | | | | USR_DUE_DATE | date | NO | | NULL | | | USR_CREATE_DATE | datetime | NO | | NULL | | | USR_UPDATE_DATE | datetime | NO | | NULL | | | USR_STATUS | varchar(32) | NO | | ACTIVE | | | USR_COUNTRY | varchar(3) | NO | | | | | USR_CITY | varchar(3) | NO | | | | | USR_LOCATION | varchar(3) | NO | | | | | USR_ADDRESS | varchar(255) | NO | | | | | USR_PHONE | varchar(24) | NO | | | | | USR_FAX | varchar(24) | NO | | | | | USR_CELLULAR | varchar(24) | NO | | | | | USR_ZIP_CODE | varchar(16) | NO | | | | | DEP_UID | varchar(32) | NO | | | | | USR_POSITION | varchar(100) | NO | | | | | USR_RESUME | varchar(100) | NO | | | | | USR_BIRTHDAY | date | YES | | NULL | | | USR_ROLE | varchar(32) | YES | | PROCESSMAKER_ADMIN| | | USR_REPORTS_TO | varchar(32) | YES | | | | | USR_REPLACED_BY | varchar(32) | YES | | | | | USR_UX | varchar(128) | YES | | NORMAL | | | USR_TOTAL_INBOX | int(11) | YES | | 0 | | | USR_TOTAL_DRAFT | int(11) | YES | | 0 | | | USR_TOTAL_CANCELLED | int(11) | YES | | 0 | | | USR_TOTAL_PARTICIPATED | int(11) | YES | | 0 | | | USR_TOTAL_PAUSED | int(11) | YES | | 0 | | | USR_TOTAL_COMPLETED | int(11) | YES | | 0 | | | USR_TOTAL_UNASSIGNED | int(11) | YES | | 0 | | | USR_COST_BY_HOUR | decimal(7,2) | YES | | 0.00 | | | USR_UNIT_COST | varchar(50) | YES | | | | | USR_PMDRIVE_FOLDER_UID | varchar(32) | YES | | | | | USR_BOOKMARK_START_CASES| mediumtext | YES | | NULL | | | USR_TIME_ZONE | varchar(100) | YES | | | | | USR_DEFAULT_LANG | varchar(10) | YES | | | | +-------------------------+--------------+------+-----+-------------------+----+ 39 rows in set (0.01 sec)

Exiting MySQL:

mysql> exit; bye

From the phpMyAdmin GUI

The ProcessMaker databases can be accessed with a graphical interface by using phpMyAdmin. The Windows installer for ProcessMaker automatically installs phpMyAdmin by default, but for Linux/UNIX, it will have to be manually installed.

To use phpMyAdmin, enter the address http://IP-ADDRESS/phpmyadmin in your web browser. For example, if ProcessMaker was installed on the local machine, use the address http://localhost/phpmyadmin . The Windows installer for ProcessMaker automatically created the user "administrator", so login to phpMyAdmin as "administrator" with the password which you entered during the installation.

Finding ProcessMaker Objects

Objects in ProcessMaker such as users, groups, processes, tasks, cases, DynaForms, database connections, etc. are identified by their UID or unique identification, which is a string of 32 hexadecimal numbers generated by ProcessMaker. In most cases, the individual tables which hold information about ProcessMaker objects do not include their names (i.e, titles or labels), so it is very hard to know which object is being referenced in the table.

Instead the name of most objects (except users) is stored in the wf_<workspace>.CONTENT table. To find out information about a particular object, look up their names and their matching UID in the CONTENT table, then look up the information about the object in their particular table.

To find types of elements in the CONTENT table, look for the following values in the CON_CATEGORY field:

CategoryDescription
'APP_TITLE'The title of a case, which contains the case number such as "#234" by default, but can be set in the properties of the tasks int the process.
'APP_DESCRIPTION'Case description, which can be set in the properties of the tasks in the process.
'APP_DOC_COMMENT'The comment added to an uploaded file in an Input Document.
'APP_DOC_FILENAME'The filename of a file which is uploaded to an Input Document or File field or generated as an Output Document.
'APP_DOC_TITLE'The title of a file associated with a case. This feature is deprecated and no longer used in version 2 and later.
'PRO_TITLE'Process title.
'PRO_DESCRIPTION'Process description
'TAS_TITLE'Task title, set in the task's properties under Definitions.
'TAS_DESCRIPTION'Task description, set in the task's properties under Definitions.
'TAS_DEF_SUBJECT_MESSAGE'The subject line of an email, which is set to the next assigned user(s) in the task's properties under Notifications.
'TAS_DEF_MESSAGE'The body of an email, which is set to the next assigned user(s) in the task's properties under Notifications.
'TAS_DEF_TITLE'The title of the case when it arrives at the task, which is set in the task's properties under Case Labels.
'TAS_DEF_DESCRIPTION'The description of the case when it arrives at the task, which is set in the task's properties under Case Labels.
'TAS_DEF_PROC_CODE'Task property to assign a code. This feature is deprecated and no longer used in version 3 and later.
'DYN_TITLE'DynaForm title.
'DYN_DESCRIPTION'DynaForm description.
'GRP_TITLE'Group title.
'DEPO_TITLE'Department title.
'INP_DOC_TITLE'Input Document title.
'INP_DOC_DESCRIPTION'Input Document description.
'OUT_DOC_TITLE'Output Document title.
'OUT_DOC_DESCRIPTION'Output Document description.
'OUT_DOC_FILENAME'Output Document filename, set in the definition of the Output Document. For the filename of a generated Output Document file, use 'APP_DOC_FILENAME'.
'OUT_DOC_TEMPLATE'The HTML code of the template for the Output Document.
'PER_NAME'Name of a role's permission. For example, the PM_REASSIGNCASE permission has a name of "Reassign case".
'ROL_NAME'Name of the role. For example, the PROCESSMAKER_MANAGER role has a name of "Manager".
'TRI_TITLE'Trigger title.
'TRI_DESCRIPTION'Trigger Description.

Examples

To see a list of all the cases and their UIDs:

SELECT CON_ID, CON_VALUE FROM CONTENT WHERE CON_CATEGORY='APP_TITLE';

Running the query from the command line:

Running the query from the phpMyAdmin GUI

To look up the UID for a particular case whose title is "#46":

SELECT CON_ID FROM CONTENT WHERE CON_CATEGORY='APP_TITLE' AND CON_VALUE='#46';

Users

Listing all available users
SELECT USR_FIRSTNAME, USR_LASTNAME, USR_USERNAME, USR_EMAIL FROM USERS;

Running the query from the command line:

Running the query from the phpMyAdmin GUI

Finding information about a particular user
select * from USERS where USR_USERNAME='admin';
Listing what groups a user belongs to
SELECT C.CON_ID, C.CON_VALUE FROM CONTENT C, GROUP_USER GU, USERS U WHERE
 C.CON_ID=GU.GRP_UID AND U.USR_UID=GU.USR_UID AND
 U.USR_USERNAME='admin';

Running the query from the command line:

Running the query from the phpMyAdmin GUI

Listing what tasks a user is assigned to
SELECT C.CON_VALUE FROM CONTENT C, USERS U, TASK_USER TU WHERE
 C.CON_ID = TU.TAS_UID AND U.USR_UID = TU.USR_UID
 AND U.USR_USERNAME = 'admin' AND C.CON_CATEGORY = 'TAS_TITLE';

Running the query from the command line:

Running the query from the phpMyAdmin GUI

Listing the pending cases for a user
SELECT C.CON_ID AS CASE_ID, A.APP_NUMBER AS CASE_NUMBER, C.CON_VALUE AS CASE_TITLE
 FROM CONTENT C, APPLICATION A, APP_DELEGATION AD, USERS U
 WHERE C.CON_ID = AD.APP_UID AND AD.APP_UID=A.APP_UID AND U.USR_UID = AD.USR_UID AND
 U.USR_USERNAME = 'admin' AND C.CON_CATEGORY = 'APP_TITLE' AND (A.APP_STATUS = 'OPEN'
 OR A.APP_STATUS='TO_DO') AND AD.DEL_THREAD_STATUS='OPEN';

Change U.USR_USERNAME = 'admin' to the username to query. The @@USR_USERNAME system variable can be used if needing to query for the currently logged-in user in a trigger or a DynaForm field.

To see the pending cases for a particular user from the command line:

Accessing in Dynaform Fields

If needing to use a database query in a dropdown box, radiogroup or checkgroup inside a DynaForm, make sure that the query returns two fields where the first field is the value which is stored when an option is selected and the second field is the label which is displayed to the user. Enter the query in the SQL query field without a semicolon (;) at the end.

For example listing what groups a user belongs to:

The XML definition:

<GroupsUser type="dropdown" required="0" readonly="0" mode="edit" btn_cancel="Cancel"><![CDATA[
 SELECT C.CON_ID, C.CON_VALUE FROM CONTENT C, GROUP_USER GU, USERS U WHERE C.CON_ID = GU.GRP_UID AND U.USR_UID = GU.USR_UID AND U.USR_USERNAME = 'liriarte'
 ]]><en>Groups that a user belongs to</en>
</GroupsUser>

The preview will be:

If needing to use a case or system variable in the query, then create a hidden field in the DynaForm which has the same name as the variable so that its value will be passed to the DynaForm when it is loaded. Then, that variable can be used in the SQL query, either as @@variable (variable inserted inside double quotation marks) or @#variable (variable inserted without enclosing quotation marks).

For example, if needing to display a list of the list of groups for the currently logged-in user, then use the @@USR_USERNAME system variable by adding a hidden field named "USR_USERNAME" and placing U.USR_USERNAME = @@USR_USERNAME in the SQL query:

<USR_USERNAME type="hidden" mode="edit" required="0" readonly="0" optgroup="0"/>
<GroupsUser type="dropdown" required="0" readonly="0" mode="edit" btn_cancel="Cancel"><![CDATA[
 SELECT C.CON_ID, C.CON_VALUE FROM CONTENT C, GROUP_USER GU, USERS U WHERE C.CON_ID = GU.GRP_UID AND U.USR_UID = GU.USR_UID AND U.USR_USERNAME = @@USR_USERNAME
 ]]><en>Groups that the user belongs to</en>
</GroupsUser>

Groups

Listing the available groups
SELECT CON_VALUE, CON_ID FROM CONTENT WHERE CON_CATEGORY='GRP_TITLE';

Running the query from the command line:

Running the query from the phpMyAdmin GUI

Finding the status of a group
SELECT GROUPWF.GRP_UID, GROUPWF.GRP_STATUS FROM GROUPWF INNER JOIN CONTENT
 ON GROUPWF.GRP_UID=CONTENT.CON_ID WHERE CONTENT.CON_VALUE='Employees';

Running the query from the command line:

Running the query from the phpMyAdmin GUI

Listing members of a particular group
SELECT U.USR_UID, U.USR_USERNAME FROM (USERS U LEFT JOIN GROUP_USER GU
  ON U.USR_UID=GU.USR_UID) LEFT JOIN CONTENT C
  ON GU.GRP_UID=C.CON_ID WHERE C.CON_VALUE='Employees';

Running the query from the command line:

Running the query from the phpMyAdmin GUI

Accessing in DynaForm Fields

If queries are required to make them work in a dropdown inside a Dynaform refer them without the semicolon(;) at the end of the sentence.

For example listing members of a particular group:

The XML definition:

<membersGroup type="dropdown" required="0" readonly="0" mode="edit" btn_cancel="Cancel">
  <![CDATA[SELECT U.USR_UID, U.USR_USERNAME from (USERS U left join GROUP_USER GU on U.USR_UID=GU.USR_UID) left join CONTENT C on
 GU.GRP_UID=C.CON_ID where C.CON_VALUE='Employees']]>
  <en>Members of a particular Group</en>
</membersGroup>

The preview will be:

Using ProcesMaker classes to lookup translations

It is also possible to lookup the text associated with a specified ProcessMaker object in the CONTENT by using the Content::load() method. This method allows the language to be specified, so this method can be used to obtain translations.

Example:
Obtain the title an task with the UID "13027240856a7f04516b3e1011191907" in English and French from France:

$titleEn = Content::load('TAS_TITLE', '', '12a16611256a0006a028ab7006995270', 'en');
$titleFr = Content::load('TAS_TITLE', '', '12a16611256a0006a028ab7006995270', 'fr-FR');

Protecting Processmaker Core Tables and PM_Tables

Available Version: From 3.0.1.8 on.

To avoid database integrity issues when SQL Queries are executed with the executeQuery() function directly into the ProcessMaker Core tables or PM_Tables, users can limit the executeQuery() usage defining which SQL statements would not be able to be executed on core tables or PM_Tables, this configuration can be defined in the following files located in: /opt/processmaker/workflow/engine/config/

  • system-tables.ini: This file is created when ProcessMaker is installed or upgraded and contains all the ProcessMaker Core Tables. It is suggested not to modify this file to protect the integrity of the system.
  • execute-query-blacklist.ini: This file is created when ProcessMaker is installed and defines the SQL Statements that cannot be executed and the PM_Tables that cannot be modified by these statements. The statements INSERT|UPDATE|REPLACE|DELETE are set by default, this configuration applies to all tables defined.

Note: These SQL Statements or tables cannot be set individually.

The executeQuery() function accepts the following statements: SELECT, EXECUTE, EXEC, SHOW, DESCRIBE, EXPLAIN, BEGIN, INSERT, UPDATE, DELETE, REPLACE.

For example, if the user wants to forbid the INSERT, UPDATE, REPLACE, DELETE AND TRUNCATE operations from being executed on the PM_MY_TABLE and all ProcessMaker Core tables; the system-tables.ini file should remain as is and the execute-query-blacklist.ini file configuration should be the following:

;The separator parameter is |

;Setting statements - Statements can not allowed in the execution, example queries="INSERT|UPDATE|REPLACE|DELETE"
queries  = "INSERT|UPDATE|REPLACE|DELETE|TRUNCATE"
 
;Setting tables - Tables that can not apply sentences, pmtables="PMTABLE1|PMTABLE2"
pmtables = "PM_MY_TABLE"

If an executeQuery function similar to the following ones is executed on the PM_MY_TABLE or a core table with any of the following statements: INSERT, UPDATE, REPLACE, DELETE AND TRUNCATE.

$ins = executeQuery("INSERT INTO APP_FOLDER (FOLDER_UID) VALUES ('0001')");
$upd = executeQuery("UPDATE APP_FOLDER SET FOLDER_NAME = 'Folder A'");
$del = executeQuery("DELETE FROM PM_MY_TABLE");

The following error message will be displayed:

The log of errors for restricted executions is saved in the PHP error log, whose location can be set with the error_log directive in the php.ini file on the ProcessMaker server. By default, it is generally located at /var/log/php_errors.log in Linux/UNIX servers.