def database_diff(server1_val, server2_val, db1, db2, options):
"""Find differences among objects from two databases.
This method compares the object definitions among two databases. If any
differences are found, the differences are printed in the format chosen
and the method returns False. A True result is returned only when all
object definitions match.
The method will stop and return False on the first difference found unless
the option force is set to True (default = False).
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, force)
Returns bool True if all object match, False if partial match
"""
from mysql.utilities.common.dbcompare import get_common_objects
from mysql.utilities.common.dbcompare import server_connect
force = options.get("force", False)
server1, server2 = server_connect(server1_val, server2_val, db1, db2,
options)
in_both, in_db1, in_db2 = get_common_objects(server1, server2, db1, db2,
True, options)
in_both.sort()
if (len(in_db1) > 0 or len(in_db2) > 0) and not force:
return False
# Do the diff for the databases themselves
result = object_diff(server1, server2, db1, db2, options)
if result is not None:
success = False
if not force:
return False
# For each that match, do object diff
success = True
for item in in_both:
obj_name1 = quote_with_backticks(item[1][0]) \
if is_quoted_with_backticks(db1) else item[1][0]
obj_name2 = quote_with_backticks(item[1][0]) \
if is_quoted_with_backticks(db2) else item[1][0]
object1 = "%s.%s" % (db1, obj_name1)
object2 = "%s.%s" % (db2, obj_name2)
result = object_diff(server1, server2, object1, object2, options)
if result is not None:
success = False
if not force:
return False
return success
def object_diff(server1_val, server2_val, object1, object2, options):
"""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)
Returns None = objects are the same, diff[] = tables differ
"""
from mysql.utilities.common.dbcompare import diff_objects, server_connect
try:
server1, server2 = server_connect(server1_val, server2_val,
object1, object2, options)
except:
raise
result = diff_objects(server1, server2, object1, object2, options)
return result
def object_diff(server1_val, server2_val, object1, object2, options):
"""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)
Returns None = objects are the same, diff[] = tables differ
"""
from mysql.utilities.common.dbcompare import diff_objects, server_connect
server1, server2 = server_connect(server1_val, server2_val, object1,
object2, options)
result = diff_objects(server1, server2, object1, object2, options)
return result
def database_diff(server1_val, server2_val, db1, db2, options):
"""Find differences among objects from two databases.
This method compares the object definitions among two databases. If any
differences are found, the differences are printed in the format chosen
and the method returns False. A True result is returned only when all
object definitions match.
The method will stop and return False on the first difference found unless
the option force is set to True (default = False).
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, force)
Returns bool True if all object match, False if partial match
"""
from mysql.utilities.common.dbcompare import get_common_objects
from mysql.utilities.common.dbcompare import server_connect
force = options.get("force", False)
server1, server2 = server_connect(server1_val, server2_val,
db1, db2, options)
in_both, in_db1, in_db2 = get_common_objects(server1, server2,
db1, db2, True, options)
in_both.sort()
if (len(in_db1) > 0 or len(in_db2) > 0) and not force:
return False
# Do the diff for the databases themselves
result = object_diff(server1, server2, db1, db2, options)
if result is not None:
success = False
if not force:
return False
# For each that match, do object diff
success = True
for item in in_both:
obj_name1 = quote_with_backticks(item[1][0]) \
if is_quoted_with_backticks(db1) else item[1][0]
obj_name2 = quote_with_backticks(item[1][0]) \
if is_quoted_with_backticks(db2) else item[1][0]
object1 = "%s.%s" % (db1, obj_name1)
object2 = "%s.%s" % (db2, obj_name2)
result = object_diff(server1, server2, object1, object2, options)
if result is not None:
success = False
if not force:
return False
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)
# 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 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 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
"""
_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))
# Print a different message is server2 is not defined
if not server2_val:
message = "# Checking databases {0} and {1} on server1\n#"
else:
message = "# Checking databases {0} on server1 and {1} on server2\n#"
print(message.format(db1_conn.db_name, db2_conn.db_name))
# Check for database existence and CREATE differences
_check_databases(server1, server2, db1_conn.q_db_name, db2_conn.q_db_name,
options)
# Get common objects and report discrepancies
(in_both, differs) = _check_objects(server1, server2, db1, db2, db1_conn,
db2_conn, options)
success = not differs
reporter = _CompareDBReport(options)
reporter.print_heading()
# Remaining operations can occur in a loop one for each object.
for item in in_both:
error_list = []
debug_msgs = []
# Set the object type
obj_type = item[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, q_obj1, q_obj2, reporter,
options, obj_type)
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:
error_list.extend(errors)
else:
reporter.report_state("-")
# Check data consistency for tables
if obj_type == 'TABLE':
errors, debug_msgs = _check_data_consistency(
server1, server2, q_obj1, q_obj2, reporter, options)
if len(errors) != 0:
error_list.extend(errors)
else:
reporter.report_state("-")
if options['verbosity'] > 0:
print
get_create_object(server1, q_obj1, options, obj_type)
get_create_object(server2, q_obj2, options, obj_type)
if debug_msgs and options['verbosity'] > 2:
reporter.report_errors(debug_msgs)
reporter.report_errors(error_list)
# Fail if errors are found
if error_list:
success = False
return success
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
"""
_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))
# Print a different message is server2 is not defined
if not server2_val:
message = "# Checking databases {0} and {1} on server1\n#"
else:
message = "# Checking databases {0} on server1 and {1} on server2\n#"
print(message.format(db1_conn.db_name, db2_conn.db_name))
# Check for database existence and CREATE differences
_check_databases(server1, server2, db1_conn.q_db_name, db2_conn.q_db_name,
options)
# Get common objects and report discrepancies
(in_both, differs) = _check_objects(server1, server2, db1, db2,
db1_conn, db2_conn, options)
success = not differs
reporter = _CompareDBReport(options)
reporter.print_heading()
# Remaining operations can occur in a loop one for each object.
for item in in_both:
error_list = []
# Set the object type
obj_type = item[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, q_obj1, q_obj2,
reporter, options, obj_type)
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:
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:
error_list.extend(errors)
else:
reporter.report_state("-")
if options['verbosity'] > 0:
print
get_create_object(server1, obj1, options, obj_type)
get_create_object(server2, obj2, options, obj_type)
reporter.report_errors(error_list)
# Fail if errors are found
if error_list:
success = False
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 database_diff(server1_val, server2_val, db1, db2, options):
"""Find differences among objects from two databases.
This method compares the object definitions among two databases. If any
differences are found, the differences are printed in the format chosen
and the method returns False. A True result is returned only when all
object definitions match.
The method will stop and return False on the first difference found unless
the option force is set to True (default = False).
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, force)
Returns bool True if all object match, False if partial match
"""
force = options.get("force", False)
server1, server2 = server_connect(server1_val, server2_val,
db1, db2, options)
in_both, in_db1, in_db2 = get_common_objects(server1, server2,
db1, db2, True, options)
in_both.sort()
if (len(in_db1) > 0 or len(in_db2) > 0) and not force:
return False
# Get sql_mode value set on servers
server1_sql_mode = server1.select_variable("SQL_MODE")
server2_sql_mode = server2.select_variable("SQL_MODE")
# Quote database names with backticks.
q_db1 = db1 if is_quoted_with_backticks(db1, server1_sql_mode) \
else quote_with_backticks(db1, server1_sql_mode)
q_db2 = db2 if is_quoted_with_backticks(db2, server2_sql_mode) \
else quote_with_backticks(db2, server2_sql_mode)
# Do the diff for the databases themselves
result = object_diff(server1, server2, q_db1, q_db2, options, 'DATABASE')
if result is not None:
success = False
if not force:
return False
# For each that match, do object diff
success = True
for item in in_both:
# Quote object name with backticks with sql_mode from server1
q_obj_name1 = item[1][0] if \
is_quoted_with_backticks(item[1][0], server1_sql_mode) \
else quote_with_backticks(item[1][0], server1_sql_mode)
# Quote object name with backticks with sql_mode from server2
q_obj_name2 = item[1][0] if \
is_quoted_with_backticks(item[1][0], server2_sql_mode) \
else quote_with_backticks(item[1][0], server2_sql_mode)
object1 = "{0}.{1}".format(q_db1, q_obj_name1)
object2 = "{0}.{1}".format(q_db2, q_obj_name2)
result = object_diff(server1, server2, object1, object2, options,
item[0])
if result is not None:
success = False
if not force:
return False
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
"""
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 database_diff(server1_val, server2_val, db1, db2, options):
"""Find differences among objects from two databases.
This method compares the object definitions among two databases. If any
differences are found, the differences are printed in the format chosen
and the method returns False. A True result is returned only when all
object definitions match.
The method will stop and return False on the first difference found unless
the option force is set to True (default = False).
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, force)
Returns bool True if all object match, False if partial match
"""
force = options.get("force", False)
server1, server2 = server_connect(server1_val, server2_val, db1, db2,
options)
in_both, in_db1, in_db2 = get_common_objects(server1, server2, db1, db2,
True, options)
direction = options.get("changes-for", None)
if direction == 'server1' or direction is None:
include = [
'{}.{}'.format(db2, tb[1][0]) if tb[0] == 'TABLE' else ''
for tb in in_db2
]
server = '{}:{}@{}:{}'.format(server2_val.get('user', 'root'),
server2_val.get('passwd', '123456'),
server2_val.get('host', 'localhost'),
server2_val.get('port', '3306'))
db = db2
else:
include = [
'{}.{}'.format(db1, tb[1][0]) if tb[0] == 'TABLE' else ''
for tb in in_db1
]
server = '{}:{}@{}:{}'.format(server1_val.get('user', 'root'),
server1_val.get('passwd', '123456'),
server1_val.get('host', 'localhost'),
server1_val.get('port', '3306'))
db = db1
if include:
default = [
'--server={}'.format(server), '--skip-blobs', '--skip-gtid',
'--skip-fkey-checks', '--multiprocess=0',
'--output-file={}'.format(options.get("output", "")),
'--skip=views,triggers,procedures,functions,events,grants,data,create_db'
]
for tb in include:
default.append('--include={}'.format(tb))
parser = parse_options()
if parser:
export_db(parser, args=default, values=[db])
in_both.sort()
if (len(in_db1) > 0 or len(in_db2) > 0) and not force:
return False
# Get sql_mode value set on servers
server1_sql_mode = server1.select_variable("SQL_MODE")
server2_sql_mode = server2.select_variable("SQL_MODE")
# Quote database names with backticks.
q_db1 = db1 if is_quoted_with_backticks(db1, server1_sql_mode) \
else quote_with_backticks(db1, server1_sql_mode)
q_db2 = db2 if is_quoted_with_backticks(db2, server2_sql_mode) \
else quote_with_backticks(db2, server2_sql_mode)
# Do the diff for the databases themselves
result = object_diff(server1, server2, q_db1, q_db2, options, 'DATABASE')
if result is not None:
success = False
if not force:
return False
# For each that match, do object diff
success = True
for item in in_both:
# Quote object name with backticks with sql_mode from server1
q_obj_name1 = item[1][0] if \
is_quoted_with_backticks(item[1][0], server1_sql_mode) \
else quote_with_backticks(item[1][0], server1_sql_mode)
# Quote object name with backticks with sql_mode from server2
q_obj_name2 = item[1][0] if \
is_quoted_with_backticks(item[1][0], server2_sql_mode) \
else quote_with_backticks(item[1][0], server2_sql_mode)
object1 = "{0}.{1}".format(q_db1, q_obj_name1)
object2 = "{0}.{1}".format(q_db2, q_obj_name2)
result = object_diff(server1, server2, object1, object2, options,
item[0])
if result is not None:
success = False
if not force:
return False
return success
