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.

--
Phlip
http://industrialxp.org/community/bin/view/Main/TestFirstUserInterfaces

[Jamis Buck]

On 16:01 Mon 10 Jan , Phlip wrote:
> 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.
>
> --
> Phlip
> http://industrialxp.org/community/bin/view/Main/TestFirstUserInterfaces

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

- Jamis

--
Jamis Buck
jamis...@byu.edu
http://www.jamisbuck.org/jamis
------------------------------
"I am Victor of Borge. You will be assimil-nine-ed."

[Phlip]

Jamis Buck wrote:

> 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!

--
Phlip
http://industrialxp.org/community/bin/view/Main/TestFirstUserInterfaces

[Jamis Buck]

On 00:06 Tue 11 Jan , Phlip wrote:
> Jamis Buck wrote:
>
> > 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!

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. :)