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)