Friday, 20 June 2014

Websphere Commerce BOD Get Data Service using wcf:get Data Service call


 wcf:get Data Service call is used for read operation and it works in the below steps. 

1.For read operations, the data service layer facade receives a query from the business logic layer. The query consists of an XPath expression and an access profile, which are extracted from the OAGIS GET verb.

2.The data service layer forwards this query to the business object mediator (BOM) who, in turn, passes it to the persistence service. That service looks up the correct SQL template for the query, and uses it to generate one or more SQL statements.(Using the Query Template Files)

3.It then runs these statements, and maps their result sets into physical SDOs. This mapping, between the database schema (tables and columns) and the SDO classes, is defined by XML called metadata. This mapping is done with the help of wc-object-relational-metadata.xml.There can be an extension for this file which   describes any new custom tables and its relationships as well.

4.Finally, the physical SDOs are returned to the business object mediator(BOM).The BOM configuration describes how to instantiate the necessary mediators. These are returned to the business logic layer. Note that it is the mediator who is returned, not just the SDO. The mediator contains the physical SDO data. It also contains the logic to convert the physical SDO to the logical SDO (which is the Java representation of an OAGIS noun).This mapping from physical SDO to logical SDO is done with the help of wc-business-object-mediator.xml.There can be an extension business object mediator configuration file that configures the business object mediator to include data from the custom table in the user data of a noun.

5.Logical SDO are returned to JSP which is used for calling the service.



More details are provided in the below section

1). Use the Expression Builder to make the service call in JSP by making use of   wcf:getData  tag

<wcf:getData type="com.ibm.commerce.subscription.facade.datatypes.SubscriptionType" var="recurringOrdersDetails"  expressionBuilder="getSubscriptionDetailsByUniqueID"  >
<wcf:param name="UniqueID" value="${param.subscriptionId}" />
<wcf:param name="storeId" value="${WCParam.storeId}" />
</wcf:getData>


2) The expression builder  will be defined in get-data-config.xml

This file contains the   expression builder name, X path and access Profile:
eg:
<?xml version="1.0" encoding="UTF-8"?>
<_config:get-data-config> 
<expression-builder>
<name>getSubscriptionDetailsByUniqueID</name>
<data-type-name>Subscription</data-type-name> <class>com.ibm.commerce.foundation.internal.client.taglib.util.UniqueIDsExpressionBuilder</class>
<method>formatExpression</method> 
<param> <name>template</name> <value>/Subscription[SubscriptionIdentifier[(UniqueID=)]]
</value> </param>
 <param> <name>accessProfile</name> <value>MyCompany_Store_Details</value> 
</param>
</expression-builder>
</_config:get-data-config>


3 ) Define Query Template file :(.tpl file)

The data service layer supports two types of queries:

Single-Step and Two-Step.

Single-Step queries 

For single-step queries, the access profile name along with the XPathkey is used to select a single XPath to SQL query to retrieve all the requested information. Multiple queries can retrieve different levels of detail for the same XPath expression and different access profiles. 
Eg: wc-query-MyCompanyCatalogEntry-update.tpl. (Single Step Query)
In this file the path (/Subscription) and Access Profile (IBM_IdResolve) maps to SQL as defined below.



<!-- Defining all columns of the table SUBSCRIPTION --> 
COLS: SUBSCRIPTION = SUBSCRIPTION:* 
<!-- Defining all columns of the XSUBSCRSCHTYPE table -->
COLS: XSUBSCRSCHTYPE = XSUBSCRSCHTYPE:* 

BEGIN_XPATH_TO_SQL_STATEMENT name=/Subscription[SubscriptionIdentifier[UniqueID=]]
+MyCompany_Store_Details

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  


Two-Step queries 

Two-step queries use the XPath to SQL statements and the association SQL statements. The XPath to SQL statements fetch the primary keys of the base objects that are of interest for search criteria specified by the XPath query. The association SQL statements, scoped by profile name, retrieve the information about those objects. The associated SQL needs have a foreign key relationship to the XPathSQL base table. Use single-step queries whenever possible. However, in some cases, it is not possible to fetch all the data in a single query. Alternately, your query may join a very large number of tables and may not meet your performance requirements.Two Step Queries are used in the below  mentioned scenarios. 
1.Parametric search queries. 
2.Queries that need to page data. Paging on the result of a single-step query is   not possible if it returns multiple records for each base table record. A two-step query allows you to page on the result set returned by the first statement (the primary keys) rather than on the result set of the second statement. 
3.Complex queries that perform poorly because of joining too many tables. Breaking these queries into an XPath SQL statement and multiple associated SQL statements may improve performance.

