Simple Database Patching Strategy
Monday, March 1. 2010
I would suggest adding a patch history instead of one row for your version.
Just do a ‘filename’, ‘version’, ‘timestamp’ meta_data table.
That way you also know which file was executed when and what version they upgraded the database to.
The current database version is then the MAX
Nice post. Like it much, really. My projects are usually so rushed that no one takes the time to set up any versioning whatsoever.
One exception: CuddleFish CMS. It is Zend Framework based and has a dedicated DbController that has an action for every db update ever applied.
The advantage? You can use the regular CMS GUI to update the database by clicking a friendly name in a list of actions. From the same interface you are one click away from an install script for the database.
But then, this is highly dedicated. I like your approach very, very much. Anyone who is not using a database versioning system yet should give it a try.
This seems a good approach when you own all the code and the database. However, there are many times when you don’t completely own one or both of these – eg. linking into a corporate database that the webdevs can’t change or using a CMS that is mostly written by other people. In those situations it becomes really difficult to do decent versioning, and I’ve yet to see a good way of handling Drupal databases in version control.
Alice: so true, and I’ve seen several scary ways of managing database changes on drupal, mostly involving mysql proxy! All the dynamic apps have the same issues and none seem to solve them in a good way.
I use something similar, although I bundle it all into an ‘upgrade.php’ script which contains all the sql changes and updates the meta_data like table for you; as the ‘admin’ you just need to hit ‘upgrade.php’ every once in a while and off it goes.
As an example, see http://postfixadmin.svn.sourceforge.net/viewvc/postfixadmin/trunk/upgrade.php?revision=794&view=markup
the only downside to my approach is that it doesn’t (yet) support reverting to an old configuration – something I’ve never found to be necessary so far.
I think the postfixadmin one takes into account the svn revision number; while on other projects I just use a sequential number.
Take a look at
http://sourceforge.net/projects/liquibase/
I used this in the way Lorna Jane mentions, for DDL changes, only. This also runs nicely inside a Phing script.
This does not allow rollbacks, but does allow roll-forwards from the beginning.
Perhaps it is not unreasonable to dump data as XML or CSV, especially if all tables have timestamps.
I like the idea of example data, which can be used in PhpUnit testing
Thanks to everyone who is leaving comments, I knew there would be some great suggestions!
Harro: That’s a nice extension to this method, thanks for adding it.
Bart: I haven’t used CuddleFish but after your mention I will look out for it in future.
David: Thanks for the example links, those are great exaamples of this type of approach in use :)
Bruce and Mark: some great additional resources listed there – cheers!
Here is the simplest scheme, which allows rollback: ZFS. It definitely works on *BSD; not sure about simple, but feasible. It’s bundled with Solaris and some OSX; I don’t know about Linux. I hope you weren’t really trying to run this kind of web on MS Windows.
http://dev.mysql.com/doc/refman/5.0/en/ha-zfs-replication.html
Hi,
Good overview of a very simple scheme. I like it :).
In my day job, we use a particular ORM a lot, which does the same kind of thing wrapped in a little DSL – and from it, I’d suggest the following:
First, especially for /big/ projects in a distributed VCS, use timestamps instead of sequential numbers. It really reduces the potential pain at the cost of looking slightly uglier.
Second, if you want to get a bit fancy, have a ‘schema_versions’ (or similarly named) table with a ‘version’ column. Every time you apply a patch, insert a new row into that table with the version ID/timestamp. This lets you have a stable deployment with migrations 1,2,3 and a staging one with 1,2,3,4,5,then backport a bugfix that relies on migration 5 but not 4 all by itself, then do a migration with 4,6,7,... when the time comes to upgrade stable by a major version.
It also lets you have ‘up’ and ‘down’ migrations – so you can apply and unapply each one individually if you want to – but I’ve never found that to be particularly useful. But then, I have a bad habit of doing data migrations at the same time as the schema migrations… ;)
I have made a php script for versioning a mysql database exacly in the strategy you talked about.
http://code.google.com/p/php-mysql-version-control/
