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)')