This post originated from an RSS feed registered with Ruby Buzz by Matt Bauer. Original Post: Migrations For Function And Procedures Feed Title: blogmmmultiworks Feed URL: http://blog.mmmultiworks.com/feed/rss.xml Feed Description: Thoughts on Ruby, hosting and more Latest Ruby Buzz Posts Latest Ruby Buzz Posts by Matt Bauer Latest Posts From blogmmmultiworks

In my current project I have a couple of stored procedures and functions. What, you thought they were forbidden in Rails? Please, use the best tool for the job. Having said that, Rails doesn't provide a nice way to get functions and procedures into the database. What I want is to use the migrations framework. Below is the the code that does just that. It has some clear duplication but it's not worth it to metaprogram it to remove the duplication. Especially since it's only 3 lines per method.

To use this code, simply copy the code below into the file #{RAILS_ROOT}/config/initializers/mysql.rb, create two directories call functions and procedures under #{RAILS_ROOT}/db, create files name <function name>.sql or <procedure name>.sql with the sql for you function or procedure, and run rake db:migrate.

module ActiveRecord
  module ConnectionAdapters # :nodoc:
    class AbstractAdapter
      def supports_procedures?
        false
      end
      
      def create_procedure(name, sql)
        return nil unless supports_procedures?
        execute "DROP PROCEDURE #{name}" rescue nil
        execute sql
      end
      
      def supports_functions?
        false
      end
      
      def create_function(name, sql)
        return nil unless supports_functions?
        execute "DROP FUNCTION #{name}" rescue nil
        execute sql
      end  
    end
    
    class MysqlAdapter
      def supports_procedures?
        true
      end
      
      def supports_functions?
        true
      end      
    end    
  end

  class Migration
    class << self
      def create_procedures
        Dir["#{RAILS_ROOT}/db/procedures/*.sql"].sort.each do |full_path|
          ActiveRecord::Base.connection.create_procedure File.basename(full_path)[0..-5], File.read(full_path)
        end
      end
      
      def migrate_with_procedures(direction)
        migrate_without_procedures(direction)
        create_procedures
      end
      
      alias_method_chain :migrate, :procedures
      
      def create_functions
        Dir["#{RAILS_ROOT}/db/functions/*.sql"].sort.each do |full_path|
          ActiveRecord::Base.connection.create_function File.basename(full_path)[0..-5], File.read(full_path)
        end
      end

      def migrate_with_functions(direction)
        migrate_without_functions(direction)
        create_functions
      end

      alias_method_chain :migrate, :functions
    end
  end
  
  class << Schema
    def define_with_procedures(info={}, &block)
      define_without_procedures(info, &block)
      ActiveRecord::Migration::create_procedures
    end
    
    alias_method_chain :define, :procedures

    def define_with_functions(info={}, &block)
      define_without_functions(info, &block)
      ActiveRecord::Migration::create_functions
    end
    
    alias_method_chain :define, :functions
  end
  
end

Please note this code is based off some code by Rick Olsen.