def class_modifier(cls): # First, we check that we are not in a production environment conn_handler = SQLConnectionHandler() # It is possible that we are connecting to a production database test_db = conn_handler.execute_fetchone("SELECT test FROM settings")[0] # Or the loaded configuration file belongs to a production environment # or the test database is not qiita_test if not qiita_config.test_environment or not test_db \ or qiita_config.database != 'qiita_test': raise RuntimeError("Working in a production environment. Not " "executing the tests to keep the production " "database safe.") # Now, we decorate the setup and teardown functions class DecoratedClass(cls): def setUp(self): super(DecoratedClass, self).setUp() self.conn_handler = SQLConnectionHandler() @reset_test_database def tearDown(self): super(DecoratedClass, self).tearDown() del self.conn_handler return DecoratedClass
def __getitem__(self, key): r"""Returns the value of the metadata category `key` Parameters ---------- key : str The metadata category Returns ------- obj The value of the metadata category `key` Raises ------ KeyError If the metadata category `key` does not exists See Also -------- get """ conn_handler = SQLConnectionHandler() key = key.lower() if key not in self._get_categories(conn_handler): # The key is not available for the sample, so raise a KeyError raise KeyError("Metadata category %s does not exists for sample %s" " in template %d" % (key, self._id, self._md_template.id)) sql = """SELECT {0} FROM qiita.{1} WHERE sample_id=%s""".format(key, self._dynamic_table) return conn_handler.execute_fetchone(sql, (self._id, ))[0]
def class_modifier(cls): # First, we check that we are not in a production environment conn_handler = SQLConnectionHandler() # It is possible that we are connecting to a production database test_db = conn_handler.execute_fetchone("SELECT test FROM settings")[0] # Or the loaded configuration file belongs to a production environment # or the test database is not qiita_test if not qiita_config.test_environment or not test_db \ or qiita_config.database != 'qiita_test': raise RuntimeError("Working in a production environment. Not " "executing the tests to keep the production " "database safe.") # Now, we decorate the setup and teardown functions class DecoratedClass(cls): @build_test_database def setUp(self): super(DecoratedClass, self).setUp() self.conn_handler = SQLConnectionHandler() @drop_test_database def tearDown(self): super(DecoratedClass, self).tearDown() del self.conn_handler return DecoratedClass
def preprocessed_data(self): conn_handler = SQLConnectionHandler() prep_datas = conn_handler.execute_fetchall( "SELECT preprocessed_data_id FROM " "qiita.prep_template_preprocessed_data WHERE prep_template_id=%s", (self.id,)) return [x[0] for x in prep_datas]
def test_get_preprocess_fasta_cmd_sff_run_prefix_match_error_1(self): # Test that the run prefixes in the prep_template and the file names # actually match and raise an error if not conn_handler = SQLConnectionHandler() sql = (""" INSERT INTO qiita.filepath (filepath_id, filepath, filepath_type_id, checksum, checksum_algorithm_id, data_directory_id) VALUES (19, '1_new.sff', 17, 852952723, 1, 5); INSERT INTO qiita.raw_filepath (raw_data_id , filepath_id) VALUES (3, 19); INSERT INTO qiita.filepath (filepath_id, filepath, filepath_type_id, checksum, checksum_algorithm_id, data_directory_id) VALUES (20, '1_error.sff', 17, 852952723, 1, 5); INSERT INTO qiita.raw_filepath (raw_data_id , filepath_id) VALUES (3, 20); UPDATE qiita.prep_1 SET run_prefix='preprocess_test'; UPDATE qiita.prep_1 SET run_prefix='new' WHERE sample_id = '1.SKB8.640193'; """) conn_handler.execute(sql) raw_data = RawData(3) params = Preprocessed454Params(1) prep_template = PrepTemplate(1) with self.assertRaises(ValueError): _get_preprocess_fasta_cmd(raw_data, prep_template, params)
def status(self): """The status of the prep template Returns ------- str The status of the prep template Notes ----- The status of a prep template is inferred by the status of the processed data generated from this prep template. If no processed data has been generated with this prep template; then the status is 'sandbox'. """ conn_handler = SQLConnectionHandler() sql = """SELECT processed_data_status FROM qiita.processed_data_status pds JOIN qiita.processed_data pd USING (processed_data_status_id) JOIN qiita.preprocessed_processed_data ppd_pd USING (processed_data_id) JOIN qiita.prep_template_preprocessed_data pt_ppd USING (preprocessed_data_id) WHERE pt_ppd.prep_template_id=%s""" pd_statuses = conn_handler.execute_fetchall(sql, (self._id,)) return infer_status(pd_statuses)
def get_filepaths(self): r"""Retrieves the list of (filepath_id, filepath)""" # Check that this function has been called from a subclass self._check_subclass() # Check if the connection handler has been provided. Create a new # one if not. conn_handler = SQLConnectionHandler() try: filepath_ids = conn_handler.execute_fetchall( "SELECT filepath_id, filepath FROM qiita.filepath WHERE " "filepath_id IN (SELECT filepath_id FROM qiita.{0} WHERE " "{1}=%s) ORDER BY filepath_id DESC".format( self._filepath_table, self._id_column), (self.id, )) except Exception as e: LogEntry.create('Runtime', str(e), info={self.__class__.__name__: self.id}) raise e _, fb = get_mountpoint('templates')[0] base_fp = partial(join, fb) return [(fpid, base_fp(fp)) for fpid, fp in filepath_ids]
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_preprocess_fasta_cmd_sff_run_prefix(self): # Need to alter the run_prefix of one sample so we can test the # multiple values conn_handler = SQLConnectionHandler() sql = ("UPDATE qiita.prep_1 SET run_prefix='test1' WHERE " "sample_id = '1.SKM9.640192'") conn_handler.execute(sql) raw_data = RawData(3) params = Preprocessed454Params(1) prep_template = PrepTemplate(1) obs_cmd, obs_output_dir = _get_preprocess_fasta_cmd( raw_data, prep_template, params) obs_cmds = obs_cmd.split('; ') # assumming that test_get_preprocess_fasta_cmd_sff_no_run_prefix is # working we only need to test for the commands being ran and # that n is valid self.assertEqual(len(obs_cmds), 8) self.assertTrue(obs_cmds[0].startswith('process_sff.py')) self.assertTrue(obs_cmds[1].startswith('process_sff.py')) self.assertTrue(obs_cmds[2].startswith('split_libraries.py')) self.assertIn('-n 1', obs_cmds[2]) self.assertTrue(obs_cmds[3].startswith('split_libraries.py')) self.assertIn('-n 800000', obs_cmds[3]) self.assertTrue(obs_cmds[4].startswith('cat')) self.assertIn('split_library_log.txt', obs_cmds[4]) self.assertTrue(obs_cmds[5].startswith('cat')) self.assertTrue('seqs.fna', obs_cmds[5]) self.assertTrue(obs_cmds[6].startswith('cat')) self.assertIn('seqs_filtered.qual', obs_cmds[6])
def _to_dict(self): r"""Returns the categories and their values in a dictionary Returns ------- dict of {str: str} A dictionary of the form {category: value} """ conn_handler = SQLConnectionHandler() d = dict(conn_handler.execute_fetchone( "SELECT * FROM qiita.{0} WHERE {1}=%s AND " "sample_id=%s".format(self._table, self._id_column), (self._md_template.id, self._id))) dynamic_d = dict(conn_handler.execute_fetchone( "SELECT * from qiita.{0} WHERE " "sample_id=%s".format(self._dynamic_table), (self._id, ))) d.update(dynamic_d) del d['sample_id'] del d[self._id_column] d.pop('study_id', None) # Modify all the *_id columns to include the string instead of the id for k, v in viewitems(self._md_template.translate_cols_dict): d[v] = self._md_template.str_cols_handlers[k][d[k]] del d[k] return d
def to_dataframe(self): """Returns the metadata template as a dataframe Returns ------- pandas DataFrame The metadata in the template,indexed on sample id """ conn_handler = SQLConnectionHandler() cols = get_table_cols(self._table, conn_handler) if 'study_id' in cols: cols.remove('study_id') dyncols = get_table_cols(self._table_name(self._id), conn_handler) # remove sample_id from dyncols so not repeated dyncols.remove('sample_id') # Get all metadata for the template sql = """SELECT {0}, {1} FROM qiita.{2} req INNER JOIN qiita.{3} dyn on req.sample_id = dyn.sample_id WHERE req.{4} = %s""".format( ", ".join("req.%s" % c for c in cols), ", ".join("dyn.%s" % d for d in dyncols), self._table, self._table_name(self._id), self._id_column) meta = conn_handler.execute_fetchall(sql, [self._id]) cols = cols + dyncols # Create the dataframe and clean it up a bit df = pd.DataFrame((list(x) for x in meta), columns=cols) df.set_index('sample_id', inplace=True, drop=True) # Turn id cols to value cols for col, value in viewitems(self.str_cols_handlers): df[col].replace(value, inplace=True) df.rename(columns=self.translate_cols_dict, inplace=True) return df
def create(cls, md_template, study): r"""Creates the sample template in the database Parameters ---------- md_template : DataFrame The metadata template file contents indexed by samples Ids study : Study The study to which the sample template belongs to. """ cls._check_subclass() # Check that we don't have a MetadataTemplate for study if cls.exists(study.id): raise QiitaDBDuplicateError(cls.__name__, 'id: %d' % study.id) conn_handler = SQLConnectionHandler() queue_name = "CREATE_SAMPLE_TEMPLATE_%d" % study.id conn_handler.create_queue(queue_name) # Clean and validate the metadata template given md_template = cls._clean_validate_template(md_template, study.id, study.id, conn_handler) cls._add_common_creation_steps_to_queue(md_template, study.id, conn_handler, queue_name) conn_handler.execute_queue(queue_name) st = cls(study.id) st.generate_files() return st
def test_get_qiime_minimal_mapping_single_reverse_primer(self): conn_handler = SQLConnectionHandler() conn_handler sql = """INSERT INTO qiita.prep_columns (prep_template_id, column_name, column_type) VALUES (1, 'reverselinkerprimer', 'varchar'); ALTER TABLE qiita.prep_1 ADD COLUMN reverselinkerprimer varchar; DELETE FROM qiita.prep_columns WHERE prep_template_id = 1 AND column_name = 'run_prefix'; ALTER TABLE qiita.prep_1 DROP COLUMN run_prefix; UPDATE qiita.prep_1 SET reverselinkerprimer = %s """ conn_handler.execute(sql, ('GTGCCAGCM',)) prep_template = PrepTemplate(1) prep_template.generate_files() out_dir = mkdtemp() obs_fps = _get_qiime_minimal_mapping(prep_template, out_dir) exp_fps = [join(out_dir, 'prep_1_MMF.txt')] # Check that the returned list is as expected self.assertEqual(obs_fps, exp_fps) # Check that the file exists self.assertTrue(exists(exp_fps[0])) # Check the contents of the file with open(exp_fps[0], "U") as f: self.assertEqual(f.read(), EXP_PREP_RLP)
def update(self, md_template): r"""Update values in the template Parameters ---------- md_template : DataFrame The metadata template file contents indexed by samples Ids Raises ------ QiitaDBError If md_template and db do not have the same sample ids If md_template and db do not have the same column headers If self.can_be_updated is not True """ conn_handler = SQLConnectionHandler() # Clean and validate the metadata template given new_map = self._clean_validate_template(md_template, self.study_id, self.columns_restrictions) # Retrieving current metadata current_map = self._transform_to_dict(conn_handler.execute_fetchall( "SELECT * FROM qiita.{0}".format(self._table_name(self.id)))) current_map = pd.DataFrame.from_dict(current_map, orient='index') # simple validations of sample ids and column names samples_diff = set(new_map.index).difference(current_map.index) if samples_diff: raise QiitaDBError('The new template differs from what is stored ' 'in database by these samples names: %s' % ', '.join(samples_diff)) columns_diff = set(new_map.columns).difference(current_map.columns) if columns_diff: raise QiitaDBError('The new template differs from what is stored ' 'in database by these columns names: %s' % ', '.join(columns_diff)) # here we are comparing two dataframes following: # http://stackoverflow.com/a/17095620/4228285 current_map.sort(axis=0, inplace=True) current_map.sort(axis=1, inplace=True) new_map.sort(axis=0, inplace=True) new_map.sort(axis=1, inplace=True) map_diff = (current_map != new_map).stack() map_diff = map_diff[map_diff] map_diff.index.names = ['id', 'column'] changed_cols = map_diff.index.get_level_values('column').unique() if not self.can_be_updated(columns=set(changed_cols)): raise QiitaDBError('The new template is modifying fields that ' 'cannot be modified. Try removing the target ' 'gene fields or deleting the processed data. ' 'You are trying to modify: %s' % ', '.join(changed_cols)) for col in changed_cols: self.update_category(col, new_map[col].to_dict()) self.generate_files()
def raw_data(self): conn_handler = SQLConnectionHandler() result = conn_handler.execute_fetchone( "SELECT raw_data_id FROM qiita.prep_template " "WHERE prep_template_id=%s", (self.id,)) if result: return result[0] return None
def __getitem__(self, key): r"""Returns the value of the metadata category `key` Parameters ---------- key : str The metadata category Returns ------- obj The value of the metadata category `key` Raises ------ KeyError If the metadata category `key` does not exists See Also -------- get """ conn_handler = SQLConnectionHandler() key = key.lower() if key in self._get_categories(conn_handler): # It's possible that the key is asking for one of the *_id columns # that we have to do the translation def handler(x): return x # prevent flake8 from complaining about the function not being # used and a redefinition happening in the next few lines handler(None) if key in self._md_template.translate_cols_dict.values(): handler = ( lambda x: self._md_template.str_cols_handlers[key][x]) key = "%s_id" % key # Check if we have either to query the table with required columns # or the dynamic table if key in get_table_cols(self._table, conn_handler): result = conn_handler.execute_fetchone( "SELECT {0} FROM qiita.{1} WHERE {2}=%s AND " "sample_id=%s".format(key, self._table, self._id_column), (self._md_template.id, self._id))[0] return handler(result) else: return conn_handler.execute_fetchone( "SELECT {0} FROM qiita.{1} WHERE " "sample_id=%s".format(key, self._dynamic_table), (self._id, ))[0] else: # The key is not available for the sample, so raise a KeyError raise KeyError("Metadata category %s does not exists for sample %s" " in template %d" % (key, self._id, self._md_template.id))
def _check_id(self, id_): r"""Checks that the MetadataTemplate id_ exists on the database""" self._check_subclass() conn_handler = SQLConnectionHandler() return conn_handler.execute_fetchone( "SELECT EXISTS(SELECT * FROM qiita.{0} WHERE " "{1}=%s)".format(self._table, self._id_column), (id_, ))[0]
def test_init(self): obs = SQLConnectionHandler() self.assertEqual(obs.admin, "no_admin") self.assertEqual(obs.queues, {}) self.assertTrue(isinstance(obs._connection, connection)) self.assertEqual(self.conn_handler._user_conn.closed, 0) # Let's close the connection and make sure that it gets reopened obs.close() obs = SQLConnectionHandler() self.assertEqual(self.conn_handler._user_conn.closed, 0)
def preprocessing_status(self): r"""Tells if the data has been preprocessed or not Returns ------- str One of {'not_preprocessed', 'preprocessing', 'success', 'failed'} """ conn_handler = SQLConnectionHandler() return conn_handler.execute_fetchone( "SELECT preprocessing_status FROM qiita.prep_template " "WHERE {0}=%s".format(self._id_column), (self.id,))[0]
def metadata_headers(): """Returns metadata headers available Returns ------- list Alphabetical list of all metadata headers available """ conn_handler = SQLConnectionHandler() return [x[0] for x in conn_handler.execute_fetchall( "SELECT DISTINCT column_name FROM qiita.study_sample_columns " "ORDER BY column_name")]
def setUp(self): if self.database: self.conn_handler = SQLConnectionHandler() # Drop the schema self.conn_handler.execute("DROP SCHEMA qiita CASCADE") # Create the schema with open(LAYOUT_FP, 'U') as f: self.conn_handler.execute(f.read()) # Initialize the database with open(INITIALIZE_FP, 'U') as f: self.conn_handler.execute(f.read()) # Populate the database with open(POPULATE_FP, 'U') as f: self.conn_handler.execute(f.read()) super(TestHandlerBase, self).setUp()
def metadata_headers(): """Returns metadata headers available Returns ------- list Alphabetical list of all metadata headers available """ conn_handler = SQLConnectionHandler() return [x[0] for x in conn_handler.execute_fetchall( "SELECT DISTINCT column_name FROM qiita.study_sample_columns " "UNION SELECT column_name FROM information_schema.columns " "WHERE table_name = 'required_sample_info' " "ORDER BY column_name")]
def raw_data(self, raw_data): conn_handler = SQLConnectionHandler() sql = """SELECT ( SELECT raw_data_id FROM qiita.prep_template WHERE prep_template_id=%s) IS NOT NULL""" exists = conn_handler.execute_fetchone(sql, (self.id,))[0] if exists: raise QiitaDBError( "Prep template %d already has a raw data associated" % self.id) sql = """UPDATE qiita.prep_template SET raw_data_id = %s WHERE prep_template_id = %s""" conn_handler.execute(sql, (raw_data.id, self.id))
def __call__(self, searchstr, user): """Runs a Study query and returns matching studies and samples Parameters ---------- searchstr : str Search string to use user : User object User making the search. Needed for permissions checks. Returns ------- dict Found samples in format {study_id: [[samp_id1, meta1, meta2, ...], [samp_id2, meta1, meta2, ...], ...} list metadata column names searched for Notes ----- Metadata information for each sample is in the same order as the metadata columns list returned Metadata column names and string searches are case-sensitive """ study_sql, sample_sql, meta_headers = \ self._parse_study_search_string(searchstr, True) conn_handler = SQLConnectionHandler() # get all studies containing the metadata headers requested study_ids = {x[0] for x in conn_handler.execute_fetchall(study_sql)} # strip to only studies user has access to if user.level not in {'admin', 'dev', 'superuser'}: study_ids = study_ids.intersection(Study.get_by_status('public') | user.user_studies | user.shared_studies) results = {} # run search on each study to get out the matching samples for sid in study_ids: study_res = conn_handler.execute_fetchall(sample_sql.format(sid)) if study_res: # only add study to results if actually has samples in results results[sid] = study_res self.results = results self.meta_headers = meta_headers return results, meta_headers
def study_id(self): """Gets the study id with which this prep template is associated Returns ------- int The ID of the study with which this prep template is associated """ conn = SQLConnectionHandler() sql = ("SELECT study_id FROM qiita.study_prep_template " "WHERE prep_template_id=%s") study_id = conn.execute_fetchone(sql, (self.id,)) if study_id: return study_id[0] else: raise QiitaDBError("No studies found associated with prep " "template ID %d" % self._id)
def __call__(self, searchstr, user): """Runs a Study query and returns matching studies and samples Parameters ---------- searchstr : str Search string to use user : User object User making the search. Needed for permissions checks. Returns ------- dict Found samples in format {study_id: [[samp_id1, meta1, meta2, ...], [samp_id2, meta1, meta2, ...], ...} list metadata column names searched for Notes ----- Metadata information for each sample is in the same order as the metadata columns list returned Metadata column names and string searches are case-sensitive """ study_sql, sample_sql, meta_headers = \ self._parse_study_search_string(searchstr, True) conn_handler = SQLConnectionHandler() # get all studies containing the metadata headers requested study_ids = {x[0] for x in conn_handler.execute_fetchall(study_sql)} # strip to only studies user has access to if user.level not in {'admin', 'dev', 'superuser'}: study_ids = study_ids.intersection( Study.get_by_status('public') + user.user_studies + user.shared_studies) results = {} # run search on each study to get out the matching samples for sid in study_ids: study_res = conn_handler.execute_fetchall(sample_sql.format(sid)) if study_res: # only add study to results if actually has samples in results results[sid] = study_res return results, meta_headers
def test_get_preprocess_fasta_cmd_sff_run_prefix_match_error_2(self): # Should raise error conn_handler = SQLConnectionHandler() sql = (""" UPDATE qiita.prep_1 SET run_prefix='test1'; UPDATE qiita.prep_1 SET run_prefix='test2' WHERE sample_id = '1.SKB2.640194'; UPDATE qiita.prep_1 SET run_prefix='error' WHERE sample_id = '1.SKB8.640193'; """) conn_handler.execute(sql) raw_data = RawData(3) params = Preprocessed454Params(1) prep_template = PrepTemplate(1) with self.assertRaises(ValueError): _get_preprocess_fasta_cmd(raw_data, prep_template, params)
def _to_dict(self): r"""Returns the categories and their values in a dictionary Returns ------- dict of {str: str} A dictionary of the form {category: value} """ conn_handler = SQLConnectionHandler() d = dict(conn_handler.execute_fetchone( "SELECT * from qiita.{0} WHERE " "sample_id=%s".format(self._dynamic_table), (self._id, ))) # Remove the sample_id, is not part of the metadata del d['sample_id'] return d
def extend(self, md_template): """Adds the given sample template to the current one Parameters ---------- md_template : DataFrame The metadata template file contents indexed by samples Ids """ conn_handler = SQLConnectionHandler() queue_name = "EXTEND_SAMPLE_TEMPLATE_%d" % self.id conn_handler.create_queue(queue_name) md_template = self._clean_validate_template(md_template, self.study_id, SAMPLE_TEMPLATE_COLUMNS) self._add_common_extend_steps_to_queue(md_template, conn_handler, queue_name) conn_handler.execute_queue(queue_name) self.generate_files()
def to_file(self, fp, samples=None): r"""Writes the MetadataTemplate to the file `fp` in tab-delimited format Parameters ---------- fp : str Path to the output file samples : set, optional If supplied, only the specified samples will be written to the file """ conn_handler = SQLConnectionHandler() metadata_map = self._transform_to_dict(conn_handler.execute_fetchall( "SELECT * FROM qiita.{0} WHERE {1}=%s".format(self._table, self._id_column), (self.id,))) dyn_vals = self._transform_to_dict(conn_handler.execute_fetchall( "SELECT * FROM qiita.{0}".format(self._table_name(self.id)))) for k in metadata_map: for key, value in viewitems(self.translate_cols_dict): id_ = metadata_map[k][key] metadata_map[k][value] = self.str_cols_handlers[key][id_] del metadata_map[k][key] metadata_map[k].update(dyn_vals[k]) metadata_map[k].pop('study_id', None) # Remove samples that are not in the samples list, if it was supplied if samples is not None: for sid, d in metadata_map.items(): if sid not in samples: metadata_map.pop(sid) # Write remaining samples to file headers = sorted(list(metadata_map.values())[0].keys()) with open(fp, 'w') as f: # First write the headers f.write("sample_name\t%s\n" % '\t'.join(headers)) # Write the values for each sample id for sid, d in sorted(metadata_map.items()): values = [str(d[h]) for h in headers] values.insert(0, sid) f.write("%s\n" % '\t'.join(values))
def test_get_preprocess_fasta_cmd_sff_run_prefix_match(self): # Test that the run prefixes in the prep_template and the file names # actually match and raise an error if not conn_handler = SQLConnectionHandler() sql = (""" INSERT INTO qiita.filepath (filepath_id, filepath, filepath_type_id, checksum, checksum_algorithm_id, data_directory_id) VALUES (19, '1_new.sff', 17, 852952723, 1, 5); INSERT INTO qiita.raw_filepath (raw_data_id , filepath_id) VALUES (3, 19); UPDATE qiita.prep_1 SET run_prefix='preprocess_test'; UPDATE qiita.prep_1 SET run_prefix='new' WHERE sample_id = '1.SKB8.640193'; """) conn_handler.execute(sql) raw_data = RawData(3) params = Preprocessed454Params(1) prep_template = PrepTemplate(1) obs_cmd, obs_output_dir = _get_preprocess_fasta_cmd( raw_data, prep_template, params) obs_cmds = obs_cmd.split('; ') # assumming that test_get_preprocess_fasta_cmd_sff_no_run_prefix is # working we only need to test for the commands being ran and # that n is valid self.assertEqual(len(obs_cmds), 9) self.assertTrue(obs_cmds[0].startswith('process_sff.py')) self.assertTrue(obs_cmds[1].startswith('process_sff.py')) self.assertTrue(obs_cmds[2].startswith('process_sff.py')) self.assertTrue(obs_cmds[3].startswith('split_libraries.py')) self.assertIn('-n 1', obs_cmds[3]) self.assertTrue(obs_cmds[4].startswith('split_libraries.py')) self.assertIn('-n 800000', obs_cmds[4]) self.assertTrue(obs_cmds[5].startswith('cat')) self.assertIn('split_library_log.txt', obs_cmds[5]) self.assertTrue(obs_cmds[6].startswith('cat')) self.assertIn('seqs.fna', obs_cmds[6]) self.assertEqual(len(obs_cmds[6].split(' ')), 5) self.assertTrue(obs_cmds[7].startswith('cat')) self.assertIn('seqs_filtered.qual', obs_cmds[7]) self.assertEqual(len(obs_cmds[7].split(' ')), 5)
def data_type(self, ret_id=False): """Returns the data_type or the data_type id Parameters ---------- ret_id : bool, optional If true, return the id instead of the string, default false. Returns ------- str or int string value of data_type or data_type_id if ret_id is True """ ret = "_id" if ret_id else "" conn_handler = SQLConnectionHandler() return conn_handler.execute_fetchone( "SELECT d.data_type{0} FROM qiita.data_type d JOIN " "qiita.prep_template p ON p.data_type_id = d.data_type_id WHERE " "p.prep_template_id=%s".format(ret), (self.id,))[0]
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_get_qiime_minimal_mapping_single_no_run_prefix(self): conn_handler = SQLConnectionHandler() sql = """DELETE FROM qiita.prep_columns WHERE prep_template_id = 1 AND column_name = 'run_prefix'; ALTER TABLE qiita.prep_1 DROP COLUMN run_prefix""" conn_handler.execute(sql) prep_template = PrepTemplate(1) prep_template.generate_files() out_dir = mkdtemp() obs_fps = _get_qiime_minimal_mapping(prep_template, out_dir) exp_fps = [join(out_dir, 'prep_1_MMF.txt')] # Check that the returned list is as expected self.assertEqual(obs_fps, exp_fps) # Check that the file exists self.assertTrue(exists(exp_fps[0])) # Check the contents of the file with open(exp_fps[0], "U") as f: self.assertEqual(f.read(), EXP_PREP)
class TestHandlerBase(AsyncHTTPTestCase): database = False def get_app(self): BaseHandler.get_current_user = Mock(return_value="*****@*****.**") self.app = Application() return self.app def setUp(self): if self.database: self.conn_handler = SQLConnectionHandler() # Drop the schema self.conn_handler.execute("DROP SCHEMA qiita CASCADE") # Create the schema with open(LAYOUT_FP, 'U') as f: self.conn_handler.execute(f.read()) # Initialize the database with open(INITIALIZE_FP, 'U') as f: self.conn_handler.execute(f.read()) # Populate the database with open(POPULATE_FP, 'U') as f: self.conn_handler.execute(f.read()) super(TestHandlerBase, self).setUp() def tearDown(self): if self.database: del self.conn_handler # helpers from http://www.peterbe.com/plog/tricks-asynchttpclient-tornado def get(self, url, data=None, headers=None, doseq=True): if data is not None: if isinstance(data, dict): data = urlencode(data, doseq=doseq) if '?' in url: url += '&%s' % data else: url += '?%s' % data return self._fetch(url, 'GET', headers=headers) def post(self, url, data, headers=None, doseq=True): if data is not None: if isinstance(data, dict): data = urlencode(data, doseq=doseq) return self._fetch(url, 'POST', data, headers) def _fetch(self, url, method, data=None, headers=None): self.http_client.fetch(self.get_url(url), self.stop, method=method, body=data, headers=headers) return self.wait()
class TestHandlerBase(AsyncHTTPTestCase): database = False conn_handler = SQLConnectionHandler() app = Application() def get_app(self): BaseHandler.get_current_user = Mock(return_value=User("*****@*****.**")) return self.app def setUp(self): if self.database: # First, we check that we are not in a production environment # It is possible that we are connecting to a production database test_db = self.conn_handler.execute_fetchone( "SELECT test FROM settings")[0] # Or the loaded config file belongs to a production environment if not qiita_config.test_environment or not test_db: raise RuntimeError("Working in a production environment. Not " "executing the tests to keep the production" " database safe.") # Drop the schema and rebuild the test database drop_and_rebuild_tst_database(self.conn_handler) super(TestHandlerBase, self).setUp() # helpers from http://www.peterbe.com/plog/tricks-asynchttpclient-tornado def get(self, url, data=None, headers=None, doseq=True): if data is not None: if isinstance(data, dict): data = urlencode(data, doseq=doseq) if '?' in url: url += '&%s' % data else: url += '?%s' % data return self._fetch(url, 'GET', headers=headers) def post(self, url, data, headers=None, doseq=True): if data is not None: if isinstance(data, dict): data = urlencode(data, doseq=doseq) return self._fetch(url, 'POST', data, headers) def _fetch(self, url, method, data=None, headers=None): self.http_client.fetch(self.get_url(url), self.stop, method=method, body=data, headers=headers) return self.wait()
def reset_test_database(wrapped_fn): """Decorator that drops the qiita schema, rebuilds and repopulates the schema with test data, then executes wrapped_fn """ conn_handler = SQLConnectionHandler() def decorated_wrapped_fn(*args, **kwargs): # Drop the schema conn_handler.execute("DROP SCHEMA qiita CASCADE") # Create the schema with open(LAYOUT_FP, 'U') as f: conn_handler.execute(f.read()) # Initialize the database with open(INITIALIZE_FP, 'U') as f: conn_handler.execute(f.read()) # Populate the database with open(POPULATE_FP, 'U') as f: conn_handler.execute(f.read()) # Execute the wrapped function return wrapped_fn(*args, **kwargs) return decorated_wrapped_fn
def setUp(self): super(DecoratedClass, self).setUp() self.conn_handler = SQLConnectionHandler()
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())
# 23 Nov, 2014 # This patch creates all the qiime mapping files for the existing # prep templates from qiita_db.util import get_mountpoint from qiita_db.sql_connection import SQLConnectionHandler from qiita_db.metadata_template import PrepTemplate conn_handler = SQLConnectionHandler() _id, fp_base = get_mountpoint('templates')[0] for prep_template_id in conn_handler.execute_fetchall( "SELECT prep_template_id FROM qiita.prep_template"): prep_template_id = prep_template_id[0] pt = PrepTemplate(prep_template_id) study_id = pt.study_id for _, fpt in pt.get_filepaths(): pt.create_qiime_mapping_file(fpt)
# Feberuary 7, 2015 # This patch recreates all the QIIME mapping files to avoid lower/upper case # problems. See https://github.com/biocore/qiita/issues/799 # # heavily based on 7.py from os.path import basename from skbio.util import flatten from qiita_db.sql_connection import SQLConnectionHandler from qiita_db.metadata_template import PrepTemplate conn_handler = SQLConnectionHandler() sql = "SELECT prep_template_id FROM qiita.prep_template" all_ids = conn_handler.execute_fetchall(sql) q_name = 'unlink-bad-mapping-files' conn_handler.create_queue(q_name) # remove all the bad mapping files for prep_template_id in all_ids: prep_template_id = prep_template_id[0] pt = PrepTemplate(prep_template_id) fps = pt.get_filepaths() # get the QIIME mapping file, note that the way to figure out what is and # what's not a qiime mapping file is to check for the existance of the # word qiime in the basename of the file path, hacky but that's the way
def postgres_test(**kwargs): """Open a connection and query postgres""" from qiita_db.sql_connection import SQLConnectionHandler c = SQLConnectionHandler() return c.execute_fetchone("SELECT 42")[0]