wc-query-MyCompanyCatalogEntry-get.tpl (Two Step Query)

BEGIN_SYMBOL_DEFINITIONS 
COLS: XSUBSCRSCHTYPE = XSUBSCRSCHTYPE:*
<!-- Defining all columns of the table SUBSCRIPTION --> 
COLS: SUBSCRIPTION = SUBSCRIPTION:* END_SYMBOL_DEFINITIONS 

BEGIN_XPATH_TO_SQL_STATEMENT name=/Subscription[SubscriptionIdentifier [UniqueID=]]
+MyCompany_Store_Details
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 
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

BEGIN_PROFILE name=MyCompany_Store_Details extends=IBM_Store_Details
BEGIN_ENTITY
associated_sql_statement=MYCOMPANY_SUBSCR_DETAILS
END_ENTITY
END_PROFILE 


4) Access Control Policy for the  Access Profile

The previous step created an access profile, MyCompany_Store_Details. By default, only the users with a site administrator role have access to this new data. In this step, you update the Subscription service access control policy to state that all registered users have access to view this data.



The new policy defines a new action for the MyCompany_Store_Details access profile and adds the new action to the Subscription all registered users group.
Create the following file: WCDE_installdir\xml\policies\xml\MyCompanySubscriptionAccessControlPolicies.xml 

Copy the following access control policy XML into this file: 
<Policies>
<Action Name="GetSubscription.MyCompany_Store_Details" CommandName="GetSubscription.MyCompany_Store_Details"/>
<ActionGroup Name="Subscription-Subscription-RegisteredCustomersForOrg-AccessProfileActionGroup" OwnerID="RootOrganization">
<ActionGroupAction Name="GetSubscription.MyCompany_Store_Details"/>
</ActionGroup>
</Policies> 


Other Configuration files Involved

1.wc-object-relational-metadata.xml : This mapping, between the database schema (tables and columns) and the SDO classes, is defined by XML called metadata. There can be an extension for this file which describes any  new custom tables and its relationships as well.
2.wc-component.xml:  This file instructs WebSphere Commerce to use the newly created physical SDO class created   using Data Service Layer wizard.
3.wc-business-object-mediator.xml :This file is responsible for mapping physical sdo to logical SDO before  returning the response.Logical SDO is the java representation of an OAGIS noun.There can be an extension business object mediator configuration file that configures the business object mediator to include data from the custom table in the user data of a noun .

$ENTITY_PKS$

The $ENTITY_PKS$ tag is used with two step queries, in the associated SQL statements. It marks where to insert the primary key values returned by the XPath to SQL statement. The association SQL statement and XPath to SQL statements work together to obtain the data from the base table and tables related to it. The XPath SQL selects primary keys from the base table. The associated SQL selects data from the tables related to the base table through foreign key relationships. The default behaviour is to fetch primary key values and inject them into the association SQL statement replacing the $ENTITY_PKS$ tag. However, you can configure the query generation to form a single query by injecting the XPath to SQL statement into the association SQL statement as a sub select.

This configuration is controlled by the 'usePrimaryKeyValues' flag which is specified in the entity section of the access profile definition. If the flag is set to 'true', the primary key values retrieved by the XPath to SQL statement are injected into the association SQL statements replacing the $ENTITY_PKS$ tag. This is done for all association SQL statements referenced by this profile.
This is the default behavior. If the flag is set to 'false', the primary key subselect is injected to the associated SQL statements referenced by the access profile.
Note: If paging is used, the value of the 'usePrimaryKeyValues' is always assumed to be 'true'.The XPath to SQL template that fetches the primary keys of all CATENTRY records given their parent catalog group id is defined as shown in the following sample:

