Specifying a loop with varying number of keys

periapt has asked for the wisdom of the Perl Monks concerning the following question:

OK, I'm looking for an alternative implementation that allows me to categorize a data element based on 2 or maybe 3 keys.

Here is what I'm working on. I am importing several pipe seperated text files into a database. Record length varies by file but is constant within a file. To simplify processing, I am parsing each record into individual data elements based on a set of four key values. Two are static to a record and two are dynamic. Assume the dynamic keys are sequential so that key1 varies, say, from 1 to 3 and key2 varies from 1 to 6. The range of the key values depend on the data file I'm processing at the time. It turns out that I can process most of the data files with the same algorithm. I just have to set a couple of initialization parameters. The one monkey wrench is that one of the files requires three key values. It is also possible that future data added to this system will also require three key values.

I've come up with a rather inelegant partial solution that still allows me to use one algorithm but I'm wondering if there isn't something better. something with references? aliases? fancy symbol table manipulation? function templates?

As you can see from the below code, the sticking point is the insert statment which is incorrectly specified if a third key is not needed. I would like to find some way to specify the @key array so that it contains the correct number of elements without resorting to a ladder of if..elsif 's (or some similar structure). The idea is to keep the code as compact and generic as possible since it is certain that I will be adding data sets in the future which may also require a differing number of key values.

Super search and google search didn't turn up much of use (lot's of almost but not quite). Any suggestions?

# the routine is an anonymous sub
return sub {
my $rec = shift;
my $rtncd = 0;

# define primary key structure
# w/ unchanging values set
my @key = ($statickey1,
           $statickey2,
           0, 0, 0);      # zeros are places for dynamic keys

# key1 is initialized to zero if key not needed,
# $keylmt[0] = 0 and $keylmt[1] = 0 so that outer loop is
# only executed once
                                    # dynamic key1
for($key[2]=$keylmt[0]; $key[2] <= $keylmt[1]; ++$key[2]){ 
                                    # dynamic key2    
  for($key[3]=$keylmt[2]; $key[3] <= $keylmt[3]; ++$key[3]){
                                    # dynamic key3
    for($key[4]=$keylmt[4]; $key[4]<=$keylmt[5]; ++$key[4]){

          $rtncd = ProcessRecord($updt_sth,@key,$datavalue)
      }
    }
  }

} # end anonymous sub

# The ProcessRecord() sub does a variety of things including
# the insertion of new records and the update of existing ones
# the SQL statements are prepared from a hash using DBI like this

# third key not needed
$sql{updt_sth} = "UPDATE $dbf01..$table ".
                 'SET nrevts = ? '.
                 'WHERE statickey1 = ? AND statickey2 = ? '. 
                   'AND 0 = ? AND key2 = ? AND key3 = ?';

# third key needed
$sql{updt_sth} = "UPDATE $dbf01..$table ".
                 'SET nrevts = ? '.
                 'WHERE statickey1 = ? AND statickey2 = ? '. 
                   'AND key1 = ? AND key2 = ? AND key3 = ?';

# the insert stmt is a sticking point as key1 may not exist
# in table
$sql{ins_sth} = 
  "INSERT INTO $dbf01..$table".
    '(statickey1,statickey2,key1,key2,key3,value)'.
  'VALUES(?,        ?,       ?,   ?,   ?,    ?)';

# how the db actions are called

sub InsNewRec{
# query placeholder order: (key, value) 
    my $sth = shift;
    
    eval{$sth->execute(@_)};
    return $@;
} # end InsNewRec();

sub UpdtStoTbl{
# query placeholder order: (value,key)
    my $sth = shift;
    
    eval{$sth->execute(@_)};
    return $@;
} # end UpdtStoTbl();

sub SaveAmendedRec{
# query placeholder order: (key,chgfrom,value,chgdte)
    my $sth     = shift;
    
    eval{$sth->execute(@_)};
    return $@;
} # end SaveAmendedRec();
[download]

PJ
use strict; use warnings; use diagnostics;

Comment on Specifying a loop with varying number of keys Download Code

Replies are listed 'Best First'.
Re: Specifying a loop with varying number of keys by dragonchild (Archbishop) on Mar 22, 2005 at 14:27 UTC
Build your queries on the fly, once you know whether you're dealing with 2 keys or 3 keys. The SQL is just a string that gets passed to prepare(). 90% of all the DBI queries I've ever run were dynamically generated. As for your nested for-loops, take a look at Algorithm::Loops for a better solution, particularly the NestedLoops() function. Being right, does not endow the right to be rude; politeness costs nothing. Being unknowing, is not the same as being stupid. Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence. Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.	[reply]
Re^2: Specifying a loop with varying number of keys by periapt (Hermit) on Mar 23, 2005 at 13:03 UTC
Algorithm::Loops was just what I needed. A few minor changes to the structure of @keylmt and one dynamically defined function template for the iteration test routine and I am off and running. Thanks. Maddeningly ironic that in the dozen or so searches and subsearches I ran yesterday, I never thought to use the word, loop. Keys, iterators, for next and so on but not loop. Ah well... Thanks again :o) PJ use strict; use warnings; use diagnostics;	[reply]


Pathologically Eclectic Rubbish Lister
	PerlMonks