package MySQL::Profile;

use strict;
use warnings;
use base qw(Exporter);
use HTML::Entities qw(encode_entities);
our @EXPORT;
our @EXPORT_OK = qw(mysql_profile mysql_profile_to_table);

sub mysql_profile {
    my ($dbh, $code) = @_;

    $dbh->do("SET profiling = 1");
    my @resp = eval { $code->($dbh) };
    $dbh->do("SET profiling = 0");

    my $prof = $dbh->selectall_arrayref("SHOW profile ALL", {Slice => {}});
    die {error => "$@", profile => $prof} if $@;

    return wantarray ? ($prof, @resp) : $prof;
}

sub mysql_profile_to_table {
    my $prof = shift;
    my $args = shift || {};

    my $fields = $args->{'fields'} || [qw(Duration CPU_user CPU_system)];

    my $totals = {Status => 'TOTAL'};
    for my $r (@$prof) {
        $totals->{$_} += $r->{$_} for grep {defined($r->{$_}) && $r->{$_} =~ /^[\d\.]+$/} keys %$r;
    }
    $totals->{$_} = sprintf('%.6f', $totals->{$_}) for @$fields;

    my $t = $args->{'table_head'} || "<table>\n";
    $t .= "<tr><th>".join("</th><th>", 'Status', @$fields)."</th><th>Other</th></tr>\n";
    for my $r (@$prof, $totals) {
        $t .= "<tr><td>".join("</td><td>", map {my $v = delete($r->{$_}); encode_entities(defined($v) ? $v : '')} 'Status', @$fields)."</td>"
            ."<td title=\"".join(",\n", map {my $v = $r->{$_}; "$_: ".encode_entities(defined($v) ? $v : '')} sort keys %$r)."\">Other</td></tr>\n";
    }
    $t .= "</table>\n";

    return $t;
}

=head1 ROUTINES

=over 4

=item mysql_profile

Takes a mysql DBI database handle, and a code ref.  The dbh will be
passed as the first argument to the code ref.  The code ref will be called
in an eval block in order to make sure that it has a chance to restore a mysql session
variable when done.  Returns a $profile arrayref in scalar context.  Returns the
$profile as well as any items returned from the coderef in wantarray context.

   my $prof = mysql_profile($dbh, sub { shift->do($sql, {}, $arg1) });

   my ($prof, @values) = mysql_profile($dbh, sub { shift->selectrow_array($sql) } );

   # you do not need to use the database handle passed to the code ref or return args through
   my @values;
   my $prof = mysql_profile($dbh, sub {
       my $sth = $dbh->prepare($sql);
       $sth->execute;
       @values = $sth->fetchrow;
   });

=item mysql_profile_to_table

Takes a profile returned by mysql_profile and returns a simple html table suitable for display.

   my $html = mysql_profile_to_table($prof);

   my $html = mysql_profile_to_table($prof, {table_head => '<table id="foo">'});

=back

=cut

##</code><code>##

#!/usr/bin/perl
use strict;
use warnings;
use MySQL::Profile qw(mysql_profile mysql_profile_to_table);
use DBI;
my $dbh = DBI->connect('mysql...'); # DBD::mysql handle that you provide


my ($prof, $now, $earlier) = mysql_profile($dbh, sub {
    shift->selectrow_array("SELECT NOW(), DATE_SUB(NOW(), INTERVAL 1 DAY)");
});

#print Data::Dumper->new([$prof, $now, $earlier], [qw(prof now earlier)])->Dump;

print mysql_profile_to_table($prof);