This is an automated email from the ASF dual-hosted git repository.
johnbodley 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 a199901 [sqla] Enforcing ISO 8601 date/timestamp formats (#7702)
a199901 is described below
commit a19990185d75732e82b98585ea8fbfa685a1d824
Author: John Bodley <4567245+john-bod...@users.noreply.github.com>
AuthorDate: Thu Oct 17 10:06:42 2019 -0700
[sqla] Enforcing ISO 8601 date/timestamp formats (#7702)
---
.../assets/src/datasource/DatasourceEditor.jsx | 17 ++++++++---
superset/connectors/sqla/models.py | 2 ++
superset/connectors/sqla/views.py | 34 ++++++++++++++++++----
3 files changed, 44 insertions(+), 9 deletions(-)
diff --git a/superset/assets/src/datasource/DatasourceEditor.jsx
b/superset/assets/src/datasource/DatasourceEditor.jsx
index 014be93..10a0861 100644
--- a/superset/assets/src/datasource/DatasourceEditor.jsx
+++ b/superset/assets/src/datasource/DatasourceEditor.jsx
@@ -97,14 +97,23 @@ function ColumnCollectionTable({
<Field
fieldKey="python_date_format"
label={t('Datetime Format')}
- descr={
+ descr={/* Note the fragmented translations may not work. */
<div>
- {t('The pattern of the timestamp format, use ')}
+ {t('The pattern of timestamp format. For strings use ')}
<a
href="https://docs.python.org/2/library/datetime.html#strftime-strptime-behavior";>
{t('python datetime string pattern')}
</a>
- {t(` expression. If time is stored in epoch format, put
\`epoch_s\` or
- \`epoch_ms\`.`)}
+ {t(' expression which needs to adhere to the ')}
+ <a href="https://en.wikipedia.org/wiki/ISO_8601";>
+ {t('ISO 8601')}
+ </a>
+ {t(` standard to ensure that the lexicographical ordering
+ coincides with the chronological ordering. If the
+ timestamp format does not adhere to the ISO 8601 standard
+ you will need to define an expression and type for
+ transforming the string into a date or timestamp. Note
+ currently time zones are not supported. If time is stored
+ in epoch format, put \`epoch_s\` or \`epoch_ms\`.`)}
</div>
}
control={<TextControl />}
diff --git a/superset/connectors/sqla/models.py
b/superset/connectors/sqla/models.py
index 054fa2b..fcd045e 100644
--- a/superset/connectors/sqla/models.py
+++ b/superset/connectors/sqla/models.py
@@ -219,6 +219,8 @@ class TableColumn(Model, BaseColumn):
return "'{}'".format(dttm.strftime(tf))
else:
s = self.table.database.db_engine_spec.convert_dttm(self.type or
"", dttm)
+
+ # TODO(john-bodley): SIP-15 will explicitly require a type
conversion.
return s or "'{}'".format(dttm.strftime("%Y-%m-%d %H:%M:%S.%f"))
diff --git a/superset/connectors/sqla/views.py
b/superset/connectors/sqla/views.py
index 3b242ec..38145ef 100644
--- a/superset/connectors/sqla/views.py
+++ b/superset/connectors/sqla/views.py
@@ -17,6 +17,7 @@
# pylint: disable=C,R,W
"""Views used by the SqlAlchemy connector"""
import logging
+import re
from flask import flash, Markup, redirect
from flask_appbuilder import CompactCRUDMixin, expose
@@ -27,6 +28,7 @@ from flask_appbuilder.security.decorators import has_access
from flask_babel import gettext as __
from flask_babel import lazy_gettext as _
from wtforms.ext.sqlalchemy.fields import QuerySelectField
+from wtforms.validators import Regexp
from superset import appbuilder, db, security_manager
from superset.connectors.base.views import DatasourceModelView
@@ -99,12 +101,17 @@ class TableColumnInlineView(CompactCRUDMixin,
SupersetModelView): # noqa
),
"python_date_format": utils.markdown(
Markup(
- "The pattern of timestamp format, use "
+ "The pattern of timestamp format. For strings use "
'<a href="https://docs.python.org/2/library/'
'datetime.html#strftime-strptime-behavior">'
- "python datetime string pattern</a> "
- "expression. If time is stored in epoch "
- "format, put `epoch_s` or `epoch_ms`."
+ "python datetime string pattern</a> expression which needs to "
+ 'adhere to the <a
href="https://en.wikipedia.org/wiki/ISO_8601";>'
+ "ISO 8601</a> standard to ensure that the lexicographical
ordering "
+ "coincides with the chronological ordering. If the timestamp "
+ "format does not adhere to the ISO 8601 standard you will need
to "
+ "define an expression and type for transforming the string
into a "
+ "date or timestamp. Note currently time zones are not
supported. "
+ "If time is stored in epoch format, put `epoch_s` or
`epoch_ms`."
),
True,
),
@@ -121,6 +128,24 @@ class TableColumnInlineView(CompactCRUDMixin,
SupersetModelView): # noqa
"python_date_format": _("Datetime Format"),
"type": _("Type"),
}
+ validators_columns = {
+ "python_date_format": [
+ # Restrict viable values to epoch_s, epoch_ms, or a strftime format
+ # which adhere's to the ISO 8601 format (without time zone).
+ Regexp(
+ re.compile(
+ r"""
+ ^(
+ epoch_s|epoch_ms|
+
(?P<date>%Y(-%m(-%d)?)?)([\sT](?P<time>%H(:%M(:%S(\.%f)?)?)?))?
+ )$
+ """,
+ re.VERBOSE,
+ ),
+ message=_("Invalid date/timestamp format"),
+ )
+ ]
+ }
add_form_extra_fields = {
"table": QuerySelectField(
@@ -303,7 +328,6 @@ class TableModelView(DatasourceModelView, DeleteMixin,
YamlExportMixin): # noqa
"template_params": _("Template parameters"),
"modified": _("Modified"),
}
-
edit_form_extra_fields = {
"database": QuerySelectField(
"Database",
