Merge lp:~percona-toolkit-dev/percona-toolkit/advisor-json-output into lp:percona-toolkit/2.1

[Daniel Nichter]

[Daniel Nichter (daniel-nichter)]

review: Approve

=== modified file 'bin/pt-query-advisor'
--- bin/pt-query-advisor	2012-05-24 20:55:51 +0000
+++ bin/pt-query-advisor	2012-06-01 14:45:24 +0000
@@ -2688,6 +2688,7 @@
 
 use Time::Local qw(timegm timelocal);
 use Digest::MD5 qw(md5_hex);
+use B qw();
 
 require Exporter;
 our @ISA         = qw(Exporter);
@@ -2705,6 +2706,7 @@
    any_unix_timestamp
    make_checksum
    crc32
+   encode_json
 );
 
 our $mysql_ts  = qr/(\d\d)(\d\d)(\d\d) +(\d+):(\d+):(\d+)(\.\d+)?/;
@@ -2912,6 +2914,96 @@
    return $crc ^ 0xFFFFFFFF;
 }
 
+my $got_json = eval { require JSON };
+sub encode_json {
+   return JSON::encode_json(@_) if $got_json;
+   my ( $data ) = @_;
+   return (object_to_json($data) || '');
+}
+
+
+sub object_to_json {
+   my ($obj) = @_;
+   my $type  = ref($obj);
+
+   if($type eq 'HASH'){
+      return hash_to_json($obj);
+   }
+   elsif($type eq 'ARRAY'){
+      return array_to_json($obj);
+   }
+   else {
+      return value_to_json($obj);
+   }
+}
+
+sub hash_to_json {
+   my ($obj) = @_;
+   my @res;
+   for my $k ( sort { $a cmp $b } keys %$obj ) {
+      push @res, string_to_json( $k )
+         .  ":"
+         . ( object_to_json( $obj->{$k} ) || value_to_json( $obj->{$k} ) );
+   }
+   return '{' . ( @res ? join( ",", @res ) : '' )  . '}';
+}
+
+sub array_to_json {
+   my ($obj) = @_;
+   my @res;
+
+   for my $v (@$obj) {
+      push @res, object_to_json($v) || value_to_json($v);
+   }
+
+   return '[' . ( @res ? join( ",", @res ) : '' ) . ']';
+}
+
+sub value_to_json {
+   my ($value) = @_;
+
+   return 'null' if(!defined $value);
+
+   my $b_obj = B::svref_2object(\$value);  # for round trip problem
+   my $flags = $b_obj->FLAGS;
+   return $value # as is 
+      if $flags & ( B::SVp_IOK | B::SVp_NOK ) and !( $flags & B::SVp_POK ); # SvTYPE is IV or NV?
+
+   my $type = ref($value);
+
+   if( !$type ) {
+      return string_to_json($value);
+   }
+   else {
+      return 'null';
+   }
+
+}
+
+my %esc = (
+   "\n" => '\n',
+   "\r" => '\r',
+   "\t" => '\t',
+   "\f" => '\f',
+   "\b" => '\b',
+   "\"" => '\"',
+   "\\" => '\\\\',
+   "\'" => '\\\'',
+);
+
+sub string_to_json {
+   my ($arg) = @_;
+
+   $arg =~ s/([\x22\x5c\n\r\t\f\b])/$esc{$1}/g;
+   $arg =~ s/\//\\\//g;
+   $arg =~ s/([\x00-\x08\x0b\x0e-\x1f])/'\\u00' . unpack('H2', $1)/eg;
+
+   utf8::upgrade($arg);
+   utf8::encode($arg);
+
+   return '"' . $arg . '"';
+}
+
 sub _d {
    my ($package, undef, $line) = caller 0;
    @_ = map { (my $temp = $_) =~ s/\n/\n# /g; $temp; }
@@ -5130,6 +5222,7 @@
    return bless $self, $class;
 }
 
+<<<<<<< TREE
 sub get_create_table {
    my ( $self, $dbh, $db, $tbl ) = @_;
    die "I need a dbh parameter" unless $dbh;
@@ -5179,6 +5272,55 @@
    return $href->{$key};
 }
 
+=======
+sub get_create_table {
+   my ( $self, $dbh, $db, $tbl ) = @_;
+   die "I need a dbh parameter" unless $dbh;
+   die "I need a db parameter"  unless $db;
+   die "I need a tbl parameter" unless $tbl;
+   my $q = $self->{Quoter};
+
+   my $sql = '/*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, '
+           . q{@@SQL_MODE := REPLACE(REPLACE(@@SQL_MODE, 'ANSI_QUOTES', ''), ',,', ','), }
+           . '@OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, '
+           . '@@SQL_QUOTE_SHOW_CREATE := 1 */';
+   PTDEBUG && _d($sql);
+   eval { $dbh->do($sql); };
+   PTDEBUG && $EVAL_ERROR && _d($EVAL_ERROR);
+
+   $sql = 'USE ' . $q->quote($db);
+   PTDEBUG && _d($dbh, $sql);
+   $dbh->do($sql);
+
+   $sql = "SHOW CREATE TABLE " . $q->quote($db, $tbl);
+   PTDEBUG && _d($sql);
+   my $href;
+   eval { $href = $dbh->selectrow_hashref($sql); };
+   if ( $EVAL_ERROR ) {
+      PTDEBUG && _d($EVAL_ERROR);
+      return;
+   }
+
+   $sql = '/*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, '
+        . '@@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */';
+   PTDEBUG && _d($sql);
+   $dbh->do($sql);
+
+   my ($key) = grep { m/create table/i } keys %$href;
+   if ( $key ) {
+      PTDEBUG && _d('This table is a base table');
+      $href->{$key}  =~ s/\b[ ]{2,}/ /g;
+      $href->{$key} .= "\n";
+   }
+   else {
+      PTDEBUG && _d('This table is a view');
+      ($key) = grep { m/create view/i } keys %$href;
+   }
+
+   return $href->{$key};
+}
+
+>>>>>>> MERGE-SOURCE
 sub parse {
    my ( $self, $ddl, $opts ) = @_;
    return unless $ddl;
@@ -6195,8 +6337,10 @@
       return $event;
    };
 
+   my $json = $o->get('report-type')->{json}
+            ? {} : undef;
    # Print info (advice) about each rule that matched this query.
-   if ( $groupby eq 'none' ) {
+   if ( $groupby eq 'none' || $json ) {
       push @pipeline, sub {
          my ( %args ) = @_;
          PTDEBUG && _d('callback: print advice');
@@ -6210,6 +6354,7 @@
             severity_count => \%severity_count,
             verbose        => $o->get('verbose'),
             report_format  => $o->get('report-format'),
+            json           => $json,
             Advisor        => $adv,
          );
          return $event;
@@ -6322,7 +6467,7 @@
    # ########################################################################
    # Aggregate and report items for group-by reports
    # ########################################################################
-   if ( $groupby ne 'none' ) {
+   if ( $groupby ne 'none' && !$json ) {
       print_grouped_report(
          advice_queue  => \%advice_queue,
          group_by      => $groupby,
@@ -6334,7 +6479,7 @@
    # ########################################################################
    # Create and print profile of each items note/warn/crit count.
    # ########################################################################
-   if ( keys %severity_count ) {
+   if ( keys %severity_count && !$json ) {
       eval {
          my $profile = new ReportFormatter(
             long_last_column => 1,
@@ -6365,6 +6510,8 @@
       };
    }
 
+   print Transformers::encode_json($json), "\n" if $json;
+
    return 0;
 }
 
@@ -6380,6 +6527,7 @@
    my $adv            = $args{Advisor};
    my $seen_id        = $args{seen_id};
    my $severity_count = $args{severity_count};
+   my $json           = $args{json};
 
    my $advice   = $event->{advice};
    my $near_pos = $event->{near_pos};
@@ -6388,7 +6536,9 @@
 
    # Header
    my $query_id = $event->{query_id} || "";
-   print "\n# Query ID 0x$query_id at byte " . ($event->{pos_in_log} || 0) . "\n";
+
+   print "\n# Query ID 0x$query_id at byte " . ($event->{pos_in_log} || 0) . "\n"
+      unless $json;
 
    # New check IDs and their descriptions
    foreach my $i ( 1..$n_advice ) {
@@ -6409,9 +6559,10 @@
                   : $verbose == 2 ? "$desc[0] $desc[1]"  # fuller
                   : $verbose >  2 ? $desc                # complete
                   :                 '';                  # none
-         print "# ", uc $info->{severity}, " $rule_id $desc\n";
+         print "# ", uc $info->{severity}, " $rule_id $desc\n"
+            unless $json;
 
-         if ( $pos ) {
+         if ( $pos && !$json ) {
             my $offset = $pos > POS_CONTEXT ? $pos - POS_CONTEXT : 0;
             print "#   matches near: ",
                substr($event->{arg}, $offset, ($pos - $offset) + POS_CONTEXT),
@@ -6419,14 +6570,24 @@
          }
       }
 
+      if ( $json ) {
+         my $info_for_json = {
+            rule => $rule_id,
+            %$info
+         };
+         push @{$json->{$query_id} ||= []}, $info_for_json;
+      }
+
       $severity_count->{$query_id}->{$info->{severity}}++;
    }
 
-   # Already seen check IDs
-   print "# Also: @seen_ids\n" if scalar @seen_ids;
-
-   # The query
-   print "$event->{arg}\n";
+   if ( !$json ) {
+      # Already seen check IDs
+      print "# Also: @seen_ids\n" if scalar @seen_ids;
+   
+      # The query
+      print "$event->{arg}\n";
+   }
 
    return;
 }
@@ -6476,7 +6637,6 @@
    my $verbose = $args{verbose} || 0;
    my %seen;
 
-
    foreach my $groupby_attrib ( sort keys %$advice_queue ) {
       print "\n" . ($groupby eq 'query_id' ? "0x" : "") . $groupby_attrib;
       foreach my $groupby_value (sort keys %{$advice_queue->{$groupby_attrib}}){
@@ -6549,14 +6709,29 @@
 
 Usage: pt-query-advisor [OPTION...] [FILE]
 
+<<<<<<< TREE
 pt-query-advisor analyzes queries and advises on possible problems. It can read
 queries from several types of log files, or you can use the --query or --review
 options.
+=======
+pt-query-advisor detects bad patterns in a SQL query from the text alone.
+>>>>>>> MERGE-SOURCE
 
+<<<<<<< TREE
 To analyze all queries in a MySQL slow query log file:
+=======
+Analyze all queries in a log in MySQL's slow query log format:
+>>>>>>> MERGE-SOURCE
 
    pt-query-advisor /path/to/slow-query.log
 
+<<<<<<< TREE
+=======
+Get queries from tcpdump using pt-query-digest:
+
+   pt-query-digest --type tcpdump.txt --print --no-report | pt-query-advisor
+
+>>>>>>> MERGE-SOURCE
 =head1 RISKS
 
 The following section is included to inform users about the potential risks,
@@ -6565,8 +6740,14 @@
 tools) and those created by bugs.
 
 pt-query-advisor simply reads queries and examines them, and is thus
+<<<<<<< TREE
 very low risk. At the time of this release we know of no issues that could harm
 users.
+=======
+very low risk.
+
+At the time of this release there are no known bugs that could harm users.
+>>>>>>> MERGE-SOURCE
 
 The authoritative source for updated information is always the online issue
 tracking system.  Issues that affect this tool will be marked as such.  You can
@@ -6648,14 +6829,16 @@
 
 severity: warn
 
-SELECT without WHERE.  The SELECT statement has no WHERE clause.
+SELECT without WHERE.  The SELECT statement has no WHERE clause and could
+examine many more rows than intended.
 
 =item CLA.002
 
 severity: note
 
 ORDER BY RAND().  ORDER BY RAND() is a very inefficient way to
-retrieve a random row from the results.
+retrieve a random row from the results, because it sorts the entire result
+and then throws most of it away.
 
 =item CLA.003
 
@@ -6663,7 +6846,8 @@
 
 LIMIT with OFFSET.  Paginating a result set with LIMIT and OFFSET is
 O(n^2) complexity, and will cause performance problems as the data
-grows larger.
+grows larger.  Pagination techniques such as bookmarked scans are much more
+efficient.
 
 =item CLA.004
 
@@ -6677,21 +6861,30 @@
 
 severity: warn
 
-ORDER BY constant column.
+ORDER BY constant column.  This is probably a bug in your SQL; at best it is a
+useless operation that does not change the query results.
 
 =item CLA.006
 
 severity: warn
 
-GROUP BY or ORDER BY different tables will force a temp table and filesort.
+GROUP BY or ORDER BY on different tables. This will force the use of a temporary
+table and filesort, which can be a huge performance problem and can consume
+large amounts of memory and temporary space on disk.
 
 =item CLA.007
 
 severity: warn
 
+<<<<<<< TREE
 ORDER BY clauses that sort the results in different directions prevents indexes
 from being used. All expressions in the ORDER BY clause must be ordered either
 ASC or DESC so that MySQL can use an index.
+=======
+ORDER BY different directions. All tables in the ORDER BY clause must be in the
+same direction, either ASC or DESC, or MySQL cannot use an index to avoid a sort
+after generating results.
+>>>>>>> MERGE-SOURCE
 
 =item COL.001
 
@@ -6723,8 +6916,9 @@
 
 severity: warn
 
-Unquoted date/time literal.  A query such as "WHERE col<2010-02-12"
-is valid SQL but is probably a bug; the literal should be quoted.
+Unquoted date/time literal.  A query such as "WHERE col<2010-02-12" is valid SQL
+but is probably a bug, because it will be interpreted as "WHERE col<1996"; the
+literal should be quoted.
 
 =item KWR.001
 
@@ -6739,23 +6933,25 @@
 
 severity: crit
 
-Mixing comma and ANSI joins.  Mixing comma joins and ANSI joins
-is confusing to humans, and the behavior differs between some
-MySQL versions.
+Mixing comma and ANSI joins.  Mixing comma joins and ANSI joins is confusing to
+humans, and the behavior and precedence differs between some MySQL versions,
+which can introduce bugs.
 
 =item JOI.002
 
 severity: crit
 
 A table is joined twice.  The same table appears at least twice in the
-FROM clause.
+FROM clause in a manner that can be reduced to a single access to the table.
 
 =item JOI.003
 
 severity: warn
 
-Reference to outer table column in WHERE clause prevents OUTER JOIN,
-implicitly converts to INNER JOIN.
+OUTER JOIN defeated.  The reference to an outer table column in the WHERE clause
+prevents the OUTER JOIN from returning any non-matched rows, which implicitly
+converts the query to an INNER JOIN.  This is probably a bug in the query or a
+misunderstanding of how OUTER JOIN works.
 
 =item JOI.004
 
@@ -6786,7 +6982,8 @@
 
 severity: note
 
-!= is non-standard.  Use the <> operator to test for inequality.
+The != operator is non-standard.  Use the <> operator to test for inequality
+instead.
 
 =item SUB.001
 
@@ -6794,9 +6991,14 @@
 
 IN() and NOT IN() subqueries are poorly optimized.  MySQL executes the subquery
 as a dependent subquery for each row in the outer query.  This is a frequent
+<<<<<<< TREE
 cause of serious performance problems.  This might improve in version 5.6 of
 MySQL, but for versions 5.1 and older, the query should be rewritten as a JOIN
 or a LEFT OUTER JOIN, respectively.
+=======
+cause of serious performance problems in MySQL 5.5 and older versions.  The
+query probably should be rewritten as a JOIN or a LEFT OUTER JOIN, respectively.
+>>>>>>> MERGE-SOURCE
 
 =back
 
@@ -6978,6 +7180,13 @@
 The type of input to parse (default slowlog).  The permitted types are
 slowlog and genlog.
 
+=item --report-type
+
+type: Hash
+
+Alternative formats to output the report. Currently, only "json" is
+recognized -- anything else is ignored and the default behavior used.
+
 =item --user
 
 short form: -u; type: string

=== modified file 'lib/Transformers.pm'
--- lib/Transformers.pm	2012-01-19 19:46:56 +0000
+++ lib/Transformers.pm	2012-06-01 14:45:24 +0000
@@ -29,6 +29,7 @@
 
 use Time::Local qw(timegm timelocal);
 use Digest::MD5 qw(md5_hex);
+use B qw();
 
 require Exporter;
 our @ISA         = qw(Exporter);
@@ -46,6 +47,7 @@
    any_unix_timestamp
    make_checksum
    crc32
+   encode_json
 );
 
 our $mysql_ts  = qr/(\d\d)(\d\d)(\d\d) +(\d+):(\d+):(\d+)(\.\d+)?/;
@@ -285,6 +287,98 @@
    return $crc ^ 0xFFFFFFFF;
 }
 
+my $got_json = eval { require JSON };
+sub encode_json {
+   return JSON::encode_json(@_) if $got_json;
+   my ( $data ) = @_;
+   return (object_to_json($data) || '');
+}
+
+# The following is a stripped down version of JSON::PP by Makamaka Hannyaharamitu
+# https://metacpan.org/module/JSON::PP
+
+sub object_to_json {
+   my ($obj) = @_;
+   my $type  = ref($obj);
+
+   if($type eq 'HASH'){
+      return hash_to_json($obj);
+   }
+   elsif($type eq 'ARRAY'){
+      return array_to_json($obj);
+   }
+   else {
+      return value_to_json($obj);
+   }
+}
+
+sub hash_to_json {
+   my ($obj) = @_;
+   my @res;
+   for my $k ( sort { $a cmp $b } keys %$obj ) {
+      push @res, string_to_json( $k )
+         .  ":"
+         . ( object_to_json( $obj->{$k} ) || value_to_json( $obj->{$k} ) );
+   }
+   return '{' . ( @res ? join( ",", @res ) : '' )  . '}';
+}
+
+sub array_to_json {
+   my ($obj) = @_;
+   my @res;
+
+   for my $v (@$obj) {
+      push @res, object_to_json($v) || value_to_json($v);
+   }
+
+   return '[' . ( @res ? join( ",", @res ) : '' ) . ']';
+}
+
+sub value_to_json {
+   my ($value) = @_;
+
+   return 'null' if(!defined $value);
+
+   my $b_obj = B::svref_2object(\$value);  # for round trip problem
+   my $flags = $b_obj->FLAGS;
+   return $value # as is 
+      if $flags & ( B::SVp_IOK | B::SVp_NOK ) and !( $flags & B::SVp_POK ); # SvTYPE is IV or NV?
+
+   my $type = ref($value);
+
+   if( !$type ) {
+      return string_to_json($value);
+   }
+   else {
+      return 'null';
+   }
+
+}
+
+my %esc = (
+   "\n" => '\n',
+   "\r" => '\r',
+   "\t" => '\t',
+   "\f" => '\f',
+   "\b" => '\b',
+   "\"" => '\"',
+   "\\" => '\\\\',
+   "\'" => '\\\'',
+);
+
+sub string_to_json {
+   my ($arg) = @_;
+
+   $arg =~ s/([\x22\x5c\n\r\t\f\b])/$esc{$1}/g;
+   $arg =~ s/\//\\\//g;
+   $arg =~ s/([\x00-\x08\x0b\x0e-\x1f])/'\\u00' . unpack('H2', $1)/eg;
+
+   utf8::upgrade($arg);
+   utf8::encode($arg);
+
+   return '"' . $arg . '"';
+}
+
 sub _d {
    my ($package, undef, $line) = caller 0;
    @_ = map { (my $temp = $_) =~ s/\n/\n# /g; $temp; }

=== modified file 't/lib/Transformers.t'
--- t/lib/Transformers.t	2012-03-06 13:56:08 +0000
+++ t/lib/Transformers.t	2012-06-01 14:45:24 +0000
@@ -1,5 +1,7 @@
 #!/usr/bin/perl
 
+# This file is encoded in UTF-8.
+
 BEGIN {
    die "The PERCONA_TOOLKIT_BRANCH environment variable is not set.\n"
       unless $ENV{PERCONA_TOOLKIT_BRANCH} && -d $ENV{PERCONA_TOOLKIT_BRANCH};
@@ -12,14 +14,14 @@
 use strict;
 use warnings FATAL => 'all';
 use English qw(-no_match_vars);
-use Test::More tests => 49;
+use Test::More tests => 74;
 
 use Transformers;
 use PerconaTest;
 
 Transformers->import( qw(parse_timestamp micro_t shorten secs_to_time
    time_to_secs percentage_of unix_timestamp make_checksum any_unix_timestamp
-   ts crc32) );
+   ts crc32 encode_json) );
 
 # #############################################################################
 # micro_t() tests.
@@ -189,6 +191,107 @@
    'any_unix_timestamp MySQL expression that looks like another type'
 );
 
+{
+   # Tests borrowed from http://api.metacpan.org/source/MAKAMAKA/JSON-2.53/t/08_pc_base.t
+   my $obj  = {};
+   my $js  = encode_json($obj);
+   is($js,'{}', '{}');
+   
+   $obj = [];
+   $js  = encode_json($obj);
+   is($js,'[]', '[]');
+   
+   $obj = {"foo" => "bar"};
+   $js  = encode_json($obj);
+   is($js,'{"foo":"bar"}', '{"foo":"bar"}');
+   
+   $js  = encode_json({"foo" => ""});
+   is($js,'{"foo":""}', '{"foo":""}');
+   
+   $js  = encode_json({"foo" => " "});
+   is($js,'{"foo":" "}' ,'{"foo":" "}');
+   
+   $js  = encode_json({"foo" => "0"});
+   is($js,'{"foo":"0"}',q|{"foo":"0"} - autoencode (default)|);
+   
+   $js  = encode_json({"foo" => "0 0"});
+   is($js,'{"foo":"0 0"}','{"foo":"0 0"}');
+   
+   $js  = encode_json([1,2,3]);
+   is($js,'[1,2,3]');
+   
+   $js  = encode_json({"foo"=>{"bar"=>"hoge"}});
+   is($js,q|{"foo":{"bar":"hoge"}}|);
+   
+   $obj = [{"foo"=>[1,2,3]},-0.12,{"a"=>"b"}];
+   $js  = encode_json($obj);
+   is($js,q|[{"foo":[1,2,3]},-0.12,{"a":"b"}]|);
+      
+   $obj = ["\x01"];
+   is(encode_json($obj),'["\\u0001"]');
+   
+   $obj = ["\e"];
+   is(encode_json($obj),'["\\u001b"]');
+
+   {
+      # http://api.metacpan.org/source/MAKAMAKA/JSON-2.53/t/07_pc_esc.t
+      use utf8;
+   
+      $obj = {test => qq|abc"def|};
+      my $str = encode_json($obj);
+      is($str,q|{"test":"abc\"def"}|);
+      
+      $obj = {qq|te"st| => qq|abc"def|};
+      $str = encode_json($obj);
+      is($str,q|{"te\"st":"abc\"def"}|);
+      
+      $obj = {test => q|abc\def|};
+      $str = encode_json($obj);
+      is($str,q|{"test":"abc\\\\def"}|);
+      
+      $obj = {test => "abc\bdef"};
+      $str = encode_json($obj);
+      is($str,q|{"test":"abc\bdef"}|);
+      
+      $obj = {test => "abc\fdef"};
+      $str = encode_json($obj);
+      is($str,q|{"test":"abc\fdef"}|);
+      
+      $obj = {test => "abc\ndef"};
+      $str = encode_json($obj);
+      is($str,q|{"test":"abc\ndef"}|);
+      
+      $obj = {test => "abc\rdef"};
+      $str = encode_json($obj);
+      is($str,q|{"test":"abc\rdef"}|);
+      
+      $obj = {test => "abc-def"};
+      $str = encode_json($obj);
+      is($str,q|{"test":"abc-def"}|);
+      
+      $obj = {test => "abc(def"};
+      $str = encode_json($obj);
+      is($str,q|{"test":"abc(def"}|);
+      
+      $obj = {test => "abc\\def"};
+      $str = encode_json($obj);
+      is($str,q|{"test":"abc\\\\def"}|);
+      
+      
+      $obj = {test => "あいうえお"};
+      $str = encode_json($obj);
+      my $expect = q|{"test":"あいうえお"}|;
+      utf8::encode($expect);
+      is($str,$expect);
+      
+      $obj = {"あいうえお" => "かきくけこ"};
+      $str = encode_json($obj);
+      $expect = q|{"あいうえお":"かきくけこ"}|;
+      utf8::encode($expect);
+      is($str,$expect);
+   }
+}
+
 
 # #############################################################################
 # Done.