Customizing SEO Title in WCS

WCS is using SEOPAGEDEFDESC table for maintaining the SEO related attributes such as meta tile, description and keywords. Google crawlers are using the values in meta description and keyword fields while calculating the ranking. Hence it is important to have meaningful & product/category specific values to be generated in each of the pages displayed to the user.

We can either insert values corresponding to each category and product pages in this table or we can insert substitution parameters defined by WCS, which will get evaluated during runtime. There is a pre-defined set of substitution parameters defined by WCS, a few them can be used only in category pages and others in product pages. For eg: <seo:productShortDescription> can be used only in product details pages.

By default, the SEOPAGEDEFDESC table will be populated with the substitution parameters defined by WCS.

There can be situations, where business want us to configure a different set of title and meta description by providing a template, which is applicable to all products/categories. This can be done by using a script/stored procedure to generate meta title and keyword for each product/category based on the template and insert it into the above table. In this case, there will be an entry in  SEOPAGEDEFDESC against each and every product/category. Each time, a new product gets added to WCS, this activity should happen.

Another option to handle this situation is by creating new substitution parameters. This can be done by inserting a new substitution parameter name in SEOPAGEDEFSUBPARAM table. Insert an entry in CMDREG table to override the default implementation class used for substitution parameter.

INSERT INTO CMDREG (storeent_id,interface,classname) VALUES (0, 'com.ibm.commerce.seo.pagedefinition.commands.ProcessSEOSubstitutionParameterCmd', 'com.test.commerce.CustomSubstitutionCmdImpl')

The business logic to generate meta title and description as per the template provided needs to be written in the CustomSubstitutionCmdImpl.

Generating Data Feeds from Solr

Solr is the default search engine which comes along with Websphere commerce installation. Apart from using it for implementing search functionality in your website, it can be used for feed generation also, when you don't have enough time to create a new feed generation utility. 

This will be done using the wt parameter passed in the Solr request. Given below is a sample Solr request.

http://localhost/solr/MC_10001_CatalogEntry_en_US/select?q=(*:*)&wt=csv

The wt parameter in the request specifies in which format the Solr response will be generated. When the call happen from WCS application, this field will have value javabin. We can also pass other values such as json, based on which it will generate the response in that particular format.

To generate the feed in csv format, what need to be done is to invoke the above URL from a wrapper application/script, which will generate a csv file, which can be ftp'ed to the required location.

Using Solr for feed generation will be ideal in situations when all the required attributes are already available in Solr search engine and the Solr servers have enough capability to handle this additional load.

SEO URLs in WCS

   When it comes to generating SEO URLs, there are mainly 2 things we need to give focus on. One is removing dynamic attributes (? ,= , & etc) from the URL and another one is shortening the url.

In the earlier versions of WCS, it was relying only on configuration files to support SEO URLs. From Websphere Commerce Server 7 Feature pack 3 onwards, we can also maintain SEO URL related data in 2 OOB tables named SEOURL and SEOURLKEYWORD. The keywords mentioned in this table will be used to construct SEO URLs. The configurations mentioned in SEOURLPatterns.xml will be used for the same.

The data can loaded in to these new tables by using seourlkeywordgen utility or it can be loaded along with data load process by configuring loadSEO property as true.

URL Shortening can be done by modifying the following files.

1. httpd.conf
2. wc-server.xml

Httpd.conf:

The following rewrite rule can be added to map the shortened URLs to WCS specific URLs.

 RewriteEngine on
 RewriteRule ^/(.*)$      /webapp/wcs/stores/servlet/$1 [PT,NC]

wc-server.xml:

The SEOConfiguration section in wc-server.xml needs to be modified to change the context root value.

 <SEOConfiguration defaultUrl="" dynamicUrl="true" enable="true">
        <context-root-rewrite value="/" />
 </SEOConfiguration>

To handle 404 and 301 redirects, WCS is also maintaining a table named SEOREDIRECT.

Enabling features in WCS

Once the WCS installation is completed and the required feature packs are installed, the following scripts should be executed to enable them. You need not execute all the features. It depends on what all features you are going to use in your application.

