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 <[email protected]> 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)", +} + + [email protected]("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
