Monday, 15 October 2012

Making views/triggers/functions work in mysql for rails

We use mysql with Rails. We also use foreign-keys, views, triggers and stored procedures.

Rails's mysql gem still doesn't know how to handle any of the above yet... which is kind of inconvenient if you want to test your application.

The problem arises because even with a SQL schema dump - it only dumps the tables and foreign keys. No views, no triggers, no stored procedures. If you have code that relies on these... your application starts getting MySql errors.

Luckily there seems to be a solution with the db_structure_ext gem, which I've been using happily now for a couple of weeks.

I found the README isn't at all clear as to how to get it set up, so here's some basic instructions on how I made it work with MySQL on Rails 2.3.X

2) require it in your Rakefile

3) Monkey-patch ActiveRecord

Create a file call config/initializers/mysql_adapter.rb (or similar) and add the following code:

module ActiveRecord
module ConnectionAdapters
class MysqlAdapter
require 'db_structure_ext/init_mysql_adapter'
# This is an overridden implementation of the structure_dump so that the
# rake take db:structure:dump will dump out the schema elements.
def structure_dump
connection_proxy = DbStructureExt::MysqlConnectionProxy.new(ActiveRecord::Base.connection)
connection_proxy.structure_dump
end
end
end
end

You need this so that Active Record actually extends the new methods. If you don't do this, then you can call "structure_dump" yourself in your code, independently for certain tables. But the new functionality won't come through as the default for all of your db tables unless you extend MysqlAdapter as per above.

4 comments:

Did you really mean 2.3, or actually 3.2? I'm looking for a solution for 3.2 -- and possibly soon for 4.0. Do you know offhand if this gem will work with those? The repo says it's been tested with AR up to 3.0, and everything but the license is two years old. If it turns out it won't work with these, do you have any suggestions? Mainly I need some triggers to actually show up in the testing environment.

Sadly there are still a number of legacy Rails systems out there and I was working on one of them.

I haven't used this gem with rails 3.2, but what it does is pretty straightforward - I don't suppose that it needs to change much - I'd give it a go and see if it works for you.

If it doesn't work, I'd expect that it'd be minor changes such as the jiggering with module names - you could probably monkey-patch that without much effort (or better yet, fork it and submit back as a patch).

We wound up going with HairTrigger, which supplies Rails-ish ways to declare triggers (in the models and/or migrations, able to generate the 2nd from the 1st), and (the important bit) makes the schema dumping and loading trigger-aware.