The Product Catalog Database Schema

 

This topic documents the database schema for the WebLogic Commerce Server Product Catalog. This topic includes the following sections:

• The Entity-Relation Diagram

• The Catalog Schema Is Based on Dublin Core Standard

• The WLCS_CATEGORY Database Table

• The WLCS_PRODUCT Database Table

• The WLCS_PRODUCT_CATEGORY Database Table

• The WLCS_PRODUCT_KEYWORD Database Table

• The WLCS_SCHEMA Database Table

• The WLCS_CAT_PROP_* Database Tables for Custom Attributes

• The WLCS_PROP_MD_* Database Tables

• The SQL Scripts Used to Create the Database

• Defined Constraints

 

---

The Entity-Relation Diagram

Figure 2-1 shows the logical Entity-Relation diagram for the WebLogic Commerce Server core product catalog tables in the Commerce database. See the subsequent sections in this chapter for information about the data type syntax.

Figure 2-1 Entity-Relation Diagram for the Core Product Catalog Tables

 

Figure 2-2 shows the database tables that are used to store different types of custom attributes for product items. These custom attributes are optionally defined by a Web site administrator and are implemented as Property Sets. For more on defining custom attributes, see the section Defining Custom Attributes for Items.

Figure 2-2 Custom Attribute Tables for Catalog

 
 

 

---

The Catalog Schema Is Based on Dublin Core Standard

The metadata for items in WebLogic Commerce Server product catalog are based on the Dublin Core Metadata Open Standard. This standard offers a number of advantages for a Web-based catalog:

• Simplicity

  The Dublin Core is intended to be usable by non-catalogers as well as resource description specialists. Most of the elements have commonly understood semantics that is roughly the complexity of a library catalog card.

• Semantic interoperability

  In an Internet environment, disparate description models interfere with the ability to search across discipline boundaries. Promoting a commonly understood set of descriptors that helps to unify other data content standards increases the possibility of semantic interoperability across disciplines.

• International consensus

  Recognition of the international scope of resource discovery on the Web is critical to the development of effective discovery infrastructure. The Dublin Core benefits from active participation and promotion in some 20 countries in North America, Europe, Australia, and Asia.

• Extensibility

  The Dublin Core provides an economical alternative to more elaborate description models such as the full MARC cataloging of the library world. Additionally, Dublin Core includes sufficient flexibility and extensibility to encode the structure and more elaborate semantics inherent in richer description standards

• Metadata modularity on the Web

  The diversity of metadata needs on the Web requires an infrastructure that supports the coexistence of complementary, independently maintained metadata packages. The World Wide Web Consortium (W3C) has begun implementing an architecture for metadata for the Web. The Resource Description Framework, or RDF, is designed to support the many different metadata needs of vendors and information providers. Representatives of the Dublin Core effort are actively involved in the development of this architecture, bringing the digital library perspective to bear on this important component of the Web infrastructure.

For more information about the Dublin Core Metadata Open Standard, please see http://purl.org/dc.

 

---

The Catalog Schema Metadata Tables

This section covers the following database tables:

The WLCS_CATEGORY Database Table

The WLCS_PRODUCT Database Table

The WLCS_PRODUCT_CATEGORY Database Table

The WLCS_PRODUCT_KEYWORD Database Table

The WLCS_SCHEMA Database Table

The WLCS_CATEGORY Database Table

Table 8-1 describes the metadata for the WebLogic Commerce Server WLCS_CATEGORY table. This table is used to store categories in the Commerce database. The descriptions shown in the table reflect the "recommended best practice" for the use of that field by the Dublin Core standard.

The Primary Key is CATEGORY_ID.

 
 

Table 2-1 WLCS_CATEGORY Table Metadata

