MySql Views and Rails, The Easy Way

Joe Cannatti and I recently needed to tame a interesting database design in a legacy code base and determined that a mysql view would be just the ticket to create a simple solution to a complex data problem.

Out of the box rails (3.2 RC1 as of this writing), you cannot use views very easily. We could not find proper solutions online and there is a "view" gem that is not updated so we did some digging and developed the following simple solution to utilize views in rails.

1. Update your config/application.rb file to use sql instead of active records schema dumper by adding the following line.
   

   config.active_record.schema_format = :sql
   

2. Monkey patch Mysql2Adapter or other appropriate data adapter to include the views in the export. If you are using rails 3.* then the update is small.
   
   Create a file called config/initializers/monkey_patch_abstract_mysql_adapter.rb

   module ActiveRecord
     module ConnectionAdapters
       class Mysql2Adapter
   
         alias :build_table_structure :structure_dump
   
         def structure_dump
           build_table_structure << build_view_structure
         end
   
         def build_view_structure
           sql = "SHOW FULL TABLES WHERE Table_type = 'VIEW'"
   
           select_all(sql).inject("") do |structure, table|
             table.delete('Table_type')
             structure += select_one("SHOW CREATE VIEW #{quote_table_name(table.to_a.first.last)}")["Create View"] + ";\n\n"
           end
         end
   
       end
     end
   end 
   

3. Create your view in a migration file using "execute" to run raw sql.
   

   class MyDatabaseView < ActiveRecord::Migration
   
     def self.up
       execute "CREATE OR REPLACE VIEW my_database_view ..."
     end
   
     def self.down
       execute "DROP VIEW my_database_view"
     end
   
   end
   

When creating your active record models against your views, please remember to to define the "readonly?" method to true unless you have created your view as a "update/insert" view.

def readonly?
   true
 end

Now you are all setup to use mysql views in your rails application. If you are using another database adapter, look for the method called "structure_dump" in your adapter as the rake task uses this task to dump the database structure as sql.