Database clearing

I’m seeing something strange and was just wondering if someone can
confirm
my assumptions for me:

I have user model with a number of specs: some of them use fixtures and
some of them don’t. Today, while talking someone through some specs
that
needed developming, I noticed that the fixtures always seemed to be
loading. When I investigated, I discovered that it was not the fixtures
always loading, but the db not clearing between runs. So, given a very
simple spec (with the standard fixtures):

-- coding: mule-utf-8 --

require File.dirname(FILE) + ‘/…/spec_helper’

describe User do
fixtures :users

it “should have some users” do
User.all.should_not be_blank
end
end

describe User, “description” do
it “should not show emails” do
User.all.should be_blank
# Or, to be a bit more concise:
User.all.select{|u| u.email == ‘[email protected]’}.should be_blank
end
end

The second describe is failing because the db is populated with the
users
from fixtures (still). I am correct in assuming, with transactions
fixtures
switched to true (as it is), this should not be the case, right?

Reenforcing my feeling that this is wrong is the fact that if I include
a
before(:each) do block in the first set of statements that creates a
non-fixture user, this user does get removed from the db before the
second
describe runs:

describe User do
fixtures :users

before(:each) do
user = User.new(:email => ‘[email protected]’)
user.save(false)
end

it “should have some users” do
User.all.should_not be_blank
end
end

This passes

describe User, “description” do
it “should description” do
User.all.select{|u| u.email == ‘[email protected]’}.should be_blank
end
end

On Fri, Sep 19, 2008 at 8:22 AM, Todd T. [email protected] wrote:

-- coding: mule-utf-8 --

describe User, “description” do
switched to true (as it is), this should not be the case, right?
user = User.new(:email => ‘[email protected]’)
it “should description” do
User.all.select{|u| u.email == ‘[email protected]’}.should be_blank
end
end

Does your spec_helper file have this:

Spec::Runner.configure do |config|
config.use_transactional_fixtures = true
end

Also, what versions of rspec and rails are you using?

Sorry, hit the enter key prematurely, That ‘bit more concise’ bit of the
spec should read:
describe User, “description” do
it “should not show emails” do
User.all.should be_blank
# Or, to be a bit more concise:
User.all.select{|u| u.email == ‘[email protected]
[email protected]’}.should
be_blank
end
end

On Fri, Sep 19, 2008 at 2:22 PM, Todd T. [email protected] wrote:

-- coding: mule-utf-8 --

describe User, “description” do
switched to true (as it is), this should not be the case, right?
user = User.new(:email => ‘[email protected]’)
it “should description” do
User.all.select{|u| u.email == ‘[email protected]’}.should be_blank
end
end


Todd T.
Managing Director
Social Network Applications, Ltd.
Unit 72
49 Effra Road
London
SW2 1BZ
t 0207 733 6610
m 07861 220 182

Does your spec_helper file have this:

Spec::Runner.configure do |config|
config.use_transactional_fixtures = true
end

Yes.

Also, what versions of rspec and rails are you using?

1.1.4 and 2.1.0

Cheers,
Todd

On Fri, Sep 19, 2008 at 6:22 AM, Todd T. [email protected] wrote:

I have user model with a number of specs: some of them use fixtures and
some of them don’t. Today, while talking someone through some specs that
needed developming, I noticed that the fixtures always seemed to be
loading. When I investigated, I discovered that it was not the fixtures
always loading, but the db not clearing between runs.

I don’t know the exact mechanism, but if we want a table to be emply
that
might have had fixtures loaded into it at some point, we delete_all in
before(). It seems to me that if you have fixtures, they would be loaded
before every test, but you say that you’re not seeing that.

///ark

On Fri, Sep 19, 2008 at 3:42 PM, Mark W. [email protected] wrote:

I don’t know the exact mechanism, but if we want a table to be emply that
might have had fixtures loaded into it at some point, we delete_all in
before(). It seems to me that if you have fixtures, they would be loaded
before every test, but you say that you’re not seeing that.

Thanks Mark, but it won’t work. The question was more a sanity check
when I
first asked it (‘have I just not noticed this happening before?’): I
have a
lot of existing specs that rely on the user table–and many of them that
make changes to attributes. I need to sort out what’s going on: calling
delete_all would be just about acceptable if I didn’t have go back
through
all 800+ specs and try and plumb it in where it was needed.

I’ve tried paring the setup down one spec and the helper to the bare
minimum
(bascially what I sent earlier and a spec helper config that only sets
transactional_fixtures = true) with no luck. I’m guessing it is
probably
some problem with something I’ve installed or updated recently
(has_many_polymorphs?) I see several other people have had similar
issues,
but there doesn’t seem to be one clear cause. Any thoughts on a good
place
to start looking (can clearly see the rollback calls in the test log,
but
they don’t seem to work right) would be greatly appreciated.

