Data service layer query processing

The data service layer uses the different sections of the query template file to control how the query is run.

The sections for XPath to SQL statements and association SQL statements each define a list of template SQL statements. Each SQL template has a unique name and a base table name. The base table refers to the main table in the query, which is typically the table representing your Noun. Each XPath to SQL template, when used in combination with the association SQL statements in a two-step query, returns a list of primary keys satisfying the search criteria (for example, select a list of product IDs given the category name).

The PROFILE section defines Access profiles that make use of association SQL statements. Within a profile, one or more association SQL statements are referenced for each base object defined. Each association SQL statement defines the data to be returned given the list of primary keys (from the XPath to SQL statement). For example, one association SQL statement under a profile can retrieve all the properties and descriptions for the product given a list of product IDs, while another association SQL statement under a different profile retrieves all cross-sell and up-sell information and the basic properties and descriptions.

The data service layer supports two types of queries: single-step and two-step.


Single-step queries

For single-step queries, the access profile name along with the XPath key is used to select a single XPath to SQL query to retrieve all the requested information. Multiple queries can retrieve different levels of detail for the same XPath expression and different access profiles. The following example defines a single-step query for the XPath key /Catalog[CatalogIdentifier[(UniqueID=))] and access profile IBM_Admin_Details.

In the preceding example, the XPath expression queries the catalogs with specified unique identifiers and the IBM_Admin_Details access profile selects all columns from the CATALOG, CATALOGDSC, and STORECAT tables for these catalogs.


Two-step queries

Two-step queries use the XPath to SQL statements and the association SQL statements. The XPath to SQL statements fetch the primary keys of the base objects that are of interest for search criteria specified by the XPath query. The association SQL statements, scoped by profile name, retrieve the information about those objects. The associated SQL needs have a foreign key relationship to the XPathSQL base table. And the association SQL statement needs return the foreign key column. See: Query template file tags. The following example defines a two-step query for the XPath key /CatalogEntry[CatalogEntryIdentifier[(UniqueID=)]] and access profile IBM_Admin_CatalogEntryDescription:

Note: The individual SQL statements cannot be called independently of each other. They must be used in combination with each other.

In the preceding example, the XPath to SQL statement is used to fetch the primary keys from the base table, CATENTRY. The base table for the XPath to SQL statement must be the same as the base table for the association SQL statement. The PROFILE section lists the associated SQL statements to run for the IBM_Admin_CatalogEntryDescription access profile. This example lists a single associated SQL statement, IBM_RootCatalogEntryWithDescription, which is run with the primary keys substituted for the $ENTITY_PKS$ tag to retrieve all the data requested by the original query.

For two-step queries, each access profile can list many associated SQL statements that each select different data. For example, one association SQL statement might retrieve all the properties and descriptions for the product given a list of product IDs, while another association SQL statement under a different profile can retrieve all the cross-sell and up-sell in addition to the basic properties and descriptions.

Note: To avoid potential problems when moving to the next version of WebSphere Commerce, create our own custom access profiles and never directly modify the default WebSphere Commerce access profiles. We can add new queries or extend the queries shipped with the base product, by placing your query template files under the extended configuration directory for our service module (for example, for catalog, xml\config\com.ibm.commerce.catalog-ext).

There is a default level of sorting when you have created a query returning the primary keys in a predefined order in the query template. The order of the data objects representing the base table in the data graph is consistent with the ordering of the primary keys returned by the XPath to SQL query for two-step queries. The list of objects in the PhysicalDataContainer should appear in the same order as the order of the primary keys returned by the XPath to SQL query.


Guidelines on query use

Use single-step queries whenever possible. However, in some cases, it is not possible to fetch all the data in a single query. Alternately, your query may join a very large number of tables and may not meet your performance requirements. Use a two-step query under the following conditions: