Rails, rake tasks, mysql 5.0 views, PAIN, and a fix.

David Heinemeier Hansson has been vocal about philosophical reasons to not have Rails support database-based logic like VIEWS and STORED PROCEDURES. I like simplicity and agree with David in theory, but I ran into an issue recently with a legacy database that relied heavily on VIEWS and Rails did not play nice. If there is interest, I can touch on how I mapped my models to my VIEWS--but a more frustrating task was getting the testing infrastructure to be happy with non-standard database schema. The production database is Microsoft SQL Server, and our development database is MySQL 5.0. Development is done on OS/X, Windows and Linux and in all environments testing and development is in local MySQL 5 instances, with pre-staging environment configurations for remotely testing on a MS SQL server. I have previously discussed other subtle issues with a setup of this fashion regardless of the use of views. When running: 'rake test', we were seeing a relatively ambiguous error:

rake Aborted! Test Failures

A little debugging 'rake test --trace' and perusing the logs showed that the error was being caused by an SQL statement asking MySQL to create a table with invalid parameters. Why? Because this table was actually a VIEW, but when rails exported it, it exported it like a table. Our config/environment.rb was set for:

config.active_record.schema_format = :ruby

Diving into Rails table exporter was a bit intimidating, so we tried setting the schema format to:

config.active_record.schema_format = :sql

This yielded the same error. What was going on? Running rake with the trace flag revealed that the malformed SQL was generated within

/usr/local/lib/rails-1.1.2/lib/tasks/testing.rake:35

This error was tracked down to the db:test:prepare task which was calling db:test:clone_structure (when schema_format was set to :sql) and db:test:clone (when schema_format was set to :ruby). Needless to say, BOTH of these tasks failed when dealing with VIEWS. Our options at this point were to fix Rails schema dumpers, replace them with a database-specific command like MySQL's mysqldump command, or come up with a complete workaround. We chose the latter--and took advantage of our strong adoption of rake migrations. Our development database build procedure is to run 'rake migrate' and let our migrations ready the DB. Our migrations use ruby syntax for the majority of the table configuration, then use bulk SQL commands to create all of the VIEWS in the application. Since this worked for our development databases, there was no reason not use it for our test databases as well. We did a bit of digging through the Rails rake tasks and modified db:test:prepare to run our migrate task within the test environment and it worked quite well. We did this by adding a third option to our config.active_record.schema_format (set in config/environment.rb) named :migrate--then adding an additional case statement within the existing db:test:prepare. So now environment.rb has :

config.active_record.schema_format = :migration

And our modified database.rake (within the /usr/local/lib/rails-1.1.2/lib/tasks) directory database.rake has a new :prepare task:

desc 'Prepare the test database and load the schema' task :prepare => :environment do schema_format = ActiveRecord::Base.schema_format case schema_format when :sql Rake::Task["db:test:clone_structure"].invoke when :ruby Rake::Task["db:test:clone"].invoke when :migration # Use a migration to ready the test database # Useful when :sql or :ruby are failing which occurs universally when # views are present. Rake::Task["db:test:purge"].invoke ActiveRecord::Base.establish_connection( ActiveRecord::Base.configurations['test']) ActiveRecord::Schema.verbose = false Rake::Task["db:migrate"].invoke else # This could also default to :ruby... DHH thoughts on this? raise "Task not supported by '#{schema_format}'" end end

Hopefully this is a helpful addition for anyone else managing a legacy database. Between this patch and adopting migrations (a great tool regardless), VIEWS and STORED PROCEDURES can work nicely with the Rails testing system.