Maven: Database migration plugin.

If you want to manage your databases automatically. If you have multiple environments  with different databases. If you don’t know what patch was executed where, this plugin is for you.

Features:

• Manage sql patches
• Display which patch should by applied
• Apply patches manually or automatically

License: LGPL

You can use it in your commercial projects as maven plugin, but you can not include dbpatch code in to your project.

Author: Marek Szalik szalik@jsoftw???.org (antispam protection – replace question marks)

If you want to ask something or request a plugin feature please leave a comment below or email me.

Maven goals:

• dbpatch:help – help screen
• dbpatch:help-parse – parse sql (use dbpatch.file system property to indicate file to parse)
• dbpatch:list – display list of patches
• dbpatch:patch - patch database
• dbpatch:interactive    - interactive mode

How to add plugin to your project in four simple steps:

1. Add maven plugin repository to your poject’s pom.xml file.

<pluginRepositories>
....
  <pluginRepository>
    <id>jsoftware.org</id>
    <url>http://maven.jsoftware.org</url>
    <snapshots>
       <enabled>true</enabled>
    </snapshots>
  </pluginRepository>
</pluginRepositories>

2 A. Add maven plugin to your maven project – configuration stored in properties file.

<build>
 <plugins>
  ....
 <plugin>
     <groupId>org.jsoftware</groupId>
     <artifactId>dbpatch</artifactId>
 <configuration>
     <configFile>dbpatch.properties</configFile> <!-- path to configuration file by default classpath:dbpatch.properties -->
     <selectedConfiguration>mydbconf</selectedConfiguration> <!-- database configuration, see dbpatch.properties file -->
 </configuration>
 <dependencies>
    <dependency> <!-- Jdbc driver required to make connection to your database -->
        <groupId>postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <version>8.4-701.jdbc4</version>
    </dependency>
 </dependencies>
 </plugin>
 </plugins>
</build>

2 B. Add maven plugin to your maven project – whole configuration stored in pom.xml.

Instead using properties file you can set all values in plugin’s configuration tag.

<build>
 <plugins>
  ....
 <plugin>
     <groupId>org.jsoftware</groupId>
     <artifactId>dbpatch</artifactId>
     <version>1.5</version>
 <configuration>
     <conf>
          <!-- put all configuration properties from properties file here -->
          <driverClass>org.postgresql.Driver</driverClass>
          <jdbcUri>jdbc:postgresql://localhost/my_db</jdbcUri>
          <username>db_username</username>
          <password>db_password</password>
          <patchDirs>dbpatches/main,dbpatches/testing</patchDirs>
          <!-- other properties -->
     </conf>
 </configuration>
 <dependencies>
    <dependency> <!-- Jdbc driver required to make connection to your database -->
        <groupId>postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <version>8.4-701.jdbc4</version>
    </dependency>
 </dependencies>
 </plugin>
 </plugins>
</build>

3. Create directory dbpatches in root of your project.

If you need different database profiles, create few directories then put your patches in those directories. You can provide different database profiles by using different patchDirs properties.

4. Copy your sql files (*.sql) into this directory.

Patches are executed in order of filenames. It is good practice to use filename patter like this patchXXXX-NAME.sql.
Where XXXX is four digit number and NAME is a patch title.

5. Create db.properties file in root directory of your project.

You can skip this step if you are using pom.xml configuration variant (see 2.B).

The properties file should look like this:
# configuration for database mydbconf
mydbconf.driverClass=org.postgresql.Driver
mydbconf.jdbcUri=jdbc:postgresql://localhost/my_db
mydbconf.username=db_username
mydbconf.password=db_secretpass
# directories to scan for database patches (coma separated)
mydbconf.patchDirs=dbatches – coma separated directories example: mydbconf.patchDirs=dbpatches/main,dbpatches/testing

# configuration for next database (db2)
db2.driverClass=org.postgresql.Driver
db2.jdbcUri=jdbc:postgresql://localhost/my_db
db2.username=db_username
db2.password=db_secretpass
db2.patchDirs=dbpatches – coma separated directories

Oracle users have to add property dialect=oracle.
Example below:
db2.dialect=oracle

Now you can use plugin goals:

• mvn dbpatch:help – plugin help message
• mvn dbpatch:list – list of all patches and information if they already are in database
• mvn dbpatch:patch – apply missing patches
• mvn dbpatch:skipErrors – mark patches in “in progress” state (P) as proper applied
• mvm dbpatch:interactive – interactive mode, swing application

Frequency Asked Questions – faq:

What does the symbol next to patch name mean?

When you run goal dbpatch:list you can see all patches that are available for configuration you selected in pom file. Before file name you can see symbol that indicate database patch state. Those symbols are:

• * (star) for patches that was applied to your database
• + (plus) for patches that should be applied to your database
• P for patches that are in progress or an error occurred during applying

Where patch states are stored?

State of each patch is stored in your database in table db_patches. The table contains data about any applied patch. If column patch_db_date is null it means that patch contains invalid statement, and an error occurred during execution of the patch.

How to deactivate dbpatcher?

You can set system property maven.dbpatch.skip to deactivate patcher.

mvn -Dmaven.dbpatch.skip=true <your maven goal>

How to define patch directories (configuration property patchDirs) ?

A patchDirs property  coma separated list of directories or file paths. Patch can also contain wild card characters to define file selection mask (example: /sqls/good-*.sql or /sqls/nov???.sql). If mask is not defined the default mask is used - *.sql . So if your patchDirs property contains element like /sqls/ only patches matched to /sqls/*.sql will be executed.

Working example for MySQL: dbpatch-usage-example.zip

Troubleshooting:

My patches (that contains stored procedures or functions) are not well interpreted.

If your patch is miss interpreted you can add hint for dbpach plugin to your patch that indicate sql statements. To do this add single line comment starting with “block” before statement and single line comment after it. Example below:

-- block - my create function block
CREATE FUNCTION add(integer, integer) RETURNS integer
    AS 'select $1 + $2;'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;
-- single line comment indicate end of block

You can see how your patch is interpreted using mvn dbpatch:help-parse -Ddbpatch.file=/path/to/sqlFile

Be Sociable, Share!

•