NAME

StoredHash - Minimalistic, yet fairly complete DBI Persister

DESCRIPTION

Allow DB Persistence operations (insert(), load(), update(), delete(), exists()) on a plain old hash (unblessed or blessed) without writing classes, persistence code or SQL.

SYNOPSIS

   use StoredHash;
   use DBI;
   use Data::Dumper;
   
   my $dbh = DBI->connect("dbi:SQLite:dbname=zoo.db");
   # Lightweight demonstration of StoredHash in action (with SQLite)
   $dbh->do("CREATE TABLE animals (speciesid INTEGER NOT NULL PRIMARY KEY, name CHAR(16), limbcnt INTEGER, family CHAR(16))");
   my $shp = StoredHash->new('table' => 'animals', 'pkey' => ['speciesid'],
      'autoid' => 1, 'dbh' => $dbh, 'debug' => 0);
   # Hash object to be stored
   my $monkey = {'name' => 'Common Monkey', 'limbcnt' => 5, 'family' => 'mammal',};
   
   # Happens to return numeric id (because of auto-increment pkey / autoid)
   my $spid = $shp->insert($monkey);
   print("Created $spid\n");
   # Load entry
   my $ent = $shp->load([$spid]);
   print("Fetched: ".Dumper($ent)."\n");
   # Get: {'name' => 'Common Monkey', 'speciesid' => 469, 'limbcnt' => 5,'family' => 'mammal',}
   # Fix error in entry (don't count tail to be limb)
   $ent->{'limbcnt'} = 4;
   # Update (with some redundant attributes that do not change)
   print("Update $ent->{'speciesid'}\n");
   $shp->update($ent, [$ent->{'speciesid'}]);
   # Could reduce / optimize change to bare minimum:
   my %change = ('limbcnt' => 4);
   print("Reduce property value on $spid\n");
   $shp->update(\%change, [$spid]);
   # Later (species dies extinct ?)
   #$shp->delete($spid);
   
   # Test if we need to insert / update (based on presence in DB)
   my $id = 5987;
   my $invals = {'name' => 'Crow', 'limbcnt' => 4, 'family' => 'birds'};
   print("Test Presence of Animal '$id'\n");
   if ($shp->exists($id)) {$shp->update($invals, [$id]);}
   else {$shp->insert($invals);}
   
   ##### Easy loading of sets / collections
   # Load all the animals
   my $animarr = $shp->loadset();
   print("All Animals: ".Dumper($animarr)."\n");
   # Load only mammals
   my $mammarr = $shp->loadset({'family' => 'mammal'});
   print("Mammals: ".Dumper($mammarr)."\n");

METHODS

$p = StoredHash->new(%opt);

Create New instance of StoredHash Persister. Options in %opt must have 1) pkey/idattr - array (ref) to reflect the identifying attrtibute(s) of entry (single attr for numeric ids, multiple for composite key) Optional attributes 1) dbh - DBI connection to database. Not passing 'dbh' makes methods insert/update/load/delete return the SQL query only (as a string)

$p->errstr($v);

Access error string that method may leave to object. Notice that many methods throw exception (by die()) with error message rather than leave it within object.

$p->insert($e);

Store entry %$e (hash) inserting it as a new entry to a database. Connection has been passed previously in construction of persister. The table / schema to store to is either the one passed at construction or derived from perl "blessing" of entry ($e). Returns (ref to) an array of ID values for the entry that got stored (array of one element for numeric primary key, multiple for composite key).

$p->update($e,$idvals);

Update an existing entry in the database with values in %$e (hash). Provide protection for AUTO-ID (to not be changed) ? For flexibility the $idvals may be hash or array (reference) with hash containing (all) id keys and id values or alternatively array containing id values IN THE SAME ORDER as keys were passed during construction (with idattr/pkey parameter).

$p->delete($e);

Delete an entry from database by passing $e as one of the following ) hash %$e - a hash containing (all) primary key(s) and their values. 1) scalar $e - Entry ID for entry to be deleted 2) array @$e - One or many primary key values for entry to be deleted The recommended use is caae "array" as it is most versatile and most consistent with other API methods.

$p->exists($ids);

Test if an entry exists in the DB table with ID values passed in @$ids (array). Returns 1 (entry exists) or 0 (does not exist) under normal conditions.

$p->load($ids);

Load entry from DB table by its IDs passed in @$ids (array, single id typical sequece autoid pkey, multiple for composite primary key). Entry will be loaded from single table passed at construction (never as result of join from multiple tables). Return entry as a hash (ref).

$p->loadset($h,$sort);

Load a set of Entries from persistent storage. Optionally provide simple "where filter hash" ($h), whose key-value criteria is ANDed together to form the filter. Return set / collection of entries as array of hashes.

$p->cols();

Sample Column names from (current) DB table. Return (ref to) array with field names in it.

$p->pkeyvals($e);

Return Primary key values (as real array) from hash %$e passed as parameter. undef values are produced for non-existing keys. Mostly used for internal operations (and maybe debugging).

$p->whereid();

Generate SQL WHERE Clause for UPDATE based on primary keys of current type. Return WHERE clause with id-attribute(s) and placeholder(s) (idkey = ?, ...), without the WHERE keyword.

StoredHash::wherefilter($e,%c);

Generate simple WHERE filter by hash %$e. The keys are assumed to be attributes of DB and values are embedded as values into SQL (as opposed to using placeholers). To be perfect in escaping per attribute type info would be needed. For now we do best effort heuristics (attr val \d+ is assumed to be a numeric field in SQL, however 000002345 could actually be content of a char/text/varchar field). Return WHERE filter clause without WHERE keyword.

SEE ALSO

Perl Database Interface DBI, Drivers for connecting to possible DB backends: DBD::SQLite, DBD::mysql

COPYRIGHT

Copyright 2009 Olli Hollmen (olli.hollmen@gmail.com)

This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.