database code testing

[bob philbin]

One application I've written demands a great deal of database interaction, and I've not found any good way to use ATDD because of that. It seems like I'll have to write way too much code just populating tables for a given test and thus embedding new sources of error. Does your C6 (or other parts of your book) cover this? Do you have suggestions for db-intensive ATDD?

[Stefan Bell]

Originally posted by bob philbin:
One application I've written demands a great deal of database interaction, and I've not found any good way to use ATDD because of that. It seems like I'll have to write way too much code just populating tables for a given test and thus embedding new sources of error. Does your C6 (or other parts of your book) cover this? Do you have suggestions for db-intensive ATDD?

Take a look at DBUnit.

[Paul Croarkin]

We've had good success with DBUnit. We have also done things with Hibernate where we wrote a mock transaction handler that always rolled back rather than committing.

[Lasse Koskela]

Originally posted by bob philbin:
One application I've written demands a great deal of database interaction [...] It seems like I'll have to write way too much code just populating tables for a given test and thus embedding new sources of error. Does your C6 (or other parts of your book) cover this?

Yes, I talk about this stuff in Part 3 and chapter 6 talks a bit about populating data for integration tests, which applies (to some degree) to ATDD as well.

When it comes to populating data being a lot of work, my first suggestion would be to look for opportunities to create named data fixtures that you could reuse throughout your tests?

I'd also be curious as to whether your test automation tool allows for proper refactoring of the acceptance test code? If it does, make sure you refactor duplication in your test code as mercilessly as you do for production code and unit tests.

I'm also curious if you're overusing the "testing backdoor" pattern (the database being the backdoor). Maybe there's another "seam" in your system where you could probe for expected behavior and state? Maybe you could run some of your acceptance tests against a partially stubbed configuration of the system under test?

[Craig Bayley]

We are currently looking at the pros and cons of stubbing out the database calls for our application testing, so we can guarantee always getting the same results back. Being a service oriented architecture, with many provider systems we have no control over, it is easier for us to "mock" the database than to try and keep up with possible changes. Our automated test framework would catch changes as failing tests, but that smacks of false positives...

[Lasse Koskela]

Originally posted by Craig Bayley:
Being a service oriented architecture, with many provider systems we have no control over, it is easier for us to "mock" the database than to try and keep up with possible changes. Our automated test framework would catch changes as failing tests, but that smacks of false positives...

Well, tests are essentially explicit assumptions about the system's behavior. If you put it that way, they're no longer false positives

[Craig Bayley]

Maybe....But that kinda talk aint gonna get me a pay rise.

[Craig Bayley]

Hmmm....Maybe I could set all the JUnits to:
System.out.println("Nothings wrong. Everything's fine, Dave.");

[Charles Hasegawa]

I think the question is what do you mean by database testing?

Interactions with the DAO's are one test - ie do my objects use my DAO's appropriately and handle errors or bad data the way I expect. In most of these cases, the correct answer is mock object. Mock object will let you define the behaviors (ie I expect x going in to return 5 rows of data that will be a-b-c-d-e, so just give me that - OR - if I call this with a bad value I should get an exception, do I handle that correctly).

The DAO interaction with the database is another set of tests. Do my DAO's save the data correctly? Do they retrieve data correctly for given inputs? Are my transactions handled properly when there are problems? For these tests, DBUnit is one of your better choices. You set the initial state of the database and the expected state of the database as XML files. The framework handles the loading of the data and will also handle verification. It is a bit tedious writing the XML if you are testing on large datasets, but it beats most of the alternatives...

[Himanhsu Yadav]

Originally posted by Charles Hasegawa:
I think the question is what do you mean by database testing?

Interactions with the DAO's are one test - ie do my objects use my DAO's appropriately and handle errors or bad data the way I expect. In most of these cases, the correct answer is mock object. Mock object will let you define the behaviors (ie I expect x going in to return 5 rows of data that will be a-b-c-d-e, so just give me that - OR - if I call this with a bad value I should get an exception, do I handle that correctly).

The DAO interaction with the database is another set of tests. Do my DAO's save the data correctly? Do they retrieve data correctly for given inputs? Are my transactions handled properly when there are problems? For these tests, DBUnit is one of your better choices. You set the initial state of the database and the expected state of the database as XML files. The framework handles the loading of the data and will also handle verification. It is a bit tedious writing the XML if you are testing on large datasets, but it beats most of the alternatives...

Can we test DAO using Mock Objects?

[Charles Hasegawa]

I don't really understand the question...
The DAO is your last entry point between the raw data store (whether that is a DB or flat files or whatever), so how/why would you think to use mock objects to test the DAO?

[Jeanne Boyarsky]

There are some benefits to unit testing a DAO. For example, mocking out a JDBC result set allows you to check the object is populated correctly even if nulls/border cases are returned. It is important that the unit tests don't replace automated integration tests entirely of course as we need to test the database connectivity/syntax/interactions too.

[Himanhsu Yadav]

Originally posted by Charles Hasegawa:
I don't really understand the question...
The DAO is your last entry point between the raw data store (whether that is a DB or flat files or whatever), so how/why would you think to use mock objects to test the DAO?

Do I need not to unit test DAOs as I dont have any local DB.

[Lasse Koskela]

Originally posted by Himanhsu Yadav:
Do I need not to unit test DAOs as I dont have any local DB.

A unit test by definition does not touch an actual database. Instead, you unit test your DAOs in isolation from the database--usually against mock objects.

