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

use Perl Log In

Log In

[ Create a new account ]

Ovid (2709)

Ovid
  (email not shown publicly)
http://publius-ovidius.livejournal.com/
AOL IM: ovidperl (Add Buddy, Send Message)

Stuff with the Perl Foundation. A couple of patches in the Perl core. A few CPAN modules. That about sums it up.

Journal of Ovid (2709)

Wednesday May 07, 2008
03:46 AM

SQL Tidy

[ #36349 ]

It seems tough to find a well-written SQL formatter out there which is both accurate and open-source. I looked at SQL::Tidy as an inspiration, but it's tokenizer is bad and there are plenty of issues with the code. A quick bit of hacking has gotten it to the point where it will take this:

SELECT me.import_id, me.timestamp, DATE_FORMAT ( timestamp, '%Y-%m-%d' ) as day
FROM import me LEFT JOIN import_error import_errors ON ( me.import_id =
import_errors.import_id AND import_errors.type IN (
'X::IMPORTER::VALIDATION::BRANDTITLEMISSING')) WHERE ( ( ( timestamp >=
'2008-05-01T00:00:00' ) AND ( timestamp <= '2008-05-06T13:12:30' ))
) GROUP BY me.import_id

And turn it into this:

SELECT me.import_id, me.timestamp, DATE_FORMAT ( timestamp, '%Y-%m-%d' )
           as day
      FROM import me LEFT
      JOIN import_error import_errors
      ON ( me.import_id = import_errors.import_id
               AND import_errors.type
               IN (
                        'X::IMPORTER::VALIDATION::BRANDTITLEMISSING'
               )
      )
WHERE ( ( ( timestamp >= '2008-05-01T00:00:00' )
               AND ( timestamp <= '2008-05-06T13:12:30' )
             )
           ) GROUP BY me.import_id

That's pretty ugly, but at least it makes it a lot easier to read auto-generated SQL that's all on one line. Still, it's only for quick cleanup. The tokenizer needs a lot of work before we can even think of a reasonable reformatter.

Amusingly, though, I accidentally ran Perl::Tidy over it.

SELECT me . import_id, me . timestamp,
    DATE_FORMAT(timestamp, '%Y-%m-%d')
    as day FROM import me LEFT JOIN import_error import_errors ON(me
        . import_id
        = import_errors
        . import_id AND import_errors
        . type IN('X::IMPORTER::VALIDATION::BRANDTITLEMISSING')) WHERE((
    (timestamp >= '2008-05-01T00:00:00')
    AND(timestamp <= '2008-05-06T13:12:30'))
        ) GROUP BY me
    . import_id

OK, not great, but not significantly worse than the SQL::Tidy.

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.