[MO-java-dev] SQL validation

[Greg M. <gre...@ya...>]

I recently had a brief discussion with a couple of
developers on the c2 wiki
(http://c2.com/cgi/wiki?MockObject) regarding database
testing & MockObjects.  Here is an excerpt:

<excerpt>
I'm curious - for those of you out there who are using
MockObjects to test JDBC code, do you typically have
at least one unit test which uses a "real" database
connection as a santiy check? Or do you keep live
connections out of the unit testing altogether,
letting this type of testing fall under the umbrella
of "functional testing"? While I like the speed and
focus of MockObjects for database testing, certain
things about it make me nervous, e.g. SQL statements
are not validated. --GregMerrill 

I'm currently getting big into database mock objects
with VbUnitThree. I'm currently ignoring the issue of
testing the real database objects. But I know this is
a mistake; I'll have to come back and write unit tests
for them, just like any other class. -- JeffGrigg 

OK, it bit me today: My unit tests didn't test the
SQL, and so didn't catch a stray column. But that's
OK, because the end-to-end functional tests (done in
the same unit testing framework, only less often),
caught the problem. No blood; no foul. (Yes, I still
need to write unit tests for the SQL. I will, I
will... ;-) -- jtg 
</excerpt>

My question to this list is: would anyone be
interested in an optional SQL validation feature on
relevant mock sql objects
(MockStatement/MockPreparedStatement)?  Something
along the lines of setExpectValidSQL(boolean)?  When
true, this property would validate the syntax of SQL
statements on calls to executeUpdate() &
executeQuery().  Very handy to ensure that
dynamically-generated queries don't have syntax
problems.

I've created a prototype of such a feature, using a
parser generated with JavaCC and a contributed grammar
(http://www.cobase.cs.ucla.edu/pub/javacc/).  It seems
to work OK.  (The grammar is a little Oracle-specific,
but should handle standard SQL just fine.)

Do you think this kind of feature would fit well in
the mock objects core?  Or does it seem more like an
optional feature which should be released/maintained
separately?  

And the bottom line - does basic SQL validation have a
place in MockObject-style unit testing?  Or should
this type of responsibililty lie with functional
testing, since the SQL validation which is actually
done at runtime is database-dependent?

-Greg Merrill


__________________________________________________
Do You Yahoo!?
Find the one for you at Yahoo! Personals
http://personals.yahoo.com