class TestPostgresDBSync(unittest.TestCase):
def setUp(self):
self.temp_schema = TEMP_SCHEMA
self.db = Postgres()
# Create a schema.
setup_sql = f"""
DROP SCHEMA IF EXISTS {self.temp_schema} CASCADE;
CREATE SCHEMA {self.temp_schema};
"""
self.db.query(setup_sql)
# Load dummy data to parsons tables
self.table1 = Table.from_csv(f'{_dir}/test_data/sample_table_1.csv')
self.table2 = Table.from_csv(f'{_dir}/test_data/sample_table_2.csv')
# Create source table
self.db.copy(self.table1, f'{self.temp_schema}.source')
# Create DB Sync object
self.db_sync = DBSync(self.db, self.db)
def tearDown(self):
# Drop the view, the table and the schema
teardown_sql = f"""
DROP SCHEMA IF EXISTS {self.temp_schema} CASCADE;
"""
self.db.query(teardown_sql)
def test_table_sync_full_drop(self):
# Test a db sync with drop.
self.db_sync.table_sync_full(f'{self.temp_schema}.source',
f'{self.temp_schema}.destination')
source = self.db.query(f"SELECT * FROM {self.temp_schema}.source")
destination = self.db.query(
f"SELECT * FROM {self.temp_schema}.destination")
assert_matching_tables(source, destination)
def test_table_sync_full_truncate(self):
# Test a db sync with truncate.
self.db_sync.table_sync_full(f'{self.temp_schema}.source',
f'{self.temp_schema}.destination',
if_exists='truncate')
source = self.db.query(f"SELECT * FROM {self.temp_schema}.source")
destination = self.db.query(
f"SELECT * FROM {self.temp_schema}.destination")
assert_matching_tables(source, destination)
def test_table_sync_full_empty_table(self):
# Test a full sync of a table when the source table is empty.
# Empty the source table
self.db.query(f"TRUNCATE {self.temp_schema}.source")
# Attempt to sync.
self.db_sync.table_sync_full(f'{self.temp_schema}.source',
f'{self.temp_schema}.destination')
def test_table_sync_full_chunk(self):
# Test chunking in full sync.
self.db_sync.chunk_size = 10
self.db_sync.table_sync_full(f'{self.temp_schema}.source',
f'{self.temp_schema}.destination')
source = self.db.query(f"SELECT * FROM {self.temp_schema}.source")
destination = self.db.query(
f"SELECT * FROM {self.temp_schema}.destination")
assert_matching_tables(source, destination)
def test_table_sync_incremental(self):
# Test that incremental sync
self.db.copy(self.table1, f'{self.temp_schema}.destination')
self.db.copy(self.table2,
f'{self.temp_schema}.source',
if_exists='append')
self.db_sync.table_sync_incremental(f'{self.temp_schema}.source',
f'{self.temp_schema}.destination',
'pk')
count1 = self.db.query(f"SELECT * FROM {self.temp_schema}.source")
count2 = self.db.query(f"SELECT * FROM {self.temp_schema}.destination")
assert_matching_tables(count1, count2)
def test_table_sync_incremental_chunk(self):
# Test chunking of incremental sync.
self.db_sync.chunk_size = 10
self.db.copy(self.table1, f'{self.temp_schema}.destination')
self.db.copy(self.table2,
f'{self.temp_schema}.source',
if_exists='append')
self.db_sync.table_sync_incremental(f'{self.temp_schema}.source',
f'{self.temp_schema}.destination',
'pk')
count1 = self.db.query(f"SELECT * FROM {self.temp_schema}.source")
count2 = self.db.query(f"SELECT * FROM {self.temp_schema}.destination")
assert_matching_tables(count1, count2)
def test_table_sync_incremental_create_destination_table(self):
# Test that an incremental sync works if the destination table does not exist.
self.db_sync.table_sync_incremental(f'{self.temp_schema}.source',
f'{self.temp_schema}.destination',
'pk')
count1 = self.db.query(f"SELECT * FROM {self.temp_schema}.source")
count2 = self.db.query(f"SELECT * FROM {self.temp_schema}.destination")
assert_matching_tables(count1, count2)
def test_table_sync_incremental_empty_table(self):
# Test an incremental sync of a table when the source table is empty.
# Empty the source table
self.db.query(f"TRUNCATE {self.temp_schema}.source")
# Attempt to sync.
self.db_sync.table_sync_incremental(f'{self.temp_schema}.source',
f'{self.temp_schema}.destination',
'pk')
class TestPostgresDB(unittest.TestCase):
def setUp(self):
self.temp_schema = TEMP_SCHEMA
self.pg = Postgres()
self.tbl = Table([['ID', 'Name'], [1, 'Jim'], [2, 'John'],
[3, 'Sarah']])
# Create a schema, create a table, create a view
setup_sql = f"""
drop schema if exists {self.temp_schema} cascade;
create schema {self.temp_schema};
"""
other_sql = f"""
create table {self.temp_schema}.test (id smallint,name varchar(5));
create view {self.temp_schema}.test_view as (select * from {self.temp_schema}.test);
""" # noqa: E501
self.pg.query(setup_sql)
self.pg.query(other_sql)
def tearDown(self):
# Drop the view, the table and the schema
teardown_sql = f"""
drop schema if exists {self.temp_schema} cascade;
"""
self.pg.query(teardown_sql)
def test_query(self):
# Check that query sending back expected result
r = self.pg.query('select 1')
self.assertEqual(r[0]['?column?'], 1)
def test_query_with_parameters(self):
table_name = f"{self.temp_schema}.test"
self.pg.copy(self.tbl, f"{self.temp_schema}.test", if_exists='append')
sql = f"select * from {table_name} where name = %s"
name = 'Sarah'
r = self.pg.query(sql, parameters=[name])
self.assertEqual(r[0]['name'], name)
sql = f"select * from {table_name} where name in (%s, %s)"
names = ['Sarah', 'John']
r = self.pg.query(sql, parameters=names)
self.assertEqual(r.num_rows, 2)
def test_copy(self):
# Copy a table and ensure table exists
self.pg.copy(self.tbl,
f'{self.temp_schema}.test_copy',
if_exists='drop')
r = self.pg.query(
f"select * from {self.temp_schema}.test_copy where name='Jim'")
self.assertEqual(r[0]['id'], 1)
# Copy table and ensure truncate works.
self.pg.copy(self.tbl,
f'{self.temp_schema}.test_copy',
if_exists='truncate')
tbl = self.pg.query(
f"select count(*) from {self.temp_schema}.test_copy")
self.assertEqual(tbl.first, 3)
# Copy table and ensure that drop works.
self.pg.copy(self.tbl,
f'{self.temp_schema}.test_copy',
if_exists='drop')
tbl = self.pg.query(
f"select count(*) from {self.temp_schema}.test_copy")
self.assertEqual(tbl.first, 3)
# Copy table and ensure that append works.
self.pg.copy(self.tbl,
f'{self.temp_schema}.test_copy',
if_exists='append')
tbl = self.pg.query(
f"select count(*) from {self.temp_schema}.test_copy")
self.assertEqual(tbl.first, 6)
# Try to copy the table and ensure that default fail works.
self.assertRaises(ValueError, self.pg.copy, self.tbl,
f'{self.temp_schema}.test_copy')
# Try to copy the table and ensure that explicit fail works.
self.assertRaises(ValueError,
self.pg.copy,
self.tbl,
f'{self.temp_schema}.test_copy',
if_exists='fail')