In order to store asset version metadata, the asset management server’s database schema requires additional columns for the primary tables used by your application’s repositories, and additional tables to store versioning information. This section describes the following tasks:

Note: You only create the versioned schema for repositories that store application repository assets. ATG Content Administration provides the versioned schema for the PublishingFileRepository, which stores the metadata for application file assets.

See Appendix A, Database Schema for descriptions of the ATG Content Administration tables and columns.

Create the Versioned Schema

To modify the asset management server’s database schema to store versioning data, follow these steps:

Note: Repeat this process for each VersionRepository for which you need to create a DDL file.

  1. Copy each database DDL file that you plan to modify.

    Note: Do not edit the original DDL files to be used with the production database; only modify the copies.

  2. Add the following columns to every table that represents a primary table for an item descriptor:

    asset_version      INT           NOT NULL
    workspace_id       VARCHAR(40)   NOT NULL
    branch_id          VARCHAR(40)   NOT NULL
    is_head            NUMERIC(1)    NOT NULL
    version_deleted    NUMERIC(1)    NOT NULL
    version_editable   NUMERIC(1)    NOT NULL
    pred_version       INT           NULL
    checkin_date       TIMESTAMP     NULL

    Note: If the existing schema has a column that uses one of these names, you can use a different name for the new column. In this case you must also configure the VersionRepository component to override the default column name and use the new name. See Modify the Repository Configuration for more information.

  3. Add the following column to every table that represents an auxiliary or multi table in an item descriptor:

    asset_version   INT   NOT NULL

    See also Configure a Versioned Repository to Use Shared Tables.

  4. Change all primary keys to composite primary keys, composed of the original primary key column(s) and the asset_version column.

  5. Create an index for the workspace_id and checkin_date columns that are added to each primary table.

  6. Remove from all tables:

    • All foreign key references.

    • All unique constraints on columns. Also remove unique attributes from all <property> tags. For more information, see the discussion on unique properties in the ATG Repository Guide.

    • All unique indexes on columns.

The following table describes the additional columns that are required for all primary tables:

Column Name



Counter that specifies the version of the asset.


ID used to persist the branch of a version. ATG Content Administration always sets this to the ID of the main branch.


Flag that determines whether a version is the head of a branch.


Stores the check-in date of a version. Null for working versions of assets.


Flag that indicates whether the asset version is a deleted version.


Flag that indicates whether the asset version is an editable version. That is, the version is a working version in a workspace, where modifications to it can be made.


The asset version upon which this version was based. For example, if you create version 2 by checking out version 1, version 2’s predecessor version is version 1.


The ID of the workspace where the asset version was initially created.

For example, the original DDL for table type_x might look like this:

create table type_x (
       type_x_id       VARCHAR(16)    NOT NULL,
       name            VARCHAR(128)   NULL,
       type_y_ref_id   VARCHAR(16)    NULL,
       FOREIGN KEY (type_y_ref_id) REFERENCES type_y (type_y_id),
       PRIMARY KEY (type_x_id)

The modified DDL for table type_x DDL looks like this:

create table type_x (
       type_x_id          VARCHAR(16)    NOT NULL,
       asset_version      INT            NOT NULL,
       branch_id          VARCHAR(40)    NOT NULL,
       is_head            NUMERIC(1)     NOT NULL,
       version_deleted    NUMERIC(1)     NOT NULL,
       version_editable   NUMERIC(1)     NOT NULL,
       workspace_id       VARCHAR(40)    NOT NULL,
       pred_version       INT            NULL,
       checkin_date       TIMESTAMP      NULL,
       name               VARCHAR(128)   NULL,
       type_y_ref_id      VARCHAR(16)    NULL,
       PRIMARY KEY (type_x_id, asset_version)
create index type_x_workspace_id on type_x (workspace_id);
create index type_x_workspace_id on type_x (checkin_date);
Configure a Versioned Repository to Use Shared Tables

The following constraints apply to versioned repositories with shared database tables:

If you need to use table sharing with a versioned repository, also perform these steps:

Install the Versioned Database Schema

After you configure your database for ATG Content Administration (described in the previous chapter, Setting Up an Asset Management Server), finish installing your versioned database schema by running the custom DDL scripts that you created earlier.