Enabling product sequencing with search rule

Prerequisite: APAR JR48954

1. Add the following new index field to the <fields> section of the schema.xml file:

   <!--
   Catentry's display sequence: map to table: TI_ENTGRPPATH
   -->
   <field name="globalSequence" type="wc_keywordText" indexed="true" stored="true" multiValued="true"/>

   For example: solr_home/MC_catalogId/locale/CatalogEntry/conf/schema.xml
2. Replace the following line in the WC_instance/xml/config/com.ibm.commerce.catalog-fep/wc-component.xml file to enable the new feature:

   <_config:property name="BoostByRankInCatalogAndCategory" value="getSequenceByCatalogAndCategory(sequence,%s,%s)" />

   with:

   <_config:property name="BoostByRankInCatalogAndCategory" value="getSequenceByCatalogAndCategory(globalSequence,'%s','%s')" />

   Optionally, in order to allow search rule with filtering conditions to work with product sequencing during category navigation, add and set to true the following property to this same file:

   <_config:property name="CombineFilterRuleWithProductSequencing" value="true" />

   When this property is enabled, search rule for all keywords can be used for category navigation and products returned will be ordered according to their sequence defined at that category. Default is false.
   Limitation: because sorting override ranking at runtime, search rule with boosting and relevancy ranking criteria will be ignored. Only search rule with filtering conditions can be used with product sequencing.
   Note: in order to trigger search rule for all keywords during category navigation, a search term '*' should be added to the browse query request.
   
   
3. Update the following configuration in the solrconfig.xml file to enable the new feature:

   <valueSourceParser name="getSequenceByCatalogAndCategory"
        class="com.ibm.commerce.foundation.internal.server.services.search.function.solr.SolrSearchGetDeepSequenceFunctionParser" />

   For example: solr_home/MC_catalogId/locale/CatalogEntry/conf/solrconfig.xml
4. On the search indexer, copy the components/foundation/samples/dataimport/catalog/database/wc-dataimport-preprocess-catgroup-global-sequence.xml file to the preprocessor configuration directory.
   For example: solr_home/pre-processConfig/MC_catalogId/database/wc-dataimport-preprocess-catgroup-global-sequence.xml
   The perform the following steps in the given order:
   1. Find the index scope tag by running this SQL statement: select indexscope, indextype, config from srchconf where indexscope='masterCatalogId' and indextype='CatalogEntry'. For example, if IndexScopeTag=0, then the tag is 0.
   2. Open the wc-dataimport-preprocess-catgroup-global-sequence.xml file in a text editor.
   3. Replace all occurrences of #INDEX_SCOPE_TAG# with the index scope tag in (1) above.
   4. Replace all occurrences of #MASTER_CATALOG_ID# with the master catalog ID used in the SQL in (1) above.
   5. Check the table names in the file. For example, TI_GROUPPATH_0, to ensure that the index scope tag is 0.
   6. If you have more than one MC_masterCatalogId directory, repeat the above steps for all the MC_masterCatalogId directories.
