3 # Tool for upgrading/converting a db
5 # 1) Databse Schema - schema for the new database you what to upgrade to
6 # 2) Config File - the config file that describes how to convert the db
9 # 1) Will attempt to convert the db defined in /etc/planetlab/plc_config
10 # 2) Does not automatically drop archived database. They must be removed
19 config_file = "/etc/planetlab/plc_config"
20 execfile(config_file, config)
21 upgrade_config_file = "plcdb.3-4.conf"
22 schema_file = "planetlab4.sql"
27 print "Usage: %s [OPTION] UPGRADE_CONFIG_FILE " % sys.argv[0]
29 print " -s, --schema=FILE Upgraded Database Schema"
30 print " -t, --temp-dir=DIR Temp Directory"
31 print " --help This message"
35 (opts, argv) = getopt.getopt(sys.argv[1:],
40 except getopt.GetoptError, err:
41 print "Error: ", err.msg
44 for (opt, optval) in opts:
45 if opt == "-s" or opt == "--schema":
47 elif opt == "-d" or opt == "--temp-dir":
52 upgrade_config_file = argv[0]
54 print "Error: too few arguments"
59 schema_items_ordered = []
64 # load conf file for this upgrade
67 execfile(upgrade_config_file, upgrade_config)
68 upgrade_config.pop('__builtins__')
69 db_version_previous = upgrade_config['DB_VERSION_PREVIOUS']
70 db_version_new = upgrade_config['DB_VERSION_NEW']
72 except IOError, fault:
73 print "Error: upgrade config file (%s) not found. Exiting" % \
76 except KeyError, fault:
77 print "Error: %s not set in upgrade confing (%s). Exiting" % \
78 (fault, upgrade_config_file)
85 db = pgdb.connect(user = config['PLC_DB_USER'],
86 database = config['PLC_DB_NAME'])
89 def archive_db(database, archived_database):
91 archive_db = " dropdb -U postgres %s > /dev/null 2>&1;" \
92 " psql template1 postgres -qc " \
93 " 'ALTER DATABASE %s RENAME TO %s;';" % \
94 (archived_database, database, archived_database)
95 exit_status = os.system(archive_db)
97 print "Error: unable to archive database. Upgrade failed"
99 #print "Status: %s has been archived. now named %s" % (database, archived_database)
102 def encode_utf8(inputfile_name, outputfile_name):
103 # rewrite a iso-8859-1 encoded file in utf8
105 inputfile = open(inputfile_name, 'r')
106 outputfile = open(outputfile_name, 'w')
107 for line in inputfile:
108 if line.upper().find('SET CLIENT_ENCODING') > -1:
110 outputfile.write(unicode(line, 'iso-8859-1').encode('utf8'))
114 print 'error encoding file'
117 def create_item_from_schema(item_name):
120 (type, body_list) = schema[item_name]
121 exit_status = os.system('psql %s %s -qc "%s" > /dev/null 2>&1' % \
122 (config['PLC_DB_NAME'], config['PLC_DB_USER'],"".join(body_list) ) )
125 except Exception, fault:
126 print 'Error: create %s failed. Check schema.' % item_name
131 print "Error: cannot create %s. definition not found in %s" % \
135 def fix_row(row, table_name, table_fields):
137 if table_name in ['interfaces']:
138 # convert str bwlimit to bps int
139 bwlimit_index = table_fields.index('bwlimit')
140 if isinstance(row[bwlimit_index], int):
142 elif row[bwlimit_index].find('mbit') > -1:
143 row[bwlimit_index] = int(row[bwlimit_index].split('mbit')[0]) \
145 elif row[bwlimit_index].find('kbit') > -1:
146 row[bwlimit_index] = int(row[bwlimit_index].split('kbit')[0]) \
148 elif table_name in ['slice_attribute']:
149 # modify some invalid foreign keys
150 attribute_type_index = table_fields.index('attribute_type_id')
151 if row[attribute_type_index] == 10004:
152 row[attribute_type_index] = 10016
153 elif row[attribute_type_index] == 10006:
154 row[attribute_type_index] = 10017
155 elif row[attribute_type_index] in [10031, 10033]:
156 row[attribute_type_index] = 10037
157 elif row[attribute_type_index] in [10034, 10035]:
158 row[attribute_type_index] = 10036
159 elif table_name in ['slice_attribute_types']:
160 type_id_index = table_fields.index('attribute_type_id')
161 if row[type_id_index] in [10004, 10006, 10031, 10033, 10034, 10035]:
165 def fix_table(table, table_name, table_fields):
166 if table_name in ['slice_attribute_types']:
167 # remove duplicate/redundant primary keys
168 type_id_index = table_fields.index('attribute_type_id')
170 if row[type_id_index] in [10004, 10006, 10031, 10033, 10034, 10035]:
174 def remove_temp_tables():
177 for temp_table in temp_tables:
178 os.remove(temp_tables[temp_table])
182 def generate_temp_table(table_name, db):
185 # get upgrade directions
186 table_def = upgrade_config[table_name].replace('(', '').replace(')', '').split(',')
187 table_fields, old_fields, joins, wheres = [], [], set(), set()
188 for field in table_def:
189 field_parts = field.strip().split(':')
190 table_fields.append(field_parts[0])
191 old_fields.append(field_parts[1])
193 joins.update(set(filter(lambda x: not x.find('=') > -1, field_parts[2:])))
194 wheres.update(set(filter(lambda x: x.find('=') > -1, field_parts[2:])))
196 # get indices of fields that cannot be null
197 (type, body_list) = schema[table_name]
198 not_null_indices = []
199 for field in table_fields:
200 for body_line in body_list:
201 if body_line.find(field) > -1 and \
202 body_line.upper().find("NOT NULL") > -1:
203 not_null_indices.append(table_fields.index(field))
204 # get index of primary key
205 primary_key_indices = []
206 for body_line in body_list:
207 if body_line.find("PRIMARY KEY") > -1:
208 primary_key = body_line
209 for field in table_fields:
210 if primary_key.find(" "+field+" ") > -1:
211 primary_key_indices.append(table_fields.index(field))
215 get_old_data = "SELECT DISTINCT %s FROM %s" % \
216 (", ".join(old_fields), old_fields[0].split(".")[0])
218 get_old_data = get_old_data + " INNER JOIN %s USING (%s) " % \
219 (join.split('.')[0], join.split('.')[1])
221 get_old_data = get_old_data + " WHERE "
223 get_old_data = get_old_data + " %s" % where
224 cursor.execute(get_old_data)
225 rows = cursor.fetchall()
227 # write data to a temp file
228 temp_file_name = '%s/%s.tmp' % (temp_dir, table_name)
229 temp_file = open(temp_file_name, 'w')
231 # attempt to make any necessary fixes to data
232 row = fix_row(row, table_name, table_fields)
233 # do not attempt to write null rows
236 # do not attempt to write rows with null primary keys
237 if filter(lambda x: row[x] == None, primary_key_indices):
239 for i in range(len(row)):
240 # convert nulls into something pg can understand
242 if i in not_null_indices:
243 # XX doesnt work if column is int type
247 if isinstance(row[i], int) or isinstance(row[i], float):
249 # escape whatever can mess up the data format
250 if isinstance(row[i], str):
251 row[i] = row[i].replace('\t', '\\t')
252 row[i] = row[i].replace('\n', '\\n')
253 row[i] = row[i].replace('\r', '\\r')
254 data_row = "\t".join(row)
255 temp_file.write(data_row + "\n")
256 temp_file.write("\.\n")
258 temp_tables[table_name] = temp_file_name
261 #print "WARNING: cannot upgrade %s. upgrade def not found. skipping" % \
264 except IndexError, fault:
265 print "Error: error found in upgrade config file. " \
266 "check %s configuration. Aborting " % \
270 print "Error: configuration for %s doesnt match db schema. " \
271 " Aborting" % (table_name)
279 # Connect to current db
283 # determin current db version
285 cursor.execute("SELECT relname from pg_class where relname = 'plc_db_version'")
286 rows = cursor.fetchall()
288 print "Warning: current db has no version. Unable to validate config file."
290 cursor.execute("SELECT version FROM plc_db_version")
291 rows = cursor.fetchall()
292 if not rows or not rows[0]:
293 print "Warning: current db has no version. Unable to validate config file."
294 elif rows[0][0] == db_version_new:
295 print "Status: Versions are the same. No upgrade necessary."
297 elif not rows[0][0] == db_version_previous:
298 print "Stauts: DB_VERSION_PREVIOUS in config file (%s) does not" \
299 " match current db version %d" % (upgrade_config_file, rows[0][0])
302 print "STATUS: attempting upgrade from %d to %d" % \
303 (db_version_previous, db_version_new)
306 sql = " SELECT pg_catalog.pg_encoding_to_char(d.encoding)" \
307 " FROM pg_catalog.pg_database d " \
308 " WHERE d.datname = '%s' " % config['PLC_DB_NAME']
310 rows = cursor.fetchall()
311 if rows[0][0] not in ['UTF8', 'UNICODE']:
312 print "WARNING: db encoding is not utf8. Attempting to encode"
315 dump_file = '%s/dump.sql' % (temp_dir)
316 dump_file_encoded = dump_file + ".utf8"
317 dump_cmd = 'pg_dump -i %s -U postgres -f %s > /dev/null 2>&1' % \
318 (config['PLC_DB_NAME'], dump_file)
319 if os.system(dump_cmd):
320 print "ERROR: during db dump. Exiting."
322 # encode dump to utf8
323 print "Status: encoding database dump"
324 encode_utf8(dump_file, dump_file_encoded)
325 # archive original db
326 archive_db(config['PLC_DB_NAME'], config['PLC_DB_NAME']+'_sqlascii_archived')
327 # create a utf8 database and upload encoded data
328 recreate_cmd = 'createdb -U postgres -E UTF8 %s > /dev/null; ' \
329 'psql -a -U %s %s < %s > /dev/null 2>&1;' % \
330 (config['PLC_DB_NAME'], config['PLC_DB_USER'], \
331 config['PLC_DB_NAME'], dump_file_encoded)
332 print "Status: recreating database as utf8"
333 if os.system(recreate_cmd):
334 print "Error: database encoding failed. Aborting"
337 os.remove(dump_file_encoded)
346 # parse the schema user wishes to upgrade to
348 file = open(schema_file, 'r')
350 lines = file.readlines()
351 while index < len(lines):
353 if line.find("--") > -1:
354 line_parts = line.split("--")
356 # find all created objects
357 if line.startswith("CREATE"):
358 line_parts = line.split(" ")
359 if line_parts[1:3] == ['OR', 'REPLACE']:
360 line_parts = line_parts[2:]
361 item_type = line_parts[1]
362 item_name = line_parts[2]
363 schema_items_ordered.append(item_name)
364 if item_type in ['INDEX']:
365 schema[item_name] = (item_type, line)
367 # functions, tables, views span over multiple lines
368 # handle differently than indexes
369 elif item_type in ['AGGREGATE', 'TABLE', 'VIEW']:
371 while index < len(lines):
373 nextline =lines[index]
374 if nextline.find("--") > -1:
375 new_line_parts = nextline.split("--")
376 nextline = new_line_parts[0]
377 # look for any sequences
378 if item_type in ['TABLE'] and nextline.find('serial') > -1:
379 sequences[item_name] = nextline.strip().split()[0]
380 fields.append(nextline)
381 if nextline.find(";") >= 0:
383 schema[item_name] = (item_type, fields)
385 print "Error: unknown type %s" % item_type
386 elif line.startswith("INSERT"):
393 print "Status: generating temp tables"
394 # generate all temp tables
395 for key in schema_items_ordered:
396 (type, body_list) = schema[key]
398 generate_temp_table(key, db)
400 # disconenct from current database and archive it
404 print "Status: archiving database"
405 archive_db(config['PLC_DB_NAME'], config['PLC_DB_NAME']+'_archived')
406 os.system('createdb -U postgres -E UTF8 %s > /dev/null; ' % config['PLC_DB_NAME'])
408 print "Status: upgrading database"
409 # attempt to create and load all items from schema into temp db
411 for key in schema_items_ordered:
412 (type, body_list) = schema[key]
413 create_item_from_schema(key)
415 if upgrade_config.has_key(key):
416 # attempt to populate with temp table data
417 table_def = upgrade_config[key].replace('(', '').replace(')', '').split(',')
418 table_fields = [field.strip().split(':')[0] for field in table_def]
419 insert_cmd = "psql %s %s -c " \
420 " 'COPY %s (%s) FROM stdin;' < %s " % \
421 (config['PLC_DB_NAME'], config['PLC_DB_USER'], key,
422 ", ".join(table_fields), temp_tables[key] )
423 exit_status = os.system(insert_cmd)
425 print "Error: upgrade %s failed" % key
427 # update the primary key sequence
428 if sequences.has_key(key):
429 sequence = key +"_"+ sequences[key] +"_seq"
430 update_seq = "psql %s %s -c " \
431 " \"select setval('%s', max(%s)) FROM %s;\" > /dev/null" % \
432 (config['PLC_DB_NAME'], config['PLC_DB_USER'], sequence,
434 exit_status = os.system(update_seq)
436 print "Error: sequence %s update failed" % sequence
439 # check if there are any insert stmts in schema for this table
440 print "Warning: %s has no temp data file. Unable to populate with old data" % key
441 for insert_stmt in inserts:
442 if insert_stmt.find(key) > -1:
443 insert_cmd = 'psql %s postgres -qc "%s;" > /dev/null 2>&1' % \
444 (config['PLC_DB_NAME'], insert_stmt)
445 os.system(insert_cmd)
447 print "Error: failed to populate db. Unarchiving original database and aborting"
448 undo_command = "dropdb -U postgres %s > /dev/null; psql template1 postgres -qc" \
449 " 'ALTER DATABASE %s RENAME TO %s;'; > /dev/null" % \
450 (config['PLC_DB_NAME'], config['PLC_DB_NAME']+'_archived', config['PLC_DB_NAME'])
451 os.system(undo_command)
452 #remove_temp_tables()
455 #remove_temp_tables()
457 print "upgrade complete"