Commits

tuck committed 02c9e00 Merge

backport branch 0.2 to trunk prepare tag 0.2

  • Participants
  • Parent commits a297b5b, 602d63f
  • Tags v_0.2.0

Comments (0)

Files changed (7)

 CHANGES
 =======
 
+0.2.0 (2012-02-13)
+------------------
+    monitor number of sql queries per request
+    monitor the average time of sql passed inside a request
+    add 2 new columns inside table report
+    some other enhancement :
+
+         - using now config.scan and @view_config 
+    this version modify the db structure. The easiest is to
+    delete the file (perf_app.db) inside your webapp directory
+
 0.1.0 (2012-02-08)
 ------------------
     stylesheet enhancement
 pyramid_perstat application depends on **pyramid** (a light webframework)
 see : http://www.pylonsproject.org/ for more information
 
-In fact this application is a pyramid tween (a contraction of the 
-word between). It surrounds your webapp in order to measure performance
-over time.
+In fact this application is a pyramid tween
+( look at http://docs.pylonsproject.org/projects/pyramid/en/1.3-branch/whatsnew-1.2.html?highlight=tween
+for more information). It surrounds your webapp in order to measure performance over time.
 
-Pyramid_debugtoolbar is a very good tween to analyze the code
-and measure the performance of a view. It's very accurate and
-it adds a very unique exposure of youre code. Pyramid perfstat try
-to record each request and its execution time. Thus it can
-display averages by view or route. This gives you an overview of your
+Pyramid_debugtoolbar is a very good tween to analyze the code and measure the performance of a view. It's very accurate and
+it adds a very unique exposure of youre code. Pyramid perfstat try to record each request and its execution time and some other stuff
+(average queries count per request etc ...). Thus it can display averages by view or route. This gives you an overview of your
 application in order to quickly determine its points of improvement.
 
 For the moment it's only tested with pyramid route dispatch scaffold.
 
 it's published under BSD licence.
 
-===========================================================
+Note :
+======
+
+     if you use a previous version of pyramid_perfstat please
+     remove perf_app.db before updating pyramid_perfstat.
+     0.2 version is incompatible with the structure of the previous base.
+
+     Look at changes for more information.
+
+Requirement :
+=============
+
+
+     - pyramid 1.2>
+
+Usage :
+=======
 
 it's have been tested with pythons dependencies of pyramid.
 

File pyramid_perfstat/__init__.py

 
 from pyramid_perfstat.utils import STATIC_PATH, ROUTE_PREFIX
 from pyramid_perfstat.perf_serializer import PerfDbManager
-from pyramid_perfstat.perf_serializer import new_perfstat_request_subscriber
-
 
 def to_int(*segment_names):
     def predicate(info, request):
 def perf_tween_factory(handler, registry):
     """
     """
-    
     PerfDbManager(None).init_db()
     
     def perf_tween(request, getLogger=logging.getLogger):
         """
-        TODO : should avoid it's own url
-        TODO : get route_name
-        TODO : get view name (or full path,function name ...)
         """
         t0=time.time()
         result = handler(request)
 
         perf_manager = PerfDbManager(request)
         perf_manager.insert_data(time.time()-t0)
-        
         return result
     
     return perf_tween
     config.add_route('pyramid_perfstat.reporting.url_detail', '/stat/{id_session}/{id_view}/{id_route}',
                      custom_predicates=(to_int('id_session', 'id_view', 'id_route'),))
     
-    config.add_view('pyramid_perfstat.views.reporting',
-                    route_name='pyramid_perfstat.reporting',
-                    renderer='pyramid_perfstat:templates/reporting.mako')
-
-    config.add_view('pyramid_perfstat.views.reporting',
-                    route_name='pyramid_perfstat.reporting.session_detail',
-                    renderer='pyramid_perfstat:templates/reporting.mako')
-
-    config.add_view('pyramid_perfstat.views.reporting',
-                    route_name='pyramid_perfstat.reporting.view_detail',
-                    renderer='pyramid_perfstat:templates/reporting.mako')
-
-    config.add_view('pyramid_perfstat.views.reporting',
-                    route_name='pyramid_perfstat.reporting.url_detail',
-                    renderer='pyramid_perfstat:templates/reporting.mako')
-
-    config.add_view('pyramid_perfstat.views.reset_db',
-                    route_name='pyramid_perfstat.reset')
     
