Configuring the table object builder

Use the TableObjectBuilder to build a table object for directly loading data for a business object into one or more database tables. You can configure this builder by defining sub elements and attribute values in the business object configuration file. By using the table object builder and mediator, you can load data from a single input file into multiple database tables.

For general information about the table object builder and table object mediator, see Data Load utility table-based mediator and builder.

Procedure

Open your business object configuration for editing.
Find the <_config:BusinessObjectBuilder> element. If you are creating a business object configuration instead of editing an existing file, create this <_config:BusinessObjectBuilder> element within the <_config:DataLoader> element in your business object configuration file. Add your <_config:BusinessObjectBuilder> element after the <_config:DataReader> element.
Set the className attribute value to be the com.ibm.commerce.foundation.dataload.businessobjectbuilder.TableObjectBuilder object builder class
For example, the following snippet specifies the table object builder class
```
<_config:BusinessObjectBuilder 
  className="com.ibm.commerce.foundation.dataload.businessobjectbuilder.TableObjectBuilder" >
...
</_config:BusinessObjectBuilder>
```
Configure the tables that the data is to be loaded into. Specify each of the tables that the Data Load utility is to load data into within a separate <_config:Table> element.
Specify the attributes for this <_config:Table> configuration property. The available attributes are:

name

The table name in the database.

deleteKey

It points to a column name in the CSV file. See the description of deleteValue attribute on how to use this attribute.

deleteValue

It is a delete flag. If the data in the column name that is specified by the deleteKey matches the deleteValue, the row is deleted from the database.

excludeUnListedColumns

Indicates whether to exclude any unlisted columns from loading. The values for this element can be true or false. The default value is false and indicates that all columns are to be overwritten.
For example, the following snippet declares the <_config:Table> element for loading attachment data into WebSphere Commerce:
```
<_config:BusinessObjectBuilder 
  className="com.ibm.commerce.foundation.dataload.businessobjectbuilder.TableObjectBuilder" >
  <_config:Table name="ROLE">
  ...
  </_config:Table>
  ...
</_config:BusinessObjectBuilder>
```
Configure the columns for each of the tables that the utility is loading data into.
Specify the configuration for each column separately within the appropriate <_config:Table>. Use the <_config:Column> element to configure a column. Specify the attributes for each column configuration. The available attributes that you can use to configure a column are:
name

The column name in the database.

value

The column value. Typically, it is a key and the real value depends on the valueFrom attribute to indicate how the value is resolved.

valueFrom

It indicates how to resolve the value. The following are the supported valueFrom:

InputData

It indicates that the value is from InputData.

Fixed

The value is a fixed value that is specified directly within the value attribute in this business object configuration file.

IDResolve

The value is from the ID resolver. In this case, the <_config:Column> element might contain an optional <_config:IDResolve> element to indicate how to resolve the ID.

BusinessContext

The value is from the business context section in the data load environment configuration file.

CurrentTimestamp

The value is the current time stamp.

ColumnHandler

The value is from a column handler. In this case, the <_config:ColumnHandler> element specifies the column handler class to compute the column value.

defaultValue

The default column value. This column is used when the valueFrom is InputData but the value is omitted in the input data.

defaultValueFrom
Indicates how to resolve the defaultValue. The following are the supported defaultValueFrom values:
- InputData: Indicates that the defaultValue is from InputData.
- Fixed: Indicates that the defaultValue is a fixed value.
- BusinessContext: The defaultValue is from the business context.
- CurrentTimestamp: The defaultValue is the current time stamp.
- ColumnHandler: The defaultValue is from a column handler. In this case, the <_config:ColumnHandler> element specifies the column handler class to compute the column value.
The following snippet specifies the column configuration for loading data into the MBRROLE database table:
```
<_config:BusinessObjectBuilder 
  className="com.ibm.commerce.foundation.dataload.businessobjectbuilder.TableObjectBuilder" >
  ...
   <_config:Table name="MBRROLE">
    <_config:Column name="MEMBER_ID" value="-2001" valueFrom="Fixed" />
    <_config:Column name="ROLE_ID" value="ROLE_ID" valueFrom="IDResolve" />
    <_config:Column name="ORGENTITY_ID" value="-2001" valueFrom="Fixed" />
  </_config:Table>
  ...
</_config:BusinessObjectBuilder>
```
Optional: Configure the ID resolve for any columns that require this property. Specify this property with the <_config:IDResolve> element.
The attribute definitions for this property are specified as follows:

