I've been trying to track down some SQL issues with Tau Station and to be honest, I've never been terribly happy with the output from the DBIx::Class DBIC_TRACE or the DBI DBI_TRACE. So I have something better.

You need to install my Data::Record module (think "split" on steroids) and the Regexp::Common module.

use Data::Record;
use Regexp::Common;

my $schema = ...;

open my $fh, '>', $file
  or die "Cannot open $file for writing: $!";
my $split_sql = Data::Record->new(
    {   split  => ":",
        unless => $RE{quoted},
        limit  => 2,
    }
);
my $split_params = Data::Record->new(
    {   split  => ",",
        unless => $RE{quoted},
    }
);
my $split_placeholders = Data::Record->new(
    {   split  => "?",
        unless => $RE{quoted},
        chomp  => 0,
    }
);

my ( %seen_sql, %seen_with_params );

# turn on debugging
$schema->storage->debug(1);

# hey, it allows a callback!
$schema->storage->debugcb(
    sub ( $op, $info ) {
        my ( $sql, $params ) = $split_sql->records($info);
        my $seen = ++$seen_sql{$sql};
        chomp( my @params = $split_params->records($params) );

        # it's a hack, but it seems to work well
        my @fragments = $split_placeholders->records($sql);
        my $error;
        FRAGMENT: foreach (@fragments) {
            next unless /\?$/;
            unless (@params) {
                $error = '-- Found more placeholders than params';
                last FRAGMENT;
            }
            my $param = shift @params;
            s/\?$/$param/;
        }
        $sql = join '' => @fragments;
        if (@params) {
            $error = '-- Found fewer placeholders than params';
        }
        my $seen_with_params = ++$seen_with_params{$sql};
        my $times            = 1 == $seen ? 'time' : 'times';
        my $times_params     = 1 == $seen_with_params ? 'time' : 'times';
        say $fh $error if $error;
        say $fh
          "-- Seen $seen $times ($seen_with_params $times_params with params)";
        say $fh "$sql;";
    }
);

This code hooks into th DBIx::Class::Storage debug system and for each SELECT, INSERT, UPDATE, or DELETE, it substitutes the query params into the SQL so that every line in the debug file contains an SQL statement you can cut 'n paste directly into the database client. Before each SQL statement is an SQL comment indicating how many times that SQL was seen, along with how many times it was seen with those exact parameters.

So instead of this:

SELECT COUNT( * ) FROM character_skills me WHERE ( me.character_id = ? ): '17065'

You get this:

-- Seen 131 times (2 times with params)
SELECT COUNT( * ) FROM character_skills me WHERE ( me.character_id =  '17065' )

Here's an example from our full test suite run (wrapped and truncated to fit):

-- Seen 2871 times (1835 times with params)
SELECT ... FROM station_area me 
WHERE ( ( me.area_id =  '3' AND me.station_id =  '351' ) );

That's a beautiful target for optimizing because that data never changes.

Hire Me

Drop me a line at ovid at allaroundtheworld.fr if you'd like top-notch training or software development. Depending on your needs, we provide individual developers in a variety of languages (both computer and spoken), or entire teams with project managers