def get(self, query_id=None, query_result_id=None, filetype="json"):
if query_result_id:
query_result = get_object_or_404(
models.QueryResult.get_by_id_and_org, query_result_id,
self.current_org)
# Look for queries matching this result whose data source is 'Query Results'.
if (models.Query.query.join(models.DataSource).filter(
models.Query.query_hash == query_result.query_hash,
models.DataSource.type == "results",
).first()):
table_names = extract_table_names(query_result.query_text)
for table_name in table_names:
# Look for query IDs being accessed.
if table_name.startswith("query_"):
try:
qid = int(table_name.split("_", 1)[1])
except ValueError:
# If it's not "query_NNN" it can't affect our permissions check here.
continue
upstream_q = models.Query.query.filter(
models.Query.id == qid).first()
if upstream_q is None:
continue
# If the user making this request doesn't have permission to
# view the query results being accessed in this query, deny
# access.
require_access(upstream_q.data_source.groups,
self.current_user, view_only)
require_access(query_result.data_source.groups, self.current_user,
view_only)
return super(StmoQueryResultResource,
self).get(query_id, query_result_id, filetype)
def add_query(self, query_def):
data_source = models.DataSource.get_by_id_and_org(
query_def.pop('data_source_id'), self.current_org)
require_access(data_source.groups, self.current_user, not_view_only)
for field in [
'id', 'created_at', 'api_key', 'visualizations',
'latest_query_data', 'last_modified_by'
]:
query_def.pop(field, None)
# plain text query
query_def['query_text'] = query_def.pop('query')
query_def['user'] = self.current_user
query_def['data_source'] = data_source
query_def['org'] = self.current_org
query_def['is_draft'] = True
query = models.Query.create(**query_def)
models.db.session.add(query)
models.db.session.commit()
self.record_event({
'action': 'create',
'object_id': query.id,
'object_type': 'query'
})
return query
def post(self):
req = request.get_json(True)
require_fields(req, ('options', 'name', 'query_id'))
query = models.Query.get_by_id_and_org(req['query_id'],
self.current_org)
require_access(query.groups, self.current_user, view_only)
alert = models.Alert(name=req['name'],
query_rel=query,
user=self.current_user,
options=req['options'])
models.db.session.add(alert)
models.db.session.flush()
models.db.session.commit()
self.record_event({
'action': 'create',
'timestamp': int(time.time()),
'object_id': alert.id,
'object_type': 'alert'
})
return alert.to_dict()
def post(self):
"""
Create a new query.
:<json number data_source_id: The ID of the data source this query will run on
:<json string query: Query text
:<json string name:
:<json string description:
:<json string schedule: Schedule interval, in seconds, for repeated execution of this query
:<json object options: Query options
.. _query-response-label:
:>json number id: Query ID
:>json number latest_query_data_id: ID for latest output data from this query
:>json string name:
:>json string description:
:>json string query: Query text
:>json string query_hash: Hash of query text
:>json string schedule: Schedule interval, in seconds, for repeated execution of this query
:>json string api_key: Key for public access to this query's results.
:>json boolean is_archived: Whether this query is displayed in indexes and search results or not.
:>json boolean is_draft: Whether this query is a draft or not
:>json string updated_at: Time of last modification, in ISO format
:>json string created_at: Time of creation, in ISO format
:>json number data_source_id: ID of the data source this query will run on
:>json object options: Query options
:>json number version: Revision version (for update conflict avoidance)
:>json number user_id: ID of query creator
:>json number last_modified_by_id: ID of user who last modified this query
:>json string retrieved_at: Time when query results were last retrieved, in ISO format (may be null)
:>json number runtime: Runtime of last query execution, in seconds (may be null)
"""
query_def = request.get_json(force=True)
data_source = models.DataSource.get_by_id_and_org(
query_def.pop('data_source_id'), self.current_org)
require_access(data_source.groups, self.current_user, not_view_only)
for field in [
'id', 'created_at', 'api_key', 'visualizations',
'latest_query_data', 'last_modified_by'
]:
query_def.pop(field, None)
query_def['query_text'] = query_def.pop('query')
query_def['user'] = self.current_user
query_def['data_source'] = data_source
query_def['org'] = self.current_org
query_def['is_draft'] = True
query = models.Query.create(**query_def)
models.db.session.add(query)
models.db.session.commit()
self.record_event({
'action': 'create',
'object_id': query.id,
'object_type': 'query'
})
return QuerySerializer(query).serialize()
def get(self, query_id=None, query_result_id=None, filetype='json'):
# TODO:
# This method handles two cases: retrieving result by id & retrieving result by query id.
# They need to be split, as they have different logic (for example, retrieving by query id
# should check for query parameters and shouldn't cache the result).
should_cache = query_result_id is not None
if query_result_id is None and query_id is not None:
query = get_object_or_404(models.Query.get_by_id_and_org, query_id,
self.current_org)
if query:
query_result_id = query._data['latest_query_data']
if query_result_id:
query_result = get_object_or_404(
models.QueryResult.get_by_id_and_org, query_result_id,
self.current_org)
if query_result:
require_access(query_result.data_source.groups, self.current_user,
view_only)
if isinstance(self.current_user, models.ApiUser):
event = {
'user_id': None,
'org_id': self.current_org.id,
'action': 'api_get',
'timestamp': int(time.time()),
'api_key': self.current_user.name,
'file_type': filetype,
'user_agent': request.user_agent.string,
'ip': request.remote_addr
}
if query_id:
event['object_type'] = 'query'
event['object_id'] = query_id
else:
event['object_type'] = 'query_result'
event['object_id'] = query_result_id
record_event.delay(event)
if filetype == 'json':
response = self.make_json_response(query_result)
elif filetype == 'xlsx':
response = self.make_excel_response(query_result)
else:
response = self.make_csv_response(query_result)
if len(settings.ACCESS_CONTROL_ALLOW_ORIGIN) > 0:
self.add_cors_headers(response.headers)
if should_cache:
response.headers.add_header('Cache-Control',
'max-age=%d' % ONE_YEAR)
return response
else:
abort(404)
def get(self, query_id):
query = get_object_or_404(models.Query.get_by_id_and_org, query_id, self.current_org)
require_access(query.data_source, current_user, view_only)
try:
return dropdown_values(query_id, self.current_org)
except QueryDetachedFromDataSourceError as e:
abort(400, message=e.message)
def embed(query_id, visualization_id, org_slug=None):
# TODO: add event for embed access
query = models.Query.get_by_id_and_org(query_id, current_org)
require_access(query.groups, current_user, view_only)
vis = query.visualizations.where(models.Visualization.id == visualization_id).first()
qr = {}
if vis is not None:
vis = vis.to_dict()
qr = query.latest_query_data
if qr is None:
abort(400, message="No Results for this query")
else:
qr = qr.to_dict()
else:
abort(404, message="Visualization not found.")
client_config = {}
client_config.update(settings.COMMON_CLIENT_CONFIG)
qr = project(qr, ('data', 'id', 'retrieved_at'))
vis = project(vis, ('description', 'name', 'id', 'options', 'query', 'type', 'updated_at'))
vis['query'] = project(vis, ('created_at', 'description', 'name', 'id', 'latest_query_data_id', 'name', 'updated_at'))
return render_template("embed.html",
name=settings.NAME,
base_href=base_href(),
client_config=json_dumps(client_config),
visualization=json_dumps(vis),
query_result=json_dumps(qr),
analytics=settings.ANALYTICS)
def get(self, data_source_id):
data_source = get_object_or_404(models.DataSource.get_by_id_and_org, data_source_id, self.current_org)
require_access(data_source.groups, self.current_user, view_only)
refresh = request.args.get('refresh') is not None
schema = data_source.get_schema(refresh)
return schema
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, self.current_user, not_view_only)
parameter_values = collect_parameters_from_request(request.args)
parameterized_query = ParameterizedQuery(query.query_text, org=self.current_org)
should_apply_auto_limit = query.options.get("apply_auto_limit", False)
return run_query(
parameterized_query, parameter_values, query.data_source, query.id, should_apply_auto_limit
)
def get(self, data_source_id):
data_source = get_object_or_404(models.DataSource.get_by_id_and_org, data_source_id, self.current_org)
require_access(data_source.groups, self.current_user, view_only)
refresh = request.args.get('refresh') is not None
schema = data_source.get_schema(refresh)
return schema
def post(self, alert_id):
req = request.get_json(True)
alert = models.Alert.get_by_id_and_org(alert_id, self.current_org)
require_access(alert.groups, self.current_user, view_only)
kwargs = {'alert': alert, 'user': self.current_user}
if 'destination_id' in req:
destination = models.NotificationDestination.get_by_id_and_org(req['destination_id'], self.current_org)
kwargs['destination'] = destination
subscription = models.AlertSubscription(**kwargs)
models.db.session.add(subscription)
models.db.session.commit()
self.record_event({
'action': 'subscribe',
'timestamp': int(time.time()),
'object_id': alert_id,
'object_type': 'alert',
'destination': req.get('destination_id')
})
d = subscription.to_dict()
return d
def get(self, data_source_id):
data_source = get_object_or_404(models.DataSource.get_by_id_and_org,
data_source_id, self.current_org)
require_access(data_source.groups, self.current_user, view_only)
schema = data_source.get_schema()
return schema
def get(self, alert_id):
alert_id = int(alert_id)
alert = models.Alert.get_by_id_and_org(alert_id, self.current_org)
require_access(alert.groups, self.current_user, view_only)
subscriptions = models.AlertSubscription.all(alert_id)
return [s.to_dict() for s in subscriptions]
def post(self):
req = request.get_json(True)
require_fields(req, ('options', 'name', 'query_id'))
query = models.Query.get_by_id_and_org(req['query_id'],
self.current_org)
require_access(query.groups, self.current_user, view_only)
alert = models.Alert(
name=req['name'],
query_rel=query,
user=self.current_user,
rearm=req.get('rearm'),
options=req['options']
)
models.db.session.add(alert)
models.db.session.flush()
models.db.session.commit()
self.record_event({
'action': 'create',
'timestamp': int(time.time()),
'object_id': alert.id,
'object_type': 'alert'
})
return serialize_alert(alert)
def post(self):
# 忽视mimetype类型，强制为Json类型
req = request.get_json(True)
# 判断请求参数
require_fields(req, ('options', 'name', 'query_id'))
#### 根据哪个query_id创建的alert
query = Query.get_by_id_and_org(req['query_id'], self.current_org)
## 权限系统？？？？？？？？？？？？？？
require_access(query.groups, self.current_user, view_only)
# query_rel 是 relationShip， 传对方的行对象,需要提供
# 但是不用提供外键对应的列
alert = Alert(name=req['name'],
query_rel=query,
user=self.current_user,
rearm=req.get('rearm'),
options=req['options'])
session.add(alert)
session.flush()
session.commit()
self.record_event({
'action': 'create',
'timestamp': int(time.time()),
'object_id': alert.id,
'object_type': 'alert'
})
### RESETFUL 规范， 不能只是响应个200，就完事了， 最好把创建好的东西返回给前端
return serialize_alert(alert)
def get(self, query_id):
q = get_object_or_404(models.Query.get_by_id_and_org, query_id, self.current_org)
require_access(q.groups, self.current_user, view_only)
result = q.to_dict(with_visualizations=True)
result['can_edit'] = can_modify(q, self.current_user)
return result
def get(self, data_source_id):
data_source = get_object_or_404(models.DataSource.get_by_id_and_org,
data_source_id, self.current_org)
require_access(data_source, self.current_user, view_only)
refresh = request.args.get('refresh') is not None
response = {}
try:
if refresh:
refresh_schema.apply_async(
args=(data_source.id, ),
queue=settings.SCHEMAS_REFRESH_QUEUE)
response['schema'] = data_source.get_schema()
except NotSupported:
response['error'] = {
'code': 1,
'message':
'Data source type does not support retrieving schema'
}
except Exception:
response['error'] = {
'code': 2,
'message': 'Error retrieving schema.'
}
return response
def post(self):
"""
Add a widget to a dashboard.
:<json number dashboard_id: The ID for the dashboard being added to
:<json visualization_id: The ID of the visualization to put in this widget
:<json object options: Widget options
:<json string text: Text box contents
:<json number width: Width for widget display
:>json object widget: The created widget
"""
widget_properties = request.get_json(force=True)
dashboard = models.Dashboard.get_by_id_and_org(widget_properties.pop('dashboard_id'), self.current_org)
require_object_modify_permission(dashboard, self.current_user)
widget_properties['options'] = json.dumps(widget_properties['options'])
widget_properties.pop('id', None)
widget_properties['dashboard'] = dashboard
visualization_id = widget_properties.pop('visualization_id')
if visualization_id:
visualization = models.Visualization.get_by_id_and_org(visualization_id, self.current_org)
require_access(visualization.query_rel.groups, self.current_user, view_only)
else:
visualization = None
widget_properties['visualization'] = visualization
widget = models.Widget(**widget_properties)
models.db.session.add(widget)
models.db.session.commit()
models.db.session.commit()
return serialize_widget(widget)
def post(self):
req = request.get_json(True)
require_fields(req, ('options', 'name', 'query_id'))
query = models.Query.get_by_id_and_org(req['query_id'],
self.current_org)
require_access(query.groups, self.current_user, view_only)
alert = models.Alert.create(name=req['name'],
query=query,
user=self.current_user,
options=req['options'])
self.record_event({
'action': 'create',
'timestamp': int(time.time()),
'object_id': alert.id,
'object_type': 'alert'
})
# TODO: should be in model?
models.AlertSubscription.create(alert=alert, user=self.current_user)
self.record_event({
'action': 'subscribe',
'timestamp': int(time.time()),
'object_id': alert.id,
'object_type': 'alert'
})
return alert.to_dict()
def post(self, query_id):
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)
return run_query(query.data_source, parameter_values, query.query, query.id)
def post(self):
query_def = request.get_json(force=True)
data_source = models.DataSource.get_by_id_and_org(
query_def.pop('data_source_id'), self.current_org)
require_access(data_source.groups, self.current_user, not_view_only)
for field in [
'id', 'created_at', 'api_key', 'visualizations',
'latest_query_data', 'last_modified_by'
]:
query_def.pop(field, None)
# If we already executed this query, save the query result reference
if 'latest_query_data_id' in query_def:
query_def['latest_query_data'] = query_def.pop(
'latest_query_data_id')
query_def['user'] = self.current_user
query_def['data_source'] = data_source
query_def['org'] = self.current_org
query = models.Query.create(**query_def)
self.record_event({
'action': 'create',
'object_id': query.id,
'object_type': 'query'
})
return query.to_dict()
def get(self, visualization_id):
"""
Retrieve a visualization.
:param visualization_id: ID of visualization to fetch
Responds with the :ref:`visualization <visualization-response-label>` contents.
"""
vis = get_object_or_404(models.Visualization.get_by_id,
visualization_id)
require_access(vis.query_rel, self.current_user, view_only)
result = serialize_visualization(vis, True)
api_key = models.ApiKey.get_by_object(vis)
if api_key:
result['api_key'] = api_key.api_key
result['can_edit'] = can_modify(vis, self.current_user)
self.record_event({
'action': 'view',
'object_id': visualization_id,
'object_type': 'query',
})
return result
def post(self):
req = request.get_json(True)
require_fields(req, ('options', 'name', 'query_id'))
query = models.Query.get_by_id_and_org(req['query_id'], self.current_org)
require_access(query.groups, self.current_user, view_only)
alert = models.Alert.create(
name=req['name'],
query=query,
user=self.current_user,
options=req['options']
)
self.record_event({
'action': 'create',
'timestamp': int(time.time()),
'object_id': alert.id,
'object_type': 'alert'
})
# TODO: should be in model?
models.AlertSubscription.create(alert=alert, user=self.current_user)
self.record_event({
'action': 'subscribe',
'timestamp': int(time.time()),
'object_id': alert.id,
'object_type': 'alert'
})
return alert.to_dict()
def post(self):
req = request.get_json(True)
require_fields(req, ("options", "name", "query_id"))
query = models.Query.get_by_id_and_org(req["query_id"],
self.current_org)
require_access(query, self.current_user, view_only)
alert = models.Alert(
name=req["name"],
query_rel=query,
user=self.current_user,
rearm=req.get("rearm"),
options=req["options"],
)
models.db.session.add(alert)
models.db.session.flush()
models.db.session.commit()
self.record_event({
"action": "create",
"object_id": alert.id,
"object_type": "alert"
})
return serialize_alert(alert)
def post(self, alert_id):
req = request.get_json(True)
alert = models.Alert.get_by_id_and_org(alert_id, self.current_org)
require_access(alert.groups, self.current_user, view_only)
kwargs = {'alert': alert, 'user': self.current_user}
if 'destination_id' in req:
destination = models.NotificationDestination.get_by_id_and_org(
req['destination_id'], self.current_org)
kwargs['destination'] = destination
subscription = models.AlertSubscription(**kwargs)
models.db.session.add(subscription)
models.db.session.commit()
self.record_event({
'action': 'subscribe',
'timestamp': int(time.time()),
'object_id': alert_id,
'object_type': 'alert',
'destination': req.get('destination_id')
})
d = subscription.to_dict()
return d
def post(self, query_id):
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)
return run_query(query.data_source, parameter_values, query.query, query.id)
def get(self, alert_id):
alert_id = int(alert_id)
alert = models.Alert.get_by_id_and_org(alert_id, self.current_org)
require_access(alert.groups, self.current_user, view_only)
subscriptions = models.AlertSubscription.all(alert_id)
return [s.to_dict() for s in subscriptions]
def post(self, alert_id):
req = request.get_json(True)
alert = models.Alert.get_by_id_and_org(alert_id, self.current_org)
require_access(alert, self.current_user, view_only)
kwargs = {"alert": alert, "user": self.current_user}
if "destination_id" in req:
destination = models.NotificationDestination.get_by_id_and_org(
req["destination_id"], self.current_org)
kwargs["destination"] = destination
subscription = models.AlertSubscription(**kwargs)
models.db.session.add(subscription)
models.db.session.commit()
self.record_event({
"action": "subscribe",
"object_id": alert_id,
"object_type": "alert",
"destination": req.get("destination_id"),
})
d = subscription.to_dict()
return d
def post(self):
req = request.get_json(True)
require_fields(req, ('options', 'name', 'query_id'))
query = models.Query.get_by_id_and_org(req['query_id'],
self.current_org)
require_access(query, self.current_user, view_only)
alert = models.Alert(
name=req['name'],
query_rel=query,
user=self.current_user,
rearm=req.get('rearm'),
options=req['options'],
)
models.db.session.add(alert)
models.db.session.flush()
models.db.session.commit()
self.record_event({
'action': 'create',
'object_id': alert.id,
'object_type': 'alert'
})
return serialize_alert(alert)
def _load_result(query_id):
query = models.Query.get_by_id_and_org(query_id, current_org)
require_access(query.data_source.groups, current_user, view_only)
query_result = models.QueryResult.get_by_id_and_org(
query.latest_query_data_id, current_org)
return json_loads(query_result.data)
def get(self, query_id):
q = get_object_or_404(models.Query.get_by_id_and_org, query_id, self.current_org)
require_access(q.groups, self.current_user, view_only)
if q:
return q.to_dict(with_visualizations=True)
else:
abort(404, message="Query not found.")
def embed(query_id, visualization_id, org_slug=None):
# TODO: add event for embed access
query = models.Query.get_by_id_and_org(query_id, current_org)
require_access(query.groups, current_user, view_only)
vis = query.visualizations.where(
models.Visualization.id == visualization_id).first()
qr = {}
if vis is not None:
vis = vis.to_dict()
qr = query.latest_query_data
if qr is None:
abort(400, message="No Results for this query")
else:
qr = qr.to_dict()
else:
abort(404, message="Visualization not found.")
client_config = {}
client_config.update(settings.COMMON_CLIENT_CONFIG)
qr = project(qr, ('data', 'id', 'retrieved_at'))
vis = project(vis, ('description', 'name', 'id', 'options', 'query',
'type', 'updated_at'))
vis['query'] = project(vis, ('created_at', 'description', 'name', 'id',
'latest_query_data_id', 'name', 'updated_at'))
return render_template("embed.html",
name=settings.NAME,
base_href=base_href(),
client_config=json_dumps(client_config),
visualization=json_dumps(vis),
query_result=json_dumps(qr),
analytics=settings.ANALYTICS)
def post(self, query_id):
"""
Modify a query.
:param query_id: ID of query to update
:<json number data_source_id: The ID of the data source this query will run on
:<json string query: Query text
:<json string name:
:<json string description:
:<json string schedule: Schedule interval, in seconds, for repeated execution of this query
:<json object options: Query options
Responds with the updated :ref:`query <query-response-label>` object.
"""
query = get_object_or_404(models.Query.get_by_id_and_org, query_id,
self.current_org)
query_def = request.get_json(force=True)
require_object_modify_permission(query, self.current_user)
require_access_to_dropdown_queries(self.current_user, query_def)
for field in [
"id",
"created_at",
"api_key",
"visualizations",
"latest_query_data",
"user",
"last_modified_by",
"org",
]:
query_def.pop(field, None)
if "query" in query_def:
query_def["query_text"] = query_def.pop("query")
if "tags" in query_def:
query_def["tags"] = [tag for tag in query_def["tags"] if tag]
if "data_source_id" in query_def:
data_source = models.DataSource.get_by_id_and_org(
query_def["data_source_id"], self.current_org)
require_access(data_source, self.current_user, not_view_only)
query_def["last_modified_by"] = self.current_user
query_def["changed_by"] = self.current_user
# SQLAlchemy handles the case where a concurrent transaction beats us
# to the update. But we still have to make sure that we're not starting
# out behind.
if "version" in query_def and query_def["version"] != query.version:
abort(409)
try:
self.update_model(query, query_def)
models.db.session.commit()
except StaleDataError:
abort(409)
return QuerySerializer(query, with_visualizations=True).serialize()
def embed(query_id, visualization_id, org_slug=None):
query = models.Query.get_by_id_and_org(query_id, current_org)
require_access(query.groups, current_user, view_only)
vis = query.visualizations.where(
models.Visualization.id == visualization_id).first()
qr = {}
parameter_values = collect_parameters_from_request(request.args)
if vis is not None:
vis = vis.to_dict()
qr = query.latest_query_data
if settings.ALLOW_PARAMETERS_IN_EMBEDS == True and len(
parameter_values) > 0:
# run parameterized query
#
# WARNING: Note that the external query parameters
# are a potential risk of SQL injections.
#
max_age = int(request.args.get('maxAge', 0))
results = run_query_sync(query.data_source,
parameter_values,
query.query,
max_age=max_age)
if results is None:
abort(400, message="Unable to get results for this query")
else:
qr = {"data": json.loads(results)}
elif qr is None:
abort(400, message="No Results for this query")
else:
qr = qr.to_dict()
else:
abort(404, message="Visualization not found.")
record_event(
current_org, current_user, {
'action': 'view',
'object_id': visualization_id,
'object_type': 'visualization',
'query_id': query_id,
'embed': True,
'referer': request.headers.get('Referer')
})
client_config = {}
client_config.update(settings.COMMON_CLIENT_CONFIG)
qr = project(qr, ('data', 'id', 'retrieved_at'))
vis = project(vis, ('description', 'name', 'id', 'options', 'query',
'type', 'updated_at'))
vis['query'] = project(vis['query'],
('created_at', 'description', 'name', 'id',
'latest_query_data_id', 'name', 'updated_at'))
return render_template("embed.html",
client_config=json_dumps(client_config),
visualization=json_dumps(vis),
query_result=json_dumps(qr))
def get(self, query_id):
q = get_object_or_404(models.Query.get_by_id_and_org, query_id, self.current_org)
require_access(q.groups, self.current_user, view_only)
if q:
return q.to_dict(with_visualizations=True)
else:
abort(404, message="Query not found.")
def _fetch_rows(self, query_id):
query = models.Query.get_by_id_and_org(query_id, self.current_org)
require_access(query.data_source.groups, self.current_user, view_only)
query_result = models.QueryResult.get_by_id_and_org(
query.latest_query_data_id, self.current_org)
return json_loads(query_result.data)["rows"]
def get(self, query_id):
q = get_object_or_404(models.Query.get_by_id_and_org, query_id,
self.current_org)
require_access(q.groups, self.current_user, view_only)
result = q.to_dict(with_visualizations=True)
result['can_edit'] = can_modify(q, self.current_user)
return result
def post(self):
"""
Add a widget to a dashboard.
:<json number dashboard_id: The ID for the dashboard being added to
:<json visualization_id: The ID of the visualization to put in this widget
:<json object options: Widget options
:<json string text: Text box contents
:<json number width: Width for widget display
:>json object widget: The created widget
"""
widget_properties = request.get_json(force=True)
dashboard = models.Dashboard.get_by_id_and_org(
widget_properties.get('dashboard_id'), self.current_org)
flag = True
manage_boards = models.Dashboard.queries(self.current_org,
self.current_user.group_ids,
self.current_user.id)
for group_id in self.current_user.group_ids:
group = get_object_or_404(models.Group.get_by_id_and_org, group_id,
self.current_org)
try:
d = [
ds.to_dict(with_permissions_for=group)
for ds in manage_boards
]
for item in d:
if item['view_only'] == False and item[
'id'] == dashboard.id:
flag = False
except:
pass
if flag:
require_object_modify_permission(dashboard, self.current_user)
widget_properties['options'] = json_dumps(widget_properties['options'])
widget_properties.pop('id', None)
visualization_id = widget_properties.pop('visualization_id')
if visualization_id:
visualization = models.Visualization.get_by_id_and_org(
visualization_id, self.current_org)
require_access(visualization.query_rel, self.current_user,
view_only)
else:
visualization = None
widget_properties['visualization'] = visualization
widget = models.Widget(**widget_properties)
models.db.session.add(widget)
models.db.session.commit()
models.db.session.commit()
return serialize_widget(widget)
def post(self):
"""
Create a new query.
:<json number data_source_id: The ID of the data source this query will run on
:<json string query: Query text
:<json string name:
:<json string description:
:<json string schedule: Schedule interval, in seconds, for repeated execution of this query
:<json object options: Query options
.. _query-response-label:
:>json number id: Query ID
:>json number latest_query_data_id: ID for latest output data from this query
:>json string name:
:>json string description:
:>json string query: Query text
:>json string query_hash: Hash of query text
:>json string schedule: Schedule interval, in seconds, for repeated execution of this query
:>json string api_key: Key for public access to this query's results.
:>json boolean is_archived: Whether this query is displayed in indexes and search results or not.
:>json boolean is_draft: Whether this query is a draft or not
:>json string updated_at: Time of last modification, in ISO format
:>json string created_at: Time of creation, in ISO format
:>json number data_source_id: ID of the data source this query will run on
:>json object options: Query options
:>json number version: Revision version (for update conflict avoidance)
:>json number user_id: ID of query creator
:>json number last_modified_by_id: ID of user who last modified this query
:>json string retrieved_at: Time when query results were last retrieved, in ISO format (may be null)
:>json number runtime: Runtime of last query execution, in seconds (may be null)
"""
query_def = request.get_json(force=True)
data_source = models.DataSource.get_by_id_and_org(query_def.pop('data_source_id'), self.current_org)
require_access(data_source, self.current_user, not_view_only)
require_access_to_dropdown_queries(self.current_user, query_def)
for field in ['id', 'created_at', 'api_key', 'visualizations', 'latest_query_data', 'last_modified_by']:
query_def.pop(field, None)
query_def['query_text'] = query_def.pop('query')
query_def['user'] = self.current_user
query_def['data_source'] = data_source
query_def['org'] = self.current_org
query_def['is_draft'] = True
query = models.Query.create(**query_def)
models.db.session.add(query)
models.db.session.commit()
self.record_event({
'action': 'create',
'object_id': query.id,
'object_type': 'query'
})
return QuerySerializer(query, with_visualizations=True).serialize()
def get(self, query_id=None, query_result_id=None, filetype='json'):
# TODO:
# This method handles two cases: retrieving result by id & retrieving result by query id.
# They need to be split, as they have different logic (for example, retrieving by query id
# should check for query parameters and shouldn't cache the result).
should_cache = query_result_id is not None
if query_result_id is None and query_id is not None:
query = get_object_or_404(models.Query.get_by_id_and_org, query_id, self.current_org)
if query:
query_result_id = query._data['latest_query_data']
if query_result_id:
query_result = get_object_or_404(models.QueryResult.get_by_id_and_org, query_result_id, self.current_org)
else:
query_result = None
if query_result:
require_access(query_result.data_source.groups, self.current_user, view_only)
if isinstance(self.current_user, models.ApiUser):
event = {
'user_id': None,
'org_id': self.current_org.id,
'action': 'api_get',
'timestamp': int(time.time()),
'api_key': self.current_user.name,
'file_type': filetype,
'user_agent': request.user_agent.string,
'ip': request.remote_addr
}
if query_id:
event['object_type'] = 'query'
event['object_id'] = query_id
else:
event['object_type'] = 'query_result'
event['object_id'] = query_result_id
record_event.delay(event)
if filetype == 'json':
response = self.make_json_response(query_result)
elif filetype == 'xlsx':
response = self.make_excel_response(query_result)
else:
response = self.make_csv_response(query_result)
if len(settings.ACCESS_CONTROL_ALLOW_ORIGIN) > 0:
self.add_cors_headers(response.headers)
if should_cache:
response.headers.add_header('Cache-Control', 'max-age=%d' % ONE_YEAR)
return response
else:
abort(404, message='No cached result found for this query.')
def get(self, alert_id):
alert = get_object_or_404(models.Alert.get_by_id_and_org, alert_id, self.current_org)
require_access(alert, self.current_user, view_only)
self.record_event({
'action': 'view',
'object_id': alert.id,
'object_type': 'alert'
})
return serialize_alert(alert)
def get(self, query_id, dropdown_query_id):
query = get_object_or_404(models.Query.get_by_id_and_org, query_id, self.current_org)
require_access(query, current_user, view_only)
related_queries_ids = [p['queryId'] for p in query.parameters if p['type'] == 'query']
if int(dropdown_query_id) not in related_queries_ids:
dropdown_query = get_object_or_404(models.Query.get_by_id_and_org, dropdown_query_id, self.current_org)
require_access(dropdown_query.data_source, current_user, view_only)
return dropdown_values(dropdown_query_id)
def post(self):
"""
Add a widget to a dashboard.
:<json number dashboard_id: The ID for the dashboard being added to
:<json visualization_id: The ID of the visualization to put in this widget
:<json object options: Widget options
:<json string text: Text box contents
:<json number width: Width for widget display
:>json object widget: The created widget
:>json array layout: The new layout of the dashboard this widget was added to
:>json boolean new_row: Whether this widget was added on a new row or not
:>json number version: The revision number of the dashboard
"""
widget_properties = request.get_json(force=True)
dashboard = models.Dashboard.get_by_id_and_org(widget_properties.pop('dashboard_id'), self.current_org)
require_object_modify_permission(dashboard, self.current_user)
widget_properties['options'] = json.dumps(widget_properties['options'])
widget_properties.pop('id', None)
widget_properties['dashboard'] = dashboard
visualization_id = widget_properties.pop('visualization_id')
if visualization_id:
visualization = models.Visualization.get_by_id_and_org(visualization_id, self.current_org)
require_access(visualization.query_rel.groups, self.current_user, view_only)
else:
visualization = None
widget_properties['visualization'] = visualization
widget = models.Widget(**widget_properties)
models.db.session.add(widget)
models.db.session.commit()
layout = json.loads(widget.dashboard.layout)
new_row = True
if len(layout) == 0 or widget.width == 2:
layout.append([widget.id])
elif len(layout[-1]) == 1:
neighbour_widget = models.Widget.query.get(layout[-1][0])
if neighbour_widget.width == 1:
layout[-1].append(widget.id)
new_row = False
else:
layout.append([widget.id])
else:
layout.append([widget.id])
widget.dashboard.layout = json.dumps(layout)
models.db.session.add(widget.dashboard)
models.db.session.commit()
return {'widget': widget.to_dict(), 'layout': layout, 'new_row': new_row, 'version': dashboard.version}
def embed(query_id, visualization_id, org_slug=None):
query = models.Query.get_by_id_and_org(query_id, current_org)
require_access(query.groups, current_user, view_only)
vis = query.visualizations.where(models.Visualization.id == visualization_id).first()
qr = {}
parameter_values = collect_parameters_from_request(request.args)
if vis is not None:
vis = vis.to_dict()
qr = query.latest_query_data
logging.info("jonhere")
logging.info( settings.ALLOW_PARAMETERS_IN_EMBEDS)
if settings.ALLOW_PARAMETERS_IN_EMBEDS == True and len(parameter_values) > 0:
#abort(404,message="jlk")
# run parameterized query
#
# WARNING: Note that the external query parameters
# are a potential risk of SQL injections.
#
results = run_query_sync(query.data_source, parameter_values, query.query)
logging.info("jonhere2")
logging.info("results")
if results is None:
abort(400, message="Unable to get results for this query")
else:
qr = {"data": json.loads(results)}
elif qr is None:
abort(400, message="No Results for this query")
else:
qr = qr.to_dict()
else:
abort(404, message="Visualization not found.")
record_event(current_org, current_user, {
'action': 'view',
'object_id': visualization_id,
'object_type': 'visualization',
'query_id': query_id,
'embed': True,
'referer': request.headers.get('Referer')
})
client_config = {}
client_config.update(settings.COMMON_CLIENT_CONFIG)
qr = project(qr, ('data', 'id', 'retrieved_at'))
vis = project(vis, ('description', 'name', 'id', 'options', 'query', 'type', 'updated_at'))
vis['query'] = project(vis['query'], ('created_at', 'description', 'name', 'id', 'latest_query_data_id', 'name', 'updated_at'))
return render_template("embed.html",
client_config=json_dumps(client_config),
visualization=json_dumps(vis),
query_result=json_dumps(qr))
def require_access_to_dropdown_queries(user, query_def):
parameters = query_def.get('options', {}).get('parameters', [])
dropdown_query_ids = [str(p['queryId']) for p in parameters if p['type'] == 'query']
if dropdown_query_ids:
groups = models.Query.all_groups_for_query_ids(dropdown_query_ids)
if len(groups) < len(dropdown_query_ids):
abort(400, message="You are trying to associate a dropdown query that does not have a matching group."
"Please verify the dropdown query id you are trying to associate with this query.")
require_access(dict(groups), user, view_only)
def post(self, query_id):
"""
Creates a new query, copying the query text from an existing one.
:param query_id: ID of query to fork
Responds with created :ref:`query <query-response-label>` object.
"""
query = get_object_or_404(models.Query.get_by_id_and_org, query_id, self.current_org)
require_access(query.data_source.groups, self.current_user, not_view_only)
forked_query = query.fork(self.current_user)
models.db.session.commit()
return forked_query.to_dict(with_visualizations=True)
def post(self, alert_id):
alert = models.Alert.get_by_id_and_org(alert_id, self.current_org)
require_access(alert.groups, self.current_user, view_only)
subscription = models.AlertSubscription.create(alert=alert_id, user=self.current_user)
self.record_event({
'action': 'subscribe',
'timestamp': int(time.time()),
'object_id': alert_id,
'object_type': 'alert'
})
return subscription.to_dict()
def get(self, query_id):
"""
Retrieve a query.
:param query_id: ID of query to fetch
Responds with the :ref:`query <query-response-label>` contents.
"""
q = get_object_or_404(models.Query.get_by_id_and_org, query_id, self.current_org)
require_access(q.groups, self.current_user, view_only)
result = q.to_dict(with_visualizations=True)
result['can_edit'] = can_modify(q, self.current_user)
return result
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.
"""
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)
return run_query(query.data_source, parameter_values, query.query_text, query.id)
def post(self):
query_def = request.get_json(force=True)
data_source = models.DataSource.get_by_id_and_org(query_def.pop('data_source_id'), self.current_org)
require_access(data_source.groups, self.current_user, not_view_only)
for field in ['id', 'created_at', 'api_key', 'visualizations', 'latest_query_data', 'last_modified_by']:
query_def.pop(field, None)
query_def['user'] = self.current_user
query_def['data_source'] = data_source
query_def['org'] = self.current_org
query = models.Query.create(**query_def)
return query.to_dict()
def delete(self, query_id):
query = get_object_or_404(models.Query.get_by_id_and_org, query_id, self.current_org)
require_access(query.groups, self.current_user, view_only)
models.Favorite.query.filter(
models.Favorite.object_id == query_id,
models.Favorite.object_type == u'Query',
models.Favorite.user==self.current_user,
).delete()
models.db.session.commit()
self.record_event({
'action': 'favorite',
'object_id': query.id,
'object_type': 'query'
})
def _load_result(query_id, should_require_access):
from redash.authentication.org_resolving import current_org
from redash import models
query = models.Query.get_by_id_and_org(query_id, current_org)
if should_require_access:
require_access(query.data_source, current_user, view_only)
query_result = models.QueryResult.get_by_id_and_org(query.latest_query_data_id, current_org)
if query.data_source:
require_access(query.data_source.groups, current_user, view_only)
query_result = models.QueryResult.get_by_id_and_org(query.latest_query_data_id, current_org)
return json_loads(query_result.data)
else:
abort(400, message="This query is detached from any data source. Please select a different query.")
def post(self, query_id):
query = get_object_or_404(models.Query.get_by_id_and_org, query_id, self.current_org)
require_access(query.groups, self.current_user, view_only)
fav = models.Favorite(org_id=self.current_org.id, object=query, user=self.current_user)
models.db.session.add(fav)
try:
models.db.session.commit()
except IntegrityError as e:
if 'unique_favorite' in e.message:
models.db.session.rollback()
else:
raise e
self.record_event({
'action': 'favorite',
'object_id': query.id,
'object_type': 'query'
})
def post(self, query_id):
"""
Creates a new query, copying the query text from an existing one.
:param query_id: ID of query to fork
Responds with created :ref:`query <query-response-label>` object.
"""
query = get_object_or_404(models.Query.get_by_id_and_org, query_id, self.current_org)
require_access(query.data_source.groups, self.current_user, not_view_only)
forked_query = query.fork(self.current_user)
models.db.session.commit()
self.record_event({
'action': 'fork',
'object_id': query_id,
'object_type': 'query',
})
return QuerySerializer(forked_query, with_visualizations=True).serialize()
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)
return run_query(query.data_source, parameter_values, query.query_text, query.id)
def get(self, data_source_id):
data_source = get_object_or_404(models.DataSource.get_by_id_and_org, data_source_id, self.current_org)
require_access(data_source.groups, self.current_user, view_only)
refresh = request.args.get('refresh') is not None
response = {}
try:
response['schema'] = data_source.get_schema(refresh)
except NotSupported:
response['error'] = {
'code': 1,
'message': 'Data source type does not support retrieving schema'
}
except Exception:
response['error'] = {
'code': 2,
'message': 'Error retrieving schema.'
}
return response
def get(self, query_id):
"""
Retrieve a query.
:param query_id: ID of query to fetch
Responds with the :ref:`query <query-response-label>` contents.
"""
q = get_object_or_404(models.Query.get_by_id_and_org, query_id, self.current_org)
require_access(q.groups, self.current_user, view_only)
result = QuerySerializer(q, with_visualizations=True).serialize()
result['can_edit'] = can_modify(q, self.current_user)
self.record_event({
'action': 'view',
'object_id': query_id,
'object_type': 'query',
})
return result
def post(self):
widget_properties = request.get_json(force=True)
dashboard = models.Dashboard.get_by_id_and_org(widget_properties.pop('dashboard_id'), self.current_org)
require_admin_or_owner(dashboard.user_id)
widget_properties['options'] = json.dumps(widget_properties['options'])
widget_properties.pop('id', None)
widget_properties['dashboard'] = dashboard
visualization_id = widget_properties.pop('visualization_id')
if visualization_id:
visualization = models.Visualization.get_by_id_and_org(visualization_id, self.current_org)
require_access(visualization.query.groups, self.current_user, view_only)
else:
visualization = None
widget_properties['visualization'] = visualization
widget = models.Widget.create(**widget_properties)
layout = json.loads(widget.dashboard.layout)
new_row = True
if len(layout) == 0 or widget.width == 2:
layout.append([widget.id])
elif len(layout[-1]) == 1:
neighbour_widget = models.Widget.get(models.Widget.id == layout[-1][0])
if neighbour_widget.width == 1:
layout[-1].append(widget.id)
new_row = False
else:
layout.append([widget.id])
else:
layout.append([widget.id])
widget.dashboard.layout = json.dumps(layout)
widget.dashboard.save()
return {'widget': widget.to_dict(), 'layout': layout, 'new_row': new_row}
def post(self):
query_def = request.get_json(force=True)
data_source = models.DataSource.get_by_id_and_org(query_def.pop('data_source_id'), self.current_org)
require_access(data_source.groups, self.current_user, not_view_only)
for field in ['id', 'created_at', 'api_key', 'visualizations', 'latest_query_data', 'last_modified_by']:
query_def.pop(field, None)
# If we already executed this query, save the query result reference
if 'latest_query_data_id' in query_def:
query_def['latest_query_data'] = query_def.pop('latest_query_data_id')
query_def['user'] = self.current_user
query_def['data_source'] = data_source
query_def['org'] = self.current_org
query = models.Query.create(**query_def)
self.record_event({
'action': 'create',
'object_id': query.id,
'object_type': 'query'
})
return query.to_dict()
