Query template file

The query template file is a mechanism by which you 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 that are 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.

Query template file location and naming

A service module can have one or more query template files. Prefix query template files with 'wc-query' and use the extension .tpl.

Default query template files are in the following directories:
  • WebSphere Commerce Developerworkspace_dir\wc\xml\config\com.ibm.commerce.servicemodule
  • WebSphere Commerce EnterpriseWebSphere Commerce - ExpressWebSphere Commerce ProfessionalWC_eardir\xml\config\com.ibm.commerce.servicemodule

You can add new queries or extend provided queries, by placing your query template files under the extended configuration directory for your service module.

Custom query template files must be placed in the following directories:
  • WebSphere Commerce Developerworkspace_dir\wc\xml\config\com.ibm.commerce.servicemodule-ext
  • WebSphere Commerce EnterpriseWebSphere Commerce - ExpressWebSphere Commerce ProfessionalWC_eardir\xml\config\com.ibm.commerce.servicemodule-ext
For example, a catalog query template file is in this directory: xml\config\com.ibm.commerce.catalog-ext.
Note: Adding new columns to your custom tables requires changing physical SDOs that represent those tables, with the Data Service Layer wizard.

Query template file syntax and loading

Query template files are loaded in ascending alphanumeric order. When files that contain query definitions are loaded, the new queries override the previously loaded queries.

The blocks in the query template must be in this order:

  1. BEGIN_SYMBOL_DEFINITIONS
  2. END_SYMBOL_DEFINITIONS
  3. BEGIN_XPATH_TO_SQL_STATEMENT
  4. END_XPATH_TO_SQL_STATEMENT
  5. BEGIN_ ASSOCIATION_SQL_STATEMENT
  6. END_ ASSOCIATION_SQL_STATEMENT
  7. BEGIN_SQL_STATEMENT
  8. END_SQL_STATEMENT
  9. BEGIN_PROFILE
  10. END_PROFILE.
The BEGIN_PROFILE_ALIASES and END_PROFILE_ALIASES blocks can be defined anywhere within a query template file.
Note:
  • Every comment line or comment block must start with <!-- and end with -->. A comment line or block can be at any place in the file.
  • During development, you might want to modify and reload a query template file, without having to restart the server. You can use a .reloadconfig file. For more information about using a .reloadconfig file, see reloading the configuration of a BOD service module.

Query template file organization

A query template file has five main sections, of which the first two are mandatory:
SYMBOL_DEFINITIONS
The column symbol definition section defines column symbols that are used and referenced in the SELECT list of your SQL template statements. If your physical schema changes, you can adjust the symbols without rewriting SQL. Symbol definition is similar to defining constants in programming languages. You can define information in one location and use it in multiple places. This approach helps locate where to update if future changes are required. When you define a subset of the columns to select from a table, ensure that the query includes the primary and foreign key columns.
  • There must be only one BEGIN_SYMBOL_DEFINITIONS, END_SYMBOL_DEFINITIONS block per query template file.
  • All the symbols must be defined in the BEGIN_SYMBOL_DEFINITIONS block.
  • There can be comment line or symbol definition line. Symbol definition must be completed in one line.
  • A symbol definition line defines only one symbol definition.
  • The symbol name, between "COLS:" and '=', is the unique identifier of the symbols. The legal characters of the symbol names are a-z, A-Z and 0-9.
  • If the wildcard (*) is used in the column definition, the column names are retrieved from the Object-relational metadata and the column definitions have a file scope.
Example
BEGIN_SYMBOL_DEFINITIONS

	COLS:CATENTRY_ID=CATENTRY:CATENTRY_ID
	COLS:CATENTRY=CATENTRY:*	
	COLS:CATENTDESC=CATENTDESC:CATENTRY_ID,SHORTDESCRIPTION, OPTCOUNTER

