def test_quote_flag_propagate_anon_label(self):
m = MetaData()
t = Table("t", m, Column("x", Integer, quote=True))
self.assert_compile(
select([t.alias()]).apply_labels(),
'SELECT t_1."x" AS "t_1_x" FROM t AS t_1',
)
t2 = Table("t2", m, Column("x", Integer), quote=True)
self.assert_compile(
select([t2.c.x]).apply_labels(),
'SELECT "t2".x AS "t2_x" FROM "t2"',
)
def test_update_from(self):
metadata = MetaData()
table1 = Table(
"mytable",
metadata,
Column("myid", Integer),
Column("name", String(30)),
Column("description", String(50)),
)
table2 = Table(
"myothertable",
metadata,
Column("otherid", Integer),
Column("othername", String(30)),
)
mt = table1.alias()
u = (table1.update().values(name="foo").where(
table2.c.otherid == table1.c.myid))
# testing mssql.base.MSSQLCompiler.update_from_clause
self.assert_compile(
u,
"UPDATE mytable SET name=:name "
"FROM mytable, myothertable WHERE "
"myothertable.otherid = mytable.myid",
)
self.assert_compile(
u.where(table2.c.othername == mt.c.name),
"UPDATE mytable SET name=:name "
"FROM mytable, myothertable, mytable AS mytable_1 "
"WHERE myothertable.otherid = mytable.myid "
"AND myothertable.othername = mytable_1.name",
)
class LegacySchemaAliasingTest(fixtures.TestBase, AssertsCompiledSQL):
"""Legacy behavior tried to prevent schema-qualified tables
from being rendered as dotted names, and were instead aliased.
This behavior no longer seems to be required.
"""
def setup(self):
metadata = MetaData()
self.t1 = table(
"t1",
column("a", Integer),
column("b", String),
column("c", String),
)
self.t2 = Table(
"t2",
metadata,
Column("a", Integer),
Column("b", Integer),
Column("c", Integer),
schema="schema",
)
def _assert_sql(self, element, legacy_sql, modern_sql=None):
dialect = mssql.dialect(legacy_schema_aliasing=True)
self.assert_compile(element, legacy_sql, dialect=dialect)
dialect = mssql.dialect()
self.assert_compile(element, modern_sql or "foob", dialect=dialect)
def _legacy_dialect(self):
return mssql.dialect(legacy_schema_aliasing=True)
def test_result_map(self):
s = self.t2.select()
c = s.compile(dialect=self._legacy_dialect())
assert self.t2.c.a in set(c._create_result_map()["a"][1])
def test_result_map_use_labels(self):
s = self.t2.select(use_labels=True)
c = s.compile(dialect=self._legacy_dialect())
assert self.t2.c.a in set(c._create_result_map()["schema_t2_a"][1])
def test_straight_select(self):
self._assert_sql(
self.t2.select(),
"SELECT t2_1.a, t2_1.b, t2_1.c FROM [schema].t2 AS t2_1",
"SELECT [schema].t2.a, [schema].t2.b, "
"[schema].t2.c FROM [schema].t2",
)
def test_straight_select_use_labels(self):
self._assert_sql(
self.t2.select(use_labels=True),
"SELECT t2_1.a AS schema_t2_a, t2_1.b AS schema_t2_b, "
"t2_1.c AS schema_t2_c FROM [schema].t2 AS t2_1",
"SELECT [schema].t2.a AS schema_t2_a, "
"[schema].t2.b AS schema_t2_b, "
"[schema].t2.c AS schema_t2_c FROM [schema].t2",
)
def test_join_to_schema(self):
t1, t2 = self.t1, self.t2
self._assert_sql(
t1.join(t2, t1.c.a == t2.c.a).select(),
"SELECT t1.a, t1.b, t1.c, t2_1.a, t2_1.b, t2_1.c FROM t1 "
"JOIN [schema].t2 AS t2_1 ON t2_1.a = t1.a",
"SELECT t1.a, t1.b, t1.c, [schema].t2.a, [schema].t2.b, "
"[schema].t2.c FROM t1 JOIN [schema].t2 ON [schema].t2.a = t1.a",
)
def test_union_schema_to_non(self):
t1, t2 = self.t1, self.t2
s = (
select([t2.c.a, t2.c.b])
.apply_labels()
.union(select([t1.c.a, t1.c.b]).apply_labels())
.alias()
.select()
)
self._assert_sql(
s,
"SELECT anon_1.schema_t2_a, anon_1.schema_t2_b FROM "
"(SELECT t2_1.a AS schema_t2_a, t2_1.b AS schema_t2_b "
"FROM [schema].t2 AS t2_1 UNION SELECT t1.a AS t1_a, "
"t1.b AS t1_b FROM t1) AS anon_1",
"SELECT anon_1.schema_t2_a, anon_1.schema_t2_b FROM "
"(SELECT [schema].t2.a AS schema_t2_a, [schema].t2.b AS "
"schema_t2_b FROM [schema].t2 UNION SELECT t1.a AS t1_a, "
"t1.b AS t1_b FROM t1) AS anon_1",
)
def test_column_subquery_to_alias(self):
a1 = self.t2.alias("a1")
s = select([self.t2, select([a1.c.a]).as_scalar()])
self._assert_sql(
s,
"SELECT t2_1.a, t2_1.b, t2_1.c, "
"(SELECT a1.a FROM [schema].t2 AS a1) "
"AS anon_1 FROM [schema].t2 AS t2_1",
"SELECT [schema].t2.a, [schema].t2.b, [schema].t2.c, "
"(SELECT a1.a FROM [schema].t2 AS a1) AS anon_1 FROM [schema].t2",
)
