Stories
Slash Boxes
Comments
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.
 Full
 Abbreviated
 Hidden
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 [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.

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

          • 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 '
                , 'FROM some_table
                      WHERE x = ', \$x
                , ' GROUP BY foo '
                      ( $include_bar
                                ? ', bar'
                                : ()
                      ),
                      ' ORDER BY foo '
                        ( $include_bar
                                ? ', bar'
                                : ()
                        ),
            );

            (Geez, I can't even figure out how to indent that usefully. Certainly the output isn't indented properly!) or

            push @queries,
                {
                    sql => qq{
                        SELECT foo
            [% IF include_bar -%]
                            , bar
            [% END -%]
                        FROM some_table
                        WHERE x = :x
            [% FOREACH section = ["GROUP", "ORDER" -%]
                        [% section %] BY foo
            [%   IF include_bar -%]
                            , bar
            [%   END -%]
            [% END -%]
                    },
                    params => {
                        x => $x,
                    },
                    template_vars => {
                        include_bar => $include_bar,
                    }
                };

            Now pretend for a second that you have a whole bunch of these variables, a large group by section, and you're writing a lot of these. Oh, and that the GROUP BY and ORDER BY sections are kind of long. Oh, and that during debugging you need to look at the generated SQL.

            If you're imagining all of that, then you'll start to see what my life is like. :-)

            • 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