[Dbix-class] Get generated SQL

[Matt S Trout]

On Tue, Feb 26, 2008 at 12:58:20PM +0000, Matt Lawrence wrote:
> Emmanuel Quevillon wrote:
> >Hi,
> >
> >Is there a simple way to get the automatic generated SQL from a 
> >ResultSet? As for example $query = $rs->sql()?
> >Thanks
> >
> I wrote a patch to achieve this a little while ago, but I didn't get 
> around to writing good enough tests.  Since someone else seems to want 
> the feature I may as well share it.
> 
> It's a patch against CPAN version 0.08008, which I haven't checked 
> against later versions. It doesn't cause any test failures on my system, 
> although I didn't run all the optional tests.
> 
> It provides a method called as_sql() on the cursor, which is proxied 
> from the resultset.

Interesting.

Have you considered writing some code to interpolate the bind parameters?

For copy+paste into an SQL CLI prompt it's a bit inconvenient to substitute
the ?s manually.

(we're currently rounding up for 0.8100, so if somebody gets a patch like
this together in the next week or so with tests you've a good chance of
it being applied)

> diff -ru DBIx-Class-0.08008/lib/DBIx/Class/ResultSet.pm DBIx-Class-0.08008-mine/lib/DBIx/Class/ResultSet.pm
> --- DBIx-Class-0.08008/lib/DBIx/Class/ResultSet.pm	2007-10-31 21:08:51.000000000 +0000
> +++ DBIx-Class-0.08008-mine/lib/DBIx/Class/ResultSet.pm	2008-02-04 18:31:02.000000000 +0000
> @@ -284,6 +284,8 @@
>    return $self->search(\$cond, $attrs);
>  }
>  
> +sub as_sql { shift->cursor->as_sql(@_) }
> +
>  =head2 find
>  
>  =over 4
> diff -ru DBIx-Class-0.08008/lib/DBIx/Class/Storage/DBI/Cursor.pm DBIx-Class-0.08008-mine/lib/DBIx/Class/Storage/DBI/Cursor.pm
> --- DBIx-Class-0.08008/lib/DBIx/Class/Storage/DBI/Cursor.pm	2007-08-11 22:07:58.000000000 +0100
> +++ DBIx-Class-0.08008-mine/lib/DBIx/Class/Storage/DBI/Cursor.pm	2008-02-04 18:32:03.000000000 +0000
> @@ -49,6 +49,17 @@
>    return bless ($new, $class);
>  }
>  
> +sub as_sql {
> +    my $self = shift;
> +    my $storage = $self->{storage};
> +
> +    my $sql_maker = $storage->sql_maker;
> +    local $sql_maker->{for};
> +
> +    my @args = $storage->_select_args(@{$self->{args}});
> +    return $storage->_prep_for_execute(@args[0 .. 2], [@args[4 .. $#args]]);
> +}
> +
>  =head2 next
>  
>  =over 4
> diff -ru DBIx-Class-0.08008/lib/DBIx/Class/Storage/DBI.pm DBIx-Class-0.08008-mine/lib/DBIx/Class/Storage/DBI.pm
> --- DBIx-Class-0.08008/lib/DBIx/Class/Storage/DBI.pm	2007-10-07 20:02:22.000000000 +0100
> +++ DBIx-Class-0.08008-mine/lib/DBIx/Class/Storage/DBI.pm	2008-01-09 14:28:31.000000000 +0000
> @@ -925,6 +925,10 @@
>  sub _prep_for_execute {
>    my ($self, $op, $extra_bind, $ident, $args) = @_;
>  
> +  if( blessed($ident) && $ident->isa("DBIx::Class::ResultSource") ) {
> +    $ident = $ident->from();
> +  }
> +
>    my ($sql, @bind) = $self->sql_maker->$op($ident, @$args);
>    unshift(@bind,
>      map { ref $_ eq 'ARRAY' ? $_ : [ '!!dummy', $_ ] } @$extra_bind)
> @@ -970,10 +974,6 @@
>  sub _dbh_execute {
>    my ($self, $dbh, $op, $extra_bind, $ident, $bind_attributes, @args) = @_;
>    
> -  if( blessed($ident) && $ident->isa("DBIx::Class::ResultSource") ) {
> -    $ident = $ident->from();
> -  }
> -
>    my ($sql, $bind) = $self->_prep_for_execute($op, $extra_bind, $ident, \@args);
>  
>    $self->_query_start( $sql, @$bind );
> @@ -1096,6 +1096,13 @@
>  }
>  
>  sub _select {
> +  my $self = shift;
> +  my $sql_maker = $self->sql_maker;
> +  local $sql_maker->{for};
> +  return $self->_execute($self->_select_args(@_));
> +}
> +
> +sub _select_args {
>    my ($self, $ident, $select, $condition, $attrs) = @_;
>    my $order = $attrs->{order_by};
>  
> @@ -1103,9 +1110,8 @@
>      $order = $1 if $$condition =~ s/ORDER BY (.*)$//i;
>    }
>  
> -  my $for = delete $attrs->{for};
> -  my $sql_maker = $self->sql_maker;
> -  local $sql_maker->{for} = $for;
> +  # This should always be localised in containing scope.
> +  $self->sql_maker->{for} = delete $attrs->{for};
>  
>    if (exists $attrs->{group_by} || $attrs->{having}) {
>      $order = {
> @@ -1125,7 +1131,7 @@
>      push @args, $attrs->{rows}, $attrs->{offset};
>    }
>  
> -  return $self->_execute(@args);
> +  return @args;
>  }
>  
>  sub source_bind_attributes {
> Only in DBIx-Class-0.08008-mine/t: 00sql.t

> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.rawmode.org

-- 
      Matt S Trout       Need help with your Catalyst or DBIx::Class project?
   Technical Director                    http://www.shadowcat.co.uk/catalyst/
 Shadowcat Systems Ltd.  Want a managed development or deployment platform?
http://chainsawblues.vox.com/            http://www.shadowcat.co.uk/servers/