Can I pretty-print the DBIC_TRACE output in DBIx::Class?

Question

Setting the DBIC_TRACE environment variable to true:

BEGIN { $ENV{DBIC_TRACE} = 1 }

generates very helpful output, especially showing the SQL query that is being executed, but the SQL query is all on one line.

Is there a way to push it through some kinda "sql tidy" routine to format it better, perhaps breaking it up over multiple lines? Failing that, could anyone give me a nudge into where in the code I'd need to hack to add such a hook? And what the best tool is to accept a badly formatted SQL query and push out a nicely formatted one?

"nice formatting" in this context simply means better than "all on one line". I'm not particularly fussed about specific styles of formatting queries

Thanks!

Answer 1

First, thanks for the pointers! Partial answer follows ....

What I've got so far ... first some scaffolding:

# Connect to our db through DBIx::Class
my $schema = My::Schema->connect('dbi:SQLite:/home/me/accounts.db');

# See also BEGIN { $ENV{DBIC_TRACE} = 1 }
$schema->storage->debug(1);

# Create an instance of our subclassed (see below)
# DBIx::Class::Storage::Statistics class
my $stats = My::DBIx::Class::Storage::Statistics->new();

# Set the debugobj object on our schema's storage
$schema->storage->debugobj($stats);

And the definition of My::DBIx::Class::Storage::Statistics being:

package My::DBIx::Class::Storage::Statistics;

use base qw<DBIx::Class::Storage::Statistics>;
use Data::Dumper qw<Dumper>;
use SQL::Statement;
use SQL::Parser;

sub query_start {
    my ($self, $sql_query, @params) = @_;

    print "The original sql query is\n$sql_query\n\n";

    my $parser = SQL::Parser->new();
    my $stmt   = SQL::Statement->new($sql_query, $parser);
    #printf "%s\n", $stmt->command;

    print "The parameters for this query are:";
    print Dumper \@params;
}

Which solves the problem about how to hook in to get the SQL query for me to "pretty-ify".

Then I run a query:

my $rs = $schema->resultset('SomeTable')->search(
    {   
        'email' => $email,
        'others.some_col' => 1,
    },
    { join => 'others' }
);
$rs->count;

However SQL::Parser barfs on the SQL generated by DBIx::Class:

The original sql query is
SELECT COUNT( * ) FROM some_table me LEFT JOIN others other_table ON ( others.some_col_id = me.id ) WHERE ( others.some_col_id = ? AND email = ? )

SQL ERROR: Bad table or column name '(others' has chars not alphanumeric or underscore!

SQL ERROR: No equijoin condition in WHERE or ON clause

So ... is there a better parser than SQL::Parser for the job?