Query template file tags

The query template file uses the following tags.

$CTX:KEY$ - Business context tags
$ATTR_TBLS$ and $ATTR_CNDS$ - Parametric search tags
$ENTITY_PKS$
$CONTROL:LANGUAGES$
$STOREPATH:STRELTYPE$
$CM:BASE$, $CM:WRITE$, $CM:READ$

$CTX:`KEY`$ - Business context tags

Business context provides service modules with information common across multiple transactions. Transactions can be governed by multiple contexts. For example, the entitlement context can specify which users can view certain products or are entitled to special prices. The globalization context influences the language and currency of displayed information based on the locale of the user.

The data service layer helps to extract context-sensitive information from the database by allowing the developer to specify a special tag, $CTX:KEY$, in the SQL template. This tag is substituted at runtime with the value of the context property, such as language ID or store ID, corresponding to the 'KEY'. The name of the context property should correspond with the corresponding getter in the context. For example, to get the value of BaseContext.getRunAsId(), the property name should be runAsId.

Each service module defines context keys, and the mapping to the context property, in its wc-component.xml file in order to use the CTX:KEY tag in the query templates.

Context information is also used when generating SQL fragments to query tables that store attributes if these tables contain context-sensitive information. Additional predicates are appended to the 'where' clause. These contain the columns referring to the context information that are defined by the attribute mappings configuration in the component configuration file.

The value of the context property can be of either numeric type or string type. The $CTX:KEY$ tag must be enclosed in single quotation marks in the SQL template for string-based context properties.

If the context property is multi-valued, the context condition in the SQL template uses the 'IN' predicate instead of an '=' operator. In this case, the $CTX:KEY$ tag is replaced with the comma separated values listed between the parenthesis. For generated SQL fragments, the 'IN' predicate is generated.

The following SQL statement template queries all category groups and returns their descriptions in the language defined in the context at run-time. The context tag with the key LANG_ID is replaced with the value of the language ID.

SQL template:

SELECT 
CATENTRY.$COLS:CATENTRY$,
CATENTDESC.$COLS:CATENTDESC$
FROM
CATENTRY
LEFT OUTER JOIN CATENTDESC ON 
(CATENTDESC.CATENTRY_ID = CATENTRY.CATENTRY_ID AND 
CATENTDESC.LANGUAGE_ID = ($CTX:LANG_ID$))

SQL statement after substitution:

SELECT 
CATENTRY.CATENTRY_ID,
CATENTRY.PARTNUMBER,
CATENTDESC.CATENTRY_ID,
CATENTDESC.SHORTDESCRIPTION
FROM
CATENTRY
LEFT OUTER JOIN CATENTDESC ON 
(CATENTDESC.CATENTRY_ID = CATENTRY.CATENTRY_ID AND 
CATENTDESC.LANGUAGE_ID = (-1))

Below is a list of mappings between the keys and the context properties for the Catalog service module (taken from the wc-component.xml):

<_config:context key="LANG_ID" name="com.ibm.commerce.context.globalization.GlobalizationContext" propertyName="languageId" defaultValue="-1"/>
<_config:context key="CATALOG_ID" name="com.ibm.commerce.catalog.businesscontext.CatalogContext" propertyName="catalogID" defaultValue="-1"/>
<_config:context key="OWNER_ID" name="com.ibm.commerce.catalog.businesscontext.CatalogContext" propertyName="ownerID" defaultValue="-1"/>
<_config:context key="STORE_ID" name="com.ibm.commerce.context.base.BaseContext" propertyName="storeId" defaultValue="-1"/>

Below is a list of mappings between the keys and the context properties for the Marketing service module (taken from the wc-component.xml):

<_config:context key="STORE_ID" name="com.ibm.commerce.context.base.BaseContext" propertyName="storeId" defaultValue="0"/>
<_config:context key="LANG_ID" name="com.ibm.commerce.context.globalization.GlobalizationContext" propertyName="languageId" defaultValue="-1"/>

Note:

The marketing service module cannot use the CATALOG_ID and OWNER_ID tags in its queries. The LANG_ID and STORE_ID tags are shared between the two components.
Each property must have a default value. This value is used in the query if the context property is not set.

$ATTR_TBLS$ and $ATTR_CNDS$ - Parametric search tags

The $ATTR_TBLS$ and $ATTR_CNDS$ tags are used for parametric search. They mark where a list of attribute tables ( $ATTR_TBLS$ ) and attribute search conditions ( $ATTR_CNDS$ ) are to be inserted into an SQL template.

The list of attribute tables consists of the tables where the input search attributes are defined. The attribute search conditions include the join conditions between the attribute tables and the base table as well as the predicates where the values of the attribute columns are matched with the input search attribute values.

Note: The $ATTR_TBLS$ tag should be specified after the last tables in the FROM clause. The $ATTR_CNDS$ tag should be specified after the last search condition of the WHERE clause.

In the following example, a parametric search XPath expression queries products that have short description containing the '˜Polo shirt' string and part numbers prefixed with 'FU01'.

/CatalogEntry[search(contains(Description/ShortDescription, "Polo shirt") and starts-with(CatalogEntryIdentifier/ExternalIdentifier/PartNumber, "FU01"))]

The XPath to SQL template with a search() function looks as follows before search code generation:

BEGIN_XPATH_TO_SQL_STATEMENT
name=/CatalogEntry[search()]
base_table=CATENTRY
sql=
SELECT CATENTRY.$COLS:CATENTRY_ID$
FROM
CATENTRY, $ATTR_TBLS$
WHERE 
CATENTRY.MARKFORDELETE = 0 AND 
( $ATTR_CNDS$ ) 
ORDER BY
CATENTRY.CATENTRY_ID 
END_XPATH_TO_SQL_STATEMENT

In the following generated SQL statement, the $ATTR_TBLS$ variable is replaced with the associated table that contains the column corresponding to the ‘Description/ShortDescription' property. The $ATTR_CNDS$ variable is replaced with the join condition between this table and the base table and the condition to compare the ‘SHORTDESCRIPTION' column with the short description value provided as input. The condition for the ‘CatalogEntryIdentifier/ExternalIdentifier' property corresponding to the PARTNUMBER column in the base CATENTRY table is also included.

SELECT 
	CATENTRY.CATENTRY_ID
FROM
	CATENTRY, CATENTDESC IBM1
WHERE 
	CATENTRY.MARKFORDELETE = 0 AND CATENTRY.PARTNUMBER LIKE 'FU01%'
	AND (CATENTRY.CATENTRY_ID= IBM1.CATENTRY_ID AND 
	IBM1.SHORTDESCRIPTION LIKE '%Polo shirt%')
ORDER BY
	CATENTRY.CATENTRY_ID

The $ATTR_CNDS$ tag is replaced with a join condition and search conditions. The name of the base table (or its correlation name) concatenated with the '.' separator can prefix the $ATTR_CNDS$ tag. This prefix defines the correlation name to use when referencing the base table primary key column in the join condition. The second condition corresponds to the search condition specified in the input XPath request.

$ENTITY_PKS$

The $ENTITY_PKS$ tag is used with two step queries, in the associated SQL statements. It marks where to insert the primary key values returned by the XPath to SQL statement.

The association SQL statement and XPath to SQL statements work together to obtain the data from the base table and tables related to it. The XPath SQL selects primary keys from the base table. The associated SQL selects data from the tables related to the base table through foreign key relationships.

The default behavior is to fetch primary key values and inject them into the association SQL statement replacing the $ENTITY_PKS$ tag. However, you can configure the query generation to form a single query by injecting the XPath to SQL statement into the association SQL statement as a subselect.

This configuration is controlled by the 'usePrimaryKeyValues' flag which is specified in the entity section of the access profile definition. If the flag is set to 'true', the primary key values retrieved by the XPath to SQL statement are injected into the association SQL statements replacing the $ENTITY_PKS$ tag. This is done for all association SQL statements referenced by this profile. This is the default behavior. If the flag is set to 'false', the primary key subselect is injected to the associated SQL statements referenced by the access profile.

Note: If paging is used, the value of the 'usePrimaryKeyValues' is always assumed to be 'true'.

The XPath to SQL template that fetches the primary keys of all CATENTRY records given their parent catalog group id is defined as shown in the following sample:

SELECT CATENTRY.$COLS:CATENTRY_ID$
FROM CATENTRY INNER JOIN CECGREL ON CATENTRY.CATENTRY_ID = CECGREL.CATENTRY_ID
WHERE CATENTRY.MARKFORDELETE = 0 
	AND CECGREL.CATGROUP_ID = ?catGroupId? 
	AND CECGREL.USAGECODE = ?relationshipType?

The associated SQL fetches descriptions of the given CATENTRY records:

SELECT CATENTRY.$COLS:CATENTRY$, CEDESC.$COLS:CEDESC$
FROM CATENTRY, CEDESC
WHERE CATENTRY.CATENTRY_ID = CEDESC.CATENTRY_ID 
	AND CATENTRY.MARKFORDELETE = 0
	AND CEDESC.LANGUAGE_ID = $CTX:LANG_ID$ 
	AND CATENTRY.CATENTRY_ID IN ( $ENTITY_PKS$ )

After injecting the primary key subselect (XPath to SQL statement) into the associated SQL, the resulting query is as follows:

SELECT CATENTRY.$COLS:CATENTRY$, CEDESC.$COLS:CEDESC$
FROM CATENTRY, CEDESC
WHERE CATENTRY.CATENTRY_ID = CEDESC.CATENTRY_ID 
	AND CATENTRY.MARKFORDELETE = 0 
	AND CEDESC.LANGUAGE_ID = $CTX:LANG_ID$
	AND CATENTRY.CATENTRY_ID IN 
		(SELECT CATENTRY.CATENTRY_ID 
			FROM CATENTRY INNER JOIN CECGREL ON CATENTRY.CATENTRY_ID = CECGREL.CATENTRY_ID
			WHERE CATENTRY.MARKFORDELETE = 0 
				AND CECGREL.CATGROUP_ID = ?catGroupId?
				AND CECGREL.USAGECODE = ?relationshipType?)

If the flag is set to use the subselect, the primary key subselect is injected to the associated SQL. Otherwise, the $ENTITY_PKS$ is replace with the primary key values. The name of the boolean flag is 'usePrimaryKeyValues'.

If set to true, primary key values retrieved by the XPath to SQL statement will be injected into the association sql statements replacing the $ENTITY_PKS$ tag. This is done for all association SQL statements referenced by this profile. This is the default.

If set to false, the XPath to SQL statement is inserted as a subselect into the association SQL statements replacing the $ENTITY_PKS$ tag.

For example:

BEGIN_PROFILE
name=IBM_DefaultProfile

BEGIN_ENTITY 
base_table=CATENTRY 
usePrimaryKeyValues = false
associated_sql_statement=IBM_CatEntrySummary 
END_ENTITY

END_PROFILE

$CONTROL:LANGUAGES$

This tag is used to fetch language-sensitive information in a number of languages. The list of languages will come form the _wcs.dataLanguageIds control parameter on the request. The value of the parameter is a comma-separated list of language identifiers which are injected into the query template to replace the $CONTROL:LANGUAGES$ tag. If _wcs.dataLanguageIds is not passed in on the request, the language ID value is obtained form the globalization context.

For example, in an XPath expression you could use it as shown in the following sample:

{_wcf.ap=IBM_CEDescriptionProfile_LANGIDS;_wcf.dataLanguageIds='-1,-2'}/CatEntry[@catEntryId<200]

You can also use the languages tag as shown in the following sample ASSOCIATED_SQL_STATEMENT block:

BEGIN_ASSOCIATION_SQL_STATEMENT
       name= IBM_CEDescriptionProfile_LANGIDS
       base_table=CATENTRY
       sql=
              SELECT 
                            CATENTRY.$COLS:CATENTRY$,
                            CEDESC.$COLS:CEDESC$ 
              FROM
                            CATENTRY, CEDESC
              WHERE                            
                            CATENTRY.CATENTRY_ID = CEDESC.CATENTRY_ID AND
                            CEDESC.LANGUAGE_ID in ($CONTROL:LANGUAGES$)        AND
                            CATENTRY.CATENTRY_ID in ( $ENTITY_PKS$ )                            

END_ASSOCIATION_SQL_STATEMENT

$STOREPATH:STRELTYPE$

WebSphere Commerce supports several types of relationships between stores in a site. For example, a store may use the catalog provided by another store.

The data service layer helps to extract store relationship information from the database by allowing you to specify a special tag, $STOREPATH:STRELTYPE$ , in the SQL template. This tag is substituted at runtime with the value of the store relationship type, such as catalog, campaigns, corresponding to the STRELTYPE. A mapping between the STRELTYPE and the name of the store relationship type can be optionally defined in the component configuration file. If this mapping is not defined, the default value of the store relationship string will be prefixed with the "com.ibm.commerce." string.

For example, if the STRELTYPE is catalog, the default value for the relationship type will be "com.ibm.commerce.catalog'. This value matches the value of the name column in the streltyp table of the store relationship of catalog.

Therefore, if you add a new type of store relationship, you have to define the STRELTYPE in the wc-component.xml file to ensure the correct mapping between the STRELTYPE and the name of the store relationship type:


<_config:store-relationship key="xxxxx" value="com.mycompany.commerce.xxxxx"/>

$CM:BASE$, $CM:WRITE$, $CM:READ$

$CM:BASE$, $CM:WRITE$, and $CM:READ$ substitution variables are used by the template query to represent the names of the BASE, WRITE, and READ schemas. These variables will be substituted with the actual schema names at the query execution time.

In the workspaces environment, you might need to define queries that explicitly reference the BASE, WRITE, and READ schemas. See Workspaces data model and Techniques for improving the performance of SQL queries under workspaces in the Data Service Layer for more information.

$SCHEMA$

The $SCHEMA$ tag can be used in direct SQL queries executed by the JDBCServiceQuery class. It allows a schema name to be passed in so that it is dynamically controlled by the caller executing the query. To specify the substitution value of this tag, the value must be passed in with a key named "$SCHEMA$", resembling any other query parameter specified. Once specified, this variable is substituted at the query execution time.

Note: This tag is only supported by direct SQL queries. It does not work in UPDATE, INSERT and DELETE statements as business logic should only update/insert/delete records in the current schema.

For example, the following sample query uses the $SCHEMA$ tag:


BEGIN_SQL_STATEMENT
	<!-- Fetch all store IDs for which their search term associations is modified in the given task groups -->
	name=IBM_Select_Store_Modified_STA_In_TaskGroup
	base_table=SRCHTERMASSOC
	sql=
		SELECT 
				DISTINCT(STOREENT_ID)
		FROM
				$SCHEMA$.SRCHTERMASSOC
		WHERE
				CONTENT_TASKGRP in (?taskgrp_id?)
END_SQL_STATEMENT