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