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.