-
 def includeme(config):
     """
     Set up am implicit :term:`tween` to log performance information of each
     such as mean time, number of request urls etc ...
     """
     config.add_static_view(ROUTE_PREFIX+'/static', STATIC_PATH)
-    config.add_subscriber(new_perfstat_request_subscriber,NewRequest)
     
     config.add_tween('pyramid_perfstat.perf_tween_factory', under=EXCVIEW)
     config.include(include_perf_routes, route_prefix=ROUTE_PREFIX)
 
+    # scan views
+    config.scan('pyramid_perfstat.views')
+    config.scan('pyramid_perfstat.perf_serializer')
+

File pyramid_perfstat/perf_serializer.py

 # -*- coding: utf-8 -*- 
 #-----------------------------------------------------------------------------
 # Name:        pyramid_perfstat/perf_serializer.py
-# Purpose:     
+# Purpose:     serialize recorded data inside a db
+#              for the moment only sqlite db is supported ...
+#
+#              this module don't use sqlalchemy or ORM to keep 
+#              performance as best as possible. SqlAlchemy would
+#              bring confort and will decrease performance as well...
+#              pyramid_perfstat try to be as light as possible
+#              
+#              (even if sql is boring ...  :'( )
+#
+#              to reduce overhead :
+#                    - no object
+#                    - procedural code
+#                    - simple sql query
+#
+#              TODO : add some configuration key to change default db
+#              TODO : add interface for serialization
+#              TODO : write other db target
 #
 # Author:      sbard
 #
 
 import logging
 import datetime
+import sqlite3
+import time
+import threading
 
 from zope.interface import Interface
 
+from pyramid.events import subscriber
+from pyramid.events import NewRequest
+
 from pyramid.interfaces import IRouteRequest
 from pyramid.interfaces import IRoutesMapper
 from pyramid.interfaces import IViewClassifier
 from pyramid.interfaces import IView
 
-import sqlite3
+from pyramid.threadlocal import get_current_request
+from pyramid_perfstat.utils import ROUTE_PREFIX
+from sqlalchemy.ext.sqlsoup import SqlSoup
 
-from pyramid.events import NewRequest
-from pyramid_perfstat.utils import ROUTE_PREFIX
+lock = threading.Lock()
 
+try:
+    from sqlalchemy import event
+    from sqlalchemy.engine.base import Engine
+
+    @event.listens_for(Engine, "before_cursor_execute")
+    def _before_cursor_execute(conn, cursor, stmt, params, context, execmany):
+        setattr(conn, 'pfstb_start_timer', time.time())
+
+    @event.listens_for(Engine, "after_cursor_execute")
+    def _after_cursor_execute(conn, cursor, stmt, params, context, execmany):
+        stop_timer = time.time()
+        request = get_current_request()
+        duration = stop_timer - conn.pfstb_start_timer
+        if request is not None:
+            with lock:
+                duration_queries = getattr(request, 'pfstb_duration_queries', [])
+                duration_queries.append(duration)
+                setattr(request, 'pfstb_duration_queries', duration_queries)
+        delattr(conn, 'pfstb_start_timer')
+    has_sqla = True
+except ImportError:
+    has_sqla = False
+
+@subscriber(NewRequest)
 def new_perfstat_request_subscriber(event):
+    """
+    open the connection to sqlite db
+    subscribe to event NewRequest
+    """
     request = event.request
     request._PerfDbManager__perfstat_db = sqlite3.connect('perf_app.db')
     request.add_finished_callback(close_db_connection)
 
 def close_db_connection(request):
+    """
+    close the connection to sqlite db
+    """
     request._PerfDbManager__perfstat_db.close()
 
 class PerfDbManager(object):
         else :
             self.conn = request.__perfstat_db
         
