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.

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 directory workspace_dir\WC\xml\config\com.ibm.commerce.servicemodule

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

Custom query template files must be placed in the following directory: workspace_dir\WC\wc\xml\config\com.ibm.commerce.servicemodule-extFor example, a catalog query template file is in this directory: xml\config\com.ibm.commerce.catalog-ext.

Note: Adding new columns to our 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, we can modify and reload a query template file, without having to restart the server. We 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, we can adjust the symbols without rewriting SQL. Symbol definition is similar to defining constants in programming languages. We 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 our 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. We 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

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 containing 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 we 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 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 our 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 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. We 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

• SQL parameters
  Within query templates, we can use SQL parameters to substitute values in SQL queries. The extended XPath query expression specifies the values of the SQL parameters in two ways, using XPath attributes or elements, and using control parameters.

• Query template file tags
  The query template file uses the following tags.

• Graph composer
  In the query template file, an optional graph composer Java class can be specified in the ENTITY section within the PROFILE block. This Java class allows us to augment the final graph of physical SDOs. It can perform data aggregation from other sources and can merge multiple graphs of physical SDOs into a single graph if there is more than one associated SQL statement defined in the profile for the base table object.