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, \@array;
                }
            }
        }
        $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/  
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.danga.com/pipermail/memcached/attachments/20070802/43c2d05c/attachment.htm