def execute_non_query(sql, params, correlation_id=new_correlation_id()):
"""
Use this method to make changes that will be committed to the database (e.g. UPDATE, DELETE calls)
"""
logger = get_logger()
conn = _get_connection(correlation_id)
sql = minimise_white_space(sql)
param_str = str(params)
logger.info(
"postgres query",
extra={
"query": sql,
"parameters": param_str,
"correlation_id": correlation_id
},
)
with conn.cursor() as cursor:
try:
cursor.execute(sql, params)
rowcount = cursor.rowcount
conn.commit()
except psycopg2.IntegrityError as err:
errorjson = {
"error": err.args[0],
"correlation_id": str(correlation_id)
}
raise DetailedIntegrityError("Database integrity error", errorjson)
return rowcount
def __init__(self, survey_consent_event):
self.logger = survey_consent_event.get("logger", utils.get_logger())
self.correlation_id = survey_consent_event.get(
"correlation_id", utils.new_correlation_id())
self.consent_dict = json.loads(survey_consent_event["body"])
self.consent_info_url = self.consent_dict["consent_info_url"]
del self.consent_dict["consent_info_url"]
consent_embedded_data_fieldname = "consent_statements"
self.consent_dict[consent_embedded_data_fieldname] = json.loads(
self.consent_dict[consent_embedded_data_fieldname])
self.to_recipient_email = self.consent_dict.get("to_recipient_email")
try:
self.template_name = self.consent_dict["template_name"]
except KeyError:
self.template_name = DEFAULT_CONSENT_EMAIL_TEMPLATE
else:
del self.consent_dict["template_name"]
try:
self.consent_dict[
"consent_datetime"] = qualtrics2thiscovery_timestamp(
self.consent_dict["consent_datetime"])
except KeyError:
self.consent_dict["consent_datetime"] = str(utils.now_with_tz())
self.core_api_client = CoreApiClient(
correlation_id=self.correlation_id)
self.consent = Consent(core_api_client=self.core_api_client,
correlation_id=self.correlation_id)
self.consent.from_dict(consent_dict=self.consent_dict)
def views_interview_rescheduled_event_from_acuity_info(acuity_info):
return {
"version": "0",
"id": str(utils.new_correlation_id()),
"detail-type": "interview_rescheduled",
"detail": {
"anon_project_specific_user_id":
acuity_info["forms"][0]["values"][1]["value"],
"anon_user_task_id":
acuity_info["forms"][0]["values"][0]["value"],
"appointment_datetime":
acuity_info["datetime"],
"appointment_duration":
acuity_info["duration"],
"appointment_id":
acuity_info["id"],
"appointment_timezone":
acuity_info["timezone"],
"appointment_type":
acuity_info["type"],
"appointment_type_id":
acuity_info["appointmentTypeID"],
"calendar_id":
acuity_info["calendarID"],
"calendar_name":
acuity_info["calendar"],
"calendar_timezone":
acuity_info["calendarTimezone"],
"interview_id":
str(utils.new_correlation_id()),
"interview_room_url":
"https://www.video.thiscovery.org/",
"project_task_id":
"683598e8-435f-4052-a417-f0f6d808373a",
"room_phone_number":
"+44 (0)1111 111 111",
"room_phone_pin":
"11111111",
},
"source": "thiscovery_video",
"account": "REDACTED",
"region": "REDACTED",
"resources": [],
"time": "2021-02-12T10:57:09Z",
}
def execute_non_query_multiple(sql_iterable,
params_iterable,
correlation_id=new_correlation_id()):
"""
Args:
sql_iterable (tuple, list, etc): iterable containing sql queries to be executed
params_iterable (tuple, list, etc): iterable containing params for sql queries in sql_iterable
correlation_id:
Returns:
List of number of rows affected by each of the input sql queries
"""
logger = get_logger()
conn = _get_connection(correlation_id)
results = []
with conn.cursor() as cursor:
for (sql, params) in zip(sql_iterable, params_iterable):
sql = minimise_white_space(sql)
param_str = str(params)
logger.info(
"postgres query",
extra={
"query": sql,
"parameters": param_str,
"correlation_id": correlation_id,
},
)
try:
cursor.execute(sql, params)
except psycopg2.IntegrityError as err:
errorjson = {
"error": err.args[0],
"correlation_id": str(correlation_id),
}
raise DetailedIntegrityError("Database integrity error",
errorjson)
except Exception as ex:
raise ex
rowcount = cursor.rowcount
logger.info(
f"postgres query updated {rowcount} rows",
extra={
"query": sql,
"parameters": param_str,
"correlation_id": correlation_id,
},
)
results.append(rowcount)
conn.commit()
return results
def execute_query(
base_sql,
params=None,
correlation_id=new_correlation_id(),
return_json=True,
jsonize_sql=True,
):
"""
Use this method to query the database (e.g. using SELECT). Changes will not be committed to the database, so don't use this method for UPDATE and DELETE
calls.
Args:
base_sql:
params (tuple or list): http://initd.org/psycopg/docs/usage.html#passing-parameters-to-sql-queries
correlation_id:
return_json:
jsonize_sql:
Returns:
"""
logger = get_logger()
# tell sql to create json if that's what's wanted
if return_json and jsonize_sql:
sql = _jsonize_sql(base_sql)
else:
sql = base_sql
sql = minimise_white_space(sql)
param_str = str(params)
logger.info(
"postgres query",
extra={
"query": sql,
"parameters": param_str,
"correlation_id": correlation_id
},
)
conn = _get_connection(correlation_id)
with conn.cursor() as cursor:
cursor.execute(sql, params)
records = cursor.fetchall()
logger.info(
"postgres result",
extra={
"rows returned": str(len(records)),
"correlation_id": correlation_id
},
)
if return_json:
return _get_json_from_tuples(records)
else:
return records
def execute_query_multiple(
base_sql_tuple,
params_tuple=None,
correlation_id=new_correlation_id(),
return_json=True,
jsonize_sql=True,
):
"""
Use this method to query the database (e.g. using SELECT). Changes will not be committed to the database, so don't use this method for UPDATE and DELETE
calls.
"""
logger = get_logger()
conn = _get_connection(correlation_id)
if params_tuple is None:
params_tuple = tuple([None] * len(base_sql_tuple))
results = []
with conn.cursor() as cursor:
for (base_sql, params) in zip(base_sql_tuple, params_tuple):
# tell sql to create json if that's what's wanted
if return_json and jsonize_sql:
sql = _jsonize_sql(base_sql)
else:
sql = base_sql
sql = minimise_white_space(sql)
param_str = str(params)
logger.info(
"postgres query",
extra={
"query": sql,
"parameters": param_str,
"correlation_id": correlation_id,
},
)
cursor.execute(sql, params)
records = cursor.fetchall()
logger.info(
"postgres result",
extra={
"rows returned": str(len(records)),
"correlation_id": correlation_id,
},
)
if return_json:
results.append(_get_json_from_tuples(records))
else:
results.append(records)
logger.info("Returning multiple results", extra={"results": results})
return results
def execute_jsonpatch(
id_column,
id_to_update,
mappings,
patch_json,
modified_time,
correlation_id=new_correlation_id(),
):
"""
Args:
id_column:
id_to_update:
mappings:
patch_json:
modified_time:
correlation_id:
Returns:
Total number of rows updated in RDS database
"""
# todo - wrap in transaction if ever extended to multi table updates
updated_rows = 0
try:
tables_to_update, columns_to_update = create_updates_list_from_jsonpatch(
mappings, patch_json, correlation_id)
sql_updates = create_sql_from_updates_list(tables_to_update,
columns_to_update,
id_column, id_to_update,
modified_time)
for (sql_update, params) in sql_updates:
rowcount = execute_non_query(sql_update, params, correlation_id)
if rowcount == 0:
errorjson = {
"id_column": id_column,
"id_to_update": id_to_update,
"sql_update": sql_update,
"correlation_id": str(correlation_id),
}
raise ObjectDoesNotExistError("user does not exist", errorjson)
updated_rows += rowcount
return updated_rows
except Exception as ex:
# all exceptions will be dealt with by calling method
raise ex
def __init__(
self,
survey_id,
anon_project_specific_user_id,
account,
project_task_id,
correlation_id=None,
):
self.survey_id = survey_id
self.anon_project_specific_user_id = anon_project_specific_user_id
self.account = account
self.account_survey_id = f"{account}_{survey_id}"
self.correlation_id = correlation_id
if correlation_id is None:
self.correlation_id = utils.new_correlation_id()
self.ddb_client = Dynamodb(stack_name=const.STACK_NAME,
correlation_id=self.correlation_id)
self.project_task_id = project_task_id
def process_user_login(notification):
logger = get_logger()
correlation_id = new_correlation_id()
logger.info(
"Processing user login notification",
extra={
"notification": notification,
"correlation_id": correlation_id
},
)
posting_result = None
marking_result = None
try:
# get basic data out of notification
login_details = notification["details"]
hs_client = HubSpotClient(correlation_id=correlation_id,
stack_name=const.STACK_NAME)
posting_result = hs_client.post_user_login_to_crm(login_details)
logger.debug(
"Response from HubSpot API",
extra={
"posting_result": posting_result,
"correlation_id": correlation_id
},
)
if posting_result == http.HTTPStatus.NO_CONTENT:
marking_result = mark_notification_processed(
notification, correlation_id, stack_name=const.STACK_NAME)
except Exception as ex:
logger.debug("Traceback", extra={"traceback": traceback.format_exc()})
error_message = str(ex)
marking_result = mark_notification_failure(notification,
error_message,
correlation_id,
stack_name=const.STACK_NAME)
finally:
return posting_result, marking_result
def create_updates_list_from_jsonpatch(mappings,
jsonpatch,
correlation_id=new_correlation_id()):
tables_to_update = set()
columns_to_update = []
# process each attribute update and figure out where it belongs in database
for update in jsonpatch:
# remove leading '/' if present in jsonpatch
try:
if update["path"][0] == "/":
attribute = update["path"][1:]
else:
attribute = update["path"]
except KeyError:
errorjson = {
"update": update,
"correlation_id": str(correlation_id)
}
raise PatchInvalidJsonError("path not found in jsonpatch",
errorjson)
# get mapping for this particular attribute
if attribute not in mappings:
errorjson = {
"attribute": attribute,
"correlation_id": str(correlation_id)
}
raise PatchAttributeNotRecognisedError(
"Patch attribute not recognised", errorjson)
mapping = mappings[attribute]
try:
operation = update["op"]
except KeyError:
errorjson = {
"update": update,
"correlation_id": str(correlation_id)
}
raise PatchInvalidJsonError("op not found in jsonpatch", errorjson)
if operation != "replace":
errorjson = {
"operation": operation,
"correlation_id": str(correlation_id)
}
raise PatchOperationNotSupportedError(
"Patch operation not currently supported", errorjson)
try:
value = update["value"]
except KeyError:
errorjson = {
"update": update,
"correlation_id": str(correlation_id)
}
raise PatchInvalidJsonError("value not found in jsonpatch",
errorjson)
# add table to list that need to be updated
table_name = mapping["table_name"]
tables_to_update.add(table_name)
# add column to list of cols that need to be updated
column_name = mapping["column_name"]
columns_to_update.append({
"table_name": table_name,
"column_name": column_name,
"value": value
})
return tables_to_update, columns_to_update
def run_sql_script_file(sql_script_file, correlation_id=new_correlation_id()):
sql = get_file_as_string(sql_script_file)
execute_non_query(sql, None, correlation_id)
def test_20_patch_user_api_ok(self):
user_id = "d1070e81-557e-40eb-a7ba-b951ddb7ebdc"
expected_status = HTTPStatus.NO_CONTENT
user_jsonpatch = [
{"op": "replace", "path": "/title", "value": "Sir"},
{"op": "replace", "path": "/first_name", "value": "simon"},
{"op": "replace", "path": "/last_name", "value": "smith"},
{"op": "replace", "path": "/email", "value": "*****@*****.**"},
{"op": "replace", "path": "/auth0_id", "value": "new-auth0-id"},
{"op": "replace", "path": "/status", "value": "singing"},
{"op": "replace", "path": "/country_code", "value": "GB-SCT"},
]
body = json.dumps(user_jsonpatch)
path_parameters = {"id": user_id}
result = test_patch(
patch_user_api,
ENTITY_BASE_URL,
path_parameters=path_parameters,
request_body=body,
)
result_status = result["statusCode"]
self.assertEqual(expected_status, result_status)
# now check database values...
path_parameters = {"id": user_id}
expected_body = {
"id": user_id,
"created": f"2018-08-17T{tz_hour}:10:56.798192+{tz_offset}",
"email": "*****@*****.**",
"title": "Sir",
"first_name": "simon",
"last_name": "smith",
"auth0_id": "new-auth0-id",
"country_code": "GB-SCT",
"country_name": "United Kingdom - Scotland",
"crm_id": None,
"avatar_string": "ss",
"status": "singing",
}
result = test_get(
u.get_user_by_id_api, ENTITY_BASE_URL, path_parameters=path_parameters
)
result_json = json.loads(result["body"])
# will test modified separately so extract it from dictionary here
self.now_datetime_test_and_remove(
result_json, "modified", tolerance=TIME_TOLERANCE_SECONDS
)
# now check that we have a corresponding entity update record
entity_updates = EntityUpdate.get_entity_updates_for_entity(
"user", user_id, new_correlation_id()
)
self.assertTrue(len(entity_updates) > 0, "No entity update record found")
if len(entity_updates) > 0:
# get most recent update record
last_entity_update = entity_updates[-1]
# remove from returned value those things we don't want to test
self.remove_dict_items_to_be_ignored_by_tests(
last_entity_update, ["id", "modified"]
)
# remove and store data items to be tested individually
# check created datetime - allow up to TIME_TOLERANCE_SECONDS difference
self.now_datetime_test_and_remove(
last_entity_update, "created", tolerance=TIME_TOLERANCE_SECONDS
)
result_json_reverse_patch = last_entity_update["json_reverse_patch"]
del last_entity_update["json_reverse_patch"]
result_json_patch = last_entity_update["json_patch"]
del last_entity_update["json_patch"]
# check jsonpatch - compare as lists in case order different
result_json_patch = json.loads(result_json_patch)
self.assertCountEqual(user_jsonpatch, result_json_patch)
# need to compare list objects not strings as elements may be in different order
result_json_reverse_patch = json.loads(result_json_reverse_patch)
expected_json_reverse_patch = [
{"op": "replace", "path": "/first_name", "value": "Altha"},
{"op": "replace", "path": "/auth0_id", "value": None},
{"op": "replace", "path": "/title", "value": "Mrs"},
{"op": "replace", "path": "/last_name", "value": "Alcorn"},
{"op": "replace", "path": "/status", "value": None},
{"op": "replace", "path": "/email", "value": "*****@*****.**"},
{"op": "replace", "path": "/country_code", "value": "GB"},
]
self.assertCountEqual(
expected_json_reverse_patch, result_json_reverse_patch
)
# and finally check what's left
expected_body = {
"entity_name": "user",
"entity_id": user_id,
}
self.assertDictEqual(expected_body, last_entity_update)