Downloading & Installing

Download the migrations application from here and save anywhere on the computer.

Now unzip the application and it will create a folder similar to migrations-gui-1.0. Enter the folder and you will see a file called migrations.cmd (or migrations.sh). Windows users may double-click on the migrations.cmd to activate the program. Unix users should double-click on the migrations.sh file.

Creating a migrations configuration

Migrations works off of a simple pattern based system. The application will be told the location of a configuration file (often referred to as the migrations.xml). 1 or more folders which contain sql scripts should be present where the migration configuration is located at. Each folder represents a schema (things which could in theory reside in different database schemas even if they don't always in reality).

Included with the downloaded applications are a basic-migration and advanced-migration folders which include example migrations.xml files as well.

The basic migrations.xml looks like 

<migration>
  <schemas>
    <schema id="rolodex" driver="org.hsqldb.jdbcDriver"
      url="jdbc:hsqldb:mem:test" username="sa" password="" />
  </schemas>
</migration>
In this example migration, there is a rolodex defined. Thus there should be a folder located here as well named rolodex. Also note that for the schema element, the attributes driver, url, username, and password are optional and if not present will be prompted for during the migration process.

Schema Folders

Also, as mentioned earlier, schema folders shall contain the up/down migration scripts which follow this pattern 

###_(up|down)_description.sql
Meaning that it must first start with a number 1 or higher which represents the migration version represented by that sql script. Then next section which is separated by an _ is the up/down section. Specifies whether these are upgrade or downgrade scripts respective. Both should exist in order to be able and downgrade or revert changes made. The final section is just a human sensible quick description of what the purpose of that migration script is. An example of what might exist in the myschema folder would be 
001_up_initialize.sql
001_down_destroy.sql
002_up_patch-person-table.sql
002_down_rollback-person-table.sql
003_up_create-uuid-column.sql
003_down_delete-uuid-column.sql

Once a migration configuration and sql scripts exist, anyone can simply point to the configuration location and migrate any schema to any version, use it for however long, and then migrate down to 0 to restore the database schema back to its original state.

Templating SQL Scripts

The .sql scripts themselves may be your everyday run of the mill sql scripts. However, there are sometimes a need to make the .sql scripts a bit more dynamic. Whether to support multiple database servers or even different environmental values. To facilitate this need, a simple templating language is used supported.

A normal non-templated and valid .sql script for oracle might be 

CREATE TABLE FOO(ID VARCHAR2(50));
However, a templated .sql script in order to support multiple databases (like mysql, postgres, sql server, and oracle) might be 
CREATE TABLE FOO(ID #string(50));
Anything following the #name() pattern can be substituted at runtime. In this case, either in the migrations.xml or at runtime, you may define the value of #string() to be VARCHAR2($1) for oracle and VARCHAR($1) for other database types. This allows for a single .sql script to be used and maintained but the ability to install on many different database types and environments.

To support this, your migrations.xml can predefine default values for properties or if not defined, will prompt the user at runtime to provide those values. If predefined in the migrations.xml, a configuration for the above example might look like, 

<migration>
  <schemas>
    <schema id="rolodex" />
  </schemas>

  <properties>
    <string>VARCHAR($1)</string>
  </properties>
</migration>

Schema Dependencies

Often times a database is comprised of schema features which may or may not be needed but when needed are dependant upon another schema being up-to-date to some degree.

For example, you might have a core schema which contains a user table. And an optional schema for blogging functionality which isn't always needed. But the blogging tables have a dependency upon the user table.

To support this, your migrations.xml can define orders which let it know that in order to successfully install the blog schema, it must first make sure that the core schema has been upgraded to a certain version first. The migrations.xml configuration might look something like the following, 

<migration>
  <schemas>
    <schema id="admin" />
    <schema id="core" />
    <schema id="blog" />
    <schema id="forum" />
  </schemas>

  <orders>
    <order schema="blog" version="1">
      <depend schema="core" version="1" />
    </order>
    <order schema="blog" version="3">
      <depend schema="core" version="2" />
    </order>
    <order schema="forum" version="1">
      <depend schema="core" version="2" />
    </order>
    <order schema="admin" version="1">
      <depend schema="core" version="2" />
      <depend schema="blog" version="3" />
      <depend schema="forum" version="1" />
    </order>
  </orders>
</migration>