Do I use mocking (vs fixtures) for methods that produce results based on non-trivial SQL queries

Testing RSpec
1

Hi,

In cases where I have model methods that are generating data based on
non-trivial SQL queries to a database (e.g. to pull together data
required to plot an appropriate graph), does this really require a
traditional fixture (i.e. pre-canned test data) as opposed to use of
mocks?

That is, the bulk of the method is really the SQL that is pull back
(with multiple joins / selects etc) the data required. Testing to
some extent should be focused around testing the SQL is producing what
it should. How would one use rspec / mocha to solve this? i.e. in a
normal testing sense you may need 5 to 10 rows before you can feel
comfortable the SQL code is doing what it should…

Tks

2

On Fri, Sep 5, 2008 at 7:08 AM, Greg H. <
[email protected]> wrote:

Hi,

In cases where I have model methods that are generating data based on
non-trivial SQL queries to a database (e.g. to pull together data
required to plot an appropriate graph), does this really require a
traditional fixture (i.e. pre-canned test data) as opposed to use of
mocks?

I don’t see how you could use mocks to test SQL. You need rows in the
database to do this. I personally do not use fixtures because my model
specs
usually don’t use a “typical” data set. I’ll create my own data set for
each
type of spec, often using an object mother to create the valid network
of
objects.

///ark

3

On Fri, Sep 5, 2008 at 7:23 AM, Mark W. [email protected] wrote:

I don’t see how you could use mocks to test SQL. You need rows in the
database to do this. I personally do not use fixtures because my model specs
usually don’t use a “typical” data set. I’ll create my own data set for each
type of spec, often using an object mother to create the valid network of
objects.

+1

4

On Sep 5, 2008, at 10:08 AM, Greg H. wrote:

Hi,

In cases where I have model methods that are generating data based on
non-trivial SQL queries to a database (e.g. to pull together data
required to plot an appropriate graph), does this really require a
traditional fixture (i.e. pre-canned test data) as opposed to use of
mocks?
…
it should. How would one use rspec / mocha to solve this? i.e. in a

You tell me. Actually, there is no way, because stubs operate at a
higher level on the call stack. Until rails treats SQL as an object
(i.e., until there is a ruby SQL parser out there and rails uses it),
stubbing will never be able to test real database activity. This is
why rspec still recommends using real database rows in your tests -
not because it’s an apriori requirement for testing against SQL data,
but because it is currently the only way.

Of course, the reason why people don’t like this answer is not really
for the reason people like mocks otherwise (functional isolation).
Instead, its because a large test suite hitting the database will
become very slow over time. Just do the math: If one database test
takes 0.2 sec (a realistic figure), then 5 will take 1 sec, at 1000
over 3 minutes, and at 2000 over 6 minutes. This is starting to get
into “time for a coffee/cigarette/juggling what have you” breaks. So

  • either you end up very hyped up on coffee not doing much work, or
    you don’t run your test suite very often, which leads to legitimate
    bugs which can be solved. (At one point I had actually deployed code
    into production which had a serious bug. This all could have been
    avoided because the test suite was failing, but I had gotten into
    the habit of not running it because it was too expensive).

I’ve been working hard on solving this issue by building a SQL parser
in treetop, and hope anyone who is interested will join me (just let
me know if you are interested and I’ll make the project publicly
viewable on github).

Scott

5

time. Just do the math: If one database test takes 0.2 sec (a realistic
figure), then 5 will take 1 sec, at 1000 over 3 minutes, and at 2000 over 6
minutes. This is starting to get into “time for a coffee/cigarette/juggling
what have you” breaks. So - either you end up very hyped up on coffee not
doing much work, or you don’t run your test suite very often, which leads to
legitimate bugs which can be solved. (At one point I had actually deployed
code into production which had a serious bug. This all could have been
avoided because the test suite was failing, but I had gotten into the
habit of not running it because it was too expensive).

There’s a couple things you can do to help with this:

  • Split slow tests into another dir and run them separately from your
    fast suite. Keep rspactor on your fast suite and run the slow tests
    before checkin

  • Use a CI server

I’ve been working hard on solving this issue by building a SQL parser in
treetop, and hope anyone who is interested will join me (just let me know if
you are interested and I’ll make the project publicly viewable on github).

GitHub - nkallen/arel: A Relational Algebra might also be interesting to
you.

Pat

6

On Sep 5, 2008, at 10:55 AM, Pat M. wrote:

