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.

Monday, December 26, 2011

Create Rails Models From JSON

Recently, I had the need to be able to create and save an active record model based on a json object returned back to the server. After some digging around I came across the following solution that fits the bill.

my_model_params = params[:my_model_params]
my_model = MyModel.new(JSON.parse(my_model_params))

JSON.parse from the ruby json gem allows you to parse a json string into a hash object which can then be used to create your model as you normally would in rails.