Getting Started with Liquid Data: A Basic Example

These Getting Started steps walk you through a simple example to illustrate the general process of configuring some data sources and constructing a query. This example shows how to build a query similar to the Order Query used in our BEA Liquid Data for WebLogic^TM "Avitek Sample." Following through this process using our example query is an easy and fun way to get familiar with how to configure data sources, construct data views, and run the generated queries in Liquid Data.

The following sections are included here:

If you have not already done so, start the Liquid Data Samples server.

If you have just installed Liquid Data and this the first time running the Samples server after installation, be sure to first populate the Samples database by choosing the following menu option:

Start—>Programs—>BEA WebLogic Platform 7.0—>Liquid Data for WebLogic 1.0—>Liquid Data Samples—>First-Time Samples Configuration

Wait for the Samples setup to complete. Now you are ready to start the server.

BEA_Home/WL_HOME/liquiddata/samples/config/ld_samples/startWeblogic.sh

To start the WLS Administration Console for the Liquid Data Samples server running on your local machine, type the following URL in a Web browser address field:

http://localhost:7001/console/

Login to the console by providing the following default username and password for the Samples server.

The Console Home Page is displayed.

Figure 1-1 Samples WLS Administration Console

This section describes how to configure the two data sources used in the Order Query in the Sample:

Configuring a Relational Database Liquid Data source description consists of first creating a JDBC connection pool and JDBC data source, and then configuring the Liquid Data data source using the JDBC configuration you just created. Each of these tasks is described here:

Now you are ready to add a description of your Relational Database data source to the Liquid Data Server registry. To do this:

You can click on Relational Databases in the breadcrumbs path at the top of the console to see the relational database you added displayed in the summary table.

Configuring an XML File data source description in Liquid Data simply consists of configuring it as a resource using the Liquid Data node in the WLS Administration Console:

You can click on XML Files in the breadcrumbs path at the top of the console to see the file you added displayed in the summary table.

To start the Data View Builder and connect to the Samples server, do the following:

The Data View Builder allows you to construct queries (or data views) by dragging and dropping elements or nodes in source and target schemas for the data sources with which you are working.

You can drag and drop elements among source schemas and use other tools (such as constants and functions) to express the query statement. This is also referred to as defining source conditions.

You can also drag and drop elements from your source schemas to the target schema to shape and define the structure of the query result. This is also referred to as mapping.

As you work in Design mode with the data elements and attributes in source and target schemas using drag-and-drop actions to join or map elements, Data View Builder is generating the XQuery for the query in the background. You can also use the Data View Builder to run the query and view the results in Test Query mode—as a way of testing queries as you build them.

The query you are about to build here is similar to the Order Query featured in the Liquid Data Avitek Sample. Given a customer with orders stored in two data sources ("Wireless" orders in a relational database and "Broadband" orders in an XML file), this query will return the order information for that customer.

To construct our sample Order Query, you need to do the following:

View a Demo

Constructing the "Order Query" Demo... If you are looking at this documentation online, you can click the "Demo" button to see a viewlet demo showing how to build the conditions and create the mappings described in this example. This demo previews the steps described in detail in the following sections.

To open the data sources you need to construct the data view, simply navigate to them on the Builder Toolbar—>Sources tab and double-click on them:

Note: You can open multiple data source schemas and move them around on the workspace as needed.

The target schema we will use for the Order Query is in a file called customerOrderReport.xsd which is available in the Samples server repository.

To add the target schema:

Note: You can open only one target schema at a time and it is always docked to the right side of the workspace.

At this point, your Data View Builder workspace contains two data source schemas and a target schemas you plan to work with as shown in the following figure.

Figure 1-7 Data View Builder with Source and Target Schemas

Note: To expand the XML element trees in source and target schemas select the top element, right mouse-click to get the pop-up menu, and choose Expand.

Note: As you drag and drop elements from source to target, the Mappings tab is automatically displayed in the lower part of the workspace. The mapped elements are highlighted in the schema windows and each mapping is recorded on the Mappings tab showing Source and Target details. If you need to delete a mapping, select it on the Mappings tab by clicking in the Mapping ID field to highlight the row in which the Source/Target mapping is shown and then click the Trashcan icon.

The final step in constructing the data view for our example query is to define some source conditions. This is the way in which you actually pose the "question" (query) or filter designed to get at the data in a particular way. In this example, we want to find out which customers (whose customer IDs match a given constant) have orders in both BroadBand and Wireless.

In the Wireless data source, drag and drop the sub-element CUSTOMER_ID (from CUSTOMER) to CUSTOMER_ID (subelement in CUSTOMER_ORDER) within the same data source schema window.

