# Custom Functions (DB-UDF) Sample Query Readme

A copy of this readme is included with the DB-DB sample in your Liquid Data installation at <WL_HOME>/liquiddata/samples/buildQuery/db-udf/readme.htm. (The default install home for Liquid Data is bea/weblogic700.)
This readme includes the following topics:

- What this Query Demonstrates
- How to Run the Query
- If You Want to Re-create the Custom Functions and the Query. .

## What this Query Demonstrates

This query demonstrates how to create a custom function (also known as a "user-defined" function). This example also shows how to write custom functions that map to methods in other sources such as a view engine or finder methods in an EJB. Once we define these custom functions, we can use them in XQuery statements the same way we use the Liquid Data built-in functions.
For example, suppose you need to access legacy data via a custom interface such as a Session Bean, Entity Bean, stored procedure and so on. Assume the custom interface is the only way to get data that is needed for another query. In this example, the custom interface is exposed by a session bean which implements two functions: getCustomerOrder and getCustomer. We will expose these as custom functions. By exposing the interface methods as custom functions, we eliminate the need to expose the data source and hence all the data in it, to the Liquid Data engine. A custom function only delivers a subset of data from the data source (in this case, Customer Orders and Customers) without exposing other tables and content.

## How to Run the Query

1. Start the Liquid Data Samples server.
2. Start the Data View Builder.
3. In the Data View Builder, open either of the following project files:

- <WL_HOME>/liquiddata/samples/buildQuery/db-udf/src/sampleProjects/dvbProjects/CustOrdUdf.qpr
- <WL_HOME>/liquiddata/samples/buildQuery/db-udf/src/sampleProjects/dvbProjects/CustUdf.qpr

4. Click the Test tab. (This shows the generated query statement.)
5. Click the "Run Query" button and view the XML result. If the query requires query parameters, enter values for these before running the query.

## If You Want to Re-create the Custom Functions and the Query. . .

## Create a CFLD file

A CFLD file is where you define your function along with a schema associated with the function's return type. This sample uses the cfld file located at:

<WL_HOME>/samples/buildQuery/db-udf/src/examples/ldi/userDefinedFunc/UserDefinedFunction.cfld

The cfld is copied to the Repository under custom_functions folder.

<types>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">

<!-- The schema describing the CustomerOrder object returned -->

<xs:element name="CustomerOrder">
<xs:complexType>
<xs:sequence>
<xs:element ref="ORDER_DATE"/>
<xs:element ref="ORDER_ID"/>
<xs:element ref="CUSTOMER_ID"/>
<xs:element ref="SHIP_METHOD"/>
<xs:element ref="TOTAL_ORDER_AMOUNT"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="ORDER_DATE" type="xs:date"/>
<xs:element name="ORDER_ID" type="xs:string"/>
<xs:element name="SHIP_METHOD" type="xs:string"/>
<xs:element name="TOTAL_ORDER_AMOUNT" type="xs:decimal"/>
<xs:element name="CUSTOMER_ID" type="xs:string"/>
<xs:element name="CustomerOrders">
<xs:complexType>
<xs:sequence>
<xs:element ref="CustomerOrder" minOccurs="0" maxOccurs="unbounded"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
</types>

Note that in the cfld file shown above under the type we have defined a customerOrder Element consisting of ORDER_DATE,ORDER_ID,SHIP_METHOD,TOTAL_ORDER_AMOUNT,CUSTOMER_ID

Next we create a function
<functions>
<!-- name is the function name,
return_type : is the custom method return type
class: fully qualified classname
method: the method in the class to map to
argument: list of argument with type. This is are the input(s) to the method with asscociated labels
-->
<!-- The function is mapping to getCustomerOrder in examples.ldi.userDefinedFunc.UserFunctionMapping.class
It takes to input (url and customer_id), and returns a Element object of type CustomerOrder
as defined above
-->
<function name="getCustomerOrder" return_type="CustomerOrders" class="examples.ldi.userDefinedFunc.UserDefinedFuncMapping" method="getCustomerOrder">
<argument type="xs:string" label="url"/>
<argument type="xs:string" label="customerID"/>
<presentation group="Sample custom functions" />

<description>Get Customer Order gets a list of customer order for a given customer id </description>

</function>

Create a Mapping class
The mapping class maps the functions defined in the cfld with the actual implementation.

The mapping class used in this sample is located at:
<BEA_HOME>/weblogic700.liquiddata/samples/buildQuery/db-udf/src/examples/ldi/userDefinedFunc/UserDefinedFuncMapping.java

public static Element getCustomerOrder(String url, String customerID)

The method getCustomerOrder matches with the function description specified in cfld. Note that it return type is an Element (XML). But this element is described in the cfld file as a type of customerOrders. Hence the function mapping is responsible for generating an element of type customer order. In our sample the mapping function does a JNDI lookup to call getCustomerOrder in the sesion bean.

### Build the Query in the Data View Builder

1. Create a new project.
2. Click the Toolbox tab and then click Functions.
3. Find the custom function you added: either getCustomerOrder() or getCustomer()
4. Depending on which function you use, add either custOrdUdf.xsd or CustUdf.xsd from the repository as the target schema
5. Map similar elements from source to target schemas to project the output.
6. Run the query. (Click the Test tab and click Run Query button.)

## If You Want to Build the Sample Source Code . . .

If you want to go through the process of building a custom function, you can build the sample source code as follows.

1. Run setLDExampleEnv.cmd or setLDExampleEnv.sh located at <LD_HOME>/samples/config/ld_samples. (This will set up your environment variables needed to run the query.
2. Add ant to your path
3. Compile the source code via build.xml using ant:

- cd <LD_HOME>/samples/buildQuery/db-udf/build
- ant

This will compile the script and generate the ear and other jar files for you in the output directory.
4. Find the compiled code at <WL_HOME>/liquiddata/samples/buildQuery/db-udf/build /output.
5. Copy the ldsample_udf.ear to the applications directory of your development domain <LD_HOME>/samples/buildQuery/db-udf/build /output/applications/ldsample_udf.ear to <SAMPLE_DOMAIN>/applications
6. Copy the ldsample_udf_rep.jar and ldsample_clientAPI.jar to the domain's repository under custom_lib folder

<LD_HOME>/samples/buildQuery/db-udf/build /output/mapping_classes/*.jar
to
<LDSAMPLE_DOMAIN>/ldrepository/custom_lib

7. Copy the UserDefinedFuncMapping.cfld to the repository under custom_functionsn folder.

<LD_HOME>/samples/buildQuery/db-udf/src/examples/ldi/userDefinedFunc/UserDefinedFuncMapping.cfld
to
<LDSAMPLE_DOMAIN>/ldrepository/custom_functions

8. On the ldConsole server---liquiddata refresh or create a entry for functionResourcelib giving a name for the function group along with the name of the cfld file.

If you used the DVB, you will notice your custom functions have been added.

### Reference

- You can view the target schema that gets customers at <WL_HOME>/liquiddata/samples/config/ld_samples/ldrepository/schemas/custUdf.xsd
- You can view the target schema that gets customer orders at <WL_HOME>/liquiddata/samples/config/ld_samples/ldrepository/schemas/custorderUdf.xsd
- You can view the cfld file at
<WL_HOME>/samples/buildQuery/db-udf/src/examples/ldi/userDefinedFunc/UserDefinedFunction.cfld