Using MXUnit to Test SQL Server Database Logic

Tuesday, July 1, 2008

I need a way to test database logic and to persist those tests. In the past, I would open query analyzer type in some batch code, hit F5 and visually inspect the results. If all looked good, I would move it to a stored procedure, view, CF code, or some other object. There are a number of problems with this approach, and the one that bugs me the most is that the work product of this process becomes lost. Though I may have something working in the end, the pieces and what I was thinking at the time are gone, and at my age, that means gone forever.

Enter MXUnit ... using an xUnit framework to test a database? Why not? Ideally, it would be nice to have an xUnit database tool that allowed me to test snippets of batch code. I know there's sqlunit and dbunit that may work, but where I'm at, our db's are tied down pretty tight, but given a cf datasource and MXUnit, I can get pretty far. I can also wrap stored procedures up in tests and print out reports of database test suite runs for the dbas (more on this one later).

Here's a little gem that popped up the other day. Did you know you can define a CURSOR object inside of CFQUERY? I didn't, but here's is part of something I wanted to test:

  
---- Super Simple SQL Date Stuff ----  
DECLARE @fiveMinutesAgo datetime, @rightnow datetime 
SELECT @rightnow = GETDATE() 
SELECT @fiveMinutesAgo = DATEADD (n, -5, @rightnow)
PRINT @rightnow 
PRINT @fiveMinutesAgo
---- End ----

This simply prints the time the batch was run and that time less 5 minutes - mine eyes verify the correctness. Not too bad; certainly not automated; but, it has a smell that is becoming increasingly intolerable for me.

Here's the same thing written as an MXUnit test:

<cffunction name="testCallingDbDateFunctions">
  <cfquery name="q" datasource="logparser"> 
    DECLARE @fiveMinutesAgo datetime, @rightnow datetime  
    SELECT @rightnow = GETDATE()  
    SELECT @fiveMinutesAgo = DATEADD (n, -5, @rightnow)  
    
    DECLARE dbTestCursor CURSOR FOR  
    SELECT @rightnow as nowDate, 
           @fiveMinutesAgo as fiveMinutesAgo;  
    OPEN dbTestCursor;  
    FETCH NEXT FROM dbTestCursor  
    CLOSE dbTestCursor;  
    DEALLOCATE dbTestCursor  
  </cfquery>  

  <cfscript>  
    rightNow = q.nowDate;  
    exepectedDate = dateAdd('n', -5, rightNow);  
    fiveMinutesAgo = createODBCDateTime(q.fiveMinutesAgo);  
    debug(q); 
    assertEquals(exepectedDate,fiveMinutesAgo,
                "If broken, could be a date bug in CF or SQL.");  
  </cfscript>
</cffunction>

The debug output looks like this:

image

This looks like a lot of code to test such a simple piece of logic, and it is. But, the cool things about this for me, were (1) I can create a CURSOR object within CFQUERY and the name of the query becomes a reference to the CURSOR, and (2) I can use MXUnit and ColdFusion to run and persist database logic tests.

More on database testing soon ...

-bill

10 comments:

Marc Esher said...

nice!

one thing: in that test, if your assertion fails, the debug() won't fire.

bill said...

got it. thanks!

Aaron Longnion said...

thanks! I'm excited to hear more on this, and appreciate you taking the time to explain your take on how to unit test databases and queries via MXUnit.

bill said...

@aaron: yeah, it seems like there's a pretty big need, or at least a desire, to have some ways to test databases. I was only able to touch on that briefly at the testing talk at CFUnited, but several folks mentioned afterwards that they really wanted some more info on the subject. Stay tuned ...

thanks,
-bill

Marc Esher said...

just thinking out loud: it'd be cool if you kept the DML scripts (create procedure, etc) in source control. then your test could fetch the script, strip off the "create..." junk and just run the guts of it. this way, you wouldn't duplicate your "real" db code and you'd always be testing the most recent. it'd help keep the tests from going stale, too.

just a thought....

bill said...

@marc - funny you should mention that! I had similar thoughts this morning with respect to creating and loading tables with data. This also speaks to the some of the TestNG/annotation stuff we've been discussing and some of Mike Rankin's thoughts. You could have a @BeforeSuite annotation that creates tables and data needed for the fixture, and then once all tests are run, you could call @AfterSuite to drop the tables ...

bill

Mike said...

If you're using mssql 2005, you absolutely want to take a look at the new snapshots feature. I'm able to do a complete back and restore around each one of my database method tests. It's pretty amazing how fast it is; maybe a second or two to do the backup and about the same for the restore.

This goes hand in hand with the "develop locally" approach. You definitely don't want to use this approach with a database that isn't just yours.

One caveat that I have, though is that some of my db test fail if I run them with anything other than the test runner embedded in the eclipse plugin. It's very strange. It's almost like the plugin runs tests serially and the html test runner runs them in parallel. Trying to run tests in parallel while doing backups and restores in between probably ain't gonna work.

When it does work, though, it's pretty freaking amazing.

Marc Esher said...

yer right, mike, the plugin does work that way in that it runs each test function as a separate webservice request. technically, only one object is created, and it's put in an object cache, and then subsequent calls to each test just pull that object from the cache. but the difference is that there's no request scope to speak of, and each function gets run in a single "operation" inside CF so to speak.

i'm really curious though why you're having the problem when you run it in the html test runner. i wish i was sitting beside you now to watch it because this sounds like a fun problem to solve.

also, we're heading out for stogies today during lunch. too bad you're down in the hood and not up here in sunny owings mills!

bill said...

@mike - super-cool use of sql-05!

@all - One of of the numerous things about the TestNG approach that struck me was the ability to control the execution threads of tests: "You can also define new groups ..., such as whether to run the tests in parallel, how many threads to use, whether you are running JUnit tests, etc... "; e.g.,
@Test(threadPoolSize = 3, invocationCount = 10, timeOut = 10000)

Marc Esher said...

big time. the notion of groups of tests is very cool. and i know you shouldn't care about test order, but i can see how this would be useful.

i really like the whole "method interceptor" approach, too. TestNG got their shit together