Perl Hacks

Hack #22. Factor Out Database Code

Separate SQL and Perl to make your life easier.

Small scripts have a way of growing up into essential programs. Unfortunately, they don't always mature design-wise. Far too often a business-critical program starts life as a quick-and-dirty just-get-it-done script and evolves mostly by accretion, not the clear and thoughtful hand of good design.

This is especially true in programs that work with data in various formats or which embed other languages such as HTML or SQL. Fortunately, it only takes a little bit of discipline—if no small amount of work—to clean up this mess and make your code somewhat easier to maintain.

The Hack

The only trick is to remove all SQL from within the code and to isolate it in its own module. You don't have to abstract away or factor out all of the database access code or the various means by which you fetch data or bind to parameters—just untangle the Perl and non-Perl code.

Be strict. Store every instance of SQL in the module. For example, if you have a subroutine such as:

sub install_nodemethods
{
    my $dbh = shift;

    my $sth = $dbh->prepare(<<'END_SQL');
SELECT
    types.title AS class, methods.title AS method, nodemethod.code AS code
FROM
    nodemethod
LEFT JOIN
    node AS types ON types.node_id = nodemethod.supports_nodetype
END_SQL

    $sth->execute( );

    # ... do something with the data
}

store the SQL in the SQL module in its own subroutine:

package Lots::Of::SQL; use base 'Exporter'; use vars '@EXPORT'; @EXPORT = 'select_nodemethod_attributes'; ...