Column Name	Data Type	Description and Recommendations
CATEGORY_ID	VARCHAR(20)	A unique identifier for a category; the primary key for this table. This field cannot be NULL. All other fields in the WLCS_CATEGORY table can be NULL.
NAME	VARCHAR(50)	The name of the category in the product catalog.
SOURCE	VARCHAR(30)	A reference to a category from which the present category is derived.
LANG	VARCHAR(30)	A language of the intellectual content of the category. The recommended best practice for the values of the language element is defined by RFC 1766, which includes a two-letter Language Code (taken from the ISO 639 standard), such as: en for English; fr for French, or de for German. The language code can, optionally, be followed by a two-letter Country Code (taken from the ISO 3166 standard [ISO3166]). For example, en-uk for English used in the United Kingdom.
RELATION	VARCHAR(30)	A reference to a related category.
COVERAGE	VARCHAR(30)	The extent or scope of the content of the category.
RIGHTS	VARCHAR(30)	Information about rights held in and over the category.
CREATOR	VARCHAR(50)	An entity primarily responsible for making the content of the category.
PUBLISHER	VARCHAR(50)	An entity responsible for making the category available.
CONTRIBUTOR	VARCHAR(50)	An entity responsible for making contributions to the content of the category.
CREATION_DATE	DATE	A date associated with an event in the life cycle of the category. Recommended best practice for encoding the date value is defined in a profile of ISO 8601 and follows the YYYY-MM-DD format.
MODIFIED_DATE	DATE	A date associated with an event in the life cycle of the category, such as an update or insert by the DBLoader program that is provided with WebLogic Commerce Server. The recommended best practice for encoding the date value is defined in a profile of ISO 8601 and follows the YYYY-MM-DD format.
SMALL_IMG_TYPE	NUMBER(3)	A type field of your own design that relates to the graphic. For example, you can implement your own numbering scheme, such as: 0 = display a low resolution graphic for users with low bandwidth. 1 = display a high resolution graphic for users with high bandwidth.
SMALL_IMG_LANG	VARCHAR(30)	The language of the thumbnail image for the category. For related information, see the description of the LANG column.
SMALL_IMG_NAME	VARCHAR(50)	The name of the thumbnail image for the category.
SMALL_IMG_URL	VARCHAR(254)	The URL of the thumbnail image for the category.
SMALL_IMG_ALT_TEXT	VARCHAR(254)	The alternate text to display when the user has their cursor over the thumbnail image for the category, or if they have disabled the display of graphics in their browser settings.
LARGE_IMG_TYPE	NUMBER(3)	A type field of your own design that relates to the graphic. For example, you can implement your own numbering scheme, such as: 0 = display a low resolution graphic for users with low bandwidth. 1 = display a high resolution graphic for users with high bandwidth.
LARGE_IMG_LANG	VARCHAR(30)	The language of the full-size image for the category. For related information, see the description of the LANG column.
LARGE_IMG_NAME	VARCHAR(50)	The name of the full-size image for the category.
LARGE_IMG_URL	VARCHAR(254)	The URL of the full-size image for the category.
LARGE_IMG_ALT_TEXT	VARCHAR(254)	The alternate text to display when the user has their cursor over the full-size image for the category, or if they have disabled the display of graphics in their browser settings.
DISPLAY_JSP_URL	VARCHAR(254)	AThe URL to the JSP used to display the category. For example: /commerce/catalog/includes/ category.jsp
SHORT_DESC	VARCHAR(50)	A short description of the content of the category.
LONG_DESC	VARCHAR(254)	A long description of the content of the category.
PARENT_ID	VARCHAR(20)	The value of the CATEGORY_ID of the parent category in the hierarchy of categories that comprise your product catalog. If this is a top-level user-defined category, the PARENT_ID will be com.beasys.ROOT.

 

See the section The SQL Scripts Used to Create the Database for information about the constraint defined for this table.

The WLCS_PRODUCT Database Table

Table 8-2 describes the metadata for the WebLogic Commerce Server WLCS_PRODUCT table. This table is used to store item records in the Commerce database. The descriptions shown in the table reflect the "recommended best practice" for the use of that field by the Dublin Core standard.

The Primary Key is SKU.

 

Table 2-2 WLCS_PRODUCT Table Metadata

