The WebLogic Personalization Server Database Schema

 

This topic 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

• Defined Constraints

 

---

The Entity-Relation Diagram

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

 

Figure 12-1 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_PREVIEW Database Table

Data Synchronization tables

The DATA_SYNC_APPLICATION Database Table

The DATA_SYNC_ITEM Database Table

The DATA_SYNC_SCHEMA_URI Database Table

The DATA_SYNC_VERSION Database Table

Documentation Management tables

The DOCUMENT Database Table

The DOCUMENT_METADATA Database Table

Mail tables

The MAIL_ADDRESS Database Table

The MAIL_BATCH Database Table

The MAIL_BATCH_ENTRY Database Table

The MAIL_HEADER Database Table

The MAIL_MESSAGE Database Table

User Management tables

The GROUP_HIERARCHY Database Table

The GROUP_SECURITY Database Table

The USER_GROUP_CACHE Database Table

The USER_GROUP_HIERARCHY Database Table

The USER_PROFILE Database Table

The USER_SECURITY Database Table

Common tables used by both WebLogic Personalization Server and WebLogic Portal

The ENTITLEMENT_RULESET Database Table Database Table

The ENTITY Database Table

The PROPERTY_KEY Database Table

The PROPERTY_VALUE Database Table

The SAMPLE_UUP_INFO Database Tablee

The SEQUENCER Database Table

The WEBLOGIC_IS_ALIVE 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 12-1 describes the AD_BUCKET table. This table maintains content queries for ads.

The Primary Key is AD_BUCKET_ID.

Table 12-24 AD_BUCKET Table Metadata

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.

 

The AD_COUNT Database Table

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

The Primary Key is comprised of AD_ID, CONTAINER_REF, and APPLICATION_NAME.

 

Table 12-25 AD_COUNT Table Metadata

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.

 

The DATA_SYNC_APPLICATION Database Table

Table 12-3 describes the DATA_SYNC_APPLICATION table. This table holds the various applications available for the data synchronization process..

The Primary Key is APPLICATION_ID.

 

Table 12-26 DATA_SYNC_APPLICATION Table Metadata

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.

 

The DATA_SYNC_ITEM Database Table

Table 12-4 describes the DATA_SYNC_ITEM table. This table stores all the data items to be synchronized.

For information on defined constraints in this table, see Defined Constraints.

The Primary Key is DATA_SYNC_ITEM_ID.

 

Table 12-27 DATA_SYNC_ITEM Table Metadata

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.

 

The DATA_SYNC_SCHEMA_URI Database Table

Table 12-5 describes the DATA_SYNC_SCHEMA_URI table. This table holds information pertaining to each of the governing schemas used by various documents.

The Primary Key is SCHEMA_URI_ID.

 

Table 12-28 DATA_SYNC_SCHEMA_URI Table Metadata

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.

 

The DATA_SYNC_VERSION Database Table

Table 12-6 describes the DATA_SYNC_VERSION table. This table is not being used currently. It is reserved for future use and is expected to accommodate data synchronization versioning. As a result, this table only holds one record.

The Primary Key is comprised of both VERSION_MAJOR and VERSION_MINOR.

 

Table 12-29 DATA_SYNC_VERSION Table Metadata

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.

 

The DOCUMENT Database Table

Table 12-7 describes the 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 12-30 DOCUMENT Table Metadata

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.

 

The DOCUMENT_METADATA Database Table

Table 12-8 describes the DOCUMENT_METADATA table. This table is used to store user-defined properties associated with each document.

For information on defined constraints in this table, see Defined Constraints.

The Primary Key is comprised of both ID and NAME.

Table 12-31 DOCUMENT_METADATA Table Metadata

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).

 

The ENTITLEMENT_RULESET Database Table

Table 12-9 describes the ENTITLEMENT_RULESET table. This table stores the access decision rules used by the Entitlements Engine.

The Primary Key is comprised of both APPLICATION_NAME and RULESET_URI.

Table 12-32 ENTITLEMENT_RULESET Table Metadata

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.

 

The ENTITY Database Table

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

The Primary Key is ENTITY_ID.

Table 12-33 ENTITY Table Metadata

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.

 

The GROUP_HIERARCHY Database Table

Table 12-11 describes the PARENT_CHILD_GROUP table. This table stores relationship information between groups.

For information on defined constraints in this table, see Defined Constraints.

The Primary Key is comprised of both PARENT_GROUP_ID and CHILD_GROUP_ID.

Table 12-34 GROUP_HIERARCHY Table Metadata

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.

 

The GROUP_SECURITY Database Table

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

The Primary Key is GROUP_ID.

Table 12-35 GROUP_SECURITY Table Metadata

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.

 

The MAIL_ADDRESS Database Table

Table 12-13 describes the metadata for the E-Business Control Center MAIL_ADDRESS table. This table stores all of the address info for e-mail purposes.

For information on defined constraints in this table, see Defined Constraints.

The Primary Key is MAIL_ADDRESS_ID.

Table 12-36 MAIL_ADDRESS Table Metadata

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.

 

The MAIL_BATCH Database Table

