From: Phlip <phlip_cpp@...>
Date: 2005-01-10T16:01:21+09:00
Subject: Tutorial: TDD for sqlite3-ruby

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