Column Name	Data Type	Description and Recommendations
SKU	VARCHAR(40)	A unique identifier (the "Stock Keeping Unit," or SKU) for a product item. This field is the table's primary key and cannot be NULL. All other fields in the WLCS_PRODUCT table can be NULL.
IN_STOCK	VARCHAR(1)	Provide 1 if in stock, or 0 if out of stock.
VISIBLE	VARCHAR(1)	Indicates whether the item should be displayed to the user. Enter 1 if visible or 0 if not visible. If not specified in the database, the default is 1. See the section Controlling the Visibility of Items in the Catalog for important information about this field.
TAX_CODE	VARCHAR(10)	The code used by the TAXWARE system to identify the specific tax category to which this item belongs.
SHIPPING_CODE	VARCHAR(10)	The code used by the shipping company for this item.
NAME	VARCHAR(100)	A name given to the product item.
SOURCE	VARCHAR(30)	A reference to another product item from which the present item is derived.
LANG	VARCHAR(30)	A language of the intellectual content of the category. The recommended best practice for the values of the language element is defined by RFC 1766, which includes a two-letter Language Code (taken from the ISO 639 standard), such as: en for English; fr for French, or de for German. The language code can, optionally, be followed by a two-letter Country Code (taken from the ISO 3166 standard [ISO3166]). For example, en-uk for English used in the United Kingdom.
RELATION	VARCHAR(30)	A reference to a related product item.
COVERAGE	VARCHAR(30)	The extent or scope of the content of the product item.
RIGHTS	VARCHAR(30)	Information about rights held in and over the item.
FORMAT	VARCHAR(30)	The physical or digital manifestation of the item.
TYPE	VARCHAR(30)	The nature or genre of the content of the item.
MSRP_CURRENCY	VARCHAR(30)	The currency type of the manufacturer's recommended price.
MSRP_AMOUNT	NUMBER(16,4)	The manufacturer's recommended price.
PRICE_CURRENCY	VARCHAR(30)	The currency type of our catalog price for this item.
PRICE_AMOUNT	NUMBER(16,4)	Our current price for this item in the catalog.
ESTIMATE_SHIP_TIME	VARCHAR(100)	Inventory: number of days/weeks before the item can be shipped.
SPECIAL_NOTES	VARCHAR(100)	Inventory related message to display with the item.
CREATOR	VARCHAR(50)	An entity primarily responsible for making the content of the product item.
PUBLISHER	VARCHAR(50)	An entity responsible for making the product item available.
CONTRIBUTOR	VARCHAR(50)	An entity responsible for making contributions to the content of the product item.
CREATION_DATE	DATE	A date associated with an event in the life cycle of the product item. Recommended best practice for encoding the date value is defined in a profile of ISO 8601 and follows the YYYY-MM-DD format.
MODIFIED_DATE	DATE	A date associated with an event in the life cycle of the item, such as an update or insert by the DBLoader program that is provided with WebLogic Commerce Server. The recommended best practice for encoding the date value is defined in a profile of ISO 8601 and follows the YYYY-MM-DD format.
SMALL_IMG_TYPE	NUMBER(3)	A type field of your own design that relates to the graphic. For example, you can implement your own numbering scheme, such as: 0 = display a low resolution graphic for users with low bandwidth. 1 = display a high resolution graphic for users with high bandwidth.
SMALL_IMG_LANG	VARCHAR(30)	The language of the thumbnail image for the item. For related information, see the description of the LANG column.
SMALL_IMG_NAME	VARCHAR(50)	The name of the thumbnail image for the item.
SMALL_IMG_URL	VARCHAR(254)	The URL of the thumbnail image for the category.
SMALL_IMG_ALT_TEXT	VARCHAR(254)	The alternate text to display when the user has their cursor over the thumbnail image for the item, or if they have disabled the display of graphics in their browser settings.
LARGE_IMG_TYPE	NUMBER(3)	A type field of your own design that relates to the graphic. For example, you can implement your own numbering scheme, such as: 0 = display a low resolution graphic for users with low bandwidth. 1 = display a high resolution graphic for users with high bandwidth.
LARGE_IMG_LANG	VARCHAR(30)	The language of the full-size image for the item. For related information, see the description of the LANG column.
LARGE_IMG_NAME	VARCHAR(50)	The name of the full-size image for the item.
LARGE_IMG_URL	VARCHAR(254)	The URL of the full-size image for the item.
LARGE_IMG_ALT_TEXT	VARCHAR(254)	The alternate text to display when the user has their cursor over the full-size image of the item, or if they have disabled the display of graphics in their browser settings.
SUM_DISPLAY_JSP_URL	VARCHAR(254)	The URL to the JSP used to display the item in summary form. For example: /commerce/catalog/includes/ itemsummary.jsp
DET_DISPLAY_JSP_URL	VARCHAR(254)	The URL to the JSP used to display the item in detailed form. For example: /commerce/catalog/includes/ itemdetails.jsp
SHORT_DESC	VARCHAR(254)	A short description of the content of the product item.
LONG_DESC	VARCHAR(2000)	A long description of the content of the product item.

 
 

See the section The SQL Scripts Used to Create the Database for information about the constraint defined for this table.

The WLCS_PRODUCT_CATEGORY Database Table

Table 8-3 describes the metadata for the WebLogic Commerce Server WLCS_PRODUCT_CATEGORY table in the Commerce database. This table is used to join categories and items.

The Primary Keys are SKU and CATEGORY_ID.