Cheers,
Todd

On Fri, Sep 19, 2008 at 5:34 PM, Pat M. [email protected] wrote:

example, you just rollback the transaction so the fixtures are clean
I’ve long been using #3. There are a couple reasons why, in addition to
guys I work with truncates all the tables in the db before every
example. He says this runs as fast or faster than transactional
fixtures, and has the added benefit of NOT being in a transaction, which
means that if he actually DOES use a transaction in his application code
then he can test it very easily. I’ve not tried it, but it seems like a
cool idea.

Pat, fantastic! Thanks for that! Never having had problems with
fixtures
before (HA! Who am I kidding? Never having had THIS problem before) I
was
at a loss. Your colleague’s truncate trick sounds like a fantastic
idea.
I’ll give it a go straight away.

Cheers,
Todd

cool idea.
Pat -

Seems like this would be a good plugin. Does it have a name?

Scott

On 20 Sep 2008, at 01:59, Scott T. wrote:

cool idea.
We have a few lines of code in our features steps/env.rb that does this

  1. patch AR to stash each AR object created in an array
  2. in an After do block, call destroy on each of those objects.

We can probably share if anyone would like to see this.

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.

“Todd T.” [email protected] writes:

-- coding: mule-utf-8 --

describe User, “description” do
it “should not show emails” do
User.all.should be_blank
# Or, to be a bit more concise:
User.all.select{|u| u.email == ‘[email protected]’}.should be_blank
end
end

The second describe is failing because the db is populated with the users from
fixtures (still). I am correct in assuming, with transactions fixtures
switched to true (as it is), this should not be the case, right?

No, that is not correct. Here’s how transactional fixtures work:

  1. Before all specs run, load the fixtures into the database
  2. Start a transaction. Run the first spec
  3. Rollback transaction
  4. Start another transaction. Run the second spec
  5. Rollback transaction

And that’s it. The transaction part is basically a hack to repopulate
the db more quickly…instead of doing a bunch of inserts before each
example, you just rollback the transaction so the fixtures are clean
again. This has the effect of bleeding DB data into other example
groups if you’re not careful (Mark: does that help to explain the
acerbic comments?).

To my knowledge, there are three main choices:

  1. Delete stuff before example
  2. Load the fixtures for whatever model you’re specifying, and just deal
    with the fact that there’s stuff in there
  3. Don’t use Rails db fixtures

I’ve long been using #3. There are a couple reasons why, in addition to
the problem you’re experiencing. The first is that I like to do as much
of the setup close to the exaple as possible. Creating a user in my
‘before’ means I don’t have to look very far to see/understand/modify
the data being used in a particular example. And the second reason is
that when you use db fixtures, every example that uses them becomes
coupled. Things will be going fine for a while, and then you have to
change the fixture to support one thing, and then a bunch of other
example start failing as a result.

There’s another approach though that I haven’t tried yet. One of the
guys I work with truncates all the tables in the db before every
example. He says this runs as fast or faster than transactional
fixtures, and has the added benefit of NOT being in a transaction, which
means that if he actually DOES use a transaction in his application code
then he can test it very easily. I’ve not tried it, but it seems like a
cool idea.

Pat

On Sep 20, 2008, at 6:31 AM, Matt W. wrote:

application code
We can probably share if anyone would like to see this.

Seems like it would be more efficient to make sql calls to find the
tables, and then truncate each table.

Scott

Ok, here’s what I’ve come up with on the spur of the moment (goes in
spec_helper.rb):

