Friday, 18 June 2010

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) 
        DECLARE dist FLOAT;
        SET dist = Sqrt(Pow((lat2 - lat1),2) + Pow((lng2 - lng1),2));
        RETURN dist;

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

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

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.


Jason Weathered said...

Having hit this issue fairly early on when I started to play with Rails, I don't use `schema.rb`. I always have `config.active_record.schema_format = :sql` in my environment.rb and things seem to pretty much Just Work™ in late Rails.

By using a real schema dump, all my constraints, foreign keys, functions, triggers, etc are all available in my test environment. I've found this to be quite a reliable way to ensure the test environment gets the database you expect.

As for creating functions and what not, I've found an `execute` heredoc in a migration is the easiest way. If I need to change it in the future, I create another migration which does a DROP/CREATE or REPLACE as applicable. My down method does the same in reverse, restoring the old function.

Taryn said...

Awesome comment. Thanks Jason.

I'll give the schema = :sql a go (I hadn't heard of that one).

Cody Russell said...

Just a minor correction: up and down are instance methods, not class methods, so you need to instantiate AddStoredProcedures before you can call up/down.

migration =

Taryn said...

Thanks Cody! yes, of course you're quite right. I'll fix that up.

BTW - I notice you posted the comment a couple of times. blogger has comment-moderation that... could be improved.

It doesn't notify people that a comment has been recorded - even though it has... I presume in an attempt to not give away any info to potential spammers (and I get plenty unfortunately).

Your comments definitely came through ok - blogger just didn't say so.

Taryn said...

Hmmm - I just checked. migrations in my project have: self,up / self.down - so that makes the class-methods. Is this a Rails 2 vs 3 thing?