def test_where_clause_n_prefix(app_context: AppContext) -> None:
from superset.db_engine_specs.mssql import MssqlEngineSpec
dialect = mssql.dialect()
# non-unicode col
sqla_column_type = MssqlEngineSpec.get_sqla_column_type("VARCHAR(10)")
assert sqla_column_type is not None
type_, _ = sqla_column_type
str_col = column("col", type_=type_)
# unicode col
sqla_column_type = MssqlEngineSpec.get_sqla_column_type("NTEXT")
assert sqla_column_type is not None
type_, _ = sqla_column_type
unicode_col = column("unicode_col", type_=type_)
tbl = table("tbl")
sel = (select([
str_col, unicode_col
]).select_from(tbl).where(str_col == "abc").where(unicode_col == "abc"))
query = str(
sel.compile(dialect=dialect, compile_kwargs={"literal_binds": True}))
query_expected = ("SELECT col, unicode_col \n"
"FROM tbl \n"
"WHERE col = 'abc' AND unicode_col = N'abc'")
assert query == query_expected
def test_apply_limit(self):
def compile_sqla_query(qry: Select, schema: Optional[str] = None) -> str:
return str(
qry.compile(
dialect=mssql.dialect(), compile_kwargs={"literal_binds": True}
)
)
database = Database(
database_name="mssql_test",
sqlalchemy_uri="mssql+pymssql://sa:Password_123@localhost:1433/msdb",
)
db.session.add(database)
db.session.commit()
with mock.patch.object(database, "compile_sqla_query", new=compile_sqla_query):
test_sql = "SELECT COUNT(*) FROM FOO_TABLE"
limited_sql = MssqlEngineSpec.apply_limit_to_sql(test_sql, 1000, database)
expected_sql = (
"SELECT TOP 1000 * \n"
"FROM (SELECT COUNT(*) AS COUNT_1 FROM FOO_TABLE) AS inner_qry"
)
self.assertEqual(expected_sql, limited_sql)
test_sql = "SELECT COUNT(*), SUM(id) FROM FOO_TABLE"
limited_sql = MssqlEngineSpec.apply_limit_to_sql(test_sql, 1000, database)
expected_sql = (
"SELECT TOP 1000 * \n"
"FROM (SELECT COUNT(*) AS COUNT_1, SUM(id) AS SUM_2 FROM FOO_TABLE) "
"AS inner_qry"
)
self.assertEqual(expected_sql, limited_sql)
test_sql = "SELECT COUNT(*), FOO_COL1 FROM FOO_TABLE GROUP BY FOO_COL1"
limited_sql = MssqlEngineSpec.apply_limit_to_sql(test_sql, 1000, database)
expected_sql = (
"SELECT TOP 1000 * \n"
"FROM (SELECT COUNT(*) AS COUNT_1, "
"FOO_COL1 FROM FOO_TABLE GROUP BY FOO_COL1)"
" AS inner_qry"
)
self.assertEqual(expected_sql, limited_sql)
test_sql = "SELECT COUNT(*), COUNT(*) FROM FOO_TABLE"
limited_sql = MssqlEngineSpec.apply_limit_to_sql(test_sql, 1000, database)
expected_sql = (
"SELECT TOP 1000 * \n"
"FROM (SELECT COUNT(*) AS COUNT_1, COUNT(*) AS COUNT_2 FROM FOO_TABLE)"
" AS inner_qry"
)
self.assertEqual(expected_sql, limited_sql)
db.session.delete(database)
db.session.commit()
def assert_type(type_string, type_expected, generic_type_expected):
if type_expected is None:
type_assigned = MssqlEngineSpec.get_sqla_column_type(type_string)
self.assertIsNone(type_assigned)
else:
column_spec = MssqlEngineSpec.get_column_spec(type_string)
if column_spec != None:
self.assertIsInstance(column_spec.sqla_type, type_expected)
self.assertEquals(column_spec.generic_type, generic_type_expected)
def test_mssql_column_types(
type_string: str,
type_expected: TypeEngine,
generic_type_expected: GenericDataType,
) -> None:
from superset.db_engine_specs.mssql import MssqlEngineSpec
if type_expected is None:
type_assigned = MssqlEngineSpec.get_sqla_column_type(type_string)
assert type_assigned is None
else:
column_spec = MssqlEngineSpec.get_column_spec(type_string)
if column_spec is not None:
assert isinstance(column_spec.sqla_type, type_expected)
assert column_spec.generic_type == generic_type_expected
def test_time_exp_mixd_case_col_1y(app_context: AppContext) -> None:
from superset.db_engine_specs.mssql import MssqlEngineSpec
col = column("MixedCase")
expr = MssqlEngineSpec.get_timestamp_expr(col, None, "P1Y")
result = str(expr.compile(None, dialect=mssql.dialect()))
assert result == "DATEADD(YEAR, DATEDIFF(YEAR, 0, [MixedCase]), 0)"
def test_fetch_data(self, mock_pyodbc_rows_to_tuples):
data = [(1, "foo")]
with mock.patch.object(BaseEngineSpec, "fetch_data",
return_value=data) as mock_fetch:
result = MssqlEngineSpec.fetch_data(None, 0)
mock_pyodbc_rows_to_tuples.assert_called_once_with(data)
self.assertEqual(result, "converted")
def test_column_datatype_to_string(original: TypeEngine,
expected: str) -> None:
from superset.db_engine_specs.mssql import MssqlEngineSpec
actual = MssqlEngineSpec.column_datatype_to_string(original,
mssql.dialect())
assert actual == expected
def test_convert_dttm(self):
dttm = self.get_dttm()
self.assertEqual(
MssqlEngineSpec.convert_dttm("DATE", dttm),
"CONVERT(DATE, '2019-01-02', 23)",
)
self.assertEqual(
MssqlEngineSpec.convert_dttm("DATETIME", dttm),
"CONVERT(DATETIME, '2019-01-02T03:04:05.678', 126)",
)
self.assertEqual(
MssqlEngineSpec.convert_dttm("SMALLDATETIME", dttm),
"CONVERT(SMALLDATETIME, '2019-01-02 03:04:05', 20)",
)
def test_convert_dttm(
actual: str,
expected: str,
dttm: datetime,
) -> None:
from superset.db_engine_specs.mssql import MssqlEngineSpec
assert MssqlEngineSpec.convert_dttm(actual, dttm) == expected
def test_convert_dttm(self):
dttm = self.get_dttm()
test_cases = (
(
MssqlEngineSpec.convert_dttm("DATE", dttm),
"CONVERT(DATE, '2019-01-02', 23)",
),
(
MssqlEngineSpec.convert_dttm("DATETIME", dttm),
"CONVERT(DATETIME, '2019-01-02T03:04:05.678', 126)",
),
(
MssqlEngineSpec.convert_dttm("SMALLDATETIME", dttm),
"CONVERT(SMALLDATETIME, '2019-01-02 03:04:05', 20)",
),
)
for actual, expected in test_cases:
self.assertEqual(actual, expected)
def test_fetch_data(app_context: AppContext) -> None:
from superset.db_engine_specs.base import BaseEngineSpec
from superset.db_engine_specs.mssql import MssqlEngineSpec
with mock.patch.object(
MssqlEngineSpec, "pyodbc_rows_to_tuples", return_value="converted",
) as mock_pyodbc_rows_to_tuples:
data = [(1, "foo")]
with mock.patch.object(BaseEngineSpec, "fetch_data", return_value=data):
result = MssqlEngineSpec.fetch_data(None, 0)
mock_pyodbc_rows_to_tuples.assert_called_once_with(data)
assert result == "converted"
def test_extract_error_message(self):
test_mssql_exception = Exception(
"(8155, b\"No column name was specified for column 1 of 'inner_qry'."
"DB-Lib error message 20018, severity 16:\\nGeneral SQL Server error: "
'Check messages from the SQL Server\\n")')
error_message = MssqlEngineSpec.extract_error_message(
test_mssql_exception)
expected_message = (
"mssql error: All your SQL functions need to "
"have an alias on MSSQL. For example: SELECT COUNT(*) AS C1 FROM TABLE1"
)
self.assertEqual(expected_message, error_message)
test_mssql_exception = Exception(
'(8200, b"A correlated expression is invalid because it is not in a '
"GROUP BY clause.\\n\")'")
error_message = MssqlEngineSpec.extract_error_message(
test_mssql_exception)
expected_message = "mssql error: " + MssqlEngineSpec._extract_error_message(
test_mssql_exception)
self.assertEqual(expected_message, error_message)
def test_extract_error_message(app_context: AppContext) -> None:
from superset.db_engine_specs.mssql import MssqlEngineSpec
test_mssql_exception = Exception(
"(8155, b\"No column name was specified for column 1 of 'inner_qry'."
"DB-Lib error message 20018, severity 16:\\nGeneral SQL Server error: "
'Check messages from the SQL Server\\n")')
error_message = MssqlEngineSpec.extract_error_message(test_mssql_exception)
expected_message = (
"mssql error: All your SQL functions need to "
"have an alias on MSSQL. For example: SELECT COUNT(*) AS C1 FROM TABLE1"
)
assert expected_message == error_message
test_mssql_exception = Exception(
'(8200, b"A correlated expression is invalid because it is not in a '
"GROUP BY clause.\\n\")'")
error_message = MssqlEngineSpec.extract_error_message(test_mssql_exception)
expected_message = "mssql error: " + MssqlEngineSpec._extract_error_message(
test_mssql_exception)
assert expected_message == error_message
def test_column_datatype_to_string(self):
test_cases = (
(DATE(), "DATE"),
(VARCHAR(length=255), "VARCHAR(255)"),
(VARCHAR(length=255, collation="utf8_general_ci"), "VARCHAR(255)"),
(NVARCHAR(length=128), "NVARCHAR(128)"),
(TEXT(), "TEXT"),
(NTEXT(collation="utf8_general_ci"), "NTEXT"),
)
for original, expected in test_cases:
actual = MssqlEngineSpec.column_datatype_to_string(
original, mssql.dialect())
self.assertEqual(actual, expected)
def test_time_exp_mixd_case_col_1y(self):
col = column("MixedCase")
expr = MssqlEngineSpec.get_timestamp_expr(col, None, "P1Y")
result = str(expr.compile(None, dialect=mssql.dialect()))
self.assertEqual(result,
"DATEADD(year, DATEDIFF(year, 0, [MixedCase]), 0)")
def test_extract_errors(self):
"""
Test that custom error messages are extracted correctly.
"""
msg = dedent("""
DB-Lib error message 20009, severity 9:
Unable to connect: Adaptive Server is unavailable or does not exist (locahost)
""")
result = MssqlEngineSpec.extract_errors(Exception(msg))
assert result == [
SupersetError(
error_type=SupersetErrorType.CONNECTION_INVALID_HOSTNAME_ERROR,
message='The hostname "locahost" cannot be resolved.',
level=ErrorLevel.ERROR,
extra={
"engine_name":
"Microsoft SQL",
"issue_codes": [{
"code":
1007,
"message":
"Issue 1007 - The hostname provided can't be resolved.",
}],
},
)
]
msg = dedent("""
DB-Lib error message 20009, severity 9:
Unable to connect: Adaptive Server is unavailable or does not exist (localhost)
Net-Lib error during Connection refused (61)
DB-Lib error message 20009, severity 9:
Unable to connect: Adaptive Server is unavailable or does not exist (localhost)
Net-Lib error during Connection refused (61)
""")
result = MssqlEngineSpec.extract_errors(Exception(msg),
context={
"port": 12345,
"hostname": "localhost"
})
assert result == [
SupersetError(
error_type=SupersetErrorType.CONNECTION_PORT_CLOSED_ERROR,
message=
'Port 12345 on hostname "localhost" refused the connection.',
level=ErrorLevel.ERROR,
extra={
"engine_name":
"Microsoft SQL",
"issue_codes": [{
"code":
1008,
"message":
"Issue 1008 - The port is closed."
}],
},
)
]
msg = dedent("""
DB-Lib error message 20009, severity 9:
Unable to connect: Adaptive Server is unavailable or does not exist (example.com)
Net-Lib error during Operation timed out (60)
DB-Lib error message 20009, severity 9:
Unable to connect: Adaptive Server is unavailable or does not exist (example.com)
Net-Lib error during Operation timed out (60)
""")
result = MssqlEngineSpec.extract_errors(Exception(msg),
context={
"port": 12345,
"hostname": "example.com"
})
assert result == [
SupersetError(
error_type=SupersetErrorType.CONNECTION_HOST_DOWN_ERROR,
message=('The host "example.com" might be down, '
"and can't be reached on port 12345."),
level=ErrorLevel.ERROR,
extra={
"engine_name":
"Microsoft SQL",
"issue_codes": [{
"code":
1009,
"message":
"Issue 1009 - The host might be down, and can't be reached on the provided port.",
}],
},
)
]
msg = dedent("""
DB-Lib error message 20009, severity 9:
Unable to connect: Adaptive Server is unavailable or does not exist (93.184.216.34)
Net-Lib error during Operation timed out (60)
DB-Lib error message 20009, severity 9:
Unable to connect: Adaptive Server is unavailable or does not exist (93.184.216.34)
Net-Lib error during Operation timed out (60)
""")
result = MssqlEngineSpec.extract_errors(Exception(msg),
context={
"port": 12345,
"hostname": "93.184.216.34"
})
assert result == [
SupersetError(
error_type=SupersetErrorType.CONNECTION_HOST_DOWN_ERROR,
message=('The host "93.184.216.34" might be down, '
"and can't be reached on port 12345."),
level=ErrorLevel.ERROR,
extra={
"engine_name":
"Microsoft SQL",
"issue_codes": [{
"code":
1009,
"message":
"Issue 1009 - The host might be down, and can't be reached on the provided port.",
}],
},
)
]
msg = dedent("""
DB-Lib error message 20018, severity 14:
General SQL Server error: Check messages from the SQL Server
DB-Lib error message 20002, severity 9:
Adaptive Server connection failed (mssqldb.cxiotftzsypc.us-west-2.rds.amazonaws.com)
DB-Lib error message 20002, severity 9:
Adaptive Server connection failed (mssqldb.cxiotftzsypc.us-west-2.rds.amazonaws.com)
""")
result = MssqlEngineSpec.extract_errors(
Exception(msg), context={"username": "******"})
assert result == [
SupersetError(
message=
'Either the username "testuser" or the password is incorrect.',
error_type=SupersetErrorType.CONNECTION_ACCESS_DENIED_ERROR,
level=ErrorLevel.ERROR,
extra={
"engine_name":
"Microsoft SQL",
"issue_codes": [{
"code":
1014,
"message":
"Issue 1014 - Either the username or the password is wrong.",
}],
},
)
]
def test_top_query_parsing(original: TypeEngine, expected: str,
top: int) -> None:
from superset.db_engine_specs.mssql import MssqlEngineSpec
actual = MssqlEngineSpec.apply_top_to_sql(original, top)
assert actual == expected
def assert_type(type_string, type_expected):
type_assigned = MssqlEngineSpec.get_sqla_column_type(type_string)
if type_expected is None:
self.assertIsNone(type_assigned)
else:
self.assertIsInstance(type_assigned, type_expected)
def test_cte_query_parsing(app_context: AppContext, original: TypeEngine,
expected: str) -> None:
from superset.db_engine_specs.mssql import MssqlEngineSpec
actual = MssqlEngineSpec.get_cte_query(original)
assert actual == expected
def test_mssql_time_expression_mixed_case_column_1y_grain(self):
col = column('MixedCase')
expr = MssqlEngineSpec.get_timestamp_expr(col, None, 'P1Y')
result = str(expr.compile(dialect=mssql.dialect()))
self.assertEqual(result,
'DATEADD(year, DATEDIFF(year, 0, [MixedCase]), 0)')
