From a157a6438725231466022d102d37715b3bdaf04a Mon Sep 17 00:00:00 2001 From: Joey Hagedorn Date: Tue, 11 Jul 2006 20:21:22 +0000 Subject: 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 --- reports/brpt/importscript.py | 111 ++++++++++++++++++++++++++++++----------- reports/brpt/reports/models.py | 30 +++++++---- 2 files changed, 102 insertions(+), 39 deletions(-) (limited to 'reports') 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(): -- cgit v1.2.3-1-g7c22