This all started by looking at ways to refactor a method which was getting long enough in line count to be annoying.

My own design pattern has been to record all my queries in a configuration file so I can find them in one place and I can abstract the details away as a call to my $self->cfg->param("sql.abitrary_name") query definition.

Now I want the abstract additional detailsfrom my program flow, by auto-vivifying a statement handle into existence as I need it, and die'ing on an error if I have failed to define a corresponding query in the configuration file.

My intention is to create an object which can be instantiated to handle these details, as an sth attribute on a Moose powered object, so that this simply works without cluttering the code with all that query definition and preparation steps.

I see a factory to register each query as it is first used, plus a handful of methods to query and manage the registry of cache'd prepare'd statement handles, perhaps at a reserved _CACHE namespace on that same sth attribute (some of these design decisions are being made up in writing this).

My biggest stumbling block here is how to create an attribute for an arbitrary string.

So far, the code below, lifted from a distinct module which wants use DBI::Config::Cache::sth; but does not yet have one to use, gives some sense of what I have in mind, only I want to make these DBI statement handles be attributes of the $self->sth object, rather than hash keys of that object. At any rate, stripped of its error handling, this is where I start:

sub sth_registration_factory {
  my $self = shift;
  my $sth_name = shift;

  unless( $self->sth->{$sth_name} ){
    my $sql = $self->cfg->param("sql.sql_$sth_name");
    my $self->sth->{$sth_name} = $self->dbh->prepare( $sql );
    print "$sth_name : $sql \n\n" if $self->debug;
  }

  return;
}
[download]

Guidance on how to autovivify into existence an attribute with an arbitrary name as I need them would be appreciated. Thoughts on the utility or design of this idea are also welcome, particularly the cache management methods which I have not yet begun to consider. My own needs are that I cache these statement handles until my script is through doing its work in a few hours or days. But I can see how use in a persistent environments could easily challenge that conception of this module. Any ideas about what wheels I may be reinventing are also good to hear about.

Thanks folks.

OK, thinking about the cache. Seems I would want to be able to expire those as we leave scope on the actions which triggered their creation, even if they were actually instantiated in some three line method four calls deep into the stack. I would want to say that the scope of those statement handles is my main() routine, not the one where it was created. So that leaves me with additional questions about how to query the stack to determine when the cache'd handles ought to be expired as I leave scope on the logic which needs them?