3 # Tool that removes zombie records from database tables#
8 from pprint import pprint
11 config_file = "/etc/planetlab/plc_config"
13 execfile(config_file, config)
16 print "Usage: %s SCHEMA_FILE " % sys.argv[0]
20 schema_file = sys.argv[1]
22 print "Error: too few arguments"
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'}
29 foreign_keys_ordered = []
31 # parse the schema for foreign keys
33 file = open(schema_file, 'r')
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):
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:
65 db = pgdb.connect(user = config['PLC_DB_USER'],
66 database = config['PLC_DB_NAME'])
69 for foreign_key in foreign_keys_ordered:
70 primary_key = foreign_keys[foreign_key]
71 sql = "SELECT distinct %s from %s"
73 # get all foreign keys in this table
74 foreign_key_parts = foreign_key.split(".")
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]
83 cursor.execute(sql % (foreign_key_parts[1], foreign_key_parts[0]))
84 foreign_rows = cursor.fetchall()
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])
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"
104 primary_key_rows = cursor.fetchall()
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
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)
119 #zombie_keys[foreign_key] = zombie_keys_list
121 except pgdb.DatabaseError: