Package com.ibm.openpages.api.query

OpenPages Query Service API

See: Description

  • Interface Summary 
    Interface Description
    IPage
    Interface that provides the ability to consume a result set using pages.
    IQuery
    An object that represents a query to execute.
    IResultSetRow
    Interface that provides the ability to consume a row of fields.
    ITabularResultSet
    Interface that provides the ability to consume a result set using rows.
  • Class Summary 
    Class Description
    ParameterValue
    An object that represents an input binding parameter for a parameterized SQL statement.

Package com.ibm.openpages.api.query Description

OpenPages Query Service API

Supports interfaces and classes for the OpenPages Query Service.

The OpenPages Query Service provides a type-based query service for discovering objects that match specified criteria, by projecting the OpenPages Domain model as a relational view. Through this relational view, queries may be performed using a simplified SQL SELECT statement.

Query Language Definition

The query language is based on a subset of the SQL-92 grammar with extensions to enhance its filtering capability for the Domain model, such as enumeration-valued/multi enumeration-valued fields, full-text search, and hierarchal traversal.

The semantics of this query language is defined by the SQL-92 standard, including the extensions, in conjunction with the Domain model relational view. The basic structure of an OpenPages query is an SQL statement that must include the following clauses:

  • SELECT [field names]: identifies the set of virtual columns that will be included in the query results for each row.
  • FROM [object type name]: identifies which object type the query will run against.
Additionally, a query may include the following clauses:
  • [OUTER] JOIN [join specification]: identifies the traversal constraints between types that rows must satisfy to be considered a result for the query.
  • WHERE [conditions]: identifies the constraints that rows must satisfy to be considered a result for the query.
  • GROUP BY [group by specification]: identifies the columns by which the aggregate functions will group the result set.
  • ORDER BY [sort specification]: identifies the order in which the result rows must be sorted in the result row set.

Grammar

The ANTLR input grammar syntax is a subset of the SQL-92 grammar with extensions specific to OpenPages for handling hierarchical traversal. The ANTLR is primarily based on EBNF (Extended Backus-Naur Form) grammar form.
query : SELECT select_list from_clause where_clause? group_by_clause? order_by_clause?

select_list : select_sublist (COMMA select_sublist)* | STAR

select_sublist : column_reference | qualifier DOT STAR

qualifier : one_table

column_reference : column_name | qualifier DOT column_name 
    | COUNT LPAR column_name RPAR | COUNT LPAR qualifier DOT column_name RPAR
    | COUNT LPAR qualifier DOT STAR RPAR | COUNT LPAR STAR RPAR

from_clause : FROM table_reference

table_join : JOIN one_table join_specification

join_specification : ON join_predicate LPAR one_table RPAR
    | ON join_predicate LPAR one_table COMMA level RPAR

join_predicate : CHILD | PARENT | ANCESTOR

table_reference : one_table table_join*

one_table : table_name | table_name AS correlation_name

correlation_name : ENTITY

table_name : ENTITY

column_name : ENTITY

where_clause : WHERE search_condition

search_condition : sub_search_condition ((AND^ | OR^) sub_search_condition)*

sub_search_condition : predicate | LPAR search_condition RPAR

predicate : comparison_predicate | like_predicate | null_predicate
    | text_search_predicate | in_predicate

text_search_predicate : CONTAINS LPAR value_expression COMMA string_literal RPAR 
    | NOTCONTAINS LPAR value_expression COMMA string_literal RPAR

comparison_predicate : value_expression comparison_op literal

comparison_op : EQ | NEQ | LT | GT | LTEQ | GTEQ

like_predicate : value_expression LIKE string_literal | value_expression NOTLIKE string_literal

in_predicate : value_expression IN LPAR literal_list RPAR
    | value_expression NOTIN LPAR literal_list RPAR

null_predicate : value_expression ISNULL | value_expression ISNOTNULL

literal_list : literal (COMMA literal)*

value_expression : column_reference

literal : num_literal | string_literal | date_literal | boolean_literal

string_literal : QUOTED_STRING

date_literal : DATE QUOTED_STRING

boolean_literal : TRUE | FALSE

level : INTEGER_LIT

