• Files
• Commits
• Log
• Graph
• Stats

use App::Class;

class App::Utils;

use Data::Printer;
use Term::ANSIColor qw/RESET :constants :constants256/;
 
# modification of Local::Utils dump_query
method dump_query ($sql, @bind) { # p $sql; p @bind;
    # replace each '?' with next element of array:
    $sql =~ s/\Q?/$_/ for @bind;
    # replace all white-space (spaces, tabs, new-lines) with single space:
    $sql =~ s/\s+/ /g; # p $sql;

    if ($sql =~ /^SELECT/) {
        # all queries have a SELECT ... FROM ...
        my ($cols, $remainder) = $sql =~ /SELECT (.*) FROM (.*)/;  # p $cols;
        # new-lines after comma in SELECT statement, unless inside brackets; doesn't
        # work for brackets-within-brackets eg LEFT(ref.name, .... LOCATE(' ', ....))
        $cols =~ s/(\,(?!\s?\?|[^(]+\)))(?!\n)/$1\n /g;      # p $cols;
        $remainder =~ s/(LEFT OUTER|INNER|\sAND\s)/\n  $1/g; # p $from;
        # re-build query string:
        $sql = qq!SELECT\n  $cols\nFROM\n  $remainder!;
        $sql =~ s/\b(WHERE|ORDER BY|GROUP BY|HAVING|LIMIT|OFFSET)\b/\n$1/g;

        my $lh_parens = "\Q(";
        my $rh_parens = "\Q)";
        $sql =~ s/(OR $lh_parens)/\n\t$1/g;
        # $sql =~ s/($lh_parens|$rh_parens)/\n\t$1/g; # also works OK
        # $sql =~ s/(OR|AND)/\n\t$1/g; # doesn't really work
    }
    elsif ($sql =~ /^INSERT/) {
        #$sql =~ s/(VALUES|ON CONFLICT)/\n  $1/g; # new-lines before VALUES or ON CONFLICT
        # new-line & spaces after '(' unless "ON CONFLICT("
        $sql =~ s{(?<!ON CONFLICT)\(}{\(\n  }g;
        # new-line before ')' unless ") DO UPDATE"
        $sql =~ s{\)(?! DO UPDATE)}{\n\)}g;
        # new-line & spaces after comma or "UPDATE SET"
        $sql =~ s{(,|UPDATE SET)}{$1\n  }g;
    }
    # not using UPDATE or DELETE in this app
    $sql .= ';' unless $sql =~ /;$/; # p $sql;

    my $divider = '#' . '-' x 80 ;

    # say STDERR $divider;
    # say STDERR color($sql)->cyan; # eg color($var)->foreground->background
    say STDERR ITALIC FAINT YELLOW ON_GREY3 $sql, RESET;
    say STDERR $divider;
 }

1;