Table 2-3 WLCS_PRODUCT_CATEGORY Table Metadata

Column Name	Data Type	Description and Recommendations
SKU	VARCHAR(40)	A unique identifier (the "Stock Keeping Unit," or SKU) for an item. NOT NULL.
CATEGORY_ID	VARCHAR(20)	A unique identifier for a category. NOT NULL.

 

See the section The SQL Scripts Used to Create the Database for information about the constraint defined for this table.

The WLCS_PRODUCT_KEYWORD Database Table

Table 8-4 describes the metadata for the WebLogic Commerce Server WLCS_PRODUCT_KEYWORD table in the Commerce database. This table stores the keywords that you associate with each product item. The keywords enable rapid retrieval of item records via the search functions on the Web site's pages or Administration pages.

The Primary Keys are KEYWORD and SKU.

Table 2-4 WLCS_PRODUCT_KEYWORD Table Metadata

Column Name	Data Type	Description and Recommendations
KEYWORD	VARCHAR(30)	Contains a keyword that you associate with the product item assigned to the unique SKU. NOT NULL. Recommendation: for a given item, select a value from a controlled vocabulary or formal classification scheme implemented in your company.
SKU	VARCHAR(40)	A unique identifier (the "Stock Keeping Unit," or SKU) for an item. NOT NULL.

 

See the section The SQL Scripts Used to Create the Database for information about the two constraints defined for this table.

The WLCS_SCHEMA Database Table

Table 2-5 describes the WLCS_SCHEMA table. This table stores property set definitions.

The Primary Keys are SCHEMA_GROUP_NAME and SCOPE_NAME.

Table 2-5 WLCS_SCHEMA Table Metadata

Column Name	Data Type	Description and Recommendations
SCHEMA_GROUP_NAME	VARCHAR(100)	The type of object this schema is used for.
SCOPE_NAME	VARCHAR(100)	The application name since it is defining names for the application.
DESCRIPTION	VARCHAR(254)	A description of the schema.
SCHEMA_ID	NUMBER(15)	A system generated number used throughout the application.

 

 

---

The WLCS_CAT_PROP_* Database Tables for Custom Attributes

This section describes several database tables that are used to store different types of custom attributes for product items. These custom attributes are optionally defined by a Web site administrator and are implemented as Property Sets. For more information about defining custom attributes, see the section Defining Custom Attributes for Items.

• The WLCS_CAT_ENTITY_ID Database Table

• The WLCS_CAT_PROP_ID Database Table

• The WLCS_CAT_PROP_BOOLEAN Database Table

• The WLCS_CAT_PROP_INTEGER Database Table

• The WLCS_CAT_PROP_FLOAT Database Table

• The WLCS_CAT_PROP_TEXT Database Table

• The WLCS_CAT_PROP_DATETIME Database Table

• The WLCS_CAT_PROP_USER_DEFINED Database Table

In this section, the database tables for custom attributes are listed alphabetically, as a data dictionary.

The WLCS_CAT_ENTITY_ID Database Table

Table 8-5 describes the metadata for the WebLogic Commerce Server WLCS_CAT_ENTITY_ID table in the Commerce database. This table stores unique identification numbers for configurable entities.

The Primary Keys are JNDI_HOME_NAME and PK_STRING.

Table 2-6 WLCS_CAT_ENTITY_ID Table Metadata

Column Name	Data Type	Description and Recommendations
JNDI_HOME_NAME	VARCHAR(254)	The class name for the configurable entity. Either: com.beasys.ebusiness.catalog.ProductCategory or com.beasys.ebusiness.catalog.ProductItem
PK_STRING	VARCHAR(480)	The primary key string for the category or item.
ENTITY_ID	NUMBER(15)	NOT NULL

 

The WLCS_CAT_PROP_BOOLEAN Database Table

Table 8-6 describes the metadata for the WebLogic Commerce Server WLCS_CAT_PROP_BOOLEAN table in the Commerce database. This table stores Boolean property values that are associated with configurable entities.

Table 2-7 WLCS_CAT_PROP_BOOLEAN Table Metadata

Column Name	Data Type	Description and Recommendations
PROPERTY_ID	NUMBER(15)	The identifier for each Boolean property.
VALUE	NUMBER(3)	The value for each Boolean property identifier.

 

The WLCS_CAT_PROP_DATETIME Database Table

Table 8-7 describes the metadata for the WebLogic Commerce Server WLCS_CAT_PROP_DATETIME table in the Commerce database. This table stores timestamp property values that are associated with configurable entities.

