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 HackInstall 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 HackWhenever 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 the HackThis isn't very exciting until you get to more complex querieswhere the order of joins is important, where the exact nature of queries changes, or where there's lots of manipulation and editing going on. Being able to modify the SQL without touching the code is very handy. For example, consider a reporting application. Choose a filename to hold the queries. Write a bit of code that processes the queries and feeds them to a library to produce graphs or spreadsheets. (The trick with NAME_lc in "Bind Database Columns" [Hack #25] is very useful here.) Then just loop through all of the queries in the library, preparing and executing them, and processing the results: use SQL::Library; my $library = SQL::Library->new({ lib => 'daily_reports.sql' }); for my $query ( $library->elements( ) ) { my $sth = $dbh->prepare( $query ); my %columns; $sth->bind_columns( \\@columns{ @{ $sth->{NAME_lc} } } ); $sth->execute( ); process_report( \\%columns ); } Now whenever your users want another query, just write it and store it in the appropriate library file. You never have to touch the reporting program (as long as it can draw its pretty graphs correctly)and if you can teach your users to write their own queries, you can make your job that much easier. |