Since ModeShape 4.0:

• Support for the specifying whether nulls appear first or last in the results using the "ORDER BY ... NULLS FIRST" or "ORDER BY ... NULLS LAST" expressions. For detail, see the grammar for ordering.

• Additional dynamic operand "CHILDCOUNT(selectorName)" that enables placing constraints on the number of children. Although there are several ways to use it, this is especially effective for finding nodes with no children, which cannot otherwise be done in JCR-SQL2 (it is not possible to use an anti-join with CHILDNODE join criteria). This dynamic operand can be used in a manner similar to "NAME(selectorName)" and "LOCALNAME(selectorName)" that are defined by JCR-SQL2. Note in each of these cases, the selectorName is optional if there is only one selector in the query. For detail, see the grammar for dynamic operands.

• Support for the "mode:id" pseudo-column that, like other ModeShape-specific pseudocolumns, can be used in the SELECT, equijoin, and WHERE clauses. This pseudo-column makes it possible to get or constrain the nodes' identifier.

Since ModeShape 3.x:

• Support for the "FULL OUTER JOIN" and "CROSS JOIN" join types, in addition to the "LEFT OUTER JOIN", "RIGHT OUTER JOIN" and "INNER JOIN" types defined by JCR-SQL2. Note that "JOIN" is a shorthand for "INNER JOIN". For detail, see the grammar for joins.

• Support for the UNION, INTERSECT, and EXCEPT set operations on multiple result sets to form a single result set. As with standard SQL, the result sets being combined must have the same columns. The UNION operator combines the rows from two result sets, the INTERSECT operator returns the difference between two result sets, and the EXCEPT operator returns the rows that are common to two result sets. Duplicate rows are removed unless the operator is followed by the ALL keyword. For detail, see the grammar for set queries.

• Removal of duplicate rows in the results, using "SELECT DISTINCT ..." expression. For detail, see the grammar for queries.

• Limiting the number of rows in the result set with the "LIMIT count" clause, where count is the maximum number of rows that should be returned. This clause may optionally be followed by the "OFFSET number" clause to specify the number of initial rows that should be skipped. For detail, see the grammar for limits and offsets.

• Additional dynamic operands "DEPTH(selectorName)" and "PATH(selectorName)" that enable placing constraints on the node depth and path, respectively. These dynamic operands can be used in a manner similar to "NAME(selectorName)" and "LOCALNAME(selectorName)" that are defined by JCR-SQL2. Note in each of these cases, the selectorName is optional if there is only one selector in the query. For detail, see the grammar for dynamic operands.

• Additional dynamic operand "REFERENCE(selectorName.propertyName)" and "REFERENCE(selectorName)" that enables placing constraints on one or any of the reference properties, respectively, and which can be used in a manner similar to the standard dynamic operand "PropertyValue(selectorName.propertyName)". Note in each of these cases, the selectorName is optional if there is only one selector in the query, and that the propertyName can be excluded if the constraint should apply to all reference properties. For detail, see the grammar for dynamic operands.

• Support for the "IN" and "NOT IN" clauses to more easily and concisely supply multiple of discrete static operands. For example, "WHERE ... [my:type].[prop1] IN (3,5,7,10,11,50) ...". For detail, see the grammar for set constraints.

• Support for the "BETWEEN" clause to more easily and concisely supply a range of discrete operands. For example, "WHERE ... [my:type].[prop1] BETWEEN 3 EXCLUSIVE AND 10 ...". For detail, see the grammar for between constraints.

• Support for simple arithmetic in numeric-based criteria and order-by clauses. For example, "... WHERE SCORE(type1) + SCORE(type2) > 1.0" or "... ORDER BY (SCORE(type1) * SCORE(type2)) ASC, LENGTH(type2.property1) DESC". For detail, see the grammar for order-by clauses.

• Support for (non-correlated) subqueries in the WHERE clause, wherever a static operand can be used. Subqueries can even be used within another subquery. All subqueries must return a single column, and each row's single value will be treated as a literal value. If the subquery is used in a clause that expects a single value (e.g., in a comparison), only the subquery's first row will be used. If the subquery is used in a clause that allows multiple values (e.g., "IN (...)"), then all of the subquery's rows will be used. For example, this expression "WHERE ... [my:type].[prop1] IN ( SELECT [my:prop2] FROM [my:type2] WHERE [my:prop3] < '1000' ) AND ..." will use the results of the subquery as the literal values in the IN clause. See the subqueries section for more information.

• Support for several pseudo-columns ("jcr:path", "jcr:score", "jcr:name", "mode:localName", and "mode:depth") that can be used in the SELECT, equijoin, and WHERE clauses. These pseudo-columns make it possible to return location-related and score information within the QueryResult's rows. They also make queries look more like SQL, and thus may be more friendly and easier to use in existing SQL-aware client applications. See the pseudo-columns section for more information.

• Support for NOT LIKE as an operator in comparison criteria, and which is equivalent to wrapping a LIKE comparison criteria in a NOT(...) clause.

• Support for RELIKE(...) operator that tests patterns stored in property values against a supplied string parameter.

Queries

The top-level rule for ModeShape's extended JCR-SQL2 grammar is QueryCommand, which consists of both Query and SetQuery:

QueryCommand ::= Query | SetQuery

SetQuery ::= Query ('UNION'|'INTERSECT'|'EXCEPT') ['ALL'] Query
                 { ('UNION'|'INTERSECT'|'EXCEPT') ['ALL'] Query }

Query ::= 'SELECT' ['DISTINCT'] columns
          'FROM' Source
          ['WHERE' Constraint]
          ['ORDER BY' orderings]
          [Limit]

ModeShape adds the concept of a set query, which is a query that performs a union, intersection, or complement of the results of two other queries. Set queries are common in SQL (which is essentially a set manipulation language) and are a very useful tool that would otherwise require significant processing of the results of multiple queries by the application. By supporting set queries, the application merely needs to declare that set operation be performed, and ModeShape will perform all the work before returning the results.

ModeShape also adds the ability to use "SELECT DISTINCT", which eliminates duplicate rows in a manner similar to SQL.

Source

A source is a named set of tuples, which in ModeShape corresponds to the nodes of a particular named node type. In other words, a source is equivalent to a table in a relational database. The available columns of a source are the named properties declared on the node type.

In the JCR-SQL2 grammar, a source is either a selector (a named node type) or a join specification:

Source ::= Selector | Join

Selector ::= nodeTypeName ['AS' selectorName]

nodeTypeName ::= Name
selectorName ::= /* A string that contains only SQL-legal characters,
                    and which can be used elsewhere in the query to
                    refer to the selector. */

See the Name rule below.

Joins

The JCR 2.0 specification does include joins in the standard JCR-SQL2 grammar, though the only defined types of joins included inner, left outer, and right outer joins. Because SQL also defines the useful full outer and cross join types, ModeShape adds support for these.

Join ::= left [JoinType] 'JOIN' right 'ON' JoinCondition
         /* If JoinType is omitted INNER is assumed. */

left ::= Source
right ::= Source

JoinType ::= Inner | LeftOuter | RightOuter | FullOuter | Cross

Inner ::= 'INNER' ['JOIN']

LeftOuter ::= 'LEFT JOIN' | 'OUTER JOIN' | 'LEFT OUTER JOIN'

RightOuter ::= 'RIGHT OUTER' ['JOIN']

RightOuter ::= 'FULL OUTER' ['JOIN']

RightOuter ::= 'CROSS' ['JOIN']

JoinCondition ::= EquiJoinCondition |
                  SameNodeJoinCondition |
                  ChildNodeJoinCondition |
                  DescendantNodeJoinCondition

Each of the four kinds of join conditions are described below.

EquiJoinCondition ::= selector1Name'.'property1Name
                      '=' selector2Name'.'property2Name

selector1Name ::= selectorName
selector2Name ::= selectorName
property1Name ::= propertyName
property2Name ::= propertyName

propertyName ::= Name

SameNodeJoinCondition ::= 'ISSAMENODE(' selector1Name
                                    ',' selector2Name
                                   [',' selector2Path] ')'

selector1Name ::= selectorName
selector2Name ::= selectorName
selector2Path ::= Path

ChildNodeJoinCondition ::= 'ISCHILDNODE('
                            childSelectorName ','
                            parentSelectorName ')'

childSelectorName ::= selectorName
parentSelectorName ::= selectorName

DescendantNodeJoinCondition ::= 'ISDESCENDANTNODE('
                                 descendantSelectorName ','
                                 ancestorSelectorName ')'

descendantSelectorName ::= selectorName
ancestorSelectorName ::= selectorName

Constraints

The Query rule defined above included a "WHERE" clause that can define multiple constraints on the nodes included in the results. The standard JCR-SQL2 grammar defined several such constraints, including and, or, not, comparison, property existence, full-text search, same-node, child-node, and descendant-node constraints. ModeShape supports all of these, but adds two others: between and set constraints.

Constraint ::= ConstraintItem | '(' ConstraintItem ')'

ConstraintItem ::= And | Or | Not
                   Comparison | Between |
                   PropertyExistence |
                   SetConstraint |
                   FullTextSearch |
                   SameNode |
                   ChildNode |
                   DescendantNode |
                   Relike

