The WebLogic Personalization Server Database Schema

Column Name	Data Type	Null Value	Description and Recommendations
AD_BUCKET_ID	NUMBER(15)	NOT NULL	PK-a unique, system-generated number used as the record identifier.
USER_NAME	VARCHAR (200)	NOT NULL	The user's name associated with the ad.
PLACEHOLDER_XML_REF	VARCHAR(254)	NOT NULL	The location identifier of the XML-based placeholder definition file.
APPLICATION_NAME	VARCHAR(100)	NOT NULL	The name of the application for which the ad has been scoped.
CONTEXT_REF	VARCHAR(254)	NULL	The scenario unique identifier.
CONTAINER_REF	VARCHAR(254)	NULL	The campaign unique identifier.
CONTAINER_TYPE	VARCHAR(50)	NULL	Identifies the service associated with the CONTAINER_REF.
WEIGHT	NUMBER(15)	NULL	A weighted scheme used in prioritizing one placeholder over another.
VIEW_COUNT	NUMBER(15)	NULL	Disabled. Reserved for future use.
EXPIRATION_DATE	DATE	NULL	The date and time the ad expires or becomes invalid.
CREATION_DATE	DATE	NOT NULL	The date and time this record was created.
MODIFIED_DATE	DATE	NOT NULL	The date and time this record was last modified.
AD_QUERY	CLOB	NULL	The actual content query.

Column Name	Data Type	Null Value	Description and Recommendations
AD_ID	VARCHAR(254)	NOT NULL	A unique, system-generated number used as the record identifier.
CONTAINER_REF	VARCHAR(254)	NOT NULL	The campaign unique identifier.
APPLICATION_NAME	VARCHAR(100)	NOT NULL	The name of the application for which the ad clicks or views were scoped
DISPLAY_COUNT	NUMBER(15)	NOT NULL	The number of times the ad has been displayed.
CLICK_THROUGH_COUNT	NUMBER(15)	NOT NULL	The number of times the ad has been clicked on.

Column Name	Data Type	Null Value	Description and Recommendations
APPLICATION_ID	NUMBER(15)	NOT NULL	PK - A unique, system-generated number used as the record identifier.
APPLICATION_NAME	VARCHAR(100)	NOT NULL	The deployed J2EE application name. (This should match the name in the WebLogic Server console.)
CREATION_DATE	DATE	NOT NULL	The date and time this record was created.
MODIFIED_DATE	DATE	NOT NULL	The date and time this record was last modified.

Column Name	Data Type	Null Value	Description and Recommendations
DATA_SYNC_ITEM_ID	NUMBER(15)	NOT NULL	PK - A unique, system-generated number used as the record identifier.
APPLICATION_ID	NUMBER(15)	NOT NULL	FK - to DATA_SYNC_APPLICATON.APPLICATION_ID
SCHEMA_URI_ID	NUMBER(15)	NOT NULL	FK - to DATA_SYNC_SCHEMA_URI.SCHEMA_URI_ID
VERSION_MAJOR	NUMBER(15)	NOT NULL	FK - to DATA_SYNC_VERSION.VERSION_MAJOR
VERSION_MINOR	NUMBER(15)	NOT NULL	FK - to DATA_SYNC_VERSION.VERSION_MINOR
ITEM_CHECKSUM	NUMBER(15)	NOT NULL	A generated number representing the contents of the XML_DEFINITION column.
CREATION_DATE	DATE	NOT NULL	The date and time this record was created.
MODIFIED_DATE	DATE	NOT NULL	The date and time this record was last modified.
XML_MODIFIED_DATE	DATE	NOT NULL	The date and time the XML file was last modified.
XML_CREATION_DATE	DATE	NOT NULL	The date and time the XML file was created.
XML_DEFINITION	CLOB	NOT NULL	The XML representation of the data item to be synchronized.
ITEM_URI	VARCHAR(254)	NOT NULL	The path on the file system of the data item to be synchronized.
ITEM_AUTHOR	VARCHAR(200)	NULL	Metadata info-the o/s login.
ITEM_NAME	VARCHAR(100)	NULL	Metadata info-the full path to the item.
ITEM_DESCRIPTION	VARCHAR(254)	NULL	Metadata info-a general description of the item to be synchronized.

