How can I reuse the WHERE clause logic with DBI?

Question

Disclaimer: The first time I used DBI.

I have a MySQL table with a large number of indexed fields (f1, f2, f3, etc.) that are used to generate WHERE clauses by lengthy processes that iterate over database chunks that perform various cleaning and testing operations.

The current version of this code works something like this:

sub get_list_of_ids() { my ($value1, $value2, $value3...) = @_; my $stmt = 'SELECT * FROM files WHERE 1'; my @args; if (defined($value1)) { $stmt .= ' AND f1 = ?'; push(@args, $value1); } # Repeat for all the different fields and values my $select_sth = $dbh->prepare($stmt) or die $dbh->errstr; $select_sth->execute(@args) or die $select_sth->errstr; my @result; while (my $array = $select_sth->fetch) { push(@result, $$array[0]); } return \@result; } sub function_A() { my ($value1, $value2, $value3...) = @_; my $id_aref = get_list_of_ids($value1, $value2, $value3...); foreach my $id (@$id_aref) { # Do something with $id # And something else with $id } } sub function_B() { my ($value1, $value2, $value3...) = @_; my $id_aref = get_list_of_ids($value1, $value2, $value3...); foreach my $id (@$id_aref) { # Do something different with $id # Maybe even delete the row } } 

In any case, I am going to dump a lot more lines into the database, and I know well that the code above will not expand. I can come up with several ways to fix this based on other languages. What is the best way to handle this in Perl?

It should be noted that the logic in get_list_of_ids() too large for replication in each function; and that the operations on the selected lines are very diverse.

Thanks in advance.

Answer 1

I assume that you “zoom in” you mean in terms of service, not performance.

The key change to your code is to pass your arguments as column / value pairs, not a list of values ​​with an intended set of columns. This will allow your code to handle any new columns that you might add.

DBI->selectcol_arrayref is convenient and slightly faster, written to C.

If you enable RaiseError in your connect call, the DBI will throw an error exception and not write or die ... all the time. You have to do it.

Finally, since we are writing SQL from possibly unreliable user input, I took care to avoid the column name.

The rest is explained in this Etherpad , you can watch how your code will be converted step by step.

 sub get_ids { my %search = @_; my $sql = 'SELECT id FROM files'; if( keys %search ) { $sql .= " WHERE "; $sql .= join " AND ", map { "$_ = ?" } map { $dbh->quote_identifier($_) } keys %search; } return $dbh->selectcol_arrayref($sql, undef, values %search); } my $ids = get_ids( foo => 42, bar => 23 ); 

If you expect get_ids return a huge list, too much to store in memory, instead of pulling out the entire array and storing it in memory, you can return the instruction handle and repeat it.

 sub get_ids { my %search = @_; my $sql = 'SELECT id FROM files'; if( keys %search ) { $sql .= " WHERE "; $sql .= join " AND ", map { "$_ = ?" } map { $dbh->quote_identifier($_) } keys %search; } my $sth = $dbh->prepare($sql); $sth->execute(values %search); return $sth; } my $sth = get_ids( foo => 42, bar => 23 ); while( my $id = $sth->fetch ) { ... } 

You can combine both approaches by returning a list of identifiers in the context of an array or an instruction handle in scalar format.

 sub get_ids { my %search = @_; my $sql = 'SELECT id FROM files'; if( keys %search ) { $sql .= " WHERE "; $sql .= join " AND ", map { "$_ = ?" } map { $dbh->quote_identifier($_) } keys %search; } # Convenient for small lists. if( wantarray ) { my $ids = $dbh->selectcol_arrayref($sql, undef, values %search); return @$ids; } # Efficient for large ones. else { my $sth = $dbh->prepare($sql); $sth->execute(values %search); return $sth; } } my $sth = get_ids( foo => 42, bar => 23 ); while( my $id = $sth->fetch ) { ... } my @ids = get_ids( baz => 99 ); 

In the end, you need to stop manual SQL coding and use Map Relation Mapper (ORM) like DBIx :: Class . One of the main advantages of ORM is its very flexible approach and can do the above for you. DBIx :: Class can return a simple list of results or a very powerful iterator. The iterator is lazy, it will not execute the request until you start receiving rows, which will allow you to modify the query as necessary without having to complicate your fetch procedure.

 my $ids = get_ids( foo => 23, bar => 42 ); $ids->rows(20)->all; # equivalent to adding LIMIT 20