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.
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.
… aaaand
You can easily execute a compiled stored procedure from within Rspec by just using
ActiveRecord::Base.connection.execute(“select * from hq.activate_defence_matrix()”)
"But stored procedures depend on lots of tables and associations" - I hear you cry....
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.
The example app
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
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
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.