Monday, 20 September 2010

Database Migrations with the Zend Framework

If you're a user of the Zend Framework then you're no doubt aware that it was developed by Zend Technologies in response to the growing popularity of other frameworks (most notably Ruby on Rails and Spring) that don't use PHP. Worried that PHP would lose developer mind share to Ruby and Java, Zend developed the Zend Framework to give PHP developers a well designed foundation to build their PHP applications on.

Over the last 5 years, the Zend Framework has steadily grown into a fine framework to rival its competitors but for me there are a few areas where it is still lacking. If you're familiar with the Rails framework, then you'll know that one tool that Rails provides but that ZF doesn't is managed database migrations.

For these of you who don't know what a database migration in Rails is, consider your approach to designing your application database. Think about how you change a database table design and how you communicate those changes to your colleagues. You might use a WYSIWYG designer like MySQL workbench (and there's nothing wrong with that) to design and modify individual tables but how do you make sure that your team all update their local database schemas? How do you know which version of the database design should the staging server or the live server have?

This is the problem that the database migration paradigm aims to solve. It asserts that the design for the database should sit in the same code base as the whole application, and be versioned by your Revision Control System. Unfortunately, the Zend Framework doesn't help solve this problem and Rails has ZF beat here. In Rails, you drop to your command line, and using Rake (Ruby's make tool) you issue a command to create a migration. This command creates a file that you open in your editor and enter the changes you want to make to the database (in a rather lovely Domain Specific Language). Then you pop back to your command line, issue another command, and Rake applies these changes to your local database. Should you need to roll the changes back, you can do that too.

There have been attempts to bring database migrations to ZF before, notably Rob Allen's Akrabat project. In fact, the only issue I have with Rob Allen's implementation is that it passes the actual database adapter to the migration object, and the migration script interacts with the database directly. To my mind, this isn't quite right; I want the migration script to return a number of SQL statements and to have the migration system manage any actual interaction with the database. One benefit of this is that it makes the migration plugin more flexible; for example, it can then run in what Zend calls pretend mode (where it runs the migration but doesn't actually commit the changes).

The Mooduino Implementation


I've uploaded the first version of my migration plugin to my github (http://github.com/michaelhodgins/Mooduino). It doesn't do everything that I want it to do as yet but it is functional and I've started using it in my own work.

Installation is quite straight forward. First, download the Mooduino library from github. I put my libraries in my home directory on my development PC, so Mooduino would end up in the directory '/home/michael/programming/php/mooduino/'.

Open a terminal and issue these two commands:

zf --setup storage-directory
zf --setup config-file

These will create a file called .zf.ini in your home directory (or in a directory called .zf). Open the .zf.ini file and add the location of your local copy of the Mooduino library to the php.include_path value. This value should also include the path to your local copy of the Zend Framework.

You also need a new line telling the zf command line tool about the Mooduino migration provider. Add the following:

basicloader.classes.0 = "Mooduino_Db_Migrations_MigrationProvider"

If you already have a value for basicloader.classes.0, make it basicloader.classes.1. As an aside, if there is a value for php.includepath, this is a bug (related to Netbeans I think) and it should be php.include_path instead.

To check that this worked, return to your terminal and issue this command:

zf ? migration

This should print the following list, showing you the commands that you can use with the migration plugin.

zf generate migration name env[=development] base-class[=default]
zf redo migration step[=1] env[=development]
zf undo migration step[=1] env[=development]
zf current migration env[=development]
zf update migration to[=latest] env[=development]
zf show migration revision[=list] env[=development]
zf clear migration env[=development]

In operation


To actually use the plugin, you'll need a ZF project with the database configured in application/config/application.ini. The database will also need to have been created.

Use the first command in the above list to generate a migration file. For example:

zf generate migration Notes

creates a file called scripts/migrations/1283772577_Notes.php and this file contains a class called Migration_1283772577_Notes. The long number is a timestamp in seconds and obviously depends on when you issue the generate command. This timestamp is used to sequence the files. This class extends Mooduino_Db_Migrations_Migration_Abstract and contains two empty methods, up() and down(). These two methods are expected to return either a string or an array of strings; the strings are expected to be valid SQL statements for the application's RDBMS. The statements returned by the down() method are expected to reverse the effects of the statements returned by the up() method.

For example, if I wanted to create the notes table from my previous blog post, I'd fill in the migration as follows.

class Migration_1283772577_Notes extends Mooduino_Db_Migrations_Migration_Abstract {

  public function __construct() {
    parent::__construct('Notes', 12837725773
);
  }

  public function up() {
    return 'CREATE TABLE `notes` (
             `id` bigint(20) NOT NULL AUTO_INCREMENT,
             `text` longtext NOT NULL,
             `title` varchar(255) NOT NULL,
             `priority` int(11) NOT NULL,
             PRIMARY KEY (`id`),
             UNIQUE KEY `Search` (`title`),
             KEY `Priority` (`priority`)
            );
    ';
  }

  public function down() {
    return 'DROP TABLE `notes`;';
  }

}


If you return to your terminal, and issue the command zf show migration list, you'll see the migration details, showing that it hasn't been executed against your local database. To execute the up() method, issue the command zf update migration. This will execute all unapplied migrations unless you include the fourth parameter. If you issue this command and then look at your database, you'll see the notes table has been added, along with a table called schema_version, which the plugin uses to maintain its state.

Once you'll got a few migrations created, the plugin allows you to move your database back and forth between them. For example, the redo command rolls back a number or migrations and then reapplies them. The undo command just rolls them back. Both of these methods default to one step but for example the command zf redo migration 2 will rollback and reapply the last two migrations that were applied. The method clear rolls back all migrations. The current method shows you which migration was the last to be executed.

Lastly, notice that each method has an env parameter that defaults to development; you use this to set which database to execute the migrations on and should be an environment from your application.ini file (but that doesn't mean I recommend executing the migrations against your production database - this is a development tool, not a deployment tool).

Future developments


Though the database migration plugin is functional, it is far from feature complete. There are a number of improvements I'd like to make.

  • I've played around with using milliseconds for the timestamps (to reduce the chance of getting two migration files with the same timestamp) but this doesn't work reliably on 32bit operating systems because the integer overflows.
  • I'd like to add the pretend mode mentioned earlier.
  • I'd also like to make the database adapter object available to the migration object so that it can interact with the database directly if it needs to.
  • In a team environment, it is likely that more than one team member will be adding migration files and when it comes time to merge working copies, you might end up with missed migrations - migrations that have not been executed but that are older than your own migrations that have been executed. I'd like to add a new method to the plugin to automatically apply missed migrations.
  • Arguably the most impressive feature in the Rails migration system is the domain specific language used to script the changes to the database; rather than use SQL directly, in Rails you use a database agnostic language that builds at runtime SQL statements that are specific to the database that you are using. This means that you can use one RDBMS on your development machine (say, SQLite) and another in your staging environment (say MySQL). Creating this system would obviously be an enormous undertaking (perhaps 90% effort for 10% functionality) but it would be nice to have.

Feedback


If you find any bugs in the plugin, or have any suggestions for improvements, I'd love to hear from you.

No comments:

Post a comment