import script ready to go. Fast SQL fixed up so it returns the correct data too.

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

2 files changed, 102 insertions, 39 deletions

diff --git a/reports/brpt/importscript.py b/reports/brpt/importscript.py
index 56c1def1d..4de369f12 100755
--- a/reports/brpt/importscript.py
+++ b/reports/brpt/importscript.py
@@ -56,8 +56,8 @@ if __name__ == '__main__':
     from django.db import connection, backend
     cursor = connection.cursor()
 
-    cursor.execute("SELECT name, id from reports_client;")
     clients = {}
+    cursor.execute("SELECT name, id from reports_client;")
     [clients.__setitem__(a,b) for a,b in cursor.fetchall()]
     
     for node in statsdata.findall('Node'):
@@ -71,31 +71,35 @@ if __name__ == '__main__':
 
 
     cursor.execute("SELECT client_id, timestamp, id from reports_interaction")
-    interactions = cursor.fetchall()
-    #interactions_slice = [x[0:2] for x in interactions]
     interactions_hash = {}
-    [interactions_hash.__setitem__(str(x[0])+"-"+x[1].isoformat(),x[2]) for x in interactions]
+    [interactions_hash.__setitem__(str(x[0])+"-"+x[1].isoformat(),x[2]) for x in cursor.fetchall()]#possibly change str to tuple
     pingability = {}
     [pingability.__setitem__(n.get('name'),n.get('pingable',default='N')) for n in clientsdata.findall('Client')]
 
     cursor.execute("SELECT id, owner, current_owner, %s, current_group, perms, current_perms, status, current_status, %s, current_to, version, current_version, current_exists, current_diff from reports_reason"%(backend.quote_name("group"),backend.quote_name("to")))
-    reasons = cursor.fetchall()
+    reasons_hash = {}
+    [reasons_hash.__setitem__(tuple(n[1:]),n[0]) for n in cursor.fetchall()]
 
     cursor.execute("SELECT id, name, kind, reason_id from reports_bad")
-    bad_things = cursor.fetchall()
+    bad_hash = {}
+    [bad_hash.__setitem__((n[1],n[2]),(n[0],n[3])) for n in cursor.fetchall()]
+
     cursor.execute("SELECT id, name, kind, reason_id from reports_extra")
-    extra_things = cursor.fetchall()
+    extra_hash = {}
+    [extra_hash.__setitem__((n[1],n[2]),(n[0],n[3])) for n in cursor.fetchall()]
+
     cursor.execute("SELECT id, name, kind, reason_id from reports_modified")
-    modified_things = cursor.fetchall()
-    cursor.execute("SELECT id, metric, value from reports_performance")
-    performance_things = cursor.fetchall()
+    modified_hash = {}
+    [modified_hash.__setitem__((n[1],n[2]),(n[0],n[3])) for n in cursor.fetchall()]
 
-    reasons_hash = {}
-    [reasons_hash.__setitem__(tuple(n[1:]),n[0]) for n in reasons]
+    cursor.execute("SELECT id, metric, value from reports_performance")
+    performance_hash = {}
+    [performance_hash.__setitem__((n[1],n[2]),n[0]) for n in cursor.fetchall()]
+    
     for r in statsdata.findall('.//Bad/*')+statsdata.findall('.//Extra/*')+statsdata.findall('.//Modified/*'):
         arguments = [r.get('owner', default=""), r.get('current_owner', default=""),
                      r.get('group', default=""), r.get('current_group', default=""),
-                     eval(r.get('perms', default="''")), eval(r.get('current_perms', default="''")),
+                     r.get('perms', default=""), r.get('current_perms', default=""),
                      r.get('status', default=""), r.get('current_status', default=""),
                      r.get('to', default=""), r.get('current_to', default=""),
                      r.get('version', default=""), r.get('current_version', default=""),
@@ -107,11 +111,12 @@ if __name__ == '__main__':
             cursor.execute("INSERT INTO reports_reason VALUES (NULL, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);",
                            arguments)
             current_reason_id = cursor.lastrowid
-            reasons.append([current_reason_id]+arguments)
             reasons_hash[tuple(arguments)] = current_reason_id
                                
 #            print("Reason inserted with id %s"%current_reason_id)
-    print "----------------REASONS SYNCED------------------"
+
+    print "----------------REASONS SYNCED---------------------"
+    
     for node in statsdata.findall('Node'):
         name = node.get('name')
         try:
@@ -127,21 +132,66 @@ if __name__ == '__main__':
 #                      datetime(t[0],t[1],t[2],t[3],t[4],t[5]),current_interaction_id))
             else:
                 cursor.execute("INSERT INTO reports_interaction VALUES (NULL, %s, %s, %s, %s, %s, %s, %s, %s);",
-                               [clients[name],
-                                timestamp,
-                                statistics.get('state', default="unknown"),
-                                statistics.get('revision',default="unknown"),
-                                statistics.get('client_version',default="unknown"),
-                                pingability[name],
-                                statistics.get('good',default="0"),
-                                statistics.get('total',default="0")])
+                               [clients[name], timestamp,
+                                statistics.get('state', default="unknown"), statistics.get('revision',default="unknown"),
+                                statistics.get('client_version',default="unknown"), pingability[name],
+                                statistics.get('good',default="0"), statistics.get('total',default="0")])
                 current_interaction_id = cursor.lastrowid
