Pages

Friday, May 18, 2012

MySQL Hudson/Maven Automatic DB Migration using Carbon 5(cb) plugin


MySQL Automatic DB Migration

Today I am going to explain about MySQL DB migration with maven using “c5-db-migration”.

The Carbon Five Database Migration framework and Maven plugin for Java provides a simple solution
to the problem of managing discrete, incremental changes to databases over time across multiple
environments. Each migration is versioned and tracked when applied to the database.
                      Actually we got some request from client side that they need to execute DB scripts automatically,whenever some any script is added or some changes are made.
                I am showing this using hudson(I’ll post about hudson Later in detail. In Short : Hudson is a continuous integration (CI) tool written in Java, which runs in a servlet container, such as Apache Tomcat, the GlassFish or Jboss application server. It supports SCM tools including CVS, Subversion, Git and Clearcase and can execute Apache Ant and Apache Maven based projects, as well as arbitrary shell scripts and Windows batch commands.).

SVN Contents:
#/test-db/trunk/
pom.xml
src/

[mc26157@ph158004]~$ tree src/
src/
`-- main
    `-- db
        `-- migrations
            |-- 20120514_ks.sql
            |-- 20120515_test1.sql
            `-- 20120515_test2.sql

3 directories, 3 files

Below is the pom.xml file with contents as below :
   
pom.xml
   
                      

From the command line, you can run the migration plugin like this:
$ mvn db-migration:migrate
If you want the plugin to also create the database, you can invoke the plugin like this:
$ mvn db-migration:create db-migration:migrate
Sometimes you want to blow away the database and start fresh:
$ mvn db-migration:reset
You can also just check to see if the database is up-to-date:
$ mvn db-migration:validate
When it's time to create a new migration script, you can do so by:
$ mvn db-migration:new -Dname=users_and_roles

Which results in a new, empty file called: src/main/db/migrations/yyyyMMddHHmmss_users_and_roles.sql (assuming default settings). 


Maven Plugin Goal Reference
The maven plugin has several goals:
Goal
Description
db-migration:check
Check to see if the database is up-to-date and fail the build if there are pending migrations
db-migration:create
Create a new, empty database1
db-migration:drop
Drop the database1
db-migration:migrate
Apply all pending migrations2
db-migration:new
Create a new, empty migration file (-name=... to include a name in the filename)
db-migration:reset
Drop the existing database, create a new one, and apply all pending migrations1
db-migration:validate
Check to see if the database is up-to-date and report pending migrations
Notes:
  1. Must have create and drop database privileges.
  2. Must have schema update privileges.
I am running some examples below manually :
$ mvn db-migration:reset


[mc26157@ph158004]/local2/hudson/jobs/test-db/workspace$ sudo mvn db-migration:reset
[INFO] Scanning for projects...
[WARNING]
[WARNING] Some problems were encountered while building the effective model for com.carbonfive:migration-sample1:jar:1.0
[WARNING] 'build.plugins.plugin.version' for org.apache.maven.plugins:maven-compiler-plugin is missing. @ line 35, column 21
[WARNING] The expression ${artifactId} is deprecated. Please use ${project.artifactId} instead.
[WARNING]
[WARNING] It is highly recommended to fix these problems because they threaten the stability of your build.
[WARNING]
[WARNING] For this reason, future Maven versions might no longer support building such malformed projects.
[WARNING]
[INFO]                                                                        
[INFO] ------------------------------------------------------------------------
[INFO] Building Carbon Five Migration Sample 1 1.0
[INFO] ------------------------------------------------------------------------
[INFO]
[INFO] --- db-migration-maven-plugin:0.9.9-m2:reset (default-cli) @ migration-sample1 ---
[INFO] Resetting database jdbc:mysql://10.122.70.142:3306/test.
[INFO] Dropping database jdbc:mysql://10.122.70.142:3306/test.
[INFO] Creating database jdbc:mysql://10.122.70.142:3306/test.
[INFO] Migrating database jdbc:mysql://10.122.70.142:3306/test.
[INFO] Loaded JDBC driver: com.mysql.jdbc.Driver
[INFO] Successfully enabled migrations.
[INFO] Migrating database... applying 3 migrations.
[INFO] Running migration 20120514_ks.sql.
[INFO] Running migration 20120515_test1.sql.
[INFO] Running migration 20120516_test2.sql.
[INFO] Migrated database in 0:00:00.065.
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 1.246s
[INFO] Finished at: Wed May 16 19:42:46 PDT 2012
[INFO] Final Memory: 7M/723M
[INFO] ------------------------------------------------------------------------

