Parametric search support

Parametric search support

A parametric search allows queries using multiple criteria to narrow the results. For instance, all products that have a name starting with "Toy", under $50, that have color = "blue". WebSphere Commerce supports parametric search queries on both vertical and horizontal schemas.
In the database, if the search attributes are stored in the horizontal schema, then every different attribute has a specific column where it is stored. For instance, products have a PARTNUMBER column. If, instead, they are stored in the vertical schema then the columns are not specific and different attributes can be stored in every row. For example, product can have two attribute rows associated with it, color = blue, and size = large.
For optimal performance, the searchable attributes must be stored in the horizontal schema tables. These tables may include the base table to store base information about a product such as a part number or brand, or associated tables for storing localized data such as descriptions.
Note:

Parametric search queries must use two-step queries.
Parametric search queries should not specify the paging_count SQL statements.

The search() function

search() is a special function that is part of a WebSphere Commerce extended XPath notation. The function can be used to reduce the number of parametric search queries that a developer needs to create.
The search function can be placed in an XPath expression in the predicate (the string in square brackets in the example below) of the noun. The argument to the search() function, together with the mapping between the logical model SDO objects and database schema are used to generate SQL fragments that get injected into the parametric search query templates.
Note: The search function also supports 'and' and 'or' conditions. However, both 'and' and 'or' cannot be used in the search function simultaneously. In the following example, a parametric search XPath expression queries products that have short description containing the "Polo shirt" string and part numbers prefixed with "FU01":
/CatalogEntry[search(contains(Description/ShortDescription, "Polo shirt") and starts-with(CatalogEntryIdentifier/ExternalIdentifier/PartNumber, "FU01"))]
The attribute mapping for the logical entity CatalogEntry shown below, defines CATENTRY as a base table and the CATENTDESC as an associated table. The ‘Description/ShortDescription‘ element (property name) mapping is searched for in the ‘columns' elements of the base table, CATENTRY. If no match is found, the CATENTDESC associated table is searched.
<_config:mapping>
  <_config:key name="CatalogEntry"/>
   <_config:basetable name="CATENTRY" useAllColumns="false">
    <_config:columns name="PARTNUMBER" propertyName="CatalogEntryIdentifier/ExternalIdentifier/PartNumber"/>
    <_config:columns name="MFPARTNUMBER" propertyName="CatalogEntryAttribute/Attributes/mfPartNumber"/>
    <_config:columns name="MFNAME" propertyName="CatalogEntryAttribute/Attributes/mfName"/>

    <_config:associatedtable name="CATENTDESC" useAllColumns="false">
     <_config:columns name="NAME" propertyName="Description/Name"/>
     <_config:columns name="SHORTDESCRIPTION" propertyName="Description/ShortDescription"/>
     <_config:columns name="PUBLISHED" propertyName="Description/Attributes/published"/>
    </_config:associatedtable>
   </_config:basetable>
  </_config:mapping>
The XPath to SQL template with a search() function looks as follows:
BEGIN_XPATH_TO_SQL_STATEMENT
  name=/CatalogEntry[search()]
  base_table=CATENTRY
  sql=
    SELECT DISTINCT CATENTRY.$COLS:CATENTRY_ID$
  FROM
      CATENTRY, $ATTR_TBLS$
  WHERE      
    CATENTRY.MARKFORDELETE = 0 AND
    ( $ATTR_CNDS$ ) 
  ORDER BY
      CATENTRY.CATENTRY_ID 
END_XPATH_TO_SQL_STATEMENT
In the following generated SQL statement, the $ATTR_TBLS$ variable is replaced with the associated table containing the column corresponding to the ‘Description/ShortDescription' property as defined in the mapping above. The $ATTR_CNDS$ variable is replaced with the join condition between this table and the base table and the condition to compare the ‘SHORTDESCRIPTION' column with the short description value provided as input. The condition for the ‘CatalogEntryIdentifier/ExternalIdentifier' property corresponding to the PARTNUMBER column in the base CATENTRY table is also included.
SELECT DISTINCT CATENTRY.CATENTRY_ID
  FROM
      CATENTRY, CATENTDESC IBM_1
  WHERE      
    CATENTRY.MARKFORDELETE = 0 AND CATENTRY.PARTNUMBER LIKE 'FU01%' AND 
    (CATENTRY.CATENTRY_ID= IBM_1.CATENTRY_ID AND 
     IBM_1.SHORTDESCRIPTION LIKE '%Polo shirt%')
  ORDER BY
      CATENTRY.CATENTRY_ID 
