<?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="udfs">
	<title>User Defined Functions</title>
	<para>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.</para>
	<section id="define_udf">
		<title>UDF Definition</title>
		<para>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.</para>
		<itemizedlist>
			<para>The following are used to define a UDF.</para>
			<listitem>
				<para>
					<emphasis>Function Name</emphasis>
					When you create the function name, keep these requirements in
					mind:
					<itemizedlist>
						<listitem>
							<para>You cannot overload existing Teiid System functions.</para>
						</listitem>
						<listitem>
							<para>The function name must be unique among user-defined
								functions in its model for the number of arguments.  You can use the
								same function name for different numbers of types of
								arguments.  Hence, you can overload your user-defined
								functions.</para>
						</listitem>
						<listitem>
							<para>The function name cannot contain the '.' character.</para>
						</listitem>
						<listitem>
							<para>The function name cannot exceed 255 characters.
                </para>
						</listitem>
					</itemizedlist>
				</para>
			</listitem>
			<listitem>
				<para>
					<emphasis>Input Parameters</emphasis>
					- defines a type specific signature list. All arguments are
					considered required.
				</para>
			</listitem>
			<listitem>
				<para>
					<emphasis>Return Type</emphasis>
					- the expected type of the returned scalar value.
				</para>
			</listitem>
			<listitem>
				<para>
					<emphasis>Pushdown</emphasis>
					- can be one of REQUIRED, NEVER, ALLOWED. Indicates the expected
					pushdown behavior. If NEVER or ALLOWED are specified then a Java
					implementation of the function should be supplied. If REQUIRED is used, then user must extend the 
                    Translator for the source and add this function to its pushdown function library.
				</para>
			</listitem>
			<listitem>
				<para>
					<emphasis>invocationClass/invocationMethod
            </emphasis>
					- optional properties indicating the static method to invoke when
					the UDF is not pushed down.
				</para>
			</listitem>
			<listitem>
				<para>
					<emphasis>Deterministic</emphasis>
					- if the method will always return the same result for the same
					input parameters.
				</para>
			</listitem>
		</itemizedlist>
		    <para>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.</para>
     
        <note>
            <title>Dynamic VDBs</title>
            <para>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.</para>
        </note>     
	</section>
	<section>
		<title>Source Supported UDF</title>
		<para>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.</para>
			
		<para>For example, suppose you want to use the Oracle-specific
			functions score and contains:
      </para>
		<informalexample>
			<programlisting language="SQL">SELECT score(1), ID, FREEDATA FROM Docs WHERE contains(freedata, 'nick', 1) &gt; 0</programlisting>
		</informalexample>
		<para>
			The
			<function>score</function>
			and
			<function>contains</function>
			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.
		</para>
		<para>
			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).
      </para>
		<itemizedlist>
			<para>For example, to extend the Oracle Connector</para>
			<listitem>
				<para>
					<emphasis>Required</emphasis>
					- 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
					<code>org.teiid.translator.Translator</code>
					annotation to the class, e.g.
					<code>@Translator(name="myoracle")</code>
				</para>
			</listitem>
			<listitem>
				<para>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 isn't needed - the default translation
					should work.</para>
			</listitem>
			<listitem>
				<para>
					Create a new translator jar containing your custom
					ExecutionFactory. Refer to <xref linkend="translator_package"/> and 
					<xref linkend="translator_deploy"/> for instructions on using the JAR file. Once this is extended 
                    translator is deployed in the Teiid Server, use "myoracle" as
                    translator name instead of the "oracle" in your VDB's Oracle source configuration.
					</para>
			</listitem>
		</itemizedlist>
        <section>
            <title>Designer based Source Function Pushdown</title>
            <para>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.</para>
        </section>
	</section>
	<section>
		<title>Non-pushdown Support for User-Defined Functions</title>
		<para>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.</para>
		<section>
			<title>Java Code</title>
			<itemizedlist>
				<para>Code Requirements</para>
				<listitem>
					<para>The java class containing the function method must be defined
						public.  <note><para>As many UDF methods as you want can be declared on the same class.</para></note>
            </para>
				</listitem>
				<listitem>
					<para>The function method must be public and static.</para>
				</listitem>
				<listitem>
					<para>
						Number of input arguments and types must match the function metadata defined
						in <xref linkend="define_udf"/>.
					</para>
				</listitem>
				<listitem>
					<para>
						Any exception can be thrown, but Teiid will rethrow the exception
						as a
						<classname>FunctionExecutionException</classname>
						.
					</para>
				</listitem>
			</itemizedlist>
			<para>You may optionally add an additional <code>org.teiid.CommandContext</code> argument as the first parameter.  
			The <code>CommandContext</code> interface provides access to information about the current command, such as the executing user, Subject, the vdb, the session id, etc.  
			This <code>CommandContext</code> parameter should not be delared in the function metadata.</para>
			<example>
				<title>Sample code</title>
				<programlisting language="Java" role="JAVA"><![CDATA[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;
   }
}]]></programlisting>
			</example>
			<example>
				<title>Sample CommandContext Usage</title>
				<programlisting language="Java" role="JAVA"><![CDATA[package org.something;

public class SessionInfo 
{
   /**
   * @param context 
   * @return the created Timestamp 
   */
   public static Timestamp sessionCreated(CommandContext context)
   {
      return new Timestamp(context.getSession().getCreatedTime());
   }
}]]></programlisting>
            <para>The corresponding UDF would be declared as Timestamp sessionCreated().</para>
			</example>
		</section>
		<section>
			<title>Post Code Activities</title>
			<orderedlist>
				<listitem>
					<para> After coding the functions you should compile the Java
						code into a Java Archive (JAR) file.</para>
				</listitem>
				<listitem>
					<para>The JAR file needs be attached to the VDB file under "lib" directory.</para>
				</listitem>
			</orderedlist>
		</section>
	</section>
	<section>
		<title>Installing user-defined functions</title>
		<para>
			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.</para>
	</section>
	<section>
		<title>User Defined Functions in Dynamic VDBs</title>
		<para>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 <code>ExecutionFactory.getMetadata</code> call allows for the definition of metadata via a <code>MetadataFactory.</code>
		Use the <code>MetadataFactory.addFunction</code> 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.	
		</para>	
		<para>The <code>ExecutionFactory.getPushdownFunctions</code> 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.
		</para>
		<para>Any funcitons added via these mechanisms do not need to be declared in <code>ExecutionFactory.getSupportedFunctions.</code>  Any of the additional handling, such as adding a <code>FunctionModifier</code>, 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.</para>
	</section>
</chapter>