Friday, May 12, 2006

Maintaining Database-Application Versioning

Maintaining Database-Application Versioning

One major challenge in product development is managing/tracking the database changes and versioning it. There are many things to consider developing a proper strategy for database version controlling.
  • Application should be able to identify the database version it is using
  • If the database is not up-to-date, the application should handle the problem gracefully with out bringing surprises to the users (either gracefully exit or upgrade the database).
  • Database also should be upgraded if the ‘Online Update’ (such as in ‘no-touch deployment’) strategy is used in the application.
  • If application has capabilities to upgrade the database then preferably it should be able to update the databases in various versions.
  • System should be able to track any improper database changes (e.g. uncontrolled manual changes by the customer)

After a little thought I came up with a model to solve the issue. Still I couldn’t experiment this in a real world project. Here is an outline of the model:
  • Obviously the database should contain a Metadata Table keeping the current version of it. This version is unique for a particular state of the (database schema + system data) in the database
  • Any version change (db upgrade) should increment the database version number and should be performed through a set of scripts. That is for any two adjacent versions are associated with a set of scripts performing that version upgrade.
  • For example bringing database from version 2.3.5 to version 2.3.7 requires running two sets of scripts, one to upgrade from 2.3.5 to 2.3.6 and another to upgrade from 2.3.6 to 2.3.7. At the end of this script, it should properly change the db version to indicate the current db version.
  • Now we need to have a database level trigger to alter the db version number if any uncontrolled change is done to the database schema in an improper way. Additionally system data tables can also be protected with the same trigger mechanism which changes the db version if any uncontrolled change is happened to the system tables.
  • This trigger will change the db version to a state such as “NOT-DEFINED” so that the application may inform the user regarding the uncontrolled database state.
  • Additionally if we need to make automatic db upgrades to happen with the application upgrades, then we can build a small class library which will check the database version at the application startup and if the database needs an upgrade run the necessary upgrade scripts.
  • For this we can use the ‘Command Pattern’ to have individual command classes which can upgrade the database from one version to the next adjacent version. For an example to upgrade database from version 2.3.5 to version 2.3.7, application will run two command objects one responsible for upgrading from 2.3.5 to 2.3.6 and another for upgrading from 2.3.6 to 2.3.7.
This may not be the ultimate model for managing database versions. But I feel this would do well if implemented and controlled consciously.


creed said...

How do u think we manage the version control of the actual DB scripts itself.
Any special tricks with respect to version control systems when it comes to DB scripts?

Hasith Yaggahavita said...

If we go for the 'automatic db upgrade' mechanism with the command pattern, the respective commands are responsible of managing their upgrade sripts. Then scripts will be included as source files in the codebase and managed by the version controlling system.

If not, then we need to adopt our own mechanism. I dont think that the use of version coltrolling system is required to manage this. Because any of the scripts are responsible of upgrading the db from one version to the immediate next version. We do not do modifications to those files, hence putting then in a version controlling system is not necessary.

Between versions developer changes can be track on the common developer database itself for many of the small team projects. Employ a regular backup job on this database so we can revert if needed.