Each of these types of constraints are described below.

And ::= constraint1 'AND' constraint2

constraint1 ::= Constraint
constraint2 ::= Constraint

Or ::= constraint1 'OR' constraint2

constraint1 ::= Constraint
constraint2 ::= Constraint

Not ::= 'NOT' constraint

constraint ::= Constraint

Comparison ::= DynamicOperand Operator StaticOperand

Operator ::= '=' | '!=' | '<' | '<=' | '>' | '>=' | 'LIKE' | 'NOT LIKE'

Between ::= DynamicOperand ['NOT'] 'BETWEEN'
                lowerBound ['EXCLUSIVE'] 'AND'
                upperBound ['EXCLUSIVE']

lowerBound ::= StaticOperand
upperBound ::= StaticOperand

PropertyExistence ::= [selectorName'.']propertyName 'IS' ['NOT'] 'NULL'

                      /* If only one selector exists in this query,
                         explicit specification of the selectorName
                         preceding the propertyName is optional */

SetConstraint ::= [selectorName '.']propertyName ['NOT'] 'IN'
                    '(' firstStaticOperand
                        {',' additionalStaticOperand }
                    ')'

                   /* If only one selector exists in this query,
                      explicit specification of the selectorName
                      preceding the propertyName is optional */

firstStaticOperand ::= StaticOperand
additionalStaticOperand ::= StaticOperand

SELECT * FROM [acme:taggable] as tagged
WHERE tagged.[acme:tagName] IN ('tag1','tag2','tag3','tag4')

SELECT * FROM [acme:taggable] as tagged
WHERE tagged.[acme:tagName] = 'tag1'
   OR tagged.[acme:tagName] = 'tag2'
   OR tagged.[acme:tagName] = 'tag3'
   OR tagged.[acme:tagName] = 'tag4'

FullTextSearch ::= 'CONTAINS('
                      ([selectorName'.']propertyName | selectorName'.*')
                      ',' ''' fullTextSearchExpression''' ')'

                   /* If only one selector exists in this query,
                      explicit specification of the selectorName
                      preceding the propertyName is optional */

fullTextSearchExpression ::= FulltextSearch

SELECT * FROM [acme:taggable] as tagged
WHERE CONTAINS(tagged.[acme:tagName],'foo')

SameNode ::= 'ISSAMENODE(' [selectorName ','] Path ')'

              /* If only one selector exists in this query,
                 explicit specification of the selectorName
                 preceding the propertyName is optional */

ChildNode ::= 'ISCHILDNODE(' [selectorName ','] Path ')'

              /* If only one selector exists in this query,
                 explicit specification of the selectorName
                 preceding the propertyName is optional */

DescendantNode ::= 'ISDESCENDANTNODE(' [selectorName ','] Path ')'

              /* If only one selector exists in this query,
                 explicit specification of the selectorName
                 preceding the propertyName is optional */

SELECT *
FROM [my:country] AS country
WHERE RELIKE("Principality of Monaco", country.[my:namePattern]);

Path and name

Many of the rules above have used paths and names, and the rules for these are defined as follows:

Name ::= '[' quotedName ']' | '[' simpleName ']' | simpleName

quotedName ::= /* A JCR Name (see the JCR specification) */
simpleName ::= /* A JCR Name that contains only SQL-legal
                  characters (namely letters, digits, and underscore) */

Path ::= '[' quotedPath ']' | '[' simplePath ']' | simplePath

quotedPath ::= /* A JCR Path that contains non-SQL-legal characters */
simplePath ::= /* A JCR Path (rather Name) that contains only SQL-legal
                    characters (namely letters, digits, and underscore) */

Note that JCR-SQL2 surrounds identifiers with square brackets (e.g., '[' and ']'), allowing names to contain a ':' character needed with namespaced names. If the names or paths only contain valid SQL characters, then they do not need to be quoted.

Static operand

In the standard JCR-SQL2 grammar, a static operand appears on the right-hand side of an operator, and represents an expression whose value can be determined by static analysis of the query (e.g., when the query is parsed). In particular, a static operand in the standard JCR-SQL2 grammar comprised of either a literal value or a variable.

In SQL, however, the expression that appears on the right-hand side of an operator is not always able to be determined at query parse time. An example is a subquery, which appears on the right hand side but obviously can only be evaluated into values during query execution time. Since standard JCR-SQL2 does not include any such features, the term "static operand" is technically valid.

In addition to literal values and variables, ModeShape also supports subqueries appearing on the right-hand side of an operator. So this grammar continues to use the "static operand" term for easy comparison with the standard JCR-SQL2 grammar, but the term has a different (and expanded) semantic than in the standard grammar.

