X-Git-Url: http://git.onelab.eu/?a=blobdiff_plain;f=tools%2Fupgrade-db.py;h=18adaad0b1536b85afc29b6831a5c58141074df5;hb=d51a2803a4fb08e542e2debb4b49983066e5aaf4;hp=ad89fbbe3909dbac842d54d6aa840658532a9b77;hpb=3b1f8ac0685804da11d54d1c08666dd145ca8ad9;p=plcapi.git diff --git a/tools/upgrade-db.py b/tools/upgrade-db.py index ad89fbb..18adaad 100755 --- a/tools/upgrade-db.py +++ b/tools/upgrade-db.py @@ -1,6 +1,15 @@ -#!/usr/bin/python +#!/usr/bin/env python3 # -# Tool for upgrading a db based on db version # +# Tool for upgrading/converting a db +# Requirements: +# 1) Databse Schema - schema for the new database you what to upgrade to +# 2) Config File - the config file that describes how to convert the db +# +# Notes: +# 1) Will attempt to convert the db defined in /etc/planetlab/plc_config +# 2) Does not automatically drop archived database. They must be removed +# manually + import sys import os import getopt @@ -8,18 +17,18 @@ import pgdb config = {} config_file = "/etc/planetlab/plc_config" -execfile(config_file, config) +exec(compile(open(config_file).read(), config_file, 'exec'), config) upgrade_config_file = "plcdb.3-4.conf" schema_file = "planetlab4.sql" temp_dir = "/tmp" def usage(): - print "Usage: %s [OPTION] UPGRADE_CONFIG_FILE " % sys.argv[0] - print "Options:" - print " -s, --schema=FILE Upgraded Database Schema" - print " -t, --temp-dir=DIR Temp Directory" - print " --help This message" + print("Usage: %s [OPTION] UPGRADE_CONFIG_FILE " % sys.argv[0]) + print("Options:") + print(" -s, --schema=FILE Upgraded Database Schema") + print(" -t, --temp-dir=DIR Temp Directory") + print(" --help This message") sys.exit(1) try: @@ -28,8 +37,8 @@ try: ["schema=", "temp-dir=", "help"]) -except getopt.GetoptError, err: - print "Error: ", err.msg +except getopt.GetoptError as err: + print("Error: ", err.msg) usage() for (opt, optval) in opts: @@ -42,7 +51,7 @@ for (opt, optval) in opts: try: upgrade_config_file = argv[0] except IndexError: - print "Error: too few arguments" + print("Error: too few arguments") usage() schema = {} @@ -55,18 +64,18 @@ temp_tables = {} # load conf file for this upgrade try: upgrade_config = {} - execfile(upgrade_config_file, upgrade_config) + exec(compile(open(upgrade_config_file).read(), upgrade_config_file, 'exec'), upgrade_config) upgrade_config.pop('__builtins__') db_version_previous = upgrade_config['DB_VERSION_PREVIOUS'] db_version_new = upgrade_config['DB_VERSION_NEW'] -except IOError, fault: - print "Error: upgrade config file (%s) not found. Exiting" % \ - (fault) +except IOError as fault: + print("Error: upgrade config file (%s) not found. Exiting" % \ + (fault)) sys.exit(1) -except KeyError, fault: - print "Error: %s not set in upgrade confing (%s). Exiting" % \ - (fault, upgrade_config_file) +except KeyError as fault: + print("Error: %s not set in upgrade confing (%s). Exiting" % \ + (fault, upgrade_config_file)) sys.exit(1) @@ -81,12 +90,11 @@ def archive_db(database, archived_database): archive_db = " dropdb -U postgres %s > /dev/null 2>&1;" \ " psql template1 postgres -qc " \ - " 'ALTER DATABASE %s RENAME TO %s;';" \ - " createdb -U postgres %s > /dev/null; " % \ - (archived_database, database, archived_database, database) - exit_status = os.system(archive_db) + " 'ALTER DATABASE %s RENAME TO %s;';" % \ + (archived_database, database, archived_database) + exit_status = os.system(archive_db) if exit_status: - print "Error: unable to archive database. Upgrade failed" + print("Error: unable to archive database. Upgrade failed") sys.exit(1) #print "Status: %s has been archived. now named %s" % (database, archived_database) @@ -97,11 +105,13 @@ def encode_utf8(inputfile_name, outputfile_name): inputfile = open(inputfile_name, 'r') outputfile = open(outputfile_name, 'w') for line in inputfile: - outputfile.write(unicode(line, 'iso-8859-1').encode('utf8')) + if line.upper().find('SET CLIENT_ENCODING') > -1: + continue + outputfile.write(str(line, 'iso-8859-1').encode('utf8')) inputfile.close() outputfile.close() except: - print 'error encoding file' + print('error encoding file') raise def create_item_from_schema(item_name): @@ -112,19 +122,19 @@ def create_item_from_schema(item_name): (config['PLC_DB_NAME'], config['PLC_DB_USER'],"".join(body_list) ) ) if exit_status: raise Exception - except Exception, fault: - print 'Error: create %s failed. Check schema.' % item_name + except Exception as fault: + print('Error: create %s failed. Check schema.' % item_name) sys.exit(1) raise fault except KeyError: - print "Error: cannot create %s. definition not found in %s" % \ - (key, schema_file) + print("Error: cannot create %s. definition not found in %s" % \ + (key, schema_file)) return False def fix_row(row, table_name, table_fields): - if table_name in ['nodenetworks']: + if table_name in ['interfaces']: # convert str bwlimit to bps int bwlimit_index = table_fields.index('bwlimit') if isinstance(row[bwlimit_index], int): @@ -142,9 +152,13 @@ def fix_row(row, table_name, table_fields): row[attribute_type_index] = 10016 elif row[attribute_type_index] == 10006: row[attribute_type_index] = 10017 + elif row[attribute_type_index] in [10031, 10033]: + row[attribute_type_index] = 10037 + elif row[attribute_type_index] in [10034, 10035]: + row[attribute_type_index] = 10036 elif table_name in ['slice_attribute_types']: type_id_index = table_fields.index('attribute_type_id') - if row[type_id_index] in [10004, 10006]: + if row[type_id_index] in [10004, 10006, 10031, 10033, 10034, 10035]: return None return row @@ -153,7 +167,7 @@ def fix_table(table, table_name, table_fields): # remove duplicate/redundant primary keys type_id_index = table_fields.index('attribute_type_id') for row in table: - if row[type_id_index] in [10004, 10006]: + if row[type_id_index] in [10004, 10006, 10031, 10033, 10034, 10035]: table.remove(row) return table @@ -170,13 +184,14 @@ def generate_temp_table(table_name, db): try: # get upgrade directions table_def = upgrade_config[table_name].replace('(', '').replace(')', '').split(',') - table_fields, old_fields, required_joins = [], [], set() + table_fields, old_fields, joins, wheres = [], [], set(), set() for field in table_def: field_parts = field.strip().split(':') table_fields.append(field_parts[0]) old_fields.append(field_parts[1]) - if field_parts[2:]: - required_joins.update(set(field_parts[2:])) + if field_parts[2:]: + joins.update(set([x for x in field_parts[2:] if not x.find('=') > -1])) + wheres.update(set([x for x in field_parts[2:] if x.find('=') > -1])) # get indices of fields that cannot be null (type, body_list) = schema[table_name] @@ -186,24 +201,26 @@ def generate_temp_table(table_name, db): if body_line.find(field) > -1 and \ body_line.upper().find("NOT NULL") > -1: not_null_indices.append(table_fields.index(field)) - # get index of primary key primary_key_indices = [] for body_line in body_list: if body_line.find("PRIMARY KEY") > -1: primary_key = body_line for field in table_fields: - if primary_key.find(field) > -1: + if primary_key.find(" "+field+" ") > -1: primary_key_indices.append(table_fields.index(field)) - break - + #break + # get old data get_old_data = "SELECT DISTINCT %s FROM %s" % \ (", ".join(old_fields), old_fields[0].split(".")[0]) - for join in required_joins: + for join in joins: get_old_data = get_old_data + " INNER JOIN %s USING (%s) " % \ (join.split('.')[0], join.split('.')[1]) - + if wheres: + get_old_data = get_old_data + " WHERE " + for where in wheres: + get_old_data = get_old_data + " %s" % where cursor.execute(get_old_data) rows = cursor.fetchall() @@ -217,7 +234,7 @@ def generate_temp_table(table_name, db): if row == None: continue # do not attempt to write rows with null primary keys - if filter(lambda x: row[x] == None, primary_key_indices): + if [x for x in primary_key_indices if row[x] == None]: continue for i in range(len(row)): # convert nulls into something pg can understand @@ -244,14 +261,14 @@ def generate_temp_table(table_name, db): #print "WARNING: cannot upgrade %s. upgrade def not found. skipping" % \ # (table_name) return False - except IndexError, fault: - print "Error: error found in upgrade config file. " \ + except IndexError as fault: + print("Error: error found in upgrade config file. " \ "check %s configuration. Aborting " % \ - (table_name) + (table_name)) sys.exit(1) except: - print "Error: configuration for %s doesnt match db schema. " \ - " Aborting" % (table_name) + print("Error: configuration for %s doesnt match db schema. " \ + " Aborting" % (table_name)) try: db.rollback() except: @@ -268,32 +285,31 @@ try: cursor.execute("SELECT relname from pg_class where relname = 'plc_db_version'") rows = cursor.fetchall() if not rows: - print "Warning: current db has no version. Unable to validate config file." + print("Warning: current db has no version. Unable to validate config file.") else: cursor.execute("SELECT version FROM plc_db_version") rows = cursor.fetchall() if not rows or not rows[0]: - print "Warning: current db has no version. Unable to validate config file." + print("Warning: current db has no version. Unable to validate config file.") elif rows[0][0] == db_version_new: - print "Status: Versions are the same. No upgrade necessary." + print("Status: Versions are the same. No upgrade necessary.") sys.exit() elif not rows[0][0] == db_version_previous: - print "Stauts: DB_VERSION_PREVIOUS in config file (%s) does not" \ - " match current db version %d" % (upgrade_config_file, rows[0][0]) + print("Stauts: DB_VERSION_PREVIOUS in config file (%s) does not" \ + " match current db version %d" % (upgrade_config_file, rows[0][0])) sys.exit() else: - print "STATUS: attempting upgrade from %d to %d" % \ - (db_version_previous, db_version_new) + print("STATUS: attempting upgrade from %d to %d" % \ + (db_version_previous, db_version_new)) # check db encoding sql = " SELECT pg_catalog.pg_encoding_to_char(d.encoding)" \ " FROM pg_catalog.pg_database d " \ - " LEFT JOIN pg_catalog.pg_user u ON d.datdba = u.usesysid " \ " WHERE d.datname = '%s' " % config['PLC_DB_NAME'] cursor.execute(sql) rows = cursor.fetchall() - if rows[0][0] not in ['UTF8']: - print "WARNING: db encoding is not utf8. Attempting to encode" + if rows[0][0] not in ['UTF8', 'UNICODE']: + print("WARNING: db encoding is not utf8. Attempting to encode") db.close() # generate db dump dump_file = '%s/dump.sql' % (temp_dir) @@ -301,21 +317,21 @@ try: dump_cmd = 'pg_dump -i %s -U postgres -f %s > /dev/null 2>&1' % \ (config['PLC_DB_NAME'], dump_file) if os.system(dump_cmd): - print "ERROR: during db dump. Exiting." + print("ERROR: during db dump. Exiting.") sys.exit(1) # encode dump to utf8 - print "Status: encoding database dump" + print("Status: encoding database dump") encode_utf8(dump_file, dump_file_encoded) # archive original db archive_db(config['PLC_DB_NAME'], config['PLC_DB_NAME']+'_sqlascii_archived') # create a utf8 database and upload encoded data - recreate_cmd = 'createdb -U postgres -E UTF8 %s > /dev/null 2>&1; ' \ + recreate_cmd = 'createdb -U postgres -E UTF8 %s > /dev/null; ' \ 'psql -a -U %s %s < %s > /dev/null 2>&1;' % \ (config['PLC_DB_NAME'], config['PLC_DB_USER'], \ config['PLC_DB_NAME'], dump_file_encoded) - print "Status: recreating database as utf8" + print("Status: recreating database as utf8") if os.system(recreate_cmd): - print "Error: database encoding failed. Aborting" + print("Error: database encoding failed. Aborting") sys.exit(1) os.remove(dump_file_encoded) @@ -334,9 +350,14 @@ try: lines = file.readlines() while index < len(lines): line = lines[index] + if line.find("--") > -1: + line_parts = line.split("--") + line = line_parts[0] # find all created objects if line.startswith("CREATE"): line_parts = line.split(" ") + if line_parts[1:3] == ['OR', 'REPLACE']: + line_parts = line_parts[2:] item_type = line_parts[1] item_name = line_parts[2] schema_items_ordered.append(item_name) @@ -350,6 +371,9 @@ try: while index < len(lines): index = index + 1 nextline =lines[index] + if nextline.find("--") > -1: + new_line_parts = nextline.split("--") + nextline = new_line_parts[0] # look for any sequences if item_type in ['TABLE'] and nextline.find('serial') > -1: sequences[item_name] = nextline.strip().split()[0] @@ -358,7 +382,7 @@ try: break schema[item_name] = (item_type, fields) else: - print "Error: unknown type %s" % item_type + print("Error: unknown type %s" % item_type) elif line.startswith("INSERT"): inserts.append(line) index = index + 1 @@ -366,7 +390,7 @@ try: except: raise -print "Status: generating temp tables" +print("Status: generating temp tables") # generate all temp tables for key in schema_items_ordered: (type, body_list) = schema[key] @@ -377,18 +401,18 @@ for key in schema_items_ordered: cursor.close() db.close() -print "Status: archiving database" +print("Status: archiving database") archive_db(config['PLC_DB_NAME'], config['PLC_DB_NAME']+'_archived') +os.system('createdb -U postgres -E UTF8 %s > /dev/null; ' % config['PLC_DB_NAME']) - -print "Status: upgrading database" +print("Status: upgrading database") # attempt to create and load all items from schema into temp db try: for key in schema_items_ordered: (type, body_list) = schema[key] create_item_from_schema(key) if type == 'TABLE': - if upgrade_config.has_key(key): + if key in upgrade_config: # attempt to populate with temp table data table_def = upgrade_config[key].replace('(', '').replace(')', '').split(',') table_fields = [field.strip().split(':')[0] for field in table_def] @@ -398,10 +422,10 @@ try: ", ".join(table_fields), temp_tables[key] ) exit_status = os.system(insert_cmd) if exit_status: - print "Error: upgrade %s failed" % key + print("Error: upgrade %s failed" % key) sys.exit(1) # update the primary key sequence - if sequences.has_key(key): + if key in sequences: sequence = key +"_"+ sequences[key] +"_seq" update_seq = "psql %s %s -c " \ " \"select setval('%s', max(%s)) FROM %s;\" > /dev/null" % \ @@ -409,25 +433,25 @@ try: sequences[key], key) exit_status = os.system(update_seq) if exit_status: - print "Error: sequence %s update failed" % sequence + print("Error: sequence %s update failed" % sequence) sys.exit(1) else: # check if there are any insert stmts in schema for this table - print "Warning: %s has no temp data file. Unable to populate with old data" % key + print("Warning: %s has no temp data file. Unable to populate with old data" % key) for insert_stmt in inserts: if insert_stmt.find(key) > -1: insert_cmd = 'psql %s postgres -qc "%s;" > /dev/null 2>&1' % \ (config['PLC_DB_NAME'], insert_stmt) os.system(insert_cmd) except: - print "Error: failed to populate db. Unarchiving original database and aborting" + print("Error: failed to populate db. Unarchiving original database and aborting") undo_command = "dropdb -U postgres %s > /dev/null; psql template1 postgres -qc" \ " 'ALTER DATABASE %s RENAME TO %s;'; > /dev/null" % \ (config['PLC_DB_NAME'], config['PLC_DB_NAME']+'_archived', config['PLC_DB_NAME']) os.system(undo_command) - remove_temp_tables() + #remove_temp_tables() raise -remove_temp_tables() +#remove_temp_tables() -print "upgrade complete" +print("upgrade complete")