<?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="translators">
    <title>Translators</title>

    <section>
        <title>Introduction to the Teiid Connector Architecture</title>
        <para>
        The Teiid Connector Architecture (TCA) provides Teiid with a robust mechanism 
        for integrating with external systems.  The TCA defines a common client interface 
        between Teiid and an external system that includes metadata as to what SQL 
        constructs are supported for pushdown and the ability to import metadata from 
        the external system.
        </para>
        
        <para>
        A Translator is the heart of the TCA and acts as the bridge logic between Teiid 
        and an external system, which is most commonly accessed through a JCA resource 
        adapter.  Refer to the Teiid Developers Guide for details on developing custom 
        Translators and JCA resource adapters for use with Teiid.
        </para>
        
        <note>
            <para>
            The TCA is not the same as the JCA, the JavaEE Connector Architecture, although 
            the TCA is designed for use with JCA resource adapters.
            </para>
        </note>
        
        <note>
            <para>
            The import capabilities of Teiid Translators is currently only used in 
            <link linkend="dynamic_vdbs">dynamic VDBs</link> and not by the Teiid Designer.
            </para>
        </note>
        
    </section>

    <section>
        <title>Translators</title>
        <para>
        A Translator is typically paired with a particular JCA resource adapter.  In 
        instances where pooling, environment dependent configuration management, advanced 
        security handling, etc. are not needed, then a JCA resource adapter is not needed.  
        The configuration of JCA ConnectionFactories for needed resource adapters is not 
        part of this guide, please see the Teiid Administrator Guide and the kit examples 
        for configuring resource adapters for use in JBossAS.
        </para>
        
        <para>
        Translators can have a number of configurable properties.  These are broken down 
        into execution properties, which determine aspects of how data is retrieved, and 
        import settings, which determine what metadata is read for import.
        </para>
        
        <para>
        The execution properties for a translator typically have reasonable defaults.  For 
        specific translator types, e.g. the Derby translator, base execution properties are 
        already tuned to match the source.  In most cases the user will not need to adjust 
        their values.
        </para>

        <table>
            <title>Base Execution Properties - shared by all translators</title>
            <tgroup cols="3">
                <colspec colwidth="4*" />
                <colspec colwidth="6*" />
                <colspec colwidth="2*" />
                <thead>
                    <row>
                        <entry>Name</entry>
                        <entry>Description</entry>
                        <entry>Default</entry>
                    </row>
                </thead>
                <tbody>
                    <row>
                        <entry>Immutable</entry>
                        <entry>Set to true to indicate that the source never changes.</entry>
                        <entry>false</entry>
                    </row>
                    <row>
                        <entry>RequiresCriteria</entry>
                        <entry>Set to true to indicate that source SELECT/UPDATE/DELETE queries require a where clause.</entry>
                        <entry>false</entry>
                    </row>
                    <row>
                        <entry>SupportsOrderBy</entry>
                        <entry>Set to true to indicate that the ORDER BY clause is supported.</entry>
                        <entry>false</entry>
                    </row>
                    <row>
                        <entry>SupportsOuterJoins</entry>
                        <entry>Set to true to indicate that OUTER JOINs are supported.</entry>
                        <entry>false</entry>
                    </row>
                    <row>
                        <entry>SupportsFullOuterJoins</entry>
                        <entry>If outer joins are supported, true indicates that FULL OUTER JOINs are supported.</entry>
                        <entry>false</entry>
                    </row>
                    <row>
                        <entry>SupportsInnerJoins</entry>
                        <entry>Set to true to indicate that INNER JOINs are supported.</entry>
                        <entry>false</entry>
                    </row>
                    <row>
                        <entry>SupportedJoinCriteria</entry>
                        <entry>If joins are supported, defines what criteria may be used as the join criteria.  May be one of (ANY, THETA, EQUI, or KEY).</entry>
                        <entry>ANY</entry>
                    </row>
                    <row>
                        <entry>MaxInCriteriaSize</entry>
                        <entry>If in criteria are supported, defines what the maximum number of in entries are per predicate.  -1 indicates no limit.</entry>
                        <entry>-1</entry>
                    </row>
                    <row>
                        <entry>MaxDependentInPredicates</entry>
                        <entry>If in criteria are supported, defines what the maximum number of predicates that can be used for a dependent join.  
                        Values less than 1 indicate to use only one in predicate per dependent value pushed (which matches the pre-7.4 behavior).</entry>
                        <entry>-1</entry>
                    </row>
                </tbody>
            </tgroup>
        </table>
        
        <note>
            <para>
            Only a subset of the supports metadata can be set through execution properties.  If more control is needed, please 
            consult the Teiid Developers Guide.
            </para>
        </note>
        
        <para>There are no base importer settings.</para>

        <section>
            <title>File Translator</title>
            <para>
            The file translator, known by the type name <emphasis>file</emphasis>, exposes 
            stored procedures to leverage file system resources exposed by the file resource 
            adapter. It will commonly be used with the <link linkend="texttable">TEXTTABLE</link> 
            or <link linkend="xmltable">XMLTABLE</link> table functions to use CSV or XML 
            formated data.
            </para>

            <table>
                <title>Execution Properties</title>
                <tgroup cols="3">
                    <colspec colwidth="1*" />
                    <colspec colwidth="4*" />
                    <colspec colwidth="1*" />
                    <thead>
                        <row>
                            <entry>Name</entry>
                            <entry>Description</entry>
                            <entry>Default</entry>
                        </row>
                    </thead>
                    <tbody>
                        <row>
                            <entry>Encoding</entry>
                            <entry>The encoding that should be used for CLOBs returned by the getTextFiles procedure</entry>
                            <entry>The system default encoding</entry>
                        </row>
                        <row>
                            <entry>ExceptionIfFileNotFound</entry>
                            <entry>Throw an exception in getFiles or getTextFiles if the specified file/directory does not exist.</entry>
                            <entry>false</entry>
                        </row>
                    </tbody>
                </tgroup>
            </table>
            
            <section>
                <title>Usage</title>
                <para>
                Retrieve all files as BLOBs with an optional extension at the given path.
                </para>
                
                <programlisting>call getFiles('path/*.ext')</programlisting>
                
                <para>
                If the extension path is specified, then it will filter all of the file in the directory referenced by the base path. 
                If the extension pattern is not specified and the path is a directory, 
                then all files in the directory will be returned.  Otherwise the single file referenced will be returned.  If the path 
                doesn't exist, then no results will be returned if ExceptionIfFileNotFound is false, otherwise an exception will be raised.
                </para>
                
                <para>
                Retrieve all files as CLOB(s) with the an optional extension at the given path.
                </para>
                <programlisting>call getTextFiles('path/*.ext')</programlisting>
                
                <para>
                All the same files a getFiles will be retrieved, the only difference is that
                the results will be CLOB values using the encoding execution property as the character set. 
                </para>
                
                <para>
                Save the CLOB, BLOB, or XML value to given path
                </para>
                <programlisting>call saveFile('path', value)</programlisting>
                
                <para>The path should reference a new file location or an existing file to overwrite completely.</para>
            </section>
            
            <section>
                <title>JCA Resource Adapter</title>
                <para>The resource adapter for this translator provided through "File Data Source", Refer to Admin Guide for 
                configuration information.</para>
            </section>            

        </section>
        
        <section>
            <title>JDBC Translator</title>
            <para>
            The JDBC translator bridges between SQL semantic and data type difference 
            between Teiid and a target RDBMS.  Teiid has a range of specific translators 
            that target the most popular open source and proprietary databases.
            </para>

            <itemizedlist>
                <title>Type names:</title>
                <listitem>
                    <para>
                    <emphasis>jdbc-ansi</emphasis> - declares support for most SQL 
                    constructs supported by Teiid, except for row limit/offset and 
                    EXCEPT/INTERCECT.  Translates source SQL into ANSI compliant syntax.  
                    This translator should be used when another more specific type is 
                    not available.
                    </para>
                </listitem>
                <listitem>
                    <para>
                    <emphasis>jdbc-simple</emphasis> - same as jdbc-ansi, except disables 
                    support for function, UNION, and aggregate pushdown.
                    </para>
                </listitem>
                <listitem>
                    <para>
                    <emphasis>access</emphasis> - for use with Microsoft Access 2003 or later.
                    </para>
                </listitem>
                <listitem>
                    <para>
                    <emphasis>db2</emphasis> - for use with DB2 8 or later.
                    </para>
                </listitem>
                <listitem>
                    <para>
                    <emphasis>derby</emphasis> - for use with Derby 10.1 or later.
                    </para>
                </listitem>
                <listitem>
                    <para>
                    <emphasis>h2</emphasis> - for use with H2 version 1.1 or later.
                    </para>
                </listitem>
                <listitem>
                    <para>
                    <emphasis>hive</emphasis> - For use with Hive database based on Hadoop. Hive is a data warehousing 
                    infrastructure based on the Hadoop. Hadoop provides massive scale out and fault tolerance capabilities 
                    for data storage and processing (using the map-reduce programming paradigm) on commodity hardware.
                    </para>
                    <para>Hive has limited support for data types as it supports integer varients, boolean, float, 
                    double and string. It is does not have native support for time based types, xml or LOBs. These limitations
                    are reflected in the translator capabilities. The view table can use these types, however the tranformation
                    would need to specify the necessary transformations. Note that in those situations, the evaluations will be
                    done in Teiid engine. Another limitation Hive has is, it only supports EQUI join, so using any other joins types
                    on its source tables will result in in-effiecient queries. Currently there is no tooling support for metadata 
                    import from Hive in Designer. To write criteria based on partitioned columns, they can be modeled 
                    on source table, but do not include in selection columns.</para>
                </listitem>
                <listitem>
                    <para>
                    <emphasis>hsql</emphasis> - for use with HSQLDB 1.7 or later.
                    </para>
                </listitem>
                <listitem>
                    <para>
                    <emphasis>ingres</emphasis> - for use with Ingres 2006 or later.
                    </para>
                </listitem>
                <listitem>
                    <para>
                    <emphasis>ingres93</emphasis> - for use with Ingres 9.3 or later.
                    </para>
                </listitem>
                <listitem>
                    <para>
                    <emphasis>intersystems-cache</emphasis> - for use with Intersystems Cache Object database (only relational aspect of it)
                    </para>
                </listitem>                
                <listitem>
                    <para>
                    <emphasis>informix</emphasis> - for use with any version.
                    </para>
                </listitem>
                <listitem>
                    <para>
                    <emphasis>metamatrix</emphasis> - for use with MetaMatrix 5.5.0 or later.
                    </para>
                </listitem>
                <listitem>
                    <para>
                    <emphasis>modeshape</emphasis> - for use with Modeshape 2.2.1 or later. The PATH, NAME, LOCALNODENAME, DEPTH, and SCORE functions should be accessed as pseudo-columns, e.g. "nt:base"."jcr:path". 
                    Teiid UFDs (prefixed by JCR_) are available for CONTIANS, ISCHILDNODE, ISDESCENDENT, ISSAMENODE, REFERENCE - see the JCRFunctions.xmi.  
                    If a selector name is needed in a JCR function, you should use the pseudo-column "jcr:path", e.g. JCR_ISCHILDNODE(foo.jcr_path, 'x/y') would become ISCHILDNODE(foo, 'x/y') in the ModeShape query.
                    An additional pseudo-column "mode:properties" should be imported by setting the ModeShape JDBC connection property teiidsupport=true.  
                    The column "mode:properties" should be used by the JCR_REFERENCE and other 
                    functions that expect a .* selector name, e.g. JCR_REFERENCE(nt_base.jcr_properties) would become REFERENCE("nt:base".*) in the ModeShape query.
                    </para>
                </listitem>
                <listitem>
                    <para>
                    <emphasis>mysql</emphasis>/<emphasis>mysql5</emphasis> - for use with 
                    MySQL version 4.x and 5 or later respectively.  
                    </para>
                    <para>
                    The MySQL Translators expect the database or session to be using ANSI 
                    mode.  If the database is not using ANSI mode, an initialization query 
                    should be used on the pool to set ANSI mode:  
                    </para>
                    <programlisting>set SESSION sql_mode = 'ANSI'</programlisting>
                    <para>If retrieving timestamp values, you should set zeroDateTimeBehavior=convertToNull on the URL, otherwise MySQL or Teiid may throw an exception
                    retriieving '0000-00-00 00:00:00'</para>
                </listitem>
                <listitem>
                    <para>
                    <emphasis>oracle</emphasis> - for use with Oracle 9i or later.  
                    Sequences may be used with the Oracle translator. A sequence may be 
                    modeled as a table with a name in source of DUAL and columns with the 
                    name in source set to <code>&lt;sequencesequence name&gt;.[nextval|currentval].</code>  
                    You can use a sequence as the default value for insert columns by 
                    setting the column to autoincrement and the name in source to 
                    <code>&lt;element name&gt;:SEQUENCE=&lt;sequence name&gt;.&lt;sequence value&gt;</code>.
                    A rownum column can also added to any Oracle physical table to support the rownum pseudo-column.
                    A rownum colum should have a name in source of <code>rownum</code>.  These rownum columns do not 
                    have the same semantics as the Oracle rownum construct so care must be taken in their usage. 
                    </para>
                    <para>Oracle specific execution properties:</para>
		            <itemizedlist>
		            	<listitem>
		            		<para><emphasis>OracleSuppliedDriver</emphasis> - indicates that the Oracle supplied driver (typically prefixed by ojdbc) is being used.  Defaults to true.  Set to false when using DataDirect or other Oracle JDBC drivers.</para>
		            	</listitem>
	            	</itemizedlist>
                </listitem>
                <listitem>
                    <para>
                    <emphasis>postgresql</emphasis> - for use with 8.0 or later clients 
                    and 7.1 or later server.
                    </para>
                </listitem>
                <listitem>
                    <para>
                    <emphasis>sqlserver</emphasis> - for use with SQL Server 2000 or later.  A SQL Server JDBC driver version 2.0 or later (or compatible e.g. JTDS 1.2 or later) should be used.
                    </para>
                </listitem>
                <listitem>
                    <para>
                    <emphasis>sybase</emphasis> - for use with Sybase version 12.5 or later.
                    </para>
                </listitem>
                <listitem>
                    <para>
                    <emphasis>teiid</emphasis> - for use with Teiid 6.0 or later.
                    </para>
                </listitem>
                <listitem>
                    <para>
                    <emphasis>teradata</emphasis> - for use with Teradata V2R5.1 or later.
                    </para>
                </listitem> 
            </itemizedlist>
            
            <table>
                <title>Execution Properties - shared by all JDBC Translators</title>
                <tgroup cols="3">
                    <colspec colwidth="4*" />
                    <colspec colwidth="6*" />
                    <colspec colwidth="2*" />
                    <thead>
                        <row>
                            <entry>Name</entry>
                            <entry>Description</entry>
                            <entry>Default</entry>
                        </row>
                    </thead>
                    <tbody>
                    <row>
                        <entry>DatabaseTimeZone</entry>
                        <entry>The time zone of the database.  Used when fetchings date, time, or timestamp values.</entry>
                        <entry>The system default time zone</entry>
                    </row>
                    <row>
                        <entry>DatabaseVersion</entry>
                        <entry>The specific database version.  Used to further tune pushdown support.</entry>
                        <entry>The base supported version</entry>
                    </row>
                    <row>
                        <entry>TrimStrings</entry>
                        <entry>true to trim trailing whitespace from fixed length character strings.  Note that Teiid only has a string, or varchar, type that treats trailing whitespace as meaningful.</entry>
                        <entry>false</entry>
                    </row>
                    <row>
                        <entry>UseBindVariables</entry>
                        <entry>true to indicate that PreparedStatements should be used and that literal values in the source query should be replace with bind variables.  If false only LOB values will trigger the use of PreparedStatements.</entry>
                        <entry>true</entry>
                    </row>
                    <row>
                        <entry>UseCommentsInSourceQuery</entry>
                        <entry>This will embed a /*comment*/ leading comment with session/request id in source SQL query for informational purposes</entry>
                        <entry>false</entry>
                    </row>
                    <row>
                        <entry>MaxPreparedInsertBatchSize</entry>
                        <entry>The max size of a prepared insert batch.</entry>
                        <entry>2048</entry>
                    </row>
                </tbody>
            </tgroup>
        </table>
            
        <table>
            <title>Importer Properties - shared by all JDBC Translators</title>
            <tgroup cols="3">
            <colspec colwidth="4*" />
            <colspec colwidth="6*" />
            <colspec colwidth="2*" />
                <thead>
                    <row>
                        <entry>Name</entry>
                        <entry>Description</entry>
                        <entry>Default</entry>
                    </row>
                </thead>
                <tbody>
                    <row>
                        <entry>catalog</entry>
                        <entry>See DatabaseMetaData.getTables<footnote label="1" id="dbmd"><para>Full JavaDoc for <ulink url="http://java.sun.com/javase/6/docs/api/java/sql/DatabaseMetaData.html">DatabaseMetaData</ulink></para></footnote></entry>
                        <entry>null</entry>
                    </row>
                    <row>
                        <entry>schemaPattern</entry>
                        <entry>See DatabaseMetaData.getTables<footnoteref linkend="dbmd"/></entry>
                        <entry>null</entry>
                    </row>
                    <row>
                        <entry>tableNamePattern</entry>
                        <entry>See DatabaseMetaData.getTables<footnoteref linkend="dbmd"/></entry>
                        <entry>null</entry>
                    </row>
                    <row>
                        <entry>procedurePatternName</entry>
                        <entry>See DatabaseMetaData.getProcedures<footnoteref linkend="dbmd"/></entry>
                        <entry>null</entry>
                    </row>
                    <row>
                        <entry>tableTypes</entry>
                        <entry>Comma separated list - without spaces - of imported table types.  See DatabaseMetaData.getTables<footnoteref linkend="dbmd"/></entry>
                        <entry>null</entry>
                    </row>
                    <row>
                        <entry>useFullSchemaName</entry>
                        <entry>When false, directs the importer to drop the source catalog/schema from the Teiid object name, so that the Teiid fully qualified name will be in the form of &lt;model name&gt;.&lt;table name&gt; - Note: that this may lead to objects with duplicate names when importing from multiple schemas, which results in an exception</entry>
                        <entry>true</entry>
                    </row>
                    <row>
                        <entry>importKeys</entry>
                        <entry>true to import primary and foriegn keys</entry>
                        <entry>true</entry>
                    </row>
                    <row>
                        <entry>importIndexes</entry>
                        <entry>true to import index/unique key/cardinality information</entry>
                        <entry>true</entry>
                    </row>
                    <row>
                        <entry>importApproximateIndexes</entry>
                        <entry>true to import approximate index information.  See DatabaseMetaData.getIndexInfo<footnoteref linkend="dbmd"/></entry>
                        <entry>true</entry>
                    </row>
                    <row>
                        <entry>importProcedures</entry>
                        <entry>true to import procedures and procedure columns - Note that it is not always possible to import procedure result set columns due to database limitations.  It is also not currently possible to import overloaded procedures.</entry>
                        <entry>true</entry>
                    </row>
                    <row>
                        <entry>widenUnsignedTypes</entry>
                        <entry>true to convert unsigned types to the next widest type.  For example SQL Server reports tinyint as an unsigned type.  With this option enabled, tinyint would be imported as a short instead of a byte.</entry>
                        <entry>true</entry>
                    </row>
                    <row>
                        <entry>quoteNameInSource</entry>
                        <entry>false will override the default and direct Teiid to create source queries using unquoted identifiers.</entry>
                        <entry>true</entry>
                    </row>
                    <row>
                        <entry>useProcedureSpecificName</entry>
                        <entry>true will allow the import of overloaded procedures (which will normally result in a duplicate procedure error) by using the unique procedure specific name as the Teiid name.  This option will only work with JDBC 4.0 compatable drivers that report specific names.</entry>
                        <entry>false</entry>
                    </row>
                    <row>
                        <entry>useCatalogName</entry>
                        <entry>true will use any non-null/non-empty catalog name as part of the name in source, e.g. "catalog"."table"."column", and in the Teiid runtime name if useFullSchemaName is true.  
                        false will not use the catalog name in either the name in source or the Teiid runtime name.  Should be set to false for sources that do not fully support a catalog concept, but return a non-null catalog name in their metadata - such as HSQL.</entry>
                        <entry>true</entry>
                    </row>
                </tbody>
            </tgroup>
        </table>

        <warning>
            <para>
            The default import settings will crawl all available metadata.  This import 
            process is time consuming and full metadata import is not needed in most 
            situations.  Most commonly you'll want to limit import by schemaPattern 
            and tableTypes.
            </para>
        </warning>

        <para>
        Example importer settings to only import tables and views from my-schema.
        </para>
        <programlisting language="XML" role="XML"><![CDATA[...
<property name="importer.tableTypes" value="TABLE,VIEW"/>
<property name="importer.schemaPattern" value="my-schema"/>
...]]></programlisting>

        <section>
            <title>Usage</title>
            <para>
            Usage of a JDBC source is straight-forward.  Using Teiid SQL, the source may be 
            queried as if the tables and procedures were local to the Teiid system.
            </para>
        </section>
        
        <section>
        	<title>Native Queries</title>
        	<para>Both physical tables and procedures may optionally have native queries associated with them.  No validation of the native query is performed, it is simply used in a straight-forward manner to generate the source SQL.  
        	For a physical table setting the {http://www.teiid.org/ext/relational/2012}native-query extension metadata to the desired query string will have Teiid execute the native query as an inline view in the source query.  
        	This feature should only be used against sources that support inline views.  For example on a physical table y with nameInSource="x" and {http://www.teiid.org/ext/relational/2012}native-query="select c from g", the Teiid source query
        	"SELECT c FROM y" would generate the SQL query "SELECT c FROM (select c from g) as x".  Note that the column names in the native query must match the nameInSource of the physical table columns for the resulting SQL
        	to be valid.</para>
        	<para>For physical procedures you may also set the {http://www.teiid.org/ext/relational/2012}native-query extension metadata to a desired query string with the added ability to positionally reference IN parameters.  A parameter reference has the form
        	$integer, e.g. $1.  Note that 1 based indexing is used and that only IN parameters may be referenced.  Dollar-sign integer is reserved in physical procedure native queries.  To use a $integer directly, it must be escaped with another $, e.g. $$1.
        	By default bind values will be used for parameter values.  In some situations you may wish to bind values directly into the resulting SQL.  The {http://www.teiid.org/ext/relational/2012}non-prepared extension metadata property may be set to false to turn off
        	parameter binding.  Note this option should be used with caution as inbound may allow for SQL injection attacks if not properly validated.  The native query does not need to call a stored procedure.  Any SQL that returns
        	a result set positionally matching the result set expected by the physical stored procedure metadata will work.  For example on a stored procedure x with {http://www.teiid.org/ext/relational/2012}native-query="select c from g where c1 = $1 and c2 = '$$1'", the Teiid source query
        	"CALL x(?)" would generate the SQL query "select c from g where c1 = ? and c2 = '$1'".  Note that ? in this example will be replaced with the actual value bound to parameter 1.
        	</para>
        </section>
        
        <section>
            <title>JCA Resource Adapter</title>
            <para>The resource adapter for this translator provided through data source in JBoss AS, 
            Refer to Admin Guide for "JDBC Data Sources" configuration section.</para>
        </section>          
        
    </section>
    
    <section>
        <title>LDAP Translator</title>
        <para>
        The LDAP translator, known by the type name <emphasis>ldap</emphasis>, exposes an 
        LDAP directory tree relationally with pushdown support for filtering via criteria.  
        This is typically coupled with the LDAP resource adapter.
        </para>
        
        <table>
            <title>Execution Properties</title>
            <tgroup cols="3">
                <thead>
                    <row>
                        <entry>Name</entry>
                        <entry>Description</entry>
                        <entry>Default</entry>
                    </row>
                </thead>
                <tbody>
                    <row>
                        <entry>SearchDerfaultBaseDN</entry>
                        <entry>Default Base DN for LDAP Searches</entry>
                        <entry>null</entry>
                    </row>
                    <row>
                        <entry>SearchDefaultScope</entry>
                        <entry>Default Scope for LDAP Searches. Can be one of SUBTREE_SCOPE, OBJECT_SCOPE, ONELEVEL_SCOPE.</entry>
                        <entry>ONELEVEL_SCOPE</entry>
                    </row>
                    <row>
                        <entry>RestrictToObjectClass</entry>
                        <entry>Restrict Searches to objectClass named in the Name field for a table</entry>
                        <entry>false</entry>
                    </row>
                    <row>
                    	<entry>UsePagination</entry>
                        <entry>Use a PagedResultsControl to page through large results.  This is not supported by all directory servers.</entry>
                        <entry>false</entry>
                    </row>
                    <row>
                    	<entry>ExceptionOnSizeLimitExceeded</entry>
                        <entry>Set to true to throw an exception when a SizeLimitExceededException is received and a LIMIT is not properly enforced.</entry>
                        <entry>false</entry>
                    </row>
                </tbody>
            </tgroup>
        </table>
        
        <para>
        There are no import settings for the ldap translator; it also does not 
        provide metadata.
        </para>
        
        <section>
        	<title>Metadata Directives</title>
	        <para>String columns with a default value of "multivalued-concat" will concatinate all attribute 
	        values together in alphabetical order using a ? delimiter.  If a multivalued attribute does not have a default value of "multivalued-concat", then
	        any value may be returned.  
	        </para>        
        </section>
        
        <section>
            <title>JCA Resource Adapter</title>
            <para>The resource adapter for this translator provided through "LDAP Data Source", 
            Refer to Admin Guide for configuration.</para>
        </section> 
                
    </section>

    <section>
        <title>Loopback Translator</title>
        <para>
        The Loopback translator, known by the type name <emphasis>loopback</emphasis>, 
        provides a quick testing solution.  It supports all SQL constructs and returns 
        default results, with configurable behavior.
        </para>
        
        <table>
            <title>Execution Properties</title>
            <tgroup cols="3">
                <colspec colwidth="3*" />
                <colspec colwidth="6*" />
                <colspec colwidth="2*" />
                <thead>
                    <row>
                        <entry>Name</entry>
                        <entry>Description</entry>
                        <entry>Default</entry>
                    </row>
                </thead>
                <tbody>
                    <row>
                        <entry>ThrowError</entry>
                        <entry>true to always throw an error</entry>
                        <entry>false</entry>
                    </row>
                    <row>
                        <entry>RowCount</entry>
                        <entry>Rows returned for non-update queries.</entry>
                        <entry>1</entry>
                    </row>
                    <row>
                        <entry>WaitTime</entry>
                        <entry>Wait randomly up to this number of milliseconds with each sourc query.</entry>
                        <entry>0</entry>
                    </row>
                    <row>
                        <entry>PollIntervalInMilli</entry>
                        <entry>if positive results will be "asynchronously" returned - that is a DataNotAvailableException will be thrown initially and the engine will wait the poll interval before polling for the results.</entry>
                        <entry>-1</entry>
                    </row>
                </tbody>
            </tgroup>
        </table>

        <para>
        There are no import settings for the Loopback translator; it also does not 
        provide metadata - it should be used as a testing stub.
        </para>
        
        <section>
            <title>JCA Resource Adapter</title>
            <para>The source connection is required for this translator</para>
        </section>         
        
    </section>
    
    <section>
        <title>Salesforce Translator</title>
        <para>
        The Salesforce translator, known by the type name <emphasis>salesforce</emphasis> 
        supports the SELECT, DELETE, INSERT and UPDATE operations against a Salesforce.com 
        account.  It is designed for use with the Teiid Salesforce resource adapter.
        </para>
        
        <table>
            <title>Execution Properties</title>
            <tgroup cols="3">
                <colspec colwidth="3*" />
                <colspec colwidth="6*" />
                <colspec colwidth="2*" />
                <thead>
                    <row>
                        <entry>Name</entry>
                        <entry>Description</entry>
                        <entry>Default</entry>
                    </row>
                </thead>
                <tbody>
                    <row>
                        <entry>ModelAuditFeilds</entry>
                        <entry>Audit Model Fields</entry>
                        <entry>false</entry>
                    </row>
                </tbody>
            </tgroup>
        </table>

        <para>
        The Salesforce translator can import metadata, but does not currently 
        have import settings.
        </para>
        
        <section>
            <title>Usage</title>
            <section>
                <title>SQL Processing</title>
                    <para>
                    Salesforce does not provide the same set of
                    functionality as a relational database. For example, Salesforce does
                    not support arbitrary joins between tables. However, working in
                    combination with the Teiid Query Planner, the Salesforce
                    connector supports nearly all of the SQL syntax supported by the
                    Teiid.
                    </para>
                    <para>
                    The Salesforce Connector executes SQL commands by “pushing
                    down” the command to Salesforce whenever possible, based on the
                    supported capabilities. Teiid will automatically provide
                    additional database functionality when the Salesforce Connector does
                    not	explicitly provide support for a given SQL construct. In these
                    cases, the SQL construct cannot be “pushed down” to the data source,
                    so it will be evaluated in Teiid, in order to ensure that the
                    operation is performed.
                    </para>
                    <para>
                    In cases where certain SQL capabilities cannot be pushed down
                    to Salesforce, Teiid will push down the capabilities that are
                    supported, and fetch a set of data from Salesforce. Then, Teiid
                    will evaluate the additional capabilities, creating a subset of the
                    original data set. Finally, Teiid will pass the result to the
                    client.
                    </para>
    
                    <programlisting language="SQL"><![CDATA[SELECT sum(Reports) FROM Supervisor where Division = 'customer support';]]></programlisting>
    
                    <para>
                    Neither Salesforce nor the Salesforce Connector support
                    the sum() scalar function, but they do support CompareCriteriaEquals,
                    so the query that is passed to Salesforce by the connector will be
                    transformed to this query.
                    </para>
                    
                    <programlisting language="SQL"><![CDATA[SELECT Reports FROM Supervisor where Division = 'customer support';]]></programlisting>
    
                    <para>
                    The sum() scalar function will be applied by the Teiid Query Engine to 
                    the result set returned by the connector.
                    </para>
                    
                    <para>
                    In some cases multiple calls to the Salesforce application
                    will be made to support the SQL passed to the connector.
                    </para>
                    
                    <programlisting language="SQL"><![CDATA[DELETE From Case WHERE Status = 'Closed';]]></programlisting>
                    
                    <para>
                    The API in Salesforce to delete objects only supports
                    deleting by ID. In order to accomplish this the Salesforce connector
                    will first execute a query to get the IDs of the correct objects, and
                    then delete those objects. So the above DELETE command will result in
                    the following two commands.
                    </para>
                    
                    <programlisting language="SQL"><![CDATA[SELECT ID From Case WHERE Status = 'Closed';
DELETE From Case where ID IN (<result of query>);]]></programlisting>

                    <para>
                    *The Salesforce API DELETE call is not expressed in SQL, but
                    the above is an SQL equivalent expression.
                    </para>
                    
                    <para>
                    It's useful to be aware of unsupported capabilities, in order
                    to avoid fetching large data sets from Salesforce and making you
                    queries as performant as possible. 
                    See all <link linkend="sf_supported">Supported Capabilities</link>.
                    </para>
                    
                </section>
                
                <section>
                    <title>Selecting from Multi-Select Picklists</title>
                    <para>
                    A multi-select picklist is a field type in Salesforce that can
                    contain multiple values in a single field. Query criteria operators
                    for fields of this type in SOQL are limited to EQ, NE, includes and
                    excludes. The full Salesforce documentation for selecting from
                    mullti-select picklists can be found at the following link.
                    <ulink url="http://www.salesforce.com/us/developer/docs/api/index_Left.htm#StartTopic=Content%2Fsforce_api_calls_soql_querying_multiselect_picklists.htm|SkinName=webhelp">Querying Mulit-select Picklists</ulink>
                    </para>
        
                    <para>
                    Teiid SQL does not support the includes or
                    excludes operators, but the Salesforce connector provides user
                    defined function definitions for these operators that provided
                    equivalent functionality for fields of type multi-select. The
                    definition for the functions is:
                    </para>
                    
                    <programlisting>boolean includes(Column column, String param)
boolean excludes(Column column, String param)</programlisting>

                    <para>
                    For example, take a single multi-select picklist column
                    called Status that contains all of these values.
                    </para>
                    
                    <itemizedlist mark='opencircle'>
                        <listitem>
                            <para>current</para>
                        </listitem>
                        <listitem>
                            <para>working</para>
                        </listitem>
                        <listitem>
                            <para>critical</para>
                        </listitem>
                    </itemizedlist>
                
                    <para>
                    For that column, all of the below are valid queries:
                    </para>
                    
                    <programlisting language="SQL"><![CDATA[SELECT * FROM Issue WHERE true = includes (Status, 'current, working' );
SELECT * FROM Issue WHERE true = excludes (Status, 'current, working' );
SELECT * FROM Issue WHERE true = includes (Status, 'current;working, critical' );]]></programlisting>

                    <para>
                    EQ and NE criteria will pass to Salesforce as supplied. For
                    example, these queries will not be modified by the connector.
                    </para>
                    
                    <programlisting language="SQL"><![CDATA[SELECT * FROM Issue WHERE Status = 'current';
SELECT * FROM Issue WHERE Status = 'current;critical';
SELECT * FROM Issue WHERE Status != 'current;working';]]></programlisting>

                </section>
                <section>
                    <title>Selecting All Objects</title>
                    <para>
                    The Salesforce connector supports the calling the queryAll operation
                    from the Salesforce API. The queryAll operation is equivalent
                    to the query operation with the exception that it returns data about
                    <emphasis role="strong">all current and deleted</emphasis>
                    objects in the system.
                    </para>
                    <para>
                    The connector determines if it will call the
                    query or queryAll operation via reference to the
                    isDeleted property present on each  Salesforce object, 
                    and modeled as a column on each table generated by
                    the importer. By default this value is set to
                    False when the model is generated and thus the connector calls
                    query. Users are free to change the value in the model to True,
                    changing the default behavior of the connector to be queryAll.
                    </para>
                    <para>
                    The behavior is different if isDeleted is used as a parameter
                    in the query. If the isDeleted column is used as a parameter
                    in the query, and the value is 'true' the connector will call queryAll.
                    </para>
                    
                    <programlisting language="SQL"><![CDATA[select * from Contact where isDeleted = true;]]></programlisting>
                    
                    <para>
                    If the isDeleted column is used as a parameter in the query,
                    and the value is 'false' the connector perform the default behavior
                    will call query.
                    </para>
                    
                    <programlisting language="SQL"><![CDATA[select * from Contact where isDeleted = false;]]></programlisting>
                </section>
                <section>
                    <title>Selecting Updated Objects</title>
                    <para>
                    If the option is selected when importing metadata from
                    Salesforce, a GetUpdated procedure is generated in the model with
                    the following structure:
                    </para>
                    
                    <programlisting language="SQL"><![CDATA[GetUpdated (ObjectName IN string,
	StartDate IN datetime, 
	EndDate IN datetime,
	LatestDateCovered OUT datetime)
returns
	ID string]]></programlisting>
	
                    <para>
                    See the description of the
                    <ulink url="http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_getupdated.htm">GetUpdated</ulink>
                    operation in the Salesforce documentation for usage details.
                    </para>
                    
                </section>
                <section>
                    <title>Selecting Deleted Objects</title>
                    <para>
                    If the option is selected when importing metadata from
                    Salesforce, a GetDeleted procedure is generated in the model with
                    the following structure:
                    </para>
                    
                    <programlisting language="SQL"><![CDATA[GetDeleted (ObjectName IN string,
	StartDate IN datetime, 
	EndDate IN datetime,
	EarliestDateAvailable OUT datetime,
	LatestDateCovered OUT datetime)
returns
	ID string,
	DeletedDate datetime]]></programlisting>

                    <para>
                    See the description of the
                    <ulink url="http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_getdeleted.htm">GetDeleted</ulink>
                    operation in the Salesforce documentation for usage details.
                    </para>
                    
                </section>
                <section>
                    <title>Relationship Queries</title>
                    <para>
                    Salesforce does not support joins like a relational database, 
                    but it does have support for queries that include parent-to-child
                    or child-to-parent relationships between objects.  These are termed 
                    Relationship Queries.  The SalesForce connector supports Relationship 
                    Queries through Outer Join syntax.
                    </para>
                    
                    <programlisting language="SQL"><![CDATA[SELECT Account.name, Contact.Name from Contact LEFT OUTER JOIN Account 
on Contact.Accountid = Account.id]]></programlisting>

                    <para>
                    This query shows the correct syntax to query a SalesForce model with 
                    to produce a relationship query from child to parent.  It resolves to the 
                    following query to SalesForce.
                    </para>
                    
                    <programlisting language="SQL"><![CDATA[SELECT Contact.Account.Name, Contact.Name FROM Contact]]></programlisting>
                    
                    <programlisting language="SQL"><![CDATA[select Contact.Name, Account.Name from Account Left outer Join Contact
on Contact.Accountid = Account.id]]></programlisting>
    
                    <para>
                    This query shows the correct syntax to query a SalesForce model with 
                    to produce a relationship query from parent to child.  It resolves to the 
                    following query to SalesForce.
                    </para>
                    
                    <programlisting language="SQL"><![CDATA[SELECT Account.Name, (SELECT Contact.Name FROM
Account.Contacts) FROM Account]]></programlisting>
        
                    <para>
                    See the description of the
                    <ulink url="http://www.salesforce.com/us/developer/docs/api/index_Left.htm#StartTopic=Content/sforce_api_calls_soql_relationships.htm">Relationship Queries</ulink>
                    operation in the SalesForce documentation for limitations.
                    </para>
                </section>
        
                <section id="sf_supported">
                    <title>Supported Capabilities</title>
                    <para>
                    The following are the the connector capabilities supported by
                    the Salesforce Connector. These SQL constructs will be pushed down to
                    Salesforce.
                    </para>
                    
                    <itemizedlist mark='opencircle'>
                        <listitem>
                            <para>SELECT command</para>
                        </listitem>
                        <listitem>
                            <para>INSERT Command</para>
                        </listitem>
                        <listitem>
                            <para>UPDATE Command</para>
                        </listitem>
                        <listitem>
                            <para>DELETE Command</para>
                        </listitem>
                        <listitem>
                            <para>CompareCriteriaEquals</para>
                        </listitem>
                        <listitem>
                            <para>InCriteria</para>
                        </listitem>
                        <listitem>
                            <para>LikeCriteria - Supported for String fields only.</para>
                        </listitem>
                        <listitem>
                            <para>RowLimit</para>
                        </listitem>
                        <listitem>
                            <para>AggregatesCountStar</para>
                        </listitem>
                        <listitem>
                            <para>NotCriteria</para>
                        </listitem>
                        <listitem>
                            <para>OrCriteria</para>
                        </listitem>
                        <listitem>
                            <para>CompareCriteriaOrdered</para>
                        </listitem>
                        <listitem>
                            <para>OuterJoins with join criteria KEY</para>
                        </listitem>
                    </itemizedlist>
                </section>
                
            </section>
            
        <section>
            <title>JCA Resource Adapter</title>
            <para>The resource adapter for this translator provided through "Salesforce Data Source", 
            Refer to Admin Guide for configuration.</para>
        </section> 
                    
        </section>
        
        <section>
            <title>Web Services Translator</title>
            <para>
            The Web Services translator, known by the type name <emphasis>ws</emphasis>, 
            exposes stored procedures for calling web services backed by a Teiid WS 
            resource adapter. It will commonly be used with the 
            <link linkend="texttable">TEXTTABLE</link> or 
            <link linkend="xmltable">XMLTABLE</link> table functions to use CSV or XML 
            formated data.
            </para>
            <note><para>Setting the proper binding value on the translator is recommended as it removes the need for callers to pass an explict value.  If your service is actually uses SOAP11, but the binding used SOAP12 you will receive execution failures.</para></note>
            <table>
                <title>Execution Properties</title>
                <tgroup cols="3">
                    <colspec colwidth="3*" />
                    <colspec colwidth="6*" />
                    <colspec colwidth="2*" />
                    <thead>
                        <row>
                            <entry>Name</entry>
                            <entry>Description</entry>
                            <entry>Default</entry>
                        </row>
                    </thead>
                    <tbody>
                        <row>
                            <entry>DefaultBinding</entry>
                            <entry>The binding that should be used if one is not specified.  Can be one of HTTP, SOAP11, or SOAP12
                            </entry>
                            <entry>SOAP12</entry>
                        </row>
                        <row>
                            <entry>DefaultServiceMode</entry>
                            <entry>The default service mode.  For SOAP, MESSAGE mode indicates that the request will contain the entire SOAP envelope and not just the contents of the SOAP body.  Can be one of MESSAGE or PAYLOAD</entry>
                            <entry>PAYLOAD</entry>
                        </row>
                        <row>
                            <entry>XMLParamName</entry>
                            <entry>Used with the HTTP binding (typically with the GET method) to indicate that the request document should be part of the query string.</entry>
                            <entry>null - unused</entry>
                        </row>
                    </tbody>
                </tgroup>
            </table>
            
            <para>
            There are ws importer settings, but it does provide metadata for dynamic VDBs.
            </para>
    
            <section>
                <title>Usage</title>
                <para>
                The WS translator exposes low level procedures for accessing web services.  
                See also the ws-weather example in the kit.
                </para>

                <section>
                    <title>Invoke Procedure</title>
                    <para>
                    Invoke allows for multiple binding, or protocol modes, including 
                    HTTP, SOAP11, and SOAP12. 
                    </para>
                    <programlisting>Procedure invoke(binding in STRING, action in STRING, request in XML, endpoint in STRING) returns XML</programlisting>

                    <para>
                    The binding may be one of null (to use the default) HTTP, SOAP11, or 
                    SOAP12.  Action with a SOAP binding indicates the SOAPAction value.  
                    Action with a HTTP binding indicates the HTTP method (GET, POST, etc.), 
                    which defaults to POST.
                    </para>
                    
                    <para>
                    A null value for the binding or endpoint will use the default value.  
                    The default endpoint is specified in the WS resource adapter 
                    configuration.  The endpoint URL may be absolute or relative.  If it's 
                    relative then it will be combined with the default endpoint.
                    </para>
                    
                    <para>
                    Since multiple parameters are not required to have values, it is often 
                    more clear to call the invoke procedure with named parameter syntax. 
                    </para> 
                    <programlisting>call invoke(binding=>'HTTP', action=>'GET')</programlisting>
                    <para>The request XML should be a valid XML document or root element.</para>
                </section>

                <section>
                    <title>InvokeHTTP Procedure</title>
                    <para>
                    <methodname>invokeHttp</methodname> can return the byte contents of an HTTP(S) call. 
                    </para>
                    <programlisting>Procedure invokeHttp(action in STRING, request in OBJECT, endpoint in STRING, contentType out STRING) returns BLOB</programlisting>
        
                    <para>
                    Action indicates the HTTP method (GET, POST, etc.), which defaults to POST.
                    </para>
                    <para>
                    A null value for endpoint will use the default value.  The default endpoint 
                    is specified in the WS resource adapter configuration.  The endpoint URL may 
                    be absolute or relative.  If it's relative then it will be combined with the 
                    default endpoint.
                    </para>
                    <para>
                    Since multiple parameters are not required to have values, it is often more 
                    clear to call the invoke procedure with named parameter syntax.
                    </para>
                    <programlisting>call invokeHttp(action=>'GET')</programlisting>
                    
                    <para>
                    The request can be one of SQLXML, STRING, BLOB, or CLOB.  The request will be 
                    sent as the POST payload in byte form.  For STRING/CLOB values this will 
                    default to the UTF-8 encoding.  To control the byte encoding, see the 
                    <link linkend="to_bytes">to_bytes</link> function.
                    </para>
                </section>
            </section>
            <section>
                <title>JCA Resource Adapter</title>
                <para>Theresource adapter for this translator provided through "Web Service Data Source", 
                Refer to Admin Guide for configuration.</para>
            </section>             
        </section>
        
        <section>
            <title>OLAP Translator</title>
            <para>
            The OLAP Services translator, known by the type name <emphasis>olap</emphasis>, 
            exposes stored procedures for calling analysis sevices backed by a OLAP server using MDX query lanaguage. 
            This translator exposes a stored procedure, invokeMDX, that returns a result set containing tuple array values for a given MDX query.
            invokeMDX will commonly be used with the <link linkend="arraytable">ARRAYTABLE</link> table function to extract the results.
            </para>
            <para>
            Since the Cube metadata exposed by the OLAP servers and relational database metadata are so different, there is no single 
            way to map the metadata from one to other. It is best to query OLAP system using its own native MDX language
            through. MDX queries my be defined statically or built dynamically in Teiid's abstraction layers.
            </para>
                
            <section>
                <title>Usage</title>
                <para>
                The olap translator exposes one low level procedure for accessing olap services.  
                </para>

                <section>
                    <title>InvokeMDX Procedure</title>
                    <para>
                    <methodname>invokeMdx</methodname> returns a resultset of the tuples as array values. 
                    </para>
                    <programlisting>Procedure invokeMdx(mdx in STRING) returns resultset (tuple object)</programlisting>
        
                    <para>
                    The mdx parameter is a MDX query to be executed on the OLAP server.
                    </para>
                    <para>
                    The results of the query will be returned such that each row on the row axis will be packed into an array value that will first contain each hierarcy member name on the row axis then each measure value from the column axis.
                    </para>
                    <note><para>The use of <xref linkend="dataroles"/> should be considered to prevent arbitrary MDX from being submitted to the invokeMDX procedure.</para></note>
                    <para>
                    </para>
                </section>
            </section>
            <section>
                <title>JCA Resource Adapter</title>
                <para>The resource adapter for this translator provided through data source in JBoss AS, 
                     Refer to Admin Guide for "JDBC Data Sources" configuration section. Two sample
                    -ds.xml files provided for accessing OLAP servers in teiid-examples section. One is Mondrian specific, 
                    when Mondrian server is deloyed
                    in the same JBoss AS as Teiid (mondrian-ds.xml). To access any other OLAP servers using XMLA interface, 
                    the data source for them can be created using them example template olap-xmla-ds.xml</para>
            </section>              
        </section>        
        
        <section>
            <title>Delegating Translators</title>
            <para>
            You may create a delegating translator by extending the <code>org.teiid.translator.BaseDelegatingExecutionFactory</code>.
            Once your classes are then packaged as a custom translator, you will be able to wire another translator instance into your delegating translator at runtime in order to intercept
            all of the calls to the delegate.  This base class does not provide any functionality on its own, other than delegation.
            </para>
            
            <table>
                <title>Execution Properties</title>
                <tgroup cols="3">
                    <colspec colwidth="3*" />
                    <colspec colwidth="6*" />
                    <colspec colwidth="2*" />
                    <thead>
                        <row>
                            <entry>Name</entry>
                            <entry>Description</entry>
                            <entry>Default</entry>
                        </row>
                    </thead>
                    <tbody>
                        <row>
                            <entry>delegateName</entry>
                            <entry>Translator instance name to delegate to</entry>
                            <entry></entry>
                        </row>
                    </tbody>
                </tgroup>
            </table>        
        </section>        
    </section>

</chapter>