Therefore, the rules for what ModeShape allows on the right-hand side of an operator in a constraint is as follows:

StaticOperand ::= Literal | BindVariableValue | Subquery

Literal ::= CastLiteral | UncastLiteral

CastLiteral ::= 'CAST(' UncastLiteral ' AS ' PropertyType ')'

PropertyType ::= 'STRING' | 'BINARY' | 'DATE' |
                 'LONG' | 'DOUBLE' | 'DECIMAL' |
                 'BOOLEAN' | 'NAME' | 'PATH' |
                 'REFERENCE' | 'WEAKREFERENCE' |
                 'URI'

UncastLiteral ::= UnquotedLiteral |
                  ''' UnquotedLiteral ''' |
                  '"' UnquotedLiteral '"'

UnquotedLiteral ::= /* String form of a JCR Value,
                       as defined in the JCR specification */

where the grammar rules for BindVariableValue and Subquery are defined below.

BindVariableValue ::= '$'bindVariableName

bindVariableName ::= /* A string that conforms to the JCR Name syntax,
                        though the prefix does not need to be a
                        registered namespace prefix. */

SELECT * FROM [acme:taggable] as tagged
WHERE tagged.[acme:tagName] = $tagValue

// Obtain the query manager for the session via the workspace ...
javax.jcr.Session session = // ...
javax.jcr.query.QueryManager mgr = session.getWorkspace().getQueryManager();

// Create a query object ...
String language = ...
String expression = ...
javax.jcr.query.Query query = queryManager.createQuery(expression,language);

// Bind a value to the variable ...
Value tag = session.getValueFactory().create("foo");
query.bindVariable("tagValue",tag);

// Execute the query and get the results ...
javax.jcr.query.QueryResult result = query.execute();

Subquery ::= '(' QueryCommand  ')' |
             QueryCommand

WHERE ... [my:type].[prop1] IN
   ( SELECT [my:prop2] FROM [my:type2]
     WHERE  [my:prop3] < '1000' )
AND ...

WHERE ... [my:type].[prop1] =
   ( SELECT [my:prop2] FROM [my:type2]
     WHERE  [my:prop3] < '1000' )
AND ...

Dynamic operand

In various constraints described above, the dynamic operand appears on the left-hand side of an operator, and signifies that the values can only be determined when the query is evaluated.

The standard JCR-SQL2 grammar defines seven kinds of dynamic operands: property value, length, node name, node local name, full-text search score, lowercase, and uppercase.

ModeShape supports all these types, but adds support for four more: reference value, node path, node depth, and simple arithmetic clauses. ModeShape also allows the dynamic operand to be surrounded by parentheses, which is sometimes convenient for complex queries.

The DynamicOperand rule in ModeShape's extended grammar is:

DynamicOperand ::= PropertyValue | ReferenceValue |
                   Length | NodeName | NodeLocalName |
                   NodePath | NodeDepth | ChildCount |
                   FullTextSearchScore |
                   LowerCase | UpperCase |
                   Arithmetic |
                   '(' DynamicOperand ')'

Each of these types of dynamic operands is described in the following subsections.

PropertyValue ::= [selectorName'.'] propertyName

                /* If only one selector exists in this query,
                   explicit specification of the selectorName
                   preceding the propertyName is optional. */

SELECT * FROM [acme:taggable] as tagged
WHERE tagged.[acme:tagNames] = 'foo'

ReferenceValue ::= 'REFERENCE(' selectorName '.' propertyName ')' |
                   'REFERENCE(' selectorName ')' |
                   'REFERENCE()' |

                /* If only one selector exists in this query,
                   explicit specification of the selectorName
                   preceding the propertyName is optional.
                   Also, the property name may be excluded
                   if the constraint should apply to any
                   reference property.*/

SELECT * FROM [nt:base]
WHERE REFERENCE() IN ('id1','id2','id3')

SELECT * FROM [nt:base]
WHERE REFERENCE() IN (
  SELECT [jcr:uuid] FROM [nt:base] AS refd
  WHERE ISDESCENDANTNODE(refd,'/foo/bar/baz')
  )

Length ::= 'LENGTH(' PropertyValue ')'

NodeName ::= 'NAME(' [selectorName] ')'

                /* If only one selector exists in this query,
                   explicit specification of the selectorName
                   is optional */

NodeLocalName ::= 'LOCALNAME(' [selectorName] ')'

                /* If only one selector exists in this query,
                   explicit specification of the selectorName
                   is optional */

NodeDepth ::= 'DEPTH(' [selectorName] ')'

                /* If only one selector exists in this query,
                   explicit specification of the selectorName
                   is optional */

