Why does rake db:migrate in Rails not add functions to the schema file?

Question

I recently added some SQL functions to my database via a migrate, and they work perfectly fine. Code works, database works, tests don't. The schema.rb file is telling me that it's at the latest version (and it's correct), but it only contains definitions for tables and indexes, not the functions I added.

I added the functions via the "execute()" method in my migration, and need them in the test database for the RSpec tests to pass (company policy dictates that I can't commit this changes until this is fixed).

Does anyone know why this is happening, or how to fix it? I can manually go into the MySQL command line and add the functions, but then they're erased the next time someone does a db:test:prepare. I need a solution that can be automated.

Thanks for any help and replies,

-Mike Trpcic

Answer 1

schema.rb is the Ruby schema format and it doesn't support functions or many other more advanced features. Change the format to plain SQL (in environment.rb) and you should be good to go.

config.active_record.schema_format = :sql

Edit: After Mike's comment, I did a bit of digging. The Rake task to dump the schema calls into the ActiveRecord connection adapter for MySQL and Oracle. MySQL's isn't very smart and only dumps the table structure, ignoring everything else. PostgreSQL, SQLite, SQL Server and Firebird call out to a vendor-provided executable.

mysqldump doesn't support dumping stored procedures before version 5.0.13.

I looked around the Rails Lighthouse, but searching for open tickets there is far harder than it should be. If no one else has filed a bug on this, you might consider doing so, though without a patch, it's not likely anyone is going to work on it.