def __subset_upstream(self, target, processed_tables, relationships):
redacted_relationships = redact_relationships(relationships)
relevant_key_constraints = list(
filter(
lambda r: r['target_table'] in processed_tables and r[
'fk_table'] == target, redacted_relationships))
# this table isn't referenced by anything we've already processed, so let's leave it empty
# OR
# table was already added, this only happens if the upstream table was also a direct target
if len(relevant_key_constraints) == 0 or target in processed_tables:
return False
temp_target_name = 'subset_temp_' + table_name(target)
try:
# copy the whole table
columns_query = columns_to_copy(target, relationships,
self.__source_conn)
self.__db_helper.run_query(
'CREATE TEMPORARY TABLE {} AS SELECT * FROM {} LIMIT 0'.format(
quoter(temp_target_name),
fully_qualified_table(
mysql_db_name_hack(target, self.__destination_conn))),
self.__destination_conn)
query = 'SELECT {} FROM {}'.format(columns_query,
fully_qualified_table(target))
self.__db_helper.copy_rows(self.__source_conn,
self.__destination_conn, query,
temp_target_name)
# filter it down in the target database
table_columns = self.__db_helper.get_table_columns(
table_name(target), schema_name(target), self.__source_conn)
clauses = [
'{} IN (SELECT {} FROM {})'.format(
columns_tupled(kc['fk_columns']),
columns_joined(kc['target_columns']),
fully_qualified_table(
mysql_db_name_hack(kc['target_table'],
self.__destination_conn)))
for kc in relevant_key_constraints
]
clauses.extend(upstream_filter_match(target, table_columns))
select_query = 'SELECT * FROM {} WHERE TRUE AND {}'.format(
quoter(temp_target_name), ' AND '.join(clauses))
insert_query = 'INSERT INTO {} {}'.format(
fully_qualified_table(
mysql_db_name_hack(target, self.__destination_conn)),
select_query)
self.__db_helper.run_query(insert_query, self.__destination_conn)
self.__destination_conn.commit()
finally:
# delete temporary table
mysql_temporary = 'TEMPORARY' if config_reader.get_db_type(
) == 'mysql' else ''
self.__db_helper.run_query(
'DROP {} TABLE IF EXISTS {}'.format(mysql_temporary,
quoter(temp_target_name)),
self.__destination_conn)
return True
def get_redacted_table_references(table_name, tables, conn):
relationships = get_unredacted_fk_relationships(tables, conn)
redacted = redact_relationships(relationships)
return [r for r in redacted if r['target_table'] == table_name]