JBoss.orgCommunity Documentation

Teiid - Scalable Information Integration

Teiid Developer's Guide

7.6

Copyright © 2011 Red Hat, Inc.

Legal Notice

Abstract

This guide contains information for developers creating custom solutions with Teiid. It covers creating JEE JCA connectors with the Teiid framework, Teiid Translators, Teiid User Defined Functions (UDFs) as well as related topics.

1. Developing For Teiid
1.1. Introduction to the Teiid Connector Architecture
1.2. Do You Need a New Translator?
1.2.1. Custom Translators
1.3. Do You Need a New Resource Adapter?
1.3.1. Custom Resource Adapters
1.4. Other Teiid Development
2. Developing JEE Connectors
2.1. Using the Teiid Framework
2.1.1. Define Managed Connection Factory
2.1.2. Define the Connection Factory class
2.1.3. Define the Connection class
2.1.4. XA Transactions
2.1.5. Define the configuration properties in a "ra.xml" file
2.2. Build and Package the Adapter
2.3. Adding Dependent Libraries
2.4. Deploying the Adapter
3. Translator Development
3.1. Setting up the build environment
3.2. Extending the ExecutionFactory Class
3.2.1. ConnectionFactory
3.2.2. Connection
3.2.3. Configuration Properties
3.2.4. Initializing the Translator
3.2.5. TranslatorCapabilities
3.2.6. Execution (and sub-interfaces)
3.2.7. Metadata
3.2.8. Logging
3.2.9. Exceptions
3.3. Connections to Source
3.3.1. Obtaining connections
3.3.2. Releasing Connections
3.4. Executing Commands
3.4.1. Execution Modes
3.4.2. ExecutionContext
3.4.3. ResultSetExecution
3.4.4. Update Execution
3.4.5. Procedure Execution
3.4.6. Asynchronous Executions
3.4.7. Reusable Executions
3.4.8. Bulk Execution
3.4.9. Command Completion
3.4.10. Command Cancellation
3.5. Command Language
3.5.1. Language
3.5.2. Language Utilities
3.5.3. Runtime Metadata
3.5.4. Language Visitors
3.5.5. Translator Capabilities
3.6. Large Objects
3.6.1. Data Types
3.6.2. Why Use Large Object Support?
3.6.3. Handling Large Objects
3.6.4. Inserting or Updating Large Objects
3.7. Dependent Join Pushdown
3.8. Delegating Translator
3.9. Adding Dependent Libraries
3.10. Packaging
3.11. Deployment
4. Extending The JDBC Translator
4.1. Capabilities Extension
4.2. SQL Translation Extension
4.3. Results Translation Extension
4.4. Adding Function Support
4.4.1. Using FunctionModifiers
4.5. Installing Extensions
5. User Defined Functions
5.1. UDF Definition
5.2. Source Supported UDF
5.2.1. Designer based Source Function Pushdown
5.3. Non-pushdown Support for User-Defined Functions
5.3.1. Java Code
5.3.2. Post Code Activities
5.4. Installing user-defined functions
5.5. User Defined Functions in Dynamic VDBs
6. AdminAPI
6.1. Connecting
6.2. Admin Methods
7. Logging
7.1. Customized Logging
7.1.1. Command Logging API
7.1.2. Audit Logging API
8. Custom Security
8.1. Login Modules
8.1.1. Built-in LoginModules
8.1.2. Custom LoginModules
8.2. Custom Authorization
9. Runtime Updates
9.1. Data Updates
9.2. Runtime Metadata Updates
9.2.1. Costing Updates
9.2.2. Schema Updates
A. ra.xml file Template
B. Advanced Topics
B.1. Security Migration From Previous Versions

Chapter 1. Developing For Teiid

1.1. Introduction to the Teiid Connector Architecture
1.2. Do You Need a New Translator?
1.2.1. Custom Translators
1.3. Do You Need a New Resource Adapter?
1.3.1. Custom Resource Adapters
1.4. Other Teiid Development

Integrating data from a Enterprise Information System (EIS) into Teiid, is separated into two parts.

  1. A Translator, which is required.

  2. An optional Resource Adapter, which will typically be a JCA Resource Adapter (also called a JEE Connector)

A Translator is used to:

  1. Translate a Teiid-specific command into a native command,

  2. Execute the command,

  3. Return batches of results translated to expected Teiid types.

A Resource Adapter is used to:

Given a combination of a Translator + Resource Adapter, one can connect any EIS system to Teiid for their data integration needs.

Teiid provides several translators for common enterprise information system types. If you can use one of these enterprise information systems, you do not need to develop a custom one.

Teiid offers the following translators:

JDBC Translator

Works with many relational databases. The JDBC translator is validated against the following database systems: Oracle, Microsoft SQL Server, IBM DB2, MySQL, Postgres, Derby, Sybase, H2, and HSQL. In addition, the JDBC Translator can often be used with other 3rd-party drivers and provides a wide range of extensibility options to specialize behavior against those drivers.

File Translator

Provides a procedural way to access the file system to handle text files.

WS Translator

Provides procedural access to XML content using Web Services.

LDAP Translator

Accesses to LDAP directory services.

Salesforce Translator

Works with Salesforce interfaces.

Below are the high-level steps for creating custom Translators. This guide covers how to do each of these steps in detail. It also provides additional information for advanced topics, such as streaming large objects.

For sample Translator code, refer to the Teiid source code at http://anonsvn.jboss.org/repos/teiid/trunk/connectors/.

  1. Create a new or reuse an existing Resource Adapater for the EIS system, to be used with this Translator.

    Refer to Section 1.3.1, “Custom Resource Adapters”.

  2. Implement the required classes defined by the Translator API.

    • Create an ExecutionFactory – Extend the org.teiid.translator.ExecutionFactory class

    • Create relevant Executions (and sub-interfaces) – specifies how to execute each type of command

    Refer to Chapter 3, Translator Development.

  3. Define the template for exposing configuration properties. Refer to Section 3.10, “Packaging”.

  4. Deploy your Translator. Refer to Section 3.11, “Deployment”.

  5. Deploy a Virtual Database (VDB) that uses your Translator.

  6. Execute queries via Teiid.

As mentioned above, for every Translator that needs to gather data from external source systems, it requires a resource adapter.

The following resource adapters are available to Teiid.

High-level Resource Adapter development procedure:

  1. Understand the JEE Connector specification to have basic idea about what JCA connectors are how they are developed and packaged.

    Refer to http://java.sun.com/j2ee/connector/.

  2. Gather all necessary information about your Enterprise Information System (EIS). You will need to know:

    • API for accessing the system

    • Configuration and connection information for the system

    • Expectation for incoming queries/metadata

    • The processing constructs, or capabilities, supported by information system.

    • Required properties for the connection, such as URL, user name, etc.

  3. Base classes for all of the required supporting JCA SPI classes are provided by the Teiid API. The JCA CCI support is not provided from Teiid, since Teiid uses the Translator API as it's common client interface. You will want to extend:

    • BasicConnectionFactory – Defines the Connection Factory

    • BasicConnection – represents a connection to the source.

    • BasicResourceAdapter – Specifies the resource adapter class

  4. Package your resource adapter. Refer to Section 2.2, “Build and Package the Adapter”.

  5. Deploy your resource adapter. Refer to Section 2.2, “Build and Package the Adapter”.

This guide covers how to do each of these steps in detail. It also provides additional information for advanced topics, such as transactions. For sample resource adapter code refer to the Teiid Source code at http://anonsvn.jboss.org/repos/teiid/trunk/connectors/.

Teiid is highly extensible in other ways:

Chapter 2. Developing JEE Connectors

2.1. Using the Teiid Framework
2.1.1. Define Managed Connection Factory
2.1.2. Define the Connection Factory class
2.1.3. Define the Connection class
2.1.4. XA Transactions
2.1.5. Define the configuration properties in a "ra.xml" file
2.2. Build and Package the Adapter
2.3. Adding Dependent Libraries
2.4. Deploying the Adapter

This chapter examines how to use facilities provided by the Teiid API to develop a JEE JCA Connector. Please note that these are standard JEE JCA connectors, nothing special needs to be done for Teiid. As an aid to our Translator developers, we provided a base implementation framework. If you already have a JCA Connector or some other mechanism to get data from your source system, you can skip this chapter.

If you are not familiar with JCA API, please read the JCA 1.5 Specification at http://java.sun.com/j2ee/connector/. There are lot of online tutorials on how to design and build a JCA Connector. The below we show you to build very simple connector, however building actual connector that supports transactions, security can get much more complex.

Refer to the JBoss Application Server Connectors documentation at http://docs.jboss.org/jbossas/jboss4guide/r4/html/ch7.chapt.html.

