The WebLogic Personalization Server Database Schema

 

This chapter documents the database schema for the WebLogic Personalization Server. This topic includes the following sections:

• The Entity-Relation Diagram

• List of Tables Comprising the WebLogic Personalization Server

• The Personalization Server Data Dictionary

• The SQL Scripts Used to Create the Database

• SQL Server

 

---

The Entity-Relation Diagram

Figure 1-25 shows the logical Entity-Relation diagram for the WebLogic Personalization Server database. See the subsequent sections in this chapter for information about the data type syntax.

Figure 1-25 Entity-Relation Diagram for the WebLogic Personalization Server

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

 

---

List of Tables Comprising the WebLogic Personalization Server

 

The WebLogic Personalization Server is comprised of the following tables. In this list, the tables are sorted by functionality:

Ads and Placeholders tables

The AD_BUCKET Database Table

The AD_COUNT Database Table

The PLACEHOLDER Database Table

The PLACEHOLDER _PREVIEW Database Table

Documentation Management tables

The WLCS_COLUMN_INFORMATION Database Table

The WLCS_DOCUMENT Database Table

The WLCS_DOCUMENT_METADATA Database Table

Rule Editor tables

The RULESET Database Table

The WLCS_RULESET_DEFINITION Database Table

User Management tables

The WLCS_GROUP Database Table

The WLCS_GROUP_HIERARCHY Database Table

The WLCS_GROUP_PERSONALIZATION Database Table

The WLCS_UNIFIED_PROFILE_TYPE Database Table

The WLCS_USER Database Table

The WLCS_USER_GROUP_CACHE Database Table

The WLCS_USER_GROUP_HIERARCHY Database Table

The WLCS_USER_PERSONALIZATION Database Table

The WLCS_UIDS Database Table

Common tables used by both WebLogic Personalization Server and WebLogic Commerce Server

The WLCS_CATEGORIES Database Table

The WLCS_SCHEMA Database Table

The WLCS_ENTITY_ID Database Table

The WLCS_BOOKMARKS Database Table

The WLCS_IS_ALIVE Database Table

The WLCS_LDAP_CONFIG Database Table

The WLCS_SEQUENCER Database Table

The WLCS_TODO Database Table

The WLCS_USER_PERSONALIZATION Database Table

The WLCS_UUP_EXAMPLE Database Table

The WLCS_PROP_MD Database Table

The WLCS_PROP_MD_BOOLEAN Database Table

The WLCS_PROP_MD_INTEGER Database Table

The WLCS_PROP_MD_FLOAT Database Table

The WLCS_PROP_MD_TEXT Database Table

The WLCS_PROP_MD_DATETIME Database Table

The WLCS_PROP_MD_USER_DEFINED Database Table

The WLCS_PROP_ID Database Table

The WLCS_PROP_BOOLEAN Database Table

The WLCS_PROP_INTEGER Database Table

The WLCS_PROP_FLOAT Database Table

The WLCS_PROP_TEXT Database Table

The WLCS_PROP_DATETIME Database Table

The WLCS_PROP_USER_DEFINED Database Table

 

---

The Personalization Server Data Dictionary

In this section, the WebLogic Personalization Server schema tables are arranged alphabetically as a data dictionary.

Note: Even though the following documentation references "foreign keys" to various tables, these constraints do not currently exist in this release of WebLogic Personalization Server. However, they will be (available in future releases) in place in future versions of WebLogic Personalization Server and we want you to be aware of these relationships now.

The AD_BUCKET Database Table

Table 1-1 describes the AD_BUCKET table. This table maintains content queries for ads.

The Primary Key is AD_BUCKET_ID.

Table 1-1 AD_BUCKET Table Metadata

