def test_validates_text_parameters(self): schema = [{"name": "bar", "type": "text"}] query = ParameterizedQuery("foo {{bar}}", schema) query.apply({"bar": u"baz"}) self.assertEquals("foo baz", query.text)
def test_validates_number_parameters(self): schema = [{"name": "bar", "type": "number"}] query = ParameterizedQuery("foo {{bar}}", schema) query.apply({"bar": 7}) self.assertEquals("foo 7", query.text)
def test_validates_query_parameters(self, _): schema = [{"name": "bar", "type": "query", "queryId": 1}] query = ParameterizedQuery("foo {{bar}}", schema) query.apply({"bar": "baz"}) self.assertEquals("foo baz", query.text)
def test_validates_date_parameters(self): schema = [{"name": "bar", "type": "date"}] query = ParameterizedQuery("foo {{bar}}", schema) query.apply({"bar": "2000-01-01 12:00:00"}) self.assertEquals("foo 2000-01-01 12:00:00", query.text)
def test_validation_accepts_integer_values_for_dropdowns(self, _): schema = [{"name": "bar", "type": "query", "queryId": 1}] query = ParameterizedQuery("foo {{bar}}", schema) query.apply({"bar": 1}) self.assertEquals("foo 1", query.text)
def test_validates_date_range_parameters(self): schema = [{"name": "bar", "type": "date-range"}] query = ParameterizedQuery("foo {{bar.start}} {{bar.end}}", schema) query.apply({"bar": {"start": "2000-01-01 12:00:00", "end": "2000-12-31 12:00:00"}}) self.assertEquals("foo 2000-01-01 12:00:00 2000-12-31 12:00:00", query.text)
def test_validates_enum_parameters(self): schema = [{"name": "bar", "type": "enum", "enumOptions": ["baz", "qux"]}] query = ParameterizedQuery("foo {{bar}}", schema) query.apply({"bar": "baz"}) self.assertEquals("foo baz", query.text)
def test_raises_on_unlisted_enum_value_parameters(self): schema = [{ "name": "bar", "type": "enum", "enumOptions": ["baz", "qux"] }] query = ParameterizedQuery("foo", schema) with pytest.raises(InvalidParameterError): query.apply({"bar": "shlomo"})
def run_query(data_source, parameter_values, query_text, query_id, max_age=0): if data_source.paused: if data_source.pause_reason: message = '{} is paused ({}). Please try later.'.format( data_source.name, data_source.pause_reason) else: message = '{} is paused. Please try later.'.format( data_source.name) return error_response(message) query = ParameterizedQuery(query_text).apply(parameter_values) if query.missing_params: return error_response(u'Missing parameter value for: {}'.format( u", ".join(query.missing_params))) if max_age == 0: query_result = None else: query_result = models.QueryResult.get_latest(data_source, query.text, max_age) if query_result: return {'query_result': query_result.to_dict()} else: job = enqueue_query(query.text, data_source, current_user.id, metadata={ "Username": current_user.email, "Query ID": query_id }) return {'job': job.to_dict()}
def test_finds_all_params(self): query = ParameterizedQuery(u"SELECT {{param}} FROM {{table}}").apply({ 'param': 'value', 'table': 'value' }) self.assertEqual(set([]), query.missing_params)
def test_handles_objects(self): query = ParameterizedQuery( u"SELECT * FROM USERS WHERE created_at between '{{ created_at.start }}' and '{{ created_at.end }}'" ).apply({'created_at': { 'start': 1, 'end': 2 }}) self.assertEqual(set([]), query.missing_params)
def test_handles_nested_params(self): query = ParameterizedQuery( u"SELECT {{param}}, {{param}} FROM {{table}} -- {{#test}} {{nested_param}} {{/test}}" ).apply({ 'param': 'value', 'table': 'value' }) self.assertEqual(set(['test', 'nested_param']), query.missing_params)
def post(self): """ Execute a query (or retrieve recent results). :qparam string query: The query text to execute :qparam number query_id: The query object to update with the result (optional) :qparam number max_age: If query results less than `max_age` seconds old are available, return them, otherwise execute the query; if omitted or -1, returns any cached result, or executes if not available. Set to zero to always execute. :qparam number data_source_id: ID of data source to query :qparam object parameters: A set of parameter values to apply to the query. """ params = request.get_json(force=True) query = params['query'] max_age = params.get('max_age', -1) # max_age might have the value of None, in which case calling int(None) will fail if max_age is None: max_age = -1 max_age = int(max_age) query_id = params.get('query_id', 'adhoc') parameters = params.get('parameters', collect_parameters_from_request(request.args)) parameterized_query = ParameterizedQuery(query) data_source = models.DataSource.get_by_id_and_org( params.get('data_source_id'), self.current_org) if not has_access(data_source.groups, self.current_user, not_view_only): return { 'job': { 'status': 4, 'error': 'You do not have permission to run queries with this data source.' } }, 403 self.record_event({ 'action': 'execute_query', 'object_id': data_source.id, 'object_type': 'data_source', 'query': query, 'query_id': query_id, 'parameters': parameters }) return run_query(parameterized_query, parameters, data_source, query_id, max_age)
def run_query_sync(data_source, parameter_values, query_text, max_age=0): query = ParameterizedQuery(query_text).apply(parameter_values) if query.missing_params: raise Exception('Missing parameter value for: {}'.format(", ".join( query.missing_params))) if max_age <= 0: query_result = None else: query_result = models.QueryResult.get_latest(data_source, query.text, max_age) query_hash = gen_query_hash(query.text) if query_result: logging.info("Returning cached result for query %s" % query_hash) return query_result try: started_at = time.time() data, error = data_source.query_runner.run_query( query.text, current_user) if error: logging.info('got bak error') logging.info(error) return None run_time = time.time() - started_at query_result, updated_query_ids = models.QueryResult.store_result( data_source.org_id, data_source, query_hash, query.text, data, run_time, utcnow()) models.db.session.commit() return query_result except Exception as e: if max_age > 0: abort( 404, message= "Unable to get result from the database, and no cached query result found." ) else: abort(503, message="Unable to get result from the database.") return None
def post(self, query_id): """ Execute a query, updating the query object with the results. :param query_id: ID of query to execute Responds with query task details. """ # TODO: this should actually check for permissions, but because currently you can only # get here either with a user API key or a query one, we can just check whether it's # an api key (meaning this is a query API key, which only grants read access). if self.current_user.is_api_user(): abort(403, message="Please use a user API key.") query = get_object_or_404(models.Query.get_by_id_and_org, query_id, self.current_org) require_access(query.groups, self.current_user, not_view_only) parameter_values = collect_parameters_from_request(request.args) parameterized_query = ParameterizedQuery(query.query_text) return run_query(parameterized_query, parameter_values, query.data_source, query.id)
def parameterized(self): return ParameterizedQuery(self.query_text, self.options.get("parameters", []))
def test_finds_all_params_when_missing(self): query = ParameterizedQuery(u"SELECT {{param}} FROM {{table}}") self.assertEqual(set(['param', 'table']), query.missing_params)
def test_raises_on_invalid_date_parameters(self): schema = [{"name": "bar", "type": "date"}] query = ParameterizedQuery("foo", schema) with pytest.raises(InvalidParameterError): query.apply({"bar": "baz"})
def test_returns_empty_list_for_regular_query(self): query = ParameterizedQuery(u"SELECT 1") self.assertEqual(set([]), query.missing_params)
def test_raises_on_unlisted_query_value_parameters(self, _): schema = [{"name": "bar", "type": "query", "queryId": 1}] query = ParameterizedQuery("foo", schema) with pytest.raises(InvalidParameterError): query.apply({"bar": "shlomo"})
def test_raises_on_parameters_not_in_schema(self): schema = [{"name": "bar", "type": "text"}] query = ParameterizedQuery("foo", schema) with pytest.raises(InvalidParameterError): query.apply({"qux": 7})
def test_raises_on_unexpected_param_types(self): schema = [{"name": "bar", "type": "burrito"}] query = ParameterizedQuery("foo", schema) with pytest.raises(InvalidParameterError): query.apply({"bar": "baz"})
def test_is_safe_if_not_expecting_any_parameters(self): schema = [] query = ParameterizedQuery("foo", schema) self.assertTrue(query.is_safe)
def test_is_safe_if_not_expecting_text_parameter(self): schema = [{"name": "bar", "type": "number"}] query = ParameterizedQuery("foo", schema) self.assertTrue(query.is_safe)