What I have to show you here is in continuation of original discussion
you may find in '
I basically wrote a simple module which allows me to read data from
a database (executing SQL statement using DBI) and store it in memory
for later use. The data is also specially arranged to allow seamless key
look up mechanism. Making database data readily available by storing it
in memory is a good way to avoid having to request bits and pieces of that
data from the database everytime I need to use it at run-time. I'm currently
using it in a script of mine that has to do a lot of database lookups during
the course of it's execution. Previously, though, I had to use weird hash constructs
to achieve the same results. I'm lucky that I've got the time now to fix
this ;-).
- new() - to instantiate a new object
- get() - to get records maching given key/value.
However, these are enough to do the job as demonstrated in the
test script included below. Module code is found at the end of this node.
I'd appreciate it if you could review the module and suggest any changes/enhancements.
Please refer to inline documentation to draw your responce (especially the TODO section).
Test script: (you'll have to specify your own SQL statement to make the test meaningful).
use strict;
use DBI;
use TableHash;
#-- Connect to the database
#-- Build lookup hash which will contain a number
#-- of TableHash objects used for data lookup later
#-- in the code.
my %lookup;
$lookup{province_country_data} = new DBIx::TableHash( dbh => $dbh,
statement => q{
select distinct p.provin
+cename, p.provincecode, c.countryname, co.countrycode
from city c, province p, country co
where c.provincecode = p.provincecode
and co.countryname = c.countryname
and ...
},
keys => [ qw(provincename countryname) ],
);
my @province_info = $lookup{provname}->get(provincename => 'Alaska');
my @country_provinces = $lookup{provname}->get(countryname => 'CANADA'
+);
print "Done.\n";
#
# . . . work on retrieved data . . .
#
DBIx::TableHash module source:
package DBIx::TableHash;
# AUTHOR: Vladimir Bogdanov
#
# $Date: 2002/04/19 22:28:34 $
#
# $Id: TableHash.pm,v 1.5 2002/04/19 22:28:34 vlad Exp $
#
# $Revision: 1.5 $
#
# $Log: TableHash.pm,v $
# Revision 1.5 2002/04/19 22:28:34 vlad
#
# minor
#
# Revision 1.4 2002/04/19 22:13:09 vlad
#
# First working release.
#
# Revision 1.3 2002/04/19 20:51:21 vlad
#
# fixed a few bugs. Rearranged code flow.
#
# Revision 1.2 2002/04/19 20:45:56 vlad
#
# beta.
#
# Revision 1.1 2002/04/19 17:46:52 vlad
#
# a simple module to store table data into a hash (for quicker access)
+.
# The hash also could be dumped into a static file (using Data::Dumper
+)
# for use on consequent runs of the same script.
#
#
# DESCRIPTION:
# somewhat similar to DBIx::Cache but is very simpler and
# serves narrower purpose.
#
# TODO:
# 0. Enable lookup by multiple keys so that only records
# containing both matching keys will get returned.
# Also, could implement support for complex look up
# rules (near to what you'd get with SQL WHERE clause).
#
# 1. Add set(field => value) method to allow user to set
# a record field to a new value.
#
# 2. Add commit() ? to save data back into the database.
# Note: may have to deal with original SQL statement
# in odrer to build a proper UPDATE SQL command.
#
use strict;
#
# Instantiates an object that will store database data
# from a single table (or multiple for that matter, depending
# on the kind of SQL statement used to grab that data)
# in an internal specially arranged structure to facilitate
# quick key value lookup machanism.
#
# example:
# statement = "select col1, col2, col3 from table foobar"
# fields = qw(col1 col2 col3)
# keys = qw(col2)
#
# if return data is:
#
# col1_val1, col2_val1, col3_val1 # record 1
# col1_val2, col2_val2, col3_val2 # record 2
# col1_val3, col2_val3, col3_val3 # record 3
#
# Data will be structured as follows:
# %table =
# (
# fields => {
# 'col1' => 0,
# 'col2' => 1,
# 'col3' => 2,
# }
# records => [
# ['col1_val1','col2_val1','col3_val1'], # record 1
# ['col1_val2','col2_val2','col3_val2'], # record 2
# ['col1_val3','col2_val3','col3_val3'], # record 3
# ]
# record_keys => {
# # col1 serves as key
# col1 => {
# # key field value => list of matching records
# col2_val1 => [0],
# col2_val2 => [1],
# col2_val3 => [2],
# },
# }
# )
#
# So, to find a record by a value of col1, you'd have to do this:
# name of a key field --\
# $table{records}[$table_data{record_keys}{col1}{'col2_val2'}][$table_
+data{fields}{col3}]
#
# Which is equivalent to this SQL:
#
# select col3 from table foobar where col1 = 'col2_val2';
#
sub new {
my $pkg = shift;
my $self; { my %hash; $self = bless(\%hash, $pkg); }
my (%vars) = @_;
my $ar_keys;
if (exists $vars{keys}) {
if (ref $vars{keys} eq "ARRAY") {
@{$ar_keys} = map {uc($_)} @{$vars{keys}};
}
elsif (ref $vars{keys} eq "SCALAR") {
$ar_keys = $vars{keys};
} else {
return; # foobar!
}
}
my $statement= $vars{statement};
my $params_aref = $vars{params};
my $dbh = $vars{dbh};
my $sth = $dbh->prepare($statement);
$sth->execute(@$params_aref) or die $sth->errstr;
# key field(s) (will allow easy hash key lookup).
# use the first field by default.
my $ar_fields = $sth->{NAME};
$ar_keys ||= [$ar_fields->[0]]; # first field as key by default.
my $i = 0;
%{$self->{table}{fields}} = map {$_ => $i++} @$ar_fields;
while (my @row = $sth->fetchrow_array()) {
# store in records hash
push @{$self->{table}{records}}, [@row];
my $record_indx = scalar(@{$self->{table}{records}}) - 1;
foreach my $key_field (@$ar_keys) {
my $key_val = $row[$self->{table}{fields}{$key_field}];
push @{$self->{table}{record_keys}{$key_field}{$key_val}}, $reco
+rd_indx;
}
}
return $self;
}
#
# returns list of records in the table that
# matched key value.
#
# Note: actual records are not being copied here.
# Therefore, if user chooses to update
# a record field value, he/she will be
# modifying a record field value stored
# in this object's table.
#
sub get {
my ($self, $key_field, $key_val) = @_;
my $rec_num = $self->_find_record_indxs($key_field, $key_val);
return ($rec_num) ? @{$self->{table}{records}}[@$rec_num] : undef;
}
# returns reference to list of
# record indexes which contain the key
sub _find_record_indxs {
my ($self, $key_field, $key_val) = @_;
my $table = $self->{table};
my $key_field = uc($key_field);
my $rec_num;
# this awkward nested thing here is required in order
# not to create new keys in existing hashes in cases
# when the original hash didn't contain the key.
$rec_num = (exists $table->{record_keys}{$key_field})
&& (exists $table->{record_keys}{$key_field}{$key_val})
&& $table->{record_keys}{$key_field}{$key_val};
return $rec_num;
}
1;
|
"There is no system but GNU, and Linux is one of its kernels." -- Confession of Faith
|
