1
# This program is copyright 2008-2011 Percona Inc.
2
# Feedback and improvements are welcome.
4
# THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED
5
# WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF
6
# MERCHANTIBILITY AND FITNESS FOR A PARTICULAR PURPOSE.
8
# This program is free software; you can redistribute it and/or modify it under
9
# the terms of the GNU General Public License as published by the Free Software
10
# Foundation, version 2; OR the Perl Artistic License. On UNIX and similar
11
# systems, you can issue `man perlgpl' or `man perlartistic' to read these
14
# You should have received a copy of the GNU General Public License along with
15
# this program; if not, write to the Free Software Foundation, Inc., 59 Temple
16
# Place, Suite 330, Boston, MA 02111-1307 USA.
17
# ###########################################################################
18
# QueryReview package $Revision: 7342 $
19
# ###########################################################################
23
# This module is an interface to a "query review table" in which certain
24
# historical information about classes of queries is stored. See the docs on
25
# mk-query-digest for context.
28
use warnings FATAL => 'all';
29
use English qw(-no_match_vars);
30
Transformers->import(qw(make_checksum parse_timestamp));
34
use constant MKDEBUG => $ENV{MKDEBUG} || 0;
36
# These columns are the minimal set of columns for every review table. TODO:
37
# maybe it's possible to specify this in the tool's POD and pass it in so it's
38
# not hardcoded here and liable to get out of sync.
39
my %basic_cols = map { $_ => 1 }
40
qw(checksum fingerprint sample first_seen last_seen reviewed_by
41
reviewed_on comments);
42
my %skip_cols = map { $_ => 1 } qw(fingerprint sample checksum);
45
# dbh A dbh to the server with the query review table.
46
# db_tbl Full quoted db.tbl name of the query review table.
47
# Make sure the table exists! It's not checked here;
48
# check it before instantiating an object.
49
# tbl_struct Return val from TableParser::parse() for db_tbl.
50
# This is used to discover what columns db_tbl has.
51
# quoter Quoter object.
54
# ts_default SQL expression to use when inserting a new row into
55
# the review table. If nothing else is specified, NOW()
56
# is the default. This is for dependency injection while
59
my ( $class, %args ) = @_;
60
foreach my $arg ( qw(dbh db_tbl tbl_struct quoter) ) {
61
die "I need a $arg argument" unless $args{$arg};
64
foreach my $col ( keys %basic_cols ) {
65
die "Query review table $args{db_tbl} does not have a $col column"
66
unless $args{tbl_struct}->{is_col}->{$col};
69
my $now = defined $args{ts_default} ? $args{ts_default} : 'NOW()';
71
# Design statements to INSERT and statements to SELECT from the review table.
73
INSERT INTO $args{db_tbl}
74
(checksum, fingerprint, sample, first_seen, last_seen)
75
VALUES(CONV(?, 16, 10), ?, ?, COALESCE(?, $now), COALESCE(?, $now))
76
ON DUPLICATE KEY UPDATE
80
LEAST(first_seen, COALESCE(?, $now))),
84
GREATEST(last_seen, COALESCE(?, $now)))
86
MKDEBUG && _d('SQL to insert into review table:', $sql);
87
my $insert_sth = $args{dbh}->prepare($sql);
89
# The SELECT statement does not need to get the fingerprint, sample or
91
my @review_cols = grep { !$skip_cols{$_} } @{$args{tbl_struct}->{cols}};
93
. join(', ', map { $args{quoter}->quote($_) } @review_cols)
94
. ", CONV(checksum, 10, 16) AS checksum_conv FROM $args{db_tbl}"
95
. " WHERE checksum=CONV(?, 16, 10)";
96
MKDEBUG && _d('SQL to select from review table:', $sql);
97
my $select_sth = $args{dbh}->prepare($sql);
101
db_tbl => $args{db_tbl},
102
insert_sth => $insert_sth,
103
select_sth => $select_sth,
104
tbl_struct => $args{tbl_struct},
105
quoter => $args{quoter},
108
return bless $self, $class;
111
# Tell QueryReview object to also prepare to save values in the review history
113
sub set_history_options {
114
my ( $self, %args ) = @_;
115
foreach my $arg ( qw(table dbh tbl_struct col_pat) ) {
116
die "I need a $arg argument" unless $args{$arg};
119
# Pick out columns, attributes and metrics that need to be stored in the
123
foreach my $col ( @{$args{tbl_struct}->{cols}} ) {
124
my ( $attr, $metric ) = $col =~ m/$args{col_pat}/;
125
next unless $attr && $metric;
127
# TableParser lowercases the column names so, e.g., Query_time
128
# becomes query_time. We have to fix this so attribs in the event
129
# match keys in $self->{history_metrics}...
131
# If the attrib name has at least one _ then it's a multi-word
132
# attrib like Query_time or Lock_time, so the first letter should
133
# be uppercase. Else, it's a one-word attrib like ts, checksum
134
# or sample, so we leave it alone. Except Filesort which is yet
136
$attr = ucfirst $attr if $attr =~ m/_/;
137
$attr = 'Filesort' if $attr eq 'filesort';
139
$attr =~ s/^Qc_hit/QC_Hit/; # Qc_hit is really QC_Hit
140
$attr =~ s/^Innodb/InnoDB/g; # Innodb is really InnoDB
141
$attr =~ s/_io_/_IO_/g; # io is really IO
144
push @metrics, [$attr, $metric];
147
my $sql = "REPLACE INTO $args{table}("
149
map { $self->{quoter}->quote($_) } ('checksum', 'sample', @cols))
150
. ') VALUES (CONV(?, 16, 10), ?'
151
. (@cols ? ', ' : '') # issue 1265
153
# ts_min and ts_max might be part of the PK, in which case they must
155
$_ eq 'ts_min' || $_ eq 'ts_max'
156
? "COALESCE(?, $self->{ts_default})"
161
$self->{history_sth} = $args{dbh}->prepare($sql);
162
$self->{history_metrics} = \@metrics;
167
# Save review history for a class of queries. The incoming data is a bunch
168
# of hashes. Each top-level key is an attribute name, and each second-level key
169
# is a metric name. Look at the test for more examples.
170
sub set_review_history {
171
my ( $self, $id, $sample, %data ) = @_;
172
# Need to transform ts->min/max into timestamps
173
foreach my $thing ( qw(min max) ) {
174
next unless defined $data{ts} && defined $data{ts}->{$thing};
175
$data{ts}->{$thing} = parse_timestamp($data{ts}->{$thing});
177
$self->{history_sth}->execute(
180
map { $data{$_->[0]}->{$_->[1]} } @{$self->{history_metrics}});
183
# Fetch information from the database about a query that's been reviewed.
184
sub get_review_info {
185
my ( $self, $id ) = @_;
186
$self->{select_sth}->execute(make_checksum($id));
187
my $review_vals = $self->{select_sth}->fetchall_arrayref({});
188
if ( $review_vals && @$review_vals == 1 ) {
189
return $review_vals->[0];
194
# Store a query into the table. The arguments are:
199
# There's no need to convert the fingerprint to a checksum, no need to parse
201
sub set_review_info {
202
my ( $self, %args ) = @_;
203
$self->{insert_sth}->execute(
204
make_checksum($args{fingerprint}),
205
@args{qw(fingerprint sample)},
206
map { $args{$_} ? parse_timestamp($args{$_}) : undef }
207
qw(first_seen last_seen first_seen first_seen last_seen last_seen));
210
# Return the columns we'll be using from the review table.
213
return grep { !$skip_cols{$_} } @{$self->{tbl_struct}->{cols}};
217
my ($package, undef, $line) = caller 0;
218
@_ = map { (my $temp = $_) =~ s/\n/\n# /g; $temp; }
219
map { defined $_ ? $_ : 'undef' }
221
print STDERR "# $package:$line $PID ", join(' ', @_), "\n";
225
# ###########################################################################
226
# End QueryReview package
227
# ###########################################################################
added
removed
lib/QueryReview.pm