Column Name	Data Type	Null Value	Description and Recommendations
SCHEMA_URI_ID	NUMBER(15)	NOT NULL	PK - A unique, system-generated number used as the record identifier.
SCHEMA_URI	VARCHAR(254)	NOT NULL	The governing schema of the document.
CREATION_DATE	DATE	NOT NULL	The date and time this record was created.
MODIFIED_DATE	DATE	NOT NULL	The date and time this record was last modified.

Column Name	Data Type	Null Value	Description and Recommendations
VERSION_MAJOR	NUMBER(15)	NOT NULL	The current record has a value of zero.
VERSION_MINOR	NUMBER(15)	NOT NULL	The current record has a value of zero.
CREATION_DATE	DATE	NOT NULL	The date and time the record was created.
MODIFIED_DATE	DATE	NOT NULL	The date and time the record was last modified.
BUILD_NUMBER	NUMBER(15)	NULL	The build number associated with the version.
VERSION_DESCRIPTION	VARCHAR(30)	NULL	A description of the particular sync version.

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

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

Column Name	Data Type	Null Value	Description and Recommendations
APPLICATION_NAME	VARCHAR(100)	NOT NULL	PK - A unique application name within a J2EE server.
RULESET_URI	VARCHAR(254)	NOT NULL	The URI used to identify an entitlement access decision rule.
CREATION_DATE	DATE	NOT NULL	The date and time this record was created.
MODIFIED_DATE	DATE	NOT NULL	The date and time this record was last modified.
RULESET_DOCUMENT	CLOB	NULL	The XML document describing an access decision rule.

Column Name	Data Type	Null Value	Description and Recommendations
ENTITY_ID	NUMBER(15)	NOT NULL	PK - A unique, sequence-generated number used as the record identifier.
ENTITY_NAME	VARCHAR(200)	NOT NULL	The name of the ConfigurableEntity.
ENTITY_TYPE	VARCHAR(100)	NOT NULL	Defines what type of ConfigurableEntity this is.
CREATION_DATE	DATE	NOT NULL	The date and time this record was created.
MODIFIED_DATE	DATE	NOT NULL	The date and time this record was last modified.

Column Name	Data Type	Null Value	Description and Recommendations
PARENT_GROUP_ID	NUMBER(15)	NOT NULL	The parent group identifier. This column is a foreign key to the ENTITY_ID column in the ENTITY table.
CHILD_GROUP_ID	NUMBER(15)	NOT NULL	The child group identifier. This column is a foreign key to the ENTITY_ID column in the ENTITY table.
CREATION_DATE	DATE	NOT NULL	The date and time this record was created.
MODIFIED_DATE	DATE	NOT NULL	The date and time this record was last modified.

Column Name	Data Type	Null Value	Description and Recommendations
GROUP_ID	NUMBER(15)	NOT NULL	PK - a unique, system-generated number used as the record identifier.
GROUP_NAME	VARCHAR(200)	NOT NULL	The name of the group.
CREATION_DATE	DATE	NOT NULL	The date and time this record was created.
MODIFIED_DATE	DATE	NOT NULL	The date and time this record was last modified.

Column Name	Data Type	Null Value	Description and Recommendations
MAIL_ADDRESS_ID	NUMBER(15)	NOT NULL	PK-a unique, system-generated number to be used as the record ID.
MESSAGE_ID	NUMBER(15)	NOT NULL	FK-foreign key to the MAIL_MESSAGE table.
ADDRESS	VARCHAR(254)	NOT NULL	Stores the various e-mail addresses on the distribution list.
SEND_TYPE	VARCHAR(4)	NOT NULL	Determines how the ADDRESS should be included on the distribution. Possible values are TO, CC, or BCC.