-                interactions.append([clients[name], timestamp, current_interaction_id])
                 interactions_hash[str(clients[name])+"-"+timestamp.isoformat()] = current_interaction_id
-#                print("Interaction for %s at %s with id %s INSERTED in to db"%(clients[name],
-#                      timestamp, current_interaction_id))
+                #print("Interaction for %s at %s with id %s INSERTED in to db"%(clients[name],
+                #    timestamp, current_interaction_id))
+            for (xpath, hashname, tablename) in [('Bad/*', bad_hash, 'reports_bad'),
+                                                 ('Extra/*', extra_hash, 'reports_extra'),
+                                                 ('Modified/*', modified_hash, 'reports_modified')]:
+                for x in statistics.findall(xpath):
+                    if not hashname.has_key((x.get('name'), x.tag)):
+                        arguments = [x.get('owner', default=""), x.get('current_owner', default=""),
+                                     x.get('group', default=""), x.get('current_group', default=""),
+                                     x.get('perms', default=""), x.get('current_perms', default=""),
+                                     x.get('status', default=""), x.get('current_status', default=""),
+                                     x.get('to', default=""), x.get('current_to', default=""),
+                                     x.get('version', default=""), x.get('current_version', default=""),
+                                     eval(x.get('current_exists', default="True").capitalize()), x.get('current_diff', default="")]
+                        cursor.execute("INSERT INTO "+tablename+" VALUES (NULL, %s, %s, %s);",
+                                       [x.get('name'), x.tag, reasons_hash[tuple(arguments)]])
+                        item_id = cursor.lastrowid
+                        hashname[(x.get('name'), x.tag)] = (item_id, current_interaction_id)
+                    #print "Bad item INSERTED having reason id %s and ID %s"%(hashname[(x.get('name'),x.tag)][1],
+                    #                                                         hashname[(x.get('name'),x.tag)][0])                
+                    else:
+                        item_id = hashname[(x.get('name'), x.tag)][0]
+                        #print "Bad item exists, has reason id %s and ID %s"%(hashname[(x.get('name'),x.tag)][1],
+                        #                                                              hashname[(x.get('name'),x.tag)][0])
+                    try:
+                        cursor.execute("INSERT INTO "+tablename+"_interactions VALUES (NULL, %s, %s);",
+                                       [item_id, current_interaction_id])
+                    except:
+                        pass
+
+            for times in statistics.findall('OpStamps'):
+                for tags in times.items():
+                    if not performance_hash.has_key((tags[0],eval(tags[1]))):
+                        cursor.execute("INSERT INTO reports_performance VALUES (NULL, %s, %s)",[tags[0],tags[1]])
+                        performance_hash[(tags[0],tags[1])] = cursor.lastrowid
+                    else:
+                        item_id = performance_hash[(tags[0],eval(tags[1]))]
+                        #already exists
+                    try:
+                        cursor.execute("INSERT INTO reports_performance_interaction VALUES (NULL, %s, %s);",
+                                       [item_id, current_interaction_id])
+                    except:
+                        pass
+
+                
+    print("----------------INTERACTIONS SYNCED----------------")
+    connection._commit()
+    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")
+    for row in cursor.fetchall():
+        cursor.execute("UPDATE reports_client SET current_interaction_id = %s where reports_client.id = %s",
+                       [row[0],row[1]])
+        print "setting current_interaction_id for client ID %s to %s"%(row[1],row[0])
 
