On Thu, Sep 08, 2011 at 01:19:17PM +0100, Andrew Ford wrote:
> Hi Tim
>
> Sorry for mailing you directly, but I don't know whether my emails
> are getting through to the dbi-users mailing list,
[CC'd to dbi-users]
> and I would quite like to get this module onto CPAN and would like some
> feedback as to
> whether "the expert" thinks it is a bad idea. I think it would be a
> useful addition to the testing
> armoury, but then I am obviously biased.
The general idea is certainly useful. I'd like to generalise it though.
There's a conceptual similarity with PSGI/Plack middlewares that I'd
like to build on. Specifically, rather than creating a new transport,
I'd like to see DBD/Gofer/Transport/Base.pm extended to call one or more
'Gofer client middlewares' just before it calls transmit_request_by_transport().
The calling code could be something like:
+ my $mw = $self->middleware;
my $transmit_sub = sub {
my $response = eval {
...
- $self->transmit_request_by_transport($request)
+ $mw->($self, $request);
}
...
}
The middleware attribute would default to:
sub { shift->transmit_request_by_transport(@_) }
new middleware layer would be added by doing:
my $mw = $self->middleware;
$self->middleware( sub {
my ($self, $request) = @_;
...do something with $request...
my $response = $mw->($self, $request);
...do something with $response...
return $response;
} );
[This is all off-the-top-of-my-head, and I'm not very familar with Plack
internals so I may well be missing important issues.]
Then we just need a way to add middlewares via the environment.
I'd be delighted if you would work up a patch to add that to the DBI.
Your module could then use this mechanism. If you wanted to release it
to CPAN then a name like DBDx::GoferMiddleware::FOO would be good.
(Note the DBDx not DBIx since this is client-side. We may well end up
with server-side gofer middlewares as well.)
Tim.
> I have attached the current version of the module file with some
> documentation from my intial post
> included below (I've changed my mind about the attribute naming and
> currently am going for
> transform=modulename and method=methodname for the module/method that
> implements the transformation). I
> will of course add unit tests and everything that goes with a proper CPAN
> release, but I would be
> grateful for some quick feedback.
>
> Regards
> Andrew
>
> -------- Original Message --------
>
> Subject: Idea for a Gofer transport for translating SQL - primarily
> intended for testing
> Date: Thu, 08 Sep 2011 11:16:41 +0100
> From: Andrew Ford [1]<[email protected]>
> To: [2][email protected]
>
> I have a software system that I have to test that uses a very large
> database accessed exclusively with
> stored procedures. It is a pain setting up a test database for simple
> unit tests of the Perl code (I am
> not trying to test the stored procedures).
>
> What I want is to be able to do is set up a simple database (SQLite)
> quickly to use instead, but of
> course there is the issue of the stored procedures being database-specific
> - but then for testing
> purposes most of the stored procedures could be represented by simple SQL
> statements.
>
> What I have done as a proof of concept is to take the
> DBD::Gofer::Transport::null and create a new
> module that maps the stored procedures into simple SQL statements and then
> executes them in-process
> against the test database
>
> The code is pretty simple and included below. Currently it has a map of
> stored procedure
> transformations hard-coded in the code and assumes ODBC style stored
> procedure call syntax, but I was
> thinking of allowing a mapping module to be specified as an attribute in
> the DSN so that the DSN would
> be specified like:
>
>
> dbi:Gofer:transport=adaptor;mapper=My::Mapping::Module;dsn=dbi:SQLite:test.db
>
> Overriding the system's DSN then causes the system to use my test
> database, transparently doing the
> appropriate mappings.
>
> I would appreciate any comments, suggestions, and thoughts as to whether
> this would be a useful addition
> to CPAN.
>
> The immediate issue that I see is: what would the interface to the mapping
> module be (probably the
> module should provide a single function that takes a request object and
> either modifies it or returns a
> new request object).
>
> Andrew
>
> current proof-of-concept code:
>
> package DBD::Gofer::Transport::adaptor;
>
> use strict;
> use warnings;
>
> use DBI::Gofer::Execute;
>
> use base qw(DBD::Gofer::Transport::Base);
>
> __PACKAGE__->mk_accessors( qw( pending_response transmit_count ) );
>
> # Lookup table for stored procedure transformations
>
> my %stored_proc_transform
> = (
> fetch_user => 'select * from user where email = ?',
> # ...
> );
>
> my $executor = DBI::Gofer::Execute->new();
>
> sub transmit_request_by_transport {
> my ($self, $request) = @_;
>
> # Intercept 'do' and 'prepare' methods
>
> if ( exists $request->{dbh_method_call}
> and ref $request->{dbh_method_call} eq 'ARRAY'
> and @{$request->{dbh_method_call}} > 2
> and ( $request->{dbh_method_call}[1] eq 'do'
> or $request->{dbh_method_call}[1] eq 'prepare'))
> {
>
> # The SQL statement is held in the third element of the method
> call slot - check
> # for stored procedure calls and look and see if there is a
> transformation
> # defined. The transformation may be a simple string
> replacement or a code ref to
> # a function that performs more complex transformations.
>
> my $orig_sql = $request->{dbh_method_call}[2];
>
> if ($orig_sql =~ m{ ^ \s* [{] \s* call \s+ (\w+) \s* [\(] ( .* )
> [\)] \s* [}] \s* $ }isx) {
> my ($sp, $placeholders) = ($1, $2);
>
> my $xformed_sql = $stored_proc_transform{$sp};
> if (ref $xformed_sql eq 'CODE') {
> $xformed_sql = $xformed_sql->($request, $orig_sql, $sp,
> $placeholders);
> }
> $request->{dbh_method_call}->[2] = $xformed_sql;
> }
> }
>
> my $response = $executor->execute_request( $request );
>
> # put response 'on the shelf' ready for receive_response()
> $self->pending_response( $response );
>
> return undef;
> }
>
> sub receive_response_by_transport { return shift->pending_response; }
>
> 1;
>
> References
>
> Visible links
> 1. mailto:[email protected]
> 2. mailto:[email protected]
