You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@superset.apache.org by ta...@apache.org on 2020/10/29 20:12:19 UTC
[incubator-superset] branch master updated: fix(datasets): add
custom filter for virtual datasets based on sql attribute (#11452)
This is an automated email from the ASF dual-hosted git repository.
tai pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/incubator-superset.git
The following commit(s) were added to refs/heads/master by this push:
new f918ca1 fix(datasets): add custom filter for virtual datasets based on sql attribute (#11452)
f918ca1 is described below
commit f918ca14aa56046f060021d564e05e3e4cf585fb
Author: ʈᵃᵢ <td...@gmail.com>
AuthorDate: Thu Oct 29 13:11:33 2020 -0700
fix(datasets): add custom filter for virtual datasets based on sql attribute (#11452)
---
superset-frontend/src/components/ListView/types.ts | 3 +-
.../src/views/CRUD/data/dataset/DatasetList.tsx | 10 +--
superset/datasets/api.py | 2 +
superset/datasets/filters.py | 35 +++++++++++
tests/datasets/api_tests.py | 72 +++++++++++++++++++++-
5 files changed, 113 insertions(+), 9 deletions(-)
diff --git a/superset-frontend/src/components/ListView/types.ts b/superset-frontend/src/components/ListView/types.ts
index cd955f6..82dcc51 100644
--- a/superset-frontend/src/components/ListView/types.ts
+++ b/superset-frontend/src/components/ListView/types.ts
@@ -55,7 +55,8 @@ export interface Filter {
| 'title_or_slug'
| 'name_or_description'
| 'all_text'
- | 'chart_all_text';
+ | 'chart_all_text'
+ | 'dataset_is_null_or_empty';
input?: 'text' | 'textarea' | 'select' | 'checkbox' | 'search';
unfilteredLabel?: string;
selects?: SelectOption[];
diff --git a/superset-frontend/src/views/CRUD/data/dataset/DatasetList.tsx b/superset-frontend/src/views/CRUD/data/dataset/DatasetList.tsx
index 419fe5a..a45d394 100644
--- a/superset-frontend/src/views/CRUD/data/dataset/DatasetList.tsx
+++ b/superset-frontend/src/views/CRUD/data/dataset/DatasetList.tsx
@@ -242,7 +242,7 @@ const DatasetList: FunctionComponent<DatasetListProps> = ({
size: 'lg',
},
{
- accessor: 'is_sqllab_view',
+ accessor: 'sql',
hidden: true,
disableSortBy: true,
},
@@ -350,13 +350,13 @@ const DatasetList: FunctionComponent<DatasetListProps> = ({
},
{
Header: t('Type'),
- id: 'is_sqllab_view',
+ id: 'sql',
input: 'select',
- operator: 'eq',
+ operator: 'dataset_is_null_or_empty',
unfilteredLabel: 'All',
selects: [
- { label: 'Virtual', value: true },
- { label: 'Physical', value: false },
+ { label: 'Virtual', value: false },
+ { label: 'Physical', value: true },
],
},
{
diff --git a/superset/datasets/api.py b/superset/datasets/api.py
index fff69bf..19c85d0 100644
--- a/superset/datasets/api.py
+++ b/superset/datasets/api.py
@@ -48,6 +48,7 @@ from superset.datasets.commands.export import ExportDatasetsCommand
from superset.datasets.commands.refresh import RefreshDatasetCommand
from superset.datasets.commands.update import UpdateDatasetCommand
from superset.datasets.dao import DatasetDAO
+from superset.datasets.filters import DatasetIsNullOrEmptyFilter
from superset.datasets.schemas import (
DatasetPostSchema,
DatasetPutSchema,
@@ -160,6 +161,7 @@ class DatasetRestApi(BaseSupersetModelRestApi):
"owners": RelatedFieldFilter("first_name", FilterRelatedOwners),
"database": "database_name",
}
+ search_filters = {"sql": [DatasetIsNullOrEmptyFilter]}
filter_rel_fields = {"database": [["id", DatabaseFilter, lambda: []]]}
allowed_rel_fields = {"database", "owners"}
allowed_distinct_fields = {"schema"}
diff --git a/superset/datasets/filters.py b/superset/datasets/filters.py
new file mode 100644
index 0000000..4bbe80f
--- /dev/null
+++ b/superset/datasets/filters.py
@@ -0,0 +1,35 @@
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements. See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership. The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License. You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied. See the License for the
+# specific language governing permissions and limitations
+# under the License.
+from flask_babel import lazy_gettext as _
+from sqlalchemy import not_, or_
+from sqlalchemy.orm.query import Query
+
+from superset.connectors.sqla.models import SqlaTable
+from superset.views.base import BaseFilter
+
+
+class DatasetIsNullOrEmptyFilter(BaseFilter): # pylint: disable=too-few-public-methods
+ name = _("Null or Empty")
+ arg_name = "dataset_is_null_or_empty"
+
+ def apply(self, query: Query, value: bool) -> Query:
+ filter_clause = or_(SqlaTable.sql.is_(None), SqlaTable.sql == "")
+
+ if not value:
+ filter_clause = not_(filter_clause)
+
+ return query.filter(filter_clause)
diff --git a/tests/datasets/api_tests.py b/tests/datasets/api_tests.py
index 50ab609..d7d6bb3 100644
--- a/tests/datasets/api_tests.py
+++ b/tests/datasets/api_tests.py
@@ -17,7 +17,7 @@
"""Unit tests for Superset"""
import json
from io import BytesIO
-from typing import List
+from typing import List, Optional
from unittest.mock import patch
from zipfile import is_zipfile
@@ -43,17 +43,26 @@ from tests.conftest import CTAS_SCHEMA_NAME
class TestDatasetApi(SupersetTestCase):
fixture_tables_names = ("ab_permission", "ab_permission_view", "ab_view_menu")
+ fixture_virtual_table_names = ("sql_virtual_dataset_1", "sql_virtual_dataset_2")
@staticmethod
def insert_dataset(
- table_name: str, schema: str, owners: List[int], database: Database
+ table_name: str,
+ schema: str,
+ owners: List[int],
+ database: Database,
+ sql: Optional[str] = None,
) -> SqlaTable:
obj_owners = list()
for owner in owners:
user = db.session.query(security_manager.user_model).get(owner)
obj_owners.append(user)
table = SqlaTable(
- table_name=table_name, schema=schema, owners=obj_owners, database=database
+ table_name=table_name,
+ schema=schema,
+ owners=obj_owners,
+ database=database,
+ sql=sql,
)
db.session.add(table)
db.session.commit()
@@ -73,6 +82,29 @@ class TestDatasetApi(SupersetTestCase):
)
@pytest.fixture()
+ def create_virtual_datasets(self):
+ with self.create_app().app_context():
+ datasets = []
+ admin = self.get_user("admin")
+ main_db = get_main_database()
+ for table_name in self.fixture_virtual_table_names:
+ datasets.append(
+ self.insert_dataset(
+ table_name,
+ "",
+ [admin.id],
+ main_db,
+ "SELECT * from ab_view_menu;",
+ )
+ )
+ yield datasets
+
+ # rollback changes
+ for dataset in datasets:
+ db.session.delete(dataset)
+ db.session.commit()
+
+ @pytest.fixture()
def create_datasets(self):
with self.create_app().app_context():
datasets = []
@@ -1101,3 +1133,37 @@ class TestDatasetApi(SupersetTestCase):
uri = f"api/v1/dataset/{table.id}/related_objects"
rv = self.client.get(uri)
assert rv.status_code == 404
+
+ @pytest.mark.usefixtures("create_datasets", "create_virtual_datasets")
+ def test_get_datasets_custom_filter_sql(self):
+ """
+ Dataset API: Test custom dataset_is_null_or_empty filter for sql
+ """
+ arguments = {
+ "filters": [
+ {"col": "sql", "opr": "dataset_is_null_or_empty", "value": False}
+ ]
+ }
+ self.login(username="admin")
+ uri = f"api/v1/dataset/?q={prison.dumps(arguments)}"
+ rv = self.client.get(uri)
+
+ assert rv.status_code == 200
+
+ data = json.loads(rv.data.decode("utf-8"))
+ for table_name in self.fixture_virtual_table_names:
+ assert table_name in [ds["table_name"] for ds in data["result"]]
+
+ arguments = {
+ "filters": [
+ {"col": "sql", "opr": "dataset_is_null_or_empty", "value": True}
+ ]
+ }
+ self.login(username="admin")
+ uri = f"api/v1/dataset/?q={prison.dumps(arguments)}"
+ rv = self.client.get(uri)
+ assert rv.status_code == 200
+
+ data = json.loads(rv.data.decode("utf-8"))
+ for table_name in self.fixture_tables_names:
+ assert table_name in [ds["table_name"] for ds in data["result"]]