Note: The search() function can only refer to the properties mapped to the columns of the base table representing the logical entity object or the columns of the tables that have foreign key relationships to the base table. For example, the search() function used in the predicate of the 'Catalog' element can refer to the catalog's 'description' property. However, it should not refer to the description property of the catalog entries. Also, the search() function is limited to a single element in the XPath expression. Only the first occurrence of the search() function is considered when generating SQL fragments for generated searches.

Configure parametric search
The component configuration file, wc-component.xml, contains a section for the data service layer. It can be divided into 3 main subsections:

Data mediator information
Context information
Attribute mapping information
This configuration is described by the <dataservice> element. An example of the configuration is shown in the following XML sample:
<_config:dataservice dataMediatorType="JDBC" 
		metadataClass="com.ibm.commerce.catalog.facade.server.metadata.CatalogMetadata" 
		maximumPagingResultLimit="2000">
		<_config:context key="LANG_ID" 
					name="com.ibm.commerce.context.globalization.GlobalizationContext" 
					propertyName="languageId" defaultValue="-1"/>
     <_config:context key="CATALOG_ID" 
					name="com.ibm.commerce.catalog.businesscontext.CatalogContext" 
					propertyName="catalogID" defaultValue="-1"/>
     <_config:context key="OWNER_ID" 
					name="com.ibm.commerce.catalog.businesscontext.CatalogContext" 
					propertyName="ownerID" defaultValue="-1"/>
     <_config:context key="STORE_ID" 
					name="com.ibm.commerce.context.base.BaseContext" 
					propertyName="storeId" defaultValue="-1"/>


    
    <_config:mapping>
      <_config:key name="CatalogEntry"/>
      <_config:basetable name="CATENTRY" useAllColumns="false">

        <_config:columns name="PARTNUMBER" propertyName="CatalogEntryIdentifier/ExternalIdentifier/PartNumber"/>
        <_config:columns name="MFPARTNUMBER" propertyName="CatalogEntryAttribute/Attributes/mfPartNumber"/>
        <_config:columns name="MFNAME" propertyName="CatalogEntryAttribute/Attributes/mfName"/>

        <_config:associatedtable name="CATENTDESC" useAllColumns="false">
          <_config:columns name="NAME" propertyName="Description/Name"/>
          <_config:columns name="SHORTDESCRIPTION" propertyName="Description/ShortDescription"/>
          <_config:columns name="PUBLISHED" propertyName="Description/Attributes/published"/>
        </_config:associatedtable>
      </_config:basetable>
  </_config:mapping>

      
    <_config:mapping>
         <_config:key name="CatalogGroup"/>
      <_config:basetable name="CATGROUP" useAllColumns="false">
        <_config:columns name="IDENTIFIER" propertyName="CatalogGroupIdentifier/ExternalIdentifier/GroupIdentifier"/>
               <_config:associatedtable name="CATGRPDESC" useAllColumns="false">
          <_config:columns name="NAME" propertyName="Description/Name"/>
                     <_config:columns name="SHORTDESCRIPTION" propertyName="Description/ShortDescription"/>
               </_config:associatedtable>
      </_config:basetable>
    </_config:mapping>
  </_config:dataservice>
The data mediator section defines the following information:

Data Mediator type: only JDBC is supported.
Metadata class: full class name of the metadata class that provides object-relational metadata information. This class must subclass from com.ibm.commerce.dataservice.db.jdbc.ComponentMetadata
These context definitions are used to retrieve the context data at runtime and substitute them into the template SQL statement. The context information section has the following information:

