def test_context_manager_multiple(self):
self.assertEqual(TRN._contexts_entered, 0)
with TRN:
self.assertEqual(TRN._contexts_entered, 1)
TRN.add("SELECT 42")
with TRN:
self.assertEqual(TRN._contexts_entered, 2)
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)
# We exited the second context, nothing should have been executed
self.assertEqual(TRN._contexts_entered, 1)
self.assertEqual(
TRN._connection.get_transaction_status(),
TRANSACTION_STATUS_IDLE)
self._assert_sql_equal([])
# We have exited the first context, everything should have been
# executed and committed
self.assertEqual(TRN._contexts_entered, 0)
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_multiple_2(self):
self.assertEqual(TRN._contexts_entered, 0)
def tester():
self.assertEqual(TRN._contexts_entered, 1)
with TRN:
self.assertEqual(TRN._contexts_entered, 2)
sql = """SELECT EXISTS(
SELECT * FROM ag.test_table WHERE int_column=%s)"""
TRN.add(sql, [2])
self.assertTrue(TRN.execute_fetchlast())
self.assertEqual(TRN._contexts_entered, 1)
with TRN:
self.assertEqual(TRN._contexts_entered, 1)
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)
tester()
self.assertEqual(TRN._contexts_entered, 1)
self._assert_sql_equal([])
self.assertEqual(TRN._contexts_entered, 0)
self._assert_sql_equal([('insert1', True, 1), ('insert2', True, 2),
('insert3', True, 3)])
self.assertEqual(
TRN._connection.get_transaction_status(),
TRANSACTION_STATUS_IDLE)
def get_survey_id(self, ag_login_id, participant_name):
"""Return the survey ID associated with a participant or None
Parameters
----------
ag_login_id : str
A valid login ID, that should be a test as a valid UUID
participant_name : str
A participant name
Returns
-------
str or None
The survey ID, or None if a survey ID cannot be found.
Raises
------
ValueError
Unknown ag_login_id or participant_name passed
"""
with TRN:
sql = """SELECT survey_id
FROM ag_login_surveys
WHERE ag_login_id=%s AND participant_name=%s"""
TRN.add(sql, [ag_login_id, participant_name])
survey_id = TRN.execute_fetchindex()
if not survey_id:
raise ValueError("No survey ID found!")
return survey_id[0][0]
def _triggers(self):
"""What other question-response combinations this question can trigger
Returns
-------
tuple
(other_question_id, [triggering indices to that question])
"""
with TRN:
sql = """SELECT triggered_question, display_index
FROM {0} sst
JOIN {1} sqr
ON sst.survey_question_id=sqr.survey_question_id
AND sqr.response=sst.triggering_response
WHERE sst.survey_question_id = %s
ORDER BY triggered_question
""".format(self._supplemental_survey_table,
self._question_response_table)
TRN.add(sql, [self.id])
trigger_list = TRN.execute_fetchindex()
results = defaultdict(list)
for question, index in trigger_list:
results[question].append(index)
if results:
return results
else:
return ()
def get_survey_ids(self, ag_login_id, participant_name):
"""Return the survey IDs associated with a participant or None
Parameters
----------
ag_login_id : str
A valid login ID, that should be a test as a valid UUID
participant_name : str
A participant name
Returns
-------
dict or None
The survey IDs keyed to the survey id,
or None if a survey ID cannot be found.
Raises
------
ValueError
Unknown ag_login_id or participant_name passed
"""
with TRN:
sql = """SELECT DISTINCT s.survey_id, als.survey_id
FROM ag.ag_login_surveys als
LEFT JOIN ag.survey_answers sa USING (survey_id)
LEFT JOIN ag.group_questions gq USING (survey_question_id)
LEFT JOIN ag.surveys s USING (survey_group)
WHERE ag_login_id=%s AND participant_name=%s"""
TRN.add(sql, [ag_login_id, participant_name])
survey_id = TRN.execute_fetchindex()
if not survey_id:
raise ValueError("No survey ID found!")
return dict(i for i in survey_id)
def ut_get_arbitrary_barcode(self, deposited=True):
""" Returns arbitrarily chosen barcode.
For unit testing only!
Parameters
----------
deposited : boolean
If true, pick a deposited barcode. Default = True
Returns
-------
str: barcode
Example: '000032951'
Raises
------
ValueError
If no barcodes can be found in the DB."""
with TRN:
sql = """SELECT barcode
FROM ag.ag_kit_barcodes
WHERE deposited=%s
LIMIT 1"""
TRN.add(sql, [deposited])
info = TRN.execute_fetchindex()
if not info:
raise ValueError('No barcodes found.')
return info[0][0]
def ut_get_ag_login_id_from_barcode(self, barcode):
""" Returns ag_login_id for a given barcode.
For unit testing only!
Parameters
----------
barcode : str
The barcode for which the ag_login_id should be retrieved.
Returns
-------
str: ag_login_id
Example: 'd8592c74-9694-2135-e040-8a80115d6401'
Raises
------
ValueError
If the given barcode can not be found in the DB.
"""
with TRN:
sql = """SELECT ag.ag_kit.ag_login_id
FROM ag.ag_kit_barcodes
JOIN ag.ag_kit USING (ag_kit_id)
WHERE ag.ag_kit_barcodes.barcode = %s"""
TRN.add(sql, [barcode])
info = TRN.execute_fetchindex()
if not info:
raise ValueError('Barcode "%s" not in DB' % barcode)
return info[0][0]
def test_context_manager_multiple(self):
self.assertEqual(TRN._contexts_entered, 0)
with TRN:
self.assertEqual(TRN._contexts_entered, 1)
TRN.add("SELECT 42")
with TRN:
self.assertEqual(TRN._contexts_entered, 2)
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)
# We exited the second context, nothing should have been executed
self.assertEqual(TRN._contexts_entered, 1)
self.assertEqual(TRN._connection.get_transaction_status(),
TRANSACTION_STATUS_IDLE)
self._assert_sql_equal([])
# We have exited the first context, everything should have been
# executed and committed
self.assertEqual(TRN._contexts_entered, 0)
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_multiple_2(self):
self.assertEqual(TRN._contexts_entered, 0)
def tester():
self.assertEqual(TRN._contexts_entered, 1)
with TRN:
self.assertEqual(TRN._contexts_entered, 2)
sql = """SELECT EXISTS(
SELECT * FROM ag.test_table WHERE int_column=%s)"""
TRN.add(sql, [2])
self.assertTrue(TRN.execute_fetchlast())
self.assertEqual(TRN._contexts_entered, 1)
with TRN:
self.assertEqual(TRN._contexts_entered, 1)
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)
tester()
self.assertEqual(TRN._contexts_entered, 1)
self._assert_sql_equal([])
self.assertEqual(TRN._contexts_entered, 0)
self._assert_sql_equal([('insert1', True, 1), ('insert2', True, 2),
('insert3', True, 3)])
self.assertEqual(TRN._connection.get_transaction_status(),
TRANSACTION_STATUS_IDLE)
def logParticipantSample(self, ag_login_id, barcode, sample_site,
environment_sampled, sample_date, sample_time,
participant_name, notes):
with TRN:
if sample_site is not None:
# Get survey id
sql = """SELECT survey_id
FROM ag_login_surveys
WHERE ag_login_id = %s AND participant_name = %s"""
TRN.add(sql, (ag_login_id, participant_name))
survey_id = TRN.execute_fetchindex()
if not survey_id:
raise ValueError("No survey ID for ag_login_id %s and "
"participant name %s" %
(ag_login_id, participant_name))
survey_id = survey_id[0][0]
else:
# otherwise, it is an environmental sample
survey_id = None
# Add barcode info
sql = """UPDATE ag_kit_barcodes
SET site_sampled = %s, environment_sampled = %s,
sample_date = %s, sample_time = %s,
participant_name = %s, notes = %s, survey_id = %s
WHERE barcode = %s"""
TRN.add(sql, [sample_site, environment_sampled, sample_date,
sample_time, participant_name, notes, survey_id,
barcode])
def get_vioscreen_status(self, survey_id):
"""Retrieves the vioscreen status for a survey_id
Parameters
----------
survey_id : str
The survey to get status for
Returns
-------
int
Vioscreen status
Raises
------
ValueError
survey_id passed is not in the database
"""
with TRN:
sql = """SELECT vioscreen_status
FROM ag.ag_login_surveys
WHERE survey_id = %s"""
TRN.add(sql, [survey_id])
status = TRN.execute_fetchindex()
if not status:
raise ValueError("Survey ID %s not in database" % survey_id)
return status[0][0]
def get_survey_ids(self, ag_login_id, participant_name):
"""Return the survey IDs associated with a participant or None
Parameters
----------
ag_login_id : str
A valid login ID, that should be a test as a valid UUID
participant_name : str
A participant name
Returns
-------
dict or None
The survey IDs keyed to the survey id,
or None if a survey ID cannot be found.
Raises
------
ValueError
Unknown ag_login_id or participant_name passed
"""
with TRN:
sql = """SELECT DISTINCT s.survey_id, als.survey_id
FROM ag.ag_login_surveys als
LEFT JOIN ag.survey_answers sa USING (survey_id)
LEFT JOIN ag.group_questions gq USING (survey_question_id)
LEFT JOIN ag.surveys s USING (survey_group)
WHERE ag_login_id=%s AND participant_name=%s"""
TRN.add(sql, [ag_login_id, participant_name])
survey_id = TRN.execute_fetchindex()
if not survey_id:
raise ValueError("No survey ID found!")
return dict(i for i in survey_id)
def get_login_info(self, ag_login_id):
"""Get kit registration information
Parameters
----------
ag_login_id : str
A valid login ID, that should be a test as a valid UUID
Returns
-------
list of dict
A list of registration information associated with a common login
ID.
Raises
------
ValueError
Unknown ag_login_id passed
"""
with TRN:
sql = """SELECT ag_login_id, email, name, address, city, state,
zip, country
FROM ag_login
WHERE ag_login_id = %s"""
TRN.add(sql, [ag_login_id])
info = TRN.execute_fetchindex()
if not info:
raise ValueError('ag_login_id not in database: %s' %
ag_login_id)
return [dict(row) for row in info]
def __init__(self, ID):
with TRN:
self.id = ID
n = self.american_name
sql = """SELECT gq.survey_question_id
FROM {0} sg
JOIN {1} gq ON sg.group_order = gq.survey_group
LEFT JOIN {2} sq USING (survey_question_id)
WHERE sg.group_order = %s AND sq.retired = FALSE
ORDER BY gq.display_index
""".format(self._group_table, self._group_questions_table,
self._questions_table)
TRN.add(sql, [self.id])
results = TRN.execute_fetchindex()
qs = [Question.factory(x[0], n) for x in results]
self.id_to_eid = {q.id: q.interface_element_ids for q in qs}
self.question_lookup = {q.id: q for q in qs}
self.questions = qs
self.supplemental_eids = set()
for q in qs:
for id_ in q.triggers:
triggered = self.question_lookup[id_]
triggered_eids = triggered.interface_element_ids
self.supplemental_eids.update(set(triggered_eids))
def verifyKit(self, supplied_kit_id):
"""Set the KIT_VERIFIED for the supplied_kit_id to 'y'"""
sql = """UPDATE AG_KIT
SET kit_verified='y'
WHERE supplied_kit_id=%s"""
with TRN:
TRN.add(sql, [supplied_kit_id])
def get_nonconsented_scanned_barcodes(self, kit_id):
"""Returns list of barcodes that have been scanned but not consented
Parameters
----------
kit_id : str
The supplied kit identifier to check for barcodes.
Returns
-------
list of str
The barcodes, if any, that have been scanned but not consented
"""
sql = """SELECT barcode
FROM ag_kit_barcodes
INNER JOIN ag_kit USING (ag_kit_id)
RIGHT JOIN ag_login USING (ag_login_id)
LEFT JOIN barcode USING (barcode)
FULL JOIN ag.source_barcodes_surveys USING (barcode)
WHERE ag.source_barcodes_surveys.survey_id IS NULL
AND scan_date IS NOT NULL
AND ag_login_id = %s"""
with TRN:
user = self.get_user_for_kit(kit_id)
TRN.add(sql, [user])
return TRN.execute_fetchflatten()
def ut_get_supplied_kit_id(self, ag_login_id):
""" Returns supplied_kit_id for a given ag_login_id.
For unit testing only!
Parameters
----------
ag_login_id : str
Existing ag_login_id.
Returns
-------
str
The supplied_kit_id for the given ag_login_id.
Example: 'DokBF'
Raises
------
ValueError
If ag_login_id is not in DB.
"""
with TRN:
sql = """SELECT supplied_kit_id
FROM ag.ag_kit
WHERE ag_login_id = %s"""
TRN.add(sql, [ag_login_id])
info = TRN.execute_fetchindex()
if not info:
raise ValueError('ag_login_id not in database: %s' %
ag_login_id)
return info[0][0]
def ut_get_email_from_ag_login_id(self, ag_login_id):
""" Returns email for a given ag_login_id.
For unit testing only!
Parameters
----------
ag_login_id : str
Existing ag_login_id.
Returns
-------
str: email
Example: 'xX/tEv7O+T@6Ri7C.)LO'
Raises
------
ValueError
If ag_login_id is not in DB.
"""
with TRN:
sql = """SELECT email
FROM ag.ag_login
WHERE ag_login_id=%s"""
TRN.add(sql, [ag_login_id])
info = TRN.execute_fetchindex()
if not info:
raise ValueError('No emails found.')
return info[0][0]
def get_vioscreen_status(self, survey_id):
"""Retrieves the vioscreen status for a survey_id
Parameters
----------
survey_id : str
The survey to get status for
Returns
-------
int
Vioscreen status
Raises
------
ValueError
survey_id passed is not in the database
"""
with TRN:
sql = """SELECT vioscreen_status
FROM ag.ag_login_surveys
WHERE survey_id = %s"""
TRN.add(sql, [survey_id])
status = TRN.execute_fetchindex()
if not status:
raise ValueError("Survey ID %s not in database" % survey_id)
return status[0][0]
def get_login_info(self, ag_login_id):
"""Get kit registration information
Parameters
----------
ag_login_id : str
A valid login ID, that should be a test as a valid UUID
Returns
-------
list of dict
A list of registration information associated with a common login
ID.
Raises
------
ValueError
Unknown ag_login_id passed
"""
with TRN:
sql = """SELECT ag_login_id, email, name, address, city, state,
zip, country
FROM ag_login
WHERE ag_login_id = %s"""
TRN.add(sql, [ag_login_id])
info = TRN.execute_fetchindex()
if not info:
raise ValueError('ag_login_id not in database: %s' %
ag_login_id)
return [dict(row) for row in info]
def authenticateWebAppUser(self, username, password):
""" Attempts to validate authenticate the supplied username/password
Attempt to authenticate the user against the list of users in
web_app_user table. If successful, a dict with user innformation is
returned. If not, the function returns False.
"""
with TRN:
sql = """SELECT cast(ag_login_id as varchar(100)) as ag_login_id,
email, name, address, city,
state, zip, country,kit_password
FROM ag_login
INNER JOIN ag_kit USING (ag_login_id)
WHERE supplied_kit_id = %s"""
TRN.add(sql, [username])
row = TRN.execute_fetchindex()
if not row:
return False
results = dict(row[0])
password = password.encode('utf-8')
if not bcrypt.checkpw(password, results['kit_password']):
return False
results['ag_login_id'] = str(results['ag_login_id'])
return results
def authenticateWebAppUser(self, username, password):
""" Attempts to validate authenticate the supplied username/password
Attempt to authenticate the user against the list of users in
web_app_user table. If successful, a dict with user innformation is
returned. If not, the function returns False.
"""
with TRN:
sql = """SELECT cast(ag_login_id as varchar(100)) as ag_login_id,
email, name, address, city,
state, zip, country,kit_password
FROM ag_login
INNER JOIN ag_kit USING (ag_login_id)
WHERE supplied_kit_id = %s"""
TRN.add(sql, [username])
row = TRN.execute_fetchindex()
if not row:
return False
results = dict(row[0])
if not bcrypt.verify(password, results['kit_password']):
return False
results['ag_login_id'] = str(results['ag_login_id'])
return results
def _triggers(self):
"""What other question-response combinations this question can trigger
Returns
-------
tuple
(other_question_id, [triggering indices to that question])
"""
with TRN:
sql = """SELECT triggered_question, display_index
FROM {0} sst
JOIN {1} sqr
ON sst.survey_question_id=sqr.survey_question_id
AND sqr.response=sst.triggering_response
WHERE sst.survey_question_id = %s
ORDER BY triggered_question
""".format(self._supplemental_survey_table,
self._question_response_table)
TRN.add(sql, [self.id])
trigger_list = TRN.execute_fetchindex()
results = defaultdict(list)
for question, index in trigger_list:
results[question].append(index)
if results:
return results
else:
return ()
def __init__(self, ID):
with TRN:
self.id = ID
n = self.american_name
sql = """SELECT gq.survey_question_id
FROM {0} sg
JOIN {1} gq ON sg.group_order = gq.survey_group
LEFT JOIN {2} sq USING (survey_question_id)
WHERE sg.group_order = %s AND sq.retired = FALSE
ORDER BY gq.display_index
""".format(self._group_table, self._group_questions_table,
self._questions_table)
TRN.add(sql, [self.id])
results = TRN.execute_fetchindex()
qs = [Question.factory(x[0], n) for x in results]
self.id_to_eid = {q.id: q.interface_element_ids for q in qs}
self.question_lookup = {q.id: q for q in qs}
self.questions = qs
self.supplemental_eids = set()
for q in qs:
for id_ in q.triggers:
triggered = self.question_lookup[id_]
triggered_eids = triggered.interface_element_ids
self.supplemental_eids.update(set(triggered_eids))
def ut_get_participant_names_from_ag_login_id(self, ag_login_id):
""" Returns all participant_name(s) for a given ag_login_id.
For unit testing only!
Parameters
----------
ag_login_id : str
Existing ag_login_id.
Returns
-------
[[str]]
Example: ["Name - z\xc3\x96DOZ8(Z~'",
"Name - z\xc3\x96DOZ8(Z~'",
'Name - QpeY\xc3\xb8u#0\xc3\xa5<',
'Name - S)#@G]xOdL',
'Name - Y5"^&sGQiW',
'Name - L\xc3\xa7+c\r\xc3\xa5?\r\xc2\xbf!',
'Name - (~|w:S\xc3\x85#L\xc3\x84']
Raises
------
ValueError
If ag_login_id is not in DB.
"""
with TRN:
sql = """SELECT participant_name
FROM ag.ag_login_surveys
WHERE ag_login_id = %s"""
TRN.add(sql, [ag_login_id])
info = TRN.execute_fetchindex()
if not info:
raise ValueError('ag_login_id not in database: %s' %
ag_login_id)
return [n[0] for n in info]
def verifyKit(self, supplied_kit_id):
"""Set the KIT_VERIFIED for the supplied_kit_id to 'y'"""
sql = """UPDATE AG_KIT
SET kit_verified='y'
WHERE supplied_kit_id=%s"""
with TRN:
TRN.add(sql, [supplied_kit_id])
def ut_get_arbitrary_supplied_kit_id_scanned_unconsented(self):
""" Returns arbitrarily chosen supplied_kit_id and barcode which has
been scanned but is without consent.
For unit testing only!
Returns
-------
list of str: [supplied_kit_id, barcode]
example: ['fNIYa', '000001053']
Raises
------
ValueError
If no kits can be found in the DB that have been scanned and
are without consent."""
with TRN:
sql = """SELECT supplied_kit_id, barcode
FROM barcodes.barcode
JOIN ag.ag_kit_barcodes USING (barcode)
JOIN ag.ag_kit USING (ag_kit_id)
LEFT JOIN ag.source_barcodes_surveys USING (barcode)
WHERE barcodes.barcode.scan_date IS NOT NULL
AND ag.source_barcodes_surveys.survey_id IS NULL
LIMIT 1"""
TRN.add(sql, [])
info = TRN.execute_fetchindex()
if not info:
raise ValueError('No kits found.')
return info[0]
def test_post_commit_funcs_error(self):
def func():
raise ValueError()
with self.assertRaises(RuntimeError):
with TRN:
TRN.add("SELECT 42")
TRN.add_post_commit_func(func)
def american_name(self):
"""Gets the locale-specific name of the group"""
with TRN:
sql = """SELECT american
FROM {0}
WHERE group_order = %s""".format(self._group_table)
TRN.add(sql, [self.id])
return TRN.execute_fetchlast()
def test_post_commit_funcs_error(self):
def func():
raise ValueError()
with self.assertRaises(RuntimeError):
with TRN:
TRN.add("SELECT 42")
TRN.add_post_commit_func(func)
def _get_unverified_kits(self):
"""Gets list of unverified kit IDs, Helper function for tests"""
sql = """SELECT supplied_kit_id
FROM AG_KIT
WHERE NOT kit_verified = 'y'"""
with TRN:
TRN.add(sql)
return TRN.execute_fetchflatten()
def american_name(self):
"""Gets the locale-specific name of the group"""
with TRN:
sql = """SELECT american
FROM {0}
WHERE group_order = %s""".format(self._group_table)
TRN.add(sql, [self.id])
return TRN.execute_fetchlast()
def _get_unverified_kits(self):
"""Gets list of unverified kit IDs, Helper function for tests"""
sql = """SELECT supplied_kit_id
FROM AG_KIT
WHERE NOT kit_verified = 'y'"""
with TRN:
TRN.add(sql)
return TRN.execute_fetchflatten()
def tester():
self.assertEqual(TRN._contexts_entered, 1)
with TRN:
self.assertEqual(TRN._contexts_entered, 2)
sql = """SELECT EXISTS(
SELECT * FROM ag.test_table WHERE int_column=%s)"""
TRN.add(sql, [2])
self.assertTrue(TRN.execute_fetchlast())
self.assertEqual(TRN._contexts_entered, 1)
def tester():
self.assertEqual(TRN._contexts_entered, 1)
with TRN:
self.assertEqual(TRN._contexts_entered, 2)
sql = """SELECT EXISTS(
SELECT * FROM ag.test_table WHERE int_column=%s)"""
TRN.add(sql, [2])
self.assertTrue(TRN.execute_fetchlast())
self.assertEqual(TRN._contexts_entered, 1)
def name(self):
"""Gets the locale-specific name of the group"""
with TRN:
sql = """SELECT {0}
FROM {1}
WHERE group_order = %s""".format(_LOCALE_COLUMN,
self._group_table)
TRN.add(sql, [self.id])
return TRN.execute_fetchlast()
def check_if_consent_exists(self, ag_login_id, participant_name):
"""Return True if a consent already exists"""
with TRN:
sql = """SELECT EXISTS(
SELECT 1
FROM ag_consent
WHERE ag_login_id = %s AND participant_name = %s)"""
TRN.add(sql, [ag_login_id, participant_name])
return TRN.execute_fetchlast()
def getAvailableBarcodes(self, ag_login_id):
sql = """SELECT barcode
FROM ag_kit_barcodes
INNER JOIN ag_kit USING (ag_kit_id)
WHERE coalesce(sample_date::text, '') = ''
AND kit_verified = 'y' AND ag_login_id = %s"""
with TRN:
TRN.add(sql, [ag_login_id])
return TRN.execute_fetchflatten()
def getAnimalParticipants(self, ag_login_id):
sql = """SELECT DISTINCT participant_name from ag.ag_login_surveys
JOIN ag.survey_answers USING (survey_id)
JOIN ag.group_questions gq USING (survey_question_id)
JOIN ag.surveys ags USING (survey_group)
WHERE ag_login_id = %s AND ags.survey_id = %s"""
with TRN:
TRN.add(sql, [ag_login_id, 2])
return TRN.execute_fetchflatten()
def check_if_consent_exists(self, ag_login_id, participant_name):
"""Return True if a consent already exists"""
with TRN:
sql = """SELECT EXISTS(
SELECT 1
FROM ag_consent
WHERE ag_login_id = %s AND participant_name = %s)"""
TRN.add(sql, [ag_login_id, participant_name])
return TRN.execute_fetchlast()
def getAvailableBarcodes(self, ag_login_id):
sql = """SELECT barcode
FROM ag_kit_barcodes
INNER JOIN ag_kit USING (ag_kit_id)
WHERE coalesce(sample_date::text, '') = ''
AND kit_verified = 'y' AND ag_login_id = %s"""
with TRN:
TRN.add(sql, [ag_login_id])
return TRN.execute_fetchflatten()
def name(self):
"""Gets the locale-specific name of the group"""
with TRN:
sql = """SELECT {0}
FROM {1}
WHERE group_order = %s""".format(_LOCALE_COLUMN,
self._group_table)
TRN.add(sql, [self.id])
return TRN.execute_fetchlast()
def getAnimalParticipants(self, ag_login_id):
sql = """SELECT participant_name from ag.ag_login_surveys
JOIN ag.survey_answers USING (survey_id)
JOIN ag.group_questions gq USING (survey_question_id)
JOIN ag.surveys ags USING (survey_group)
WHERE ag_login_id = %s AND ags.survey_id = %s"""
with TRN:
TRN.add(sql, [ag_login_id, 2])
return TRN.execute_fetchflatten()
def handoutCheck(self, username, password):
with TRN:
sql = "SELECT password FROM ag.ag_handout_kits WHERE kit_id = %s"
TRN.add(sql, [username])
to_check = TRN.execute_fetchindex()
if not to_check:
return False
else:
return bcrypt.verify(password, to_check[0][0])
def test_add_many(self):
with TRN:
self.assertEqual(TRN._queries, [])
sql = "INSERT INTO ag.test_table (int_column) VALUES (%s)"
args = [[1], [2], [3]]
TRN.add(sql, args, many=True)
exp = [(sql, [1]), (sql, [2]), (sql, [3])]
self.assertEqual(TRN._queries, exp)
def getHumanParticipants(self, ag_login_id):
# get people from new survey setup
sql = """SELECT DISTINCT participant_name from ag.ag_login_surveys
LEFT JOIN ag.survey_answers USING (survey_id)
JOIN ag.group_questions gq USING (survey_question_id)
JOIN ag.surveys ags USING (survey_group)
WHERE ag_login_id = %s AND ags.survey_id = %s"""
with TRN:
TRN.add(sql, [ag_login_id, 1])
return TRN.execute_fetchflatten()
def test_add_many(self):
with TRN:
self.assertEqual(TRN._queries, [])
sql = "INSERT INTO ag.test_table (int_column) VALUES (%s)"
args = [[1], [2], [3]]
TRN.add(sql, args, many=True)
exp = [(sql, [1]), (sql, [2]), (sql, [3])]
self.assertEqual(TRN._queries, exp)
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 getHumanParticipants(self, ag_login_id):
# get people from new survey setup
sql = """SELECT DISTINCT participant_name from ag.ag_login_surveys
LEFT JOIN ag.survey_answers USING (survey_id)
JOIN ag.group_questions gq USING (survey_question_id)
JOIN ag.surveys ags USING (survey_group)
WHERE ag_login_id = %s AND ags.survey_id = %s"""
with TRN:
TRN.add(sql, [ag_login_id, 1])
return TRN.execute_fetchflatten()
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 get_countries(self):
"""
Returns
-------
list of str
All country names in database"""
with TRN:
sql = 'SELECT country FROM ag.iso_country_lookup ORDER BY country'
TRN.add(sql)
return TRN.execute_fetchflatten()
def get_countries(self):
"""
Returns
-------
list of str
All country names in database"""
with TRN:
sql = 'SELECT country FROM ag.iso_country_lookup ORDER BY country'
TRN.add(sql)
return TRN.execute_fetchflatten()
def patch_number():
# Make sure the system is using the latest patch before starting up.
with TRN:
TRN.add('SELECT current_patch FROM ag.settings')
system = TRN.execute_fetchlast()
patches_dir = join(dirname(abspath(__file__)), '../db/patches')
latest = sorted(listdir(patches_dir)).pop()
if latest != system:
raise EnvironmentError(("Not running latest patch. System: %s "
"Latest: %s") % (system, latest))
def test_execute_fetchlast(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 = """SELECT EXISTS(
SELECT * FROM ag.test_table WHERE int_column=%s)"""
TRN.add(sql, [2])
self.assertTrue(TRN.execute_fetchlast())
def handoutCheck(self, username, password):
with TRN:
password = password.encode('utf-8')
sql = "SELECT password FROM ag.ag_handout_kits WHERE kit_id = %s"
TRN.add(sql, [username])
to_check = TRN.execute_fetchindex()
if not to_check:
return False
else:
return bcrypt.checkpw(password, to_check[0][0])
def getParticipantSamples(self, ag_login_id, participant_name):
sql = """SELECT barcode, site_sampled, sample_date, sample_time,
notes, status
FROM ag_kit_barcodes akb
INNER JOIN barcode USING (barcode)
INNER JOIN ag_kit ak USING (ag_kit_id)
WHERE (site_sampled IS NOT NULL AND site_sampled::text <> '')
AND ag_login_id = %s AND participant_name = %s"""
with TRN:
TRN.add(sql, [ag_login_id, participant_name])
rows = TRN.execute_fetchindex()
return [dict(row) for row in rows]
def ag_update_kit_password(self, kit_id, password):
"""updates ag_kit table with password
kit_id is supplied_kit_id in the ag_kit table
password is the new password
"""
with TRN:
password = bcrypt.encrypt(password)
sql = """UPDATE AG_KIT
SET kit_password = %s, pass_reset_code = NULL
WHERE supplied_kit_id = %s"""
TRN.add(sql, [password, kit_id])
def get_user_for_kit(self, supplied_kit_id):
with TRN:
sql = """SELECT ag_login_id
FROM ag.ag_kit
JOIN ag_login USING (ag_login_id)
WHERE supplied_kit_id = %s"""
TRN.add(sql, [supplied_kit_id])
results = TRN.execute_fetchindex()
if results:
return results[0][0]
else:
raise ValueError("No user ID for kit %s" % supplied_kit_id)
def getBarcodesByKit(self, kitid):
"""Returns a list of barcodes in a kit
kitid is the supplied_kit_id from the ag_kit table
"""
sql = """SELECT barcode
FROM ag_kit_barcodes
INNER JOIN ag_kit USING (ag_kit_id)
WHERE supplied_kit_id = %s"""
with TRN:
TRN.add(sql, [kitid])
return TRN.execute_fetchflatten()
