Patch for Class::DBI::MySQL to support LIMIT

[prev] [thread] [next] [Date index for 2004/07/27]

--Apple-Mail-6-986642574
Content-Transfer-Encoding: 7bit
Content-Type: text/plain;
	charset=US-ASCII;
	format=flowed

the patch says it all.

Ideally, perhaps _do_search inside the base Class::DBI needs to be a 
bit more flexible so we don't need to duplicate the whole function in 
subclasses.  If we could do something like pass in an extra bit of text 
that would get added to the query, that may be cool.  Still needs more 
thought, but that would allow this function to be reduced to 
(simplified):

sub _do_search {

if ( defined $search_opts->{limit} ) {

         if ( $search_opts->{limit} =~ m/^(\d+\s?,\s?)?\d+$/ ) {
             $frag .= " LIMIT $search_opts->{limit}"
         } else {
             $class->_croak("LIMIT can only be a number (10), or 2 
numbers separated by commas ( 1, 2 ), not '$search_opts->{limit}'.");
         }
     }
	
	return $SUPER->_do_search( original_params, $extra_juicy_bits );
}

Sorry, just thinking out loud.

ANyway, this patch has the new _do_search function and 10 new tests.

:)


--Apple-Mail-6-986642574
Content-Transfer-Encoding: 7bit
Content-Type: application/octet-stream;
	x-unix-mode=0644;
	name="class_dbi_mysql_limit.patch"
Content-Disposition: attachment;
	filename=class_dbi_mysql_limit.patch

Only in Class-DBI-mysql-0.23_jason/: blib
diff -r -u Class-DBI-mysql-0.23/lib/Class/DBI/mysql.pm Class-DBI-mysql-0.23_jason/lib/Class/DBI/mysql.pm
--- Class-DBI-mysql-0.23/lib/Class/DBI/mysql.pm	2004-02-28 04:19:47.000000000 -0600
+++ Class-DBI-mysql-0.23_jason/lib/Class/DBI/mysql.pm	2004-07-27 16:09:46.000000000 -0500
@@ -35,6 +35,7 @@
 
 =head1 METHODS
 
+        
 =head2 set_up_table
 
 	__PACKAGE__->set_up_table("table_name");
@@ -211,6 +212,70 @@
 	return $class->count_all(@_);
 }
 
+=head2 _do_search
+
+Overrides Class::DBI _do_search() method to provide for more MySQL specific features, such
+as LIMIT clauses.
+
+This function should NOT be called directly.  It is automatically called when
+you do $obj->search() or $obj->search_like().
+
+=cut
+
+sub _do_search {
+    my ($proto, $search_type, @args) = @_;
+    my $class = ref $proto || $proto;
+
+    @args = %{ $args[0] } if ref $args[0] eq "HASH";
+    my (@cols, @vals);
+    my $search_opts = @args % 2 ? pop @args : {};
+    while (my ($col, $val) = splice @args, 0, 2) {
+        my $column = $class->find_column($col)
+        || (List::Util::first { $_->accessor eq $col } $class->columns)
+        || $class->_croak("$col is not a column of $class");
+        push @cols, $column;
+        push @vals, $class->_deflated_column($column, $val);
+    }
+    
+    my $frag = join " AND ",
+    map defined($vals[$_]) ? "$cols[$_] $search_type ?" : "$cols[$_] IS NULL",
+        0 .. $#cols;
+    # ORDERING
+    $frag .= " ORDER BY $search_opts->{order_by}"
+        if $search_opts->{order_by};
+    
+    # LIMITING
+    if ( defined $search_opts->{limit} ) {
+        if ( $search_opts->{limit} =~ m/^(\d+\s?,\s?)?\d+$/ ) { 
+            $frag .= " LIMIT $search_opts->{limit}" 
+        } else {
+            $class->_croak("LIMIT can only be a number (10), or 2 numbers separated by commas ( 1, 2 ), not '$search_opts->{limit}'.");
+        }
+    }
+
+    return $class->sth_to_objects($class->sql_Retrieve($frag),
+        [ grep defined, @vals ]);
+    
+}
+
+=head2 search and search_like
+
+These functions are the same as they are Class::DBI proper, except they will
+also accept a limit clause in the search params, like so:
+
+        # This will return only the first 5 rows:
+        __PACKAGE__->search( artist => "The Beatles", { limit => 5 } );
+        
+        # This will return only 5 rows, starting from the 3rd row:
+        __PACKAGE__->search( artist => "The Beatles", { limit => "2, 5" } );
+
+        # can also be combined with order_by:
+        __PACKAGE__->search( artist => "The Beatles", 
+            { order_by => "album DESC", limit => "2, 5" } );
+
+=cut
+
+
 =head1 COPYRIGHT
 
 Copyright (C) 2001-2004 Tony Bowden. All rights reserved.
