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 HackThe 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 HackNow 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 }
Hacking the HackOf 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. |