This creates a join between CUSTOMER and CUSTOMER_ORDER. The syntax of this join is displayed on the first row on Conditions tab:

Note: As you drag and drop elements from source element to source element, the Conditions tab is automatically displayed in the lower part of the workspace. The mapped elements are highlighted in the schema windows and each source condition is recorded on the Conditions tab. If you need to delete a condition, select it on the Conditions tab by clicking in the Condition ID field to highlight the row in which the condition is shown and then click the Trashcan icon.

Drag and drop the Wireless element CUSTOMER/CUSTOMER_ID onto the Broadband data source element CUSTOMER/CUSTOMER_ID.

The syntax of this Join is displayed on the next row on the Conditions tab.

Note: If you ran the query at this point, you would get a query result showing all customers who have orders in both BroadBand and Wireless. In the next step, however, we refine the query a little further.

You can use a function to create filter that matches a given customer.

Note: Make sure that you have the left navigation panel expanded out far enough to the right to be able to see the arrow icons to the left of the Constants fields.

The syntax of this statement is displayed in the Functions Editor.

Figure 1-13 Equation for a Source Condition Shown in Functions Editor

You have set up all the source conditions. They are reflected on the Conditions tab.

Note: To see the full text of the source conditions statements, you may have to expand the Conditions area by clicking and dragging the left or right borders.

Figure 1-14 Source Conditions

Switch to Test View

Now you are ready to switch to the Test tab where you can look at the XQuery syntax of the generated query you just constructed, and then run the query to test it.

Click the Test tab at the top of the workspace.

The Test tab is displayed as shown in the following figure.

Figure 1-15 Test Tab

The Data View Builder generates the following XQuery for the query you constructed with drag-and-drop tools.

Listing 1-1 Generated XQuery for Getting Started Query

{--	Generated by Data View Builder 1.0	--}
<CustomerOrderReport>
	{
	for $MyWireless_LD_DS.CUSTOMER_1 in document("MyWireless-LD-DS")/db/CUSTOMER
	where ($MyWireless_LD_DS.CUSTOMER_1/CUSTOMER_ID  eq  "CUSTOMER_1")
	return
	<customerOrder>
	<CUSTOMER>
	<FIRST_NAME>{ xf:data($MyWireless_LD_DS.CUSTOMER_1/FIRST_NAME) }</FIRST_NAME>
	<LAST_NAME>{ xf:data($MyWireless_LD_DS.CUSTOMER_1/LAST_NAME) }</LAST_NAME>
	<CUSTOMER_ID>{ xf:data($MyWireless_LD_DS.CUSTOMER_1/CUSTOMER_ID) }</CUSTOMER_ID>
	<STATE>{ xf:data($MyWireless_LD_DS.CUSTOMER_1/STATE) }</STATE>
	<EMAIL_ADDRESS>{ xf:data($MyWireless_LD_DS.CUSTOMER_1/EMAIL_ADDRESS) }</EMAIL_ADDRESS>
	<TELEPHONE_NUMBER>{ cast as xs:long(xf:data($MyWireless_LD_DS.CUSTOMER_1/TELEPHONE_NUMBER)) }</TELEPHONE_NUMBER>
	</CUSTOMER>
	<wireless_orders>
	{
	for $MyWireless_LD_DS.CUSTOMER_ORDER_2 in document("MyWireless-LD-DS")/db/CUSTOMER_ORDER
	where ($MyWireless_LD_DS.CUSTOMER_1/CUSTOMER_ID  eq  $MyWireless_LD_DS.CUSTOMER_ORDER_2/CUSTOMER_ID)
	return
	<CUSTOMER_ORDER>
	<ORDER_DATE>{ cast as xs:string(xf:data($MyWireless_LD_DS.CUSTOMER_ORDER_2/ORDER_DATE)) }</ORDER_DATE>
	<ORDER_ID>{ xf:data($MyWireless_LD_DS.CUSTOMER_ORDER_2/ORDER_ID) }</ORDER_ID>

	<CUSTOMER_ID>{ xf:data($MyWireless_LD_DS.CUSTOMER_ORDER_2/CUSTOMER_ID) }</CUSTOMER_ID>
	<SHIP_METHOD>{ xf:data($MyWireless_LD_DS.CUSTOMER_ORDER_2/SHIP_METHOD) }</SHIP_METHOD>
	<TOTAL_ORDER_AMOUNT>{ xf:data($MyWireless_LD_DS.CUSTOMER_ORDER_2/TOTAL_ORDER_AMOUNT) }</TOTAL_ORDER_AMOUNT>
	</CUSTOMER_ORDER>
			}
	</wireless_orders>
	<broadband_orders>
		{
	for $MyBroadBand_LD_DS.CUSTOMER_ORDER_3 in document("MyBroadBand-LD-DS")/db/CUSTOMER_ORDER
	where ($MyWireless_LD_DS.CUSTOMER_1/CUSTOMER_ID  eq  $MyBroadBand_LD_DS.CUSTOMER_ORDER_3/CUSTOMER_ID)
	return
	<CUSTOMER_ORDER>
	<ORDER_DATE>{ xf:data($MyBroadBand_LD_DS.CUSTOMER_ORDER_3/ORDER_DATE) }</ORDER_DATE>
	<ORDER_ID>{ xf:data($MyBroadBand_LD_DS.CUSTOMER_ORDER_3/ORDER_ID) }</ORDER_ID>
	<CUSTOMER_ID>{ xf:data($MyBroadBand_LD_DS.CUSTOMER_ORDER_3/CUSTOMER_ID) }</CUSTOMER_ID>
	<SHIP_METHOD>{ xf:data($MyBroadBand_LD_DS.CUSTOMER_ORDER_3/SHIP_METHOD) }</SHIP_METHOD>
	<TOTAL_ORDER_AMOUNT>{ cast as xs:decimal(xf:data($MyBroadBand_LD_DS.CUSTOMER_ORDER_3/TOTAL_ORDER_AMOUNT)) }</TOTAL_ORDER_AMOUNT>
	</CUSTOMER_ORDER>
			}
	</broadband_orders>
	</customerOrder>
	}