If you are going to use the Teiid framework for developing a JCA connector, follow these steps. The required classes are in org.teiid.resource.api package. Please note that Teiid framework does not make use JCA's CCI framework, only the JCA's SPI interfaces.

Extend the BasicManagedConnectionFactory, and provide a implementation for the "createConnectionFactory()" method. This method defines a factory method that can create connections.

This class also defines configuration variables, like user, password, URL etc to connect to the EIS system. Define an attribute for each configuration variable, and then provide both "getter" and "setter" methods for them. Note to use only "java.lang" objects as the attributes, DO NOT use Java primitives for defining and accessing the properties. See the following code for an example.

public class MyManagedConnectionFactory extends BasicManagedConnectionFactory 

{

   @Override

   public Object createConnectionFactory() throws ResourceException 

   {

      return new MyConnectionFactory();

   }


   // config property name (metadata for these are defined inside the ra.xml)

   String userName;

   public String getUserName()          {  return this.userName;  }

   public void setUserName(String name){  this.userName = name;  }


   // config property count  (metadata for these are defined inside the ra.xml)

   Integer count;

   public Integer getCount()            {  return this.count;  }

   public void setCount(Integer value) {  this.count = value; }               


}

Extend the BasicConnectionFactory class, and provide a implementation for the "getConnection()" method.

public class MyConnectionFactory extends BasicConnectionFactory 

{

   @Override

   public MyConnection getConnection() throws ResourceException 

   {

      return new MyConnection();

   }  

}

Since the Managed connection object created the "ConnectionFactory" class it has access to all the configuration parameters, if "getConnection" method needs to do pass any of credentials to the underlying EIS system. The Connection Factory class can also get reference to the calling user's javax.security.auth.Subject during "getConnection" method by calling 

Subject subject = ConnectionContext.getSubject();

This "Subject" object can give access to logged-in user's credentials and roles that are defined. Note that this may be null.

Note that you can define "security-domain" for this resource adapter, that is separate from the Teiid defined "security-domain" for validating the JDBC end user. However, it is users responsibility to make the necessary logins before the Container's thread accesses this resource adapter, and this can get overly complex.

Extend the BasicConnection class, and provide a implementation based on your access of the Connection object in the Translator. If your connection is stateful, then override "isAlive()" and "cleanup()" methods and provide proper implementations. These are called to check if a Connection is stale or need to flush them from the connection pool etc. by the Container.

public class MyConnection extends BasicConnection 

{

   public void doSomeOperation(command)

   {

      // do some operation with EIS system..

      // This is method you use in the Translator, you should know

      // what need to be done here for your source..

   }

     

   @Override

   public boolean isAlive() 

   {

      return true;

   }

   

   @Override

   public void cleanUp() 

   {

         

   }

}

If your EIS source can participate in XA transactions, then on your Connection object, override the "getXAResource()" method and provide the "XAResource" object for the EIS system. Refer to Section 2.1.3, “Define the Connection class”. Also, You need to extend the "BasicResourceAdapter" class and provide implementation for method "public XAResource[] getXAResources(ActivationSpec[] specs)" to participate in crash recovery.

Note that, only when the resource adapters are XA capable, then Teiid can make them participate in a distributed transactions. If they are not XA capable, then source can participate in distributed query but will not participate in the transaction. Transaction semantics are defined by how you you configured "connection-factory" in a "resource-adapter". i.e. jta=true/false.

Define a "ra.xml" file in "META-INF" directory of your RAR file. An example file is provided in Appendix A, ra.xml file Template.

For every attribute defined inside the your ManagedConnectionFactory class, define the following XML configuration for that attribute inside the "ra.xml" file. These properties are used by user to configure instance of this Connector inside a Container. Also, during the startup the Container reads these properties from this file and knows how to inject provided values in the "-ds.xml" file into a instance of "ManagedConnectionFactory" to create the Connection. Refer to Section 2.1.1, “Define Managed Connection Factory”. 


<config-property>

   <description>

      {$display:"${display-name}",$description:"${description}", $allowed="${allowed}", 

      $required="${true|false}", $defaultValue="${default-value}"}

   </description>

   <config-property-name>${property-name}</config-property-name>

   <config-property-type>${property-type}</config-property-type>

   <config-property-value>${optioal-property-value}</config-property-value>

</config-property>

The format and contents of "<description>" element may be used as extended metadata for tooling. The special format must begin and end with curly braces e.g. {...}. This use of the special format and all properties is optional. Property names begin with '$' and are separated from the value with ':'. Double quotes identifies a single value. A pair of square brackets, e.g. [...], containing comma separated double quoted entries denotes a list value.

Extended metadata properties

  • $display: Display name of the property

  • $description: Description about the property

  • $required: The property is a required property; or optional and a default is supplied

  • $allowed: If property value must be in certain set of legal values, this defines all the allowed values

  • $masked: The tools need to mask the property; Do not show in plain text; used for passwords

  • $advanced: Notes this as Advanced property

  • $editable: Property can be modified; or read-only

Note that all these are optional properties; however in the absence of this metadata, Teiid tooling may not work as expected.

Once all the required code is developed, it is time to package them into a RAR artifact, that can be deployed into a Conainer. A RAR artifact is lot more similar to a WAR. To put together a RAR file it really depends upon build system you are using.

Make sure that the RAR file, under its "META-INF" directory has the "ra.xml" file. If you are using maven refer to http://maven.apache.org/plugins/maven-rar-plugin/. In the root of the RAR file, you can embed the JAR file containing your connector code and any dependent library JAR files.

Add MANIFEST.MF file in the MATA-INF directory, and the following line to add the core Teiid API dependencies for resource adapter. Dependencies: org.jboss.teiid.common-core,org.jboss.teiid.api,javax.api If your resource adapter depends upon any other third party jar files, .dll or .so files they can be placed at the root of the rar file. If any of these libraries are already available as modules in JBoss AS, then you can add the module name to the above MANIFEST.MF file to define as dependency.

Once the RAR file is built, deploy it by copying the RAR file into "deploy" directory of JBoss AS's choosen profile. Typically the server does not need to be restarted when a new RAR file is being added. Alternatively, you can also use "admin-console" a web based monitoring and configuration tool to deploy this file into the container.

Once the Connector's RAR file is deployed into the JBoss container, now you can start creating a instance of this connector to be used with your Translator. Creating a instance of this Connector is no different than creating a "Connection Factory" in JBoss AS. Again, you have have two ways you can create a "ConnectionFactory".

Chapter 3. Translator Development

3.1. Setting up the build environment
3.2. Extending the ExecutionFactory Class
3.2.1. ConnectionFactory
3.2.2. Connection
3.2.3. Configuration Properties
3.2.4. Initializing the Translator
3.2.5. TranslatorCapabilities
3.2.6. Execution (and sub-interfaces)
3.2.7. Metadata
3.2.8. Logging
3.2.9. Exceptions
3.3. Connections to Source
3.3.1. Obtaining connections
3.3.2. Releasing Connections
3.4. Executing Commands
3.4.1. Execution Modes
3.4.2. ExecutionContext
3.4.3. ResultSetExecution
3.4.4. Update Execution
3.4.5. Procedure Execution
3.4.6. Asynchronous Executions
3.4.7. Reusable Executions
3.4.8. Bulk Execution
3.4.9. Command Completion
3.4.10. Command Cancellation
3.5. Command Language
3.5.1. Language
3.5.2. Language Utilities
3.5.3. Runtime Metadata
3.5.4. Language Visitors
3.5.5. Translator Capabilities
3.6. Large Objects
3.6.1. Data Types
3.6.2. Why Use Large Object Support?
3.6.3. Handling Large Objects
3.6.4. Inserting or Updating Large Objects
3.7. Dependent Join Pushdown
3.8. Delegating Translator
3.9. Adding Dependent Libraries
3.10. Packaging
3.11. Deployment

For Eclipse create a empty java project and add dependencies to "teiid-common-core", "teiid-api" and JEE "connector-api" jars.

For using maven use following as your dependencies.




    <dependencies>

        <dependency>

            <groupId>org.jboss.teiid</groupId>

            <artifactId>teiid-api</artifactId>

            <scope>provided</scope>

        </dependency>

        <dependency>

            <groupId>org.jboss.teiid</groupId>

            <artifactId>teiid-common-core</artifactId>

            <scope>provided</scope>

        </dependency>

        <dependency>

            <groupId>javax.resource</groupId>

            <artifactId>connector-api</artifactId>

            <scope>provided</scope>

        </dependency>

    </dependencies>        

        

You can find teiid artifacts in the JBoss maven repository

The main class in the translator implementation is ExecutionFactory. A base class is provided in the Teiid API, so a custom translator must extend org.teiid.translator.ExecutionFactory to connect and query an enterprise data source. This extended class must provide a no-arg constructor that can be constructed using Java reflection libraries. This Execution Factory need define/override following elements. 