+        self.request = request
+
         if request is not None :
             self.matched_url = request.path_url
             self.route = request.matched_route
                                                                route_name text,
                                                                view_name text,
                                                                measure real,
+                                                               sql_avg_duration real,
+                                                               sql_nb_queries integer,
                                                                date_measure datetime )""")
+
+        c.execute("""create index if not exists idx_url on pyramid_perf (url)""")
+        c.execute("""create index if not exists idx_route on pyramid_perf (route_name)""")
+        c.execute("""create index if not exists idx_view on pyramid_perf (view_name)""")
+
         if from_scratch :
             c.execute("""drop table if exists pyramid_agg_route_perf""")
             
         c.execute("""create table if not exists pyramid_agg_route_perf ( id integer PRIMARY KEY,
                                                                id_measure integer,
-                                                               url text,
                                                                route_name text,
                                                                view_name text,
                                                                measure real,
+                                                               sql_avg_duration real,
+                                                               sql_nb_queries integer,
                                                                fetch_count integer )""")
 
+        c.execute("""create index if not exists idx_url on pyramid_agg_route_perf (url)""")
+        c.execute("""create index if not exists idx_route on pyramid_agg_route_perf (route_name)""")
+        c.execute("""create index if not exists idx_view on pyramid_agg_route_perf (view_name)""")
+
         if from_scratch :
             c.execute("""drop table if exists pyramid_agg_view_perf""")
             
                                                                id_measure integer,
                                                                view_name text,
                                                                measure real,
+                                                               sql_avg_duration real,
+                                                               sql_nb_queries integer,
                                                                fetch_count integer )""")
-        
+        c.execute("""create index if not exists idx_view on pyramid_agg_view_perf (view_name)""")
+
         self.conn.commit()
         self.end_connection()
 
         return resume measure liste
         """
         c = self.conn.cursor()
-        c.execute("""select pr.id, pr.measure, pr.url from pyramid_perf pr
+        c.execute("""select pr.id, pr.measure, pr.sql_avg_duration, pr.sql_nb_queries, pr.url from pyramid_perf pr
                      join pyramid_agg_route_perf rp on rp.id=%d and pr.route_name=rp.route_name
                      where rp.id_measure=%d and pr.id_measure=%d"""%(id_route, id_measure, id_measure))
         rows = c.fetchall()
         :param id_view: an view recorded id
         """
         c = self.conn.cursor()
-        c.execute("""select pr.id, pv.id, pr.measure, pr.url, pr.route_name, pr.view_name, pr.fetch_count
+        c.execute("""select pr.id, pv.id, pr.measure, pr.sql_avg_duration, pr.sql_nb_queries,
+                            pr.route_name, pr.view_name, pr.fetch_count
                      from pyramid_agg_route_perf pr
                      join pyramid_agg_view_perf pv on pv.id=%d and pr.view_name=pv.view_name
-                     where pr.id_measure=%d order by pr.measure desc, pr.fetch_count desc, pr.url desc"""%(id_view,id_measure))
+                     where pr.id_measure=%d order by pr.measure desc, pr.fetch_count desc"""%(id_view,id_measure))
         rows = c.fetchall()
         c.close()
         return rows 
         :param id_measure: the session id
         """
         c = self.conn.cursor()
-        c.execute("""select id, measure, view_name, fetch_count from pyramid_agg_view_perf 
-                     where id_measure=%d order by measure desc, fetch_count desc, view_name desc"""%id_measure)
+        c.execute("""select id, measure, sql_avg_duration, sql_nb_queries, view_name, fetch_count 
+                     from pyramid_agg_view_perf 
+                     where id_measure=%d
+                     order by measure desc, fetch_count desc, view_name desc"""%id_measure)
         rows = c.fetchall()
         c.close()
         return rows 
 
     def get_last_route_mean_perf(self):
         """
-          return last value inserted in pyramid_agg_route_perf
+          return last values inserted in pyramid_agg_route_perf
         """
         c = self.conn.cursor()
-        c.execute("""select measure, fetch_count from pyramid_agg_route_perf 
-                    where id_measure=%d and url='%s'"""%(self.session_perf_id, self.matched_url))
+        c.execute("""select measure, sql_avg_duration, sql_nb_queries, fetch_count from pyramid_agg_route_perf 
+                    where id_measure=%d and route_name='%s'"""%(self.session_perf_id, self.matched_route_name))
         rows = c.fetchall()
-        measure, fetch_count = None, None
+        measure, sql_avg_duration, sql_nb_queries, fetch_count = None, None, None, None
         if rows :
-           measure, fetch_count = rows[0]
+           measure, sql_avg_duration, sql_nb_queries, fetch_count = rows[0]
         c.close()
-        return measure, fetch_count
-
+        return measure, sql_avg_duration, sql_nb_queries, fetch_count
 
     def get_last_view_mean_perf(self):
         """
           return last value inserted in pyramid_agg_route_perf
         """
         c = self.conn.cursor()
-        c.execute("""select measure, fetch_count from pyramid_agg_view_perf 
+        c.execute("""select measure, sql_avg_duration, sql_nb_queries, fetch_count from pyramid_agg_view_perf 
                     where id_measure=%d and view_name='%s'"""%(self.session_perf_id, self.view_name))
         rows = c.fetchall()
-        measure, fetch_count = None, None
+        measure, sql_avg_duration, sql_nb_queries, fetch_count = None, None, None, None
         if rows :
-           measure, fetch_count = rows[0]
+           measure, sql_avg_duration, sql_nb_queries, fetch_count = rows[0]
         c.close()
-        return measure, fetch_count
+        return measure, sql_avg_duration, sql_nb_queries, fetch_count
 
-    def update_mean_route_perf_value(self, resume_perf, insert=False):
+    def update_mean_route(self, request_duration_time, sql_sigma_duration, sql_nb_queries):
         """
-            return last value inserted in pyramid_agg_route_perf
+        """
+        last_record_resume, last_sql_avg_duration, last_sql_nb_queries, last_cpt = self.get_last_route_mean_perf()
+        new_record_resume = self.cumulative_upkeep(last_record_resume, last_cpt, request_duration_time)
+        
+        new_sql_sigma_duration = 0.0
+        new_sql_nb_queries = 0
+        if sql_sigma_duration is not None :
+            new_sql_sigma_duration = self.cumulative_upkeep(last_sql_avg_duration, last_cpt, sql_sigma_duration)
+            new_sql_nb_queries = int(round(self.cumulative_upkeep(last_sql_nb_queries, last_cpt, sql_nb_queries)))
+        if last_record_resume is None :
+            self.update_mean_route_perf_value(new_record_resume, new_sql_sigma_duration, new_sql_nb_queries, insert=True)
+        else :
+            self.update_mean_route_perf_value(new_record_resume, new_sql_sigma_duration, new_sql_nb_queries)
+
+    def update_mean_route_perf_value(self, resume_perf, sql_resume_perf, sql_nb_queries, insert=False):
+        """
+        :resume_perf: the average to request time duration
+        :sql_resume_perf: the average to sql time duration
+        :sql_nb_queries: the number of queries occured during view call
+        :key-param insert: this key-word indicate we should insert or update
         """
         c = self.conn.cursor()
         if insert :
             c.execute("""insert into pyramid_agg_route_perf values 
-                     (null, ?, ?, ?, ?, ?, 1)""", (self.session_perf_id,
-                                                  self.matched_url,
-                                                  self.matched_route_name,
-                                                  self.view_name,
-                                                  resume_perf,))
+                     (null, ?, ?, ?, ?, ?, ?, 1)""", (self.session_perf_id,
+                                                      self.matched_route_name,
+                                                      self.view_name,
+                                                      resume_perf,
+                                                      sql_resume_perf,
+                                                      sql_nb_queries))
         else :
             c.execute("""update pyramid_agg_route_perf
-             set measure=%f, fetch_count=fetch_count+1 
-                     where id_measure=%d and url='%s'"""%(resume_perf,
+             set measure=%f, sql_avg_duration=%f, sql_nb_queries=%d, fetch_count=fetch_count+1 
+                     where id_measure=%d and route_name='%s'"""%(resume_perf,
+                                                   sql_resume_perf,
+                                                   sql_nb_queries,
                                                    self.session_perf_id,
-                                                   self.matched_url))
+                                                   self.matched_route_name))
         c.close()
 
-    def update_mean_view_perf_value(self, resume_perf, insert=False):
+    def update_mean_view(self, request_duration_time, sql_sigma_duration, sql_nb_queries):
         """
+        update the average time table for view resume
+        
+        :param request_duration_time: 
+        :param sql_sigma_duration:
+        :param sql_nb_queries:
+        """
+        last_record_resume, last_sql_avg_duration, last_sql_nb_queries, last_cpt = self.get_last_view_mean_perf()
+        new_avg_resume = self.cumulative_upkeep(last_record_resume, last_cpt, request_duration_time)
+        new_sql_sigma_duration = 0.0
+        new_sql_nb_queries = 0
+        
+        if sql_sigma_duration is not None :
+            new_sql_sigma_duration = self.cumulative_upkeep(last_sql_avg_duration, last_cpt, sql_sigma_duration)
+            new_sql_nb_queries = int(round(self.cumulative_upkeep(last_sql_nb_queries, last_cpt, sql_nb_queries)))
+        
+        if last_record_resume is None :
+            self.update_mean_view_perf_value(new_avg_resume, new_sql_sigma_duration, new_sql_nb_queries, insert=True)
+        else :
+            self.update_mean_view_perf_value(new_avg_resume, new_sql_sigma_duration, new_sql_nb_queries)
+        
+
+    def update_mean_view_perf_value(self, request_time_avg, sql_resume_perf, sql_nb_queries, insert=False):
+        """
+           update every recorded params linked to the current view
+           :request_time_avg:
+           :sql_resume_perf:
+           :sql_nb_queries:
+           :insert: boolean
         """
         c = self.conn.cursor()
         if insert :
             c.execute("""insert into pyramid_agg_view_perf values 
-                     (null, ?, ?, ?, 1)""", (self.session_perf_id,
-                                            self.view_name,
-                                            resume_perf,))
+                     (null, ?, ?, ?, ?, ?, 1)""", (self.session_perf_id,
+                                                   self.view_name,
+                                                   request_time_avg,
+                                                   sql_resume_perf,
+                                                   sql_nb_queries))
         else :
             c.execute("""update pyramid_agg_view_perf
-             set measure=%f, fetch_count=fetch_count+1 
-                     where id_measure=%d and view_name='%s'"""%(resume_perf,
-                                                   self.session_perf_id,
-                                                   self.view_name))         
+             set measure=%f, sql_avg_duration=%f, sql_nb_queries=%d, fetch_count=fetch_count+1 
+                     where id_measure=%d and view_name='%s'"""%(request_time_avg,
+                                                                sql_resume_perf,
+                                                                sql_nb_queries,
+                                                                self.session_perf_id,
+                                                                self.view_name))
         c.close()
+    
+    def cumulative_upkeep(self, last_resume, last_cpt, perf):
+        """
+        :param last_resume: the last recorded average
+        :param last_cpt: last average number of elements having served for the calculation
+        :param perf: new record
+        """
+        result = None
+        if last_resume is None :
+           result = perf
+        else :
+           result = (last_resume+(perf/float(last_cpt)))*(float(last_cpt)/float(last_cpt+1))
+        return result
+    
+    def get_queries_information(self):
+        """
+        if sql alchemy is know. we can return sql query sigma time execution
+        :param return :
+              sql_sigma_duration : sum duration of every sql request executed during the request
+              sql_nb_queries : the number of queries executed in the request
+        """
+        sql_sigma_duration, sql_nb_queries = None, None
         
-    def insert_data(self, perf):
+        if self.request is not None and has_sqla and hasattr(self.request,'pfstb_duration_queries') :
+              duration_queries_liste = self.request.pfstb_duration_queries
+              # sum duration of every sql request executed during the request
+              sql_sigma_duration = sum(duration_queries_liste)
+              # the number of queries executed in the request
+              sql_nb_queries = len(duration_queries_liste)
+
+        return sql_sigma_duration, sql_nb_queries
+    
+    def insert_data(self, request_duration_time):
         """
-        insert the perf data in db
-        :param matched_url: the application pyramid url
-        :param matched_route_name: the application pyramid matched route name
-               if url dispatched si used and request.matched_route is fill
-        :param pef: the time measure (should be ms)
+        insert all recorded data in db
+        :param request_duration_time: the request time duration
         """
+        
+        sql_sigma_duration, sql_nb_queries = self.get_queries_information()
+
         # we forget pyramid_perfstat self routes
         if self.matched_route_name is not None and self.matched_url.count(ROUTE_PREFIX) == 0 :
              c = self.conn.cursor()
              c.execute("""insert into pyramid_perf values 
-                          (null, ?, ?, ?, ?, ?, ?)""", (self.session_perf_id,
-                                                        self.matched_url,
-                                                        self.matched_route_name,
-                                                        self.view_name,
-                                                        perf,
-                                                        datetime.datetime.now(),))
+                          (null, ?, ?, ?, ?, ?, ?, ?, ?)""", (self.session_perf_id,
+                                                             self.matched_url,
+                                                             self.matched_route_name,
+                                                             self.view_name,
+                                                             request_duration_time,
+                                                             sql_sigma_duration,
+                                                             sql_nb_queries,
+                                                             datetime.datetime.now(),))
      
