STORED_PROCEDURE = 'find_the_id'
class AddStoredProcedure < ActiveRecord::Migration
  def self.up

    sql_statement <<-STORED_PROC
        DELIMITER $$
        CREATE PROCEDURE find_the_id(IN the_id INT)
        BEGIN
          SELECT name FROM items WHERE id = the_id;
        END $$
        DELIMITER ;
    STORED PROC
    conf = Rails::Configuration.new.database_configuration[RAILS_ENV]
    host = conf['host'] ? conf['host'] : 'localhost'
    database = conf['database']
    username = conf['username'] ? conf['username'] : 'root'
    password = conf['password'] ? conf['password'] : ''
    cmd_line = 'mysql5 -h ' + host + ' -D ' + database + ' -u ' + username
    cmd_line += ' -p ' + password if password.nil?
    cmd_line += ' < ' + sql_file

    unless system(cmd_line)
      raise Exception, 'Error creating stored procedure ' + STORED_PROCEDURE
    end
  end

  def self.down
    execute 'DROP PROCEDURE ' + STORED_PROCEDURE
  end
end