AutoPatch
We are using an open source project called AutoPatch to automatically upgrade our software. This way there is no need to manually run an script to upgrade/create our database and therefore simply greatly our installation instructions.
- Learn how it works
- Learn our current migration tasks
- Learn how to support another database
- Learn how to write migration tasks
How does it work
Patch level
The tool implements a simple patch management system. Fundamentally an application version has a patch level. Every times the database schema or the data has to change, the patch level is incremented. An application knows its patch level and the database stores its patch level. On start up, autopatch verify that both database and application are on the same patch level and applies the appropriate patches to bring up the database level up to the application level As of 1.2, autopatch supports database downgrading. If a upgrade fails, autopatch will try to revert it by downgrading the applied patches. In any case it will prevent the application from starting if there is an unresolvable discrepancy between levels.
Patches
Each modification of the database is included in a patch. A patch comes in 2 forms:
- sql scripts and
- java classes.
Each patch has an unique level. autopatch can automatically search for patches in a classpath and in directories. On upgrade each patch that has a level greater than the database's level are run. At the end, the database is updated to reflect the last successful patch level (Note: autopatch does not support branching so if you are doing "release branching" you are out-of-luck).
Our environment
Migration tasks
Tasks specified using the following properties
| Property | Description | Default value |
|---|---|---|
| xplanner.migration.databasetype | Select the database to use for autopatch. TODO: This should select the subdirectory of resources/patches to get specific database scripts | mysql |
| xplanner.migration.patchpath | sql script directories and java migration task packages to look into | patches: com.technoetic.xplanner.upgrade: com.technoetic.xplanner.security.install |
As of 0.7b5 we are supporting mysql and hsqldb officially.
| Database | SQL patch file location |
|---|---|
| mysql | resources/patches |
| hsqldb | resource/patches/hsqldb |
| new database | resource/patches/<database name> |
All these are configured with the xplanner.properties properties.
How to add support for another database
- Copy the patch files from another database under resource/patches into a directory
<database name> and make the necessary sql syntax correction for your target database specific sql in all patch files - In your xplanner-custom.properties
- Change xplanner.migration.databasetype to the name of the leaf directory where you patch files are: <database name>
- Change xplanner.migration.patchpath to patches/<database name>: com.technoetic.xplanner.upgrade: com.technoetic.xplanner.security.install
- If you had to modify any column or table names, correct them in the mapping files under resources/mappings.
- If some native patch tasks fail, you will have to change them yourself and recompile them. Take a look at how to build xplanner from source at Subversion
Build
autopatch.xml is an ant script that has several administrative targets to manage autopatch:
| Target | Description |
|---|---|
| getinfo | Print the current database level |
| force.level | Set the database level to the property patch.level |
| apply.patch | Upgrade the database to the current software level |
Patch levels
Our patch levels are
| XPlanner version | Level | Old migration scripts |
|---|---|---|
| 1 | initial 0.6.0 schema | |
| 0.6.0 | 2 | com.technoetic.xplanner.security.install.BootstrapSystemUser |
| 0.6.1 | 2 | |
| 0.6.2 | 3 | xplanner_0-6-1_to_0-6-2.sql |
| 4 | xplanner_0-6-2_to_0.7b1_A.sql | |
| 5 | com.technoetic.xplanner.upgrade.CleanUpDuplicateUsers | |
| 0.7b1 | 6 | xplanner_0-6-2_to_0.7b1_B.sql |
| 7 | iteration44.sql + iteration45.sql | |
| 0.7b2 | 8 | com.technoetic.xplanner.upgrade.CleanUpAttachments |
| 0.7b3 | 9 | iteration47.sql |
| 0.7b4 | 9 | |
| 0.7b5 | 9 | |
| 10 | patch0010_story_order.sql | |
| 0.7b6 | 11 | MigrateStoryPriorityToOrderNo |
| 12 | patch0012_add_positive_permission_column.sql | |
| 13 | CleanUpNoteTableConstraints | |
| 0.7b7 | 14 | RemoveIterationDeletionRightsFromEditor |
| 0.7b8 | 15 | patch0015_add_is_deleted_column.sql |
Transition to autopatch
The first time someone upgrades to a release beyond 0.7b5, they will have to setup the patch level corresponding to their database version. This is accomplished with a few ant targets named set-version-at-XXX where XXX is the target version. Example:
If your current version (to be upgraded) is 0.6.2 then first run set-version-at-0.6.2
If your current version is 0.7b3 then run set-version-at-0.7b3
 | Versions prior to 0.6.0 must first upgrade to 0.6.0 through concecutive upgrades (0.5.1 -> 0.5.2 -> 0.5.3 -> 0.6.0) then run set-version-at-0.6.0 |
Technology
[Excerpt from Zdot ]
DBUnit and thier best practices for database testing.
The HSQLDB home page.
Scott Ambler's excellent discussion of database refactoring.
An article from TheServerSide.com about unit testing with HSQLDB.
Spring's AbstractTransactionalDataSourceSpringContextTests class that helps make some of your database testing easier. Check the other classes in the hierarchy for specifics on what they do as well.
tk-autopatch is an opensource project that provides a framework to run automatically migration tasks. These tasks can be stored in DDL file, in DML file, or coded as java classes. There is web filter to run these at startup. We have already created the issue XPR-152 to migrate to it.
Also look at http://db.apache.org/ddlutils/index.html for ways to manipulate the schema dynamically
