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:46 UTC
[superset] branch update_sqlite_specs created (now 5914ce62e4)
This is an automated email from the ASF dual-hosted git repository.
beto pushed a change to branch update_sqlite_specs
in repository https://gitbox.apache.org/repos/asf/superset.git
at 5914ce62e4 feat: improve SQLite DB engine spec
This branch includes the following new commits:
new 5914ce62e4 feat: improve SQLite DB engine spec
The 1 revisions listed above as "new" are entirely new to this
repository and will be described in separate emails. The revisions
listed as "add" were already present in the repository and have only
been added to this reference.
[superset] 01/01: feat: improve SQLite DB engine spec
Posted by be...@apache.org.
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",
+ ]