legitimate bugs which can be solved. (At one point I had actually
fast suite. Keep rspactor on your fast suite and run the slow tests
before checkin

  • Use a CI server

Yes, most certainly. But the db tests •still* aren’t cheap, meaning
you’ll need to wait for the CI server to run them there is a failure,
or wait before checking in.

Also - have you found a CI server that is working with git?

I’ve been working hard on solving this issue by building a SQL
parser in
treetop, and hope anyone who is interested will join me (just let
me know if
you are interested and I’ll make the project publicly viewable on
github).

GitHub - nkallen/arel: A Relational Algebra might also be interesting
to you.

Hmm…Well, that’s interesting, although I think that project, like so
many others, is generating SQL, not parsing it, correct?

I’ve made my sql parser public, and here is is if anyone wants to take
a gander (although it most certainly is still a work in progress):

Scott

7

On Sep 5, 2008, at 11:40 AM, Ben M. wrote:

coffee/cigarette/juggling
habit of not running it because it was too expensive).

I’ve been working hard on solving this issue by building a SQL
would be great. I have been using the nulldb adapter will a lot of
I’ve made my sql parser public, and here is is if anyone wants to
rspec-users mailing list
the calls to that object#method in my specs where the interesting
business logic uses that data to produce results. I then have an
interaction test in form of a story that does more complex setup and
testing of both methods and the DB in unison. My main motivation for
this separation is for speed of my specs that are for my business
logic. If what I have said doesn’t make sense I can given an
example of
what I mean.

Doesn’t this violate the rule of “never stub the method under test?”

Scott

8

Scott T. wrote:

coffee not

  • Split slow tests into another dir and run them separately from your
    Also - have you found a CI server that is working with git?
    On github you can find forks of cc.rb with added git support. I have
    been using it like a charm for months.

I’ve been working hard on solving this issue by building a SQL
parser in
treetop, and hope anyone who is interested will join me (just let me
know if
you are interested and I’ll make the project publicly viewable on
github).

Thats cool. I was actually talking to our DBA about the possibility of
this the other day. I think for the simple cases something like this
would be great. I have been using the nulldb adapter will a lot of
success recently but there are a few spots where just simple insert and
select statements would go a long ways.

GitHub - nkallen/arel: A Relational Algebra might also be interesting
to you.

Hmm…Well, that’s interesting, although I think that project, like so
many others, is generating SQL, not parsing it, correct?

I’ve made my sql parser public, and here is is if anyone wants to take
a gander (although it most certainly is still a work in progress):

GitHub - smtlaissezfaire/guillotine: A Ruby SQL Parser

Awesome. I’ll for sure take a look at this.

Scott

rspec-users mailing list
[email protected]
http://rubyforge.org/mailman/listinfo/rspec-users

To answer the original poster’s question… I tend to isolate my SQL
calls to single methods and don’t allow those methods to operate on the
results. That way I can have a faster functional spec test that method
individually with a smaller data set in the DB. I will then stub out
the calls to that object#method in my specs where the interesting
business logic uses that data to produce results. I then have an
interaction test in form of a story that does more complex setup and
testing of both methods and the DB in unison. My main motivation for
this separation is for speed of my specs that are for my business
logic. If what I have said doesn’t make sense I can given an example of
what I mean.

-Ben

9

Scott T. wrote:

over 6
avoided because the test suite was failing, but I had gotten

success recently but there are a few spots where just simple insert and

logic. If what I have said doesn’t make sense I can given an example of
what I mean.

Doesn’t this violate the rule of “never stub the method under test?”

Scott

I probably didn’t explain that very well. Simple example:

class WidgetRepository
def self.some_method_that_isssues_complex_sql_to_find_the_data(*args)
…
end
end

describe WidgetRepository,
“.some_method_that_isssues_complex_sql_to_find_the_data” do
#allow this one to hit the db

it “should blah, blah…” do
widget = create_widget
…(more setup)…

WidgetRepository.some_method_that_isssues_complex_sql_to_find_the_data(*args).should
== expected_results
end

…

end

Then we have an object that uses the data:

class WidgetYieldCalculator

def calculate(widget, other_stuff)
data =
WidgetRepository.some_method_that_isssues_complex_sql_to_find_the_data(some_args)
…operate on data…
return result
end
end