Column Name	Data Type	Null Value	Description and Recommendations
BATCH_ID	NUMBER(15)	NOT NULL	PK-a unique, system-generated number to be used as the record ID.
BATCH_NAME	VARCHAR(254)	NOT NULL	The name of the mail message batch.

Column Name	Data Type	Null Value	Description and Recommendations
BATCH_ID	NUMBER(15)	NOT NULL	PK and FK-a unique, system-generated number to be used as the record ID.
MESSAGE_ID	NUMBER(15)	NOT NULL	PK and FK-foreign key to the MAIL_MESSAGE table.

Column Name	Data Type	Null Value	Description and Recommendations
HEADER_ID	NUMBER(15)	NOT NULL	PK-a unique, system-generated number to be used as the record ID.
MESSAGE_ID	NUMBER(15)	NOT NULL	FK-foreign key to the MAIL_MESSAGE table.
HEADER_NAME	VARCHAR(50)	NULL	The name of the mail message header.
HEADER_VALUE	VARCHAR(254)	NULL	The value of the mail message header.

Column Name	Data Type	Null Value	Description and Recommendations
MESSAGE_ID	NUMBER(15)	NOT NULL	PK-a unique, system-generated number to be used as the record ID.
FROM_ADDRESS	VARCHAR(254)	NULL	Identifies who is sending the message.
SUBJECT	VARCHAR(128)	NULL	Stores the mail message subject.
MESSAGE_TEXT	CLOB	NULL	Holds the content of the mail message.

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

Column Name	Data Type	Null Value	Description and Recommendations
PROPERTY_KEY_ID	NUMBER(15)	NOT NULL	PK-a unique, system-generated number used as the record identifier.
PROPERTY_NAME	VARCHAR(100)	NOT NULL	The name of the property.
CREATION_DATE	DATE	NOT NULL	The date and time this record was created.
MODIFIED_DATE	DATE	NOT NULL	The date and time this record was last modified.
PROPERTY_SET_NAME	VARCHAR(100)	NULL	The name of the property set.
PROPERTY_SET_TYPE	VARCHAR(100)	NULL	The type the property set.

Column Name	Data Type	Null Value	Description and Recommendations
PROPERTY_VALUE_ID	NUMBER(15)	NOT NULL	PK - a unique, system-generated number used as the record identifier.
PROPERTY_KEY_ID	NUMBER(15)	NOT NULL	FK - to PROPERTY_KEY.PROPERTY_KEY_ID
ENTITY_ID	NUMBER(15)	NOT NULL	FK - to ENTITY.ENTITY_ID
PROPERTY_TYPE	NUMBER(1)	NOT NULL	Valid entries are: 0=Boolean, 1=Integer, 2=Float, 3=Text, 4=Date and Time, 5=User-Defined (BLOB)
CREATION_DATE	DATE	NOT NULL	The date and time this record was created.
MODIFIED_DATE	DATE	NOT NULL	The date and time this record was last modified.
BOOLEAN_VALUE	NUMBER(1)	NULL	The value for each boolean property identifier.
DATETIME_VALUE	DATE	NULL	The value for each date and time property identifier.
DOUBLE_VALUE	NUMBER	NULL	The value associated with each float property identifier.
LONG_VALUE	NUMBER(20)	NULL	The value associated with the integer property.
TEXT_VALUE	VARCHAR(254)	NULL	The value associated with the text property.
BLOB_VALUE	BLOB	NULL	The value associated with the user-defined property.

Column Name	Data Type	Null Value	Description and Recommendations
USER_NAME	VARCHAR(100)	NOT NULL	A username.
USER_INFO	CLOB	NOT NULL	User data stored in XML representation.

