def test_dialect_flag(self): d1 = default.DefaultDialect(supports_right_nested_joins=True) d2 = default.DefaultDialect(supports_right_nested_joins=False) j1 = b.join(c) j2 = a.join(j1) s = select([a, b, c], use_labels=True).select_from(j2) self.assert_compile( s, "SELECT a.id AS a_id, b.id AS b_id, b.a_id AS b_a_id, " "c.id AS c_id, " "c.b_id AS c_b_id FROM a JOIN (b JOIN c ON b.id = c.b_id) " "ON a.id = b.a_id", dialect=d1, ) self.assert_compile( s, "SELECT a.id AS a_id, anon_1.b_id AS b_id, " "anon_1.b_a_id AS b_a_id, " "anon_1.c_id AS c_id, anon_1.c_b_id AS c_b_id " "FROM a JOIN (SELECT b.id AS b_id, b.a_id AS b_a_id, " "c.id AS c_id, " "c.b_id AS c_b_id FROM b JOIN c ON b.id = c.b_id) AS anon_1 " "ON a.id = anon_1.b_a_id", dialect=d2, )
def setup_class(cls): global t1, t2, metadata metadata = MetaData() t1 = Table( "t1", metadata, Column("c1", Integer, primary_key=True), Column("c2", String(30)), ) t2 = Table( "t2", metadata, Column("c1", Integer, primary_key=True), Column("c2", String(30)), ) cls.dialect = default.DefaultDialect() # do a "compile" ahead of time to load # deferred imports, use the dialect to pre-load # dialect-level types t1.insert().compile(dialect=cls.dialect) # go through all the TypeEngine # objects in use and pre-load their _type_affinity # entries. for t in (t1, t2): for c in t.c: c.type._type_affinity from sqlalchemy_1_3.sql import sqltypes for t in list(sqltypes._type_map.values()): t._type_affinity
def test_positional_w_defaults(self): table1 = self.tables.table_w_defaults values = [{"id": 1}, {"id": 2}, {"id": 3}] checkpositional = (1, None, None, 2, None, None, 3, None, None) dialect = default.DefaultDialect() dialect.supports_multivalues_insert = True dialect.paramstyle = "format" dialect.positional = True self.assert_compile( table1.insert().values(values), "INSERT INTO table_w_defaults (id, x, z) VALUES " "(%s, %s, %s), (%s, %s, %s), (%s, %s, %s)", checkpositional=checkpositional, check_prefetch=[ table1.c.x, table1.c.z, crud._multiparam_column(table1.c.x, 0), crud._multiparam_column(table1.c.z, 0), crud._multiparam_column(table1.c.x, 1), crud._multiparam_column(table1.c.z, 1), ], dialect=dialect, )
def test_positional(self): table1 = self.tables.mytable values = [ { "myid": 1, "name": "a", "description": "b" }, { "myid": 2, "name": "c", "description": "d" }, { "myid": 3, "name": "e", "description": "f" }, ] checkpositional = (1, "a", "b", 2, "c", "d", 3, "e", "f") dialect = default.DefaultDialect() dialect.supports_multivalues_insert = True dialect.paramstyle = "format" dialect.positional = True self.assert_compile( table1.insert().values(values), "INSERT INTO mytable (myid, name, description) VALUES " "(%s, %s, %s), (%s, %s, %s), (%s, %s, %s)", checkpositional=checkpositional, dialect=dialect, )
def test_long_labels(self): dialect = default.DefaultDialect() dialect.max_identifier_length = 30 ora_dialect = oracle.dialect() m = MetaData() a_table = Table( "thirty_characters_table_xxxxxx", m, Column("id", Integer, primary_key=True), ) other_table = Table( "other_thirty_characters_table_", m, Column("id", Integer, primary_key=True), Column( "thirty_characters_table_id", Integer, ForeignKey("thirty_characters_table_xxxxxx.id"), primary_key=True, ), ) anon = a_table.alias() self.assert_compile( select([other_table, anon ]).select_from(other_table.outerjoin(anon)).apply_labels(), "SELECT other_thirty_characters_table_.id " "AS other_thirty_characters__1, " "other_thirty_characters_table_.thirty_char" "acters_table_id AS other_thirty_characters" "__2, thirty_characters_table__1.id AS " "thirty_characters_table__3 FROM " "other_thirty_characters_table_ LEFT OUTER " "JOIN thirty_characters_table_xxxxxx AS " "thirty_characters_table__1 ON " "thirty_characters_table__1.id = " "other_thirty_characters_table_.thirty_char" "acters_table_id", dialect=dialect, ) self.assert_compile( select([other_table, anon ]).select_from(other_table.outerjoin(anon)).apply_labels(), "SELECT other_thirty_characters_table_.id " "AS other_thirty_characters__1, " "other_thirty_characters_table_.thirty_char" "acters_table_id AS other_thirty_characters" "__2, thirty_characters_table__1.id AS " "thirty_characters_table__3 FROM " "other_thirty_characters_table_ LEFT OUTER " "JOIN thirty_characters_table_xxxxxx " "thirty_characters_table__1 ON " "thirty_characters_table__1.id = " "other_thirty_characters_table_.thirty_char" "acters_table_id", dialect=ora_dialect, )
def test_unformat_custom(self): class Custom(compiler.IdentifierPreparer): def __init__(self, dialect): super(Custom, self).__init__( dialect, initial_quote="`", final_quote="`" ) def _escape_identifier(self, value): return value.replace("`", "``") def _unescape_identifier(self, value): return value.replace("``", "`") prep = Custom(default.DefaultDialect()) unformat = prep.unformat_identifiers def a_eq(have, want): if have != want: print("Wanted %s" % want) print("Received %s" % have) self.assert_(have == want) a_eq(unformat("foo"), ["foo"]) a_eq(unformat("`foo`"), ["foo"]) a_eq(unformat(repr("foo")), ["'foo'"]) a_eq(unformat("foo.bar"), ["foo", "bar"]) a_eq(unformat("`foo`.`bar`"), ["foo", "bar"]) a_eq(unformat("foo.`bar`"), ["foo", "bar"]) a_eq(unformat("`foo`.bar"), ["foo", "bar"]) a_eq(unformat("`foo`.`b``a``r`.`baz`"), ["foo", "b`a`r", "baz"])
class NameNormalizeTest(fixtures.TestBase): dialect = default.DefaultDialect() @testing.combinations( ("NAME", "name", False), ("NA ME", "NA ME", False), ("NaMe", "NaMe", False), (u"姓名", u"姓名", False), ("name", "name", True), # an all-lower case name needs quote forced ) def test_name_normalize(self, original, normalized, is_quote): orig_norm = self.dialect.normalize_name(original) eq_(orig_norm, normalized) if is_quote: is_(orig_norm.quote, True) else: assert not isinstance(orig_norm, quoted_name) @testing.combinations( ("name", "NAME", False), ("NA ME", "NA ME", False), ("NaMe", "NaMe", False), (u"姓名", u"姓名", False), (quoted_name("name", quote=True), "name", True), ) def test_name_denormalize(self, original, denormalized, is_quote): orig_denorm = self.dialect.denormalize_name(original) eq_(orig_denorm, denormalized) if is_quote: is_(orig_denorm.quote, True) else: assert not isinstance(orig_denorm, quoted_name)
def test_adjustable_5(self): table1 = self.table1 q = table1.select(table1.c.this_is_the_primarykey_column == 4).alias() x = select([q], use_labels=True) compile_dialect = default.DefaultDialect(label_length=4) self.assert_compile( x, "SELECT " "_1._2 AS _1, " "_1._4 AS _3 " "FROM (" "SELECT " "some_large_named_table.this_is_the_primarykey_column " "AS _2, " "some_large_named_table.this_is_the_data_column " "AS _4 " "FROM " "some_large_named_table " "WHERE " "some_large_named_table.this_is_the_primarykey_column " "= :_1" ") " "AS _1", dialect=compile_dialect, )
def test_adjustable_result_schema_column_1(self): table1 = self.table1 q = ( table1.select(table1.c.this_is_the_primarykey_column == 4) .apply_labels() .alias("foo") ) dialect = default.DefaultDialect(label_length=10) compiled = q.compile(dialect=dialect) assert set(compiled._create_result_map()["some_2"][1]).issuperset( [ table1.c.this_is_the_data_column, "some_large_named_table_this_is_the_data_column", "some_2", ] ) assert set(compiled._create_result_map()["some_1"][1]).issuperset( [ table1.c.this_is_the_primarykey_column, "some_large_named_table_this_is_the_primarykey_column", "some_1", ] )
def test_adjustable_result_schema_column_2(self): table1 = self.table1 q = table1.select(table1.c.this_is_the_primarykey_column == 4).alias( "foo" ) x = select([q]) dialect = default.DefaultDialect(label_length=10) compiled = x.compile(dialect=dialect) assert set(compiled._create_result_map()["this_2"][1]).issuperset( [ q.corresponding_column(table1.c.this_is_the_data_column), "this_is_the_data_column", "this_2", ] ) assert set(compiled._create_result_map()["this_1"][1]).issuperset( [ q.corresponding_column(table1.c.this_is_the_primarykey_column), "this_is_the_primarykey_column", "this_1", ] )
def test_adjustable_3(self): table1 = self.table1 compile_dialect = default.DefaultDialect(label_length=4) q = table1.select(table1.c.this_is_the_primarykey_column == 4).alias( "foo" ) x = select([q]) self.assert_compile( x, "SELECT " "foo._1, foo._2 " "FROM (" "SELECT " "some_large_named_table.this_is_the_primarykey_column " "AS _1, " "some_large_named_table.this_is_the_data_column " "AS _2 " "FROM " "some_large_named_table " "WHERE " "some_large_named_table.this_is_the_primarykey_column " "= :_1" ") " "AS foo", dialect=compile_dialect, )
def test_not_supported_by_dialect_should_just_use_update(self): User = self.classes.User sess = Session() self.assert_compile( sess.query(User.id).with_for_update(read=True), "SELECT users.id AS users_id FROM users FOR UPDATE", dialect=default.DefaultDialect(), )
def test_default_update(self): User = self.classes.User sess = Session() self.assert_compile( sess.query(User.id).with_for_update(), "SELECT users.id AS users_id FROM users FOR UPDATE", dialect=default.DefaultDialect(), )
def _polymorphic_join_target(self, cls): from sqlalchemy_1_3.orm import class_mapper sel = class_mapper(Person)._with_polymorphic_selectable.element comp_sel = sel.compile(dialect=default.DefaultDialect()) return (comp_sel.process(sel, asfrom=True).replace("\n", "") + " AS anon_1 ON companies.company_id = anon_1.company_id")
def _length_fixture(self, length=IDENT_LENGTH, positional=False): dialect = default.DefaultDialect() dialect.max_identifier_length = ( dialect._user_defined_max_identifier_length ) = length if positional: dialect.paramstyle = "format" dialect.positional = True return dialect
def _polymorphic_join_target(self, cls): from sqlalchemy_1_3.orm import class_mapper from sqlalchemy_1_3.sql.expression import FromGrouping sel = FromGrouping( class_mapper(Person)._with_polymorphic_selectable.alias(flat=True)) comp_sel = sel.compile(dialect=default.DefaultDialect()) return (comp_sel.process(sel, asfrom=True).replace("\n", "") + " ON companies.company_id = people_1.company_id")
def test_insert_returning_not_in_default(self): table1 = self.tables.mytable stmt = table1.insert().returning(table1.c.myid) assert_raises_message( exc.CompileError, "RETURNING is not supported by this dialect's statement compiler.", stmt.compile, dialect=default.DefaultDialect(), )
def test_supports_empty_insert_true(self): table1 = self.tables.mytable dialect = default.DefaultDialect() dialect.supports_empty_insert = dialect.supports_default_values = True stmt = table1.insert().values({}) # hide from 2to3 self.assert_compile(stmt, "INSERT INTO mytable DEFAULT VALUES", dialect=dialect)
def test_not_supported(self): table1 = self.tables.mytable dialect = default.DefaultDialect() stmt = table1.insert().values([{"myid": 1}, {"myid": 2}]) assert_raises_message( exc.CompileError, "The 'default' dialect with current database version settings " "does not support in-place multirow inserts.", stmt.compile, dialect=dialect, )
def _dialect_level_fixture(self): class ImplString(String): def bind_expression(self, bindvalue): return func.dialect_bind(bindvalue) def column_expression(self, col): return func.dialect_colexpr(col) from sqlalchemy_1_3.engine import default dialect = default.DefaultDialect() dialect.colspecs = {String: ImplString} return dialect
def test_insert_literal_binds_sequence_notimplemented(self): table = Table("x", MetaData(), Column("y", Integer, Sequence("y_seq"))) dialect = default.DefaultDialect() dialect.supports_sequences = True stmt = table.insert().values(myid=3, name="jack") assert_raises( NotImplementedError, stmt.compile, compile_kwargs=dict(literal_binds=True), dialect=dialect, )
def test_supports_empty_insert_false(self): table1 = self.tables.mytable dialect = default.DefaultDialect() dialect.supports_empty_insert = dialect.supports_default_values = False stmt = table1.insert().values({}) # hide from 2to3 assert_raises_message( exc.CompileError, "The 'default' dialect with current database version " "settings does not support empty inserts.", stmt.compile, dialect=dialect, )
def _test_deferrable(self, constraint_factory): dialect = default.DefaultDialect() t = Table( "tbl", MetaData(), Column("a", Integer), Column("b", Integer), constraint_factory(deferrable=True), ) sql = str(schema.CreateTable(t).compile(dialect=dialect)) assert "DEFERRABLE" in sql, sql assert "NOT DEFERRABLE" not in sql, sql t = Table( "tbl", MetaData(), Column("a", Integer), Column("b", Integer), constraint_factory(deferrable=False), ) sql = str(schema.CreateTable(t).compile(dialect=dialect)) assert "NOT DEFERRABLE" in sql t = Table( "tbl", MetaData(), Column("a", Integer), Column("b", Integer), constraint_factory(deferrable=True, initially="IMMEDIATE"), ) sql = str(schema.CreateTable(t).compile(dialect=dialect)) assert "NOT DEFERRABLE" not in sql assert "INITIALLY IMMEDIATE" in sql t = Table( "tbl", MetaData(), Column("a", Integer), Column("b", Integer), constraint_factory(deferrable=True, initially="DEFERRED"), ) sql = str(schema.CreateTable(t).compile(dialect=dialect)) assert "NOT DEFERRABLE" not in sql assert "INITIALLY DEFERRED" in sql
def test_insert_from_select_cte_follows_insert_two(self): dialect = default.DefaultDialect() dialect.cte_follows_insert = True table1 = self.tables.mytable cte = table1.select().cte("c") stmt = cte.select() ins = table1.insert().from_select(table1.c, stmt) self.assert_compile( ins, "INSERT INTO mytable (myid, name, description) " "WITH c AS (SELECT mytable.myid AS myid, mytable.name AS name, " "mytable.description AS description FROM mytable) " "SELECT c.myid, c.name, c.description FROM c", dialect=dialect, )
def test_unicode_warnings_dialectlevel(self): unicodedata = self.data with testing.expect_deprecated( "The create_engine.convert_unicode parameter and " "corresponding dialect-level"): dialect = default.DefaultDialect(convert_unicode=True) dialect.supports_unicode_binds = False s = String() uni = s.dialect_impl(dialect).bind_processor(dialect) uni(util.b("x")) assert isinstance(uni(unicodedata), util.binary_type) eq_(uni(unicodedata), unicodedata.encode("utf-8"))
def test_named(self): table1 = self.tables.mytable values = [ { "myid": 1, "name": "a", "description": "b" }, { "myid": 2, "name": "c", "description": "d" }, { "myid": 3, "name": "e", "description": "f" }, ] checkparams = { "myid_m0": 1, "myid_m1": 2, "myid_m2": 3, "name_m0": "a", "name_m1": "c", "name_m2": "e", "description_m0": "b", "description_m1": "d", "description_m2": "f", } dialect = default.DefaultDialect() dialect.supports_multivalues_insert = True self.assert_compile( table1.insert().values(values), "INSERT INTO mytable (myid, name, description) VALUES " "(:myid_m0, :name_m0, :description_m0), " "(:myid_m1, :name_m1, :description_m1), " "(:myid_m2, :name_m2, :description_m2)", checkparams=checkparams, dialect=dialect, )
def test_unformat(self): prep = compiler.IdentifierPreparer(default.DefaultDialect()) unformat = prep.unformat_identifiers def a_eq(have, want): if have != want: print("Wanted %s" % want) print("Received %s" % have) self.assert_(have == want) a_eq(unformat("foo"), ["foo"]) a_eq(unformat('"foo"'), ["foo"]) a_eq(unformat("'foo'"), ["'foo'"]) a_eq(unformat("foo.bar"), ["foo", "bar"]) a_eq(unformat('"foo"."bar"'), ["foo", "bar"]) a_eq(unformat('foo."bar"'), ["foo", "bar"]) a_eq(unformat('"foo".bar'), ["foo", "bar"]) a_eq(unformat('"foo"."b""a""r"."baz"'), ["foo", 'b"a"r', "baz"])
def test_ignoring_unicode_error(self): """checks String(unicode_error='ignore') is passed to underlying codec.""" unicodedata = self.data with testing.expect_deprecated( "The String.convert_unicode parameter is deprecated and " "will be removed in a future release.", "The String.unicode_errors parameter is deprecated and " "will be removed in a future release.", ): type_ = String(248, convert_unicode="force", unicode_error="ignore") dialect = default.DefaultDialect(encoding="ascii") proc = type_.result_processor(dialect, 10) utfdata = unicodedata.encode("utf8") eq_(proc(utfdata), unicodedata.encode("ascii", "ignore").decode())
def test_label_overlap_unlabeled(self): """test that an anon col can't overlap with a fixed name, #3396""" table1 = table( "tablename", column("columnname_one"), column("columnn_1") ) stmt = select([table1]).apply_labels() dialect = default.DefaultDialect(label_length=23) self.assert_compile( stmt, "SELECT tablename.columnname_one AS tablename_columnn_1, " "tablename.columnn_1 AS tablename_columnn_2 FROM tablename", dialect=dialect, ) compiled = stmt.compile(dialect=dialect) eq_( set(compiled._create_result_map()), set(["tablename_columnn_1", "tablename_columnn_2"]), )
def test_positional_binds_2_asliteral(self): orders = table("orders", column("order")) s = select([orders.c.order, literal("x")]).cte("regional_sales") s = select([s.c.order, literal("y")]) dialect = default.DefaultDialect() dialect.positional = True dialect.paramstyle = "numeric" s1 = (select([orders.c.order ]).where(orders.c.order == "x").cte("regional_sales_1")) s1a = s1.alias() s2 = (select([ orders.c.order == "y", s1a.c.order, orders.c.order, s1.c.order, ]).where(orders.c.order == "z").cte("regional_sales_2")) s3 = select([s2]) self.assert_compile( s3, "WITH regional_sales_1 AS " '(SELECT orders."order" AS "order" ' "FROM orders " "WHERE orders.\"order\" = 'x'), " "regional_sales_2 AS " "(SELECT orders.\"order\" = 'y' AS anon_1, " 'anon_2."order" AS "order", orders."order" AS "order", ' 'regional_sales_1."order" AS "order" ' "FROM orders, regional_sales_1 AS anon_2, regional_sales_1 " "WHERE orders.\"order\" = 'z') " 'SELECT regional_sales_2.anon_1, regional_sales_2."order" ' "FROM regional_sales_2", checkpositional=(), dialect=dialect, literal_binds=True, )