Perl Hacks

Hack #23. Build a SQL Library

Store queries where non-programmers can maintain them.

Most serious programmers know the dangers of mixing their user interface code (HTML, GUI, text) with their business logic. When you have a designer making things pretty, it's too much work for any programmer to integrate change after change to font size, placement, and color.

If you have a DBA, the same goes for your SQL.

Why not keep your queries where they don't clutter up your code and where your DBA can modify and optimize them without worrying about a misplaced brace or semicolon breaking your software? If you use SQL::Library with a plain text file under version control, you can.

The Hack

Install SQL::Library from the CPAN. Extract all of the SQL from your code into one place [Hack #22], and then put it all in a plain text file in INI format:

[select_nodemethod_attributes]
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

The section title (the names in square brackets) is the name of the query and the rest is the SQL. Save the file (for example, nodemethods.sql). Then from your code, create a SQL::Library object:

use SQL::Library;

my $library = SQL::Library->new({ lib => 'nodemethods.sql' });

Running the Hack

Whenever you need a query, retrieve it by name from the library:

my $sth = $dbh->prepare( $library->retr( 'select_nodemethod_attributes' ) );

From there, treat it as normal.

Hacking ...