funsim
diff --git a/‎mlab_maxmind_processed.py‎
Lines changed: 16 additions & 44 deletions b/‎mlab_maxmind_processed.py‎
Lines changed: 16 additions & 44 deletions
@@ -7,7 +7,8 @@
 #               AX  Axel Roest
 #
 # Version history
-# 20120629      AX  first version, doesn't work yet
+# 20120629		AX	first version
+# 20120701      AX  cleanup of unused methods, Ruben fixed the locID bug
 #
 # test: 
 # cd /DATA
@@ -18,6 +19,7 @@
 #       v move files naar archive directory
 #       v move error files naar error directory
 #       v log process and errors
+#		todo: loop through all maxmind tables and update full mlab set
 
 import sys
 import re
@@ -44,12 +46,7 @@
 
 # directories
 baseDir     = '/DATA/mlab/'
-scratchDir  = baseDir + 'scratch/'
-workDir     = baseDir + 'work/'
-archiveDir  = baseDir + 'archive/'
-errorDir    = baseDir + 'error/'
 logDir      = baseDir + 'logs/'
-cleanDir    = baseDir + 'clean/'
 
 #files
 errorLog    = "error.log"
@@ -83,35 +80,6 @@ def extract_date(string):
   date = datetime(int(date_match.group(1)),int(date_match.group(2)),int(date_match.group(3)))
   return date
 
-def exists_dbentry(cur, file_id, db_table, test_datetime, destination, source_ip):
-    ''' Test if the entry already exists in the database '''
-    # Check if the entry exists already 
-    sql = "SELECT COUNT(*) FROM " + db_table + " WHERE date = '" + test_datetime.isoformat() + "' AND destination = '" + destination +  "' AND  source = '" + source_ip + "' AND file_id = " + str(file_id) 
-    cur.execute(sql)
-
-    if cur.fetchone()[0] < 1:
-        return False
-    else:
-        return True
-
-def blunt_insert_dbentry(cur, file_id, db_table, test_datetime, destination, source_ip):
-    ''' Insert a connection to the database without testing '''
-    columns = ', '.join(['date', 'destination', 'source', 'file_id'])
-    values = '"' + '", "'.join([test_datetime.isoformat(), destination, source_ip, str(file_id)]) + '"'
-    sql = "INSERT INTO  " + db_table + " (" + columns + ") VALUES(" + values + ") "
-    cur.execute(sql)
-
-def insert_dbentry(cur, file_id, db_table, test_datetime, destination, source_ip):
-    ''' Insert a test connection to the database, if it not already exists '''
-    # Check if the entry exists already 
-    sql = "SELECT COUNT(*) FROM " + db_table + " WHERE date = '" + test_datetime.isoformat() + "' AND destination = '" + destination +  "' AND  source = '" + source_ip + "' AND file_id = " + str(file_id) 
-    cur.execute(sql)
-
-    # If not, then isert it
-    if cur.fetchone()[0] < 1:
-        print 'Found new test performed on the', test_datetime, 'from ' + destination + ' -> ' + source_ip + '.' 
-        blunt_insert_dbentry(cur, file_id, db_table, test_datetime, destination, source_ip)
-
 # return True if the table exists in the database
 def check_maxmind_exist(cur, table):
     sql = "select * FROM maxmind.`" + table + "` LIMIT 1"
@@ -121,22 +89,25 @@ def check_maxmind_exist(cur, table):
     else:
         return True
 
-def get_maxmind_dates(cur):
-    datehash = {}
+def get_maxmind_tableset(cur):
     sql = "SHOW TABLES FROM `maxmind`"
     cur.execute(sql)
-    rows = cur.fetchall()
-    rows2 = []
+    allrows = cur.fetchall()
+    rows = []
     # filter rows
-    for item in rows:
+    for item in allrows:
         m = re.search('Blocks_GeoLiteCity_(\d+)$', item[0])
         if (m):
-            rows2.append(m.group(0))
+            rows.append(m.group(0))
+    print rows
+    return rows
 
-    # print rows2
+def get_maxmind_dates(cur):
+    datehash = {}
+    rows = get_maxmind_tableset(cur)
 
     skipfirst = True
-    for table in rows2:
+    for table in rows:
         date = extract_datestring(table)
         if (skipfirst):
             skipfirst = False
@@ -156,7 +127,7 @@ def update_mlab_glasnost(cur,table):
     end_datum   = maxmind_dates[start_datum]
     print 'updating between' + start_datum + ' AND ' + end_datum
     try:
-        sql = 'UPDATE mlab.glasnost SET locID = M.`locId` FROM mlab.glasnost L , maxmind.' + table + ' M WHERE  L.`source` BETWEEN M.`startnumip` AND M.`endnumip` AND L.`date` BETWEEN "' + start_datum + '" AND "' + end_datum + '" AND L.`locId` = 0'
+        sql = 'UPDATE mlab.glasnost SET locId = M.`locId` FROM mlab.glasnost L , maxmind.' + table + ' M WHERE  L.`source` BETWEEN M.`startnumip` AND M.`endnumip` AND L.`date` BETWEEN "' + start_datum + '" AND "' + end_datum + '" AND L.`locId` = 0'
         print sql
         cur.execute(sql)
     except MySQLdb.Error, e:
@@ -199,6 +170,7 @@ def update_mlab_glasnost(cur,table):
     sys.stderr.write('Error, cannot connect to database' + db_name + '\n')
 
 # contains hash with key = start_date, value = enddate (= startdate of next table, except for the last one)
+# maxmind_all_tables = get_maxmind_tableset(cur)
 maxmind_dates = get_maxmind_dates(cur)
 print maxmind_dates