Performance updates to dynamic reports

Added bad_entries, modified_entries, extra_entries database fields to
    reduce the number of calls.
Created (bad|modified|extra)_entry_count methods to return the sbove fields
    and populate any missing values.
Consolidate interaction_per_client query.
Added unique constraint on client_id and timestamp in Interaction.  Greatly improved
    the performance of interaction_per_client.
Modified detailed-list.html to add modified field and reduce the database calls to two...
    unless the bad_entries, modified_entries, extra_entries fields need updating.
Modified client index to use only two database queries.
Adding automatic update
Updating load_stats to update counters in reports_interaction

git-svn-id: https://svn.mcs.anl.gov/repos/bcfg/trunk/bcfg2@5885 ce84e21b-d406-0410-9b95-82705330c041

7 files changed, 140 insertions, 60 deletions

diff --git a/src/lib/Server/Reports/importscript.py b/src/lib/Server/Reports/importscript.py
index 017bfd470..cc71837ea 100755
--- a/src/lib/Server/Reports/importscript.py
+++ b/src/lib/Server/Reports/importscript.py
@@ -114,11 +114,13 @@ def load_stats(cdata, sdata, vlevel, logger, quick=False, location=''):
                         timestamp, current_interaction.id))
 
 
+            counter_fields = { TYPE_CHOICES[0]: 0, TYPE_CHOICES[1]: 0, TYPE_CHOICES[2]: 0 }
             pattern = [('Bad/*', TYPE_CHOICES[0]),
                        ('Extra/*', TYPE_CHOICES[2]),
                        ('Modified/*', TYPE_CHOICES[1]),]
             for (xpath, type) in pattern:
                 for x in statistics.findall(xpath):
+                    counter_fields[type] = counter_fields[type] + 1
                     kargs = build_reason_kwargs(x)
                     if not quick:
                         rls = Reason.objects.filter(**kargs)
@@ -158,6 +160,12 @@ def load_stats(cdata, sdata, vlevel, logger, quick=False, location=''):
                     if vlevel > 0:
                         logger.info("%s interaction created with reason id %s and entry %s" % (xpath, rr.id, entry.id))
 
+            # Update interaction counters
+            current_interaction.bad_entries = counter_fields[TYPE_CHOICES[0]]
+            current_interaction.modified_entries = counter_fields[TYPE_CHOICES[1]]
+            current_interaction.extra_entries = counter_fields[TYPE_CHOICES[2]]
+            current_interaction.save()
+
             for times in statistics.findall('OpStamps'):
                 for metric, value in times.items():
                     if not quick:
diff --git a/src/lib/Server/Reports/reports/fixtures/initial_version.xml b/src/lib/Server/Reports/reports/fixtures/initial_version.xml
index 4fc05b7af..5c9ca2de5 100644
--- a/src/lib/Server/Reports/reports/fixtures/initial_version.xml
+++ b/src/lib/Server/Reports/reports/fixtures/initial_version.xml
@@ -24,4 +24,8 @@
 	  <field type='IntegerField' name='version'>11</field>
 	  <field type='DateTimeField' name='updated'>2009-01-13 12:26:10</field>
 	</object>
+	<object pk="6" model="reports.internaldatabaseversion">
+	  <field type='IntegerField' name='version'>16</field>
+	  <field type='DateTimeField' name='updated'>2010-06-01 12:26:10</field>
+	</object>
 </django-objects>
diff --git a/src/lib/Server/Reports/reports/models_new.py b/src/lib/Server/Reports/reports/models_new.py
index 40c7137a7..40236e79b 100644
--- a/src/lib/Server/Reports/reports/models_new.py
+++ b/src/lib/Server/Reports/reports/models_new.py
@@ -85,25 +85,23 @@ class InteractiveManager(models.Manager):
     '''returns most recent interaction as of specified timestamp in format:
     '2006-01-01 00:00:00' or 'now' or None->'now'  '''
     def interaction_per_client(self, maxdate = None):
