Skip to main content

URL Syntax for OData Query Extension

Image
By
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 QueryComponents, QueryComponentPresentations 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 QueryComponents, QueryComponentPresentations 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.


Labels:

Comments

Post a Comment

Popular posts from this blog

Scaling Policies

By
This post is part of a bigger topic Autoscaling Publishers in AWS . In a previous post we talked about the Auto Scaling Groups , but we didn't go into details on the Scaling Policies. This is the purpose of this blog post. As defined earlier, the Scaling Policies define the rules according to which the group size is increased or decreased. These rules are based on instance metrics (e.g. CPU), CloudWatch custom metrics, or even CloudWatch alarms and their states and values. We defined a Scaling Policy with Steps, called 'increase_group_size', which is triggered first by the CloudWatch Alarm 'Publish_Alarm' defined earlier. Also depending on the size of the monitored CloudWatch custom metric 'Waiting for Publish', the Scaling Policy with Steps can add a difference number of instances to the group. The scaling policy sets the number of instances in group to 1 if there are between 1000 and 2000 items Waiting for Publish in the queue. It also sets the
Post a Comment
Read more

Toolkit - Dynamic Content Queries

By
This post if part of a series about the  File System Toolkit  - a custom content delivery API for SDL Tridion. This post presents the Dynamic Content Query capability. The requirements for the Toolkit API are that it should be able to provide CustomMeta queries, pagination, and sorting -- all on the file system, without the use third party tools (database, search engines, indexers, etc). Therefore I had to implement a simple database engine and indexer -- which is described in more detail in post Writing My Own Database Engine . The querying logic does not make use of cache. This means the query logic is executed every time. When models are requested, the models are however retrieved using the ModelFactory and those are cached. Query Class This is the main class for dynamic content queries. It is the entry point into the execution logic of a query. The class takes as parameter a Criterion (presented below) which triggers the execution of query in all sub-criteria of a Criterio
Post a Comment
Read more

Running sp_updatestats on AWS RDS database

By
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
1 comment
Read more