Table 12-14 describes the metadata for the E-Business Control Center MAIL_BATCH table. This table establishes a batch for each mailing.

The Primary Key is BATCH_ID.

 

Table 12-37 MAIL_BATCH Table Metadata

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.

 

The MAIL_BATCH_ENTRY Database Table

Table 12-15 describes the metadata for the E-Business Control Center MAIL_BATCH_ENTRY table. This table is used to correlate the mail batch with the specific mail message.

For information on defined constraints in this table, see Defined Constraints.

The Primary Keys are BATCH_ID and MESSAGE_ID.

 

Table 12-38 MAIL_BATCH_ENTRY Table Metadata

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.

 

The MAIL_HEADER Database Table

Table 12-16 describes the metadata for the E-Business Control Center MAIL_HEADER table. This table contains all of the header information specific to the e-mail message.

For information on defined constraints in this table, see Defined Constraints.

The Primary Key is HEADER_ID.

 

Table 12-39 MAIL_HEADER Table Metadata

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.

 

The MAIL_MESSAGE Database Table

Table 12-17 describes the metadata for the E-Business Control Center MAIL_MESSAGE table. This table contains the specifics of the mail message (e.g., the subject line, text, etc.).

The Primary Key is MESSAGE_ID.

Table 12-40 MAIL_MESSAGE Table Metadata

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.

 

The PLACEHOLDER_PREVIEW Database Table

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

The Primary Key is PREVIEW_ID.

Table 12-41 PLACEHOLDER_PREVIEW Table Metadata

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.

 

The PROPERTY_KEY Database Table

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

The Primary Key is PROPERTY_KEY_ID.

Table 12-42 PROPERTY_KEY Table Metadata

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.

 

The PROPERTY_VALUE Database Table

Table 12-20 describes the PROPERTY_VALUE table. This table stores property values for boolean, datetime, float, integer, text, and user-defined properties.

For information on defined constraints in this table, see Defined Constraints.

The Primary Key is PROPERTY_VALUE_ID.

Table 12-43 PROPERTY_VALUE Table Metadata

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.

 

The SAMPLE_UUP_INFO Database Table

Table 12-21 describes the SAMPLE_UUP_INFO table. This is an example of how to use the Unified Profile Types.

The Primary Key is USER_NAME.

Table 12-44 SAMPLE_UUP_INFO Table Metadata

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.

 

The SEQUENCER Database Table

Table 12-22 describes the SEQUENCER table. The 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.

For information on defined constraints in this table, see Defined Constraints.

The Primary Key is SEQUENCE_NAME.

Table 12-45 SEQUENCER Table Metadata

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.

 

The USER_GROUP_CACHE Database Table

Table 12-23 describes the 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 12-46 USER_GROUP_CACHE Table Metadata

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.

 

The USER_GROUP_HIERARCHY Database Table

Table 12-24 describes the USER_GROUP_HIERARCHY table. This table allows you to store associated users and groups.

For information on defined constraints in this table, see Defined Constraints.

The Primary Key is comprised of both GROUP_ID and USER_ID.

Table 12-47 USER_GROUP_HIERARCHY Table Metadata

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.

 

The USER_PROFILE Database Table

Table 12-25 describes the USER_PROFILE table. This table stores all user login/password combinations.

The Primary Key is USER_NAME.

Table 12-48 USER_PROFILE Table Metadata

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.

 

The USER_SECURITY Database Table

Table 12-26 describes the USER_SECURITY table. This table holds all the user records for security authentication of the rdbms realm.

The Primary Key is USER_ID.

Table 12-49 USER_SECURITY Table Metadata

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.

 

The WEBLOGIC_IS_ALIVE Database Table

Table 12-27 describes the WEBLOGIC_IS_ALIVE table. This table is used by the JDBC connection pools to insure the connection to the database is still alive.

The Primary Key is NAME.

Table 12-50 WEBLOGIC_IS_ALIVE Table Metadata

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.

 

 

---

The SQL Scripts Used to Create the Database

The database schemas for WebLogic Portal and WebLogic Personalization Server are all created by executing the create_all script for the target database environment.

Scripts

Regardless of your database, execute one of the following to generate the necessary database objects for the modules desired (WebLogic Portal, WebLogic Personalization Server, Commerce services, Campaign services and Sample Portal):

• P13N_HOME\db\create_all.bat (Windows)

• P13N_HOME/db/create_all.sh (UNIX)

  The following are the various directories underneath WL_COMMERCE_HOME/db
  (as seen in a UNIX environment):

  P13N_HOME/db/cloudscape/351

  P13N_HOME/db/oracle/817

Note: In this documentation,P13N_HOME is used to designate the directory where the WebLogic Personalization Server product is installed.

Each of the databases supported have the same number of scripts in each of their subdirectories. The scripts are listed and described in Table 12-51 below.

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.

 

 

---

Defined Constraints

Various constraints are defined and used in the WebLogic Personalization Server database schema. These constraints can be found in the following scripts:

p13n_create_fkeys.sql-contains the Foreign Keys

p13n_create_tables.sql-contains the Check Constraints

Table 12-52 Constraints Defined on WebLogic Personalization Server Database Tables

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).