What is the best way to use different SQL commands for unit testing?

Question

I have a .NET project that uses NHibernate. Due to some project requirements, a very specific section of the code uses HQL to select a random record using "order by newid ()". However, for unit test purposes, I use an in-memory SQLite database, which of course throttles to newid (). I need this method to use an alternative SQLite-compatible query only when starting from unit test. I cannot add conditional compilation constants for unit test purposes only, and #define works only at the file level, so I cannot just add a constant there.

Actually, I don't want my repository class to be run with some junk email code to enable this unit test. What are my options?

Edit:

I already have a global class for other things, so I added the static TestMode property to it, which will be false at any time, except when I explicitly set it in my unit test, so the code now looks like this:

string random, update; if (Globals.TestMode) { random = "from Customer order by random()"; } else { random = "from Customer order by newid()"; } 

This works, but I was hoping to avoid such an if statement. Still looking for suggestions.

Answer 1

You can ignore the responsibility of providing an HQL query to a separate class that is entered into your repository. Thus, your test code can simply introduce another implementation of this class (even a mock implementation) that provides HQL, which, as you know, will work on your SQLite server.

Remember that this is not really a unit test, because you are testing a lot more than just your code. For your test to pass, you rely on the NHibernate database and provider to make everything work. In addition, it is not possible to deterministically verify that the selected record is "random." Unit tests are deterministic. You are describing an integration test because you are trying to test the interaction with the database through ORM.

The problem is that you are trying to test it using a different database server than what you actually use in production. This is of dubious value, because, as you noticed, even if the test passes, you cannot guarantee that the code will actually work in a production environment.