Column Name	Data Type	Null Value	Description and Recommendations
SEQUENCE_NAME	VARCHAR(50)	NOT NULL	PK - A unique name used to identify the sequence.
CURRENT_VALUE	NUMBER(15)	NOT NULL	The current value of the sequence.
IS_LOCKED	NUMBER(1)	NOT NULL	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.

Column Name	Data Type	Null Value	Description and Recommendations
USER_NAME	VARCHAR(200)	NOT NULL	A user's name.
GROUP_NAME	VARCHAR(200)	NOT NULL	A group name.

Column Name	Data Type	Null Value	Description and Recommendations
GROUP_ID	NUMBER(15)	NOT NULL	FK - to USER_SECURITY.USER_ID
USER_ID	NUMBER(15)	NOT NULL	FK - to GROUP_SECURITY.GROUP_ID
CREATION_DATE	DATE	NOT NULL	The date and time this record was created.
MODIFIED_DATE	DATE	NOT NULL	The date and time this record was last modified.

Column Name	Data Type	Null Value	Description and Recommendations
USER_NAME	VARCHAR(200)	NOT NULL	PK - The name of the user.
PROFILE_TYPE	VARCHAR(100)	NOT NULL	A type of profile associated with the user (such as WLCS_Customer).
CREATION_DATE	DATE	NOT NULL	The date and time this record was created.

Column Name	Data Type	Null Value	Description and Recommendations
USER_ID	NUMBER(15)	NOT NULL	PK-a unique, system-generated number used as the record identifier.
USER_NAME	VARCHAR(200)	NOT NULL	The user's name.
PASSWORD	VARCHAR(50)	NULL	The user's password.
CREATION_DATE	DATE	NOT NULL	The date and time this record was created.
MODIFIED_DATE	DATE	NOT NULL	The date and time this record was last modified.

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

Table 12-51 The Scripts Supporting the Databases

Script Name	Description
create_all.bat	Windows script used to connect to the database and create the necessary database objects for the modules desired (e.g., WebLogic Portal, WebLogic Personalization Server, Commerce services, Campaign services and Sample Portal)
create_all.sh	Unix script used to connect to the database and create the necessary database objects for the modules desired (e.g., WebLogic Portal, WebLogic Personalization Server, Commerce services, Campaign services and Sample Portal)
campaign_create_fkeys.sql	SQL script used to create all foreign keys associated with the Campaign services.
campaign_create_indexes.sql	SQL script used to create all indexes associated with the Campaign services.
campaign_create_tables.sql	SQL script used to create all tables associated with the Campaign services.
campaign_create_triggers.sql	SQL script used to create all database triggers associated with the Campaign services.
campaign_create_views.sql	SQL script used to create all views associated with the Campaign services.
campaign_drop_constraints.sql	SQL script used to drop all constraints (other than foreign keys) associated with the Campaign services.
campaign_drop_fkeys.sql	SQL script used to drop all foreign key constraints associated with the Campaign services.
campaign_drop_indexes.sql	SQL script used to drop all indexes associated with the Campaign services.
campaign_drop_tables.sql	SQL script used to drop all tables associated with the Campaign services.
campaign_drop_views.sql	SQL script used to drop all views associated with the Campaign services.
p13n_create_fkeys.sql	SQL script used to create all foreign keys associated with the WebLogic Personalization Server.
p13n_create_indexes.sql	SQL script used to create all indexes associated with the WebLogic Personalization Server.
p13n_create_tables.sql	SQL script used to create all tables associated with the WebLogic Personalization Server.
p13n_create_triggers.sql	SQL script used to create all database triggers associated with the WebLogic Personalization Server.
p13n_create_views.sql	SQL script used to create all views associated with the WebLogic Personalization Server.
p13n_drop_constraints.sql	SQL script used to drop all constraints (other than foreign keys) associated with the WebLogic Personalization Server.
p13n_drop_fkeys.sql	SQL script used to drop all foreign key constraints associated with the WebLogic Personalization Server.
p13n_drop_indexes.sql	SQL script used to drop all indexes associated with the WebLogic Personalization Server.
p13n_drop_tables.sql	SQL script used to drop all tables associated with the WebLogic Personalization Server.
p13n_drop_views.sql	SQL script used to drop all views associated with the WebLogic Personalization Server.
portal_create_fkeys.sql	SQL script used to create all foreign keys associated with the WebLogic Portal.
portal_create_indexes.sql	SQL script used to create all indexes associated with the WebLogic Portal.
portal_create_tables.sql	SQL script used to create all tables associated with the WebLogic Portal.
portal_create_triggers.sql	SQL script used to create all database triggers associated with the WebLogic Portal.
portal_create_views.sql	SQL script used to create all views associated with the WebLogic Portal.
portal_drop_constraints.sql	SQL script used to drop all constraints (other than foreign keys) associated with the WebLogic Portal.
portal_drop_fkeys.sql	SQL script used to drop all foreign key constraints associated with the WebLogic Portal.
portal_drop_indexes.sql	SQL script used to drop all indexes associated with the WebLogic Portal.
portal_drop_tables.sql	SQL script used to drop all tables associated with the WebLogic Portal.
portal_drop_views.sql	SQL script used to drop all views associated with the WebLogic Portal.
sample_portal_create_fkeys.sql	SQL script used to create all foreign keys associated with the Sample Portal.
sample_portal_create_indexes.sql	SQL script used to create all indexes associated with the Sample Portal.
sample_portal_create_tables.sql	SQL script used to create all tables associated with the Sample Portal.
sample_portal_create_triggers.sql	SQL script used to create all database triggers associated with the Sample Portal.
sample_portal_create_views.sql	SQL script used to create all views associated with the Sample Portal.
sample_portal_drop_constraints.sql	SQL script used to drop all constraints (other than foreign keys) associated with the Sample Portal.
sample_portal_drop_fkeys.sql	SQL script used to drop all foreign key constraints associated with the Sample Portal.
sample_portal_drop_indexes.sql	SQL script used to drop all indexes associated with the Sample Portal.
sample_portal_drop_tables.sql	SQL script used to drop all tables associated with the Sample Portal.
sample_portal_drop_views.sql	SQL script used to drop all views associated with the Sample Portal.
wlcs_create_fkeys.sql	SQL script used to create all foreign keys associated with the Commerce services.
wlcs_create_indexes.sql	SQL script used to create all indexes associated with the Commerce services.
wlcs_create_tables.sql	SQL script used to create all tables associated with the Commerce services.
wlcs_create_triggers.sql	SQL script used to create all database triggers associated with the Commerce services.
wlcs_create_views.sql	SQL script used to create all views associated with the Commerce services.
wlcs_drop_constraints.sql	SQL script used to drop all constraints (other than foreign keys) associated with the Commerce services.
wlcs_drop_fkeys.sql	SQL script used to drop all foreign key constraints associated with the Commerce services.
wlcs_drop_indexes.sql	SQL script used to drop all indexes associated with the Commerce services.
wlcs_drop_tables.sql	SQL script used to drop all tables associated with the Commerce services.
wlcs_drop_views.sql	SQL script used to drop all views associated with the Commerce services.

