So, it turns out that I had a need for database migrations within a Zend Framework project that's using Zend_Db_Adapter.
Those of you with long memories may remember that I created a proposal along these lines back in 2006. Nearly four years later, I read it again and implemented the core section. One of the comments, by Wil, asked why it's worth bothering with a DDL to create tables, columns, indexes etc. as sooner or later you'll hit a wall and need to use SQL anyway. He has a point. My original idea was that as I routinely use MySQL and MS SQL Server, it would be handy to have cross-database code. Thinking about it however, there's not that much that isn't cross-database and I could just as easily write the specific SQL for those situations.
If you remove the DDL bit, then the rest is quite easy, so I wrote it and put it up on github into a project I've egotistically called Akrabat.
Migration files
It is intended that any time you want to make a change to your database schema (add columns, tables, indexes, etc), then you create a new migration file. A migration file is stored in ./scripts/migrations and is named like this 001-CreateUsersTable.php. The number defines the order that the migrations should be performed as any given migration will make assumptions about the state of the database before it is run.
The migration file contains a class that extends Akrabat_Db_Schema_AbstractChange and must contain two methods: up() and down(). It follows that up() is called when implementing the changes in this migration and down() is called to put the database back where it was if the change is backed out.
001-CreateUsersTable.php:
class CreateUsersTable extends Akrabat_Db_Schema_AbstractChange
{
function up()
{
$sql = "CREATE TABLE IF NOT EXISTS users (
id int(11) NOT NULL AUTO_INCREMENT,
username varchar(50) NOT NULL,
password varchar(75) NOT NULL,
roles varchar(200) NOT NULL DEFAULT 'user',
PRIMARY KEY (id)
)";
$this->_db->query($sql);
$data = array();
$data['username'] = 'admin';
$data['password'] = sha1('password');
$data['roles'] = 'user,admin';
$this->_db->insert('users', $data);
}
function down()
{
$sql = "DROP TABLE IF EXISTS users";
$this->_db->query($sql);
}
}
As you can see, this code will create a database table called users and then insert the first user. The backing out code (down()) just drops the table. As you can see, easy enough and uses Zend_Db_Adater so the database calls should be familiar.
For this to work, we need to store the current version of the schema within the database. This is done using a table called schema_version which contains just one column, version, and one row holding the current version number. Akrabat_Db_Schema_Manager will create this table if it does not exist.
Akrabat_Db_Schema_Manager
This is the class that does the work. It's based on the skeleton on the proposal and the code is here. There are two public methods in Akrabat_Db_Schema_Manager :
- getCurrentSchemaVersion()
- updateTo($version)<
In order to operate, Akrabat_Db_Schema_Manager needs the directory holding the migration files and a database adapter, so these are passed in via the constructor. The operation of updateTo() is fairly simple:
- Find and order migration files from current version to target version
- Iterate over migration files and call up() or down() as appropriate
- Update the current version number in the database
Obviously, getCurrentSchemaVersion() simply queries the database for the current version number.
Command line
To actually run the schema manager, we hook into Zend_Tool so we can use the zf command line tool. This means that we need a provider, Akrabat_Tool_DatabaseSchemaProvider. A provider is used by Zend_Tool to provide functionality for zf. We create a public method for each operation we want to be available at the command line:
- updateTo($version, $env='development', $dir='./scripts/migrations')
- update($env='development', $dir='./scripts/migrations')
- current($env='development', $dir='./scripts/migrations')
The update() method is simply an alias to updateTo() that ensures that you update to the latest migration script that you have.
They are used like this:
$ zf current database-schema
Current schema version is 0
$ zf update database-schema
Schema updated to version 2
$ zf current database-schema
Current schema version is 2
The update-to operation allows you to roll-back also:
$ zf current database-schema
Current schema version is 2
$ zf update-to database-schema 1
Schema updated to version 1
$ zf current database-schema
Current schema version is 1
It turns out that adding a new provider to Zend_Tool requires a little bit of work. Ralph Schindler is the man who writes it all and Cal Evans is the ZF command line guru that writes it up in a way I can understand. A quick search of his blog helped me work this out. Thanks Cal!
These are the steps that you need to do:
- At the command line, run zf --setup storage-directory
This creates a folder for storing Zend_Tool configuration files. Make a note of which directory it has created.
- Run zf --setup config-file
This creates zf.ini in your storage directory.
- Edit zf.ini and change the php.include_path path so that it includes the full path to Akrabat/library in addition to the path to Zend Framework.For my installation, I have placed both Zend Framework and the Akrabat library in /usr/local/include, so I have:
php.include_path = "/usr/local/include/zend-framework/library:/usr/local/include/Akrabat/library/"
- Finally, we need to tell Zend_Tool about our new provider. This is done using the basicloader.classes key in zf.ini. So add:
basicloader.classes.0 = "Akrabat_Tool_DatabaseSchemaProvider"
Once you've done that (and installed Akrabat into /usr/local/include or wherever), then you should now be able to run $ zf ? database-schema and get a result:
Everything is now in place. All you need to do is create a script/migrations directory and populate it with migration scripts and you're good to go!
If you come across any bugs, please report any bugs you find (preferably with the fix!)
Update: Table prefixes are now supported.
