The problem is what you want to test. I'm not familiar with Zope 3,
therefore I'm talk on the base of ZSQLMethod.SQL.SQL from Zope 2.<br>
<br>
If I understand correctly, you've some high level construct which
creates an nvSQL instance. Now, if you put yourself at the end of the
line, what you need to do is to parse the output of nvSQL, in order to
see if the generated SQL is correct.<br>
<br>
What you're doing therefore is to test whether nvSQL is able to
generate correctly the SQL. But I think this is not what you want to
test. You could safely assume that, given the proper input, nvSQL will
generate the proper output.<br>
<br>
Therefore, what you should test is that the generated nvSQL has the appropriate characteristics.<br>
<br>
I will try to post a sample for Zope2, just to give an idea of what could be done.<br>
<br>
Regards<br>
Marco<br><br><div><span class="gmail_quote">On 4/12/06, <b class="gmail_sendername">Paul Winkler</b> <<a href="mailto:pw_lists@slinkp.com">pw_lists@slinkp.com</a>> wrote:</span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
This is a very general question: I'm looking for ideas on how to<br>effectively test dynamically generated sql queries.<br>Both unit and functional test ideas welcome.<br><br>We have a bunch of views (in the zope 3 / Five sense, but that's not
<br>really relevant) which typically construct a Shared.DC.ZRDB.DA.nvSQL<br>instance, passing it a dictionary to use as the "namespace" mapping (I'm<br>simplifying, there's more indirection than that).<br><br>We have a bunch of tests for this stuff, but typically they just assert
<br>that the generated sql query exactly matches some expected string. This<br>strikes me as only a minimal useful regression test when it passes, but<br>it suffers from a number of drawbacks:<br><br>* When a test fails, it can be very difficult to spot what changed.
<br> I'm getting really tired of staring at stuff like (contrived example,<br> but some of my test failures actually look pretty close to this):<br><br> AssertionError: "select aaaa, aabb, aacc, aadd, aaee, aaff, aagg,
<br> bbaa, bbbb, bbcc, bbdd, bbee, bbff, bbgg, ccaa, ccbb, cccc, ccdd,<br> ccee, ccff, ccgg, ddaa, ddbb, ddcc, dddd, ddee, ddff, ddgg, eeaa,<br> eebb, eecc, eedd, eeee, eeff, eegg, ffaa, ffbb, ffcc, ffdd, ffee,<br> ffff, ffgg, ggaa, ggbb, ggcc, ggdd, ggee, ggff, gggg, case when
<br> bbcc != '' then bbcc when aaddd != '' then aaddd end as bbcc from<br> blarftable b where (b.aacc = fred and b.aaee != joe) order by<br> aabb" != "select aaaa, aabb, aacc, aadd, aaee, aaff, aagg, bbaa,<br>
bbbb, bbcc, bbdd, bbee, bbff, bbgg, ccaa, ccdd, cccc, ccbb, ccee,<br> ccff, ccgg, ddaa, ddbb, ddcc, dddd, ddee, ddff, ddgg, eeaa, eebb,<br> eecc, eedd, eeee, eeff, eegg, ffaa, ffbb, ffcc, ffdd, ffee, ffff,<br> ffgg, ggaa, ggbb, ggcc, ggdd, ggee, ggff, gggg, case when bbcc
<br> != '' then bbcc when aaddd != '' then aaddd end as bbcc from<br> blarftable b where (b.aacc = fred and b.aaee != joe) order by aabb"<br><br> Gee thanks!<br><br>* I think it would be a lot more useful to be able to reliably parse out
<br> and make assertions about some of the sub-clauses.<br> So I could write tests that make assertions like:<br><br> - this query is selecting columns a, b, c, d in that order.<br><br> - the "where" clause should compare column X against value Y
<br><br> etc.<br><br> I could of course do some ad-hoc stuff with string methods and<br> maybe regexes, but I have to wonder if there's some sql-specific<br> library that I could leverage. Googling suggests that<br> there's a parser in gadfly (of course), and an example sql
<br> parser in pyparsing. Worth the effort or should I stick<br> with simple string stuff?<br><br><br>Functionally, we are missing some important testables:<br><br>* we have no way to verify that these queries are syntactically correct sql.
<br><br>* we have no way to verify that these queries behave as expected against<br> a sample data set.<br><br>How do people test this sort of thing? Do you go whole-hog and<br>fire up MySQL or whatever? Or use gadfly? sqlite? or what?
<br><br>Are there other tools I should know about?<br><br><br>--<br><br>Paul Winkler<br><a href="http://www.slinkp.com">http://www.slinkp.com</a><br>_______________________________________________<br>Zope maillist - <a href="mailto:Zope@zope.org">
Zope@zope.org</a><br><a href="http://mail.zope.org/mailman/listinfo/zope">http://mail.zope.org/mailman/listinfo/zope</a><br>** No cross posts or HTML encoding! **<br>(Related lists -<br> <a href="http://mail.zope.org/mailman/listinfo/zope-announce">
http://mail.zope.org/mailman/listinfo/zope-announce</a><br> <a href="http://mail.zope.org/mailman/listinfo/zope-dev">http://mail.zope.org/mailman/listinfo/zope-dev</a> )<br></blockquote></div><br><br clear="all"><br>-- <br>
Icube Srl<br><a href="http://www.icube.it/">http://www.icube.it/</a><br>