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_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 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_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_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 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)