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.provincename, 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 . . .
#

##</code><code>##

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}}, $record_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;