</CustomerOrderReport>

Run the Query

When you are ready to run the query, click the Run query button on the toolbar in the upper left.

Figure 1-16 Query Result on Test Tab

<CustomerOrderReport>
  <customerOrder>
    <CUSTOMER>
      <FIRST_NAME>JOHN_1</FIRST_NAME>
      <LAST_NAME>KAY_1</LAST_NAME>
      <CUSTOMER_ID>CUSTOMER_1</CUSTOMER_ID>
      <STATE>TX</STATE>
      <EMAIL_ADDRESS>abc@abc.com</EMAIL_ADDRESS>
      <TELEPHONE_NUMBER>4081231234</TELEPHONE_NUMBER>
    </CUSTOMER>
    <wireless_orders>
      <CUSTOMER_ORDER>
        <ORDER_DATE>2002-03-06-08:00</ORDER_DATE>
        <ORDER_ID>ORDER_ID_1_0</ORDER_ID>
        <CUSTOMER_ID>CUSTOMER_1</CUSTOMER_ID>
        <SHIP_METHOD>AIR</SHIP_METHOD>
        <TOTAL_ORDER_AMOUNT>1000</TOTAL_ORDER_AMOUNT>
      </CUSTOMER_ORDER>
      <CUSTOMER_ORDER>
        <ORDER_DATE>2002-03-06-08:00</ORDER_DATE>
        <ORDER_ID>ORDER_ID_1_1</ORDER_ID>
        <CUSTOMER_ID>CUSTOMER_1</CUSTOMER_ID>
        <SHIP_METHOD>AIR</SHIP_METHOD>
        <TOTAL_ORDER_AMOUNT>2000</TOTAL_ORDER_AMOUNT>
      </CUSTOMER_ORDER>
    </wireless_orders>
    <broadband_orders>
      <CUSTOMER_ORDER>
        <ORDER_DATE>2002-04-09</ORDER_DATE>
        <ORDER_ID>ORDER_ID_1_0</ORDER_ID>
        <CUSTOMER_ID>CUSTOMER_1</CUSTOMER_ID>
        <SHIP_METHOD>AIR</SHIP_METHOD>
        <TOTAL_ORDER_AMOUNT>1000.00</TOTAL_ORDER_AMOUNT>
      </CUSTOMER_ORDER>
      <CUSTOMER_ORDER>
        <ORDER_DATE>2002-04-09</ORDER_DATE>
        <ORDER_ID>ORDER_ID_1_1</ORDER_ID>
        <CUSTOMER_ID>CUSTOMER_1</CUSTOMER_ID>
        <SHIP_METHOD>AIR</SHIP_METHOD>
        <TOTAL_ORDER_AMOUNT>1500.00</TOTAL_ORDER_AMOUNT>
      </CUSTOMER_ORDER>
    </broadband_orders>
  </customerOrder>
</CustomerOrderReport>

Congratulations! You have successfully built and run a query using Liquid Data.

Note: If you want to switch back to the Design mode again, click the Design View tab on the toolbar.

We suggest you save the project file at this point if you have not done so already.

To save the project for the first time:

The process you just completed with these Getting Started steps is a thin slice of the Liquid Data picture meant to help familiarize you with some of the basics. If you are ready to learn more, try the following topics: