The rand() method of the current module does not support all
DBIx::Class::Storage types related to common DBMSs, I noticed this in a
case where I used a connection to an Oracle database and the resulting
storage type was 'DBIx::Class::Storage::DBI::Oracle::Generic'.
rand() did not know about this storage type and therefore tried to use the
default RAND() function in Oracle PL/SQL which caused an exception (it
should have been dbms_random.value instead).
I have attached a patch. I tried to single out the relevant storage types,
but as I am not familiar with all of them it may be that more need to be
added or some may need to be removed.
| Subject: | random.patch |
--- Random_orig.pm 2012-12-19 18:47:09.998331738 +0100
+++ Random.pm 2012-12-19 18:47:17.334331975 +0100
@@ -1,6 +1,6 @@
package DBIx::Class::Helper::ResultSet::Random;
{
- $DBIx::Class::Helper::ResultSet::Random::VERSION = '2.016003';
+ $DBIx::Class::Helper::ResultSet::Random::VERSION = '2.016003';
}
use strict;
@@ -8,35 +8,68 @@
# ABSTRACT: Get random rows from a ResultSet
-# this is ghetto
my %rand_order_by = (
- 'DBIx::Class::Storage::DBI::SQLite' => 'RANDOM()',
- 'DBIx::Class::Storage::DBI::mysql' => 'RAND()',
- 'DBIx::Class::Storage::DBI::ODBC::Microsoft_SQL_Server' => 'NEWID()',
- 'DBIx::Class::Storage::DBI::MSSQL' => 'NEWID()',
- 'DBIx::Class::Storage::DBI::Pg' => 'RANDOM()',
- 'DBIx::Class::Storage::DBI::Oracle' => 'dbms_random.value',
+ 'DBIx::Class::Storage::DBI::Sybase::MSSQL' => 'NEWID()',
+ 'DBIx::Class::Storage::DBI::Sybase::Microsoft_SQL_Server::NoBindVars' =>
+ 'NEWID()',
+ 'DBIx::Class::Storage::DBI::Sybase::Microsoft_SQL_Server' => 'NEWID()',
+ 'DBIx::Class::Storage::DBI::Sybase::ASE::NoBindVars' => 'RAND()',
+ 'DBIx::Class::Storage::DBI::Sybase::ASE' => 'RAND()',
+ 'DBIx::Class::Storage::DBI::Sybase' => 'RAND()',
+ 'DBIx::Class::Storage::DBI::SQLite' => 'RANDOM()',
+ 'DBIx::Class::Storage::DBI::SQLAnywhere' => 'RAND()',
+ 'DBIx::Class::Storage::DBI::Pg' => 'RANDOM()',
+ 'DBIx::Class::Storage::DBI::Oracle::WhereJoins' => 'dbms_random.value',
+ 'DBIx::Class::Storage::DBI::Oracle::Generic' => 'dbms_random.value',
+ 'DBIx::Class::Storage::DBI::Oracle' => 'dbms_random.value',
+ 'DBIx::Class::Storage::DBI::ODBC::SQL_Anywhere' => 'RAND()',
+ 'DBIx::Class::Storage::DBI::ODBC::Microsoft_SQL_Server' => 'NEWID()',
+ 'DBIx::Class::Storage::DBI::ODBC::Firebird' => 'RAND()',
+ 'DBIx::Class::Storage::DBI::ODBC::ACCESS' => 'RND()',
+ 'DBIx::Class::Storage::DBI::mysql::backup' => 'RAND()',
+ 'DBIx::Class::Storage::DBI::mysql' => 'RAND()',
+ 'DBIx::Class::Storage::DBI::MSSQL' => 'NEWID()',
+ 'DBIx::Class::Storage::DBI::InterBase' => 'RAND()',
+ 'DBIx::Class::Storage::DBI::Firebird::Common' => 'RAND()',
+ 'DBIx::Class::Storage::DBI::Firebird' => 'RAND()',
+ 'DBIx::Class::Storage::DBI::DB2' => 'RAND()',
+ 'DBIx::Class::Storage::DBI::ADO::MS_Jet' => 'RND()',
+ 'DBIx::Class::Storage::DBI::ADO::Microsoft_SQL_Server' => 'NEWID()',
+ 'DBIx::Class::Storage::DBI::ACCESS' => 'RND()',
);
-sub _rand_order_by {
- my $self = shift;
- $self->result_source->storage->_determine_driver;
- return $rand_order_by{ref $self->result_source->storage} || 'RAND()';
+{
+ #sort keys descending to handle more specific storage classes first
+ #(right now it does not make a difference though)
+ my @keys_rand_order_by = sort { $b cmp $a } keys %rand_order_by;
+
+ sub _rand_order_by {
+ my $self = shift;
+ $self->result_source->storage->_determine_driver;
+ my $storage = $self->result_source->storage;
+
+ foreach my $dbms (@keys_rand_order_by) {
+ return $rand_order_by{$dbms} if $storage->isa($dbms);
+ }
+
+ return 'RAND()';
+ }
+
}
sub rand {
- my $self = shift;
- my $amount = shift || 1;
+ my $self = shift;
+ my $amount = shift || 1;
- $self->throw_exception('rand can only return a positive amount of rows')
+ $self->throw_exception('rand can only return a positive amount of rows')
unless $amount > 0;
- $self->throw_exception('rand can only return an integer amount of rows')
+ $self->throw_exception('rand can only return an integer amount of rows')
unless $amount == int $amount;
- my $order_by = $self->_rand_order_by;
+ my $order_by = $self->_rand_order_by;
- return $self->search(undef, { rows=> $amount, order_by => \$order_by});
+ return $self->search( undef, { rows => $amount, order_by => \$order_by } );
}
1;