5. Update the solr_home/MC_catalogId/locale/CatalogEntry/conf/wc-data-config.xml file with the following snippets in bold:

   <dataSource name="WC database"
                 type="JdbcDataSource"
                 transactionIsolation="TRANSACTION_READ_COMMITTED"
                 holdability="CLOSE_CURSORS_AT_COMMIT"
     />
   
     <dataSource name="unstructuretmpfile"
                 type="FileDataSource"
   
          <!-- Product start -->
          <entity name="Product"
           
            TI_CATGPENREL.SEQUENCE,
            TI_ENTGRPPATH.ENTGRPPATH,
            TI_SEOURL.SEO_TOKEN,
   
            LEFT OUTER JOIN TI_CATGPENREL_indexScope TI_CATGPENREL ON (CATENTRY.CATENTRY_ID=TI_CATGPENREL.CATENTRY_ID)
            LEFT OUTER JOIN TI_ENTGRPPATH_indexScope TI_ENTGRPPATH ON (CATENTRY.CATENTRY_ID=TI_ENTGRPPATH.CATENTRY_ID)
            LEFT OUTER JOIN TI_SEOURL_indexScope_languageId TI_SEOURL ON (CATENTRY.CATENTRY_ID=TI_SEOURL.CATENTRY_ID)
   
            deltaImportQuery="SELECT CATENTRY.CATENTRY_ID,CATENTRY.MEMBER_ID,CATENTRY.CATENTTYPE_ID,CATENTRY.PARTNUMBER,CATENTRY.MFPARTNUMBER,CATENTRY.MFNAME, CATENTRY.BUYABLE, CATENTRY.STARTDATE, CATENTRY.ENDDATE,
   
            TI_CATGPENREL.SEQUENCE,
            TI_ENTGRPPATH.ENTGRPPATH,
            TI_SEOURL.SEO_TOKEN,
   
            LEFT OUTER JOIN TI_CATGPENREL_indexScope TI_CATGPENREL ON (CATENTRY.CATENTRY_ID=TI_CATGPENREL.CATENTRY_ID)
            LEFT OUTER JOIN TI_ENTGRPPATH_indexScope TI_ENTGRPPATH ON (CATENTRY.CATENTRY_ID=TI_ENTGRPPATH.CATENTRY_ID)
            LEFT OUTER JOIN TI_SEOURL_indexScope_languageId TI_SEOURL ON (CATENTRY.CATENTRY_ID=TI_SEOURL.CATENTRY_ID)
   
            <field column="sequence" splitBy=";" sourceColName="SEQUENCE"/>
            <field column="ENTGRPPATH" clob="true" />
            <field column="globalSequence" splitBy=";" sourceColName="ENTGRPPATH"/>
            <field column="productset_id" splitBy=";" sourceColName="PRODUCTSET"/>
   
          <!-- Product end -->
   
          <!-- Bundle start -->
          <entity name="Bundle"
          
            TI_CATGPENREL.SEQUENCE,
            TI_ENTGRPPATH.ENTGRPPATH,
            TI_SEOURL.SEO_TOKEN,
   
            LEFT OUTER JOIN TI_CATGPENREL_indexScope TI_CATGPENREL ON (CATENTRY.CATENTRY_ID=TI_CATGPENREL.CATENTRY_ID)
            LEFT OUTER JOIN TI_ENTGRPPATH_indexScope TI_ENTGRPPATH ON (CATENTRY.CATENTRY_ID=TI_ENTGRPPATH.CATENTRY_ID)
            LEFT OUTER JOIN TI_SEOURL_indexScope_languageId TI_SEOURL ON (CATENTRY.CATENTRY_ID=TI_SEOURL.CATENTRY_ID)
   
            deltaImportQuery="SELECT CATENTRY.CATENTRY_ID,CATENTRY.MEMBER_ID,CATENTRY.CATENTTYPE_ID,CATENTRY.PARTNUMBER,CATENTRY.MFPARTNUMBER,CATENTRY.MFNAME, CATENTRY.BUYABLE, CATENTRY.STARTDATE, CATENTRY.ENDDATE,
   
            TI_CATGPENREL.SEQUENCE,
            TI_ENTGRPPATH.ENTGRPPATH,
            TI_SEOURL.SEO_TOKEN,
   
            LEFT OUTER JOIN TI_CATGPENREL_indexScope TI_CATGPENREL ON (CATENTRY.CATENTRY_ID=TI_CATGPENREL.CATENTRY_ID)
            LEFT OUTER JOIN TI_ENTGRPPATH_indexScope TI_ENTGRPPATH ON (CATENTRY.CATENTRY_ID=TI_ENTGRPPATH.CATENTRY_ID)
            LEFT OUTER JOIN TI_SEOURL_indexScope_languageId TI_SEOURL ON (CATENTRY.CATENTRY_ID=TI_SEOURL.CATENTRY_ID)
   
            <field column="sequence" splitBy=";" sourceColName="SEQUENCE"/>
            <field column="ENTGRPPATH" clob="true" />
            <field column="globalSequence" splitBy=";" sourceColName="ENTGRPPATH"/>
            <field column="productset_id" splitBy=";" sourceColName="PRODUCTSET"/>
   
          <!-- Bundle end -->
   
          <!-- Dynamic Kit start -->
          <entity name="DynamicKit"
   
            TI_CATGPENREL.SEQUENCE,
            TI_ENTGRPPATH.ENTGRPPATH,
            TI_SEOURL.SEO_TOKEN,
   
            LEFT OUTER JOIN TI_CATGPENREL_indexScope TI_CATGPENREL ON (CATENTRY.CATENTRY_ID=TI_CATGPENREL.CATENTRY_ID)
            LEFT OUTER JOIN TI_ENTGRPPATH_indexScope TI_ENTGRPPATH ON (CATENTRY.CATENTRY_ID=TI_ENTGRPPATH.CATENTRY_ID)
            LEFT OUTER JOIN TI_SEOURL_indexScope_languageId TI_SEOURL ON (CATENTRY.CATENTRY_ID=TI_SEOURL.CATENTRY_ID)
   
            deltaImportQuery="SELECT CATENTRY.CATENTRY_ID,CATENTRY.MEMBER_ID,CATENTRY.CATENTTYPE_ID,CATENTRY.PARTNUMBER,CATENTRY.MFPARTNUMBER,CATENTRY.MFNAME, CATENTRY.BUYABLE, CATENTRY.STARTDATE, CATENTRY.ENDDATE,
   
            TI_CATGPENREL.SEQUENCE,
            TI_ENTGRPPATH.ENTGRPPATH,
            TI_SEOURL.SEO_TOKEN,
   
            LEFT OUTER JOIN TI_CATGPENREL_indexScope TI_CATGPENREL ON (CATENTRY.CATENTRY_ID=TI_CATGPENREL.CATENTRY_ID)
            LEFT OUTER JOIN TI_ENTGRPPATH_indexScope TI_ENTGRPPATH ON (CATENTRY.CATENTRY_ID=TI_ENTGRPPATH.CATENTRY_ID)
            LEFT OUTER JOIN TI_SEOURL_indexScope_languageId TI_SEOURL ON (CATENTRY.CATENTRY_ID=TI_SEOURL.CATENTRY_ID)
   
            <field column="sequence" splitBy=";" sourceColName="SEQUENCE"/>
            <field column="ENTGRPPATH" clob="true" />
            <field column="globalSequence" splitBy=";" sourceColName="ENTGRPPATH"/>
            <field column="productset_id" splitBy=";" sourceColName="PRODUCTSET"/>
   
          <!-- Dynamic Kit end -->
   

6. Perform a full search re-index and restart your WebSphere Commerce server.