C::M::DBIC::Schema::QyeryLog: can't collect any stats...

[Marcello Romani]

Again on querylog vs. catalyst...

I've setup a minimal app to test the querylog stuff, but, I can't get
any results, i.e. the statistics obtained from querylog are always zero.

The attached file contains the entire app; here I show the most relevant
files.

I think I must be missing something obvious...

Thanks in advance for any help.


Model/Main.pm:
==============
package TestQueryLog::Model::Main;

use strict;
use warnings;
use base 'Catalyst::Model::DBIC::Schema::QueryLog';

__PACKAGE__->config(
     schema_class => 'TestQueryLog::Schema',
     connect_info => [
         'dbi:Pg:dbname=testquerylog',
         'testquerylog',
         'testquerylog',
         { AutoCommit => 1, PrintError => 1, RaiseError => 1 },
     ],
);

1;


Schema.pm:
==========

package TestQueryLog::Schema;

use strict;
use warnings;

use base 'DBIx::Class::Schema';

__PACKAGE__->load_classes;

1;


Schema/Users.pm:
================

package TestQueryLog::Schema::Users;

use strict;
use warnings;

use base qw/ DBIx::Class /;

__PACKAGE__->load_components( qw/ PK::Auto Core / );
__PACKAGE__->table( 'users' );
__PACKAGE__->add_columns( qw/ id name email / );
__PACKAGE__->set_primary_key( qw/ id / );

1;


Controller/Users.pm:
====================

package TestQueryLog::Controller::Users;

use strict;
use warnings;
use base 'Catalyst::Controller';

sub list : Local {
     my ( $self, $c ) = @_;

     $c->stash->{items} = $c->model('Users');

     return;
}

[snip]

1;


users/list.tt
=============

<table>
     [% SET item = items.next %]
     [% WHILE item %]
         <tr>
             <td>[% item.id %]</td>
             <td>[% item.name %]</td>
             <td>[% item.email %]</td>
         </tr>
         [% SET item = items.next %]
     [% END %]
</table>



--
Marcello Romani
Responsabile IT
Ottotecnica s.r.l.
http://www.ottotecnica.com

_______________________________________________
List: Catalyst@...
Listinfo: http://lists.rawmode.org/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@.../
Dev site: http://dev.catalyst.perl.org/

[Cory G Watson]

On 9/19/07, Marcello Romani <mromani@...> wrote:
> Again on querylog vs. catalyst...
>
> I've setup a minimal app to test the querylog stuff, but, I can't get
> any results, i.e. the statistics obtained from querylog are always zero.
>
> The attached file contains the entire app; here I show the most relevant
> files.

Not being familiar with the Model, I've just looked at the code and it
seems pretty simple, creating a QueryLog instance for each schema.

You didn't show how you were getting the information OUT of QueryLog.
The docs for the model contain an example of such.  The first mistake
I could imagine is creating a new schema and then asking it for
QueryLog info.  If it's different from the schema you used in your app
then the log would be empty.

--
Cory 'G' Watson
http://www.onemogin.com

_______________________________________________
List: Catalyst@...
Listinfo: http://lists.rawmode.org/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@.../
Dev site: http://dev.catalyst.perl.org/

[Marcello Romani]

Cory Watson ha scritto:

> On 9/19/07, Marcello Romani <mromani@...> wrote:
>> Again on querylog vs. catalyst...
>>
>> I've setup a minimal app to test the querylog stuff, but, I can't get
>> any results, i.e. the statistics obtained from querylog are always zero.
>>
>> The attached file contains the entire app; here I show the most relevant
>> files.
>
> Not being familiar with the Model, I've just looked at the code and it
> seems pretty simple, creating a QueryLog instance for each schema.
>
> You didn't show how you were getting the information OUT of QueryLog.
> The docs for the model contain an example of such.  The first mistake
> I could imagine is creating a new schema and then asking it for
> QueryLog info.  If it's different from the schema you used in your app
> then the log would be empty.
>

