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

1) Install it as per the README:

gem install db_structure_ext and/or add gem 'db_structure_ext' to Gemfile and then bundle install

2) require it in your Rakefile

# extended db-structure-dump (also does triggers, routines etc)
require 'db_structure_ext/tasks'

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:

Dave Aronson said...

Hi Taryn!

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.

Thanks,
Dave

Taryn East said...

Yes, I really meant 2.3.

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).

Dave Aronson said...

Hi Taryn!

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.

Thanks,
Dave

Taryn East said...

Cool. Looks interesting - it has a lot of support for generating and manipulating your triggers, and supports Rails 4.

Looks like it's very specific to triggers (ie won't do procedures et al), but if that's all you need, this looks like it'll do much more for you.

Thanks for sharing it :)

Taryn