NAME

DataLookup - Perl extension for database view lookup table.

SYNOPSIS

use DataLookup; 1. Create a DBIx::DataLookup object:

 my $country_table = new DBIx::DataLookup(
                   dbh => $dbh,
                   statement => qq{ select countrycode countryname from countries },
                   );

2. Lookup records with matching 'countrycode' field:

 my $country_rec = $country_table->get_hashref(countrycode => 'USA');
 print "Country name: " . $country_rec->[0]{COUNTRYNAME} . "\n";

Similarly, you may create DataLookup objects to allow you lookup records by multiple keys. Here's an example of just how you could do this:

1. Again, create a DBIx::DataLookup object, but a little more complex than the one before:

 #
 # Note:  '. . .' denotes SQL expression of any complexity you wish.
 #
 my $country_table = new DBIx::DataLookup(
                   dbh => $dbh,
                   statement => qq{ select provname, provcode, countryname, countrycode
                                    from . . .
                                    where . . . },
                   keys => [qw(provcode countrycode)], # lookup keys
                   );

2. (a) Lookup records with matching provcode (Province code):

 my $prov_rec = $country_table->get_hashref(provcode => 'BC');
 print "First province name: " . $prov_rec->[0]{PROVNAME} . "\n";

2. (b) Find all provinces (or states) that belong to specified country:

 my $prov_rec = $country_table->get_hashref(countrycode => 'USA');

 foreach (@$prov_rec) {
   # $_ is a HASHREF to a hash representing 
   # a matched record.
 }

DESCRIPTION

Remotely similar to DBIx::Cache but is very simpler and serves narrower purpose. This module allows you to both cache records pulled by an SQL statement from a database in the memory as well as look them up later at any time during execution of your script.

This also speeds up access to your data at run-time and subsequently reduces load on the database.

For example, in your scripts, you could simply aggregate every SQL statement inside a hash in a config file and use them later to initialize a number of DBIx::DataLookup objects. Later in the code, you would simply invoke the get_hashref() method of your DBIx::DataLookup object(s) to retrieve records matching certain key values.

This module also supports alternative key mapping, which is not offered by any other module. A user of this module may add alternative key mappings at run-time to be able to link certain records with specific keys etc. For example, if you have to deal with data supplied to you by various providers (such as news/weather syndicates etc), there's a chance for minor irregularities in otherwise similar data (say, two vendors use different identification codes for one theater ...) So, when you are talking of only a dozen (or fewer) such differing keys, key mapping offered by this module becomes quite handy.

Methods

new(%vars)

 my $lookup_table = new DBIx::DataLookup(dbh => reference to a database handle, 
                                         statement => SQL statement, 
                                         [keys => reference to an array of query key fields);

This constructor will create a new data lookup object for any data retrieved by the 'SQL statement' from a data source which the 'dbh' database handle object was created for. By default, the 'keys' parameter may be omitted in which case the first field of the 'SQL query' will be used as the 'lookup' field (you may look up data only via that field's values).

For example,

 my $employee_table = new DBIx::DataLookup(dbh => $dbh,
                                         statement => qq{ select first_name, last_name, phone
                                                          from employee
                                                          where type = 'temp' });

Or, you may also explicitly specify a list of keys to be used:

 my $employee_table = new DBIx::DataLookup(dbh => $dbh,
                                         statement => qq{ select first_name, last_name, phone
                                                          from employee
                                                          where type = 'temp' }
                                         keys => ['last_name','phone']); 

add_key_mapping($key_field, $key_value, $map_to_value)

$key_field -- name of a key field. $key_value -- new value for this key field. $map_to_value -- existing value of this key field.

Maps new key field value to existing key field value. This is useful for associating additional key values with existing records during run-time.

Example,

 $country_table->add_key_mapping('countryname','ENGLAND','United Kingdom');

get($key_field, $key_value)

$key_field -- name of a key field. $key_value -- value of the key field.

Returns one or more record(s) with matching key field value. Each record is represented by an array of values. Here, values are ordered similarly to how the corresponding fields appear in the 'SQL statement' that was used in the new().

get_hashref($key_field, $key_value)

Similar to get() with the only disctinction that instead of returning an array of field values for each matched record, this method will return a hash structure where keys are field names, and values are their respective values.

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.

3. Write more POD!

EXPORT

:default - add_key_mapping(), get(), get_hashref()

AUTHOR

Vladimir Bogdanov <b_vlad@telus.net>

SEE ALSO

DBI.

cpanm DBIx::DataLookup

perl -MCPAN -e shell
install DBIx::DataLookup