[Oc-pm] June 20th Meeting Notes

[Ben Tilly]

On 6/28/07, Douglas Wilson <dgwilson1 at cox.net> wrote:
> Ben Tilly wrote:
> >
> >   my $example_sql = qq{
> >     SELECT s.this
> >       , s.that
> >       , s.the_other
> >     FROM foo f
> >       , (} . indent($complex_subquery_sql) . qq{    ) s
> >     WHERE f.some_key = s.some_key
> >       AND f.some_field = 'some condition'
> >   };
> >
> > each of which has a clear and readable intent, and the result of which
> > is nicely indented and is as legible as possible.  (ie not very.)
>
> I'm happy as long as the statement is broken up by newlines
> (single line SQL statements make for debugging fun...not!) :)

It truly puzzles me that experienced programmers who would never think
of not indenting code, frequently don't indent SQL.  SQL is code like
anything else.  All of the reasons to indent anything, apply to SQL.

> Though I like the style above, so maybe I'll just have to
> get over my irrational fear of extra-indentation in multi-line
> strings. :-)

:-)

Please note that the leading , and AND statements are a very concious
choice.  I settled on that style after noticing how often I'd get a
stupid SQL error when adding new conditions because I left that out.
I do the same thing in, say, parameters to JavaScript functions.  But
not in Perl, there I just leave trailing commas.

> When embedding SQL into indented blocks, I tend to use
> the style at the bottom of this post:
> http://www.perlmonks.org/?node_id=615032

Did you notice that you have syntax errors there?

> Though when using that style in the past, I have at least once
> misplaced a "," for a "." and ended up with a momentarily
> mysterious "Odd number of hash elements" warning :-)

For me a bigger deal is that I like having the ability to cut and
paste SQL directly from code into an interactive SQL prompt to check
for errors.  This is particularly important with Oracle - its
interactive SQL prompt gives far, far more useful error messages than
go back to DBI.

> > If you routinely wind up writing several hundred line SQL statements
>
> I often find myself deconstructing 300 line SQL statements
> rather than writing them, which was the entire reason for that
> perlmonks thread. Then by the time I have them torn apart, figured
> out and fixed, I'm too lazy to put them back together, so if
> performance is reasonable, I'll leave it as is. It makes future
> debugging easy, like when trying to figure out why certain data
> doesn't make it through the entire pipeline, as I can eliminate joins
> by commenting out one line at a time.

You can comment out individual lines of SQL with --.  Or blocks with
traditional C-style comments like /* ... */.  (I almost always use the
former style.)

> The biggest drawback with the framework in that thread is that you
> can't order by arbitrary columns :-(  So you have to pipe it through
> some other process if you want to order by anything other than columns
> in the outermost SQL statement (to columns statements contiguously inward).

Native SQL avoids that drawback as well. :-P

But it seems to me like it should be easy to add that back in?

> Well, I should be able to make the next meeting...

I look forward to seeing you again.

Ben