How can I automate testing for my database driven function?

I'm trying to improve the automated testing in my application, but am unsure of the best way to proceed.

My app gathers data from multiple forms, recodes it and stores it in a database. I have created a pretty complex SQL view, which flattens the structure out, so it can be imported into a stats package (SPSS).

My concern is that the view is complex, and I want to autom开发者_如何学JAVAate some tests around it.

Currently I have some functional tests, which create a complete form objects model, and sends it into the application. I then retrieve the view from the database, and use reflection to test that the retrieved view fields match the original data.

The problem is that this is very manual and heavy, my fixtures are lengthy, and it is cumbersome to add new scenarios (i.e. various parts of the model incomplete).

Does anyone have any advice on how I could improve my test strategy? Tips tricks all welcome!

Thanks!

DbFit is perfect for this. DbFit is an extension of FitNesse which maybe you are already using since you spoke of using "fixtures". In any case, DbFit makes it really easy to set up a test where you can seed some data, run the View, compare the expected results, and then it will automatically rollback the data that you just seeded for the test. And it is very easy to update as you add more fields to the View. AND it requires no additional objects in your DB like some other SQL "unit" testing suites.

You can find more info on using DbFit at:

http://benilovj.github.com/dbfit

http://groups.google.com/group/dbfit

And here is a tutorial that I wrote for it that explains the basic options:

http://www.sqlservercentral.com/articles/Testing/64636/

This is a very difficult question to answer. It almost sounds to me like You want to make a single test that tests all in one go.

First, Your app should be constructed, so each functionality is isolated in its own class, thereby making it easy to test AND easy to replace by stubs when testing other things. Dependencies on other functions should be injected (Dependency Injection).

Second, you should use the same technique for external systems like database connections and SPSS file writers. This involves wrapping such functionality so these dependencies also can be injected, and thus replaced by stubs when testing other aspects of your app.

Third, be aware that if tests are hard to write, 99,99% of the time this indicates that your design is not as strong as it could be.

Regards, Morten