$ mvn db-migration:migrate

[mc26157@ph158004]/local2/hudson/jobs/test-db/workspace$ sudo mvn db-migration:migrate
[INFO] Scanning for projects...
[WARNING]
[WARNING] Some problems were encountered while building the effective model for com.carbonfive:migration-sample1:jar:1.0
[WARNING] 'build.plugins.plugin.version' for org.apache.maven.plugins:maven-compiler-plugin is missing. @ line 35, column 21
[WARNING] The expression ${artifactId} is deprecated. Please use ${project.artifactId} instead.
[WARNING]
[WARNING] It is highly recommended to fix these problems because they threaten the stability of your build.
[WARNING]
[WARNING] For this reason, future Maven versions might no longer support building such malformed projects.
[WARNING]
[INFO]                                                                        
[INFO] ------------------------------------------------------------------------
[INFO] Building Carbon Five Migration Sample 1 1.0
[INFO] ------------------------------------------------------------------------
[INFO]
[INFO] --- db-migration-maven-plugin:0.9.9-m2:migrate (default-cli) @ migration-sample1 ---
[INFO] Migrating jdbc:mysql://10.122.70.142:3306/test using migrations at src/main/db/migrations/.
[INFO] Loaded JDBC driver: com.mysql.jdbc.Driver
[INFO] Successfully enabled migrations.
[INFO] Migrating database... applying 3 migrations.
[INFO] Running migration 20120514_ks.sql.
[INFO] Running migration 20120515_test1.sql.
[INFO] Running migration 20120516_test2.sql.
[INFO] Migrated database in 0:00:00.073.
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 1.045s
[INFO] Finished at: Wed May 16 19:47:57 PDT 2012
[INFO] Final Memory: 7M/723M
[INFO] ------------------------------------------------------------------------


Now if you are using hudson, then you don’t need to run these above commands. You have to just click on “Build Now” Button on UI or it will automatically run based on you SCM configuration.

Some More Useful Information that may give some more idea about the things.

Maven Plugin Configuration

General Settings

Here is the exhaustive list of configuration options and the goal to which they apply.
Option
Description
Required
Default
Goals
driver
JDBC driver class name (fully-qualified)
No
Autodetected from JDBC connection URL
migrate, validate, drop, create, reset
url
JDBC connection URL
Yes
N/A
migrate, validate, drop, create, reset
username
Database username
Yes
N/A
migrate, validate, drop, create, reset
password
Database password
No
"" - empty string
migrate, validate, drop, create, reset
databaseType
MYSQL, POSTGRESQL, SQL_SERVER, HSQL, H2, DB2, ORACLE, UNKNOWN
No
Autodetected from JDBC connection URL
migrate, validate, drop, create, reset
migrationsPath
Location of migrations
No
"src/main/db/migrations/"
migrate, validate, new
versionPattern
Date format for new migration filenames
No
"yyyyMMddHHmmss"
new
versionTimeZone
Time zone for new migration filename date format
No
"UTC"
new
migrationExtension
Filename extension for new migrations
No
".sql"
new
versionTable
Name of version tracking table
No
"schema_version"
migrate, validate
versionColumn
Name of version column
No
"version"
migrate, validate
appliedDate
Name of applied-date column
No
"applied_on"
migrate, validate
durationColumn
Name of duration column
No
"duration"
migrate, validate
createSql
SQL to execute to create the database
No
"create database %s"1
create, drop, reset
dropSql
SQL to execute to drop the database
No
"drop database %s"1
create, drop, reset
Notes:
  1. Where %s is the name of the database, as extracted from the JDBC connection URL.
Hope the things will help you!!