DBIx::FetchLoop - Fetch with change detection and aggregates
use DBIx::FetchLoop;
$lph = DBIx::FetchLoop->new($sth, $dbi_method);
$hash_ref = $lph->fetch_current_data;
$lph->set_aggregate($new_field, $field); $lph->reset_aggregate($new_field);
$lph->set_concatenate($new_field, $field); $lph->reset_concatenate($new_field);
$boolean = $lph->pre_loop($field); $boolean = $lph->post_loop($field);
$boolean = $lph->is_first; $boolean = $lph->is_last;
$count = $lph->count;
DBIx::FetchLoop is a supplemental approach for data retrieval with DBI. Result rows are queued with hash references to previous, current and next rows. Utility functions allow for simplified comparison of a field between previous and current or current and next rows. Additional functions allow you automatically create new fields for aggregating or concatenating based on fields in the resulting dataset.
Note: This module was created with ease of use and performance in mind. This module is intended to eliminate the need for temporary variables for loop detection as well as aggregation and concatenation. The reason that not all DBI methods for data retrieval are not implemented (such as selectall_arrayref) is that the modules design for performance would be defeated.
In essence you can write cleaner looking, more efficient code minus a few hassles.
DBIx::FetchLoop requires two arguements when creating an object: a dbi statement handle, and a scalar identifying the DBI data retrieval method to utilize. Supported DBI methods are: fetchrow_arrayref fetchrow_hashref
The module automatically handles calling the $sth->execute and $sth->finish functions of DBI, therefore you only need to create the statement handle and pass it along.
Instantiating an object would look like this:
use DBI; use DBIx::FetchLoop;
$dbh = DBI->connect($connect_string); $sth = $dbh->prepare($sql); $lph = DBIx::FetchLoop->new($sth,'fetchrow_hashref');
If $dbi_method is not supplied, the modules will default to using fetchrow_hashref.
$d = $lph->fetch_current_data;
$d is a hashref with elements to previous, current and next datasets as available.
eg (fetchrow_hashref)
$d->{previous}->{field}
$d->{current}->{field}
$d->{next}->{field}
eg (fetchrow_arrayref)
$d->{previous}->[1]
$d->{current}->[1]
$d->{next}->[1]
These functions exist to make the code necessary for detecting a new loop a little cleaner.
$lph->pre_loop($field); - compares $field between previous and current rows, returns true if different $lph->post_loop($field); - compares $field between current and next rows, returns true if different
$lph->is_first; - returns true if current record is first record $lph->is_last; - returns true if current record is last record
$lph->set_aggregate($new_field, $field); $lph->set_concatenate($new_field, $field);
These functions allow you to create new fields in the resulting dataset that are aggregates or concatenates of an original field in the data set. They must be called before the first time you call $lph->fetch_current_data.
$lph->reset_aggregate($new_field); $lph->reset_concatenate($new_field);
These functions reset the value of the specified field to undef in the current dataset. They can be called anytime during the running of the program.
$lph->count; - return the number of the current row returned (starts at 1)
use DBI; use DBIx::FetchLoop;
$dbh = DBI->connect(...);
$sth = $dbh->prepare('select company, department, bank_account, balance from account_table");
$lph = DBIx::FetchLoop->new($sth,'fetchrow_hashref');
$lph->set_aggregate('department_rollup','balance');
$lph->set_aggregate('company_rollup','balance');
while (my $d = $lph->fetch_current_data) {
if ($lph->pre_loop('company')) {
print "Company: " . $d->{current}->{company} . "\n";
}
if ($lph->pre_loop('department')) {
print "Department: " . $d->{current}->{department} . "\n";
}
print "Account: " . $d->{current}->{bank_account} . " : " . $d->{current}->{balance} . "\n";
if ($lph->post_loop('department')) {
print "Department Balance: " . $d->{current}->{department_rollup} . "\n";
$lph->reset_aggregate('department_rollup');
}
if ($lph->post_loop('company')) {
print "Company Balance: " . $d->{current}->{company_rollup} . "\n\n";
$lph->reset_aggregate('company_rollup');
}
}
$dbh->disconnect;
use DBI; use DBIx::FetchLoop;
$dbh = DBI->connect(...);
$sth = $dbh->prepare('select company, department, bank_account, balance from account_table");
$lph = DBIx::FetchLoop->new($sth,'fetchrow_arrayref');
$lph->set_aggregate(4,3); $lph->set_aggregate(5,3);
while (my $d = $lph->fetch_current_data) {
if ($lph->pre_loop(0)) {
print "Company: " . $d->{current}->[0] . "\n";
}
if ($lph->pre_loop(1)) {
print "Department: " . $d->{current}->[1] . "\n";
}
print "Account: " . $d->{current}->[2] . " : " . $d->{current}->[3] . "\n";
if ($lph->post_loop(1)) {
print "Department Balance: " . $d->{current}->[4] . "\n";
$lph->reset_aggregate(4);
}
if ($lph->post_loop(0)) {
print "Company Balance: " . $d->{current}->[5] . "\n\n";
$lph->reset_aggregate(5);
}
}
$dbh->disconnect;
use DBI; use DBIx::FetchLoop;
$dbh = DBI->connect(...);
$sth = $dbh->prepare('select news_group, message_header, message_part from news");
$lph = DBIx::FetchLoop->new($sth,'fetchrow_hashref');
$lph->set_concatenate('whole_message','message_part');
while (my $d = $lph->fetch_current_data) {
if ($lph->is_first) {
print "News Viewing App\n";
}
if ($lph->pre_loop('news_group')) {
print "Group: " . $d->{current}->{news_group} . "\n";
}
if (substr($d->{previous}->{message_header},4,10) ne substr($d->{current}->{message_header},4,10)) {
print "Title: " . substr($d->{current}->{message_header},4,10) . "\n";
print "Author: " . substr($d->{current}->{message_header},14,10) . "\n";
print "Result #" . $lph->count . "\n";
}
if (substr($d->{current}->{message_header},4,10) ne substr($d->{next}->{message_header},4,10)) {
print "Message: \n" . $d->{current}->{whole_message} . "\n\n";
$lph->reset_concatenate('whole_message');
}
if ($lph->is_last) {
print "All done\n";
}
}
$dbh->disconnect;
v.3 - added $lph->is_first, $lph->is_last, and $lph->count methods
v.2 - support of fetchrow_hashref and fetchrow_arrayref
v.1 - initial version
Spend more time on the documentation.
More in-depth examples (with comments)
Thanks to Tim Bunce for a lesson in the finer points of module naming. :)
Thanks to Ron M'Sadoques and Tom (d'Oh) Sullivan for listening to my ideas and constant promises to get around to writing this module.
Brendan L. Fagan <bits@csh.rit.edu>. Comments, bug reports, patches and flames are appreciated.