Tutorial: TDD for sqlite3-ruby

[Phlip]

Rubies:

I work at a shop that has bought all the latest payware tools for GUIs, web
interfaces, and databases. So, naturally, when I encountered a user story
(that I made up) which required SQL statements to query a small database, I
leapt at the chance to avoid them all and have some fun.

I can't express in words what a joy using Ruby and TDD to populate an SQLite
database has been. However, the following tutorial, using snips of code with
the actual project details censored out, might lead others to this
particular mountaintop.

The most common trick for database TDD is this: Declare a transation in the
setup() fixture, and a rollback in the teardown():

class MyLiteDatabase < Test::Unit::TestCase

def setup()
generateDatabase('test.db') if not File.exists?('test.db')
@db = Database.open('test.db')
@db.transaction()
end

def test_Foo()
# call Foo() to push data in
# assert they are in there
end
#...
def teardown()
@db.rollback()
@db.close()
end

end

If you delete test.db before running the tests, generateDatabase() will
build the database schema again:

def generateDatabase(databaseName)
db = Database.open(databaseName)

db.execute_batch <<-SQL
create table my_table (
id integer primary key,
whatever varchar(40) not null
);
....
SQL
end

If the tests recreate the database once per test case, then they will run
too slow. Because databases are tuned to support transactions efficiently,
declaring the tests inside a transaction, then rolling it back, efficiently
tests the database at a usage profile similar to production usage.

Each case then only needs to call our target method ("Foo()"), then query
the database to see if the data arrived.

 

[Jamis Buck]

If the tests recreate the database once per test case, then they will run
too slow. Because databases are tuned to support transactions efficiently,
declaring the tests inside a transaction, then rolling it back, efficiently
tests the database at a usage profile similar to production usage.

This is a very important point, and I'd like to stress it. SQLite3, in
particular, behaves _very poorly_ unless you use explicit
transactions. In my testing and benchmarking, I've seen inserts take
_orders of magnitude_ longer to execute outside of an explicit
transaction, mostly due to disk activity (from reading and writing
journals, I imagine).

Each case then only needs to call our target method ("Foo()"), then query
the database to see if the data arrived.

Thanks for sharing, Phlip! I'm glad you've found some use for the
sqlite3 bindings.

- Jamis

 

[Phlip]

This is a very important point, and I'd like to stress it. SQLite3, in
particular, behaves _very poorly_ unless you use explicit
transactions. In my testing and benchmarking, I've seen inserts take
_orders of magnitude_ longer to execute outside of an explicit
transaction, mostly due to disk activity (from reading and writing
journals, I imagine).

So if I write a bunch of test cases, and if setup() erased the database and
built it again (unpopulated), then how much of the performance hit was from
rebuilding the database (with 4 simple tables), and how much was from the
lack of transactions?

My bad for not profiling, and just assuming it was the rebuilding!

 

[Jamis Buck]

So if I write a bunch of test cases, and if setup() erased the database and
built it again (unpopulated), then how much of the performance hit was from
rebuilding the database (with 4 simple tables), and how much was from the
lack of transactions?

My bad for not profiling, and just assuming it was the rebuilding!

Rebuilding is slow. On my machine, it took (including the time to do
disk IO's) about 6.5 seconds to delete and recreate a simple 4-table
database 100 times. 7.5 seconds to do a create table/drop table
combination, instead of just deleting the database file.

In general, rebuilding the database will be one of the most
time-consuming things your app will do, so your optimization (building
the database once) is a good idea.

I just focused on the transaction thing in my reply because that's
what bit me most recently.