def delete(cls, id_): r"""Deletes the table from the database Parameters ---------- id_ : integer The object identifier Raises ------ QiitaDBUnknownIDError If no sample template with id id_ exists QiitaDBError If the study that owns this sample template has raw datas """ cls._check_subclass() if not cls.exists(id_): raise QiitaDBUnknownIDError(id_, cls.__name__) raw_datas = [str(rd) for rd in Study(cls(id_).study_id).raw_data()] if raw_datas: raise QiitaDBError("Sample template can not be erased because " "there are raw datas (%s) associated." % ', '.join(raw_datas)) table_name = cls._table_name(id_) conn_handler = SQLConnectionHandler() # Delete the sample template filepaths queue = "delete_sample_template_%d" % id_ conn_handler.create_queue(queue) conn_handler.add_to_queue( queue, "DELETE FROM qiita.sample_template_filepath WHERE study_id = %s", (id_, )) conn_handler.add_to_queue( queue, "DROP TABLE qiita.{0}".format(table_name)) conn_handler.add_to_queue( queue, "DELETE FROM qiita.{0} where {1} = %s".format(cls._table, cls._id_column), (id_,)) conn_handler.add_to_queue( queue, "DELETE FROM qiita.{0} where {1} = %s".format(cls._column_table, cls._id_column), (id_,)) conn_handler.execute_queue(queue)
WHERE raw_data_id = %s""" move_files = [] for rd_id in rd_ids: rd = RawData(rd_id) filepaths = rd.get_filepaths() studies = [s[0] for s in conn_handler.execute_fetchall(sql_studies, (rd_id,))] if filepaths: # we need to move the files to a study. We chose the one with lower # study id. Currently there is no case in the live database in which a # RawData with no prep templates is attached to more than one study, # but I think it is better to normalize this just in case move_files.append((min(studies), filepaths)) # To delete the RawData we first need to unlink all the files conn_handler.add_to_queue(queue, sql_unlink, (rd_id,)) # Then, remove the raw data from all the studies for st_id in studies: conn_handler.add_to_queue(queue, sql_detach, (rd_id, st_id)) conn_handler.add_to_queue(queue, sql_delete, (rd_id,)) # We can now perform all changes in the DB. Although these changes can be # done in an SQL patch, they are done here because we need to execute the # previous clean up in the database before we can actually execute the SQL # patch. sql = """CREATE TABLE qiita.study_prep_template ( study_id bigint NOT NULL, prep_template_id bigint NOT NULL, CONSTRAINT idx_study_prep_template
def create(cls, md_template, study, data_type, investigation_type=None): r"""Creates the metadata template in the database Parameters ---------- md_template : DataFrame The metadata template file contents indexed by samples Ids study : Study The study to which the prep template belongs to. data_type : str or int The data_type of the prep template investigation_type : str, optional The investigation type, if relevant Returns ------- A new instance of `cls` to access to the PrepTemplate stored in the DB Raises ------ QiitaDBColumnError If the investigation_type is not valid If a required column is missing in md_template """ # If the investigation_type is supplied, make sure it is one of # the recognized investigation types if investigation_type is not None: cls.validate_investigation_type(investigation_type) # Get a connection handler conn_handler = SQLConnectionHandler() queue_name = "CREATE_PREP_TEMPLATE_%d_%d" % (study.id, id(md_template)) conn_handler.create_queue(queue_name) # Check if the data_type is the id or the string if isinstance(data_type, (int, long)): data_type_id = data_type data_type_str = convert_from_id(data_type, "data_type") else: data_type_id = convert_to_id(data_type, "data_type") data_type_str = data_type pt_cols = PREP_TEMPLATE_COLUMNS if data_type_str in TARGET_GENE_DATA_TYPES: pt_cols = deepcopy(PREP_TEMPLATE_COLUMNS) pt_cols.update(PREP_TEMPLATE_COLUMNS_TARGET_GENE) md_template = cls._clean_validate_template(md_template, study.id, pt_cols) # Insert the metadata template # We need the prep_id for multiple calls below, which currently is not # supported by the queue system. Thus, executing this outside the queue prep_id = conn_handler.execute_fetchone( "INSERT INTO qiita.prep_template " "(data_type_id, investigation_type) " "VALUES (%s, %s) RETURNING prep_template_id", (data_type_id, investigation_type))[0] cls._add_common_creation_steps_to_queue(md_template, prep_id, conn_handler, queue_name) # Link the prep template with the study sql = ("INSERT INTO qiita.study_prep_template " "(study_id, prep_template_id) VALUES (%s, %s)") conn_handler.add_to_queue(queue_name, sql, (study.id, prep_id)) try: conn_handler.execute_queue(queue_name) except Exception: # Clean up row from qiita.prep_template conn_handler.execute( "DELETE FROM qiita.prep_template where " "{0} = %s".format(cls._id_column), (prep_id,)) # Check if sample IDs present here but not in sample template sql = ("SELECT sample_id from qiita.study_sample WHERE " "study_id = %s") # Get list of study sample IDs, prep template study IDs, # and their intersection prep_samples = set(md_template.index.values) unknown_samples = prep_samples.difference( s[0] for s in conn_handler.execute_fetchall(sql, [study.id])) if unknown_samples: raise QiitaDBExecutionError( 'Samples found in prep template but not sample template: ' '%s' % ', '.join(unknown_samples)) # some other error we haven't seen before so raise it raise pt = cls(prep_id) pt.generate_files() return pt
class TestConnHandler(TestCase): def test_create_queue(self): self.conn_handler.create_queue("toy_queue") self.assertEqual(self.conn_handler.list_queues(), ["toy_queue"]) def test_close(self): self.assertEqual(self.conn_handler._user_conn.closed, 0) self.conn_handler.close() self.assertNotEqual(self.conn_handler._user_conn.closed, 0) def test_run_queue(self): self.conn_handler.create_queue("toy_queue") self.conn_handler.add_to_queue( "toy_queue", "INSERT INTO qiita.qiita_user (email, name, password," "phone) VALUES (%s, %s, %s, %s)", ['*****@*****.**', 'Toy', 'pass', '111-111-11112']) self.conn_handler.add_to_queue( "toy_queue", "UPDATE qiita.qiita_user SET user_level_id = 1, " "phone = '222-222-2221' WHERE email = %s", ['*****@*****.**']) obs = self.conn_handler.execute_queue("toy_queue") self.assertEqual(obs, []) obs = self.conn_handler.execute_fetchall( "SELECT * from qiita.qiita_user WHERE email = %s", ['*****@*****.**']) exp = [['*****@*****.**', 1, 'pass', 'Toy', None, None, '222-222-2221', None, None, None]] self.assertEqual(obs, exp) def test_run_queue_many(self): sql = ("INSERT INTO qiita.qiita_user (email, name, password," "phone) VALUES (%s, %s, %s, %s)") sql_args = [ ('*****@*****.**', 'p1', 'pass1', '111-111'), ('*****@*****.**', 'p2', 'pass2', '111-222') ] self.conn_handler.create_queue("toy_queue") self.conn_handler.add_to_queue( "toy_queue", sql, sql_args, many=True) self.conn_handler.execute_queue('toy_queue') # make sure both users added obs = self.conn_handler.execute_fetchall( "SELECT * from qiita.qiita_user WHERE email = %s", ['*****@*****.**']) exp = [['*****@*****.**', 5, 'pass1', 'p1', None, None, '111-111', None, None, None]] self.assertEqual(obs, exp) obs = self.conn_handler.execute_fetchall( "SELECT * from qiita.qiita_user WHERE email = %s", ['*****@*****.**']) exp = [['*****@*****.**', 5, 'pass2', 'p2', None, None, '111-222', None, None, None]] self.assertEqual(obs, exp) def test_run_queue_last_return(self): self.conn_handler.create_queue("toy_queue") self.conn_handler.add_to_queue( "toy_queue", "INSERT INTO qiita.qiita_user (email, name, password," "phone) VALUES (%s, %s, %s, %s)", ['*****@*****.**', 'Toy', 'pass', '111-111-11112']) self.conn_handler.add_to_queue( "toy_queue", "UPDATE qiita.qiita_user SET user_level_id = 1, " "phone = '222-222-2221' WHERE email = %s RETURNING phone", ['*****@*****.**']) obs = self.conn_handler.execute_queue("toy_queue") self.assertEqual(obs, ['222-222-2221']) def test_run_queue_placeholders(self): self.conn_handler.create_queue("toy_queue") self.conn_handler.add_to_queue( "toy_queue", "INSERT INTO qiita.qiita_user (email, name, password," "phone) VALUES (%s, %s, %s, %s) RETURNING email, password", ['*****@*****.**', 'Toy', 'pass', '111-111-11112']) self.conn_handler.add_to_queue( "toy_queue", "UPDATE qiita.qiita_user SET user_level_id = 1, " "phone = '222-222-2221' WHERE email = %s AND password = %s", ['{0}', '{1}']) obs = self.conn_handler.execute_queue("toy_queue") self.assertEqual(obs, []) obs = self.conn_handler.execute_fetchall( "SELECT * from qiita.qiita_user WHERE email = %s", ['*****@*****.**']) exp = [['*****@*****.**', 1, 'pass', 'Toy', None, None, '222-222-2221', None, None, None]] self.assertEqual(obs, exp) def test_queue_fail(self): """Fail if no results data exists for substitution""" self.conn_handler = SQLConnectionHandler() self.conn_handler.create_queue("toy_queue") self.conn_handler.add_to_queue( "toy_queue", "INSERT INTO qiita.qiita_user (email, name, password) VALUES " "(%s, %s, %s)", ['*****@*****.**', 'Toy', 'pass']) self.conn_handler.add_to_queue( "toy_queue", "UPDATE qiita.qiita_user SET user_level_id = 1 " "WHERE email = %s and password = %s", [{0}, {1}]) with self.assertRaises(QiitaDBExecutionError): self.conn_handler.execute_queue("toy_queue") # make sure roll back correctly obs = self.conn_handler.execute_fetchall( "SELECT * from qiita.qiita_user WHERE email = %s", ['*****@*****.**']) self.assertEqual(obs, []) def test_huge_queue(self): self.conn_handler = SQLConnectionHandler() self.conn_handler.create_queue("toy_queue") # add tons of inserts to queue for x in range(120): self.conn_handler.add_to_queue( "toy_queue", "INSERT INTO qiita.qiita_user (email, name, password) VALUES " "(%s, %s, %s)", ['*****@*****.**' % x, 'Toy', 'pass']) # add failing insert as final item in queue self.conn_handler.add_to_queue( "toy_queue", "INSERT INTO qiita.qiita_BADTABLE (email, name, password) VALUES " "(%s, %s, %s)", ['*****@*****.**' % x, 'Toy', 'pass']) self.conn_handler.add_to_queue( "toy_queue", "UPDATE qiita.qiita_user SET user_level_id = 1 " "WHERE email = %s and password = %s", [{0}, {1}]) with self.assertRaises(QiitaDBExecutionError): self.conn_handler.execute_queue("toy_queue") # make sure roll back correctly obs = self.conn_handler.execute_fetchall( "SELECT * from qiita.qiita_user WHERE email LIKE " "'%[email protected]%'") self.assertEqual(obs, []) def test_get_temp_queue(self): my_queue = self.conn_handler.get_temp_queue() self.assertTrue(my_queue in self.conn_handler.list_queues()) self.conn_handler.add_to_queue(my_queue, "SELECT * from qiita.qiita_user") self.conn_handler.add_to_queue(my_queue, "SELECT * from qiita.user_level") self.conn_handler.execute_queue(my_queue) self.assertTrue(my_queue not in self.conn_handler.list_queues())
table = 'prep_template_filepath' column = 'prep_template_id' # unlink all the qiime mapping files for this prep template object for mf in mapping_files: # (1) get the ids that we are going to delete. # because of the FK restriction, we cannot just delete the ids ids = conn_handler.execute_fetchall( 'SELECT filepath_id FROM qiita.{0} WHERE ' '{1}=%s and filepath_id=%s'.format(table, column), (pt.id, mf[0])) ids = flatten(ids) # (2) delete the entries from the prep_template_filepath table conn_handler.add_to_queue( q_name, "DELETE FROM qiita.{0} " "WHERE {1}=%s and filepath_id=%s;".format(table, column), (pt.id, mf[0])) # (3) delete the entries from the filepath table conn_handler.add_to_queue( q_name, "DELETE FROM qiita.filepath WHERE " "filepath_id IN ({0});".format(', '.join(map(str, ids)))) try: conn_handler.execute_queue(q_name) except Exception as e: raise # create correct versions of the mapping files for prep_template_id in all_ids:
class TestConnHandler(TestCase): def test_create_queue(self): self.conn_handler.create_queue("toy_queue") self.assertEqual(self.conn_handler.list_queues(), ["toy_queue"]) def test_run_queue(self): self.conn_handler.create_queue("toy_queue") self.conn_handler.add_to_queue( "toy_queue", "INSERT INTO qiita.qiita_user (email, name, password," "phone) VALUES (%s, %s, %s, %s)", ['*****@*****.**', 'Toy', 'pass', '111-111-11112']) self.conn_handler.add_to_queue( "toy_queue", "UPDATE qiita.qiita_user SET user_level_id = 1, " "phone = '222-222-2221' WHERE email = %s", ['*****@*****.**']) obs = self.conn_handler.execute_queue("toy_queue") self.assertEqual(obs, []) obs = self.conn_handler.execute_fetchall( "SELECT * from qiita.qiita_user WHERE email = %s", ['*****@*****.**']) exp = [['*****@*****.**', 1, 'pass', 'Toy', None, None, '222-222-2221', None, None, None]] self.assertEqual(obs, exp) def test_run_queue_many(self): sql = ("INSERT INTO qiita.qiita_user (email, name, password," "phone) VALUES (%s, %s, %s, %s)") sql_args = [ ('*****@*****.**', 'p1', 'pass1', '111-111'), ('*****@*****.**', 'p2', 'pass2', '111-222') ] self.conn_handler.create_queue("toy_queue") self.conn_handler.add_to_queue( "toy_queue", sql, sql_args, many=True) self.conn_handler.execute_queue('toy_queue') # make sure both users added obs = self.conn_handler.execute_fetchall( "SELECT * from qiita.qiita_user WHERE email = %s", ['*****@*****.**']) exp = [['*****@*****.**', 5, 'pass1', 'p1', None, None, '111-111', None, None, None]] self.assertEqual(obs, exp) obs = self.conn_handler.execute_fetchall( "SELECT * from qiita.qiita_user WHERE email = %s", ['*****@*****.**']) exp = [['*****@*****.**', 5, 'pass2', 'p2', None, None, '111-222', None, None, None]] self.assertEqual(obs, exp) def test_run_queue_last_return(self): self.conn_handler.create_queue("toy_queue") self.conn_handler.add_to_queue( "toy_queue", "INSERT INTO qiita.qiita_user (email, name, password," "phone) VALUES (%s, %s, %s, %s)", ['*****@*****.**', 'Toy', 'pass', '111-111-11112']) self.conn_handler.add_to_queue( "toy_queue", "UPDATE qiita.qiita_user SET user_level_id = 1, " "phone = '222-222-2221' WHERE email = %s RETURNING phone", ['*****@*****.**']) obs = self.conn_handler.execute_queue("toy_queue") self.assertEqual(obs, ['222-222-2221']) def test_run_queue_placeholders(self): self.conn_handler.create_queue("toy_queue") self.conn_handler.add_to_queue( "toy_queue", "INSERT INTO qiita.qiita_user (email, name, password," "phone) VALUES (%s, %s, %s, %s) RETURNING email, password", ['*****@*****.**', 'Toy', 'pass', '111-111-11112']) self.conn_handler.add_to_queue( "toy_queue", "UPDATE qiita.qiita_user SET user_level_id = 1, " "phone = '222-222-2221' WHERE email = %s AND password = %s", ['{0}', '{1}']) obs = self.conn_handler.execute_queue("toy_queue") self.assertEqual(obs, []) obs = self.conn_handler.execute_fetchall( "SELECT * from qiita.qiita_user WHERE email = %s", ['*****@*****.**']) exp = [['*****@*****.**', 1, 'pass', 'Toy', None, None, '222-222-2221', None, None, None]] self.assertEqual(obs, exp) def test_queue_fail(self): """Fail if no results data exists for substitution""" self.conn_handler = SQLConnectionHandler() self.conn_handler.create_queue("toy_queue") self.conn_handler.add_to_queue( "toy_queue", "INSERT INTO qiita.qiita_user (email, name, password) VALUES " "(%s, %s, %s)", ['*****@*****.**', 'Toy', 'pass']) self.conn_handler.add_to_queue( "toy_queue", "UPDATE qiita.qiita_user SET user_level_id = 1 " "WHERE email = %s and password = %s", [{0}, {1}]) with self.assertRaises(QiitaDBExecutionError): self.conn_handler.execute_queue("toy_queue") # make sure roll back correctly obs = self.conn_handler.execute_fetchall( "SELECT * from qiita.qiita_user WHERE email = %s", ['*****@*****.**']) self.assertEqual(obs, []) def test_huge_queue(self): self.conn_handler = SQLConnectionHandler() self.conn_handler.create_queue("toy_queue") # add tons of inserts to queue for x in range(120): self.conn_handler.add_to_queue( "toy_queue", "INSERT INTO qiita.qiita_user (email, name, password) VALUES " "(%s, %s, %s)", ['*****@*****.**' % x, 'Toy', 'pass']) # add failing insert as final item in queue self.conn_handler.add_to_queue( "toy_queue", "INSERT INTO qiita.qiita_BADTABLE (email, name, password) VALUES " "(%s, %s, %s)", ['*****@*****.**' % x, 'Toy', 'pass']) self.conn_handler.add_to_queue( "toy_queue", "UPDATE qiita.qiita_user SET user_level_id = 1 " "WHERE email = %s and password = %s", [{0}, {1}]) with self.assertRaises(QiitaDBExecutionError): self.conn_handler.execute_queue("toy_queue") # make sure roll back correctly obs = self.conn_handler.execute_fetchall( "SELECT * from qiita.qiita_user WHERE email LIKE " "'%[email protected]%'") self.assertEqual(obs, []) def test_get_temp_queue(self): my_queue = self.conn_handler.get_temp_queue() self.assertTrue(my_queue in self.conn_handler.list_queues()) self.conn_handler.add_to_queue(my_queue, "SELECT * from qiita.qiita_user") self.conn_handler.add_to_queue(my_queue, "SELECT * from qiita.user_level") self.conn_handler.execute_queue(my_queue) self.assertTrue(my_queue not in self.conn_handler.list_queues())