Just about everything in WebCenter Sites, its modules, and WebCenter Sites products is represented as a row in a database table.
This chapter describes the various kinds of tables and columns in the WebCenter Sites database and presents procedures for creating tables. The WebCenter Sites modules and products (Engage, for example) deliver most of the tables that you need. However, if you are using WebCenter Sites to develop your own application or you need to use a table that does not hold assets (a lookup table, for example) you create that table using one of the methods described in this chapter.
This chapter contains the following sections:
For information about managing the data in non-asset tables, see Chapter 13, "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.
There are five types of tables in the WebCenter Sites database:
Object tables, which hold data as objects and provides 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 contains the following topics:
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 that WebCenter Sites assigns 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 that WebCenter Sites assigns 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 Section 11.2.4.2, "Default Columns in the Basic Asset Type Database Table."
Anytime you need to store data and you want to ensure that each row of that data is uniquely identified, use an object table because WebCenter Sites handles ID generation for you.
Examples of object tables (catalogs)
All tables that hold assets
Many of the publishing tables
The Engage tables that hold visitor data
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 the following tables to the WebCenter Sites database:
AssetRelationTree
: which stores information about associations between assets. These associations create parent-child relationships. For information about asset associations, see Section 11.3, "The Flex Asset Model."
SitePlanTree
: which 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 Plan 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. That object's relationships to other objects in the database (as described by the tree) are stored in the tree table as a node on a tree.
Note that children nodes point to parent nodes but parents do not point to children.
When you create a tree table, it has the following columns by default. You cannot add to or modify these 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 |
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 adds to the WebCenter Sites database:
Source
, which holds strings that are used to identify the source of an article or image asset
Category
, which holds codes that are used to organize assets in several ways
StatusCode
, which 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 Burlington Financial sample site asset types named stylesheet and imagefile. 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 futuretense.ini
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 futuretense.ini
file. This property must use the following format:
cc.tablenameKey
For example, if you create a content table named Books
and you want to override the default primary key so that it uses the ISBN
column instead, you would add a property named cc.BooksKey
and set it to ISBN
.
A foreign table is one that WebCenter Sites does not completely manage. For example, perhaps your site pages perform queries against a table that is populated by an ERP system and WebCenter Sites displays that information to your 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 Chapter 14, "Resultset Caching and Queries."
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 of the system tables (either using the WebCenter Sites Management Tools forms, found on the Admin tab of the WebCenter Sites interface, or the Oracle WebCenter Sites 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.
The following table lists and defines the WebCenter Sites system tables:
Table | Description |
---|---|
|
Lists all the XML or JSP elements used in your system. An element is a named piece of code. For more information about the |
|
Lists a page reference for each page or pagelet served by WebCenter Sites. For more information about the |
|
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. For information about creating ACLs, see the Oracle Fusion Middleware WebCenter Sites Administrator's Guide. For information about using ACLs to implement user management for your online site, Chapter 31, "User Management on the Delivery System." |
|
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. |
|
Holds information about specific items on pages that are cached (assets, for example): the identity of the item, the page it is associated with, and the time it was cached. |
|
Holds information about pages that are cached: the folder that it is cached to, the query used to generate the file name, the time it was cached, and the time that it should expire. |
|
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 if you are using LDAP, this table is not used. |
|
Lists all the users who are allowed access to pages, functions, and tables. Note that if you are using LDAP, this table is not used. |
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.
Open the Oracle WebCenter Sites Explorer and log in to the WebCenter Sites database.
Double-click on the SystemInfo table.
In the list of tables, examine the systable column. The value in this column identifies the type of table represented in the row:
Table 12-3 Determining Table Types
Value in systable column | Definition |
---|---|
|
system table |
|
content table |
|
object table |
|
tree table |
|
foreign table |
Note:
If you do not have the appropriate ACLs assigned to your user name, you cannot open and examine the SystemInfo table.
When you create new tables for the WebCenter Sites database, whether they hold assets or not, you can specify three general categories of field (column) types for the columns in those tables:
Generic field types
Database-specific field types
The WebCenter Sites URL field
This section contains the following topics:
Generic field types refers 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.
When you use generic, JDBC-compliant field types, you can 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 futuretense.ini
file) that define their data types. Refer to this list whenever you create a new table with the WebCenter Sites Management Tools forms, found on the Admin tree in the WebCenter Sites interface, or the CatalogManager API:
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 (blob). |
|
|
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 following column constraints applies to the column:
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 new flex attributes, the data types for those items are different than the ones listed here.
For more information about the data types for columns for basic asset types, see Section 15.1.3.2, "Storage Types for the Columns." For information about the data types for flex attributes, see Section 11.3.3.1, "Data Types for Attributes."
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.
Object and content tables in the WebCenter Sites database have a unique characteristic: columns can store their data indirectly, which means that 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.
Why 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 wish to store in a URL column exceeds the value set for the cc.maxvarcharsize
property in the futuretense.ini
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. Why a relative path? Because the value in a URL field 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:
If you create a new WebCenter Sites table with the WebCenter Sites Management Tools forms, found on the Admin tree in the WebCenter Sites user interface, and your table has a URL field, you enter the value for defdir in the File Storage Directory field in the "Add Catalog" (table) form.
If you create a new WebCenter Sites table with the CatalogManager API, you use the uploadDir
argument to set the value of defdir.
If you create a new basic asset type, you 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 a defdir value set.)
If you create a new flex asset type, you 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.
Important:
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, you will break the link between the storage directory and the URL column, which means that 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 Section 15.2.3.3, "Upload Example 1: A Standard Upload Field."
The procedure for creating flex attributes of type blob in the section Section 16.3.6.2, "Creating Flex Attributes of Type Blob (Upload Field)."
This section describes how to create object, tree, and content tables and how to register foreign tables (that is, identify them to WebCenter Sites). You cannot create or modify system tables.
This section contains the following topics:
There are three ways to create object tables:
Create tables that hold basic asset types. You must use AssetMaker, a WebCenter Sites utility located on the Admin tab. AssetMaker creates the object table for the asset type as well as the forms that you use to create assets of that type. For more information, see Chapter 15, "Designing Basic Asset Types."
Create tables that hold flex asset types. You must use Flex Family Maker, a utility located on the Admin tab. For more information, see Chapter 16, "Designing Flex Asset Types."
Create an object table that does not hold assets. Use the WebCenter Sites Management Tools, found on the Admin interface (or Oracle WebCenter Sites Explorer).
To create an object table that does not hold assets
Log in to WebCenter Sites as an administrator.
Select the site in which you want to work.
Select the Admin interface.
Select the Admin tab, then select Management Tools, and then Sites Database.
In the Sites Database window, select Add Table and click OK.
The Add Table form displays.
Click in the Table Name field and enter a name. Do not use the name of a table that already exists. You can enter up to 64 alphanumeric characters, including the underscore (_) character but not including spaces.
If your table will have a URL column (an upload column), click in the File Storage Directory field (that is, the defdir) and enter the path to the file directory that will store the data from the URL column. If the directory does not exist yet, WebCenter Sites will create it for you.
Click in the Access Privileges field to select which ACLs (access control lists) a user must have in order to access this table. For information about ACLs, see the Oracle Fusion Middleware WebCenter Sites Administrator's Guide.
Click in the Field Name column and enter the name of the field. Remember that to create a URL column that stores data as a file located in an external directory, you must start the field name with the letters url
. If you are creating a URL column, be sure that you have specified the file storage directory (defdir) for the data stored in this field (see step 7 of this procedure).
Click in the Field Type column and specify both the data type and column constraint for the column. Include a space between the data type and the column constraint.
For example: VARCHAR(32) NULL
or INTEGER NOT NULL
.
For a list of the valid data types and the column constraints, see Section 12.2.1, "Generic Field Types."
Click the Add button.
WebCenter Sites adds the table to the database.
To verify that your table has been added, open Oracle WebCenter Sites Explorer and examine the SystemInfo
table. Your new table should be included in the list with its systable
column set to obj
. If you specified a file storage directory, it is listed in the defdir
column.
Managing Data in Object Tables
There are several ways to modify and manage the data in object tables.
To create and modify assets, you use the WebCenter Sites and Engage applications. To extract assets from the database and then display them to the visitors of your delivery system, you use WebCenter Sites, Engage XML and JSP tags.
You can enter data into object tables that do not hold assets in one of the following ways:
Programmatically, by coding forms with the ics.CatalogManager
Java method or the CATALOGMANAGER
XML and JSP tags, the OBJECT
XML and JSP tags, and the WebCenter Sites SQL methods and tags, that prompt users for information and then to write that information to the database
Manually by using either the Oracle WebCenter Sites Explorer tool or a form in the WebCenter Sites Management Tools forms to add rows to the table.
Chapter 13, "Managing Data in Non-Asset Tables" presents information about the CatalogManager API and examples of adding rows to tables that do not hold assets.
If you are using WebCenter Sites modules or products, it is unlikely that you would need to create a tree table.
Tree tables are managed by the TreeManager servlet. To create a tree table (catalog), you use the ICS.TreeManager
Java method or the TREEMANAGER
XML or JSP tags. You cannot create a tree table (catalog) with the WebCenter Sites Management Tools.
For example:
<TREEMANAGER>
<ARGUMENT NAME="ftcmd" VALUE="createtree"/> <ARGUMENT NAME="treename" VALUE="ExampleTree"/>
</TREEMANAGER>
For a list of the columns that are created for tree tables, see Section 12.1.2, "Tree Tables." For information about the TreeManager methods and tags, see the Oracle Fusion Middleware WebCenter Sites Tag Reference.
The WebCenter Sites modules and products manage all the data in their tree tables. You should never attempt to manually modify information in any of the tree tables.
If you have any tree tables that you created to manage relationships between your own object tables (that is, object tables that do not store assets), you use the ICS.TreeManager
Java method or the TREEMANAGER
XML or JSP tags. These tags and methods use an FTValList
parameter, which describes the tree operation to be performed.
The following chapter, Chapter 13, "Managing Data in Non-Asset Tables" presents information about the CatalogManager API and examples of adding rows to tables that do not hold assets.
To create a content table, use the WebCenter Sites Management Tools
Log in to WebCenter Sites as an administrator.
Select the site in which you want to work.
Select the Admin interface.
Select the Admin tab, then select Management Tools, and then Sites Database.
In the Sites Database window, select Add Table and click OK.
The Add Table form displays.
Click in the Table Name field and enter a name. Do not use the name of a table that already exists. You can enter up to 64 alphanumeric characters, including the underscore (_) character but not including spaces.
If your table will have a URL column, click in the File Storage Directory field and enter the path to the file directory that will store the data from the URL column. If the directory does not exist yet, WebCenter Sites will create it for you.
Click in the Access Privileges field to select which ACLs (access control lists) a user must have in order to access this table. For information about ACLs, see the Oracle Fusion Middleware WebCenter Sites Administrator's Guide.
Click in the Field Name field and enter the name of the field. Remember that to create a URL column that stores data as a file located in an external directory, you must start the field name with the letters url
. If you are creating a URL column, be sure that you have specified the file storage directory (defdir) for the data stored in this field (see step 7 of this procedure).
Click in the Field Type column and specify both the data type and column constraint for the column. Include a space between the data type and the column constraint.
For example: VARCHAR(32) NULL
or INTEGER NOT NULL
.
Remember that you must specify a primary key column and that it must exactly match either the setting for the cc.contentkey
property or a custom property specified for this table in the futuretense.ini
file.
For example: INTEGER PRIMARY KEY NOT NULL
Click the Add button.
WebCenter Sites adds the table to the database.
To verify that your table has been added, open the Oracle WebCenter Sites Explorer tool and examine the SystemInfo
table. Your new table should be included in the list with the value in its systable
column set to no
. If you specified a file storage directory, it is listed in the defdir
column.
When you add non-asset tables to facilitate some function of your site, you then need to either customize your asset forms in WebCenter Sites or create your own forms to enter and manipulate that data.
Managing Data in Content Tables
There are several ways to modify and manage the data in content tables.
Most of the WebCenter Sites content tables have a WebCenter Sites form available from the Admin tab that you can use to edit or add data. For example, Source
and Category
. (MimeType
, however, does not.)
You can enter data into your custom content tables in one of the following ways:
If the table was created such that users or visitors supply data that is written into the table, you code forms with the ics.CatalogManager
Java method or the CATALOGMANAGER
XML and JSP tags along with the WebCenter Sites SQL methods and tags, to prompt users for information and to then write it to the database programmatically.
If the table is a simple lookup table that facilitates some function on your site, you enter data into it manually by using either the Oracle WebCenter Sites Explorer utility or the WebCenter Sites Management Tools window to add rows to the table.
Chapter 13, "Managing Data in Non-Asset Tables" presents information about the CatalogManager API and examples of adding rows to tables that do not hold assets.
Registering a foreign table means identifying the table to WebCenter Sites by adding a row for the table to the SystemInfo
table. Note that this is the only condition in which you should ever add a row to the SystemInfo
table or change information held in the SystemInfo
table in any way.
Open Oracle WebCenter Sites Explorer and log in to the WebCenter Sites database.
Double-click on the SystemInfo table.
Right-click the header for the tblname column and then select New from the context menu.
A new row appears.
In the new row, click in the tblname column and enter the name of the table.
Click in the defdir column and enter the path to the table.
Click in the systable column and enter fgn
.
Click in the acl column and enter the names of the ACLs that have access to the table.
Select File and then Save All.
Managing Data in a Foreign Table
You can use the ics.CatalogManager
Java method or the CATALOGMANAGER
XML and JSP tags and the WebCenter Sites SQL methods and tags to interact with a foreign table. When you use these methods or tags to update data in the foreign table, WebCenter Sites can flush its resultset cache as needed.
If you use a method external to WebCenter Sites to update a foreign table, you must be sure to also use the CATALOGMANGER
command flushcatalog
to instruct WebCenter Sites to flush the resultset cache for that table.
You cannot create system tables and with very few exceptions, you should always use the WebCenter Sites Management Tools to add rows to the system tables that you are allowed to add rows to. The way that information is added to each system table varies, as described in the following table:
Table 12-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:
Note that if you want 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 Oracle WebCenter Sites Explorer. |
|
There are several ways that elements are added to this table:
For information about coding elements and pages, see Chapter 28, "Coding Elements for Templates and CSElements." |
|
The ACL form in the WebCenter Sites Management Tools node. For more information, see the Oracle Fusion Middleware WebCenter Sites Administrator's Guide. |
|
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 need to identify a foreign table to WebCenter Sites. |
|
The Oracle WebCenter Sites Explorer tool. For information about the various kinds of queries that are available, see Chapter 14, "Resultset Caching and Queries." |
|
The User form in the WebCenter Sites Management Tools node. |
|
The User form in the WebCenter Sites Management Tools node. |
There are several properties in the futuretense.ini
file that control the connection to the WebCenter Sites database. These properties specify the configuration of the database and establish a privileged and non-privileged user connection between the database and the application server.
All of the database properties were configured for your system when your system was installed. By default, all commands operate on the WebCenter Sites database identified in the futuretense.ini
file. The futuretense.ini
file is located in this directory:
<installation_directory>/ContentServer/11.1.1.6.0/