Key: this is a unique identifier represents the context information. This key is used in the Query Template file as substitution string.
Name: This is the context name defined in the Business Context Service.
Property name: this identifies the property within context containing the data. The context implementation class must have a getter defined for this property.
Default value: this value is used to substitute into the template SQL statement if the context value is not set at runtime.

Attribute mapping
A single logical object has properties stored in multiple physical tables. DSL supports 3 different kinds of tables:

Base table: contains the domain unique identity information. Each column could represent a property of the logical object (the base table typically represents your noun).
Associated table: additional properties stored in a separate table, as part of the horizontal schema. This table has a foreign key relationship with the base table. Each column could represent a property of the logical object.
Property table: additional properties stored in a separate table, as part of the vertical schema. Each row could represent a property/value pair of the logical object. This table has a foreign key relationship with the base table.
The following XML is a sample attribute mapping configuration:
<_config:mapping>
  <_config:key name="CatalogEntry"/>
   <_config:basetable name="CATENTRY" useAllColumns="false">
    <_config:columns name="PARTNUMBER" propertyName="CatalogEntryIdentifier/ExternalIdentifier/PartNumber"/>
    <_config:columns name="MFPARTNUMBER" propertyName="CatalogEntryAttribute/Attributes/mfPartNumber"/>
    <_config:columns name="MFNAME" propertyName="CatalogEntryAttribute/Attributes/mfName"/>

  <_config:associatedtable name="CATENTDESC" useAllColumns="false">
     <_config:columns name="NAME" propertyName="Description/Name"/>
     <_config:columns name="SHORTDESCRIPTION" propertyName="Description/ShortDescription"/>
     <_config:columns name="PUBLISHED" propertyName="Description/Attributes/published"/>
	</_config:associatedtable>

    <_config:propertyTable name="CEPROPERTY">
		<_config:columns name="NAME" propertyName=".name."/>
		<_config:columns name="VALUE" propertyName=".value."/>
    </_config:propertyTable>

   </_config:basetable>
 </_config:mapping>
Each mapping element defines a mapping of a logical object to multiple physical tables. The key element identifies the property of the logical model that references the logical entity object.

Element Attribute Description
basetable Identifies the base table
name Name of the table
useAllColumn Specifies whether all columns in the table are used for parametric search. The attribute values are:

true - all table columns are used. The logical property name mapping to the column name is based on the object-relational metadata information unless they are explicitly overridden by the columns sub-elements (described later). The useAllColumn attribute is optional. If not specified, this is the default.
false - only those columns identified in the columns sub-elements are considered.

columns Sub element of basetable, and associated table that identifies the column information for these tables.
name The name of the column.
propertyname The logical XPath property name, if specified. Otherwise, the property name defined for this column in the object-relational metadata is used.
searchable Specifies if this is used for parametric search.
caseSensitive Specifies whether the searches should treat the value of this column as case-sensitive or not case-sensitive.

true - the content of this column is considered case-sensitive. This is the default.
false - the value of this column is not case-sensitive. When the caseSensitive attribute is set to false, the UPPER() SQL function is used in the generated SQL statement to change the case of the column content. Users should be aware of the performance impact of using the not case-sensitive searches. The use of the UPPER() function will cause a full index scan or even a table scan. For Oracle users, IBM recommends that we use a function-based index to improve performance.

genmode Specifies the generation mode.
associatedtable Defines the associated table. Attributes of this element are the same as for the basetable element.
propertytable Defines the properties table. Only the name attribute is supported.
columns Sub element of propertytable that identifies the column information for these tables.
name The name of the column.
propertyname The logical model property name.