+        '''Returns the most recent interactions for clients as of a date'''
+        '''FIXME - check the dates passed in'''
         from django.db import connection
         cursor = connection.cursor()
 
-        #in order to prevent traceback when zero records are returned.
-        #this could mask some database errors
+        sql = 'select reports_interaction.id, x.client_id from (select client_id, MAX(timestamp) ' + \
+                    'as timer from reports_interaction'
+        if maxdate != 'now':
+            sql = sql + " where timestamp < '%s' " % maxdate
+        sql = sql + ' GROUP BY client_id) x, reports_interaction where ' + \
+                    'reports_interaction.client_id = x.client_id AND reports_interaction.timestamp = x.timer'
         try:
-            if (maxdate == 'now' or maxdate == None):
-                cursor.execute("select reports_interaction.id, x.client_id from (select client_id, MAX(timestamp) "+
-                    "as timer from reports_interaction GROUP BY client_id) x, reports_interaction where "+
-                    "reports_interaction.client_id = x.client_id AND reports_interaction.timestamp = x.timer")
-            else:
-                cursor.execute("select reports_interaction.id, x.client_id from (select client_id, timestamp, MAX(timestamp) "+
-                    "as timer from reports_interaction WHERE timestamp < %s GROUP BY client_id) x, reports_interaction where "+
-                    "reports_interaction.client_id = x.client_id AND reports_interaction.timestamp = x.timer",
-                               [maxdate])
-            in_idents = [item[0] for item in cursor.fetchall()]
+            cursor.execute(sql)
         except:
-            in_idents = []
-        return self.filter(id__in = in_idents)
+            '''FIXME - really need some error hadling'''
+            return self.none()
+        return self.filter(id__in = [item[0] for item in cursor.fetchall()])
 
 
 class Interaction(models.Model):
@@ -116,6 +114,9 @@ class Interaction(models.Model):
     goodcount = models.IntegerField()#of good config-items
     totalcount = models.IntegerField()#of total config-items
     server = models.CharField(max_length=256)    # Name of the server used for the interaction
+    bad_entries = models.IntegerField(default=-1)
+    modified_entries = models.IntegerField(default=-1)
+    extra_entries = models.IntegerField(default=-1)
 
     def __str__(self):
         return "With " + self.client.name + " @ " + self.timestamp.isoformat()
@@ -133,8 +134,7 @@ class Interaction(models.Model):
             return 0
     
     def isclean(self):
-        if (self.bad().count() == 0 and self.goodcount == self.totalcount):
-        #if (self.state == "good"):
+        if (self.bad_entry_count() == 0 and self.goodcount == self.totalcount):
             return True
         else:
             return False
@@ -165,11 +165,32 @@ class Interaction(models.Model):
     def bad(self):
         return Entries_interactions.objects.select_related().filter(interaction=self, type=TYPE_BAD)
 
+    def bad_entry_count(self):
+        '''Number of bad entries.  Store the count in the interation field to save db queries'''
+        if self.bad_entries < 0:
+            self.bad_entries = Entries_interactions.objects.filter(interaction=self, type=TYPE_BAD).count()
+            self.save()
+        return self.bad_entries
+
     def modified(self):
         return Entries_interactions.objects.select_related().filter(interaction=self, type=TYPE_MODIFIED)
 
+    def modified_entry_count(self):
+        '''Number of modified entries.  Store the count in the interation field to save db queries'''
+        if self.modified_entries < 0:
+            self.modified_entries = Entries_interactions.objects.filter(interaction=self, type=TYPE_MODIFIED).count()
+            self.save()
+        return self.modified_entries
+
     def extra(self):
         return Entries_interactions.objects.select_related().filter(interaction=self, type=TYPE_EXTRA)
+
+    def extra_entry_count(self):
+        '''Number of extra entries.  Store the count in the interation field to save db queries'''
+        if self.extra_entries < 0:
+            self.extra_entries = Entries_interactions.objects.filter(interaction=self, type=TYPE_EXTRA).count()
+            self.save()
+        return self.extra_entries
     
     objects = InteractiveManager()
 
