Slash Boxes
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
More | Login | Reply
Loading... please wait.
  • 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

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

      Personally I prefer SQL::Interp [] (via DBIx::Simple [] 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.

      • 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.

        • 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 = 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,

          • 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

            • 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 ORDER ),

              And then processing @queries consists merely of passing each entry through sql_interp.

              This does leave the SQL indenting issue, admittedly. I wish SQL::Tidy [] had gone anywhere, but it appears not to have.