Table 2-8 WLCS_CAT_PROP_DATETIME Table Metadata

Column Name	Data Type	Description and Recommendations
PROPERTY_ID	NUMBER(15)	The identifier for each date and time property.
VALUE	DATE	The value for each data and time property identifier.

 

The WLCS_CAT_PROP_FLOAT Database Table

Table 8-8 describes the metadata for the WebLogic Commerce Server WLCS_CAT_PROP_FLOAT table in the Commerce database. This table stores integer property values that are associated with configurable entities.

Table 2-9 WLCS_CAT_PROP_FLOAT Table Metadata

Column Name	Data Type	Description and Recommendations
PROPERTY_ID	NUMBER(15)	The identifier for each float property.
VALUE	NUMBER	The value associated with each float property identifier.

 

The WLCS_CAT_PROP_ID Database Table

Table 8-9 describes the metadata for the WebLogic Commerce Server WLCS_CAT_PROP_ID table in the Commerce database. This table stores unique identification numbers for scoped property names that are associated with configurable entities.

Table 2-10 WLCS_CAT_PROP_ID Table Metadata

Column Name	Data Type	Description and Recommendations
ENTITY_ID	NUMBER(15)	A system generated value and foreign key to the WLCS_ENTITY_ID column.
SCOPE_NAME	VARCHAR(100)	This column may be null. If this property is defined in a property set, then the SCOPE_NAME will match the SCHEMA_NAME for that property set in the WLCS_SCHEMA table.
PROPERTY_NAME	VARCHAR(100)	The name of the property.
PROPERTY_TYPE	NUMBER(3)	This column identifies the type of property we are dealing with (for example, boolean, integer, float, text, and so on).
PROPERTY_META_DATA_ID	NUMBER(15)	The identifier for the Property Meta Data information. Again, we use the PROPERTY_TYPE column to identify which type of Property Meta Data we are looking at (for example, boolean, integer, and so on).
SCHEMA_HAS_CHANGED	NUMBER(3)	A flag informing to identify whether anything in the WLCS_SCHEMA or WLCS_PROP_MD_xxx tables has changed. If so, then certain cleanup activities must be performed prior to using this property next time
PROPERTY_ID	NUMBER(15)	The property identifier is a unique system generated number.

 

The WLCS_CAT_PROP_INTEGER Database Table

Table 8-10 describes the metadata for the WebLogic Commerce Server WLCS_CAT_PROP_INTEGER table in the Commerce database. This table stores integer property values that are associated with configurable entities.

Table 2-11 WLCS_CAT_PROP_INTEGER Table Metadata

Column Name	Data Type	Description and Recommendations
PROPERTY_ID	NUMBER(15)	The identifier of the integer property.
VALUE	NUMBER(20)	The value associated with the integer property.

 

The WLCS_CAT_PROP_TEXT Database Table

Table 8-11 describes the metadata for the WebLogic Commerce Server WLCS_CAT_PROP_TEXT table in the Commerce database. This table stores text property values that are associated with configurable entities.

Table 2-12 WLCS_CAT_PROP_TEXT Table Metadata

Column Name	Data Type	Description and Recommendations
PROPERTY_ID	NUMBER(15)	The identifier of the text property.
VALUE	VARCHAR(254)	The value associated with the text property.

 

The WLCS_CAT_PROP_USER_DEFINED Database Table

Table 2-13 describes the metadata for the WebLogic Commerce Server WLCS_CAT_PROP_USER_DEFINED table in the Commerce database. This table stores user-defined (object) property values that are associated with configurable entities.

Table 2-13 WLCS_CAT_PROP_USER_DEFINED Table Metadata

Column Name	Data Type	Description and Recommendations
PROPERTY_ID	NUMBER(15)	The identifier of the user-defined property.
VALUE	BLOB	The value associated with the user-defined property.

 

 

---

The WLCS_PROP_MD_* Database Tables

• The WLCS_PROP_MD Database Table

• The WLCS_PROP_MD_BOOLEAN Database Table

• The WLCS_PROP_MD_DATETIME Database Table

• The WLCS_PROP_MD_FLOAT Database Table

• The WLCS_PROP_MD_INTEGER Database Table

• The WLCS_PROP_MD_TEXT Database Table

• The WLCS_PROP_MD_USER_DEFINED Database Table

The WLCS_PROP_MD Database Table

Table 2-14 describes the WLCS_PROP_MD table. This table stores information about defined properties in a property set.

The Primary Keys are SCHEMA_ID and PROPERTY_NAME.