[Charles Hasegawa]

Originally posted by Lasse Koskela:

A unit test by definition does not touch an actual database. Instead, you unit test your DAOs in isolation from the database--usually against mock objects.

I'd argue that isn't that case. Testing your DAOs with mocks to test that the results are handled correctly is one test. I'd argue that testing that the DAO is asking for the correct information is just as important.
- Is the SQL formed correctly (ie, did we incorrectly set any params)?
- Did I write the SQL correctly - select from the correct table, join correctly, ask for the correct column names, etc.
- Does my SQL update the database correctly - does my sql leave the DB in the state I believe it should be left?

Even if you are using another layer - hibernate, JDO, ibatis, etc, these are still valid questions. In fact, your DAO may not even retrieve data or send values to the db. It may be that you are simply trying to kick off a stored procedure that needs to be run and you want to verify that the db state has changed after.

Another thought - lets say you did straight JDBC and never tested against the database. Months later, everything is working well, but you decided you want to introduce Hibernate. If you had a complete set of DAO tests that worked with an actual database, you'd know instantly, as you migrated each bit to hibernate, if you hadn't configured something correctly, or if you had done your Criteria based HSQLs correctly or not.

Mocks have their place in helping to isolate parts of your code, but you can't say that your DAOs are well tested if you never check the datastore's state.

[Lasse Koskela]

Originally posted by Charles Hasegawa:
I'd argue that isn't that case. Testing your DAOs with mocks to test that the results are handled correctly is one test. I'd argue that testing that the DAO is asking for the correct information is just as important.
- Is the SQL formed correctly (ie, did we incorrectly set any params)?
- Did I write the SQL correctly - select from the correct table, join correctly, ask for the correct column names, etc.
- Does my SQL update the database correctly - does my sql leave the DB in the state I believe it should be left?
[...]
Mocks have their place in helping to isolate parts of your code, but you can't say that your DAOs are well tested if you never check the datastore's state.

Yes, but those aren't unit tests. They're integration tests.

[Himanhsu Yadav]

Originally posted by Lasse Koskela:

Yes, but those aren't unit tests. They're integration tests.

Now I am confused. Should one unit test its DAO classes or not. If yes then how and what can be achieved by this.

[Jeanne Boyarsky]

Yes, it is still good to unit tests DAOs. One major benefit is that it significantly easier to test exception cases through a unit test. I listed some other benefits above.

[Charles Hasegawa]

(from the wiki - http://en.wikipedia.org/wiki/Integration_testing )
Integration testing (sometimes called Integration and Testing, abbreviated I&T) is the phase of software testing in which individual software modules are combined and tested as a group. It follows unit testing and precedes system testing.

Integration testing takes as its input modules that have been unit tested, groups them in larger aggregates, applies tests defined in an integration test plan to those aggregates, and delivers as its output the integrated system ready for system testing.

So now we're just being nit-picky. I'll argue its a unit test if I'm testing the DAO in isolation. The fact is, its coupled to a data store and I need to test its behavior to verify that my one piece of code (the DAO) works correctly. Before I can test if the business objects interact with the DAOs at an integration level, I need to know if my one piece of code works.
[ October 05, 2007: Message edited by: Charles Hasegawa ]

[M Mistroni]

Hello all,
found this post on integration testing.. would like to add my feedback as i neede some arguments..
So far, i have always used DbUnit for populating data in my database...
Now i have an argument at work on how to populate data in db for testing..
my colleagues want to use dao.create() to populate database... while i am for DbUnit, as i think we should NOT Use a method (create()) that is supposed to be tested by itself ...

i'd like to know what are you opinions.... if it is a good idea to use a dao.create() to put the data in the database or DbUnit

thanks in advance and regards
marco

[Jeanne Boyarsky]

Originally posted by M Mistroni:
as i think we should NOT Use a method (create()) that is supposed to be tested by itself ...

This isn't inherently good or bad. DB Unit does quite a bit more though. For example, it can export the data to XML. If I was just inserting a few rows, I would use dao.create().

Why do you think we should not use dao.create()? If it fails, you would find out when most of your tests fail, right?

[M Mistroni]

Hello,
thanks for the reply..
i didnt say using dao.create is totally bad..
beside, doing a google search for integration testing, i was not able to find a single result that uses a create() method for populating the database..

i take your point that if data is simple, you'd go for using a create() statement.
let's assume then that there's something wrong with your dao.create such as the population of the db fails.

then, for each and every testMethod method - where you should test the invocation of method Method - , you are actually calling 2 methods of your class - the create, and the methodMethod.

where would you go to find what is the problem? is it your create method or is your Method method that is causing the problem? or is it a change in the internal state of your object between create and method Method that is causing the problem? or everything is fine, it's just a mouse that step on the db server cable?
or create is fine and it's just method Method that is failing?

Personally, i would not use the code that i have written in order to populate the database for testing the code that i have written.
I'd use a robust framework that has been designed just for that.

It might be just my personal opinion though...

thanks for your feedback

regards
marco

[Jeanne Boyarsky]

Originally posted by M Mistroni:
let's assume then that there's something wrong with your dao.create such as the population of the db fails.

then, for each and every testMethod method - where you should test the invocation of method Method - , you are actually calling 2 methods of your class - the create, and the methodMethod.

Well, first I would test the create method on it's own to make sure it works. After that, if I got a whole slew of failures, I would know something more global broke.