def object_diff(server1_val, server2_val, object1, object2, options, object_type=None): """diff the definition of two objects Find the difference among two object definitions. server1_val[in] a dictionary containing connection information for the first server including: (user, password, host, port, socket) server2_val[in] a dictionary containing connection information for the second server including: (user, password, host, port, socket) object1[in] the first object in the compare in the form: (db.name) object2[in] the second object in the compare in the form: (db.name) options[in] a dictionary containing the options for the operation: (quiet, verbosity, difftype) object_type[in] type of the objects to be compared (e.g., TABLE, PROCEDURE, etc.). By default None (not defined). Returns None = objects are the same, diff[] = tables differ """ server1, server2 = server_connect(server1_val, server2_val, object1, object2, options) # Get the object type if unknown considering that objects of different # types can be found with the same name. if not object_type: #Get object types of object1 regexp_obj = re.compile(REGEXP_QUALIFIED_OBJ_NAME) m_obj = regexp_obj.match(object1) db_name, obj_name = m_obj.groups() db = Database(server1, db_name, options) obj1_types = db.get_object_type(obj_name) if not obj1_types: raise UtilDBError("The object {0} does not exist.".format(object1)) # Get object types of object2 m_obj = regexp_obj.match(object2) db_name, obj_name = m_obj.groups() db = Database(server2, db_name, options) obj2_types = db.get_object_type(obj_name) if not obj2_types: raise UtilDBError("The object {0} does not exist.".format(object2)) # Merge types found for both objects obj_types = set(obj1_types + obj2_types) # Diff objects considering all types found result = [] for obj_type in obj_types: res = diff_objects(server1, server2, object1, object2, options, obj_type) if res: result.append(res) return result if len(result) > 0 else None else: # Diff objects of known type return diff_objects(server1, server2, object1, object2, options, object_type)
def _get_transform(server1, server2, object1, object2, options): """Get the transformation SQL statements This method generates the SQL statements to transform the destination object based on direction of the compare. server1[in] first server connection server2[in] second server connection object1 the first object in the compare in the form: (db.name) object2 the second object in the compare in the form: (db.name) options[in] a dictionary containing the options for the operation: (quiet, etc.) Returns tuple - (bool - same db name?, list of transformation statements) """ from mysql.utilities.common.database import Database from mysql.utilities.common.sql_transform import SQLTransformer obj_type = None direction = options.get("changes-for", "server1") # If there is no dot, we do not have the format 'db_name.obj_name' for # object1 and therefore must treat it as a database name. if object1.find(".") == -1: obj_type = "DATABASE" # We are working with databases so db and name need to be set # to the database name to tell the get_object_definition() method # to retrieve the database information. db1 = object1 db2 = object2 name1 = object1 name2 = object2 else: try: db1, name1 = object1.split(".") db2, name2 = object2.split(".") except: raise UtilError("Invalid object name arguments for _get_transform" "(): %s, %s." % (object1, object2)) db_1 = Database(server1, db1, options) db_2 = Database(server2, db2, options) if obj_type is None: obj_type = db_1.get_object_type(name1) transform_str = [] obj1 = db_1.get_object_definition(db1, name1, obj_type) obj2 = db_2.get_object_definition(db2, name2, obj_type) # Get the transformation based on direction. transform_str = [] same_db_name = True xform = SQLTransformer(db_1, db_2, obj1[0], obj2[0], obj_type, options.get("verbosity", 0)) differences = xform.transform_definition() if differences is not None and len(differences) > 0: transform_str.extend(differences) return transform_str
def get_create_object(server, object_name, options): """Get the object's create statement. This method retrieves the object create statement from the database. server[in] server connection object_name[in] name of object in the form db.objectname options[in] options: verbosity, quiet Returns string : create statement or raise error if object or db not exist """ from mysql.utilities.common.database import Database verbosity = options.get("verbosity", 0) quiet = options.get("quiet", False) db_name, sep, obj_name = object_name.partition(".") object = [db_name] db = Database(server, object[0], options) # Error if atabase does not exist if not db.exists(): raise UtilDBError("The database does not exist: {0}".format(object[0])) if not obj_name: object.append(object[0]) obj_type = "DATABASE" else: object.append(obj_name) obj_type = db.get_object_type(object[1]) if obj_type is None: raise UtilDBError( "The object {0} does not exist.".format(object_name)) create_stmt = db.get_create_statement(object[0], object[1], obj_type) if verbosity > 0 and not quiet: print "\n# Definition for object {0}:".format(object_name) print create_stmt return create_stmt
def get_create_object(server, object_name, options): """Get the object's create statement. This method retrieves the object create statement from the database. server[in] server connection object_name[in] name of object in the form db.objectname options[in] options: verbosity, quiet Returns string : create statement or raise error if object or db not exist """ from mysql.utilities.common.database import Database verbosity = options.get("verbosity", 0) quiet = options.get("quiet", False) db_name, sep, obj_name = object_name.partition(".") object = [db_name] db = Database(server, object[0], options) # Error if atabase does not exist if not db.exists(): raise UtilDBError("The database does not exist: {0}".format(object[0])) if not obj_name: object.append(object[0]) obj_type = "DATABASE" else: object.append(obj_name) obj_type = db.get_object_type(object[1]) if obj_type is None: raise UtilDBError("The object {0} does not exist.". format(object_name)) create_stmt = db.get_create_statement(object[0], object[1], obj_type) if verbosity > 0 and not quiet: print "\n# Definition for object {0}:".format(object_name) print create_stmt return create_stmt
def database_compare(server1_val, server2_val, db1, db2, options): """Perform a consistency check among two databases This method performs a database consistency check among two databases which ensures the databases exist, the objects match in number and type, the row counts match for all tables, and the data for each matching tables is consistent. If any errors or differences are found, the operation stops and the difference is printed. The following steps are therefore performed: 1) check to make sure the databases exist and are the same definition 2) check to make sure the same objects exist in each database 3) for each object, ensure the object definitions match among the databases 4) for each table, ensure the row counts are the same 5) for each table, ensure the data is the same By default, the operation stops on any failure of any test. The caller can override this behavior by specifying run_all_tests = True in the options dictionary. TODO: allow the user to skip object types (e.g. --skip-triggers, et. al.) server1_val[in] a dictionary containing connection information for the first server including: (user, password, host, port, socket) server2_val[in] a dictionary containing connection information for the second server including: (user, password, host, port, socket) db1[in] the first database in the compare db2[in] the second database in the compare options[in] a dictionary containing the options for the operation: (quiet, verbosity, difftype, run_all_tests) Returns bool True if all object match, False if partial match """ from mysql.utilities.common.database import Database from mysql.utilities.common.dbcompare import server_connect, \ get_create_object _check_option_defaults(options) # Connect to servers server1, server2 = server_connect(server1_val, server2_val, db1, db2, options) # Check to see if databases exist db1_conn = Database(server1, db1, options) if not db1_conn.exists(): raise UtilDBError(_ERROR_DB_MISSING.format(db1)) db2_conn = Database(server2, db2, options) if not db2_conn.exists(): raise UtilDBError(_ERROR_DB_MISSING.format(db2)) message = "# Checking databases {0} on server1 and {1} on server2\n#" print message.format(db1, db2) # Check for database existance and CREATE differences _check_databases(server1, server2, db1, db2, options) # Get common objects and report discrepencies in_both = _check_objects(server1, server2, db1, db2, db1_conn, db2_conn, options) reporter = _CompareDBReport(options) reporter.print_heading() # Remaining operations can occur in a loop one for each object. success = True if len(in_both) > 0 else False for item in in_both: error_list = [] obj_type = db1_conn.get_object_type(item[1][0]) obj1 = "{0}.{1}".format(db1, item[1][0]) obj2 = "{0}.{1}".format(db2, item[1][0]) reporter.report_object(obj_type, item[1][0]) # Check for differences in CREATE errors = _compare_objects(server1, server2, obj1, obj2, reporter, options) error_list.extend(errors) # Check row counts if obj_type == 'TABLE': errors = _check_row_counts(server1, server2, obj1, obj2, reporter, options) if len(errors) != 0: success = False error_list.extend(errors) else: reporter.report_state("-") # Check data consistency for tables if obj_type == 'TABLE': errors = _check_data_consistency(server1, server2, obj1, obj2, reporter, options) if len(errors) != 0: success = False error_list.extend(errors) else: reporter.report_state("-") if options['verbosity'] > 0: print object1_create = get_create_object(server1, obj1, options) object2_create = get_create_object(server2, obj2, options) reporter.report_errors(error_list) return success
def object_diff(server1_val, server2_val, object1, object2, options, object_type=None): """diff the definition of two objects Find the difference among two object definitions. server1_val[in] a dictionary containing connection information for the first server including: (user, password, host, port, socket) server2_val[in] a dictionary containing connection information for the second server including: (user, password, host, port, socket) object1[in] the first object in the compare in the form: (db.name) object2[in] the second object in the compare in the form: (db.name) options[in] a dictionary containing the options for the operation: (quiet, verbosity, difftype) object_type[in] type of the objects to be compared (e.g., TABLE, PROCEDURE, etc.). By default None (not defined). Returns None = objects are the same, diff[] = tables differ """ server1, server2 = server_connect(server1_val, server2_val, object1, object2, options) force = options.get("force", None) # Get the object type if unknown considering that objects of different # types can be found with the same name. if not object_type: # Get object types of object1 sql_mode = server1.select_variable("SQL_MODE") db_name, obj_name = parse_object_name(object1, sql_mode) db = Database(server1, db_name, options) obj1_types = db.get_object_type(obj_name) if not obj1_types: msg = "The object {0} does not exist.".format(object1) if not force: raise UtilDBError(msg) print("ERROR: {0}".format(msg)) return [] # Get object types of object2 sql_mode = server2.select_variable("SQL_MODE") db_name, obj_name = parse_object_name(object2, sql_mode) db = Database(server2, db_name, options) obj2_types = db.get_object_type(obj_name) if not obj2_types: msg = "The object {0} does not exist.".format(object2) if not force: raise UtilDBError(msg) print("ERROR: {0}".format(msg)) return [] # Merge types found for both objects obj_types = set(obj1_types + obj2_types) # Diff objects considering all types found result = [] for obj_type in obj_types: res = diff_objects(server1, server2, object1, object2, options, obj_type) if res: result.append(res) return result if len(result) > 0 else None else: # Diff objects of known type return diff_objects(server1, server2, object1, object2, options, object_type)
def validate_obj_type_dict(server, obj_type_dict): """Validates the dictionary of objects against the specified server This function builds a dict with the types of the objects in obj_type_dict, filtering out non existing databases and objects. Returns a dictionary with only the existing objects, using object_types as keys and as values a list of tuples (<DB NAME>, <OBJ_NAME>). """ valid_obj_dict = defaultdict(list) server_dbs = set(row[0] for row in server.get_all_databases( ignore_internal_dbs=False)) argument_dbs = set(obj_type_dict.keys()) # Get non existing_databases and dbs to check non_existing_dbs = argument_dbs.difference(server_dbs) dbs_to_check = server_dbs.intersection(argument_dbs) if non_existing_dbs: if len(non_existing_dbs) > 1: plurals = ('s', '', 'them') else: plurals = ('', 'es', 'it') print('# WARNING: specified database{0} do{1} not ' 'exist on base server and will be skipped along ' 'any tables and routines belonging to {2}: ' '{3}.'.format(plurals[0], plurals[1], plurals[2], ", ".join(non_existing_dbs))) # Now for each db that actually exists, get the type of the specified # objects for db_name in dbs_to_check: db = Database(server, db_name) # quote database name if necessary quoted_db_name = db_name if not is_quoted_with_backticks(db_name): quoted_db_name = quote_with_backticks(db_name) for obj_name in obj_type_dict[db_name]: if obj_name is None: # We must consider the database itself valid_obj_dict[DATABASE_TYPE].append((quoted_db_name, quoted_db_name)) else: # get quoted name for obj_name quoted_obj_name = obj_name if not is_quoted_with_backticks(obj_name): quoted_obj_name = quote_with_backticks(obj_name) # Test if the object exists and if it does, test if it # is one of the supported object types, else # print a warning and skip the object obj_type = db.get_object_type(obj_name) if obj_type is None: print("# WARNING: specified object does not exist. " "{0}.{1} will be skipped." "".format(quoted_db_name, quoted_obj_name)) elif 'PROCEDURE' in obj_type or 'FUNCTION' in obj_type: valid_obj_dict[ROUTINE_TYPE].append((quoted_db_name, quoted_obj_name)) elif 'TABLE' in obj_type: valid_obj_dict[TABLE_TYPE].append((quoted_db_name, quoted_obj_name)) else: print('# WARNING: specified object is not supported ' '(not a DATABASE, FUNCTION, PROCEDURE or TABLE),' ' as such it will be skipped: {0}.{1}.' ''.format(quoted_db_name, quoted_obj_name)) return valid_obj_dict
def object_diff(server1_val, server2_val, object1, object2, options, object_type=None): """diff the definition of two objects Find the difference among two object definitions. server1_val[in] a dictionary containing connection information for the first server including: (user, password, host, port, socket) server2_val[in] a dictionary containing connection information for the second server including: (user, password, host, port, socket) object1[in] the first object in the compare in the form: (db.name) object2[in] the second object in the compare in the form: (db.name) options[in] a dictionary containing the options for the operation: (quiet, verbosity, difftype) object_type[in] type of the objects to be compared (e.g., TABLE, PROCEDURE, etc.). By default None (not defined). Returns None = objects are the same, diff[] = tables differ """ if isinstance(server1_val, dict): # dict or common.server.Server object server1, server2 = server_connect(server1_val, server2_val, object1, object2, options) else: # to save connection server1, server2 = server1_val, server2_val force = options.get("force", None) # compare db's all objects include_create = options.get("include_create", False) # db1.*:db2.* if include_create and object1.endswith('.*') and object2.endswith('.*'): direction = options.get("changes-for", None) reverse = options.get("reverse", False) db_name1, _ = parse_object_name(object1, server1.select_variable("SQL_MODE")) db_name2, _ = parse_object_name(object2, server2.select_variable("SQL_MODE")) in_both, in_db1, in_db2 = get_common_objects(server1, server2, db_name1, db_name2, True, options) # create/alter/drop need all objects compare all_object = set(in_both + in_db1 + in_db2) # call myself recusively to compare all objects for this_obj in all_object: object1 = db_name1 + "." + this_obj[1][0] object2 = db_name2 + "." + this_obj[1][0] # share the same connection in this loop. object_type=None object_diff(server1, server2, object1, object2, options, object_type=None) return [] # Get the object type if unknown considering that objects of different # types can be found with the same name. if not object_type: # Get object types of object1 sql_mode = server1.select_variable("SQL_MODE") db_name, obj_name = parse_object_name(object1, sql_mode) db = Database(server1, db_name, options) obj1_types = db.get_object_type(obj_name) if not obj1_types: if include_create: # if allow generating create object ddl, give 'NULL' object here to tell common.dbcompare.py to handle obj1_types = ['NULL'] else: msg = "The object {0} does not exist.".format(object1) if not force: raise UtilDBError(msg) print("ERROR: {0}".format(msg)) return [] # Get object types of object2 sql_mode = server2.select_variable("SQL_MODE") db_name, obj_name = parse_object_name(object2, sql_mode) db = Database(server2, db_name, options) obj2_types = db.get_object_type(obj_name) if not obj2_types: if include_create: obj2_types = ['NULL'] else: msg = "The object {0} does not exist.".format(object2) if not force: raise UtilDBError(msg) print("ERROR: {0}".format(msg)) return [] # Merge types found for both objects obj_types = set(obj1_types + obj2_types) if obj_types == set(['NULL']): msg = "The object {0} or {1} does not exist in the source side.".format( object1, object2) if not force: raise UtilDBError(msg) print("ERROR: {0}".format(msg)) return [] elif 'NULL' in obj_types: # at least one object exist in db1 and db2 # new db object like TABLE-NULL or NULL-TABLE , 'TABLE' is needed for after use in diff_objects() obj_types = set(['-'.join(obj1_types + obj2_types)]) # Diff objects considering all types found result = [] for obj_type in obj_types: res = diff_objects(server1, server2, object1, object2, options, obj_type) if res: result.append(res) return result if len(result) > 0 else None else: # Diff objects of known type return diff_objects(server1, server2, object1, object2, options, object_type)
def object_diff(server1_val, server2_val, object1, object2, options, object_type=None): """diff the definition of two objects Find the difference among two object definitions. server1_val[in] a dictionary containing connection information for the first server including: (user, password, host, port, socket) server2_val[in] a dictionary containing connection information for the second server including: (user, password, host, port, socket) object1[in] the first object in the compare in the form: (db.name) object2[in] the second object in the compare in the form: (db.name) options[in] a dictionary containing the options for the operation: (quiet, verbosity, difftype) object_type[in] type of the objects to be compared (e.g., TABLE, PROCEDURE, etc.). By default None (not defined). Returns None = objects are the same, diff[] = tables differ """ objectype = options.get("objectype", 'ALL').upper() if not object_type and objectype != 'ALL': object_type = objectype if object_type and objectype != 'ALL' and object_type != objectype: print('The object type {} is skip'.format(object_type)) return None server1, server2 = server_connect(server1_val, server2_val, object1, object2, options) force = options.get("force", None) # Get the object type if unknown considering that objects of different # types can be found with the same name. result = [] if not object_type: # Get object types of object1 sql_mode = server1.select_variable("SQL_MODE") db_name, obj_name = parse_object_name(object1, sql_mode) db = Database(server1, db_name, options) obj1_types = db.get_object_type(obj_name) if not obj1_types: msg = "The object {0} does not exist.".format(object1) if not force: raise UtilDBError(msg) print("ERROR: {0}".format(msg)) return [] # Get object types of object2 sql_mode = server2.select_variable("SQL_MODE") db_name, obj_name = parse_object_name(object2, sql_mode) db = Database(server2, db_name, options) obj2_types = db.get_object_type(obj_name) if not obj2_types: msg = "The object {0} does not exist.".format(object2) if not force: raise UtilDBError(msg) print("ERROR: {0}".format(msg)) return [] # Merge types found for both objects obj_types = set(obj1_types + obj2_types) # Diff objects considering all types found for obj_type in obj_types: res = diff_objects(server1, server2, object1, object2, options, obj_type) if res: result.append(res) else: # Diff objects of known type res = diff_objects(server1, server2, object1, object2, options, object_type) if res: result.append(res) if len(result) > 0 and options.get( "difftype", None) == 'sql' and options.get( "output", None) and options.get("output", '').endswith('.sql'): with open(options.get("output"), 'a', encoding='utf8') as fp: for res in result: if isinstance(res, list): for r in res: if r and r.strip().startswith('#'): continue fp.write('{}\n'.format(r)) else: fp.write('{}\n'.format(res)) return result if len(result) > 0 else None
def _get_transform(server1, server2, object1, object2, options): """Get the transformation SQL statements This method generates the SQL statements to transform the destination object based on direction of the compare. server1[in] first server connection server2[in] second server connection object1 the first object in the compare in the form: (db.name) object2 the second object in the compare in the form: (db.name) options[in] a dictionary containing the options for the operation: (quiet, etc.) Returns tuple - (bool - same db name?, list of transformation statements) """ from mysql.utilities.common.database import Database from mysql.utilities.common.sql_transform import SQLTransformer obj_type = None direction = options.get("changes-for", "server1") # If there is no dot, we do not have the format 'db_name.obj_name' for # object1 and therefore must treat it as a database name. if object1.find('.') == -1: obj_type = "DATABASE" # We are working with databases so db and name need to be set # to the database name to tell the get_object_definition() method # to retrieve the database information. db1 = object1 db2 = object2 name1 = object1 name2 = object2 else: try: db1, name1 = object1.split('.') db2, name2 = object2.split('.') except: raise UtilError("Invalid object name arguments for _get_transform" "(): %s, %s." % (object1, object2)) db_1 = Database(server1, db1, options) db_2 = Database(server2, db2, options) if obj_type is None: obj_type = db_1.get_object_type(name1) transform_str = [] obj1 = db_1.get_object_definition(db1, name1, obj_type) obj2 = db_2.get_object_definition(db2, name2, obj_type) # Get the transformation based on direction. transform_str = [] same_db_name = True xform = SQLTransformer(db_1, db_2, obj1[0], obj2[0], obj_type, options.get('verbosity', 0)) differences = xform.transform_definition() if differences is not None and len(differences) > 0: transform_str.extend(differences) return transform_str
def database_compare(server1_val, server2_val, db1, db2, options): """Perform a consistency check among two databases This method performs a database consistency check among two databases which ensures the databases exist, the objects match in number and type, the row counts match for all tables, and the data for each matching tables is consistent. If any errors or differences are found, the operation stops and the difference is printed. The following steps are therefore performed: 1) check to make sure the databases exist and are the same definition 2) check to make sure the same objects exist in each database 3) for each object, ensure the object definitions match among the databases 4) for each table, ensure the row counts are the same 5) for each table, ensure the data is the same By default, the operation stops on any failure of any test. The caller can override this behavior by specifying run_all_tests = True in the options dictionary. TODO: allow the user to skip object types (e.g. --skip-triggers, et. al.) server1_val[in] a dictionary containing connection information for the first server including: (user, password, host, port, socket) server2_val[in] a dictionary containing connection information for the second server including: (user, password, host, port, socket) db1[in] the first database in the compare db2[in] the second database in the compare options[in] a dictionary containing the options for the operation: (quiet, verbosity, difftype, run_all_tests) Returns bool True if all object match, False if partial match """ from mysql.utilities.common.database import Database from mysql.utilities.common.dbcompare import server_connect, \ get_create_object _check_option_defaults(options) # Connect to servers server1, server2 = server_connect(server1_val, server2_val, db1, db2, options) # Check to see if databases exist db1_conn = Database(server1, db1, options) if not db1_conn.exists(): raise UtilDBError(_ERROR_DB_MISSING.format(db1)) db2_conn = Database(server2, db2, options) if not db2_conn.exists(): raise UtilDBError(_ERROR_DB_MISSING.format(db2)) message = "# Checking databases {0} on server1 and {1} on server2\n#" print message.format(db1, db2) # Check for database existance and CREATE differences _check_databases(server1, server2, db1, db2, options) # Get common objects and report discrepencies in_both = _check_objects(server1, server2, db1, db2, db1_conn, db2_conn, options) reporter = _CompareDBReport(options) reporter.print_heading() # Remaining operations can occur in a loop one for each object. success = True if len(in_both) > 0 else False for item in in_both: error_list = [] obj_type = db1_conn.get_object_type(item[1][0]) obj1 = "{0}.{1}".format(db1, item[1][0]) obj2 = "{0}.{1}".format(db2, item[1][0]) q_obj1 = "{0}.{1}".format(quote_with_backticks(db1), quote_with_backticks(item[1][0])) q_obj2 = "{0}.{1}".format(quote_with_backticks(db2), quote_with_backticks(item[1][0])) reporter.report_object(obj_type, item[1][0]) # Check for differences in CREATE errors = _compare_objects(server1, server2, obj1, obj2, reporter, options) error_list.extend(errors) # Check row counts if obj_type == 'TABLE': errors = _check_row_counts(server1, server2, q_obj1, q_obj2, reporter, options) if len(errors) != 0: success = False error_list.extend(errors) else: reporter.report_state("-") # Check data consistency for tables if obj_type == 'TABLE': errors = _check_data_consistency(server1, server2, q_obj1, q_obj2, reporter, options) if len(errors) != 0: success = False error_list.extend(errors) else: reporter.report_state("-") if options['verbosity'] > 0: print object1_create = get_create_object(server1, obj1, options) object2_create = get_create_object(server2, obj2, options) reporter.report_errors(error_list) return success