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 disciplineif no small amount of workto 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 parametersjust 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'; sub select_nodemethod_attributes ( ) {     return <<'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 }

Running the Hack

Now call the query from the refactored original subroutine:

use Lots::Of::SQL; sub install_nodemethods {     my $dbh = shift;     my $sth = $dbh->prepare( select_nodemethod_attributes( ) );     $sth->execute( );     # ... do something with the data }

Putting the empty prototype on the SQL abstraction function tells Perl that it can inline the (constant) return value whenever other code calls this function. You get the benefit of hiding all that SQL behind a readable name without paying a runtime price.

Hacking the Hack

Of course, stuffing all of that code into one potentially huge module isn't exactly the end result of refactoringbut with a bit more polish, it's a good step. Exporting all of the SQL subroutines is overkill that doesn't really balance out the niceness of being able to maintain the same SQL just once for any application that uses the database.

Why not export just what you need?

Consider that every operation on a table is its own exporter group, then create an exporter tag for that operation. For example, if you have the tables users, stories, and comments, group each type of SQL query into a tag:

package Lots::Of::SQL; use base 'Exporter'; use vars qw( @EXPORT_OK %EXPORT_TAGS ); @EXPORT_OK = qw(     select_user    insert_user    update_user       select_story   insert_story   update_story      select_comment insert_comment     select_stories     select_user_stories     select_user_comments ); %EXPORT_TAGS = (     user    =>     [ qw(         select_user insert_user update_user select_user_stories          select_user_comments     )],     story   =>     [ qw(         select_story insert_story update_story select_user_stories         select_stories     )],     comment => [ qw( select_comment insert_comment select_user_comments )], );

Then a hypothetical User module can use Lots::Of::SQL ':user'; and receive only the SQL it needs.

This isn't the end of the story. Suppose you want DBAs or non-Perl types to edit and reuse the SQL. "Build a SQL Library" [Hack #23] has ideas.

Perhaps maintaining those export lists by hand is too much work. Using attributes [Hack #45] could simplify your life.

Maybe static SQL written for a single database isn't your style. Try generating it with a templating system or using an abstract, Perlish representation [Hack #24] instead. You might even switch to a persistence or object-relational mapping module such as Class::DBI. There are plenty of options, once you untangle SQL from Perl.