ATG & Endeca BR: RQL - Repository Query Language

Repository Query Language

DAF provides a generic language for formulating queries. Because these queries must map to any repository implementation, the queries cannot be expressed in terms of any one back-end implementation (such as SQL or LDAP query syntax). Instead, the queries must be expressed in Repository Query Language (RQL), and the individual repository connectors are responsible for translating those queries into the query syntax expected by the underlying data store.

RQL Overview

RQL is a textual query syntax that is similar to SQL. It describes the set of conditions that must be matched by items of a particular item descriptor. The following is a simple RQL query that matches all items whose age property is greater than 30.

age > 30

Note: This RQL query omits the name of the item descriptor, which is usually implied by the context of the query’s use.

RQL supports all standard comparison operators and logical operators such as AND, OR, and NOT. For example:

age > 30 AND (lastName = "jones" OR paymentOverdue = true)

RQL keywords are case-insensitive — for example, keywords NOT and not are equivalent.

Constants such as 30, true, or jones can represent numbers, boolean, or String values. String values are represented with Java syntax. They must be enclosed by quotes, and escape sequences for special characters or UNICODE characters must use Java escape syntax.

Properties such as age or lastName must be property names as they are defined in the repository. RQL statements specify the conditions that an item must meet in order to be included in the result set. An RQL statement can also specify other directives to apply to the result set, such as ordering results and returning a portion of the result set.

Comparison Queries

Comparison queries are the simplest RQL queries, where a property’s value is compared against another property value, or against a constant. For example:

age > 30

All standard comparison operators can be used:

These operators can be applied to String properties and arguments, where case ordering is determined by lexical order of the Strings.

In general, these operators can only be used on properties that are scalar values. They should not be used on properties that are array or collection values.

Text Comparison Queries

Text comparison queries can be applied to String properties to determine if a portion or all of a property’s value matches a given comparison value. For example:

firstName STARTS WITH "h"

lastName ENDS WITH "son"

phoneNumber CONTAINS "33"

state EQUALS "Utah"

Be sure to enclose the comparison value in double quotes; otherwise, the RQL parser assumes the operand refers to a property name rather than a value.

By default, text comparison queries are case-sensitive. To perform a case-insensitive comparison, use the IGNORECASE directive. For example:

sports CONTAINS IGNORECASE "ball"

You can also make a negative text comparison query:

NOT firstName STARTS WITH IGNORECASE "j"

Note: Negated pattern match queries can cause performance problems. Consider the queries you want to use and plan your database indexes accordingly to avoid table scans. STARTS WITH and EQUALS queries can be optimized easily with database indexes, while other pattern match queries generally cannot be. Case-insensitive pattern matching can also affect the ability of the database to optimize the query.

Property of Property Queries

The queries shown earlier, as well as those described in the Full Text Search Queries section, can be applied to scalar properties. Some repositories support the use of properties that are themselves an item from another (or the same) item descriptor. For example, the address property might point to another item descriptor which itself has properties like city, state, and zip.

Queries can drill down through these properties with a dot notation. For example:

address.zip = "48322"

RQL allows for multiple levels of “property-of-property” expressions. For example:

department.manager.address.state = "NY"

Logical Operators

Query expressions can be combined with AND, OR, and NOT operators. Parentheses can be used to affect grouping. NOT has the highest precedence, AND the next highest precedence, and OR has the lowest precedence. For example, this expression:

name = "joe" OR NOT phone ENDS WITH "7" AND age > 30

is grouped as follows:

(name = "joe" OR ((NOT phone ENDS WITH "7") AND age > 30))

Collection Queries

Logical operators and the MATCH and MATCHES operators (described in the later section Full Text Search Queries) should only be applied to scalar properties. Another set of queries can be applied to arrays or collections of scalar values—for example, properties of type int[], or Set of Strings. To query multi-valued properties, use the operators INCLUDES, INCLUDES ANY, and INCLUDES ALL.

The INCLUDES query matches items where the specified property includes the specified value. For example:

interests INCLUDES "biking"

The INCLUDES query can also match one of a set of items by including the ANY or ALL keyword, followed by a comma-separated set of items that are enclosed in braces. For example:

interests INCLUDES ANY { "biking", "swimming" }

This is equivalent to:

(interests INCLUDES "biking") OR (interests INCLUDES "swimming")

While this:

interests INCLUDES ALL { "biking", "swimming" }

is equivalent to:

(interests INCLUDES "biking") AND (interests INCLUDES "swimming")

Includes Item Queries

Some repositories support properties that are arrays or collections of items belonging to another (or the same) item descriptor. For example, the addresses property might point to an array of items, which themselves have address-related properties.

