def query_db(self, query, args=()):
'''
Simple queries to DB
:param query: SQL query
:param args: Arguments to inject into the query
:return: Single hit for the given query
'''
db = db_handler.get_db(host=self.host,
password=self.passwd,
user=self.user,
port=self.port,
database=self.db)
cursor = db.cursor()
cur = cursor.execute(query, args)
try:
rv = cursor.fetchone() # Returns tuple
debug_log.info(rv)
if rv is not None:
db.close()
return rv[1] # The second value in the tuple.
else:
return None
except Exception as e:
debug_log.exception(e)
debug_log.info(cur)
db.close()
return None
def storeCSR_JSON(self, DictionaryToStore):
"""
Store CSR into database
:param DictionaryToStore: Dictionary in form {"key" : "dict_to_store"}
:return: None
"""
cr_id = DictionaryToStore["cr_id"]
csr_id = DictionaryToStore["csr_id"]
consent_status = DictionaryToStore["consent_status"]
rs_id = DictionaryToStore["rs_id"]
surrogate_id = DictionaryToStore["surrogate_id"]
previous_record_id = DictionaryToStore["previous_record_id"]
slr_id = DictionaryToStore["slr_id"]
json = DictionaryToStore["json"]
db = db_handler.get_db(host=self.host, password=self.passwd, user=self.user, port=self.port, database=self.db)
cursor = db.cursor()
debug_log.info("Storing following csr structure:")
debug_log.info(DictionaryToStore)
# debug_log.info(key)
try:
cursor.execute("INSERT INTO csr_storage (cr_id, csr_id, previous_record_id, consent_status, surrogate_id, slr_id, rs_id, json) \
VALUES (%s, %s, %s, %s, %s, %s, %s, %s)",
[cr_id, csr_id, previous_record_id, consent_status, surrogate_id, slr_id, rs_id,
dumps(json)])
db.commit()
except IntegrityError as e:
# db.execute("UPDATE csr_storage SET json=? WHERE cr_id=? ;", [dumps(DictionaryToStore[key]), key])
# db.commit()
db.rollback()
raise DetailedHTTPException(detail={"msg": "Adding CSR to the database has failed.", },
title="Failure in CSR storage", exception=e)
def verifySurrogate(self, code, surrogate):
db = db_handler.get_db(self.db_path)
for code_row in self.query_db("select * from codes where ID = ? AND code = ?;", [code, surrogate]):
code_from_db = code_row["code"]
# TODO: Could we remove code and surrogate_id after this check to ensure they wont be abused later.
return True
return False
def storeRS_ID(self, rs_id):
db = db_handler.get_db(host=self.host, password=self.passwd, user=self.user, port=self.port, database=self.db)
cursor = db.cursor()
rs_id_status = False
cursor.execute("INSERT INTO rs_id_tbl (rs_id, used) \
VALUES (%s, %s)", (rs_id, rs_id_status))
db.commit()
debug_log.info("Stored RS_ID({}) into DB".format(rs_id))
cursor.close()
def store_service_key_json(self, kid, surrogate_id, key_json):
db = db_handler.get_db(host=self.host, password=self.passwd, user=self.user, port=self.port, database=self.db)
cursor = db.cursor()
cursor.execute("INSERT INTO service_keys_tbl (kid, surrogate_id, key_json) \
VALUES (%s, %s, %s);", (kid, surrogate_id, dumps(key_json)))
db.commit()
# cursor.execute("UPDATE service_keys_tbl SET key_json=%s WHERE kid=%s ;", (dumps(key_json), kid))
# db.commit()
debug_log.info("Stored key_json({}) for surrogate_id({}) into DB".format(key_json, surrogate_id))
cursor.close()
def storeRS_ID(self, rs_id):
db = db_handler.get_db(self.db_path)
try:
db_handler.init_db(db)
except OperationalError:
pass
rs_id_status = False
db.execute("INSERT INTO rs_id_tbl (rs_id, used) \
VALUES (?, ?)", [rs_id, rs_id_status])
db.commit()
db.close()
def add_surrogate_id_to_code(self, code, surrogate_id):
db = db_handler.get_db(self.db_path)
try:
db_handler.init_db(db)
except OperationalError:
pass
for code in self.query_db("select * from codes where code = ?;", [code]):
code_from_db = code["code"]
code_is_valid_and_unused = "!" in code_from_db
if (code_is_valid_and_unused):
db.execute("UPDATE codes SET code=? WHERE ID=? ;", [surrogate_id, code])
db.commit()
else:
raise Exception("Invalid code")
def storeSurrogateJSON(DictionaryToStore):
db = db_handler.get_db()
try:
db_handler.init_db(db)
except OperationalError:
pass
debug_log.info(DictionaryToStore)
for key in DictionaryToStore:
debug_log.info(key)
db.execute(
"INSERT INTO surrogate_and_user_mapping (user_id, surrogate_id) \
VALUES (?, ?)", [key, dumps(DictionaryToStore[key])])
db.commit()
def storeCode(self, code):
"""
Store generated code into database
:param code:
:return: None
"""
db = db_handler.get_db(host=self.host, password=self.passwd, user=self.user, port=self.port, database=self.db)
cursor = db.cursor()
code_key = list(code.keys())[0]
code_value = code[code_key]
cursor.execute("INSERT INTO codes (ID,code) \
VALUES (%s, %s)", (code_key, code_value))
db.commit()
debug_log.info("Storing code(key,value): {}, {}".format(code_key, code_value))
db.close()
def get_user_id_with_code(code):
db = db_handler.get_db()
for code_row in db_handler.query_db(
"select * from code_and_user_mapping where code = ?;", [code]):
user_from_db = code_row["user_id"]
return user_from_db
raise DetailedHTTPException(
status=500,
detail={
"msg": "Unable to link code to user_id in database",
"detail": {
"code": code
}
},
title="Failed to link code to user_id")
def storeCodeUser(DictionaryToStore):
# {"code": "user_id"}
db = db_handler.get_db()
try:
db_handler.init_db(db)
except OperationalError:
pass
debug_log.info(DictionaryToStore)
for key in DictionaryToStore:
debug_log.info(key)
db.execute(
"INSERT INTO code_and_user_mapping (code, user_id) \
VALUES (?, ?)", [key, dumps(DictionaryToStore[key])])
db.commit()
def storeJSON(self, DictionaryToStore):
db = db_handler.get_db(self.db_path)
try:
db_handler.init_db(db)
except OperationalError:
pass
debug_log.info(DictionaryToStore)
for key in DictionaryToStore:
debug_log.info(key)
try:
db.execute("INSERT INTO storage (surrogate_id,json) \
VALUES (?, ?)", [key, dumps(DictionaryToStore[key])])
db.commit()
except IntegrityError as e:
db.execute("UPDATE storage SET json=? WHERE surrogate_id=? ;", [dumps(DictionaryToStore[key]), key])
db.commit()
def storeCode(self, code):
db = db_handler.get_db(self.db_path)
try:
db_handler.init_db(db)
except OperationalError:
pass
code_key = list(code.keys())[0]
code_value = code[code_key]
db.execute("INSERT INTO codes (ID,code) \
VALUES (?, ?)", [code_key, code_value])
db.commit()
debug_log.info("{} {}".format(code_key, code_value))
for code in self.query_db("select * from codes where ID = ?;", [code_key]):
debug_log.info(code["code"])
db.close()
def storeSurrogateJSON(self, DictionaryToStore):
db = db_handler.get_db(host=self.host,
password=self.passwd,
user=self.user,
port=self.port,
database=self.db)
cursor = db.cursor()
debug_log.info(DictionaryToStore)
for key in DictionaryToStore:
debug_log.info(key)
cursor.execute(
"INSERT INTO surrogate_and_user_mapping (user_id, surrogate_id) \
VALUES (%s, %s)", [key, dumps(DictionaryToStore[key])])
db.commit()
db.close()
def store_ssr_JSON(self, json):
"""
Store SSR into database
:param record_id:
:param surrogate_id:
:param slr_id:
:param json:
:return:
"""
db = db_handler.get_db(host=self.host,
password=self.passwd,
user=self.user,
port=self.port,
database=self.db)
cursor = db.cursor()
decoded_payload = base_token_tool.decode_payload(
json["attributes"]["payload"])
record_id = decoded_payload["record_id"]
surrogate_id = decoded_payload["surrogate_id"]
slr_id = decoded_payload["slr_id"]
debug_log.info("Storing SSR '{}' momentarily.\n {}".format(
record_id, decoded_payload))
prev_id = decoded_payload["prev_record_id"]
if prev_id != "NULL":
debug_log.info(
"Verifying SSR chain is unbroken.\n Looking up previous record '{}'"
.format(prev_id))
prev_record = self.query_db(
"select record_id, json from ssr_storage where record_id = %s",
(prev_id, ))
if prev_record is None:
raise TypeError(
"Previous record_id is not found"
) # Todo We make this basic check but is it enough?
debug_log.info("Found record: \n{}".format(prev_record))
debug_log.info(
"Storing SSR '{}' belonging to surrogate_id '{}' with content:\n {}"
.format(record_id, surrogate_id, json))
cursor.execute(
"INSERT INTO ssr_storage (surrogate_id,json,record_id,slr_id,prev_record_id) \
VALUES (%s, %s, %s, %s, %s)",
(surrogate_id, dumps(json), record_id, slr_id, prev_id))
db.commit()
db.close()
def storeCodeUser(self, DictionaryToStore):
# {"code": "user_id"}
db = db_handler.get_db(host=self.host,
password=self.passwd,
user=self.user,
port=self.port,
database=self.db)
cursor = db.cursor()
debug_log.info(DictionaryToStore)
for key in DictionaryToStore:
debug_log.info(key)
cursor.execute(
"INSERT INTO code_and_user_mapping (code, user_id) \
VALUES (%s, %s)", (key, dumps(DictionaryToStore[key])))
db.commit()
db.close()
def change_rs_id_status(self, rs_id, status):
db = db_handler.get_db(self.db_path)
try:
db_handler.init_db(db)
except OperationalError:
pass
for rs_id_object in self.query_db("select * from rs_id_tbl where rs_id = ?;", [rs_id]):
rs_id_from_db = rs_id_object["rs_id"]
status_from_db = bool(rs_id_object["used"])
status_is_unused = status_from_db == False
if (status_is_unused):
db.execute("UPDATE rs_id_tbl SET used=? WHERE rs_id=? ;", [status, rs_id])
db.commit()
db.close()
return True
else:
db.close()
return False
def store_session(self, DictionaryToStore):
db = db_handler.get_db(host=self.host, password=self.passwd, user=self.user, port=self.port, database=self.db)
cursor = db.cursor()
debug_log.info(DictionaryToStore)
for key in DictionaryToStore:
debug_log.info(key)
try:
cursor.execute("INSERT INTO session_store (code,json) \
VALUES (%s, %s)", (key, dumps(DictionaryToStore[key])))
db.commit()
# db.close()
except IntegrityError as e:
cursor.execute("UPDATE session_store SET json=%s WHERE code=%s ;", (dumps(DictionaryToStore[key]), key))
db.commit()
# db.close()
db.close()
def change_rs_id_status(self, rs_id, status):
db = db_handler.get_db(host=self.host, password=self.passwd, user=self.user, port=self.port, database=self.db)
cursor = db.cursor()
query = cursor.execute("select * from rs_id_tbl where rs_id=%s;", (rs_id,))
result = cursor.fetchone()
rs_id = result[0]
used = result[1]
debug_log.info(result)
status_from_db = bool(used)
status_is_unused = status_from_db is False
if status_is_unused:
cursor.execute("UPDATE rs_id_tbl SET used=%s WHERE rs_id=%s ;", (status, rs_id))
db.commit()
cursor.close()
return True
else:
cursor.close()
return False
def storeToken(self, DictionaryToStore):
"""
Store token into database
:param DictionaryToStore: Dictionary in form {"key" : "dict_to_store"}
:return:
"""
db = db_handler.get_db(host=self.host, password=self.passwd, user=self.user, port=self.port, database=self.db)
cursor = db.cursor()
for key in DictionaryToStore:
try:
cursor.execute("INSERT INTO token_storage (cr_id,token) \
VALUES (%s, %s)", (key, dumps(DictionaryToStore[key])))
db.commit()
except IntegrityError as e: # Rewrite incase we get new token.
cursor.execute("UPDATE token_storage SET token=? WHERE cr_id=%s ;",
(dumps(DictionaryToStore[key]), key))
db.commit()
db.close()
def store_session(self, DictionaryToStore):
db = db_handler.get_db(self.db_path)
try:
db_handler.init_db(db)
except OperationalError:
pass
debug_log.info(DictionaryToStore)
for key in DictionaryToStore:
debug_log.info(key)
try:
db.execute("INSERT INTO session_store (code,json) \
VALUES (?, ?)", [key, dumps(DictionaryToStore[key])])
db.commit()
db.close()
except IntegrityError as e:
db.execute("UPDATE session_store SET json=? WHERE code=? ;", [dumps(DictionaryToStore[key]), key])
db.commit()
db.close()
def add_surrogate_id_to_code(self, code, surrogate_id):
"""
Link code with a surrogate_id
:param code:
:param surrogate_id:
:return: None
"""
db = db_handler.get_db(host=self.host, password=self.passwd, user=self.user, port=self.port, database=self.db)
cursor = db.cursor()
debug_log.info("Code we look up is {}".format(code))
code = self.query_db("select * from codes where ID = %s;", (code,))
debug_log.info("Result for query: {}".format(code))
code_from_db = code
code_is_valid_and_unused = "!" in code_from_db
if (code_is_valid_and_unused):
cursor.execute("UPDATE codes SET code=%s WHERE ID=%s ;", (surrogate_id, code))
db.commit()
db.close()
else:
raise Exception("Invalid code")
def store_slr_JSON(self, json, slr_id, surrogate_id):
"""
Store SLR into database
:param surrogate_id:
:param slr_id:
:param json:
:return:
"""
db = db_handler.get_db(host=self.host,
password=self.passwd,
user=self.user,
port=self.port,
database=self.db)
cursor = db.cursor()
debug_log.info(
"Storing SLR '{}' belonging to surrogate_id '{}' with content:\n {}"
.format(slr_id, surrogate_id, json))
cursor.execute(
"INSERT INTO storage (surrogate_id,json,slr_id) \
VALUES (%s, %s, %s)", (surrogate_id, dumps(json), slr_id))
db.commit()
db.close()
def storeJSON(DictionaryToStore):
db = db_handler.get_db()
try:
db_handler.init_db(db)
except OperationalError:
pass
debug_log.info(DictionaryToStore)
for key in DictionaryToStore:
debug_log.info(key)
# codes = {"jsons": {}}
# codes = {"jsons": {}}
try:
db.execute(
"INSERT INTO storage (ID,json) \
VALUES (?, ?)", [key, dumps(DictionaryToStore[key])])
db.commit()
except IntegrityError as e:
db.execute("UPDATE storage SET json=? WHERE ID=? ;",
[dumps(DictionaryToStore[key]), key])
db.commit()
def storeJSON(self, DictionaryToStore):
"""
Store SLR into database
:param DictionaryToStore: Dictionary in form {"key" : "dict_to_store"}
:return:
"""
db = db_handler.get_db(host=self.host, password=self.passwd, user=self.user, port=self.port, database=self.db)
cursor = db.cursor()
debug_log.info("Storing dictionary:")
debug_log.info(DictionaryToStore)
for key in DictionaryToStore:
debug_log.info("Storing key:")
debug_log.info(key)
try:
cursor.execute("INSERT INTO storage (surrogate_id,json) \
VALUES (%s, %s)", (key, dumps(DictionaryToStore[key])))
db.commit()
except IntegrityError as e:
cursor.execute("UPDATE storage SET json=%s WHERE surrogate_id=%s ;",
(dumps(DictionaryToStore[key]), key))
db.commit()
db.close()
def query_db_multiple(self, query, args=(), one=False):
'''
Simple queries to DB
:param query: SQL query
:param args: Arguments to inject into the query
:return: all hits for the given query
'''
db = db_handler.get_db(host=self.host, password=self.passwd, user=self.user, port=self.port, database=self.db)
cursor = db.cursor()
cur = cursor.execute(query, args)
if one:
try:
rv = cursor.fetchone() # Returns tuple
debug_log.info(rv)
if rv is not None:
db.close()
return rv # The second value in the tuple.
else:
return None
except Exception as e:
debug_log.exception(e)
debug_log.info(cur)
db.close()
return None
else:
try:
rv = cursor.fetchall() # Returns tuple
debug_log.info(rv)
if rv is not None:
db.close()
return rv # This should be list of tuples [(1,2,3), (3,4,5)...]
else:
return None
except Exception as e:
debug_log.exception(e)
debug_log.info(cur)
db.close()
return None
def get_user_id_with_code(self, code):
try:
db = db_handler.get_db(host=self.host,
password=self.passwd,
user=self.user,
port=self.port,
database=self.db)
query = self.query_db(
"select * from code_and_user_mapping where code=%s;", (code, ))
debug_log.info(query)
user_from_db = loads(query)
return user_from_db
except Exception as e:
debug_log.exception(e)
raise DetailedHTTPException(
status=500,
detail={
"msg": "Unable to link code to user_id in database",
"detail": {
"code": code
}
},
title="Failed to link code to user_id")
def storeSurrogateJSON(self, user_id, surrogate_id, operator_id):
db = db_handler.get_db(host=self.host,
password=self.passwd,
user=self.user,
port=self.port,
database=self.db)
cursor = db.cursor()
debug_log.info(
"Mapping surrogate_id '{}' with user_id '{}' for operator '{}'".
format(surrogate_id, user_id, operator_id))
try:
cursor.execute(
"INSERT INTO surrogate_and_user_mapping (user_id, surrogate_id, operator_id) \
VALUES (%s, %s, %s)", [user_id, surrogate_id, operator_id])
except Exception as e:
debug_log.exception(e)
debug_log.debug(
"Storing surrogate_id into user/surrogate mapping FAILED,"
" likely surrogate_id assigned already.")
db.commit()
db.close()
def storeCSR_JSON(self, DictionaryToStore):
cr_id = DictionaryToStore["cr_id"]
rs_id = DictionaryToStore["rs_id"]
surrogate_id = DictionaryToStore["surrogate_id"]
slr_id = DictionaryToStore["slr_id"]
json = DictionaryToStore["json"]
db = db_handler.get_db(self.db_path)
try:
db_handler.init_db(db)
except OperationalError:
pass
debug_log.info(DictionaryToStore)
# debug_log.info(key)
try:
db.execute("INSERT INTO csr_storage (cr_id, surrogate_id, slr_id, rs_id, json) \
VALUES (?, ?, ?, ?, ?)", [cr_id, surrogate_id, slr_id, rs_id, dumps(json)])
db.commit()
except IntegrityError as e:
# db.execute("UPDATE csr_storage SET json=? WHERE cr_id=? ;", [dumps(DictionaryToStore[key]), key])
# db.commit()
db.rollback()
raise DetailedHTTPException(detail={"msg": "Adding CSR to the database has failed.",},
title="Failure in CSR storage", exception=e)
def verifyCode(self, code):
db = db_handler.get_db(self.db_path)
for code_row in self.query_db("select * from codes where ID = ?;", [code]):
code_from_db = code_row["code"]
return True
return False