package org.teiid.translator.custom;


@Translator(name="custom", description="Connect to My EIS")

public class CustomExecutionFactory extends ExecutionFactory<MyConnectionFactory, MyConnection> {


    public CustomExecutionFactory() {

    }

}                

         

Define the annotation @Translator on extended "ExecutionFactory" class. This annotation defines name and description of your translator, and also used as identifier during the deployment. This is name you would be using in the VDB and else where in the configuration to refer to this translator.

Defines the "ConnectionFactory" interface that is defined in resource adapter. This defined as part of class definition of extended "ExecutionFactory" class. Refer to "MyConnectionFactory" sample in the previous chapter.

Defines the "Connection" interface that is defined in resource adapter. This defined as part of class definition of extended "ExecutionFactory" class. Refer to "MyConnection" class sample in the previous chapter.

If the translator requires external configuration, that defines ways for the user to alter the behavior of a program, then define a attribute variable in the class and define "get" and "set" methods for that attribute. Also, annotate each "get" method with @TranslatorProperty annotation and provide the metadata about the property.

For example, if you need a property called "foo", by providing the annotation on these properties, the Teiid tooling can automatically interrogate and provide graphical way to configure your Translator while designing your VDB. 

private String foo = "blah";

@TranslatorProperty(display="Foo property", description="description about Foo") 

public String getFoo() 

{

   return foo;

}


public void setFoo(String value) 

{

   return this.foo = value;

} 

Only java primitive (int, boolean), primitive object wrapper (java.lang.Integer), or Enum types are supported as Translator properties. Complex objects are not supported. The default value will be derived from calling the getter method, if available, on a newly constructed instance. All properties should have a default value. If there is no applicable default, then the property should be marked in the annotation as required. Initialization will fail if a required property value is not provided.

The @TranslatorProperty defines the following metadata that you can define about your property

Override and implement the start method (be sure to call "super.start()") if your translator needs to do any initializing before it is used by the Teiid engine. This method will be called by Teiid, once after all the configuration properties set above are injected into the class.

These are various methods that typically begin with method signature "supports" on the "ExecutionFactory" class. These methods need to be overridden to describe the execution capabilities of the Translator. Refer to Section 3.5.5, “Translator Capabilities” for more on these methods.

Based on types of executions you are supporting, the following methods need to be overridden and need to provide implementations for these methods by extending respective interfaces.

You can choose to implement all the execution modes or just what you need. See more details on this below.

Override and implement the method getMetadata(), if you want to expose the metadata about the source for use in Dynamic VDBs. This defines the tables, column names, procedures, parameters, etc. for use in the query engine. This method is not yet used by Designer tooling.

Teiid provides org.teiid.logging.LogManager class for logging purposes. Create a logging context and use the LogManager to log your messages. These will be automatically sent to the main Teiid logs. You can edit the "jboss-log4j.xml" inside "conf" directory of the JBoss AS's profile to add the custom context. Teiid uses Log4J as its underlying logging system.

If you need to bubble up any exception use org.teiid.translator.TranslatorException class.

The extended "ExecutionFactory" must implement the getConnection() method to allow the Connector Manager to obtain a connection.

Once the Connector Manager has obtained a connection, it will use that connection only for the lifetime of the request.  When the request has completed, the closeConnection() method called on the "ExecutionFactory". You must also override this method to properly close the connection.

In cases (such as when a connection is stateful and expensive to create), connections should be pooled. If the resource adapter is JEE JCA connector based, then pooling is automatically provided by the JBoss AS container. If your resource adapter does not implement the JEE JCA, then connection pooling semantics are left to the user to define on their own.

The Teiid query engine uses the "ExecutionFactory" class to obtain the "Execution" interface for the command it is executing. The actual queries themselves are sent to translators in the form of a set of objects, which are further described in Command Language. Refer to Section 3.5, “Command Language”. Translators are allowed to support any subset of the available execution modes.

Table 3.1. Types of Execution Modes

Execution InterfaceCommand interface(s)Description
ResultSetExecution QueryExpression A query corresponding to a SQL SELECT or set query statement.
UpdateExecution Insert, Update, Delete, BatchedUpdates An insert, update, or delete, corresponding to a SQL INSERT, UPDATE, or DELETE command
ProcedureExecution Call A procedure execution that may return a result set and/or output values.

All of the execution interfaces extend the base Execution interface that defines how executions are cancelled and closed. ProcedureExecution also extends ResultSetExecution, since procedures may also return resultsets.

The org.teiid.translator.ExecutionContext provides a considerable amount of information related to the current execution. An ExecutionContext instance is made available to each Execution. Specific usage is highlighted in this guide where applicable, but you may use any informational getter method as desired. Example usage would include calling ExecutionContext.getRequestId(), ExecutionContext.getSession(), etc. for logging purposes.

The Teiid source meta-hint is used to provide hints directly to source executions via user or transformation queries. See the reference for more on source hints. If specified and applicable, the general and source specific hint will be supplied via the ExecutionContext methods getGeneralHint and getSourceHint. See the source for the OracleExecutionFactory for an example of how this source hint information can be utilized.

Typically most commands executed against translators are QueryExpression. While the command is being executed, the translator provides results via the ResultSetExecution's "next" method. The "next" method should return null to indicate the end of results. Note: the expected batch size can be obtained from the ExecutionContext.getBatchSize() method and used as a hint in fetching results from the EIS.

Each execution returns the update count(s) expected by the update command. If possible BatchedUpdates should be executed atomically. The ExecutionContext.isTransactional() method can be used to determine if the execution is already under a transaction.

Procedure commands correspond to the execution of a stored procedure or some other functional construct. A procedure takes zero or more input values and can return a result set and zero or more output values.  Examples of procedure execution would be a stored procedure in a relational database or a call to a web service.

If a result set is expected when a procedure is executed, all rows from it will be retrieved via the ResultSetExecution interface first. Then, if any output values are expected, they will be retrieved via the getOutputParameterValues() method.

In some scenarios, a translator needs to execute asynchronously and allow the executing thread to perform other work. To allow this, you should Throw a DataNotAvailableExecption during a retrival method, rather than explicitly waiting or sleeping for the results.

Note

A DataNotAvailableException should not be thrown by the execute method. The DataNotAvailableException may take a delay parameter in its constructor to indicate how long the system should wait befor polling for results. Any non-negative value indicates a time until the next polling should be performed. The DataNotAvailableException.NO_POLLING exception (or any DataNotAvailableException with a negative delay) can be thrown to indicate that the execution will call ExecutionContext.dataAvailable() to indicate processing should resume.

Note

Since the exection and the associated connection are not closed until the work has completed, care should be taken if using asynchronous executions that hold a lot of state.

A translator may return instances of ReusableExecutions for the expected Execution objects. There can be one ReusableExecution per query execting node in the processing plan. The lifecycle of a ReusableExecution is different that a normal Execution. After a normal creation/execute/close cycle the ReusableExecution.reset is called for another execution cycle. This may occur indefinitely dependin on how many times a processing node executes its query. The behavior of the close is no different than a regular Execution, that is it may not be called until the end of the statement if lobs are detected and any connection associated with the Execution will also be closed. When the user command is finished, the ReusableExecution.dispose() method will be called.

In general ReusableExecutions are most useful for continuous query execution and will also be implemented using the ExecutionCotext.dataAvailable() method for Section 3.4.6, “Asynchronous Executions”. See the Client Developer's Guide for executing continuous statements. In continuous mode the user query will be continously re-executed. A ReusableExecution allows the same Execution object to be associated with the processing plan for a given processing node for the lifetime of the user query. This can simplify asynch resource management, such as establishing queue listeners.

Non batched Insert, Update, Delete commands may have Literal values marked as multiValued if the capabilities shows support for BulkUpdate. Commands with multiValued Literals represent multiple executions of the same command with different values. As with BatchedUpdates, bulk operations should be executed atomically if possible.

All normal command executions end with the calling of close() on the Execution object.  Your implementation of this method should do the appropriate clean-up work for all state created in the Execution object.

Commands submitted to Teiid may be aborted in several scenarios:

Unlike the other execution methods, which are handled in a single-threaded manner, calls to cancel happen asynchronously with respect to the execution thread.

Your connector implementation may choose to do nothing in response to this cancellation message. In this instance, Teiid will call close() on the execution object after current processing has completed. Implementing the cancel() method allows for faster termination of queries being processed and may allow the underlying data source to terminate its operations faster as well.

