Customizing REST service over Solr to include custom table data in WCS 8 Mod Pack1

Introduction
--------------
PFB the steps to include the custom table XI_RATING data in Solr response and then in REST response in WCS 8-

1) Create the new DB table which will keep the ratings of the products loaded through third party system, populate it with some data for testing-
CREATE TABLE "WCSDB"."X_RATINGS"  (
"CATENTRY_ID" BIGINT NOT NULL ,
"RATING" BIGINT NOT NULL)  

2) Create the custom preprocess file to pick the data from the custom table X_ratings and populate the tem ptable XI_Ratings
File name - WCDE80\search\pre-processConfig\MC_10001\DB2\wc-dataimport-preprocess-custom.xml

<_config:DIHPreProcessConfig xmlns:_config="http://www.ibm.com/xmlns/prod/commerce/foundation/config" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.ibm.com/xmlns/prod/commerce/foundation/config ../../xsd/wc-dataimport-preprocess.xsd ">
  <_config:data-processing-config processor="com.ibm.commerce.foundation.dataimport.preprocess.StaticAttributeDataPreProcessor" batchSize="500">
      <_config:table definition="CREATE TABLE XI_RATINGS ( CATENTRY_ID BIGINT NOT NULL, RATING BIGINT, PRIMARY KEY (CATENTRY_ID))" name="XI_RATINGS"/>
         
   <_config:query sql="SELECT DISTINCT CATENTRY.CATENTRY_ID,X_RATINGS.RATING FROM CATENTRY,X_RATINGS WHERE CATENTRY.CATENTRY_ID = X_RATINGS.CATENTRY_ID"/>
       <_config:mapping>
          <_config:key queryColumn="CATENTRY_ID" tableColumn="CATENTRY_ID"/>
             <_config:column-mapping>
                <_config:column-column-mapping>
                    <_config:column-column queryColumn="RATING" tableColumn="RATING" />
                </_config:column-column-mapping>
             </_config:column-mapping>
        </_config:mapping>   
  </_config:data-processing-config>
</_config:DIHPreProcessConfig>

3) Modify the Solr extension files as follow-
a)x-schema.xml-
<field name="rating" type="string" indexed="true" stored="true" required="false" multiValued="false"/>

b) x-data-config.xml-
<field column="RATING" name="rating"/>

c) solrcore.properties-
dataImporter.ext.querySelect=XI_RATINGS.RATING,
dataImporter.ext.queryFrom=LEFT OUTER JOIN XI_RATINGS ON (CATENTRY.CATENTRY_ID=XI_RATINGS.CATENTRY_ID)

4) Run preprocess with server stopped, and then start server and build Solr index
Newly included field can be validated using the Solr testing URL-
http://localhost/solr/MC_10001_CatalogEntry_en_US/select?q=catentry_id:<catentryId>

5) Make the new search profile in wc-search.xml by extending the existing search profile invoked in the REST service call under Search folder-
<_config:profile name="X_findCatalogEntry" extends="IBM_findProductByIds_Details">
   <_config:query inherits="true">
       <_config:postprocessor           classname="com.ibm.commerce.foundation.server.services.rest.search.postprocessor.solr.SolrRESTSearchCatalogEntryViewUserDataQueryPostprocessor" />
   </_config:query>
   <_config:result inherits="true">
           <_config:field name="rating" />
   </_config:result>  
</_config:profile>

6) Make the new wc-component.xml under Search folder ext folder- (It takes care of the mapping we used to have it under wc-business-object-mediator.xml prior to version 7 feature pack 7)

<?xml version="1.0" encoding="UTF-8"?>
<_config:DevelopmentComponentConfiguration
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.ibm.com/xmlns/prod/commerce/foundation/config ../xsd/wc-component.xsd "
    xmlns:_config="http://www.ibm.com/xmlns/prod/commerce/foundation/config">

    <_config:valuemappingservice>
        <_config:valuemapping externalName="CatalogEntryUserDataFieldNameMapping" internalName="CatalogEntryUserDataFieldNameMapping">
                <_config:valuemap externalValue="RATING" internalValue="rating" />
        </_config:valuemapping>
    </_config:valuemappingservice>
</_config:DevelopmentComponentConfiguration>

7) Make the new wc-rest-resourceconfig.xml file under /Search-Rest/WebContent/WEB-INF/config/com.ibm.commerce.rest-ext/wc-rest-resourceconfig.xml

<ResourceConfig>
    <Resource name="productview">
    <GetUri uri="store/{storeId}/productview/byId/{productId}"
            description="Get product by unique ID"                 searchProfile="X_findCatalogEntry,IBM_findProductByIds_Details,IBM_findProductByIdsWithAttributesAndAttachments,IBM_findProductByIds_Summary,IBM_findProductByIds_Summary_WithNoEntitlementCheck,IBM_Admin_findProductByIds"/>
</Resource>
</ResourceConfig>

8) Restart the server and access the new indexed fields in userdata section using rest call.

9) Testing
http://localhost/search/resources/store/10158/productview/byId/121892?responseFormat=xml&catalogId=10158&currency=USD