Only in Class-DBI-mysql-0.23_jason/: Makefile
Only in Class-DBI-mysql-0.23_jason/: Makefile.old
Only in Class-DBI-mysql-0.23_jason/: pm_to_blib
diff -r -u Class-DBI-mysql-0.23/t/mysql.t Class-DBI-mysql-0.23_jason/t/mysql.t
--- Class-DBI-mysql-0.23/t/mysql.t	2004-02-28 04:16:09.000000000 -0600
+++ Class-DBI-mysql-0.23_jason/t/mysql.t	2004-07-27 15:43:57.000000000 -0500
@@ -5,7 +5,7 @@
 
 my $HAVE_TP = eval { require Time::Piece::MySQL };
 
-plan tests => 11;
+plan tests => 21;
 
 use_ok "Class::DBI::mysql";
 
@@ -37,7 +37,7 @@
 __PACKAGE__->set_up_table;
 __PACKAGE__->autoinflate(dates => 'Time::Piece') if $HAVE_TP;
 
-END { __PACKAGE__->drop_table }
+# END { __PACKAGE__->drop_table }
 
 #-------------------------------------------------------------------------
 
@@ -80,4 +80,60 @@
 eval { Foo->enum_vals('mydate') };
 ok $@, $@;
 
+# Test overriden search methods
+my @searched_recs1 = Foo->search( name => "Full-text indexes" );
+is @searched_recs1, 1, "Overridden normal search works.";
+
+my @searched_recs1 = Foo->search_like( name => "support collections" );
+is @searched_recs1, 1, "Overridden normal search_like works.";
+
+my @limit_1         = Foo->search_like( name => "%", 
+    { order_by => "name ASC", limit => 1} );
+is @limit_1, 1, "Limit 1 returns proper number of rows";
+# should return 
+# +-------------------------------+
+# | name                          |
+# +-------------------------------+
+# | are called collections        |
+# | for full-text search          |
+# | Full-text indexes             |
+# | Full-text search in MySQL     |
+# | Function MATCH ... AGAINST()  |
+# | implements vector space model |
+# | is used to do a search        |
+# | MySQL has now support         |
+# | Only MyISAM tables            |
+# | support collections           |
+# +-------------------------------+
+
+
+my @limit_2         = Foo->search_like( name => "%", 
+    { order_by => "name ASC", limit => 2} );
+is @limit_2, 2, "Limit 2 returns proper number of rows";
+
+my @limit_3         = Foo->search_like( name => "%", 
+    { order_by => "name ASC", limit => "1, 3"} );
+is @limit_3, 3, "Limit 1, 3 returns proper number of rows";
+
+# should return 
+# +-------------------------------+
+# | name                          |
+# +-------------------------------+
+# | for full-text search          |
+# | Full-text indexes             |
+# | Full-text search in MySQL     |
+# +-------------------------------+
+
+is $limit_3[0]->Name, "for full-text search", "First row is correct";
+is $limit_3[1]->Name, "Full-text indexes", "Second row is correct";
+is $limit_3[2]->Name, "Full-text search in MySQL", "Third row is correct";
+
+my @limit_0         = Foo->search_like( name => "%", 
+    { order_by => "name ASC", limit => "0"} );
+is @limit_0, 0, "Limit 0 returns empty set";
+
+
+eval { Foo->search_like( name => "%", 
+    { order_by => "name ASC", limit => "z"} ); };
+ok $@, "Invalid Limit param croaks: $@";
 

--Apple-Mail-6-986642574
Content-Transfer-Encoding: 7bit
Content-Type: text/plain;
	charset=US-ASCII;
	format=flowed





-jason scott gessner
     jason@xxxxxxxx.xxx
--Apple-Mail-6-986642574--