def __assert_no_events_exist_in_database(self, idp_fraud_events):
with RunInTransaction(self.db_connection) as cursor:
for event in idp_fraud_events:
cursor.execute(
"""
SELECT
a.id,
a.idp_entity_id,
a.idp_event_id,
a.time_stamp,
a.fid_code,
a.request_id,
a.pid,
a.client_ip_address,
a.contra_score,
a.upload_session_id,
b.source_file_name
FROM idp_data.idp_fraud_events a
INNER JOIN idp_data.upload_sessions b ON a.upload_session_id = b.id
WHERE a.idp_entity_id = %s
AND a.idp_event_id = %s
""", [event.idp_entity_id, event.idp_event_id])
result = cursor.fetchone()
self.assertIsNone(result)
def __assert_fraud_events_table_has_fraud_event_records(self, expected_fraud_events):
for fraud_event in expected_fraud_events:
with RunInTransaction(self.db_connection) as cursor:
cursor.execute("""
SELECT
time_stamp,
session_id,
hashed_persistent_id,
request_id,
entity_id,
fraud_event_id,
fraud_indicator,
transaction_entity_id
FROM
billing.fraud_events
WHERE
event_id = %s;
""", [fraud_event[0]])
matching_records = cursor.fetchone()
self.assertIsNotNone(matching_records)
self.assertEqual(matching_records[0], datetime.fromtimestamp(TIMESTAMP / 1e3))
self.assertEqual(matching_records[1], fraud_event[1])
self.assertEqual(matching_records[2], PID)
self.assertEqual(matching_records[3], REQUEST_ID)
self.assertEqual(matching_records[4], IDP_ENTITY_ID)
self.assertEqual(matching_records[5], fraud_event[2])
self.assertEqual(matching_records[6], GPG45_STATUS)
self.assertEqual(matching_records[7], TRANSACTION_ENTITY_ID)
def __assert_billing_events_table_has_billing_event_records(self, expected_ids):
for (session_id, event_id) in expected_ids:
with RunInTransaction(self.db_connection) as cursor:
cursor.execute("""
SELECT
time_stamp,
session_id,
hashed_persistent_id,
request_id,
idp_entity_id,
minimum_level_of_assurance,
preferred_level_of_assurance,
provided_level_of_assurance,
event_id,
transaction_entity_id
FROM
billing.billing_events
WHERE
session_id = %s;
""", [session_id])
matching_records = cursor.fetchone()
self.assertIsNotNone(matching_records)
self.assertEqual(matching_records[0], datetime.fromtimestamp(TIMESTAMP / 1e3))
self.assertEqual(matching_records[1], session_id)
self.assertEqual(matching_records[2], PID)
self.assertEqual(matching_records[3], REQUEST_ID)
self.assertEqual(matching_records[4], IDP_ENTITY_ID)
self.assertEqual(matching_records[5], MINIMUM_LEVEL_OF_ASSURANCE)
self.assertEqual(matching_records[6], PROVIDED_LEVEL_OF_ASSURANCE)
self.assertEqual(matching_records[7], PREFERRED_LEVEL_OF_ASSURANCE)
self.assertEqual(matching_records[8], event_id)
self.assertEqual(matching_records[9], TRANSACTION_ENTITY_ID)
def __clean_db(self):
with RunInTransaction(self.db_connection) as cursor:
cursor.execute("""
DELETE FROM audit.audit_events;
DELETE FROM billing.billing_events;
DELETE FROM billing.fraud_events;
""")
def __assert_audit_events_table_has_fraud_event_records(self, expected_events):
for event in expected_events:
with RunInTransaction(self.db_connection) as cursor:
cursor.execute("""
SELECT
event_id,
time_stamp,
originating_service,
session_id,
event_type,
details->>'session_event_type',
details->>'pid',
details->>'request_id',
details->>'idp_entity_id',
details->>'idp_fraud_event_id',
details->>'gpg45_status'
FROM
audit.audit_events
WHERE
event_id = %s;
""", [event[0]])
matching_records = cursor.fetchone()
self.assertIsNotNone(matching_records)
self.assertEqual(matching_records[0], event[0])
self.assertEqual(matching_records[1], datetime.fromtimestamp(TIMESTAMP / 1e3))
self.assertEqual(matching_records[2], ORIGINATING_SERVICE)
self.assertEqual(matching_records[3], event[1])
self.assertEqual(matching_records[4], EVENT_TYPE)
self.assertEqual(matching_records[5], FRAUD_SESSION_EVENT_TYPE)
self.assertEqual(matching_records[6], PID)
self.assertEqual(matching_records[7], REQUEST_ID)
self.assertEqual(matching_records[8], IDP_ENTITY_ID)
self.assertEqual(matching_records[9], event[2])
self.assertEqual(matching_records[10], GPG45_STATUS)
def __assert_fraud_events_table_has_no_fraud_event_records(self):
with RunInTransaction(self.db_connection) as cursor:
cursor.execute("""
SELECT
*
FROM
billing.fraud_events;
""")
matching_records = cursor.fetchone()
self.assertIsNone(matching_records)
def clean_db(db_connection):
with RunInTransaction(db_connection) as cursor:
cursor.execute("""
DELETE FROM idp_data.idp_fraud_event_contraindicators;
DELETE FROM idp_data.idp_fraud_events;
DELETE FROM idp_data.upload_session_validation_failures;
DELETE FROM idp_data.upload_sessions;
DELETE FROM billing.fraud_events;
DELETE FROM billing.billing_events;
DELETE FROM audit.audit_events;
""")
def __assert_database_has_records(self, expected_event_ids):
for event_id in expected_event_ids:
with RunInTransaction(self.db_connection) as cursor:
cursor.execute(
"""
SELECT * FROM events WHERE event_id = %s;
""", [event_id])
matching_records = cursor.fetchone()
self.assertIsNotNone(matching_records)
self.assertEqual(matching_records[0], event_id)
self.assertEqual(matching_records[1], EVENT_TYPE)
self.assertEqual(matching_records[2],
datetime.strptime(TIMESTAMP, '%Y-%m-%d %H:%M:%S'))
self.assertEqual(matching_records[3],
{'sessionEventType': SESSION_EVENT_TYPE})
def process_file(bucket,
filename,
upload_session,
db_connection,
has_header=DEFAULT_HAS_HEADER,
dialect=DEFAULT_DIALECT,
timezone=DEFAULT_TIMEZONE):
logger.info('Processing data for IDP {}'.format(
upload_session.idp_entity_id))
temp_file = download_import_file(bucket, filename)
try:
with open(temp_file, newline='') as csvfile:
reader = csv.reader(csvfile, dialect=dialect)
errors_occurred = False
skip_row = has_header
row_number = 0
try:
with RunInTransaction(db_connection) as cursor:
for row in reader:
row_number = row_number + 1
if skip_row:
skip_row = False
continue
idp_fraud_event = parse_line(
row, upload_session.idp_entity_id, timezone)
id = write_idp_fraud_event_to_database(
upload_session, idp_fraud_event, cursor, logger)
if id:
logger.info(
'Successfully wrote IDP fraud event ID {} to database.'
.format(idp_fraud_event.idp_event_id))
except Exception as exception:
message = 'Failed to store IDP fraud event: {} (line {})'.format(
exception, row_number)
logger.exception(message)
write_upload_error(upload_session, row_number,
'**Row Exception**', message, db_connection)
errors_occurred = True
finally:
os.remove(temp_file)
return not errors_occurred
def __assert_error_in_database_failure_table(self, row, field, message):
with RunInTransaction(self.db_connection) as cursor:
cursor.execute("""
SELECT
id,
upload_session_id,
row,
field,
message
FROM idp_data.upload_session_validation_failures
""")
result = cursor.fetchone()
self.assertIsNotNone(result)
self.assertIsNotNone(result[0])
self.assertIsNotNone(result[1])
self.assertEqual(result[2], row)
self.assertEqual(result[3], field)
self.assertEqual(result[4], message)
def __assert_upload_session_exists_in_database(self, passed_validation):
with RunInTransaction(self.db_connection) as cursor:
cursor.execute("""
SELECT
id,
source_file_name,
idp_entity_id,
userid,
passed_validation
FROM idp_data.upload_sessions
""")
result = cursor.fetchone()
self.assertIsNotNone(result)
self.assertIsNotNone(result[0])
self.assertEqual(result[1], UPLOAD_FILE_NAME)
self.assertEqual(result[2], IDP_ENTITY_ID)
self.assertEqual(result[3], UPLOAD_USERNAME)
self.assertEqual(result[4], passed_validation)
def __assert_audit_events_table_has_billing_event_records(
self, expected_events, minimum_level_of_assurance):
for event in expected_events:
with RunInTransaction(self.db_connection) as cursor:
cursor.execute(
"""
SELECT
event_id,
time_stamp,
originating_service,
session_id,
event_type,
details->>'session_event_type',
details->>'pid',
details->>'request_id',
details->>'idp_entity_id',
details->>'transaction_entity_id',
details->>'minimum_level_of_assurance',
details->>'provided_level_of_assurance',
details->>'preferred_level_of_assurance'
FROM
audit.audit_events
WHERE
event_id = %s;
""", [event[0]])
matching_records = cursor.fetchone()
self.assertIsNotNone(matching_records)
self.assertEqual(matching_records[0], event[0])
self.assertEqual(matching_records[1],
datetime.fromtimestamp(TIMESTAMP / 1e3))
self.assertEqual(matching_records[2], ORIGINATING_SERVICE)
self.assertEqual(matching_records[3], event[1])
self.assertEqual(matching_records[4], EVENT_TYPE)
self.assertEqual(matching_records[5], SESSION_EVENT_TYPE)
self.assertEqual(matching_records[6], PID)
self.assertEqual(matching_records[7], REQUEST_ID)
self.assertEqual(matching_records[8], IDP_ENTITY_ID)
self.assertEqual(matching_records[9], TRANSACTION_ENTITY_ID)
self.assertEqual(matching_records[10], minimum_level_of_assurance)
self.assertEqual(matching_records[11], PROVIDED_LEVEL_OF_ASSURANCE)
self.assertEqual(matching_records[12],
PREFERRED_LEVEL_OF_ASSURANCE)
def __assert_events_exist_in_database(self, idp_fraud_events):
with RunInTransaction(self.db_connection) as cursor:
for event in idp_fraud_events:
cursor.execute(
"""
SELECT
a.id,
a.idp_entity_id,
a.idp_event_id,
a.time_stamp,
a.fid_code,
a.request_id,
a.pid,
a.client_ip_address,
a.contra_score,
a.upload_session_id,
b.source_file_name
FROM idp_data.idp_fraud_events a
INNER JOIN idp_data.upload_sessions b ON a.upload_session_id = b.id
WHERE a.idp_entity_id = %s
AND a.idp_event_id = %s
""", [event.idp_entity_id, event.idp_event_id])
result = cursor.fetchone()
self.assertIsNotNone(result)
self.assertIsNotNone(result[0])
self.assertEqual(
result[3].timestamp(),
dateparser.parse(
event.timestamp,
settings={
'TIMEZONE': idp_fraud_data_handler.DEFAULT_TIMEZONE
}).timestamp())
self.assertEqual(result[4], event.fid_code)
self.assertEqual(result[5], event.request_id)
self.assertEqual(result[6], event.pid)
self.assertEqual(result[7], event.client_ip_address)
self.assertEqual(result[8], event.contra_score)
self.assertIsNotNone(result[9])
self.assertEqual(result[10], UPLOAD_FILE_NAME)
if event.contra_indicators:
contra_indicators = list(set(event.contra_indicators))
contra_indicators.sort()
cursor.execute(
"""
SELECT
contraindicator_code,
count
FROM
idp_data.idp_fraud_event_contraindicators
WHERE idp_fraud_events_id = %s
ORDER BY contraindicator_code ASC
""", [result[0]])
for contra_indicator in contra_indicators:
contra_result = cursor.fetchone()
self.assertEqual(contra_result[0], contra_indicator)
expected_count = len([
c for c in event.contra_indicators
if c == contra_indicator
])
self.assertEqual(contra_result[1], expected_count)
def __clean_db(self):
with RunInTransaction(self.db_connection) as cursor:
cursor.execute("""
DELETE FROM events;
""")