In this case, RQL allows for a subquery to be defined on these properties. For example:

addresses INCLUDES ITEM (zip = "48322" AND state = "MI")

This query means “find all people whose list of addresses includes at least one address whose zip code is 48322 and whose state is MI”.

Is Null Queries

An IS NULL query can determine whether an expression evaluates to null. For example:

phoneNumber IS NULL

This expression evaluates to true if the phoneNumber is null.

Count Expressions

The COUNT operator can be used to query on the size of a collection property. For example:

COUNT (addresses) > 3

This finds all people whose addresses property contains 4 or more elements.

All Query

An RQL query of ALL returns all items in a particular item descriptor. Use this query with care, because the result set can be very large. Usually this is combined with an ORDER BY or RANGE directive (described below). The RQL query is simply:

ALL

Full Text Search Queries

Some content repositories support the ability to perform full text searches. The formats of the text strings and other search directives vary from repository to repository. However, the basic query looks like this:

MATCH "mars"

This returns those items whose content matches mars in a full text search. (Content repositories allow parts of the item’s data to be designated as “content” for the purposes of display and searching).

Another form of the query allows the full text search to proceed over a particular property:

firstName MATCHES "abr"

Note that MATCH and MATCHES queries apply only to scalar properties.

Both forms of the query allow a special USING directive to pass special instructions to the underlying search engine. The format of this directive depends on the repository and whatever search engine it uses.

For example, to use the Sybase Full-Text Search Specialty Data Store, the query looks like this:

firstName MATCHES "abr" USING "SYBASE_SDS"

To use the Oracle ConText full text search engine, the query looks like this:

firstName MATCHES "abr" USING "ORACLE_CONTEXT"

ID-based Queries

RQL can query items based on their repository IDs. This ability should be used with care, because repository IDs are not portable across repository implementations.

The first query searches for items that match a set of IDs. For example:

ID IN { "0002421", "0002219", "0003244" }

The next ID-based query applies only to content repositories, where items are organized into folders. This query restricts the search to only those items in the specified folders. The folders must be specified by ID:

IN FOLDERS { "10224", "10923", "12332" }

Note that passing in an empty or null set of IDs results in an exception.

Composite IDs can be specified in RQL queries with the following format for integers:

[value1, value2 ... valueN]

String IDs use the format:

["value1", "value2" ... "valueN"]

So a simple comparison query of a composite ID property might look like:

id = ["dept2", "emp345"]

Such a query returns an item with a composite repository ID of dept2:emp345. A query like this returns items with any of the IDs dept2:emp345, dept2:emp346, or dept2:emp347:

ID IN { ["dept2", "emp345"], ["dept2", "emp346"], ["dept2", "emp347"] }

Order By Directives

After a query is defined with the aforementioned query elements, the result is a set of items. The ORDER BY directive orders the results by item properties. For example:

age > 30 ORDER BY firstName

This query returns a result set where items are ordered by the firstName property in ascending order—the default. Results can also be ordered in descending order by adding SORT DESC to the end of the directive:

age > 30 ORDER BY firstName SORT DESC

Results can be ordered by multiple properties. For example:

age > 30 ORDER BY lastName, firstName SORT DESC

This orders results by lastName. If multiple results have the same lastName, within their group they are ordered by firstName in descending order.

A further directive, CASE IGNORECASE, can specify case-insensitive sorting:

age > 30 ORDER BY firstName SORT DESC CASE IGNORECASE

Note that you can omit the tokens SORT and CASE, unless you use parameters for the ASC/DESC or USECASE/IGNORECASE tokens.

Range Directives

Many queries have the potential for returning large result sets. Most applications do not want to display the entire result set –they might want to display just the first 10 results. Or they might want to page through the results, showing results 0-9, then results 10-19, and so on.

The RANGE directive is used to specify this in the RQL query. The RANGE directive must come after the ORDER BY directive (if any). It has three forms. The first is the most common:

age > 30 RANGE +10

This causes only the first 10 results to be returned. If the result set is already less than 10, all results are returned.

The next form of the RANGE directive allows the results to start at a specified index:

age > 30 RANGE 10+

This causes the first 10 results to be skipped, and the remaining results to be returned.

The final form of the RANGE directive combines the above two forms, and is often used for paging:

age > 30 RANGE 40+10

This skips the first 40 results, then returns up to the next 10 results.

Parameters in Queries

In all of the previous examples, the queries contain hard-coded constants, such as 30 or joe. Most of the time, the actual values used in the query are unknown at the time the RQL statement is written. In these cases, the values may be substituted with parameter expressions. For example:

age > ?0 AND firstName CONTAINS ?1 RANGE ?2+10

