Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I'm starting a new project, and trying very very hard to use TDD to drive the design. I've been pushing for years, and finally got approval to spend the extra time on this project to use it while I learn how to do it properly.

This is a new module, to tie into an existing system. Currently, all data access happens through webservices, which for the most part are just a thin wrapper over database stored procedures.

One requirement is that for a given store, I return all Purchase Orders that are considered valid for this application. A PO is considered valid if it's ship date falls withing a given range from the stores opening date (this is for new stores).

Now, I can't put this logic in the application code, as I'm not going to bring back a million POs just to get the dozen that apply to could apply to this store given the constraint above.

I was thinking, I could pass the date range to a GetValidPOs proc, and have it use those values to return the valid POs. But, what if we add another requirement to what is considered a valid PO?

And how do I test this and verify it keeps working? We are not using an ORM, and it's unlikely to happen. And I can't call the DB in my test.

I'm stuck.

My other thought, is have some mocks that return valid data, others that return some bad data, and have the local repository throw an exception if bad data occurs, and test that the exception gets thrown if invalid data is returned by GetValidPOs proc (or the mock used in testing).

Does this make sense? Or is there a better way?

UPDATE: I am able to use EF it would seem. Now I just need to figure out how to use it, and make it testable, while still being able to rely on stored procedures, and the difficulty of having data scattered across several databases.

share|improve this question
    
Out of curiosity, why can't you select just the valid POs with a plain SQL statement? (This question or the answer does not imply a solution.) –  scarfridge May 4 '12 at 16:07
add comment

2 Answers

This is a major downside of stored procedures in the age of TDD. They have some real upsides, even now, but by definition any test that exercises a stored proc is not a unit test; it's an integration test at best.

The usual solution, assuming the architecture cannot change to use an ORM instead, is to not put these tests in the unit test suite; instead, put the tests in an integration suite. You can still run the test whenever you like to verify it works, but because the cost inherent in setting up the test (initializing a DB with the proper test data) is high and it touches resources your build-bot's unit-test agent may not have access to, it shouldn't be in the unit test suite.

You can still unit-test code that requires the data, by abstracting anything you cannot unit-test (ADO.NET classes) into a DAO class that you can then mock. You can then verify that the expected calls are made by consuming code and reproduce real-world behavior (such as finding no results) allowing testing of various use cases. However, the actual setting up of the SqlCommand to call the stored proc is pretty much the very last thing you can unit-test, by severing command creation from command execution and mocking the command executor. If this sounds like a lot of separation of concerns, it can be; remember, "there is no problem that cannot be solved by another layer of indirection, except for having too many layers of indirection". At some point you must say "enough; I just can't unit-test this, we'll do it in integration".

Other options:

  • Test the stored proc using a "short-lived" DBMS instance like SQLite. It's usually easier to do this when using an ORM, but the test can then be done "in-memory" (or with a pre-set database file included with the test suite). Still not a unit test, but it can be run with a high degree of isolation (the DBMS is part of the running process, and not something you connect to remotely that may be in the middle of someone else's conflicting test suite). The downside is that changes to the stored proc can happen in production without the test reflecting the change, so you have to be disciplined about making sure the change is first made in a test environment.

  • Consider upgrading to an ORM. An ORM with a Linq provider (virtually all the ones in common use have one) would allow you to define the query as a Linq statement; that statement can then be given to a mocked Repository that has an in-memory collection of test data to apply it to. You can thus verify the query is correct without even touching the DB (you should still run the query in an integration environment, to test that the Linq provider can correctly digest the query).

share|improve this answer
2  
-1 because TDD != unit testing. Perfectly fine to include integration-level tests when doing TDD. –  Steven A. Lowe May 5 '12 at 3:08
    
Unit testing is a subset of test driven development. In a test driven development you would create a walking skeleton of your system and then run unit, integration and functional tests on that system. Your integration, unit or acceptance tests fail, you then make them pass and write further tests. –  CodeART May 5 '12 at 9:07
    
I understand all that, both of you. Where did I say that it having to be an integration test meant you couldn't TDD it? My point was that a stored procedure cannot be tested in isolation, which is something you want to do to as much of your codebase as you can. Testing SPs instead requires more complex, longer-running integration tests; while they're still better than manual testing, an integration-heavy test suite can take hours to run and can have a detrimental effect on CI efforts. –  KeithS May 7 '12 at 16:15
    
SP testing often also requires a specific data set in the test database; the code to get the database in the proper state to achieve the expected results is very often more LoC and several times longer-running than the code you're actually exercising. This further compounds the time complexity of the test suite, and often the setup must be repeated for each individual test (and there should probably be several for each SP, to test that each functional requirement of the query within is met). –  KeithS May 7 '12 at 16:17
add comment

My advise is to divide and conquer. Forget about database and persistence for the time being and concentrate on testing fake implementations of your repositories or data access objects.

Now, I can't put this logic in the application code, as I'm not going to bring back a million POs just to get the dozen that apply to could apply to this store given the constraint above.

I would mock the repository that returns purchase orders. Create a mock with twenty odd purchase orders.

I was thinking, I could pass the date range to a GetValidPOs proc, and have it use those values to return the valid POs. But, what if we add another requirement to what is considered a valid PO?

Stub a call to GetValidPOs so that it calls your mock, rather than database procedure.

And how do I test this and verify it keeps working? We are not using an ORM, and it's unlikely to happen. And I can't call the DB in my test.

You need a unit-test to make sure correct data is returned from a mock.

You also need an integration test to make sure correct data is returned from a database. Integration test would require some configuration and clean-up. For example, before running integration test, seed your database by running a script. Verify that your script has worked. Query the database by calling your stored procedures. Verify that your results are correct. Clean up the database.

My other thought, is have some mocks that return valid data, others that return some bad data, and have the local repository throw an exception if bad data occurs, and test that the exception gets thrown if invalid data is returned by GetValidPOs proc (or the mock used in testing).

As I already said, you need a mock that returns at least some data that you can query.

When you query data you want to make sure that your system can handle exceptions gracefully. Therefore you mock behaviour so that it throws exceptions in certain scenarios. You then write tests to ensure that your system can handle these exceptions gracefully.

share|improve this answer
    
That's what I am trying to do. Just having difficulty writing a real implementation that will work the same as the mock, since our data access is not conducive to using an ORM. The majority of data I need is in several systems, and supposed to be accessed through web services...even when updating. –  CaffGeek May 8 '12 at 15:51
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.