END_SYMBOL_DEFINITIONS
Note: When you define a subset of the columns to select from a table, include the OPTCOUNTER column. See Optimistic locking for more details. Always add an OPTCOUNTER column to your custom tables. For example:
COLS:DMACTIVITY_NAME=DMACTIVITY:DMACTIVITY_ID, NAME, OPTCOUNTER 
XPATH_TO_SQL_STATEMENT
The XPATH_TO_SQL_STATEMENT links the logical and physical layers by mapping an XPath key directly to an SQL statement. The name of the XPATH_TO_SQL_STATEMENT is the key of the XPath expression. In single-step queries, this name is a combination of the XPath key and the access profile. For example, if the XPath key is /CatalogEntry[CatalogEntryIdentifier[ExternalIdentifier[(PartNumber=)]]] and the access profile is IBM_Admin_Details, the name of a single-step template is /CatalogEntry[CatalogEntryIdentifier[ExternalIdentifier[(PartNumber=)]]]+IBM_Admin_Details. You can use the wcs_xpathkey utility to get the XPath key for an XPath expression.
Note: The mapping from XPath key to SQL query can be overridden. If an XPath key is defined in more than one query template file, the one defined in the file last loaded overrides the others.
  • There can be more than one BEGIN_XPATH_TO_SQL_STATEMENT, END_XPATH_TO_SQL_STATEMENT block.
  • Each block defines only one SQL statement.
  • Each block must have a name and base_table defined.
  • Spaces are not allowed in the name.
  • The SQL statement might expand to multiple lines, and it must be defined last in the block.
  • The optional dbtype tag is specified when an SQL statement is specific to the database platform. This tag is useful when developing SQL statements for multiple database platforms. These statements can be included in a single template file. The valid values for the dbtype tag include 'db2', 'oracle', 'derby', and 'any'. Queries that do not have this tag specified apply for all database platforms. If you include a query for a specific database platform, also include a default one (dbtype value 'any') to use on other platforms.
  • The elements in the block must be in the same order as shown in the example.
    Example
    BEGIN_XPATH_TO_SQL_STATEMENT
    name=/CatalogEntry[CatalogEntryIdentifier[ExternalIdentifier[(PartNumber=)]]]
    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.PARTNUMBER IN (?PartNumber?) 
    	AND CATENTRY.MARKFORDELETE = 0
    
    END_XPATH_TO_SQL_STATEMENT 
    
  • The preceding snippet contains CTX tags that represent business contexts. The Data Service Layer allows a developer to specify a special tag, $CTX:KEY$, in the SQL template. The data service layer uses this tag to help extract context-sensitive information from the database. This tag is substituted at run time with the value of the context property, such as language ID or store ID, corresponding to the 'KEY'.

    For more information about these tags, see query template file tags.

  • The SQL statement can be written for workspace content management.

    For more information about writing for workspace content management, see techniques for improving the performance of SQL queries under workspaces in the Data Service Layer.

ASSOCIATION_SQL_STATEMENT
Associated SQL statements define a specific SQL query. These queries can then be reused to build different access profiles that are defined in the PROFILE section.
  • There can be more than one BEGIN_ ASSOCIATION_SQL_STATEMENT, END_ ASSOCIATION_SQL_STATEMENT block.
  • Each block might define at most one SQL statement.
  • The rules in the XPATH_TO_SQL_STATEMENT block are applied in this block.
  • The name is the unique identifier of the ASSOCIATION_SQL_STATEMENT.
Example
BEGIN_ASSOCIATION_SQL_STATEMENT
	name=IBM_CatalogEntryWithDescription
	base_table=CATENTRY
	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
SQL_STATEMENT
This section contains named SQL statements. The SQL statements are executed directly via the JDBC interface with the JDBCQueryService class. This class is similar to the session bean JDBC helper used by SOI service modules. This section might contain select statements that use aggregate functions, like sum() or avg(). As a result of the use of these functions, queries do not map to physical Java objects by object-relational metadata.

Under certain circumstances, SQL statements might need to be run to update data, delete data, or retrieve data independent of the data model. For example, a business operation might insert or delete records in data tables that are not defined in the logical model. The business operating might also update multiple data objects that are more efficient to issue a direct SQL rather than using the Data Service Layer to retrieve and update each object.

Example
BEGIN_SQL_STATEMENT
	name=IBM_Update_DeleteCatalogEntry
	base_table=CATENTRY
	sql= UPDATE CATENTRY   	
				SET CATENTRY.PARTNUMBER=
	     				CASE WHEN 
	     					LENGTH(CATENTRY.PARTNUMBER||'-'||$DB:CURRENT_TIMESTAMP$)<=64 
	     				THEN 
	     					CATENTRY.PARTNUMBER||'-'||$DB:CURRENT_TIMESTAMP$  
	     				ELSE 
	     					SUBSTR(CATENTRY.PARTNUMBER,1,64-LENGTH(''||$DB:CURRENT_TIMESTAMP$)-1)||'-'||$DB:CURRENT_TIMESTAMP$ END
	     				,CATENTRY.MARKFORDELETE=1
	     	WHERE
			CATENTRY.CATENTRY_ID=?catalogEntryId? 
			OR CATENTRY.CATENTRY_ID IN (SELECT CATENTREL.CATENTRY_ID_CHILD FROM CATENTREL WHERE CATENTREL.CATENTRY_ID_PARENT=?catalogEntryId?)
END_SQL_STATEMENT	

The JDBCQueryService class supports batch update, insert, and delete statements with the executeBatchUpdate batch update interface.

Note: The SQL_STATEMENT section appears in only the wc-query-utilities.tpl file. This appearance is a special convention for a query template file that contains these special named SQL statements.
Note: Never read or update the same data with the JDBCQueryService and the PhysicalDataContainer within the same transaction. If you do, there is a chance that you can read stale data or end up with corrupted data in the database.
The SQL statement can be written for workspace content management. For more information about writing for workspace content management, see techniques for improving the performance of SQL queries under workspaces in the Data Service Layer.
PROFILE
This section defines access profiles that use associated SQL statements. If needed, more than one associated SQL statement can be used by a profile. Each associated SQL statement performs in turn and the results of the different associated SQL statements are merged together with a GraphComposer class.

Queries that are associated with an access profile must always be defined in the same file where the access profile is defined. The exception for this definition location is when you extend an access profile. The extension mechanism provides you the capability to reuse the default associated SQL statements without having to redefine them in your custom query template file.

  • There can be more than one BEGIN_PROFILE, END_PROFILE block.
  • All the profile blocks have to be at the end of the file.
  • Each block has one profile name that is defined, and the profile name is the unique identifier of the profile.
  • Each profile block might have only one BEGIN_ENTITY, END-ENTITY block, and in each entity block
    • base_table must be defined.
    • There can be one or more associated_sql_statement defined. This associated_sql_statement must match the one of the ASSOCIATION_SQL_STATEMENT names defined. In addition, the base_table name that is defined in the entity block must match the base_table name in the corresponding queries in the ASSOCIATION_SQL_STATEMENT block and XPATH_TO_SQL_STATEMENT block that is used in the access profile.
    • An optional Graph composer can be specified in the entity block. If the className is specified, it must be the full path of the class, and the class must subclass com.ibm.is.component.dsl.GraphComposer.
Example
BEGIN_PROFILE 
 name=IBM_Admin_Summary
 BEGIN_ENTITY
   base_table=CATENTRY
   associated_sql_statement=IBM_CatalogEntryWithDescription
 END_ENTITY
END_PROFILE
PROFILE_ALIASES
Use the PROFILE_ALIASES section to define any aliases for profiles. The aliases have a global scope and are used for supporting deprecated access profiles that are renamed. You can specify aliases for different access profiles in a single BEGIN_PROFILE_ALIASES - END_PROFILE_ALIASES block when the profiles apply to the same base table, for instance, the same noun.
For example, the following code defines the IBM_CatalogAttachmentReference and IBM_CatAttachment as aliases of the IBM_Admin_CatalogAttachmentReference profile.
BEGIN_PROFILE_ALIASES
  base_table=CATALOG
  IBM_CatalogAttachmentReference=IBM_Admin_CatalogAttachmentReference
  IBM_CatAttachment=IBM_Admin_CatalogAttachmentReference
END_PROFILE_ALIASES

Support for column aliases

To avoid ambiguity in selecting columns with identical name from different tables, column aliases are used. The column alias prefix can be specified before a column symbol reference. The general syntax of the column symbol reference is:

[<table_alias>.][<columns_alias_prefix>]$COLS:<column_symbol_name>$
For example:

WITH TEMP_TABLE AS (
SELECT 
      CATENTRY.CE_$COLS:CATENTRY_ID$, ATTRVALUE.ATTR_$COLS:ATTRVALUE$, ATTRVALUE2.ATTR2_$COLS:ATTRVALUE$
FROM CATENTRY, ATTRVALUE 
JOIN ATTRIBUTE 
      ON ATTRIBUTE.LANGUAGE_ID = ATTRVALUE.LANGUAGE_ID 
      AND ATTRIBUTE.ATTRIBUTE_ID = ATTRVALUE.ATTRIBUTE_ID 
LEFT OUTER JOIN ATTRVALUE ATTRVALUE2 
      ON ATTRIBUTE.ATTRIBUTE_ID = ATTRVALUE2.ATTRIBUTE_ID 
      AND ATTRVALUE2.CATENTRY_ID = 0 
      AND ATTRIBUTE.LANGUAGE_ID = ATTRVALUE2.LANGUAGE_ID 
WHERE CATENTRY.CATENTRY_ID IN ($ENTITY_PKS$) 
      AND ATTRVALUE.CATENTRY_ID = CATENTRY.CATENTRY_ID 
) SELECT * FROM TEMP_TABLE
Assume that the following symbol definitions exist:

COLS:CATENTRY_ID=CATENTRY:CATENTRY_ID
COLS:ATTRVALUE=ATTRVALUE:ATTRVALUE_ID,LANGUAGE_ID
The resulting query is translated as:

WITH TEMP_TABLE AS (
SELECT 
      CATENTRY.CATENTRY_ID CE_CATENTRY_ID, 
      ATTRVALUE.ATTRVALUE_ID ATTR_ATTRVALUE_ID, ATTRVALUE.LANGUAGE_ID ATTR_LANGUAGE_ID,                         
 ATTRVALUE2.ATTRVALUE_ID ATTR2_ATTRVALUE_ID, ATTRVALUE2.LANGUAGE_ID ATTR2_ATTRVALUE_ID
FROM CATENTRY, ATTRVALUE 
JOIN ATTRIBUTE 
      ON ATTRIBUTE.LANGUAGE_ID = ATTRVALUE.LANGUAGE_ID 
      AND ATTRIBUTE.ATTRIBUTE_ID = ATTRVALUE.ATTRIBUTE_ID 
LEFT OUTER JOIN ATTRVALUE ATTRVALUE2 
      ON ATTRIBUTE.ATTRIBUTE_ID = ATTRVALUE2.ATTRIBUTE_ID 
      AND ATTRVALUE2.CATENTRY_ID = 0 
      AND ATTRIBUTE.LANGUAGE_ID = ATTRVALUE2.LANGUAGE_ID 
WHERE CATENTRY.CATENTRY_ID IN ($ENTITY_PKS$) 
      AND ATTRVALUE.CATENTRY_ID = CATENTRY.CATENTRY_ID 
) SELECT * FROM TEMP_TABLE