1package Jifty::Plugin::SQLQueries;
2use strict;
3use warnings;
4use base 'Jifty::Plugin';
5use List::Util 'sum';
6use Carp;
7use Scalar::Util;
8
9__PACKAGE__->mk_accessors(qw(stacktrace explain));
10
11sub prereq_plugins { 'RequestInspector' }
12
13sub init {
14    my $self = shift;
15    return if $self->_pre_init;
16
17    my %opts = (
18        stacktrace => 1,
19        explain    => 0,
20        @_,
21    );
22    $self->explain($opts{explain});
23    $self->stacktrace($opts{stacktrace});
24
25    Jifty->add_trigger(
26        post_init => sub { $self->post_init(@_) }
27    );
28}
29
30sub post_init {
31    my $self = shift;
32    Jifty->handle or return;
33
34    if ($self->stacktrace) {
35        Jifty->handle->log_sql_hook(SQLQueryPlugin_Stacktrace => sub {
36            my ($time, $statement, $bindings, $duration) = @_;
37            __PACKAGE__->log->debug(sprintf 'Query (%.3fs): "%s", with bindings: %s',
38                                $duration,
39                                $statement,
40                                join ', ',
41                                    map { defined $_ ? $_ : 'undef' } @$bindings,
42            );
43            return Carp::longmess("Query");
44        });
45    }
46
47    if ($self->explain) {
48        Jifty->handle->log_sql_hook(SQLQueryPlugin_Explain => sub {
49            my ($time, $statement, $bindings, $duration) = @_;
50            my $ret = Jifty->handle->dbh->selectcol_arrayref( "EXPLAIN $statement", {}, @{$bindings});
51            return $ret;
52        });
53    }
54}
55
56sub inspect_before_request {
57    my $self = shift;
58    Jifty->handle->log_sql_statements(1);
59    Jifty->handle->clear_sql_statement_log;
60}
61
62sub inspect_after_request {
63    Jifty->handle->log_sql_statements(0);
64    my $ret = [ Jifty->handle->sql_statement_log ];
65    Jifty->handle->clear_sql_statement_log;
66    return $ret;
67}
68
69sub inspect_render_summary {
70    my $self = shift;
71    my $log = shift;
72
73    my $count = @$log;
74    my $seconds = sprintf '%.2g', sum map { $_->[3] } @$log;
75
76    return _("%quant(%1,query,queries) taking %2s", $count, $seconds);
77}
78
79sub inspect_render_analysis {
80    my $self = shift;
81    my $log = shift;
82    my $id = shift;
83
84    Jifty::View::Declare::Helpers::render_region(
85        name => 'sqlqueries',
86        path => '/__jifty/admin/requests/queries',
87        args => {
88            id => $id,
89        },
90    );
91}
92
931;
94
95__END__
96
97=head1 NAME
98
99Jifty::Plugin::SQLQueries - Inspect your application's SQL queries
100
101=head1 DESCRIPTION
102
103This plugin will log each SQL query, its duration, its bind
104parameters, and its stack trace. Such reports are available at:
105
106    http://your.app/__jifty/admin/requests
107
108=head1 USAGE
109
110Add the following to your site_config.yml
111
112 framework:
113   Plugins:
114     - SQLQueries: {}
115
116You can turn on and off the stacktrace, as well as an "EXPLAIN" of
117each query, using options to the plugin:
118
119 framework:
120   Plugins:
121     - SQLQueries:
122         stacktrace: 0
123         explain: 1
124
125The plugin defaults to logging the stack trace, but not the explain.
126
127=head1 METHODS
128
129=head2 init
130
131Sets up a L</post_init> hook.
132
133=head2 inspect_before_request
134
135Clears the query log so we don't log any unrelated previous queries.
136
137=head2 inspect_after_request
138
139Stash the query log.
140
141=head2 inspect_render_summary
142
143Display how many queries and their total time.
144
145=head2 inspect_render_analysis
146
147Render a template with all the detailed information.
148
149=head2 post_init
150
151Tells L<Jifty::DBI> to log queries in a way that records stack traces.
152
153=head2 prereq_plugins
154
155This plugin depends on L<Jifty::Plugin::RequestInspector>.
156
157=head1 COPYRIGHT AND LICENSE
158
159Copyright 2007-2010 Best Practical Solutions
160
161This is free software and may be modified and distributed under the same terms as Perl itself.
162
163=cut
164
165