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

1 files changed, 83 insertions, 28 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