SpiderLogic.com

Spider Architecture Series

Database Versioning

Mike Dietz
Al Wick

Abstract

This article discusses the maintenance of local client databases for applications that must operate in “disconnected mode” – without a server connection.

Overview

There are a number of issues inherent with rich-client applications. There are even more issues when the client application must be able to run independently, even when a server connection is unavailable. One set of those issues relate to data storage.

The client application must have a mechanism for storing data locally when there is no server connection. Typically, a local database will be used for temporary offline data storage, and a data synchronization process will be implemented.

With a local database, the maintenance process has to accommodate not only the updating of the client software, but must also have a mechanism for updating the client database, when the database structure inevitably changes. An additional requirement for this process is that the process must update the server database, which has the same structure as the client database.

Detailed Requirements

The main goals for this functionality were described above. More specifically, the process must:

Maintain the structure of a database, to correspond with the current version of the application.
Must be able to upgrade, even if multiple versions behind
Must be able to modify the database without loosing data
Allow adding and modifying of tables
Allow adding, deleting, and renaming columns
Allow changes to indices
Allow changes to views
Allow the specification of default values for inserted columns
Allow the execution of custom (free-form) SQL statements

Upgrade Process

There are three main parts to the process:

The application component for managing the upgrade process
The specification of the schema for each version of the database
The generation of the SQL for updating the database, using the schema specification

Within the application, there is a component dedicated to managing the upgrade process. It is responsible for creating / updating the database. When the application starts up, the component will check the database version (stored in a particular table within the database). It will then compare the database version against the required database version specified within the application and, if it is not current, it will initiate the database upgrade process. The upgrade is done using a generated SQL script, as described below.

For each version of the database, an XML representation of the database schema will be created. The XML document will contain an element for each table in the database, and those elements will contain elements for each column. Each column element will contain attributes for the column name and data type and an optional attribute for the new column name, to accommodate the renaming of columns. A default value can also be specified for new columns. Within the table element are also elements for the primary key and other indices. There can also be an optional CDATA element for the table, which can be used to specify any type of SQL statement to be executed as part of the conversion process. The XML document may also contain elements to define any required database views.

To create the SQL for updating each database, an XSL transformation is applied to the XML document representing the schema for the current version of the database. Because the SQL for updating the client database must be different from the SQL for updating the server database, there are separate XSL transformations for each; both use the same XML schema representation document, however. The main steps for updating a table are:

Drop all of the indices for the table
Rename the existing table (i.e., to “old_<tablename>”)
Create the new table with the new indices
Copy the data from the old table to the new table, performing any specified transformations
Initialize any new columns
Drop the old table

Summary

There are a number of issues with maintaining rich-client applications. One significant issue is the upgrading of the client software. If each installation of the client application requires its own database instance (to allow the application to run without a network connection, for example), then the database must upgraded as required to support new features in the software. An automated database upgrade process, as described herein, can mitigate this issue.