Thanks!
Aaron Longnion

that said, i'm piecing together, here and there, documentation on
testing DAOs in addition to other hard stuff. In particular,
documentation on how to design components for testability.

Also, as you state testing DAOs is a special case. rankin's been
working on some stuff to make it less of a burden... stuff with
settingup/tearingdown db tables.

at work, where maintaining multiple databases just isn't an option, i
have my own lame methods for testing crud, usually involving a few
"delete" statements in the teardown function.  but that is admittedly
not the ideal way to do it.

i'm curious: how do other people test DAOs... and i'm not talking
about using mocks, but testing the real deal.

Was just working on this today, so this is what I did so far.

My "framework" (laughable term in my case, cuz it's hastily bolted
onto an existing CF 5-ish app) is in the works: I've been ripping
cfquery's out of the app and putting them into CRUD methods in DAO
CFCs.  And we also use Gateway CFCs for business logic in front of the
DAOs.

1. I added an isUnitTest boolean Argument to my Create, Update, and
Delete methods.  When isUnitTest is True, I use SQL Server syntax in
the cfquery call to do a TRANSACTION ROLLBACK after the normal INSERT,
UPDATE, or DELETE.  The example below seems to work fine, and cleans
up itself, but I'm not sure it's *right* way to do it:

<cffunction name="insertNotifier" access="package" output="false"
returntype="numeric">

                <!--- setup local structure --->
                <cfset Var local = StructNew()>

                <cfquery name="local.qryInsertNotifier"
datasource="#Variables.DSNs.primary#" result="local.rsInsertNotifier">

                        <!--- for unit testing --->
                        <cfif Arguments.isUnitTest>
                                BEGIN TRAN
                        </cfif>

                        INSERT INTO     Notifier
                                (
                                acct_id
                                ,       state_id
                                ,       county_id
                                ,       price_low
                                ,       price_high
                                ,       style
                                ,       acreage_low
                                ,       acreage_high
                                ,       email
                                ,       status
                                )
                        VALUES
                                (
                                <cfqueryparam value="#Arguments.Acct_ID#"
cfsqltype="cf_sql_integer">
                                ,       <cfqueryparam value="#Arguments.State_ID#"
cfsqltype="cf_sql_integer">
                                ,       <cfqueryparam value="#Arguments.County_ID#"
cfsqltype="cf_sql_integer">
                                ,       <cfqueryparam value="#Arguments.Price_Low#"
cfsqltype="cf_sql_integer">
                                ,       <cfqueryparam value="#Arguments.Price_High#"
cfsqltype="cf_sql_integer">
                                ,       <cfqueryparam value="#Arguments.Style#"
cfsqltype="cf_sql_varchar">
                                ,       <cfqueryparam value="#Arguments.Acreage_Low#"
cfsqltype="cf_sql_integer">
                                ,       <cfqueryparam value="#Arguments.Acreage_High#"
cfsqltype="cf_sql_integer">
                                ,       <cfqueryparam value="#Arguments.Email#"
cfsqltype="cf_sql_varchar">
                                ,       <cfqueryparam value="#Arguments.Status#"
cfsqltype="cf_sql_bit">
                                )

                        <!--- for unit testing --->
                        <cfif Arguments.isUnitTest>
                                ROLLBACK TRAN
                        </cfif>

                </cfquery>

                <cfif Arguments.isUnitTest>
                        <cfreturn 0>
                <cfelse>
                        <cfreturn local.rsInsertNotifier.IdentityCol>
                </cfif>

        </cffunction>

2. As a second way, I tried to call a Create method in the setUp() and
a Delete method on the same records in the tearDown()... it worked,
but unfortunately it appears setUp() and tearDown() each run once per
test method in each MXUnit test CFC.  For example, in my test CFC
called TestNotifierGateway.cfc, I had 3 test methods
(test_getNotifier, test_insertNotifier, and test_updateNotifier), and
noticed in cf debugging that the Create and Delete methods from the
setUp() and tearDown() each ran 3 times.  I'm not sure if that's a
feature or bug???  :-)

3. 3rd way: did nothing special in the setUp() or tearDown(), but
rather I just ran a Create method at the top of the test_getNotifier
function and the Delete method after all the unit tests in the same
test method.  Again, not sure if this is *right*, but it seems to do
the job: it gives me reproducible data that I can run Read() method
unit tests against *without* adding permanent data to my database.

In summary, really it looks like try #1 and #3 above compliment each
other.  With #1, I can run temp inserts, updates, and deletes that are
valid tests without ever actually committing any SQL transactions.
And with #3, I can insert data that's decoupled from data actually
stored in the dB, run my tests, and then throw away the data again.
This is an excellent discussion (one I've wanted to have for a long
time), so I'm anxious to hear what others (especially from folks with
JUnit/DBUnit testing experience) have to say on the matter.

Thanks,
Aaron

On Mar 20, 2:25 pm, "Marc Esher" <marc.es...@gmail.com> wrote:

- Aaron

