def merge_tables(source: DbData, target: DbData, table_name: str) -> RowsDict:
source_table = db_helpers.get_table(source, table_name)
target_table = db_helpers.get_table(target, table_name)
# source_table.columns.user_id.foreign_keys
# TODO: pass strategy from user input
merged_rows = RowsDict(table_name=table_name, strategy=SourceMergeStrategy())
if db_helpers.table_structures_equal(source_table, target_table):
for row in source.session.query(source_table).all():
merged_rows.put(
hash_row(source_table, row),
row,
"source"
)
for row in target.session.query(target_table).all():
merged_rows.put(
hash_row(target_table, row),
row,
"target"
)
print("merged:", merged_rows)
# rows = adjust_relationships(merged_rows)
# # truncating does not work...
# db_helpers.truncate_table(target, table_name)
# db_helpers.insert_rows(target, target_table, rows)
else:
print(
f"WARNING: not merging tables with name '{table_name}' "
"because their structures are not equal."
)
return merged_rows
def test_get_table(self):
user_table = db_helpers.get_table(self.db, "users")
order_table = db_helpers.get_table(self.db, "orders")
self.assertIsInstance(user_table, Table)
self.assertEqual(user_table.name, "users")
self.assertIsInstance(order_table, Table)
self.assertEqual(order_table.name, "orders")
def setUp(self):
Base = declarative_base()
class User(Base): # type: ignore
__tablename__ = "users"
id = Column(Integer, primary_key=True)
name = Column(String)
password = Column(String)
orders = relationship("Order")
self.User = User
class Order(Base): # type: ignore
__tablename__ = "orders"
id = Column(Integer, primary_key=True)
# Using string instead of numeric to avoid sqlalchemy's warning.
total = Column(String)
user_id = Column(Integer, ForeignKey("users.id"))
user = relationship("User", back_populates="orders")
self.Order = Order
engine = create_engine(self.DB_URL)
engine2 = create_engine(self.DB2_URL)
Base.metadata.create_all(bind=engine)
Base.metadata.create_all(bind=engine2)
self.db = db_helpers.get_reflected_db(self.DB_URL)
self.db2 = db_helpers.get_reflected_db(self.DB2_URL)
# DATA SETUP
db_helpers.insert_rows(self.db, db_helpers.get_table(self.db, "users"), [
(1, "testuser1", "pw1"),
(2, "testuser2 with more data", "pw2"),
(3, "testuser3", "pw3"),
(4, "testuser4", "pw4"),
])
db_helpers.insert_rows(self.db, db_helpers.get_table(self.db, "orders"), [
(1, "30.12", 1),
(2, "12.39", 2),
(3, "42.00", 3),
(4, "43.00", 4),
])
db_helpers.insert_rows(self.db2, db_helpers.get_table(self.db2, "users"), [
(2, "testuser2", "pw21"),
(3, "testuser3 with more data", "pw3"),
(7, "testuser4", "pw4"),
])
db_helpers.insert_rows(self.db2, db_helpers.get_table(self.db2, "orders"), [
(2, "13.37", 2),
(5, "51.10", 3),
(6, "1.18", 7),
])
def merge_dbs(source: DbData, target: DbData) -> None:
common_tables = (
set(table_name for table_name in source.inspector.get_table_names())
&
set(table_name for table_name in target.inspector.get_table_names())
)
logging.debug(
f"common_tables of {source.engine.url} and {target.engine.url}:"
+ str(sorted(common_tables))
)
# TODO: analyze foreign keys:
# Each database is expected to have valid foreign keys.
# That means all FKs of a database reference the databas'es tables only.
# Thus there are 2 dependency graphs (1 for each database).
# They most likely have tables (in the dep. graph) in common.
# Since we assume the database's tables to have the same structure we know
# that tables with the same name also have the same dependencies.
# EXAMPLE:
# Graph 1:
# A -> B -> B ...
# In the case of cycles (B, e.g. tree structures) assigning primary and
# foreign keys MUST BE 2 STEPS.
merged_tables = []
# The sorting will place Table objects that have dependencies first,
# before the dependencies themselves, representing the order
# in which they can be created.
source_tables = reversed(source.base.metadata.sorted_tables)
for source_table in source_tables:
table_name = source_table.name
# source_table = source_tables[table_name]
if table_name in common_tables:
print("merging table", table_name)
merged_tables.append(merge_tables(source, target, table_name))
else:
print("copying table", table_name)
db_helpers.copy_table(source, target, table_name)
merged_and_adjusted_relations_tables = adjust_relationships(target, merged_tables)
for table_name, rows_to_insert in merged_and_adjusted_relations_tables.items():
db_helpers.truncate_table(target, table_name)
db_helpers.insert_rows(
target,
db_helpers.get_table(target, table_name),
rows_to_insert
)
def test_insert_rows(self):
db_helpers.insert_rows(self.db, db_helpers.get_table(self.db, "users"),
DbHelpersTest.get_test_data())
queried_rows = self.db.session.query(self.tables["users"]).all()
self.assertEqual(queried_rows, DbHelpersTest.get_test_data())