Stored Procedure-fu

So, I've been tuning my db performance on our heaviest database call... and this involved adding a quickie stored procedure to make life a little simpler.

It's nothing complex, just does a really simple distance calculation (using pythagorus)... which we needed because most versions of MySql don't come fully-loaded with all those nice functions that you can find in the manual.

Now the problem with stored procedures, is how to add them to the db without too much hassle. I have heard there are gems out there - but they seem a little like overkill, and the ones I've seen have been "build it on the fly"... I just want to put it into the db and leave it there.

So what's the quickest and easiest way?

A migration:

class AddStoredProcedures < ActiveRecord::Migration
  def self.up
    # function distance(lat1,lng1,lat2,lng2)
    #   sqrt((lat2 - lat1)^2 + (lng2 - lng1)^2)
    # end
    execute <<EOS 
      CREATE FUNCTION Distance(lat1 FLOAT, lng1 FLOAT, lat2 FLOAT, lng2 FLOAT) 
      RETURNS FLOAT
      DETERMINISTIC
      BEGIN
        DECLARE dist FLOAT;
        SET dist = Sqrt(Pow((lat2 - lat1),2) + Pow((lng2 - lng1),2));
        RETURN dist;
      END
EOS
  end

  def self.down
    execute "DROP FUNCTION IF EXISTS `Distance`" 
  end
end

Down sides?

I'm told that using migrations for stored procedures has one major drawback - that if you want to change the procedure later then it's annoying to maintain - and can be the wrong version etc... In this case that's not a problem. Pythagorus isn't likely the change any time soon, so I'm unlikely to need to update it.

But there is one annoyance about putting it as a migration, and that's the fact that it doesn't show up as part of the schema... which means our Test environment can't find it. :P

This is a bit annoying, and I haven't found an elegant solution to the problem, but to get the test suite running, I have found a hack...

Re-run the migration:

class ActiveSupport::TestCase
  # ... lots of guff

  # Run stored procedures to add to the test db
  require File.expand_path(File.dirname(__FILE__) + "/../db/migrate/20100528190204_add_stored_procedures.rb")
  AddStoredProcedures.down # clear it out first, to be sure
  AddStoredProcedures.up
end

It's ugly, but it works. You may note I have to run the "down" then the "up" - this is because ActiveSupport ::TestCase actually gets loaded for each type of test-case you are running (eg once for Unit tests and again for Functional etc) and the "up" barfs if you try to create a procedure that has already been created in the db.

Otherwise, it runs and lets you get on with your work...

if anybody has a more elegant, but unheavy solution to the whole issue of Stored Procedures in test dbs... please do let me know.