Ovid

Quite often when running tests, you do something like this:

use Test::Most;

# do stuff

eq_or_diff $have, $want, '... and we gots whats we needs'
    or show $sql, $arguments;

Except that this likely will give you something like this:

# $sql = 'SELECT `format`.`id`, `format`.`title`, `format`.`title_cy`, `format`.`title_gd` FROM `format_container` `me` LEFT OUTER JOIN `format` `format` ON ( `format`.`id` = `me`.`format_id` ) WHERE ( `me`.`container_id` = ? )';
# $arguments = '\'c_series\'';

... and that's a simple one. I didn't like that, so I fixed it.

eq_or_diff $have, $want, '... and we gots whats we needs'
    or dump_sql($sql, $arguments);

That outputs something like:

# SQL: 
#   
#   SELECT `format`.`id`,
#      `format`.`title`,
#      `format`.`title_cy`,
#      `format`.`title_gd` 
#   FROM `format_container` `me` 
#       LEFT OUTER JOIN `format` `format` ON ( `format`.`id` = `me`.`format_id` ) 
#   WHERE ( `me`.`container_id` = ? )
# 
# ARGUMENTS: 
#   
#   'c_series'
# -------------------

The syntax highlighting isn't perfect, but it doesn't need to be. Also, the line breaks are done with a heuristic, so they're not perfect either, but they've not failed me yet. Best of all, if my colleagues don't have the needed modules installed, this falls back gracefully.

sub dump_sql {
  my ( $sql, $arguments ) = @_;

  eval "use Regexp::Common 'RE_quoted'";

  # Reformat the SQL and arguments
  my $re_quoted = RE_quoted();
  unless ($@) {
    my @sql = split /($re_quoted)/ => $sql;
    foreach (@sql) {
      next if /$re_quoted/;
      s/((?:LEFT )?(?:OUTER )?JOIN|AND)/\n      $1/g;
      s/(FROM|WHERE|(?:GROUP|ORDER) BY)/\n  $1/g;
      s/,/,\n    /g unless /$re_quoted/;
    }
    $sql = join '' => @sql;

    # this works because the arguments are actually the bind parameters
    # pulled from an SQL log. Season to taste
    my @arguments = split /($re_quoted)/ => $arguments;
    foreach (@arguments) {
      s/,/,\n  /g unless /$re_quoted/;
    }
    $arguments = join '' => @arguments;
  }

  # syntax highlight it
  eval <<'  END';
  use Syntax::Highlight::Engine::Kate::SQL_MySQL;
  END
  unless($@) {
    my $hl = Syntax::Highlight::Engine::Kate::SQL_MySQL->new(
      format_table => {
        'Keyword'      => [ GREEN,   RESET ],
        'Comment'      => [ BLUE,    RESET ],
        'Data Type'    => [ WHITE,   RESET ],
        'Decimal'      => [ YELLOW,  RESET ],
        'Float'        => [ YELLOW,  RESET ],
        'Function'     => [ CYAN,    RESET ],
        'Identifier'   => [ RED,     RESET ],
        'Normal'       => [ MAGENTA, RESET ],
        'Operator'     => [ CYAN,    RESET ],
        'Preprocessor' => [ RED,     RESET ],
        'String'       => [ RED,     RESET ],
        'String Char'  => [ RED,     RESET ],
        'Symbol'       => [ CYAN,    RESET ],
      }
    );
    $sql = $hl->highlightText($sql);
  }

  explain <<"  END";
SQL: 

  $sql

ARGUMENTS: 

  $arguments
-------------------
  END
}

The whole "split the string, munge it, rejoin it" behaviour is something I do often enough that I need a cleaner way of doing this. Maybe I could hook that into my Data::Record module?