def test_change_tracking_after_update(pg_cursor: pyodbc.Cursor) -> None:
src_db_adapter = adapter.PostgresAdapter()
dest_db_adapter = adapter.PostgresAdapter()
pg_cursor.execute("""
UPDATE sales.customer
SET customer_last_name = 'Smithers'
WHERE customer_first_name = 'Steve'
""")
pg_cursor.commit()
result = pg_cursor.execute("""
SELECT c.customer_last_name
FROM sales.customer AS c
WHERE c.customer_first_name = 'Steve'
""").fetchval()
assert result == "Smithers"
service.update_history_table(
src_cur=pg_cursor,
dest_cur=pg_cursor,
src_db_adapter=src_db_adapter,
dest_db_adapter=dest_db_adapter,
src_schema_name="sales",
src_table_name="customer",
compare_cols=None, # compare all columns
recreate=False,
)
check_history_table_in_sync(cur=pg_cursor)
pg_cursor.execute("""
UPDATE sales.customer
SET customer_last_name = 'Smalls'
WHERE customer_first_name = 'Steve'
""")
pg_cursor.commit()
service.update_history_table(
src_cur=pg_cursor,
dest_cur=pg_cursor,
src_db_adapter=src_db_adapter,
dest_db_adapter=dest_db_adapter,
src_schema_name="sales",
src_table_name="customer",
compare_cols=None, # compare all columns
recreate=False,
)
check_history_table_in_sync(cur=pg_cursor)
result = pg_cursor.execute("""
SELECT h.customer_last_name
FROM sales.customer_history AS h
WHERE
h.customer_first_name = 'Steve'
AND h.valid_to = '9999-12-31'
""").fetchall()
assert result is not None
assert len(result) == 1
assert result[0].customer_last_name == "Smalls"
def setup_table(cur: pyodbc.Cursor, data: List):
''' Create table and populate data. '''
print('Create a new table for users.')
cur.execute(SQL_CREATE_TABLE)
cur.commit()
print('Populate users data.')
for row in data:
cur.execute(SQL_INSERT_DATA, row)
cur.commit()
def _commitInsert(self, cursor: odbc.Cursor) -> None:
prop_names = ""
prop_value_tags = ""
prop_values = []
for prop in self.properties:
if prop.value != None:
if prop_names != "":
prop_names += ","
prop_value_tags += ","
prop_names += prop.name
prop_value_tags += "?"
prop_values.append(prop.value)
cursor.execute(
"INSERT INTO {0} ({1}) VALUES ({2})".format(
self._tableName, prop_names, prop_value_tags), prop_values)
cursor.commit()
cursor.execute("SELECT LAST_INSERT_ID()")
self._primaryKey.value = cursor.fetchone()[0]
def delete_movie(cursor: pyodbc.Cursor, id: int):
query = "DELETE FROM [dbo].[testTable] WHERE id = ?;"
cursor.execute(query, (id))
cursor.commit()
def update_movie(cursor: pyodbc.Cursor, title: str, id: int):
query = "UPDATE [dbo].[testTable] SET title = ? WHERE id = ?;"
cursor.execute(query, (title, id))
cursor.commit()
def test_sync_with_explicit_cols(pg_cursor: pyodbc.Cursor) -> None:
db_adapter = adapter.PostgresAdapter()
result = service.sync(
src_cur=pg_cursor,
dest_cur=pg_cursor,
src_db_adapter=db_adapter,
dest_db_adapter=db_adapter,
src_schema_name="sales",
src_table_name="customer",
dest_schema_name="sales",
dest_table_name="customer2",
pk_cols=["customer_id"],
compare_cols={"customer_first_name", "customer_last_name"},
)
check_customer2_table_in_sync(cur=pg_cursor)
assert result.added == 9
assert result.deleted == 0
assert result.updated == 0
# test update
pg_cursor.execute(
"UPDATE sales.customer SET customer_first_name = 'Frank' WHERE customer_first_name = 'Dan'"
)
pg_cursor.commit()
result = service.sync(
src_cur=pg_cursor,
dest_cur=pg_cursor,
src_db_adapter=db_adapter,
dest_db_adapter=db_adapter,
src_schema_name="sales",
src_table_name="customer",
dest_schema_name="sales",
dest_table_name="customer2",
pk_cols=["customer_id"],
compare_cols={"customer_first_name", "customer_last_name"},
)
check_customer2_table_in_sync(cur=pg_cursor)
assert result.added == 0
assert result.deleted == 0
assert result.updated == 1
# test delete
pg_cursor.execute(
"DELETE FROM sales.customer WHERE customer_first_name = 'Steve'")
pg_cursor.commit()
result = service.sync(
src_cur=pg_cursor,
dest_cur=pg_cursor,
src_db_adapter=db_adapter,
dest_db_adapter=db_adapter,
src_schema_name="sales",
src_table_name="customer",
dest_schema_name="sales",
dest_table_name="customer2",
pk_cols=["customer_id"],
compare_cols={"customer_first_name", "customer_last_name"},
)
check_customer2_table_in_sync(cur=pg_cursor)
rows = pg_cursor.execute("SELECT COUNT(*) FROM sales.customer").fetchval()
assert rows == 8
assert result.added == 0
assert result.deleted == 1
assert result.updated == 0
