Composite query tables

Composite query tables in Business Process Choreographer do not have a specific representation of data in the database; they comprise of a combination of data from related predefined and supplemental query tables. Use a composite query table to retrieve the information for a process instance list or task list, such as My To Dos.

Composite query tables are designed by client developers and they allow for a fine-grained configuration of filters and authorization options for optimized data access when the query is run. They are realized with SQL, which is optimized for task and process list queries.

It is recommended that you use composite query tables in production scenarios in place of the standard Business Process Choreographer query APIs, because composite query tables provide an abstraction over the actual implementation of the query and thus enable query optimizations.

Furthermore, you can change composite query tables at run time without redeploying the client that accesses the query table.

The following figure provides an overview of the content of composite query tables:

Figure 1. Composite query table content

All composite query tables are defined with one primary query table and zero or more attached query tables.

Primary query tables:

Constitute the main information that is contained in a composite query table.
Must be one of the predefined query tables.
Uniquely identify each object in the composite query table by the primary key. For example, for the TASK predefined query table, this is the task ID TKIID.
Authorize the contents of a query table using work items which are contained in the WORK_ITEM query table, if instance-based authorization is used.
Determine the list of objects that are returned as rows of a table when querying the composite query table.

Attached query tables:

Can be predefined query tables and supplemental query tables, which are already deployed on the system.
Are available to provide information in addition to the information that is provided by the primary query table. For example, if TASK is the primary query table, the description of the task provided in the TASK_DESC query table can be added to the contents of the composite query table.

Typically, the primary query table is chosen based on the purpose of the composite query table.

If the composite query table describes a task list, the TASK query table is the primary query table.
If the composite query table describes a process list, the PROCESS_INSTANCE query table is the primary query table.
Lists of activities are retrieved using the ACTIVITY primary query table.
Lists of human task escalations are retrieved using the ESCALATION primary query table.
Lists of work baskets are retrieved using the WORK_BASKET primary query table.
Lists of business categories are retrieved using the BUSINESS_CATEGORY primary query table.

The relationship between primary and attached query tables

The attached query table and the primary query table must have a one-to-one or one-to-zero relationship. If the one-to-one or one-to-zero relationship is violated, a runtime exception occurs when the query is run.

Primary query tables and attached query tables are correlated using a join attribute that is defined on the attached query table. This join attribute cannot be changed for predefined query tables, because it describes the relationship between the data in the various query tables of Business Process Choreographer. The join attribute is usually sufficient to maintain the one-to-one or one-to-zero relationship. For example, the CONTAINMENT_CTX_ID attribute is used on the TASK query table to attach the related process instance information that is identified by the PIID attribute on the PROCESS_INSTANCE query table.

When a one-to-many relationship exists, you must specify an additional criterion, known as selection criterion, in the Query Table Builder when you define the query table. For example, this could be “LOCALE='en_US'”. A task can have several descriptions that are identified using different locales for a single task. To further restrict the items that are returned by the query, you can attach a supplemental query table with selection criteria to the composite query table, and enable the optimizeForFiltering option.

Example 1:

The following figure provides a sample visualization of the selection criteria that is specified on attached query tables:

Figure 2. Composite query table with selection criteria

The composite query table contains the ID, STATE, NAME, CUSTOMER, and DESCRIPTION attributes.

ID, STATE, and NAME are provided by the TASK primary query table.
CUSTOMER is a custom property on TASK. Custom properties are stored in the TASK_CPROP query table. For a particular task, a custom property is uniquely identified using its name. This is reflected in the selection criterion “CUSTOMER='IBM'”.
DESCRIPTION is the description of the task, which is stored in TASK_DESC query table. For each task instance, the task description for a particular task is uniquely identified by its locale. This is reflected in the selection criterion “LOCALE='en_US'”.

Example 2:

The focus of this example is on the relationship between the primary and the attached query tables, using TASK as the primary query table and TASK_DESC as the attached query table. When you define your composite query table, the LOCALE attribute of the TASK_DESC query table must be specified to ensure that there is a one-to-one or one-to-zero relationship between the primary query table and the attached query table. The table shows sample contents of a composite query table with a valid selection criterion for the TASK_DESC attached query table.

Table 1. Valid contents of a composite query table
TASK primary query table information	TASK_DESC attached query table information
NAME	LOCALE	DESCRIPTION
task_one	en_US	This is a description.
task_two	en_US	This is a description.
...	...	...

The following table shows hypothetical invalid contents (in bold type) if the selection criterion is set incorrectly, which means that the one-to-one or one-to-zero relationship is violated.

Table 2. Invalid contents of a composite query table
Information from TASK (primary query table)	Information from TASK_DESC (attached query table)
NAME	LOCALE	DESCRIPTION
task_one	en_US	This is a description.
task_one	de_DE	Das ist eine Beschreibung.
...	...	...

Properties

Composite query tables have the following properties:

Table 3. Properties of composite query tables
Property	Description
Name	The query table name must be unique within a Business Process Choreographer installation. When the query is run, this query table name is used to identify the query table that is queried. A query table is uniquely identified using its name, which is defined as `prefix.name` for composite query tables. The maximum length of the `prefix.name` is 28 characters. The prefix must be different from the reserved prefix ‘IBM', for example, 'COMPANY.TODO_TASK_LIST'. Do not use a digit at the end of the table name. If a table is used multiple times within a query, the name of the table is extended with a number ranging from 0 to 9. For example, CUSTOM_VIEW0, CUSTOM_VIEW1 and so on. If there is already a digit at the end of your table name, Business Process Choreographer will remove this digit, which causes an QueryUnknownTableException.
Attributes	Attributes of composite query tables define the pieces of information that are available for queries. The attributes are defined with a name, in uppercase. The type is inherited from the referenced attribute, which is one of the following: Boolean: A boolean value Decimal: A floating point number ID: An object ID, such as TKIID of query table TASK Number: An integer, short, or long String: A string Timestamp: A timestamp Attributes of composite query tables are defined using a reference to attributes of the primary query table or the attached query tables. The attributes of the composite query tables inherit the types and constants of referenced attributes. In addition to the attributes that are part of the query table definition, work item information can be queried at run time. This is possible if the primary query table contains instance data, such as TASK or PROCESS_INSTANCE, and if instance-based authorization is used on the composite query table. For example, the query can be defined to return only human tasks of which the user is a potential owner.
Authorization	Each composite query table defines if instance-based, role-based, or no authorization is used when queries are run on it. If instance-based authorization is defined, only objects with a work item for the user who performs the query are returned. However, using AdminAuthorizationOptions this verification can be reduced to a verification of the existence of a work item of any user. The user must be in the BPESystemAdministrator Java EE role if the Business Flow Manager EJB is used or the TaskSystemAdministrator Java EE role if the Human Task Manager EJB is used, for those queries, and AdminAuthorizationOptions must be passed to the query table API. If role-based authorization is defined, the user must be in the BPESystemAdministrator Java EE role if the Business Flow Manager EJB is used or the TaskSystemAdministrator Java EE role if the Human Task Manager EJB is used, for those queries, and AdminAuthorizationOptions must be passed to the query table API. If you use the WorkBasketSystemAdministrator Java EE role for the Human Task Manager EJB, the AdminAuthorizationOptions object can also be used for the WORK_BASKET query table. Similarly, if you use the BusinessCategorySystemAdministrator Java EE role, the AdminAuthorizationOptions object can be used for the BUSINESS_CATEGORY query tables. If no authorization is defined, the query is run without checks against the existence of work items of the related objects in the query table. All authenticated users can see the contents of the query table. Instance-based authorization can be defined if the primary query table contains instance data; role-based authorization can be defined if the primary query table contains template data. No authorization can be defined on composite query tables regardless of which primary query table is used.

Filters

Filters are used to limit the number of objects, or rows, that are contained in a composite query table.

Figure 3. Filters in composite query tables

Filters in composite query tables can be defined during development on the:

Primary query table, as the primary query table filter.
Implicitly available WORK_ITEM query table which is responsible for authorization if the primary query table contains instance data. This filter is called the authorization filter, and is available only if the composite query table is configured to use instance-based authorization.
Composite query table, as the query table filter.
Composite query table, as an attached supplemental query table with selection criteria and the optimizeForFiltering option enabled.

Filters are defined during query table development. For example, a composite query table with the TASK primary query table can filter on tasks that are in the ready state (“STATE=STATE_READY” as the primary query table filter).

Authorization

Authorization for accessing the contents of a composite query table with a primary query table is similar to the authorization that is used to access the primary query table. The difference is that composite query tables can be configured to be more restrictive.

If instance-based authorization is configured for use, the data contained in the composite query table is verified for existing work items in the WORK_ITEM query table. This verification is made against the primary query table. Everybody, individual, group, and inherited work items are used for the verification, depending on the configuration of the composite query table. If inherited work items are specified, objects that have a process instance as parent, such as a participating human task, with a related everybody, individual, or group work item as configured, are contained in the composite query table. Typically, inherited work items are useful only for administrators.
Composite query tables with a primary query table that contains template data must not be set to use instance-based authorization. If role-based authorization is used, queries can be run only by users that are in the BPESystemAdministrator Java EE role if the Business Flow Manager EJB is used or the TaskSystemAdministrator Java EE role if the Human Task Manager EJB is used, and the AdminAuthorizationOptions object must be used.