Summary

The PegaRULES database (V5.1) contains 39 tables, 6 views, and dozens of indexes. A dozen of these tables involve work objects plus the associated attachments, history, and links, as presented in an Entity-Relationship Diagram.

Relationships among the tables are implicit from values of keys and are not reflected in the schema structure.

Suggested Approach

Process Commander developers and database administrators may find a conventional Entity-Relationship Diagram (ERD) useful in understanding the purpose and use of the tables that relate to work objects. These tables are the most important in a Process Commander system in terms of management reporting. In a production system, these tables may grow to have the among the largest number of rows, and may consume a big portion of the total tablespace.

Diagram

Click the image to open a larger version of the diagram as a JPG file, which you can save, print, and examine. Notations such as FK (foreign key) refer to logical relationships among table, not relations that are enforced in the schema. Gray boxes in the diagram group tables that are similar in purpose. Blue lines identify one-to-many relationships among rows of the tables.

To download a ZIP archive containing this diagram as a Visio 2003 VSD file, click PRKB-24260-ERDDiagram.zip.

Columns

Column names, except for the pzPVStream column, correspond to scalar properties in an object (known as Single Value properties).

For instance, most tables include a column named pxObjClass, corresponding to the property rule named @baseclass.pxObjClass. In a table row, the value of this property identifies the concrete class of the object. For example, the pr_assign_worklist table contains one row for assignments on any operator's worklist, that is, each persistent saved instance of the Assign-Worklist class.

Unlike the pr_assign_worklist table, most tables hold rows that represent one of many concrete Process Commander classes, as determined by a class-to-table mapping defined in Data-Admin-DB-Table instances. For example, the pc_data_workattach table contains rows for five types of work object attachments, including file attachments (class Data-WorkAttach-File), captured screen images (class Data-WorkAttach-ScreenShot) and so on. For the pc_data_workattach table, the pxObjClass column holds five distinct values that identify the Process Commander class of a row.

For most tables, the single column pzInsKey is the unique key. (Within Process Commander, this value is known as the 'handle" of the object.). However, the internal structure of pzInskey values differs from table to table. For work objects (in table pc_work), the pzInskey value has the format CCCCCCCC PP-NNNNN-SS, where CCCCCCCC is a class group and PP-NNNNN-XX is the work object ID, consisting of an optional prefix PP, number NNNNN, and optional suffix SS. For example, the pzInskey value for the first work object created in the sample application PegaSample is "PEGASAMPLE W-1".

The property values that when concatenated make up pzInskey values for a class is defined on the Locking tab of the Class form for the class. For example, the pzInskey of an assignment in the Assign-Worklist class is the value of property pxRefObjectKey concatenated with the value of the pxFlowName property. (In turn, pxRefObject key contains the class group of the work object concatenated with the work object ID).

All but a few tables in the PegaRULES database contain a BLOB column named pzPvStream that contains the details for a row in an internal format. Values within the BLOB column cannot be accessed through SQL commands; the BLOB column is decoded when an object is loaded into the Process Commander server, and re-encoded when a new or updated object is saved into the PegaRULES database.