[Dbix-class] Get generated SQL

[Matt Lawrence]

Emmanuel Quevillon wrote:
> Hi,
>
> Is there a simple way to get the automatic generated SQL from a =

> ResultSet? As for example $query =3D $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.


Matt

-------------- next part --------------
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.0000=
00000 +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(@_) }
+
 =3Dhead2 find
 =

 =3Dover 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:0=
7: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 =3D shift;
+    my $storage =3D $self->{storage};
+
+    my $sql_maker =3D $storage->sql_maker;
+    local $sql_maker->{for};
+
+    my @args =3D $storage->_select_args(@{$self->{args}});
+    return $storage->_prep_for_execute(@args[0 .. 2], [@args[4 .. $#args]]=
);
+}
+
 =3Dhead2 next
 =

 =3Dover 4
diff -ru DBIx-Class-0.08008/lib/DBIx/Class/Storage/DBI.pm DBIx-Class-0.0800=
8-mine/lib/DBIx/Class/Storage/DBI.pm
--- DBIx-Class-0.08008/lib/DBIx/Class/Storage/DBI.pm	2007-10-07 20:02:22.00=
0000000 +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) =3D @_;
 =

+  if( blessed($ident) && $ident->isa("DBIx::Class::ResultSource") ) {
+    $ident =3D $ident->from();
+  }
+
   my ($sql, @bind) =3D $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) =3D =
@_;
   =

-  if( blessed($ident) && $ident->isa("DBIx::Class::ResultSource") ) {
-    $ident =3D $ident->from();
-  }
-
   my ($sql, $bind) =3D $self->_prep_for_execute($op, $extra_bind, $ident, =
\@args);
 =

   $self->_query_start( $sql, @$bind );
@@ -1096,6 +1096,13 @@
 }
 =

 sub _select {
+  my $self =3D shift;
+  my $sql_maker =3D $self->sql_maker;
+  local $sql_maker->{for};
+  return $self->_execute($self->_select_args(@_));
+}
+
+sub _select_args {
   my ($self, $ident, $select, $condition, $attrs) =3D @_;
   my $order =3D $attrs->{order_by};
 =

@@ -1103,9 +1110,8 @@
     $order =3D $1 if $$condition =3D~ s/ORDER BY (.*)$//i;
   }
 =

-  my $for =3D delete $attrs->{for};
-  my $sql_maker =3D $self->sql_maker;
-  local $sql_maker->{for} =3D $for;
+  # This should always be localised in containing scope.
+  $self->sql_maker->{for} =3D delete $attrs->{for};
 =

   if (exists $attrs->{group_by} || $attrs->{having}) {
     $order =3D {
@@ -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