NAME
DBIx::TableHash - Tie a hash to a mysql table + SQL utils
SYNOPSIS
use DBIx::TableHash;
my $DBHash = DBIx::TableHash->create_or_die
(my $Params =
{
DBIDriver => 'mysql',
Database => 'mydatabase',
HostName => 'localhost',
Port => undef,
Login => '',
Password => '',
TableName => 'SalesPeople',
KeyField => 'FullName',
## For multi-key lookup:
FixedKeys => {AreaCode => 415,
StatusCode => 'Active',
RecordType => 'Primary'},
## To retrieve a single value:
ValueField => 'PhoneNumber',
## ... or for "multi-value" retrieval...
ValueField => undef,
## ... optionally specifying...
RetrieveFields => [qw(Title Territory Quota)],
## For caching:
CacheMode => 'CacheBeforeIterate'
## or...
CacheMode => 'CacheOneTime'
## or...
CacheMode => 'CacheNone'
}
);
my %DBHash; tie(%DBHash, 'DBIx::TableHash', $Params);
my $DBHash = DBIx::TableHash->create($Params) or die "Help!";
my $DBHash = DBIx::TableHash->create_or_die($Params);
my $DBHash = DBIx::TableHash->create_copy($Params) or die "Help!";
my $DBHash = DBIx::TableHash->create_copy_or_die($Params);OVERVIEW
All parameters are passed via a single anonymous hash.
All parameters are optional, but you'll almost always need to specify Database, TableName, and KeyField.
Omitting ValueField puts the hash in "multi-value" mode, where you store/retrieve a hash of fields/values instead of a single value. In "multi-value" mode all fields in each record are retrieved on every fetch; RetrieveFields limits fields retrieved to a specified list.
Specifying FixedKeys puts the hash in "multi-key" mode, in which only a subset of the database table, corresopnding to records that match the spec in FixedKeys, is operated on.
Cache modes reduce querying, but lose synchronization and hog memory.
The object is designed to be easy subclass. Try making a subclass that sets defaults for all or most of the parameters, so the caller doesn't have to supply any at instantiation time.
"create_copy" methods efficiently create and return potentially huge untied hash "snapshot" of the same data that would have been retrieved by the corresponding tied hash.
DETAILS
The DBHash object is designed to tie a hash to a table or a subset of records in a table in a DBI database (only tested with mysql in the current version, but expected to work with any vendor).
If the table only has a single KeyField, which this modules assumes to be a unique key field in the table, then the hash keys are stored and retrieved in that field, and the values are saved in and returned from the ValueField. Records are automatically created and deleted as the hash is used like any other hash. (If the table is read-only, be sure not to try to store into the tied hash!)
To access only a subset of the records in a table, you may specify hash of "FixedKeys", which is a hash mapping OTHER field names to fixed values that those fields must have for all lookups, updates, and inserts done via the hash interface. This sets up a virtual hash corresponding to a subset of the table where N key fields are fixed at given values and a different key field may vary.
There are several ways to use this module.
Quick and dirty mode (single-key, single-value) using tie:
use DBIx::TableHash;
my %PhoneNumbers;
tie (%PhoneNumbers, 'DBIx::TableHash',
{Database => 'mydatabase',
TableName => 'SalesPeople',
KeyField => 'FullName',
ValueField => 'PhoneNumber'})
or die "Failed to connect to database";Then you can use %PhoneNumbers like any hash mapping FullName to PhoneNumber. Any retrieval of data results in corresponding SQL queries being made to the database. Modifying the hash modifies the database.
Even quicker mode using create():
For convenience, you can use the create() class method to do the tying for you. It creates an anonymous hash, ties it and, returns it. It takes the same parameters as new() and tie().
use DBIx::TableHash;
my $PhoneNumbers = DBIx::TableHash->create(......)
or die "Failed to connect to database";Quicker still using create_or_die():
use DBIx::TableHash;
my $PhoneNumbers = DBIx::TableHash->create_or_die(......);create() carps and returns undef if it can't connect to the database.
create_or_die() croaks (dies) your program, with the same error message as create() would have given.
Normally, create() will carp() (warn) you with an error message upon failure, and return undef.
If you would have handled your error by saying "or die", and ar comfortable with create's error message rather than your own, then create_or_die() is for you.
Using one of the create() methods instead of new() and tie() works with all of the different modes discussed below, and all parameters are the same either way.
Cooler subclassing mode:
You can create a simple subclass that provides default parmas in an initialize method so they don't have to be provided by the caller ...
### MyCompany/SalesPhoneHash.pm:
#!/usr/bin/perl
use strict;
package MyCompany::SalesPhoneHash;
use vars qw(@ISA);
use DBIx::TableHash;
@ISA = qw(DBIx::TableHash);
sub initialize
{
my $this = shift;
$this->{Database} ||= 'mydatabase'; ## Name of database to connect to.
$this->{TableName} ||= 'SalesPeople'; ## Table in which to store the data
$this->{KeyField} ||= 'FullName'; ## Name of the key field
$this->{ValueField} ||= 'PhoneNumber'; ## Name of the value field.
done:
return($this->SUPER::initialize());
}
1;Then to use the object, your script merely does:
use MyCompany::SalesPhoneHash;
my %PhoneNumbers = MyCompany::SalesPhoneHash->create_or_die();Of course, when instantiating a subclass, if you wish you can still override any parameters you wish, as long as the initialize() method in the subclass uses ||= rather than = to set defaults for any unspecified parameters.
Multi-key mode:
You may also use the "FixedKeys" parameter to specify a hash of some additional key fields and their fixed values that must match exactly for any records that are retrieved, deleted, or created by the tied object, effectively allowing the hash to operate on only a subset of the data in the database. This is typically helpful in a multi-keyed table where, for the purposes of your script, all key values should be fixed except one (and that one is the hash key).
use DBIx::TableHash;
my $PhoneNumbers =
DBIx::TableHash->
create_or_die(
{Database => 'mydatabase',
TableName => 'SalesPeople',
KeyField => 'FullName',
ValueField => 'PhoneNumbers',
FixedKeys =>
{AreaCode => 415,
StatusCode => 'Active',
RecordType => 'Primary'}});Multi-value mode:
If instead of getting and setting a single value, you'd like to get or set a hash of all fields in the record, simply don't specify ValueField, and the object will use "multi-value" mode, where an entire record, as a hash, is gotten or set on each fetch or store. Feel free to combine this mode with multi-key mode.
When storing a record in multi-value mode, if the record already exists, only the specified fields are overwritten. If it did not already exist, then only the specified fields will be written and the others will be NULL or defaulted according to the table schema.
When storing a record in multi-value mode, you can't change the values of the primary key field or any other key field specified in FixedKeys (if any), since that would mess up the whole point of this module which is to leave the main key and fixed keys fixed while mucking with the other values in the record. Any changed values in key fields are simply ignored.
use DBIx::TableHash;
my $SalesPeopleTable =
DBIx::TableHash->
create_or_die(
{Database => 'mydatabase',
TableName => 'SalesPeople',
KeyField => 'FullName'});
my $SalesPersonFullName = "Joe Jones";
my $EntireRecord = $SalesPeopleTable->{$SalesPersonFullName};When fetching records in multi-value mode, you can limit the list of returned fields to a subset of all available fields in case there might be some very big ones that you don't want to waste bandwidth getting. Just set the RetrieveFields parameter to an anonymous list of the fields you care to retrieve. (This setting does not limit the fields you can SET, just the ones that get retrieved.)
use DBIx::TableHash;
my $SalesPeopleTable =
DBIx::TableHash->
create_or_die(
{Database => 'mydatabase',
TableName => 'SalesPeople',
KeyField => 'FullName',
RetrieveFields=> [qw(Territory Quota)]});Warning:
In multi-value mode, you might expect that this:
$Hash->{$MyKey}->{FieldX} = 'foo';would set the value of the FieldX field in the appropriate record in the database. IT DOES NOT.
This is because the anonymous hash returned by $Hash->{$MyKey} is not in any way tied back to the database. You'd have to retrieve the record hash, change any value in it, and then set $Hash->{$MyKey} back to it.
Making the above syntax work with a multi-valued tied hash to set a value in the database is a possible future enhancement under consideration by the author. Let me know if you would like to have that work.
In the meanwhile, here's how you do could do it:
(my $Record = $Hash->{$MyKey})->{FieldX} = 'foo';
$Hash->{$MyKey}->{FieldX} = $Record;WARNING: If you use the above approach to update a record in multi-value mode, beware that there's potentially a race condition in the above code if someone else updates the same record after you've copied it but before you've modified and set it. So use this technique with caution and understanding. If in doubt, don't use this module and instead use an SQL query to update the record in a single transaction. Only you know the usage patterns of your database, the concurrency issues, and the criticality of errors.
Caching modes:
The object has several ways it can cache data to help minimize the number of SQL queries to the database, at the expense of potentially dramatically increased memory usage. The following cache parameters can be specified to enable caching:
CacheMode => 'CacheBeforeIterate'
CacheMode => 'CacheOneTime'To disable caching, specify:
CacheMode => 'CacheNone'(You can also assign undef to CacheMode, but you'll get warnings.)
Normally, every time you fetch a value from the hash, it makes an SQL query to the database. This, of course, is the intended and normal mode of operation.
Unfortunately, in Perl, just calling values(%Hash) or each(%Hash) or even copying the hash with {%Hash} results in a separate FETCH, and consequently, a separate SQL query made by this module, for each item. This could result in thousands of queries just to fetch all the values from a thousand-item table in the database.
However, often you want to iterate over all the elements of a hash without it having to go back to the database and issue another query for each item that you retrieve.
Using the 'CacheBeforeIterate' mode, all keys and values are cached upon each call to FIRSTKEYS (i.e. at the start of any iteration or enumeration). Then, any subsequent calls to FETCH data from the hash retrieve it from the cache instead of doing an SQL query. STORING or DELETING any items from the hash results in them being stored and deleted from both the database and the cache.
Using the CacheOneTime mode, the full cache is built at object instantiation and time never fully rebuilt. In fact, its contents never change unless you make alterations by using it to store into and/or delete from the database.
CACHE WARNING: With both caching modes, of course, you must be comfortable with the fact that the data being retrieved is a "snapshot" of the database and consequently will not reflect updates done by other parties during the lifetime of the object; it will only reflect updates that you make by storing or deleting values from it. If other people are using the database simultaneously, your cache and the actual data could "drift" out of agreement. This is mainly dangerous to you, not others, unless you then go make updates to the data based on potentially outdated values.
All modes may be combined...
All modes and parameters are orthogonal, so any combination of parameters may be specified, with the exception that the RetrieveFields parameter is only meaningful when ValueField is not unspecified.
With subclassing, you may create objects that pre-specify any parameters, even those that affect the major modes of operation. For example, you may combine the subclassing technique and the multi-key mode to make an object that accesses only the appropriate subset of a multi-keyed table without requiring any parameters to be supplied by the caller.
Getting a COPY of the data instead of a tied hash:
What if you just want a big copy -- a snapshot -- of the data in the table, in a regular old hash that's no longer tied to the database at all? (Memory constraints be damned!)
Just use the create_copy() or create_copy_or_die() methods. They work just like create() and create_or_die(), but instead of returning a tied object, they just return a potentially huge hash containing a copy of all the data.
In other words:
create_copy() is equivalent to: {%{create() || {} }}
create_copy_or_die() is equivalent to: {%{create_or_die()}}... but the _copy methods are more efficient because internally, a caching mode is used to minimize the queries to the database and generate the hash as efficiently as possible.
In all other respects, create_copy() and create_copy_or_die() perform exactly like their non-copying namesakes, taking all the same parameters, except CacheMode which is not relevant when making a static copy.
Please remember that the object returned by the _copy methods is no longer tied to the database.
PARAMETER SUMMARY
The full list of recognized parameters is:
DBI Parameters
Param Default Description
------------------------------------------------------------------------
DBIDriver 'mysql' Name of DBI driver to try to use (only
mysql has currently been tested by the
author).
HostName 'localhost' Host name containing the database and table;
Port undef Port number if different from the standard.
Login '' Login to use when connecting, if any.
Password '' Password to use when connecting, if any.SQL Parameters
Param Default Description
------------------------------------------------------------------------
Database '' Name of database to connect to.
TableName '' Table to connect to.
KeyField '' Name of field in which lookup key is found.
ValueField '' Name of field to pull value from.
If empty or undef, then a
multi-value hash is used both for
saving and retrieving. This is
called "multi-value mode".Module Parameters
Param Default Description
------------------------------------------------------------------------
FixedKeys {} If supplied, gives names and
fixed, hardcoded values that other
keys in the table must have; this
effectively limits the scope of
the tied hash from operating over
the entire table to operating over
just the subset of records that
match the values in FixedKeys.
This is called "multi-key mode".
RetrieveFields [] In multi-value mode, limits the
fields that are retrieved; default
is all fields in the record.SUPPORT
I am unable to provide any technical support for this module. The whole reason I had to make it was that I was way too busy (lazy?) to write all that SQL code...
But you are encouraged to send patches, bug warnings, updates, thanks, or suggestions for improvements to the author as listed below.
Just be aware that I may not have time to respond. Please be sure to put the name of this module somewhere in the Subject line.
The code is a pretty simple tied hash implementation, so you're on your own to debug it. If you're having trouble debugging via the "tie" interface, try instantiating an object directly (or retrieving it when you tie (see perltie)) and calling its methods individually. Use the debugger or Data::Dumper to dump intermediate values at key points, or whatever it takes. Use your database server logs if you want to see what SQL code is getting generated. Or contribute a debugging mode to this module which prints out or logs the SQL statements before executing them.
BUGS/GOTCHAS
Problem: If you iterate or enumerate the hash, all keys get pulled in from the database and stay stored in memory for the lifetime of the object. FIRSTKEY, which is called every time you do a keys(), each() or any full iteration or enumeration over the tied hash (such as copying it) retrieves and hangs on to a full list of all keys in KeyField. If the keys are long or there are lots of them, this could be a memory problem. (Don't confuse this with CacheMode in which BOTH keys AND values are stored in memory.)
Solutions:
1) Don't iterate or enumerate. Just fetch and store.
2) Only iterate or enumerate on short tables.
3) LValue or RValue hash slices should be safe to do.INSTALLATION
Using CPAN module:
perl -MCPAN -e 'install DBIx::TableHash'Or manually:
tar xzvf DBIx-TableHash*gz
cd DBIx-TableHash-?.??
perl Makefile.PL
make
make test
make installSEE ALSO
The DBIx::TableHash home page:
http://christhorman.com/projects/perl/DBIx-TableHash/The implementation in TableHash.pm.
The perlref and perltie manual pages.
The mysql home page:
http://mysql.com/THANKS
Thanks to Mark Leighton Fisher <fisherm@tce.com> for providing a patch to fix -w support (change in standard "none" setting of CacheMode from undef to CacheNone).
AUTHOR
Chris Thorman <chthorman@cpan.org>
Copyright (c) 1995-2002 Chris Thorman. All rights reserved.
This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself.