Transcript gojko.net
Effective Test Driven
Database Development
Gojko Adzic
http://gojko.net
[email protected]
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
Lots of teams struggle with
Database testing...
Bad tools
Inherently hard to
test
O/R Mismatch
Changes are
persistent
Attitude of DB
Specialists
But I use an ORM tool...
You will still have
integration issues
Session
management and
caching can hide
serious problems
If it does not fit, look for a
better solution
Instead of fighting
against database
features, use them
in your favour!
Run tests in a transaction
This makes them instantly repeatable
and isolated.
Alternatively – clean up after, but
prefer transactions.
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.
...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/
...Spring, Hibernate, FitNesse...
public void process() {
ApplicationContext ctx = new
FileSystemXmlApplicationContext("lib/test.xml");
RollbackBean rollbackProcessingBean = (RollbackBean)
ctx.getBean("rollback");
try {
while ((size = FitProtocol.readSize(socketReader)) != 0) {
try {
rollbackProcessingBean.process(
new DocumentRunner(size));
} catch (RollbackNow rn) {
print("rolling back now" + "\n");
}
}
} catch (Exception e) {
exception(e);
}
}
...Spring, Hibernate, FitNesse
public class RollbackNow extends RuntimeException
{ }
public class RollbackBean{
@Transactional
public void process(Runnable r){
r.run();
throw new RollbackNow();
}
}
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
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)
Unit tests have to run quickly
...Or people simply will not run them
Full build is the only thing you
can really trust
So have run full builds on a base DB and all the
tests overnight
Reducing replication
Maybe use “create” scripts and
generate “update” scripts
Generate Java/.NET wrappers for
stored procs
Generate object definitions and
loaders
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
DBFIT: Test Driven DB Development
Made Easy
FIT+FitNesse+DB Fixtures
http://fitnesse.info/dbfit
http://sourceforge.net/projects/dbfit
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
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)
Simple commands
Execute procedure
Query
Execute
Insert
Update
FitNesse symbols directly mapped
to bind variables
Retrieve auto-generated keys and use them
directly
<< and >> available in Java as well
Already mapped to bind variables
Advanced features
Inspect queries, tables or procs to automatically
generate test tables and regression tests
Store and compare queries
Standalone mode for full control
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
Image credits
http://www.flickr.com/photos/seantubridy/
http://www.flickr.com/photos/aasta/
http://www.flickr.com/photos/guiniveve/