PHP - Database schema: version control, branching, migrations.

Question

I'm trying to come up with (or find) a reusable system for database schema versioning in php projects.

There are a number of Rails-style migration projects available for php. http://code.google.com/p/mysql-php-migrations/ is a good example. It uses timestamps for migration files, which helps with conflicts between branches.

General problem with this kind of system: When development branch A is checked out, and you want to check out branch B instead, B may have new migration files. This is fine, migrating to newer content is straight forward.

If branch A has newer migration files, you would need to migrate downwards to the nearest shared patch. If branch A and B have significantly different code bases, you may have to migrate down even further. This may mean: Check out B, determine shared patch number, check out A, migrate downwards to this patch. This must be done from A since the actual applied patches are not available in B. Then, checkout branch B, and migrate to newest B patch. Reverse process again when going from B to A.

Proposed system: When migrating upwards, instead of just storing the patch version, serialize the whole patch in database for later use, though I'd probably only need the down() method. When changing branches, compare patches that have been run to patches that are available in the destination branch. Determine nearest shared patch (or oldest difference, maybe) between db table of run patches and patches in destination branch by ID or hash. Could also look for new or missing patches that are buried under a number of shared patches between the two branches.

Automatically merge down to the nearest shared patch, using the db table stored down() methods, and then merge up to the branche's latest patch.

My question is: Is this system too crazy and/or fraught with consequences to bother developing? My experience with database schema versioning is limited to PHP autopatch, which is an up()-only system requiring filenames with sequential IDs.

Answer 1

Well, I wasn't able to find any reason not to move forward.

The project, such as it is, is here:

http://github.com/Billiam/MySQL-PHP-AutoMigrations

Needs some love (accurate comments, unit testing, actual bug testing), but seems to be working well for me now.

It's a fork of http://code.google.com/p/mysql-php-migrations/ to include the ideas above, and some other little stuff.

Migrating down is done from methods saved in the database on the way up so that file changes (like those when switching between branches) do not affect downward migrations. Added two functions:

• a magic 'auto' function that handles migrating down to the oldest shared migration, and then up to the newest migration in the migrations directory.
• 'propose' function that shows what auto will actually do.

Still very open to hearing potential (or even expected) pitfalls from this approach however.