Effective Test Driven Database Development

1
Effective Test Driven Database Development

• Gojko Adzic
• http//gojko.net
• gojko_at_gojko.com

2
What we'll talk about

• Unit testing in the database
• Stored procedures
• Data management
• Integration testing with Java/.NET
• xUnit and FIT/FitNesse
• Preparing and verifying data
• Key considerations with ORMs

3
Lots of teams struggle with Database testing...

• Bad tools
• Inherently hard to test
• O/R Mismatch
• Changes are persistent
• Attitude of DB Specialists

4
But I use an ORM tool...

• You will still have integration issues
• Session management and caching can hide serious
  problems

5
If it does not fit, look for a better solution

• Instead of fighting against database features,
  use them in your favour!

6
Run tests in a transaction

• This makes them instantly repeatable and
  isolated.
• Alternatively clean up after, but prefer
  transactions.

7
Running integration tests inside a transaction

• Often easier than you think...
• Make sure that everything goes through the same
  DB connection
• Set up the testing framework so that a
  transaction is started in set-up and rolled back
  on the end.

8
...Spring, Hibernate, FitNesse...

• Declarative transactions, ORM controls the
  database...
• So change the test runner and use the automation
  to your advantage...
• !define TEST_RUNNER test.RollbackServer
• http//gojko.net/2008/01/22/spring-rollback/

9
...Spring, Hibernate, FitNesse...

• public void process()
• ApplicationContext ctx new
• FileSystemXmlApplicationContext("lib/test.xml")
• RollbackBean rollbackProcessingBean
  (RollbackBean)
• ctx.getBean("rollback")
• try
• while ((size FitProtocol.readSize(socketRe
  ader)) ! 0)
• try
• rollbackProcessingBean.process(
• new DocumentRunner(size))
• catch (RollbackNow rn)
• print("rolling back now" "\n")
• catch (Exception e)
• exception(e)

10
...Spring, Hibernate, FitNesse

• public class RollbackNow extends RuntimeException
• public class RollbackBean
• _at_Transactional
• public void process(Runnable r)
• r.run()
• throw new RollbackNow()

11
If transactions are not possible...

• Eg build tests or integrated web tests
• Preferably have a separate database instance for
  each developer and one for the build server.
• Or a dev, build and integration db
• Run these tests overnight

12
Count on stuff being in the database, but not the
things that you need

• Make tests self-sufficient.
• Don't count on the order of tests
• Prepare everything you need for the test in the
  set-up.
• Or restore a known state (DbUnit, custom loaders,
  base db)?

13
Unit tests have to run quickly

• ...Or people simply will not run them

14
Full build is the only thing you can really trust

• So have run full builds on a base DB and all the
  tests overnight

15
Reducing replication

• Maybe use create scripts and generate update
  scripts
• Generate Java/.NET wrappers for stored procs
• Generate object definitions and loaders

16
If you use an ORM tool...

• Flush on the end to make sure that DB and OO
  models are consistent
• Have tests that commit and rehydrate objects in a
  different transaction to make sure that mappings
  are complete

17
DBFIT Test Driven DB Development Made Easy

• FITFitNesseDB Fixtures
• http//fitnesse.info/dbfit
• http//sourceforge.net/projects/dbfit

18
Why DbFit?

• Manipulate data in a relational model
• Provides all the plumbing
• Transaction management
• Smart features based on meta-data
• Parameter mapping
• wizards for regression tests
• Because it runs inside FitNesse, already
  integrated with a lot of other tools/libraries

19
Use DbFit to

• Write and execute DB Unit tests
• Prepare/verify Java or .NET integration tests
• .NET Sql Server, Oracle, (DB2)?
• Java Mysql, Oracle, (DB2, SQL Server, Derby)?

20
Simple commands

• Execute procedure
• Query
• Execute
• Insert
• Update

21
FitNesse symbols directly mapped to bind variables

• Retrieve auto-generated keys and use them
  directly
• available in Java as well
• Already mapped to bind variables

22
Advanced features

• Inspect queries, tables or procs to automatically
  generate test tables and regression tests
• Store and compare queries
• Standalone mode for full control

23
Where next

• Beers at the Crown
• ALT.NET community talk 31st July
• Agile 2008 in August
• Next talk about testing Selenium 28th Sept
• http//gojko.net

24
Image credits

• http//www.flickr.com/photos/seantubridy/
• http//www.flickr.com/photos/aasta/
• http//www.flickr.com/photos/guiniveve/