NodePath ::= 'PATH(' [selectorName] ')'

                /* If only one selector exists in this query,
                   explicit specification of the selectorName
                   is optional */

NodeDepth ::= 'CHILDCOUNT(' [selectorName] ')'

                /* If only one selector exists in this query,
                   explicit specification of the selectorName
                   is optional */

FullTextSearchScore ::= 'SCORE(' [selectorName] ')'
                /* If only one selector exists in this query,
                   explicit specification of the selectorName
                   is optional */

LowerCase ::= 'LOWER(' DynamicOperand ')'

UpperCase ::= 'UPPER(' DynamicOperand ')'

Arithmetic ::= DynamicOperand ('+'|'-'|'*'|'/') DynamicOperand

SELECT * FROM [acme:type1] AS type1
         JOIN [acme:type2] as type2 ON type1.prop1 < type2.prop2
         WHERE SCORE(type1) + SCORE(type2) > 1.0

SELECT * FROM [acme:type1] AS type1
         JOIN [acme:type2] as type2 ON type1.prop1 < type2.prop2
         ORDER BY ( SCORE(type1) - SCORE(type2) ) ASC,
                  LENGTH(type2.prop3) DESC

Cast ::= 'CAST(' PropertyValue ' AS ' PropertyType ')'

SELECT * FROM [node] as x  where CAST(x.stringProperty AS DOUBLE) <= 20.50

Ordering

The "ORDER BY" clause defined by the standard JCR-SQL2 grammar allows the order of the results to be dictated by the values evaluated at execution time based upon one or more dynamic operands. The rule for the expression is as follows:

orderings ::= Ordering {',' Ordering}

Ordering ::= DynamicOperand [Order] [NullOrder]

Order ::= 'ASC' | 'DESC'

NullOrder ::= 'NULLS FIRST' | 'NULLS LAST'

As with SQL, the "ASC" qualifier specifies that the ordering should be in ascending order, and is the default; likewise, the "DESC" qualifier specifies that the ordering should be in descending order.

The "NULLS FIRST" and "NULLS LAST" qualifiers specify whether rows with null values in the columns specified by the dynamic operand
should appear first (before rows with non-null values) or last (after rows with non-null values). If the null ordering is not specified then the handling of the null values is:

• NULLS LAST if the sort is ASC

• NULLS FIRST if the sort is DESC

Columns

The standard JCR-SQL2 grammar allows a query to include in the "SELECT" clause which property values should be returned and included in the results:

columns ::= (Column ',' {Column}) | '*'

Column ::= ([selectorName'.']propertyName ['AS' columnName]) |
           (selectorName'.*')

        /* If only one selector exists in this query,
           explicit specification of the selectorName
           is optional */

selectorName ::= Name
propertyName ::= Name
columnName ::= Name

When "*" is used for the list of selected columns, the result set is expected to minimally include, for each selector, a column for each single-valued non-residual property of the selector's node type, including those explicitly declared on the node type and those inherited from the node's supertypes.

For example, the result set for the following query would contain at least the '[jcr:primaryType]' column, since it is the only single-valued, non-residual property defined on the '[nt:base]' node type. The '[jcr:mixinTypes]' property is also non-residual, but the results need not include it since it's multi-valued.

SELECT * FROM [nt:base]

If there are multiple selectors, then "SELECT *" will include all of the selectable columns from each selector's node type. However, it's possible to request all of the selectable columns from some of the selectors, using the form. For example:

SELECT type1.*
FROM [acme:type1] AS type1
JOIN [acme:type2] as type2 ON type1.prop1 < type2.prop2

Note, however, that although only single-valued, non-residual properties are included when "*" is used in the SELECT clause, it is possible to explicitly include residual properties. For example, the following query finds all nodes that have at least one "foo" value for the 'acme:tagNames' property:

SELECT [acme:tagNames] AS tagName
FROM [nt:base] WHERE tagName = 'foo'

Limit and offset

Neither the standard JCR-SQL2 grammar or the JCR API itself provide support for limiting the rows that are returned in the results. This is a common need, especially for applications that paginate the results, where each page shows a subset of the results.

Because this is such an essential feature that can't be accomplished any other way, ModeShape adds support for specifying the maximum number of rows to return, and optionally specifying the number of initial rows that should be skipped. The ModeShape extension is follows the SQL syntax:

Limit ::= 'LIMIT' count [ 'OFFSET' offset ]
count ::= /* Positive integer value */
offset ::= /* Non-negative integer value */

The LIMIT clause is entirely optional, and if absent does not limit the result set rows in any way. However, if the "LIMIT count" clause is used, then the result set will contain no more than count rows. This LIMIT clause may optionally be followed by the "OFFSET number" clause, where number is the number of initial rows that should be skipped before the rows are included in the results.

Pseudo-columns

The design of the JCR-SQL2 query language makes fairly heavy use of functions, including SCORE(), NAME(), LOCALNAME(), and various constraints. ModeShape adds several more useful functions, such as PATH() and DEPTH(), that follow the same patterns.

However, these functions have several disadvantages. First, they make the JCR-SQL2 language less "SQL-like", since SQL-92 and -99 don't define similar kinds of functions. (There are aggregate functions, like COUNT, SUM, etc., but they operate on a particular column in all tuples and are therefore more dissimilar than similar.) This means that applications that use SQL and SQL-like query languages are less likely to be able to build and issue JCR-SQL2 queries.

A second disadvantage of these functions is that JCR-SQL2 does not allow them to be used within the SELECT clause. As a result, the location-related and score information cannot be included as columns of values in the QueryResult rows. Instead, a client can only access this information by obtaining the Node object(s) for each row. Relying upon both the result set and additional Java objects makes it difficult to use the JCR query system. It also makes certain kinds of applications impossible.

For example, ModeShape's JDBC driver is designed to enable JDBC-aware applications to query repository content using JCR-SQL2 queries. The standard JDBC API cannot expose the Node objects, so the only way to return the path-related and score information is through additional columns in the result. While such columns could always "magically" appear in the result set, doing this is not compatible with JDBC applications that dynamically build the SELECT clauses of queries based upon database metadata. Such applications require the columns to be properly described in database metadata, and the columns need to be used within queries.

ModeShape attempts to solve these issues by directly supporting a number of "pseudo-columns" within JCR-SQL2 queries, wherever columns can be used. These "pseudo-columns" include:

• jcr:score is a column of type DOUBLE that represents the full-text search score of the node, which is a measure of the node's relevance to the full-text search expression. ModeShape does compute the scores for all queries, though the score for rows in queries that do not include a full-text search criteria may not be reliable.

• jcr:path is a column of type PATH that represents the normalized path of a node, including same-name siblings. This is the same as what would be returned by the getPath() method of Node. Examples of paths include "/jcr:system" and "/foo/bar3".

• jcr:name is a column of type NAME that represents the node name in its namespace-qualified form using namespace prefixes and excluding same-name-sibling indexes. Examples of node names include "jcr:system", "jcr:content", "ex:UserData", and "bar".

• mode:localName is a column of type STRING that represents the local name of the node, which excludes the namespace prefix and same-name-sibling index. As an example, the local name of the "jcr:system" node is "system", while the local name of the "ex:UserData3" node is "UserData".

• mode:depth is a column of type LONG that represents the depth of a node, which corresponds exactly to the number of path segments within the path. For example, the depth of the root node is 0, whereas the depth of the "/jcr:system/jcr:nodeTypes" node is 2.

• mode:id is a column of type STRING that represents the identifier of a node as returned by javax.jcr.Node.getIdentifier().

All of these pseudo-columns can be used in the SELECT clause of any JCR-SQL2 query, and their use defines whether such columns appear in the result set. In fact, all of these pseudo-columns will be included when "SELECT *" clauses in JCR-SQL2 queries are expanded by the query engine. This means that every node type (even mixin node types that have no properties and are essentially markers) are represented by a queryable table with at least one column. However, unlike the older JCR-SQL query language, these pseudo-columns are never included in the result unless explicitly included or implicitly included with the "SELECT *" clause.

Like any other column, all of these pseudo-columns can be also be used in the WHERE clause of any JCR-SQL2 query, even if they are not included in the SELECT clause. They can be used anywhere that a regular column can be used, including within constraints and dynamic operands. ModeShape will automatically rewrite queries that use pseudo-columns in the dynamic operands of constraints to use the corresponding function, such as SCORE(), PATH(), NAME(), LOCALNAME(), and DEPTH(). Additionally, any property existence constraint using these pseudo-columns will always evaluate to 'true' (and thus ModeShape's query optimizer will always remove such constraints from the query plan).

The "jcr:path" pseudo-column may also be used on both sides of an equijoin constraint clause. For example, equijoin expressions similar to:

 ... selector1.[jcr:path] = selector2.[jcr:path] ...

will be automatically rewritten by ModeShape's optimizer to the following form:

 ... ISSAMENODE(selector1,selector2) ...

As with regular columns, the pseudo-columns must be qualified with the selector name if the query contains more than one selector.

The basics

One of the simplest JCR-SQL2 queries finds all nodes in the current workspace of the repository:

SELECT * FROM [nt:base]

This query will return a result set containing the "jcr:primaryType" column, since the nt:base node type defines only one single-valued, non-residual property called "jcr:primaryType".

Since our query used "SELECT *", ModeShape also includes the five non-standard pseudo-columns mentioned above: "jcr:path", "jcr:score", "jcr:name", "mode:localName", and "mode:depth". These columns are very convenient to have in the results, but also make certain criteria much easier than with the corresponding standard or ModeShape-specific functions.

Queries can explicitly specify the columns that are to be returned in the results. The following query is very similar to the previous query and will return the same rows, but the result set will have only a single column and will not include any of the pseudo-columns:

SELECT [jcr:primaryType] FROM [nt:base]

The following query will return the same rows as in the previous two queries, but the SELECT clause explicitly includes only two of the pseudo-columns for the path and depth (which are computed from the nodes' locations):

SELECT [jcr:primaryType], [jcr:path], [mode:depth] FROM [nt:base]

In JCR-SQL2, a table representing a particular node type will have a column for each of the node type's property definitions, including those inherited from supertypes. For example, the nt:file node type, its nt:hierarchyNode supertype, and the mix:created mixin type are defined using the CND notation as follows:

[mix:created] mixin
  - jcr:created (date) protected
  - jcr:createdBy (string) protected

[nt:hierarchyNode] > mix:created abstract

[nt:file] > nt:hierarchyNode
  + jcr:content (nt:base) primary mandatory

Therefore, the table representing the nt:file node type will have 3 columns: the "jcr:created" and "jcr:createdBy" columns inherited from the mix:created mixin node type (via the nt:hierarchyNode node type), and the "jcr:primaryType" column inherited from the nt:base node type, which is the implicit supertype of the nt:hierarchyNode (and all node types).

ModeShape adheres to this behavior with the exception that a "SELECT *" will result in the additional pseudo-columns. Thus, this next query:

SELECT * FROM [nt:file]

is equivalent to this query:

SELECT [jcr:primaryType], [jcr:created], [jcr:createdBy],
       [jcr:path], [jcr:name], [jcr:score], [mode:localName], [mode:depth]
FROM [nt:file]

Using columns in constraints

Next, let's look at a query that selects some of the available columns from the nt:file table and uses a constraint to ensure the resulting file nodes have names that end in '.txt':

SELECT [jcr:primaryType], [jcr:created], [jcr:createdBy], [jcr:path] FROM [nt:file]
WHERE LOCALNAME() LIKE '%.txt'

ModeShape also supports placing criteria against the mode:localName pseudo-column instead of using the LOCALNAME() function. Such a query is equivalent to the previous query and will produce the exact same results:

SELECT [jcr:primaryType], [jcr:created], [jcr:createdBy], [jcr:path]
FROM [nt:file]
WHERE [mode:localName] LIKE '%.txt'

Although this query looks much more like SQL, the use of the '' and '' characters to quote the identifiers is not typical of a SQL dialect. ModeShape actually supports the using double-quote characters and square braces interchangeably around identifiers (although they must match around any single identifier). Again, this next query, which looks remarkably like any SQL-92 or -99 dialect, is functionally identical to the previous two queries:

SELECT "jcr:primaryType", "jcr:created", "jcr:createdBy", "jcr:path" FROM "nt:file"
WHERE "mode:localName" LIKE '%.txt'

Inner joins

In JCR-SQL2, a node will appear as a row in each table that corresponds to the node types defined by that node's primary type or mixin types, or any supertypes of these node types. In other words, a node will appear in the table corresponding to each node type for which Node.isNodeType(...) returns true.

For example, consider a node that has a primary type of nt:file but has an explicit mixin of mix:referenceable. This node will appear as a row in the all of these tables:

• nt:file

• mix:referenceable

• nt:hierarchyNode

• mix:created

• nt:base

However, the columns in each of these tables will differ. The nt:file node type has the nt:hierarchyNode, mix:created, and nt:base for supertypes, and therefore the table for nt:file contains columns for the property definitions on all of these types. But because mix:referenceable is not a supertype of nt:file, the table for nt:file will not contain a jcr:uuid column. To obtain a single result set that contains columns for all the properties of our node, we need to perform an identity join.

The next query shows how to return all properties for nt:file nodes that are also mix:referenceable:

SELECT file.*, ref.*
FROM [nt:file] AS file
JOIN [mix:referenceable] AS ref
  ON ISSAMENODE(file,ref)

Since wildcards were used in the SELECT clause, ModeShape expands the SELECT clause to include the columns for all (explicit and inherited) property definitions of each type plus pseudo-columns for each type, which is equivalent to:

SELECT file.[jcr:primaryType],
       file.[jcr:created],
       file.[jcr:createdBy],
       file.[jcr:path],
       file.[jcr:name],
       file.[jcr:score],
       file.[mode:localName],
       file.[mode:depth],
       ref.[jcr:path],
       ref.[jcr:name],
       ref.[jcr:score],
       ref.[mode:localName],
       ref.[mode:depth],
       ref.[jcr:uuid]
FROM [nt:file] AS file
JOIN [mix:referenceable] AS ref
  ON ISSAMENODE(file,ref)

Note because we are using an identity join, the "file.[jcr:path]" column will contain the same value as the "ref.[jcr:path]".

Here is a query that does this by eliminating columns with duplicate values and using aliases that are simpler than the namespace-qualified names:

SELECT file.[jcr:primaryType] AS primaryType,
       file.[jcr:created] AS created,
       file.[jcr:createdBy] AS createdBy,
       ref.[jcr:uuid] AS uuid,
       file.[jcr:path] AS path,
       file.[jcr:name] AS name,
       file.[jcr:score] AS score,
       file.[mode:localName] AS localName,
       file.[mode:depth] AS depth
FROM [nt:file] AS file
JOIN [mix:referenceable] AS ref
  ON ISSAMENODE(file,ref)

Although this query looks much more like SQL, use of the '[' and ']' characters in JCR-SQL2 to quote the identifiers is not typical of a SQL dialect. Again, ModeShape supports the using double-quote characters and square braces interchangeably around identifiers (although they must match around any single identifier). This makes it easier for existing SQL-oriented tools and applications to work more readily with ModeShape, including applications that use ModeShape's JDBC driver to query a ModeShape JCR repository.

This next query, which looks remarkably like any SQL-92 or -99 dialect, is functionally identical to the previous query. However, it uses double quotes and a pseudo-column identity constraint on "jcr:path" (which is identical in semantics and performance as the "ISSAMENODE(...)" constraint):

SELECT file."jcr:primaryType" AS primaryType,
       file."jcr:created" AS created,
       file."jcr:createdBy" AS createdBy,
       ref."jcr:uuid" AS uuid,
       file."jcr:path" AS path,
       file."jcr:name" AS name,
       file."jcr:score" AS score,
       file."mode:localName" AS localName,
       file."mode:depth" AS depth
FROM "nt:file" AS file
JOIN "mix:referenceable" AS ref
  ON file."jcr:path" = ref."jcr:path"

Other joins

These are examples of two-way inner joins, but ModeShape supports joining multiple tables together in a single query. ModeShape also supports a variety of joins, including:

• INNER JOIN (or just JOIN)

• LEFT OUTER JOIN

• RIGHT OUTER JOIN

• FULL OUTER JOIN

• CROSS JOIN

Set operations: unions, intersections, and complements

ModeShape also supports several other query features beyond JCR-SQL2. One of these is support for set queries that use:

• UNION and UNION ALL

• INTERSECT and INTERSECT ALL

• EXCEPT and EXCEPT ALL.

Here is an example of a union:

SELECT [jcr:primaryType], [jcr:created], [jcr:createdBy], [jcr:path] FROM [nt:file]
UNION
SELECT [jcr:primaryType], [jcr:created], [jcr:createdBy], [jcr:path] FROM [nt:folder]

Subqueries

ModeShape also supports using (non-correlated) subqueries within the WHERE clause and wherever a static operand can be used. Subqueries can even be used within another subquery. All subqueries, though, should return a single column (all other columns will be ignored), and each row's single value will be treated as a literal value. If the subquery is used in a clause that expects a single row (e.g., in a comparison), only the subquery's first row will be used.

Subqueries in ModeShape are a powerful and easy way to use more complex criteria that is a function of the content in the repository, without having to resort to multiple queries and complex application logic, such as taking the results of one query and dynamically generating the criteria of another query.

Here's an example of a query that finds all nt:file nodes in the repository whose paths are referenced in the value of the vdb:originalFile property of the vdb:virtualDatabase nodes. (This query also uses the "$maxVersion" variable in the subquery.)

SELECT [jcr:primaryType], [jcr:created], [jcr:createdBy], [jcr:path]
FROM [nt:file]
WHERE PATH() IN (
   SELECT [vdb:originalFile] FROM [vdb:virtualDatabase]
   WHERE [vdb:version] <= $maxVersion
     AND CONTAINS([vdb:description],'xml OR xml maybe')
)

Without subqueries, this query would need to be broken into two separate queries: the first would find all of the paths referenced by the vdb:virtualDatabase nodes matching the version and description criteria, followed by one (or more) subsequent queries to find the nt:file nodes with the paths expressed as literal values (or variables).