def test_convert_legacy_filters_into_adhoc_present_and_nonempty(self):
form_data = {
'adhoc_filters': [
{
'clause': 'WHERE',
'expressionType': 'SQL',
'sqlExpression': 'a = 1',
},
],
'filters': [{
'col': 'a',
'op': 'in',
'val': 'someval'
}],
'having':
'COUNT(1) = 1',
'having_filters': [{
'col': 'COUNT(1)',
'op': '==',
'val': 1
}],
}
expected = {
'adhoc_filters': [
{
'clause': 'WHERE',
'expressionType': 'SQL',
'sqlExpression': 'a = 1',
},
],
}
convert_legacy_filters_into_adhoc(form_data)
self.assertEquals(form_data, expected)
def test_convert_legacy_filters_into_adhoc_present_and_nonempty(self):
form_data = {
"adhoc_filters": [{
"clause": "WHERE",
"expressionType": "SQL",
"sqlExpression": "a = 1"
}],
"filters": [{
"col": "a",
"op": "in",
"val": "someval"
}],
"having":
"COUNT(1) = 1",
"having_filters": [{
"col": "COUNT(1)",
"op": "==",
"val": 1
}],
}
expected = {
"adhoc_filters": [{
"clause": "WHERE",
"expressionType": "SQL",
"sqlExpression": "a = 1"
}]
}
convert_legacy_filters_into_adhoc(form_data)
self.assertEqual(form_data, expected)
def test_convert_legacy_filters_into_adhoc_present_and_empty(self):
form_data = {"adhoc_filters": [], "where": "a = 1"}
expected = {
"adhoc_filters": [
{"clause": "WHERE", "expressionType": "SQL", "sqlExpression": "a = 1"}
]
}
convert_legacy_filters_into_adhoc(form_data)
self.assertEqual(form_data, expected)
def test_convert_legacy_filters_into_adhoc_having(self):
form_data = {"having": "COUNT(1) = 1"}
expected = {
"adhoc_filters": [{
"clause": "HAVING",
"expressionType": "SQL",
"sqlExpression": "COUNT(1) = 1",
}]
}
convert_legacy_filters_into_adhoc(form_data)
self.assertEqual(form_data, expected)
def test_convert_legacy_filters_into_adhoc_having(self):
form_data = {"having": "COUNT(1) = 1"}
expected = {
"adhoc_filters": [{
"clause": "HAVING",
"expressionType": "SQL",
"filterOptionName": "683f1c26466ab912f75a00842e0f2f7b",
"sqlExpression": "COUNT(1) = 1",
}]
}
convert_legacy_filters_into_adhoc(form_data)
self.assertEqual(form_data, expected)
def test_convert_legacy_filters_into_adhoc_present_and_empty(self):
form_data = {"adhoc_filters": [], "where": "a = 1"}
expected = {
"adhoc_filters": [{
"clause": "WHERE",
"expressionType": "SQL",
"filterOptionName": "46fb6d7891e23596e42ae38da94a57e0",
"sqlExpression": "a = 1",
}]
}
convert_legacy_filters_into_adhoc(form_data)
self.assertEqual(form_data, expected)
def test_convert_legacy_filters_into_adhoc_filters(self):
form_data = {"filters": [{"col": "a", "op": "in", "val": "someval"}]}
expected = {
"adhoc_filters": [{
"clause": "WHERE",
"comparator": "someval",
"expressionType": "SIMPLE",
"operator": "in",
"subject": "a",
}]
}
convert_legacy_filters_into_adhoc(form_data)
self.assertEqual(form_data, expected)
def filter_values(column: str, default: Optional[str] = None) -> List[str]:
""" Gets a values for a particular filter as a list
This is useful if:
- you want to use a filter box to filter a query where the name of filter box
column doesn't match the one in the select statement
- you want to have the ability for filter inside the main query for speed
purposes
Usage example::
SELECT action, count(*) as times
FROM logs
WHERE action in ( {{ "'" + "','".join(filter_values('action_type')) + "'" }} )
GROUP BY action
:param column: column/filter name to lookup
:param default: default value to return if there's no matching columns
:return: returns a list of filter values
"""
from superset.views.utils import get_form_data
form_data, _ = get_form_data()
convert_legacy_filters_into_adhoc(form_data)
merge_extra_filters(form_data)
return_val = [
comparator
for filter in form_data.get("adhoc_filters", [])
for comparator in (
filter["comparator"]
if isinstance(filter["comparator"], list)
else [filter["comparator"]]
)
if (
filter.get("expressionType") == "SIMPLE"
and filter.get("clause") == "WHERE"
and filter.get("subject") == column
and filter.get("comparator")
)
]
if return_val:
return return_val
if default:
return [default]
return []
def upgrade():
bind = op.get_bind()
session = db.Session(bind=bind)
for slc in session.query(Slice).all():
try:
params = json.loads(slc.params)
convert_legacy_filters_into_adhoc(params)
slc.params = json.dumps(params, sort_keys=True)
except Exception:
pass
session.commit()
session.close()
def upgrade():
bind = op.get_bind()
session = db.Session(bind=bind)
for slc in session.query(Slice).all():
try:
params = json.loads(slc.params)
convert_legacy_filters_into_adhoc(params)
slc.params = json.dumps(params, sort_keys=True)
except Exception:
pass
session.commit()
session.close()
def test_convert_legacy_filters_into_adhoc_having(self):
form_data = {
'having': 'COUNT(1) = 1',
}
expected = {
'adhoc_filters': [
{
'clause': 'HAVING',
'expressionType': 'SQL',
'sqlExpression': 'COUNT(1) = 1',
},
],
}
convert_legacy_filters_into_adhoc(form_data)
self.assertEquals(form_data, expected)
def test_convert_legacy_filters_into_adhoc_where(self):
form_data = {
'where': 'a = 1',
}
expected = {
'adhoc_filters': [
{
'clause': 'WHERE',
'expressionType': 'SQL',
'sqlExpression': 'a = 1',
},
],
}
convert_legacy_filters_into_adhoc(form_data)
self.assertEquals(form_data, expected)
def test_convert_legacy_filters_into_adhoc_where(self):
form_data = {
'where': 'a = 1',
}
expected = {
'adhoc_filters': [
{
'clause': 'WHERE',
'expressionType': 'SQL',
'sqlExpression': 'a = 1',
},
],
}
convert_legacy_filters_into_adhoc(form_data)
self.assertEquals(form_data, expected)
def test_convert_legacy_filters_into_adhoc_having_filters(self):
form_data = {"having_filters": [{"col": "COUNT(1)", "op": "==", "val": 1}]}
expected = {
"adhoc_filters": [
{
"clause": "HAVING",
"comparator": 1,
"expressionType": "SIMPLE",
"operator": "==",
"subject": "COUNT(1)",
}
]
}
convert_legacy_filters_into_adhoc(form_data)
self.assertEqual(form_data, expected)
def test_convert_legacy_filters_into_adhoc_having(self):
form_data = {
'having': 'COUNT(1) = 1',
}
expected = {
'adhoc_filters': [
{
'clause': 'HAVING',
'expressionType': 'SQL',
'sqlExpression': 'COUNT(1) = 1',
},
],
}
convert_legacy_filters_into_adhoc(form_data)
self.assertEquals(form_data, expected)
def test_convert_legacy_filters_into_adhoc_having_filters(self):
form_data = {
'having_filters': [{'col': 'COUNT(1)', 'op': '==', 'val': 1}],
}
expected = {
'adhoc_filters': [
{
'clause': 'HAVING',
'comparator': 1,
'expressionType': 'SIMPLE',
'operator': '==',
'subject': 'COUNT(1)',
},
],
}
convert_legacy_filters_into_adhoc(form_data)
self.assertEquals(form_data, expected)
def test_convert_legacy_filters_into_adhoc_having_filters(self):
form_data = {
'having_filters': [{'col': 'COUNT(1)', 'op': '==', 'val': 1}],
}
expected = {
'adhoc_filters': [
{
'clause': 'HAVING',
'comparator': 1,
'expressionType': 'SIMPLE',
'operator': '==',
'subject': 'COUNT(1)',
},
],
}
convert_legacy_filters_into_adhoc(form_data)
self.assertEquals(form_data, expected)
def test_convert_legacy_filters_into_adhoc_filters(self):
form_data = {
'filters': [{'col': 'a', 'op': 'in', 'val': 'someval'}],
}
expected = {
'adhoc_filters': [
{
'clause': 'WHERE',
'comparator': 'someval',
'expressionType': 'SIMPLE',
'operator': 'in',
'subject': 'a',
},
],
}
convert_legacy_filters_into_adhoc(form_data)
self.assertEquals(form_data, expected)
def test_convert_legacy_filters_into_adhoc_filters(self):
form_data = {
'filters': [{'col': 'a', 'op': 'in', 'val': 'someval'}],
}
expected = {
'adhoc_filters': [
{
'clause': 'WHERE',
'comparator': 'someval',
'expressionType': 'SIMPLE',
'operator': 'in',
'subject': 'a',
},
],
}
convert_legacy_filters_into_adhoc(form_data)
self.assertEquals(form_data, expected)
def upgrade():
bind = op.get_bind()
session = db.Session(bind=bind)
for slc in session.query(Slice).all():
if slc.params:
try:
source = json.loads(slc.params)
target = copy.deepcopy(source)
convert_legacy_filters_into_adhoc(target)
if source != target:
slc.params = json.dumps(target, sort_keys=True)
except Exception as ex:
logging.warn(ex)
session.commit()
session.close()
def add_slice_to_dashboard(request,
args,
datasource_type=None,
datasource_id=None):
form_data = json.loads(args.get('form_data'))
datasource_id = args.get('datasource_id')
datasource_type = args.get('datasource_type')
datasource_name = args.get('datasource_name')
viz_type = form_data.get('viz_type')
form_data['datasource'] = str(datasource_id) + '__' + datasource_type
# On explore, merge legacy and extra filters into the form data
utils.convert_legacy_filters_into_adhoc(form_data)
utils.merge_extra_filters(form_data)
"""Save or overwrite a slice"""
slice_name = args.get('slice_name')
action = args.get('action')
#saving slice
slc = models.Slice(owners=[g.user] if g.user else [])
slc.params = json.dumps(form_data, indent=2, sort_keys=True)
slc.datasource_name = datasource_name
slc.viz_type = form_data['viz_type']
slc.datasource_type = datasource_type
slc.datasource_id = datasource_id
slc.slice_name = slice_name
session = db.session()
session.add(slc)
session.commit()
#adding slice to dashboard
dash = (db.session.query(models.Dashboard).filter_by(
id=int(args.get('save_to_dashboard_id'))).one())
dash.slices.append(slc)
db.session.commit()
logging.info('Slice [' + slc.slice_name +
'] was added to dashboard id [ ' +
str(args.get('save_to_dashboard_id')) + ' ]')
return {
'form_data': slc.form_data,
'slice': slc.data,
}
def test_convert_legacy_filters_into_adhoc_present_and_nonempty(self):
form_data = {
'adhoc_filters': [
{
'clause': 'WHERE',
'expressionType': 'SQL',
'sqlExpression': 'a = 1',
},
],
'filters': [{'col': 'a', 'op': 'in', 'val': 'someval'}],
'having': 'COUNT(1) = 1',
'having_filters': [{'col': 'COUNT(1)', 'op': '==', 'val': 1}],
}
expected = {
'adhoc_filters': [
{
'clause': 'WHERE',
'expressionType': 'SQL',
'sqlExpression': 'a = 1',
},
],
}
convert_legacy_filters_into_adhoc(form_data)
self.assertEquals(form_data, expected)
def run(self) -> Optional[Dict[str, Any]]:
initial_form_data = {}
if self._permalink_key is not None:
command = GetExplorePermalinkCommand(self._permalink_key)
permalink_value = command.run()
if not permalink_value:
raise ExplorePermalinkGetFailedError()
state = permalink_value["state"]
initial_form_data = state["formData"]
url_params = state.get("urlParams")
if url_params:
initial_form_data["url_params"] = dict(url_params)
elif self._form_data_key:
parameters = FormDataCommandParameters(key=self._form_data_key)
value = GetFormDataCommand(parameters).run()
initial_form_data = json.loads(value) if value else {}
message = None
if not initial_form_data:
if self._slice_id:
initial_form_data["slice_id"] = self._slice_id
if self._form_data_key:
message = _(
"Form data not found in cache, reverting to chart metadata."
)
elif self._dataset_id:
initial_form_data[
"datasource"
] = f"{self._dataset_id}__{self._dataset_type}"
if self._form_data_key:
message = _(
"Form data not found in cache, reverting to dataset metadata."
)
form_data, slc = get_form_data(
use_slice_data=True, initial_form_data=initial_form_data
)
try:
self._dataset_id, self._dataset_type = get_datasource_info(
self._dataset_id, self._dataset_type, form_data
)
except SupersetException:
self._dataset_id = None
# fallback unkonw datasource to table type
self._dataset_type = SqlaTable.type
dataset: Optional[BaseDatasource] = None
if self._dataset_id is not None:
try:
dataset = DatasourceDAO.get_datasource(
db.session, cast(str, self._dataset_type), self._dataset_id
)
except DatasetNotFoundError:
pass
dataset_name = dataset.name if dataset else _("[Missing Dataset]")
if dataset:
if app.config["ENABLE_ACCESS_REQUEST"] and (
not security_manager.can_access_datasource(dataset)
):
message = __(security_manager.get_datasource_access_error_msg(dataset))
raise DatasetAccessDeniedError(
message=message,
dataset_type=self._dataset_type,
dataset_id=self._dataset_id,
)
viz_type = form_data.get("viz_type")
if not viz_type and dataset and dataset.default_endpoint:
raise WrongEndpointError(redirect=dataset.default_endpoint)
form_data["datasource"] = (
str(self._dataset_id) + "__" + cast(str, self._dataset_type)
)
# On explore, merge legacy and extra filters into the form data
utils.convert_legacy_filters_into_adhoc(form_data)
utils.merge_extra_filters(form_data)
dummy_dataset_data: Dict[str, Any] = {
"type": self._dataset_type,
"name": dataset_name,
"columns": [],
"metrics": [],
"database": {"id": 0, "backend": ""},
}
try:
dataset_data = dataset.data if dataset else dummy_dataset_data
except (SupersetException, SQLAlchemyError):
dataset_data = dummy_dataset_data
return {
"dataset": sanitize_datasource_data(dataset_data),
"form_data": form_data,
"slice": slc.data if slc else None,
"message": message,
}
def get_filters(self,
column: str,
remove_filter: bool = False) -> List[Filter]:
"""Get the filters applied to the given column. In addition
to returning values like the filter_values function
the get_filters function returns the operator specified in the explorer UI.
This is useful if:
- you want to handle more than the IN operator in your SQL clause
- you want to handle generating custom SQL conditions for a filter
- you want to have the ability for filter inside the main query for speed
purposes
Usage example::
WITH RECURSIVE
superiors(employee_id, manager_id, full_name, level, lineage) AS (
SELECT
employee_id,
manager_id,
full_name,
1 as level,
employee_id as lineage
FROM
employees
WHERE
1=1
{# Render a blank line #}
{%- for filter in get_filters('full_name', remove_filter=True) -%}
{%- if filter.get('op') == 'IN' -%}
AND
full_name IN ( {{ "'" + "', '".join(filter.get('val')) + "'" }} )
{%- endif -%}
{%- if filter.get('op') == 'LIKE' -%}
AND
full_name LIKE {{ "'" + filter.get('val') + "'" }}
{%- endif -%}
{%- endfor -%}
UNION ALL
SELECT
e.employee_id,
e.manager_id,
e.full_name,
s.level + 1 as level,
s.lineage
FROM
employees e,
superiors s
WHERE s.manager_id = e.employee_id
)
SELECT
employee_id, manager_id, full_name, level, lineage
FROM
superiors
order by lineage, level
:param column: column/filter name to lookup
:param remove_filter: When set to true, mark the filter as processed,
removing it from the outer query. Useful when a filter should
only apply to the inner query
:return: returns a list of filters
"""
# pylint: disable=import-outside-toplevel
from superset.utils.core import FilterOperator
from superset.views.utils import get_form_data
form_data, _ = get_form_data()
convert_legacy_filters_into_adhoc(form_data)
merge_extra_filters(form_data)
filters: List[Filter] = []
for flt in form_data.get("adhoc_filters", []):
val: Union[Any, List[Any]] = flt.get("comparator")
op: str = flt["operator"].upper() if flt.get("operator") else None
# fltOpName: str = flt.get("filterOptionName")
if (flt.get("expressionType") == "SIMPLE"
and flt.get("clause") == "WHERE"
and flt.get("subject") == column and val):
if remove_filter:
if column not in self.removed_filters:
self.removed_filters.append(column)
if column not in self.applied_filters:
self.applied_filters.append(column)
if op in (
FilterOperator.IN.value,
FilterOperator.NOT_IN.value,
) and not isinstance(val, list):
val = [val]
filters.append({"op": op, "col": column, "val": val})
return filters