tableName

The name of the table to be used for the ID resolver.

generateNewKey

true

If the ID cannot be resolved from the database, a new key is generated.

false

If the ID cannot be resolved from the database, an exception is thrown, instead of generating a new key.

primaryKeyColumnName

The name of the primary key column to be resolved. This parameter is optional if the primary key for this table has only one column. Otherwise, it is mandatory.
The following snippet specifies the ID resolve property for the ROLE table.
```
<_config:Column name="ROLE_ID" value="ROLE_ID" valueFrom="IDResolve">
  <_config:IDResolve tableName="ROLE" generateNewKey="true" >
    ...
  </_config:IDResolve>
</_config:Column>
```
Note: If the ID for a column is declared in the attributes for the column property, or is declared in a different preceding element in the business object configuration file, the ID resolve property is not required.
Optional: Configure any unique index column sub elements for the ID resolve element.
use the <_config:UniqueIndexColumn> element to specify these properties. A <_config:IDResolve> property can contain one or more <_config:UniqueIndexColumn> sub elements to help to resolve the ID. The attribute definitions are specified as follows:

name

The unique index column name.

value

The unique index column value. Typically, it is a key and the real value depends on the valueFrom attribute to indicate how the value is resolved.

valueFrom

It indicates how to resolve the value. The following are supported valueFrom:

InputData

It indicates that the value is from InputData.

Fixed

The value is a fixed value.

BusinessContext

The value is from the business context.

CurrentTimestamp

The value is the current time stamp.

ColumnHandler

The value is from a column handler. In this case, the <_config:ColumnHandler> element specifies the column handler class to compute the column value.

skipIDResolveIfNull

Indicates whether to skip the ID resolve when the column value is null. The value for this column can be true or false. The default value is false.
The following snippet specifies the unique index column index for the ID resolve property for the ROLE table.
```
<_config:Column name="ROLE_ID" value="ROLE_ID" valueFrom="IDResolve">
  <_config:IDResolve tableName="ROLE" generateNewKey="true" >
    <_config:UniqueIndexColumn name="NAME" value="roleName" />
  </_config:IDResolve>
</_config:Column>
```
Specify the business object mediator to be the com.ibm.commerce.foundation.dataload.businessobjectmediator.TableObjectMediator class. If you are using the com.ibm.commerce.foundation.dataload.businessobjectbuilder.TableObjectBuilder class as the builder, always use this table object mediator as the business object mediator. This class is the mediator that the Data Load utility uses for all table-based data load.
The following snippet specifies the table object mediator:
```
<_config:BusinessObjectBuilder 
  className="com.ibm.commerce.foundation.dataload.businessobjectbuilder.TableObjectBuilder" >
  ...
  <_config:BusinessObjectMediator 
    className="com.ibm.commerce.foundation.dataload.businessobjectmediator.TableObjectMediator" >
  </_config:BusinessObjectMediator>
</_config:BusinessObjectBuilder>
```

Optional: If you want to include the objects that you are loading within the HCL Commerce search index, specify the search index mediator for the business object that you are loading. You must also specify the data mapping for the object that is to be included within the search index.

To specify the search index mediator and data mapping, create a second business object builder configuration for indexing the business object. Add the second business object builder configuration after the </_config:BusinessObjectBuilder> element that closes the initial business object builder configuration. For a list of the available search index mediators, see Data Load utility business object mediators.

For example, the following snippet specifies the search index mediator for loading categories and the data that is to be included in the search index.


<_config:BusinessObjectBuilder 
  className="com.ibm.commerce.foundation.dataload.businessobjectbuilder.TableObjectBuilder" >
  ...
  <_config:BusinessObjectMediator 
    className="com.ibm.commerce.foundation.dataload.businessobjectmediator.TableObjectMediator" >
  </_config:BusinessObjectMediator>
</_config:BusinessObjectBuilder>