Column Name	Data Type	Description and Recommendations
AD_BUCKET_ID	NUMBER(15)	PK-a unique, system-generated number used as the record identifier.
USER_ID	VARCHAR(50)	FK-foreign key to the WLCS_USER.IDENTIFIER column.
PLACEHOLDER_NAME	VARCHAR(50)	FK-foreign key to PLACEHOLDER.PLACEHOLDER_NAME.
CONTEXT_UID	VARCHAR(50)	The scenario unique identifier.
CONTAINER_UID	VARCHAR(50)	The campaign unique identifier.
CONTAINER_TYPE	VARCHAR(50)	Identifies the service associated with the CONTAINER_UID.
WEIGHT	NUMBER(15)	A weighting scheme used in prioritizing one placeholder over another.
VIEW_COUNT	NUMBER(15)	Disabled. Reserved for future use.
CREATION_DATE	DATE	The date and time this record was created.
AD_QUERY	CLOB	The actual content query.

 

The AD_COUNT Database Table

Table 1-2 describes the AD_COUNT table. This table tracks the number of times the ads are displayed and clicked through.

The Primary Keys are AD_IDENTIFIER and CONTAINER_UID.

 

Table 1-2 AD_COUNT Table Metadata

Column Name	Data Type	Description and Recommendations
AD_IDENTIFIER	NUMBER(15)	A unique, system-generated number used as a record identifier.
CONTAINER_UID	VARCHAR(50)	The campaign unique identifier.
DISPLAY_COUNT	NUMBER(15)	The number of times the ad has been displayed.
CLICK_THROUGH_COUNT	NUMBER(15)	The number of times the ad has been clicked on.

 

The PLACEHOLDER Database Table

Table 1-3 describes the PLACEHOLDER table. This table maps placeholder and content bucket services (e.g., ad bucket service).

The Primary Key is PLACEHOLDER_NAME.

 

Table 1-3 PLACEHOLDER Table Metadata

Column Name	Data Type	Description and Recommendations
PLACEHOLDER_NAME	VARCHAR(50)	PK-a textual name given to the placeholder to uniquely identify it from other placeholders.
CONTENT_TYPE	VARCHAR(20)	Identifies the type of service to work with (e.g., ad).
MIX_GLOBALS	NUMBER(1)	Determines whether or not this placeholder is to be used with a specific campaign or not. 0 = do not mix with other adshis placeholder is specific to certain campaign(s). 1 = mix with all ads.
DESCRIPTION	VARCHAR(254)	A description of the placeholder and its purpose.
XML_DEFINITION	CLOB	The content used to define the placeholder.

 

The PLACEHOLDER _PREVIEW Database Table

Table 1-4 describes the PLACEHOLDER_PREVIEW table. This table is used as a mechanism to hold the placeholder for previewing purposes only.

The Primary Key is PPREVIEW_ID.

 

Table 1-4 PLACEHOLDER_PREVIEW Table Metadata

Column Name	Data Type	Description and Recommendations
PREVIEW_ID	NUMBER(15)	PK-a unique, system-generated number used as the record identifier.
XML_DEFINITION	CLOB	The representation of the expression to be previewed.

 

The WLCS_BOOKMARKS Database Table

Table 1-5 describes the WLCS_BOOKMARKS table. This table is used by the Example portal and is not used except for demonstration purposes. It contains information used in the Bookmark portlet.

The Primary Key is NAME and OWNER.

Table 1-5 WLCS_BOOKMARKS Table Metadata

Column Name	Data Type	Description and Recommendations
NAME	VARCHAR(150)	The name of the bookmark.
OWNER	VARCHAR(150)	The owner of the bookmark.
URL	VARCHAR(50)	The URL of the bookmark.

 
 

The WLCS_CATEGORIES Database Table

Table 1-6 describes the WLCS_CATEGORIES table. This table is used to store category information for the portal portion of the WebLogic Personalization Server application.

Note: The CATEGORY feature has not been implemented at this time and, therefore, this table is not being used/populated.

The Primary Key is NID.

Table 1-6 WLCS_CATEGORIES

