| Download Docs | Site Map | Glossary | |
|
|
|||
| bea.com | products | dev2dev | support | askBEA |
|
|
|
|||||||
 |
| e-docs > Liquid Data for WebLogic > Building Queries and Data Views > Designing Queries |
|
Building Queries and Data Views
|
Designing Queries
This section explains how to design and build a BEA Liquid Data for WebLogicTM query using the Data View Builder, and provides example walk-throughs of how to build some simple queries. The following topics are included:
Designing a Query
The first step in constructing a query (or, more often, a set of queries) is a design step—drawing on the requirements identified to answer the following questions critical to the query design:
Note: Proper design of the target schema is a key factor in building a successful query. In a nutshell, you need to ensure that cardinality is correct and check for target conformity. For complete guidelines and examples of recommended design patterns, see Target Schema Design Guidelines and Query Examples.
Once you have designed or "modeled" the query in this way based on what you want the query to do and defined an outline strategy for accomplishing the information filtering, you are ready to build a test version of the query. For other than very simple queries, you will probably revise, refine and test the query several times adding optimization if necessary.
Building a Query
Building a query involves specifying one or more source schemas that describe resource data, selecting a single target schema that describes the shape of the query result, creating source-to-target mappings to further define what the query result will look like, and defining source conditions or filters on the data sources. The query extracts the results based on the conditions and mappings that you define in the query. The results can change dramatically depending on how you do the following:
If you have taken the time to outline a design for the query first, considering all the factors mentioned in the previous section (Designing Queries), constructing it will be a matter of following your design as a blueprint for drag-and-drop query building. Then you can test, fine-tune, and modify as needed to produce variations on the results, or to optimize the query for better performance.
The following sections take you through the basic tasks involved in building a query:
Opening the Source Schemas for the Data Sources You Want to Query
A source schema is the XML schema representation for the structure of the data in a data source. You can use multiple data source schemas per query.
The Sources tab on the Builder Toolbar contains the data sources configured on the Liquid Data Server to which you are connected. Note that a data source type only shows up as a button on the Builder Toolbar if it has been configured in the Server to which you are connecting.
Note: Only data sources that have been configured for access by Liquid Data are available from the Builder Toolbar. For information on how to configure Liquid Data data sources, see the Liquid Data Administration Guide.
For this example, open the schemas for the following two data sources which are already configured on the Liquid Data Samples server:
To do this, follow these steps:
The XML schemas for the each of the data sources are displayed.
Position the schema windows so you can view the data nodes in each schema. You can expand the data nodes by clicking the plus (+) sign. For example, in the PB-WL data source, CUSTOMER is a parent node with subordinate child nodes. The child nodes are the ones you will use as function parameters and map to the target schema.
Adding a Target Schema
A target schema is the XML schema representation for the structure of the target data (query result). Only one target schema per project is allowed. If you have a target schema open and decide to choose another, the current target schema is closed and the new one replaces it.
You can use a target schema file that you have saved on your local system or on the network, or one that has been saved to the Liquid Data server repository.
Note: Only target schemas that are saved to the Liquid Data server repository will be available to other Liquid Data users for distributed, team-style development.
For this example, we will use a target schema called amtByState. If this schema is not available in the Samples server repository and you would like to follow along with our example, you can create it yourself and save it locally or to the server repository as a .xsd file.
To create and set the target schema do the following:
<WL_HOME>liquiddata/samples/config/ld_samples/repository/schemas/
Note: It is not necessary to save the target schema to the server Repository in order to use it in your local project—you can save it anywhere on your system. However, we recommend saving schemas to the Repository because it makes projects more "portable" and schema files accessible to all users who log onto this server.
Listing 3-1 XML Source for amtByState.xsd Target Schema File
<?xml version = "1.0" encoding = "UTF-8"?>
<xsd:schema xmlns:xsd = "http://www.w3.org/2001/XMLSchema">
<xsd:element name="customers">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="STATE" minOccurs="0" maxOccurs="unbounded">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="state" type="xsd:string" minOccurs="0" maxOccurs="1"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="CUSTOMER" minOccurs="0" maxOccurs="unbounded">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="FIRST_NAME" type="xsd:string"/>
<xsd:element name="LAST_NAME" type="xsd:string"/>
<xsd:element name="AVERAGE_ORDER" type="xsd:string"/>
<xsd:element name="CUSTOMER_ID" type="xsd:string"/>
<xsd:element name="STATE" type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
Note: Remember that only one target schema per project is allowed. The target schema docks on the right side of the desktop area. The target schema may have more data nodes than you need for your result, but it must contain the data nodes required for the query result. Unreferenced nodes are disregarded in the result.
Editing a Target Schema
You can make simple changes to a target schema by right-clicking a node. A shortcut menu shows the editing functions that are available.
Mapping Source and Target Schemas Mapping is a visual relationship structure among the critical data elements in the query. When you combine these relationships with functions, you have a set of instructions for the query generation engine. If you think of the selected data nodes as the nouns (what we want to work on), the functions as the verbs (the action), then the mapping among the data elements creates a logical sentence that expresses the query. Before you begin creating a query, it is important to expand the nodes in the source schemas to reveal the data elements that you want to use at their lowest level. To expand a node, right click on it and choose Expand. Note: You can use automatic type casting to ensure that input parameters used in functions and mappings are appropriate to the function in which they are used. When Automatic Type Casting is in effect, Liquid Data verifies (and if necessary promotes) the data types of input parameters for all source-to-target mappings and functions. For more information about automatic type casting, see Using Automatic Type Casting. Mapping Node to Node You can use drag-and-drop mappings from one element or attribute to another to create conditions on source data and source-to-target mappings that will define the shape of the query result. Mapping Nodes to Create Conditions on Source Data Choose one of the following methods to map a source schema node to another source schema node to create a Condition.
Or
Mapping Nodes to Create Source-to-Target Mappings
Choose one of the following methods to map a source schema node to a target schema node.
Or
Note: You cannot map the same node from more than one source schema to a single node in the target schema. For example, if you map STATE (under CUSTOMER) from the Broadband database to state? in the target schema, you cannot successfully map STATE from a second source schema to state? in the target schema. The last mapping completed is the only mapping from source to target that the query generation engine processes. If you need to create a relationship among all three STATE elements, map the element in one source schema to the element in the second source schema. Then map one of the source elements to the target element.
Example: Query Customers by State
Figure 3-1 shows a simple join of the source element STATE in the Broadband source schema (XM-BB-C) with a source element STATE in the Wireless source schema (PB-WL). This action joins the common elements in each schema and disregards those that do not occur in both schemas.
Next, to project a result we designate what the output of this relationship should look when the query runs. By mapping one of the sources to the target, we specify that we want to store the result in the target schema. Because we are collecting only information about states and defining only one element in the target schema, we are in effect asking that Liquid Data fill only that data element in the result when the query runs.
To do this, drag and drop the STATE element in PB-WL source schema onto the state? element (under STATE*) in the Target schema.
See Figure 3-1 for an example of the mappings described in this example.
Figure 3-1 Mapping Element to Element
Mapping Nodes to Functions When you drag and drop a source node onto another source node (either within the same source schema or among different source schemas) you are automatically creating an equality relationship between the two elements/attributes using the eq (equals) function. In other words, the eq function is mapped by default for all drag-and-drop relationships you create among source elements/attributes. You can also create the same equality relationship the "long" way by dragging and dropping the eq function onto a row in the Conditions tab and then dragging and dropping source elements/attributes into the same row, or by opening the Functions Editor and dragging and dropping the function and elements directly into the Editor. To use any of the available functions other than eq (equality) function, you must use this second method of dealing directly with the functions as described below. To use a function:
To edit an existing functional relationship:
Figure 3-2 shows the functional relationship of equality (eq) between two source elements that was created by default when you mapped the source elements in Example: Query Customers by State. (Note that you could have created this same relationship directly in the Functions Editor the way you would create any other functional relationship between elements/attributes.)
Figure 3-2 Mapping Elements to Functions
To get the view shown in Figure 3-2, click on the Conditions tab, select the row with the condition in it to activate the Edit button, and click the Edit button. This displays the condition in the Functions Editor. For more information about functions, see What are Functions?. For more information about using the Functions Editor and working with functions on the UI, see Functions and The Function Editor in Starting the Builder and Touring the GUI. Supported Mapping Relationships Data View Builder and Liquid Data support any of the Mapping actions described in the following table. Table 3-1 Supported Mapping Relationships
Removing Mappings Mapped elements/attributes in a query are displayed on the Mappings tab. You can change your mind and remove a mapping by selecting the row or cell that contains it and then clicking the trashcan button. (See Figure 3-3.) Figure 3-3 Removing a Mapping
Setting Conditions You can create conditions or filters on source data by doing any of the following:
What are Functions?
Functions are used as the verbs or actions in condition statements that establish relationships between or operations on data source elements or attributes. (The data source elements/attributes become one type of parameter to the functions.) A function is a built-in executable process that manipulates the data to perform a task. You must pass one or more parameters, which can be source data, variables, or constant values, for the function to produce output. The function returns a result to you based on the conditional statements you build and how you specify where to store the result.
In the previous example (Example: Query Customers by State) we defined the default equality relationship between two source elements (by dragging and dropping the CUSTOMER "STATE" element from one source to another); then defined the result by dragging and dropping the CUSTOMER "STATE" element from one of the source schemas onto the analogous "STATE" element in the target schema.
If you need to find out something other than information based on equality, you will need to use a different function. For example, suppose you want to find out how many customer IDs in the Broadband database are not equal to those in the Wireless database. The default functional action is to look for equality. If you simply map one customer ID source element/attribute to the other, the query engine looks for those instances of matching data, or equality.
(For the relationship of not equal to, you need to go to Builder Toolbar—>Sources tab—>Functions panel, expand the Operators node, and choose the ne function.)
When any functional relationship is involved besides equality, you must chose from the list of functions available in the Builder Toolbar—>Sources tab—>Functions panel. At that point you are applying a filter of your choice. It is very important to choose the function before you map the elements. Most of the Data View Builder functions are standard XML query language functions supported by the W3C. For related information about using functions, see the Functions Reference.
Note: You can use automatic type casting to ensure that input parameters used in functions and mappings are appropriate to the function in which they are used. When Automatic Type Casting is in effect, Liquid Data verifies (and if necessary promotes) the data types of input parameters for all source-to-target mappings and functions. For more information about automatic type casting, see Using Automatic Type Casting.
Using Constants and Variables in Functions
Instead of choosing an existing element/attribute as a parameter value, you can use one of these methods to specify that a constant value should be used instead of a data element from a source schema.
Removing Conditions
Conditions are displayed in the Design view on the Conditions tab. You can change your mind and remove a condition by selecting the row or cell that contains it and then clicking the trashcan button. (See Figure 3-4.)
Figure 3-4 Removing a Condition
Adding or Deleting Parameters in a Condition Statement To add or delete a parameter, select the row that contains the condition you want to edit and click the Edit button to bring up the Functions Editor.
In the Functions Editor, you can select the parameter you want to delete and click the trash can or use the options on the Edit menu to modify the condition statement. You can drag and drop different functions into the Functions Editor from the Functions panel on the Builder Toolbar—>Toolbox tab. Showing or Hiding Data Types You can show or hide data types on all source and target elements/attributes in schema windows. Select View—>Data Types to display the data type of any source or target element/attribute, as well as required function parameter types. (An "X" next to the Data Types option on the View menu indicates that it is on.) You can use automatic type casting to ensure that input parameters used in functions and mappings are appropriate to the function in which they are used. Note: For a complete reference showing how Liquid Data transforms source element/attribute data types to data types of target elements/attributes, see the Type Casting Reference. Select Automatic Type Casting on the Query menu to ensure that Liquid Data will assign (cast) a new data type when the source node data type does not match the mapped target node data type, and the source node is eligible to be type cast to the target node data type. (An "X" next to the Automatic Type Casting option on the Query menu indicates that it is on.) When function parameters have a numeric type mismatch, the Liquid Data server can promote the input source to the input type required by the function if the promotion adheres to the prescribed promotion hierarchy. The promotion hierarchy exists only for numeric values.
If the type mismatch requires casting in the reverse order, the server does not attempt type casting. In this case, Liquid Data attempts to type cast but the results may be unpredictable. For example, if the required function input type is xs:decimal, then source data that is integer, long, int, short, or byte can easily be promoted to a data type with more precision or larger number of digits. The server will complete that task. If the input function type is xs:double or xs:float and the required input type is xs:integer or xs:byte, Liquid Data tries to type cast successfully, but there may be unpredictable rounding or truncating. All other type mismatches, such as xs:date, xs:dateTime, or xs:string, require a type cast to avoid a type mismatch error. Clear the Automatic Type Casting check box to disable this feature. Exceptions to Automatic Type Casting Liquid Data does not type cast comparison operators (such as eq, le, ge, ne, gt, lt, or ne) or any functions that accept xsext:anytype. Type casting does not apply to function parameters (as well as target schema elements/attributes) that require these data types:
Automatic type casting does not succeed in all cases. If the source data is not compatible with the data type of the target node, automatic type casting will not improve the query results. For example, mapping a date to a numeric type may not produce useful results if the data is not relevant. You may not see an error on a type mismatch until the Liquid Data Server tries to run the query.
Examples of Simple Queries
This section includes walk-through examples of how to build some simple queries using the Data View Builder tools and features just described:
To work through these examples, begin on the Data View Builder "Design" tab. If you have worked through the previous example using amtByState target schema, we suggest you close that project and open/save a new project for each of the examples described below.
Example: Return Customers by Name
In this example, you want to return the last and first names of all Wireless customers with a last name that begins with "K."
Build the Query
The approach we will use is similar to the first example in this chapter; however, you are adding a condition that the last name begins with "K." Build the condition with the starts-with function as follows.
Figure 3-5 shows the Design view of the query with conditions and source-to-target mappings completed.
Figure 3-5 Design View of Query Example: Return Customers By Name
View the XQuery and Run the Query to Test it Now that you have built the query, you can switch to the Test tab to view the generated XQuery and run the query to see the kind of result it returns.
Listing 3-2 XQuery for Example: Return Customers By Name
{-- Generated by Data View Builder 1.0 --}<customers>
{
for $PB_WL.CUSTOMER_1 in document("PB-WL")/db/CUSTOMER
where xf:starts-with($PB_WL.CUSTOMER_1/LAST_NAME,"K")
return
<CUSTOMER>
<LAST_NAME>{ xf:data($PB_WL.CUSTOMER_1/LAST_NAME) }</LAST_NAME>
</CUSTOMER>
}
</customers>
Listing 3-3 XML Query Result for Example: Return Customers By Name
<customers>
<CUSTOMER>
<LAST_NAME>KAY_1</LAST_NAME>
</CUSTOMER>
<CUSTOMER>
<LAST_NAME>KAY_2</LAST_NAME>
</CUSTOMER>
<CUSTOMER>
<LAST_NAME>KAY_3</LAST_NAME>
</CUSTOMER>
<CUSTOMER>
<LAST_NAME>KAY_4</LAST_NAME>
</CUSTOMER>
<CUSTOMER>
<LAST_NAME>KAY_5</LAST_NAME>
</CUSTOMER>
<CUSTOMER>
<LAST_NAME>KAY_6</LAST_NAME>
</CUSTOMER>
<CUSTOMER>
<LAST_NAME>KAY_7</LAST_NAME>
</CUSTOMER>
<CUSTOMER>
<LAST_NAME>KAY_8</LAST_NAME>
</CUSTOMER>
<CUSTOMER>
<LAST_NAME>KAY_9</LAST_NAME>
</CUSTOMER>
<CUSTOMER>
<LAST_NAME>KAY_10</LAST_NAME>
</CUSTOMER>
</customers>
(For complete details on how to test and run a query, see Testing Queries.)
Example: Query Customers by ID and Sort Output by State
In this example, there are two pieces of information that we want to display in the result. We want to find Customer IDs for customers who exist in both databases and we want to know the state each found customer resides in.
This example shows how to do the following:
Open the Data Sources and Add a Target Schema
Map Nodes from Source to Target Schema to Project Output
To project Customer first and last names and state to Target, do the following:
Join Two Sources
To create a join between Wireless (PB-WL) and Broadband (PB-BB) on customer IDs, do the following:
The following shows the mappings in the Data View Builder.
Figure 3-8 Example: Query Customers by ID and Sort Output by State
Specify the Order of the Result Using the Sort By Features To order the output alphabetically by State do the following:
View the XQuery and Run the Query to Test it
Now that you have built the query, you can switch to the Test tab to view the generated XQuery and run the query to see the kind of result it returns.
Listing 3-4 XQuery for Example: Query Customers by ID and Sort Output by State
{-- Generated by Data View Builder 1.0 --}<customers>
{
for $PB_WL.CUSTOMER_1 in document("PB-WL")/db/CUSTOMER
let $CUSTOMER_2 :=
for $PB_BB.CUSTOMER_3 in document("PB-BB")/db/CUSTOMER
where ($PB_BB.CUSTOMER_3/CUSTOMER_ID eq $PB_WL.CUSTOMER_1/CUSTOMER_ID)
return
xf:true()
where xf:not(xf:empty($CUSTOMER_2))
return
<CUSTOMER>
<FIRST_NAME>{ xf:data($PB_WL.CUSTOMER_1/FIRST_NAME) }</FIRST_NAME>
<LAST_NAME>{ xf:data($PB_WL.CUSTOMER_1/LAST_NAME) }</LAST_NAME>
<STATE>{ xf:data($PB_WL.CUSTOMER_1/STATE) }</STATE>
</CUSTOMER>
sortby(STATE ascending)
}
</customers>
Listing 3-5 XML Query Result for Example: Query Customers by ID and Sort Output by State
<customers>
<CUSTOMER>
<FIRST_NAME>JOHN_3</FIRST_NAME>
<LAST_NAME>KAY_3</LAST_NAME>
<STATE>AZ</STATE>
</CUSTOMER>
<CUSTOMER>
<FIRST_NAME>JOHN_8</FIRST_NAME>
<LAST_NAME>KAY_8</LAST_NAME>
<STATE>AZ</STATE>
</CUSTOMER>
<CUSTOMER>
<FIRST_NAME>JOHN_10</FIRST_NAME>
<LAST_NAME>KAY_10</LAST_NAME>
<STATE>CA</STATE>
</CUSTOMER>
<CUSTOMER>
<FIRST_NAME>JOHN_5</FIRST_NAME>
<LAST_NAME>KAY_5</LAST_NAME>
<STATE>CA</STATE>
</CUSTOMER>
<CUSTOMER>
<FIRST_NAME>JOHN_4</FIRST_NAME>
<LAST_NAME>KAY_4</LAST_NAME>
<STATE>NV</STATE>
</CUSTOMER>
<CUSTOMER>
<FIRST_NAME>JOHN_9</FIRST_NAME>
<LAST_NAME>KAY_9</LAST_NAME>
<STATE>NV</STATE>
</CUSTOMER>
<CUSTOMER>
<FIRST_NAME>JOHN_1</FIRST_NAME>
<LAST_NAME>KAY_1</LAST_NAME>
<STATE>TX</STATE>
</CUSTOMER>
<CUSTOMER>
<FIRST_NAME>JOHN_6</FIRST_NAME>
<LAST_NAME>KAY_6</LAST_NAME>
<STATE>TX</STATE>
</CUSTOMER>
<CUSTOMER>
<FIRST_NAME>JOHN_2</FIRST_NAME>
<LAST_NAME>KAY_2</LAST_NAME>
<STATE>WA</STATE>
</CUSTOMER>
<CUSTOMER>
<FIRST_NAME>JOHN_7</FIRST_NAME>
<LAST_NAME>KAY_7</LAST_NAME>
<STATE>WA</STATE>
</CUSTOMER>
</customers>
Understanding Scope in Basic and Advanced Views
Adding Scope to a condition is a way to specify the extent that the condition applies to the result. It helps you specify which part of a data view is the focal point for a particular condition in the query. A scope setting affects the placement of a "where" clause in the XQuery generation.
When you add a condition, the Data View Builder makes a best guess as to where the condition should appear in the query. The Data View Builder draws information from the structure of the target schema, the mappings from source schemas to the target schema, and the conditions.
In most cases, scope is implicit and the query generator can determine what the desired result should be. In other cases, it makes a very conservative assumption about the resulting scope of the condition. You can communicate your objectives more efficiently if you specify exactly what you want the query to return. By toggling to the Advanced view in the Data View Builder (Advanced View for Defining Explicit Scope for Conditions) and setting scope you explicitly indicate what part of the output, or query result, is affected by the condition.
The following sections are included here:
Where Does Scope Apply?
There are three candidate areas where Liquid Data sets scope. Scope candidates are:
Remember that a repeatable element always appears with an asterisk (*) or plus sign (+) occurrence indicator.
Basic View (Automatic Scope Settings)
The default setting in the Data View Builder is the basic view. In this view, when you add a condition to any query, the Data View Builder applies an automatic scope setting using internal rules that specify where the condition should appear in the query.
In most cases, the scope setting that Data View Builder chooses for each condition is the correct setting. When you have complex conditions, or a particular result in mind, you may want to switch to the Advanced mode where you can change scope settings as you wish.
Advanced View (Setting the Scope Manually)
Data View Builder enables you to override the automatic scope setting by using an Advanced view of the existing scope settings. When you switching to an Advanced view, Data View Builder displays the setting it selected for automatic scoping. You can change any or all of the individual scope settings or allow them to retain their original values.
When you switch to the Advanced view, it is not necessary to change any of the explicit scope settings selected by Data View Builder. However, if you add new conditions when you are in Advanced view, or change existing conditions, you must set the new scope manually for each condition.
To switch to the Advanced view, click Advanced view toggle so that an X is displayed next to Advanced view. The Conditions tab expands to show more information about the condition targets.
Figure 3-9 Advanced View Showing Explicit Scope on Conditions Tab
Condition and Target pairs appear row by row. If there are multiple scope settings for a condition, the condition reappears in separate rows to display each unique scope setting.
Figure 3-10 Advanced View
The Current Scope text box shows the default scope setting for every condition that you add. Remember that the Basic view settings will continue to appear until you change them. If you add a new condition in Advanced view, the default scope is the target schema root until you change that value. When to Use Advanced View to Set Scope Manually Data View Builder automatically scopes conditions wherever it is most logical, possibly in more than one place. However, occasionally it may not put the automatic scope setting where you think it should be. In these cases, you can switch to the Advanced view to overwrite the automatic scope setting. The most common case occurs when a condition logically applies in two places, but you want it to appear in only one place. You can diagnose this by examining the XQuery translation for where clauses, or do a test run of the query to view the result. If you are not satisfied, switch to the Advanced view to determine where the condition appears. Remember that Data View Builder lists the same condition more than once if it has more than one scope setting. Change the scope setting that you do not want by following the directions in Task Flow Model for Advanced View Manual Scoping. A less common case is when you want to create an assertion. For example, the Liquid Data Server should return a result only when a certain condition occurs. You can accomplish this if you switch to the Advanced view, create the condition, and set the scope for the condition to be the root of the target schema. Note: It is a good idea to run the query using the automatic scope settings first to ensure that it is necessary to revise the scope setting. Task Flow Model for Advanced View Manual Scoping If you decide to override automatic scope settings, there is a workflow model that will help you design the query, create conditions, and determine the scope. By following this methodology, you will find it is easy to create a query where you control the scope. Consider the example shown in Figure 3-11 of two source schemas: PB-BB and PB-WL, and the target schema customerLineItems.xsd. Figure 3-11 Schemas for Manual Scope Example
The target schema, customerLineItems.xsd, has a hierarchical structure. There are three distinct sections in the schema that represent repeatable data. customer and order each have an asterisk (*) as the occurrence indicator. line_item has a plus sign (+) as the occurrence indicator. This means that the child nodes without an asterisk or plus are non-repeating. For each customer, there is one occurrence of first_name, last_name, and id. Each customer may have zero or more orders. When an order exists, each order has one id, date, and amount. If an order exists, there must be at least one line_item. Work on sections that appear under a repeatable node. This workflow model assumes that you can build your query in steps, focusing on each section in the target schema as you go. Follow these steps for each section in the target schema where you want a result to appear.
By setting the default scope before creating the condition, Data View Builder sets the condition scope to that value.
By mapping one section at a time and using the repetitive ancestor node as the default scope, your conditions will apply exactly where you need them to appear in the result.
For our example, we set as a condition a join between CUSTOMER_ID in the PB-BB schema and CUSTOMER_ID in the PB-WL schema as shown in the figure below.
In a small number of cases, you may apply a condition on the argument (input) to a function that requires choosing the function as the default scope. This is not common but will occur when you choose a complex aggregate function.
Returning to Basic View
When you toggle Advanced view off (no X showing next to Advanced view), Data View Builder returns to automatic scoping mode and discards the changes you made in manual mode. The Current Scope text box and the Targets column disappear.
Saving Projects from Basic or Advanced View
If you save a project from Basic view, the project file discards scope information. When you reopen this project, Liquid Data once again applies automatic scope using its internal algorithms.
If you save a project from the Advanced view, all conditions retain current scope settings. When you reopen this project, all Advanced view settings appear.
Version Control
Liquid Data assigns a version attribute to the project file. If you open a project file created with an earlier version of Liquid Data, the project opens in the Advanced view if all conditions have explicit scope settings.
Scope Recursion Errors
It is possible to create a query where a condition depends on the values returned by a function, but the function input depends on the condition. For example:
The xf:count function input must be filtered by applying the condition, but the condition input is the output of xf:count.
Data View Builder does not allow this to happen when automatic scoping is enabled. However, if you clear the Auto-Select Targets check box and set scope manually, it is possible for you to set the scope of a condition to a function input that creates a circular dependency. Data View Builder cancels the action and generates an error message:
Setting Scope/Target of condition {condition} to {scope node} creates circular dependency
Recommended Action
Basic view should generally support most scenarios—we expect that only a few users and/or queries will require use of the Advanced view manual scoping feature. You can assume that Liquid Data can interpret the scope requirements correctly for most types of queries. If you do choose to set scope manually, examine the generated XQuery to ensure the condition targets meet your expectations. If the recursion error message appears, consider resetting all condition scope targets. Override the automatic settings one at a time, switch to Test view to examine the query, run it, and assess the results.
Understanding Query Design Patterns
Here we present some common query patterns generated by the Data View Builder and provide high-level guidelines for effective query design including target schema design and source replication.
Target Schema Design Guidelines and Query Examples
This section provides several examples of queries built with the Data View Builder. We describe the conditions and mappings for a query and the resulting XQuery. The purpose of this is to illustrate how we follow certain guidelines to design the various types of example queries.
Design Guidelines
Use these guidelines when working with target schemas in your queries:
Examples of Effective Query Design
For the following examples, assume we have two schema sets (databases) with the following entities (tables).
Broadband Schema with the following tables:
Wireless Schema with the following tables:
Example 1: Find all Broadband customers who are also Wireless customers
In this situation you do not project anything from the Wireless customer table.
The generated query will iterate over all customers and in Broadband and check for the existence of a matching customer in Wireless. This query also ensures duplicate customers are not returned from Broadband in the event a Broadband customer matches more than one Wireless customer.
Instructions to create query using Data View Builder:
The query looks like:
<db>
{
for $PB_BB.CUSTOMER_1 in document("PB-BB")/db/CUSTOMER
let $CUSTOMER_2 :=
for $PB_WL.CUSTOMER_3 in document("PB-WL")/db/CUSTOMER
where ($PB_BB.CUSTOMER_1/CUSTOMER_ID eq $PB_WL.CUSTOMER_3/CUSTOMER_ID)
return
xf:true()
where xf:not(xf:empty($CUSTOMER_2))
return
<CUSTOMER>
<FIRST_NAME>{ xf:data($PB_BB.CUSTOMER_1/FIRST_NAME) }</FIRST_NAME>
<LAST_NAME>{ xf:data($PB_BB.CUSTOMER_1/LAST_NAME) }</LAST_NAME>
</CUSTOMER>
}
</db>
If you do not care about duplicates or know there will not be duplicates, you can avoid xf:entity(...) checking by projecting an element from the Wireless customer table.
Instructions to create this alternative version of the query using Data View Builder:
The query now looks like:
<db>
{
for $PB_WL.CUSTOMER_1 in document("PB-WL")/db/CUSTOMER
for $PB_BB.CUSTOMER_2 in document("PB-BB")/db/CUSTOMER
where ($PB_BB.CUSTOMER_2/CUSTOMER_ID eq $PB_WL.CUSTOMER_1/CUSTOMER_ID)
return
<CUSTOMER>
<FIRST_NAME>{ xf:data($PB_BB.CUSTOMER_2/FIRST_NAME) }</FIRST_NAME>
<LAST_NAME>{ xf:data($PB_BB.CUSTOMER_2/LAST_NAME) }</LAST_NAME>
<STATE>{ xf:data($PB_WL.CUSTOMER_1/STATE) }</STATE>
</CUSTOMER>
}
</db>
Example 2: Find all Broadband customers and their Wireless line items
This query basically asks for all Broadband customers and Wireless line items for which there exists a Wireless order that joins with both the Broadband customer and Wireless line item.
Now for this situation user does not project anything from Wireless order table.
The generated query will iterate over all customers and in Broadband, then for each line item it will check for the existence of a matching order in Wireless that also matches a customer in Broadband.
Instructions to create query using Data View Builder:
The query looks like:
<ROWS>
{
for $PB_BB.CUSTOMER_1 in document("PB-BB")/db/CUSTOMER
return
<CUSTOMER>
<FIRST_NAME>{ xf:data($PB_BB.CUSTOMER_1/FIRST_NAME) }</FIRST_NAME>
<LAST_NAME>{ xf:data($PB_BB.CUSTOMER_1/LAST_NAME) }</LAST_NAME>
<CUSTOMER_ORDER>
{
for $PB_WL.CUSTOMER_ORDER_LINE_ITEM_2 in document("PB-WL")/db/CUSTOMER_ORDER_LINE_ITEM
let $CUSTOMER_ORDER_LINE_ITEM_3 :=
for $PB_WL.CUSTOMER_ORDER_4 in document("PB-WL")/db/CUSTOMER_ORDER
where ($PB_BB.CUSTOMER_1/CUSTOMER_ID eq $PB_WL.CUSTOMER_ORDER_4/CUSTOMER_ID)
and ($PB_WL.CUSTOMER_ORDER_4/ORDER_ID eq $PB_WL.CUSTOMER_ORDER_LINE_ITEM_2/ORDER_ID)
return
xf:true()
where xf:not(xf:empty($CUSTOMER_ORDER_LINE_ITEM_3))
return
<CUSTOMER_ORDER_LINE_ITEM>
<PRODUCT_NAME>{ xf:data($PB_WL.CUSTOMER_ORDER_LINE_ITEM_2/PRODUCT_NAME) }</PRODUCT_NAME>
<EXPECTED_SHIP_DATE>{ xf:data($PB_WL.CUSTOMER_ORDER_LINE_ITEM_2/EXPECTED_SHIP_DATE) }</EXPECTED_SHIP_DATE>
</CUSTOMER_ORDER_LINE_ITEM>
}
</CUSTOMER_ORDER>
</CUSTOMER>
}
</ROWS>
For performance reasons, we recommend that you project the intermediate data, especially if you do not care about duplicates or know there will not be duplicates. In the example above, you can project an element from the Wireless order table.
Instructions to create query using Data View Builder:
The query looks like:
<ROWS>
{
for $PB_BB.CUSTOMER_1 in document("PB-BB")/db/CUSTOMER
return
<CUSTOMER>
<FIRST_NAME>{ xf:data($PB_BB.CUSTOMER_1/FIRST_NAME) }</FIRST_NAME>
<LAST_NAME>{ xf:data($PB_BB.CUSTOMER_1/LAST_NAME) }</LAST_NAME>
{
for $PB_WL.CUSTOMER_ORDER_2 in document("PB-WL")/db/CUSTOMER_ORDER
where ($PB_BB.CUSTOMER_1/CUSTOMER_ID eq $PB_WL.CUSTOMER_ORDER_2/CUSTOMER_ID)
return
<CUSTOMER_ORDER>
<ORDER_ID>{ xf:data($PB_WL.CUSTOMER_ORDER_2/ORDER_ID) }</ORDER_ID>
{
for $PB_WL.CUSTOMER_ORDER_LINE_ITEM_3 in document("PB-WL")/db/CUSTOMER_ORDER_LINE_ITEM
where ($PB_WL.CUSTOMER_ORDER_2/ORDER_ID eq $PB_WL.CUSTOMER_ORDER_LINE_ITEM_3/ORDER_ID)
return
<CUSTOMER_ORDER_LINE_ITEM>
<PRODUCT_NAME>{ xf:data($PB_WL.CUSTOMER_ORDER_LINE_ITEM_3/PRODUCT_NAME) }</PRODUCT_NAME>
<EXPECTED_SHIP_DATE>{ xf:data($PB_WL.CUSTOMER_ORDER_LINE_ITEM_3/EXPECTED_SHIP_DATE) }</EXPECTED_SHIP_DATE>
</CUSTOMER_ORDER_LINE_ITEM>
}
</CUSTOMER_ORDER>
}
</CUSTOMER>
}
</ROWS>
Example 3: Find all Broadband customers (CUSTOMER is Repeatable and Optional)
The target schema is ROWS(CUSTOMER*). This query returns the root element and all Broadband customers. Since, CUSTOMER is optional, an empty <ROWS/> element could be returned as the result of the query since it would conform to the schema.
Instructions to create query using Data View Builder:
The following query will be generated. Notice that this query will indeed return an empty root element <ROWS/> if there are not any Broadband customers.
The query looks like:
<ROWS>
{
for $PB_BB.CUSTOMER_1 in document("PB-BB")/db/CUSTOMER
return
<CUSTOMER>
<FIRST_NAME>{ xf:data($PB_BB.CUSTOMER_1/FIRST_NAME) }</FIRST_NAME>
<LAST_NAME>{ xf:data($PB_BB.CUSTOMER_1/LAST_NAME) }</LAST_NAME>
</CUSTOMER>
}
</ROWS>
Example 4: Find all Broadband customers (CUSTOMER is Repeatable and Required)
This time the target schema is ROWS(CUSTOMER+). Again, this query returns the root element and all Broadband customers. But, since, CUSTOMER is required, in case there are no Broadband customer, an empty <ROWS/> element cannot be returned as the result of the query since such a result would not conform to the given target schema.
Instructions to create query using Data View Builder:
Below is the query generated under this schema. This query will return the root element and all Broadband customers that exist. Observe that if there are not any Broadband customers then an empty result will be returned (not even the root element).
let $CUSTOMER_1 :=
for $PB_BB.CUSTOMER_2 in document("PB-BB")/db/CUSTOMER
return
<CUSTOMER>
<FIRST_NAME>{ xf:data($PB_BB.CUSTOMER_2/FIRST_NAME) }</FIRST_NAME>
<LAST_NAME>{ xf:data($PB_BB.CUSTOMER_2/LAST_NAME) }</LAST_NAME>
</CUSTOMER>
where xf:not(xf:empty($CUSTOMER_1))
return
<ROWS>
{ $CUSTOMER_1 }
</ROWS>
The pattern of this query is discussed in more detail in Example 6: Find the list of all Broadband customers that have at least one Wireless order and return their Wireless orders (ORDER is Repeatable and Required).
Example 5: Find all Broadband customers and return their Wireless orders if the customer has Wireless orders (ORDER is Required and Optional)
In this case, the target schema is ROWS(CUSTOMER*(ORDER*)). The target schema allows for customers with zero orders. This means that the query can (and should) return customers without orders. Practically, this makes the query is a left outer-join between customers and orders.
Instructions to create query using Data View Builder:
The query looks like:
<ROWS>
{
for $PB_BB.CUSTOMER_1 in document("PB-BB")/db/CUSTOMER
return
<CUSTOMER>
<FIRST_NAME>{ xf:data($PB_BB.CUSTOMER_1/FIRST_NAME) }</FIRST_NAME>
<LAST_NAME>{ xf:data($PB_BB.CUSTOMER_1/LAST_NAME) }</LAST_NAME>
{
for $PB_WL.CUSTOMER_ORDER_2 in document("PB-WL")/db/CUSTOMER_ORDER
where ($PB_BB.CUSTOMER_1/CUSTOMER_ID eq $PB_WL.CUSTOMER_ORDER_2/CUSTOMER_ID)
return
<CUSTOMER_ORDER>
<ORDER_DATE>{ xf:data($PB_WL.CUSTOMER_ORDER_2/ORDER_DATE) }</ORDER_DATE>
<SHIP_METHOD>{ xf:data($PB_WL.CUSTOMER_ORDER_2/SHIP_METHOD) }</SHIP_METHOD>
</CUSTOMER_ORDER>
}
</CUSTOMER>
}
Example 6: Find the list of all Broadband customers that have at least one Wireless order and return their Wireless orders (ORDER is Repeatable and Required)
In this case, the target schema is ROWS(CUSTOMER*(ORDER+)). Now, the target schema does not allow for customers with zero orders. This means that the query should not return customers without orders. Practically, this makes the query is a (natural) join between customers and orders.
Instructions to create query using Data View Builder:
The query looks like:
<ROWS>
{
for $PB_BB.CUSTOMER_1 in document("PB-BB")/db/CUSTOMER
let $CUSTOMER_ORDER_2 :=
for $PB_WL.CUSTOMER_ORDER_3 in document("PB-WL")/db/CUSTOMER_ORDER
where ($PB_BB.CUSTOMER_1/CUSTOMER_ID eq $PB_WL.CUSTOMER_ORDER_3/CUSTOMER_ID)
return
<CUSTOMER_ORDER>
<ORDER_DATE>{ xf:data($PB_WL.CUSTOMER_ORDER_3/ORDER_DATE) }</ORDER_DATE>
<SHIP_METHOD>{ xf:data($PB_WL.CUSTOMER_ORDER_3/SHIP_METHOD) }</SHIP_METHOD>
</CUSTOMER_ORDER>
where xf:not(xf:empty($CUSTOMER_ORDER_2))
return
<CUSTOMER>
<FIRST_NAME>{ xf:data($PB_BB.CUSTOMER_1/FIRST_NAME) }</FIRST_NAME>
<LAST_NAME>{ xf:data($PB_BB.CUSTOMER_1/LAST_NAME) }</LAST_NAME>
{ $CUSTOMER_ORDER_2 }
</CUSTOMER>
}
</ROWS>
The general pattern for handling required repeatable elements in the target schema is as follows. The goal is to be able to check for existence of at least one element before we generate the parent. Generation of required repeatable elements is "promoted" the element to the nearest optional repeatable ancestor (or the root of the result if there is no such element). There the list of elements is computed inside a let clause. After that, and the result (list) of this let clause is checked whether it is empty or not, before producing the rest of the result.
In this case, the ORDER element is required so we need to check for existence of orders before we produce customer. This means that we need to generate the list of orders for each customer, and output the customer only if this list is not empty.
Source Replication
A source is said to be replicated if the source appears multiple times in a query. Specifically in XQuery, a source is replicated if document("source name") appears multiple times in the XQuery, usually appearing in two different for clauses. Similarly in SQL, a source is replicated if the source (table) appears twice in a FROM clause (or in two different FROM clauses). (See the next section for examples.)
Why is source replication necessary?
The simplest example of a necessary source replication would be a self-join in SQL. In the classic example of a self-join, the query wants to get all the pairs of employee names to manager names from a single employee table:
SELECT e.name, m.name
FROM employee e, employee m
WHERE e.manager_id = m.id
In XQuery, the query would look like:
<employee_managers>
{
for $e in document("employee")//employee
for $m in document("employee")//employee
where $e.manager_id eq $m.id
return
<employee_manager>
<employee> {$e.name} </employee>
<manager> {$m.name} </manager>
</employee_manager>
}
</employee_managers>
In both of these examples, given the sources, there is no way to write these queries without replicating the sources.
When is source replication necessary?
Source replication is necessary whenever you want to use a source for two different purposes that will require iterating over the source twice. Another way to state it is when two different tuples from a source will be required at the same time.
When should you manually replicate sources?
In ambiguous cases, both replicating and not replicating a source would lead to reasonable queries.
For example, at the beginning of this section, we presented a self-join to get employee-manager pairs. Without replicating the source, you might try the following:
Of course, the Data View Builder would interpret this query as: "give me all employees who are their own manager." This interpretation is no less valid than the desired one.
There is no way to go into Advanced mode to fix this query. You simply must replicate the source in this case.
Next Steps
|
|
|
|
||
 |
|
|
 |
 |
 |
|
||