Query template file

Default query template files are in the following directories:

• workspace_dir\wc\xml\config\com.ibm.commerce.servicemodule
• WC_eardir\xml\config\com.ibm.commerce.servicemodule

Custom query template files must be placed in the following directories:

• workspace_dir\wc\xml\config\com.ibm.commerce.servicemodule-ext
• WC_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.

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.

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.

  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.

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.

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.

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

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