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:
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 ...