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
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
-
Start the Liquid Data Samples server.
-
Start the Data View Builder.
-
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
-
Click the Test tab. (This shows the generated query statement.)
-
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
-
Create a new project.
-
Click the Toolbox tab and then click Functions.
-
Find the custom function you added: either getCustomerOrder() or getCustomer()
-
Depending on which function you use, add either custOrdUdf.xsd or CustUdf.xsd
from the repository as the target schema
-
Map similar elements from source to target schemas to project the output.
-
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.
-
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.
-
Add ant to your path
-
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.
-
Find the compiled code at <WL_HOME>/liquiddata/samples/buildQuery/db-udf/build
/output.
-
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
-
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
-
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
-
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