zero.data.Evolution

At present, zero.data provides the Manager interface to query, update, and delete data in a database. zero.data.Evolution attempts to provide conventions for the developer of an application and library to apply database schema migrations in an automated way.

How it works

zero.data.Evolution works by specifying a series of migration scripts at an application or library level. These scripts will deal with any DDL and other data manipulation needed in an automated way. These can take the form of both Groovy or SQL scripts.

To add migrations to an application, you need to create the {app_root}/db/schema.groovy file and set up a default list of schema migrations:

def migrations = []

The above empty list, when zero.data.Evolution executes, will execute, well, nothing.

Using SQL migrations

You need to add a new column called my_attribute to the foo table. This needs to be rolled out to both the live and dev servers, so you need to create an SQL migration that will add the column definition. In this case we arbitrarily name the migration AddMyAttribute. Since this is an SQL migration (and not a Groovy) we place the AddMyAttribute.sql in the {app_root}/db/sql folder.

ALTER TABLE foo
  ADD COLUMN my_attribute VARCHAR(255);

Then add the migration to schema.groovy:

def migrations = ['AddMyAttribute']

Using Groovy migrations

Often it is easier to do migrations using the full power of a programming language, rather than just in SQL. To define a Groovy migration, simply define a function with the same name as the migration in schema.groovy. For example, if you wanted to do something to all foo records with an AOL email address after the above described AddMyAttribute migration, but that something special is easier to do in Groovy than SQL, you would do this:

def migrations = ['AddMyAttribute','migration1']

def migration1() {
  // data is bound to the groovy script
  def pattern = '%@aol.com'
  def default_value = 'something special'
  def aols = data.queryList("SELECT * FROM foo WHERE email LIKE $pattern")
  for (aol in aols) {
    data.update("UPDATE foo SET my_attribute = $default_value")
  }
}

Pre-migration Checks

There are cases where you might want to check if a certain condition exists and only run the migration if it does. An example of this might be checking a particular value in the database, a 'version' number, for instance. To do this, define a method (for either SQL or Groovy migrations) and prefix it with pre_ to run as a check before the actual migration runs.

def migrations = ['AddMyAttribute', 'migration1']

def foo_check() {
  def version = date.queryFirst("SELECT version FROM app_schema WHERE table = 'foo'")
  if (version < 10) {
    return true
  } else {
    return false
  }
}

def pre_AddMyAttribute() {
 foo_check()
}

def pre_migration1() {
  foo_check()
}
def migration1() { ... }

Prior Art

Primarily from Python DbMigration