Column Name	Data Type	Description and Recommendations
NID	NUMBER(15)	Category identifier.
PORTAL_NID	NUMBER(15)	The Portal identifier. This column is a foreign key to the NID column of the WLCS_PORTAL_DEFINITION table.
NAME	VARCHAR(100)	The name for the category.
ICON_URL	VARCHAR(100)	The URL pointing to the icon associated with the category. This may be null.
CATEGORY_ORDER	NUMBER(5)	The sequence number identifying the order of display.

 
 

The WLCS_COLUMN_INFORMATION Database Table

Table 1-7 describes the WLCS_COLUMN_INFORMATION table. This table is used to store column definition information for each portal and category.

The Primary Key is comprised of PORTAL_NID, CATEGORY_NID and COLUMN_ORDER.

 

Table 1-7 WLCS_COLUMN_INFORMATION

Column Name	Data Type	Description and Recommendations
PORTAL_NID	NUMBER(15)	The Portal identifier. This column is a foreign key to the NID column of the WLCS_PORTAL_DEFINITION table.
CATEGORY_NID	NUMBER(15)	The Category identifier.
COLUMN_ORDER	NUMBER(5)	A sequence number identifying the display sequence for this column. Starting at the left-most part of the screen the COLUMN_ORDER would be 1.
COLUMN_WIDTH	NUMBER(5)	The value entered here is a percentage of the screen width. An example would be 30. This represents how wide this particular portal column is to be (30% of the screen).

 

The WLCS_DOCUMENT Database Table

Table 1-8 describes the WLCS_DOCUMENT table. This table is used to store information pertinent to each document used within the WebLogic Personalization Server.

The Primary Key is ID.

Table 1-8 WLCS_DOCUMENT Table Metadata

Column Name	Data Type	Description and Recommendations
ID	VARCHAR(500)	The identifier of the document. This specifies the relative path (case sensitive using forward slashes) to the actual file.
DOCUMENT_SIZE	NUMBER(15)	The size of the document in bytes.
VERSION	NUMBER(15)	The version of the document.
AUTHOR	VARCHAR(50)	The author's name of this document.
CREATION_DATE	DATE	The date this document was created in the system.
LOCKED_BY	VARCHAR(50)	This column identifies who has this document locked for edits or updates.
MODIFIED_DATE	DATE	This tells you when this document record was last modified.
MODIFIED_BY	VARCHAR(50)	This column stores the name of the individual who last modified the document record.
DESCRIPTION	VARCHAR(50)	A description of the document.
COMMENTS	VARCHAR(50)	An area to store miscellaneous notes about the document.
MIME_TYPE	VARCHAR(100)	This column identifies which MIME type (or file type) is associated with this document. This is supposed to be MIME 1.0.

 

The WLCS_DOCUMENT_METADATA Database Table

Table 1-9 describes the WLCS_DOCUMENT_METADATA table. This table is used to store user-defined properties associated with each document.

The Primary Key is ID and NAME.

Table 1-9 WLCS_DOCUMENT_METADATA Table Metadata

Column Name	Data Type	Description and Recommendations
ID	VARCHAR(500)	The document identifier. This is a foreign key to the ID column of the WLCS_DOCUMENT table.
NAME	VARCHAR(240)	The metadata name.
VALUE	VARCHAR(2000)	The value to be associated with the metadata name (NAME).
STATE	VARCHAR(50)	The current state of this metadata property. This is used by Interwoven and can be set to null.

 
 

The WLCS_ENTITY_ID Database Table

Table 1-10 describes the WLCS_ENTITY_ID table. Any ConfigurableEntity within the system will have an entry in this table.

The Primary Key is comprised of JNDI_HOME_NAME and PK_STRING.

Table 1-10 WLCS_ENTITY_ID Table Metadata

