How to migrate stored procedures to testing db?

Asked 2010-11-05T17:42:27.857

I have an issue with stored procedures and the test database in Rails 3.0.7. When running

rake db:test:prepare

it migrates the db tables from schema.rb and not from migrations directly. The procedures are created within migrations by calling the execute method and passing in an SQL string such as CREATE FUNCTION foo() ... BEGIN ... END;.

So after researching, I found that you should use

config.active_record.schema_format = :sql

inside application.rb. After adding this line, I executed

rake db:structure:dump rake db:test:clone_structure

The first one is supposed to dump the structure into a development.sql file and the second one creates the testing database from this file. But my stored procedures, and functions are still not appearing in the testing db. If anyone knows something about this issue. Help will be appreciated.

I also tried running rake db:test:prepare again, but still no results.

MySQL 5.5, Rails 3.0.7, Ruby 1.8.7.

Thanks in advance!

Edit Report

2 Answers

Was searching for how to do the same thing then saw this: http://guides.rubyonrails.org/migrations.html#types-of-schema-dumps

To quote:

"db/schema.rb cannot express database specific items such as foreign key constraints, triggers or stored procedures. While in a migration you can execute custom SQL statements, the schema dumper cannot reconstitute those statements from the database. If you are using features like this then you should set the schema format to :sql."

i.e.:

config.active_record.schema_format = :sql

I haven't tried it yet myself though so I'll post a follow-up later.

answered 2011-03-09T03:43:00.093

I took Matthew Bass's method of removing existing rake task and redefined a task using mysqldump with the options that RolandoMySQLDBA provided

http://matthewbass.com/2007/03/07/overriding-existing-rake-tasks/

Rake::TaskManager.class_eval do
  def remove_task(task_name)
    @tasks.delete(task_name.to_s)
  end
end

def remove_task(task_name)
  Rake.application.remove_task(task_name)
end

# Override existing test task to prevent integrations
# from being run unless specifically asked for
remove_task 'db:test:prepare'

namespace :db do
  namespace :test do
    desc "Create a db/schema.rb file"
    task :prepare => :environment do
      sh "mysqldump --routines --no-data -u root ni | mysql -u root ni_test"
    end
  end
end

answered 2011-11-03T16:24:23.967

How to migrate stored procedures to testing db?

2 Answers

Your Answer