NAME

Catalyst::TraitFor::Model::DBIC::Schema::QueryLog - DBIx::Class::QueryLog support for Catalyst::Model::DBIC::Schema

VERSION

version 0.03

SYNOPSIS

use base qw/Catalyst::Model::DBIC::Schema/;

__PACKAGE__->config({
    traits => ['QueryLog'],
    connect_info => 
        ['dbi:mysql:master', 'user', 'pass'],
});

# or
__PACKAGE__->config({
    traits => ['QueryLog'],
    connect_info => 
        ['dbi:mysql:master', 'user', 'pass'],
    querylog_args => {
        passthrough => 1,
    },
});

DESCRIPTION

check Catalyst::Model::DBIC::Schema for more details

Enable DBIx::Class::QueryLog support for Catalyst::Model::DBIC::Schema.

METHODS

querylog

an instance of DBIx::Class::QueryLog.

querylog_analyzer

an instance of DBIx::Class::QueryLog::Analyzer.

querylog_args

passed to DBIx::Class::QueryLog->new;

EXAMPLE CODE

<div class="featurebox">
  <h3>Query Log Report</h3>
  [% SET total = c.model('FilmDB').querylog.time_elapsed | format('%0.6f') %]
  <div>Total SQL Time: [% total | format('%0.6f') %] seconds</div>
  [% SET qcount = c.model('FilmDB').querylog.count %]
  <div>Total Queries: [% qcount %]</div>
  [% IF qcount %]
  <div>Avg Statement Time: [% (c.model('FilmDB').querylog.time_elapsed / qcount) | format('%0.6f') %] seconds.</div>
  <div>
   <table class="table1">
    <thead>
     <tr>
      <th colspan="3">5 Slowest Queries</th>
     </tr>
    </thead>
    <tbody>
     <tr>
      <th>Time</th>
      <th>%</th>
      <th>SQL</th>
     </tr>
     [% SET i = 0 %]
     [% FOREACH q = c.model('FilmDB').querylog_analyzer.get_sorted_queries %]
     <tr class="[% IF loop.count % 2 %]odd[% END %]">
      <th class="sub">[% q.time_elapsed | format('%0.6f') %]
      <td>[% ((q.time_elapsed / total ) * 100 ) | format('%i') %]%</td>
      <td>[% q.sql %] : ([% q.params.join(', ') %])</td>
     </th></tr>
     [% IF i == 5 %]
      [% LAST %]
     [% END %]
     [% SET i = i + 1 %]
     [% END %]
    </tbody>
   </table>
  </div>
  [% END %]
</div>

OR

my $total = sprintf('%0.6f', $c->model('DBIC')->querylog->time_elapsed);
$c->log->debug("Total SQL Time: $total seconds");
my $qcount = $c->model('DBIC')->querylog->count;
if ($qcount) {
  $c->log->debug("Avg Statement Time: " . sprintf('%0.6f', $total / $qcount));
  my $i = 0;
  my $qs = $c->model('DBIC')->querylog_analyzer->get_sorted_queries();
  foreach my $q (@$qs) {
    my $q_total = sprintf('%0.6f', $q->time_elapsed);
    my $q_percent = sprintf('%0.6f', ( ($q->time_elapsed / $total) * 100 ));
    my $q_sql = $q->sql . ' : ' . join(', ', @{$q->params});
    $c->log->debug("SQL: $q_sql");
    $c->log->debug("Costs: $q_total, takes $q_percent");
    last if ($i == 5);
    $i++;
  }
}

SEE ALSO

Catalyst::Model::DBIC::Schema

DBIx::Class::QueryLog

Catalyst::Component::InstancePerContext

AUTHOR

Fayland Lam <fayland@gmail.com>

COPYRIGHT AND LICENSE

This software is copyright (c) 2010 by Fayland Lam.

This is free software; you can redistribute it and/or modify it under the same terms as perl itself.