More Dependencies ... this time DAO testing and database foreign keys

[Jeff Chastain]

In the next chapter of testing with regards to dependencies, I am putting tests around a DAO object.  The way I was approaching the tests for this DAO was to setup a transaction in the setup method, insert some data to test against, run the test, and then rollback any changes in the teardown method.  The problem I have run into with this DAO is that the database table it is working with has a couple of foreign keys set on it.  Thus, I cannot just insert a simple set of data into this table - I have to have matching records in all of the linked tables etc. and therefore, defining a data set to work with is not a small undertaking.  I can’t see a way to remove the dependency of the database though since part of the test I would think would be to make sure the SQL query code is written correctly.

 

So, how do people approach DAO testing in a real world environment where tables are linked and not just standalone?  Do you create a large data set, populating multiple tables just to run one test?  Or, is there another way to approach this?

 

Thanks

-- Jeff

[Brian Kotek]

I create a "large enough" data set for the test to run. The idea is really to test a specific method in your component. If the method actually relies on data from more than one table then that is incidental.

That said, if the data is needed in a range of tests, you might consider running the tests against a test database that is pre-populated with basic or essential data and run the tests against that, and then restore the database to the base state at the end of the test or after all of your tests have run (assuming your tests are atomic enough that any previously-modified data in the database doesn't affect other tests).

[Jeff Chastain]

Thanks Brian.  I just wanted to make sure I was not missing a obvious answer somewhere else.

 

-- Jeff

[Marc Esher]

I think this whole notion of testing databases is definitely one of
the toughest, for several reasons: 1) there's no easy answer. 2) each
answer is definitely not ideal for at least some people. 3) it's a
pain in the butt when your data model is more than just a few tables.

I myself struggle with this a lot, and usually I end up writing tests
that do waaaay too much. I guess I just err on the side of "too much
is better than nothing", although the result is almost always more
brittle tests and hard-to-maintain tests.

I wish I could find the time or inclination to do it properly, and I
think "properly" probably means a) separate databases and b) scripts
for maintaining the data model between databases. the problem there is
just the time it takes to keep the "unit test" database and the other
databases in sync. Is it worth it? I have no doubt. But I'm lazy I
guess, and particularly when you're working alone on this stuff and
noone else on the team, including managers, supports it, it becomes a
larger challenge.

here, for me, is one of those cases where building less-coupled
objects is a noble goal, but I'm not there yet. the book JUnit Recipes
has some good examples of this. For example, they use a generic
QueryExecutor object, and it's got a "delete", for example. and that
delete takes in a PreparedStatement and a list of parameters. And they
test that delete one time.... because if the delete works on one
prepared statement, it'll work on all of them. But what they do next
is they factor out the parameter creation into a separate object, and
then that's what they test. i.e. "pass in these args to some
QueryBuilder function, and did it return the appropriate parameters to
pass to the QueryExecutor.

So, I can see how this makes it easier to test. But damn...

The other thing the book advocates is a method for testing the sql
string itself rather than always hitting the database to verify that
the sql is OK. Not sure how I feel about that since when I look at
the kind of queries I write at work.... yikes.

Jeff, I guess this is my long-winded answer that simply says "we're
all in this boat, brother".

i do look forward to hearing more/better/alternate solutions to these
kinds of problems.

[Peter Bell]

This is pretty much my approach. I really think testing actual db
interactions at the unit test level is too much trouble (although it
makes sense for integration tests to check full stack interactions).
Instead I make the db interaction as dumb as possible, using a bunch
of other methods that I can test irrespective of the actual db. I'll
test my SQL generators to make sure the SQL output is as expected
rather than actually testing the db interactions with unit tests.
Another approach if you don't feel like writing your own data mapper/
ORM is to use someone elses - this is what Hibernate/Transfer were
designed for . . .

Best Wishes,
Peter

[Sean Corfield]

On Tue, Jun 10, 2008 at 7:32 AM, Peter Bell <pb...@systemsforge.com> wrote:
> Another approach if you don't feel like writing your own data mapper/
> ORM is to use someone elses - this is what Hibernate/Transfer were
> designed for . . .

Although Transfer is a pain in the ass to test around - with its
"strict" typing you end up having to create a lot of mocks all
injected into each other in order to mock any persistence operations
or to mock the base class for a decorator. Also, since it generates
methods dynamically at runtime, the mock frameworks don't work with
Transfer Objects (because they mock based on metadata - and most of
the methods don't exist in the metadata!).

I'm still trying to figure out a good way to unit test data gateways
based on Transfer and/or beans based on TransferDecorator.
--
Sean A Corfield -- (904) 302-SEAN
An Architect's View -- http://corfield.org/

"If you're not annoying somebody, you're not really alive."
-- Margaret Atwood