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_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_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_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_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_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())