Now to spec it, since the repository SQL call is all speced out, we can
simply stub the call to WidgetRepository.some_method… and rely on the
fact that the Respository will do it’s job:

describe WidgetYieldCalculator, “#calculate” do
it “should calculate the correct yield” do
# given
data = […]
widget = …

WidgetRepository.stub!(:some_method_that_isssues_complex_sql_to_find_the_data).and_return(data)

           #depending on the situation and spec an expectation is

better than a stub
# when
result = WidgetYieldCalculator.new.calculate(widget, options)
# then
result.should == expected_results
end

end

Sorry I don’t have more time to do a better example or explain it
better. Basically, I was just advocating a separation of
responsibilities: the data retrieval and operation on the data. This
then allows you to mock your interface of the repository at a higher
level. As I said, with the ActiveRecord pattern this kind of thinking
seems a bit odd. In my rails projects the “repository” calls are
usually class methods on an AR class. The big gain for this is that you
can then test all your business logic in the WidgetYieldCalculator by
varying the data which is stubbed. This reduces the number of DB calls
considerably. You will need an integration spec for both of these parts
to work together, and lately I have been starting with a story to drive
the entire process. I wouldn’t got to such lengths on simple cases, but
the current project I’m working on has driven me to do this in a number
of places where lots of data and objects are involved.

To answer your question more directly, I never stub a method of an
object that I am testing… I just stub the collaborator’s.

Hope that clears it up a little,
Ben

10

Ben - I like your suggestion, thanks. I’ll start trying to structure
some of my tests around this concept until there’s a better way.

Mark - I’m curious to understand what you actually do re “I personally
do not use fixtures because my model specs usually don’t use a
“typical” data set. I’ll create my own data set for each type of spec,
often using an object mother to create the valid network of objects.”
Did you just mean that at the start of a test you create test data
in the database programmatically, as opposed to hard wiring it in a
fixture?

11

On Fri, Sep 5, 2008 at 12:39 PM, Greg H. <
[email protected]> wrote:

Did you just mean that at the start of a test you create test data
in the database programmatically, as opposed to hard wiring it in a
fixture?

Yes, that’s right. Our code has a lot of support for creating networks
of
objects (e.g., you want to test a video, which requires some assets, a
company, a site, maybe a reseller). Or you just mock out the other
objects.
The point is that you can get just the object(s) you want without the
constraints of “one size fits all” data.

In fact, fixtures can get in the way, which is why you’ll see
Video.delete_all in a lot of our before(:each)'s (often accompanied by
acerbic comments about “fixture bullshit.”) As I’ve mentioned before,
It’s
been really great working with code from an RSpec master. The dividing
line
between his code and that of the other developers is fairly clear-cut.
:slight_smile:

///ark

12

On Sep 5, 2008, at 3:39 PM, Greg H. wrote:

Ben - I like your suggestion, thanks. I’ll start trying to structure
some of my tests around this concept until there’s a better way.

Mark - I’m curious to understand what you actually do re “I personally
do not use fixtures because my model specs usually don’t use a
“typical” data set. I’ll create my own data set for each type of spec,
often using an object mother to create the valid network of objects.”
Did you just mean that at the start of a test you create test data
in the database programmatically, as opposed to hard wiring it in a
fixture?

You might want to check out Dan M.’ post from a while back:

http://www.dcmanges.com/blog/38

There are a ton of plugins out there that implement the idea. I wrote
FixtureReplacement (http://replacefixtures.rubyforge.org/) after this
idea, but there are actually a ton of them out there that do more or
less the same thing - ObjectDaddy, factory_girl).

Scott

13

In fact, fixtures can get in the way, which is why you’ll see
Video.delete_all in a lot of our before(:each)'s (often accompanied by
acerbic comments about “fixture bullshit.”)

Someone on your team has quite the potty mouth! :open_mouth:

:wink:

But seriously. Rails-style fixtures suck. You should be creating as
much of the test data inline as possible (IMO), using helpers to build
it for you.

Pat

14

On 5 Sep 2008, at 16:40, Ben M. wrote:

Also - have you found a CI server that is working with git?

On github you can find forks of cc.rb with added git support. I have
been using it like a charm for months.

In fact, the main thoughtworks github ccrb repo works with git now -
we switched last week from the benburket fork and it seems fine.

cheers,
Matt

http://blog.mattwynne.net

In case you wondered: The opinions expressed in this email are my own
and do not necessarily reflect the views of any former, current or
future employers of mine.