Solr settings and full indexing (Attribute task update)

SOLR is mainly used for :

• Indexing
• Querying

   SOLR is a product of apache and having the inbuilt server named jeety.

Mainly the SOLR has the data types(normal java)that has to be mapped in the solr manner where these are called as dynamics fields(SOLR specific).

       Data Types = dynamic fields

Example : <field name ="catentry_id" type="string" indexed="true" stored="true" required = "true"                         multiValued=£false"/>

Here in the above example :

• type is the solr datatype.
• name is the WCS datatype (Field name of table).
• indexed must be true (used for indexing in solr search).
• stored  mainly used to store the indexed field.

     If we want to customize by adding any table data that need to be indexed then we need to update the schema.xml.

      In the schema.xml we will find the file names like stopwords.txt and protwords.txt which files are responsible for the words that can be skipped during the search filtering process.

      Steps to create the SOLR setup:

1.       setupSearchIndex.bat :

           <toolkit-home>\components\foundation\subcomponents\search\bin\setupSearchIndex.bat

Syntax :

       D:\IBM\WCDE_ENT70\bin>setupSearchIndex.bat -masterCatalogId <mastercatalog_id>

     Actually the search folder gets created in the home directory which contains the following files mentioned below:

•     solrconfig.xml (requestHandlers, search components).
•      wc-data-config.xml ( Mainly for establishing the server connections, Solr Data Import Handler configuration for DB config).
•     Schema.xml (mainly used for mapping the tables and fields that has to be indexed by solr)

          Make sure the server is in up and running when running this setupSearchIndex.bat

        

         2.di-preProcess.bat :

          <toolkit-home> \components\foundation\bin\di-preprocess.bat

       Syntax :

           D:\IBM\WCDE_ENT70\bin>di-preprocess.bat D:\IBM\WCDE_ENT70\search\pre-   processConfig\MC_<mastercat_id>\Cloudscape -localename en_US

       Actually this is used to push the data into the temporary table after the index has took place.

       3.di-buildIndex.bat :

        <toolkit-home> \components\foundation\bin\di-buildIndex.bat

          D:\IBM\WCDE_ENT70\bin>di-buildindex.bat -masterCatalogId <mastercat_id> -localename en_US

      Actually this step is the final batch that we run during the whole solr indexing process.    Especially this is mainly meant to get the indexing completed.

     The following url is used for testing the solr indexing is done properly or not is :

         http://localhost/solr/MC_<mastercatalog_id>_CatalogEntry_en_US/select?q=*.*

      Main files to be remembered for SOLR update are below mentioned:

   ·  D:\IBM\WCDE_ENT70\search\solr\home\MC_10351\en_US\CatalogEntry\conf\solrconfig.xml

   ·  D:\IBM\WCDE_ENT70\search\solr\home\MC_10351\en_US\CatalogEntry\conf\wc-data-   config.xml

   ·  D:\IBM\WCDE_ENT70\search\solr\home\MC_10351\en_US\CatalogEntry\conf\schema.xml

   ·  D:\IBM\WCDE_ENT70\search\pre-processConfig\MC_10351\Cloudscape\ALL XML files related to indexing of solr

   ·  The whole files (esply the xml) and folders (search present in toolkit home location) that are created during the process of setupSearchIndex.bat running are already present and in the  sample folder present in the below mentioned path

      D:\IBM\WCDE_ENT70\components\foundation\samples 

Task related to attribute dictionary model

    1.     Start the management center

         https://localhost:8000/lobtools/cmc/ManagementCenter

    2.     Login to CMC and select the store(AURORA) from the drop down.

    3.     Select the catalog and select the attribute dictionary

Create a new attribute under this section and give the required values.