num_literal : INTEGER_LIT | DECIMAL_LIT

group_by_clause: GROUP BY group_by_specification (COMMA group_by_specification)*

group_by_specification: column_name  | qualifier DOT column_name

order_by_clause : ORDER BY sort_specification (COMMA sort_specification)*

sort_specification : column_reference | column_reference (ASC | DESC)
 

SELECT clause

The SELECT clause must contain one of the following:
  • A comma separated list with one or more column names: the query result set includes, in its row set, all the fields specified in the SELECT clause.
  • *: The query result set includes, in its row set, all the fields of the qualifying object type or the object type specified in the FROM clause.
In addition, all specified column names must be valid field names or aggregate functions.

System fields

A query may include the following system fields:
Field Name Type Description
Resource ID ID  
Comment String  
Created By Integer  
Creation Date Date  
Description String  
Last Modification Date Date  
Last Modified By Integer  
Location String  
Name String  
Orphan String Possible values are:
Not Orphan
Direct Orphan
Indirect Orphan
Is Locked Boolean  

Aggregate functions:

An aggregate function receives a set of values for each argument (such as the values of a column) and returns a single-value result for the set of input values.
An aggregate function may only be used in single object type hierarchy, not counting joins of multiple object types.
  • COUNT: The COUNT function returns the number of rows or values in a set of rows or values.
Example: Get the number of instances in the object type [SOXIssue].
 SELECT COUNT(*)
 FROM [SOXIssue]
 
Example: Get the number of instances by each [OPSS-Issue:Conclusion] in the object type [SOXIssue].
 SELECT [OPSS-Issue:Conclusion], COUNT([OPSS-Issue:Conclusion])
 FROM [SOXIssue]
 GROUP BY [OPSS-Issue:Conclusion]
 

FROM clause

The FROM clause identifies the Object type that the query will be run against. The FROM clause must contain only one object type name that is available from the model.

JOIN clause

The JOIN clause is used to combine fields from multiple object types into a single result set. It defines how navigation between types is expressed. The JOIN clause differs slightly from the SQL-92 standard. The JOIN clause is expressed between object types with the ability to specify a CHILD/PARENT function to indicate the direction of traversal. When defining a self JOIN, a level may be included in the function as an additional parameter. JOIN behavior is equivalent to a SQL inner join. For an alternative syntax for OUTER JOIN, see the OUTER JOIN selection below.

Top down

The top down traversal JOIN means you are joining from a parent to a child.

Example:

A SOXBusEntity type is a parent of the LevelOne type and LevelOne is the parent of LevelTwo.
SELECT [SOXBusEntity].[Name], [LevelOne].[Name], [LevelTwo].[Name]
FROM [SOXBusEntity]
JOIN [LevelOne] ON PARENT([SOXBusEntity])
JOIN [LevelTwo] ON PARENT([LevelOne])
 
  • FROM [SOXBusEntity]
    The FROM clause indicates the starting point in the traversal.

  • JOIN [LevelOne] ON PARENT([SOXBusEntity])
    This join expresses the relationship between LevelOne and SOXBusEntity. LevelOne is joining to SOXBusEntity using a PARENT association.

  • JOIN [LevelTwo] ON PARENT([LevelOne])
    This join expresses the relationship between LevelTwo and LevelOne. LevelTwo is joining with LevelOne using a PARENT association.

Bottom up

The bottom up traversal JOIN means you are joining from a child to a parent.

Example:

A SOXBusEntity type is a parent of the LevelOne type and LevelOne is the parent of LevelTwo.
SELECT [SOXBusEntity].[Name], [LevelOne].[Name], [LevelTwo].[Name]
FROM [LevelTwo]
JOIN [LevelOne] ON CHILD([LevelTwo])
JOIN [SOXBusEntity] ON CHILD([LevelOne])
 
  • FROM [LevelTwo]
    The FROM clause indicates the starting point in the traversal.

  • JOIN [LevelOne] ON CHILD([LevelTwo])
    This join expresses the relationship between LevelOne and SOXBusEntity. LevelOne is joining with LevelTwo using a CHILD association.

  • JOIN [SOXBusEntity] ON CHILD([LevelOne])
    This join expresses the relationship between SOXBusEntity and LevelOne. SOXBusEntity is joining with LevelOne using a CHILD association.

