salesforce Dreamforce On-Demand
Contact Us 1-800-596-4880
  1. Homepage
  2. Connector DevKit (3.5)
  3. Defining Attributes Operations and Data Model

  4. Adding DataSense Query Language

Adding DataSense Query Language

DevKit is compatible only with Studio 6 and Mule 3. To build Mule 4 connectors, see the Mule SDK documentation.

Many SaaS applications either implement a proper query language (such as SOQL, Salesforce Object Query Language) or support selecting resources through their API in a way that corresponds to querying. DataSense Query Language (DSQL) provides a uniform query language that can be used to query data in any application with a compatible Anypoint™ Connector.

6 package
You can find the source code for the example below here.

Assumption

Enabling DSQL on Your Connector

In order to have DSQL enabled on your connector, you must do the following:

  • Enable DataSense (including @MetaDataKeyRetriever and @MetaDataRetriever annotated methods).

  • Create a new method, annotated with @QueryTranslator, responsible for doing the translation. This method must receive an org.mule.common.query.DsqlQuery object as its input.

  • Annotate a parameter with @Query, where users will input their DSQL statements.

The last two items on this list are explained below in this document.

Familiarizing Yourself with DSQL Structure

Out of the standard SQL operations (Select, Delete, Update, Insert) the only one supported in DSQL is SELECT.

Every DSQL statement must follow the structure below:

SELECT [selectedFieldName, ]
FROM [Type]
WHERE ( [ fieldName OPERATION value, AND ] )
{ ORDER BY [fieldName, ] { DESC | ASC } }
{ OFFSET [offset_number] }
{ LIMIT [max_number_total_of_items_in_query] }

-- KEY:
-- {} Optional value
-- [] Dynamic value
-- [, ] Dynamic list of values

Consider the DSQL query example from the Adding DataSense Query Language documentation:

SELECT AccountID,FirstName,Lastname
FROM Contact
WHERE (AccountID > '500' AND IsDeleted = false) OR Employee_Current = false
ORDER BY LastName
OFFSET 250
LIMIT 1000

Mule uses an internal query model to represent the different semantics elements that make up a query:

Object Example Comments

Select fields

SELECT AccountID, FirstName, LastName

A list of Fields. Each field represents metadata - field name and datatype.

Type

FROM Contact

An object exposed by the application. This is always a List of exactly one Type.

Filter expression

WHERE (AccountID > '500' AND IsDeleted = false) OR Employee_Current = false

A Boolean condition defined over the fields on the object type.

List of sort order fields

ORDER BY LastName

Offset

OFFSET 250

The number of items to skip from the beginning of the result set. Used for paging.

Limit

LIMIT 100

The number of items to include in the result set. Used for paging.

Similarly, the filter expression is represented internally by a set of field names, data literals, delimiters, operators for comparison, and grouping, etc.

More details about the query model and accessing it from your code emerge as you build out your new native query language translator that transform the query model into a query language.

Translating From a DSQL Structure

The first step is to create a new method on your @Connector, annotated with @QueryTranslator. This method will receive a DSQL query (an org.mule.common.query.DsqlQuery object) as its input.

To easily do this translation, there are a set of visitor classes you can take great advantage of.

Visitor Classes and Query Languages

A set of visitor classes implements the mapping between query model elements and specific query language syntax - classes that implement the Visitor design pattern, traverse the query model, and generate corresponding statements in the needed native query language.

Package org.mule.common.query defines Visitor interfaces that represent different parts of the DSQL query language, then provides default implementations of those Visitors that implement DSQL syntax.

Visitor Interface Implementation Classes Syntax Implementation

QueryVisitor

DefaultQueryVisitor

DSQLQueryVisitor

  • "Beginning of an expression"

    (i.e. the word "WHERE" before a filter expression)

  • Field lists

  • Types

  • Limit and Offset clauses

  • Precedence operators

  • Order By fields

  • Comparisons

  • Boolean operators

OperatorVisitor

DefaultOperatorVisitor

  • Specific comparison operators

    (equals, not-equals, greater/less than/ LIKE, etc)

To implement support for a new query language, define classes that extend DefaultQueryVisitor and DefaultOperatorVisitor and use them to construct native query language statements from the query model. You can name your new classes MyAppQueryVisitor and MyAppOperatorVisitor. The following sections describe how to construct them.

Implementing Your Operation Visitor Class

Creating a new operator visitor class lets you define the comparison operator syntax (including LIKE) for your native query language.

Define a new class in your connector that extends DefaultOperatorVisitor, which implements all the DSQL comparison operator

