tools/dzombie.py

   1 #!/usr/bin/python
   2 #
   3 # Tool that removes zombie records from database tables#
   4 import sys
   5 import os
   6 import getopt
   7 import pgdb
   8 from pprint import pprint
   9
  10 schema_file = None
  11 config_file = "/etc/planetlab/plc_config"
  12 config = {}
  13 execfile(config_file, config)
  14
  15 def usage():
  16         print "Usage: %s SCHEMA_FILE " % sys.argv[0]
  17         sys.exit(1)
  18
  19 try:
  20         schema_file  = sys.argv[1]
  21 except IndexError:
  22         print "Error: too few arguments"
  23         usage()
  24
  25 # all foreing keys exist as primary kyes in another table
  26 # will represent all foreign keys as
  27 # { 'table.foreign_key': 'table.primary_key'}
  28 foreign_keys = {}
  29 foreign_keys_ordered = []
  30 zombie_keys = {}
  31 # parse the schema for foreign keys
  32 try:
  33         file = open(schema_file, 'r')
  34         index = 0
  35         lines = file.readlines()
  36         while index < len(lines):
  37                 line = lines[index].strip()
  38                 # find all created objects
  39                 if line.startswith("CREATE"):
  40                         line_parts = line.split(" ")
  41                         if line_parts[1:3] == ['OR', 'REPLACE']:
  42                                 line_parts = line_parts[2:]
  43                         item_type = line_parts[1].strip()
  44                         item_name = line_parts[2].strip()
  45                         if item_type.upper() in ['TABLE']:
  46                                 while index < len(lines):
  47                                         index = index + 1
  48                                         nextline =lines[index].strip()
  49                                         if nextline.find("--") > -1:
  50                                                 nextline = nextline[0:nextline.index("--")].replace(',', '')
  51                                         if nextline.upper().find("REFERENCES") > -1:
  52                                                 nextline_parts = nextline.split(" ")
  53                                                 foreign_key_name = nextline_parts[0].strip()
  54                                                 foreign_key_table = nextline_parts[nextline_parts.index("REFERENCES")+1].strip()
  55                                                 foreign_key = item_name + "."+ foreign_key_name
  56                                                 primary_key = foreign_key_table +"."+ foreign_key_name
  57                                                 foreign_keys[foreign_key] = primary_key
  58                                                 foreign_keys_ordered.append(foreign_key)
  59                                         elif nextline.find(";") >= 0:
  60                                                 break
  61                 index = index + 1
  62 except:
  63         raise
  64
  65 db = pgdb.connect(user = config['PLC_DB_USER'],
  66                   database = config['PLC_DB_NAME'])
  67 cursor = db.cursor()
  68 try:
  69         for foreign_key in foreign_keys_ordered:
  70                 primary_key = foreign_keys[foreign_key]
  71                 sql = "SELECT distinct %s from %s"
  72
  73                 # get all foreign keys in this table
  74                 foreign_key_parts = foreign_key.split(".")
  75
  76                 # do not delete from primary tables
  77                 if foreign_key_parts[0] in ['addresses', 'boot_states', 'conf_files', \
  78                         'keys', 'messages', 'nodegroups', 'interfaces', 'nodes', 'pcus', 'peers' \
  79                         'persons', 'roles', 'sessions', 'sites', 'slices']:
  80                         #print "skipping table %s" % foreign_key_parts[0]
  81                         continue
  82
  83                 cursor.execute(sql % (foreign_key_parts[1], foreign_key_parts[0]))
  84                 foreign_rows = cursor.fetchall()
  85
  86                 # get all the primary keys from this foreign key's primary table
  87                 primary_key_parts = primary_key.split(".")
  88                 # foreign key name may not match primary key name. must rename these
  89                 if primary_key_parts[1] == 'creator_person_id':
  90                         primary_key_parts[1] = 'person_id'
  91                 elif primary_key_parts[1] == 'min_role_id':
  92                         primary_key_parts[1]  = 'role_id'
  93                 sql = sql % (primary_key_parts[1], primary_key_parts[0])
  94
  95                 # determin which primary records are deleted
  96                 desc = os.popen('psql planetlab4 postgres -c "\d %s;"' % primary_key_parts[0])
  97                 result = desc.readlines()
  98                 if primary_key_parts[0] in ['slices']:
  99                         sql  = sql + " where name not like '%_deleted'"
 100                 elif filter(lambda line: line.find("deleted") > -1, result):
 101                         sql = sql + " where deleted = false"
 102
 103                 cursor.execute(sql)
 104                 primary_key_rows = cursor.fetchall()
 105
 106                 # if foreign key isnt present in primay_key query, it either doesnt exist or marked as deleted
 107                 # also, ignore null foreign keys, not considered zombied
 108                 zombie_keys_func = lambda key: key not in primary_key_rows and not key == [None]
 109                 zombie_keys_list = [zombie_key[0] for zombie_key in filter(zombie_keys_func, foreign_rows)]
 110                 print zombie_keys_list
 111                 # delete these zombie records
 112                 if zombie_keys_list:
 113                         print " -> Deleting %d zombie record(s) from %s after checking %s" % \
 114                                         (len(zombie_keys_list), foreign_key_parts[0], primary_key_parts[0])
 115                         sql_delete = 'DELETE FROM %s WHERE %s IN %s' % \
 116                         (foreign_key_parts[0], foreign_key_parts[1], tuple(zombie_keys_list))
 117                         cursor.execute(sql_delete)
 118                         db.commit()
 119                 #zombie_keys[foreign_key] = zombie_keys_list
 120         print "done"
 121 except pgdb.DatabaseError:
 122         raise