The scripts are present under <WCS_INSTALLATION_DIRECTORY>/bin

enableFeature.bat -DfeatureName=foundation

enableFeature.bat -DfeatureName=management-center

enableFeature.bat -DfeatureName=content-version

enableFeature.bat -DfeatureName=store-enhancements

enableFeature.bat -DfeatureName=social-commerce

enableFeature.bat -DfeatureName=location-services

Character Functions

Common SQL queries

 

Purpose	SQL Query	O/p	Remarks
Database Name	select ora_database_name from dual;	SQLDB	Return the database name
Full Information of DB	select * from v$database;	DbName,creationdate ..	Full information about the database
Current Time	select SYSDATE from dual;	03-JUN-14
Single line comment	-- description
Multiline comments	/* …..  ……  */

 Character Functions:

 

Function	SQL Query
Length – No of characters in a string	Returns the Length (no of charaters ) of 'MALAYALAM' – 9   select LENGTH('MALAYALAM') from dual; 9   select LENGTH(ACCT_NAME) from ACCOUNTS where ACCT_ID='598934';
INITCAP  – First letterer of each words to capital letter	select INITCAP('hello to all') from dual; Hello To All   select INITCAP (ACCT_NAME) from ACCOUNTS where ACCT_ID='598934'
CONCAT  – Concatenate 2 values	select CONCAT('EmP No:','18236') from dual;   EmP No:18236     select CONCAT(ACCT_ID || ':',  ACCT_NAME)  from ACCOUNTS where ACCT_ID = '8734545';
|| ( connate operator) Concatenate 2 values	select 'EmP No:' ||'18236'  EMPDetails rom dual;   EmP No:18236
UPPER -Convert characters to upper case	select UPPER('MALAYALAM') from dual;   MALAYALAM                    select * from  ACCOUNTS where UPPER(acct_name) like '%VISHNU%'
LOWER -Convert characters to lower case	select LOWER('MALAYALAM') from dual;   malayalam     select * from ACCOUNTS where LOWER(acct_name) like '%vishnu%'
ASCII -Convert characters to ASCII value	select ASCII('0') from dual;   48
INSTR -To find the position of the given letter   INSTR(Value, character to find, starting position from where we need to find Chara position, which occurrence position you need to find)	select INSTR('MALAYALAM','L')  from dual;   3 select INSTR('MALAYALAM','A')  from dual;   2 select INSTR('MALAYALAM','A',1)  from dual 2 select INSTR('MALAYALAM','A',1,2)  from dual;   4 select INSTR('MALAYALAM','A',3,2)  from dual;   6   select INSTR(acct_name,'I',8, 2)  from accounts  where acct_id = '124466'
LTRIM LTRIM(String,Value)   -Trim the given Value from the left side of the String	select LTRIM('aaabcd','a') from dual;   bcd   select LTRIM('   MALAYALAM  ',' ') from dual;   MALAYALAM'
RTRIM RTRIM(String,Value)   -Trim the given Value from the right  side of the String	select RTRIM('aaabcdddd','d') from dual;   Aaabc select RTRIM(' MALAYALAM    ',' ') from dual;   ‘MALAYALAM'
REPLACE   REPLACE(String,valuetofind,valuetoreplace)   -Find the valuetofind in the String and replace with valuetoreplace	select REPLACE('aaabcdddd','a','e') from dual;   eeebcdddd   select REPLACE('aaabcdddd','aa','e') from dual;   eabcdddd
SUBSTR   SUBSTR(String,startingposition,endingposition) -substring will extract the String from startingposition to endingposition and return that extracted String	select SUBSTR('aaabcdddd',4,10) from dual;   bcdddd
TRANSLATE   TRANSLATE(String, series of characters to find, series of characters to replace)	select TRANSLATE('aaabcdddd','abd','efg') from dual;     eeefcgggg   In the  above example series of characters to find - 'abd'                                 series of characters to replace - 'efg' So it will find every ‘a’ from String 'aaabcdddd' and replace it with ‘e’ Next, ‘b’ with ‘f’ next – ‘d’ with ‘g’