def setUp(self):
ensure_db('postgres')
with get_test_connection('postgres') as conn:
with conn.cursor(cursor_factory=psycopg2.extras.DictCursor) as cur:
cur.execute(
'DROP MATERIALIZED VIEW IF EXISTS "LIKE CHICKEN TIMES"')
table_spec = {
"columns": [{
"name": 'our_int_array_pk',
"type": "integer[]",
"primary_key": True
}, {
"name": 'our_text_array',
"type": "text[]"
}],
"name":
TestArraysLikeTable.table_name
}
ensure_test_table(table_spec)
with get_test_connection('postgres') as conn:
with conn.cursor(cursor_factory=psycopg2.extras.DictCursor) as cur:
create_sql = "CREATE MATERIALIZED VIEW {} AS SELECT * FROM {}\n".format(
quote_ident(TestArraysLikeTable.like_table_name, cur),
quote_ident(TestArraysLikeTable.table_name, cur))
cur.execute(create_sql)
def test_catalog(self):
conn_config = get_test_connection_config()
streams = tap_postgres.do_discovery(conn_config)
chicken_streams = [
s for s in streams
if s['tap_stream_id'] == 'postgres-public-CHICKEN TIMES'
]
self.assertEqual(len(chicken_streams), 1)
stream_dict = chicken_streams[0]
stream_dict.get('metadata').sort(key=lambda md: md['breadcrumb'])
with get_test_connection() as conn:
with conn.cursor(cursor_factory=psycopg2.extras.DictCursor) as cur:
cur.execute(
"""INSERT INTO "CHICKEN TIMES" (our_int_array_pk, our_string_array) VALUES ('{{1,2,3},{4,5,6}}', '{{"a","b","c"}}' )"""
)
cur.execute("""SELECT * FROM "CHICKEN TIMES" """)
self.assertEqual(
metadata.to_map(stream_dict.get('metadata')), {
(): {
'table-key-properties': ['our_int_array_pk'],
'database-name': 'postgres',
'schema-name': 'public',
'is-view': False,
'row-count': 0
},
('properties', 'our_int_array_pk'): {
'inclusion': 'automatic',
'sql-datatype': 'integer[]',
'selected-by-default': True
},
('properties', 'our_string_array'): {
'inclusion': 'available',
'sql-datatype': 'character varying[]',
'selected-by-default': True
}
})
self.assertEqual(
{
'properties': {
'our_int_array_pk': {
'type': ['null', 'array'],
'items': {
'$ref':
'#/definitions/sdc_recursive_integer_array'
}
},
'our_string_array': {
'type': ['null', 'array'],
'items': {
'$ref':
'#/definitions/sdc_recursive_string_array'
}
}
},
'type': 'object',
'definitions': tap_postgres.BASE_RECURSIVE_SCHEMAS
}, stream_dict.get('schema'))
def setup(self):
table_spec = {
"columns": [{
"name": "string",
"type": "STRING"
}, {
"name": "integer",
"type": "INTEGER"
}, {
"name": "time_created",
"type": "TIMESTAMP"
}, {
"name": "object",
"type": "VARIANT"
}, {
"name": "boolean",
"type": "BOOLEAN"
}, {
"name": "number",
"type": "NUMBER"
}, {
"name": "date_created",
"type": "DATE"
}],
"schema":
TestMergeTable.schema_name,
"name":
TestMergeTable.table_name
}
con = get_test_connection()
ensure_test_table(con, table_spec)
def test_catalog(self):
conn_config = get_test_connection_config()
streams = tap_postgres.do_discovery(conn_config)
chicken_streams = [
s for s in streams
if s['tap_stream_id'] == 'postgres-public-CHICKEN TIMES'
]
self.assertEqual(len(chicken_streams), 1)
stream_dict = chicken_streams[0]
stream_dict.get('metadata').sort(key=lambda md: md['breadcrumb'])
with get_test_connection() as conn:
with conn.cursor(cursor_factory=psycopg2.extras.DictCursor) as cur:
cur.execute(
"""INSERT INTO "CHICKEN TIMES" (our_pk, our_hstore) VALUES ('size=>"small",name=>"betty"', 'size=>"big",name=>"fred"')"""
)
cur.execute("""SELECT * FROM "CHICKEN TIMES" """)
self.assertEqual(
metadata.to_map(stream_dict.get('metadata')), {
(): {
'table-key-properties': ['our_pk'],
'database-name': 'postgres',
'schema-name': 'public',
'is-view': False,
'row-count': 0
},
('properties', 'our_pk'): {
'inclusion': 'automatic',
'sql-datatype': 'hstore',
'selected-by-default': True
},
('properties', 'our_hstore'): {
'inclusion': 'available',
'sql-datatype': 'hstore',
'selected-by-default': True
}
})
self.assertEqual(
{
'properties': {
'our_hstore': {
'type': ['null', 'object'],
'properties': {}
},
'our_pk': {
'type': ['object'],
'properties': {}
}
},
'type': 'object',
'definitions': tap_postgres.BASE_RECURSIVE_SCHEMAS
}, stream_dict.get('schema'))
def test_catalog(self):
conn_config = get_test_connection_config()
streams = tap_postgres.do_discovery(conn_config)
chicken_streams = [
s for s in streams
if s['tap_stream_id'] == 'postgres-public-LIKE CHICKEN TIMES'
]
self.assertEqual(len(chicken_streams), 1)
stream_dict = chicken_streams[0]
stream_dict.get('metadata').sort(key=lambda md: md['breadcrumb'])
with get_test_connection('postgres') as conn:
with conn.cursor(cursor_factory=psycopg2.extras.DictCursor) as cur:
self.assertEqual(
metadata.to_map(stream_dict.get('metadata')), {
(): {
'table-key-properties': [],
'database-name': 'postgres',
'schema-name': 'public',
'is-view': True,
'row-count': 0
},
('properties', 'our_int_array_pk'): {
'inclusion': 'available',
'sql-datatype': 'integer[]',
'selected-by-default': True
},
('properties', 'our_text_array'): {
'inclusion': 'available',
'sql-datatype': 'text[]',
'selected-by-default': True
}
})
self.assertEqual(
{
'properties': {
'our_int_array_pk': {
'type': ['null', 'array'],
'items': {
'$ref':
'#/definitions/sdc_recursive_integer_array'
}
},
'our_text_array': {
'type': ['null', 'array'],
'items': {
'$ref':
'#/definitions/sdc_recursive_string_array'
}
}
},
'type': 'object',
'definitions': tap_postgres.BASE_RECURSIVE_SCHEMAS
}, stream_dict.get('schema'))
def test_escaping_values(self):
key = 'nickname'
value = "Dave's Courtyard"
elem = '"{}"=>"{}"'.format(key, value)
with get_test_connection() as conn:
with conn.cursor() as cur:
query = tap_postgres.sync_strategies.logical_replication.create_hstore_elem_query(
elem)
self.assertEqual(
query.as_string(cur),
"SELECT hstore_to_array('\"nickname\"=>\"Dave''s Courtyard\"')"
)
def setUp(self):
ensure_db()
with get_test_connection() as conn:
cur = conn.cursor()
table_spec = {"columns": [{"name": "interval_col", "type": "INTERVAL"},
{"name": "bit_string_col", "type": "bit(5)"},
{"name": "bytea_col", "type": "bytea"},
{"name": "point_col", "type": "point"},
{"name": "line_col", "type": "line"},
{"name": "lseg_col", "type": "lseg"},
{"name": "box_col", "type": "box"},
{"name": "polygon_col", "type": "polygon"},
{"name": "circle_col", "type": "circle"},
{"name": "xml_col", "type": "xml"},
{"name": "composite_col", "type": "person_composite"},
{"name": "int_range_col", "type": "int4range"},
],
"name": Unsupported.table_name}
with get_test_connection() as conn:
cur = conn.cursor()
cur.execute(""" DROP TYPE IF EXISTS person_composite CASCADE """)
cur.execute(""" CREATE TYPE person_composite AS (age int, name text) """)
ensure_test_table(table_spec)
def setUp(self):
ensure_db()
table_spec = {
"columns": [{
"name": "id",
"type": "integer",
"serial": True
}, {
"name": 'size integer',
"type": "integer",
"quoted": True
}, {
"name": 'size smallint',
"type": "smallint",
"quoted": True
}, {
"name": 'size bigint',
"type": "bigint",
"quoted": True
}],
"name":
TestColumnGrants.table_name
}
ensure_test_table(table_spec)
with get_test_connection() as conn:
cur = conn.cursor()
sql = """ DROP USER IF EXISTS {} """.format(
self.user, self.password)
LOGGER.info(sql)
cur.execute(sql)
sql = """ CREATE USER {} WITH PASSWORD '{}' """.format(
self.user, self.password)
LOGGER.info(sql)
cur.execute(sql)
sql = """ GRANT SELECT ("id") ON "{}" TO {}""".format(
TestColumnGrants.table_name, self.user)
LOGGER.info("running sql: {}".format(sql))
cur.execute(sql)
def setUp(self):
ensure_db()
table_spec = {
"columns": [{
"name": 'our_mood_enum_pk',
"type": "mood_enum",
"primary_key": True
}, {
"name": 'our_mood_enum',
"type": "mood_enum"
}],
"name":
TestHStoreTable.table_name
}
with get_test_connection() as conn:
cur = conn.cursor()
cur.execute(""" DROP TYPE IF EXISTS mood_enum CASCADE """)
cur.execute(
""" CREATE TYPE mood_enum AS ENUM ('sad', 'ok', 'happy'); """
)
ensure_test_table(table_spec)
def setUp(self):
ensure_db()
table_spec = {
"columns": [{
"name": 'our_pk',
"type": "hstore",
"primary_key": True
}, {
"name": 'our_hstore',
"type": "hstore"
}],
"name":
TestHStoreTable.table_name
}
with get_test_connection() as conn:
cur = conn.cursor()
cur.execute(
""" SELECT installed_version FROM pg_available_extensions WHERE name = 'hstore' """
)
if cur.fetchone()[0] is None:
cur.execute(""" CREATE EXTENSION hstore; """)
ensure_test_table(table_spec)
def test_catalog(self):
con = get_test_connection()
catalog = tap_amplitude.discover_catalog(con).to_dict()
test_streams = [
s for s in catalog['streams']
if s['tap_stream_id'] == "{}-{}".format(
TestEventsTable.schema_name, TestEventsTable.table_name)
]
# Is there one stream found with same name?
self.assertEqual(len(test_streams), 1)
stream_dict = test_streams[0]
self.assertEqual(TestEventsTable.table_name,
stream_dict.get('table_name'))
self.assertEqual(TestEventsTable.table_name, stream_dict.get('stream'))
# Check primary key is "UUID".
mdata = metadata.to_map(stream_dict['metadata'])
stream_metadata = mdata.get((), {})
key_properties = stream_metadata.get('table-key-properties', [])
self.assertEqual(TestEventsTable.key_property, key_properties[0])
def test_catalog(self):
singer.write_message = singer_write_message_no_cow
pg_common.write_schema_message = singer_write_message_ok
conn_config = get_test_connection_config()
streams = tap_postgres.do_discovery(conn_config)
cow_stream = [s for s in streams if s['table_name'] == 'COW'][0]
self.assertIsNotNone(cow_stream)
cow_stream = select_all_of_stream(cow_stream)
cow_stream = set_replication_method_for_stream(cow_stream, 'LOG_BASED')
with get_test_connection() as conn:
conn.autocommit = True
cur = conn.cursor()
cow_rec = {'name': 'betty', 'colour': 'blue'}
insert_record(cur, 'COW', cow_rec)
cow_rec = {'name': 'smelly', 'colour': 'brow'}
insert_record(cur, 'COW', cow_rec)
cow_rec = {'name': 'pooper', 'colour': 'green'}
insert_record(cur, 'COW', cow_rec)
state = {}
#the initial phase of cows logical replication will be a full table.
#it will sync the first record and then blow up on the 2nd record
try:
tap_postgres.do_sync(get_test_connection_config(),
{'streams': streams}, None, state)
except Exception as ex:
blew_up_on_cow = True
self.assertTrue(blew_up_on_cow)
self.assertEqual(7, len(CAUGHT_MESSAGES))
self.assertEqual(CAUGHT_MESSAGES[0]['type'], 'SCHEMA')
self.assertTrue(isinstance(CAUGHT_MESSAGES[1], singer.StateMessage))
self.assertIsNone(CAUGHT_MESSAGES[1].value['bookmarks']
['postgres-public-COW'].get('xmin'))
self.assertIsNotNone(CAUGHT_MESSAGES[1].value['bookmarks']
['postgres-public-COW'].get('lsn'))
end_lsn = CAUGHT_MESSAGES[1].value['bookmarks'][
'postgres-public-COW'].get('lsn')
self.assertTrue(
isinstance(CAUGHT_MESSAGES[2], singer.ActivateVersionMessage))
new_version = CAUGHT_MESSAGES[2].version
self.assertTrue(isinstance(CAUGHT_MESSAGES[3], singer.RecordMessage))
self.assertEqual(CAUGHT_MESSAGES[3].record, {
'colour': 'blue',
'id': 1,
'name': 'betty'
})
self.assertEqual('COW', CAUGHT_MESSAGES[3].stream)
self.assertTrue(isinstance(CAUGHT_MESSAGES[4], singer.StateMessage))
#xmin is set while we are processing the full table replication
self.assertIsNotNone(CAUGHT_MESSAGES[4].value['bookmarks']
['postgres-public-COW']['xmin'])
self.assertEqual(
CAUGHT_MESSAGES[4].value['bookmarks']['postgres-public-COW']
['lsn'], end_lsn)
self.assertEqual(CAUGHT_MESSAGES[5].record['name'], 'smelly')
self.assertEqual('COW', CAUGHT_MESSAGES[5].stream)
self.assertTrue(isinstance(CAUGHT_MESSAGES[6], singer.StateMessage))
last_xmin = CAUGHT_MESSAGES[6].value['bookmarks'][
'postgres-public-COW']['xmin']
old_state = CAUGHT_MESSAGES[6].value
#run another do_sync, should get the remaining record which effectively finishes the initial full_table
#replication portion of the logical replication
singer.write_message = singer_write_message_ok
global COW_RECORD_COUNT
COW_RECORD_COUNT = 0
CAUGHT_MESSAGES.clear()
tap_postgres.do_sync(get_test_connection_config(),
{'streams': streams}, None, old_state)
self.assertEqual(8, len(CAUGHT_MESSAGES))
self.assertEqual(CAUGHT_MESSAGES[0]['type'], 'SCHEMA')
self.assertTrue(isinstance(CAUGHT_MESSAGES[1], singer.StateMessage))
self.assertEqual(
CAUGHT_MESSAGES[1].value['bookmarks']['postgres-public-COW'].get(
'xmin'), last_xmin)
self.assertEqual(
CAUGHT_MESSAGES[1].value['bookmarks']['postgres-public-COW'].get(
'lsn'), end_lsn)
self.assertEqual(
CAUGHT_MESSAGES[1].value['bookmarks']['postgres-public-COW'].get(
'version'), new_version)
self.assertTrue(isinstance(CAUGHT_MESSAGES[2], singer.RecordMessage))
self.assertEqual(CAUGHT_MESSAGES[2].record, {
'colour': 'brow',
'id': 2,
'name': 'smelly'
})
self.assertEqual('COW', CAUGHT_MESSAGES[2].stream)
self.assertTrue(isinstance(CAUGHT_MESSAGES[3], singer.StateMessage))
self.assertTrue(
CAUGHT_MESSAGES[3].value['bookmarks']['postgres-public-COW'].get(
'xmin'), last_xmin)
self.assertEqual(
CAUGHT_MESSAGES[3].value['bookmarks']['postgres-public-COW'].get(
'lsn'), end_lsn)
self.assertEqual(
CAUGHT_MESSAGES[3].value['bookmarks']['postgres-public-COW'].get(
'version'), new_version)
self.assertTrue(isinstance(CAUGHT_MESSAGES[4], singer.RecordMessage))
self.assertEqual(CAUGHT_MESSAGES[4].record['name'], 'pooper')
self.assertEqual('COW', CAUGHT_MESSAGES[4].stream)
self.assertTrue(isinstance(CAUGHT_MESSAGES[5], singer.StateMessage))
self.assertTrue(CAUGHT_MESSAGES[5].value['bookmarks']
['postgres-public-COW'].get('xmin') > last_xmin)
self.assertEqual(
CAUGHT_MESSAGES[5].value['bookmarks']['postgres-public-COW'].get(
'lsn'), end_lsn)
self.assertEqual(
CAUGHT_MESSAGES[5].value['bookmarks']['postgres-public-COW'].get(
'version'), new_version)
self.assertTrue(
isinstance(CAUGHT_MESSAGES[6], singer.ActivateVersionMessage))
self.assertEqual(CAUGHT_MESSAGES[6].version, new_version)
self.assertTrue(isinstance(CAUGHT_MESSAGES[7], singer.StateMessage))
self.assertIsNone(CAUGHT_MESSAGES[7].value['bookmarks']
['postgres-public-COW'].get('xmin'))
self.assertEqual(
CAUGHT_MESSAGES[7].value['bookmarks']['postgres-public-COW'].get(
'lsn'), end_lsn)
self.assertEqual(
CAUGHT_MESSAGES[7].value['bookmarks']['postgres-public-COW'].get(
'version'), new_version)
def test_catalog(self):
singer.write_message = singer_write_message_no_cow
pg_common.write_schema_message = singer_write_message_ok
conn_config = get_test_connection_config()
streams = tap_postgres.do_discovery(conn_config)
cow_stream = [s for s in streams if s['table_name'] == 'COW'][0]
self.assertIsNotNone(cow_stream)
cow_stream = select_all_of_stream(cow_stream)
cow_stream = set_replication_method_for_stream(cow_stream,
'FULL_TABLE')
chicken_stream = [s for s in streams
if s['table_name'] == 'CHICKEN'][0]
self.assertIsNotNone(chicken_stream)
chicken_stream = select_all_of_stream(chicken_stream)
chicken_stream = set_replication_method_for_stream(
chicken_stream, 'FULL_TABLE')
with get_test_connection() as conn:
conn.autocommit = True
cur = conn.cursor()
cow_rec = {'name': 'betty', 'colour': 'blue'}
insert_record(cur, 'COW', cow_rec)
cow_rec = {'name': 'smelly', 'colour': 'brow'}
insert_record(cur, 'COW', cow_rec)
cow_rec = {'name': 'pooper', 'colour': 'green'}
insert_record(cur, 'COW', cow_rec)
chicken_rec = {'name': 'fred', 'colour': 'red'}
insert_record(cur, 'CHICKEN', chicken_rec)
state = {}
#this will sync the CHICKEN but then blow up on the COW
try:
tap_postgres.do_sync(get_test_connection_config(),
{'streams': streams}, None, state)
except Exception as ex:
# LOGGER.exception(ex)
blew_up_on_cow = True
self.assertTrue(blew_up_on_cow)
self.assertEqual(14, len(CAUGHT_MESSAGES))
self.assertEqual(CAUGHT_MESSAGES[0]['type'], 'SCHEMA')
self.assertTrue(isinstance(CAUGHT_MESSAGES[1], singer.StateMessage))
self.assertIsNone(CAUGHT_MESSAGES[1].value['bookmarks']
['postgres-public-CHICKEN'].get('xmin'))
self.assertTrue(
isinstance(CAUGHT_MESSAGES[2], singer.ActivateVersionMessage))
new_version = CAUGHT_MESSAGES[2].version
self.assertTrue(isinstance(CAUGHT_MESSAGES[3], singer.RecordMessage))
self.assertEqual('CHICKEN', CAUGHT_MESSAGES[3].stream)
self.assertTrue(isinstance(CAUGHT_MESSAGES[4], singer.StateMessage))
#xmin is set while we are processing the full table replication
self.assertIsNotNone(CAUGHT_MESSAGES[4].value['bookmarks']
['postgres-public-CHICKEN']['xmin'])
self.assertTrue(
isinstance(CAUGHT_MESSAGES[5], singer.ActivateVersionMessage))
self.assertEqual(CAUGHT_MESSAGES[5].version, new_version)
self.assertTrue(isinstance(CAUGHT_MESSAGES[6], singer.StateMessage))
self.assertEqual(
None, singer.get_currently_syncing(CAUGHT_MESSAGES[6].value))
#xmin is cleared at the end of the full table replication
self.assertIsNone(CAUGHT_MESSAGES[6].value['bookmarks']
['postgres-public-CHICKEN']['xmin'])
#cow messages
self.assertEqual(CAUGHT_MESSAGES[7]['type'], 'SCHEMA')
self.assertEqual("COW", CAUGHT_MESSAGES[7]['stream'])
self.assertTrue(isinstance(CAUGHT_MESSAGES[8], singer.StateMessage))
self.assertIsNone(CAUGHT_MESSAGES[8].value['bookmarks']
['postgres-public-COW'].get('xmin'))
self.assertEqual("postgres-public-COW",
CAUGHT_MESSAGES[8].value['currently_syncing'])
self.assertTrue(
isinstance(CAUGHT_MESSAGES[9], singer.ActivateVersionMessage))
cow_version = CAUGHT_MESSAGES[9].version
self.assertTrue(isinstance(CAUGHT_MESSAGES[10], singer.RecordMessage))
self.assertEqual(CAUGHT_MESSAGES[10].record['name'], 'betty')
self.assertEqual('COW', CAUGHT_MESSAGES[10].stream)
self.assertTrue(isinstance(CAUGHT_MESSAGES[11], singer.StateMessage))
#xmin is set while we are processing the full table replication
self.assertIsNotNone(CAUGHT_MESSAGES[11].value['bookmarks']
['postgres-public-COW']['xmin'])
self.assertEqual(CAUGHT_MESSAGES[12].record['name'], 'smelly')
self.assertEqual('COW', CAUGHT_MESSAGES[12].stream)
old_state = CAUGHT_MESSAGES[13].value
#run another do_sync
singer.write_message = singer_write_message_ok
CAUGHT_MESSAGES.clear()
global COW_RECORD_COUNT
COW_RECORD_COUNT = 0
tap_postgres.do_sync(get_test_connection_config(),
{'streams': streams}, None, old_state)
self.assertEqual(CAUGHT_MESSAGES[0]['type'], 'SCHEMA')
self.assertTrue(isinstance(CAUGHT_MESSAGES[1], singer.StateMessage))
# because we were interrupted, we do not switch versions
self.assertEqual(
CAUGHT_MESSAGES[1].value['bookmarks']['postgres-public-COW']
['version'], cow_version)
self.assertIsNotNone(CAUGHT_MESSAGES[1].value['bookmarks']
['postgres-public-COW']['xmin'])
self.assertEqual(
"postgres-public-COW",
singer.get_currently_syncing(CAUGHT_MESSAGES[1].value))
self.assertTrue(isinstance(CAUGHT_MESSAGES[2], singer.RecordMessage))
self.assertEqual(CAUGHT_MESSAGES[2].record['name'], 'smelly')
self.assertEqual('COW', CAUGHT_MESSAGES[2].stream)
#after record: activate version, state with no xmin or currently syncing
self.assertTrue(isinstance(CAUGHT_MESSAGES[3], singer.StateMessage))
#we still have an xmin for COW because are not yet done with the COW table
self.assertIsNotNone(CAUGHT_MESSAGES[3].value['bookmarks']
['postgres-public-COW']['xmin'])
self.assertEqual(
singer.get_currently_syncing(CAUGHT_MESSAGES[3].value),
'postgres-public-COW')
self.assertTrue(isinstance(CAUGHT_MESSAGES[4], singer.RecordMessage))
self.assertEqual(CAUGHT_MESSAGES[4].record['name'], 'pooper')
self.assertEqual('COW', CAUGHT_MESSAGES[4].stream)
self.assertTrue(isinstance(CAUGHT_MESSAGES[5], singer.StateMessage))
self.assertIsNotNone(CAUGHT_MESSAGES[5].value['bookmarks']
['postgres-public-COW']['xmin'])
self.assertEqual(
singer.get_currently_syncing(CAUGHT_MESSAGES[5].value),
'postgres-public-COW')
#xmin is cleared because we are finished the full table replication
self.assertTrue(
isinstance(CAUGHT_MESSAGES[6], singer.ActivateVersionMessage))
self.assertEqual(CAUGHT_MESSAGES[6].version, cow_version)
self.assertTrue(isinstance(CAUGHT_MESSAGES[7], singer.StateMessage))
self.assertIsNone(
singer.get_currently_syncing(CAUGHT_MESSAGES[7].value))
self.assertIsNone(CAUGHT_MESSAGES[7].value['bookmarks']
['postgres-public-CHICKEN']['xmin'])
self.assertIsNone(
singer.get_currently_syncing(CAUGHT_MESSAGES[7].value))
