Authoring environment schema update tool

The content management solution in WebSphere Commerce introduces multiple database schemas on the authoring environment that require each and every table to have a definition within each schema. The definition within the workspaces schemas differ depending on whether the table is considered to be one of the following resources:

• Managed content resource

• Operational resource

• Unmanaged resource

These resources are specified in the resource.xml configuration files (all unspecified tables are considered unmanaged resources).

During instance creation of the authoring environment, new database schemas are created based on all the tables and views defined in base schema at the time. Previously, if the base schema was modified in any way, for example new tables were added or fix packs installed, the authoring schemas were updated manually. With the introduction of the authoring environment schema update tool we can now update the workspaces schema easily.

The authoring update tool makes the customization and installation process simpler, by having the workspace management schemas allocated, calibrated or patched by running an Ant script in the server environment, and a batch file in the development environment. This means that when you change the schema, we can run the Ant script or batch file on an authoring environment and determine any new tables that must be managed into the workspace schemas. As a result, you will no longer have to follow the manual steps to adjust the workspace schemas. We can also use this tool to adjust your workspace schemas when creating our own content managed resources. If you introduce new tables, we can run the Ant script or batch file through a command line interface.

The following Ant scripts are used in the server environment:

• UpdateWorkspacesSchemaTable

• UpdateWorkspacesSchema

(Developer) The following batch files are used in the development environment:

• UpdateWorkspacesTable.bat

• UpdateWorkspacesSchema.bat

When to use the authoring environment update tool

If we deploy changes to the WebSphere Commerce database schema, such as adding a new table, these changes must also be made in the workspace schemas. This tool allows us to recalibrate the workspace schemas with the master database schema. Without recalibration, the authoring environment will not work with your new changes to the base schema, and may cause data lost or inconsistency.

Note: The WebSphere Commerce Server must not be running since this tool updates the workspace schema structure.

Supported functions

The authoring environment update tool supports the following functions:

• Adjusts the workspace schemas according to the resource type of the table in the following situations:

  • You add new tables to the base schema.

    Note: Content-managed tables must have a primary key defined.

  • You increase the size of a column in any existing table in the base schema.

    Note: Only column types that are allowed to be increased by the ALTER SQL command will be adjusted. The list of column types supported for column size increase are VARCHAR and VARCHAR2.

  • You change an existing non-managed type table to a content-managed type table.

  • You change an existing non-managed type table to an operational type table.

• Adds new columns to all of the corresponding workspace tables according to the resource type of the table.

• Adds new indexes or deletes existing indexes for any existing table.

• Gives users the option to generate the "update SQLs" statement to a file or have the tool directly implement the "update SQLs" statement.

Note: None of the supported functions delete tables or data from the existing workspace schemas. If a table gets deleted in the base schema, the table in the write schema of the workspace for any content-managed or operational table remains. You will not lose any data after running this tool.

Non-supported functions

The following functions are not provided by the workspace schema update tool:

• Changing an existing content-managed type table to a non-managed type table. This is not supported because it requires removing tables from the existing workspaces and can result in data loss. IBM recommends doing a manual update to the workspace as described in the customization guide for this scenario.

• Changing an existing operational type table to a non-managed type table. This is not supported because it requires removing tables from the existing workspaces and can result in data loss. It is recommended doing a manual update to the workspace as described in the customization guide for this scenario.

• Dropping a table in the base schema. This is not supported as the recommended way for removing a table in WebSphere Commerce is by deprecating a table and not dropping a table. If we must drop a table, IBM recommends to do a manual update to the workspace as described in the customization guide.

• Running the authoring environment update scripts on the WebSphere Commerce Server
  

Related tasks
Enable Workspaces
Creating an authoring environment
Synchronizing an authoring environment with a production environment
Running the authoring environment update scripts on the WebSphere Commerce Server
Updating the quick publish target
Updating the JDBC provider setting for the quick publish target
Enable retry for quick publish
Enable e-mail notification for workspaces
Enable shopping flow preview for workspaces
Changing workspaces locking policy

Related reference
Ant target: CM_updateWorkspacesTable
Ant target: CM_updateWorkspacesSchema
Troubleshooting: Update Workspace failing to update the workspace because of missing column for primary key