-            #insert bad children, extra children, modified children, performance items for this iteration of Client/Interaction
+    print("------------LATEST INTERACTION SET----------------")
 
     #use that crazy query to update all the latest client_interaction records.
     
@@ -149,7 +199,12 @@ if __name__ == '__main__':
     connection._commit()
     #Clients are consistent
     for q in connection.queries:
-        print q
+        if not (q['sql'].startswith('INSERT INTO reports_bad_interactions')|
+                q['sql'].startswith('INSERT INTO reports_extra_interactions')|
+                q['sql'].startswith('INSERT INTO reports_performance_interaction')|
+                q['sql'].startswith('INSERT INTO reports_modified_interactions')|
+                q['sql'].startswith('UPDATE reports_client SET current_interaction_id')):
+            print q
 
     raise SystemExit, 0
 
diff --git a/reports/brpt/reports/models.py b/reports/brpt/reports/models.py
index c9c656ab0..1cc0a7a1e 100644
--- a/reports/brpt/reports/models.py
+++ b/reports/brpt/reports/models.py
@@ -71,10 +71,16 @@ class Interaction(models.Model):
         return "With " + self.client.name + " @ " + self.timestamp.isoformat()
 
     def percentgood(self):
-        return (self.goodcount/self.totalcount)*100
+        if not self.totalcount == 0:
+            return (self.goodcount/self.totalcount)*100
+        else:
+            return 0
 
     def percentbad(self):
-        return (self.totalcount-self.goodcount)/(self.totalcount)
+        if not self.totalcount == 0:
+            return (self.totalcount-self.goodcount)/(self.totalcount)
+        else:
+            return 0
     
     def isclean(self):
         if (self.bad_items.count() == 0 and self.extra_items.count() == 0 and self.goodcount == self.totalcount):
@@ -116,8 +122,8 @@ class Reason(models.Model):
     current_owner = models.TextField(maxlength=128, blank=True)
     group = models.TextField(maxlength=128, blank=True)
     current_group = models.TextField(maxlength=128, blank=True)
-    perms =  models.IntegerField(blank=True)
-    current_perms = models.IntegerField(blank=True)
+    perms =  models.TextField(maxlength=4, blank=True)#because permissions might start with zero, and the db might think its octal and break
+    current_perms = models.TextField(maxlength=4,blank=True)
     status = models.TextField(maxlength=3, blank=True)#on/off/(None)
     current_status = models.TextField(maxlength=1, blank=True)#on/off/(None)
     to = models.TextField(maxlength=256, blank=True)
@@ -165,13 +171,15 @@ class PerformanceManager(models.Manager):
             "reports_client.current_interaction_id = reports_performance_interaction.interaction_id AND "+
             "reports_performance.id = reports_performance_interaction.performance_id)")
         else:
-            cursor.execute("SELECT reports_client.name, reports_performance.metric, reports_performance.value, "+
-            "MAX(reports_interaction.timestamp) FROM reports_performance, reports_performance_interaction, "+
-            "reports_interaction, reports_client WHERE reports_interaction.id = "+
-            "reports_performance_interaction.interaction_id AND reports_client.id = "+
-            "reports_interaction.client_id AND reports_performance.id = "+
-            "reports_performance_interaction.performance_id AND reports_interaction.timestamp < %s GROUP BY "+
-            "reports_performance.id", [maxdate])
+            cursor.execute("select reports_client.name, reports_performance.metric, "+
+                           "reports_performance.value from (Select reports_interaction.client_id as client_id, "+
+                           "MAX(reports_interaction.timestamp) as timestamp from reports_interaction where "+
+                           "timestamp < %s GROUP BY reports_interaction.client_id) x, reports_client, "+
+                           "reports_interaction, reports_performance, reports_performance_interaction where "+
+                           "reports_client.id = x.client_id AND x.timestamp = reports_interaction.timestamp AND "+
+                           "x.client_id = reports_interaction.client_id AND reports_performance.id = "+
+                           "reports_performance_interaction.performance_id AND "+
+                           "reports_performance_interaction.interaction_id = reports_interaction.id", [maxdate])
 
         results = {}
         for row in cursor.fetchall():