1# This program is copyright 2008-2011 Percona Ireland Ltd.
2# Feedback and improvements are welcome.
3#
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.
7#
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
12# licenses.
13#
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
19# ###########################################################################
20{
21# Package: QueryReview
22# QueryReview is an API to a query review table.
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.
26package QueryReview;
27
28use strict;
29use warnings FATAL => 'all';
30use English qw(-no_match_vars);
31use constant PTDEBUG => $ENV{PTDEBUG} || 0;
32
33Transformers->import(qw(make_checksum parse_timestamp));
34
35# These columns are the minimal set of columns for every review table.  TODO:
36# maybe it's possible to specify this in the tool's POD and pass it in so it's
37# not hardcoded here and liable to get out of sync.
38my %basic_cols = map { $_ => 1 }
39   qw(checksum fingerprint sample first_seen last_seen reviewed_by
40      reviewed_on comments);
41my %skip_cols  = map { $_ => 1 } qw(fingerprint sample checksum);
42
43# Required args:
44# dbh           A dbh to the server with the query review table.
45# db_tbl        Full quoted db.tbl name of the query review table.
46#               Make sure the table exists! It's not checked here;
47#               check it before instantiating an object.
48# tbl_struct    Return val from TableParser::parse() for db_tbl.
49#               This is used to discover what columns db_tbl has.
50# quoter        Quoter object.
51#
52# Optional args:
53# ts_default    SQL expression to use when inserting a new row into
54#               the review table.  If nothing else is specified, NOW()
55#               is the default.  This is for dependency injection while
56#               testing.
57sub new {
58   my ( $class, %args ) = @_;
59   foreach my $arg ( qw(dbh db_tbl tbl_struct quoter) ) {
60      die "I need a $arg argument" unless $args{$arg};
61   }
62
63   foreach my $col ( keys %basic_cols ) {
64      die "Query review table $args{db_tbl} does not have a $col column"
65         unless $args{tbl_struct}->{is_col}->{$col};
66   }
67
68   my $now = defined $args{ts_default} ? $args{ts_default} : 'NOW()';
69
70   # Design statements to INSERT and statements to SELECT from the review table.
71   my $sql = <<"      SQL";
72      INSERT INTO $args{db_tbl}
73      (checksum, fingerprint, sample, first_seen, last_seen)
74      VALUES(CONV(?, 16, 10), ?, ?, COALESCE(?, $now), COALESCE(?, $now))
75      ON DUPLICATE KEY UPDATE
76         first_seen = IF(
77            first_seen IS NULL,
78            COALESCE(?, $now),
79            LEAST(first_seen, COALESCE(?, $now))),
80         last_seen = IF(
81            last_seen IS NULL,
82            COALESCE(?, $now),
83            GREATEST(last_seen, COALESCE(?, $now)))
84      SQL
85   PTDEBUG && _d('SQL to insert into review table:', $sql);
86   my $insert_sth = $args{dbh}->prepare($sql);
87
88   # The SELECT statement does not need to get the fingerprint, sample or
89   # checksum.
90   my @review_cols = grep { !$skip_cols{$_} } @{$args{tbl_struct}->{cols}};
91   $sql = "SELECT "
92        . join(', ', map { $args{quoter}->quote($_) } @review_cols)
93        . ", CONV(checksum, 10, 16) AS checksum_conv FROM $args{db_tbl}"
94        . " WHERE checksum=CONV(?, 16, 10)";
95   PTDEBUG && _d('SQL to select from review table:', $sql);
96   my $select_sth = $args{dbh}->prepare($sql);
97
98   my $self = {
99      dbh         => $args{dbh},
100      db_tbl      => $args{db_tbl},
101      insert_sth  => $insert_sth,
102      select_sth  => $select_sth,
103      tbl_struct  => $args{tbl_struct},
104      quoter      => $args{quoter},
105      ts_default  => $now,
106   };
107   return bless $self, $class;
108}
109
110# Fetch information from the database about a query that's been reviewed.
111sub get_review_info {
112   my ( $self, $id ) = @_;
113   $self->{select_sth}->execute(make_checksum($id));
114   my $review_vals = $self->{select_sth}->fetchall_arrayref({});
115   if ( $review_vals && @$review_vals == 1 ) {
116      return $review_vals->[0];
117   }
118   return undef;
119}
120
121# Store a query into the table.  The arguments are:
122#  *  fingerprint
123#  *  sample
124#  *  first_seen
125#  *  last_seen
126# There's no need to convert the fingerprint to a checksum, no need to parse
127# timestamps either.
128sub set_review_info {
129   my ( $self, %args ) = @_;
130   $self->{insert_sth}->execute(
131      make_checksum($args{fingerprint}),
132      @args{qw(fingerprint sample)},
133      map { $args{$_} ? parse_timestamp($args{$_}) : undef }
134         qw(first_seen last_seen first_seen first_seen last_seen last_seen));
135}
136
137# Return the columns we'll be using from the review table.
138sub review_cols {
139   my ( $self ) = @_;
140   return grep { !$skip_cols{$_} } @{$self->{tbl_struct}->{cols}};
141}
142
143sub _d {
144   my ($package, undef, $line) = caller 0;
145   @_ = map { (my $temp = $_) =~ s/\n/\n# /g; $temp; }
146        map { defined $_ ? $_ : 'undef' }
147        @_;
148   print STDERR "# $package:$line $PID ", join(' ', @_), "\n";
149}
150
1511;
152}
153# ###########################################################################
154# End QueryReview package
155# ###########################################################################
156