def test_union_label(self):
s1 = select([func.foo("hoho").label("x")])
s2 = select([func.foo("Bar").label("y")])
stmt = union(s1, s2).order_by("x")
self.assert_compile(
stmt,
"SELECT foo(:foo_1) AS x UNION SELECT foo(:foo_2) AS y ORDER BY x",
)
def test_function(self):
self.assert_compile(func.foo(1, 2), "foo(:foo_1, :foo_2)")
self.assert_compile(func.current_time(), "CURRENT_TIME")
self.assert_compile(func.foo(), "foo")
m = MetaData()
t = Table("sometable", m, Column("col1", Integer),
Column("col2", Integer))
self.assert_compile(
select([func.max(t.c.col1)]),
"SELECT max(sometable.col1) AS max_1 FROM "
"sometable",
)
def test_columnadapter_anonymized(self):
"""test issue #3148
Testing the anonymization applied from the ColumnAdapter.columns
collection, typically as used in eager loading.
"""
exprs = [
table1.c.myid,
table1.c.name.label("t1name"),
func.foo("hoho").label("x"),
]
ta = table1.alias()
adapter = sql_util.ColumnAdapter(ta, anonymize_labels=True)
s1 = (select([adapter.columns[expr] for expr in exprs
]).apply_labels().order_by("myid", "t1name", "x"))
assert_raises_message(
exc.CompileError,
r"Can't resolve label reference for ORDER BY / GROUP BY / "
"DISTINCT etc. "
"Textual SQL "
"expression 't1name' should be explicitly "
r"declared as text\('t1name'\)",
s1.compile,
)
def test_columnadapter_non_anonymized(self):
"""test issue #3148
Testing the anonymization applied from the ColumnAdapter.columns
collection, typically as used in eager loading.
"""
exprs = [
table1.c.myid,
table1.c.name.label("t1name"),
func.foo("hoho").label("x"),
]
ta = table1.alias()
adapter = sql_util.ColumnAdapter(ta)
s1 = (select([adapter.columns[expr] for expr in exprs
]).apply_labels().order_by("myid", "t1name", "x"))
# labels are maintained
self.assert_compile(
s1,
"SELECT mytable_1.myid AS mytable_1_myid, "
"mytable_1.name AS t1name, foo(:foo_1) AS x "
"FROM mytable AS mytable_1 ORDER BY mytable_1.myid, t1name, x",
)
def _join_fixture_o2m_to_annotated_func(self, **kw):
return relationships.JoinCondition(
self.left,
self.right,
self.left,
self.right,
primaryjoin=self.left.c.id == foreign(func.foo(self.right.c.lid)),
**kw)
def test_order_by_func_label_desc(self):
stmt = select([func.foo("bar").label("fb"),
table1]).order_by(desc("fb"))
self.assert_compile(
stmt,
"SELECT foo(:foo_1) AS fb, mytable.myid, mytable.name, "
"mytable.description FROM mytable ORDER BY fb DESC",
)
def _join_fixture_o2m_to_oldstyle_func(self, **kw):
return relationships.JoinCondition(
self.left,
self.right,
self.left,
self.right,
primaryjoin=self.left.c.id == func.foo(self.right.c.lid),
consider_as_foreign_keys=[self.right.c.lid],
**kw)
def test_exec_options(self):
f = func.foo()
eq_(f._execution_options, {})
f = f.execution_options(foo="bar")
eq_(f._execution_options, {"foo": "bar"})
s = f.select()
eq_(s._execution_options, {"foo": "bar"})
ret = testing.db.execute(func.now().execution_options(foo="bar"))
eq_(ret.context.execution_options, {"foo": "bar"})
ret.close()
def _join_fixture_m2o_sub_to_joined_sub_func(self, **kw):
# see test.orm.test_mapper:MapperTest.test_add_column_prop_deannotate,
right = self.base.join(self.right_w_base_rel,
self.base.c.id == self.right_w_base_rel.c.id)
return relationships.JoinCondition(
self.right_w_base_rel,
right,
self.right_w_base_rel,
self.right_w_base_rel,
primaryjoin=self.right_w_base_rel.c.base_id == func.foo(
self.base.c.id),
)
def _join_fixture_o2m_composite_selfref_func_annotated(self, **kw):
return relationships.JoinCondition(
self.composite_selfref,
self.composite_selfref,
self.composite_selfref,
self.composite_selfref,
primaryjoin=and_(
remote(self.composite_selfref.c.group_id) == func.foo(
self.composite_selfref.c.group_id),
remote(self.composite_selfref.c.parent_id) ==
self.composite_selfref.c.id,
),
**kw)
def test_functional_index_w_string_cols_combo(self):
metadata = MetaData()
x = Table(
"x",
metadata,
Column("q", String(50)),
Column("p", Integer),
Column("z", Integer),
)
for idx, ddl in [
(
Index("y", func.lower(x.c.q), "p", x.c.z),
"CREATE INDEX y ON x (lower(q), p, z)",
),
(
Index("y", "p", func.lower(x.c.q), "z"),
"CREATE INDEX y ON x (p, lower(q), z)",
),
(
Index("y", "p", "z", func.lower(x.c.q)),
"CREATE INDEX y ON x (p, z, lower(q))",
),
(
Index("y", func.foo("foob"), x.c.p, "z"),
"CREATE INDEX y ON x (foo('foob'), p, z)",
),
(
Index("y", x.c.p, func.foo("foob"), "z"),
"CREATE INDEX y ON x (p, foo('foob'), z)",
),
(
Index("y", func.foo("foob"), "p", "z"),
"CREATE INDEX y ON x (foo('foob'), p, z)",
),
]:
x.append_constraint(idx)
self.assert_compile(schema.CreateIndex(idx), ddl)
def test_update_to_expression(self):
"""test update from an expression.
this logic is triggered currently by a left side that doesn't
have a key. The current supported use case is updating the index
of a PostgreSQL ARRAY type.
"""
table1 = self.tables.mytable
expr = func.foo(table1.c.myid)
eq_(expr.key, None)
self.assert_compile(
table1.update().values({expr: "bar"}),
"UPDATE mytable SET foo(myid)=:param_1",
)
def test_offset_dont_misapply_labelreference(self):
m = MetaData()
t = Table("t", m, Column("x", Integer))
expr1 = func.foo(t.c.x).label("x")
expr2 = func.foo(t.c.x).label("y")
stmt1 = select([expr1]).order_by(expr1.desc()).offset(1)
stmt2 = select([expr2]).order_by(expr2.desc()).offset(1)
self.assert_compile(
stmt1,
"SELECT anon_1.x FROM (SELECT foo(t.x) AS x, "
"ROW_NUMBER() OVER (ORDER BY foo(t.x) DESC) AS mssql_rn FROM t) "
"AS anon_1 WHERE mssql_rn > :param_1",
)
self.assert_compile(
stmt2,
"SELECT anon_1.y FROM (SELECT foo(t.x) AS y, "
"ROW_NUMBER() OVER (ORDER BY foo(t.x) DESC) AS mssql_rn FROM t) "
"AS anon_1 WHERE mssql_rn > :param_1",
)
def test_inline_defaults(self):
m = MetaData()
foo = Table("foo", m, Column("id", Integer))
t = Table(
"test",
m,
Column("col1", Integer, default=func.foo(1)),
Column(
"col2",
Integer,
default=select([func.coalesce(func.max(foo.c.id))]),
),
)
self.assert_compile(
t.insert(inline=True, values={}),
"INSERT INTO test (col1, col2) VALUES (foo(:foo_1), "
"(SELECT coalesce(max(foo.id)) AS coalesce_1 FROM "
"foo))",
)
def test_inline_defaults(self):
m = MetaData()
foo = Table("foo", m, Column("id", Integer))
t = Table(
"test",
m,
Column("col1", Integer, onupdate=func.foo(1)),
Column(
"col2",
Integer,
onupdate=select([func.coalesce(func.max(foo.c.id))]),
),
Column("col3", String(30)),
)
self.assert_compile(
t.update(inline=True, values={"col3": "foo"}),
"UPDATE test SET col1=foo(:foo_1), col2=(SELECT "
"coalesce(max(foo.id)) AS coalesce_1 FROM foo), "
"col3=:col3",
)
def value(cls):
"This is a class-level docstring"
return func.foo(cls._value) + cls.bar_value
def value(cls, value):
"This is a class-level docstring"
return func.foo(cls._value, value) + value
def other_value(cls, value):
return func.foo(cls._value, value) + value
def value(cls):
return func.foo(cls._value) + cls.bar_value
def test_use_labels(self):
self.assert_compile(
select([func.foo()], use_labels=True), "SELECT foo() AS foo_1"
)