Teiid sends commands to your Translator in object form. These classes are all defined in the "org.teiid.language" package. These objects can be combined to represent any possible command that Teiid may send to the Translator. However, it is possible to notify Teiid that your Translator can only accept certain kinds of constructs via the capabilities defined on the "ExecutionFactory" class. Refer to Section 3.5.5, “Translator Capabilities” for more information.

The language objects all extend from the LanguageObject interface. Language objects should be thought of as 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 Translator are in the form of these language trees, where the root of the tree is a subclass of Command. Command has several sub-interfaces, namely:

  • QueryExpression

  • Insert

  • Update

  • Delete

  • BatchedUpdates

  • Call

Important components of these commands are expressions, criteria, and joins, which are examined in closer detail below. For more on the classes and interfaces described here, refer to the Teiid JavaDocs http://docs.jboss.org/teiid/7.6/apidocs.

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 column reference such as "table.EmployeeName" represents a column in a data source and may take on many values while the command is being evaluated.

A criteria is a combination of expressions and operators that evaluates to true, false, or unknown.  Criteria are most commonly used in the WHERE or HAVING clauses.

The FROM clause contains a list of TableReference's.  

A list of TableReference are used by default, in the pushdown query when no outer joins are used. If an outer join is used anywhere in the join tree, there will be a tree of Join s with a single root. This latter form is the ANSI perfered style. If you wish all pushdown queries containing joins to be in ANSI style have the capability "useAnsiJoin" return true. Refer to Section 3.5.5.3, “Command Form” for more information.

QueryExpression is the base for both SELECT queries and set queries. It may optionally take an OrderBy (representing a SQL ORDER BY clause), a Limit (represent a SQL LIMIT clause), or a With (represents a SQL WITH clause).

Each QueryExpression can be a Select describing the expressions (typically elements) being selected and an TableReference specifying the table or tables being selected from, along with any join information.  The Select may optionally also supply an Condition (representing a SQL WHERE clause), a GroupBy (representing a SQL GROUP BY clause), an an Condition (representing a SQL HAVING clause).

A QueryExpression can also be a SetQuery that represents on of the SQL set operations (UNION, INTERSECT, EXCEPT) on two QueryExpression. The all flag may be set to indicate UNION ALL (currently INTERSECT and EXCEPT ALL are not allowed in Teiid)

A With clause contains named QueryExpressions held by WithItems that can be referenced as tables in the main QueryExpression.

Each Insert will have a single NamedTable specifying the table being inserted into.  It will also has a list of ColumnReference specifying the columns of the NamedTable that are being inserted into. It also has InsertValueSource, which will be a list of Expressions (ExpressionValueSource) or a QueryExpression

Each Update will have a single NamedTable specifying the table being updated and list of SetClause entries that specify ColumnReference and Expression pairs for the update. The Update may optionally provide a criteria Condition specifying which rows should be updated.

Each Delete will have a single NamedTable specifying the table being deleted from. It may also optionally have a criteria specifying which rows should be deleted.  

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

Each BatchedUpdates has a list of Command objects (which must be either Insert, Update or Delete) that compose the batch.

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

The Translator API contains an interface TypeFacility that defines data types and provides value translation facilities. This interface can be obtained from calling "getTypeFacility()" method on the "ExecutionFactory" class.

The TypeFacitlity interface has methods that support data type transformation and detection of appropriate runtime or JDBC types.  The TypeFacility.RUNTIME_TYPES and TypeFacility.RUNTIME_NAMES interfaces defines constants for all Teiid runtime data types.  All Expression instances define a data type based on this set of types.  These constants are often needed in understanding or creating language interfaces.

In Translators 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:

Similar to the TypeFacility, you can call "getLanguageFactory()" method on the "ExecutionFactory" to get a reference to the LanguageFactory instance for your translator.  This interface is a factory that can be used to create new instances of all the concrete language interface objects.  

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

Teiid uses a library of metadata, known as "runtime metadata" for each virtual database that is deployed in Teiid. The runtime metadata is a subset of metadata as defined by models in the Teiid models that compose the virtual database.  While builing your VDB in the Designer, you can define what called "Extension Model", that defines any number of arbitary properties on a model and its objects. At runtime, using this runtime metadata interface, you get access to those set properties defined during the design time, to define/hint any execution behavior.

Translator gets access to the RuntimeMetadata interface at the time of Excecution creation. Translators can access runtime metadata by using the interfaces defined in org.teiid.metadata package.  This package defines API representing a Schema, Table, Columns and Procedures, and ways to navigate these objects.

All the language objects extend AbstractMetadataRecord class

Once a metadata record has been obtained, it is possible to use its metadata about that object or to find other related metadata.

The RuntimeMetadata interface is passed in for the creation of an "Execution". See "createExecution" method on the "ExecutionFactory" class. It provides the ability to look up metadata records based on their fully qualified names in the VDB.

Example 3.1. Obtaining Metadata Properties

The process of getting a Table's properties is sometimes needed for translator development.  For example to get the "NameInSource" property or all extension properties:

//getting the Table metadata from an Table is straight-forward

Table table = runtimeMetadata.getTable("table-name");

String contextName = table.getNameInSource();


//The props will contain extension properties

Map<String, String> props = table.getProperties();

The API provides a language visitor framework in the org.teiid.language.visitor package.  The framework provides utilities useful in navigating and extracting information from trees of language objects.

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 AbstractLanguageVisitor 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 translator 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.  

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.  

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(ColumnReference) method.  Collect any state in local variables and provide accessor methods for that state.

Decide whether to use pre-order or post-order iteration. Note that visitation order is based upon syntax ordering of SQL clauses - not processing order.

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();

The ExecutionFactory class defines all the methods that describe the capabilities of a Translator. These are used by the Connector Manager to determine what kinds of commands the translator is capable of executing. A base ExecutionFactory class implements all the basic capabilities methods, which says your translator does not support any capabilities. Your extended ExecutionFactory class must override the the necessary methods to specify which capabilities your translator supports.  You should consult the debug log of query planning (set showplan debug) to see if desired pushdown requires additional capabilities.

Note that if your capabilities will remain unchanged for the lifetime of the translator, since the engine will cache them for reuse by all instances of that translator. Capabilities based on connection/user are not supported.

The following table lists the capabilities that can be specified in the ExecutionFactory class.

Table 3.2. Available Capabilities

Capability

Requires

Description

SelectDistinct

Translator can support SELECT DISTINCT in queries.

SelectExpression

Translator can support SELECT of more than just column references.

AliasedTable

Translator can support Tables in the FROM clause that have an alias.

InnerJoins

Translator can support inner and cross joins

SelfJoins

AliasedGroups and at least on of the join type supports.

Translator can support a self join between two aliased versions of the same Table.

OuterJoins

Translator can support LEFT and RIGHT OUTER JOIN.

FullOuterJoins

Translator can support FULL OUTER JOIN.

DependentJoins

Base join and criteria support

Translator supports dependent join pushdown. See Section 3.7, “Dependent Join Pushdown”. When set the MaxDependentInPredicates and MaxInCriteriaSize values are not used by the engine, rather all independent values are made available to the pushdown command.

InlineViews

AliasedTable

Translator can support a named subquery in the FROM clause.

BetweenCriteria

Not currently used - between criteria is rewriten as compound comparisions.

CompareCriteriaEquals

Translator can support comparison criteria with the operator "=".

CompareCriteriaOrdered

Translator can support comparison criteria with the operator ">" or "<".

LikeCriteria

Translator can support LIKE criteria.

LikeCriteriaEscapeCharacter

LikeCriteria

Translator can support LIKE criteria with an ESCAPE character clause.

SimilarTo

Translator can support SIMILAR TO criteria.

LikeRegexCriteria

Translator can support LIKE_REGEX criteria.

InCriteria

MaxInCriteria

Translator can support IN predicate criteria.

InCriteriaSubquery

Translator can support IN predicate criteria where values are supplied by a subquery.

IsNullCriteria

Translator can support IS NULL predicate criteria.

OrCriteria

Translator can support the OR logical criteria.

NotCriteria

Translator can support the NOT logical criteria. IMPORTANT: This capability also applies to negation of predicates, such as specifying IS NOT NULL, "<=" (not ">"), ">=" (not "<"), etc.

ExistsCriteria

Translator can support EXISTS predicate criteria.

QuantifiedCompareCriteriaAll

Translator can support a quantified comparison criteria using the ALL quantifier.

QuantifiedCompareCriteriaSome

Translator can support a quantified comparison criteria using the SOME or ANY quantifier.

OnlyLiteralComparison

Translator if only Literal comparisons (equality, ordered, like, etc.) are supported for non-join conditions.

Convert(int fromType, int toType)

