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

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

From the documentation of DBIx::Class::Storage

If DBIC_TRACE is set then trace information is produced (as when the debug method is set).

...

debug
Causes trace information to be emitted on the debugobj object. (or STDERR if debugobj has not specifically been set).

debugobj
Sets or retrieves the object used for metric collection. Defaults to an instance of DBIx::Class::Storage::Statistics that is compatible with the original method of using a coderef as a callback. See the aforementioned Statistics class for more information.

In other words, you should set debugobj in that class to an object that subclasses DBIx::Class::Storage::Statistics. In your subclass, you can reformat the query the way you want it to be.

Answer 2

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?

Answer 3

As of DBIx::Class 0.08124 it's built in.

Just set $ENV{DBIC_TRACE_PROFILE} to console or console_monochrome.