[Dbix-class] Get generated SQL
Matt Lawrence
matt.lawrence at ymogen.net
Tue Mar 11 18:12:37 GMT 2008
Ash Berlin wrote:
>
> On 11 Mar 2008, at 16:39, Matt Lawrence wrote:
>
>> I found some time last night to work on this. I put the tests in a =
>> new file t/as_sql.t, they are pretty basic: just calling each new =
>> method once and checking the output is as expected
>>
>> There are now 2 cursor methods that are proxied from the resultset: =
>> as_sql and as_static_sql.
>>
>> as_sql leaves placeholders in the sql string and passes back the raw =
>> values that go into it, as_static_sql replaces the placeholders using =
>> the quote method from DBI, with optional data type mapping via =
>> arguments (either name-based or positional). This seems to me to be =
>> an acceptable compromise between fully-automatic data_type mapping =
>> (which seems at first glance to be non-trivial and usually =
>> unnecessary) and total disregard of data_type (which will bite Pg =
>> bytea columns et al)
>>
>> Comments?
>>
>> Matt
>
>
> Yes - where's the code?
Bah! Here.
-------------- next part --------------
diff -Nru 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-03-11 14:22:46=
.000000000 +0000
@@ -2107,6 +2107,35 @@
}
}
=
+=3Dhead2 as_sql
+
+ ($sql, @bind_values) =3D $rs->as_sql()
+
+Returns a representation of the resultset as a select query. Values for any
+placeholders in the query are passed back.
+
+If you want to execute the query, do not use this method. Use L</next> or
+L</all> for that.
+
+See L<DBIx::Class::Cursor/as_sql> for more information.
+
+=3Dhead2 as_static_sql
+
+ $sql =3D $rs->as_static_sql([ \%datatypes | \@datatypes ])
+
+Returns a representation of the resultset as a select query with all
+placeholders replaced with quoted values.
+
+If you want to execute the query, do not use this method. Use L</next> or
+L</all> for that.
+
+See L<DBIx::Class::Cursor/as_static_sql> for more information.
+
+=3Dcut
+
+sub as_sql { shift->cursor->as_sql(@_) }
+sub as_static_sql { shift->cursor->as_static_sql(@_) }
+
=3Dhead2 throw_exception
=
See L<DBIx::Class::Schema/throw_exception> for details.
diff -Nru DBIx-Class-0.08008/lib/DBIx/Class/Storage/DBI/Cursor.pm DBIx-Clas=
s-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-03-11=
15:24:10.000000000 +0000
@@ -49,6 +49,110 @@
return bless ($new, $class);
}
=
+=3Dhead2 as_sql
+
+ ($sql, @bind_values) =3D $cursor->as_sql([ \%datatypes ])
+
+Returns the query behind the cursor as a SQL select statement, with
+placeholders left intact and the relevant values passed back as an array.
+
+This method is intended for debugging and informational purposes only, it =
is
+not recommended that you use it to query the database.
+
+=3Dcut
+
+# Get SQL and raw bind data
+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]]=
);
+}
+
+sub as_sql {
+ my $self =3D shift;
+
+ my ($sql, $bind) =3D $self->_as_sql;
+
+ # Pass back the values only
+ my @bind_vals =3D map { $_->[1] } @$bind;
+
+ return ($sql, @bind_vals);
+}
+
+=3Dhead2 as_static_sql
+
+ $sql =3D $cursor->as_static_sql([ \%datatypes | \@datatypes ])
+
+Returns the SQL for the cursor as complete SQL, with placeholders replaced=
with
+relevant values, quoted by DBI's L<quote|DBI/quote> method.
+
+If a reference is passed as an argument it is used to ascertain the data t=
ype
+to pass to L<quote()|DBI/quote>. Array refs are passed in order for the
+positional parameters, hashrefs are mapped to columns by name.
+
+Warnings are issued if there is a mismatch between the number of placehold=
ers
+and the number of bind values.
+
+Passing this SQL to a database is not recommended, certainly not
+programmatically. It should be used for informational or debugging purposes
+only.
+
+=3Dcut
+
+sub as_static_sql {
+ my ($self, $datatypes) =3D @_;
+ my $storage =3D $self->{storage};
+
+ my ($sql, $bind) =3D $self->_as_sql;
+
+ if (ref $datatypes eq 'HASH') {
+ $datatypes =3D [ map { $datatypes->{$_->[0]} } @$bind ];
+ }
+ elsif (ref $datatypes ne 'ARRAY') {
+ $datatypes =3D [];
+ }
+
+ # Store quoted versions of the values
+ my @bind_vals =3D map {
+ $storage->dbh->quote($_->[1], shift @$datatypes)
+ } @$bind;
+
+ # Replace placeholders in the SQL string directly
+ # Are there any other possible placeholders other than '?'
+ $sql =3D~ s/(\?)/shift @bind_vals || $1/ge;
+
+ # Check for a mismatch in the number of placeholders
+ if (my $extra =3D $sql =3D~ y/\?/\?/) {
+ warn "$extra extra placeholder", $extra=3D=3D1?'':'s', " in SQL";
+ }
+ elsif (@bind_vals) {
+ warn @bind_vals." extra bind parameter", @bind_vals=3D=3D1?'':'s';
+ }
+
+ return $sql;
+}
+
+=3Dhead2 bind_names
+
+ @bind_names =3D $cursor->bind_names()
+
+Returns the names of the bind parameters in the query, to help with mapping
+data types to named columns in as_static_sql.
+
+=3Dcut
+
+sub bind_names {
+ my $self =3D shift;
+ my ($sql, $bind) =3D $self->_as_sql;
+ return map { $_->[0] } @$bind;
+}
+
=3Dhead2 next
=
=3Dover 4
diff -Nru DBIx-Class-0.08008/lib/DBIx/Class/Storage/DBI.pm DBIx-Class-0.080=
08-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 {
diff -Nru DBIx-Class-0.08008/t/as_sql.t DBIx-Class-0.08008-mine/t/as_sql.t
--- DBIx-Class-0.08008/t/as_sql.t 1970-01-01 01:00:00.000000000 +0100
+++ DBIx-Class-0.08008-mine/t/as_sql.t 2008-03-11 16:37:50.000000000 +0000
@@ -0,0 +1,47 @@
+use strict;
+use warnings;
+
+use Test::More tests =3D> 8;
+
+use lib qw( t/lib );
+use DBICTest;
+
+my $schema =3D DBICTest->init_schema;
+
+# BooksInLibrary has a default where clause with a placeholder
+my $rs =3D $schema->resultset('BooksInLibrary')->search({
+ owner =3D> 1,
+ title =3D> { '!=3D' =3D> undef }, # IS NOT NULL (no placeholder)
+});
+
+my ($sql, @bind) =3D eval { $rs->as_sql };
+ok(!$@, 'as_sql lives') or diag $@;
+
+like($sql, qr(^\s*select\s+)i, 'as_sql output starts with select');
+
+# owner and source should result in parameters, title should not
+# The order of parameters might vary
+my %bind =3D map { $_ =3D> 1 } @bind;
+is_deeply(\%bind, { 1 =3D> 1, 'Library' =3D> 1 }, 'bind_values as expected=
');
+
+$sql =3D eval { $rs->as_static_sql };
+ok(!$@, 'as_static_sql lives');
+like($sql, qr(^\s*select\s+)i, 'as_static_sql starts with select');
+like(
+ $sql, qr(\bsource\s*=3D\s*'Library'),
+ 'as_static_sql contains quoted source condition'
+);
+
+my @names =3D $rs->cursor->bind_names;
+is(@names, 2, 'Got 2 bind names');
+
+# hash for membership checking, order may not be predictable
+my %names =3D map { $_ =3D> 1 } @names;
+my %expect =3D map { $_ =3D> 1 } qw( owner source );
+is_deeply(\%names, \%expect, 'bind_names as expected');
+
+# TODO:
+ # test quoting different data types in as_static_sql
+ # Exception tests for mismatched query parameters - is this even possi=
ble?
+
+
More information about the DBIx-Class
mailing list