Rose::DBx::Object::InternalPager - Throttle Rose DB Iterator Fetching

    use Rose::DBx::Object::InternalPager;

    my $pager = Rose::DBx::Object::InternalPager->new(
        class_name      => "Namespace::Author",
        manager_method  => "get_authors",
        manager_options => { 
          query           => [ published => 'yes' ],
          require_objects => [ 'city_of_birth' ],
          sort_by         => 'last_name',
        },
    );

    while(my $author = $pager->next()) {
        print $author->first_name(), " ",
              $author->last_name(), " ",
              $author->city_of_birth->string(), "\n";
    }

Rose::DBx::Object::InternalPager is a 3rd party module for Rose::DB iterators to work around MySQL client's limited control over how many rows are fetched from the database at a time.

Rose::DBx::Object::InternalPager provides a hack to limit the number of fetched records and prevents programs from running out of memory.

The pager creates an iterator object, similar to the Rose Manager's get_xxx_iterator(), method. Except, behind the scenes, the pager makes sure to never fetch more than a preset number of records from the database at a time. To accomplish this, it uses LIMIT to limit the number of records retrieved, and OFFSET to fetch the next batch.

This approach might lead to anomalies when the database gets modified while the pager is at work, and this is the reason why this module has been released outside of the Rose::DB realm as a 3rd party module.

While normally, you would call

    my $itr = Namespace::Author::Manager->get_authors_iterator(...);

to get an iterator object which offers a next() method to move from one database record to the next. With Rose::DBx::Object::InternalPager, you call

    my $pager = Rose::DBx::Object::InternalPager->new(
        class_name     => "Namespace::Author", # Note: no 'Manager'
        manager_method => "get_authors",       # Note: no 'iterator'
        # ...
    );

which returns a pager object that can be used to iterate over all database records found via

    while(my $author = $pager->next()) {
        # ...     
    }

Just as the manager's get_xxx_iterator() method offers ways to modify the query with query, sort_by and other parameters, these parameters can be set with the pager by using the manager_options parameter:

    my $pager = Rose::DBx::Object::InternalPager->new(
        class_name      => "Namespace::Author", # Note: no 'Manager'
        manager_method  => "get_authors",       # Note: no 'iterator'
        manager_options => { 
          query           => [ published => 'yes' ],
          require_objects => [ 'city_of_birth' ],
          sort_by         => 'last_name',
        },
    );

By default, the pager fetches 50 records at a time. This value can be modified by setting the per_page parameter in the optional pager_options hash:

    my $pager = Rose::DBx::Object::InternalPager->new(
        # ...
        pager_options => {
          per_page => 100,
        },
    );

By default, the pager starts at page 1. This value can be modified by setting the start_page parameter in the optional pager_options hash:

    my $pager = Rose::DBx::Object::InternalPager->new(
        # ...
        pager_options => {
          start_page => 17,
        },
    );

Even with mysql_use_result set, I've found that with large database tables, clients run out of memory when they want to iterate over all records of a table. At the cost of eventually creating anomalies, the pager provides fine-grained control over the amount of memory used by the database client application.

Note that while this module uses Rose::DB, it was released and will be maintained separately from John Siracusa's project.

Copyright 2007 by Mike Schilli, all rights reserved. This program is free software, you can redistribute it and/or modify it under the same terms as Perl itself.

2007, Mike Schilli <m@perlmeister.com>