Chapter 4. Command Language

4.1. Language Interfaces

Teiid sends commands to your connector in object form. The interfaces for these objects are all defined in the com.metamatrix.data.language package. These interfaces can be combined to represent any possible command that Teiid may send to the connector. However, it is possible to notify the Teiid Server that your connector can only accept certain kinds of commands via the ConnectorCapabilities class. See the section on using Connector Capabilities for more information.

The language interfaces all extend from the main interface, ILanguageObject. They are composed in a tree where each node is a language object that has zero or more child language objects of types that are dependent on the current node.

All commands sent to your connector are in the form of these language trees, where the root of the tree is a subclass of ICommand. ICommand has several sub-interfaces, namely: IQuery, IInsert, IUpdate, IDelete, and IProcedure. These represent the query in SQL form. Important components of these commands are expressions, criteria, and joins, which are examined in closer detail below.

4.1.1. Expressions

An expression represents a single value in context, although in some cases that value may change as the query is evaluated. For example, a literal value, such as “5” represents an integer value. An element such as “EmployeeName” represents a column in a data source and may take on many scalar values while the command is being evaluated.

The following diagram shows the IExpression interface and all sub-interfaces in the language objects.

Figure 4.1. Execution Interfaces Class Diagram

These interfaces are explained in greater detail here:

IExpression – base expression interface
IElement – represents an element in the data source
ILiteral – represents a literal scalar value
IFunction – represents a scalar function with parameters that are also IExpressions
IAggregate – represents an aggregate function which holds a single expression
IScalarSubquery – represents a subquery that returns a single value
ICaseExpression – represents a CASE expression. The CASE expression evaluates an expression, then compares with the values in the WHEN clauses to determine the THEN clause to evaluate.
ISearchedCaseExpression – represents a searched CASE expression. The searched CASE expression evaluates the criteria in WHEN clauses till one evaluates to TRUE, then evaluates the associated THEN clause.

4.1.2. Criteria

A criteria is a combination of expressions and operators that evaluates to true or false. Criteria are most commonly used in the FROM or HAVING clauses. The following diagram shows the criteria interfaces present in the language objects.

Figure 4.2. Criteria Interfaces Class Diagram

These interfaces are described in greater detail here:

ICriteria – the base criteria interface
ILogicalCriteria – used to logically combine other criteria
INotCriteria – used to NOT another criteria
ICompoundCriteria – used to combine other criteria via AND or OR
IPredicateCriteria – a predicate that evaluates to true or false
ISubuqeryCompareCriteria – represents a comparison criteria with a subquery including a quantifier such as SOME or ALL
ICompareCriteria – represents a comparison criteria with =, >, <, etc
IBaseInCriteria – base class for an IN criteria
IInCriteria – represents an IN criteria that has a set of expressions for values
ISubqueryInCriteria – represents an IN criteria that uses a subquery to produce the value set
IIsNullCriteria – represents an IS NULL criteria
IExistsCriteria – represents an EXISTS criteria that determines whether a subquery will return any values
ILikeCriteria – represents a LIKE criteria that compares string values

4.1.3. Joins

The FROM clause contains a list of IFromItems. Each IFomItem can either represent a group or a join between two other IFromItems. This allows joins to be composed into a join tree.

Figure 4.3. IJoin Interface Class Diagram

The IGroup represents a single group, which must be a leaf of the join tree. The IJoin has a left and right IFromItem and information on the join between the items.

4.1.4. IQuery Structure

The following diagram shows the structure of an IQuery command.

Figure 4.4. IQuery Interface Class Diagram

Each IQuery will have an ISelect describing the expressions (typically elements) being selected and an IFrom specifying the group or groups being selected from, along with any join information. The IQuery may optionally also supply an ICriteria (representing a SQL WHERE clause), an IGroupBy (representing a SQL GROUP BY clause), and an ICriteria (representing a SQL HAVING clause).

4.1.5. IUnion Structure

The following diagram shows the structure of an IUnion command.

Figure 4.5. IUnion Interface Class Diagram

IUnion extends from IQuery and allows for one or more additional IQuery objects to be attached as a UNION query. For each additional IQuery, there is a Boolean “ALL” flag that must be set. The ORDER BY clause for the UNION as a whole can also be set on the IUnion object.

4.1.6. IInsert Structure

The following diagram shows the structure of an IInsert command.

Figure 4.6. IInsert Interface Class Diagram

Each IInsert will have a single IGroup specifying the group being inserted into. It will also have two matched lists – one a list of IElement specifying the columns of the IGroup that are being inserted into and one a list of ILiteral specifying the values that will be inserted into each matching IElement.

4.1.7. IUpdate Structure

The following diagram shows the structure of an IUpdate command.

Figure 4.7. IUpdate Interface Class Diagram

