def is_deposited_ebi(self, barcode): """Check if barcode is deposited to EBI Parameters ---------- barcode : str Barcode to check Returns ------- bool If the barcode has been deposited (True) or has not (False) Raises ------ ValueError Barcode is not a registered AG barcodes """ with TRN: sql = """SELECT EXISTS( SELECT 1 FROM ag.ag_kit_barcodes WHERE barcode = %s)""" TRN.add(sql, [barcode]) if not TRN.execute_fetchlast(): raise ValueError('Barcode %s not a registered AG barcode' % barcode) sql = "SELECT deposited FROM ag.ag_kit_barcodes WHERE barcode = %s" TRN.add(sql, [barcode]) return TRN.execute_fetchlast()
def __init__(self, ID, group_name): with TRN: self.id = ID self.group_name = group_name self.set_response = None sql = """SELECT sr.{0} FROM {1} q JOIN {2} qr ON q.survey_question_id = qr.survey_question_id JOIN {3} sr ON qr.response = sr.{0} WHERE q.survey_question_id = %s ORDER BY qr.display_index """.format(_LOCALE_COLUMN, self._survey_question_table, self._question_response_table, self._response_table) TRN.add(sql, [self.id]) self.responses = TRN.execute_fetchflatten() if not self.responses: self.responses = None sql = """SELECT survey_response_type FROM {0} WHERE survey_question_id = %s """.format(self._response_type_table) TRN.add(sql, [self.id]) self.response_type = TRN.execute_fetchlast() sql = """SELECT {0} FROM {1} WHERE survey_question_id = %s """.format(_LOCALE_COLUMN, self._survey_question_table) TRN.add(sql, [self.id]) self.question = TRN.execute_fetchlast() sql = """SELECT american FROM {0} WHERE survey_question_id = %s """.format(self._survey_question_table) TRN.add(sql, [self.id]) self.american_question = TRN.execute_fetchlast() self.triggers = self._triggers() self.qid = '_'.join(self.group_name.split() + [str(self.id)]) element_ids, elements = self._interface_elements() self.interface_elements = elements self.interface_element_ids = [ '%s_%d' % (self.qid, i) for i in element_ids ]
def __init__(self, ID, group_name): with TRN: self.id = ID self.group_name = group_name self.set_response = None sql = """SELECT sr.{0} FROM {1} q JOIN {2} qr ON q.survey_question_id = qr.survey_question_id JOIN {3} sr ON qr.response = sr.{0} WHERE q.survey_question_id = %s ORDER BY qr.display_index """.format(_LOCALE_COLUMN, self._survey_question_table, self._question_response_table, self._response_table) TRN.add(sql, [self.id]) self.responses = TRN.execute_fetchflatten() if not self.responses: self.responses = None sql = """SELECT survey_response_type FROM {0} WHERE survey_question_id = %s """.format(self._response_type_table) TRN.add(sql, [self.id]) self.response_type = TRN.execute_fetchlast() sql = """SELECT {0} FROM {1} WHERE survey_question_id = %s """.format(_LOCALE_COLUMN, self._survey_question_table) TRN.add(sql, [self.id]) self.question = TRN.execute_fetchlast() sql = """SELECT american FROM {0} WHERE survey_question_id = %s """.format(self._survey_question_table) TRN.add(sql, [self.id]) self.american_question = TRN.execute_fetchlast() self.triggers = self._triggers() self.qid = '_'.join(self.group_name.split() + [str(self.id)]) element_ids, elements = self._interface_elements() self.interface_elements = elements self.interface_element_ids = ['%s_%d' % (self.qid, i) for i in element_ids]
def patch_db(patches_dir=PATCHES_DIR, verbose=False): """Patches the database schema based on the settings table Pulls the current patch from the settings table and applies all subsequent patches found in the patches directory. """ with TRN: TRN.add("SELECT current_patch FROM settings") current_patch = TRN.execute_fetchlast() current_patch_fp = join(patches_dir, current_patch) sql_glob = join(patches_dir, '*.sql') patch_files = natsorted(glob(sql_glob)) if current_patch == 'unpatched': next_patch_index = 0 elif current_patch_fp not in patch_files: raise RuntimeError("Cannot find patch file %s" % current_patch) else: next_patch_index = patch_files.index(current_patch_fp) + 1 patch_update_sql = "UPDATE settings SET current_patch = %s" for patch_fp in patch_files[next_patch_index:]: patch_filename = split(patch_fp)[-1] with open(patch_fp, 'U') as patch_file: if verbose: echo('\tApplying patch %s...' % patch_filename) TRN.add(patch_file.read()) TRN.add(patch_update_sql, [patch_filename])
def deleteAGParticipantSurvey(self, ag_login_id, participant_name): # Remove user from new schema 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_fetchlast() sql = "DELETE FROM survey_answers WHERE survey_id = %s" TRN.add(sql, [survey_id]) sql = "DELETE FROM survey_answers_other WHERE survey_id = %s" TRN.add(sql, [survey_id]) # Reset survey attached to barcode(s) sql = """UPDATE ag_kit_barcodes SET survey_id = NULL WHERE survey_id = %s""" TRN.add(sql, [survey_id]) sql = "DELETE FROM promoted_survey_ids WHERE survey_id = %s" TRN.add(sql, [survey_id]) # Delete last due to foreign keys sql = "DELETE FROM ag_login_surveys WHERE survey_id = %s" TRN.add(sql, [survey_id]) sql = """DELETE FROM ag_consent WHERE ag_login_id = %s AND participant_name = %s""" TRN.add(sql, [ag_login_id, participant_name])
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 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 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 deleteSample(self, barcode, ag_login_id): """ Removes by either releasing barcode back for relogging or withdraw Parameters ---------- barcode : str Barcode to delete ag_login_id : UUID4 Login ID for the barcode Notes ----- Strictly speaking the ag_login_id isn't needed but it makes it really hard to hack the function when you would need to know someone else's login id (a GUID) to delete something maliciously. If the barcode has never been scanned, assume a mis-log and wipe it so barcode can be logged again. If barcode has been scanned, that means we have recieved it and must withdraw it to delete it from the system. """ with TRN: # Figure out if we've received the barcode or not sql = "SELECT scan_date FROM barcode WHERE barcode = %s" TRN.add(sql, [barcode]) received = TRN.execute_fetchlast() if not received: # Not recieved, so we release the barcode back to be relogged set_text = """site_sampled = NULL, sample_time = NULL, sample_date = NULL, environment_sampled = NULL, notes = NULL""" sql = "UPDATE barcode SET status = NULL WHERE barcode = %s" TRN.add(sql, [barcode]) else: # barcode already recieved, so we withdraw the barcode set_text = "withdrawn = 'Y'" sql = """UPDATE ag_kit_barcodes SET {} WHERE barcode IN ( SELECT akb.barcode FROM ag_kit_barcodes akb INNER JOIN ag_kit ak USING (ag_kit_id) WHERE ak.ag_login_id = %s AND akb.barcode = %s)""".format(set_text) TRN.add(sql, [ag_login_id, barcode]) sql = """DELETE FROM ag.source_barcodes_surveys WHERE barcode = %s""" TRN.add(sql, [barcode])
def ag_verify_kit_password_change_code(self, email, kitid, passcode): """returns true if it still in the password change window email is the email address of the participant kitid is the supplied_kit_id in the ag_kit table passcode is the password change verification value """ sql = """SELECT EXISTS(SELECT pass_reset_time FROM ag.ag_kit INNER JOIN ag.ag_login USING (ag_login_id) WHERE pass_reset_code = %s and email = %s AND supplied_kit_id = %s AND NOW() < pass_reset_time)""" with TRN: TRN.add(sql, [passcode, email, kitid]) return TRN.execute_fetchlast()
def deleteSample(self, barcode, ag_login_id): """ Removes by either releasing barcode back for relogging or withdraw Parameters ---------- barcode : str Barcode to delete ag_login_id : UUID4 Login ID for the barcode Notes ----- Strictly speaking the ag_login_id isn't needed but it makes it really hard to hack the function when you would need to know someone else's login id (a GUID) to delete something maliciously. If the barcode has never been scanned, assume a mis-log and wipe it so barcode can be logged again. If barcode has been scanned, that means we have recieved it and must withdraw it to delete it from the system. """ with TRN: # Figure out if we've received the barcode or not sql = "SELECT scan_date FROM barcode WHERE barcode = %s" TRN.add(sql, [barcode]) received = TRN.execute_fetchlast() if not received: # Not recieved, so we release the barcode back to be relogged set_text = """site_sampled = NULL, sample_time = NULL, sample_date = NULL, environment_sampled = NULL, notes = NULL, survey_id = NULL""" sql = "UPDATE barcode SET status = NULL WHERE barcode = %s" TRN.add(sql, [barcode]) else: # barcode already recieved, so we withdraw the barcode set_text = "withdrawn = 'Y'" sql = """UPDATE ag_kit_barcodes SET {} WHERE barcode IN ( SELECT akb.barcode FROM ag_kit_barcodes akb INNER JOIN ag_kit ak USING (ag_kit_id) WHERE ak.ag_login_id = %s AND akb.barcode = %s)""".format(set_text) TRN.add(sql, [ag_login_id, barcode])
def addAGLogin(self, email, name, address, city, state, zip_, country): """Adds a new login or returns the login_id if email already exists Parameters ---------- email : str Email to register for user name : str Name to register for user address : str Street address to register for user city : str City to register for user state : str State to register for user zip_ : str Postal code to register for user country : str Country to register for user Returns ------- ag_login_id : str UUID for new user, or existing user if email already in system """ with TRN: clean_email = email.strip().lower() ag_login_id = self.check_login_exists(email) if not ag_login_id: # create the login sql = """INSERT INTO ag_login (email, name, address, city, state, zip, country) VALUES (%s, %s, %s, %s, %s, %s, %s) RETURNING ag_login_id""" TRN.add( sql, [clean_email, name, address, city, state, zip_, country]) ag_login_id = TRN.execute_fetchlast() return ag_login_id
def addAGLogin(self, email, name, address, city, state, zip_, country): """Adds a new login or returns the login_id if email already exists Parameters ---------- email : str Email to register for user name : str Name to register for user address : str Street address to register for user city : str City to register for user state : str State to register for user zip_ : str Postal code to register for user country : str Country to register for user Returns ------- ag_login_id : str UUID for new user, or existing user if email already in system """ with TRN: clean_email = email.strip().lower() ag_login_id = self.check_login_exists(email) if not ag_login_id: # create the login sql = """INSERT INTO ag_login (email, name, address, city, state, zip, country) VALUES (%s, %s, %s, %s, %s, %s, %s) RETURNING ag_login_id""" TRN.add(sql, [clean_email, name, address, city, state, zip_, country]) ag_login_id = TRN.execute_fetchlast() return ag_login_id
def __init__(self, ID): self.id = ID with TRN: sql = """SELECT survey_group FROM {0} WHERE survey_id = %s ORDER BY survey_group""".format(self._surveys_table) TRN.add(sql, [self.id]) results = TRN.execute_fetchflatten() self.groups = [Group(x) for x in results] self.questions = {} self.question_types = {} for group in self.groups: for question in group.questions: self.question_types[question.id] = question.response_type self.questions[question.id] = question sql = """SELECT {0} FROM {1} WHERE american='Unspecified' """.format(_LOCALE_COLUMN, self._survey_response_table) TRN.add(sql) self.unspecified = TRN.execute_fetchlast()
def check_access(self, supplied_kit_id, barcode): """Check if the user has access to the barcode Parameters ---------- supplied_kit_id : str The user's supplied kit ID barcode : str The barcode to check access for Returns ------- boolean True if the user can access the barcode, False otherwise """ with TRN: ag_login_id = self.get_user_for_kit(supplied_kit_id) sql = """SELECT EXISTS ( SELECT barcode FROM ag.ag_kit JOIN ag.ag_kit_barcodes USING (ag_kit_id) WHERE ag_login_id = %s AND barcode = %s)""" TRN.add(sql, [ag_login_id, barcode]) return TRN.execute_fetchlast()
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'])) # 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 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 so we need to attach this # survey ID to the consent and the login surveys join table 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'])) # 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)