Red Hat Training

A Red Hat training course is available for Red Hat JBoss Data Virtualization

7.3. Source Supported Functions

While Red Hat JBoss Data Virtualization 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 is 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 order to configure Red Hat JBoss Data Virtualization to push the above function evaluation to Oracle, you can either: extend the translator in Java, define the function as a pushdown function via Teiid Designer, or, for dynamic VDBs, define it in the VDB.

7.3.1. Defining a Source Supported Function by Extending the Translator

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 functions 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.
To extend the Oracle Connector:
  • Required - extend the OracleExecutionFactory and add SCORE and CONTAINS as supported pushdown functions by either overriding or adding additional functions in "getPushDownFunctions" method. For this example, we'll call the class MyOracleExecutionFactory. Add the org.teiid.translator.Translator annotation to the class, e.g. @Translator(name="myoracle")
  • Optionally register new FunctionModifiers on the start of the ExecutionFactory to handle translation of these functions. Given that the syntax of these functions is same as other typical functions, this probably is not needed - the default translation should work.
  • Create a new translator JAR containing your custom ExecutionFactory. Once this extended translator is deployed in Red Hat JBoss Data Virtualization, use "myoracle" as translator name instead of the "oracle" in your VDB's Oracle source configuration.

7.3.2. Defining a Source Supported Function via Teiid Designer

If you are designing your VDB using Teiid 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.

Note

The function will be visible only for that VDB; whereas, if you extend the translator, the functions can be used by any VDB.

7.3.3. Defining a Source Supported Function Using Dynamic VDBs

If you are using the Dynamic VDB, and defining the metadata using DDL, you can define your source function in the VDB as 
<vdb name="{vdb-name}" version="1">
    <model name="{model-name}" type="PHYSICAL">
        <source name="AccountsDB" translator-name="oracle" connection-jndi-name="java:/oracleDS"/>
        <metadata type="DDL"><![CDATA[
           CREATE FOREIGN FUNCTION SCORE (val integer) RETURNS integer;
           .... (other tables, procedures etc)
         ]]>
        </metadata>
    </model>
</vdb>
By default, in the Dynamic VDBs, metadata for the Source models is automatically retrieved from the source if they were JDBC, File, WebService. The File and WebService sources are static, so one can not add additional metadata on them. However on the JDBC sources you can retrieve the metadata from source and then user can append additional metadata on top of them. For example 
<vdb name="{vdb-name}" version="1">
    <model name="{model-name}" type="PHYSICAL">
        <source name="AccountsDB" translator-name="oracle" connection-jndi-name="java:/oracleDS"/>
        <metadata type="NATIVE,DDL"><![CDATA[
           CREATE FOREIGN FUNCTION SCORE (val integer) RETURNS integer;
         ]]>
        </metadata>
    </model>
</vdb>
The above example uses NATIVE metadata type (NATIVE is the default for source/physical models) first to retrieve schema information from source, then uses DDL metadata type to add additional metadata. Only metadata not available via the NATIVE translator logic would need to be specified via DDL.
Alternatively, if you are using custom MetadataRepository with your VDB, then provide the "function" metadata directly from your implementation. ex. 
<vdb name="{vdb-name}" version="1">
    <model name="{model-name}" type="PHYSICAL">
        <source name="AccountsDB" translator-name="oracle" connection-jndi-name="java:/oracleDS"/>
        <metadata type="{metadata-repo-module}"></metadata>
    </model>
</vdb>
In the above example, user can implement MetadataRepository interface and package the implementation class along with its dependencies in a JBoss EAP module and supply the module name in the above XML. For more information on how to write a Metadata Repository refer to the section on Custom Metadata Repository.