def test_multiple(self):
m = MetaData()
Table(
"foo",
m,
Column("id", Integer, primary_key=True),
Column("bar", Integer, primary_key=True),
)
tb = Table(
"some_table",
m,
Column("id", Integer, primary_key=True),
Column("foo_id", Integer, ForeignKey("foo.id")),
Column("foo_bar", Integer, ForeignKey("foo.bar")),
)
self.assert_compile(
schema.CreateTable(tb),
"CREATE TABLE some_table ("
"id INTEGER NOT NULL, "
"foo_id INTEGER, "
"foo_bar INTEGER, "
"PRIMARY KEY (id), "
"FOREIGN KEY(foo_id) REFERENCES foo (id), "
"FOREIGN KEY(foo_bar) REFERENCES foo (bar))",
)
def test_use_alter(self):
m = MetaData()
Table("t", m, Column("a", Integer))
Table(
"t2",
m,
Column("a", Integer, ForeignKey("t.a",
use_alter=True,
name="fk_ta")),
Column("b", Integer, ForeignKey("t.a", name="fk_tb")),
)
e = engines.mock_engine(dialect_name="postgresql")
m.create_all(e)
m.drop_all(e)
e.assert_sql([
"CREATE TABLE t (a INTEGER)",
"CREATE TABLE t2 (a INTEGER, b INTEGER, CONSTRAINT fk_tb "
"FOREIGN KEY(b) REFERENCES t (a))",
"ALTER TABLE t2 "
"ADD CONSTRAINT fk_ta FOREIGN KEY(a) REFERENCES t (a)",
"ALTER TABLE t2 DROP CONSTRAINT fk_ta",
"DROP TABLE t2",
"DROP TABLE t",
])
def define_tables(cls, metadata):
Table(
"users",
metadata,
Column(
"id", Integer, primary_key=True, test_needs_autoincrement=True
),
Column("name", String(50)),
test_needs_fk=True,
)
Table(
"user_items",
metadata,
Column("user_id", ForeignKey("users.id"), primary_key=True),
Column("item_id", ForeignKey("items.id"), primary_key=True),
test_needs_fk=True,
)
Table(
"items",
metadata,
Column(
"id", Integer, primary_key=True, test_needs_autoincrement=True
),
Column("name", String(50)),
test_needs_fk=True,
)
def define_tables(cls, metadata):
global foo, bar, foo_bar
foo = Table(
"foo",
metadata,
Column(
"id",
Integer,
Sequence("foo_id_seq", optional=True),
primary_key=True,
),
Column("data", String(20)),
)
bar = Table(
"bar",
metadata,
Column("bid", Integer, ForeignKey("foo.id"), primary_key=True),
)
foo_bar = Table(
"foo_bar",
metadata,
Column("foo_id", Integer, ForeignKey("foo.id")),
Column("bar_id", Integer, ForeignKey("bar.bid")),
)
def define_tables(cls, metadata):
Table(
"datas",
metadata,
Column("id",
Integer,
primary_key=True,
test_needs_autoincrement=True),
Column("a", Integer, nullable=False),
)
Table(
"foo",
metadata,
Column("data_id",
Integer,
ForeignKey("datas.id"),
primary_key=True),
Column("bar", Integer),
)
Table(
"stats",
metadata,
Column("id",
Integer,
primary_key=True,
test_needs_autoincrement=True),
Column("data_id", Integer, ForeignKey("datas.id")),
Column("somedata", Integer, nullable=False),
)
def define_tables(cls, metadata):
Table(
"base",
metadata,
Column("uid", String(30), primary_key=True),
Column("x", String(30)),
)
Table(
"derived",
metadata,
Column("uid", String(30), ForeignKey("base.uid"),
primary_key=True),
Column("y", String(30)),
)
Table(
"derivedII",
metadata,
Column("uid", String(30), ForeignKey("base.uid"),
primary_key=True),
Column("z", String(30)),
)
Table(
"comments",
metadata,
Column("id",
Integer,
primary_key=True,
test_needs_autoincrement=True),
Column("uid", String(30), ForeignKey("base.uid")),
Column("comment", String(30)),
)
def define_tables(cls, metadata):
Table(
"users",
metadata,
Column(
"id", Integer, primary_key=True, test_needs_autoincrement=True
),
Column("name", String(30), nullable=False),
Column("some_update", String(30), onupdate="im the update"),
)
Table(
"addresses",
metadata,
Column(
"id", Integer, primary_key=True, test_needs_autoincrement=True
),
Column("user_id", None, ForeignKey("users.id")),
Column("email_address", String(50), nullable=False),
)
Table(
"foobar",
metadata,
Column(
"id", Integer, primary_key=True, test_needs_autoincrement=True
),
Column("user_id", None, ForeignKey("users.id")),
Column("data", String(30)),
Column("some_update", String(30), onupdate="im the other update"),
)
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 define_tables(cls, metadata):
Table(
"items",
metadata,
Column(
"item_id",
Integer,
primary_key=True,
test_needs_autoincrement=True,
),
Column("name", String(40)),
)
Table(
"item_keywords",
metadata,
Column("item_id", Integer, ForeignKey("items.item_id")),
Column("keyword_id", Integer, ForeignKey("keywords.keyword_id")),
Column("data", String(40)),
)
Table(
"keywords",
metadata,
Column(
"keyword_id",
Integer,
primary_key=True,
test_needs_autoincrement=True,
),
Column("name", String(40)),
)
def define_tables(cls, metadata):
Table(
"people",
metadata,
Column("people_id", Integer, primary_key=True),
Column("age", Integer),
Column("name", String(30)),
)
Table(
"bookcases",
metadata,
Column("bookcase_id", Integer, primary_key=True),
Column(
"bookcase_owner_id", Integer, ForeignKey("people.people_id")
),
Column("bookcase_shelves", Integer),
Column("bookcase_width", Integer),
)
Table(
"books",
metadata,
Column("book_id", Integer, primary_key=True),
Column(
"bookcase_id", Integer, ForeignKey("bookcases.bookcase_id")
),
Column("book_owner_id", Integer, ForeignKey("people.people_id")),
Column("book_weight", Integer),
)
def define_tables(cls, metadata):
Table(
"left",
metadata,
Column("id",
Integer,
primary_key=True,
test_needs_autoincrement=True),
Column("data", String(30)),
)
Table(
"right",
metadata,
Column("id",
Integer,
primary_key=True,
test_needs_autoincrement=True),
Column("data", String(30)),
)
Table(
"secondary",
metadata,
Column("left_id", Integer, ForeignKey("left.id"),
primary_key=True),
Column("right_id",
Integer,
ForeignKey("right.id"),
primary_key=True),
)
def define_tables(cls, metadata):
Table("a", metadata, Column("id", Integer, primary_key=True))
Table(
"b",
metadata,
Column("id", Integer, primary_key=True),
Column("aid", ForeignKey("a.id"), nullable=True),
)
Table(
"c",
metadata,
Column("id", Integer, primary_key=True),
Column("aid", ForeignKey("a.id"), nullable=False),
)
Table(
"d",
metadata,
Column("id", Integer, primary_key=True),
Column("aid",
ForeignKey("a.id", ondelete="cascade"),
nullable=False),
)
Table(
"e",
metadata,
Column("id", Integer, primary_key=True),
Column("aid",
ForeignKey("a.id", ondelete="set null"),
nullable=True),
)
def test_collection_setstate(self):
"""test a particular cycle that requires CollectionAdapter
to not rely upon InstanceState to deserialize."""
m = MetaData()
c1 = Table(
"c1",
m,
Column("parent_id", String, ForeignKey("p.id"), primary_key=True),
)
c2 = Table(
"c2",
m,
Column("parent_id", String, ForeignKey("p.id"), primary_key=True),
)
p = Table("p", m, Column("id", String, primary_key=True))
mapper(
Parent,
p,
properties={
"children1": relationship(Child1),
"children2": relationship(Child2),
},
)
mapper(Child1, c1)
mapper(Child2, c2)
obj = Parent()
screen1 = Screen(obj)
screen1.errors = [obj.children1, obj.children2]
screen2 = Screen(Child2(), screen1)
pickle.loads(pickle.dumps(screen2))
def define_tables(cls, metadata):
Table(
"a",
metadata,
Column("id",
Integer,
primary_key=True,
test_needs_autoincrement=True),
Column("x", Integer),
Column("y", Integer),
)
Table(
"b",
metadata,
Column("id",
Integer,
primary_key=True,
test_needs_autoincrement=True),
Column("a_id", ForeignKey("a.id")),
Column("x", Integer),
Column("y", Integer),
)
Table(
"c",
metadata,
Column("id",
Integer,
primary_key=True,
test_needs_autoincrement=True),
Column("b_id", ForeignKey("b.id")),
Column("x", Integer),
Column("y", Integer),
)
def define_tables(cls, metadata):
Table(
"owners",
metadata,
Column("id",
Integer,
primary_key=True,
test_needs_autoincrement=True),
Column("data", String(30)),
)
Table(
"categories",
metadata,
Column("id",
Integer,
primary_key=True,
test_needs_autoincrement=True),
Column("name", String(20)),
)
Table(
"tests",
metadata,
Column("id",
Integer,
primary_key=True,
test_needs_autoincrement=True),
Column("owner_id",
Integer,
ForeignKey("owners.id"),
nullable=False),
Column(
"category_id",
Integer,
ForeignKey("categories.id"),
nullable=False,
),
)
Table(
"options",
metadata,
Column("test_id",
Integer,
ForeignKey("tests.id"),
primary_key=True),
Column("owner_id",
Integer,
ForeignKey("owners.id"),
primary_key=True),
Column(
"someoption",
sa.Boolean,
server_default=sa.false(),
nullable=False,
),
)
def test_cycle_named_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", use_alter=True, name="aidfk"),
),
)
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), "
"FOREIGN KEY(bid) REFERENCES b (id)"
")"),
),
CompiledSQL("ALTER TABLE b ADD CONSTRAINT aidfk "
"FOREIGN KEY(aid) REFERENCES a (id)"),
]
with self.sql_execution_asserter() as asserter:
metadata.create_all(checkfirst=False)
if testing.db.dialect.supports_alter:
asserter.assert_(*assertions)
with self.sql_execution_asserter() as asserter:
metadata.drop_all(checkfirst=False)
asserter.assert_(
CompiledSQL("ALTER TABLE b DROP CONSTRAINT aidfk"),
AllOf(CompiledSQL("DROP TABLE b"),
CompiledSQL("DROP TABLE a")),
)
else:
with self.sql_execution_asserter() as asserter:
metadata.drop_all(checkfirst=False)
asserter.assert_(
AllOf(CompiledSQL("DROP TABLE b"),
CompiledSQL("DROP TABLE a")))
def define_tables(cls, metadata):
global principals
global users
global groups
global user_group_map
principals = Table(
"principals",
metadata,
Column(
"principal_id",
Integer,
Sequence("principal_id_seq", optional=False),
primary_key=True,
),
Column("name", String(50), nullable=False),
)
users = Table(
"prin_users",
metadata,
Column(
"principal_id",
Integer,
ForeignKey("principals.principal_id"),
primary_key=True,
),
Column("password", String(50), nullable=False),
Column("email", String(50), nullable=False),
Column("login_id", String(50), nullable=False),
)
groups = Table(
"prin_groups",
metadata,
Column(
"principal_id",
Integer,
ForeignKey("principals.principal_id"),
primary_key=True,
),
)
user_group_map = Table(
"prin_user_group_map",
metadata,
Column(
"user_id",
Integer,
ForeignKey("prin_users.principal_id"),
primary_key=True,
),
Column(
"group_id",
Integer,
ForeignKey("prin_groups.principal_id"),
primary_key=True,
),
)
def test_alias_pathing(self):
metadata = MetaData(self.engine)
a = Table(
"a",
metadata,
Column("id",
Integer,
primary_key=True,
test_needs_autoincrement=True),
Column("bid", Integer, ForeignKey("b.id")),
Column("type", String(30)),
)
asub = Table(
"asub",
metadata,
Column("id", Integer, ForeignKey("a.id"), primary_key=True),
Column("data", String(30)),
)
b = Table(
"b",
metadata,
Column("id",
Integer,
primary_key=True,
test_needs_autoincrement=True),
)
mapper(A, a, polymorphic_identity="a", polymorphic_on=a.c.type)
mapper(ASub, asub, inherits=A, polymorphic_identity="asub")
mapper(B, b, properties={"as_": relationship(A)})
metadata.create_all()
sess = Session()
a1 = ASub(data="a1")
a2 = ASub(data="a2")
a3 = ASub(data="a3")
b1 = B(as_=[a1, a2, a3])
sess.add(b1)
sess.commit()
del sess
# sqlite has a slow enough growth here
# that we have to run it more times to see the
# "dip" again
@profile_memory(maxtimes=120)
def go():
sess = Session()
sess.query(B).options(subqueryload(B.as_.of_type(ASub))).all()
sess.close()
try:
go()
finally:
metadata.drop_all()
clear_mappers()
def define_tables(cls, metadata):
Table(
"design_types",
metadata,
Column(
"design_type_id",
Integer,
primary_key=True,
test_needs_autoincrement=True,
),
)
Table(
"design",
metadata,
Column(
"design_id",
Integer,
primary_key=True,
test_needs_autoincrement=True,
),
Column(
"design_type_id",
Integer,
ForeignKey("design_types.design_type_id"),
),
)
Table(
"parts",
metadata,
Column(
"part_id",
Integer,
primary_key=True,
test_needs_autoincrement=True,
),
Column("design_id", Integer, ForeignKey("design.design_id")),
Column(
"design_type_id",
Integer,
ForeignKey("design_types.design_type_id"),
),
)
Table(
"inherited_part",
metadata,
Column(
"ip_id",
Integer,
primary_key=True,
test_needs_autoincrement=True,
),
Column("part_id", Integer, ForeignKey("parts.part_id")),
Column("design_id", Integer, ForeignKey("design.design_id")),
)
class Job(ComparableEntity, Base):
__tablename__ = "job"
id = Column(Integer,
primary_key=True,
test_needs_autoincrement=True)
type = Column(String(10))
widget_id = Column(ForeignKey("widget.id"))
widget = relationship("Widget")
container_id = Column(Integer, ForeignKey("data_container.id"))
__mapper_args__ = {"polymorphic_on": type}
def define_tables(cls, metadata):
Table(
"companies",
metadata,
Column(
"company_id",
Integer,
primary_key=True,
test_needs_autoincrement=True,
),
Column("company_name", String(40)),
)
Table(
"addresses",
metadata,
Column(
"address_id",
Integer,
primary_key=True,
test_needs_autoincrement=True,
),
Column("company_id", Integer, ForeignKey("companies.company_id")),
Column("address", String(40)),
)
Table(
"phone_numbers",
metadata,
Column(
"phone_id",
Integer,
primary_key=True,
test_needs_autoincrement=True,
),
Column("address_id", Integer, ForeignKey("addresses.address_id")),
Column("type", String(20)),
Column("number", String(10)),
)
Table(
"invoices",
metadata,
Column(
"invoice_id",
Integer,
primary_key=True,
test_needs_autoincrement=True,
),
Column("company_id", Integer, ForeignKey("companies.company_id")),
Column("date", sa.DateTime),
)
class Grunt(Employee):
__tablename__ = "grunt"
id = Column(Integer, ForeignKey("employee.id"), primary_key=True)
savings = Column(Numeric)
employer_id = Column(Integer, ForeignKey("boss.id"))
employer = relationship("Boss",
backref="employees",
primaryjoin=Boss.id == employer_id)
__mapper_args__ = {"polymorphic_identity": "grunt"}
def define_tables(cls, metadata):
Table(
"t1",
metadata,
Column("id", Integer, primary_key=True),
Column("foo", Integer),
)
Table(
"t2",
metadata,
Column("id", Integer, ForeignKey("t1.id"), primary_key=True),
Column("t1id", Integer, ForeignKey("t1.id")),
)
def test_basic_reflection(self):
meta = self.metadata
users = Table(
"engine_users",
meta,
Column("user_id", types.INT, primary_key=True),
Column("user_name", types.VARCHAR(20), nullable=False),
Column("test1", types.CHAR(5), nullable=False),
Column("test2", types.Float(5), nullable=False),
Column("test2.5", types.Float(), nullable=False),
Column("test3", types.Text()),
Column("test4", types.Numeric, nullable=False),
Column("test4.5", types.Numeric(10, 2), nullable=False),
Column("test5", types.DateTime),
Column(
"parent_user_id",
types.Integer,
ForeignKey("engine_users.user_id"),
),
Column("test6", types.DateTime, nullable=False),
Column("test7", types.Text()),
Column("test8", types.LargeBinary()),
Column("test_passivedefault2", types.Integer, server_default="5"),
Column("test9", types.BINARY(100)),
Column("test_numeric", types.Numeric()),
)
addresses = Table(
"engine_email_addresses",
meta,
Column("address_id", types.Integer, primary_key=True),
Column("remote_user_id", types.Integer,
ForeignKey(users.c.user_id)),
Column("email_address", types.String(20)),
)
meta.create_all()
meta2 = MetaData()
reflected_users = Table("engine_users",
meta2,
autoload=True,
autoload_with=testing.db)
reflected_addresses = Table(
"engine_email_addresses",
meta2,
autoload=True,
autoload_with=testing.db,
)
self.assert_tables_equal(users, reflected_users)
self.assert_tables_equal(addresses, reflected_addresses)
def define_tables(cls, metadata):
Table(
"people",
metadata,
Column(
"person_id",
Integer,
primary_key=True,
test_needs_autoincrement=True,
),
Column("name", String(50)),
Column("type", String(30)),
)
Table(
"engineers",
metadata,
Column(
"person_id",
Integer,
ForeignKey("people.person_id"),
primary_key=True,
),
Column("status", String(30)),
Column("primary_language", String(50)),
)
Table(
"managers",
metadata,
Column(
"person_id",
Integer,
ForeignKey("people.person_id"),
primary_key=True,
),
Column("status", String(30)),
Column("manager_name", String(50)),
)
Table(
"boss",
metadata,
Column(
"boss_id",
Integer,
ForeignKey("managers.person_id"),
primary_key=True,
),
Column("golf_swing", String(30)),
)
def test_case_sensitive_reflection_dual_case_references(self):
# this tests that within the fix we do for MySQL bug
# 88718, we don't do case-insensitive logic if the backend
# is case sensitive
m = self.metadata
Table(
"t1",
m,
Column("some_id", Integer, primary_key=True),
mysql_engine="InnoDB",
)
Table(
"T1",
m,
Column("Some_Id", Integer, primary_key=True),
mysql_engine="InnoDB",
)
Table(
"t2",
m,
Column("id", Integer, primary_key=True),
Column("t1id", ForeignKey("t1.some_id", name="t1id_fk")),
Column("cap_t1id", ForeignKey("T1.Some_Id", name="cap_t1id_fk")),
mysql_engine="InnoDB",
)
m.create_all(testing.db)
eq_(
dict((rec["name"], rec)
for rec in inspect(testing.db).get_foreign_keys("t2")),
{
"cap_t1id_fk": {
"name": "cap_t1id_fk",
"constrained_columns": ["cap_t1id"],
"referred_schema": None,
"referred_table": "T1",
"referred_columns": ["Some_Id"],
"options": {},
},
"t1id_fk": {
"name": "t1id_fk",
"constrained_columns": ["t1id"],
"referred_schema": None,
"referred_table": "t1",
"referred_columns": ["some_id"],
"options": {},
},
},
)
def define_tables(cls, metadata):
Table(
"mytable",
metadata,
Column("myid", Integer),
Column("name", String(30)),
Column("description", String(50)),
)
Table(
"myothertable",
metadata,
Column("otherid", Integer),
Column("othername", String(30)),
)
Table(
"users",
metadata,
Column(
"id", Integer, primary_key=True, test_needs_autoincrement=True
),
Column("name", String(30), nullable=False),
)
Table(
"addresses",
metadata,
Column(
"id", Integer, primary_key=True, test_needs_autoincrement=True
),
Column("user_id", None, ForeignKey("users.id")),
Column("name", String(30), nullable=False),
Column("email_address", String(50), nullable=False),
)
Table(
"dingalings",
metadata,
Column(
"id", Integer, primary_key=True, test_needs_autoincrement=True
),
Column("address_id", None, ForeignKey("addresses.id")),
Column("data", String(30)),
)
Table(
"update_w_default",
metadata,
Column("id", Integer, primary_key=True),
Column("x", Integer),
Column("ycol", Integer, key="y"),
Column("data", String(30), onupdate=lambda: "hi"),
)
def test_misc_one(self):
metadata = MetaData(testing.db)
node_table = Table(
"node",
metadata,
Column("node_id", Integer, primary_key=True),
Column("name_index", Integer, nullable=True),
)
node_name_table = Table(
"node_name",
metadata,
Column("node_name_id", Integer, primary_key=True),
Column("node_id", Integer, ForeignKey("node.node_id")),
Column("host_id", Integer, ForeignKey("host.host_id")),
Column("name", String(64), nullable=False),
)
host_table = Table(
"host",
metadata,
Column("host_id", Integer, primary_key=True),
Column("hostname", String(64), nullable=False, unique=True),
)
metadata.create_all()
try:
node_table.insert().execute(node_id=1, node_index=5)
class Node(object):
pass
class NodeName(object):
pass
class Host(object):
pass
mapper(Node, node_table)
mapper(Host, host_table)
mapper(
NodeName,
node_name_table,
properties={
"node": relationship(Node, backref=backref("names")),
"host": relationship(Host),
},
)
sess = create_session()
assert sess.query(Node).get(1).names == []
finally:
metadata.drop_all()
def define_tables(cls, metadata):
Table(
"base",
metadata,
Column("id",
Integer,
primary_key=True,
test_needs_autoincrement=True),
)
Table(
"sub",
metadata,
Column("id",
Integer,
primary_key=True,
test_needs_autoincrement=True),
)
Table(
"base_mtom",
metadata,
Column("base_id", Integer, ForeignKey("base.id"),
primary_key=True),
Column(
"related_id",
Integer,
ForeignKey("related.id"),
primary_key=True,
),
)
Table(
"sub_mtom",
metadata,
Column("base_id", Integer, ForeignKey("sub.id"), primary_key=True),
Column(
"related_id",
Integer,
ForeignKey("related.id"),
primary_key=True,
),
)
Table(
"related",
metadata,
Column("id",
Integer,
primary_key=True,
test_needs_autoincrement=True),
)
def define_tables(cls, metadata):
Table(
"single",
metadata,
Column("id", Integer, primary_key=True),
Column("type", String(10)),
test_needs_fk=True,
)
Table(
"joined_base",
metadata,
Column("id", Integer, primary_key=True),
Column("type", String(10)),
test_needs_fk=True,
)
Table(
"joined_inh",
metadata,
Column("id",
Integer,
ForeignKey("joined_base.id"),
primary_key=True),
test_needs_fk=True,
)
FixtureTest.define_tables(metadata)