@@ -179,6 +200,7 @@ class Interaction(models.Model):
         pass
     class Meta:
         get_latest_by = 'timestamp'
+        unique_together = ("client", "timestamp")
 
 class Reason(models.Model):
     '''reason why modified or bad entry did not verify, or changed'''
diff --git a/src/lib/Server/Reports/reports/templates/clients/detailed-list.html b/src/lib/Server/Reports/reports/templates/clients/detailed-list.html
index 37bfb006b..5a1352cff 100644
--- a/src/lib/Server/Reports/reports/templates/clients/detailed-list.html
+++ b/src/lib/Server/Reports/reports/templates/clients/detailed-list.html
@@ -56,13 +56,14 @@
     <td class='right_column' style='width:75px'>State</td>
     <td class='right_column_narrow'>Good</td>
     <td class='right_column_narrow'>Bad</td>
+    <td class='right_column_narrow'>Modified</td>
     <td class='right_column_narrow'>Extra</td>
     <td class='right_column'>Last Run</td>
     <td class='right_column_wide'>Server</td>
   </tr>
-  {% for entry in entry_list %}
+  {% for client,entry,stale in entry_list %}
   <tr class='{% cycle listview,listview_alt %}'>
-    <td class='left_column'><a href='{% url Bcfg2.Server.Reports.reports.views.client_detail hostname=entry.client.name, pk=entry.id %}'>{{ entry.client.name }}</a></td>
+    <td class='left_column'><a href='{% url Bcfg2.Server.Reports.reports.views.client_detail hostname=client, pk=entry.id %}'>{{ client }}</a></td>
     <td class='right_column' style='width:75px'><a href=
       {% if server %}
         '{% url Bcfg2.Server.Reports.reports.views.client_detailed_list server=server,state=entry.state %}{{ qsa }}'
@@ -71,9 +72,10 @@
       {% endif %}
     {% ifequal entry.state 'dirty' %}style='background:#FF6A6A'{% endifequal %}>{{ entry.state }}</a></td>
     <td class='right_column_narrow'>{{ entry.goodcount }}</td>
-    <td class='right_column_narrow'>{{ entry.badcount }}</td>
-    <td class='right_column_narrow'>{{ entry.extra|length }}</td>
-    <td class='right_column'><span {% if entry.isstale %}style='background:#FF6A6A'{% endif %}>{{ entry.timestamp|date:"Y-m-d H:i" }}</span></td>
+    <td class='right_column_narrow'>{{ entry.bad_entry_count }}</td>
+    <td class='right_column_narrow'>{{ entry.modified_entry_count }}</td>
+    <td class='right_column_narrow'>{{ entry.extra_entry_count }}</td>
+    <td class='right_column'><span {% if stale %}style='background:#FF6A6A'{% endif %}>{{ entry.timestamp|date:"Y-m-d H:i" }}</span></td>
     <td class='right_column_wide'>
        {% if entry.server %}
          <a href=
diff --git a/src/lib/Server/Reports/reports/templates/clients/index.html b/src/lib/Server/Reports/reports/templates/clients/index.html
index 708d52ad1..cfb8a6c83 100644
--- a/src/lib/Server/Reports/reports/templates/clients/index.html
+++ b/src/lib/Server/Reports/reports/templates/clients/index.html
@@ -31,24 +31,24 @@
 </span></form>
 <br/><br/><br/></div>
 
-{% if client_list_b %}
+{% if inter_list %}
 <table><tr><td valign="top">
     <ul style="list-style-type:none;">
-    {% for client in client_list_b %}
-        <li><div class="{{client.current_interaction.state}}-lineitem">
-		<a href="{% url Bcfg2.Server.Reports.reports.views.client_detail client.name %}">{{ client.name }}</a>
+    {% for client,inter in inter_list %}
+        <li><div class="{{inter.state}}-lineitem">
+		<a href="{% spaceless %}{% ifequal timestamp 'now' %}
+			{% url Bcfg2.Server.Reports.reports.views.client_detail client %}
+			{% else %}
+			{% url Bcfg2.Server.Reports.reports.views.client_detail client,inter.id %}
+			{% endifequal %}
+			{% endspaceless %}">{{ client }}</a>
 		</div></li>
