Dbmaintain, what it means and what you should follow.

This will tell you how you can manage your DB scripts in your project.

Applying Continuous Delivery means to automate the delivery pipeline and to release frequently. However, databases are a big challenge, because with every deployment we may need to update and migrate our database before we can deploy our application. This post points out solutions for dealing with databases in a Continuous Delivery scenario.

Folder structure

Folder and script names must start with an index number followed by an underscore and a description. The index indicates the sequence of the scripts or script folders.

The DBMAINTAIN_SCRIPTS table

The database keeps track of all executed scripts in the table DBMAINTAIN_SCRIPTS. The table definition looks like the following

FILE_NAME

The relative path of the script file, starting from the root of the script's directory or archive.

FILE_LAST_MODIFIED_AT

Last modification timestamp of the script. If the property useScriptFileLastModificationDates is set to true, this timestamp is used to determine whether a script might have changed, in order to improve performance when there are many or large scripts. If the timestamp is unchanged, we assume that the script didn’t change, and the script doesn’t have to be read to calculate the checksum. If the timestamp changed, the checksum is verified to decide whether the script changed.

CHECKSUM

MD5 hash calculated on the contents of the script. This value is used to determine whether a script has changed since the last update.

EXECUTED_AT

Indicates when the script was executed on the database. This is the info for the user, which is not used by the system.

SUCCEEDED

Indicates whether the script was executed successfully (1 if successful, 0 if there was an error)

Error handling

If an error occurs during the execution of a script, DbMaintain immediately stops execution and performs a rollback of the script. If a repeatable script caused the error you can simply fix this repeatable script and it will be executed again when executing the update database the next time.

If an incremental script caused the error and you try to perform the update database operation again, DbMaintain will refuse to do it and will just give an error. This is because implicit or explicit commits could have been performed by the script itself. For example, a create table statement in oracle implicitly performs a commit. As a result, the database could be in an invalid state and should first be cleaned up. There are 2 options to recover from this state:

  • Fix the script, manually perform the changes of the script, and call the markErrorScriptPerformed task.
  • Fix the script, revert committed changes of the script (if any) and call the markErrorScriptReverted task.

Setting a baseline revision

Sometimes you don’t start from an empty database. After a release for example, you could do an export of the database and use that image as a starting point for other databases.

DbMaintain supports setting a baseline revision. If you set this revision, all scripts with a lower version will no longer be taken into account. Modifying or removing them will no longer give an error. You could for example cleanup the old scripts or replace the old scripts with the export script.

If you create a script archive, it will only contain scripts starting from this revision. In other words, it will only contain the deltas with the image. This results in smaller jar files that only contain the actual changes for the next release.

Note: when a script folder or a script does not have a version, the version is set to the value ‘x’. For example, suppose you have the following structure:

scripts/01_release_1/001_my_script.sql

Then the version number of this script will be x.1.1. If you want to set this script as the first script of the baseline, you will have to set the baseline revision to x.1.1 and not too 1.1.