Class DefaultOperatorVisitor 
public class DefaultOperatorVisitor implements OperatorVisitor {
    public static final String LIKE = " like ";
    public static final String GREATER_OR_EQUALS = " >= ";
    public static final String NOT_EQUALS = " <> ";
    public static final String EQUALS = " = ";
    public static final String LESS_OR_EQUALS = " <= ";
    public static final String GREATER = " > ";
    public static final String LESS = " < ";
    @Override public String lessOperator() {
        return LESS;
    }
    @Override public String greaterOperator() {
        return GREATER;
    }
    @Override public String lessOrEqualsOperator() {
        return LESS_OR_EQUALS;
    }
    @Override public String equalsOperator() {
        return EQUALS;
    }
    @Override public String notEqualsOperator() {
        return NOT_EQUALS;
    }
    @Override public String greaterOrEqualsOperator() {
        return GREATER_OR_EQUALS;
    }
    @Override public String likeOperator() {
        return LIKE;
    }
}

Most languages will mostly use similar operators. To implement operations in your own language, the shortest path is to create a new class that extends DefaultOperatorVisitor, and then override the methods that return those operators where your language differs from DSQL.

For example, in the Salesforce.com connector, class SfdcOperatorVisitor overrides notEqualsOperator():

import org.mule.common.query.DefaultOperatorVisitor;


public class SfdcOperatorVisitor extends DefaultOperatorVisitor {
 @Override public java.lang.String notEqualsOperator() {
        return " != ";
    }
}

Because the rest of the operators are the same SOQL and DSQL, there are no other changes needed.

Implementing Your Query Visitor Class

Creating a new query visitor class lets you define the query syntax for expressing the core query model constructs in your native query language.

In order to do so, create a new class in your connector project that extends DefaultQueryVisitor class.

Suppose that your service uses the following syntax:

S [selectedFieldName, ]
F [Type]
W ( [ fieldName OPERATION value, & ] )

Below is an example of a visitor made for translating DSQL into a syntax that is show above:

public class SimpleSyntaxVisitor extends DefaultQueryVisitor {

    private StringBuilder stringBuilder;

    public SimpleSyntaxVisitor()
    {
        stringBuilder = new StringBuilder();
    }

    @Override
    public void visitFields(java.util.List<org.mule.common.query.Field> fields) {
        StringBuilder select = new StringBuilder();
        select.append("S ");
        Iterator<Field> fieldIterable = fields.iterator();
        while (fieldIterable.hasNext())
        {
            String fieldName = addQuotesIfNeeded(fieldIterable.next().getName());
            select.append(fieldName);
            if (fieldIterable.hasNext())
            {
                select.append(",");
            }
        }

        stringBuilder.insert(0, select);
    }

    @Override
    public void visitTypes(List<Type> types)
    {
        stringBuilder.append(" F ");
        Iterator<Type> typeIterator = types.iterator();
        while (typeIterator.hasNext())
        {
            String typeName = addQuotesIfNeeded(typeIterator.next().getName());
            stringBuilder.append(typeName);
            if (typeIterator.hasNext())
            {
                stringBuilder.append(",");
            }
        }
    }

    //(...)


    @Override
    public void visitBeginExpression()
    {
        stringBuilder.append(" W ");
    }


    public String toSimpleQuery()
    {
        return stringBuilder.toString();
    }
}
For a complete connector that includes this functionality, view the GitHub Repo.

Adding Your Query Visitor into you @QueryTranslator Method

In your @Connector class, implement a method annotated with @QueryTranslator that instances the visitor class and calls the method that returns your query.

@QueryTranslator
public String toNativeQuery(DsqlQuery query){
    SimpleSyntaxVisitor visitor = new SimpleSyntaxVisitor();
    query.accept(visitor);
    return visitor.dsqlQuery();
}

How Users of your Connector Will See DSQL

After you have built you connector and installed it in Studio, it will be available for use in Mule projects. Add it to a flow, and open its property editor:

qb2

You can select a language for your query, in this case, you can choose between the service’s Native Query Language or DSQL. When having DSQL selected, you can use the Query Builder tool to automatically generate a query by simply selecting amongst the existing fields.

query+builder

The query you build here will be populated into the connector’s "Query Text" field. You are also free to edit the text in this field once again.

Once you have created a Query in DSQL, you can change the query language to the service’s Native Query Language to verify that the translation is being correctly executed:

qb3

Thanks to DataSense, if you include a DataMapper element after your connector, DataMapper will interpret your query and only present the fields that are returned by it:

dm

In this case, DataMapper knows that it will deal with a list of Authors that only have a firstName field.

See Also