Tutorials > Management Center > Add new properties to a WebSphere Commerce service using the data service layer

< Previous | Next >

Customize the WebSphere Commerce schema

In this step we will customize the physical layer by adding tables to contain store warranty and care instruction information to the WebSphere Commerce schema.

The following diagram outlines the changes to the WebSphere Commerce schema:

The above diagram shows the new XWARRANTY and XCAREINSTRUCTION tables, and how they have been related to the existing WebSphere Commerce tables, CATENTRY and CATENTDESC.

• The XWARRANTY table has a foreign key to the CATENTRY table. This allows the Data Service Layer (DSL) to populate data from the XWARRANTY table in the user data element of the CatalogEntry noun.

• The XCAREINSTRUCTION table has a foreign key to the CATENTDESC table to allow DSL to populate data in the attribute element of the CatalogEntryDescription noun part.

• The XCAREINSTRUCTION table has a foreign key to the CATENTRY table to provide support to the DSL search function.

To take advantage of the Data Service Layer's user data (and attribute) support, the following restrictions apply when adding custom database tables:

• The custom tables must have a foreign key to the base table for the noun or noun part that will contain the data. A base table is a table that contains the unique key for the noun or noun part. For example, the CATENTRY table is the base table for the CatalogEntry noun. These relationships are used when fetching or updating a noun or noun part to access the custom data.

• The relationship between the custom table and the base table must be a one-to-one relationship. User data is essentially a map of name-value pairs, where each name represents a database column name and each value is the value in that database column. One-to-many relationships are not supported as each name in the map would collide.

To take advantage of the Data Service Layer's parameter search support, foreign key should also be created to the base table for the noun, when adding a custom table for a noun part. This relationship provides support to the DSL search function and this relationship does not have to be one-to-one.

If you are using workspaces, do the following:

1. Run the utility to enable the tables. See ANT target: CM_updateWorkspacesSchema for more information.

2. Run the following SQL to register the custom access profile in the cmdreg to allow the runtime to return the locking information:

   insert into cmdreg (storeent_id, interfacename, classname) values 
   (0,  'com.ibm.commerce.catalog.facade.server.commands.InsertMoreCatalogEntryDataCmd+MyCompany_All.0', 
   'com.ibm.commerce.foundation.server.command.bod.bom.InsertMoreNounChangeControlMetaDataCmdImpl');
   

3. Complete the following tutorial to enable the warranty tutorial in the workspace:

   • Tutorial: Adding content managed resources to the Catalog service module

4. Run the utility to enable the tables after completing the workspace tutorial. See ANT target: CM_updateWorkspacesSchema for more information.

All foreign key relationships in the custom tables should specify ON DELETE CASCADE

Procedure

1. Connect to the development database..

2. Create the customization tables by running the following SQL statements:

   CREATE TABLE XWARRANTY (CATENTRY_ID BIGINT NOT NULL, WARTERM INTEGER, WARTYPE VARCHAR(32), OPTCOUNTER SMALLINT, CONSTRAINT XWARRANTY_PK PRIMARY KEY(CATENTRY_ID), CONSTRAINT XWARRANTY_FK FOREIGN KEY (CATENTRY_ID) REFERENCES CATENTRY(CATENTRY_ID) ON DELETE CASCADE);
   
   CREATE TABLE XCAREINSTRUCTION (
   CATENTRY_ID BIGINT NOT NULL, LANGUAGE_ID INTEGER NOT NULL, CAREINSTRUCTION VARCHAR(254), OPTCOUNTER SMALLINT, CONSTRAINT XCAREINST_PK PRIMARY KEY (CATENTRY_ID, LANGUAGE_ID), CONSTRAINT XCAREINST_FK1 FOREIGN KEY (CATENTRY_ID, LANGUAGE_ID) REFERENCES CATENTDESC(CATENTRY_ID, LANGUAGE_ID) ON DELETE CASCADE, CONSTRAINT XCAREINST_FK2 FOREIGN KEY (CATENTRY_ID) REFERENCES CATENTRY (CATENTRY_ID) ON DELETE CASCADE
   );
   

   

   CREATE TABLE XWARRANTY (CATENTRY_ID NUMBER NOT NULL, WARTERM INTEGER, WARTYPE VARCHAR(32), OPTCOUNTER SMALLINT, CONSTRAINT XWARRANTY_PK PRIMARY KEY(CATENTRY_ID), CONSTRAINT XWARRANTY_FK FOREIGN KEY (CATENTRY_ID) REFERENCES CATENTRY(CATENTRY_ID));
   
   CREATE TABLE XCAREINSTRUCTION (
   CATENTRY_ID NUMBER NOT NULL, LANGUAGE_ID INTEGER NOT NULL, CAREINSTRUCTION VARCHAR(254), OPTCOUNTER SMALLINT, CONSTRAINT XCAREINSTRUCTION_PK PRIMARY KEY (CATENTRY_ID, LANGUAGE_ID), CONSTRAINT XCAREINSTRUCTION_FK1 FOREIGN KEY (CATENTRY_ID, LANGUAGE_ID) REFERENCES CATENTDESC(CATENTRY_ID, LANGUAGE_ID), CONSTRAINT XCAREINSTRUCTION_FK2 FOREIGN KEY (CATENTRY_ID) REFERENCES CATENTRY (CATENTRY_ID)
   );
   

3. Populate the custom tables with some sample data by running the following SQL statements:

   INSERT INTO XWARRANTY (CATENTRY_ID, WARTERM, WARTYPE) VALUES (10251, 30, 'LIMITED'); 
   INSERT INTO XWARRANTY (CATENTRY_ID, WARTERM, WARTYPE) VALUES (10253, 45, 'COMPREHENSIVE'); 
   INSERT INTO XWARRANTY (CATENTRY_ID, WARTERM, WARTYPE) VALUES (10255, 60, 'LIMITED');
   
   INSERT INTO XCAREINSTRUCTION (CATENTRY_ID, LANGUAGE_ID, CAREINSTRUCTION) VALUES (10251, -1, 'Never use an abrasive cleaner or material on any finished product');
   INSERT INTO XCAREINSTRUCTION (CATENTRY_ID, LANGUAGE_ID, CAREINSTRUCTION) VALUES (10253, -1, 'Avoid soap and water');
   INSERT INTO XCAREINSTRUCTION (CATENTRY_ID, LANGUAGE_ID, CAREINSTRUCTION) VALUES (10255, -1, 'Never use household cleaners');
   
   

   Verify the store is published, to avoid encountering an error when attempting to insert the test data into the database.

< Previous | Next >

+

Search Tips   |   Advanced Search