Generation modes
When search attributes are connected by the logical ‘or' operator, a number of search query generation options can be selected by configuration. Different generation modes allow you to fine-tune the performance of the generated queries. For example, with large data sets, breaking a single search query into multiple queries and combining the result set using the UNION operator may provide the best performance. The following generation modes can be specified using the genmode attribute of the columns element:
UNION mode: a query is generated for every search parameter. The result is combined using the UNION SQL operator. For example:
select distinct ce.id, ce.partnumber from catentry ce where ce.partnumber like 'FU01%'
union 
select distinct ce.catentry_id, ce.partnumber from catentry ce, catentdesc d where ce.catentry_id = d.catentry_id and d.shortdescription like '%shirt%' 
EXISTS mode: a single query is generated using the EXISTS condition. For example, the previous example would be generated as:
select distinct ce.catentry_id, ce.partnumber from catentry ce where ce.partnumber like 'FU01%' or exists(select distinct * from catentdesc d where ce.catentry_id=d.catentry_id and d.shortdescription like '%shirt%')
IN mode: a single query is generated using the IN condition. For example, the previous example would be generated as:
select distinct ce.catentry_id, ce.partnumber from catentry ce where ce.partnumber like 'FU01%' or ce.catentry_id in   (select distinct d.catentry_id from catentdesc d where ce.catentry_id=d.catentry_id and d.shortdescription like '%shirt%') 
Generation mode genmode attribute value
UNION 0
EXISTS 1
IN 2
The UNION mode is the default mode. It is used if no configuration is specified. It is also used if at least one of the search attributes is configured to use the UNION mode. The EXISTS mode is used if one of the search attributes is configured for the EXISTS mode and none are configured for the UNION mode. The IN mode is used if all search attributes are configured to use it.

Overriding the generated SQL for parametric search queries

We can override the generated SQL for parametric search queries. For example, if a certain parametric search is not performing well, the database administrator may suggest another way to write the SQL. In this case, we can completely override the SQL used for the parametric search query.
To override the generated SQL, all you need to do is define a new XPATH_TO_SQL_STATEMENT block in our custom template file. The name of the query must be the XPath key, with the search parameters specified explicitly. This query is used instead of generating the SQL.
Following the sample expression we have been using in this section, the name (and overriding SQL) would be as shown in the following example:
BEGIN_XPATH_TO_SQL_STATEMENT 
name=/CatalogEntry[search(contains(Description/ShortDescription,) and starts-with(CatalogEntryIdentifier/ExternalIdentifier/PartNumber,))] 

base_table=CATENTRY 
sql= 
	SELECT DISTINCT CATENTRY.CATENTRY_ID 
	FROM 
		CATENTRY, CATENTDESC IBM_1 
	WHERE 
		CATENTRY.MARKFORDELETE = 0 AND CATENTRY.PARTNUMBER LIKE '?CatalogEntryIdentifier/ExternalIdentifier/PartNumber?%' AND 
			(CATENTRY.CATENTRY_ID= IBM_1.CATENTRY_ID AND 
			IBM_1.SHORTDESCRIPTION LIKE '%?Description/ShortDescription?%') 
		ORDER BY 
			CATENTRY.CATENTRY_ID 
END_XPATH_TO_SQL_STATEMENT

Element	Attribute	Description
basetable		Identifies the base table
	name	Name of the table
	useAllColumn	Specifies whether all columns in the table are used for parametric search. The attribute values are: true - all table columns are used. The logical property name mapping to the column name is based on the object-relational metadata information unless they are explicitly overridden by the columns sub-elements (described later). The useAllColumn attribute is optional. If not specified, this is the default. false - only those columns identified in the columns sub-elements are considered.
columns		Sub element of basetable, and associated table that identifies the column information for these tables.
	name	The name of the column.
	propertyname	The logical XPath property name, if specified. Otherwise, the property name defined for this column in the object-relational metadata is used.
	searchable	Specifies if this is used for parametric search.
	caseSensitive	Specifies whether the searches should treat the value of this column as case-sensitive or not case-sensitive. true - the content of this column is considered case-sensitive. This is the default. false - the value of this column is not case-sensitive. When the caseSensitive attribute is set to false, the UPPER() SQL function is used in the generated SQL statement to change the case of the column content. Users should be aware of the performance impact of using the not case-sensitive searches. The use of the UPPER() function will cause a full index scan or even a table scan. For Oracle users, IBM recommends that we use a function-based index to improve performance.
	genmode	Specifies the generation mode.
associatedtable		Defines the associated table. Attributes of this element are the same as for the basetable element.
propertytable		Defines the properties table. Only the name attribute is supported.
columns		Sub element of propertytable that identifies the column information for these tables.
	name	The name of the column.
	propertyname	The logical model property name.