bea.com | products | dev2dev | support | askBEA |
|
e-docs > WebLogic Platform > WebLogic Portal > Administration Guide > Database Schemas |
Administration Guide |
Database Schemas
This section describes the database schemas for WebLogic Portal. This information is provided to help you restructure your database to better customize or extend the technologies provided in WebLogic Portal.
This section includes information on the following subjects:
Campaign Database Schemas
This section describes the database schema for the Campaign services.
The Entity-Relation Diagram for Campaign Manager Database Tables
Figure A-1 shows the Entity-Relation diagram for the E-Business Control Center for the Campaign services database. See the subsequent sections in this chapter for information about the data type syntax.
Figure A-1 Entity-Relation Diagram for Campaign Manager Database Tables
List of Tables Composing the BEA Campaign Manager The BEA Campaign Manager is composed of the following table: Campaign and Scenarios
The Campaign Manager Data Dictionary
At this time, there is only one database table pertaining to the Campaign Manager.
The SCENARIO_END_STATE Database Table
Table A-1 describes the metadata for the E-Business Control Center SCENARIO_END_STATE table. This table identifies when a user is no longer eligible to participate in a particular scenario.
The Primary Key is comprised of SCENARIO_XML_REF, USER_NAME, CONTAINER_REF, CONTAINER_TYPE and APPLICATION_NAME.
Event Database Schemas
To record how online visitors are interacting with your Web site, you can record event information to a database. These kinds of events are called Behavior Tracking events. E-analytics and e-marketing systems can then analyze these events offline to evaluate visitor behavior and transactional data. You can use the knowledge gained from analysis to create and optimize personalization rules, set up product offers, and develop interactive marketing campaigns. This section describes the requirements and database schema needed to log event data for analytical use.
This section includes information on the following subjects:
Data Storage
This section provides an overview of relational databases and the database schemas and tables that are required for recording Behavior Tracking events.
Relational Databases
Relational databases have both logical and physical structures. Logically you may define one or more databases. Each database may contain one or more tables and indexes, and each table may have multiple columns and rows. The logical structure of databases is quite similar between vendors. However, the physical structure of a database is very vendor-specific. Essentially, the physical structure defines areas on disk drives where the data is stored. Each database environment uses its own terminology and implementation for storing data at the operating system level. For example, Oracle uses the term tablespace and the Microsoft SQL Server uses the term filegroup.
Recommendation When a database structure is defined by a database administrator, attention must be paid to the location of specific tables. Some tables are static in that they do not change much; some tables are dynamic in that many rows are being added and deleted; and some tables are read frequently and some rarely. Depending on their behavior, tables should be placed on different physical locations. Some of the most highly-used tables in WebLogic Portal are used for Behavior Tracking. The activity of a single visitor moving around your site may generate multiple table entries. Therefore, it is recommended that you place these tables on the fastest drives in the computer. Experienced database administrators are aware of many techniques for monitoring and configuring a database installation for optimal performance. If you do not have a database administrator working with your installation and you have a lot of activity on your site, you should bring in a well-qualified database administer for regular maintenance of your system.
Database Directory Paths
The default database directory paths are:
where PORTAL_HOME is the directory in which you installed WebLogic Portal.
For example, if you are using Oracle 8.1.7 on UNIX, the location would be $PORTAL_HOME/db/oracle/817/....
Scripts BEA provides scripts to help set up the database schema needed for recording Behavior Tracking events, as well as the schema needed for recording data associated with WebLogic Portal. This data includes information from orders, catalogs, products, portals, and portlets.
For Oracle databases, the tablespaces created for WebLogic Portal data are the WEBLOGIC_DATA and WEBLOGIC_INDEX.
Note: WEBLOGIC_DATA and WEBLOGIC_INDEX are tablespace names created by BEA scripts. If you use a particular naming convention, you can rename them.
Behavior tracking uses a tablespace called WEBLOGIC_EVENT_DATA. This tablespace stores all Behavior Tracking tables, indexes, and constraints. Because of the potential for high volumes of data, this tablespace should be monitored closely.
Behavior Tracking Database Schema
Three tables are provided for the Behavior Tracking data. The EVENT table stores all event data. The EVENT_ACTION table logs actions used by third-party vendors against the recorded event data, and the EVENT_TYPE table references event types and categories in the EVENT table. Figure A-2 shows a logical entity-relation diagram for the Behavior Tracking Database.
Figure A-2 Entity-Relation Diagram for the Behavior Tracking Database
The EVENT Database Table Table A-2 describes the metadata for the EVENT table. This table stores all Behavior Tracking event data. The Primary Key is EVENT_ID.
As shown in Table A-2, the EVENT table has six columns; each column corresponds to a specific event element. Five of the EVENT table's columns contain data common to every event type. The XML_DEFINITION column contains all information from these five columns plus event data that is unique to each event type. An XML document is created specifically for each event type. The data elements corresponding to each event type are captured in the XML_DEFINITION column of the EVENT table. These elements are listed in Table A-3.
The EVENT_ACTION Database Table Table A-4 describes the metadata for the EVENT_ACTION table. This table logs actions used by third-party vendors against the recorded event data. It is a fairly static. The Primary Key is comprised of EVENT_ACTION and ACTION_DATE.
The EVENT_TYPE Database Table Table A-5 describes the metadata for the EVENT_TYPE table. This table references event types and categories in the EVENT table. This table is static. The Primary Key is EVENT_TYPE.
Note: To record custom events, you must create an entry in this table. If a custom event does not have a record in this table, you cannot persist it to the EVENT table. Constraints and Indexes There is a single foreign key constraint between the EVENT_TYPE columns in the EVENT and EVENT_TYPE tables. As previously mentioned, if a custom event does not have a record in the EVENT_TYPE table, it cannot be persisted to the EVENT table. Other than Primary Keys on each of the tables, there are only two indexes on the EVENT table. One index is on the EVENT.EVENT_DATE column and the other index is comprised of the EVENT.EVENT_TYPE and EVENT.EVENT_DATE columns.
Catalog Database Schemas
This section documents the database schema for the Commerce services Product Catalog.
This section includes information on the following subjects:
The metadata for items in Commerce services Product Catalog are based on the Dublin Core Metadata Open Standard. This standard offers a number of advantages for a Web-based catalog. For more information about the Dublin Core Metadata Open Standard, please see http://purl.org/dc.
The Entity-Relation Diagram for the Core Product Catalog Tables
Figure A-3 shows the logical Entity-Relation diagram for the Commerce services core Product Catalog tables in the Commerce database.
Figure A-3 Entity-Relation Diagram for the Core Product Catalog Tables
Product Catalog Tables The following tables compose the product catalog database.
The CATALOG_ENTITY Database Table
Contents: Unique identification numbers for configurable entities.
Primary key: ENTITY_ID.
The CATALOG_PROPERTY_KEY Database Table Contents: Unique identification numbers for scoped property names that are associated with configurable entities. Primary key: PROPERTY_KEY_ID.
The CATALOG_PROPERTY_VALUE Database Table Contents: Boolean, timestamp, float, integer, text, and user-defined (object) property values that are associated with configurable entities. Primary key: PROPERTY_VALUE_ID.
The WLCS_CATEGORY Database Table Contents: Categories in the Commerce database. The descriptions shown in the table reflect the "recommended best practice" for the use of that field by the Dublin Core standard. Primary key: CATEGORY_ID.
The WLCS_PRODUCT Database Table
Contents: Item records in the Commerce database.
Primary key: SKU.
The WLCS_PRODUCT_CATEGORY Database Table Contents: Shows which product items are associated with product categories. Primary key: SKU and CATEGORY_ID.
The WLCS_PRODUCT_KEYWORD Database Table Contents: Keywords that you associate with each product item. The keywords enable rapid retrieval of item records via the search functions on the Web site's pages or Administration pages. Primary key: KEYWORD and SKU.
Defined Constraints for Product Catalog Tables Various constraints are defined and used in the Product Catalog database schema. These constraints can be found in the following scripts: wlcs_create_fkeys.sql—contains the Foreign Keys wlcs_create_tables.sql—contains the Check Constraints
Order and Discount Database Schemas
This section describes the database schema for Order services.
This section includes information on the following subjects:
The Entity-Relation Diagram for the Order and Discount Tables
Figure A-3 shows the logical Entity-Relation diagram for the WebLogic Portal order and discount tables in the WebLogic Portal database. See the subsequent sections in this chapter for information about the data type syntax.
Figure A-4 Entity-Relation Diagram for the Order and Discount Tables
The Order Processing Data Dictionary Tables Note: Even though the following documentation references "foreign keys" to various tables, these constraints do not currently exist in this release of Commerce services. However, they will be in place in future versions of Commerce services and we want you to be aware of these relationships now. The Commerce services order management system has the following tables:
The DISCOUNT Database Table
Contents: One or more discount records for every DISCOUNT_SET record.
Primary key: DISCOUNT_ID.
The DISCOUNT_ASSOCIATION Database Table Function: Associates each customer with a discount and maintains information regarding the times the customer has used each discount. Primary key: DISCOUNT_ASSOCIATION_ID.
The ORDER_ADJUSTMENT Database Table Contents: Information about a discount taken at the order level (for example, $20.00 off any order between 1/1/02 and 1/31/02.) Primary key: ORDER_ADJUSTMENT_ID.
The ORDER_LINE_ADJUSTMENT Database Table Contents: Information about a discount taken at the order line item level (for example, 10% off SKU "Power Drill"). Primary key: ORDER_LINE_ADJUSTMENT_ID.
The WLCS_CREDIT_CARD Database Table Contents: Information related to a customer's credit card(s) in the order processing database. Primary key: CREDIT_CARD_ID.
The WLCS_CUSTOMER Database Table Contents: Information about the customer in the order processing database. Primary key: CUSTOMER_ID.
The WLCS_ORDER Database Table Contents: Information about a customer's specific order in the order-processing database. Note: The Commerce services product does not populate the SHIPPING_AMOUNT, SHIPPING_CURRENCY, PRICE_AMOUNT, or PRICE_CURRENCY columns. Primary key: ORDER_ID.
The WLCS_ORDER_LINE Database Table Contents: Information about each line of a customer's shopping cart in the order processing database. Primary key: ORDER_LINE_ID.
The WLCS_SAVED_ITEM_LIST Database Table
Contents: Information about the customer's saved shopping cart items in the order processing database.
Primary key: None.
The WLCS_SECURITY Database Table Function: Persists public and private keys for encryption and decryption purposes in the order processing database. This table is meant for internal use by the Commerce services product. Primary key: None.
The WLCS_SHIPPING_ADDRESS Database Table Contents: Information related to a customer's shipping address(es) in the order processing database. Primary key: SHIPPING_ADDRESS_ID.
The WLCS_SHIPPING_METHOD Database Table Contents: Information about the shipping method in the order processing database. Primary key: PK_IDENTIFIER.
The WLCS_TRANSACTION Database Table Contents: Data for every payment transaction in the order processing database. Primary key: TRANSACTION_ID.
The WLCS_TRANSACTION_ENTRY Database Table Function: Logs the different states a payment transaction has passed through in the order processing database. Primary key: TRANSACTION_ENTRY_ID.
Defined Constraints in the Order Database Schema Various constraints are defined and used in the Order database schema. These constraints can be found in the following scripts: wlcs_create_fkeys.sql - Contains the Foreign Keys wlcs_create_tables.sql - Contains the Check Constraints
Personalization Database Schemas
This section documents the database schema for WebLogic Portal personalization features.
This section includes information on the following subjects:
The Entity-Relation Diagram for WebLogic Portal Personalization
Figure 12-1 shows the logical Entity-Relation diagram for the WebLogic Portal personalization database.
Figure A-5 Entity-Relation Diagram for WebLogic Portal Personalization
Figure A-6 Entity-Relation Diagram for WebLogic Portal Personalization Continued
List of WebLogic Portal Personalization Tables
WebLogic Portal's personalization features use the following tables. In this list, the tables are sorted by functionality: Ads and Placeholders tables
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_METADATA Database Table
Mail tables
The MAIL_ADDRESS 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 WebLogic Portal
The ENTITLEMENT_RULESET Database Table Database Table
The PROPERTY_KEY Database Table
The PROPERTY_VALUE Database Table
The SAMPLE_UUP_INFO Database Tablee
The WEBLOGIC_IS_ALIVE Database Table
The Personalization Server Data Dictionary
In this section, WebLogic Portal personalization 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 Portal. However, they will be (available in future releases) in place in future versions of WebLogic Portal 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.
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.
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.
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. The Primary Key is DATA_SYNC_ITEM_ID.
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.
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.
The DOCUMENT Database Table Table 12-7 describes the DOCUMENT table. This table is used to store information pertinent to each document used within WebLogic Portal. The Primary Key is ID.
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. The Primary Key is comprised of both ID and 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.
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.
The GROUP_HIERARCHY Database Table Table 12-11 describes the PARENT_CHILD_GROUP table. This table stores relationship information between groups. The Primary Key is comprised of both PARENT_GROUP_ID and CHILD_GROUP_ID.
The GROUP_SECURITY Database Table Table 12-12 describes the GROUP_SECURITY table. This table holds all groups that a user could be given membership to for security authentication of the rdbms realm.
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. The Primary Key is MAIL_ADDRESS_ID.
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.
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. The Primary Keys are BATCH_ID and MESSAGE_ID.
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. The Primary Key is HEADER_ID.
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.
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.
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.
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. The Primary Key is PROPERTY_VALUE_ID.
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.
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. The Primary Key is SEQUENCE_NAME.
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.
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. The Primary Key is comprised of both GROUP_ID and USER_ID.
The USER_PROFILE Database Table Table 12-25 describes the USER_PROFILE table. This table associates users with profiles (such as the WLCS_CUSTOMER user profile). User profiles use property sets to organize the properties that they contain. The Primary Key is USER_NAME.
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.
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.
Defined Constraints Various constraints are defined and used in the WebLogic Portal personalization services 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
Portal Database Schemas
This section documents the database schema for the WebLogic Portal package.
This section includes information on the following subjects:
The Entity-Relation Diagram for the Portal and Portlet Tables
Figure 1-1 shows the logical Entity-Relation diagram for the WebLogic Commerce Server Portal and Portlet tables in the Commerce database. See the subsequent sections in this chapter for information about the data type syntax.
Figure 1-1 Entity-Relation Diagram for the Portal and Portlet Tables
List of Tables Comprising the Portal Management Package Portal Management tables:
The PORTAL_P13N Database Table
The PORTAL_P13N_LAYOUT Database Table
The PORTAL_P13N_SKIN_POOL Database Table
The PORTAL_PAGE Database Table
The PORTAL_PAGE_P13N Database Table
The PORTAL_PAGE_P13N_LAYOUT_POOL Database Table
The PORTLET_P13N Database Table
The PORTLET_PLACEHOLDER Database Table
The RESOURCE_GROUP_ADMIN Database Table
The Portal Management Data Dictionary
In this section, the Portal, Portlet, and Sample Portal schema tables are arranged alphabetically as a data dictionary.
The LAYOUT Database Table
Table 1-1 describes the LAYOUT table. This table stores all of the defined layouts.
The Primary Key is LAYOUT_ID.
The PORTAL Database Table Table 1-2 describes the PORTAL table. This table stores all of the defined portals. The Primary Key is PORTAL_ID.
The PORTAL_P13N Database Table Table 1-3 describes the PORTAL_P13N table. This table stores all of the personalized portal records. The Primary Key is PORTAL_P13N_ID.
The PORTAL_P13N_LAYOUT Database Table Table 1-4 describes the PORTAL_P13N_LAYOUT table. This table stores all available layouts for each of the personalized portals. The Primary Key is comprised of both LAYOUT_ID and PORTAL_P13N_ID.
The PORTAL_P13N_SKIN_POOL Database Table Table 1-5 describes the PORTAL_P13N_SKIN_POOL table. This table stores all available skins for a personalized portal. The Primary Key is comprised of both PORTAL_P13N_ID and SKIN_ID.
The PORTAL_PAGE Database Table Table 1-6 describes the PORTAL_PAGE table. This table stores all of the defined pages for each portal. The Primary Key is PORTAL_PAGE_ID.
The PORTAL_PAGE_P13N Database Table Table 1-7 describes the PORTAL_PAGE_P13N table. This table stores information for each personalized portal page. The Primary Key is PORTAL_PAGE_P13N_ID.
The PORTAL_PAGE_P13N_LAYOUT_POOL Database Table Table 1-8 describes the PORTAL_PAGE_P13N_LAYOUT_POOL table. This table stores all available layouts for each of the personalized portal pages. The Primary Key is comprised of both PORTAL_PAGE_P13N_ID and LAYOUT_ID.
The PORTLET Database Table Table 1-9 describes the PORTLET table. This table stores all of the defined portlets. The Primary Key is PORTLET_ID.
The PORTLET_P13N Database Table Table 1-10 describes the PORTLET_P13N table. This table holds all personalized portlet records. The Primary Key is PORTLET_P13N_ID.
The PORTLET_PLACEHOLDER Database Table Table 1-11 describes the PORTLET_PLACEHOLDER table. The Primary Key is PORTLET_PLACEHOLDER_ID.
The RESOURCE_GROUP_ADMIN Database Table Table 1-12
The Primary Key is comprised of both RESOURCE_GROUP_TAXONOMY and DELEGATED_TO_USER_NAME.
The SKIN Database Table Table 1-13 describes the SKIN table. This table holds all of the defined skins. The Primary Key is comprised of SKIN_ID.
Defined Constraints Various constraints are defined and used in the Portal database schema. These constraints can be found in the following scripts: portal_create_fkeys.sql—contains the Foreign Keys portal_create_tables.sql—contains the Check Constraints