def populate_test_db():
command = [
'pg_restore', '-d', AMGUT_CONFIG.database, '--no-privileges',
'--no-owner',
'--role=%s' % AMGUT_CONFIG.user, POPULATE_FP
]
proc = Popen(command, stdin=PIPE, stdout=PIPE)
retcode = proc.wait()
if retcode != 0:
raise RuntimeError("Could not populate test database %s: retcode %d" %
(AMGUT_CONFIG.database, retcode))
# Adds a new labadmin user names 'master' to the database and grants
# highest privileges to this user. Password is 'password'.
with TRN:
username = '******'
# create new labadmin user 'master'
sql = """INSERT INTO ag.labadmin_users (email, password)
VALUES (%s, %s)"""
TRN.add(sql, [
username,
('$2a$10$2.6Y9HmBqUFmSvKCjWmBte70WF.'
'zd3h4VqbhLMQK1xP67Aj3rei86')
])
# granting new user highest privileges
sql = """INSERT INTO ag.labadmin_users_access (access_id, email)
VALUES (%s, %s)"""
TRN.add(sql, [7, username])
TRN.execute()
def deleteAGParticipantSurvey(self, ag_login_id, participant_name):
# Remove user from new schema
with TRN:
sql = """SELECT survey_id, participant_email
FROM ag_login_surveys
JOIN ag_consent USING (ag_login_id, participant_name)
WHERE ag_login_id = %s AND participant_name = %s"""
TRN.add(sql, (ag_login_id, participant_name))
# collect all survey_ids and participant_names, since at least the
# former might be more than one.
survey_ids = set()
participant_emails = set()
for hit in TRN.execute_fetchindex():
survey_ids.add(hit[0])
participant_emails.add(hit[1])
sql = """SELECT barcode
FROM ag.source_barcodes_surveys
WHERE survey_id IN %s"""
TRN.add(sql, [tuple(survey_ids)])
barcodes = [x[0] for x in TRN.execute_fetchindex()]
sql = "DELETE FROM survey_answers WHERE survey_id IN %s"
TRN.add(sql, [tuple(survey_ids)])
sql = "DELETE FROM survey_answers_other WHERE survey_id IN %s"
TRN.add(sql, [tuple(survey_ids)])
# Reset survey attached to barcode(s)
for info in self.getParticipantSamples(ag_login_id,
participant_name):
self.deleteSample(info['barcode'], ag_login_id)
# Delete last due to foreign keys
sql = """DELETE FROM ag.source_barcodes_surveys
WHERE survey_id IN %s"""
TRN.add(sql, [tuple(survey_ids)])
# only delete barcode information, if this is the last survey for
# the given source, i.e. ag_login_id, participant_name combination
if len(survey_ids) == 1:
sql = """DELETE FROM ag.ag_kit_barcodes WHERE barcode IN %s"""
TRN.add(sql, [tuple(barcodes)])
sql = "DELETE FROM ag_login_surveys WHERE survey_id IN %s"
TRN.add(sql, [tuple(survey_ids)])
sql = """DELETE FROM ag_consent
WHERE ag_login_id = %s AND participant_name = %s"""
TRN.add(sql, [ag_login_id, participant_name])
sql = """INSERT INTO ag.consent_revoked
(ag_login_id,participant_name, participant_email)
VALUES (%s, %s, %s)"""
sql_args = [[ag_login_id, participant_name, pemail]
for pemail in participant_emails]
TRN.add(sql, sql_args, many=True)
TRN.execute()
def test_context_manager_no_commit(self):
with TRN:
sql = """INSERT INTO ag.test_table (str_column, int_column)
VALUES (%s, %s) RETURNING str_column, int_column"""
args = [['insert1', 1], ['insert2', 2], ['insert3', 3]]
TRN.add(sql, args, many=True)
TRN.execute()
self._assert_sql_equal([])
self._assert_sql_equal([('insert1', True, 1), ('insert2', True, 2),
('insert3', True, 3)])
self.assertEqual(TRN._connection.get_transaction_status(),
TRANSACTION_STATUS_IDLE)
def test_context_manager_no_commit(self):
with TRN:
sql = """INSERT INTO ag.test_table (str_column, int_column)
VALUES (%s, %s) RETURNING str_column, int_column"""
args = [['insert1', 1], ['insert2', 2], ['insert3', 3]]
TRN.add(sql, args, many=True)
TRN.execute()
self._assert_sql_equal([])
self._assert_sql_equal([('insert1', True, 1), ('insert2', True, 2),
('insert3', True, 3)])
self.assertEqual(
TRN._connection.get_transaction_status(),
TRANSACTION_STATUS_IDLE)
def test_context_manager_rollback(self):
try:
with TRN:
sql = """INSERT INTO ag.test_table (str_column, int_column)
VALUES (%s, %s) RETURNING str_column, int_column"""
args = [['insert1', 1], ['insert2', 2], ['insert3', 3]]
TRN.add(sql, args, many=True)
TRN.execute()
raise ValueError("Force exiting the context manager")
except ValueError:
pass
self._assert_sql_equal([])
self.assertEqual(TRN._connection.get_transaction_status(),
TRANSACTION_STATUS_IDLE)
def test_context_manager_rollback(self):
try:
with TRN:
sql = """INSERT INTO ag.test_table (str_column, int_column)
VALUES (%s, %s) RETURNING str_column, int_column"""
args = [['insert1', 1], ['insert2', 2], ['insert3', 3]]
TRN.add(sql, args, many=True)
TRN.execute()
raise ValueError("Force exiting the context manager")
except ValueError:
pass
self._assert_sql_equal([])
self.assertEqual(
TRN._connection.get_transaction_status(),
TRANSACTION_STATUS_IDLE)
def test_context_manager_checker(self):
with self.assertRaises(RuntimeError):
TRN.add("SELECT 42")
with self.assertRaises(RuntimeError):
TRN.execute()
with self.assertRaises(RuntimeError):
TRN.commit()
with self.assertRaises(RuntimeError):
TRN.rollback()
with TRN:
TRN.add("SELECT 42")
with self.assertRaises(RuntimeError):
TRN.execute()
def test_context_manager_checker(self):
with self.assertRaises(RuntimeError):
TRN.add("SELECT 42")
with self.assertRaises(RuntimeError):
TRN.execute()
with self.assertRaises(RuntimeError):
TRN.commit()
with self.assertRaises(RuntimeError):
TRN.rollback()
with TRN:
TRN.add("SELECT 42")
with self.assertRaises(RuntimeError):
TRN.execute()
def test_execute_return(self):
with TRN:
sql = """INSERT INTO ag.test_table (str_column, int_column)
VALUES (%s, %s) RETURNING str_column, int_column"""
TRN.add(sql, ['test_insert', 2])
sql = """UPDATE ag.test_table SET bool_column = %s
WHERE str_column = %s RETURNING int_column"""
TRN.add(sql, [False, 'test_insert'])
obs = TRN.execute()
self.assertEqual(obs, [[['test_insert', 2]], [[2]]])
def test_index(self):
with TRN:
self.assertEqual(TRN.index, 0)
TRN.add("SELECT 42")
self.assertEqual(TRN.index, 1)
sql = "INSERT INTO ag.test_table (int_column) VALUES (%s)"
args = [[1], [2], [3]]
TRN.add(sql, args, many=True)
self.assertEqual(TRN.index, 4)
TRN.execute()
self.assertEqual(TRN.index, 4)
TRN.add(sql, args, many=True)
self.assertEqual(TRN.index, 7)
self.assertEqual(TRN.index, 0)
def test_execute_return(self):
with TRN:
sql = """INSERT INTO ag.test_table (str_column, int_column)
VALUES (%s, %s) RETURNING str_column, int_column"""
TRN.add(sql, ['test_insert', 2])
sql = """UPDATE ag.test_table SET bool_column = %s
WHERE str_column = %s RETURNING int_column"""
TRN.add(sql, [False, 'test_insert'])
obs = TRN.execute()
self.assertEqual(obs, [[['test_insert', 2]], [[2]]])
def test_index(self):
with TRN:
self.assertEqual(TRN.index, 0)
TRN.add("SELECT 42")
self.assertEqual(TRN.index, 1)
sql = "INSERT INTO ag.test_table (int_column) VALUES (%s)"
args = [[1], [2], [3]]
TRN.add(sql, args, many=True)
self.assertEqual(TRN.index, 4)
TRN.execute()
self.assertEqual(TRN.index, 4)
TRN.add(sql, args, many=True)
self.assertEqual(TRN.index, 7)
self.assertEqual(TRN.index, 0)
def test_execute_huge_transaction(self):
with TRN:
# Add a lot of inserts to the transaction
sql = "INSERT INTO ag.test_table (int_column) VALUES (%s)"
for i in range(1000):
TRN.add(sql, [i])
# Add some updates to the transaction
sql = """UPDATE ag.test_table SET bool_column = %s
WHERE int_column = %s"""
for i in range(500):
TRN.add(sql, [False, i])
# Make the transaction fail with the last insert
sql = """INSERT INTO ag.table_to_make (the_trans_to_fail)
VALUES (1)"""
TRN.add(sql)
with self.assertRaises(ValueError):
TRN.execute()
# make sure rollback correctly
self._assert_sql_equal([])
def test_execute_huge_transaction(self):
with TRN:
# Add a lot of inserts to the transaction
sql = "INSERT INTO ag.test_table (int_column) VALUES (%s)"
for i in range(1000):
TRN.add(sql, [i])
# Add some updates to the transaction
sql = """UPDATE ag.test_table SET bool_column = %s
WHERE int_column = %s"""
for i in range(500):
TRN.add(sql, [False, i])
# Make the transaction fail with the last insert
sql = """INSERT INTO ag.table_to_make (the_trans_to_fail)
VALUES (1)"""
TRN.add(sql)
with self.assertRaises(ValueError):
TRN.execute()
# make sure rollback correctly
self._assert_sql_equal([])
def delete_survey(self, survey_id):
ag_login_id = None
participant_name = None
with TRN:
sql = """SELECT ag_login_id, participant_name
FROM ag.ag_login_surveys
WHERE survey_id = %s"""
TRN.add(sql, [survey_id])
[ag_login_id, participant_name] = TRN.execute_fetchindex()[0]
num_surveys = None
with TRN:
sql = """SELECT COUNT(*) FROM ag.ag_login_surveys
WHERE ag_login_id = %s AND participant_name = %s"""
TRN.add(sql, [ag_login_id, participant_name])
num_surveys = TRN.execute_fetchindex()[0][0]
with TRN:
# delete survey answers
sql = """DELETE FROM ag.survey_answers WHERE survey_id = %s"""
TRN.add(sql, [survey_id])
sql = """DELETE FROM ag.survey_answers_other
WHERE survey_id = %s"""
TRN.add(sql, [survey_id])
# delete source
sql = """DELETE FROM ag.ag_login_surveys WHERE survey_id = %s"""
TRN.add(sql, [survey_id])
TRN.execute()
# delete consent if this survey is the only one for this source
if num_surveys == 1:
with TRN:
sql = """DELETE FROM ag.ag_consent
WHERE ag_login_id = %s AND participant_name = %s"""
TRN.add(sql, [ag_login_id, participant_name])
TRN.execute()
def delete_survey(self, survey_id):
ag_login_id = None
participant_name = None
with TRN:
sql = """SELECT ag_login_id, participant_name
FROM ag.ag_login_surveys
WHERE survey_id = %s"""
TRN.add(sql, [survey_id])
[ag_login_id, participant_name] = TRN.execute_fetchindex()[0]
num_surveys = None
with TRN:
sql = """SELECT COUNT(*) FROM ag.ag_login_surveys
WHERE ag_login_id = %s AND participant_name = %s"""
TRN.add(sql, [ag_login_id, participant_name])
num_surveys = TRN.execute_fetchindex()[0][0]
with TRN:
# delete survey answers
sql = """DELETE FROM ag.survey_answers WHERE survey_id = %s"""
TRN.add(sql, [survey_id])
sql = """DELETE FROM ag.survey_answers_other
WHERE survey_id = %s"""
TRN.add(sql, [survey_id])
# delete source
sql = """DELETE FROM ag.ag_login_surveys WHERE survey_id = %s"""
TRN.add(sql, [survey_id])
TRN.execute()
# delete consent if this survey is the only one for this source
if num_surveys == 1:
with TRN:
sql = """DELETE FROM ag.ag_consent
WHERE ag_login_id = %s AND participant_name = %s"""
TRN.add(sql, [ag_login_id, participant_name])
TRN.execute()
def test_execute(self):
with TRN:
sql = """INSERT INTO ag.test_table (str_column, int_column)
VALUES (%s, %s)"""
TRN.add(sql, ["test_insert", 2])
sql = """UPDATE ag.test_table
SET int_column = %s, bool_column = %s
WHERE str_column = %s"""
TRN.add(sql, [20, False, "test_insert"])
obs = TRN.execute()
self.assertEqual(obs, [None, None])
self._assert_sql_equal([])
self._assert_sql_equal([("test_insert", False, 20)])
def test_execute(self):
with TRN:
sql = """INSERT INTO ag.test_table (str_column, int_column)
VALUES (%s, %s)"""
TRN.add(sql, ["test_insert", 2])
sql = """UPDATE ag.test_table
SET int_column = %s, bool_column = %s
WHERE str_column = %s"""
TRN.add(sql, [20, False, "test_insert"])
obs = TRN.execute()
self.assertEqual(obs, [None, None])
self._assert_sql_equal([])
self._assert_sql_equal([("test_insert", False, 20)])
def test_execute_commit_false_wipe_queries(self):
with TRN:
sql = """INSERT INTO ag.test_table (str_column, int_column)
VALUES (%s, %s) RETURNING str_column, int_column"""
args = [['insert1', 1], ['insert2', 2], ['insert3', 3]]
TRN.add(sql, args, many=True)
obs = TRN.execute()
exp = [[['insert1', 1]], [['insert2', 2]], [['insert3', 3]]]
self.assertEqual(obs, exp)
self._assert_sql_equal([])
sql = """UPDATE ag.test_table SET bool_column = %s
WHERE str_column = %s"""
args = [False, 'insert2']
TRN.add(sql, args)
self.assertEqual(TRN._queries, [(sql, args)])
TRN.execute()
self._assert_sql_equal([])
self._assert_sql_equal([('insert1', True, 1), ('insert3', True, 3),
('insert2', False, 2)])
def test_execute_commit_false_wipe_queries(self):
with TRN:
sql = """INSERT INTO ag.test_table (str_column, int_column)
VALUES (%s, %s) RETURNING str_column, int_column"""
args = [['insert1', 1], ['insert2', 2], ['insert3', 3]]
TRN.add(sql, args, many=True)
obs = TRN.execute()
exp = [[['insert1', 1]], [['insert2', 2]], [['insert3', 3]]]
self.assertEqual(obs, exp)
self._assert_sql_equal([])
sql = """UPDATE ag.test_table SET bool_column = %s
WHERE str_column = %s"""
args = [False, 'insert2']
TRN.add(sql, args)
self.assertEqual(TRN._queries, [(sql, args)])
TRN.execute()
self._assert_sql_equal([])
self._assert_sql_equal([('insert1', True, 1), ('insert3', True, 3),
('insert2', False, 2)])
def populate_test_db():
command = ['pg_restore', '-d', AMGUT_CONFIG.database, '--no-privileges',
'--no-owner', '--role=%s' % AMGUT_CONFIG.user, POPULATE_FP]
proc = Popen(command, stdin=PIPE, stdout=PIPE)
retcode = proc.wait()
if retcode != 0:
raise RuntimeError("Could not populate test database %s: retcode %d" %
(AMGUT_CONFIG.database, retcode))
# Adds a new labadmin user names 'master' to the database and grants
# highest privileges to this user. Password is 'password'.
with TRN:
username = '******'
# create new labadmin user 'master'
sql = """INSERT INTO ag.labadmin_users (email, password)
VALUES (%s, %s)"""
TRN.add(sql, [username, ('$2a$10$2.6Y9HmBqUFmSvKCjWmBte70WF.'
'zd3h4VqbhLMQK1xP67Aj3rei86')])
# granting new user highest privileges
sql = """INSERT INTO ag.labadmin_users_access (access_id, email)
VALUES (%s, %s)"""
TRN.add(sql, [7, username])
TRN.execute()
def test_execute_commit_false_rollback(self):
with TRN:
sql = """INSERT INTO ag.test_table (str_column, int_column)
VALUES (%s, %s) RETURNING str_column, int_column"""
args = [['insert1', 1], ['insert2', 2], ['insert3', 3]]
TRN.add(sql, args, many=True)
obs = TRN.execute()
exp = [[['insert1', 1]], [['insert2', 2]], [['insert3', 3]]]
self.assertEqual(obs, exp)
self._assert_sql_equal([])
TRN.rollback()
self._assert_sql_equal([])
def test_execute_commit_false_rollback(self):
with TRN:
sql = """INSERT INTO ag.test_table (str_column, int_column)
VALUES (%s, %s) RETURNING str_column, int_column"""
args = [['insert1', 1], ['insert2', 2], ['insert3', 3]]
TRN.add(sql, args, many=True)
obs = TRN.execute()
exp = [[['insert1', 1]], [['insert2', 2]], [['insert3', 3]]]
self.assertEqual(obs, exp)
self._assert_sql_equal([])
TRN.rollback()
self._assert_sql_equal([])
def test_execute_many(self):
with TRN:
sql = """INSERT INTO ag.test_table (str_column, int_column)
VALUES (%s, %s)"""
args = [['insert1', 1], ['insert2', 2], ['insert3', 3]]
TRN.add(sql, args, many=True)
sql = """UPDATE ag.test_table
SET int_column = %s, bool_column = %s
WHERE str_column = %s"""
TRN.add(sql, [20, False, 'insert2'])
obs = TRN.execute()
self.assertEqual(obs, [None, None, None, None])
self._assert_sql_equal([])
self._assert_sql_equal([('insert1', True, 1), ('insert3', True, 3),
('insert2', False, 20)])
def test_execute_many(self):
with TRN:
sql = """INSERT INTO ag.test_table (str_column, int_column)
VALUES (%s, %s)"""
args = [['insert1', 1], ['insert2', 2], ['insert3', 3]]
TRN.add(sql, args, many=True)
sql = """UPDATE ag.test_table
SET int_column = %s, bool_column = %s
WHERE str_column = %s"""
TRN.add(sql, [20, False, 'insert2'])
obs = TRN.execute()
self.assertEqual(obs, [None, None, None, None])
self._assert_sql_equal([])
self._assert_sql_equal([('insert1', True, 1),
('insert3', True, 3),
('insert2', False, 20)])
def test_execute_return_many(self):
with TRN:
sql = """INSERT INTO ag.test_table (str_column, int_column)
VALUES (%s, %s) RETURNING str_column, int_column"""
args = [['insert1', 1], ['insert2', 2], ['insert3', 3]]
TRN.add(sql, args, many=True)
sql = """UPDATE ag.test_table SET bool_column = %s
WHERE str_column = %s"""
TRN.add(sql, [False, 'insert2'])
sql = "SELECT * FROM ag.test_table"
TRN.add(sql)
obs = TRN.execute()
exp = [[['insert1', 1]], # First query of the many query
[['insert2', 2]], # Second query of the many query
[['insert3', 3]], # Third query of the many query
None, # Update query
[['insert1', True, 1], # First result select
['insert3', True, 3], # Second result select
['insert2', False, 2]]] # Third result select
self.assertEqual(obs, exp)
def test_execute_return_many(self):
with TRN:
sql = """INSERT INTO ag.test_table (str_column, int_column)
VALUES (%s, %s) RETURNING str_column, int_column"""
args = [['insert1', 1], ['insert2', 2], ['insert3', 3]]
TRN.add(sql, args, many=True)
sql = """UPDATE ag.test_table SET bool_column = %s
WHERE str_column = %s"""
TRN.add(sql, [False, 'insert2'])
sql = "SELECT * FROM ag.test_table"
TRN.add(sql)
obs = TRN.execute()
exp = [
[['insert1', 1]], # First query of the many query
[['insert2', 2]], # Second query of the many query
[['insert3', 3]], # Third query of the many query
None, # Update query
[
['insert1', True, 1], # First result select
['insert3', True, 3], # Second result select
['insert2', False, 2]
]
] # Third result select
self.assertEqual(obs, exp)
def registerHandoutKit(self, ag_login_id, supplied_kit_id):
"""Registeres a handout kit to a user
Parameters
----------
ag_login_id : str
UUID4 formatted string of login ID to associate with kit
supplied_kit_id : str
kit ID for the handout kit
Returns
-------
bool
True: success
False: insert failed due to IntegrityError
Raises
------
ValueError
Non-UUID4 value sent as ag_login_id
"""
with TRN:
# make sure properly formatted UUID passed in
UUID(ag_login_id, version=4)
printresults = self.checkPrintResults(supplied_kit_id)
# make sure login_id and skid exists
sql = """SELECT EXISTS(SELECT *
FROM ag.ag_login
WHERE ag_login_id = %s)"""
TRN.add(sql, [ag_login_id])
exists = TRN.execute_fetchlast()
if not exists:
return False
sql = """SELECT EXISTS(SELECT *
FROM ag.ag_handout_kits
WHERE kit_id = %s)"""
TRN.add(sql, [supplied_kit_id])
if not TRN.execute_fetchlast():
return False
sql = """
DO $do$
DECLARE
k_id uuid;
bc varchar;
BEGIN
INSERT INTO ag_kit
(ag_login_id, supplied_kit_id, kit_password, swabs_per_kit,
kit_verification_code, print_results)
SELECT '{0}', kit_id, password, swabs_per_kit,
verification_code, '{1}'
FROM ag_handout_kits WHERE kit_id = %s LIMIT 1
RETURNING ag_kit_id INTO k_id;
FOR bc IN
SELECT barcode
FROM ag_handout_barcodes
WHERE kit_id = %s
LOOP
INSERT INTO ag_kit_barcodes
(ag_kit_id, barcode, sample_barcode_file)
VALUES (k_id, bc, bc || '.jpg');
END LOOP;
DELETE FROM ag_handout_barcodes WHERE kit_id = %s;
DELETE FROM ag_handout_kits WHERE kit_id = %s;
END $do$;
""".format(ag_login_id, printresults)
TRN.add(sql, [supplied_kit_id] * 4)
try:
TRN.execute()
except psycopg2.IntegrityError:
logging.exception('Error on skid %s:' % ag_login_id)
return False
return True
def store_survey(self, consent_details, with_fk_inserts,
without_fk_inserts):
"""Store a survey
Parameters
----------
consent_details : dict
Participant consent details
with_fk_inserts : list
[(str, int, str)] where str is the survey_id, int is a
survey_question.survey_question_id and str is a
survey_response.american
without_fk_inserts : list
[(str, int, str)] where str is the survey_id, int is a
survey_question.survey_question_id and str is a json representation
of the data to insert
"""
with TRN:
TRN.add(
"""SELECT EXISTS(
SELECT 1
FROM ag_login_surveys
WHERE survey_id=%s)""", [consent_details['survey_id']])
if TRN.execute_fetchlast():
# if the survey exists, remove all its current answers
TRN.add(
"""DELETE FROM survey_answers
WHERE survey_id=%s""",
[consent_details['survey_id']])
TRN.add(
"""DELETE FROM survey_answers_other
WHERE survey_id=%s""",
[consent_details['survey_id']])
else:
# otherwise, we have a new survey
# If this is a primary survey, we need to add the consent
if 'secondary' not in consent_details:
TRN.add(
"""INSERT INTO ag_consent
(ag_login_id, participant_name, is_juvenile,
parent_1_name, parent_2_name,
deceased_parent, participant_email,
assent_obtainer, age_range, date_signed)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s,
NOW())""",
(consent_details['login_id'],
consent_details['participant_name'],
consent_details['is_juvenile'],
consent_details['parent_1_name'],
consent_details['parent_2_name'],
consent_details['deceased_parent'],
consent_details['participant_email'],
consent_details['obtainer_name'],
consent_details['age_range']))
TRN.add(
"""INSERT INTO ag_login_surveys
(ag_login_id, survey_id, participant_name)
VALUES (%s, %s, %s)""",
(consent_details['login_id'], consent_details['survey_id'],
consent_details['participant_name']))
# checks if user has previously been
# removed and has still revoked consent
sql = """SELECT ag_login_id FROM ag.consent_revoked"""
TRN.add(sql)
revoked = {result[0] for result in TRN.execute_fetchindex()}
# removes the user from the consent_revoked
# table if they are already in it
if consent_details['login_id'] in revoked:
sql = """DELETE FROM ag.consent_revoked
WHERE ag_login_id = %s
AND participant_name = %s
AND participant_email = %s"""
TRN.add(sql, [
consent_details['login_id'],
consent_details['participant_name'],
consent_details['participant_email']
])
TRN.execute()
# now we insert the answers
TRN.add("""INSERT INTO survey_answers
(survey_id, survey_question_id, response)
VALUES (%s, %s, %s)""",
with_fk_inserts,
many=True)
TRN.add("""INSERT INTO survey_answers_other
(survey_id,survey_question_id, response)
VALUES (%s, %s, %s)""",
without_fk_inserts,
many=True)
def registerHandoutKit(self, ag_login_id, supplied_kit_id):
"""Registeres a handout kit to a user
Parameters
----------
ag_login_id : str
UUID4 formatted string of login ID to associate with kit
supplied_kit_id : str
kit ID for the handout kit
Returns
-------
bool
True: success
False: insert failed due to IntegrityError
Raises
------
ValueError
Non-UUID4 value sent as ag_login_id
"""
with TRN:
# make sure properly formatted UUID passed in
UUID(ag_login_id, version=4)
printresults = self.checkPrintResults(supplied_kit_id)
# make sure login_id and skid exists
sql = """SELECT EXISTS(SELECT *
FROM ag.ag_login
WHERE ag_login_id = %s)"""
TRN.add(sql, [ag_login_id])
exists = TRN.execute_fetchlast()
if not exists:
return False
sql = """SELECT EXISTS(SELECT *
FROM ag.ag_handout_kits
WHERE kit_id = %s)"""
TRN.add(sql, [supplied_kit_id])
if not TRN.execute_fetchlast():
return False
sql = """
DO $do$
DECLARE
k_id uuid;
bc varchar;
BEGIN
INSERT INTO ag_kit
(ag_login_id, supplied_kit_id, kit_password, swabs_per_kit,
kit_verification_code, print_results)
SELECT '{0}', kit_id, password, swabs_per_kit,
verification_code, '{1}'
FROM ag_handout_kits WHERE kit_id = %s LIMIT 1
RETURNING ag_kit_id INTO k_id;
FOR bc IN
SELECT barcode
FROM ag_handout_barcodes
WHERE kit_id = %s
LOOP
INSERT INTO ag_kit_barcodes
(ag_kit_id, barcode, sample_barcode_file)
VALUES (k_id, bc, bc || '.jpg');
END LOOP;
DELETE FROM ag_handout_barcodes WHERE kit_id = %s;
DELETE FROM ag_handout_kits WHERE kit_id = %s;
END $do$;
""".format(ag_login_id, printresults)
TRN.add(sql, [supplied_kit_id] * 4)
try:
TRN.execute()
except psycopg2.IntegrityError:
logging.exception('Error on skid %s:' % ag_login_id)
return False
return True
def store_survey(self, consent_details, with_fk_inserts,
without_fk_inserts):
"""Store a survey
Parameters
----------
consent_details : dict
Participant consent details
with_fk_inserts : list
[(str, int, str)] where str is the survey_id, int is a
survey_question.survey_question_id and str is a
survey_response.american
without_fk_inserts : list
[(str, int, str)] where str is the survey_id, int is a
survey_question.survey_question_id and str is a json representation
of the data to insert
"""
with TRN:
TRN.add("""SELECT EXISTS(
SELECT 1
FROM ag_login_surveys
WHERE survey_id=%s)""",
[consent_details['survey_id']])
if TRN.execute_fetchlast():
# if the survey exists, remove all its current answers
TRN.add("""DELETE FROM survey_answers
WHERE survey_id=%s""",
[consent_details['survey_id']])
TRN.add("""DELETE FROM survey_answers_other
WHERE survey_id=%s""",
[consent_details['survey_id']])
else:
# otherwise, we have a new survey
# If this is a primary survey, we need to add the consent
if 'secondary' not in consent_details:
TRN.add("""INSERT INTO ag_consent
(ag_login_id, participant_name, is_juvenile,
parent_1_name, parent_2_name,
deceased_parent, participant_email,
assent_obtainer, age_range, date_signed)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s,
NOW())""",
(consent_details['login_id'],
consent_details['participant_name'],
consent_details['is_juvenile'],
consent_details['parent_1_name'],
consent_details['parent_2_name'],
consent_details['deceased_parent'],
consent_details['participant_email'],
consent_details['obtainer_name'],
consent_details['age_range']))
TRN.add("""INSERT INTO ag_login_surveys
(ag_login_id, survey_id, participant_name)
VALUES (%s, %s, %s)""",
(consent_details['login_id'],
consent_details['survey_id'],
consent_details['participant_name']))
# checks if user has previously been
# removed and has still revoked consent
sql = """SELECT ag_login_id FROM ag.consent_revoked"""
TRN.add(sql)
revoked = {result[0] for result in TRN.execute_fetchindex()}
# removes the user from the consent_revoked
# table if they are already in it
if consent_details['login_id'] in revoked:
sql = """DELETE FROM ag.consent_revoked
WHERE ag_login_id = %s
AND participant_name = %s
AND participant_email = %s"""
TRN.add(sql, [consent_details['login_id'],
consent_details['participant_name'],
consent_details['participant_email']])
TRN.execute()
# now we insert the answers
TRN.add("""INSERT INTO survey_answers
(survey_id, survey_question_id, response)
VALUES (%s, %s, %s)""", with_fk_inserts, many=True)
TRN.add("""INSERT INTO survey_answers_other
(survey_id,survey_question_id, response)
VALUES (%s, %s, %s)""", without_fk_inserts, many=True)
