I hope the new year finds everyone in good health and good cheer.
Attached are the docs for DBIx::Loop that Tim requested.
I hope this will be sufficient for the my namespace to be registered.
NAME
DBIx::Loop - (some more thought apparently required - suggestions welcome)
SYNOPSIS
use DBIx::Loop;
$lph = DBIx::Loop->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);
DESCRIPTION
DBIx::Loop 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.
METHODS
Instantiating a DBIx::Loop object:
DBIx::Loop 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::Loop;
$dbh = DBI->connect($connect_string);
$sth = $dbh->prepare($sql);
$lph = DBIx::Loop->new($sth,'fetchrow_hashref');
Retrieving data:
$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]
Conditional testing:
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
Data Utilities:
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->set_aggregate($new_field, $field);
$lph->set_concatenate($new_field, $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->reset_aggregate($new_field);
$lph->reset_concatenate($new_field);
EXAMPLES
Example 1 (fetchrow_hashref):
use DBI;
use DBIx::Loop;
$dbh = DBI->connect(...);
$sth = $dbh->prepare('select company, department, bank_account, balance from
account_table");
$lph = DBIx::Loop->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;
Example 2 (fetchrow_arrayref):
use DBI;
use DBIx::Loop;
$dbh = DBI->connect(...);
$sth = $dbh->prepare('select company, department, bank_account, balance from
account_table");
$lph = DBIx::Loop->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(0);
}
}
$dbh->disconnect;
Example 3 (concatenation and manual loop logic)
use DBI;
use DBIx::Loop;
$dbh = DBI->connect(...);
$sth = $dbh->prepare('select news_group, message_header, message_part from
news");
$lph = DBIx::Loop->new($sth,'fetchrow_hashref');
$lph->set_concatenate('whole_message','message_part');
while (my $d = $lph->fetch_current_data) {
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";
}
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');
}
}
$dbh->disconnect;
AUTHOR
Brendan L. Fagan <[EMAIL PROTECTED]>. Comments, bug reports, patches and flames are
appreciated.
