DBPatch Antlib

Overview

DBPatch is an Antlib for maintaining evolving database schema and data. It provides the approach and the tools to bring up multiple databases in sync with the state of the database and application code development. It is used in development allowing multiple programmers to synchronize their local databases with the current state of the application code in repository and in deployment to synchronize production or test database with the version of the deployed code.

To include DBPatch Antlib in your build, use the following code:

<typedef resource="org/apache/ant/dbpatch/antlib.xml"/>

How It Works

From the DBPatch perspective there are two types of changes that are made to the database: (1) those made by the applications during their normal operation course and (2) those made by the programmers during the development and deployed periodically as a part of the release process. DBPatch makes the assumption that within a finite development cycle only the second category of changes (those made by developers) are needed to describe each distinct database state. The difference between each subsequent state is represented as a patch. A patch is simply a file that contains a SQL script that performs some operation (e.g. ALTER TABLE, INSERT, etc.). Patch files are created by programmers in the course of development and checked in the repository together with the application code. DBPatch tools keep track of the database state and ensure that each patch is only applied once and all patches are applied in the correct predefined order.

DBPatch Files

Using Explicit Index File

With the index file approach, there is a single index file that lists all the patch files in the order they should be applied. Index file contains one patch name per line. Empty lines and lines that start with "//" or "#" (comments) are ignored. Index file and patch files can have any names. There are no restrictions on naming them. Patch file names are resolved relative to the directory where the index file is located.

Here is an example index file:

# This is a comment
somepatch.sql
someotherpatch.sql

// another comment
lastpatch.sql

Here is an example patch file:

ALTER TABLE mytable ADD COLUMN newcolumn VARCHAR(20) NOT NULL;
INSERT INTO mytable (id, oldcolumn, newcolumn) VALUES (1, 'X', 'Y');

Using Implicit Ordering

TODO: this mode is not supported yet

Tasks