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", )