First of all, thanks for the response and for your tip.

I get the info out of querylog by putting it into the stash and by
calling its methods from the same template used in the docs of the
schema module (the only difference being I don't call c.model('FilmDB')
every time in the template, but only once in the controller).

The controller is QueryLog.pm and the template is querylog/index.tt.
Both files are included in the tar.gz archive.

I copy/paste them here for convenience.

Thanks again.


Controller/QueryLog.pm:
=======================
package TestQueryLog::Controller::QueryLog;

use strict;
use warnings;
use base 'Catalyst::Controller';

sub index : Private {
     my ( $self, $c ) = @_;

     $c->stash->{querylog} = $c->model('Main')->querylog();

     return;
}

1;


querylog/index.tt:
==================
<div class="featurebox">
   <h3>Query Log Report</h3>
   [% SET total = querylog.time_elapsed | format('%0.6f') %]
   <div>Total SQL Time: [% total | format('%0.6f') %] seconds</div>
   [% SET qcount = querylog.count %]
   <div>Total Queries: [% qcount %]</div>
   [% IF qcount %]
     <div>
       Avg Statement Time:
       [% (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 = querylog_analyzer.get_sorted_queries %]
                  <tr class="[% IF loop.count % 2 %]odd[% END %]">
                    <th class="sub">
                      [% q.time_elapsed | format('%0.6f') %]
                    </th>
                    <td>
                      [% ((q.time_elapsed / total ) * 100 )
                        | format('%i') %]%
                    </td>
                    <td>[% q.sql %] : ([% q.params.join(', ') %])</td>
                  </tr>
                  [% IF i == 5 %]
                    [% LAST %]
                  [% END %]
                  [% SET i = i + 1 %]
                [% END %]
              </tbody>
           </table>
       </div>
   [% END %]
</div>


--
Marcello Romani
Responsabile IT
Ottotecnica s.r.l.
http://www.ottotecnica.com

_______________________________________________
List: Catalyst@...
Listinfo: http://lists.rawmode.org/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@.../
Dev site: http://dev.catalyst.perl.org/

[Cory G Watson]

On 9/19/07, Marcello Romani <mromani@...> wrote:
> First of all, thanks for the response and for your tip.
>
> I get the info out of querylog by putting it into the stash and by
> calling its methods from the same template used in the docs of the
> schema module (the only difference being I don't call c.model('FilmDB')
> every time in the template, but only once in the controller).

I can't say if Model::DBIC::QueryLog works, as I'm not familiar with
writing a Model for Catalyst.  I can say, however, the QueryLog does
as I'm using it in my apps as we speak.

I know the author of the Model is on this list or the DBIC, just not
sure which one.  Perhaps he can lend us a hand.

--
Cory 'G' Watson
http://www.onemogin.com

_______________________________________________
List: Catalyst@...
Listinfo: http://lists.rawmode.org/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@.../
Dev site: http://dev.catalyst.perl.org/

[Marcello Romani]

Cory Watson ha scritto:

> On 9/19/07, Marcello Romani <mromani@...> wrote:
>> First of all, thanks for the response and for your tip.
>>
>> I get the info out of querylog by putting it into the stash and by
>> calling its methods from the same template used in the docs of the
>> schema module (the only difference being I don't call c.model('FilmDB')
>> every time in the template, but only once in the controller).
>
> I can't say if Model::DBIC::QueryLog works, as I'm not familiar with
> writing a Model for Catalyst.  I can say, however, the QueryLog does
> as I'm using it in my apps as we speak.
>
> I know the author of the Model is on this list or the DBIC, just not
> sure which one.  Perhaps he can lend us a hand.
>

Do you use it in cat apps on in plain dbic ?

Could you provide an example of usage ?

Thank you.

--
Marcello Romani
Responsabile IT
Ottotecnica s.r.l.
http://www.ottotecnica.com

_______________________________________________
List: Catalyst@...
Listinfo: http://lists.rawmode.org/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@.../
Dev site: http://dev.catalyst.perl.org/

[Cory G Watson]

On 9/19/07, Marcello Romani <mromani@...> wrote:
> Do you use it in cat apps on in plain dbic ?
>
> Could you provide an example of usage ?

http://www.onemogin.com/blog/554-profile-your-catalystdbixclass-app-with-querylog.html

The syntax has changed a bit due to the Analyzer, but you should be
able to make the leap.

--
Cory 'G' Watson
http://www.onemogin.com

_______________________________________________
List: Catalyst@...
Listinfo: http://lists.rawmode.org/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@.../
Dev site: http://dev.catalyst.perl.org/

[Marcello Romani]

Cory Watson ha scritto:

> On 9/19/07, Marcello Romani <mromani@...> wrote:
>> Do you use it in cat apps on in plain dbic ?
>>
>> Could you provide an example of usage ?
>
> http://www.onemogin.com/blog/554-profile-your-catalystdbixclass-app-with-querylog.html
>
> The syntax has changed a bit due to the Analyzer, but you should be
> able to make the leap.
>

Thank you, but I had read that article before.
While interesting and useful, it doesn't provide any code example on how
to actually use dbic::querylog in a cat app (I read the
$schema->debugobj($ql_instance) and $schema->debug(1) instruction, but
where do I exactly put them in a cat app, since the cat model's
connection is delayed until needed ?).

I went on with my tests, and I think your suggestion was right.
If I put this line in the TT template:

[% querylog %]

I get something like DBIx::Class::QueryLog=HASH(0x93b48a8)
where the hash address changes every time I refersh the page.
I think the address should be the same, otherwise I always get a fresh
object which of course doesn't have any statistics in it... Right ?

--
Marcello Romani
Responsabile IT
Ottotecnica s.r.l.
http://www.ottotecnica.com

_______________________________________________
List: Catalyst@...
Listinfo: http://lists.rawmode.org/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@.../
Dev site: http://dev.catalyst.perl.org/

[Marcello Romani]

Cory Watson ha scritto:

> On 9/19/07, Marcello Romani <mromani@...> wrote:
>> Do you use it in cat apps on in plain dbic ?
>>
>> Could you provide an example of usage ?
>
> http://www.onemogin.com/blog/554-profile-your-catalystdbixclass-app-with-querylog.html
>
> The syntax has changed a bit due to the Analyzer, but you should be
> able to make the leap.
>

I think I said something stupid about querylog object's address in my
previous post, so I put a warn() statement just after the call to
DBIx::Class::QyeryLog->new(); in my code. I could verify that only one
call to that method is ever made (until I restart the app, obviously).
So my guess is that instance should collect the stats...

--
Marcello Romani
Responsabile IT
Ottotecnica s.r.l.
http://www.ottotecnica.com

_______________________________________________
List: Catalyst@...
Listinfo: http://lists.rawmode.org/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@.../
Dev site: http://dev.catalyst.perl.org/

[Marcello Romani]

Cory Watson ha scritto:

> On 9/19/07, Marcello Romani <mromani@...> wrote:
>> Do you use it in cat apps on in plain dbic ?
>>
>> Could you provide an example of usage ?
>
> http://www.onemogin.com/blog/554-profile-your-catalystdbixclass-app-with-querylog.html
>
> The syntax has changed a bit due to the Analyzer, but you should be
> able to make the leap.
>

Ok I finally found my mistake.

I was obtaining the queryobject back from the schema like this:

my $ql = $schema->storage->debugobj();

Now, instead, I "manually" store the querylog object, and get it back
from where I put it, instead of getting it like I showed above.

Now everything works fine.

Thanks again for your time.

--
Marcello Romani
Responsabile IT
Ottotecnica s.r.l.
http://www.ottotecnica.com

_______________________________________________
List: Catalyst@...
Listinfo: http://lists.rawmode.org/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@.../
Dev site: http://dev.catalyst.perl.org/