Table 2-14 WLCS_PROP_MD Table Metadata

Column Name	Data Type	Description and Recommendations
SCHEMA_ID	NUMBER(15)	A foreign key to the WLCS_SCHEMA table.
PROPERTY_NAME	VARCHAR(100)	The name of a property.
DESCRIPTION	VARCHAR(254)	A description of the property.
IS_RESTRICTED	NUMBER(3)	If set TRUE, the value of the property is constrained to a set of values. 0 equates to FALSE and 1 equates to TRUE.
IS_EXPLICIT	NUMBER(3)	If set TRUE, the property value may be coming from an external source. 0 equates to FALSE and 1 equates to TRUE.
IS_MULTIVALUED	NUMBER(3)	Some properties may have more than one value. 0 equates to FALSE and 1 equates to TRUE.
PROPERTY_TYPE	NUMBER(3)	Defines the property type (boolean, text and so on).
PROPERTY_META_DATA_ID	NUMBER(15)	The primary key is a unique, system-generated value.

 

The WLCS_PROP_MD_BOOLEAN Database Table

Table 2-15 describes the WLCS_PROP_MD_BOOLEAN table. This table stores property set definitions for the boolean property type.

The Primary Key is PROPERTY_META_DATA_ID.

Table 2-15 WLCS_PROP_MD_BOOLEAN Table Metadata

Column Name	Data Type	Description and Recommendations
PROPERTY_META_DATA_ID	NUMBER(15)	A unique identifier for this Property metadata and foreign key to the WLCS_PROP_MD table.
VALUE	NUMBER(3)	The value associated with the Property metadata.
IS_DEFAULT	NUMBER(3)	This flag tells us whether or not the VALUE column is the default value for this piece of Property metadata. 0 equates to FALSE and 1 equates to TRUE.

 
 

The WLCS_PROP_MD_DATETIME Database Table

Table 2-16 describes the WLCS_PROP_MD_DATETIME table. This table stores property set definitions for the date and time property type.

The Primary Key is PROPERTY_META_DATA_ID.

Table 2-16 WLCS_PROP_MD_DATETIME Table Metadata

Column Name	Data Type	Description and Recommendations
PROPERTY_META_DATA_ID	NUMBER(20)	A unique identifier for this Property metadata.
VALUE	DATE	The value associated with the Property metadata.
IS_DEFAULT	NUMBER(3)	This flag tells us whether or not the VALUE column is the default value for this piece of Property metadata. 0 equates to FALSE and 1 equates to TRUE.

 

The WLCS_PROP_MD_FLOAT Database Table

Table 2-17 describes the WLCS_PROP_MD_FLOAT table. This table stores property set definitions for the float property type.

The Primary Key is PROPERTY_META_DATA_ID.

Table 2-17 WLCS_PROP_MD_FLOAT Table Metadata

Column Name	Data Type	Description and Recommendations
PROPERTY_META_DATA_ID	NUMBER(15)	A unique identifier for this Property metadata.
VALUE	NUMBER	The value associated with the Property metadata.
IS_DEFAULT	NUMBER(3)	This flag tells us whether or not the VALUE column is the default value for this piece of Property metadata. 0 equates to FALSE and 1 equates to TRUE.

 

The WLCS_PROP_MD_INTEGER Database Table

Table 2-18 describes the WLCS_PROP_MD_INTEGER table. This table stores property set definitions for the Integer property type.

The Primary Key is PROPERTY_META_DATA_ID.

Table 2-18 WLCS_PROP_MD_INTEGER Table Metadata

Column Name	Data Type	Description and Recommendations
PROPERTY_META_DATA_ID	NUMBER(15)	A unique identifier for this Property metadata.
VALUE	NUMBER(20)	The value associated with the Property metadata.
IS_DEFAULT	NUMBER(3)	This flag tells us whether or not the VALUE column is the default value for this piece of Property metadata. 0 equates to FALSE and 1 equates to TRUE.

 

The WLCS_PROP_MD_TEXT Database Table

Table 2-19 describes the WLCS_PROP_MD_TEXT table. This table stores property set definitions for the text property type.

The Primary Key is PROPERTY_META_DATA_ID.

Table 2-19 WLCS_PROP_MD_TEXT Table Metadata

Column Name	Data Type	Description and Recommendations
PROPERTY_META_DATA_ID	NUMBER(15)	A unique identifier for this Property metadata.
VALUE	VARCHAR(254)	The value associated with the Property metadata.
IS_DEFAULT	NUMBER(3)	This flag tells us whether or not the VALUE column is the default value for this piece of Property metadata. 0 equates to FALSE and 1 equates to TRUE.

 
 

