You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@superset.apache.org by be...@apache.org on 2021/03/24 17:17:21 UTC
[superset] 02/10: feat: add Firebird DB engine spec (#13353)
This is an automated email from the ASF dual-hosted git repository.
beto pushed a commit to branch 1.1.0rc1
in repository https://gitbox.apache.org/repos/asf/superset.git
commit c881c06b7c7a27d1a267c0730ca5e4d95a08bb54
Author: Beto Dealmeida <ro...@dealmeida.net>
AuthorDate: Fri Feb 26 12:07:27 2021 -0800
feat: add Firebird DB engine spec (#13353)
* feat: add Firebird DB engine spec
* Add dep to setup.py
* Fix lint
* Add tests
* Remove uneeded code
* Fix old bug
---
setup.py | 1 +
superset/db_engine_specs/base.py | 2 +-
superset/db_engine_specs/firebird.py | 83 +++++++++++++++++++++++++++++++++
tests/db_engine_specs/firebird_tests.py | 81 ++++++++++++++++++++++++++++++++
4 files changed, 166 insertions(+), 1 deletion(-)
diff --git a/setup.py b/setup.py
index 83fa144..78be764 100644
--- a/setup.py
+++ b/setup.py
@@ -129,6 +129,7 @@ setup(
"elasticsearch": ["elasticsearch-dbapi>=0.2.0, <0.3.0"],
"exasol": ["sqlalchemy-exasol>=2.1.0, <2.2"],
"excel": ["xlrd>=1.2.0, <1.3"],
+ "firebird": ["sqlalchemy-firebird>=0.7.0, <0.8"],
"gsheets": ["shillelagh>=0.2, <0.3"],
"hana": ["hdbcli==2.4.162", "sqlalchemy_hana==0.4.0"],
"hive": ["pyhive[hive]>=0.6.1", "tableschema", "thrift>=0.11.0, <1.0.0"],
diff --git a/superset/db_engine_specs/base.py b/superset/db_engine_specs/base.py
index c21b16f..8db57e9 100644
--- a/superset/db_engine_specs/base.py
+++ b/superset/db_engine_specs/base.py
@@ -456,7 +456,7 @@ class BaseEngineSpec: # pylint: disable=too-many-public-methods
)
return database.compile_sqla_query(qry)
- if LimitMethod.FORCE_LIMIT:
+ if cls.limit_method == LimitMethod.FORCE_LIMIT:
parsed_query = sql_parse.ParsedQuery(sql)
sql = parsed_query.set_or_update_query_limit(limit)
diff --git a/superset/db_engine_specs/firebird.py b/superset/db_engine_specs/firebird.py
new file mode 100644
index 0000000..72b462a
--- /dev/null
+++ b/superset/db_engine_specs/firebird.py
@@ -0,0 +1,83 @@
+# 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 datetime import datetime
+from typing import Optional
+
+from superset.db_engine_specs.base import BaseEngineSpec, LimitMethod
+from superset.utils import core as utils
+
+
+class FirebirdEngineSpec(BaseEngineSpec):
+ """Engine for Firebird"""
+
+ engine = "firebird"
+ engine_name = "Firebird"
+
+ # Firebird uses FIRST to limit: `SELECT FIRST 10 * FROM table`
+ limit_method = LimitMethod.FETCH_MANY
+
+ _time_grain_expressions = {
+ None: "{col}",
+ "PT1S": (
+ "CAST(CAST({col} AS DATE) "
+ "|| ' ' "
+ "|| EXTRACT(HOUR FROM {col}) "
+ "|| ':' "
+ "|| EXTRACT(MINUTE FROM {col}) "
+ "|| ':' "
+ "|| FLOOR(EXTRACT(SECOND FROM {col})) AS TIMESTAMP)"
+ ),
+ "PT1M": (
+ "CAST(CAST({col} AS DATE) "
+ "|| ' ' "
+ "|| EXTRACT(HOUR FROM {col}) "
+ "|| ':' "
+ "|| EXTRACT(MINUTE FROM {col}) "
+ "|| ':00' AS TIMESTAMP)"
+ ),
+ "PT1H": (
+ "CAST(CAST({col} AS DATE) "
+ "|| ' ' "
+ "|| EXTRACT(HOUR FROM {col}) "
+ "|| ':00:00' AS TIMESTAMP)"
+ ),
+ "P1D": "CAST({col} AS DATE)",
+ "P1M": (
+ "CAST(EXTRACT(YEAR FROM {col}) "
+ "|| '-' "
+ "|| EXTRACT(MONTH FROM {col}) "
+ "|| '-01' AS DATE)"
+ ),
+ "P1Y": "CAST(EXTRACT(YEAR FROM {col}) || '-01-01' AS DATE)",
+ }
+
+ @classmethod
+ def epoch_to_dttm(cls) -> str:
+ return "DATEADD(second, {col}, CAST('00:00:00' AS TIMESTAMP))"
+
+ @classmethod
+ def convert_dttm(cls, target_type: str, dttm: datetime) -> Optional[str]:
+ tt = target_type.upper()
+ if tt == utils.TemporalType.TIMESTAMP:
+ dttm_formatted = dttm.isoformat(sep=" ")
+ dttm_valid_precision = dttm_formatted[: len("YYYY-MM-DD HH:MM:SS.MMMM")]
+ return f"CAST('{dttm_valid_precision}' AS TIMESTAMP)"
+ if tt == utils.TemporalType.DATE:
+ return f"CAST('{dttm.date().isoformat()}' AS DATE)"
+ if tt == utils.TemporalType.TIME:
+ return f"CAST('{dttm.time().isoformat()}' AS TIME)"
+ return None
diff --git a/tests/db_engine_specs/firebird_tests.py b/tests/db_engine_specs/firebird_tests.py
new file mode 100644
index 0000000..5e00e2e
--- /dev/null
+++ b/tests/db_engine_specs/firebird_tests.py
@@ -0,0 +1,81 @@
+# 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 datetime import datetime
+from unittest import mock
+
+import pytest
+
+from superset.db_engine_specs.firebird import FirebirdEngineSpec
+
+grain_expressions = {
+ None: "timestamp_column",
+ "PT1S": (
+ "CAST(CAST(timestamp_column AS DATE) "
+ "|| ' ' "
+ "|| EXTRACT(HOUR FROM timestamp_column) "
+ "|| ':' "
+ "|| EXTRACT(MINUTE FROM timestamp_column) "
+ "|| ':' "
+ "|| FLOOR(EXTRACT(SECOND FROM timestamp_column)) AS TIMESTAMP)"
+ ),
+ "PT1M": (
+ "CAST(CAST(timestamp_column AS DATE) "
+ "|| ' ' "
+ "|| EXTRACT(HOUR FROM timestamp_column) "
+ "|| ':' "
+ "|| EXTRACT(MINUTE FROM timestamp_column) "
+ "|| ':00' AS TIMESTAMP)"
+ ),
+ "P1D": "CAST(timestamp_column AS DATE)",
+ "P1M": (
+ "CAST(EXTRACT(YEAR FROM timestamp_column) "
+ "|| '-' "
+ "|| EXTRACT(MONTH FROM timestamp_column) "
+ "|| '-01' AS DATE)"
+ ),
+ "P1Y": "CAST(EXTRACT(YEAR FROM timestamp_column) || '-01-01' AS DATE)",
+}
+
+
+@pytest.mark.parametrize("grain,expected", grain_expressions.items())
+def test_time_grain_expressions(grain, expected):
+ assert (
+ FirebirdEngineSpec._time_grain_expressions[grain].format(col="timestamp_column")
+ == expected
+ )
+
+
+def test_epoch_to_dttm():
+ assert (
+ FirebirdEngineSpec.epoch_to_dttm().format(col="timestamp_column")
+ == "DATEADD(second, timestamp_column, CAST('00:00:00' AS TIMESTAMP))"
+ )
+
+
+def test_convert_dttm():
+ dttm = datetime(2021, 1, 1)
+ assert (
+ FirebirdEngineSpec.convert_dttm("timestamp", dttm)
+ == "CAST('2021-01-01 00:00:00' AS TIMESTAMP)"
+ )
+ assert (
+ FirebirdEngineSpec.convert_dttm("TIMESTAMP", dttm)
+ == "CAST('2021-01-01 00:00:00' AS TIMESTAMP)"
+ )
+ assert FirebirdEngineSpec.convert_dttm("TIME", dttm) == "CAST('00:00:00' AS TIME)"
+ assert FirebirdEngineSpec.convert_dttm("DATE", dttm) == "CAST('2021-01-01' AS DATE)"
+ assert FirebirdEngineSpec.convert_dttm("STRING", dttm) is None