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.

rails gotchas: nested transactions

So I've been adding some new plugins for testing that I've never tried before, in this case: shoulda and stump, and found that stump just isn't playing nice with SQLite :P

The problem only occurred when I tried to declare a proxy! function on an existing model. ok, I didn't get around to trying everything, so maybe it occurs on other things too, but it seemed to be happy with stub and mock.

The code would simply fail with the following error:

SQLite3::SQLException: cannot start a transaction within a transaction
<insert useless backtrace here>

It's been driving me crazy for the last few days trying all sorts of ways to get it to work. I almost abandoned transactional fixtures entirely before I finally found this snippet at the very *bottom* of the ActiveRecord::Transactions page in APIdock

"Most databases don’t support true nested transactions. At the time of writing, the only database that we’re aware of that supports true nested transactions, is MS-SQL."

So the problem is not transactions... but SQLite's lack of support of proper nested transactions.

The solution: Install MySQL.

It's annoying to have to go back to creating a MySQL db/user and granting rights before I can run my tests - but if it makes the test code work, then it's worth that extra step.

SQLite is nice, but it's ability to blow away the db by just doing a quick rm isn't worth not being able to properly test my code. Besides - the real db is on MySQL anyway, so I figure I might as well.

MySQL hates Timezones

We've started using the TzTime plugin which leverages the Tzinfo gem. It's pretty good. I've adjusted it slightly for my own preferences, but it works pretty well out of the box.

Unfortunately, we ran into some odd issues. Rails suddenly started to find records that it shouldn't. Eg if we were looking for all Event objects that occurred between two given dates, (and expecting to find none) suddenly we were finding one. Which played havoc with our tests :P

The occurred_at date on the Event object was clearly outside of the date-range we were looking at. We even tested to make sure the timezone wasn't stuffing it around and moving it into the date-window.

Eventually we discovered that the to_s(:db) command was printing the date out in a weird format using a T: '2008-07-10T00:50:23+00:00'. This is quite different from the standard MySQL format, which would have it as just: '2008-07-10 00:50:23'

MySQL needs to be set up properly to accept the "T-format" for DateTime objects and ours hasn't been. However, in our case it's much easier to simply extend Rails than to go through the required infrastructure politics to get global database settings changed (which will affect all the other projects in the business).

So I just updated the way that the to_s(:db) format works to always use the 'Y-M-D H:M:S' format as below.

ActiveSupport::CoreExtensions::Time::Conversions::DATE_FORMATS.merge!(:db => '%Y-%m-%d %H:%M:%S')
ActiveSupport::CoreExtensions::Date::Conversions::DATE_FORMATS.merge!(:db => '%Y-%m-%d %H:%M:%S')

This seems to work just fine. It means that we'll need to update this if we ever change away from MySQL - but MySQL seems to be hard-coded into this organisation and seems pretty unlikely.

Note: when I first put this code into config/environment.rb it worked fine until I ran the tests - at which point I immediately got an error: uninitialized constant ActiveSupport. This went away the moment I moved the code below the Rails.initializer code (ie underneath the line that says: Include your application configuration below).