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 2023/08/07 20:52:47 UTC
[superset] 01/01: feat: improve SQLite DB engine spec
This is an automated email from the ASF dual-hosted git repository.
beto pushed a commit to branch update_sqlite_specs
in repository https://gitbox.apache.org/repos/asf/superset.git
commit 5914ce62e4878e2bdaf5d729ef615dae6698e63f
Author: Beto Dealmeida <ro...@dealmeida.net>
AuthorDate: Mon Aug 7 13:52:35 2023 -0700
feat: improve SQLite DB engine spec
---
superset/db_engine_specs/gsheets.py | 7 +-
superset/db_engine_specs/shillelagh.py | 18 ++++
superset/db_engine_specs/sqlite.py | 166 ++++++++++++++++++++++++++++++++-
3 files changed, 186 insertions(+), 5 deletions(-)
diff --git a/superset/db_engine_specs/gsheets.py b/superset/db_engine_specs/gsheets.py
index abf5bac48f..777499a8f9 100644
--- a/superset/db_engine_specs/gsheets.py
+++ b/superset/db_engine_specs/gsheets.py
@@ -32,7 +32,7 @@ from typing_extensions import TypedDict
from superset import security_manager
from superset.constants import PASSWORD_MASK
from superset.databases.schemas import encrypted_field_properties, EncryptedString
-from superset.db_engine_specs.sqlite import SqliteEngineSpec
+from superset.db_engine_specs.shillelagh import ShillelaghEngineSpec
from superset.errors import ErrorLevel, SupersetError, SupersetErrorType
if TYPE_CHECKING:
@@ -65,14 +65,13 @@ class GSheetsPropertiesType(TypedDict):
catalog: dict[str, str]
-class GSheetsEngineSpec(SqliteEngineSpec):
+class GSheetsEngineSpec(ShillelaghEngineSpec):
"""Engine for Google spreadsheets"""
- engine = "gsheets"
engine_name = "Google Sheets"
+ engine = "gsheets"
allows_joins = True
allows_subqueries = True
- disable_ssh_tunneling = True
parameters_schema = GSheetsParametersSchema()
default_driver = "apsw"
diff --git a/superset/db_engine_specs/shillelagh.py b/superset/db_engine_specs/shillelagh.py
index 3730122448..61820824b0 100644
--- a/superset/db_engine_specs/shillelagh.py
+++ b/superset/db_engine_specs/shillelagh.py
@@ -14,8 +14,15 @@
# KIND, either express or implied. See the License for the
# specific language governing permissions and limitations
# under the License.
+from __future__ import annotations
+
+from typing import TYPE_CHECKING
+
from superset.db_engine_specs.sqlite import SqliteEngineSpec
+if TYPE_CHECKING:
+ from superset.models.core import Database
+
class ShillelaghEngineSpec(SqliteEngineSpec):
"""Engine for shillelagh"""
@@ -28,3 +35,14 @@ class ShillelaghEngineSpec(SqliteEngineSpec):
allows_joins = True
allows_subqueries = True
+
+ @classmethod
+ def get_function_names(
+ cls,
+ database: Database,
+ ) -> list[str]:
+ return super().get_function_names(database) + [
+ "sleep",
+ "version",
+ "get_metadata",
+ ]
diff --git a/superset/db_engine_specs/sqlite.py b/superset/db_engine_specs/sqlite.py
index 06d5537509..e7f053da21 100644
--- a/superset/db_engine_specs/sqlite.py
+++ b/superset/db_engine_specs/sqlite.py
@@ -14,6 +14,9 @@
# KIND, either express or implied. See the License for the
# specific language governing permissions and limitations
# under the License.
+
+from __future__ import annotations
+
import re
from datetime import datetime
from re import Pattern
@@ -39,11 +42,41 @@ class SqliteEngineSpec(BaseEngineSpec):
engine = "sqlite"
engine_name = "SQLite"
+ disable_ssh_tunneling = True
+
_time_grain_expressions = {
None: "{col}",
TimeGrain.SECOND: "DATETIME(STRFTIME('%Y-%m-%dT%H:%M:%S', {col}))",
+ TimeGrain.FIVE_SECONDS: (
+ "DATETIME({col}, printf('-%d seconds', "
+ "CAST(strftime('%S', {col}) AS INT) % 5))"
+ ),
+ TimeGrain.THIRTY_SECONDS: (
+ "DATETIME({col}, printf('-%d seconds', "
+ "CAST(strftime('%S', {col}) AS INT) % 30))"
+ ),
TimeGrain.MINUTE: "DATETIME(STRFTIME('%Y-%m-%dT%H:%M:00', {col}))",
+ TimeGrain.FIVE_MINUTES: (
+ "DATETIME(STRFTIME('%Y-%m-%dT%H:%M:00', {col}), printf('-%d minutes', "
+ "CAST(strftime('%M', {col}) AS INT) % 5))"
+ ),
+ TimeGrain.TEN_MINUTES: (
+ "DATETIME(STRFTIME('%Y-%m-%dT%H:%M:00', {col}), printf('-%d minutes', "
+ "CAST(strftime('%M', {col}) AS INT) % 10))"
+ ),
+ TimeGrain.FIFTEEN_MINUTES: (
+ "DATETIME(STRFTIME('%Y-%m-%dT%H:%M:00', {col}), printf('-%d minutes', "
+ "CAST(strftime('%M', {col}) AS INT) % 15))"
+ ),
+ TimeGrain.THIRTY_MINUTES: (
+ "DATETIME(STRFTIME('%Y-%m-%dT%H:%M:00', {col}), printf('-%d minutes', "
+ "CAST(strftime('%M', {col}) AS INT) % 30))"
+ ),
TimeGrain.HOUR: "DATETIME(STRFTIME('%Y-%m-%dT%H:00:00', {col}))",
+ TimeGrain.SIX_HOURS: (
+ "DATETIME(STRFTIME('%Y-%m-%dT%H:00:00', {col}), printf('-%d hours', "
+ "CAST(strftime('%H', {col}) AS INT) % 6))"
+ ),
TimeGrain.DAY: "DATETIME({col}, 'start of day')",
TimeGrain.WEEK: "DATETIME({col}, 'start of day', \
-strftime('%w', {col}) || ' days')",
@@ -62,6 +95,13 @@ class SqliteEngineSpec(BaseEngineSpec):
"DATETIME({col}, 'start of day', 'weekday 1', '-7 days')"
),
}
+ # not sure why these are differnet
+ _time_grain_expressions.update(
+ {
+ TimeGrain.HALF_HOUR: _time_grain_expressions[TimeGrain.THIRTY_MINUTES],
+ TimeGrain.QUARTER_YEAR: _time_grain_expressions[TimeGrain.QUARTER],
+ }
+ )
custom_errors: dict[Pattern[str], tuple[str, SupersetErrorType, dict[str, Any]]] = {
COLUMN_DOES_NOT_EXIST_REGEX: (
@@ -86,7 +126,131 @@ class SqliteEngineSpec(BaseEngineSpec):
@classmethod
def get_table_names(
- cls, database: "Database", inspector: Inspector, schema: Optional[str]
+ cls, database: Database, inspector: Inspector, schema: Optional[str]
) -> set[str]:
"""Need to disregard the schema for Sqlite"""
return set(inspector.get_table_names())
+
+ @classmethod
+ def get_function_names(
+ cls,
+ database: Database,
+ ) -> list[str]:
+ """
+ Return function names.
+ """
+ return [
+ "abs",
+ "acos",
+ "acosh",
+ "asin",
+ "asinh",
+ "atan",
+ "atan2",
+ "atanh",
+ "avg",
+ "ceil",
+ "ceiling",
+ "changes",
+ "char",
+ "coalesce",
+ "cos",
+ "cosh",
+ "count",
+ "cume_dist",
+ "date",
+ "datetime",
+ "degrees",
+ "dense_rank",
+ "exp",
+ "first_value",
+ "floor",
+ "format",
+ "glob",
+ "group_concat",
+ "hex",
+ "ifnull",
+ "iif",
+ "instr",
+ "json",
+ "json_array",
+ "json_array_length",
+ "json_each",
+ "json_error_position",
+ "json_extract",
+ "json_group_array",
+ "json_group_object",
+ "json_insert",
+ "json_object",
+ "json_patch",
+ "json_quote",
+ "json_remove",
+ "json_replace",
+ "json_set",
+ "json_tree",
+ "json_type",
+ "json_valid",
+ "julianday",
+ "lag",
+ "last_insert_rowid",
+ "last_value",
+ "lead",
+ "length",
+ "like",
+ "likelihood",
+ "likely",
+ "ln",
+ "load_extension",
+ "log",
+ "log10",
+ "log2",
+ "lower",
+ "ltrim",
+ "max",
+ "min",
+ "mod",
+ "nth_value",
+ "ntile",
+ "nullif",
+ "percent_rank",
+ "pi",
+ "pow",
+ "power",
+ "printf",
+ "quote",
+ "radians",
+ "random",
+ "randomblob",
+ "rank",
+ "replace",
+ "round",
+ "row_number",
+ "rtrim",
+ "sign",
+ "sin",
+ "sinh",
+ "soundex",
+ "sqlite_compileoption_get",
+ "sqlite_compileoption_used",
+ "sqlite_offset",
+ "sqlite_source_id",
+ "sqlite_version",
+ "sqrt",
+ "strftime",
+ "substr",
+ "substring",
+ "sum",
+ "tan",
+ "tanh",
+ "time",
+ "total_changes",
+ "trim",
+ "trunc",
+ "typeof",
+ "unhex",
+ "unicode",
+ "unixepoch",
+ "unlikely",
+ "upper",
+ "zeroblob",
+ ]