Used for fine grained control of convert/cast pushdown. If ExecutionFactory.getSupportedFunctions() contains SourceSystemFunctions.CONVERT, then all of the default Teiid pushdown conversions are supported. So typically this method will be implemented when ExecutionFactory.getSupportedFunctions() contains SourceSystemFunctions.CONVERT. See TypeFacility.RUNTIME_CODES for the possible type codes. The engine will does not care about an unnecessary conversion where fromType == toType.

OrderBy

Translator can support the ORDER BY clause in queries.

OrderByUnrelated

OrderBy

Translator can support ORDER BY items that are not directly specified in the select clause.

OrderByNullOrdering

OrderBy

Translator can support ORDER BY items with NULLS FIRST/LAST.

GroupBy

Translator can support an explict GROUP BY clause.

Having

GroupBy

Translator can support the HAVING clause.

AggregatesAvg

Translator can support the AVG aggregate function.

AggregatesCount

Translator can support the COUNT aggregate function.

AggregatesCountStar

Translator can support the COUNT(*) aggregate function.

AggregatesDistinct

At least one of the aggregate functions.

Translator can support the keyword DISTINCT inside an aggregate function.  This keyword indicates that duplicate values within a group of rows will be ignored.

AggregatesMax

Translator can support the MAX aggregate function.

AggregatesMin

Translator can support the MIN aggregate function.

AggregatesSum

Translator can support the SUM aggregate function.

AggregatesEnhancedNumeric

Translator can support the VAR_SAMP, VAR_POP, STDDEV_SAMP, STDDEV_POP aggregate functions.

ScalarSubqueries

Translator can support the use of a subquery in a scalar context (wherever an expression is valid).

CorrelatedSubqueries

At least one of the subquery pushdown capabilities.

Translator can support a correlated subquery that refers to an element in the outer query.

CaseExpressions

Not currently used - simple case is rewriten as searched case.

SearchedCaseExpressions

Translator can support "searched" CASE expressions anywhere that expressions are accepted.

Unions

Translator support UNION and UNION ALL

Intersect

Translator supports INTERSECT

Except

Translator supports Except

SetQueryOrderBy

Unions, Intersect, or Except

Translator supports set queries with an ORDER BY

RowLimit

Translator can support the limit portion of the limit clause

RowOffset

Translator can support the offset portion of the limit clause

FunctionsInGroupBy

GroupBy

Translator can support non-column reference grouping expressions.

InsertWithQueryExpression

Translator supports INSERT statements with values specified by an QueryExpression.

supportsBatchedUpdates

Translator supports a batch of INSERT, UPDATE and DELETE commands to be executed together.

BulkUpdate

Translator supports updates with multiple value sets

CommonTableExpressions

Translator supports the WITH clause.

ElementaryOlapOperations

Translator supports window functions and analytic functions RANK, DENSE_RANK, and ROW_NUMBER.

WindowOrderByWithAggregates

ElementaryOlapOperations

Translator supports windowed aggregates with a window order by clause.

WindowDistinctAggregates

ElementaryOlapOperations, AggregatesDistinct

Translator supports windowed distinct aggregates.

AdvancedOlapOperations

ElementaryOlapOperations

Translator supports aggregate conditions.


Note that any pushdown subquery must itself be compliant with the Translator capabilities.

The method ExecutionFactory.useAnsiJoin() should return true if the Translator prefers the use of ANSI style join structure for join trees that contain only INNER and CROSS joins.

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

The method ExecutionFactory.getSupportedFunctions() can be used to specify which scalar functions the Translator supports.  The set of possible functions is based on the set of functions supported by Teiid. This set can be found in the Reference documentation at http://www.jboss.org/teiid//docs.html. If the Translator states that it supports a function, it must support all type combinations and overloaded forms of that function.

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

The constants interface SourceSystemFunctions contains the string names of all possible built-in pushdown functions. Note that not all system functions appear in this list. This is because some system functions will always be evaluted in Teiid, are simple aliases to other functions, or are rewriten to a more standard expression.

The method ExecutionFactory.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 ExecutionFactory.getMaxDependentInPredicates() is used to specify the maximum number of IN predicates (of at most MaxInCriteriaSize) that can be passed as part of a dependent join. For example if there are 10000 values to pass as part of the dependent join and a MaxInCriteriaSize of 1000 and a MaxDependentInPredicates setting of 5, then the dependent join logic will form two source queries each with 5 IN predicates of 1000 values each combined by OR.

The method ExecutionFactory.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.

The method ExecutionFactory.supportsBatchedUpdates() can be used to indicate that the Translator supports executing the BatchedUpdates command.

The method ExecutionFactory.supportsBulkUpdate() can be used to indicate that the Translator accepts update commands containg multi valued Literals.

Note that if the translator does not support either of these update modes, the query engine will compensate by issuing the updates individually.

The method ExecutionFactory.getDefaultNullOrder() specifies the default null order. Can be one of UNKNOWN, LOW, HIGH, FIRST, LAST. This is only used if ORDER BY is supported, but null ordering is not.

This section examines how to use facilities provided by the Teiid API to use large objects such as blobs, clobs, and xml in your Translator.

Teiid supports three large object runtime data types:  blob, clob, and xml. A blob is a "binary large object", a clob is a "character large object", and "xml" is a "xml document". Columns modeled as a blob, clob, or xml are treated similarly by the translator framework to support memory-safe streaming.  

Teiid allows a Translator to return a large object through the Teiid translator API by just returning a reference to the actual large object.  Access to that LOB will be streamed as appropriate rather than retrieved all at once.  This is useful for several reasons:

  1. Reduces memory usage when returning the result set to the user.

  2. Improves performance by passing less data in the result set.

  3. Allows access to large objects when needed rather than assuming that users will always use the large object data.

  4. Allows the passing of arbitrarily large data values.

However, these benefits can only truly be gained if the Translator itself does not materialize an entire large object all at once.  For example, the Java JDBC API supports a streaming interface for blob and clob data.

The Translator API automatically handles large objects (Blob/Clob/SQLXML) through the creation of special purpose wrapper objects when it retrieves results.

Once the wrapped object is returned, the streaming of LOB is automatically supported. These LOB objects then can for example appear in client results, in user defined functions, or sent to other translators.

A Execution is usually closed and the underlying connection is either closed/released as soon as all rows for that execution have been retrieved. However, LOB objects may need to be read after their initial retrieval of results.  When LOBs are detected the default closing behavior is prevented by setting a flag via the ExecutionContext.keepAlive method.

When the "keepAlive" alive flag is set, then the execution object is only closed when user's Statement is closed.

executionContext.keepExecutionAlive(true);

LOBs will be passed to the Translator in the language objects as Literal containing a java.sql.Blob, java.sql.Clob, or java.sql.SQLXML.  You can use these interfaces to retrieve the data in the large object and use it for insert or update.

Dependent joins are a technique used in federation to reduce the cost of cross source joins. Join values from one side of a join are made available to the other side which reduces the number of tuples needed to preform the join. Translators may indicate support for dependent join pushdown via the supportsDependentJoin capability. The handling of pushdown dependent join queries can be quite complicated. The ordering (if present) and all of the non-dependent criteria constructs on the pushdown command must be honored, but if needed the dependent criteria, which will be a Comparison with a Parameter, may be ignored in part or in total. Pushdown dependent join queries will be instances of Select with the relevant dependent sets available via Select.getDependentSets(). The dependent set is associated to Parameters by id via the Parameter.getDepenentValueId() identifier. The dependent set tuple iterators provide rows that are referenced by the column positions (available via Parameter.getValueIndex()) on the dependent join Comparison criteria right expression. Care should be taken with the tuple values as they may guareenteed to be unique or ordered.

Note

There is no reference implementation of this functionality as all built-in translators rely on the engine to handle breaking up dependent joins into simplier queries.

In some instances you may wish to extend several differnt kinds of translators with the same functionality. Rather than create separate subclasses for each extension, you can use the delegating translator framework which provides you with a proxying mechanism to override translator behavior. It implement a delegating translator, your common translator logic should be added to a subclass of BaseDelegatingExecutionFactory where you can override any of the delegation methods to perform whatever logic you want.

Example 3.2. Example BaseDelegatingExecutionFactory Subclass

@Translator(name="custom-delegator")
public class MyTranslator extends BaseDelegatingExecutionFactory<Object, Object> {
	
	@Override
	public Execution createExecution(Command command,
			ExecutionContext executionContext, RuntimeMetadata metadata,
			Object connection) throws TranslatorException {
		if (command instanceof Select) {
			//modify the command or return a different execution
			...
			
		}
		//the super call will be to the delegate instance
		return super.createExecution(command, executionContext, metadata, connection);
	}
	...	
}


You will bundle and deploy your custom delegating translator is just like any other custom translator development. To you use your delegating translator in a vdb, you define a translator override that wires in the delegate.

Example 3.3. Example Translator Override

