

As our PHP development team grows we’ve begun running into the familiar problem of database migrations. And not only between local dev environments, but deployments too. It’s nice that Susie told Joe Developer about her latest db changes so he can continue work on his feature, but if I need to do a release early tomorrow morning, I’m staring at a nasty database error on the live site.
Existing Solutions
Of course I went out on the “open market” and had a look at the available offerings. Phing seems very mature and feature complete, but involves XML editing which, after years of java development, horrifies me. Codeigniter Migrations looks great but I don’t particularly want to bootstrap the entire framework for some db migrations. Both Ruckusing Migrations and MySQL PHP Migrations look quite promising but I don’t need full blown up/down migrations right now.
Requirements
I need to be able to tie a subversion revision id with some database update. The database migration should be executed automatically on the staging environment when the developer commits their changes. And Ops needs an email notification which alerts us to a successful (or failed) db migration on staging, including the revision id and the SQL we’ll need to execute on production for the next release.
The players
The updates should be in one file which doubles as a “bootstrap” script for empty databases. Here’s a snippet of our update.sql
file:
We have a _sql_version
table in the database which contains the last successfully executed migration block id from the update.sql
file. Taking the last id from this table, the update_db.rb
ruby script scans the update.sql file looking for any/all migration blocks to execute by looking for ids greater than the last id from the table. Thus, the revision ids listed in the file are just a “best guess” from the developer (as they are writing this in before committing). The system is very tolerant as it executes any & all revisions after the last known id.
Here’s the update script – please forgive a PHP developer for playing around in Ruby 😉 :
require 'rubygems' require 'mysql' require 'net/smtp' require '_common/globalvars' # take a cmdline argument of the current SVN Revision ID # NOTE : this is NOT the user revision ID written in the update.sql (which is just a guess), # but the REAL svn revision id from the server svn_revision_id = ARGV[0].to_i db_host = MyRegistry::get()['database']['cr']['host']; db_user = MyRegistry::get()['database']['cr']['username']; db_pass = MyRegistry::get()['database']['cr']['password']; db_name = MyRegistry::get()['database']['cr']['name']; def send_email(svn_revision_id, message) msg = <<END_OF_MESSAGE From: To: Subject: [#{svn_revision_id}] db updates & schema changes #{message} END_OF_MESSAGE Net::SMTP.start('localhost') do |smtp| smtp.send_message msg, 'db_admin' end end begin dbh = Mysql.real_connect(db_host, db_user, db_pass, db_name) # determine the last svn sql update revision id from database last_sql_user_revision = 0 result = dbh.query('SELECT MAX(user_revision) FROM _sql_version'); last_sql_user_revision = result.to_i result.free #PARSE update.sql updates = [] queryString = "" user_revision = nil catch :OUT do File.open('update.sql') do |file| file.readlines.each do |line| line = line.strip() unless line.empty? throw :OUT, user_revision = line.to_i if (line.to_i > 0 && line.to_i 0) user_revision = line.to_i else if (line.index(';') != nil) updates.push(Hash[user_revision, query_string + ' ' + line]) query_string = "" else query_string += ' ' + line end end end end end end #EXECUTE SQL statements parsed out above last_user_revision_id = -1 message = "" db_updates_made = 0 catch :OUT do updates.each do |update| update.each_pair do |user_revision, query_string| throw :OUT if (user_revision <= last_sql_user_revision) dbh.query(query_string) db_updates_made += 1 message += query_string + "rn" last_user_revision_id = user_revision if (last_user_revision_id 0) update_sql_version = "insert into _sql_version (`user_revision`) value (#{last_user_revision_id})" dbh.query(update_sql_version) #EMAIL developers with ALL SQL updates for SVN Revision ID message = "Apply following " + db_updates_made.to_s + " updates to db:rnrn" + message + "rnrn#{update_sql_version};rnrnNOTE: These updates have already been applied to staging." puts message send_email(svn_revision_id, message) else puts "0 updates made to db.rnrnupdate.sql = #{user_revision} & _sql_version.user_revision = #{last_sql_user_revision}" end rescue Mysql::Error => e puts "Error message: #{e.error} (#{e.errno}) | Error SQLSTATE: #{e.sqlstate}" if e.respond_to?("sqlstate") if (db_updates_made > 0) puts "rnDied in section #{last_user_revision_id}, but successfully executed the following queries: #{message}" end raise RuntimeError ensure dbh.close if dbh end
The last update is to the _sql_version
table:
Now we’re ready for the next db migration.
Automated Migrations with Hudson
Hudson runs that ruby script on every SVN update:
A notification email gets sent out with the status, svn revision id, and any SQL that should be executed. As this email goes out to the whole team, developers can use this to bring their own environment up to speed (either manually or just executing the update_db.rb
script locally).
If you are debating how to rollout db migrations for your team, definitely take a look at those open options I first mentioned. But if you have any reservations, feel free to borrow my ideas & code! For the rest of you guys, how are you realizing migrations in your coding projects?
One thought on “Simple Database Updates with Ruby”