config.after(:each) do
result = ActiveRecord::Base.connection.execute(‘SHOW TABLES;’)
while table = result.fetch_row
# Or whatever you think is appropriate.
next if table.index(‘schema_migrations’) or table.index(‘roles’)
ActiveRecord::Base.connection.execute(“TRUNCATE #{table}”)
end
end

The main problem is that it’s slow. I tried batching them all up, and
running them in a single execute, but foundered on the mysql_options
multi-statements setting (it seems that mysql won’t, by default, allow
multi-statement strings unless you set the multi-statement flag after
init
and before connect. Not sure how to do this from Base.connect, or even
if
it’s possible. Sorry, lost track of the article where I found the
original
reference).

Best,
Todd

We have a few lines of code in our features steps/env.rb that does this

  1. patch AR to stash each AR object created in an array
  2. in an After do block, call destroy on each of those objects.

We can probably share if anyone would like to see this.

Please!

Best,
Todd

On 20 Sep 2008, at 11:33, Todd T. wrote:

Please!
undefined · GitHub

enjoy.

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.

“Todd T.” [email protected] writes:

end
A couple notes on this… first, you want to put this in a before(:each)
rather than after. That guarantees that you start with a clean
database, which is what you want.

Secondly, you’ll want to truncate them all at the same time (if mysql
supports that?) The reason is that if you have foreign key constraints
anywhere, you’re not relying upon the ordering in which you delete
them. You may not have constraints, but I imagine that doing it all in
one go is better anyway.

result = ActiveRecord::Base.connection.execute(‘SHOW TABLES;’)
tables_to_truncate = []
while table = result.fetch_row
tables_to_truncate << table unless [‘schema_migrations’,
‘roles’].include?(table.index)
end
ActiveRecord::Base.connection.execute(“TRUNCATE
#{tables_to_truncate.join(', ')}”)
end

(there’s prob a more rubyish-way using collect and reject, but I don’t
know the API for the result object offhand.

Another thing that I think Steve did is only truncate the tables that
were modified in the previous example…he did this by hooking into the
SQL queries and examining them for INSERT/UPDATE/DELETE and figuring out
what tables were used in it. Maybe that’ll help speed it up. But I bet
just doing them in one query ought to be helpful as well.

Pat

Just to be clear, these methods of truncating tables or remembering rows
to
delete are only useful in the presence of fixtures? Otherwise, the
standard
transactional support is sufficient and (probably) faster, right? (if
for no
other reason because it’s all handled deep in the bowels of the db).

///ark

“Mark W.” [email protected] writes:

Just to be clear, these methods of truncating tables or remembering rows to
delete are only useful in the presence of fixtures? Otherwise, the standard
transactional support is sufficient and (probably) faster, right? (if for no
other reason because it’s all handled deep in the bowels of the db).

Truncating tables would be good if you’re using fixtures only some of
the time. For example, you’ve got two example groups, one using
fixtures and the other one not. In the one that uses fixtures, you
shouldn’t truncate the tables (or at least not the tables for the
fixtures you’re loading - Rails will handle that automatically), but in
the example group that doesn’t use fixtures, you do want to clear the
table so that you have a known state.

Another case where truncating tables would be beneficial is when your
application code makes use of an explicit transaction.
Postgres/MySQL/sqlite don’t support nested transactions, so you wouldn’t
be able to spec your code as you want (Oracle does have nested
transactions though).

As far as transactional fixtures being faster than truncating…I would
have thought so, but evidently my colleague said he found his specs ran
FASTER with truncated tables. The flip side is, I heard that they tried
turning that off and enabling transactional fixtures, and a bunch of
specs broke unexpectedly…so that technique isn’t foolproof.

Please keep in mind I’ve never actually tried it, I’ve only talked to a
guy who talked to the guy who has :slight_smile:

Pat

On Sep 20, 2008, at 8:08 AM, Todd T. wrote:

end
Seems like it would be more efficient if you pull the “SHOW TABLES”
statement out of the block, and then allow closure to capture that
variable. This way “SHOW TABLES” would only be evaluated once (since
the tables aren’t changing in each example).

Also, it looks like there is no way to truncate multiple tables all in
one shot (at least in mysql and sql92):

http://dev.mysql.com/doc/refman/5.0/en/truncate.html

Scott

On Sep 20, 2008, at 7:18 PM, Ben M. wrote:

end
end

I’ve used a similar method before… However, your ‘SHOW TABLES’ and
next
is not needed:

(ActiveRecord::Base.connection.tables - %w{schema_migrations}).each do
|table_name|

Yeah, that’s just a cleaner way of doing it - I’m sure if you tailed
your logs you’d find a ‘SHOW TABLES’

ActiveRecord::Base.connection.execute(“TRUNCATE TABLE
#{table_name};”)

Seems like you might also want to add a truncate_table method onto
ActiveRecord::Base.

|table_name|
ActiveRecord::Base.connection.execute(“TRUNCATE TABLE
#{table_name};”)
end
ActiveRecord::Base.connection.execute(“SET FOREIGN_KEY_CHECKS = 1;”)

Nice.

Scott

Todd T. wrote:

end

I’ve used a similar method before… However, your ‘SHOW TABLES’ and next
is not needed:

(ActiveRecord::Base.connection.tables - %w{schema_migrations}).each do
|table_name|
ActiveRecord::Base.connection.execute(“TRUNCATE TABLE #{table_name};”)
end

Also, to address Pat’s statement about FKs… if ordering the truncates
is too cumbersome you could just turn off the FK checks:

ActiveRecord::Base.connection.execute(“SET FOREIGN_KEY_CHECKS = 0;”)
(ActiveRecord::Base.connection.tables - %w{schema_migrations}).each do
|table_name|
ActiveRecord::Base.connection.execute(“TRUNCATE TABLE #{table_name};”)
end
ActiveRecord::Base.connection.execute(“SET FOREIGN_KEY_CHECKS = 1;”)

(this is for mysql…)

-Ben