MySQLCasualLog.pm

package MySQLCasualLog;

use strict;
use warnings;
use utf8;

use DBIx::QueryLog;
use Term::ANSIColor;
use Text::ANSITable;

our $COLOR = "bold red";

{
    my $orig_logging = *DBIx::QueryLog::_logging{CODE};
    my @explain_cols = qw/id select_type table type possible_keys key key_len ref rows Extra/;

    sub explained_logging {
        my ($class, $dbh, $ret, $time, $bind_params, $explain) = @_;

        my $rows;
        my $badquery = 0;
        if ($explain) {
            $rows = $explain->();
            for my $row (@$rows) {
                if ($row->{select_type}) {
                    $row->{select_type} =~ s{
                        DEPENDENT\sUNION
                      | DEPENDENT\sSUBQUERY
                      | UNCACHEABLE\sUNION
                      | UNCACHEABLE\sSUBQUERY
                    }{
                        colored([$COLOR], $&)
                    }ex
                    and $badquery++;
                }
                if ($row->{type}) {
                    $row->{type} =~ s{
                        index | ALL
                    }{
                        colored([$COLOR], $&)
                    }ex
                    and $badquery++;
                }
                unless ($row->{possible_keys}) {
                    $row->{possible_keys} = colored([$COLOR], 'NULL');
                    $badquery++;
                }
                unless ($row->{key}) {
                    $row->{key} = colored([$COLOR], 'NULL');
                    $badquery++;
                }
                if ($row->{Extra}) {
                    $row->{Extra} =~ s{
                        Using\sfilesort
                      | Using\stemporary
                    }{
                        colored([$COLOR], $&)
                    }exg
                    and $badquery++;
                }
            }
        }

        if ($ENV{BADQUERY_ONLY} and $badquery == 0) {
            # nothing badquery
        } else {
            $explain = $ENV{HIDE_EXPLAIN} ? undef : sub {
                my %args = @_;

                return $rows unless $args{print};

                my $t = Text::ANSITable->new();
                $t->border_style('Default::single_ascii');
                $t->columns([@explain_cols]);
                $t->add_row([map { defined($_) ? $_ : 'NULL' } @{$_}{@explain_cols}]) for @$rows;

                $t->draw;
            };

            my $orig_color = DBIx::QueryLog->color;

            DBIx::QueryLog->color($COLOR);
            $orig_logging->($class, $dbh, $ret, $time, $bind_params, $explain);
            DBIx::QueryLog->color($orig_color);
        }
    }

    no warnings 'redefine';
    *DBIx::QueryLog::_logging = \&explained_logging;
}

DBIx::QueryLog->explain(1);

1;