<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE chapter PUBLIC "-//OASIS//DTD DocBook XML V4.5//EN" "http://www.oasis-open.org/docbook/xml/4.5/docbookx.dtd">
<chapter id="extendingjdbc">
  <title>Extending The JDBC Translator</title>
  <para>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.</para>
 
    <para>To design a JDBC Translator for any RDMS that is not already provided by the Teiid, extend the 
    <code>org.teiid.translator.jdbc.JDBCExecutionFactory</code> 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.</para>

      <table frame='all'>
        <title>Extensions</title>
        <tgroup cols='2' align='left' colsep='1' rowsep='1'>
          <colspec colname='c1' colwidth="1*"/>
          <colspec colname='c2' colwidth="2*"/>
          <thead>      
            <row>
              <entry><para>Extension</para></entry>
              <entry><para>Purpose</para></entry>
            </row>
          </thead>
          <tbody>
            <row>
              <entry><para>Capabilities</para></entry>
              <entry><para>Specify the SQL syntax and functions the source supports.</para></entry>
            </row>
            <row>
              <entry><para>SQL Translation</para></entry>
              <entry><para>Customize what SQL syntax is used, how source-specific functions are supported, how procedures are executed.</para></entry>
            </row>
            <row>
              <entry><para>Results Translation</para></entry>
              <entry><para>Customize how results are retrieved from JDBC and translated.</para></entry>
            </row>            
          </tbody>
        </tgroup>
      </table>
      
      <section>
        <title>Capabilities Extension</title>
        <para>This extension must override the methods that begin with "supports" that describe translator capabilities.
        Refer to <xref linkend="translator_capabilities"/> for all the available translator capabilities.</para>
        
        <para>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.</para>
        <para>Another common example is turning off unsupported SQL capabilities (such as outer joins or subqueries) 
        for less sophisticated JDBC sources. </para>        
      </section>

      <section>
        <title>SQL Translation Extension</title>
        <para>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:</para>
        
        <itemizedlist>
          <listitem>
          	<para>Change basic SQL syntax options.  See the useXXX methods, e.g. useSelectLimit returns true for SQLServer to indicate that limits are applied in the SELECT clause.</para>
          </listitem>
          <listitem>
            <para>Register one or more FunctionModifiers that define how a scalar function should be modified or transformed.</para>
          </listitem>
          <listitem>
            <para>Modify a LanguageObject. - see the translate, translateXXX, and FunctionModifiers.translate methods.  Modify the passed in object and return null to indicate that the standard syntax output should be used.</para>
          </listitem>
          <listitem>
            <para>Change the way SQL strings are formed for a LanguageObject. - - see the translate, translateXXX, and FunctionModifiers.translate methods.  Return a list of parts, which can contain strings and LanguageObjects, that will be appended in order to the SQL string.  If the in coming LanguageObject appears in the returned list it will not be translated again.</para>
          </listitem>
      </itemizedlist>
      
      <para>
      Refer to <xref linkend="function_modifiers"/>.
      </para>
      
      </section>
      
      <section>
        <title>Results Translation Extension</title>
        <para>The JDBCExecutionFactory provides several methods to modify the java.sql.Statement and java.sql.ResultSet interactions, including:</para>
        <orderedlist>
          <listitem>
            <para>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.</para>
          </listitem>
          <listitem>
            <para>Retrieve values from the JDBC ResultSet or CallableStatement - see the retrieveValue methods.</para>
          </listitem>
      </orderedlist>       
      
      </section>
    
      <section>
    <title>Adding Function Support</title>
      <para>Refer to <xref linkend="udfs"/> 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.</para>
      <para>Following is a summary of all coding steps in supporting a new scalar function:</para>
      <orderedlist>
          <listitem>
            <para>Override the capabilities method to declare support for the function (REQUIRED)</para>
          </listitem>
          <listitem>
            <para>Implement a FunctionModifier to change how a function is translated and register it for use (OPTIONAL)</para>
          </listitem>
      </orderedlist>      
      <para>There is a capabilities method getSupportedFunctions() that declares all supported scalar functions.</para>
      <informalexample>
      	<para>An example of an extended capabilities class to add support for the "abs" absolute value function:</para>
        <programlisting language="Java" role="JAVA"><![CDATA[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;
   }
}]]></programlisting></informalexample>      
          <para>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.</para>
          <para>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, …)".</para>       
    <section id="function_modifiers">
      <title>Using FunctionModifiers</title>
      <para>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 <code>FunctionModifier</code> for this purpose.</para>
	  <para>During the start method a modifier instance can be registered against a given function name via a call to <code>JDBCExecutionFactory.registerFunctionModifier</code>.</para>      
      <para>The FunctionModifier has a method called <code>translate</code>.  Use the translate method to change the way the function is represented.</para>

<informalexample>      
      <para>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.</para>
      
      <programlisting language="Java" role="JAVA"><![CDATA[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;
   }
}]]></programlisting> 
</informalexample>   
      
      <para>In addition to building your own FunctionModifiers, there are a number of pre-built generic 
      function modifiers that are provided with the translator.  </para>
      
      <table frame='all'>
        <title>Common Modifiers</title>
        <tgroup cols='2' align='left' colsep='1' rowsep='1'>
          <colspec colname='c1' colwidth="2*"/>
          <colspec colname='c2' colwidth="5*"/>
          <thead>      
            <row>
              <entry><para>Modifier</para></entry>
              <entry><para>Description</para></entry>
            </row>
          </thead>
          <tbody>
            <row>
              <entry><para>AliasModifier</para></entry>
              <entry><para>Handles simply renaming a function ("ucase" to "upper" for example)</para></entry>
            </row>
            <row>
              <entry><para>EscapeSyntaxModifier</para></entry>
              <entry><para>Wraps a function in the standard JDBC escape syntax for functions: {fn xxxx()}</para></entry>
            </row>            
          </tbody>
        </tgroup>
      </table>
      <para>To register the function modifiers for your supported functions, 
      you must call the <code>ExecutionFactory.registerFunctionModifier(String name, FunctionModifier modifier)</code> method.
      </para>
      <programlisting language="Java" role="JAVA"><![CDATA[public class ExtendedJDBCExecutionFactory extends JDBCExecutionFactory
{              
   @Override
   public void start() 
   {
      super.start();

      // register functions.
      registerFunctionModifier("abs", new MyAbsModifier()); 
      registerFunctionModifier("concat", new AliasModifier("concat2")); 
   }
}]]></programlisting>
      <para>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.  
      </para>
    </section>
  </section>
    
  <section>
    <title>Installing Extensions</title>
    <para>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. 
     </para>
     <para>
     Refer to <xref linkend="translator_package"/> and <xref linkend="translator_deploy"/> for more details.
     </para>
     </section>
</chapter>
