use Perl

• Don't use that on a high volume site (Score:1)

  by btilly (5037)

  At least not against a good database. (eg Oracle, PostgreSQL, etc - but MySQL would be fine.)

  The problem is that there is a trade-off between time spend preparing a query and query performance. It would not be inappropriate to think of preparing a query as a "compile and optimize this SQL" step. MySQL spends very little energy preparing, and therefore its ability to handle complex queries suffers. Most other databases put a lot of energy into preparing, and so it is very important to try to avoid recomp
  • Re: (Score:1)

    by Aristotle (5147)

    Also, source filters. Yes, Module::Compile, but still, yuck.

    Personally I prefer SQL::Interp [cpan.org] (via DBIx::Simple [cpan.org] actually), which lets me write code very nearly like the quasiquoting snippet, except it’s still plain Perl and it uses placeholders and does binding under the covers. It’s a fabulous duo.
    • Re: (Score:1)

      by btilly (5037)

      If my situation was simpler then that would look good to me as well. Unfortunately I have big chunks of SQL that are included or not based on one condition, and if included require specific parameters to be bound or not as well. Templating syntax works well for this, but standard template tools only return a string, so I really, really want to work with a string by itself, not a string plus parameters.

      Using :param_name isn't portable, but it is highly effective for my job.
      • Re:Don't use that on a high volume site (Score:1)

        by Aristotle (5147) <pagaltzis@gmx.de> on 2008.08.06 2:44 (#64215) Homepage Journal

        That is actually exactly the situation that SQL::Interp addresses. It lets you do something like this:

        my ( $sql, @bind_value ) = sql_interp(
            'SELECT foo FROM bar'
            ( $do_include_baz
                ? ( 'LEFT JOIN baz ON baz.foo = foo.id AND baz.quux =', \$quux )
                : ()
            ),
            'WHERE', \%condition,
        );

        sql_interp will combine all those SQL snippets into a single string, duly putting in with placeholders for all the variables, and shuffling all the values into a single list according to the positions of their corresponding placeholders. (It’ll also translate that hash into a list of ANDed equality comparisons.) So despite the fact that the database sees positional placeholders, you don’t need to repeat the conditional logic from building the SQL in building the bind list, and the query is written with the Perl variables inline – precluding any bugs in lining up placeholders with bind values, no matter how complex the query composition gets.

        Reply to This

        Parent

        • Re: (Score:1)

          by btilly (5037)

          Yes, you can do that - but the result is unreadable. Which would you prefer to read?
          
my ( $sql, @bind_value ) = sql_interp(
        'SELECT foo ',
            ( $include_bar
                    ? ', bar'
                    : ()
            ),
            ' , count(*) as records '
    , 'FRO
          • Re: (Score:1)

            by Aristotle (5147)

            Neither. :-)

            But the first example shows why I specifically pointed out that the list you pass is a normal Perl list and so is amenable to all of the facilities Perl provides. You can factor it just in the same way you would factor any other part of the code:

            my $columns = join ', ', 'foo', ( $include_bar ? 'bar' : () );
            
            push @queries, [
                'SELECT $columns, count(*) as records',
                'FROM some_table',
                'WHERE', { x => $x },
                map { "$_ BY $columns" } qw( GROUP O