Re: Fwd: Idea for a Gofer transport for translating SQL - primarily intended for testing

Hi Tim

I just wanted to touch base and let you know that I haven't forgotten 
about the middleware code.  I have been away and then busy at work, but 
work has involved using the middleware code and getting some feel for 
how it should behave.  I think the code in the nul transport I sent 
works pretty well and I will move it into DBD::Gofer.  What I am a bit 
more stalled on is writing some documentation for the feature and unit 
tests that fit in with the style of the main DBI unit tests.

The other thing that I have discovered is that as a middleware writer, I 
really feel the need for a way to simply create a result set.  What I 
have now is a create_result method in DBIx::GoferMiddleware::Base that 
can be called in one of the following ways:

     $self->create_response($dbh, $stmt, @params)
     $self->create_response($sth) # if the statement handle has been 
executed
     $self->create_response($sth, @params)
     $self->create_response( [ 'col1 varchar(255) not null',
                                              'col2 integer' ],
                                            [ 'catch...', 22 ],
                                            [ 'answer', 42 ] );

This covers most of my requirements (I have about 80 SQL Server stored 
procedures that I am mocking with Perl code that provides simple 
implementation of them using a SQLite database to stand in for the real 
db).  The code either comes up with a replacement statement that I want 
to return the results from (in which case I can either pass the SQL or a 
statement handle (from the underlying database) into the create_response 
method) or build up a data structure and I want to return that.

I also provide a create_error_response method:

     $self->create_error_response($rv, $errstr, $executed)

These two helper functions provide all that I have needed and hide the 
internals of DBI and DBD::Gofer.

My middleware module is project-specific, but essentially it looks for 
SQL statements like '{call sp(...)}' and sees whether a method "sp" is 
defined; if so it calls it with the transport and request objects, and 
SQL statement and parameters, and the method is expected to return 
either a replacement SQL statement (which is then executed with the 
original parameters) or a response object.

This has made unit testing a database-based application pretty quick as 
my test harness builds a SQLite database from a hacked copy of the real 
schema and loads test data as requested by the test scripts, then runs 
the tests with the middleware mapping the SQL.  Tests that took tens of 
minutes to setup and run (and which often didn't run due to the test 
data having been changed) now take a few seconds.

I'll send a patch for DBI when I've got it ready.

BTW I shall be at the London Perl Workshop (giving a talk on converting 
LaTeX books to EPUB with Perl).  I see that you are going to be at LPW - 
perhaps we could meet up there.

Rergards
Andrew

On 13/09/11 21:59, Andrew Ford wrote:
> On 12/09/11 20:46, Tim Bunce wrote:
>> On Sun, Sep 11, 2011 at 11:25:20PM +0100, Andrew Ford wrote:
>>> I suggest that we move this discussion to dbi-dev.
>> [dbi-users dropped]
>
>
>> But let's not go there just yet. There's lots to be done here first.
>
> OK - I am attaching DBD::Gofer::Transport::nul version 0.001_001.
> This implements the nul transport with the middleware handling built
> in to the transport layer.  I put it together as an interim solution
> firstly to test out the code, (from which I can then rip out the
> middleware loading code and throw it into DBD::Gofer for the proper
> solution), but also to provide something to work with straight away on
> a project I'm working on.
>
> Comments would be welcome - I will copy the code into the DBI tree
> shortly and send a patch for that.
>
> Andrew
>