The WLCS_PROP_MD_USER_DEFINED Database Table

Table 2-20 describes the WLCS_PROP_MD_USER_DEFINED table. This table stores property set definitions for any user defined property type.

The Primary Key is PROPERTY_META_DATA_ID.

Table 2-20 WLCS_PROP_MD_USER_DEFINED Table Metadata

Column Name	Data Type	Description and Recommendations
PROPERTY_META_DATA_ID	NUMBER(15)	A unique identifier for this Property metadata.
VALUE	BLOB	The value associated with the Property metadata.
IS_DEFAULT	NUMBER(3)	This flag tells us whether or not the VALUE column is the default value for this piece of Property metadata. 0 equates to FALSE and 1 equates to TRUE.

 

 

---

The SQL Scripts Used to Create the Database

The database schemas for the WebLogic Personalization Server, WebLogic Commerce Server and BEA's Campaign Manager for WebLogic are all created by executing the create_all script for the target database environment.

Cloudscape

For Cloudscape, execute one of the following:

• WL_COMMERCE_HOME\db\cloudscape\3.5.1\create_all.bat (Windows)

• WL_COMMERCE_HOME/db/cloudscape/3.5.1/create_all.sh (UNIX)

  Script Name	Description
  create_all.bat	The execution of this script will create the WLPS, WLCS and Campaign Manager database schema.
  create_all.sh	The execution of this script will create the WLPS, WLCS and Campaign Manager database schema.
  create_campaign.sql	Creates the Campaign Manager specific database objects (e.g., tables, indexes and constraints).
  create_common.sql	Creates the database objects which are common to WLPS and WLCS.
  create_mail_ad.sql	Creates all the database objects used by the mail messaging component.
  create_wlcs.sql	Creates all the database objects for WLCS (including Catalog and Order Management).
  create_wlps.sql	Creates all the database object for WLPS.
  drop_campaign.sql	Drops all database objects associated with Campaign Manager.
  drop_common.sql	Drops the database objects which are common between WLPS and WLCS.
  drop_mail_ad.sql	Drops the database objects used by the mail messaging component.
  drop_wlcs.sql	Drops the database objects associated with WLCS.
  drop_wlps.sql	Drops the database objects associated with WLPS.
  insert_common.sql	Inserts core data into the common tables between WLPS and WLCS.
  insert_wlcs.sql	Inserts core data into some of the WLCS tables.
  insert_wlcs_sample_catalog.sql	Inserts sample data into the product catalog.
  insert_wlcs_sample_customer.sql	Inserts sample customer information into WLCS tables.
  insert_wlcs_sample_data.sql	Inserts sample data into various WLCS tables.
  insert_wlps.sql	Inserts core data into WLPS tables.
  insert_wlps_sample_data.sql	Inserts sample data into various WLPS tables.

  
   

Oracle

For Oracle, from the command line, move to the following directory:

WL_COMMERCE_HOME/db/oracle/8.1.6

After logging into SQL*Plus, simply execute the create_all.sql script (e.g., @create_all).

Script Name	Description
create_campaign.sql	Creates the Campaign Manager specific database objects (e.g., tables, indexes and constraints).
create_common.sql	Creates the database objects which are common to WLPS and WLCS.
create_mail_ad.sql	Creates all the database objects used by the mail messaging component.
create_wlcs.sql	Creates all the database objects for WLCS (including Catalog and Order Management).
create_wlps.sql	Creates all the database object for WLPS.
drop_campaign.sql	Drops all database objects associated with Campaign Manager.
drop_common.sql	Drops the database objects which are common between WLPS and WLCS.
drop_mail_ad.sql	Drops the database objects used by the mail messaging component.
drop_wlcs.sql	Drops the database objects associated with WLCS.
drop_wlps.sql	Drops the database objects associated with WLPS.
insert_common.sql	Inserts core data into the common tables between WLPS and WLCS.
insert_wlcs.sql	Inserts core data into some of the WLCS tables.
insert_wlcs_sample_catalog.sql	Inserts sample data into the product catalog.
insert_wlcs_sample_customer.sql	Inserts sample customer information into WLCS tables.
insert_wlcs_sample_data.sql	Inserts sample data into various WLCS tables.
insert_wlps.sql	Inserts core data into WLPS tables.
insert_wlps_sample_data.sql	Inserts sample data into various WLPS tables.
install_report.sql	This script is used to summarize the database installation. Information such as the number of tables, indexes, etc., is displayed.
statistics.sql	This script is used in computing statistics on various database objects (e.g., tables and indexes) in an Oracle environment.

 