<translator type="custom-delegator" name="my-translator">

     <property value="delegateName" name="name of the delegate instance"/>

     <!-- any custom properties you may have on your custom translator -->

</translator>

From the previous example the translator type is custom-delegator. Now my-translator can be used as a translator-name on a source and will proxy all calls to whatever delegate instance you assign.


Note

Note that the delegate instance can be any translator instance, whether configured by it's own translator entry or just the name of a standard translator type.

Add MANIFEST.MF file in the MATA-INF directory, and the following line to add the core Teiid API dependencies for resource adapter. Dependencies: org.jboss.teiid.common-core,org.jboss.teiid.api,javax.api If your translator depends upon any other third party jar files, then those jar files need to be defined under a module and then you can add the module name to the above MANIFEST.MF file to define as dependency.

Once the "ExecutionFactory" class is implemented, package it in a JAR file. Then add the following named file in "META-INF/services/org.teiid.translator.ExecutionFactory" with contents specifying the name of your main Translator file. Note that, the name must exactly match to above. This is java's standard service loader pattern. This will register the Translator for deployment when the jar is deployed into JBoss AS. 




org.teiid.translator.custom.CustomExecutionFactory        

        

Copy the JAR file that defines the Translator into "deploy" directory of the JBoss AS's chosen profile, and the Translator will be deployed automatically. There is no restriction that, JBoss AS need to be restarted. However, if your Translator has external dependencies to other modules, they need to be placed inside the "modules" directory of the JBoss AS. This will require a restart of the JBoss Server. You can also use JBoss CLI, Admin API, admin-consle to deploy the jar file into JBoss AS server.

Chapter 4. Extending The JDBC Translator

4.1. Capabilities Extension
4.2. SQL Translation Extension
4.3. Results Translation Extension
4.4. Adding Function Support
4.4.1. Using FunctionModifiers
4.5. Installing Extensions

The JDBC Translator can be extended to handle new JDBC drivers and database versions. This is one of the most common needs of custom Translator development. This chapter outlines the process by which a user can modify the behavior of the JDBC Translator for a new source, rather than starting from scratch.

To design a JDBC Translator for any RDMS that is not already provided by the Teiid, extend the org.teiid.translator.jdbc.JDBCExecutionFactory class in the "translator-jdbc" module. There are three types of methods that you can override from the base class to define the behavior of the Translator.

Table 4.1. Extensions

Extension

Purpose

Capabilities

Specify the SQL syntax and functions the source supports.

SQL Translation

Customize what SQL syntax is used, how source-specific functions are supported, how procedures are executed.

Results Translation

Customize how results are retrieved from JDBC and translated.


This extension must override the methods that begin with "supports" that describe translator capabilities. Refer to Section 3.5.5, “Translator Capabilities” for all the available translator capabilities.

The most common example is adding support for a scalar function – this requires both declaring that the translator has the capability to execute the function and often modifying the SQL Translator to translate the function appropriately for the source.

Another common example is turning off unsupported SQL capabilities (such as outer joins or subqueries) for less sophisticated JDBC sources.

The JDBCExcecutionFactory provides several methods to modify the command and the string form of the resulting syntax before it is sent to the JDBC driver, including:

Refer to Section 4.4.1, “Using FunctionModifiers”.

The JDBCExecutionFactory provides several methods to modify the java.sql.Statement and java.sql.ResultSet interactions, including:

  1. Overriding the createXXXExecution to subclass the corresponding JDBCXXXExecution. The JDBCBaseExecution has protected methods to get the appropriate statement (getStatement, getPreparedStatement, getCallableStatement) and to bind prepared statement values bindPreparedStatementValues.

  2. Retrieve values from the JDBC ResultSet or CallableStatement - see the retrieveValue methods.

Refer to Chapter 5, User Defined Functions for adding new functions to Teiid. This example will show you how to declare support for the function and modify how the function is passed to the data source.

Following is a summary of all coding steps in supporting a new scalar function:

  1. Override the capabilities method to declare support for the function (REQUIRED)

  2. Implement a FunctionModifier to change how a function is translated and register it for use (OPTIONAL)

There is a capabilities method getSupportedFunctions() that declares all supported scalar functions.

An example of an extended capabilities class to add support for the "abs" absolute value function:

package my.connector;


import java.util.ArrayList;

import java.util.List;


public class ExtendedJDBCExecutionFactory extends JDBCExecutionFactory 

{

   @Override

   public List getSupportedFunctions() 

   {

      List supportedFunctions = new ArrayList();

      supportedFunctions.addAll(super.getSupportedFunctions());

      supportedFunctions.add("ABS"); 

      return supportedFunctions;

   }

}

In general, it is a good idea to call super.getSupportedFunctions() to ensure that you retain any function support provided by the translator you are extending.

This may be all that is needed to support a Teiid function if the JDBC data source supports the same syntax as Teiid. The built-in SQL translation will translate most functions as: "function(arg1, arg2, …)".

In some cases you may need to translate the function differently or even insert additional function calls above or below the function being translated. The JDBC translator provides an abstract class FunctionModifier for this purpose.

During the start method a modifier instance can be registered against a given function name via a call to JDBCExecutionFactory.registerFunctionModifier.

The FunctionModifier has a method called translate. Use the translate method to change the way the function is represented.

An example of overriding the translate method to change the MOD(a, b) function into an infix operator for Sybase (a % b). The translate method returns a list of strings and language objects that will be assembled by the translator into a final string. The strings will be used as is and the language objects will be further processed by the translator.

public class ModFunctionModifier implements FunctionModifier 

{

   public List translate(Function function) 

   {

      List parts = new ArrayList();

      parts.add("(");        

      Expression[] args = function.getParameters();

      parts.add(args[0]);

      parts.add(" % "); 

      parts.add(args[1]);

      parts.add(")");    

      return parts;

   }

}

In addition to building your own FunctionModifiers, there are a number of pre-built generic function modifiers that are provided with the translator.

Table 4.2. Common Modifiers

Modifier

Description

AliasModifier

Handles simply renaming a function ("ucase" to "upper" for example)

EscapeSyntaxModifier

Wraps a function in the standard JDBC escape syntax for functions: {fn xxxx()}


To register the function modifiers for your supported functions, you must call the ExecutionFactory.registerFunctionModifier(String name, FunctionModifier modifier) method. 

public class ExtendedJDBCExecutionFactory extends JDBCExecutionFactory

{              

   @Override

   public void start() 

   {

      super.start();


      // register functions.

      registerFunctionModifier("abs", new MyAbsModifier()); 

      registerFunctionModifier("concat", new AliasModifier("concat2")); 

   }

}

Support for the two functions being registered ("abs" and "concat") must be declared in the capabilities as well. Functions that do not have modifiers registered will be translated as usual.

Once you have developed an extension to the JDBC translator, you must install it into the Teiid Server. The process of packaging or deploying the extended JDBC translators is exactly as any other other translator. Since the RDMS is accessible already through its JDBC driver, there is no need to develop a resource adapter for this source as JBoss AS provides a wrapper JCA connector (DataSource) for any JDBC driver.

Refer to Section 3.10, “Packaging” and Section 3.11, “Deployment” for more details.

Chapter 5. User Defined Functions

5.1. UDF Definition
5.2. Source Supported UDF
5.2.1. Designer based Source Function Pushdown
5.3. Non-pushdown Support for User-Defined Functions
5.3.1. Java Code
5.3.2. Post Code Activities
5.4. Installing user-defined functions
5.5. User Defined Functions in Dynamic VDBs

If you need to extends Teiid's scalar function library, then Teiid provides a means to define custom scalar functions or User Defined Functions(UDF). The following steps need to be taken in creating a UDF.

A {FunctionDefinition}.xmi file provides metadata to the query engine on User Defined Functions. See the Designer Documentation for more on creating a Function Definition Model.

The following are used to define a UDF.

Even pushdown required functions need to be added as a UDF to allow Teiid to properly parse and resolve the function. Pushdown scalar functions differ from normal user-defined functions in that no code is provided for evaluation in the engine. An exception will be raised if a pushdown required function cannot be evaluated by the appropriate source.

Dynamic VDBs

Currently there is no provision to add UDF when you are working with the Dynamic VDBs. However, you can extend the Translator to define source pushdown functions.

While Teiid provides an extensive scalar function library, it contains only those functions that can be evaluated within the query engine. In many circumstances, especially for performance, a user defined function allows for calling a source specific function.

For example, suppose you want to use the Oracle-specific functions score and contains: 

SELECT score(1), ID, FREEDATA FROM Docs WHERE contains(freedata, 'nick', 1) > 0

The score and contains functions are not part of built-in scalar function library. While you could write your own custom scalar function to mimic their behavior, it's more likely that you would want to use the actual Oracle functions that are provided by Oracle when using the Oracle Free Text functionality.

