Please rate how useful you found this document: 
Average: 2.8 (4 votes)

All the information which ProcessMaker stores in held in three MySQL databases for each workspace, which are generally named wf_<workspace>, rb_<workspace> and rp_<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 databases 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 | | mydata | | mysql | | rb_workflow | | rpworkflow | | test | | wf_workflow | +--------------------+ 7 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 | +-----------------------+ | additional_tables | | app_delay | | app_delegation | | app_document | | app_message | | app_owner | | app_thread | | application | | case_tracker | | case_tracker_object | | configuration | | content | | db_source | | department | | dynaform | | fields | | group_user | | groupwf | | holiday | | input_document | | iso_country | | iso_location | | iso_subdivision | | language | | lexico | | login_log | | object_permission | | output_document | | process | | process_owner | | process_user | | report_table | | report_var | | route | | session | | shadow_table | | stage | | step | | step_supervisor | | step_trigger | | sub_application | | sub_process | | swimlanes_elements | | task | | task_user | | translation | | triggers | | users | | users_properties | +-----------------------+ 49 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 | | | | | USR_CREATE_DATE | datetime | NO | | | | | USR_UPDATE_DATE | datetime | NO | | | | | 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 | | | | | USR_POSITION | varchar(100) | NO | | | | | USR_RESUME | varchar(100) | NO | | | | | USR_BIRTHDAY | date | NO | | | | | USR_ROLE | varchar(32) | YES | | PROCESSMAKER_ADMIN | | | DEP_UID | varchar(32) | NO | | | | +-----------------+--------------+------+-----+--------------------+-------+ 23 rows in set (0.00 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:

CON_CATEGORYDescription
APP_DESCRIPTIONCase description.
APP_DOC_COMMENTComment on a document in a case.
APP_DOC_FILENAMEFilename of a document in a case.
APP_DOC_TITLETitle of a document in a case.
APP_TITLECase title.
DBS_DESCRIPTIONDatabase connection description.
DEPO_TITLEDepartment Title.
DYN_DESCRIPTIONDynaForm description.
DYN_TITLEDynaForm title.
GRP_TITLEGroup title.
INP_DOC_DESCRIPTIONInput Document description
INP_DOC_TITLEInput Document title
OUT_DOC_DESCRIPTIONOutput Document description.
OUT_DOC_FILENAMEOutput Document filename
OUT_DOC_TEMPLATEOutput Document template
OUT_DOC_TITLEOutput Document title
PRO_DESCRIPTIONProcess description.
PRO_TITLEProcess title
REP_TAB_TITLEReport Table title
SWI_TEXTText in process maps
TAS_DEF_DESCRIPTION 
TAS_DEF_MESSAGE
TAS_DEF_PROC_CODE 
TAS_DEF_TITLE 
TAS_DESCRIPTIONTask description
TAS_TITLETask title
TRI_DESCRIPTIONTrigger description
TRI_TITLETrigger title

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, listbox, 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');