NAME

DBIx::Cache - transparent SQL SELECT caching in DBI programs via MLDBM::Sync

SYNOPSIS

 use DBIx::Cache qw(MLDM::Sync::SDBM_File); # or however you want to use MLDBM
 use DBIx::Cache qw(DB_File Storable); # or however you want to use MLDBM

 my $connect = 'dbi:Pg:dbname=mydb';

 my $dbh = DBIx::Cache->connect($connect,'postgres');

 { 
 
     my $sth = $dbh->prepare('select * from weather LIMIT 10');

     $sth->execute;

     while (my $row = $sth->fetchrow_arrayref) {
	 ;
     } 

     $sth->cache;

  }

 # later queries of the same execute and select will retrieve cached results
 
 $dbh->disconnect;

DESCRIPTION

This module is available only for exploratory usage only. It was developed as something to discuss caching execute results for the dbi-dev@perl.org list.

As stated in the DBI docs, DBI usage is quite stereotyped:

prepare,
  execute, fetch, fetch, ...
  execute, fetch, fetch, ...
  execute, fetch, fetch, ...

What DBIx::Cache adds is this

prepare,
  execute, fetch, fetch, ...  cache
  execute, fetch (from cache or db), fetch, ...  (cache if not cached)
  execute, fetch (from cache or db), fetch, ...  (cache if not cached)

Now, the only problem is that plain DBI outstrips my module in every case I could create where it shouldn't...

It seems that pulling things off a Berkeley DB file is as slow as having the database formulate the query.

USAGE

The first step is to use DBIx::Cache ($cache_type). This will automatically load in DBI and it will also load in MLDBM::Sync with the $cache_type that you specify. E.g:

use DBIx::Cache qw(MLDM::Sync::SDBM_File);

the argument to the use is anything which MLDBM::Sync takes.

Next you just use DBI as normal and via various DBI::Cache $sth and $dbh subclasses, your prepared SQL selects and fetches are automatically stored. Actual caching to store is done via an explicit $sth-cache> statement.

TRYING IT OUT

There are two options, use the enclosed scripts to create a dummy table or drop in your sql statement and alter the connect strings.

using the enclosed scripts

1 create a database.

The file postgresql.sh contains a Postgresql command to do this

2 create a table

The file postgresql.sql contains SQL to do this

3 populate the table

The file i.pl contains a Perl/DBI program to do this on a Postgresql database. It creates a whole bunch of dummy data for the table.

dropping in your own heavy SQL

in this case, follow the instructions in Makefile.PL. It's easy to do this as well.

AUTHOR

T. M. Brannon, <tbone@cpan.org>

TO DO

Make it run faster than plain DBI so that it is of use. The only way I might outdo DBI is if the database were overloaded or if the database did not cache query results.

SEE ALSO

dbi-dev@perl.org