Asp Forum
Home
|
Login
|
Register
|
Search
Forums
>
comp.lang.ruby
Tutorial: TDD for sqlite3-ruby
Phlip
1/10/2005 6:57:00 AM
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/TestFirstUser...
3 Answers
Jamis Buck
1/10/2005 1:25:00 PM
0
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/TestFirstUser...
Thanks for sharing, Phlip! I'm glad you've found some use for the
sqlite3 bindings. :)
- Jamis
--
Jamis Buck
jamis_buck@byu.edu
http://www.jamisbuck...
------------------------------
"I am Victor of Borge. You will be assimil-nine-ed."
Phlip
1/10/2005 3:02:00 PM
0
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/TestFirstUser...
Jamis Buck
1/10/2005 4:16:00 PM
0
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
jamis_buck@byu.edu
http://www.jamisbuck...
------------------------------
"I am Victor of Borge. You will be assimil-nine-ed."
Servizio di avviso nuovi messaggi
Ricevi direttamente nella tua mail i nuovi messaggi per
Tutorial: TDD for sqlite3-ruby
Inserendo la tua e-mail nella casella sotto, riceverai un avviso tramite posta elettronica ogni volta che il motore di ricerca troverà un nuovo messaggio per te
Il servizio è completamente GRATUITO!
x
Login to ForumsZone
Login with Google
Login with E-Mail & Password