StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I presume by "scale up" you mean in maintenance terms rather than performance.</p> <p>The key change to your code is to pass in your arguments as column/value pairs rather than a list of values with an assumed set of columns. This will allow your code to handle any new columns you might add.</p> <p><code>DBI->selectcol_arrayref</code> is both convenient and a bit faster, being written in C.</p> <p>If you turn on <code>RaiseError</code> in your <code>connect</code> call, DBI will throw an exception on errors rather than having to write <code>or die ...</code> all the time. You should do that.</p> <p>Finally, since we're writing SQL from possibly untrusted user input, I've taken care to escape the column name.</p> <p>The rest is explained in <a href="http://etherpad.com/ep/pad/view/hGP8cWiGD9/latest" rel="nofollow noreferrer">this Etherpad</a>, you can watch your code be transformed step by step.</p> <pre><code>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 ); </code></pre> <p>If you expect <code>get_ids</code> to return a huge list, too much to keep in memory, then instead of pulling out the whole array and storing it in memory you can return the statement handle and iterate with that.</p> <pre><code>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 ) { ... } </code></pre> <p>You can combine both approaches by returning a list of IDs in array context, or a statement handle in scalar.</p> <pre><code>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 ); </code></pre> <p>Eventually you will want to stop hand coding SQL and use an Object Relation Mapper (ORM) such as <a href="http://search.cpan.org/dist/DBIx-Class" rel="nofollow noreferrer">DBIx::Class</a>. One of the major advantages of an ORM is it is very flexible and can do the above for you. DBIx::Class can return a simple list of results, or very powerful iterator. The iterator is lazy, it will not perform the query until you start fetching rows, allowing you to change the query as needed without having to complicate your fetch routine.</p> <pre><code>my $ids = get_ids( foo => 23, bar => 42 ); $ids->rows(20)->all; # equivalent to adding LIMIT 20 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload