Akrabat_Db_Schema_Manager: Zend Framework database migrations
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.
Love your post! But I'm curious why you chose for your own implementation instead of (for eg.) Doctrine to settle your db structure and migrations?
Reinventing the wheel is sooo great nowadays
I don't use Doctrine or ezcDatabaseSchema and so didn't feel like learning how to integrate them.
This is especially true as I use the MS SqlSrv driver to connect to SQL Server on Windows. This wasn't supported by either Doctrine 1.x or ezcDatabaseSchema last time I checked.
PDO and the SqlSrv driver are both supported by Zend_Db_Adapter, so a tool that uses that makes life easier for me as there's less to learn.
If there's another obvious PHP based choice that runs on Windows, Mac and Linux and supports Pdo_Mysql and SqlSrv, please let me know.
Regards,
Rob…
This looks good, I'd love to see this in ZF core.
The only thing I'd change is the numeric naming of the migrations. If you have more than one developer working on an application it then becomes possible for both to add a new migration with the same number. Ruby on Rails originally used an approach like this but switched to using timestamps in the filenames instead for exactly this reason. So you would have filenames like:
20100329120000-CreateUsersTable.php
instead. Then you just keep track of the timestamp you last migrated to in your schema table.
Like your approach. It might be especially useful together with models i.e.
$model = new My_Model($params);
$mapper->save($model);
Much cleaner, than doing a bunch of inserts manually.
However, how it differs from dbdeploy? Not sure about SqlSrv support tho.
Tim,
That's not a bad idea. In practice, the code already supports it as it uses the number in the filename as the version number and doesn't actually care about if they are sequential.
Dmitri,
Biggest difference is that dbdeploy is Java based, which won't run on my clients' Windows servers :)
Regards,
Rob…
Rob,
Doctrine uses PDO natively, however, I'm unsure if it directly supports MSSQL.
There is a design decisions I'd like for you to backup, for instance why are you running the sql directly in your up/down statements rather than returning a string/array from your method that a protected method would execute?
Other than that it looks good, I already know one place where it'll be implemented quickly (with some minor modifications)
Shawn
Shawn,
What would the benefit be? I can't see any benefit over running the queries directly in the method.
Regards,
Rob…
I'm curious how Java doesn't work on Windows servers. Regardless, have you considered, for future development, to include a Phing task?
Rob,
None of my client's IT department that provision Windows web servers for me will install Java. I appreciate that this may be unusual in the Windows server world, however it remains a hard fact for my company.
A Phing task probably wouldn't be that hard, however.
Regards,
Rob…
Rob,
The reason to not run sql directly in the migration up/down is because a. consistency and duplication and b. it will allow you to (in the future) auto-generate your migrations and that generation isn't tied into knowledge of the db interaction. Primarily though I believe it's important to cut down on the amount of times you're duplicating the call to get the db object into your method and also the amount of times you're duplicating the call to $db->query() where (even though it's not likely) the query method may have changes in the future. Anyway it's just my three cents.
Hey Rob,
I wonder in how far this is more advanced then DBMigrate, which is included in both Ant and Phing?
Ben,
DBMigrate is Java and so not useful for me.
Regards,
Rob…
Oh, I was surely talking about DBDeploy, and for Phing, it is written in PHP :)
Main reasons against DBDeploy are lack of SqlSrv driver support and SQL only in migration files. As I'm lazy, I find it helpful to be able to manipulate with PHP whilst migrating.
Having said that, DBDeploy and Phing is very nice combination.
This is great! I do agree with Tim Fountain that the identifier should be a timestamp, not an index.
The @param $options is listed in PHPDoc of __constructor() in class Akrabat_Db_Schema_Manager but not used in.
This is a stock for the future features, or it is the trace of old ideas?
Alexandr,
I was going to use a generic $options array but then decided that table prefix was important enough to have an explicit parameter.
Rob…
Hello,
I don't know why but it's right when I use the command
$ zf update-to database-schema 0
Please provide a value for $version
zf> 0
An Error Has Occurred
Value supplied for required parameter "version" is empty
And I put the version control number into a XML file… Looks great =)
I think this is a problem with Zend_Tool
Regards,
Rob…
I see you write SQL directly in the code. DDL for different databases are different, so raw SQL many brings problem when the database is changed. Schema Manager in Doctrine offers a abstract layer of DDL, and it could resolve this problem.
Hi Ting,
Yes – if you need to support multiple database servers, then an abstraction layer makes life easier. Not that many people have to do that though.
Regards,
Rob…
@Rob, I have found one of your propose about Zend_Db_Schema_Manager http://framework.zend.com/wiki/display/ZFPROP/Zend_Db_Schema_Manager+-+Rob+Allen. How about this zend_db_schema_manager now? I am working now with zendframework and using dbdeploy + phing for the database migration. Now we have problem when we change our database from mysql to postgresql (DDL of mysql and postgresql are so different). I found some solution by doctrine, but I would like to use some Zendframework staff, if there is.
Ting,
I think you're better off using Doctrine for your specific issue :)
Rob…
Rob,
We are using doctrine migration for database migration now. It is really really cool. http://www.doctrine-project.org/projects/migrations
although the doc is really poor. But the code is very good organized and easily to read and understand.
Hello again…
I'm using your migration manager and I have an idea: Why do we not read an sql file? I develop this method in your abstract change:
public function read($filename, $migration)
{
$filename = (string) $filename;
if (!is_file($filename)) {
throw new Akrabat_Db_Schema_Exception("File Not Found: '$filename'");
}
if (!is_readable($filename)) {
throw new Akrabat_Db_Schema_Exception(
"Permission Denied: '$filename'");
}
$pattern = "/– migration $migration ([0-9]+).*/";
$lines = file($filename, FILE_IGNORE_NEW_LINES);
$content = array();
foreach ($lines as $number => $line) {
if (preg_match($pattern, $line, $matches)) {
for ($i = $number; $i read($filename, 'people');
$this->_db->query($structure);
$structure = $this->read($filename, 'people-insert');
$this->_db->query($structure);
}
… and the sql file in this format:
— migration people 9 lines
CREATE TABLE "site".people
(
id SERIAL NOT NULL,
username VARCHAR(20) NOT NULL UNIQUE,
password CHAR(32) NOT NULL,
deleted BOOLEAN DEFAULT FALSE,
active BOOLEAN DEFAULT TRUE,
PRIMARY KEY(id)
);
— migration people-insert 1 line
INSERT INTO "site".people(username,password) VALUES ('root', MD5('root'));
Sorry for my English…
Hugs!
Very Nice! I think I found a small issue with Akrabat_Tool_DatabaseSchemaProvider. The functions updateTo(…) and current(…) instantiate a slightly different version of Akrabat_Db_Schema_Manager, one without the prefix and one with the prefix. I changed lines 36 and 75 to the following:
$manager = new Akrabat_Db_Schema_Manager($dir, $db, $this->getTablePrefix());
and, it seemed to resolve the issue. Without this I was getting two database tables, '_schema_version' and 'schema_version'.
Thanks for letting me know! I have updated the code on github.
Regards,
Rob…
Wow exactly what I was looking for. On the Zend proposal, it says it was archived at your request?
I think this would make an outstanding contribution to ZF.
Nick,
Akrabat_Db_Schema_Manager is a very simple solution. It would probably require considerably more effort to make if suitable for a Zend Framework component as it would probably need proper methods for the meta data creation for each database.
Regards,
Rob…
Rob,
thanks for creating Akrabat and publishing it here! It's easy to understand and it does everything I need… :-)
oh, I think I would like to mention for other readers is that the link to the git repository above is outdated… I cloned https://github.com/akrabat/Akrabat.git
The new version of the tool seems to require a prefix. If you don't want one, it prefixes with an _.
Here's a diff
— DatabaseSchemaProvider.php 2010-12-14 08:52:54.000000000 -0500
+++ DatabaseSchemaProvider_fix.php 2010-12-14 10:02:03.000000000 -0500
@@ -129,10 +129,9 @@
$prefix = ";
if (isset($this->_config->resources->db->table_prefix)) {
$prefix = $this->_config->resources->db->table_prefix;
+ $this->_tablePrefix = $prefix.'_';
}
– $this->_tablePrefix = $prefix.'_';
}
return $this->_tablePrefix;
}
–
-}
No newline at end of file
+}
Also,
Your version is an int, so we can't use 201012140001 as a version number. I manually changed it to a bigint field and it seems to work they way I want/need. I really like the idea of the version being a date.
John,
Good catches – I've updated and pushed.
Thanks!
Rob…
Hi Rob
Great stuff!
I'm seeing the same response as Wanderson when running:
$ zf update-to database-schema 0
There doesn't seem to be a way to run the down method on the first migration?
Cheers
Bruce
@Wanderson & Rob
I happened upon the solution for the version 0 migration by accident. If you make the revision portion of the command a character, it will convert to the integer 0, therefore sending you all the way back to revision 0. It was unfortunate to find the hack the way I did, but it works :P I was trying to goto revision 3 and instead went to revision "e" because I fat fingered my keyboard.
Nice tool – I have to admit though I dont like the term "update" – I think it would be better to call it "migrate". Doing something like "update-to" when you really want to downgrade seems a bit weird.
When you want to migrate to the first schema you can use this also
zf update-to database-schema 0
when asked again for a migrate version answer with (int) 0
i just try using the cast hint on the first command, it also worked:
zf update-to database-schema (int)0
Hi Rob
how can I use this tool not from command line, but in regular php script, is it possible?
I'm asking, because I want to add db migration functionality to pre_activate.php hook script(for update) and pre_rollback.php(for migrate to earlier db version), which runs during deployment to ZS/ZSCM.
So idea is just add Akrabat directory to library/ directory, and simple include necessary classes in pre_activate.php and pre_rollback.php and perform updating to necessary db version.
No reason why you can't instantiate the schema manager directly in the same way that the provider does.