[Zope] Strategies for testing generated sql?

[Dieter Maurer]

Paul Winkler wrote at 2006-4-12 12:32 -0400:
> ...
>Functionally, we are missing some important testables:
> ...
>* we have no way to verify that these queries behave as expected against
>  a sample data set. 

If you want to test this, then do it. It will also cover all
other test requirements (e.g. that the SQL is well formed).

Of course, you will need an RDBMS to perform the tests against.

>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? 

We do something like this for "Postgres".

The test setup assumes that a Postgres installation is running
and available for the test.

The test is executed against a special test database, copied on test
start from a template. After the test, the test database is deleted
again.

Drawbacks:
 
  *  there can not be more than a single test running in parallel.

     Parallel tests would cause Postgres to complain
     about already existing test databases

  *  deleting the test database was a nightmare.

     Ever and ever again, Postgres complained about
     a deletion trial while there were still some uses
     of the database: sometimes, some transactions have not
     been committed/aborted; sometimes Postgres connections were
     still open; sometimes, Postgres did not behave synchronously
     (the connection was closed, but some part of Postgres was not
     yet informed about the fact).

-- 
Dieter