[svn:p5ee] r15575 - p5ee/trunk/App-Repository/bin

[spadkins]

Author: spadkins
Date: Fri Feb 15 09:47:44 2013
New Revision: 15575

Modified:
   p5ee/trunk/App-Repository/bin/analyze_debug_sql

Log:
improved the template_sql capabilities on analyze_debug_sql

Modified: p5ee/trunk/App-Repository/bin/analyze_debug_sql
==============================================================================
--- p5ee/trunk/App-Repository/bin/analyze_debug_sql     (original)
+++ p5ee/trunk/App-Repository/bin/analyze_debug_sql     Fri Feb 15 09:47:44 2013
@@ -5,6 +5,9 @@
 use App::Options (
     options => [qw()],
     option => {
+        sql_exclude => {
+            description => "regex to identify lines that should not be in an 
SQL statement",
+        },
     },
 );
 
@@ -15,6 +18,7 @@
         templates => {},
         tags      => {},
     );
+    my $sql_exclude = $App::options{sql_exclude};
     my $line_num = 0;
     while (<>) {
         $line_num++;
@@ -40,6 +44,9 @@
         elsif (/^DEBUG_SQL:/) {
             # discard
         }
+        elsif ($sql_exclude && /$sql_exclude/) {
+            # discard
+        }
         elsif ($collecting_sql) {
             $sql .= $_;
         }
@@ -52,6 +59,8 @@
 
 sub accumulate_sql_stats {
     my ($stash, $method, $sql, $nrows, $time) = @_;
+
+    return if (!$sql);
     my ($template_sql, $tag);
 
     my $templates = $stash->{templates};
@@ -80,21 +89,27 @@
         #    $method .= ":$1";
         #}
         $template_sql = $sql;
-        $template_sql =~ s/^(where|  and )(.*) in \(([^()]*)\)$/$1$2 in 
(xxx)/gm;
-        $template_sql =~ s/^(where|  and )(.*) = (.*)$/$1$2 = xxx/gm;
+        $template_sql =~ s/^(where| *and )(.*) +(in|not in) 
+\(([^()]*)\)$/$1$2 $3 (?)/gim;
+        $template_sql =~ s/^(where| *and )(.*) +(=|!=|<|>|<=|>=|<>) 
+(.*)$/$1$2 $3 ?/gim;
+        $template_sql =~ s/(where|and +)([^ ]+) +(=|!=|<|>|<=|>=|<>) +([^ 
]+)\)/$1$2 $3 ?/gim;
+        $template_sql =~ s/(where|and +)([^ ]+) +(in|not in) 
+\(([^()]*)\)/$1$2 $3 (?)/gim;
     }
-    elsif ($method eq "_get_rows") {
+    elsif ($method eq "_get_rows" || $method eq "_get_row") {
         $template_sql = $sql;
-        $template_sql =~ s/^(where|  and )(.*) in \(([^()]*)\)$/$1$2 in 
(xxx)/gm;
-        $template_sql =~ s/^(where|  and )(.*) = (.*)$/$1$2 = xxx/gm;
+        $template_sql =~ s/^(where| *and )(.*) +(in|not in) 
+\(([^()]*)\)$/$1$2 $3 (?)/gim;
+        $template_sql =~ s/^(where| *and )(.*) +(=|!=|<|>|<=|>=|<>) 
+(.*)$/$1$2 $3 ?/gim;
+        $template_sql =~ s/(where|and +)([^ ]+) +(=|!=|<|>|<=|>=|<>) +([^ 
]+)\)/$1$2 $3 ?/gim;
+        $template_sql =~ s/(where|and +)([^ ]+) +(in|not in) 
+\(([^()]*)\)/$1$2 $3 (?)/gim;
         # print "SQL=[$sql]\n";
         # print "TSQL=[$template_sql]\n";
     }
     elsif ($method eq "_update") {
         $template_sql = $sql;
-        $template_sql =~ s/where.*//s;
-        $template_sql =~ s/^(where|  and )(.*) in \(([^()]*)\)$/$1$2 in 
(xxx)/gm;
-        $template_sql =~ s/^(where|  and )(.*) = (.*)$/$1$2 = xxx/gm;
+        #$template_sql =~ s/where.*//s;
+        $template_sql =~ s/^(where| *and )(.*) +(in|not in) 
+\(([^()]*)\)$/$1$2 $3 (?)/gim;
+        $template_sql =~ s/^(where| *and )(.*) +(=|!=|<|>|<=|>=|<>) 
+(.*)$/$1$2 $3 ?/gim;
+        $template_sql =~ s/(where|and +)([^ ]+) +(=|!=|<|>|<=|>=|<>) +([^ 
]+)\)/$1$2 $3 ?/gim;
+        $template_sql =~ s/(where|and +)([^ ]+) +(in|not in) 
+\(([^()]*)\)/$1$2 $3 (?)/gim;
     }
     else {
         print "WARNING: Don't know how to accumulate stats for [$method]\n";
@@ -102,6 +117,9 @@
 
     $methods->{$method}{count}++;
 
+    if (! exists $templates->{$template_sql}) {
+        $templates->{$template_sql} = {};
+    }
     $tag = $templates->{$template_sql}{tag};
     if (!defined $tag) {
         $methods->{$method}{seq}++;
@@ -137,7 +155,7 @@
     }
     print 
"============================================================================\n";
     printf("%-16s %9s %9s %9s %9s %9s %9s\n", "TAG", "COUNT", "NROWS", 
"CUMULTIME", "AVGTIME", "MINTIME", "MAXTIME");
-    foreach my $tag (sort keys %$tags) {
+    foreach my $tag (sort { $tags->{$b}{cumul_time} <=> 
$tags->{$a}{cumul_time} } keys %$tags) {
         printf("%-16s %9d %9d %9.3f %9.5f %9.5f %9.5f\n",
             $tag,
             $tags->{$tag}{count},
@@ -158,7 +176,10 @@
             $tags->{$tag}{cumul_time}/$tags->{$tag}{count},
             $tags->{$tag}{min_time},
             $tags->{$tag}{max_time});
+        print "-- -- SAMPLE SQL 
-----------------------------------------------------------\n";
         print $tags->{$tag}{sql};
+        print "-- -- TEMPLATE SQL 
---------------------------------------------------------\n";
+        print $tags->{$tag}{template_sql};
     }
     #print "ROWS=[$nrows] TIME=[$time] SQL=[$sql]\n";
 }