At work we have a whole infrastructure built mainly around CDBI for what regards db interface. I'm fairly new to it, but with the help of the developers here I'm managing my way through it.

I'm doing some statistics, generally involving joins, and I wrote a generic base class Newstats which accepts, as "main" parameter a subref to be used as a callback. Then I subclass it so that the typical module for a specific statistic may look like this:

# -*- Perl -*-

use strict;
use warnings;

package Newstats::Fred_by_this;

use Foo::DBI::Fred;
use base 'Newstats';

Foo::DBI::Fred->set_sql(by_this => <<".EOSQL");
SELECT this,nvl(that(*),0) as tot
FROM __TABLE(=r)__, __TABLE(Foo::DBI::Barney=n)__
WHERE status='cool'
AND r.quux=n.quux
AND (tstamp between ? AND ?)
GROUP BY this
.EOSQL

sub new {
    my $rf=Foo::DBI::Fred->sql_by_this;
    Newstats->new( action => sub {
        my $caller=shift;
        $rf->execute(@_) or
          $caller->complain("Error executing sql_by_this() method.");
        $rf->fetchall_arrayref;
    },
    other_param => 'something else' );
}


1;

__END__
[download]

(code trimmed down to a bare minimum and names changed for convenience)