Look at the following:
Output:use Modern::Perl; use Data::Dump qw/dump/; use SQL::Abstract; use DBI; my $dbh = DBI->connect( 'dbi:CSV:', '', '', { f_dir => './DB/', csv_sep_char => "\t", } ) or die "Cannot connect: $DBI::errstr"; my @where; { my $query = 'SELECT * FROM filter_file ORDER BY orde'; my $sth = $dbh->prepare($query); $sth->execute(); while ( my $row = $sth->fetchrow_hashref ) { push @where, { $row->{'column'} => { $row->{'relationship'} => $row->{'va +lue'} } }; } $sth->finish(); } my $sql = SQL::Abstract->new; my ( $where, @bind ) = $sql->where( \@where ); { my $query = "SELECT a, b, c FROM input $where"; my $sth = $dbh->prepare($query); $sth->execute(@bind); while ( my $row = $sth->fetchrow_hashref ) { say dump($row); } $sth->finish(); }
This script fetches the filter conditions from the filter_file, stuffs it into a SQL::Abstract data-structure, constructs an SQL query out of it that combines all filters at once (the "where" part of the SQL is WHERE ( ( a <= ? OR c <= ? OR b = ? ) )) and then runs that SQL query against your input file.{ a => 0.2, b => "abc", c => 0.3 } { a => 0.1, b => "abd", c => 0.3 } { a => 0.4, b => "abe", c => 0.2 } { a => 0.1, b => "abc", c => 0.5 } { a => 0.1, b => "abd", c => 0.8 }
No repeated going through your input file; clean and fast code; easy to understand, maintain and change for anyone who knows the basics of SQL.
A few caveats though:
CountZero
A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James
My blog: Imperial Deltronics
|
|---|