MXUnit newbie here. Question about testing DB queries

[kwbarrett]

I am a complete MXUnit newbie having really on installed it last night
and worked through a few tutorials. I have successfully written a
test for one of my CFCs, but not so sure I'm doing the most efficient
thing. I am a procedural CF programmer using Fusebox 4 and CFCs for
all my data handling. Usually we end up writing CFCs for the circuits
in our application and an additional "lookup" CFC that handles lookup
tables for select lists and the like. So our CFCs tend to have all
manner of methods in addition to the usual CRUD stuff. One of my
typical CFCs:

<cfcomponent hint="I retrieve page data" output="false">
<cfset variables.dsn = "">
<cfset variables.username = "">
<cfset variables.password = "">

<cffunction name="init" access="public" returnType="PagesManager"
output="false">
<cfargument name="dsn" type="string" required="true">
<!--- <cfargument name="username" type="string" required="true"
hint="" />
<cfargument name="password" type="string" required="true" hint="" />
--->
<cfset variables.dsn = arguments.dsn>
<!--- <cfset variables.username = arguments.username />
<cfset variables.password = arguments.password /> --->
<cfreturn this>
</cffunction>
<cffunction name="retrievePage" access="public" output="false"
returntype="query">
<cfargument name="cntPage" type="numeric" required="true" />
<cfset var qRetrievePage = ""/>
<cfquery name="qRetrievePage" datasource="#variables.dsn#">
SELECT *
FROM tblPage
WHERE cntPage = <cfqueryparam value="#ARGUMENTS.cntPage#"
cfsqltype="CF_SQL_INTEGER">
</cfquery>

<cfreturn qRetrievePage/>
</cffunction>
</cfcomponent>

I was trying to write a test for the retrievePage() method and came up
with this:
<cfcomponent displayname="PagesTest"
extends="mxunit.framework.TestCase">
<cffunction name="testRetrievePage" output="false" access="public"
returntype="void" hint="">
<cfscript>
PagesManager = createObject
("component","stmartinsweb.stmartins_fb.components.PagesManager").init
("somedsn","someuid", "somepwd");
expected = 1;
actual = PagesManager.retrievePage(1).RecordCount;
assertEquals(expected, actual);
</cfscript>
</cffunction>
</cfcomponent>

This test simply checks to see that a single record is returned. I
get nervous anytime I hardcode values. This test requires that I pass
in an id into the retrievePage() method. I know it's a test, but
hardcoding that id doesn't seem right even though my test seems to
work correctly.

I would also wonder how to test the createPage() method. Typically I
run INSERT queries in <cftransaction>. After the transaction, but
before <cfreturn> I typically check the last ID--I'm on SQL Server and
then set the return to that value. I'm guessing that for my test, I
run the createPage() method, followed by checking that the value in
<cfreturn> is not null or greater than 0.

I would just like to know I'm heading in the right direction.
Sometimes I get ahead of myself. While doing some of the MXUnit
tutorials, I came to like the TDD pattern of doing things and think it
would make me a better programmer, but I guess I just need a little
reassurance that I'm at least on the right path. Thanks.

Ken

[billy]

Hi Ken,

Welcome! I hope others will chime in, too ...

You're right on target with unit testing and with respect to dealing
with databases. The issue is that databases are volatile and can make
your tests fragile if the database changes. You have a couple of
options: (1) you can have a database sandbox where you have control
over its state and maintain that state in setUp() and tearDown() or
with some other private methods; or (2), and this is my preferred
method, is to mock those things that touch the "outside world". In
other words, you can spoof any method that depends upon a database and
thereby bypass the database altogether. This will allow you to test
your code without having to depend on database connections or state.
You can accomplish this using Brian Kotek's ColdMock, Mike Steale's
CFEasyMock, or MXUnit's built-in injectMethod(...) -
http://mxunit.org/doc/index.cfm?doc=injectmethod.

So, if it were me, I would do something like this:

<cffunction name="testRetrievePage">
<cfscript>
var expected = 'myId';
var actual = '';
var mgr = createObject("component","PagesManager");

//Ok. Now anytime we call retrievePage() we're calling _retrievePage
() instead. Nice, Marc!
injectMethod(this, mgr, "_retrievePage", "retrievePage");

actual = mgr.retrievePage().id;
assertEquals(expected, actual);
</cfscript>
</cffunction>

<!--- Mock method : note PRIVATE access --->
<cffunction name="_retrievePage" access="private" returnType="query">
//...preferably use QuerySim, WDDX or
var q = queryNew('id,col2,...');
queryAddRow(q);
querySetCell(q, 'id' , 'myId');
etc ...
<cfreturn q />
</cffunction>

The above tests your code without hitting the db. You would do the
same thing with createPage() - mock it. With that said, you will, at
some point, want tests that also hit the db.

bill

[Marc Esher]

But I have to add that what bill's talking about doesn't actually test
the database call itself. I was reminded of this thread from a while
back:

http://groups.google.com/group/mxunit/browse_thread/thread/1d1526cc7cab594f/61ac48ede0c0616c?lnk=gst&q=testing+dao#61ac48ede0c0616c

For your specific case, where you don't like having a hard-coded ID,
I've often used an approach where I'll have a private function in the
unit test named "getID":

<cffunction name="getID" access="private">
cfquery...
select max(myID) from mytable
cfreturn....
</cffunction>

and then inside my unit test i'll do <cfset var ID = getID()>


So that's one approach.

I find myself less and less testing database queries lately. i'm just
not seeing much bang for the buck in that respect, particularly with
inserts and updates. i will test select statements, but even then
it's really rudimentary testing.

i think this is a function of the kind of work I do at my job... it's
less CRUD and more business logic, which lends itself to unit testing
I think. I think that the "hard" parts of my code deal less with the
actual query and more with the "what do I do with this resultset?"
question. So I'll often write mocks for the queries using cfquerysim
and then I'll write unit tests for the functions that act on the data,
but no unit tests for the functions that actually get / persist the
data itself.

best,

marc