BDD with Stored Procedures

In the ruby world, stored procedures have a bad name, DHH mocks them, Rails has little or no support for them, they are just not the Rails / Agile ‘Way’ of doing things.

Sometimes though, you just can’t get away from them, be it through some legacy system that uses them for reports, or, god forbid, the developers thought it would be a good idea to write parts of the application in compiled SQL. Perhaps the idea of compiling inaccessible, unsearchable, non modular, untestable, procedural bits of almost Fortran like application logic into some system outside your application codebase for admittedly faster processing, just gets you going. If any of the above fits your description, read on.

Options

I spend a substantial amount of time working with stored procedures. Yes, in the context of a large polyglot application that has both ruby and php ... AND stored procedures calling the shots, it can get painful. However, it’s a myth to say you can’t BDD stored procs. It takes a bit of extra preparation, and there are limitations, but it's not a lost cause.

Introducing … PgTap

… aaaand

Introducing Rspec!

You can easily execute a compiled stored procedure from within Rspec by just using

  ActiveRecord::Base.connection.execute(“select * from hq.activate_defence_matrix()”)

Factory Data

Often a stored procedure is used to generate some table or return some result based on existing data in your database, if you are making a modification to a potentially complex stored proc, this can get interesting, in order to get the stored proc to run, you will need to generate the correct data signature. If you’re building the proc from scratch, you already know which data you will need to generate so this part will be easy.

For the given starting spec...

require 'spec_helper'

describe 'the user report stored procedure' do
  before(:all) do
    @defence_matrix = File.read('db/functions/activate_defence_matrix.sql')
    @res = ActiveRecord::Base.connection.execute @defence_matrix
  end

  it 'should rebuild the function' do
    @res.should be_a PG::Result
  end

  it 'should exist' do
    stored_proc_exists?('activate_defence_matrix').should == true
  end


  it 'should contain the stored proc' do
    stored_proc_src("activate_defence_matrix").should == %q||
  end
end

def arex sql
  ActiveRecord::Base.connection.execute sql
end

def stored_proc_exists? name
  res = arex("SELECT proname FROM pg_catalog.pg_proc WHERE proname = '#{name}'")
  res = res.first
  if res
    return res['proname'] == name
  else
    false
  end
end


def stored_proc_src name
  res = arex("select prosrc from pg_catalog.pg_proc where proname = '#{name}';")
  res = res.first
  if res
    return res['prosrc']
  else
    false
  end

end

For the given starting proc...

  SET search_path = hq, pg_catalog;
  --
  -- Name: activate_defence_matrix(); Type: FUNCTION; Schema: hq; Owner: steve
  --

  CREATE OR REPLACE FUNCTION hq.activate_defence_matrix() RETURNS void
  LANGUAGE plpgsql SECURITY DEFINER
  AS $$
    DECLARE
    BEGIN
    END
  $$;

Now, if you run the tests in the example app at commit acba7a50d95 You can see how we can start to iterate. The schema name for the proc you will need to add via a migration, so do this next

class CreateHqSchema < ActiveRecord::Migration
  def up
    execute 'CREATE SCHEMA hq'
  end

  def down
    execute 'DROP SCHEMA hq'
  end
end

So now we can add some logic to our stored proc and iterate on it until we have a working solution, say we add..

  --
  -- Name: activate_defence_matrix(); Type: FUNCTION; Schema: hq; Owner: steve
  --

  CREATE OR REPLACE FUNCTION hq.activate_defence_matrix() RETURNS void
  LANGUAGE plpgsql SECURITY DEFINER
  AS $$
    DECLARE
      pilot_ids integer[];
    BEGIN
      SELECT id INTO pilot_ids FROM units WHERE type = 'ion_cannon_pilot';
    END
  $$;

And an equivalent spec ...

  describe "execution" do
    before do
      @activate_defence_matrix = 'select hq.activate_defence_matrix()'
    end
    it 'should assign all the pilots to ion cannons' do
      @activate = ActiveRecord::Base.connection.execute @activate_defence_matrix
    end
  end

See 1433d0ebae7a6ea934818 for specifics.

We should see an exception bubbling up when we run our tests, this is what we want, immediate feedback!

...F

Failures:

  1) the activate defence matrix stored proc execution should assign all the pilots to ion cannons
     Failure/Error: @activate = ActiveRecord::Base.connection.execute @activate_defence_matrix
     ActiveRecord::StatementInvalid:
       PG::UndefinedTable: ERROR:  relation "units" does not exist
       LINE 1: SELECT id                FROM units WHERE type = 'cannon_pil...
                                             ^
       QUERY:  SELECT id                FROM units WHERE type = 'cannon_pilot'
       CONTEXT:  PL/pgSQL function activate_defence_matrix() line 5 at SQL statement
       : select hq.activate_defence_matrix()
     # ./spec/stored_procs/activate_defence_matrix_spec.rb:31:in `block (3 levels) in top (required)'

Finished in 0.05548 seconds
4 examples, 1 failure

Failed examples:

rspec ./spec/stored_procs/activate_defence_matrix_spec.rb:33 # the activate defence matrix stored proc execution sshould assign all the pilots to ion cannons

When is this useful?

As services scale, the time it takes to execute a stored procedure can increase exponentially as the amount of data in your system increases, if you have a proc that takes several hours to run in production, then making a change and testing it can be a lengthy process, conversely, to deploy a change without testing it could prove fatal. A better solution is to write tests that use a very small subset of data, or several permutations of the data to test different behaviour.