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 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:
- 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 us 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.
- Query template file
The query template file is a mechanism by which we can easily map a query on your logical model to one or more SQL statements. The SQL assets are kept in separate files, which are isolated from the runtime Java code. This template helps database administrators and programmers to locate and analyze SQL statements. Additionally, changes to SQL used for queries do not require recompiling Java code. Also, the addition of new columns into existing tables might not require changing SQL statements that use those tables. Column information is separated out in the SYMBOL_DEFINITIONS section.- Access profiles
It is very common, especially when building Web applications, to require a different level of detail on various web pages for the same object. For example, on a product list page, only minimal information about each product is needed, while on a product detail page, more information is required. To accommodate the varying needs for different levels of knowledge about an object, clients can use access profiles to convey the intent of their requests.- Parametric search support
A parametric search allows queries using multiple criteria to narrow the results. For instance, all products that have a name starting with "Toy", under $50, that have color = "blue". WebSphere Commerce supports parametric search queries on both vertical and horizontal schemas.