[Zope] Strategies for testing generated sql?

[Paul Winkler]

This is a very general question: I'm looking for ideas on how to
effectively test dynamically generated sql queries.
Both unit and functional test ideas welcome.

We have a bunch of views (in the zope 3 / Five sense, but that's not
really relevant) which typically construct a Shared.DC.ZRDB.DA.nvSQL
instance, passing it a dictionary to use as the "namespace" mapping (I'm
simplifying, there's more indirection than that).

We have a bunch of tests for this stuff, but typically they just assert
that the generated sql query exactly matches some expected string. This
strikes me as only a minimal useful regression test when it passes, but
it suffers from a number of drawbacks:

* When a test fails, it can be very difficult to spot what changed.
  I'm getting really tired of staring at stuff like (contrived example,
  but some of my test failures actually look pretty close to this):

  AssertionError: "select aaaa, aabb, aacc, aadd, aaee, aaff, aagg,
  bbaa, bbbb, bbcc, bbdd, bbee, bbff, bbgg, ccaa, ccbb, cccc, ccdd, 
  ccee, ccff, ccgg, ddaa, ddbb, ddcc, dddd, ddee, ddff, ddgg, eeaa,
  eebb, eecc, eedd, eeee, eeff, eegg, ffaa, ffbb, ffcc, ffdd, ffee, 
  ffff, ffgg, ggaa, ggbb, ggcc, ggdd, ggee, ggff, gggg, case when 
  bbcc != '' then bbcc when aaddd != '' then aaddd end as bbcc from 
  blarftable b where (b.aacc = fred and b.aaee != joe) order by 
  aabb" != "select aaaa, aabb, aacc, aadd, aaee, aaff, aagg, bbaa, 
  bbbb, bbcc, bbdd, bbee, bbff, bbgg, ccaa, ccdd, cccc, ccbb, ccee, 
  ccff, ccgg, ddaa, ddbb, ddcc, dddd, ddee, ddff, ddgg, eeaa, eebb, 
  eecc, eedd, eeee, eeff, eegg, ffaa, ffbb, ffcc, ffdd, ffee, ffff, 
  ffgg, ggaa, ggbb, ggcc, ggdd, ggee, ggff, gggg, case when bbcc 
  != '' then bbcc when aaddd != '' then aaddd end as bbcc from 
  blarftable b where (b.aacc = fred and b.aaee != joe) order by aabb"

 Gee thanks!

* I think it would be a lot more useful to be able to reliably parse out
  and make assertions about some of the sub-clauses.
  So I could write tests that make assertions like:

   - this query is selecting columns a, b, c, d in that order.

   - the "where" clause should compare column X against value Y

   etc.
 
  I could of course do some ad-hoc stuff with string methods and
  maybe regexes, but I have to wonder if there's some sql-specific
  library that I could leverage. Googling suggests that
  there's a parser in gadfly (of course), and an example sql 
  parser in pyparsing.  Worth the effort or should I stick
  with simple string stuff?


Functionally, we are missing some important testables:

* we have no way to verify that these queries are syntactically correct sql.

* we have no way to verify that these queries behave as expected against
  a sample data set. 

How do people test this sort of thing? Do you go whole-hog and
fire up MySQL or whatever?  Or use gadfly? sqlite? or what? 

Are there other tools I should know about?


-- 

Paul Winkler
http://www.slinkp.com