I am strongly against runtime SQL generation. It is my opinion that the mapping between a collection of program conditions and the resultant SQL queries represents a finite and describable space.

one form of dynamic sql generation: if-then-if-then-if-then ad infinitum

In other words, I want to remove things like this 
use CGI; use DBI; my $cgi = ...; my $dbh = ...;

my $sql = "SELECT ";

push @field, 'age'    if (my $age = $cgi->param('age'));
push @field, 'gender' if (my $age = $cgi->param('gender));

if ($cgi->param('weight_constraint')) {
   push @where, sprintf 'weight %s', $cgi->param('weight_constaint');
}

my $sql = sprintf "SELECT %s FROM user WHERE %s;",
   join ',' @field, join ' and ', @where;

$dbh->selectall_arrayref($sql);

[download]
and also...

Modules supporting dynamic sql generation

I also don't think that a more structured approach to dynamic SQL generation such as is represented in DBIx::Recordset, DBIx::Abstract, DBIx::SQLEngine, DBIx::SearchBuilder, et al makes things any better because there are...

Issues with runtime SQL generation

  1. time. the time consumed breaks down into these types
    1. to execute the conditionals. This is the same for both.
    2. to build data structures which then get joined, sprintf'ed and otherwise folded into a final string
    3. if the module is sophisticated, it will also spending time caching the resulting query to avoid creating it again. It will also flexibly cache it either to disk or memory using something like Cache::Cache. The advantage of the static approach (exemplified by SQL::Catalog) is that the caching happens to either place before the application starts
  2. tuning it is possibly advisable to have a database expert create well-tuned static (static meaning, the only variability is in placeholders) queries as opposed to more general on-the-fly general queries.
  3. task divisionagain, allowing the database expert to code up the queries satisfying a specification on his own while the Perl programmer accesses then purely by label, as exemplified in SQL::Catalog provides the independance that large-scale applications need.

The only thing lacking from the static approach

Is some sort of structure which maps a collection of cases to a collection of SQL labels. I am thinking about Switch but also about decision trees.

Can anyone provide any pointers to a good means of such a mapping? Thanks.

In reply to Eliminating Dynamic SQL Generation with Decision Matrices for Tighter Large Scale Applications by princepawn

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.