In addition to the normal steps outlined in the section to create and install a function model (FunctionDefinitions.xmi), you will need to extend the appropriate translator(s).

For example, to extend the Oracle Connector

If you are designing your VDB using the Designer, you can define a function on any "source" model, and that function is automatically added as pushdown function when the VDB is deployed. There is no additional need for adding Java code.

Non-pushdown support requires a Java function that matches the metadata supplied in the FunctionDefinitions.xmi file. You must create a Java method that contains the function’s logic. This Java method should accept the necessary arguments, which the Teiid System will pass to it at runtime, and function should return the calculated or altered value.

Code Requirements

You may optionally add an additional org.teiid.CommandContext argument as the first parameter. The CommandContext interface provides access to information about the current command, such as the executing user, Subject, the vdb, the session id, etc. This CommandContext parameter should not be delared in the function metadata.

Example 5.1. Sample code

package org.something;


public class TempConv 

{

   /**

   * Converts the given Celsius temperature to Fahrenheit, and returns the

   * value.

   * @param doubleCelsiusTemp 

   * @return Fahrenheit 

   */

   public static Double celsiusToFahrenheit(Double doubleCelsiusTemp)

   {

      if (doubleCelsiusTemp == null) 

      {

         return null;

      }

      return (doubleCelsiusTemp)*9/5 + 32;

   }

}

Example 5.2. Sample CommandContext Usage

package org.something;


public class SessionInfo 

{

   /**

   * @param context 

   * @return the created Timestamp 

   */

   public static Timestamp sessionCreated(CommandContext context)

   {

      return new Timestamp(context.getSession().getCreatedTime());

   }

}

The corresponding UDF would be declared as Timestamp sessionCreated().


  1. After coding the functions you should compile the Java code into a Java Archive (JAR) file.

  2. The JAR file needs be attached to the VDB file under "lib" directory.

Once a user-defined function model (FunctionDefinitions.xmi) has been created in in the Designer Tool, it can be added to the VDB for use by Teiid.

Dynamic VDBs do not use Designer generated artifacts, such as a FunctionDefinition.xmi file. Even with that limitation dynamic vdbs may still utilize UDFs through custom coding. The ExecutionFactory.getMetadata call allows for the definition of metadata via a MetadataFactory. Use the MetadataFactory.addFunction to add function for use only by that translator instance. Functions added directly to the source schema are specific to that schema - their fully qualified name will include the schema and the function can not be pushed to a different source.

The ExecutionFactory.getPushdownFunctions method can be used to describe functions that are valid against all instances of a given translator type. The function names are expected to be prefixed by the translator type, or some other logical grouping, e.g. salesforce.includes. The full name of the function once imported into the system will qualified by the SYS schema, e.g. SYS.salesforce.includes.

Any funcitons added via these mechanisms do not need to be declared in ExecutionFactory.getSupportedFunctions. Any of the additional handling, such as adding a FunctionModifier, covered above is also applicable here. All pushdown functions will have function name set to only the simple name. Schema or other qualification will be removed. Handling, such as function modifiers, can check the function metadata if there is the potential for an ambiguity.

Chapter 6. AdminAPI

6.1. Connecting
6.2. Admin Methods

In most circumstances the admin operations will be performed through the admin console or AdminShell tooling, but it is also possible to invoke admin functionality directly in Java through the AdminAPI.

All classes for the AdminAPI are in the client jar under the org.teiid.adminapi package.

An AdminAPI connection, which is represented by the org.teiid.adminapi.Admin interface, is obtained through the org.teiid.adminapi.AdminFactory.createAdmin methods. AdminFactory is a singleton, see AdminFactory.getInstance(). The Admin instance automatically tests its connection and reconnects to a server in the event of a failure. The close method should be called to terminate the connection.

See your JBoss installation for the appropriate admin port - the default port is 9999.

Admin methods exist for monitoring, server administration, and configuration purposes. Note that the objects returned by the monitoring methods, such as getRequests, are read-only and cannot be used to change server state. See the JavaDocs for all of the details.

Chapter 7. Logging

7.1. Customized Logging
7.1.1. Command Logging API
7.1.2. Audit Logging API

The Teiid system provides a wealth of information using logging. To control logging level, contexts, and log locations, you should be familiar with container's standalone.xml or domain.xml configuration fileand check out "logging" subsystem. Refer to the Administrator Guide for more details about different Teiid contexts available. Refer to http://logging.apache.org/log4j/ for more information about log4j.

If you want a custom appender, follow the Log4J directions to write a custom appender. Refer to the instructions at http://logging.apache.org/log4net/release/faq.html. If you develop a custom logging solution, the implementation jar should be placed as a jar in "org.jboss.teiid" module and define its name in the module.xml file as part of the module.

If you want to build a custom appender for command logging that will have access to log4j "LoggingEvents" to the "COMMAND_LOG" context, the appender will receive a message that is an instance of org.teiid.logging.CommandLogMessage. The relevant Teiid classes are defined in the teiid-api-7.6.jar. The CommmdLogMessage includes information about vdb, session, command sql, etc. CommandLogMessages are logged at the DEBUG level.

Example 7.1. Sample CommandLogMessage Usage

package org.something;

import org.apache.log4j.AppenderSkeleton;

import org.apache.log4j.spi.LoggingEvent;

import org.teiid.logging.*;


public class CustomAppender extends AppenderSkeleton 

{

  

  protected void append(LoggingEvent event) {

    if (event.getMessage() instanceof CommandLogMessage) {

      CommandLogMessage clMessage = (CommandLogMessage)event.getMessage();

      String sql = clMessage.getSql();

      ...

      //log to a database, trigger an email, etc.

    }

    ...

  }

  

  ...

  

}

If you want to build a custom appender for command logging that will have access to log4j "LoggingEvents" to the "org.teiid.AUDIT_LOG" context, the appender will receive a message that is an instance of org.teiid.logging.AuditMessage. The relevant Teiid classes are defined in the teiid-api-7.6.jar. The AuditMessage includes information about user, the action, and the target(s) of the action. AuditMessages are logged at the DEBUG level.

Example 7.2. Sample AuditMessage Usage

package org.something;

import org.apache.log4j.AppenderSkeleton;

import org.apache.log4j.spi.LoggingEvent;

import org.teiid.logging.*;


public class CustomAppender extends AppenderSkeleton 

{

  

  protected void append(LoggingEvent event) {

    if (event.getMessage() instanceof AuditMessage) {

      AuditMessage auditMessage = (AuditMessage)event.getMessage();

      String activity = auditMessage.getActivity();

      ...

      //log to a database, trigger an email, etc.

    }

    ...

  }

  

  ...

  

}

Chapter 8. Custom Security

8.1. Login Modules
8.1.1. Built-in LoginModules
8.1.2. Custom LoginModules
8.2. Custom Authorization

The Teiid system provides a range of built-in and extensible security features to enable the secure access of data.  For details about how to configure the available security features check out Admin Guide.

LoginModules are an essential part of the JAAS security framework and provide Teiid customizable user authentication and the ability to reuse existing LoginModules defined for JBossAS. Refer to the JBoss Application Server security documentation for information about configuring security in JBoss Application Server, http://docs.jboss.org/jbossas/admindevel326/html/ch8.chapter.html.

JBoss Application Server provides several LoginModules for common authentication needs, such as authenticating from text files or LDAP.

Below are are some of those available in JBoss Application Server:

UserRoles LoginModule

Login module that uses simple file based authentication.

Refer to http://community.jboss.org/docs/DOC-12510.

LDAP LoginModule

Login module that uses LDAP based authentication.

Refer to http://community.jboss.org/docs/DOC-11253.

Database LoginModule

Login module that uses Database-based authentication.

Refer to http://community.jboss.org/docs/DOC-9511.

Cert LoginModule

Login module that uses X509 certificate based authentication.

See http://community.jboss.org/docs/DOC-9160.

For all the available login modules refer to http://community.jboss.org/docs/DOC-11287.

If your authentication needs go beyond the provided LoginModules, please refer to the JAAS development guide at http://java.sun.com/j2se/1.5.0/docs/guide/security/jaas/JAASLMDevGuide.html. There are also numerous guides available.

If you are extending one of the built-in LoginModules, refer to http://community.jboss.org/docs/DOC-9466.

In situations where Teiid's built-in role mechanism is not sufficient, a org.teiid.PolicyDecider can be installed via JBoss module. Extend the org.teiid.PolicyDecider interface and build a custom java class. If you are using maven as your build process, you can use following dependencies




   <dependencies>

      <dependency>

         <groupId>org.jboss.teiid</groupId>

         <artifactId>teiid-api</artifactId>

         <scope>provided</scope>

      </dependency>

      <dependency>

         <groupId>org.jboss.teiid</groupId>

         <artifactId>teiid-common-core</artifactId>

         <scope>provided</scope>

      </dependency>

   </dependencies>

    