Recursive

The recursive traversal JOIN means you are joining through a self-join. That is, you are joining on a parent/child of the same type. Recursive traversal also extends the syntax of the PARENT/CHILD function. A level constraint may be included to specify which level to include in the join. When no values are specified in the function, it uses the default setting (0) and will include all levels. Any other positive integer value will constrain the traversal for that level.

The following JOIN expressions produce the same results.
JOIN [SOXBusEntity] AS [a] ON PARENT([SOXBusEntity])
is equals to
JOIN [SOXBusEntity] AS [a] ON PARENT([SOXBusEntity], 0)
 
For the purpose of all the examples we have the following SOXBusEntity object instances.
entity3
   | ---- entity2
             | ---- entity1
             |
             | ---- entity0
 
Example

A SOXBusEntity type is a parent of a SOXBusEntity type. The syntax expresses a top down traversal and will include all levels in the result set.
SELECT [SOXBusEntity].[Name], [a].[Name]
FROM [SOXBusEntity]
JOIN [SOXBusEntity] AS [a] ON PARENT([SOXBusEntity])
WHERE [SOXBusEntity].[Name] = 'entity3'
 
  • FROM [SOXBusEntity]
    The FROM clause indicates the starting point in the traversal.


  • JOIN [SOXBusEntity] AS [a] ON PARENT([SOXBusEntity])
    This join expresses the relationship between SOXBusEntity and SOXBusEntity. SOXBusEntity is joining with another SOXBusEntity using a PARENT association.
The result of this execution would produce the following result set.

[SOXBusEntity].[Name] [a].[Name]
entity3 entity2
entity3 entity1
entity3 entity0

Example

A SOXBusEntity type is a parent of a SOXBusEntity type. The syntax expresses a bottom up traversal and will include all levels in the result set.

SELECT [SOXBusEntity].[Name], [a].[Name]
FROM [SOXBusEntity]
JOIN [SOXBusEntity] AS [a] ON CHILD([SOXBusEntity])
WHERE [SOXBusEntity].[Name] = 'entity0'
 
  • FROM [SOXBusEntity]
    The FROM clause indicates the starting point in the traversal.


  • JOIN [SOXBusEntity] AS [a] ON PARENT([SOXBusEntity])
    This join expresses the relationship between SOXBusEntity and SOXBusEntity. SOXBusEntity is joining with another SOXBusEntity using a CHILD association.
The result of this execution would produce the following result set.

[SOXBusEntity].[Name] [a].[Name]
entity0 entity3
entity0 entity2

Outer Join

A left outer join is supported in the syntax using OUTER JOIN in place of JOIN. All other behavior of the OUTER JOIN is the same as JOIN

Example

A SOXBusEntity type is a parent of a LossEvent type. Do an outer join of SOXBusEntity parents and children LossEvents but include SOXBusEntity rows which do not have any associated LossEvent children.
SELECT [SOXBusEntity].[Name], [LossEvent].[Name]  
FROM [SOXBusEntity] 
OUTER JOIN [LossEvent] ON PARENT([SOXBusEntity])
 

The result of this execution would produce the following result set, with 'entity2' not having any LossEvent children, it is still listed.

[SOXBusEntity].[Name] [LossEvent].[Name]
entity0 LE001
entity0 LE002
entity1 LE003
entity2  

Indirect (Hierarchical) Join

Indirect Joins allow you to specify a top-down query that considers all paths down the object hierarchy from a parent to a child type, where the child type in the join is not a direct child association. For example, to find all Issues under a Risk, as Issue may be a child of the Risk directly, but also a child of Controls or Test plans. While it would be possible to do an explicit series of JOINs in the FROM clause from Risk to Issue, or Risk to Control then to Issue, it would not be possible or efficient to do them in one SELECT statement. Indirect join syntax can accomplish this type of query in one SELECT statement with certain restrictions on allowed queries.

The syntax for making an indirect join is the same, other than the join predicate which is ...ON ANCESTOR(...)

Example

