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

• Types of Database Tables

• Types of Columns (Fields)

• About Adding to the System Tables

• About Property Files and Databases

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:

• Object Tables

• Tree Tables

• Content Tables

• Foreign Tables

• System Tables

• Identifying a Table's Type

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

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
nid	The ID of the node. This is the primary key.
nparentid	The ID of the node's parent node.
nrank	A number that ranks peer or sibling nodes. For example, the AssetRelationTree table uses this column to determine the order of the assets that are in collections.
otype	The object type of the node. For example, in the SitePlanTree table, otype is either the asset type page or the name of a site (publication). In the AssetRelationTree table, otype is an asset type and is the name of the object table for assets of that type.
oid	The ID of the object that the node refers to.
oversion	Reserved for future use.
ncode	Holds a string that has meaning in the context of what the table is being used for. For example, in the SitePlanTree, ncode is set to placed or unplaced based on whether the page asset that the node refers to has been placed or not. In the AssetRelationTree, ncode holds the name of an association.

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.

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.

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
ElementCatalog	Lists all the XML or JSP elements used in your system. An element is a named piece of code.
SiteCatalog	Lists a page reference for each page or pagelet served by WebCenter Sites.
SystemACL	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.
SystemEvents	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 APPEVENT or EMAILEVENT tags.
SystemInfo	Lists all the tables that are in the WebCenter Sites database and any foreign tables that WebCenter Sites needs to reference.
SystemSeedAccess	Registers Java classes that are external to WebCenter Sites but that WebCenter Sites has access to (includes access control).
SystemSQL	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 ics.CallSQL method, CALLSQL XML tag, the ics:callsql JSP tag to invoke them. Then, you have to modify the SQL statement only once, if required.
SystemUserAttr	Stores attribute information about the users such as their email addresses. Note that this table is not used for LDAP.
SystemUsers	Lists all the users who are allowed access to pages, functions, and tables. Note that this table is not used for LDAP.

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:

1. Open Explorer and log in to the WebCenter Sites database.
2. Double-click the SystemInfo table.
3. 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
yes	system table
no	content table
obj	object table
tree	tree table
fgn	foreign table

Note:

You cannot open and examine the SystemInfo table without the appropriate ACLs assigned to your user name.

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:

• Generic Field Types

• Database-Specific Field Types

• Indirect Data Storage with the WebCenter Sites URL Field

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
CHAR(n)	A short string of exactly n characters.	cc.char
VARCHAR(n)	A short string of up to n characters. For example, VARCHAR(32) means that this column can hold a string of up to 32 characters.	cc.varchar and cc.maxvarcharsize (The maximum value that you can set for cc.varchar depends on the value of the cc.maxvarcharsize property.)
DATETIME	A date/time combination.	cc.datetime
TEXT	A LONGVARCHAR, a variable-length string of up to 2,147,483,647.	cc.bigtext
IMAGE	One binary large object (blob).	cc.blob
SMALLINT	A 16-bit integer, that is, an integer from -32,768 to +32,767.	cc.smallint
INTEGER	A 32-bit integer, that is, an integer from -2,147,483,648 to +2,147,483,647.	cc.integer
BIGINT	A 64-bit integer, that is, integers having up to 19 digits.	cc.bigint
NUMERIC(L,P)	A floating-point (real) number, having a total number of L significant digits of which up to P significant digits are fractional. For example, NUMERIC(5,2) could represent a number such as 806.35 but could not accurately represent a number such as 25693.2283.	cc.numeric
DOUBLE	A double precision type.	cc.double

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
NULL	It can hold a null value, that is, it can be left empty.
NOT NULL	It cannot hold a null value, that is, it cannot be left empty.
UNIQUE NOT NULL	It must hold a value that is guaranteed to be unique in this table.
PRIMARY KEY NOT NULL	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.

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.

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 the wcs_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 of defdir.

• 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 a defdir 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.

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
SiteCatalog	There are several ways that page entries are added to this table: When you create a Template asset, WebCenter Sites automatically creates a page entry for it in the SiteCatalog table. When you create a SiteEntry asset, WebCenter Sites automatically creates a page entry for it in the SiteCatalog 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.
ElementCatalog	There are several ways that elements are added to this table: When you create a Template asset, WebCenter Sites automatically creates an entry for it in the ElementCatalog table. When you create a CSElement asset, WebCenter Sites automatically creates an entry for it in the ElementCatalog table. You can use the Explorer tool to add non-asset elements. For information about coding elements and pages, see Coding Elements for Templates and CSElements.
SystemACL	The ACL form in the User Access Management node.
SystemEvents	WebCenter Sites adds a row to this table for each event that is designated when an APPEVENT tag, EMAILEVENT tag, or Java API equivalent is invoked from an element.
SystemInfo	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.
SystemSQL	The Explorer tool. For information about the various kinds of queries that are available, see Working with Resultset Caching and Queries.
SystemUsers	The User form in the User Access Management node.
SystemUserAttr	The User form in the User Access Management node.

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.