For adding Teiid API dependencies, add MANIFEST.MF file in the MATA-INF directory, and the following line. Dependencies: org.jboss.teiid.common-core,org.jboss.teiid.api,javax.api. PoilcyDecider is loaded by the Teiid using the Java's standard service loader mechanism. For this add the following named file META-INF/services/org.teiid.PolicyDecider with full name of your PolicyDecider as the contents. Now package all these files into a jar file and build JBoss module in >jboss-as</modules directory. If your PolicyDecider has any third party dependencies those jar files can also be added to the same module. Make sure you list all the files in the module.xml file.

After the module has been added then configuration needs to be changed before the changes can take effect. Edit either standalone-teiid.xml or domain-teiid.xml file, and in the "teiid" subsystem xml fragment add the following xml with the module name created. 

<!-- XML : generated by JHighlight v1.0 (http://jhighlight.dev.java.net) -->
<span class="xml_plain"></span><br />
<span class="xml_plain">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span><span class="xml_tag_symbols">&lt;</span><span class="xml_tag_name">policy-decider</span><span class="xml_plain">&nbsp;</span><span class="xml_attribute_name">module</span><span class="xml_tag_symbols">=</span><span class="xml_attribute_value">&quot;module-name&quot;</span><span class="xml_tag_symbols">/&gt;</span><span class="xml_plain"></span><br />
<span class="xml_plain">&nbsp;&nbsp;&nbsp;&nbsp;</span><br />

then restart the system. A PolicyDecider may be consulted many times for a single user command, but it is only called to make decisions based upon resources that appear in user queries. Any further access of resources through views or stored procedures, just as with data roles, is not checked against a PolicyDecider.

Chapter 9. Runtime Updates

9.1. Data Updates
9.2. Runtime Metadata Updates
9.2.1. Costing Updates
9.2.2. Schema Updates

Teiid supports several mechanisms for updating the runtime system.

Data change events are used by Teiid to invalidate result set cache entries. Result set cache entries are tracked by the tables that contributed to their results. By default Teiid will capture internal data events against physical sources and distribute them across the cluster. This approach has several limitations. First updates are scoped only to their originating VDB/version. Second updates made out side of Teiid are not captured. To increase data consistency external change data capture tools can be used to send events to Teiid. From within a Teiid cluster the org.teiid.events.EventDistributorFactory and org.teiid.events.EventDistributor can be used to distribute change events. The EventDistributorFactory can be looked up by its name "teiid/event-distributor-factory". See the example below.

Example 9.1. Usage of the EventDistributor

InitialContext ctx = new InitialContext();
EventDistributorFactory edf = (EventDistributorFactory)ctx.lookup("teiid/event-distributor-factory");
EventDistributor ed = edf.getEventDistributor();
ed.dataModification(vdbName, vdbVersion, schema, tableName);

This will distribute a change event for schema.tableName in vdb vdbName.vdbVersion.


When externally capturing all update events, "detect-change-events" property in the teiid substem in <jboss-install>/standalone/configuration/standalone-teiid.xml can be set to false, to not duplicate change events.

The use of the other EventDistributor methods to manual distribute other events is not recommended.

Runtime updates via system procedures and DDL statements are by default ephemeral. They are effective across the cluster only for the currently running vdbs. With the next vdb start the values will revert to whatever is stored in the vdb. Updates may be made persistent though by configuring a org.teiid.metadata.MetadataRepository. An instance of a MetadataRepository can be installed via the file in the teiid subsystem using "metadata-repository-module" property. The MetadataRepository repository instance may fully implement as many of the methods as needed and return null from any unneeded getter.

Note

It is not recommended to directly manipulate org.teiid.metadata.AbstractMetadataRecord instances. System procedures and DDL statements should be used instead since the effects will be distributed through the cluster and will not introduce inconsistencies.

org.teiid.metadata.AbstractMetadataRecord objects passed to the MetadataRepository have not yet been modified. If the MetadataRepository cannot persist the update, then a RuntimeException should be thrown to prevent the update from being applied by the runtime engine.

Note

The MetadataRepository can be accessed by multiple threads both during load (if using dynamic vdbs) or at runtime with through DDL statements. Your implementation should handle any needed synchronization.

See the Reference for the system procedures SYSADMIN.setColumnStats and SYSADMIN.setTableStats. To make costing updates persistent MetadataRepository implementations should be provided for: 

TableStats getTableStats(String vdbName, int vdbVersion, Table table);
void setTableStats(String vdbName, int vdbVersion, Table table, TableStats tableStats);
ColumnStats getColumnStats(String vdbName, int vdbVersion, Column column);
void setColumnStats(String vdbName, int vdbVersion, Column column, ColumnStats columnStats);

See the Reference for supported DDL statements. To make schema updates persistent implementations should be provided for:

String getViewDefinition(String vdbName, int vdbVersion, Table table);
void setViewDefinition(String vdbName, int vdbVersion, Table table, String viewDefinition);
String getInsteadOfTriggerDefinition(String vdbName, int vdbVersion, Table table, Table.TriggerEvent triggerOperation);
void setInsteadOfTriggerDefinition(String vdbName, int vdbVersion, Table table, Table.TriggerEvent triggerOperation, String triggerDefinition);
boolean isInsteadOfTriggerEnabled(String vdbName, int vdbVersion, Table table, Table.TriggerEvent triggerOperation);
void setInsteadOfTriggerEnabled(String vdbName, int vdbVersion, Table table, Table.TriggerEvent triggerOperation, boolean enabled);
String getProcedureDefinition(String vdbName, int vdbVersion, Procedure procedure);
void setProcedureDefinition(String vdbName, int vdbVersion, Procedure procedure, String procedureDefinition);			
LinkedHashMap<String, String> getProperties(String vdbName, int vdbVersion, AbstractMetadataRecord record);
void setProperty(String vdbName, int vdbVersion, AbstractMetadataRecord record, String name, String value);

Appendix A. ra.xml file Template

This appendix contains an example of the ra.xml file that can be used as a template when creating a new Connector.


<?xml version="1.0" encoding="UTF-8"?>

<connector xmlns="http://java.sun.com/xml/ns/j2ee"

   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

   xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee

   http://java.sun.com/xml/ns/j2ee/connector_1_5.xsd" version="1.5">



   <vendor-name>${comapany-name}</vendor-name>

   <eis-type>${type-of-connector}</eis-type>

   <resourceadapter-version>1.0</resourceadapter-version>

   <license>

      <description>${license text}</description>

      <license-required>true</license-required>

   </license>



   <resourceadapter>

      <resourceadapter-class>org.teiid.resource.spi.BasicResourceAdapter</resourceadapter-class>

      <outbound-resourceadapter>

         <connection-definition>

            <managedconnectionfactory-class>${connection-factory}</managedconnectionfactory-class>



            <!-- repeat for every configuration property -->

            <config-property>

               <description>

                  {$display:"${short-name}",$description:"${description}",$allowed:[${value-list}], 

                  $required:"${required-boolean}", $defaultValue:"${default-value}"}

               </description>

               <config-property-name>${property-name}</config-property-name>

               <config-property-type>${property-type}</config-property-type>

               <config-property-value>${optional-property-value}</config-property-value>

            </config-property>



            <!-- use the below as is if you used the Connection Factory interface -->

            <connectionfactory-interface>

               javax.resource.cci.ConnectionFactory

            </connectionfactory-interface>



            <connectionfactory-impl-class>

               org.teiid.resource.spi.WrappedConnectionFactory

            </connectionfactory-impl-class>



            <connection-interface>

               javax.resource.cci.Connection

            </connection-interface>



            <connection-impl-class>

               org.teiid.resource.spi.WrappedConnection

            </connection-impl-class>



         </connection-definition>



         <transaction-support>NoTransaction</transaction-support>



         <authentication-mechanism>

            <authentication-mechanism-type>BasicPassword</authentication-mechanism-type>

            <credential-interface>

               javax.resource.spi.security.PasswordCredential

            </credential-interface>

         </authentication-mechanism>

         <reauthentication-support>false</reauthentication-support>



      </outbound-resourceadapter>



   </resourceadapter>



</connector>

${...} indicates a value to be supplied by the developer.

Appendix B. Advanced Topics

It is recommended that customers who have utilized the internal JDBC membership domain from releases prior to MetaMatrix 5.5 migrate those users and groups to an LDAP compliant directory server.

Refer to the JBoss Application Server security documentation for using an LDAP directory server. If there are additional questions or the need for guidance in the migration process, please contact technical support.

Several free and open source directory servers include: