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