Migrating Stored Procedures in RoR

This similar article may be found on the internet, but i guess this article will be helpful for newbie's.

Migrations are a convenient way to alter your database in a structured and organised manner.

Here we are going to see how to create stored procedures with rake db:migrate

First create a new migration file in your db/migrate folder using

ruby script/generate migration stored_procedures. This will create the file db/migrate/001_stored_procedures.

Edit the code to tell it what to do.

The method self.up is used when migrating to a new version, self.down is used to roll back any changes if needed. The class name needs to be the same as the migration name (i.e. db/migrate/001_stored_procedures needs a class name of @StoredProcedures@).

Let us migrate a stored procedure called items with a basic sql code :

The migration file now looks like this :

class StoredProcedures < ActiveRecord::Migration
 def self.up
   execute <<-__EOI
     CREATE DEFINER=`root`@`localhost` PROCEDURE `items`(IN l_item INT,IN userid INT,OUT l_itemid INT,OUT l_item_name VARCHAR)
     BEGIN SELECT itemid,item_name,held_by,id INTO l_itemid,userid,id FROM users_items WHERE itemid=l_item AND held_by=userid;
     END
   __EOI
 end
 def self.down
   execute "DROP PROCEDURE IF EXISTS `items`"
 end
end

And now the cool part is just run rake db:migrate, Rails will create all the migration files in your database and also creates Stored procedures.