Column Name	Data Type	Description and Recommendations
JNDI_HOME_NAME	VARCHAR(100)	Defines what type of ConfigurableEntity this is.
PK_STRING	VARCHAR(200)	Unique identifier within the ConfigurableEntity.
ENTITY_ID	NUMBER(15)	A sequence-generated number providing a unique identifier used throughout the system (in the Property tables and so on).

 

The WLCS_GROUP Database Table

Table 1-11 describes the WLCS_GROUP table. This table is used to maintain each of the various Group identifiers.

The Primary Key is comprised of IDENTIFIER.

Table 1-11 WLCS_GROUP Table Metadata

Column Name	Data Type	Description and Recommendations
IDENTIFIER	VARCHAR(50)	The group name. This column is a foreign key to the PK_STRING column in the WLCS_ENTITY_ID table.

 

The WLCS_GROUP_HIERARCHY Database Table

Table 1-12 describes the WLCS_GROUP_HIERARCHY table. This table stores relationship information between groups.

The Primary Key is comprised of PARENT_ID and CHILD_ID.

Table 1-12 WLCS_GROUP_HIERARCHY Table Metadata

Column Name	Data Type	Description and Recommendations
PARENT_ID	NUMBER(15)	The parent group identifier. This column is a foreign key to the ENTITY_ID column in the WLCS_ENTITY_ID table.
CHILD_ID	NUMBER(15)	The child group identifier. This column is a foreign key to the ENTITY_ID column in the WLCS_ENTITY_ID table.

 

The WLCS_GROUP_PERSONALIZATION Database Table

Table 1-13 describes the WLCS_GROUP_PERSONALIZATION table. Portals can be associated to groups and this table helps establish those relationships and maintain specific information for the group.

The Primary Key is comprised of PORTAL_NID, CATEGORY_NID, PORTLET_NID and GROUP_NID.

Table 1-13 WLCS_GROUP_PERSONALIZATION

Column Name	Data Type	Description and Recommendations
PORTAL_NID	NUMBER(15)	The portal identifier. This column is a foreign key to the NID column of the WLCS_PORTAL_DEFINITION table.
CATEGORY_NID	NUMBER(15)	The category identifier. This column is a foreign key to the NID column of the WLCS_CATEGORIES table.
PORTLET_NID	NUMBER(15)	The portlet identifier. This column is a foreign key to the NID column of the WLCS_PORTLET_DEFINITION table.
GROUP_NID	NUMBER(15)	The group identifier. This column is a foreign key to the ENTITY_ID column of the WLCS_ENTITY_ID table.
AVAILABLE	NUMBER(5)	A switch to identify whether or not this portlet is available.
MANDATORY	NUMBER(5)	This flag, when set, overrides the VISIBLE flag and requires the portlet be displayed.
EDITABLE	NUMBER(5)	This flag determines whether a user is allowed to edit any content.
MOVEABLE	NUMBER(5)	This column is not being used.
MINIMIZEABLE	NUMBER(5)	This flag determines whether or not the user will be allowed to minimize the portlet.
MAXIMIZEABLE	NUMBER(5)	This flag determines whether or not the user will be allowed to maximize the portlet.
FLOATABLE	NUMBER(5)	This flag determines whether the portlet can open up in its own browser window.
VISIBLE	NUMBER(5)	This flag determines whether or not the portlet is visible.
X	NUMBER(5)	The X coordinate determines the placement of the portlet on the screen. This is zero based and refers to the column placement (0=column 1, 1=column 2 and so on).
Y	NUMBER(5)	The Y coordinate determines placement of the portlet on the screen. Like the X coordinate, it is zero based. The Y coordinate refers to the row placement (0=row 1, 1=row 2 and so on).
MINIMIZED	NUMBER(5)	This flag determines whether or not the portlet should be displayed in a minimized format when initially displayed.

 

The WLCS_IS_ALIVE Database Table

Table 1-14 describes the WLCS_IS_ALIVE table. This table is used by the JDBC connection pools to insure the connection to the database is still alive.

 
 
 

