generic sql caching

[Earl Cahill]

I am pondering writing a simple little package and sticking it on CPAN that 
would try and do a little generic sql caching.  The topic seems to come up 
about once a week, and I think I have a pretty good solution, kind of gleaned 
from much discussion on the list over the years.  The code is at the end of 
this message.

You have sql with ?s as place holders.  You pass the sql and what will fill the 
place holders ($binds).  There is also a memcache_version_key which tries to 
keep a bit of state.  The memcache_version_key might look like

'mvk.product_id.15'

When data_fallback runs, it generates a memkey like so

my $memkey = 'df.' . ( $memcache->get($memcache_version_key) || 0 ) . '.' . 
md5_hex($sql) . '.' . md5_hex( join( ', ', @$binds ) );

if data_fallback can find anything based on that memkey, it returns it, 
otherwise, it queries and sets memcache.

This is not a panacea, as you need to know when certain queries have become 
invalid and invalidate the cache yourself, so when you know that the data for 
the product with id 15 changes, you do something like

$memcache->incr($memcache_version_key);

Anyway, I think it works well, and the paradigm has worked well in the past, 
though the code below is from last night.  The tactic is especially good for 
querying a table in many different ways.

There are also a couple niceties; like getting hash refs back, or making 
queries that return only one column into a simple array ref.

Ideas for a name are welcome.  It would be nice to have some simple CPAN code 
to point to when the discussion comes up again.  This strategy alleviated my 
need for namespaces, and might be a nice stopgap until tags are incorporated.

Thoughts?


Thanks,
Earl

use Carp qw(confess);
use Digest::MD5 qw(md5_hex);

sub data_fallback {
    my $hash = shift || confess 'need a hash';

    my $dbh   = $hash->{dbh}   || confess 'need a dbh';
    my $sql   = $hash->{sql}   || confess 'need some sql';
    my $binds = $hash->{binds} || confess 'need some binds';
    my $memcache_version_key = $hash->{memcache_version_key}
        || confess 'need a memcache_version_key';
    my $ttl      = $hash->{ttl}      || 3600;
    my $memcache = $hash->{memcache} || confess 'need a memcache object';
    my $as_hash_ref = $hash->{as_hash_ref};

    my $memkey = 'df.'
        . ( $memcache->get($memcache_version_key) || 0 ) . '.'
        . md5_hex($sql) . '.'
        . md5_hex( join( ', ', @$binds ) );

    my $data;
    if ( $data = $memcache->get($memkey) ) {
    } else {
        $data = [];

        my $sth = $dbh->prepare($sql);
        $sth->execute( @$binds );

        if ($as_hash_ref) {
            while ( my $hash_ref = $sth->fetchrow_hashref('NAME_lc') ) {
                push @$data, $hash_ref;
            }
        } else {
            while ( my @array = $sth->fetchrow_array ) {
                if ( scalar @array == 1 ) {
                    push @$data, $array[0];
                } else {
                    push @$data, [EMAIL PROTECTED];
                }
            }
        }
        $memcache->set( $memkey, $data, $ttl );
    }
    return $data;
}




       
____________________________________________________________________________________
Moody friends. Drama queens. Your life? Nope! - their life, your story. Play 
Sims Stories at Yahoo! Games.
http://sims.yahoo.com/