<!--  Catalog Group Search Index -->
<_config:BusinessObjectBuilder 
  className="com.ibm.commerce.foundation.dataload.businessobjectbuilder.BaseBusinessObjectBuilder"  
  packageName="com.ibm.commerce.catalog.facade.datatypes.CatalogPackage" 
  dataObjectType="CatalogGroupType" >
  <_config:DataMapping>
    <!-- The unique reference number of the category -->
    <_config:mapping xpath="CatalogGroupIdentifier/UniqueID" value="GroupUniqueId" />
    <!-- The identifier of the category -->
    <_config:mapping xpath="CatalogGroupIdentifier/ExternalIdentifier/GroupIdentifier" 
      value="GroupIdentifier" />
    <!-- The unique reference number of the store that owns the category -->
    <_config:mapping 
      xpath="CatalogGroupIdentifier/ExternalIdentifier/StoreIdentifier/ExternalIdentifier/NameIdentifier" 
      value="StoreIdentifier" />
    <!-- The unique reference number of the parent category -->
    <_config:mapping xpath="ParentCatalogGroupIdentifier/UniqueID" value="ParentGroupUniqueId" />
    <!-- The identifier of the parent category -->
    <_config:mapping xpath="ParentCatalogGroupIdentifier/ExternalIdentifier/GroupIdentifier" 
      value="ParentGroupIdentifier" />
    <!-- The unique reference number of the store that owns the parent category -->
    <_config:mapping xpath="ParentCatalogGroupIdentifier/ExternalIdentifier/StoreIdentifier/UniqueID" 
      value="ParentStoreUniqueId" />
    <!-- The identifier of the store that owns the parent category -->
    <_config:mapping 
      xpath="ParentCatalogGroupIdentifier/ExternalIdentifier/StoreIdentifier/ExternalIdentifier/NameIdentifier" 
      value="ParentStoreIdentifier" />
    <!-- The identifier of the catalog -->
    <_config:mapping xpath="Attributes/CatalogIdentifier" 
      value="CatalogIdentifier" />
    </_config:DataMapping>
  <_config:BusinessObjectMediator 
    className="com.ibm.commerce.catalog.dataload.mediator.CatalogGroupRelationshipSearchIndexMediator" 
    componentId="com.ibm.commerce.catalog" >
  </_config:BusinessObjectMediator>
</_config:BusinessObjectBuilder>

As a reference to help you configure the data mapping for an object, review the business object configuration files that are provided by default for use with Catalog Upload. To review these files, go to the following directory:

utilities_root/ts.ear/xml/config/com.ibm.commerce.catalog/dataload
workspace_dir\WC\xml\config\com.ibm.commerce.catalog\dataload

The following files include the configuration to run search indexing:

wc-loader-AD-attribute-allowed-values.xml
wc-loader-catalog-entry.xml
wc-loader-catalog-group.xml
wc-loader-catalog-group-description.xml

Results

The table object builder configuration is complete. For example, the following code snippet specifies the configuration for loading member roles:


<_config:BusinessObjectBuilder 
  className="com.ibm.commerce.foundation.dataload.businessobjectbuilder.TableObjectBuilder" >
  
  <_config:Table name="ROLE">
    <_config:Column name="ROLE_ID" value="roleID" valueFrom="IDResolve">
      <_config:IDResolve tableName="ROLE" generateNewKey="true" >
        <_config:UniqueIndexColumn name="NAME" value="roleName" />
      </_config:IDResolve>
    </_config:Column>
    <_config:Column name="NAME" value="roleName" />
    <_config:Column name="DESCRIPTION" value="description" />
  </_config:Table>     
  
  <_config:Table name="MBRROLE">
    <_config:Column name="MEMBER_ID" value="-2001" valueFrom="Fixed" />
    <_config:Column name="ROLE_ID" value="roleID" valueFrom="IDResolve" />
    <_config:Column name="ORGENTITY_ID" value="-2001" valueFrom="Fixed" />
  </_config:Table>
  
  <_config:BusinessObjectMediator 
    className="com.ibm.commerce.foundation.dataload.businessobjectmediator.TableObjectMediator" >
  </_config:BusinessObjectMediator>
  
</_config:BusinessObjectBuilder>

In this code snippet, the unique index column for name to set the value as roleName is included. This property is optional in this case. The value for the column NAME is defined previously in the column configuration property for the Name column