Table 1-14 WLCS_IS_ALIVE Table Metadata

Column Name	Data Type	Description and Recommendations
NAME	VARCHAR(100)	Used by the JDBC connection pools to insure the connection to the database is still alive.

 
 

The WLCS_LDAP_CONFIG Database Table

Table 1-15 describes the WLCS_LDAP_CONFIG table. This table holds configuration information for LDAP functionality within the User Management module.

The Primary Key is LDAP_PROPERTY.

Table 1-15 WLCS_LDAP_CONFIG Table Metadata

Column Name	Data Type	Description and Recommendations
LDAP_PROPERTY	VARCHAR(100)	The property name.
LDAP_VALUE	VARCHAR(254)	The property value.

 

The WLCS_PROP_BOOLEAN Database Table

Table 1-16 describes the WLCS_PROP_BOOLEAN table. This table stores property values for boolean properties.

The Primary Key is PROPERTY_ID.

Table 1-16 WLCS_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_PROP_DATETIME Database Table

Table 1-17 describes the WLCS_PROP_DATETIME table. This table stores property values for date and time properties.

The Primary Key is PROPERTY_ID.

Table 1-17 WLCS_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 date and time property identifier.

 

The WLCS_PROP_FLOAT Database Table

Table 1-18 describes the LCS_PROP_FLOAT table. This table stores property values for float properties.

The Primary Key is PROPERTY_ID.

Table 1-18 WLCS_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_PROP_ID Database Table

Table 1-19 describes the WLCS_PROP_ID table. Any property assigned to a ConfigurableEntity has a unique PROPERTY_ID. This identifier and associated information is stored here.

The Primary Key is ENTITY_ID, PROPERTY_NAME and SCOPE_NAME.

Table 1-19 WLCS_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 metadata information. Again, we use the PROPERTY_TYPE column to identify which type of Property metadata 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_PROP_INTEGER Database Table

Table 1-20 describes the WLCS_PROP_INTEGER table. This table stores property values for integer properties.

The Primary Key is PROPERTY_ID.

Table 1-20 WLCS_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_PROP_MD Database Table

Table 1-21 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 1-21 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 1-22 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 1-22 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 1-23 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 1-23 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 1-24 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 1-24 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 1-25 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 1-25 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 1-26 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 1-26 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 1-27 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 1-27 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 WLCS_PROP_TEXT Database Table

Table 1-28 describes the WLCS_PROP_TEXT table. This table stores property values for the text for the text property type.

The Primary Key is PROPERTY_ID.

 

Table 1-28 WLCS_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_PROP_USER_DEFINED Database Table

Table 1-29 describes the WLCS_PROP_USER_DEFINED table. This table stores property values for any user-defined property type.

The Primary Key is PROPERTY_ID.

Table 1-29 WLCS_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 RULESET Database Table

Table 1-30 describes the RULESET table. This table contains all of the rule sets.

The Primary Key is NAME.

Table 1-30 RULESET Table Metadata

Column Name	Data Type	Description and Recommendations
NAME	VARCHAR(50)	PK-the rule name. A unique name to differentiate it from other rules.
DOCUMENT	CLOB	The XML document containing the rule set definition.

 

The WLCS_RULESET_DEFINITION Database Table

Table 1-31 describes the WLCS_RULESET_DEFINITION table. This table contains all rule sets.

The Primary Key is NAME.

Table 1-31 WLCS_RULESET_DEFINITION Table Metadata

Column Name	Data Type	Description and Recommendations
NAME	VARCHAR(50)	The identifier, or name, of the rule set.
DOCUMENT	BLOB	The XML document containing the rule set definition.

 

The WLCS_SCHEMA Database Table

Table 1-32 describes the WLCS_SCHEMA table. This table stores property set definitions.

The Primary Keys are SCHEMA_GROUP_NAME and SCOPE_NAME.

Table 1-32 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_SEQUENCER Database Table

