Saturday, December 31, 2011

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.

1 comment: