The Problem

WebObjects applications are most often developed with significant database dependance. When developing complex systems, complete database structures can not always be designed to support all future features. Therefore, while adding new features, one frequently needs to modify the structure of the database, or transform existing data in some way.

Structural database changes are often done manually and the required SQL is optionally stored in a file separate from the code base. The set of SQL operations most often has to be applied in sequence, and completely, for the system to operate properly. At deployment time, the production database needs to be updated to include the latest changes from the development database. With manual steps as part of the deployment process, there is significant opportunity for human error, resulting in a potential mismatch between the application and the database, or differences between the production and the development database. Unfortunately, these types of errors are not always easily identified, and sometimes lead to lengthly investigation and debug sessions.

Our Solution

We propose to alleviate the aforementioned problem by versioning the database. The current state of the database will be version x. When we need to change something, for example add a new table to support a new feature of our application, the database's version will become x+1. We also propose to create a new Java class for each new version. The responsibility of these classes will be to update the database from one version to the next by executing the required SQL, thus we call them the Update classes. Another class, the Updater will be able to determine the current version of the database and run the necessary Updates to bring it to the latest version. Finally, we suggest to run the Updater every time the application starts up. That way, we developers never need to worry about the state of the current development database (especially on big teams, where every programmer has her own database) or of the database we are deploying against. We call this approach the Database Updater, and our supplied implementation WODBUpdate.

Will I really need to make many changes to the database? In fact 60-80% of changes to an application occur after the first release of the product [1]. Introducing the concept of a Database Updater early into the project provides a mechanism to support these changes through the lifetime of the project.

The Database Updater is not only useful for structural changes but also for data manipulations. Imagine your WebObjects application manages user adresses. You have an entity State with a corresponding table containing the names of all US American states. Now you want to allow Canadians to use your application, too. For that, you simply create a new Update class that inserts the names of the Canadian provinces and territories into your State table.

Database Updater

The Database Updater is simple enough to be incorporated directly into the WebObjects application, though it could be abstracted and the core contained in a framework.

The basic algorithm of the Database Updater

In order to prevent multiple application instances from trying to update the database at the same time (and thereby likely corrupting it), a mutual exclusive lock is used. The first instance to acquire the lock performs the update and unlocks afterwards. Other instances wait for the lock to be released before resuming their startup.

A database table, _DBUPDATER [*], is used by the Updater to lock and unlock the database, and to store the version number.

The process of updating the database is:

1. getting the current version number from the database (for example 35)
2. incrementing the version number by one (36)
3. instantiating an Update class representing that version number (Update36.class)
4. executing the update (Update36)
5. writing the new version number to the database (36)
6. goto step 1.

The very first time we run the updater is special:

1. a database version will not be found and -1 is assumed instead
2. incremented to 0
3. Update0 will be instantiated - it is our bootstrapping update which creates our table _DBUPDATER

The update process is done when no more Update classes can be found. At this point the database is unlocked and control returns to the application which then continues starting up.

If an update fails, for instance because it contains invalid SQL for which the DBMS returns an error, the error is logged and the application terminates without unlocking the database. At this point the developer or a database administrator would need to investigate why the latest update failed, fix the update and revert the database to the state of the previous version. Then one would manually unlock the database with the following SQL before re-attempting the update:

update _DBUPDATER set UPDATE_LOCK = 0

Implementation Details

The update algorithm relies on a simple code structure and reflection to determine which class contains the next update. The update classes are named Update0, Update1, Update2 and so on. They are instantiated and executed by the Updater as follows:

String updatesClassNamesPrefix = Update.class.getName();
String className = updatesClassNamesPrefix + (currentDBVersion() + 1);
Update update = (Update)Class.forName(className).newInstance();
nextUpdate.setDatabase(database);
nextUpdate.performUpdate();

Example update:

public class Update27 extends Update {
    // make AllowOverTime mandatory, default existing rows to 0
    protected void executeUpdate() throws SQLException {
        executeSQL("UPDATE USER SET ALLOWOVERTIME = 0 "
                 + "WHERE ALLOWOVERTIME IS NULL");
        executeSQL("ALTER TABLE USER MODIFY(ALLOWOVERTIME NOT NULL)");
    }
}

Note that WODBUpdate assumes that Update and its subclasses are in the same package. See Database Updater As Framework if that is an issue for you.

These kind of update classes are in our experience understandable by database administrators who have no Java knowledge (should they need to get involved in case of a failure).

As new features requiring database changes are added, a new Update class is created with the next sequential number appended. Convenience methods can be added to the Update super-class, making each update as simple to understand as possible.

The Database class uses JDBC to connect with the underlying database. Earlier implementations used as much EOF as possible instead of JDBC, but then we realized that it was just complicating the code without buying us anything. [todo: How so/Why?]

The SQL in the supplied implementation has been written for FrontBase. Different DBMS products might require different SQL statements.

Class Diagram

Source

The supplied source code for WODBUpdate is seedwork [2]. This means it is not a reusable framework, it is not supported by the authors and there likely will not be any further updates. If you like what you see, you are welcome to take it and grow it to suit your individual needs. It would not take much effort to write your own updater from scratch if you desire to do so.

Source:

• Updater.java
• Databaseable.java
• Database.java
• Update.java
• Update0.java

Example Updates:

• Update1.java
• Update2.java

Unit Tests:

• UpdaterTest.java
• UpdateTest.java

Usage:

• Application.java

Issues

Some of the technical and political issues that we have experienced are outlined below. In Extensions, we allude to some measures that can be implemented to reduce the impact of some of the issues.

Extensions

WODBUpdate is an implementation presented as simply as possible. Depending on the type of project and the database being used, additional features can be useful, some of them to mitigate issues already described.

protected String tableName(String anUnformattedTableName) {
    return "\"" + schemaName + "\".\"" + anUnformattedTableName + "\"";
}

executeSQL("UPDATE " + tableName("PERSON") + " SET ADDRESSID = 1 "
         + "WHERE ADDRESSID IS NULL");

Conclusion

By providing an integrated, automated, and consistent way of maintaining database integrity with respect to the version of the application, the Database Updater reduces deployment misadventures, needless obscure bug hunts and trouble tickets. This results in a simpler deployment process, less required documentation, and happier developers!

There is not really anything WebObjects or even Java specific about the Database Updater, it just happened to be the context we are working in. Nothing should prevent you from using the same idea on any project, as long as it uses an object oriented programming language and an SQL database.

History

Peter Lyons came up with the original idea for a Database Updater in 2002 on a project at CodeFab where he and Ben Holt did the first implementation. Christian, working on the same project, implemented several update steps. Overall, close to 40 updates were implemented and it became clear that the team did not want to go back to the old-fashioned way of managing SQL scripts separate from the project's code.

We (James and Christian) later re-implemented the same idea on a different project. At the time of this writing, 100 update steps were implemented over a time frame of about 14 months. This was on an old project, where the updater was partly used for new features and partly for cleaning up old structures and data. Subsequent deployments of the application were noticeably faster and easier.

Peter Lyons reports [3] that he had used this method again on a .NET project using DotNetNuke [4], which grew to over 170 steps.

WODBUpdate is a new clean room implementation that we have written specifically for this article in February 2005.

Authors

The authors are software developers located in Calgary/Canada. James Seigel can be contacted at seigel@ieee.org and Christian Pekeler at christian@pekeler.org.

References