Table 1-33 describes the WLCS_SEQUENCER table. The WLCS_SEQUENCER table is used to maintain all of the sequence identifiers (for example, property_meta_data_id_sequence, and so on) used in the application.

The Primary Key is SEQUENCE_NAME.

Table 1-33 WLCS_SEQUENCER Table Metadata

Column Name	Data Type	Description and Recommendations
SEQUENCE_NAME	VARCHAR(50)	A unique name used to identify the sequence.
CURRENT_VALUE	NUMBER(15)	The current value of the sequence.
IS_LOCKED	NUMBER(1)	This flag identifies whether or not the particular SEQUENCE_ID has been locked for update. This column is being used as a generic locking mechanism that can be used for multiple database environments.

 

The WLCS_TODO Database Table

Table 1-34 describes the WLCS_TODO table. This table is used by the Example portal and is not used except for demonstration purposes. It contains information used in the To Do portlet.

The Primary Key is ITEM and OWNER.

Table 1-34 WLCS_TODO Table Metadata

Column Name	Data Type	Description and Recommendations
ITEM	VARCHAR(50)	The activity to be accomplished.
OWNER	VARCHAR(150)	The individual who owns, or is responsible for, this activity.
DONE	NUMBER(5)	The status identifying whether this item has been completed.
PRIORITY	NUMBER(5)	The priority of the activity.

 
 

The WLCS_UIDS Database Table

Table 1-35 describes the WLCS_UIDS table. This table stores sequence information in a generic database independent format.

The Primary Key is SID.

Table 1-35 WLCS_UIDS Table Metadata

Column Name	Data Type	Description and Recommendations
SID	VARCHAR(100)	The name of the sequence.
NEXT_SEQUENCE	NUMBER(15)	The next value available for use with the sequence.

 

The WLCS_UNIFIED_PROFILE_TYPE Database Table

Table 1-36 describes the WLCS_UNIFIED_PROFILE_TYPE table. This table allows registration of classes which extend the ProvidedUser class.

The Primary Key is TYPE_NAME.

Table 1-36 WLCS_UNIFIED_PROFILE_TYPE Table Metadata

Column Name	Data Type	Description and Recommendations
TYPE_NAME	VARCHAR(100)	Any unique name used for easy lookup.
CLASS_NAME	VARCHAR(100)	The name of the remote interface class.
HOME	VARCHAR(100)	The name of the home class.
PK	VARCHAR(100)	The name of the primary key class.
JNDI_NAME	VARCHAR(100)	The name to look up in the JNDI tree.
SUCCESSOR	VARCHAR(100)	This column allows you to define another class should the TYPE_NAME not exist. This column is a foreign key to TYPE_NAME of the WLCS_UNIFIED_PROFILE_TYPE table.

 

The WLCS_USER Database Table

Table 1-37 describes the WLCS_USER table. This table stores all user login/password combinations.

The Primary Key is IDENTIFIER.

Table 1-37 WLCS_USER Table Metadata

Column Name	Data Type	Description and Recommendations
IDENTIFIER	VARCHAR(50)	The user login. This column is a foreign key to the PK_STRING column of the WLCS_ENTITY_ID table.
PASSWORD	VARCHAR(50)	The encrypted password.
IS_EXTERNAL	NUMBER(3)	This flag determines whether a user came from an external realm as opposed to the internal database realm.
PROFILE_TYPE	VARCHAR(100)	A foreign key to the TYPE_NAME in the WLCS_UNIFIED_PROFILE_TYPE table.

 

The WLCS_USER_GROUP_CACHE Database Table

Table 1-38 describes the WLCS_USER_GROUP_CACHE table. In the event of a deep group hierarchy, this table will flatten the group hierarchy and enables quick group membership searches.

Note: The startup process GroupCache is disabled by default. This table will only be used if enabled.

The Primary Key is comprised of both USER_NAME and GROUP_NAME.

Table 1-38 WLCS_USER_GROUP_CACHE Table Metadata

Column Name	Data Type	Description and Recommendations
USER_NAME	VARCHAR(100)	FK-foreign key to WLCS_USER.IDENTIFIER.
GROUP_NAME	(VARCHAR(100)	FK-foreign key to WLCS_GROUP.IDENTIFIER.

 

The WLCS_USER_GROUP_HIERARCHY Database Table

Table 1-39 describes the WLCS_USER_GROUP_HIERARCHY table. This table allows you to store associated users and groups.

The Primary Key is comprised of USER_ID and GROUP_ID.

Table 1-39 WLCS_USER_GROUP_HIERARCHY Table Metadata

Column Name	Data Type	Description and Recommendations
USER_ID	NUMBER(15)	The ENTITY_ID of a user. This column is a foreign key to the USER_ID column of the WLCS_ENTITY_ID table.
GROUP_ID	NUMBER(15)	The ENTITY_ID of a group. This column is a foreign key to the USER_ID column of the WLCS_ENTITY_ID table.

 
 

The WLCS_USER_PERSONALIZATION Database Table

Table 1-40 describes the WLCS_USER_PERSONALIZATION table.This table contains personalized portal information for the user.

The Primary Key is comprised of PORTAL_NID, CATEGORY_NID, GROUP_NID, USER_NID and PORTLET_NID.

Table 1-40 WLCS_USER_PERSONALIZATION

Column Name	Data Type	Description and Recommendations
PORTAL_NID	NUMBER(15)	The portal identifier. This column is a foreign key to the NID column of the WLCS_PORTAL_DEFINITION table.
CATEGORY_NID	NUMBER(15)	The category identifier. This column is a foreign key to the NID column of the WLCS_CATEGORIES table.
GROUP_NID	NUMBER(15)	The group identifier. This column is a foreign key to the ENTITY_ID column of the WLCS_ENTITY_ID table.
USER_NID	NUMBER(15)	The user identifier. This column is a foreign key to the ENTITY_ID column of the WLCS_ENTITY_ID table.
PORTLET_NID	NUMBER(15)	The portlet identifier. This column is a foreign key to the NID column of the WLCS_PORTLET table.
VISIBLE	NUMBER(5)	This flag determines whether or not the portlet is visible. 0 equates to FALSE and 1 equates to TRUE.
X	NUMBER(5)	The X coordinate determines the placement of the portlet on the screen. This is zero based and refers to the column placement (0=column 1, 1=column 2 and so on).
Y	NUMBER(5)	The Y coordinate determines placement of the portlet on the screen. Like the X coordinate, it is zero based. The Y coordinate refers to the row placement (0=row 1, 1=row 2 and so on).
MINIMIZED	NUMBER(5)	This flag determines whether or not the portlet should be displayed in a minimized format when displayed initially. 0 equates to FALSE and 1 equates to TRUE.

 

The WLCS_UUP_EXAMPLE Database Table

Table 1-41 describes the WLCS_UUP_EXAMPLE table. This is an example of how to use the Unified Profile Types.

The Primary Key is NAME.

Table 1-41 WLCS_UUP_EXAMPLE Table Metadata

Column Name	Data Type	Description and Recommendations
NAME	VARCHAR(100)	A username.
POINTS	NUMBER(15)	A point accumulator based on various actions taken by the user.

 

 

---

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

For some of the database tables described earlier in this chapter, the SQL files define constraints. Table 1-42 shows the table name and describes the constraint(s) defined for it.

Table 1-42 Constraints Defined on Campaign Manager Database Tables

Table Name	Constraints as Defined in create-catalog-oracle.sql
PLACEHOLDER	A check constraint (CK_MIX_GLOBALS) ensures the column MIX_GLOBALS is populated with either a 0 or 1.
AD_BUCKET	A referential integrity constraint (FL_PLACEHOLDER_AD) ensures that a PLACEHOLDER exists.