Tutorial: TDD for sqlite3-ruby

Discussion in 'Ruby' started by Phlip, Jan 10, 2005.

  1. Phlip

    Phlip Guest

    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
     
    Phlip, Jan 10, 2005
    #1
    1. Advertising

  2. Phlip

    Jamis Buck Guest

    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

    http://www.jamisbuck.org/jamis
    ------------------------------
    "I am Victor of Borge. You will be assimil-nine-ed."
     
    Jamis Buck, Jan 10, 2005
    #2
    1. Advertising

  3. Phlip

    Phlip Guest

    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
     
    Phlip, Jan 10, 2005
    #3
  4. Phlip

    Jamis Buck Guest

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

    --
    Jamis Buck

    http://www.jamisbuck.org/jamis
    ------------------------------
    "I am Victor of Borge. You will be assimil-nine-ed."
     
    Jamis Buck, Jan 10, 2005
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Phlip
    Replies:
    4
    Views:
    524
    angus
    Mar 2, 2004
  2. mekondelta

    How to do TDD in Ruby?

    mekondelta, Dec 27, 2006, in forum: Ruby
    Replies:
    16
    Views:
    207
    mekondelta
    Dec 29, 2006
  3. Jeffrey 'jf' Lim
    Replies:
    5
    Views:
    494
    Jeffrey 'jf' Lim
    Apr 9, 2007
  4. aidy

    TDD with Ruby

    aidy, May 8, 2008, in forum: Ruby
    Replies:
    2
    Views:
    80
    Peter Jones
    May 10, 2008
  5. SunSw0rd
    Replies:
    4
    Views:
    258
    SunSw0rd
    Jul 2, 2009
Loading...

Share This Page