-    {% endfor %}
+  {% ifequal half_list forloop.counter0 %}
     </ul>
 </td><td valign="top">
     <ul style="list-style-type:none;">
-    {% if client_list_a %}
-      {% for client in client_list_a %}
-	    <li><div class="{{client.current_interaction.state}}-lineitem">
-		<a href="{% url Bcfg2.Server.Reports.reports.views.client_detail client.name %}">{{ client.name }}</a>
-		</div></li>
-      {% endfor %}
-    {% endif %}
+  {% endifequal %}
+    {% endfor %}
     </ul>
 </tr></table>
 {% else %}
diff --git a/src/lib/Server/Reports/reports/views.py b/src/lib/Server/Reports/reports/views.py
index 9e7dee77a..eea847bf4 100644
--- a/src/lib/Server/Reports/reports/views.py
+++ b/src/lib/Server/Reports/reports/views.py
@@ -90,14 +90,22 @@ def modified_item_index(request, timestamp = 'now'):
 
 def client_index(request, timestamp = 'now'):
     timestamp = timestamp.replace("@"," ")
-    client_list = Client.objects.active(timestamp).order_by('name')
-    client_list_a = client_list[len(client_list)/2:]
-    client_list_b = client_list[:len(client_list)/2]
+
+    c_dict = dict()
+    [c_dict.__setitem__(cl.id,cl.name) for cl in Client.objects.active(timestamp).order_by('name')]
+
+    list = []
+    for inter in Interaction.objects.interaction_per_client(timestamp):
+        if inter.client_id in c_dict:
+            list.append([c_dict[inter.client_id], inter])
+    list.sort(lambda a,b: cmp(a[0], b[0]))
+    half_list = len(list) / 2
+
     if timestamp == 'now':
         timestamp = datetime.now().isoformat('@')
     return render_to_response('clients/index.html',
-           {'client_list_a': client_list_a,
-            'client_list_b': client_list_b,
+            {'inter_list': list,
+             'half_list': half_list,
             'timestamp' : timestamp,
             'timestamp_date' : timestamp[:10],
             'timestamp_time' : timestamp[11:19]})
@@ -109,7 +117,7 @@ def client_detailed_list(request, **kwargs):
     '''
     context = dict(path=request.path)
     timestamp = 'now'
-    entry_max = None
+    entry_max = datetime.now()
     if request.GET:
         context['qsa']='?%s' % request.GET.urlencode()
         if request.GET.has_key('date1') and request.GET.has_key('time'):
@@ -121,31 +129,31 @@ def client_detailed_list(request, **kwargs):
     context['timestamp_date'] = timestamp[:10]
     context['timestamp_time'] = timestamp[11:19]
 
-    if 'server' in kwargs and kwargs['server']:
-        context['server'] = kwargs['server']
+    interactions = Interaction.objects.interaction_per_client(timestamp)
     if 'state' in kwargs and kwargs['state']:
         context['state'] = kwargs['state']
+        interactions=interactions.filter(state__exact=kwargs['state'])
+    if 'server' in kwargs and kwargs['server']:
+        interactions=interactions.filter(server__exact=kwargs['server'])
+        context['server'] = kwargs['server']
 
     # build the entry list from available clients
+    c_dict = dict()
+    [c_dict.__setitem__(cl.id,cl.name) for cl in client_list]
+
     entry_list = []
-    if entry_max:
-        for client in client_list:
-            try:
-                e = Interaction.objects.filter(client=client).filter(timestamp__lt=entry_max).order_by('-timestamp')[0]
-                if 'server' in context and e.server != context['server']:
-                    continue
-                if 'state' in context and e.state != context['state']:
-                    continue
-                entry_list.append(e)
-            except IndexError:
-                # Should never see this.. but skip clients with no data
-                pass
-    else:
-        if 'server' in context:
-            client_list = client_list.filter(current_interaction__server__exact=kwargs['server'])
-        if 'state' in context:
-            client_list = client_list.filter(current_interaction__state__exact=kwargs['state'])
-        [ entry_list.append(x.current_interaction) for x in client_list ]
+    for inter in interactions:
+        if inter.client_id in c_dict:
+            entry_list.append([c_dict[inter.client_id], inter, \
+		entry_max - inter.timestamp > timedelta(hours=24)])
+    entry_list.sort(lambda a,b: cmp(a[0], b[0]))
+    '''
+            if(datetime.now()-self.timestamp > timedelta(hours=25) ):
+                return True
+            else:
+                return False
+    '''
+
     context['entry_list'] = entry_list
     return render_to_response('clients/detailed-list.html', context)
 
diff --git a/src/lib/Server/Reports/updatefix.py b/src/lib/Server/Reports/updatefix.py
index d7740d358..89642f9f9 100644
--- a/src/lib/Server/Reports/updatefix.py
+++ b/src/lib/Server/Reports/updatefix.py
@@ -2,7 +2,8 @@ import Bcfg2.Server.Reports.settings
 
 from django.db import connection
 import django.core.management
-from Bcfg2.Server.Reports.reports.models import InternalDatabaseVersion
+from Bcfg2.Server.Reports.reports.models import InternalDatabaseVersion, \
+		TYPE_BAD, TYPE_MODIFIED, TYPE_EXTRA
 
 import logging, traceback
 logger = logging.getLogger('Bcfg2.Server.Reports.UpdateFix')
@@ -47,6 +48,35 @@ def _merge_database_table_entries():
         insert_cursor.execute("insert into reports_entries_interactions \
             (entry_id, interaction_id, reason_id, type) values (%s, %s, %s, %s)", (entry_id, row[3], row[2], row[4]))
 
+def _interactions_constraint_or_idx():
+    '''sqlite doesn't support alter tables.. or constraints'''
+    cursor = connection.cursor()
+    try:
+        cursor.execute('alter table reports_interaction add constraint reports_interaction_20100601 unique (client_id,timestamp)')
+    except:
+        cursor.execute('create unique index reports_interaction_20100601 on reports_interaction (client_id,timestamp)')
+            
+
+def _populate_interaction_entry_counts():
+    '''Populate up the type totals for the interaction table'''
+    cursor = connection.cursor()
+    count_field = { TYPE_BAD: 'bad_entries',
+                    TYPE_MODIFIED: 'modified_entries',
+                    TYPE_EXTRA: 'extra_entries' }
+
+    for type in count_field.keys():
+        cursor.execute("select count(type), interaction_id "+
+		"from reports_entries_interactions where type = %s group by interaction_id" % type)
+        updates = []
+        for row in cursor.fetchall():
+            updates.append(row)
+        try:
+            cursor.executemany("update reports_interaction set " + count_field[type] + "=%s where id = %s", updates)
+        except Exception, e:
+            print e
+    cursor.close()
+        
+
 # be sure to test your upgrade query before reflecting the change in the models
 # the list of function and sql command to do should go here
 _fixes = [_merge_database_table_entries,
@@ -62,6 +92,12 @@ _fixes = [_merge_database_table_entries,
           "alter table reports_interaction add server varchar(256) not null default 'N/A';",
           # fix revision data type to support $VCS hashes
           "alter table reports_interaction add repo_rev_code varchar(64) default '';",
+          # Performance enhancements for large sites
+          'alter table reports_interaction add column bad_entries integer not null default -1;',
+          'alter table reports_interaction add column modified_entries integer not null default -1;',
+          'alter table reports_interaction add column extra_entries integer not null default -1;',
+          _populate_interaction_entry_counts,
+          _interactions_constraint_or_idx,
 ]
 
 # this will calculate the last possible version of the database