[rspec-users] Database clearing

Pat Maddox pergesu at gmail.com
Sat Sep 20 11:22:08 EDT 2008

"Todd Tyree" <todd at snappl.co.uk> writes:

> 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

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)
     ActiveRecord::Base.connection.execute("TRUNCATE #{tables_to_truncate.join(', ')}")

(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.


More information about the rspec-users mailing list