def test_create_index_schema(self):
t = Table("t", MetaData(), Column("x", Integer), schema="foo")
i = Index("xyz", t.c.x)
self.assert_compile(schema.CreateIndex(i),
"CREATE INDEX xyz ON foo.t (x)")
class B(Base):
__tablename__ = "b"
id = Column(Integer, primary_key=True)
a_id = Column(ForeignKey("a.id"))
cs = relationship("C")
def _table_fixture(self):
m = MetaData()
return (m, ) + tuple(
Table("t%d" % i, m, Column("x", Integer)) for i in range(1, 6))
class B(ComparableEntity, Base):
__tablename__ = "b"
id = Column(Integer, primary_key=True)
class D(ComparableEntity, Base):
__tablename__ = "d"
id = Column(Integer, primary_key=True)
c_id = Column(ForeignKey("c.id"))
b_id = Column(ForeignKey("b.id"))
class B(Base):
__tablename__ = "b"
id = Column(Integer, primary_key=True)
a_id = Column(ForeignKey(A.id))
a = relationship(A)
class A(Base):
__tablename__ = "a"
id = Column(Integer, primary_key=True)
data = Column(String)
bs = relationship("B")
def test_python_fn_default(self):
metadata = MetaData()
table = Table(
"sometable",
metadata,
Column("id", Integer, primary_key=True),
Column("data", String),
Column("foo", Integer, default=lambda: 10),
)
values = [
{
"id": 1,
"data": "data1"
},
{
"id": 2,
"data": "data2",
"foo": 15
},
{
"id": 3,
"data": "data3"
},
]
checkparams = {
"id_m0": 1,
"id_m1": 2,
"id_m2": 3,
"data_m0": "data1",
"data_m1": "data2",
"data_m2": "data3",
"foo": None, # evaluated later
"foo_m1": 15,
"foo_m2": None, # evaluated later
}
stmt = table.insert().values(values)
eq_(
dict([(k, v.type._type_affinity) for (k, v) in stmt.compile(
dialect=postgresql.dialect()).binds.items()]),
{
"foo": Integer,
"data_m2": String,
"id_m0": Integer,
"id_m2": Integer,
"foo_m1": Integer,
"data_m1": String,
"id_m1": Integer,
"foo_m2": Integer,
"data_m0": String,
},
)
self.assert_compile(
stmt,
"INSERT INTO sometable (id, data, foo) VALUES "
"(%(id_m0)s, %(data_m0)s, %(foo)s), "
"(%(id_m1)s, %(data_m1)s, %(foo_m1)s), "
"(%(id_m2)s, %(data_m2)s, %(foo_m2)s)",
checkparams=checkparams,
dialect=postgresql.dialect(),
)
def test_sql_functions(self):
metadata = MetaData()
table = Table(
"sometable",
metadata,
Column("id", Integer, primary_key=True),
Column("data", String),
Column("foo", Integer),
)
values = [
{
"id": 1,
"data": "foo",
"foo": func.foob()
},
{
"id": 2,
"data": "bar",
"foo": func.foob()
},
{
"id": 3,
"data": "bar",
"foo": func.bar()
},
{
"id": 4,
"data": "bar",
"foo": 15
},
{
"id": 5,
"data": "bar",
"foo": func.foob()
},
]
checkparams = {
"id_m0": 1,
"data_m0": "foo",
"id_m1": 2,
"data_m1": "bar",
"id_m2": 3,
"data_m2": "bar",
"id_m3": 4,
"data_m3": "bar",
"foo_m3": 15,
"id_m4": 5,
"data_m4": "bar",
}
self.assert_compile(
table.insert().values(values),
"INSERT INTO sometable (id, data, foo) VALUES "
"(%(id_m0)s, %(data_m0)s, foob()), "
"(%(id_m1)s, %(data_m1)s, foob()), "
"(%(id_m2)s, %(data_m2)s, bar()), "
"(%(id_m3)s, %(data_m3)s, %(foo_m3)s), "
"(%(id_m4)s, %(data_m4)s, foob())",
checkparams=checkparams,
dialect=postgresql.dialect(),
)
def define_tables(cls, metadata):
global foo, bar, blub, bar_foo, blub_bar, blub_foo
# the 'data' columns are to appease SQLite which cant handle a blank
# INSERT
foo = Table(
"foo",
metadata,
Column(
"id",
Integer,
Sequence("foo_seq", optional=True),
primary_key=True,
),
Column("data", String(20)),
)
bar = Table(
"bar",
metadata,
Column("id", Integer, ForeignKey("foo.id"), primary_key=True),
Column("bar_data", String(20)),
)
blub = Table(
"blub",
metadata,
Column("id", Integer, ForeignKey("bar.id"), primary_key=True),
Column("blub_data", String(20)),
)
bar_foo = Table(
"bar_foo",
metadata,
Column("bar_id", Integer, ForeignKey("bar.id")),
Column("foo_id", Integer, ForeignKey("foo.id")),
)
blub_bar = Table(
"bar_blub",
metadata,
Column("blub_id", Integer, ForeignKey("blub.id")),
Column("bar_id", Integer, ForeignKey("bar.id")),
)
blub_foo = Table(
"blub_foo",
metadata,
Column("blub_id", Integer, ForeignKey("blub.id")),
Column("foo_id", Integer, ForeignKey("foo.id")),
)
def _baseline_1_create_tables(self):
zoo = Table(
"Zoo",
self.metadata,
Column(
"ID",
Integer,
Sequence("zoo_id_seq"),
primary_key=True,
index=True,
),
Column("Name", Unicode(255)),
Column("Founded", Date),
Column("Opens", Time),
Column("LastEscape", DateTime),
Column("Admission", Float),
)
animal = Table(
"Animal",
self.metadata,
Column("ID", Integer, Sequence("animal_id_seq"), primary_key=True),
Column("ZooID", Integer, ForeignKey("Zoo.ID"), index=True),
Column("Name", Unicode(100)),
Column("Species", Unicode(100)),
Column("Legs", Integer, default=4),
Column("LastEscape", DateTime),
Column("Lifespan", Float(4)),
Column("MotherID", Integer, ForeignKey("Animal.ID")),
Column("PreferredFoodID", Integer),
Column("AlternateFoodID", Integer),
)
self.metadata.create_all()
global Zoo, Animal
class Zoo(object):
def __init__(self, **kwargs):
for k, v in kwargs.items():
setattr(self, k, v)
class Animal(object):
def __init__(self, **kwargs):
for k, v in kwargs.items():
setattr(self, k, v)
mapper(Zoo, zoo)
mapper(Animal, animal)
def test_with_polymorphic(self):
metadata = MetaData(testing.db)
order = Table(
"orders",
metadata,
Column("id", Integer, primary_key=True),
Column(
"employee_id",
Integer,
ForeignKey("employees.id"),
nullable=False,
),
Column("type", Unicode(16)),
)
employee = Table(
"employees",
metadata,
Column("id", Integer, primary_key=True),
Column("name", Unicode(16), unique=True, nullable=False),
)
product = Table("products", metadata,
Column("id", Integer, primary_key=True))
orderproduct = Table(
"orderproducts",
metadata,
Column("id", Integer, primary_key=True),
Column("order_id",
Integer,
ForeignKey("orders.id"),
nullable=False),
Column(
"product_id",
Integer,
ForeignKey("products.id"),
nullable=False,
),
)
class Order(object):
pass
class Employee(object):
pass
class Product(object):
pass
class OrderProduct(object):
pass
order_join = order.select().alias("pjoin")
mapper(
Order,
order,
with_polymorphic=("*", order_join),
polymorphic_on=order_join.c.type,
polymorphic_identity="order",
properties={
"orderproducts":
relationship(OrderProduct, lazy="select", backref="order")
},
)
mapper(
Product,
product,
properties={
"orderproducts":
relationship(OrderProduct, lazy="select", backref="product")
},
)
mapper(
Employee,
employee,
properties={
"orders": relationship(Order,
lazy="select",
backref="employee")
},
)
mapper(OrderProduct, orderproduct)
# this requires that the compilation of order_mapper's "surrogate
# mapper" occur after the initial setup of MapperProperty objects on
# the mapper.
configure_mappers()
def test_conflicting_backref_one(self):
"""test that conflicting backrefs raises an exception"""
metadata = MetaData(testing.db)
order = Table(
"orders",
metadata,
Column("id", Integer, primary_key=True),
Column("type", Unicode(16)),
)
product = Table("products", metadata,
Column("id", Integer, primary_key=True))
orderproduct = Table(
"orderproducts",
metadata,
Column("id", Integer, primary_key=True),
Column("order_id",
Integer,
ForeignKey("orders.id"),
nullable=False),
Column(
"product_id",
Integer,
ForeignKey("products.id"),
nullable=False,
),
)
class Order(object):
pass
class Product(object):
pass
class OrderProduct(object):
pass
order_join = order.select().alias("pjoin")
mapper(
Order,
order,
with_polymorphic=("*", order_join),
polymorphic_on=order_join.c.type,
polymorphic_identity="order",
properties={
"orderproducts":
relationship(OrderProduct, lazy="select", backref="product")
},
)
mapper(
Product,
product,
properties={
"orderproducts":
relationship(OrderProduct, lazy="select", backref="product")
},
)
mapper(OrderProduct, orderproduct)
assert_raises_message(sa_exc.ArgumentError, "Error creating backref",
configure_mappers)
def test_drop_index_schema(self):
t = Table("t", MetaData(), Column("x", Integer), schema="foo")
i = Index("xyz", t.c.x)
self.assert_compile(schema.DropIndex(i), "DROP INDEX foo.xyz")
def test_column_expr(self):
c = Column("x", Integer)
is_(inspect(c), c)
assert not c.is_selectable
assert not hasattr(c, "selectable")
class Book(Base):
__tablename__ = "book"
id = Column(Integer, primary_key=True)
pages = relationship("Page")
def _xy_table_fixture(self):
m = MetaData()
t = Table("t", m, Column("x", Integer), Column("y", Integer))
return t
class A(Base):
__tablename__ = "a"
id = Column(Integer, primary_key=True)
data = Column(String(30))
deferred_data = deferred(Column(String(30)))
def setup_class(cls):
m = MetaData()
cls.left = Table(
"lft",
m,
Column("id", Integer, primary_key=True),
Column("x", Integer),
Column("y", Integer),
)
cls.right = Table(
"rgt",
m,
Column("id", Integer, primary_key=True),
Column("lid", Integer, ForeignKey("lft.id")),
Column("x", Integer),
Column("y", Integer),
)
cls.right_multi_fk = Table(
"rgt_multi_fk",
m,
Column("id", Integer, primary_key=True),
Column("lid1", Integer, ForeignKey("lft.id")),
Column("lid2", Integer, ForeignKey("lft.id")),
)
cls.selfref = Table(
"selfref",
m,
Column("id", Integer, primary_key=True),
Column("sid", Integer, ForeignKey("selfref.id")),
)
cls.composite_selfref = Table(
"composite_selfref",
m,
Column("id", Integer, primary_key=True),
Column("group_id", Integer, primary_key=True),
Column("parent_id", Integer),
ForeignKeyConstraint(
["parent_id", "group_id"],
["composite_selfref.id", "composite_selfref.group_id"],
),
)
cls.m2mleft = Table("m2mlft", m, Column("id",
Integer,
primary_key=True))
cls.m2mright = Table("m2mrgt", m,
Column("id", Integer, primary_key=True))
cls.m2msecondary = Table(
"m2msecondary",
m,
Column("lid", Integer, ForeignKey("m2mlft.id"), primary_key=True),
Column("rid", Integer, ForeignKey("m2mrgt.id"), primary_key=True),
)
cls.m2msecondary_no_fks = Table(
"m2msecondary_no_fks",
m,
Column("lid", Integer, primary_key=True),
Column("rid", Integer, primary_key=True),
)
cls.m2msecondary_ambig_fks = Table(
"m2msecondary_ambig_fks",
m,
Column("lid1", Integer, ForeignKey("m2mlft.id"), primary_key=True),
Column("rid1", Integer, ForeignKey("m2mrgt.id"), primary_key=True),
Column("lid2", Integer, ForeignKey("m2mlft.id"), primary_key=True),
Column("rid2", Integer, ForeignKey("m2mrgt.id"), primary_key=True),
)
cls.base_w_sub_rel = Table(
"base_w_sub_rel",
m,
Column("id", Integer, primary_key=True),
Column("sub_id", Integer, ForeignKey("rel_sub.id")),
)
cls.rel_sub = Table(
"rel_sub",
m,
Column(
"id",
Integer,
ForeignKey("base_w_sub_rel.id"),
primary_key=True,
),
)
cls.base = Table(
"base",
m,
Column("id", Integer, primary_key=True),
Column("flag", Boolean),
)
cls.sub = Table(
"sub",
m,
Column("id", Integer, ForeignKey("base.id"), primary_key=True),
)
cls.sub_w_base_rel = Table(
"sub_w_base_rel",
m,
Column("id", Integer, ForeignKey("base.id"), primary_key=True),
Column("base_id", Integer, ForeignKey("base.id")),
)
cls.sub_w_sub_rel = Table(
"sub_w_sub_rel",
m,
Column("id", Integer, ForeignKey("base.id"), primary_key=True),
Column("sub_id", Integer, ForeignKey("sub.id")),
)
cls.right_w_base_rel = Table(
"right_w_base_rel",
m,
Column("id", Integer, primary_key=True),
Column("base_id", Integer, ForeignKey("base.id")),
)
cls.three_tab_a = Table("three_tab_a", m,
Column("id", Integer, primary_key=True))
cls.three_tab_b = Table(
"three_tab_b",
m,
Column("id", Integer, primary_key=True),
Column("aid", Integer, ForeignKey("three_tab_a.id")),
)
cls.three_tab_c = Table(
"three_tab_c",
m,
Column("id", Integer, primary_key=True),
Column("aid", Integer, ForeignKey("three_tab_a.id")),
Column("bid", Integer, ForeignKey("three_tab_b.id")),
)
cls.composite_target = Table(
"composite_target",
m,
Column("uid", Integer, primary_key=True),
Column("oid", Integer, primary_key=True),
)
cls.composite_multi_ref = Table(
"composite_multi_ref",
m,
Column("uid1", Integer),
Column("uid2", Integer),
Column("oid", Integer),
ForeignKeyConstraint(
("uid1", "oid"),
("composite_target.uid", "composite_target.oid"),
),
ForeignKeyConstraint(
("uid2", "oid"),
("composite_target.uid", "composite_target.oid"),
),
)
cls.purely_single_col = Table("purely_single_col", m,
Column("path", String))
class Book(Base):
__tablename__ = "book"
id = Column(Integer, primary_key=True)
title = Column(String(50), nullable=False)
pages = relationship("Page", backref="book")
def data(cls):
time.sleep(0.001)
return Column(String)
class Page(Base):
__tablename__ = "page"
id = Column(Integer, primary_key=True)
book_id = Column(ForeignKey("book.id"))
title = Column(String(50))
class C(ComparableEntity, Base):
__tablename__ = "c"
id = Column(Integer, primary_key=True)
a_id = Column(ForeignKey("a.id"))
def test_update(self):
"""
Tests sending functions and SQL expressions to the VALUES and SET
clauses of INSERT/UPDATE instances, and that column-level defaults
get overridden.
"""
meta = self.metadata
t = Table(
"t1",
meta,
Column(
"id",
Integer,
Sequence("t1idseq", optional=True),
primary_key=True,
),
Column("value", Integer),
)
t2 = Table(
"t2",
meta,
Column(
"id",
Integer,
Sequence("t2idseq", optional=True),
primary_key=True,
),
Column("value", Integer, default=7),
Column("stuff", String(20), onupdate="thisisstuff"),
)
meta.create_all()
t.insert(values=dict(value=func.length("one"))).execute()
assert t.select().execute().first()["value"] == 3
t.update(values=dict(value=func.length("asfda"))).execute()
assert t.select().execute().first()["value"] == 5
r = t.insert(values=dict(value=func.length("sfsaafsda"))).execute()
id_ = r.inserted_primary_key[0]
assert t.select(t.c.id == id_).execute().first()["value"] == 9
t.update(values={t.c.value: func.length("asdf")}).execute()
assert t.select().execute().first()["value"] == 4
t2.insert().execute()
t2.insert(values=dict(value=func.length("one"))).execute()
t2.insert(values=dict(value=func.length("asfda") + -19)).execute(
stuff="hi"
)
res = exec_sorted(select([t2.c.value, t2.c.stuff]))
eq_(res, [(-14, "hi"), (3, None), (7, None)])
t2.update(values=dict(value=func.length("asdsafasd"))).execute(
stuff="some stuff"
)
assert select([t2.c.value, t2.c.stuff]).execute().fetchall() == [
(9, "some stuff"),
(9, "some stuff"),
(9, "some stuff"),
]
t2.delete().execute()
t2.insert(values=dict(value=func.length("one") + 8)).execute()
assert t2.select().execute().first()["value"] == 11
t2.update(values=dict(value=func.length("asfda"))).execute()
eq_(
select([t2.c.value, t2.c.stuff]).execute().first(),
(5, "thisisstuff"),
)
t2.update(
values={t2.c.value: func.length("asfdaasdf"), t2.c.stuff: "foo"}
).execute()
eq_(select([t2.c.value, t2.c.stuff]).execute().first(), (9, "foo"))
class A(Base):
__tablename__ = "a"
id = Column(Integer, primary_key=True)
class Foo(Base):
__tablename__ = "foo"
id = Column(Integer, primary_key=True)
class C(Base):
__tablename__ = "c"
id = Column(Integer, primary_key=True)
b_id = Column(ForeignKey("b.id"))
def test_table(self):
t = Table("t", MetaData(), Column("x", Integer))
is_(inspect(t), t)
assert t.is_selectable
is_(t.selectable, t)
def _baseline_1_create_tables(self):
Table(
"Zoo",
self.metadata,
Column(
"ID",
Integer,
Sequence("zoo_id_seq"),
primary_key=True,
index=True,
),
Column("Name", Unicode(255)),
Column("Founded", Date),
Column("Opens", Time),
Column("LastEscape", DateTime),
Column("Admission", Float),
)
Table(
"Animal",
self.metadata,
Column("ID", Integer, Sequence("animal_id_seq"), primary_key=True),
Column("ZooID", Integer, ForeignKey("Zoo.ID"), index=True),
Column("Name", Unicode(100)),
Column("Species", Unicode(100)),
Column("Legs", Integer, default=4),
Column("LastEscape", DateTime),
Column("Lifespan", Float(4)),
Column("MotherID", Integer, ForeignKey("Animal.ID")),
Column("PreferredFoodID", Integer),
Column("AlternateFoodID", Integer),
)
self.metadata.create_all()
def test_cycle_unnamed_fks(self):
metadata = MetaData(testing.db)
Table(
"a",
metadata,
Column("id", Integer, primary_key=True),
Column("bid", Integer, ForeignKey("b.id")),
)
Table(
"b",
metadata,
Column("id", Integer, primary_key=True),
Column("aid", Integer, ForeignKey("a.id")),
)
assertions = [
AllOf(
CompiledSQL("CREATE TABLE b ("
"id INTEGER NOT NULL, "
"aid INTEGER, "
"PRIMARY KEY (id)"
")"),
CompiledSQL("CREATE TABLE a ("
"id INTEGER NOT NULL, "
"bid INTEGER, "
"PRIMARY KEY (id)"
")"),
),
AllOf(
CompiledSQL("ALTER TABLE b ADD "
"FOREIGN KEY(aid) REFERENCES a (id)"),
CompiledSQL("ALTER TABLE a ADD "
"FOREIGN KEY(bid) REFERENCES b (id)"),
),
]
with self.sql_execution_asserter() as asserter:
metadata.create_all(checkfirst=False)
if testing.db.dialect.supports_alter:
asserter.assert_(*assertions)
assert_raises_message(
exc.CircularDependencyError,
"Can't sort tables for DROP; an unresolvable foreign key "
"dependency exists between tables: a, b. "
"Please ensure that the "
"ForeignKey and ForeignKeyConstraint objects involved in the "
"cycle have names so that they can be dropped using "
"DROP CONSTRAINT.",
metadata.drop_all,
checkfirst=False,
)
else:
with expect_warnings(
"Can't sort tables for DROP; an unresolvable "
"foreign key dependency exists between tables"):
with self.sql_execution_asserter() as asserter:
metadata.drop_all(checkfirst=False)
asserter.assert_(
AllOf(CompiledSQL("DROP TABLE b"),
CompiledSQL("DROP TABLE a")))