Table Name	Constraints
DATA_SYNC_ITEM	Column-APPLICATION_ID Constraint-FK1_SYNC_ITEM Constraint Type-FOREIGN KEY Ensures that each DATA_SYNC_ITEM references an existing DATA_SYNC_APPLICATION via the APPLICATION_ID column. Column-SCHEMA_URI_ID Constraint-FK2_SYNC_ITEM Constraint Type-FOREIGN KEY Ensures that each DATA_SYNC_ITEM references an existing DATA_SYNC_SCHEMA_URI via the SCHEMA_URI_ID column. Columns-VERSION_MAJOR and VERSION_MINOR Constraint-FK3_SYNC_ITEM Constraint Type-FOREIGN KEY Ensures that each DATA_SYNC_ITEM references an existing DATA_SYNC_VERSION via the VERSION_MAJOR, VERSION_MINOR columns.
DOCUMENT_METADATA	Column-ID Constraint- FK1_DOCUMENT_MD Constraint Type-FOREIGN KEY Ensures that each DOCUMENT_METADATA references an existing DOCUMENT via the ID column.
GROUP_HIERARCHY	Column-PARENT_GROUP_ID Constraint-FK1_GROUP_HRCHY Constraint Type-FOREIGN KEY Ensures that each PARENT_GROUP_HIERARCHY references an existing GROUP_SECURITY via the GROUP_ID column. Column-CHILD_GROUP_ID Constraint-FK2_GROUP_HRCHY Constraint Type-FOREIGN KEY Ensures that each CHILD_GROUP_HIERARCHY references an existing GROUP_SECURITY via the GROUP_ID column.
MAIL_ADDRESS	Column-MESSAGE_ID Constraint-FK1_MAIL_ADDRESS Constraint Type-FOREIGN KEY Ensures that each MAIL_ADDRESS references an existing MAIL_MESSAGE via the MESSAGE_ID column.
MAIL_BATCH_ENTRY	Column-BATCH_ID Constraint-FK1_MB_ENTRY Constraint Type-FOREIGN KEY Ensures that each MAIL_BATCH_ENTRY references an existing MAIL_BATCH via the BATCH_ID column. Column-MESSAGE_ID Constraint-FK2_MB_ENTRY Constraint Type-FOREIGN KEY Ensures that each MAIL_BATCH_ENTRY references an existing MAIL_MESSAGE via the MESSAGE_ID column.
MAIL_HEADER	Column-FK1_MAIL_HEADER Constraint-FK1_MAIL_HEADER Constraint Type-FOREIGN KEY Ensures that each MAIL_HEADER references an existing MAIL_MESSAGE via the FK1_MAIL_HEADER column.
USER_GROUP_HIERARCHY	Column-USER_ID Constraint-FK1_USER_G_HRCHY Constraint Type-FOREIGN KEY Ensures that each USER_GROUP_HIERARCHY references an existing USER_SECURITY via the USER_ID column. Column-GROUP_ID Constraint-FK2_USER_G_HRCHY Constraint Type-FOREIGN KEY Ensures that each USER_GROUP_HIERARCHY references an existing GROUP_SECURITY via the GROUP_ID column.
PROPERTY_VALUE	Column-ENTITY_ID Constraint-FK1_PROP_VALUE Constraint Type-FOREIGN KEY Ensures that each PROPERTY_VALUE references an existing ENTITY via the ENTITY_ID column. Column-PROPERTY_KEY_ID Constraint-FK2_PROP_VALUE Constraint Type-FOREIGN KEY Ensures that each PROPERTY_VALUE references an existing PROPERTY_KEY via the PROPERTY_KEY_ID column. Column-BOOLEAN_VALUE Constraint-CC1_PROP_VALUE Constraint Type-CHECK Ensures the value of the BOOLEAN_VALUE column is either 0 (false) or 1 (true).
SEQUENCER	Column-IS_LOCKED Constraint-CC1_SEQUENCER Constraint Type-CHECK Ensures the value of the IS_LOCKED column is either 0 (false) or 1 (true).


	BEA Home \| Events \| Solutions \| Partners \| Products \| Services \| Download \| Developer Center \| WebSUPPORT
	http://www.oracle.com/technology/documentation/index.html \| Search \| PDF Files \| Contact \| Glossary

WebLogic Portal Documentation \| Building Personalized Applications \| Previous Topic \| Next Topic \| Contents \| Index