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; """)