Each IUpdate will have a single IGroup specifying the group being updated. The list of ICompareCriteria are used to specify each element that is being modified. Each compare criteria will be of the form “element = literal”. The IUpdate may optionally provide a criterion specifying which rows should be updated.

4.1.8. IDelete Structure

The following diagram shows the structure of an IDelete command.

Figure 4.8. IDelete Interface Class Diagram

Each IDelete will have a single IGroup specifying the group being deleted from. It may also optionally have a criteria specifying which rows should be deleted.

4.1.9. IProcedure Structure

The following diagram shows the structure of an IProcedure command.

Figure 4.9. IProcedure Interface Class Diagram

Each IProcedure has zero or more IParameter objects. The IParameter objects describe the input parameters, the output result set, and the output parameters.

4.1.10. IBulkInsert Structure

The following diagram shows the structure of an IBulkInsert command.

Figure 4.10. IBulkInsert Interface Class Diagram

Each IBulkInsert extends an IInsert but provides a List of Lists of values that need to be placed into the VALUES list of the INSERT.

4.1.11. IBatchedUpdate Structure

The following diagram shows the structure of an IBatchedUpdate command.

Figure 4.11. IBatchedUpdate Interface Class Diagram

Each IBatchedUpdate has a list of ICommand objects that compose the batch.

4.2. Language Utilities

This section covers utilities available when using, creating, and manipulating the language interfaces.

4.2.1. Data Types

The Connector API contains an interface TypeFacility that defines data types and provides value translation facilities.

Figure 4.12. IBatchedUpdate Interface Class Diagram

The table below lists the role of each class in the framework.

Table 4.1. Types Facility Classes

Class	Type	Description
ConnectorEnvironment	Interface	This interface (provided by Teiid) is a factory to obtain the TypeFacility instance for the connector using the getTypeFacility() method.
TypeFacility	Interface	This interface has two methods that support data type transformation. Generally,transformations exist for all valid implicit and explicit data type transformations in the Teiid query engine.
TypeFacility.RUNTIME_TYPES	Interface	This is an inner interface of TypeFacility that defines constants for all Teiid runtime data types. All IExpression instances define a data type based on this set of types. These constants are often needed in understanding or creating language interfaces.

4.2.2. Language Manipulation

In connectors that support a fuller set of capabilities (those that generally are translating to a language of comparable to SQL), there is often a need to manipulate or create language interfaces to move closer to the syntax of choice. Some utilities are provided for this purpose:

Some helpful utilities for working with ICriteria objects are provided in the LanguageUtil class. This class has methods to combine ICriteria with AND or to break an ICriteria apart based on AND operators. These utilities are helpful for breaking apart a criteria into individual filters that your connector can implement.

4.3. Runtime Metadata

Teiid uses a library of metadata, known as “runtime metadata” for each virtual database that is deployed in the Teiid Server. The runtime metadata is a subset of metadata as defined by models in the Teiid models that compose the virtual database.

Connectors can access runtime metadata by using the interfaces defined in com.metamatrix.data.metadata.runtime. This class defines interfaces representing a MetadataID, a MetadataObject, and ways to navigate those IDs and objects.

4.3.1. Language Objects

One language interface, IMetadataReference describes whether a language object has a reference to a MetadataID. The following interfaces extend IMetadataReference:

IElement
IGroup
IProcedure
IParameter

Once a MetadataID has been obtained, it is possible to use the RuntimeMetadata interface to discover metadata about that ID or to find other related IDs or objects.

4.3.2. Access to Runtime Metadata

The following interfaces are defined in the runtime metadata package:

Figure 4.13. Runtime MetaData Class Diagram

As mentioned in the previous section, a MetadataID is obtained from one of the language objects. That MetadataID can then be used directly to obtain information about the ID, such as the full name or short name.

The RuntimeMetadata interface can be obtained from the ConnectorEnvironment. It provides the ability to look up MetadataObjects based on MetadataIDs. There are several kinds of MetadataObjects and they can be used to find more information about the object in runtime metadata.

Currently, only a subset of the most commonly used runtime metadata is available through these interfaces. In the future, more complete information will be available.

Obtaining MetadataObject Properties Example

The process of getting an element's properties is needed for most connector development For example to get the NameInSource property or all extension properties:

IMetaDataReference ref = ... //An IGroup in this example
RuntimeMetadata rm = ... //Obtained from the ConnectorEnvironemnt

MetaDataObject group = rm.getObject(ref.getMetadataID()); 
String contextName = group.getNameInSource();

//The props will contain extension properties
Properties props = group.getProperties();

4.4. Language Visitors

4.4.1. Framework

The Connector API provides a language visitor framework in the com.metamatrix.data.visitor.framework package. The framework provides utilities useful in navigating and extracting information from trees of language objects. This diagram describes the relationships of the various classes in the framework:

Figure 4.14. LanguageObjectVisitor Class Diagram

The visitor framework is a variant of the Visitor design pattern, which is documented in several popular design pattern references. The visitor pattern encompasses two primary operations: traversing the nodes of a graph (also known as iteration) and performing some action at each node of the graph. In this case, the nodes are language interface objects and the graph is really a tree rooted at some node. The provided framework allows for customization of both aspects of visiting.

The base LanguageObjectVisitor class defines the visit methods for all leaf language interfaces that can exist in the tree. The LanguageObject interface defines an acceptVisitor() method – this method will call back on the visit method of the visitor to complete the contract. A base class with empty visit methods is provided as AbstractLanguageVisitor. The AbstractLanguageVisitor is just a visitor shell – it performs no actions when visiting nodes and does not provide any iteration.

The HierarchyVisitor provides the basic code for walking a language object tree. The HierarchyVisitor performs no action as it walks the tree – it just encapsulates the knowledge of how to walk it. If your connector wants to provide a custom iteration that walks the objects in a special order (to exclude nodes, include nodes multiple times, conditionally include nodes, etc) then you must either extend HierarchyVisitor or build your own iteration visitor. In general, that is not necessary.

The DelegatingHierarchyVisitor is a special subclass of the HierarchyVisitor that provides the ability to perform a different visitor’s processing before and after iteration. This allows users of this class to implement either pre- or post-order processing based on the HierarchyVisitor. Two helper methods are provided on DelegatingHierarchyVisitor to aid in executing pre- and post-order visitors.

4.4.2. Provided Visitors

The SQLStringVisitor is a special visitor that can traverse a tree of language interfaces and output the equivalent Teiid SQL. This visitor can be used to print language objects for debugging and logging. The SQLStringVisitor does not use the HierarchyVisitor described in the last section; it provides both iteration and processing type functionality in a single custom visitor.

The CollectorVisitor is a handy utility to collect all language objects of a certain type in a tree. Some additional helper methods exist to do common tasks such as retrieving all elements in a tree, retrieving all groups in a tree, and so on.

4.4.3. Writing a Visitor

Writing your own visitor can be quite easy if you use the provided facilities. If the normal method of iterating the language tree is sufficient, then just follow these steps:

Create a subclass of AbstractLanguageVisitor. Override any visit methods needed for your processing. For instance, if you wanted to count the number of elements in the tree, you need only override the visit(IElement) method. Collect any state in local variables and provide accessor methods for that state.

Decide whether to use pre-order or post-order iteration. In many cases, it doesn’t matter, so if you’re not sure, use pre-order processing.

Write code to execute your visitor using the utility methods on DelegatingHierarchyVisitor:

// Get object tree 
LanguageObject objectTree = …

// Create your visitor initialize as necessary
MyVisitor visitor = new MyVisitor();

// Call the visitor using pre-order visitation
DelegatingHierarchyVisitor.preOrderVisit(visitor, objectTree);

// Retrieve state collected while visiting
int count = visitor.getCount();

Often it’s useful to create a static method implementing this sequence of calls within your visitor.

4.5. Connector Capabilities

All connectors must return a ConnectorCapabilities class from the Connection.getCapabilities() method. This class is used by the Connector Manager to determine what kinds of commands the connector is capable of executing. A basic implementation of the ConnectorCapabilities interface is supplied at com.metamatrix.data.basic.BasicConnectorCapabilities. This capabilities class specifies that the connector only executes queries and does not support any capability. Teiid recommends that you extend this class and override the necessary methods to specify which capabilities your connector supports.

Table 4.2. Available Connector Capabilities

Capability	Requires	Description
SelectDistinct		Connector can support SELECT DISTINCT in queries.
Joins		Connector can support joins.
OuterJoins	Joins	Connector can support LEFT and RIGHT OUTER JOIN.
FullOuterJoins	Joins, OuterJoins	Connector can support FULL OUTER JOIN.
AliasedGroup		Connector can support groups in the FROM clause that have an alias.
SelfJoins	Joins, AliasedGroups	Connector can support a self join between two aliased versions of the same group.
InlineViews	AliasedGroup	Connector can support a named subquery in the FROM clause.
Criteria		Connector can support WHERE and HAVING clauses.
RowLimit		Connector can support the limit portion of the limit clause
RowOffset		Connector can support the offset portion of the limit clause
AndCriteria	Criteria	Connector can support AND criteria in join conditions of the FROM clause, the WHERE clause, and the HAVING clause.
OrCriteria	Criteria	Connector can support the OR logical criteria.
NotCriteria	Criteria	Connector can support the NOT logical criteria.
BetweenCriteria	Criteria	Connector can support the BETWEEN predicate criteria.
CompareCriteria	Criteria	Connector can support comparison criteria such as “age > 10”.
CompareCriteriaEquals	Criteria, CompareCriteria	Connector can support comparison criteria with the operator “=”.
CompareCriteriaGreaterThan	Criteria, CompareCriteria	Connector can support comparison criteria with the operator “>”.
CompareCriteriaGreaterThanOrEqual	Criteria, CompareCriteria	Connector can support comparison criteria with the operator “>=”.
CompareCriteriaLessThan	Criteria, CompareCriteria	Connector can support comparison criteria with the operator “<”.
CompareCriteriaLessThanOrEqual	Criteria, CompareCriteria	Connector can support comparison criteria with the operator “<=”.
CompareCriteriaNotEquals	Criteria, CompareCriteria	Connector can support comparison criteria with the operator “<>”.
ExistsCriteria	Criteria	Connector can support EXISTS predicate criteria.
InCriteria	Criteria	Connector can support IN predicate criteria.
InCriteriaSubquery	Criteria, InCriteria	Connector can support IN predicate criteria where values are supplied by a subquery.
IsNullCriteria	Criteria	Connector can support IS NULL predicate criteria.
LikeCriteria	Criteria	Connector can support LIKE criteria.
LikeCriteriaEscapeCharacter	Criteria, LikeCriteria	Connector can support LIKE criteria with an ESCAPE character clause.
QuantifiedCompareCriteria	Criteria, CompareCriteria	Connector can support a quantified comparison criteria with a subquery on the right side.
QuantifiedCompareCriteriaAll	Criteria, CompareCriteria, QuantifiedCompareCriteria	Connector can support a quantified comparison criteria using the ALL quantifier.
QuantifiedCompareCriteriaSome	Criteria, CompareCriteria, QuantifiedCompareCriteria	Connector can support a quantified comparison criteria using the SOME or ANY quantifier.
OrderBy		Connector can support the ORDER BY clause in queries.
Aggregates		Connector can support GROUP BY and HAVING clauses in queries.
AggregatesAvg	Aggregates	Connector can support the AVG aggregate function.
AggregatesCount	Aggregates	Connector can support the COUNT aggregate function.
AggregatesCountStar	Aggregates, AggregatesCount	Connector can support the COUNT(*) aggregate function.
AggregatesDistinct	Aggregates	Connector can support the keyword DISTINCT inside an aggregate function. This keyword indicates that duplicate values within a group of rows will be ignored.
AggregatesMax	Aggregates	Connector can support the MAX aggregate function.
AggregatesMin	Aggregates	Connector can support the MIN aggregate function.
AggregatesSum	Aggregates	Connector can support the SUM aggregate function.
ScalarFunctions		Connector can support scalar functions wherever expressions are accepted.
CaseExpressions		Connector can support “unsearched” CASE expressions anywhere that expressions are accepted.
SearchedCaseExpressions		Connector can support “searched” CASE expressions anywhere that expressions are accepted.
ScalarSubqueries		Connector can support the use of a subquery in a scalar context (wherever an expression is valid).
CorrelatedSubqueries	ScalarSubqueries or QuantifiedCompareCriteria or ExistsCriteria or InCriteriaSubquery	Connector can support a correlated subquery that refers back to an element in the outer query.
SelectLiterals		Connector can support literals in the SELECT clause
Unions		Connector support UNIONs
Intersect		Connector supports INTERSECT
Except		Connector supports Except
SetQueryOrderBy	Unions, Intersect, or Except	Connector supports set queries with an ORDER BY
FunctionsInGroupBy	ScalarFunctions, Aggregates	Connector supports functions in the GROUP BY list
FunctionsInGroupBy	ScalarFunctions, Aggregates	Connector supports functions in the GROUP BY list

4.5.4. Command Form

The method ConnectorCapabilities.useAnsiJoin() should return true if the Connector prefers the use of ANSI style join structure for INNER and CROSS joins that are pushed down.

The method ConnectorCapabilities.requiresCriteria() should return true if the Connector requires criteria for any Query, Update, or Delete. This is a replacement for the model support property "Where All".

4.5.5. Scalar Functions

The method ConnectorCapabilities.getSupportedFunctions() can be used to specify which scalar functions the connector supports. The set of possible functions is based on the set of functions supported by Teiid. This set can be found in the Query Support Booklet documentation. If the connector states that it supports a function, it must support all type combinations and overloaded forms of that function.

There are five operators that can also be specified in the supported function list: +, -, *, /, and ||.

4.5.6. Physical Limits

The method ConnectorCapabilities.getMaxInCriteriaSize() can be used to specify the maximum number of values that can be passed in an IN criteria. This is an important constraint as an IN criteria is frequently used to pass criteria between one source and another using a dependent join.

The method ConnectorCapabilities.getMaxFromGroups() can be used to specify the maximum number of FROM Clause groups that can used in a join. -1 indicates there is no limit.