On Mar 20, 2:25 pm, "Marc Esher" <marc.es...@gmail.com> wrote:

Meanwhile, I've also asked this question of folks whose opinions I
respect and I hope to hear some of their thoughts, too. When I do I'll
post them back here.

I can tell you that in general, I don't put transaction handling in
data access objects. In fact, I don't write a whole lot of crud daos,
personally. I'm not one of those "object per table" dudes yet.  If I
do any transaction handling, it's usually in a "service" layer or, if
I don't have one, simply in an act file (we're OLD school fuseboxers
where I work. and i do mean old school.).

for testing, I've generally followed two approaches. and to be clear,
what we're talking about here is how to best clean things up. this is
a different question from "how do i test that my inserts/updates
actually worked".  so, for me, i've cleaned up in 3 ways:

1) put transactions in the tests themselves.
2) in tearDown, put deletes for all tables I know i'm going to junk up
in the test. this is usually between 1 and 3 tables. rarely more. when
I do this, the question then is "how do you know what to delete".  the
answer is usually one of two approaches: for tables that have things
like "names", I'll use a common name, like "unittest". i'm not just
talking about the proverbial "users" table. I mean like "autoparts".
parts have catalog numbers. parts have names. so i'll use those
columns and my inserts will insert strings i know will only be used in
unit testing. doubtful there'd be an autopart named "unittest".
3) (really, this is an extension of #2), in the test, i'll keep an
array of inserted IDs. and then I'll populate those IDs in the unit
tests. and then i'll delete them in the tearDown:

    <cfcomponent extends="mxunit.framework.TestCase">

     <cfset a_IDs = ArrayNew()>

  <cffunction name="teardown">
      <cfquery name="q_delete" datasource="#dsn#">
     delete from blah
    where SomeID IN (ArrayToList(a_IDs))
   </cfquery>

  </cffunction>

     <cffunction name="testSomeInsert">
      <cfset var obj = createNewObject("unittest",1,false,whatever,boo)>
        <cfset  = dao.insert(obj)>

<!--- make sure i store the ID for deletion --->
  <cfset arrayAppend(a_IDs,obj.getID())>

   <!--- do tests here --->

     </cffunction>

</cfcomponent>

I've followed this approach a few times and it's worked OK.  I usually
use this when I don't have an easy delete hook to use, like a string
column where i can use a known unique string (like "unittest") as
described above.

All of these approaches, though, have their drawbacks, and I know that
a number of frameworks have popped up around solving these problems.
But when I look at dbunit.... I don't know. I haven't gone there yet.
Part of it is that where I work testing is the redheaded bucktoothed
gimp-legged boil-backed stepchild of all programming activities. I do
it in the shadows when noone is looking. So taking even more time to
learn how to do this stuff probably wouldn't be supported by anyone
above my pay grade (probably actively discouraged), particularly
considering how we always seem to be too busy to do this stuff. but
that's a topic for another day I'd say.

I do want to quickly add though that in all my junit testing, I've
followed the same approach as described above, but with one exception:
usually my java projects use much smaller databases (1 gig compared
with 10) with fewer tables (dozens, not hundreds) and so keeping a
"dev" version of the database just for testing is far more feasible,
so that's what I usually do. with gigantic databases, i haven't found
a good way yet to keep a version of the database around and properly
synched just for use with unit testing. sadly.

This is a great topic. I hope we hear more from folks who've been
testing a while.

Marc

1) the main problem i see with having a "isInUnitTest" arg that will
force a rollback in your inserts/updates is that you lose one method
of testing which is pretty common in dao testing, which is the
"insert/read/compare" method of testing:

<cfset obj1 = createNewObject(blah,blah2,blah3)>
<cfset dao.insert(obj1)>
<cfset obj2 = dao.read(obj1.getID())>
<!--- perform comparisons between obj1 and obj2 --->

although i can say that not for crud stuff, but for other larger
objects that did a whole bunch of bulk inserts and updates, i've done
exactly what you've described..

2) sometime in the future of mxunit, we'll be implementing some form
of metadata-driven tests. among the type of metadata would be
"rollback" and "expectedexception", for example:

<cffunction name="testSomething" mxunit_rollback="true">

    <cfset doSomeDaoStuff()>

</cffunction>

and then mxunit would perform the appropriate transactional stuff
before and after your test.

we're waiting on this until we take care of existing bugs, put out a
v1, get more documentation, and take a small breather. Plus, before we
do this, I want to be sure to talk to the framework smarties in the cf
community to get their take on proper ways of implementing
attribute-driven functionality. I want to be sure that when we do
this, we do it in such a way that we could scale out pretty nicely
with easy-to-add new attributes when necessary. I'm thinking this
would behave along the AOP lines, but I don't want to have to bundle a
full-blown AOP framework with mxunit if that can be avoided.  I
remember that a lot of folks bitched about cfcunit requiring mach-II
some time ago and I don't want us to fall into that trap.  anyway...
just so you know what we're thinking.

have a great easter all.

marc