-             last_record_resume, last_cpt = self.get_last_route_mean_perf()
-     
-             if last_record_resume is None :
-                last_record_resume = perf
-                self.update_mean_route_perf_value(last_record_resume, insert=True)
-             else :
-                last_record_resume = (last_record_resume+(perf/float(last_cpt)))*(float(last_cpt)/float(last_cpt+1))
-                self.update_mean_route_perf_value(last_record_resume)
-     
-             last_record_resume, last_cpt = self.get_last_view_mean_perf()
-             if last_record_resume is None :
-                last_record_resume = perf
-                self.update_mean_view_perf_value(last_record_resume, insert=True)
-             else :
-                last_record_resume = (last_record_resume+(perf/float(last_cpt)))*(float(last_cpt)/float(last_cpt+1))
-                self.update_mean_view_perf_value(last_record_resume)
-     
-     
+             # update specific view table
+             self.update_mean_view(request_duration_time, sql_sigma_duration, sql_nb_queries)
+             
+             # update specific route table 
+             self.update_mean_route(request_duration_time, sql_sigma_duration, sql_nb_queries)
+             
              self.conn.commit()
              c.close()
 

File pyramid_perfstat/templates/reporting.mako

 	% endif
 </%def>
 
-<%def name="publish_url_row(id, avg_time, url)">
+<%def name="publish_url_row(id, request_avg_time, sql_avg_time, sql_nb_queries, url)">
 	<tr>
 		<td>${id}</td>
