class MaxIdentTest(fixtures.TestBase, AssertsCompiledSQL):
__dialect__ = "DefaultDialect"
__backend__ = True
table1 = table(
"some_large_named_table",
column("this_is_the_primarykey_column"),
column("this_is_the_data_column"),
)
table2 = table(
"table_with_exactly_29_characs",
column("this_is_the_primarykey_column"),
column("this_is_the_data_column"),
)
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 _engine_fixture(self, length=IDENT_LENGTH):
eng = engines.testing_engine()
eng.dialect.max_identifier_length = (
eng.dialect._user_defined_max_identifier_length
) = length
return eng
def test_label_length_raise_too_large(self):
max_ident_length = testing.db.dialect.max_identifier_length
eng = engines.testing_engine(
options={"label_length": max_ident_length + 10}
)
assert_raises_message(
exceptions.ArgumentError,
"Label length of %d is greater than this dialect's maximum "
"identifier length of %d"
% (max_ident_length + 10, max_ident_length),
eng.connect,
)
def test_label_length_custom_maxlen(self):
max_ident_length = testing.db.dialect.max_identifier_length
eng = engines.testing_engine(
options={
"label_length": max_ident_length + 10,
"max_identifier_length": max_ident_length + 20,
}
)
with eng.connect() as conn:
eq_(conn.dialect.max_identifier_length, max_ident_length + 20)
def test_label_length_custom_maxlen_dialect_only(self):
dialect = default.DefaultDialect(max_identifier_length=47)
eq_(dialect.max_identifier_length, 47)
def test_label_length_custom_maxlen_user_set_manually(self):
eng = engines.testing_engine()
eng.dialect.max_identifier_length = 47
# assume the dialect has no on-connect change
with mock.patch.object(
eng.dialect,
"_check_max_identifier_length",
side_effect=lambda conn: None,
):
with eng.connect():
pass
# it was maintained
eq_(eng.dialect.max_identifier_length, 47)
def test_label_length_too_large_custom_maxlen(self):
max_ident_length = testing.db.dialect.max_identifier_length
eng = engines.testing_engine(
options={
"label_length": max_ident_length - 10,
"max_identifier_length": max_ident_length - 20,
}
)
assert_raises_message(
exceptions.ArgumentError,
"Label length of %d is greater than this dialect's maximum "
"identifier length of %d"
% (max_ident_length - 10, max_ident_length - 20),
eng.connect,
)
def test_custom_max_identifier_length(self):
max_ident_length = testing.db.dialect.max_identifier_length
eng = engines.testing_engine(
options={"max_identifier_length": max_ident_length + 20}
)
with eng.connect() as conn:
eq_(conn.dialect.max_identifier_length, max_ident_length + 20)
def test_max_identifier_length_onconnect(self):
eng = engines.testing_engine()
def _check_max_identifer_length(conn):
return 47
with mock.patch.object(
eng.dialect,
"_check_max_identifier_length",
side_effect=_check_max_identifer_length,
) as mock_:
with eng.connect():
eq_(eng.dialect.max_identifier_length, 47)
eq_(mock_.mock_calls, [mock.call(mock.ANY)])
def test_max_identifier_length_onconnect_returns_none(self):
eng = engines.testing_engine()
max_ident_length = eng.dialect.max_identifier_length
def _check_max_identifer_length(conn):
return None
with mock.patch.object(
eng.dialect,
"_check_max_identifier_length",
side_effect=_check_max_identifer_length,
) as mock_:
with eng.connect():
eq_(eng.dialect.max_identifier_length, max_ident_length)
eq_(mock_.mock_calls, [mock.call(mock.ANY)])
def test_custom_max_identifier_length_onconnect(self):
eng = engines.testing_engine(options={"max_identifier_length": 49})
def _check_max_identifer_length(conn):
return 47
with mock.patch.object(
eng.dialect,
"_check_max_identifier_length",
side_effect=_check_max_identifer_length,
) as mock_:
with eng.connect():
eq_(eng.dialect.max_identifier_length, 49)
eq_(mock_.mock_calls, []) # was not called
def test_table_alias_1(self):
self.assert_compile(
self.table2.alias().select(),
"SELECT "
"table_with_exactly_29_c_1."
"this_is_the_primarykey_column, "
"table_with_exactly_29_c_1.this_is_the_data_column "
"FROM "
"table_with_exactly_29_characs "
"AS table_with_exactly_29_c_1",
dialect=self._length_fixture(),
)
def test_table_alias_2(self):
table1 = self.table1
table2 = self.table2
ta = table2.alias()
on = table1.c.this_is_the_data_column == ta.c.this_is_the_data_column
self.assert_compile(
select([table1, ta])
.select_from(table1.join(ta, on))
.where(ta.c.this_is_the_data_column == "data3"),
"SELECT "
"some_large_named_table.this_is_the_primarykey_column, "
"some_large_named_table.this_is_the_data_column, "
"table_with_exactly_29_c_1.this_is_the_primarykey_column, "
"table_with_exactly_29_c_1.this_is_the_data_column "
"FROM "
"some_large_named_table "
"JOIN "
"table_with_exactly_29_characs "
"AS "
"table_with_exactly_29_c_1 "
"ON "
"some_large_named_table.this_is_the_data_column = "
"table_with_exactly_29_c_1.this_is_the_data_column "
"WHERE "
"table_with_exactly_29_c_1.this_is_the_data_column = "
":this_is_the_data_column_1",
dialect=self._length_fixture(),
)
def test_too_long_name_disallowed(self):
m = MetaData()
t = Table(
"this_name_is_too_long_for_what_were_doing_in_this_test",
m,
Column("foo", Integer),
)
eng = self._engine_fixture()
methods = (t.create, t.drop, m.create_all, m.drop_all)
for meth in methods:
assert_raises(exceptions.IdentifierError, meth, eng)
def _assert_labeled_table1_select(self, s):
table1 = self.table1
compiled = s.compile(dialect=self._length_fixture())
assert set(
compiled._create_result_map()["some_large_named_table__2"][1]
).issuperset(
[
"some_large_named_table_this_is_the_data_column",
"some_large_named_table__2",
table1.c.this_is_the_data_column,
]
)
assert set(
compiled._create_result_map()["some_large_named_table__1"][1]
).issuperset(
[
"some_large_named_table_this_is_the_primarykey_column",
"some_large_named_table__1",
table1.c.this_is_the_primarykey_column,
]
)
def test_result_map_use_labels(self):
table1 = self.table1
s = (
table1.select()
.apply_labels()
.order_by(table1.c.this_is_the_primarykey_column)
)
self._assert_labeled_table1_select(s)
def test_result_map_limit(self):
table1 = self.table1
# some dialects such as oracle (and possibly ms-sql in a future
# version) generate a subquery for limits/offsets. ensure that the
# generated result map corresponds to the selected table, not the
# select query
s = table1.select(
use_labels=True, order_by=[table1.c.this_is_the_primarykey_column]
).limit(2)
self._assert_labeled_table1_select(s)
def test_result_map_subquery(self):
table1 = self.table1
s = table1.select(table1.c.this_is_the_primarykey_column == 4).alias(
"foo"
)
s2 = select([s])
compiled = s2.compile(dialect=self._length_fixture())
assert set(
compiled._create_result_map()["this_is_the_data_column"][1]
).issuperset(["this_is_the_data_column", s.c.this_is_the_data_column])
assert set(
compiled._create_result_map()["this_is_the_primarykey__1"][1]
).issuperset(
[
"this_is_the_primarykey_column",
"this_is_the_primarykey__1",
s.c.this_is_the_primarykey_column,
]
)
def test_result_map_anon_alias(self):
table1 = self.table1
dialect = self._length_fixture()
q = table1.select(table1.c.this_is_the_primarykey_column == 4).alias()
s = select([q]).apply_labels()
self.assert_compile(
s,
"SELECT "
"anon_1.this_is_the_primarykey__2 AS anon_1_this_is_the_prim_1, "
"anon_1.this_is_the_data_column AS anon_1_this_is_the_data_3 "
"FROM ("
"SELECT "
"some_large_named_table."
"this_is_the_primarykey_column AS this_is_the_primarykey__2, "
"some_large_named_table."
"this_is_the_data_column AS this_is_the_data_column "
"FROM "
"some_large_named_table "
"WHERE "
"some_large_named_table.this_is_the_primarykey_column "
"= :this_is_the_primarykey__1"
") "
"AS anon_1",
dialect=dialect,
)
compiled = s.compile(dialect=dialect)
assert set(
compiled._create_result_map()["anon_1_this_is_the_data_3"][1]
).issuperset(
[
"anon_1_this_is_the_data_3",
q.corresponding_column(table1.c.this_is_the_data_column),
]
)
assert set(
compiled._create_result_map()["anon_1_this_is_the_prim_1"][1]
).issuperset(
[
"anon_1_this_is_the_prim_1",
q.corresponding_column(table1.c.this_is_the_primarykey_column),
]
)
def test_column_bind_labels_1(self):
table1 = self.table1
s = table1.select(table1.c.this_is_the_primarykey_column == 4)
self.assert_compile(
s,
"SELECT some_large_named_table.this_is_the_primarykey_column, "
"some_large_named_table.this_is_the_data_column "
"FROM some_large_named_table WHERE "
"some_large_named_table.this_is_the_primarykey_column = "
":this_is_the_primarykey__1",
checkparams={"this_is_the_primarykey__1": 4},
dialect=self._length_fixture(),
)
self.assert_compile(
s,
"SELECT some_large_named_table.this_is_the_primarykey_column, "
"some_large_named_table.this_is_the_data_column "
"FROM some_large_named_table WHERE "
"some_large_named_table.this_is_the_primarykey_column = "
"%s",
checkpositional=(4,),
checkparams={"this_is_the_primarykey__1": 4},
dialect=self._length_fixture(positional=True),
)
def test_column_bind_labels_2(self):
table1 = self.table1
s = table1.select(
or_(
table1.c.this_is_the_primarykey_column == 4,
table1.c.this_is_the_primarykey_column == 2,
)
)
self.assert_compile(
s,
"SELECT some_large_named_table.this_is_the_primarykey_column, "
"some_large_named_table.this_is_the_data_column "
"FROM some_large_named_table WHERE "
"some_large_named_table.this_is_the_primarykey_column = "
":this_is_the_primarykey__1 OR "
"some_large_named_table.this_is_the_primarykey_column = "
":this_is_the_primarykey__2",
checkparams={
"this_is_the_primarykey__1": 4,
"this_is_the_primarykey__2": 2,
},
dialect=self._length_fixture(),
)
self.assert_compile(
s,
"SELECT some_large_named_table.this_is_the_primarykey_column, "
"some_large_named_table.this_is_the_data_column "
"FROM some_large_named_table WHERE "
"some_large_named_table.this_is_the_primarykey_column = "
"%s OR "
"some_large_named_table.this_is_the_primarykey_column = "
"%s",
checkparams={
"this_is_the_primarykey__1": 4,
"this_is_the_primarykey__2": 2,
},
checkpositional=(4, 2),
dialect=self._length_fixture(positional=True),
)
def test_bind_param_non_truncated(self):
table1 = self.table1
stmt = table1.insert().values(
this_is_the_data_column=bindparam(
"this_is_the_long_bindparam_name"
)
)
compiled = stmt.compile(dialect=self._length_fixture(length=10))
eq_(
compiled.construct_params(
params={"this_is_the_long_bindparam_name": 5}
),
{"this_is_the_long_bindparam_name": 5},
)
def test_bind_param_truncated_named(self):
table1 = self.table1
bp = bindparam(_truncated_label("this_is_the_long_bindparam_name"))
stmt = table1.insert().values(this_is_the_data_column=bp)
compiled = stmt.compile(dialect=self._length_fixture(length=10))
eq_(
compiled.construct_params(
params={"this_is_the_long_bindparam_name": 5}
),
{"this_1": 5},
)
def test_bind_param_truncated_positional(self):
table1 = self.table1
bp = bindparam(_truncated_label("this_is_the_long_bindparam_name"))
stmt = table1.insert().values(this_is_the_data_column=bp)
compiled = stmt.compile(
dialect=self._length_fixture(length=10, positional=True)
)
eq_(
compiled.construct_params(
params={"this_is_the_long_bindparam_name": 5}
),
{"this_1": 5},
)
class LabelLengthTest(fixtures.TestBase, AssertsCompiledSQL):
__dialect__ = "DefaultDialect"
table1 = table(
"some_large_named_table",
column("this_is_the_primarykey_column"),
column("this_is_the_data_column"),
)
table2 = table(
"table_with_exactly_29_characs",
column("this_is_the_primarykey_column"),
column("this_is_the_data_column"),
)
def test_adjustable_1(self):
table1 = self.table1
q = table1.select(table1.c.this_is_the_primarykey_column == 4).alias(
"foo"
)
x = select([q])
compile_dialect = default.DefaultDialect(label_length=10)
self.assert_compile(
x,
"SELECT "
"foo.this_1, foo.this_2 "
"FROM ("
"SELECT "
"some_large_named_table.this_is_the_primarykey_column "
"AS this_1, "
"some_large_named_table.this_is_the_data_column "
"AS this_2 "
"FROM "
"some_large_named_table "
"WHERE "
"some_large_named_table.this_is_the_primarykey_column "
"= :this_1"
") "
"AS foo",
dialect=compile_dialect,
)
def test_adjustable_2(self):
table1 = self.table1
q = table1.select(table1.c.this_is_the_primarykey_column == 4).alias(
"foo"
)
x = select([q])
compile_dialect = default.DefaultDialect(label_length=10)
self.assert_compile(
x,
"SELECT "
"foo.this_1, foo.this_2 "
"FROM ("
"SELECT "
"some_large_named_table.this_is_the_primarykey_column "
"AS this_1, "
"some_large_named_table.this_is_the_data_column "
"AS this_2 "
"FROM "
"some_large_named_table "
"WHERE "
"some_large_named_table.this_is_the_primarykey_column "
"= :this_1"
") "
"AS foo",
dialect=compile_dialect,
)
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_adjustable_4(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=10)
self.assert_compile(
x,
"SELECT "
"anon_1.this_2 AS anon_1, "
"anon_1.this_4 AS anon_3 "
"FROM ("
"SELECT "
"some_large_named_table.this_is_the_primarykey_column "
"AS this_2, "
"some_large_named_table.this_is_the_data_column "
"AS this_4 "
"FROM "
"some_large_named_table "
"WHERE "
"some_large_named_table.this_is_the_primarykey_column "
"= :this_1"
") "
"AS anon_1",
dialect=compile_dialect,
)
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_table_plus_column_exceeds_length(self):
"""test that the truncation only occurs when tablename + colname are
concatenated, if they are individually under the label length.
"""
compile_dialect = default.DefaultDialect(label_length=30)
a_table = table("thirty_characters_table_xxxxxx", column("id"))
other_table = table(
"other_thirty_characters_table_",
column("id"),
column("thirty_characters_table_id"),
)
anon = a_table.alias()
j1 = other_table.outerjoin(
anon, anon.c.id == other_table.c.thirty_characters_table_id
)
self.assert_compile(
select([other_table, anon]).select_from(j1).apply_labels(),
"SELECT "
"other_thirty_characters_table_.id "
"AS other_thirty_characters__1, "
"other_thirty_characters_table_.thirty_characters_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_characters_table_id",
dialect=compile_dialect,
)
def test_colnames_longer_than_labels_lowercase(self):
t1 = table("a", column("abcde"))
self._test_colnames_longer_than_labels(t1)
def test_colnames_longer_than_labels_uppercase(self):
m = MetaData()
t1 = Table("a", m, Column("abcde", Integer))
self._test_colnames_longer_than_labels(t1)
def _test_colnames_longer_than_labels(self, t1):
dialect = default.DefaultDialect(label_length=4)
a1 = t1.alias(name="asdf")
# 'abcde' is longer than 4, but rendered as itself
# needs to have all characters
s = select([a1])
self.assert_compile(
select([a1]), "SELECT asdf.abcde FROM a AS asdf", dialect=dialect
)
compiled = s.compile(dialect=dialect)
assert set(compiled._create_result_map()["abcde"][1]).issuperset(
["abcde", a1.c.abcde, "abcde"]
)
# column still there, but short label
s = select([a1]).apply_labels()
self.assert_compile(
s, "SELECT asdf.abcde AS _1 FROM a AS asdf", dialect=dialect
)
compiled = s.compile(dialect=dialect)
assert set(compiled._create_result_map()["_1"][1]).issuperset(
["asdf_abcde", a1.c.abcde, "_1"]
)
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"]),
)
class MySQLForUpdateCompileTest(fixtures.TestBase, AssertsCompiledSQL):
__dialect__ = mysql.dialect()
table1 = table(
"mytable", column("myid"), column("name"), column("description")
)
table2 = table("table2", column("mytable_id"))
join = table2.join(table1, table2.c.mytable_id == table1.c.myid)
for_update_of_dialect = mysql.dialect()
for_update_of_dialect.server_version_info = (8, 0, 0)
for_update_of_dialect.supports_for_update_of = True
def test_for_update_basic(self):
self.assert_compile(
self.table1.select(self.table1.c.myid == 7).with_for_update(),
"SELECT mytable.myid, mytable.name, mytable.description "
"FROM mytable WHERE mytable.myid = %s FOR UPDATE",
)
def test_for_update_read(self):
self.assert_compile(
self.table1.select(self.table1.c.myid == 7).with_for_update(
read=True
),
"SELECT mytable.myid, mytable.name, mytable.description "
"FROM mytable WHERE mytable.myid = %s LOCK IN SHARE MODE",
)
def test_for_update_skip_locked(self):
self.assert_compile(
self.table1.select(self.table1.c.myid == 7).with_for_update(
skip_locked=True
),
"SELECT mytable.myid, mytable.name, mytable.description "
"FROM mytable WHERE mytable.myid = %s "
"FOR UPDATE SKIP LOCKED",
)
def test_for_update_read_and_skip_locked(self):
self.assert_compile(
self.table1.select(self.table1.c.myid == 7).with_for_update(
read=True, skip_locked=True
),
"SELECT mytable.myid, mytable.name, mytable.description "
"FROM mytable WHERE mytable.myid = %s "
"LOCK IN SHARE MODE SKIP LOCKED",
)
def test_for_update_nowait(self):
self.assert_compile(
self.table1.select(self.table1.c.myid == 7).with_for_update(
nowait=True
),
"SELECT mytable.myid, mytable.name, mytable.description "
"FROM mytable WHERE mytable.myid = %s "
"FOR UPDATE NOWAIT",
)
def test_for_update_read_and_nowait(self):
self.assert_compile(
self.table1.select(self.table1.c.myid == 7).with_for_update(
read=True, nowait=True
),
"SELECT mytable.myid, mytable.name, mytable.description "
"FROM mytable WHERE mytable.myid = %s "
"LOCK IN SHARE MODE NOWAIT",
)
def test_for_update_of_nowait(self):
self.assert_compile(
self.table1.select(self.table1.c.myid == 7).with_for_update(
of=self.table1, nowait=True
),
"SELECT mytable.myid, mytable.name, mytable.description "
"FROM mytable WHERE mytable.myid = %s "
"FOR UPDATE OF mytable NOWAIT",
dialect=self.for_update_of_dialect,
)
def test_for_update_of_basic(self):
self.assert_compile(
self.table1.select(self.table1.c.myid == 7).with_for_update(
of=self.table1
),
"SELECT mytable.myid, mytable.name, mytable.description "
"FROM mytable WHERE mytable.myid = %s "
"FOR UPDATE OF mytable",
dialect=self.for_update_of_dialect,
)
def test_for_update_of_skip_locked(self):
self.assert_compile(
self.table1.select(self.table1.c.myid == 7).with_for_update(
of=self.table1, skip_locked=True
),
"SELECT mytable.myid, mytable.name, mytable.description "
"FROM mytable WHERE mytable.myid = %s "
"FOR UPDATE OF mytable SKIP LOCKED",
dialect=self.for_update_of_dialect,
)
def test_for_update_of_join_one(self):
self.assert_compile(
self.join.select(self.table2.c.mytable_id == 7).with_for_update(
of=[self.join]
),
"SELECT table2.mytable_id, "
"mytable.myid, mytable.name, mytable.description "
"FROM table2 "
"INNER JOIN mytable ON table2.mytable_id = mytable.myid "
"WHERE table2.mytable_id = %s "
"FOR UPDATE OF mytable, table2",
dialect=self.for_update_of_dialect,
)
def test_for_update_of_column_list_aliased(self):
ta = self.table1.alias()
self.assert_compile(
ta.select(ta.c.myid == 7).with_for_update(
of=[ta.c.myid, ta.c.name]
),
"SELECT mytable_1.myid, mytable_1.name, mytable_1.description "
"FROM mytable AS mytable_1 "
"WHERE mytable_1.myid = %s FOR UPDATE OF mytable_1",
dialect=self.for_update_of_dialect,
)
def test_for_update_of_join_aliased(self):
ta = self.table1.alias()
alias_join = self.table2.join(
ta, self.table2.c.mytable_id == ta.c.myid
)
self.assert_compile(
alias_join.select(self.table2.c.mytable_id == 7).with_for_update(
of=[alias_join]
),
"SELECT table2.mytable_id, "
"mytable_1.myid, mytable_1.name, mytable_1.description "
"FROM table2 "
"INNER JOIN mytable AS mytable_1 "
"ON table2.mytable_id = mytable_1.myid "
"WHERE table2.mytable_id = %s "
"FOR UPDATE OF mytable_1, table2",
dialect=self.for_update_of_dialect,
)
def test_for_update_of_read_nowait(self):
self.assert_compile(
self.table1.select(self.table1.c.myid == 7).with_for_update(
read=True, of=self.table1, nowait=True
),
"SELECT mytable.myid, mytable.name, mytable.description "
"FROM mytable WHERE mytable.myid = %s "
"LOCK IN SHARE MODE OF mytable NOWAIT",
dialect=self.for_update_of_dialect,
)
def test_for_update_of_read_skip_locked(self):
self.assert_compile(
self.table1.select(self.table1.c.myid == 7).with_for_update(
read=True, of=self.table1, skip_locked=True
),
"SELECT mytable.myid, mytable.name, mytable.description "
"FROM mytable WHERE mytable.myid = %s "
"LOCK IN SHARE MODE OF mytable SKIP LOCKED",
dialect=self.for_update_of_dialect,
)
def test_for_update_of_read_nowait_column_list(self):
self.assert_compile(
self.table1.select(self.table1.c.myid == 7).with_for_update(
read=True,
of=[self.table1.c.myid, self.table1.c.name],
nowait=True,
),
"SELECT mytable.myid, mytable.name, mytable.description "
"FROM mytable WHERE mytable.myid = %s "
"LOCK IN SHARE MODE OF mytable NOWAIT",
dialect=self.for_update_of_dialect,
)
def test_for_update_of_read(self):
self.assert_compile(
self.table1.select(self.table1.c.myid == 7).with_for_update(
read=True, of=self.table1
),
"SELECT mytable.myid, mytable.name, mytable.description "
"FROM mytable WHERE mytable.myid = %s "
"LOCK IN SHARE MODE OF mytable",
dialect=self.for_update_of_dialect,
)
from sqlalchemy_1_3 import text
from sqlalchemy_1_3 import union
from sqlalchemy_1_3 import util
from sqlalchemy_1_3.sql import column
from sqlalchemy_1_3.sql import quoted_name
from sqlalchemy_1_3.sql import table
from sqlalchemy_1_3.sql import util as sql_util
from sqlalchemy_1_3.testing import assert_raises_message
from sqlalchemy_1_3.testing import AssertsCompiledSQL
from sqlalchemy_1_3.testing import eq_
from sqlalchemy_1_3.testing import fixtures
from sqlalchemy_1_3.types import NullType
table1 = table(
"mytable",
column("myid", Integer),
column("name", String),
column("description", String),
)
table2 = table("myothertable", column("otherid", Integer),
column("othername", String))
class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
__dialect__ = "default"
def test_basic(self):
self.assert_compile(
text("select * from foo where lala = bar"),
"select * from foo where lala = bar",
)
def test_for_update(self):
table1 = table("mytable", column("myid"), column("name"),
column("description"))
self.assert_compile(
table1.select(table1.c.myid == 7).with_for_update(),
"SELECT mytable.myid, mytable.name, mytable.description "
"FROM mytable WHERE mytable.myid = :myid_1 FOR UPDATE",
)
self.assert_compile(
table1.select(table1.c.myid == 7).with_for_update(
of=table1.c.myid),
"SELECT mytable.myid, mytable.name, mytable.description "
"FROM mytable WHERE mytable.myid = :myid_1 "
"FOR UPDATE OF mytable.myid",
)
self.assert_compile(
table1.select(table1.c.myid == 7).with_for_update(nowait=True),
"SELECT mytable.myid, mytable.name, mytable.description "
"FROM mytable WHERE mytable.myid = :myid_1 FOR UPDATE NOWAIT",
)
self.assert_compile(
table1.select(table1.c.myid == 7).with_for_update(
nowait=True, of=table1.c.myid),
"SELECT mytable.myid, mytable.name, mytable.description "
"FROM mytable WHERE mytable.myid = :myid_1 "
"FOR UPDATE OF mytable.myid NOWAIT",
)
self.assert_compile(
table1.select(table1.c.myid == 7).with_for_update(
nowait=True, of=[table1.c.myid, table1.c.name]),
"SELECT mytable.myid, mytable.name, mytable.description "
"FROM mytable WHERE mytable.myid = :myid_1 FOR UPDATE OF "
"mytable.myid, mytable.name NOWAIT",
)
self.assert_compile(
table1.select(table1.c.myid == 7).with_for_update(
skip_locked=True, of=[table1.c.myid, table1.c.name]),
"SELECT mytable.myid, mytable.name, mytable.description "
"FROM mytable WHERE mytable.myid = :myid_1 FOR UPDATE OF "
"mytable.myid, mytable.name SKIP LOCKED",
)
# key_share has no effect
self.assert_compile(
table1.select(table1.c.myid == 7).with_for_update(key_share=True),
"SELECT mytable.myid, mytable.name, mytable.description "
"FROM mytable WHERE mytable.myid = :myid_1 FOR UPDATE",
)
# read has no effect
self.assert_compile(
table1.select(table1.c.myid == 7).with_for_update(read=True,
key_share=True),
"SELECT mytable.myid, mytable.name, mytable.description "
"FROM mytable WHERE mytable.myid = :myid_1 FOR UPDATE",
)
ta = table1.alias()
self.assert_compile(
ta.select(ta.c.myid == 7).with_for_update(
of=[ta.c.myid, ta.c.name]),
"SELECT mytable_1.myid, mytable_1.name, mytable_1.description "
"FROM mytable mytable_1 "
"WHERE mytable_1.myid = :myid_1 FOR UPDATE OF "
"mytable_1.myid, mytable_1.name",
)
def test_outer_join(self):
table1 = table(
"mytable",
column("myid", Integer),
column("name", String),
column("description", String),
)
table2 = table(
"myothertable",
column("otherid", Integer),
column("othername", String),
)
table3 = table(
"thirdtable",
column("userid", Integer),
column("otherstuff", String),
)
query = select(
[table1, table2],
or_(
table1.c.name == "fred",
table1.c.myid == 10,
table2.c.othername != "jack",
text("EXISTS (select yay from foo where boo = lar)"),
),
from_obj=[
outerjoin(table1, table2, table1.c.myid == table2.c.otherid)
],
)
self.assert_compile(
query,
"SELECT mytable.myid, mytable.name, "
"mytable.description, myothertable.otherid,"
" myothertable.othername FROM mytable, "
"myothertable WHERE (mytable.name = "
":name_1 OR mytable.myid = :myid_1 OR "
"myothertable.othername != :othername_1 OR "
"EXISTS (select yay from foo where boo = "
"lar)) AND mytable.myid = "
"myothertable.otherid(+)",
dialect=oracle.OracleDialect(use_ansi=False),
)
query = table1.outerjoin(table2,
table1.c.myid == table2.c.otherid).outerjoin(
table3,
table3.c.userid == table2.c.otherid)
self.assert_compile(
query.select(),
"SELECT mytable.myid, mytable.name, "
"mytable.description, myothertable.otherid,"
" myothertable.othername, "
"thirdtable.userid, thirdtable.otherstuff "
"FROM mytable LEFT OUTER JOIN myothertable "
"ON mytable.myid = myothertable.otherid "
"LEFT OUTER JOIN thirdtable ON "
"thirdtable.userid = myothertable.otherid",
)
self.assert_compile(
query.select(),
"SELECT mytable.myid, mytable.name, "
"mytable.description, myothertable.otherid,"
" myothertable.othername, "
"thirdtable.userid, thirdtable.otherstuff "
"FROM mytable, myothertable, thirdtable "
"WHERE thirdtable.userid(+) = "
"myothertable.otherid AND mytable.myid = "
"myothertable.otherid(+)",
dialect=oracle.dialect(use_ansi=False),
)
query = table1.join(table2, table1.c.myid == table2.c.otherid).join(
table3, table3.c.userid == table2.c.otherid)
self.assert_compile(
query.select(),
"SELECT mytable.myid, mytable.name, "
"mytable.description, myothertable.otherid,"
" myothertable.othername, "
"thirdtable.userid, thirdtable.otherstuff "
"FROM mytable, myothertable, thirdtable "
"WHERE thirdtable.userid = "
"myothertable.otherid AND mytable.myid = "
"myothertable.otherid",
dialect=oracle.dialect(use_ansi=False),
)
query = table1.join(table2,
table1.c.myid == table2.c.otherid).outerjoin(
table3, table3.c.userid == table2.c.otherid)
self.assert_compile(
query.select().order_by(table1.c.name).limit(10).offset(5),
"SELECT myid, name, description, otherid, "
"othername, userid, otherstuff FROM "
"(SELECT myid, name, description, otherid, "
"othername, userid, otherstuff, ROWNUM AS "
"ora_rn FROM (SELECT mytable.myid AS myid, "
"mytable.name AS name, mytable.description "
"AS description, myothertable.otherid AS "
"otherid, myothertable.othername AS "
"othername, thirdtable.userid AS userid, "
"thirdtable.otherstuff AS otherstuff FROM "
"mytable, myothertable, thirdtable WHERE "
"thirdtable.userid(+) = "
"myothertable.otherid AND mytable.myid = "
"myothertable.otherid ORDER BY mytable.name) "
"WHERE ROWNUM <= :param_1 + :param_2) "
"WHERE ora_rn > :param_2",
checkparams={
"param_1": 10,
"param_2": 5
},
dialect=oracle.dialect(use_ansi=False),
)
subq = (select([table1]).select_from(
table1.outerjoin(table2,
table1.c.myid == table2.c.otherid)).alias())
q = select([table3]).select_from(
table3.outerjoin(subq, table3.c.userid == subq.c.myid))
self.assert_compile(
q,
"SELECT thirdtable.userid, "
"thirdtable.otherstuff FROM thirdtable "
"LEFT OUTER JOIN (SELECT mytable.myid AS "
"myid, mytable.name AS name, "
"mytable.description AS description FROM "
"mytable LEFT OUTER JOIN myothertable ON "
"mytable.myid = myothertable.otherid) "
"anon_1 ON thirdtable.userid = anon_1.myid",
dialect=oracle.dialect(use_ansi=True),
)
self.assert_compile(
q,
"SELECT thirdtable.userid, "
"thirdtable.otherstuff FROM thirdtable, "
"(SELECT mytable.myid AS myid, "
"mytable.name AS name, mytable.description "
"AS description FROM mytable, myothertable "
"WHERE mytable.myid = myothertable.otherid("
"+)) anon_1 WHERE thirdtable.userid = "
"anon_1.myid(+)",
dialect=oracle.dialect(use_ansi=False),
)
q = select([table1.c.name]).where(table1.c.name == "foo")
self.assert_compile(
q,
"SELECT mytable.name FROM mytable WHERE "
"mytable.name = :name_1",
dialect=oracle.dialect(use_ansi=False),
)
subq = (select([
table3.c.otherstuff
]).where(table3.c.otherstuff == table1.c.name).label("bar"))
q = select([table1.c.name, subq])
self.assert_compile(
q,
"SELECT mytable.name, (SELECT "
"thirdtable.otherstuff FROM thirdtable "
"WHERE thirdtable.otherstuff = "
"mytable.name) AS bar FROM mytable",
dialect=oracle.dialect(use_ansi=False),
)
from sqlalchemy_1_3.sql.functions import FunctionElement
from sqlalchemy_1_3.sql.functions import GenericFunction
from sqlalchemy_1_3.testing import assert_raises
from sqlalchemy_1_3.testing import assert_raises_message
from sqlalchemy_1_3.testing import AssertsCompiledSQL
from sqlalchemy_1_3.testing import engines
from sqlalchemy_1_3.testing import eq_
from sqlalchemy_1_3.testing import fixtures
from sqlalchemy_1_3.testing import is_
from sqlalchemy_1_3.testing.assertions import expect_warnings
from sqlalchemy_1_3.testing.engines import all_dialects
table1 = table(
"mytable",
column("myid", Integer),
column("name", String),
column("description", String),
)
class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
__dialect__ = "default"
def setup(self):
self._registry = deepcopy(functions._registry)
self._case_sensitive_registry = deepcopy(
functions._case_sensitive_registry
)
def teardown(self):
functions._registry = self._registry
def test_limit(self):
t = table("sometable", column("col1"), column("col2"))
s = select([t])
c = s.compile(dialect=oracle.OracleDialect())
assert t.c.col1 in set(c._create_result_map()["col1"][1])
s = select([t]).limit(10).offset(20)
self.assert_compile(
s,
"SELECT col1, col2 FROM (SELECT col1, "
"col2, ROWNUM AS ora_rn FROM (SELECT "
"sometable.col1 AS col1, sometable.col2 AS "
"col2 FROM sometable) WHERE ROWNUM <= "
":param_1 + :param_2) WHERE ora_rn > :param_2",
checkparams={
"param_1": 10,
"param_2": 20
},
)
c = s.compile(dialect=oracle.OracleDialect())
eq_(len(c._result_columns), 2)
assert t.c.col1 in set(c._create_result_map()["col1"][1])
s2 = select([s.c.col1, s.c.col2])
self.assert_compile(
s2,
"SELECT col1, col2 FROM (SELECT col1, col2 "
"FROM (SELECT col1, col2, ROWNUM AS ora_rn "
"FROM (SELECT sometable.col1 AS col1, "
"sometable.col2 AS col2 FROM sometable) "
"WHERE ROWNUM <= :param_1 + :param_2) "
"WHERE ora_rn > :param_2)",
checkparams={
"param_1": 10,
"param_2": 20
},
)
self.assert_compile(
s2,
"SELECT col1, col2 FROM (SELECT col1, col2 "
"FROM (SELECT col1, col2, ROWNUM AS ora_rn "
"FROM (SELECT sometable.col1 AS col1, "
"sometable.col2 AS col2 FROM sometable) "
"WHERE ROWNUM <= :param_1 + :param_2) "
"WHERE ora_rn > :param_2)",
)
c = s2.compile(dialect=oracle.OracleDialect())
eq_(len(c._result_columns), 2)
assert s.c.col1 in set(c._create_result_map()["col1"][1])
s = select([t]).limit(10).offset(20).order_by(t.c.col2)
self.assert_compile(
s,
"SELECT col1, col2 FROM (SELECT col1, "
"col2, ROWNUM AS ora_rn FROM (SELECT "
"sometable.col1 AS col1, sometable.col2 AS "
"col2 FROM sometable ORDER BY "
"sometable.col2) WHERE ROWNUM <= "
":param_1 + :param_2) WHERE ora_rn > :param_2",
checkparams={
"param_1": 10,
"param_2": 20
},
)
c = s.compile(dialect=oracle.OracleDialect())
eq_(len(c._result_columns), 2)
assert t.c.col1 in set(c._create_result_map()["col1"][1])
s = select([t]).with_for_update().limit(10).order_by(t.c.col2)
self.assert_compile(
s,
"SELECT col1, col2 FROM (SELECT "
"sometable.col1 AS col1, sometable.col2 AS "
"col2 FROM sometable ORDER BY "
"sometable.col2) WHERE ROWNUM <= :param_1 "
"FOR UPDATE",
)
s = (select([t]).with_for_update().limit(10).offset(20).order_by(
t.c.col2))
self.assert_compile(
s,
"SELECT col1, col2 FROM (SELECT col1, "
"col2, ROWNUM AS ora_rn FROM (SELECT "
"sometable.col1 AS col1, sometable.col2 AS "
"col2 FROM sometable ORDER BY "
"sometable.col2) WHERE ROWNUM <= "
":param_1 + :param_2) WHERE ora_rn > :param_2 FOR "
"UPDATE",
)
def test_unsupported_casts(self, type_, expected):
t = sql.table("t", sql.column("col"))
with expect_warnings("Datatype .* does not support CAST on MySQL;"):
self.assert_compile(cast(t.c.col, type_), expected)
def test_inner_join(self):
t1 = table("t1", column("x"))
t2 = table("t2", column("y"))
self.assert_compile(t1.join(t2, t1.c.x == t2.c.y),
"t1 INNER JOIN t2 ON t1.x = t2.y")
def test_cast(self, type_, expected):
t = sql.table("t", sql.column("col"))
self.assert_compile(cast(t.c.col, type_), expected)
def test_delete_extra_froms(self):
t1 = table("t1", column("c1"))
t2 = table("t2", column("c1"))
q = sql.delete(t1).where(t1.c.c1 == t2.c.c1)
self.assert_compile(q,
"DELETE FROM t1 USING t1, t2 WHERE t1.c1 = t2.c1")
def test_match(self):
matchtable = table("matchtable", column("title", String))
self.assert_compile(
matchtable.c.title.match("somstr"),
"MATCH (matchtable.title) AGAINST (%s IN BOOLEAN MODE)",
)
def test_select_with_nolock(self):
t = table("sometable", column("somecolumn"))
self.assert_compile(
t.select().with_hint(t, "WITH (NOLOCK)"),
"SELECT sometable.somecolumn FROM sometable WITH (NOLOCK)",
)
def test_identifier_rendering(self, table_name, rendered_name):
t = table(table_name, column("somecolumn"))
self.assert_compile(
t.select(), "SELECT {0}.somecolumn FROM {0}".format(rendered_name))