SQL Server

For SQL Server, you must first edit the create_all.bat file and properly identify the values for the variables used in identifying the target database environment (for example, user_id, password and server). Once the variables have been set properly, execute create_all.bat from the command line.

Script Name	Description
create_all.bat	The execution of this script will create the WLPS, WLCS and Campaign Manager database schema.
create_campaign.sql	Creates the Campaign Manager specific database objects (e.g., tables, indexes and constraints).
create_common.sql	Creates the database objects which are common to WLPS and WLCS.
create_mail_ad.sql	Creates all the database objects used by the mail messaging component.
create_wlcs.sql	Creates all the database objects for WLCS (including Catalog and Order Management).
create_wlps.sql	Creates all the database object for WLPS.
drop_campaign.sql	Drops all database objects associated with Campaign Manager.
drop_common.sql	Drops the database objects which are common between WLPS and WLCS.
drop_mail_ad.sql	Drops the database objects used by the mail messaging component.
drop_wlcs.sql	Drops the database objects associated with WLCS.
drop_wlps.sql	Drops the database objects associated with WLPS.
insert_common.sql	Inserts core data into the common tables between WLPS and WLCS.
insert_wlcs.sql	Inserts core data into some of the WLCS tables.
insert_wlcs_sample_catalog.sql	Inserts sample data into the product catalog.
insert_wlcs_sample_customer.sql	Inserts sample customer information into WLCS tables.
insert_wlcs_sample_data.sql	Inserts sample data into various WLCS tables.
insert_wlps.sql	Inserts core data into WLPS tables.
insert_wlps_sample_data.sql	Inserts sample data into various WLPS tables.
readme.txt	Documentation outlining the appropriate steps necessary for proper installation of the WLPS, WLCS and Campaign Manager database schema.

 

 

---

Defined Constraints

In each create-catalog-*.sql file, the database tables described earlier in this chapter are created. In addition, the SQL files define constraints.Table 2-21 shows the table name and describes the constraint(s) defined for it.

Note: The sample SQL statements in the attached document table are from the create-catalog-oracle.sql file. The syntax is different for Cloudscape. Except where noted, the index and the effect of each constraint is the same.

Table 2-21 Constraints Defined on Product Catalog Database Tables

Table Name	Constraints as Defined in create-catalog-oracle.sql
WLCS_CATEGORY	The WLCS_CATEGORY_PARENT_FK constraint enforces the deletion of subcategories when the current category is deleted. (Cascading deletes are not supported in Cloudscape, however.) The constraint for the schema in Oracle is: ALTER TABLE WLCS_CATEGORY ADD CONSTRAINT WLCS_CATEGORY_PARENT_FK1 FOREIGN KEY (PARENT_ID) REFERENCES WLCS_CATEGORY (CATEGORY_ID) ON DELETE CASCADE;
WLCS_PRODUCT	The WLCS_PROD_ITEM_PK constraint sets the SKU as the primary key, which must be unique and cannot be NULL. The constraint is: CONSTRAINT WLCS_PROD_ITEM_PK PRIMARY KEY (SKU)
WLCS_PRODUCT_CATEGORY	The WLCS_PRODUCT_CATEGORY_PK constraint sets the CATEGORY_ID and SKU fields as the primary keys in the mapping table. For example: CONSTRAINT WLCS_PRODUCT_CATEGORY_PK PRIMARY KEY (CATEGORY_ID,SKU) This table is used to join categories with items.
WLCS_PRODUCT_KEYWORD	Two constraints are defined for this table. The first constraint, WLCS_PRODUCT_KEYWORD_PK, sets the KEYWORD and SKU fields as the primary keys. For example: CONSTRAINT WLCS_PRODUCT_KEYWORD_PK PRIMARY KEY (KEYWORD,SKU) The second constraint, WLCS_PRODUCT_KEYWORD_FK2, sets the SKU field as a foreign key that references the SKU in the WLCS_PRODUCT table. For example: CONSTRAINT WLCS_PRODUCT_KEYWORD_FK2 FOREIGN KEY (SKU) REFERENCES WLCS_PRODUCT(SKU) This table stores the keywords that you associate with each product item. The keywords enable rapid retrieval of item records from the database (or cache) via the search functions on the Web site's pages or Administration pages.