MIGRATEdb

• Purpose
• Features
• An Example
• Installation
• Running MIGRATEdb
• Substitution Parameters
• External SQL Files
• Custom Tests
• No data dictionary? - Generic Migrations

Purpose

MIGRATEdb will parse an XML file of database changes and load them to the targeted database if they are not already there.

A database change consists of tests to determine whether the change has already been applied and a set of change actions.

Download it here.

Based on blog entries from Warren Mayocchi.

Pramod Sadalage's short tutorial.

Features

Feature	Supported by MIGRATEdb	Comments
Allows construction of a database at a particular version	Yes
Allows migration from an existing database to a later version	Yes
Allows migration from an existing database to an earlier version	No
Human readable format for releases	Yes	It is in XML which could be called ugly or readable depending on your opinion
Release ‘action’ available on multiple environments (ie various operating systems) allowing development on a different platform than production	Yes
Provides a complete history of changes for each database object	Yes	Though you would probably want some type of filtering ability over the top of the file to filter for changes to the object of interest.
Provides a current creation script for each database object	No	If this is required, it can usually be generated.
The source code can be branched and merged	Yes	Most complex when the same object is being modified in the branch and trunk, even in this case the 'actions' are small functional units
Allows multiple developers to work with/on the same database source code, at the same time	Yes	The process can be re-run infinitely, only installing new changes each time
Supports an ‘automated build’ / ‘continuous integration’ enviroment	Yes
Automatically records database changes by the developer in their development area	No
Supports use of parameters to configure variable parts of the release (eg the password part of “CREATE USER warren IDENTIFIED BY xyzzy”)	Yes

An Example

A simple example could be an existing table with a 'name' column that contains first and last name. You want to create two new columns 'first_name' and 'last_name', migrate the data from the 'name' column, then remove the 'name' column. (From here)

 To start off the database changes XML file we add a project:

<project> </project>

If I wanted I could add the targeted user to the project tag:

<project user="wjm"> </project>

This will issue a warning if the targeted user is different to the actual install user.

Now, I need to add the change that creates the example table. It will include a simple test for the table's existence and two statements for the table and primary key creation (note the 'exists' parameter - it informs the test when it should proceed. In this case it needs to proceed when the object does not exist. The 'exists' value is true if a row is returned by the test SQL, and false when no row is returned.):