SELECT CATENTRY.$COLS:CATENTRY_ID$
FROM CATENTRY INNER JOIN CECGREL ON CATENTRY.CATENTRY_ID  = CECGREL.CATENTRY_ID
WHERE CATENTRY.MARKFORDELETE = 0
AND CECGREL.CATGROUP_ID = ?catGroupId?
AND CECGREL.USAGECODE = ?relationshipType?
The associated SQL fetches descriptions of the given CATENTRY records:
SELECT CATENTRY.$COLS:CATENTRY$, CEDESC.$COLS:CEDESC$
FROM CATENTRY, CEDESC
WHERE CATENTRY.CATENTRY_ID = CEDESC.CATENTRY_ID
AND CATENTRY.MARKFORDELETE = 0
AND CEDESC.LANGUAGE_ID = $CTX:LANG_ID$
AND CATENTRY.CATENTRY_ID IN ( $ENTITY_PKS$ )
After injecting the primary key subselect (XPath to SQL statement) into the associated SQL,the resulting query is as follows:


SELECT CATENTRY.$COLS:CATENTRY$, CEDESC.$COLS:CEDESC$
FROM CATENTRY, CEDESC
WHERE CATENTRY.CATENTRY_ID = CEDESC.CATENTRY_ID
AND CATENTRY.MARKFORDELETE = 0
AND CEDESC.LANGUAGE_ID = $CTX:LANG_ID$
AND CATENTRY.CATENTRY_ID IN
(SELECT CATENTRY.CATENTRY_ID
FROM CATENTRY INNER JOIN CECGREL ON CATENTRY.CATENTRY_ID
= CECGREL.CATENTRY_ID
WHERE CATENTRY.MARKFORDELETE = 0
AND CECGREL.CATGROUP_ID = ?catGroupId?
AND CECGREL.USAGECODE = ?relationshipType?)

If the flag is set to use the subselect, the primary key subselect is injected to the associated SQL. Otherwise, the $ENTITY_PKS$ is replace with the primary key values.
The name of the boolean flag is 'usePrimaryKeyValues'.

If set to true, primary key values retrieved by the XPath to SQL statement will be injected into the association SQL statements replacing the $ENTITY_PKS$ tag. This is done for all association SQL statements referenced by this profile. This is the default. If set to false, the XPath to SQL statement is inserted as a sub select into the association SQL statements replacing the $ENTITY_PKS$ tag.

For eg:
BEGIN_PROFILE
name=IBM_DefaultProfile

BEGIN_ENTITY
base_table=CATENTRY
usePrimaryKeyValues = false
associated_sql_statement=IBM_CatEntrySummary
END_ENTITY

END_PROFILE


11 comments:

  1. Many thanks. Very useful to me as starting WCS developer

    ReplyDelete
  2. Hi Deepak...Great work and nicely explained in details

    ReplyDelete
  3. Is there a way to modify the BOD not to use the SDOs but to use access beans to pull data?

    ReplyDelete
  4. Great info.. Thank you :)

    ReplyDelete
  5. Complicated topic explained in Simpler way. Great Job Deepak !!!

    ReplyDelete
  6. Nice!!
    Very Useful Blog Post!!

    ReplyDelete
  7. I really appreciate information shared above. It’s of great help. If someone want to learn Online (Virtual) instructor lead live training in TECHNOLOGY , kindly contact us http://www.maxmunus.com/contact
    MaxMunus Offer World Class Virtual Instructor led training on TECHNOLOGY. We have industry expert trainer. We provide Training Material and Software Support. MaxMunus has successfully conducted 100000+ trainings in India, USA, UK, Australlia, Switzerland, Qatar, Saudi Arabia, Bangladesh, Bahrain and UAE etc.
    For Demo Contact us.
    Saurabh Srivastava
    MaxMunus
    E-mail: saurabh@maxmunus.com
    Skype id: saurabhmaxmunus
    Ph:+91 8553576305 / 080 - 41103383
    http://www.maxmunus.com/


    ReplyDelete
  8. I really appreciate information shared above. It’s of great help. If someone want to learn Online (Virtual) instructor lead live training in Windows foundation communication, kindly contact us http://www.maxmunus.com/contact
    MaxMunus Offer World Class Virtual Instructor led training on Windows foundation communication. We have industry expert trainer. We provide Training Material and Software Support. MaxMunus has successfully conducted 100000+ trainings in India, USA, UK, Australlia, Switzerland, Qatar, Saudi Arabia, Bangladesh, Bahrain and UAE etc.
    For Demo Contact us:
    Name : Arunkumar U
    Email : arun@maxmunus.com
    Skype id: training_maxmunus
    Contact No.-+91-9738507310
    Company Website –http://www.maxmunus.com



    ReplyDelete