Adding query templates to include the new information

The Catalog Service Module needs to be configured to update warranty and care instruction user data. In this lesson, you add query template files that help retrieve our custom warranty information. Query template files store the SQL and access profile definitions for a service module, isolating it from the business logic layer completely.

About this task

A query template relates an XPath key and an access profile of a logical object to a template SQL query to select the data. Custom query templates can reuse existing XPath keys but must always define a new access profile because a different view of the data is returned. You can read more about query template files and how they are used in the Query template file topic.

The query template file consists of the following components:

A symbol definition section that defines the tables our query template uses (CATENTRY, CATENTDESC, XWARRANTY, XCAREINSTRUCTION.
An XPath to SQL statement that maps the XPath key and access profile to a specific template SQL query.
A new access profile, MyCompany_All that is used along with the XPath key to identify the SQL template query.

The default queries to fetch the data before updating the CatalogEntry noun, and CatalogEntryDescription noun part must be changed to include the XWARRANTY and XCAREINSTRUCTION tables.

The default SELECT queries for updating the CatalogEntry nouns and its parts is located inside the following file:
- WC_eardir\xml\config\com.ibm.commerce.catalog\wc-query-CatalogEntry-update.tpl
- WC_eardir\xml\config\com.ibm.commerce.catalog-fep\wc-query-CatalogEntry-update.tpl
The default SELECT query used to update the CatalogEntry noun is identified by the IBM_CatalogEntryUpdate access profile. The default SELECT query to update the CatalogEntryDescription noun part is identified by the IBM_CatalogEntryDescription_Update access profile.

To configure the Catalog service module to update the new user data, these two queries are copied and pasted into extension update query template files. The queries are modified to also select the new tables, and a new access profile to uniquely identify each query is added.

Procedure

Create a custom Get query template file:

In Enterprise Explorer, expand WC > xml > config and right click the com.ibm.commerce.catalog-ext folder. (If the com.ibm.commerce.catalog-ext folder is not visible, select the WC\config folder and select File > Refresh.)
Select New > File.
Name the file: wc-query-MyCompanyCatalogEntry-get.tpl
The name is important – it must begin with wc-query- and end with the suffix .tpl
Click Finish.

Copy and paste the following query template into the file.

BEGIN_SYMBOL_DEFINITIONS
<!-- WebSphere Commerce tables -->
COLS:CATENTRY=CATENTRY:*
COLS:CATENTDESC=CATENTDESC:*

<!-- MyCompany extension tables -->
COLS:XWARRANTY=XWARRANTY:*
COLS:XCAREINSTRUCTION=XCAREINSTRUCTION:*

END_SYMBOL_DEFINITIONS

BEGIN_XPATH_TO_SQL_STATEMENT
  name=/CatalogEntry[CatalogEntryIdentifier[(UniqueID=)]]+MyCompany_All
  base_table=CATENTRY
    sql=
      SELECT
        CATENTRY.$COLS:CATENTRY$,
        CATENTDESC.$COLS:CATENTDESC$,
        XWARRANTY.$COLS:XWARRANTY$,
        XCAREINSTRUCTION.$COLS:XCAREINSTRUCTION$
      FROM
        CATENTRY 
           LEFT OUTER JOIN XWARRANTY ON (CATENTRY.CATENTRY_ID = XWARRANTY.CATENTRY_ID)
           LEFT OUTER JOIN CATENTDESC ON (CATENTDESC.CATENTRY_ID = CATENTRY.CATENTRY_ID AND CATENTDESC.LANGUAGE_ID in ($CONTROL:LANGUAGES$))
           LEFT OUTER JOIN XCAREINSTRUCTION ON (CATENTRY.CATENTRY_ID = XCAREINSTRUCTION.CATENTRY_ID AND XCAREINSTRUCTION.LANGUAGE_ID = CATENTDESC.LANGUAGE_ID)
      WHERE
        CATENTRY.CATENTRY_ID IN (?UniqueID?) AND
        CATENTRY.MARKFORDELETE = 0
END_XPATH_TO_SQL_STATEMENT

The two subsections of this example query template are:

SYMBOL_DEFINITIONS: In this subsection you define symbols for the columns used in your SQL. If your physical schema changes, you can adjust the symbols without rewriting all of your SQL.
XPATH_TO_SQL_STATEMENT: Maps an XPath expression directly to an SQL statement. In the example above, an XPath statement was defined, named, and mapped to a base table and an SQL statement.

Save the file.

Create a custom update query file:

Right-click the com.ibm.commerce.catalog-ext folder.
Select New > File.
Name the file: wc-query-MyCompanyCatalogEntry-update.tpl.
Click Finish.

Copy and paste the following query template into the file.


BEGIN_SYMBOL_DEFINITIONS
<!-- WebSphere Commerce tables -->
COLS:CATENTRY=CATENTRY:*
COLS:CATENTDESC=CATENTDESC:*

<!-- MyCompany extension tables -->
COLS:XWARRANTY=XWARRANTY:*
COLS:XCAREINSTRUCTION=XCAREINSTRUCTION:*

END_SYMBOL_DEFINITIONS
BEGIN_ASSOCIATION_SQL_STATEMENT
	name=MyCompanyWarrantyCatalogEntry
	base_table=CATENTRY
	additional_entity_objects=true
	sql=
		SELECT 
		    CATENTRY.$COLS:CATENTRY$,
		    XWARRANTY.$COLS:XWARRANTY$
		FROM
		    CATENTRY
		      JOIN STORECENT ON STORECENT.CATENTRY_ID=CATENTRY.CATENTRY_ID 
		      LEFT OUTER JOIN XWARRANTY ON (CATENTRY.CATENTRY_ID = XWARRANTY.CATENTRY_ID) 
		WHERE
		    CATENTRY.CATENTRY_ID IN ($ENTITY_PKS$)
		    AND STORECENT.STOREENT_ID IN ($STOREPATH:catalog$) 
		    AND CATENTRY.MARKFORDELETE=0
END_ASSOCIATION_SQL_STATEMENT

BEGIN_XPATH_TO_SQL_STATEMENT
name=/CatalogEntry[CatalogEntryIdentifier[(UniqueID=)]]/Description+MyCompany_CatalogEntryDescription_Update
base_table=CATENTDESC 
sql=
SELECT 
    CATENTDESC.$COLS:CATENTDESC$,
    XCAREINSTRUCTION.$COLS:XCAREINSTRUCTION$
FROM
    CATENTDESC
      LEFT OUTER JOIN XCAREINSTRUCTION ON (CATENTDESC.CATENTRY_ID = XCAREINSTRUCTION.CATENTRY_ID AND XCAREINSTRUCTION.LANGUAGE_ID = CATENTDESC.LANGUAGE_ID)

WHERE
    CATENTDESC.CATENTRY_ID IN (?UniqueID?) 

END_XPATH_TO_SQL_STATEMENT 

BEGIN_PROFILE
	name=MyCompany_CatalogEntry_Update
	extends = IBM_Admin_CatalogEntryUpdate
	BEGIN_ENTITY
		associated_sql_statement=MyCompanyWarrantyCatalogEntry
	END_ENTITY
END_PROFILE

Save the file.

Update the wc-business-object-mediator.xml to instruct the Catalog service module to use the newly defined access profiles. This profile causes the Catalog service module to use the new queries, which include the custom tables, instead of the default queries provided by WebSphere Commerce.

Open WC\xml\config\com.ibm.commerce.catalog-ext\wc-business-object-mediator.xml.

Select the source view and find the following element:

<_config:object logicalType="com.ibm.commerce.catalog.facade.datatypes.CatalogEntryType" 
physicalType="com.mycompany.commerce.catalog.facade.server.entity.datatypes.MyCompanyCatalogEntry">

Copy and paste the following mediation configuration after the line you found in step 3b:

<_config:mediator interfaceName="com.ibm.commerce.foundation.server.services.dataaccess.bom.mediator.ChangeBusinessObjectMediator" 
className="com.ibm.commerce.catalog.facade.server.services.dataaccess.bom.mediator.ChangeCatalogEntryMediator" 
updateAccessProfile="MyCompany_CatalogEntry_Update">
    <_config:part-mediator interfaceName="com.ibm.commerce.foundation.server.services.dataaccess.bom.mediator.ChangeBusinessObjectPartMediator">
      <_config:part-mediator-implementation className="com.ibm.commerce.catalog.facade.server.services.dataaccess.bom.mediator.ChangeCatalogEntryBasePartMediator" updateAccessProfile="MyCompany_CatalogEntry_Update"/>
      <_config:part-mediator-implementation className="com.ibm.commerce.catalog.facade.server.services.dataaccess.bom.mediator.ChangeCatalogEntryDescriptionMediator" updateAccessProfile="MyCompany_CatalogEntryDescription_Update"/>
    </_config:part-mediator> 
</_config:mediator>

Save the file.