3 # Tool for upgrading a db based on db version #
10 config_file = "/etc/planetlab/plc_config"
11 execfile(config_file, config)
12 upgrade_config_file = "plcdb.3-4.conf"
13 schema_file = "planetlab4.sql"
18 print "Usage: %s [OPTION] UPGRADE_CONFIG_FILE " % sys.argv[0]
20 print " -s, --schema=FILE Upgraded Database Schema"
21 print " -t, --temp-dir=DIR Temp Directory"
22 print " --help This message"
26 (opts, argv) = getopt.getopt(sys.argv[1:],
31 except getopt.GetoptError, err:
32 print "Error: ", err.msg
35 for (opt, optval) in opts:
36 if opt == "-s" or opt == "--schema":
38 elif opt == "-d" or opt == "--temp-dir":
43 upgrade_config_file = argv[0]
45 print "Error: too few arguments"
48 database = config['PLC_DB_NAME']
49 archived_database = database + "_archived"
52 schema_items_ordered = []
56 # load conf file for this upgrade
59 execfile(upgrade_config_file, upgrade_config)
60 upgrade_config.pop('__builtins__')
61 db_version_previous = upgrade_config['DB_VERSION_PREVIOUS']
62 db_version_new = upgrade_config['DB_VERSION_NEW']
64 except IOError, fault:
65 print "ERROR: upgrade config file (%s) not found. Exiting" % \
68 except KeyError, fault:
69 print "ERROR: %s not set in upgrade confing (%s). Exiting" % \
70 (fault, upgrade_config_file)
77 db = pgdb.connect(user = config['PLC_DB_USER'],
78 database = config['PLC_DB_NAME'])
83 print "STATUS: archiving old database"
84 archive_db = "psql template1 postgres -qc " \
85 " 'ALTER DATABASE %s RENAME TO %s;';" \
86 " createdb -U postgres %s > /dev/null; " % \
87 (database, archived_database, database)
88 exit_status = os.system(archive_db)
90 print "ERROR: unable to archive database. Upgrade failed"
92 print "STATUS: %s has been archived. now named %s" % (database, archived_database)
95 def encode_utf8(inputfile_name, outputfile_name):
96 # rewrite a iso-8859-1 encoded file and in utf8
98 inputfile = open(inputfile_name, 'r')
99 outputfile = open(outputfile_name, 'w')
100 for line in inputfile:
101 outputfile.write(unicode(line, 'iso-8859-1').encode('utf8'))
105 print 'error encoding file'
108 def create_item_from_schema(item_name):
111 (type, body_list) = schema[item_name]
112 exit_status = os.system('psql %s %s -qc "%s" > /dev/null 2>&1' % \
113 (config['PLC_DB_NAME'], config['PLC_DB_USER'],"".join(body_list) ) )
116 except Exception, fault:
117 print 'ERROR: create %s failed. Check schema.' % item_name
122 print "ERROR: cannot create %s. definition not found in %s" % \
126 def fix_row(row, table_name, table_fields):
128 if table_name in ['nodenetworks']:
129 # convert str bwlimit to bps int
130 bwlimit_index = table_fields.index('bwlimit')
131 if isinstance(row[bwlimit_index], int):
133 elif row[bwlimit_index].find('mbit') > -1:
134 row[bwlimit_index] = int(row[bwlimit_index].split('mbit')[0]) \
136 elif row[bwlimit_index].find('kbit') > -1:
137 row[bwlimit_index] = int(row[bwlimit_index].split('kbit')[0]) \
139 elif table_name in ['slice_attribute']:
140 # modify some invalid foreign keys
141 attribute_type_index = table_fields.index('attribute_type_id')
142 if row[attribute_type_index] == 10004:
143 row[attribute_type_index] = 10016
144 elif row[attribute_type_index] == 10006:
145 row[attribute_type_index] = 10017
146 elif table_name in ['slice_attribute_types']:
147 type_id_index = table_fields.index('attribute_type_id')
148 if row[type_id_index] in [10004, 10006]:
152 def fix_table(table, table_name, table_fields):
153 if table_name in ['slice_attribute_types']:
154 # remove duplicate/redundant primary keys
155 type_id_index = table_fields.index('attribute_type_id')
157 if row[type_id_index] in [10004, 10006]:
161 def remove_temp_tables():
164 for temp_table in temp_tables:
165 os.remove(temp_tables[temp_table])
169 def generate_temp_table(table_name, db):
172 # get upgrade directions
173 table_def = upgrade_config[table_name].replace('(', '').replace(')', '').split(',')
174 table_fields, old_fields, required_joins = [], [], set()
175 for field in table_def:
176 field_parts = field.strip().split(':')
177 table_fields.append(field_parts[0])
178 old_fields.append(field_parts[1])
180 required_joins.update(set(field_parts[2:]))
182 # get indices of fields that cannot be null
183 (type, body_list) = schema[table_name]
184 not_null_indices = []
185 for field in table_fields:
186 for body_line in body_list:
187 if body_line.find(field) > -1 and \
188 body_line.upper().find("NOT NULL") > -1:
189 not_null_indices.append(table_fields.index(field))
191 # get index of primary key
192 primary_key_indices = []
193 for body_line in body_list:
194 if body_line.find("PRIMARY KEY") > -1:
195 primary_key = body_line
196 for field in table_fields:
197 if primary_key.find(field) > -1:
198 primary_key_indices.append(table_fields.index(field))
202 get_old_data = "SELECT DISTINCT %s FROM %s" % \
203 (", ".join(old_fields), old_fields[0].split(".")[0])
204 for join in required_joins:
205 get_old_data = get_old_data + " INNER JOIN %s USING (%s) " % \
206 (join.split('.')[0], join.split('.')[1])
208 cursor.execute(get_old_data)
209 rows = cursor.fetchall()
211 # write data to a temp file
212 temp_file_name = '%s/%s.tmp' % (temp_dir, table_name)
213 temp_file = open(temp_file_name, 'w')
215 # attempt to make any necessary fixes to data
216 row = fix_row(row, table_name, table_fields)
217 # do not attempt to write null rows
220 # do not attempt to write rows with null primary keys
221 if filter(lambda x: row[x] == None, primary_key_indices):
223 for i in range(len(row)):
224 # convert nulls into something pg can understand
226 if i in not_null_indices:
227 # XX doesnt work if column is int type
231 if isinstance(row[i], int) or isinstance(row[i], float):
233 # escape whatever can mess up the data format
234 if isinstance(row[i], str):
235 row[i] = row[i].replace('\t', '\\t')
236 row[i] = row[i].replace('\n', '\\n')
237 row[i] = row[i].replace('\r', '\\r')
238 data_row = "\t".join(row)
239 temp_file.write(data_row + "\n")
240 temp_file.write("\.\n")
242 temp_tables[table_name] = temp_file_name
245 #print "WARNING: cannot upgrade %s. upgrade def not found. skipping" % \
248 except IndexError, fault:
249 print "ERROR: error found in upgrade config file. " \
250 "check %s configuration. Aborting " % \
254 print "ERROR: configuration for %s doesnt match db schema. " \
255 " Aborting" % (table_name)
263 # Connect to current db
267 # determin current db version
269 cursor.execute("SELECT relname from pg_class where relname = 'plc_db_version'")
270 rows = cursor.fetchall()
272 print "WARNING: current db has no version. Unable to validate config file."
274 cursor.execute("SELECT version FROM plc_db_version")
275 rows = cursor.fetchall()
277 if rows[0][0] == db_version_new:
278 print "STATUS: Versions are the same. No upgrade necessary."
280 elif not rows[0][0] == db_version_previous:
281 print "STATUS: DB_VERSION_PREVIOUS in config file (%s) does not" \
282 " match current db version %d" % (upgrade_config_file, rows[0][0])
285 print "STATUS: attempting upgrade from %d to %d" % \
286 (db_version_previous, db_version_new)
289 sql = " SELECT pg_catalog.pg_encoding_to_char(d.encoding)" \
290 " FROM pg_catalog.pg_database d " \
291 " LEFT JOIN pg_catalog.pg_user u ON d.datdba = u.usesysid " \
292 " WHERE d.datname = '%s' " % config['PLC_DB_NAME']
294 rows = cursor.fetchall()
295 if rows[0][0] not in ['UTF8']:
296 print "WARNING: db encoding is not utf8. Must convert"
299 dump_file = '%s/dump.sql' % (temp_dir)
300 dump_file_encoded = dump_file + ".utf8"
301 dump_cmd = 'pg_dump -i %s -U %s -f %s > /dev/null 2>&1' % \
302 (config['PLC_DB_NAME'], config['PLC_DB_USER'], dump_file)
304 if os.system(dump_cmd):
305 print "ERROR: during db dump. Exiting."
307 # encode dump to utf8
308 print "STATUS: encoding database dump"
309 encode_utf8(dump_file, dump_file_encoded)
310 # archive original db
312 # create a utf8 database and upload encoded data
313 recreate_cmd = 'createdb -U %s -E UTF8 %s > /dev/null 2>&1; ' \
314 'psql -a -U %s %s < %s > /dev/null 2>&1;' % \
315 (config['PLC_DB_USER'], config['PLC_DB_NAME'], \
316 config['PLC_DB_USER'], config['PLC_DB_NAME'], dump_file_encoded)
317 print "STATUS: recreating database as utf8"
318 if os.system(recreate_cmd):
319 print "ERROR: database encoding failed. Aborting"
322 os.remove(dump_file_encoded)
328 # parse the schema user wishes to upgrade to
330 file = open(schema_file, 'r')
332 lines = file.readlines()
333 while index < len(lines):
335 # find all created objects
336 if line.startswith("CREATE"):
337 line_parts = line.split(" ")
338 item_type = line_parts[1]
339 item_name = line_parts[2]
340 schema_items_ordered.append(item_name)
341 if item_type in ['INDEX']:
342 schema[item_name] = (item_type, line)
344 # functions, tables, views span over multiple lines
345 # handle differently than indexes
346 elif item_type in ['AGGREGATE', 'TABLE', 'VIEW']:
348 while index < len(lines):
350 nextline =lines[index]
351 fields.append(nextline)
352 if nextline.find(";") >= 0:
354 schema[item_name] = (item_type, fields)
356 print "ERROR: unknown type %s" % item_type
357 elif line.startswith("INSERT"):
364 print "STATUS: generating temp tables"
365 # generate all temp tables
366 for key in schema_items_ordered:
367 (type, body_list) = schema[key]
369 generate_temp_table(key, db)
371 # disconenct from current database and archive it
375 print "STATUS: archiving database"
379 print "STATUS: upgrading database"
380 # attempt to create and load all items from schema into temp db
382 for key in schema_items_ordered:
383 (type, body_list) = schema[key]
384 create_item_from_schema(key)
386 if upgrade_config.has_key(key):
387 table_def = upgrade_config[key].replace('(', '').replace(')', '').split(',')
388 table_fields = [field.strip().split(':')[0] for field in table_def]
389 insert_cmd = "psql %s %s -c " \
390 " 'COPY %s (%s) FROM stdin;' < %s " % \
391 (database, config['PLC_DB_USER'], key, ", ".join(table_fields), temp_tables[key] )
392 exit_status = os.system(insert_cmd)
394 print "ERROR: upgrade %s failed" % key
397 # check if there are any insert stmts in schema for this table
398 print "WARNING: %s has no temp data file. Unable to populate with old data" % key
399 for insert_stmt in inserts:
400 if insert_stmt.find(key) > -1:
401 insert_cmd = 'psql %s postgres -qc "%s;" > /dev/null 2>&1' % \
402 (database, insert_stmt)
403 os.system(insert_cmd)
405 print "ERROR: failed to populate db. Unarchiving original database and aborting"
406 undo_command = "dropdb -U postgres %s; psql template1 postgres -qc" \
407 " 'ALTER DATABASE %s RENAME TO %s;'; > /dev/null" % \
408 (database, archived_database, database)
409 os.system(undo_command)
414 print "upgrade complete"