11 Working with the WebCenter Sites Database
Just about everything in WebCenter Sites is represented as a row in a database table. The WebCenter Sites modules and products (Oracle WebCenter Sites: Engage, for example) deliver most of the tables you need. You work with the various kinds of tables and columns in the WebCenter Sites database.
For information about managing the data in non-asset tables, see Managing Data in Non-Asset Tables.
Note:
WebCenter Sites database tables used to be called catalogs and there are still remnants of that terminology throughout the application in table names, servlet names (CatalogManager), and the Java interfaces that you use to work with data in the database.
These topics can help you develop your own application or a table that does not hold assets (a lookup table, for example):
11.1 Types of Database Tables
In the WebCenter Sites database you use these tables: Object, Tree, Content, Foreign, and System.
-
Object tables, which hold data as objects and provide a unique identifier, automatically, for each row in the table.
-
Tree tables, which hold the hierarchical information about relationships between objects in object tables.
-
Content tables, which hold flat data and do not provide a unique identifier for each row.
-
Foreign tables, which can be either of the following:
-
Tables that are outside of the WebCenter Sites database but that WebCenter Sites has access to.
-
Tables that are in the WebCenter Sites database but that WebCenter Sites did not create.
-
-
System tables, which are core WebCenter Sites application tables whose schema cannot be modified.
WebCenter Sites can cache the resultsets from queries against any table in the WebCenter Sites database, including foreign tables.
This section includes the following topics:
11.1.1 Object Tables
Object tables store data as an object and can be represented in hierarchies. Those objects can be loaded, saved, and managed with the CatalogManager API. The asset type tables are object tables.
The primary key for object tables is always the ID (id
) column and that cannot be changed. When you instruct WebCenter Sites to add an object table, it creates an ID column in that table. ID is a unique identifier assigned by default to each row as it is added to the table. For example, when someone creates a new asset, WebCenter Sites determines the ID and assigns that value as the ID for that asset.
You cannot change the ID assigned to objects (such as assets).
Note:
When AssetMaker or Flex Family maker creates an object table for a new asset type, it creates several additional columns by default. For information about the default columns in basic asset tables, see Default Columns in the Basic Asset Type Database Table.
WebCenter Sites handles ID generation for you, so use an object table to ensure that each row of the data you store is uniquely identified.
Examples of object tables (catalogs):
-
All tables that hold assets
-
Many of the publishing tables
-
The Engage tables that hold visitor data
11.1.2 Tree Tables
Tree tables store information about the hierarchical relationships between object tables. In other words, object tables can be represented in hierarchies, but the hierarchy itself is stored in a tree table (the hierarchy is the tree).
For example, WebCenter Sites adds these tables to the WebCenter Sites database:
-
AssetRelationTree
: Stores information about associations between assets. These associations create parent-child relationships. For information about asset associations, see The Flex Asset Model. -
SitePlanTree
: Stores information about parent-child relationships between page assets and the assets that are referred to from those assets. This information is presented graphically on the Site Navigation tab that is present in the WebCenter Sites interface.
Each row in a tree table is a node in that tree. Each node in a tree table points to two places:
-
To an object in an object table, that is, to the object that it represents.
-
To its parent node in that tree table, unless it is a top-level node and has no parent.
In other words, the object itself is stored in an object table. Its relationships to other objects in the database (as described by the tree) are stored in the tree table as a node on a tree. Children nodes point to parent nodes but parents do not point to children.
When you create a tree table, it has columns described in Table 11-1 by default. You cannot modify these columns or add new ones.
Table 11-1 Default Columns
Column | Description |
---|---|
|
The ID of the node. This is the primary key. |
|
The ID of the node's parent node. |
|
A number that ranks peer or sibling nodes. For example, the |
|
The object type of the node. For example, in the |
|
The ID of the object that the node refers to. |
|
Reserved for future use. |
|
Holds a string that has meaning in the context of what the table is being used for. For example, in the |
11.1.3 Content Tables
Content tables store data as flat data (rather than as objects) and that information cannot be organized in a hierarchy. You use content tables for simple lookup tables. For example, these are only a few of the content tables that add to the WebCenter Sites database:
-
Source
: Holds strings that are used to identify the source of an article or image asset. -
Category
: Holds codes that are used to organize assets in several ways. -
StatusCode
: Holds the codes that represent the status of an asset.
All three of these tables are lookup tables that the product uses to look up values for various columns in the asset type tables (object tables).
In another example, WebCenter Sites also adds a content table called MimeType
. This table holds mimetype codes that are displayed in the Mimetype fields of the stylesheet and imagefile asset types. The Mimetype fields for these asset types query the MimeType
table for mimetype codes based on the keyword
column in that table.
Setting the Primary Key for a Content Table
When you create a content table, an ID column is not created for you and the primary key is not required to be ID. This is another major difference between content tables and object tables.
The cc.contentkey
property in the wcs_properties.json
file specifies the name of the default primary key for all content tables. When you create a new content table, you are responsible for defining a column with the name specified by the cc.contentkey
property. However, you can override the identity of the primary key for a specific content table by adding and setting a custom property in the wcs_properties.json
file. This property must use the following format:
cc.tablenameKey
For example, to create a content table named Books
and to override the default primary key so that it uses the ISBN
column instead, add a property named cc.BooksKey
and set it to ISBN
.
11.1.4 Foreign Tables
A foreign table is one that WebCenter Sites does not completely manage. For example, a site's pages perform queries against a table that is populated by an ERP system and WebCenter Sites displays that information to the site visitors.
WebCenter Sites can query foreign tables and cache the resultsets just as it does for its own object and content tables. However, you must first identify that foreign table to WebCenter Sites by adding a row for it in the SystemInfo
table. This is the only time you should ever modify information in the SystemInfo
table. Additionally, you must be sure to flush the WebCenter Sites resultset cache with a CatalogManager flushcatalog
tag whenever the external system updates the tables that you query. Otherwise, the resultsets cached against those tables might not be up-to-date.
For information about resultset caching, see Working with Resultset Caching and Queries.
11.1.5 System Tables
System tables are core, WebCenter Sites tables whose schema is fixed. They are implemented in WebCenter Sites by their own classes and they do not follow the rules (for caching and so on) that the other tables follow.
You can add rows to some system tables using the Explorer tool, but you cannot add or modify the columns in these tables in any way. You also cannot add system tables to the database.
This table lists and defines the WebCenter Sites system tables:
Table 11-2 System Tables
Table | Description |
---|---|
|
Lists all the XML or JSP elements used in your system. An element is a named piece of code. |
|
Lists a page reference for each page or pagelet served by WebCenter Sites. |
|
Has a row for each of the access control lists (ACLs) that were created for your WebCenter Sites system. ACLs are sets of permissions to database tables. |
|
Has a row for each event being managed by WebCenter Sites. An event represents an action that takes place on a certain schedule. WebCenter Sites inserts a row in this table when you set an event by using either the |
|
Lists all the tables that are in the WebCenter Sites database and any foreign tables that WebCenter Sites needs to reference. |
|
Registers Java classes that are external to WebCenter Sites but that WebCenter Sites has access to (includes access control). |
|
Holds SQL queries that you can reuse in as many pages or pagelets as necessary. You can store SQL queries in this table and then use the |
|
Stores attribute information about the users such as their email addresses. Note that this table is not used for LDAP. |
|
Lists all the users who are allowed access to pages, functions, and tables. Note that this table is not used for LDAP. |
11.1.6 Identifying a Table's Type
To determine the table type of any table in the WebCenter Sites database, examine the SystemInfo
table, the system table that lists all the tables in the database.
To Determine a Table Type:
- Open Explorer and log in to the WebCenter Sites database.
- Double-click the
SystemInfo
table. - In the list of tables, examine the
systable
column. The value in this column identifies the type of table represented in Table 11-3.
Table 11-3 Determining Table Types
Value in systable column | Definition |
---|---|
|
system table |
|
content table |
|
object table |
|
tree table |
|
foreign table |
Note:
You cannot open and examine the SystemInfo
table without the appropriate ACLs assigned to your user name.
11.2 Types of Columns (Fields)
In the tables you create for the WebCenter Sites database, you can specify these field (column) types for the columns: Generic, Database-specific, and WebCenter Sites URL.
See these topics:
11.2.1 Generic Field Types
Generic field types refer to field types that work in any DBMS that WebCenter Sites supports. They are mapped to be compliant with JDBC standards. Therefore, if your WebCenter Sites system changes to a different DBMS, your database is still valid.
With generic, JDBC-compliant field types, use the CatalogManager API (CATALOGMANAGER
XML or JSP tags, or the ics.CatalogManager
Java method) to modify and maintain the data in your tables.
The following table contains a complete list of the WebCenter Sites generic field types and the database properties (from the wcs_properties.json
file) that define their data types. Refer to this list whenever you create a new table with the Explorer tool or the CatalogManager API.
Table 11-4 Field Types
Field Type | Description | Property |
---|---|---|
|
A short string of exactly n characters. |
|
|
A short string of up to n characters. For example, |
and
(The maximum value that you can set for |
|
A date/time combination. |
|
|
A |
|
|
One binary large object ( |
|
|
A 16-bit integer, that is, an integer from -32,768 to +32,767. |
|
|
A 32-bit integer, that is, an integer from -2,147,483,648 to +2,147,483,647. |
|
|
A 64-bit integer, that is, integers having up to 19 digits. |
|
|
A floating-point (real) number, having a total number of L significant digits of which up to P significant digits are fractional. For example, |
|
|
A double precision type. |
|
In addition to defining the column type, you must specify which of the column constraints, as described in Table 11-5, applies to the column.
Table 11-5 Column Constraints
Constraint | Description |
---|---|
|
It can hold a null value, that is, it can be left empty. |
|
It cannot hold a null value, that is, it cannot be left empty. |
|
It must hold a value that is guaranteed to be unique in this table. |
|
Marks the primary key column in a content table. You cannot set this column constraint for an object table. |
When you use AssetMaker to create an object table for a new asset type or when you create flex attributes, the data types for those items are different than the ones listed here.
See Storage Types for the Columns and Data Types for Attributes.
11.2.2 Database-Specific Field Types
You can use database-specific field (column) types in your tables. However, if you use field types that are specific to one kind of DBMS (that is, types that have not been mapped to a JDBC standard), note the following:
-
You may not be able to use the CatalogManager API on those tables.
-
If you ever change your DBMS you must also modify your tables.
For a complete list of field types specific to the DBMS that you are using, consult your DBMS documentation.
11.2.3 Indirect Data Storage with the WebCenter Sites URL Field
Object and content tables in the WebCenter Sites database have a unique characteristic that columns can store their data indirectly. You can store large bits of data externally to the DBMS but within the data repository.
To create such a column, you must use a column name that begins with the letters url
. When you use the letters url
as the first three letters of a column name, WebCenter Sites treats that column as an indirect data column.
Use a URL field for the following reasons:
-
When the DBMS you are using does not support fields that are large enough to accommodate the size of the data that you want to store there.
-
If the DBMS you are using does not support enough fields in an individual table to contain the data that you want to store.
-
Because the performance of selecting data degrades with large field sizes.
Note:
If the size of the data you want to store in a URL column exceeds the value set for the
cc.maxvarcharsize
property in thewcs_properties.json
file, then that data is stored in the database, instead of being stored indirectly as a file that is referenced by a pointer in the database.
The Default Storage Directory (defdir)
Any table with a URL column must have a default storage directory specified for it. This directory is where the values entered into the column are actually stored.
The phrase default storage directory is shortened to the word defdir in several places in the product. For example, the defdir
column in the SystemInfo
table holds the name of the default storage directory for tables with URL columns; one of the forms for the AssetMaker utility presents a defdir field, and so on.
The value entered into a URL field is actually a relative path to a file because this value is appended to the value of the table's defdir
setting. The way that you set the defdir
value for the tables that you create depends on the applications you have and what you are doing:
-
To create a new WebCenter Sites table with the CatalogManager API, use the
uploadDir
argument to set the value ofdefdir
. -
To create a new basic asset type, specify the value of the
defdir
in the defdir field on the AssetMaker form. Note that all tables that hold basic assets have a URL column and must have adefdir
value set. -
To create a new flex asset type, do not specify the value of the
defdir
for the URL column in the flex asset's_Mungo
table. This value is obtained from a property that was set when your WebCenter Sites application was installed. Never change the value of that property.Note:
After a table with a URL column is created, do not attempt to change or modify the
defdir
setting for the table in any way. If you do, the link between the storage directory and the URL column is broken, and your data can no longer be retrieved.
For information about creating URL fields, see the following procedures and examples:
-
The upload field examples for basic asset types, starting with Example 5-3 in Creating a Flex Asset Family.
-
The upload field example for creating flex attributes of type
blob
(Creating Flex Attributes of Type Blob (Upload Field)) in Creating a Flex Asset Family.
11.3 About Adding to the System Tables
You cannot create system tables, but you can add rows to some of them with the Explorer tool. How you add information to each of these tables is different.
Table 11-6 Methods of Adding Information to System Tables
Table | Method of Adding Information |
---|---|
|
There are several ways that page entries are added to this table:
To set or modify page cache settings for page entries, it is easier to use forms in the WebCenter Sites interface than it is to use Explorer. |
|
There are several ways that elements are added to this table:
For information about coding elements and pages, see Coding Elements for Templates and CSElements. |
|
The ACL form in the User Access Management node. |
|
WebCenter Sites adds a row to this table for each event that is designated when an |
|
Do not add or modify information to this table. The only exception to this rule is if you have to identify a foreign table to WebCenter Sites. |
|
The Explorer tool. For information about the various kinds of queries that are available, see Working with Resultset Caching and Queries. |
|
The User form in the User Access Management node. |
|
The User form in the User Access Management node. |
11.4 About Property Files and Databases
Database properties in the wcs_properties.json
file help you configure the WebCenter Sites database connection. You use these properties to establish a privileged and non-privileged user connection between the database and the application server.
The database properties were configured for your system when you installed WebCenter Sites. By default, all commands identified in the wcs_properties.json
file operate on the WebCenter Sites database. To access the properties in the wcs_properties.json
file, use the Property Management Tool in the WebCenter Sites Admin interface.