Skip Menu |
 

Preferred bug tracker

Please visit the preferred bug tracker to report your issue.

This queue is for tickets about the DBIx-Class-Helpers CPAN distribution.

Report information
The Basics
Id: 82110
Status: resolved
Priority: 0/
Queue: DBIx-Class-Helpers

People
Owner: Nobody in particular
Requestors: JDS [...] cpan.org
Cc:
AdminCc:

Bug Information
Severity: Normal
Broken in: 2.016003
Fixed in: (no value)



Download (untitled) / with headers
text/plain 655b
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
Download random.patch
text/x-diff 4.3k
--- 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;
Download (untitled) / with headers
text/plain 988b
On Wed Dec 19 13:40:16 2012, JDS wrote: Show quoted text
> 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 Show quoted text
> 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, Show quoted text
> 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. >
Good find! Thanks! I'll apply this patch tonight. I'll need to modify it as apparently you changed all the whitespace in the file and removed a comment that still applies (detecting the storage and even making storage based decisions in the resultset is ghetto.)


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.