def test_owner_database_pairs_switch_for_different_db(self): dialect = mssql.dialect() identifier = "my_other_db.some_schema" schema, owner = base._owner_plus_db(dialect, identifier) mock_connection = mock.Mock( dialect=dialect, exec_driver_sql=mock.Mock(return_value=mock.Mock(scalar=mock.Mock( return_value="my_db"))), ) mock_lambda = mock.Mock() base._switch_db(schema, mock_connection, mock_lambda, "x", y="bar") eq_( mock_connection.mock_calls, [ mock.call.exec_driver_sql("select db_name()"), mock.call.exec_driver_sql("use my_other_db"), mock.call.exec_driver_sql("use my_db"), ], eq_( mock_connection.exec_driver_sql.return_value.mock_calls, [mock.call.scalar()], ), ) eq_(mock_lambda.mock_calls, [mock.call("x", y="bar")])
def test_limit_offset_with_correlated_order_by(self): t1 = table('t1', column('x', Integer), column('y', Integer)) t2 = table('t2', column('x', Integer), column('y', Integer)) order_by = select([t2.c.y]).where(t1.c.x == t2.c.x).as_scalar() s = select([t1]).where(t1.c.x == 5).order_by(order_by) \ .limit(10).offset(20) self.assert_compile( s, "SELECT anon_1.x, anon_1.y " "FROM (SELECT t1.x AS x, t1.y AS y, " "ROW_NUMBER() OVER (ORDER BY " "(SELECT t2.y FROM t2 WHERE t1.x = t2.x)" ") AS mssql_rn " "FROM t1 " "WHERE t1.x = :x_1) AS anon_1 " "WHERE mssql_rn > :param_1 AND mssql_rn <= :param_2 + :param_1", checkparams={'param_1': 20, 'param_2': 10, 'x_1': 5} ) c = s.compile(dialect=mssql.dialect()) eq_(len(c._result_columns), 2) assert t1.c.x in set(c._create_result_map()['x'][1]) assert t1.c.y in set(c._create_result_map()['y'][1])
def test_recursive(self): parts = table('parts', column('part'), column('sub_part'), column('quantity'), ) included_parts = select([ parts.c.sub_part, parts.c.part, parts.c.quantity]).\ where(parts.c.part=='our part').\ cte(recursive=True) incl_alias = included_parts.alias() parts_alias = parts.alias() included_parts = included_parts.union( select([ parts_alias.c.part, parts_alias.c.sub_part, parts_alias.c.quantity]).\ where(parts_alias.c.part==incl_alias.c.sub_part) ) s = select([ included_parts.c.sub_part, func.sum(included_parts.c.quantity).label('total_quantity')]).\ select_from(included_parts.join( parts,included_parts.c.part==parts.c.part)).\ group_by(included_parts.c.sub_part) self.assert_compile(s, "WITH RECURSIVE anon_1(sub_part, part, quantity) " "AS (SELECT parts.sub_part AS sub_part, parts.part " "AS part, parts.quantity AS quantity FROM parts " "WHERE parts.part = :part_1 UNION SELECT parts_1.part " "AS part, parts_1.sub_part AS sub_part, parts_1.quantity " "AS quantity FROM parts AS parts_1, anon_1 AS anon_2 " "WHERE parts_1.part = anon_2.sub_part) " "SELECT anon_1.sub_part, " "sum(anon_1.quantity) AS total_quantity FROM anon_1 " "JOIN parts ON anon_1.part = parts.part " "GROUP BY anon_1.sub_part" ) # quick check that the "WITH RECURSIVE" varies per # dialect self.assert_compile(s, "WITH anon_1(sub_part, part, quantity) " "AS (SELECT parts.sub_part AS sub_part, parts.part " "AS part, parts.quantity AS quantity FROM parts " "WHERE parts.part = :part_1 UNION SELECT parts_1.part " "AS part, parts_1.sub_part AS sub_part, parts_1.quantity " "AS quantity FROM parts AS parts_1, anon_1 AS anon_2 " "WHERE parts_1.part = anon_2.sub_part) " "SELECT anon_1.sub_part, " "sum(anon_1.quantity) AS total_quantity FROM anon_1 " "JOIN parts ON anon_1.part = parts.part " "GROUP BY anon_1.sub_part", dialect=mssql.dialect() )
def test_owner_database_pairs(self, identifier, expected_schema, expected_owner, use_stmt): dialect = mssql.dialect() schema, owner = base._owner_plus_db(dialect, identifier) eq_(owner, expected_owner) eq_(schema, expected_schema) mock_connection = mock.Mock( dialect=dialect, exec_driver_sql=mock.Mock(return_value=mock.Mock(scalar=mock.Mock( return_value="Some Database"))), ) mock_lambda = mock.Mock() base._switch_db(schema, mock_connection, mock_lambda, "x", y="bar") if schema is None: eq_(mock_connection.mock_calls, []) else: eq_( mock_connection.mock_calls, [ mock.call.exec_driver_sql("select db_name()"), mock.call.exec_driver_sql(use_stmt), mock.call.exec_driver_sql("use [Some Database]"), ], ) eq_( mock_connection.exec_driver_sql.return_value.mock_calls, [mock.call.scalar()], ) eq_(mock_lambda.mock_calls, [mock.call("x", y="bar")])
def test_missing_multiple_primary_keys(self): metadata: MetaData = MetaData() table_without_primary_key: Table = Table( "TableWithoutPrimaryKey", metadata, Column("arbitrary_column", String()), ) second_table_without_primary_key: Table = Table( "SecondTableWithoutPrimaryKey", metadata, Column("second_arbitrary_column", String()), ) faulty_vertex_name_to_table: Dict[str, Table] = { table_without_primary_key.name: table_without_primary_key, second_table_without_primary_key.name: second_table_without_primary_key, } with self.assertRaises( MissingPrimaryKeyError) as missing_primary_key_error_info: get_sqlalchemy_schema_info(faulty_vertex_name_to_table, {}, dialect()) exception_message: str = missing_primary_key_error_info.exception.args[ 0] for table_name in faulty_vertex_name_to_table: self.assertIn(table_name, exception_message)
def test_noorderby_insubquery_limit_offset_newstyle(self): """test "no ORDER BY in subqueries unless TOP / LIMIT / OFFSET" present""" table1 = table( "mytable", column("myid", Integer), column("name", String), column("description", String), ) q = (select([table1.c.myid], order_by=[table1.c.myid ]).limit(10).offset(10).alias("foo")) crit = q.c.myid == table1.c.myid dialect = mssql.dialect() dialect._supports_offset_fetch = True self.assert_compile( select(["*"], crit), "SELECT * FROM (SELECT mytable.myid AS myid FROM mytable " "ORDER BY mytable.myid OFFSET :param_1 ROWS " "FETCH NEXT :param_2 ROWS ONLY ) AS foo, " "mytable WHERE foo.myid = mytable.myid", dialect=dialect, )
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_noorderby_parameters_insubquery(self): """test that the ms-sql dialect does not include ORDER BY positional parameters in subqueries""" table1 = table( "mytable", column("myid", Integer), column("name", String), column("description", String), ) q = select( [table1.c.myid, sql.literal('bar').label('c1')], order_by=[table1.c.name + '-'] ).alias("foo") crit = q.c.myid == table1.c.myid dialect = mssql.dialect() dialect.paramstyle = "qmark" dialect.positional = True self.assert_compile( select(["*"], crit), "SELECT * FROM (SELECT mytable.myid AS " "myid, ? AS c1 FROM mytable) AS foo, mytable WHERE " "foo.myid = mytable.myid", dialect=dialect, checkparams={'param_1': 'bar'}, # if name_1 is included, too many parameters are passed to dbapi checkpositional=('bar', ) )
def test_limit_offset_w_ambiguous_cols(self): t = table("t", column("x", Integer), column("y", Integer)) cols = [t.c.x, t.c.x.label("q"), t.c.x.label("p"), t.c.y] s = select(cols).where(t.c.x == 5).order_by(t.c.y).limit(10).offset(20) self.assert_compile( s, "SELECT anon_1.x, anon_1.q, anon_1.p, anon_1.y " "FROM (SELECT t.x AS x, t.x AS q, t.x AS p, t.y AS y, " "ROW_NUMBER() OVER (ORDER BY t.y) AS mssql_rn " "FROM t " "WHERE t.x = :x_1) AS anon_1 " "WHERE mssql_rn > :param_1 AND mssql_rn <= :param_2 + :param_1", checkparams={ "param_1": 20, "param_2": 10, "x_1": 5 }, ) c = s.compile(dialect=mssql.dialect()) eq_(len(c._result_columns), 4) result_map = c._create_result_map() for col in cols: is_(result_map[col.key][1][0], col)
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_limit_offset_with_correlated_order_by(self): t1 = table("t1", column("x", Integer), column("y", Integer)) t2 = table("t2", column("x", Integer), column("y", Integer)) order_by = select([t2.c.y]).where(t1.c.x == t2.c.x).scalar_subquery() s = (select( [t1]).where(t1.c.x == 5).order_by(order_by).limit(10).offset(20)) self.assert_compile( s, "SELECT anon_1.x, anon_1.y " "FROM (SELECT t1.x AS x, t1.y AS y, " "ROW_NUMBER() OVER (ORDER BY " "(SELECT t2.y FROM t2 WHERE t1.x = t2.x)" ") AS mssql_rn " "FROM t1 " "WHERE t1.x = :x_1) AS anon_1 " "WHERE mssql_rn > :param_1 AND mssql_rn <= :param_2 + :param_1", checkparams={ "param_1": 20, "param_2": 10, "x_1": 5 }, ) c = s.compile(dialect=mssql.dialect()) eq_(len(c._result_columns), 2) assert t1.c.x in set(c._create_result_map()["x"][1]) assert t1.c.y in set(c._create_result_map()["y"][1])
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_function_calls_base(self): from sqlalchemy.dialects import mssql class greatest(FunctionElement): type = Numeric() name = 'greatest' @compiles(greatest) def default_greatest(element, compiler, **kw): return compiler.visit_function(element) @compiles(greatest, 'mssql') def case_greatest(element, compiler, **kw): arg1, arg2 = list(element.clauses) return "CASE WHEN %s > %s THEN %s ELSE %s END" % ( compiler.process(arg1), compiler.process(arg2), compiler.process(arg1), compiler.process(arg2), ) self.assert_compile( greatest('a', 'b'), 'greatest(:greatest_1, :greatest_2)', use_default_dialect=True ) self.assert_compile( greatest('a', 'b'), "CASE WHEN :greatest_1 > :greatest_2 " "THEN :greatest_1 ELSE :greatest_2 END", dialect=mssql.dialect() )
def test_noorderby_parameters_insubquery(self): """test that the ms-sql dialect does not include ORDER BY positional parameters in subqueries""" table1 = table( "mytable", column("myid", Integer), column("name", String), column("description", String), ) q = select( [table1.c.myid, sql.literal("bar").label("c1")], order_by=[table1.c.name + "-"], ).alias("foo") crit = q.c.myid == table1.c.myid dialect = mssql.dialect() dialect.paramstyle = "qmark" dialect.positional = True self.assert_compile( select(["*"], crit), "SELECT * FROM (SELECT mytable.myid AS " "myid, ? AS c1 FROM mytable) AS foo, mytable WHERE " "foo.myid = mytable.myid", dialect=dialect, checkparams={"param_1": "bar"}, # if name_1 is included, too many parameters are passed to dbapi checkpositional=("bar", ), )
def test_limit_offset_with_correlated_order_by(self): t1 = table("t1", column("x", Integer), column("y", Integer)) t2 = table("t2", column("x", Integer), column("y", Integer)) order_by = select([t2.c.y]).where(t1.c.x == t2.c.x).scalar_subquery() s = ( select([t1]) .where(t1.c.x == 5) .order_by(order_by) .limit(10) .offset(20) ) self.assert_compile( s, "SELECT anon_1.x, anon_1.y " "FROM (SELECT t1.x AS x, t1.y AS y, " "ROW_NUMBER() OVER (ORDER BY " "(SELECT t2.y FROM t2 WHERE t1.x = t2.x)" ") AS mssql_rn " "FROM t1 " "WHERE t1.x = :x_1) AS anon_1 " "WHERE mssql_rn > :param_1 AND mssql_rn <= :param_2 + :param_1", checkparams={"param_1": 20, "param_2": 10, "x_1": 5}, ) c = s.compile(dialect=mssql.dialect()) eq_(len(c._result_columns), 2) assert t1.c.x in set(c._create_result_map()["x"][1]) assert t1.c.y in set(c._create_result_map()["y"][1])
def test_recursive(self): parts = table( 'parts', column('part'), column('sub_part'), column('quantity'), ) included_parts = select([ parts.c.sub_part, parts.c.part, parts.c.quantity]).\ where(parts.c.part == 'our part').\ cte(recursive=True) incl_alias = included_parts.alias() parts_alias = parts.alias() included_parts = included_parts.union( select([ parts_alias.c.sub_part, parts_alias.c.part, parts_alias.c.quantity ]).where(parts_alias.c.part == incl_alias.c.sub_part)) s = select([ included_parts.c.sub_part, func.sum(included_parts.c.quantity).label('total_quantity')]).\ select_from(included_parts.join( parts, included_parts.c.part == parts.c.part)).\ group_by(included_parts.c.sub_part) self.assert_compile( s, "WITH RECURSIVE anon_1(sub_part, part, quantity) " "AS (SELECT parts.sub_part AS sub_part, parts.part " "AS part, parts.quantity AS quantity FROM parts " "WHERE parts.part = :part_1 UNION " "SELECT parts_1.sub_part AS sub_part, " "parts_1.part AS part, parts_1.quantity " "AS quantity FROM parts AS parts_1, anon_1 AS anon_2 " "WHERE parts_1.part = anon_2.sub_part) " "SELECT anon_1.sub_part, " "sum(anon_1.quantity) AS total_quantity FROM anon_1 " "JOIN parts ON anon_1.part = parts.part " "GROUP BY anon_1.sub_part") # quick check that the "WITH RECURSIVE" varies per # dialect self.assert_compile( s, "WITH anon_1(sub_part, part, quantity) " "AS (SELECT parts.sub_part AS sub_part, parts.part " "AS part, parts.quantity AS quantity FROM parts " "WHERE parts.part = :part_1 UNION " "SELECT parts_1.sub_part AS sub_part, " "parts_1.part AS part, parts_1.quantity " "AS quantity FROM parts AS parts_1, anon_1 AS anon_2 " "WHERE parts_1.part = anon_2.sub_part) " "SELECT anon_1.sub_part, " "sum(anon_1.quantity) AS total_quantity FROM anon_1 " "JOIN parts ON anon_1.part = parts.part " "GROUP BY anon_1.sub_part", dialect=mssql.dialect())
def setup(self): t = Table('sometable', MetaData(), Column('pk_column', Integer), Column('test_column', String)) self.column = t.c.test_column dialect = mssql.dialect() self.ddl_compiler = dialect.ddl_compiler(dialect, schema.CreateTable(t))
def test_reference_to_non_existent_destination_column(self): direct_edges = { "invalid_destination_column": DirectEdgeDescriptor("Table1", "source_column", "ArbitraryObjectName", "invalid_column_name") } with self.assertRaises(InvalidSQLEdgeError): get_sqlalchemy_schema_info(self.vertex_name_to_table, direct_edges, dialect())
def test_print_query_mssql_basic(self) -> None: query = sqlalchemy.select( [self.sql_schema_info.vertex_name_to_table["Animal"].c.name]) text = print_sqlalchemy_query_string(query, mssql.dialect()) expected_text = """ SELECT db_1.schema_1.[Animal].name FROM db_1.schema_1.[Animal] """ compare_sql(self, expected_text, text)
def test_info_unicode_cast_no_2000(self): dialect = mssql.dialect() dialect.server_version_info = base.MS_2000_VERSION stmt = tables.c.table_name == "somename" self.assert_compile( stmt, "[INFORMATION_SCHEMA].[TABLES].[TABLE_NAME] = :table_name_1", dialect=dialect, )
def test_reference_to_non_existent_destination_vertex(self) -> None: direct_edges = { "invalid_source_vertex": DirectEdgeDescriptor("Table1", "source_column", "InvalidVertexName", "destination_column") } with self.assertRaises(InvalidSQLEdgeError): get_sqlalchemy_schema_info(self.vertex_name_to_table, direct_edges, dialect())
def test_reference_to_non_existent_destination_column(self): direct_edges = { 'invalid_destination_column': DirectEdgeDescriptor('Table1', 'source_column', 'ArbitraryObjectName', 'invalid_column_name') } with self.assertRaises(InvalidSQLEdgeError): get_sqlalchemy_schema_info_from_specified_metadata( self.vertex_name_to_table, direct_edges, dialect())
def test_info_unicode_cast(self): dialect = mssql.dialect() dialect.server_version_info = base.MS_2005_VERSION stmt = tables.c.table_name == "somename" self.assert_compile( stmt, "[INFORMATION_SCHEMA].[TABLES].[TABLE_NAME] = " "CAST(:table_name_1 AS NVARCHAR(max))", dialect=dialect, )
def setup(self): t = Table('sometable', MetaData(), Column('pk_column', Integer), Column('test_column', String) ) self.column = t.c.test_column dialect = mssql.dialect() self.ddl_compiler = dialect.ddl_compiler(dialect, schema.CreateTable(t))
def test_print_query_mssql_list_argument(self) -> None: animal = self.sql_schema_info.vertex_name_to_table["Animal"].alias() query = sqlalchemy.select([animal.c.name]).where( animal.c.name.in_(sqlalchemy.bindparam("names", expanding=True))) text = print_sqlalchemy_query_string(query, mssql.dialect()) expected_text = """ SELECT [Animal_1].name FROM db_1.schema_1.[Animal] AS [Animal_1] WHERE [Animal_1].name IN :names """ compare_sql(self, expected_text, text)
def test_default_schema_name_not_interpreted_as_tokenized(self): dialect = mssql.dialect() dialect.server_version_info = base.MS_2014_VERSION mock_connection = mock.Mock(scalar=lambda sql: "Jonah.The.Whale") schema_name = dialect._get_default_schema_name(mock_connection) eq_(schema_name, "Jonah.The.Whale") eq_( base._owner_plus_db(dialect, schema_name), (None, "Jonah.The.Whale"), )
def setup(self): t = Table( "sometable", MetaData(), Column("pk_column", Integer), Column("test_column", String), ) self.column = t.c.test_column dialect = mssql.dialect() self.ddl_compiler = dialect.ddl_compiler(dialect, schema.CreateTable(t))
def test_owner_database_pairs_dont_use_for_same_db(self): dialect = mssql.dialect() identifier = "my_db.some_schema" schema, owner = base._owner_plus_db(dialect, identifier) mock_connection = mock.Mock(dialect=dialect, scalar=mock.Mock(return_value="my_db")) mock_lambda = mock.Mock() base._switch_db(schema, mock_connection, mock_lambda, "x", y="bar") eq_(mock_connection.mock_calls, [mock.call.scalar("select db_name()")]) eq_(mock_lambda.mock_calls, [mock.call("x", y="bar")])
def test_missing_primary_key(self): table_without_primary_key = Table( 'TableWithoutPrimaryKey', MetaData(), Column('arbitrary_column', String()), ) faulty_vertex_name_to_table = { table_without_primary_key.name: table_without_primary_key } with self.assertRaises(MissingPrimaryKeyError): get_sqlalchemy_schema_info_from_specified_metadata( faulty_vertex_name_to_table, {}, dialect())
def test_limit_zero_using_top(self): t = table('t', column('x', Integer), column('y', Integer)) s = select([t]).where(t.c.x == 5).order_by(t.c.y).limit(0) self.assert_compile( s, "SELECT TOP 0 t.x, t.y FROM t WHERE t.x = :x_1 ORDER BY t.y", checkparams={'x_1': 5}) c = s.compile(dialect=mssql.dialect()) eq_(len(c._result_columns), 2) assert t.c.x in set(c._create_result_map()['x'][1])
def test_missing_primary_key(self) -> None: table_without_primary_key = Table( "TableWithoutPrimaryKey", MetaData(), Column("arbitrary_column", String()), ) faulty_vertex_name_to_table = { table_without_primary_key.name: table_without_primary_key } with self.assertRaises(MissingPrimaryKeyError): get_sqlalchemy_schema_info(faulty_vertex_name_to_table, {}, dialect())
def setup(self): t = Table( "sometable", MetaData(), Column("pk_column", Integer), Column("test_column", String), ) self.column = t.c.test_column dialect = mssql.dialect() self.ddl_compiler = dialect.ddl_compiler( dialect, schema.CreateTable(t) )
def test_limit_zero_using_top(self): t = table('t', column('x', Integer), column('y', Integer)) s = select([t]).where(t.c.x == 5).order_by(t.c.y).limit(0) self.assert_compile( s, "SELECT TOP 0 t.x, t.y FROM t WHERE t.x = :x_1 ORDER BY t.y", checkparams={'x_1': 5} ) c = s.compile(dialect=mssql.dialect()) eq_(len(c._result_columns), 2) assert t.c.x in set(c._create_result_map()['x'][1])
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_owner_database_pairs(self): dialect = mssql.dialect() for identifier, expected_schema, expected_owner in [ ("foo", None, "foo"), ("foo.bar", "foo", "bar"), ("Foo.Bar", "Foo", "Bar"), ("[Foo.Bar]", None, "Foo.Bar"), ("[Foo.Bar].[bat]", "Foo.Bar", "bat"), ]: schema, owner = base._owner_plus_db(dialect, identifier) eq_(owner, expected_owner) eq_(schema, expected_schema)
def test_compute_accuracy_as_sql_int_values() -> None: expected_expression = ( "1 - abs(0 - [Prediction]) / CASE WHEN ([Prediction] = 0)" " THEN 1 WHEN ([Prediction] < 0) THEN 0 ELSE [Prediction] END" ) assert ( str( compute_accuracy_as_sql(0, Column("Prediction", Float, nullable=False)).compile( compile_kwargs={"literal_binds": True}, dialect=mssql.dialect() # type: ignore ) ) == expected_expression )
def test_limit_offset_using_window(self): t = table('t', column('x', Integer), column('y', Integer)) s = select([t]).where(t.c.x == 5).order_by(t.c.y).limit(10).offset(20) self.assert_compile( s, "SELECT anon_1.x, anon_1.y " "FROM (SELECT t.x AS x, t.y AS y, " "ROW_NUMBER() OVER (ORDER BY t.y) AS mssql_rn " "FROM t " "WHERE t.x = :x_1) AS anon_1 " "WHERE mssql_rn > :param_1 AND mssql_rn <= :param_2 + :param_1", checkparams={'param_1': 20, 'param_2': 10, 'x_1': 5} ) c = s.compile(dialect=mssql.dialect()) eq_(len(c._result_columns), 2) assert t.c.x in set(c._create_result_map()['x'][1]) assert t.c.y in set(c._create_result_map()['y'][1])
def test_offset_using_window(self): t = table('t', column('x', Integer), column('y', Integer)) s = select([t]).where(t.c.x == 5).order_by(t.c.y).offset(20) # test that the select is not altered with subsequent compile # calls for i in range(2): self.assert_compile( s, "SELECT anon_1.x, anon_1.y FROM (SELECT t.x AS x, t.y " "AS y, ROW_NUMBER() OVER (ORDER BY t.y) AS " "mssql_rn FROM t WHERE t.x = :x_1) AS " "anon_1 WHERE mssql_rn > :param_1", checkparams={'param_1': 20, 'x_1': 5} ) c = s.compile(dialect=mssql.dialect()) eq_(len(c._result_columns), 2) assert t.c.x in set(c._create_result_map()['x'][1])
def test_limit_offset_w_ambiguous_cols(self): t = table('t', column('x', Integer), column('y', Integer)) cols = [t.c.x, t.c.x.label('q'), t.c.x.label('p'), t.c.y] s = select(cols).where(t.c.x == 5).order_by(t.c.y).limit(10).offset(20) self.assert_compile( s, "SELECT anon_1.x, anon_1.q, anon_1.p, anon_1.y " "FROM (SELECT t.x AS x, t.x AS q, t.x AS p, t.y AS y, " "ROW_NUMBER() OVER (ORDER BY t.y) AS mssql_rn " "FROM t " "WHERE t.x = :x_1) AS anon_1 " "WHERE mssql_rn > :param_1 AND mssql_rn <= :param_2 + :param_1", checkparams={'param_1': 20, 'param_2': 10, 'x_1': 5} ) c = s.compile(dialect=mssql.dialect()) eq_(len(c._result_columns), 4) result_map = c._create_result_map() for col in cols: is_(result_map[col.key][1][0], col)
def test_match_3(self): self.assert_compile(self.table1.c.myid.match('somstr'), "CONTAINS (mytable.myid, :myid_1)", dialect=mssql.dialect())