Find all Issues that are indirectly associated to a single Risk object.
SELECT [SOXIssue].[Name]  
FROM [SOXRisk] JOIN [SOXIssue] ON ANCESTOR([SOXRisk])
WHERE [SOXRisk].[Resource ID] = 1234
 

The result of this query would produce a list of Issues that are related via any path to the Risk with Resource ID = 1234.

Restrictions:

Indirect usage is restricted to queries that meet the following criteria:

  • The query MUST have at least one Resource ID filter in the WHERE clause on the ANCESTOR type (e.g. WHERE [SOXRisk].[Resource ID] = 1234) which scopes to a single ANCESTOR instance.
  • The query may only have one JOIN if using indirect join. Cannot combine multiple joins when using the ANCESTOR keyword.
  • Does not use Outer join. OUTER JOIN syntax (in OpenPages 7.2 and later) and the new ANCESTOR keyword are not supported together.
  • Does not have any fields from the ANCESTOR type in the SELECT clause or WHERE clause with the exception of a single filter on the Resource ID.

Performance:

It is always faster to use a direct query join (ON PARENT/CHILD) rather than indirect (ON ANCESTOR) if the query is only returning direct associations.

Due to the variable nature of the OpenPages database schema, the performance of this query will be impacted by a number of factors such as: "depth" of the indirect query (i.e., the number of hierarchy levels the query must search before finding the child type); the total number of associations beneath the starting Ancestor resource; as well as the complexity of security rules on any involved object types. Depending on these factors, queries may perform slower than expected and introduce more load on the database server.

Indirect queries support the usage of paging mechanisms, however due to the expensive nature of determining the indirect associations at the database level, it is more efficient for the database server to use larger page sizes in the query options. There is ultimately a tradeoff to consider, which is that larger page sizes will consume more system memory on the application server when results are returned. One way to restrict the amount of memory consumed by large numbers of result rows is to limit the columns returned to only required information. Please note that fields of Long String datatype can potentially have large amounts of data that would need to be kept in memory if included in the results.

If you only require indirect children through primary associations, leverage the query option honorPrimary to reduce the number of association paths to be evaluated as only primary parent associations are traversed. This will improve the performance of executing the query for this use case.


WHERE clause

Comparisons permitted in the WHERE clause

Simple comparison predicate, IN predicate, LIKE predicate, IS NULL predicate, boolean conjunction (AND), disjunction (OR), and negation (NOT) of predicates are also supported. The CONTAINS function is also supported for long text field when database indexing has been enabled.

The comparisons for the property types as described in the list below are also supported.
Field Type Supported Operators Supported Literal
String (single) =, <>, [NOT] LIKE, IS [NOT] NULL String
String (IN) [NOT] IN List of Strings
Integer (single) =, <>, >, <, >=, IS [NOT] NULL Integer
Integer (IN) [NOT] IN List of Integers
Float (single) =, <>, >, <, >=, <=, [NOT] LIKE, IS [NOT] NULL Double
Float (IN) [NOT] IN List of Doubles
Date (single) =, <>, >, <, >=, IS [NOT] NULL Date string format
Date (IN) [NOT] IN List of Date string format
Currency (single) =, <>, >, <, >=, <=, [NOT] LIKE, IS [NOT] NULL Double
Currency (IN) [NOT] IN List of Double
Boolean (single) =, <>, >, <, >=, <=, IS [NOT] NULL Boolean
Enum (single) =, <>, [NOT] LIKE, IS [NOT] NULL String
Enum (IN) [NOT] IN List of Strings/Integers
Multi-Enum(single) =, <>, [NOT] LIKE, IS [NOT] NULL String
Multi-Enum (IN) [NOT] IN List of Strings/Integers
Medium String (single) [NOT] CONTAINS, [NOT] LIKE, IS [NOT] NULL String
Large String (single) [NOT] CONTAINS, [NOT] LIKE, IS [NOT] NULL String
Unlimited String (single) [NOT] CONTAINS, [NOT] LIKE, IS [NOT] NULL String
ID (single) =, <>, [NOT] LIKE String
ID (IN) [NOT] IN List of Strings

Currency Field

A currency field is a composite type that is composed of the following values:
  • base amount
  • base currency
  • exchange rate
  • local amount
  • local currency