NOTE: Here keep in mind while creating the attribute dictionary attribute always select the attribute dictionary attribute with the predefined value.

         Under this step 3 we need fill the attribute values information with any value (for    EX: sequence can be n value and value section with any text value).

           Remaining fields can be left free.

          Once done select save and close.

       SQL : select * from attr where identifier='<Name given in the step 3 for attribute >';

       SQL : select * from attrval where attr_id= <select the attr_id which we got from the above sql>;

    4.     Now select a product to associate the create attribute ( step 3 ) for a specific product     for getting the catentryattr table updated with the associated attribute values.

       SQL : select * from catentryattr where attr_id=<select the attr_id which we got from the above sql>;

     Here from the above query we can see that the catentryattr table is being updated with  the associated attribute that we created in the step 3 above.

    5.     Now let’s check the solr part once the above steps are completed successfully, for   this we need to run the preprocess to get the temporary table updated with the above created table

     For the checking of above step lets verify the date that’s being updated in the attribute  related temporary date given in the OOB solr indexing process.( i.e. TI_ATTR_0_#lang_tag# where the lang id has to be mentioned appropriate and this table name we can get it from the preprocess xml files that are created during the setupSearchIndex.bat file running that is the search folder content (wc-dataimport-preprocess-attribute.xml) where we can see the temporary tables creation queries.)

     NOTE : while running this running this preprocess.bat keep the server in stop mode.

    6.     Now if the preprocess is completed with no errors then check the TI_ATTR_0_#lang_tag# table whether the date has been updated with the appropriate   value that was given for temporary table update.

       SQL : select * from TI_ATTR_0_1;

     You will find the updated data with field being updated with the name like

         'ads_f10501: <Value given in step3>||ads_f10501_ntk_cs:<Value given in step3>'

    7.     With this above correct value update in the temporary table we can conclude  preprocess has been done correctly.

    8.     Now the final step is buildindex in this we need to go for full build so that the data is    pushed to solr search engine so that we can retrieve when we search in the site. J J

    9.     Once the di-buildIndex.bat step is completed with no error codes. Then we need check the indexed data by running the below url in the browser.

        http://localhost/solr/MC_10351_CatalogEntry_en_US/select?q=ads_f10501: <Value given in step3>

    We can find the data indexed properly with details as expected with this the solr indexing for attribute dictionary task has been updated J J

Creating new page layout for Product display page.

In this tutorial let’s discuss of how to create a custom page layout. For this we will take the sample of the product page layout which is already present and on using this existing layout we will create a new layout for the product page. For this we will just modify the existing code for getting familiar of creation of page layout. Later you guys can try trails with your own styles J

Lets start the tast prior to this lets have some basic information like the below :

Store_id, storeent_id, member_id, Max_pagelayout_id.

Lets gather the information by simple queries,

Open the database access JSP,

• Execute : select * from STOREENT where identifier ='Aurora' ; (This query gives STOREENT_ID and MEMBER_ID of our store)
• Execute : select max(PAGELAYOUT_ID) from PAGELAYOUT; (This query gives maximum Pagelayout_id used. So we can make Max+1 for our new page layout)

Creating the new product page layout JSP:

                We will create a new product page layout by just copying the default product page layout and modify it. In the default product page layout, we will find a double e-marketing spot container. Lets remove this code part for our newly creating page layout.

Open the toolkit and search(shft+ctrl+R) ProductDisplay.jsp

Copy and paste there in the same folder(/Stores/WebContent/Aurora/Layouts/).

Then rename the copied file to XProductDisplay.jsp.

Open the newly copied file and start modifying the code by removing the double e-spot container section. Just check-out the sample code part.

<!—Start Double E-Spot Container à

<div class=”widget_double_espot_container_position”>

<div class=”widget_double_espot_container”>

<c:choose>

<c:when test=”${env_fetchMarketingDetailsOnLoad}”>

<div dojoType=”wc.widget.RefreshArea” id=”DoubleContentAreaESpot_Widget” controllerId=”DoubleContentAreaESpot_Controller”>

</div>

</c:when>

<c:otherwise>

<%out.flush();%>

<c:import url=”${env_jspStoreDir}Widgets/Espot/ContentRecommendation/ContentRecommendation.jsp”>

      <c:param name=”emsName” value=”CatalogBanner_Content” />

      <c:param name=”numberContentPerRow” value=”2” />

      <c:param name=”catalogId” value=”${catalogId}” />

      </c:import>      

      <%out.flush();%>

      </c:otherwise>

      </c:choose>

      </div>

      </div>

<!—End Double E-Spot Container -> 

• Lets add some CSS to this newly created page layout. Check-out the sample code part below,

<!-- Product Image and Product Information -->

<style type=”text/css”>

.left_product_information_div

{

float:left;

margin-top : 36px;

positive:relative;

}

.right_product_image_div

{

Border-right-width:0;

Margin-left:14px;

Float:left;

Position:relative;

}

<!-- Content with Right Sidebar -->

.left_recommended_products

{

Float:left;

}

.right_desc

{

Border-right-width:0;

Height:190px;

Width:700px;

Float:right;

}

.infoview

{

Margin-top:40px;

}

</style>

• Now finally modification part is done and now save the file J.

Now lets define the struts entries for the newly created pagelayout.

We will be updating the struts entries by following the below steps,

• Open struts-config-ext.xml and paste the following code part before to </global-forwards> tag

<!—modified for new page layout -->

<forward className="com.ibm.commerce.struts.ECActionForward" name="XProductPageLayoutView/11001" path="/Layouts/ XProductDisplay.jsp "/>

<!—modified for new page layout -->

@Note : the store_id may be different at your end.

• Under the action-mappings update the following code part exactly before the </action-mappings> tag

<!—modified for new page layout -->

<action path=”/XProductPageLayoutView” type=”com.ibm.commerce.struts.baseaction”>

<set-property property=”credentialsAccepted” value=”10101:P”/>

</action>

<!—modified for new page layout -->

• Save the struts file after updating all the modifications.

Update Struts configuration :

• we can update the struts by restarting the server or else
• we can update in the commerce administration console
• both the steps are doing the same task finally.

Now lets add the new thumbnail images in the newly created product page layout:

Copy the images (i.e. product_thumb_large.png and product_thumb_small.png) from stores>web content > Aurora > images > layouts. Edit the images by removing double e-spot rename as XProduct_thumb_small.png and XProduct_thumb_large.png and paste it under the same path.

Register the new product page layout in the DB:

• Restart the server after making all the above mentioned modifications
• Open the DB access JSP from the hintsandtips.html guide.
• Copy the below script and submit the query.

insert into pagelayout(PAGELAYOUT_ID, NAME, MEMBER_ID,

STOREENT_ID,PAGELAYOUTTYPE_ID, MASTERCSS, DEVICETYPE, STATE,

ISTEMPLATE, DESCRIPTION,THUMBNAIL, FULLIMAGE, VIEWNAME) values

(100067, 'Layout used for dress products',7000000000000000101, 10101, 'ProductPage', null,

'Web', 1, 0, 'This layout contains the following widgets: 1. Header, 2. Product image, 3. Product

promotions, 4. Product name, price and ratings, 5. Product short description, 6.Product attributes

and actions, 7. Inventory details, 8. Merchandising associations, 9. E-Marketing Spot for Catalog

entries, 10. Footer.', '/images/layouts/XProduct_thumb_small.png',

'/images/layouts/XProduct_thumb_large.png', 'XProductPageLayoutView');

• Do remember that the above query doesn’t throw any exceptions.

Apply the New layout and start testing :

1. Open the CMC and select Page layout apply our product layout
2. Select the Aurora store

Assign the new layout for the product

• Store pages for Aurora > Apparel > Women > Dresses > Catalog Entry Page layout properties > Product PageLayout Assignments.
• Drag & Drop the newly updated page layout.
• Enter today date and time for the start date in the section
• Select save and close option.

Test the new layout :

1. Publish the server
2. Reload the Aurora home page and navigate to Departments > Apparel > Women > dresses
3. Select an item. Our custom page layout will come up.
4. Now its possible to modify according to our own desired way for any product page with the layout of the same modifications.

@Let me know if any thing major obligation faced during the modifications.

Commerce composer related data..

Commerce composer important tables:

PAGELAYOUT

PAGELAYOUTTYPE

PLLOCATION

PLPAGE

PLSTOREWIDGET

PLTEMPLATEREL

PLWIDGET

PLWIDGETDEF

PLWIDGETDEFDESC

PLWIDGETNVP

PLWIDGETREL

PLWIDGETSLOT

STOREPLTYPES

Some of the important queries:

Select * from PLWIDGET where PAGELAYOUT_ID in (select PAGELAYOUT_ID from PAGELAYOUT where name like 'Sample%');

Select * from PLWIDGETDEF where IDENTIFIER like 'Sample%';

Select * from PLWIDGETDEFDESC where PLWIDGETDEF_ID in (select PLWIDGETDEF_ID from PLWIDGETDEF where IDENTIFIER like 'Sample%');

Select * from PLSTOREWIDGET where PLWIDGETDEF_ID in (select PLWIDGETDEF_ID from PLWIDGETDEF where IDENTIFIER like 'Sample%');

Select * from PAGELAYOUT where name like 'Sample%';

Select * from PLWIDGETREL where PLWIDGET_ID_CHILD in (select PLWIDGET_ID

 from PLWIDGET where PAGELAYOUT_ID in (select PAGELAYOUT_ID from PAGELAYOUT where name like 'Sample%'));

Select * from PLWIDGETSLOT where PAGELAYOUT_ID in (select PAGELAYOUT_ID from PAGELAYOUT where name like 'Sample%');

Do share the related data if anything found so that it can be helpful to others tooo...

Optimistic locking & Pessimistic locking

Optimistic locking allows you to lower the isolation level that you use in an application so that fewer locks are placed on the database assets. It allows more applications to run concurrently against the database, and potentially increase the throughput of your applications.
To protect against potential database integrity problems brought on by lowering the isolation level, an optimistic locking implementation has to ensure that no integrity problems occur. The WebSphere Commerce implementation utilizes an optimistic predicate column for each table in the WebSphere Commerce database - OPTCOUNTER.
This helps ensure that every time there is a change to the database, the optimistic predicate can be:
1. Updated with a new value
2. Checked for change
 
In this way, the implementation can guarantee that a row in the database has not changed between the time it was read and the time it was written, to protect the integrity of the database.
With this new freedom that optimistic locking offers, of placing fewer locks on the database, for a shorter period of time, one has to realize that there can also be drawbacks.

A Pessimistic locking strategy means the avoidance of optimistic locking issues by placing locks on the database. In other words, pessimistic locking assumes there will be a collision in the database, and takes precautions to avoid these collisions. A pessimistic locking strategy protects the integrity of the database by locking database assets over the entire duration of a transaction, from the beginning to the end. During this period, no other transactions can access the same assets because they are locked. This strategy results in greater wait times for more transactions. It also raises a possibility that these transactions will either time out or potentially become deadlocked.
An Optimistic locking strategy can improve the application execution by shortening the window in which these pessimistic consequences can occur. At the same time, it opens itself up to collisions where two or more transactions attempt to update the same row in the database. When this happens in an optimistic locking strategy, rollbacks (or failures) for one or more (or all) of the transactions can occur.

WebSphere Commerce uses an optimistic locking scheme that allows the database transaction isolation level to be lowered from repeatable read to read committed. Using that scheme, database rows not normally accessed concurrently are not locked with an intent to update when they are read. Instead, when the update is eventually made, the row is checked to make sure it has not been updated concurrently since it was read. If it has been updated concurrently, then the transaction is rolled back and the command may be restarted from the beginning in a new transaction, if appropriate. In this scheme, performance is improved when concurrent updates do not normally occur, because the relatively expensive process of obtaining the database locks with intent to update is avoided. On the other hand, for those operations where concurrent updates are more likely to occur, pessimistic locking, whereby intent to update locks are obtained when the row is read, continues to be used, thus avoiding the more expensive process of rolling back and re-starting from the beginning in a new transaction.

Attribute Data-Model and important tables with queries.

Attribute Data-Model

--Information related to ATTR_ID & IDENTIFIER based on this we will get the ATTR_ID with which we will be proceeding further for the next table.

select * from attr;

-- Information related to ATTRVAL_ID & ATTR_ID & IDENTIFIER based on the previous step we will be getting the ATTRVAL_ID with which we further proceed to get the catentries related to the particular value.

select * from attrval where attr_id=7000000000000009685;

-- Information related to ATTRVAL_ID  & ATTR_ID & CATENTRY_ID based on the previous step we will get the catEntries which are associated to a particular ATTR_ID

(Stores the catalog entry (CATENTRY) and attribute dictionary attribute (ATTR) relationship)

select * from catentryattr where attrval_id=7000000000000021190 and attr_id =7000000000000009685;

Attribute data model

Attribute Data Model-tables