def compare(trans_id, source_sid, source_did, source_scid,
target_sid, target_did, target_scid):
"""
This function will compare the two schemas.
"""
# Check the transaction and connection status
status, error_msg, diff_model_obj, session_obj = \
check_transaction_status(trans_id)
if error_msg == gettext('Transaction ID not found in the session.'):
return make_json_response(success=0, errormsg=error_msg, status=404)
# Server version compatibility check
status, msg = check_version_compatibility(source_sid, target_sid)
if not status:
return make_json_response(success=0, errormsg=msg, status=428)
comparison_result = []
diff_model_obj.set_comparison_info(gettext("Comparing objects..."), 0)
update_session_diff_transaction(trans_id, session_obj,
diff_model_obj)
try:
all_registered_nodes = SchemaDiffRegistry.get_registered_nodes()
node_percent = round(100 / len(all_registered_nodes))
total_percent = 0
for node_name, node_view in all_registered_nodes.items():
view = SchemaDiffRegistry.get_node_view(node_name)
if hasattr(view, 'compare'):
msg = gettext('Comparing {0}').\
format(gettext(view.blueprint.COLLECTION_LABEL))
diff_model_obj.set_comparison_info(msg, total_percent)
# Update the message and total percentage in session object
update_session_diff_transaction(trans_id, session_obj,
diff_model_obj)
res = view.compare(source_sid=source_sid,
source_did=source_did,
source_scid=source_scid,
target_sid=target_sid,
target_did=target_did,
target_scid=target_scid)
if res is not None:
comparison_result = comparison_result + res
total_percent = total_percent + node_percent
msg = gettext("Successfully compare the specified schemas.")
total_percent = 100
diff_model_obj.set_comparison_info(msg, total_percent)
# Update the message and total percentage done in session object
update_session_diff_transaction(trans_id, session_obj, diff_model_obj)
except Exception as e:
app.logger.exception(e)
return make_json_response(data=comparison_result)
def databases(sid):
"""
This function will return the list of databases for the specified
server id.
"""
res = []
try:
view = SchemaDiffRegistry.get_node_view('database')
server = Server.query.filter_by(id=sid).first()
response = view.nodes(gid=server.servergroup_id, sid=sid)
databases = json.loads(response.data)['data']
for db in databases:
res.append({
"value": db['_id'],
"label": db['label'],
"_id": db['_id'],
"connected": db['connected'],
"allowConn": db['allowConn'],
"image": db['icon'],
"canDisconn": db['canDisconn']
})
except Exception as e:
app.logger.exception(e)
return make_json_response(data=res)
def compare_schema_objects(**kwargs):
"""
This function is used to compare the specified schema and their children.
:param kwargs:
:return:
"""
trans_id = kwargs.get('trans_id')
session_obj = kwargs.get('session_obj')
source_sid = kwargs.get('source_sid')
source_did = kwargs.get('source_did')
source_scid = kwargs.get('source_scid')
target_sid = kwargs.get('target_sid')
target_did = kwargs.get('target_did')
target_scid = kwargs.get('target_scid')
schema_name = kwargs.get('schema_name')
diff_model_obj = kwargs.get('diff_model_obj')
total_percent = kwargs.get('total_percent')
node_percent = kwargs.get('node_percent')
ignore_whitespaces = kwargs.get('ignore_whitespaces')
is_schema_source_only = kwargs.get('is_schema_source_only', False)
source_schema_name = None
if is_schema_source_only:
driver = get_driver(PG_DEFAULT_DRIVER)
source_schema_name = driver.qtIdent(None, schema_name)
comparison_result = []
all_registered_nodes = SchemaDiffRegistry.get_registered_nodes()
for node_name, node_view in all_registered_nodes.items():
view = SchemaDiffRegistry.get_node_view(node_name)
if hasattr(view, 'compare'):
msg = gettext('Comparing {0} of schema \'{1}\''). \
format(gettext(view.blueprint.collection_label),
gettext(schema_name))
diff_model_obj.set_comparison_info(msg, total_percent)
# Update the message and total percentage in session object
update_session_diff_transaction(trans_id, session_obj,
diff_model_obj)
res = view.compare(source_sid=source_sid,
source_did=source_did,
source_scid=source_scid,
target_sid=target_sid,
target_did=target_did,
target_scid=target_scid,
group_name=gettext(schema_name),
ignore_whitespaces=ignore_whitespaces,
source_schema_name=source_schema_name)
if res is not None:
comparison_result = comparison_result + res
total_percent = total_percent + node_percent
# if total_percent is more then 100 then set it to less then 100
if total_percent >= 100:
total_percent = 96
return comparison_result, total_percent
def connect_server(sid):
# Check if server is already connected then no need to reconnect again.
driver = get_driver(PG_DEFAULT_DRIVER)
manager = driver.connection_manager(sid)
conn = manager.connection()
if conn.connected():
return make_json_response(success=1,
info=gettext("Server connected."),
data={})
server = Server.query.filter_by(id=sid).first()
view = SchemaDiffRegistry.get_node_view('server')
return view.connect(server.servergroup_id, sid)
def get_schemas(sid, did):
"""
This function will return the list of schemas for the specified
server id and database id.
"""
try:
view = SchemaDiffRegistry.get_node_view('schema')
server = Server.query.filter_by(id=sid).first()
response = view.nodes(gid=server.servergroup_id, sid=sid, did=did,
is_schema_diff=True)
schemas = json.loads(response.data)['data']
return schemas
except Exception as e:
app.logger.exception(e)
return None
def generate_script(trans_id):
"""This function will generate the scripts for the selected objects."""
data = request.form if request.form else json.loads(
request.data, encoding='utf-8'
)
status, error_msg, diff_model_obj, session_obj = \
check_transaction_status(trans_id)
if error_msg == gettext('Transaction ID not found in the session.'):
return make_json_response(success=0, errormsg=error_msg, status=404)
source_sid = int(data['source_sid'])
source_did = int(data['source_did'])
source_scid = int(data['source_scid'])
target_sid = int(data['target_sid'])
target_did = int(data['target_did'])
target_scid = int(data['target_scid'])
diff_ddl = ''
for d in data['sel_rows']:
node_type = d['node_type']
source_oid = int(d['source_oid'])
target_oid = int(d['target_oid'])
comp_status = d['comp_status']
view = SchemaDiffRegistry.get_node_view(node_type)
if view and hasattr(view, 'ddl_compare') and \
comp_status != SchemaDiffModel.COMPARISON_STATUS['identical']:
sql = view.ddl_compare(source_sid=source_sid,
source_did=source_did,
source_scid=source_scid,
target_sid=target_sid,
target_did=target_did,
target_scid=target_scid,
source_oid=source_oid,
target_oid=target_oid,
comp_status=comp_status,
generate_script=True)
diff_ddl += sql['diff_ddl']
return ajax_response(
status=200,
response={'diff_ddl': diff_ddl}
)
def compare_database_objects(**kwargs):
"""
This function is used to compare the specified schema and their children.
:param kwargs:
:return:
"""
trans_id = kwargs.get('trans_id')
session_obj = kwargs.get('session_obj')
source_sid = kwargs.get('source_sid')
source_did = kwargs.get('source_did')
target_sid = kwargs.get('target_sid')
target_did = kwargs.get('target_did')
diff_model_obj = kwargs.get('diff_model_obj')
total_percent = kwargs.get('total_percent')
node_percent = kwargs.get('node_percent')
ignore_owner = kwargs.get('ignore_owner')
ignore_whitespaces = kwargs.get('ignore_whitespaces')
comparison_result = []
all_registered_nodes = SchemaDiffRegistry.get_registered_nodes(
None, 'Database')
for node_name, node_view in all_registered_nodes.items():
view = SchemaDiffRegistry.get_node_view(node_name)
if hasattr(view, 'compare'):
msg = gettext('Comparing {0}'). \
format(gettext(view.blueprint.collection_label))
app.logger.debug(msg)
diff_model_obj.set_comparison_info(msg, total_percent)
# Update the message and total percentage in session object
update_session_diff_transaction(trans_id, session_obj,
diff_model_obj)
res = view.compare(source_sid=source_sid,
source_did=source_did,
target_sid=target_sid,
target_did=target_did,
group_name=gettext('Database Objects'),
ignore_owner=ignore_owner,
ignore_whitespaces=ignore_whitespaces)
if res is not None:
comparison_result = comparison_result + res
total_percent = total_percent + node_percent
return comparison_result, total_percent
def ddl_compare(trans_id, source_sid, source_did, source_scid, target_sid,
target_did, target_scid, source_oid, target_oid, node_type,
comp_status):
"""
This function is used to compare the specified object and return the
DDL comparison.
"""
# Check the transaction and connection status
status, error_msg, diff_model_obj, session_obj = \
check_transaction_status(trans_id)
if error_msg == gettext('Transaction ID not found in the session.'):
return make_json_response(success=0, errormsg=error_msg, status=404)
source_ddl = ''
target_ddl = ''
diff_ddl = ''
view = SchemaDiffRegistry.get_node_view(node_type)
if view and hasattr(view, 'ddl_compare'):
sql = view.ddl_compare(source_sid=source_sid,
source_did=source_did,
source_scid=source_scid,
target_sid=target_sid,
target_did=target_did,
target_scid=target_scid,
source_oid=source_oid,
target_oid=target_oid,
comp_status=comp_status)
return ajax_response(status=200,
response={
'source_ddl': sql['source_ddl'],
'target_ddl': sql['target_ddl'],
'diff_ddl': sql['diff_ddl']
})
msg = gettext('Selected object is not supported for DDL comparison.')
return ajax_response(status=200,
response={
'source_ddl': msg,
'target_ddl': msg,
'diff_ddl': msg
})
def schemas(sid, did):
"""
This function will return the list of schemas for the specified
server id and database id.
"""
res = []
try:
view = SchemaDiffRegistry.get_node_view('schema')
server = Server.query.filter_by(id=sid).first()
response = view.nodes(gid=server.servergroup_id, sid=sid, did=did)
schemas = json.loads(response.data)['data']
for sch in schemas:
res.append({
"value": sch['_id'],
"label": sch['label'],
"_id": sch['_id'],
"image": sch['icon'],
})
except Exception as e:
app.logger.exception(e)
return make_json_response(data=res)
def get_sql_from_submodule_diff(self, source_params, target_params,
target_schema, source, target, diff_dict):
"""
This function returns the DDL/DML statements of the
submodules of table based on the comparison status.
:param source_params:
:param target_params:
:param target_schema:
:param source:
:param target:
:param diff_dict:
:return:
"""
# Get the difference result for source and target columns
col_diff = self.table_col_comp(source, target)
diff_dict.update(col_diff)
# Get the difference result for source and target constraints
pk_diff = self.table_constraint_comp(source, target)
diff_dict.update(pk_diff)
# Get the difference DDL/DML statements for table
target_params['diff_data'] = diff_dict
diff = self.get_sql_from_table_diff(**target_params)
ignore_sub_modules = ['column', 'constraints']
if self.manager.version < 100000:
ignore_sub_modules.append('partition')
if self.manager.server_type == 'pg' or self.manager.version < 120000:
ignore_sub_modules.append('compound_trigger')
# Iterate through all the sub modules of the table
for module in self.blueprint.submodules:
if module.NODE_TYPE not in ignore_sub_modules:
module_view = \
SchemaDiffRegistry.get_node_view(module.NODE_TYPE)
if module.NODE_TYPE == 'partition' and \
('is_partitioned' in source and source['is_partitioned'])\
and ('is_partitioned' in target and
target['is_partitioned']):
target_ddl = module_view.ddl_compare(
target_params=target_params,
parent_source_data=source,
parent_target_data=target)
diff += '\n' + target_ddl
elif module.NODE_TYPE != 'partition':
dict1 = copy.deepcopy(source[module.NODE_TYPE])
dict2 = copy.deepcopy(target[module.NODE_TYPE])
# Find the duplicate keys in both the dictionaries
dict1_keys = set(dict1.keys())
dict2_keys = set(dict2.keys())
intersect_keys = dict1_keys.intersection(dict2_keys)
# Keys that are available in source and missing in target.
added = dict1_keys - dict2_keys
for item in added:
source_ddl = module_view.ddl_compare(
source_params=source_params,
target_params=target_params,
source=dict1[item],
target=None,
target_schema=target_schema,
comp_status='source_only')
diff += '\n' + source_ddl
# Keys that are available in target and missing in source.
removed = dict2_keys - dict1_keys
for item in removed:
target_ddl = module_view.ddl_compare(
source_params=source_params,
target_params=target_params,
source=None,
target=dict2[item],
target_schema=target_schema,
comp_status='target_only')
diff += '\n' + target_ddl
# Keys that are available in both source and target.
for key in intersect_keys:
# Recursively Compare the two dictionary
if not are_dictionaries_identical(
dict1[key], dict2[key], self.keys_to_ignore):
diff_ddl = module_view.ddl_compare(
source_params=source_params,
target_params=target_params,
source=dict1[key],
target=dict2[key],
target_schema=target_schema,
comp_status='different',
parent_source_data=source,
parent_target_data=target)
diff += '\n' + diff_ddl
return diff
def get_sql_from_submodule_diff(self, **kwargs):
"""
This function returns the DDL/DML statements of the
submodules of table based on the comparison status.
:param kwargs:
:return:
"""
source_params = kwargs.get('source_params')
target_params = kwargs.get('target_params')
source = kwargs.get('source')
target = kwargs.get('target')
diff_dict = kwargs.get('diff_dict')
ignore_whitespaces = kwargs.get('ignore_whitespaces')
# Get the difference result for source and target columns
col_diff = self.table_col_comp(source, target)
diff_dict.update(col_diff)
# Get the difference result for source and target constraints
pk_diff = self.table_constraint_comp(source, target)
diff_dict.update(pk_diff)
# Get the difference DDL/DML statements for table
target_params['diff_data'] = diff_dict
diff = self.get_sql_from_table_diff(**target_params)
ignore_sub_modules = ['column', 'constraints']
if self.manager.version < 100000:
ignore_sub_modules.append('partition')
if self.manager.server_type == 'pg' or self.manager.version < 120000:
ignore_sub_modules.append('compound_trigger')
# Iterate through all the sub modules of the table
for module in self.blueprint.submodules:
if module.node_type not in ignore_sub_modules:
module_view = \
SchemaDiffRegistry.get_node_view(module.node_type)
if module.node_type == 'partition' and \
('is_partitioned' in source and source['is_partitioned'])\
and ('is_partitioned' in target and
target['is_partitioned']):
target_ddl = module_view.ddl_compare(
target_params=target_params,
parent_source_data=source,
parent_target_data=target
)
diff += '\n' + target_ddl
elif module.node_type != 'partition':
dict1 = copy.deepcopy(source[module.node_type])
dict2 = copy.deepcopy(target[module.node_type])
# Find the duplicate keys in both the dictionaries
dict1_keys = set(dict1.keys())
dict2_keys = set(dict2.keys())
intersect_keys = dict1_keys.intersection(dict2_keys)
# Keys that are available in source and missing in target.
added = dict1_keys - dict2_keys
diff = SchemaDiffTableCompare._compare_source_only(
added, module_view, source_params, target_params,
dict1, diff)
# Keys that are available in target and missing in source.
removed = dict2_keys - dict1_keys
diff = SchemaDiffTableCompare._compare_target_only(
removed, module_view, source_params, target_params,
dict2, diff)
# Keys that are available in both source and target.
other_param = {
"dict1": dict1,
"dict2": dict2,
"ignore_whitespaces": ignore_whitespaces,
"source": source,
"target": target
}
diff = self._compare_source_and_target(
intersect_keys, module_view, source_params,
target_params, diff, **other_param)
return diff
def connect_database(sid, did):
server = Server.query.filter_by(id=sid).first()
view = SchemaDiffRegistry.get_node_view('database')
return view.connect(server.servergroup_id, sid, did)
def compare(self, **kwargs):
"""
This function is used to compare all the table objects
from two different schemas.
:return: Comparison Dictionary
"""
src_sid = kwargs.get('source_sid')
src_did = kwargs.get('source_did')
src_scid = kwargs.get('source_scid')
tar_sid = kwargs.get('target_sid')
tar_did = kwargs.get('target_did')
tar_scid = kwargs.get('target_scid')
sub_modules = ['index', 'rule', 'trigger']
source_tables = self.fetch_tables(sid=src_sid,
did=src_did,
scid=src_scid)
target_tables = self.fetch_tables(sid=tar_sid,
did=tar_did,
scid=tar_scid)
if self.manager.version >= 120000:
sub_modules.append('compound_trigger')
# If both the dict have no items then return None.
if not (source_tables or target_tables) or (len(source_tables) <= 0 and
len(target_tables) <= 0):
return None
src_server_type, tar_server_type = self.get_server_type(
src_sid, tar_sid)
for module in sub_modules:
module_view = SchemaDiffRegistry.get_node_view(module)
# Get sub module data for source tables
if module_view.blueprint.server_type is None or \
src_server_type in module_view.blueprint.server_type:
for key, val in source_tables.items():
source = module_view.fetch_objects_to_compare(
sid=src_sid,
did=src_did,
scid=src_scid,
tid=val['oid'],
oid=None,
ignore_keys=True)
source_tables[key][module] = source
# Get sub module data for target tables
if module_view.blueprint.server_type is None or \
tar_server_type in module_view.blueprint.server_type:
for key, val in target_tables.items():
target = module_view.fetch_objects_to_compare(
sid=tar_sid,
did=tar_did,
scid=tar_scid,
tid=val['oid'],
oid=None,
ignore_keys=True)
target_tables[key][module] = target
return compare_dictionaries(source_tables, target_tables,
self.node_type,
self.blueprint.COLLECTION_LABEL,
self.keys_to_ignore)
def ddl_compare(self, **kwargs):
"""
This function will compare properties of 2 tables and
return the source DDL, target DDL and Difference of them.
"""
src_sid = kwargs.get('source_sid')
src_did = kwargs.get('source_did')
src_scid = kwargs.get('source_scid')
src_oid = kwargs.get('source_oid')
tar_sid = kwargs.get('target_sid')
tar_did = kwargs.get('target_did')
tar_scid = kwargs.get('target_scid')
tar_oid = kwargs.get('target_oid')
comp_status = kwargs.get('comp_status')
generate_script = False
if 'generate_script' in kwargs and kwargs['generate_script']:
generate_script = True
source = ''
target = ''
diff = ''
ignore_sub_modules = ['column', 'constraints']
src_server_type, tar_server_type = self.get_server_type(
src_sid, tar_sid)
status, target_schema = self.get_schema(tar_sid, tar_did, tar_scid)
if not status:
return internal_server_error(errormsg=target_schema)
if comp_status == SchemaDiffModel.COMPARISON_STATUS['source_only']:
if not generate_script:
source = self.get_sql_from_table_diff(sid=src_sid,
did=src_did,
scid=src_scid,
tid=src_oid,
json_resp=False)
diff = self.get_sql_from_table_diff(sid=src_sid,
did=src_did,
scid=src_scid,
tid=src_oid,
diff_schema=target_schema,
json_resp=False)
elif comp_status == SchemaDiffModel.COMPARISON_STATUS['target_only']:
if not generate_script:
target = self.get_sql_from_table_diff(sid=tar_sid,
did=tar_did,
scid=tar_scid,
tid=tar_oid,
json_resp=False)
diff = self.get_drop_sql(sid=tar_sid,
did=tar_did,
scid=tar_scid,
tid=tar_oid)
elif comp_status == SchemaDiffModel.COMPARISON_STATUS['different']:
source = self.fetch_tables(
sid=src_sid,
did=src_did,
scid=src_scid,
tid=src_oid,
keys_to_remove=self.keys_to_remove_ddl_comp)
target = self.fetch_tables(
sid=tar_sid,
did=tar_did,
scid=tar_scid,
tid=tar_oid,
keys_to_remove=self.keys_to_remove_ddl_comp)
if self.manager.version < 100000:
ignore_sub_modules.append('partition')
if self.manager.version < 120000:
ignore_sub_modules.append('compound_trigger')
# In case of error return None
if not (source or target):
return None
diff_dict = directory_diff(
source,
target,
ignore_keys=self.keys_to_ignore_ddl_comp,
difference={})
# Column comparison
col_diff = self.table_col_ddl_comp(source, target)
diff_dict.update(col_diff)
# Constraint comparison
pk_diff = self.constraint_ddl_comp(source, target, diff_dict)
diff_dict.update(pk_diff)
diff_dict.update(self.parce_acl(source, target))
if not generate_script:
source = self.get_sql_from_table_diff(sid=src_sid,
did=src_did,
scid=src_scid,
tid=src_oid,
json_resp=False)
target = self.get_sql_from_table_diff(sid=tar_sid,
did=tar_did,
scid=tar_scid,
tid=tar_oid,
json_resp=False)
diff = self.get_sql_from_table_diff(sid=tar_sid,
did=tar_did,
scid=tar_scid,
tid=tar_oid,
diff_data=diff_dict,
json_resp=False)
for module in self.blueprint.submodules:
if module.NODE_TYPE not in ignore_sub_modules:
module_view = SchemaDiffRegistry.get_node_view(
module.NODE_TYPE)
if module_view.blueprint.server_type and (
src_server_type
not in module_view.blueprint.server_type
and tar_server_type
not in module_view.blueprint.server_type):
continue
if module_view.blueprint.server_type and (
(src_server_type in module_view.blueprint.server_type
and tar_server_type
not in module_view.blueprint.server_type) or
(src_server_type
not in module_view.blueprint.server_type
and tar_server_type
in module_view.blueprint.server_type)):
continue
result = module_view.compare(source_sid=src_sid,
source_did=src_did,
source_scid=src_scid,
source_tid=src_oid,
target_sid=tar_sid,
target_did=tar_did,
target_scid=tar_scid,
target_tid=tar_oid)
if result and module.NODE_TYPE != 'partition':
child_diff = ''
for res in result:
if res['status'] == \
SchemaDiffModel.COMPARISON_STATUS[
'different']:
source_oid = res['source_oid']
target_oid = res['target_oid']
else:
source_oid = res['oid']
target_oid = res['oid']
if res['status'] != \
SchemaDiffModel.COMPARISON_STATUS[
'identical']:
child_diff = module_view.ddl_compare(
source_sid=src_sid,
source_did=src_did,
source_scid=src_scid,
source_oid=source_oid,
source_tid=src_oid,
target_sid=tar_sid,
target_did=tar_did,
target_scid=tar_scid,
target_tid=tar_oid,
target_oid=target_oid,
comp_status=res['status'])
if child_diff:
diff += '\n' + child_diff
elif result:
# For partition module
identical = False
source_only = False
target_only = False
different = False
for res in result:
if res['status'] == \
SchemaDiffModel.COMPARISON_STATUS[
'identical']:
identical = True
elif res['status'] == \
SchemaDiffModel.COMPARISON_STATUS[
'source_only']:
source_only = True
elif res['status'] == \
SchemaDiffModel.COMPARISON_STATUS[
'target_only']:
target_only = True
else:
different = True
if identical:
pass
elif (source_only or target_only) and not different:
for res in result:
source_oid = res['oid']
target_oid = res['oid']
child_diff = module_view.ddl_compare(
source_sid=src_sid,
source_did=src_did,
source_scid=src_scid,
source_oid=source_oid,
source_tid=src_oid,
target_sid=tar_sid,
target_did=tar_did,
target_scid=tar_scid,
target_tid=tar_oid,
target_oid=target_oid,
comp_status=res['status'])
if child_diff:
diff += child_diff
else:
diff = self.get_sql_from_table_diff(
sid=src_sid,
did=src_did,
scid=src_scid,
tid=src_oid,
diff_schema=target_schema,
json_resp=False,
schema_diff_table=True)
else:
source = self.get_sql_from_table_diff(sid=src_sid,
did=src_did,
scid=src_scid,
tid=src_oid,
json_resp=False)
target = self.get_sql_from_table_diff(sid=tar_sid,
did=tar_did,
scid=tar_scid,
tid=tar_oid,
json_resp=False)
return {'source_ddl': source, 'target_ddl': target, 'diff_ddl': diff}