For the purpose of constraining the result set using a currency field, the base amount will be used as the constraining fields. Other fields are ignored.

Example

A SOXBusEntity type is a parent of a SOXBusEntity type. The syntax expresses a bottom up traversal and will include all levels in the result set.

SELECT [Shared:Shared Currency]
FROM [LevelOne]
WHERE [Shared:Shared Currency] = 5.0
 

Basic Predicate

A basic predicate compares two values. The result is either true or false.

Predicate is true if:

x = y    (x is equal to y)
x <> y   (x is not equal to y)
x < y    (x is less than y)
x > y    (x is greater than y)
x >= y   (x is greater than or equal to y)
x <= y   (x is less than or equal to y)
 
  • For enum or multi-enum field types, a string representing the system name of the enum value can be used.
  • For date field types, the date string format should be used.
  • For currency field types, the base amount of the currency is used as constraints.
  • For large text string field type, the basic predicate clauses are not supported.
  • All other field types are supported.

IN Predicate

The IN predicate compares a value or values with a collection of values. The values for expression1 and expression2 in the IN predicate must be compatible. If NOT is specified, the result is reversed.
  • For date field types, the Date string format needs to be specified.
Example
[Creation Date] IN ('2013-05-16', '2013-05-18')
 
  • For enum or multi-enum field types, a string representing the system name of the enum value delimited by single quotes may be used or the enum value identifier.
Example
by enum value name: [Shared:Shared Drop Down] IN ('New York', 'Rhode Island')

by enum value id: [Shared:Shared Drop Down] IN (4215, 4218)
 
  • For currency field types, the base amount of the currency is used as constraints.
  • For large text string field types, the IN clause is not supported.
  • All other field types are supported.

Like Predicate

The LIKE predicate is used in a WHERE clause to search for a specified pattern in a field value. Single quotes are used to enclose the search pattern. The "%" sign is used to define wildcards (missing letters) both before and after the pattern. If NOT is specified, the result is reversed.
  • For date field types, LIKE predicate is not supported.
  • For currency field types, the base amount of the currency is used for the pattern search.
  • For enum/multi-enum field types, the enum value is used for the pattern search.
  • All other field types are supported.
Example

A SOXBusEntity type is a parent of a SOXBusEntity type. The syntax expresses a bottom up traversal and will include all levels in the result set.

SELECT [Resource ID], [Name]
FROM [SOXBusEntity]
WHERE [Name] LIKE '%entity%'
 

IS NULL Predicate

The NULL predicate tests for null values. The result of a NULL predicate cannot be unknown. If the value of the expression is null, the result is true. If the value is not null, the result is false. If NOT is specified, the result is reversed.
  • All field types are supported.
Example
[Name] IS NULL
[Name] IS NOT NULL
 

Contains Predicate function

A search condition specifies a condition that is true/false. The CONTAINS function predicate requires that an indexing process be activated on the database. If NOT is specified, the result is reversed.
  • Only large text fields are supported by the Contains predicate.
  • All other field types are not supported.
Example
SELECT [Shared:Shared Drop Down]
FROM [LevelOne]
WHERE CONTAINS([Shared:Shared Drop Down], 'some text to search for')
 

GROUP BY clause

This clause MUST contain a comma separated list of one or more column names. All column names referenced in this clause must be a valid system name.

The GROUP BY clause is used only when there are aggregate function columns in the SELECT clause.

Example

 SELECT [OPSS-Issue:Conclusion], COUNT(*)
 FROM [SOXIssue]
 GROUP BY [OPSS-Issue:Conclusion]
 

ORDER BY clause

This clause MUST contain a comma separated list of one or more column names. All column names referenced in this clause must be a valid system name.

Only columns in the SELECT clause MAY be in the ORDER BY clause. Ordering is impacted by collation rules defined by the OpenPages schema.

Naming conventions

All identifiers referencing the name of a column type needs to be delimited by a [ ]. The use of the square bracket allows for names to contain spaces.

Unqualified column names

A column name does not need to be qualified if there is only one type included in the SQL query.
  • Only large text fields are not supported by the Order By clause.
  • All other field types are supported.
Example

The following example demonstrates the column name (Resource ID) which is not qualified.

SELECT [Resource ID]
FROM [SOXBusEntity]
 

Qualified column names

A column name can be formed by qualifying its type name.

Example

The following example demonstrates the column name (Resource ID) which is qualified by its type (SOXBusEntity).

SELECT [SOXBusEntity].[Resource ID]
FROM [SOXBusEntity]
 

Correlation names

A correlation name is an identifier that designates a result table.

Example

The following example demonstrates a column name (Resource ID) that is qualified by a correlation name that is defined on the FROM clause (entity).

SELECT [entity].[Resource ID]
FROM [SOXBusEntity] AS [entity]
 

Escaping

Escaping of characters is supported through the use of a backslash (\) character. The backslash will be used to escape characters within quoted strings in the query.

For example:

  • \' will represent a single-quote(')
  • \\ will represent a backslash (\)
  • Within a LIKE string, \% will represent the literal characters %
  • Within a LIKE string, \_ will represent the literal characters _
  • Within a LIKE string, \? will represent the literal characters ?
  • All other instances of a \ are errors.

Date string format

The date string format supported by the Query Service is 'yyyy-MM-dd'.

Example

The following example demonstrates the date string format for comparing a date field to a literal in the WHERE clause:

SELECT [Creation Date]
FROM [SOXBusEntity]
WHERE [Creation Date] = DATE '2013-05-16'
 

Creating query

This example demonstrates how to create a query object to execute.

Example:
Context context = ...
IServiceFactory serviceFactory = ServiceFactory.getServiceFactory(context);
IQueryService queryService = serviceFactory.createQueryService();

String query = "SELECT [Resource ID], [Name] FROM [SOXIssue]";
IQuery query = queryService.buildQuery(query);
...
 

Setting Query options

Once a query has been created you may optionally change query options using settings on the IQuery before the query is executed. More information on usage of these parameters is on the IQuery interface java documentation.

  • caseInsensitive : true if string-type conditions are case-insensitive or not
  • pageSize : number of rows per page
  • maxRows : total number of rows returned by the query
  • honorPrimary : only include primary associations

Example:
IQuery query = queryService.buildQuery(query);
query.setHonorPrimary(true);
query.setCaseInsensitive(true);
query.setMaxRows(100);
...
 

Binding input parameters

This example demonstrates how to bind a input parameter to a query.

Example:
Context context = ...
IServiceFactory serviceFactory = ServiceFactory.getServiceFactory(context);
IQueryService queryService = serviceFactory.createQueryService();

String query = "SELECT [Resource ID], [Name] FROM [SOXIssue] WHERE [Status] = ?";
ParameterValue param1 = new ParameterValue("Open");

IQuery query = queryService.buildQuery(query);
query.bindParameter(1, param1);
...
 

Executing query

This example demonstrates how to execute a query object.

Example:
Context context = ...
IServiceFactory serviceFactory = ServiceFactory.getServiceFactory(context);
IQueryService queryService = serviceFactory.createQueryService();       

String query = "SELECT [Resource ID], [Name] FROM [SOXIssue]";
IQuery query = queryService.buildQuery(query);
ITabularResultSet resultset = query.fetchRows(0);
...
 

Manipulating the result set

This example demonstrates how to manipulate the result set returned by a query object.

Example:
Context context = ...
IServiceFactory serviceFactory = ServiceFactory.getServiceFactory(context);
IQueryService queryService = serviceFactory.createQueryService();

String query = "SELECT [Resource ID], [Name] FROM [SOXIssue]";
IQuery query = queryService.buildQuery(query);
ITabularResultSet resultset = query.fetchRows(0);
for (IResultSetRow row : resultset) {
        // process rows...
        for (IField field : row) {
                // process fields...
        }
        ...
}
 

Performance tips

You may add indexes to improve performance of running a query. For information on adding an index, please see section Viewing the Configuration and Settings page > Platform folder settings > Reporting Schema folder settings in the OpenPages GRC Platform Administrator's Guide.

Licensed Materials - Property of IBM
© Copyright IBM Corp. 2013, 2018. All Rights Reserved.
US Government Users Restricted Rights - Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp.