In this lesson, you update the Subscription service module with the scheduler type and
the day-of-month user data. You add query template files that retrieve your custom
schedule type information. Query template files store the SQL and access profile
definitions for a service module, isolating it from the business logic layer
completely.
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 might reuse existing XPath
keys but must always define a new access profile because a different view of the data is
returned.
Read the following topic to learn more about query template files and how they are
used:
The query template file consists of the following parts:
- A symbol definition section that defines the tables that are used by the query
template (SUBSCRIPTION and XSUBSCRSCHTYPE).
- An XPath to the SQL statement that maps the XPath key and access profile to a
specific template SQL query.
- A new access profile, MyCompany_IdResolve that is used along with the XPath key
to identify the SQL template query.
The default queries to fetch the data before you update the Subscription noun and
ScheduleInfo noun part is updated to include the XSUBSCRSCHTYPE table.
- The default SQL queries for updating the Subscription noun and its part are located
inside the following file:
WC_eardir\xml\config\com.ibm.commerce.subscription\wc-query-Subscription-update.tpl
- The default SQL query that is used to update the Subscription noun is identified by
the IBM_IdResolve access profile.
To configure the Subscription service module to update the new user data, a new query is
added in the extension update query template file. This new query is used to select the
new table by using a new access profile.
Procedure
- Create a custom Get
query file:
- Right-click the com.ibm.commerce.subscription-ext folder
under the WC_eardir\xml\config path.
- Click .
- Name
the file: wc-query-MyCompanyCatalogEntry-get.tpl.
- Click Finish.
- Copy and paste the following query template
into the
file.
BEGIN_SYMBOL_DEFINITIONS
COLS:XSUBSCRSCHTYPE = XSUBSCRSCHTYPE:*
COLS:SUBSCRIPTION_ID = SUBSCRIPTION:SUBSCRIPTION_ID
END_SYMBOL_DEFINITIONS
<!-- ===========================================================================
Get XSubScrSchType query for MyCompany_Store_Details access profile.
=========================================================================== -->
BEGIN_ASSOCIATION_SQL_STATEMENT
name=MYCOMPANY_SUBSCR_DETAILS
base_table=SUBSCRIPTION
sql=
SELECT SUBSCRIPTION.$COLS:SUBSCRIPTION_ID$, XSUBSCRSCHTYPE.$COLS:XSUBSCRSCHTYPE$
FROM SUBSCRIPTION, XSUBSCRSCHTYPE
WHERE SUBSCRIPTION.SUBSCRIPTION_ID=XSUBSCRSCHTYPE.SUBSCRIPTION_ID AND XSUBSCRSCHTYPE.SUBSCRIPTION_ID IN ( $ENTITY_PKS$ )
END_ASSOCIATION_SQL_STATEMENT
<!-- ===========================================================================
Definition for MyCompany_Store_Details access profile.
=========================================================================== -->
BEGIN_PROFILE
name=MyCompany_Store_Details
extends=IBM_Store_Details
BEGIN_ENTITY
associated_sql_statement=MYCOMPANY_SUBSCR_DETAILS
END_ENTITY
END_PROFILE
- Save the file.
- 2. Create a custom Update query
file:
-
Right-click the com.ibm.commerce.subscription-ext
folder under the
WC_eardir\xml\config path.
- Click
-
Name the file:
wc-query-MyCompanyCatalogEntry-update.tpl.
- Click Finish.
- Copy and paste the following query
template into the
file.
BEGIN_SYMBOL_DEFINITIONS
<!-- The table for noun Subscription -->
<!-- Defining all columns of the table -->
COLS:SUBSCRIPTION = SUBSCRIPTION:*
<!-- Defining all columns of the XSUBSCRSCHTYPE table -->
COLS:XSUBSCRSCHTYPE = XSUBSCRSCHTYPE:*
<!-- Defining the unique ID column of the table -->
COLS:SUBSCRIPTION_ID = SUBSCRIPTION:SUBSCRIPTION_ID, OPTCOUNTER
END_SYMBOL_DEFINITIONS
<!-- ================================================================================== -->
<!-- XPath: /Subscription[SubscriptionIdentifier[UniqueID=]]-->
<!-- AccessProfile: IBM_IdResolve -->
<!-- Gets the subscription entity using the UniqueID provided as parameter -->
<!-- Access profile includes all columns from the table. -->
<!-- @param UniqueID Unique id of Subscription to retrieve -->
<!-- ================================================================================== -->
BEGIN_XPATH_TO_SQL_STATEMENT
name=/Subscription[SubscriptionIdentifier[UniqueID=]]+IBM_IdResolve
base_table=SUBSCRIPTION
sql=
SELECT SUBSCRIPTION.$COLS:SUBSCRIPTION$, XSUBSCRSCHTYPE.$COLS:XSUBSCRSCHTYPE$
FROM SUBSCRIPTION LEFT OUTER JOIN XSUBSCRSCHTYPE ON SUBSCRIPTION.SUBSCRIPTION_ID=XSUBSCRSCHTYPE.SUBSCRIPTION_ID
WHERE SUBSCRIPTION.SUBSCRIPTION_ID = ?UniqueID?
END_XPATH_TO_SQL_STATEMENT
- Save
the file.