<project user="wjm">
  <change>
    <sqltest exists="false">
      SELECT NULL FROM USER_OBJECTS
      WHERE OBJECT_NAME = 'PERSONS'
    </sqltest>
    <sqlaction>
      CREATE TABLE persons (
        person_id INTEGER
       ,full_name VARCHAR2(300)
      )
    </sqlaction>
    <sqlaction>
      ALTER TABLE persons ADD CONSTRAINT person_pk (
      PRIMARY KEY (person_id)
      USING INDEX
    </sqlaction>
  </change>
</project>

So...after running this into production, it has been in use for a number of years. Many people have been added to the table. We now want to store first and last names separately. Luckily for us our interface has formatted all inserts as "FIRST_NAME space LAST_NAME".

To the end of the database script we add a change to accomodate the new columns, the data migration and the removal of the old column:

<project user="wjm">
  <change>
    <sqltest exists="false">
      SELECT NULL FROM USER_OBJECTS
      WHERE OBJECT_NAME = 'PERSONS'
    </sqltest>
    <sqlaction>
      CREATE TABLE persons (
        person_id INTEGER
       ,full_name VARCHAR2(300)
      )
    </sqlaction>
    <sqlaction>
      ALTER TABLE persons ADD CONSTRAINT person_pk (
      PRIMARY KEY (person_id)
      USING INDEX
    </sqlaction>
  </change>
  <change>
    <sqltest exists="true">
      SELECT NULL FROM USER_TAB_COLUMNS
      WHERE TABLE_NAME = 'PERSONS' AND COLUMN_NAME = 'FULL_NAME'
    </sqltest>
    <sqlaction>
      ALTER TABLE persons ADD (
      first_name VARCHAR2(150))
    </sqlaction>
    <sqlaction>
      ALTER TABLE persons ADD (
      last_name VARCHAR2(150))
    </sqlaction>
    <sqlaction>
      UPDATE persons
      SET first_name = SUBSTR(full_name, 1, INSTR(full_name, ' ') - 1)
    </sqlaction>
    <sqlaction>
      UPDATE persons
      SET last_name = SUBSTR(full_name, INSTR(full_name, ' ') + 1)
    </sqlaction>
    <sqlaction>
      ALTER TABLE persons
      DROP COLUMN full_name
    </sqlaction>
  </change>
</project>

That's it! Whenever that script is run it will now update existing installations with the change. The next question is how to run it?

Note: just as the example displays several actions in one change, it is also possible to specify any number of tests.

Using External SQL Files and Custom Tests. The xml could be:

<project user="wjm">
  <change>
    <tabletest exists="false" table="persons" />
    <sqlaction file="${db.src.dir}/persons.tab" />
    <sqlaction file="${db.src.dir}/person_pk.con" />
  </change>
  <change>
    <columntest exists="true" table="persons" column="full_name" />
    <sqlaction file="${db.src.dir}/persons_0001.tab" />
  </change>
</project>

Using a version table (similar to ideas discussed in Generic Migrations). The xml could be:

<project user="wjm">
  <change>
    <versiontest exists="false" version="1.0.0.0" />
    <sqlaction file="${db.src.dir}/persons.tab" />
    <sqlaction file="${db.src.dir}/person_pk.con" />
    <versionupdate version="1.0.0.0" />
  </change>
  <change>
    <versiontest exists="false" version="1.0.1.0" />
    <sqlaction file="${db.src.dir}/persons_0001.tab" />
    <versionupdate version="1.0.1.0" />
  </change>
</project>

Installation

You should have the Java Runtime (1.5/5 +) installed.

Download the install file and unzip the contents.

Ensure that you have the JDBC driver for your database in your CLASSPATH.

Running MIGRATEdb

From the command line

You can execute MIGRATEdb from the command line by adding the migratedb.jar to your classpath and entering "java net.sf.migratedb.migration.Main". It requires a number of parameters:

C:\migratedb\bin>java net.sf.migratedb.migration.Main

Error: Parameter 'user' is required.
Error: Parameter 'password' is required.
Error: Parameter 'url' is required.
Error: Parameter 'driver' is required.
Error: Parameter 'file' is required.

Usage: java net.sf.migratedb.migration.Main
                -u <user> -p <password> -l <url> -d <driver> -f <file> [-x] [-v] [-s <name=value>] [-m <custom>] [-o <output>]


  -u <user>
        The database user name.

  -p <password>
        The database user password.

  -l <url>
        The connection url, for example jdbc:oracle:thin:@host:port:dbsid

  -d <driver>
        The database JDBC driver (remember to provide classpath access to the JAR file)

  -f <file>
        The DDL definition file.

  [-x]
        Apply the changes to the database - otherwise, only output what would happen to the console

  [-v]
        Output detailed progress messages

  [-s <name=value>]
        A substitution parameter - there may be as many of these as you need

  [-m <custom>]
        The custom migrations file.

  [-o <output>]
        The SQL output file.


As an ANT target

1. First create an ANT task definition:
<taskdef name="dbrelease" classname="net.sf.dbmigrate.ant.DbMigrateTask" classpath="lib/dbmigrate.jar" />
2. Then create your target:
<target name="db-migrate" description="Migrate database objects to latest version">
  <dbrelease
    driver="oracle.jdbc.OracleDriver"
    url="jdbc:oracle:thin:@host:port:sid"
    userid="user"
    password="password"
    apply="true"
    file="db.xml" />
</target>


To turn on verbose output from the ANT target add the following:

    verbose="true"

To use a custom migrations file (see Advanced topics):

    customfile="oracle.xml"

To output the SQL that is executed to a file - each SQL statement will be finished with a ';' and '/' on a new line:

    outputfile="script.sql"

Advanced topics

Substitution Parameters

There are times where string substitution may be required. For, example for the password part of a CREATE USER statement.

Substitutions can be specified with Ampersand or Dollar formats:

CREATE USER warren IDENTIFIED BY ${db.password}
CREATE USER warren IDENTIFIED BY &db.password


The user.xml file would look like:

<project>
  <change>
    <sqltest exists="false">
      SELECT NULL FROM ALL_USERS
      WHERE USERNAME = 'WARREN'
    </sqltest>
    <sqlaction>
      CREATE USER warren IDENTIFIED BY ${db.password}
    </sqlaction>
  </change>
</project>

There are two ways of specifying a value for the parameter based on how you run the migration.

From the Command Line

java net.sf.migratedb.migration.Main -u user -p password -d oracle.jdbc.OracleDriver -l jdbc:oracle:thin:@host:port:sid -f user.xml -x -s db.password=xyzzy

Note that the -s flagged parameter can be specified for as many parameters as needed.

From an ANT target

<target name="db-user" description="Create the user">
  <dbrelease
    driver="oracle.jdbc.OracleDriver"
    url="jdbc:oracle:thin:@host:port:sid"
    userid="user"
    password="password"
    apply="true"
    file="user.xml">
      <property name="db.password" value="xyzzy" />
  </dbrelease
</target>


Note:

• as many 'property' entries can be specified as needed.
• any declared properties in the ANT project will be substituted automatically.

External SQL Files

It is possible to use external SQL files rather than statements inside the XML.

• Substitution Parameters work in external files just as they do in the XML.
• Many SQL statements can be added to one file. They need to be delimited by a ';' at the end of the statement or a '/' on a line by itself.
• When there are many SQL statements in a test.sql file, all of the statements must satisfy the 'exists' condition for the action to proceed.

Using user.xml from the previous section as an example:

<project>
  <change>
    <sqltest exists="false" file="user-test.sql" />
    <sqlaction path="${sql.path}" file="create-user.sql" />
  </change>
</project>

The create-user.sql file could contain the following (demonstrating different valid ways for delimiting the SQL statement) - Note that the path parameter is optional:

CREATE USER ${db.user}
IDENTIFIED BY ${db.password} ;
GRANT CREATE TABLE TO ${db.user}
/
GRANT CREATE SESSION TO $db.user};
/

Custom tests

MIGRATEdb will handle a custom XML file with configured tests and/or actions. (MIGRATEdb is already packaged with some custom migration files, currently for the Derby and Oracle databases).

To make use of the custom migrations use code similar to:

<change>
  <tabletest exists="false" table="new_table" />
...
</change>

An example of the format for the content of the migrations file is listed below.

The only considerations to be aware of when adding a custom migration are:

• The test SQL should be a query
• When the test query returns a row the 'exists' property will be true
• When the test query returns no rows the 'exists' property will be false
• Please contribute additional tests/actions or complete database migration sets that you write to the project

<migrations database="oracle">
  <test>
    <tabletest>
      SELECT TABLE_NAME FROM ALL_TABLES WHERE TABLE_NAME = UPPER('${table}')
    </tabletest>
  </test>
  <test>
    <sequencetest>
      SELECT SEQUENCE_NAME FROM ALL_SEQUENCES WHERE SEQUENCE_NAME = UPPER('${sequence}')
  </sequencetest>
  </test>
  <test>
    <usertest>
      SELECT USERNAME FROM ALL_USERS WHERE USERNAME = UPPER('${user}')
    </usertest>
  </test>
  <test>
    <privilegetest>
      SELECT PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE = UPPER('${grantee}') AND PRIVILEGE = UPPER('${privilege}')
    </privilegetest>
  </test>
  <action>
    <tabledrop>
      DROP TABLE ${table}
    </tabledrop>
  </action>
</migrations>


Generic Migrations

What if the database in use has no data dictionary that can be tested for the existence of objects via SQL? For example, using MYSQL before v5.0 (MYSQL 5.0 has a data dictionary).

If you install a MIGRATIONS table that records at least a migration identifier...

CREATE TABLE migrations (
  migration varchar(100),
  migration_date timestamp)


You can then setup tests that query the MIGRATIONS table. The migrations demonstrated below are used by setting the custom migrations file to be 'generic-migrations.xml'. That file is included in the release. Note the 'migrationtest' test - it queries the migrations table for the existence of the migration. Also note the 'migration' action - it registers the migration in the migrations table.

<project>
  <change>
    <migrationtest exists="false" migration="one" />
    <sqlaction>
      CREATE TABLE persons (
        person_id INTEGER
       ,full_name VARCHAR2(300)
      )
    </sqlaction>
    <sqlaction>
      ALTER TABLE persons ADD CONSTRAINT person_pk (
      PRIMARY KEY (person_id)
      USING INDEX
    </sqlaction>
    <migration migration="one" />
  </change>
</project>