-		<td>${get_color_perf(avg_time)}</td>
-		<td>${get_label_perf(avg_time)}</td>
+		<td>${get_color_perf(request_avg_time)}</td>
+		<td>${get_label_perf(request_avg_time)}</td>
+            % if sql_avg_time is not None and sql_avg_time >= 0 :
+		<td>${get_color_perf(sql_avg_time)}</td>
+            % else :
+		<td>-</td>
+            % endif
+        <td>${sql_nb_queries}</td>
 		<td>${url}</td>
 	</tr>
 </%def>
 
-<%def name="publish_agg_routes_row(id, id_view, avg_time, url, route_name, view_name, cpt)">
+<%def name="publish_agg_routes_row(id, id_view, request_avg_time, sql_avg_time, sql_nb_queries, route_name, view_name, cpt)">
 	<tr>
 		<td>${id}</td>
-		<td>${get_color_perf(avg_time)}</td>
-		<td>${get_label_perf(avg_time)}</td>
-		<td>${url}</td>
+		<td>${get_color_perf(request_avg_time)}</td>
+		<td>${get_label_perf(request_avg_time)}</td>
+            % if sql_avg_time is not None and sql_avg_time > 0 :
+		<td>${get_color_perf(sql_avg_time)}</td>
+            % else :
+		<td>-</td>
+            % endif
+        <td>${sql_nb_queries}</td>
 		<td>
 				  <a href="${request.route_url("pyramid_perfstat.reporting.url_detail", id_session=id_session, id_view=id_view, id_route=id)}">${route_name}</a>
 				</td>
 	</tr>
 </%def>
 
