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.
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.
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 NULLNote: 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.
Add the following column to every table that represents an auxiliary or multi table in an item descriptor:
asset_version INT NOT NULLSee also Configure a Versioned Repository to Use Shared Tables.
Change all primary keys to composite primary keys, composed of the original primary key column(s) and the
asset_versioncolumn.Create an index for the
workspace_idandcheckin_datecolumns that are added to each primary table.Remove from all tables:
All foreign key references.
All unique constraints on columns. Also remove
uniqueattributes 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 | Description |
|---|---|
| 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:
A versioned repository cannot support more than two shares of a table in a single repository definition.
In a one-to-many relationship using a shared table, the table whose type is not
multicannot be a primary table. It must be an auxiliary table.
If you need to use table sharing with a versioned repository, also perform these steps:
To the shared table’s database schema definition, add the version column
sec_asset_version. This column must be defined as part of the primary key.Note: This name is configurable. To change it, use the
secondAssetVersionColumnNameproperty in the versionRepository component.In the repository definition that includes the shared table, specify an additional attribute called
shared-table-sequence. Add the attribute to the<table>tag of the shared table (along withnameandmulti-column-name, for example). Theshared-table-sequenceattribute takes values 1, 2, 3, and so on. The side that wants to associate thesec_asset_versioncolumn as its asset version column must have a value of 2 for this attribute. The table tag where this attribute is set to 2 is considered to be the owner of thesec_asset_versioncolumn, meaning that the side that wants to associate the column as its asset version column must have a value of 2 for this attribute. The value must not be changed after data is entered into the shared table.
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.
