package Subscriber::DB;

use base Subscriber;

use DBI;
use Carp 'croak';
use strict;
use warnings;

use subs qw(
  get_address get_anniversary get_longemail
  get_birthday get_street get_greeting
);

use constant FIELDS => qw(
  _id  _salutation  _firstname  _lastname  _company
  _street1  _street2  _city  _state  _zipcode
  _country  _phone  _email  _birthday  _anniversary
);

my $dbh = DBI->connect('DSN', 'USER', 'PASS');

my %fields = (
  greeting => [qw( _salutation _firstname _lastname )],
  longemail => [qw( _email _firstname _lastname )],
);

my %sql;

my $new_record = $dbh->prepare(q{
  INSERT INTO subscribers
  VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
});

my $check_id = $dbh->prepare(q{
  SELECT 1 FROM subscribers WHERE id = ?
});


sub new {
  my ($class, %args) = @_;
  $new_record->execute(@args{FIELDS()});
  bless { _id => $new_record->{mysql_insertid} }, $class;
}


sub new_from_id {
  my ($class, $id) = @_;
  croak "non-existent id ($id)" unless $check_id->execute($id) == 1;
  bless { _id => $id }, $class;
}


AUTOLOAD {
  our $AUTOLOAD;
  my $self = $_[0];
  (my $meth = $AUTOLOAD) =~ s/.*:://;
  my $super = "SUPER::$meth";

  if ($meth =~ /^get_(\w+)/ and $fields{$1}) {
    my $what = $1;
    my @f = @{ $fields{$what} };
    my $sql_f = join ", ", map substr($_, 1), @f;
    $sql{$what} = $dbh->prepare(qq{
      SELECT $sql_f FROM subscribers WHERE id = ?
    });

    no strict 'refs';
    *$AUTOLOAD = sub {
      my $s = shift;
      $sql{$what}->execute($s->get_id);
      $sql{$what}->bind_columns(\@$s{@f});
      $sql{$what}->fetch;
      $s->$super(@_);
    };
    goto &$AUTOLOAD;
  }

  shift->$super(@_);
}

1;
[download]

There are three bits of trickery in this module that I particularly like:

Intercepting method calls to a parent class by declaring (but not defining) methods with the same name. That's a bit wordy, so let me explain. If Horse inherits from Animal, and Animal provides a method make_sound(), and you want to handle make_sound() as an AUTOLOADed method in the Horse class, you can't just write an AUTOLOAD in Horse and expect results. This is because Perl looks through your parent classes before it looks at your AUTOLOAD (and reasonably so!).

Therefore, declare the method, but don't define it. You can do that via sub method_name; or with the subs pragma (as I do in the code). The declaration means that Perl will try calling your method, and when it turns out to be undefined, it will run AUTOLOAD.

Binding DBI results to hash keys mimicking an object. In my code, I use the %fields hash to determine exactly what fields need to be updated. One could get away with updating the entire object each time, but if you know exactly what fields are necessary for each method, you can cut down a bit on unnecessary data fetching and copying.

Passing AUTOLOAD control to the parent. This is similar to Damian Conway's NEXT, I suppose, but I don't want to add a dependency to my code that isn't necessary. Basically, if the child class's AUTOLOAD can't dispatch the method, we tack "SUPER::" onto the beginning of the method name, and call that. It's not much of a hack, but it's nifty.