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.

BEGIN_SYMBOL_DEFINITIONS
	COLS:CATALOG=CATALOG:*
	COLS:CATALOGDSC=CATALOGDSC:*	
COLS:STORECAT=STORECAT:*

END_SYMBOL_DEFINITIONS
			
BEGIN_XPATH_TO_SQL_STATEMENT
	name=/Catalog[CatalogIdentifier[(UniqueID=)]]+IBM_Admin_Details
	base_table=CATALOG
	sql=
		SELECT 
			CATALOG.$COLS:CATALOG$,
			CATALOGDSC.$COLS:CATALOGDSC$,
			STORECAT.$COLS:STORECAT$
		FROM
			CATALOG
				JOIN STORECAT ON STORECAT.CATALOG_ID=CATALOG.CATALOG_ID 
					AND STORECAT.STOREENT_ID IN ($STOREPATH:catalog$)
				LEFT OUTER JOIN CATALOGDSC ON CATALOGDSC.CATALOG_ID = 	CATALOG.CATALOG_ID 
					AND CATALOGDSC.LANGUAGE_ID IN ($CONTROL:LANGUAGES$)
     WHERE
        CATALOG.CATALOG_ID IN (?UniqueID?)

END_XPATH_TO_SQL_STATEMENT

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.

BEGIN_SYMBOL_DEFINITIONS
	COLS:CATENTRY=CATENTRY:*
	COLS:CATENTRY_ID=CATENTRY:CATENTRY_ID
	COLS:CATENTDESC=CATENTDESC:*
END_SYMBOL_DEFINITIONS

BEGIN_XPATH_TO_SQL_STATEMENT
	name=/CatalogEntry[CatalogEntryIdentifier[(UniqueID=)]]
	base_table=CATENTRY
	sql=
		SELECT 
			CATENTRY.$COLS:CATENTRY_ID$
		FROM
			CATENTRY
			  JOIN STORECENT ON (CATENTRY.CATENTRY_ID = STORECENT.CATENTRY_ID 
				AND STORECENT.STOREENT_ID = $CTX:STORE_ID$)
		WHERE
         CATENTRY.CATENTRY_ID IN (?UniqueID?) AND
         CATENTRY.MARKFORDELETE = 0
END_XPATH_TO_SQL_STATEMENT

BEGIN_ASSOCIATION_SQL_STATEMENT
	name=IBM_RootCatalogEntryWithDescription
	base_table=CATENTRY
	additional_entity_objects=true
	sql=
		SELECT 
			CATENTRY.$COLS:CATENTRY$,
			CATENTDESC.$COLS:CATENTDESC$
		FROM
			CATENTRY
			    LEFT OUTER JOIN CATENTDESC ON CATENTDESC.CATENTRY_ID = CATENTRY.CATENTRY_ID 
					AND CATENTDESC.LANGUAGE_ID IN ($CONTROL:LANGUAGES$)
		WHERE
       CATENTRY.CATENTRY_ID IN ($ENTITY_PKS$)
END_ASSOCIATION_SQL_STATEMENT

BEGIN_PROFILE 
	name=IBM_Admin_CatalogEntryDescription
	BEGIN_ENTITY 
		base_table=CATENTRY 	  
		associated_sql_statement=IBM_RootCatalogEntryWithDescription
	END_ENTITY
END_PROFILE

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 your own custom access profiles and never directly modify the default WebSphere Commerce access profiles. You 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 your 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:

Queries that need to page data. Paging on the result of a single-step query is not possible if it returns multiple records for each base table record. A two-step query allows you to page on the result set returned by the first statement (the primary keys) rather than on the result set of the second statement.
Parametric search queries.
Complex queries that perform poorly because of joining too many tables. Breaking these queries into an XPath SQL statement and multiple associated SQL statements may improve performance.