Skip to main content

URL Syntax for OData Query Extension

In my previous article Extending OData Query Functionality, I explained an extension mechanism for the Content Delivery Web Service with the goal of having more robust capabilities of querying for content than the out-of-the-box OData service offers. Please read that article first, in order to understand the context, goal and approach.

This article follows up on the previous concepts and explains the URL syntax, grammar, and URL parameters accepted by the OData Query Extension. Link to source code and distributable JAR available (odata-query-extension-1.1.jar).

Query Entity Collections

QueryComponents

Retrieves Component entities. The query API is applied to Components, only (item type = 16).

/odata.svc/QueryComponents

QueryComponentPresentations

Retrieves ComponentPresenation entities. The query API is applied to Components, only (item type = 16), yet the results of the query are used to build Dynamic Component Presentations.

/odata.svc/QueryComponentPresentations

QueryPages

Retrieves Page entities. The query API is applied to Pages, only (item type = 64).

/odata.svc/QueryPages

URL Syntax

Specifying Criterias

It is possible to specify Criterias in two ways:
  • using OData resource identifiers;
  • in $filter System Query Option;
Both mechanism accept the same Criteria names. They are as follows:

BinaryTypePublicationKey
ItemCreationDatePublicationMultimediaPath
ItemInitialPublishDatePublicationMultimediaURL
ItemLastPublishedDatePublicationPath
ItemModificationDatePublicationTitle
ItemReferencePublicationURL
ItemSchemaStructureGroup
ItemTitleStructureGroupDirectory
MultimediaStructureGroupTitle
PageTemplateTaxonomy
PageURLCustomMetaKey
PublicationCustomMetaValue
SchemaTitle

If you look a bit closer, you will notice the Criteria names are the corresponding class names (less the suffix Criteria) as defined in the package com.tridion.broker.querying.criteria.

The value of each Criteria is specified as string, but it is parsed to the required type.
The Date format is yyyy-MM-dd HH:mm:ss.SSS

OData Resource Identifiers

The syntax for OData resource identifiers is /odata.svc/Collection(CriteriaName=CriteriaValue[,CriteriaName=CriteriaValue])
where
  • Collection is one of QueryComponents, QueryComponentPresentations or QueryPages;
  • CriteriaName is one of the accepted criteria names (see above);
  • CriteriaValue is the corresponding value for that particular criteria (can represent string, int, boolean or date). The matching is done using = (equal) operator;
Multiple Criterias are separated by AND operator.

Examples:
/odata.svc/QueryComponents(Publication=3)
/odata.svc/QueryComponents(SchemaTitle=Generic Content)
/odata.svc/QueryComponents(Multimedia=true,ItemSchema=5)

$filter System Query Option

The syntax of the $filter System Query Option is given by the OData documentation.
The OData Query Extension supports the folowing syntax:
/odata.svc/Collection?$filter=CriteriaName field_operator CriteriaValue [logical_operator CriteriaName field_operator CriteriaValue]
where
  • Collection is one of QueryComponentsQueryComponentPresentations or QueryPages;
  • CriteriaName is one of the accepted criteria names (see above);
  • CriteriaValue is the corresponding value for that particular criteria
    • strings are surrounded by single quotes (');
    • numerics can be both positive/negative integer or floats using dot (.) as decimal separator;
    • dates are surrounded by single quotes (') in format yyyy-MM-dd HH:mm:ss.SSS
    • booleans are either true or false;
  • field_operator is one of eq, neq, gt, ge, lt, le, like corresponding to one of the enumeration values inside com.tridion.broker.querying.criteria.FieldOperator;
  • logical_operator is either and or or. Paranthesis '(' and ')' offer precedance and grouping options for logical_operators. AND has precedence over OR;
If both $filter and resource identifiers are specified, the logical operator between them is AND.

Examples (only the $filter):
$filter=Multimedia eq true and (Publication eq 3 or Publication eq 4)
$filter=CustomMetaValue like 'Artic%'
$filter=ItemModificationDate gt '2013-05-01 13:24:56.999'

Note: for Criterias that don't support a FieldOperator, the field_operator must be eq. The following expression will throw a ParserException: $filter=Publication gt 3

Specifying Order

OData protocol uses $orderby System Query Option to specify in which order the results should be returned. Tridion CD API uses the classes SortParameter, SortColumn and SortDirection to specify the order, column and direction of sorting. There can be more than one SortParameter associated to a query.

I implemented the following syntax for $orderby (trying to abide as much as possible to the OData standard):
/odata.svc/Collection?$orderby=Column [asc|desc] [CustomMetaKey [string|float|date] [asc|desc]]
where
  • Collection is one of QueryComponentsQueryComponentPresentations or QueryPages;
  • Column is one of the following columns as defined in the CD API package com.tridion.broker.querying.sorting.column;
  • ComponentSchemaItemOwningPublication
    ItemCreationDateItemPublication
    ItemIdItemTitle
    ItemInitialPublicationItemTrustee
    ItemLastPublishItemType
    ItemMajorVersionPageFilename
    ItemMinorVersionPageTemplate
    ItemModificationPageURL
  • CustomMetaKey - if Column is not one of the values defined above, it is considered a custom meta key, and the sorting will be performed on the said custom meta field;
  • string, float or date - the type of the custom meta field (if missing, default is string);
  • asc or desc - the sorting direction (if missing, default is ascending);
Examples (only the $orderby):
$orderby=ItemLastPublish desc
$orderby=ItemTitle ItemModification asc
$orderby=GoLiveDate date desc

Multiple sort parameters can be specified.

Specifying Pagination

OData protocol specifies pagination using $top and $skip System Query Option. Tridion CD API specifies ResultFilters LimitFilter and PagingFilter.

The OData Query Extension interprets the values of $top and $skip and converts them into a ResultFilter:
  • specifying $top, but not $skip will create a LimitFilter that returns only the top specified results;
  • specifying $skip, but not $top will create a PagingFilter that returns all results after skipping the specified number;
  • specifying both $top and $skip will create a PagingFilter that returns only the top specified results after skipping specified number;

A Look Under the Bonnet

Parsing Criteria

I implemented a recursive descent parser (LL parser) for logical expressions based on an LL(1) grammar. I used Cogito Learning as inspiration to brush up on my parsing skills.

The idea is to evaluate the $filter expression and produce a Criteria object that can be either a stand alone Criteria, AndCriteria, or OrCriteria. The LL(1) grammar imposes a tokenization of the input string and a set of rules to be able to choose the next rule to apply by just looking at one token at a time (from left to right).

The parser produces an expression tree where in upper nodes there are logical_operators (and, or) and in lower nodes (lowest non-leaf node) there are FieldOperators (eq, neq, gt, ge, lt, le, like).

The LL(1) grammar to parse such expressions is the following:

Terminal symbols:
CRITERIA
FIELD_OPERATOR
AND_OPERATOR
OR_OPERATOR
VALUE
OPEN_BRACKET
CLOSE_BRACKET

Non-terminal symbols:
expression -> or_term or_op
or_op -> OR_OPERATOR or_term or_op
or_op -> EPSILON

or_term -> and_term and_op
and_op -> AND_OPERATOR and_term and_op
and_op -> EPSILON

and_term -> CRITERIA field_op
and_term -> argument
field_op -> FIELD_OPERATOR value

argument -> OPEN_BRACKET expression CLOSE_BRACKET

value -> VALUE

This logic is available in classes Parser and CriteriaBuilder.

Sorting

Sorting is also implemented using a very simple Parser that looks at the $orderby string. I didn't even formulate a grammar for this one. Some highlights from the SortParameterBuilder class are shown below.

The top-level parsing loop (not even recursive) consumes one token at a time from orderby, builds SortColumn and SortDirection from tokens and adds a new SortParameter to the parameters collection.

    tokens = new LinkedList<String>(Arrays.asList(orderby.split("\\s")));
    curentToken = tokens.getFirst();
    while (curentToken != null) {
        SortColumn sortColumn = getSortColumn();
        SortDirection sortDirection = getSortDirection();
        sortParameters.add(new SortParameter(sortColumn, sortDirection));
    }

Method getSortColumn performs an if/else on all predefined sort columns (omitted in the sample below). Finally, the currentToken is assumed to be CustomMetaKeyColumn and we proceed to identify its MetadataType, if specified.

private SortColumn getSortColumn() {
    ...
    String customMetaKey = curentToken;
    nextToken();
    MetadataType metadataType = getMetadataType();

    return new CustomMetaKeyColumn(customMetaKey, metadataType);
}

In method getMetadataType, I attempt to read the MetadataType enumeration. In case of an invalid value, MetadataType.STRING is returned.

private MetadataType getMetadataType() {
    MetadataType metadataType;
    try {
        metadataType = MetadataType.valueOf(curentToken.toUpperCase());
        nextToken();
    } catch (IllegalArgumentException iae) {
        metadataType = MetadataType.STRING;
    }

    return metadataType;
}

Lastly, the getSortDirection method tries to match the current token as asc or desc. If not specified, asc is used.

private SortDirection getSortDirection() {
    if ("asc".equals(curentToken)) {
        nextToken();
        return SortParameter.ASCENDING;
    } else if ("desc".equals(curentToken)) {
        nextToken();
        return SortParameter.DESCENDING;
    }

    return SortDirection.ASCENDING;
}

Pagination

Implementing pagination meant simply looking at the $top and $skip values. Class ResultFilterBuilder handles this. The logic is pretty trivial :)

    if (isSkipSet()) {
        resultFilter = new PagingFilter(skip, top);
    } else {
        if (isTopSet()) {
            resultFilter = new LimitFilter(top);
        }
    }

Next Steps...

In its current format, the URL parser does not allow for functions to be specified. This limits the functionality that can be queried in the URL $filter parameter (i.e. no compound Custom Meta queries can be placed, since we would need a way to nest Custom Meta Value with Custom Met Key criterias).

In the next post Function Support for Queries in OData Extension, I am presenting an extension to the parser that accept functions.


Comments

Popular posts from this blog

Running sp_updatestats on AWS RDS database

Part of the maintenance tasks that I perform on a MSSQL Content Manager database is to run stored procedure sp_updatestats . exec sp_updatestats However, that is not supported on an AWS RDS instance. The error message below indicates that only the sa  account can perform this: Msg 15247 , Level 16 , State 1 , Procedure sp_updatestats, Line 15 [Batch Start Line 0 ] User does not have permission to perform this action. Instead there are several posts that suggest using UPDATE STATISTICS instead: https://dba.stackexchange.com/questions/145982/sp-updatestats-vs-update-statistics I stumbled upon the following post from 2008 (!!!), https://social.msdn.microsoft.com/Forums/sqlserver/en-US/186e3db0-fe37-4c31-b017-8e7c24d19697/spupdatestats-fails-to-run-with-permission-error-under-dbopriveleged-user , which describes a way to wrap the call to sp_updatestats and execute it under a different user: create procedure dbo.sp_updstats with execute as 'dbo' as

Content Delivery Monitoring in AWS with CloudWatch

This post describes a way of monitoring a Tridion 9 combined Deployer by sending the health checks into a custom metric in CloudWatch in AWS. The same approach can also be used for other Content Delivery services. Once the metric is available in CloudWatch, we can create alarms in case the service errors out or becomes unresponsive. The overall architecture is as follows: Content Delivery service sends heartbeat (or exposes HTTP endpoint) for monitoring Monitoring Agent checks heartbeat (or HTTP health check) regularly and stores health state AWS lambda function: runs regularly reads the health state from Monitoring Agent pushes custom metrics into CloudWatch I am running the Deployer ( installation docs ) and Monitoring Agent ( installation docs ) on a t2.medium EC2 instance running CentOS on which I also installed the Systems Manager Agent (SSM Agent) ( installation docs ). In my case I have a combined Deployer that I want to monitor. This consists of an Endpoint and a

Debugging a Tridion 2011 Event System

OK, so you wrote your Tridion Event System. Now it's time to debug it. I know this is a hypothetical situtation -- your code never needs any kind of debugging ;) but indulge me... Recently, Alvin Reyes ( @nivlong ) blogged about being difficult to know how exactly to debug a Tridion Event System. More exactly, the question was " What process do I attach to for debugging even system code? ". Unfortunately, there is no simple or generic answer for it. Different events are fired by different Tridion CM modules. These modules run as different programs (or services) or run inside other programs (e.g. IIS). This means that you will need to monitor (or debug) different processes, based on which events your code handles. So the usual suspects are: dllhost.exe (or dllhost3g.exe ) - running as the MTSUser is the SDL Tridion Content Manager COM+ application and it fires events on generic TOM objects (e.g. events based on Tridion.ContentManager.Extensibility.Events.CrudEven