Every ?{number} represents a parameterized value that is filled in when the query is executed. How those values are supplied depends on the application performing the query. In the case of entity EJBs, where RQL queries are used to represent finder methods, the parameters are filled in from the arguments of the finder methods. For example, ?0 is substituted with the value of the first argument, ?1 with the second, and so on.

Parameter expressions can generally be used wherever constant values are used, including in RANGE expressions. However, parameter expressions may not be used in array expressions, such as ID IN or IN FOLDERS queries. Also, parameter expressions may not be used as substitutes for property names; all property names must be hard-coded into the RQL query when it is written.

Parameterized Field Queries

When a parameterized query is used, each numbered placeholder is substituted with the value of an entire object at runtime. However, it is sometimes more useful to substitute in the value of one of the object’s fields, rather than the entire value of the object. A parameterized field query specifies this with the syntax ?{number}.{fieldName}. For example:

name = ?0.name AND age = ?0.age

In this example, only one object is passed into the query at runtime. However, this object is expected to have two public member variables called name and age. The query extracts the values of these member variables from the object and substitute those values for the ?0.name and ?0.age parameters. Note that the fields must be public member variables of the object that is passed in, not JavaBean properties. For example, the following object can be passed in to the query:

public class QuerySpecifier {

public String name;

public int age;

}

Parameterized Field Queries are used most often for entity EJBs, which allow primary key classes to contain multiple fields. In this case, only one object is passed to the query (the primary key), but if the primary key spans multiple database fields, the primary key object contains the values of those fields in its public member variables.

RQL Examples

The following example shows how you might use a parameter expression in Java code. It creates an RqlStatement and uses it in executing a query to find person repository items where the value of the age property is greater than 23.

RepositoryView view = repository.getView("person");

RqlStatement statement = RqlStatement.parseRqlStatement("age > ?0");

Object params[] = new Object[1];

params[0] = new Integer(23);

RepositoryItem [] items = statement.executeQuery (view, params);

Here is another example that demonstrates a text comparison query:

RqlStatement statement = RqlStatement.parseRqlStatement("lastName STARTS WITH ?0");

Object params[] = {new String("m")};

items = statement.executeQuery (view, params);

Note how in the text comparison queries the comparison value "m" is enclosed in double quotes; otherwise, the RQL parser assumes the comparison term refers to a property name rather than a property value.

RQL Grammar

(Query)

Comparison, ID IN, IN FOLDERS, ALL, TextSearch, PropertyTextSearch, INCLUDES ITEM, IS NULL

RQL Grammar Examples

OR:: Query OR Query ...

AND:: Query AND Query ...

NOT:: NOT Query

Comparison:: Expression ComparisonOperator Expression

ComparisonOperator:: = | != | < | <= | > | >= | INCLUDES ANY | INCLUDES ALL | INCLUDES | STARTS WITH [IGNORECASE] | ENDS WITH [IGNORECASE] | CONTAINS [IGNORECASE]

IdIn:: ID IN StringArray

InFolders:: IN FOLDERS StringArray

All:: ALL

TextSearch:: MATCH StringLiteral [USING StringLiteral]

PropertyTextSearch:: ObjectExpression MATCHES StringLiteral [USING StringLiteral]

IncludesItem:: Expression INCLUDES ITEM ( Query )

Expression:: CountExpression | ObjectExpression | ParameterExpression | ConstantExpression

CountExpression:: COUNT ( ObjectExpression | ParameterExpression | ConstantExpression )

ObjectExpression:: PropertyName | ObjectExpression.PropertyName | ObjectExpression[Expression]

PropertyName:: <Java identifier>

ParameterExpression:: ?<Parameter number>[.<Field name>]

ConstantExpression:: StringLiteral | IntegerLiteral | DoubleLiteral | BooleanLiteral | ArrayLiteral

StringLiteral:: “<Java string literal>“

The string literal uses the Java format, including escape characters (including octal and Unicode), and must be enclosed in double quotes.

IntegerLiteral:: <Java integer literal>

DoubleLiteral:: <Java double literal>

BooleanLiteral:: true | false

ArrayLiteral:: { ConstantExpression , ... }

StringArray:: { StringLiteral , ... }

OrderByClause:: ORDER BY PropertyName [[SORT] [ ASC | DESC]] [CASE [ IGNORECASE | USECASE]]

The SORT ASC/DESC directives are optional and default to SORT ASC. The CASE IGNORECASE/USECASE directives are optional and default to CASE USECASE.

RangeClause:: RANGE <Starting Index> + <Count>

ATG & Endeca BR

sexta-feira, 3 de julho de 2015

RQL - Repository Query Language