-<%def name="publish_agg_view_row(id, avg_time, view_name, cpt)">
+<%def name="publish_agg_view_row(id, request_avg_time, sql_avg_time, sql_nb_queries, view_name, cpt)">
 	<tr>
 		<td>${id}</td>
-		<td>${get_color_perf(avg_time)}</td>
-		<td>${get_label_perf(avg_time)}</td>
+		<td>${get_color_perf(request_avg_time)}</td>
+		<td>${get_label_perf(request_avg_time)}</td>
+            % if sql_avg_time is not None and sql_avg_time > 0 :
+		<td>${get_color_perf(sql_avg_time)}</td>
+            % else :
+		<td>-</td>
+            % endif
+        <td>${sql_nb_queries}</td>
 		<td><a href="${request.route_url("pyramid_perfstat.reporting.view_detail",id_session=id_session, id_view=id)}">${view_name}</a></td>
 		<td>${cpt}</td>
 	</tr>
 	min-width: 50px;
 }
 
+#perf_views_reporting_table  tbody td:nth-child(4),
+#perf_routes_reporting_table tbody td:nth-child(4),
+#perf_urls_reporting_table   tbody td:nth-child(4) {
+	text-align: right;
+	width: 5%;
+	min-width: 30px;
+}
+
 #perf_views_reporting_table  tbody td:nth-child(5),
-#perf_routes_reporting_table tbody td:nth-child(7) {
+#perf_routes_reporting_table tbody td:nth-child(5),
+#perf_urls_reporting_table   tbody td:nth-child(5) {
+	text-align: right;
+	width: 5%;
+	min-width: 30px;
+}
+
+#perf_views_reporting_table  tbody td:nth-child(7),
+#perf_routes_reporting_table tbody td:nth-child(8) {
 	width: 5%;
 	min-width: 50px;
 }
 		
 	</head>
 	<body>
-		<select onchange="javascript: goTo('${request.route_url(route_name='pyramid_perfstat.reporting.session_detail',id_session='')}'+$(this).val());">
+		<select onchange="javascript:goTo('${request.route_url(route_name='pyramid_perfstat.reporting.session_detail',id_session='')}'+$(this).val());">
 			% for id, dt_label, nb_rows in lst_ids_measures_date :
 				<option value="${id}" ${"selected='selected'" if id==id_session else ''}>SESSION-${id}: ${dt_label} nb of records : ${nb_rows}</option>
 			% endfor
 					<th>id</th>
 					<th>avg&#160;time&#160;(ms)</th>
 					<th>category</th>
+					<th><b>sql avg</b><br/>&#160;time&#160;(ms)</th>
+					<th>sql queries count</th>
 					<th>matched view name</th>
 					<th>view count</th>
 				</tr>
 			</thead>
 			<tbody>
-				% for (id, avg_time, view_name, cpt) in lst_agg_views_measures :
-					${publish_agg_view_row(id, avg_time, view_name, cpt)}
+				% for (id, request_avg_time, sql_avg_time, sql_nb_queries, view_name, cpt) in lst_agg_views_measures :
+					${publish_agg_view_row(id, request_avg_time, sql_avg_time, sql_nb_queries, view_name, cpt)}
 				% endfor
 			</tbody>
 		</table>
 					<th>id</th>
 					<th>avg&#160;time&#160;(ms)</th>
 					<th>category</th>
-					<th>requested url</th>
+					<th><b>sql avg</b><br/>&#160;time&#160;(ms)</th>
+					<th>sql queries count</th>
 					<th>matched route name</th>
 					<th>matched view name</th>
 					<th>url count</th>
 				</tr>
 			</thead>
 			<tbody>
-				% for (id, id_view, avg_time, url, route_name, view_name, cpt) in lst_agg_routes_measures :
-					${publish_agg_routes_row(id, id_view, avg_time, url, route_name, view_name, cpt)}
+				% for (id, id_view, request_avg_time, sql_avg_time, sql_nb_queries, route_name, view_name, cpt) in lst_agg_routes_measures :
+					${publish_agg_routes_row(id, id_view, request_avg_time, sql_avg_time, sql_nb_queries, route_name, view_name, cpt)}
 				% endfor
 			</tbody>
 		</table>
 					<th>id</th>
 					<th>avg&#160;time&#160;(ms)</th>
 					<th>category</th>
+					<th><b>sql avg</b><br/>&#160;time&#160;(ms)</th>
+					<th>sql queries count</th>
 					<th>requested url</th>
 				</tr>
 			</thead>
 			<tbody>
-				% for (id, avg_time, url) in lst_urls_measures :
-					${publish_url_row(id, avg_time, url)}
+				% for (id, request_avg_time, sql_avg_time, sql_nb_queries, url) in lst_urls_measures :
+					${publish_url_row(id, request_avg_time, sql_avg_time, sql_nb_queries, url)}
 				% endfor
 			</tbody>
 		</table>
 		function goTo(page)
 		{
 			window.location.assign(page);
-		}	
+		}
+
 		$(document).ready(function() { 
 			$("#perf_views_reporting_table").tablesorter({}); 
 			
 			% endif
 		});
 	</script>
-	
 </html>

File pyramid_perfstat/views.py

 #-----------------------------------------------------------------------------
 
 from pyramid.httpexceptions import HTTPFound
-
+from pyramid.view import view_config
 from pyramid_perfstat.perf_serializer import PerfDbManager
 from pyramid_perfstat import utils
+from pyramid.security import NO_PERMISSION_REQUIRED
 
+@view_config(route_name='pyramid_perfstat.reporting',
+             renderer='pyramid_perfstat:templates/reporting.mako',
+             permission=NO_PERMISSION_REQUIRED)
+@view_config(route_name='pyramid_perfstat.reporting.session_detail',
+             renderer='pyramid_perfstat:templates/reporting.mako',
+             permission=NO_PERMISSION_REQUIRED)
+@view_config(route_name='pyramid_perfstat.reporting.view_detail',
+             renderer='pyramid_perfstat:templates/reporting.mako',
+             permission=NO_PERMISSION_REQUIRED)
+@view_config(route_name='pyramid_perfstat.reporting.url_detail',
+             renderer='pyramid_perfstat:templates/reporting.mako',
+             permission=NO_PERMISSION_REQUIRED)
 def reporting(request):
     """
     display tables with all reports ...
                 'lst_urls_measures':lst_urls_measures
             }
 
+@view_config(route_name='pyramid_perfstat.reset',
+             permission=NO_PERMISSION_REQUIRED)
 def reset_db(request):
     """
     """
 
 setup(name='pyramid_perfstat',
       license='BSD License',
-      version='0.1.0',
+      version='0.2.0',
       description='Pyramid PerfStat logs and reports statistics about time usage of a pyramid webapp.',
       long_description=long_description,
       author='Bard Stéphane',
       classifiers=[
           'Development Status :: 4 - Beta',
           'Intended Audience :: Developers',
-          'License :: OSI Approved :: MIT License',
+          'License :: OSI Approved :: BSD License',
           'Natural Language :: English',
           'Operating System :: OS Independent',
           'Programming Language :: Python',