def make_test(fetchtype):
def test_roundtrip(self):
class A(fixtures.ComparableEntity):
pass
class B(A):
pass
class C(B):
pass
if fetchtype == 'union':
abc = a.outerjoin(b).outerjoin(c)
bc = a.join(b).outerjoin(c)
else:
abc = bc = None
mapper(A,
a,
with_polymorphic=('*', abc),
polymorphic_on=a.c.type,
polymorphic_identity='a')
mapper(B,
b,
with_polymorphic=('*', bc),
inherits=A,
polymorphic_identity='b')
mapper(C, c, inherits=B, polymorphic_identity='c')
a1 = A(adata='a1')
b1 = B(bdata='b1', adata='b1')
b2 = B(bdata='b2', adata='b2')
b3 = B(bdata='b3', adata='b3')
c1 = C(cdata='c1', bdata='c1', adata='c1')
c2 = C(cdata='c2', bdata='c2', adata='c2')
c3 = C(cdata='c2', bdata='c2', adata='c2')
sess = create_session()
for x in (a1, b1, b2, b3, c1, c2, c3):
sess.add(x)
sess.flush()
sess.expunge_all()
#for obj in sess.query(A).all():
# print obj
eq_([
A(adata='a1'),
B(bdata='b1', adata='b1'),
B(bdata='b2', adata='b2'),
B(bdata='b3', adata='b3'),
C(cdata='c1', bdata='c1', adata='c1'),
C(cdata='c2', bdata='c2', adata='c2'),
C(cdata='c2', bdata='c2', adata='c2'),
],
sess.query(A).order_by(A.id).all())
eq_([
B(bdata='b1', adata='b1'),
B(bdata='b2', adata='b2'),
B(bdata='b3', adata='b3'),
C(cdata='c1', bdata='c1', adata='c1'),
C(cdata='c2', bdata='c2', adata='c2'),
C(cdata='c2', bdata='c2', adata='c2'),
],
sess.query(B).order_by(A.id).all())
eq_([
C(cdata='c1', bdata='c1', adata='c1'),
C(cdata='c2', bdata='c2', adata='c2'),
C(cdata='c2', bdata='c2', adata='c2'),
],
sess.query(C).order_by(A.id).all())
test_roundtrip = function_named(test_roundtrip, 'test_%s' % fetchtype)
return test_roundtrip
def _generate_round_trip_test(use_unions=False, use_joins=False):
def test_roundtrip(self):
mapper(Publication, self.tables.publication)
mapper(
Issue,
self.tables.issue,
properties={
"publication": relationship(
Publication,
backref=backref("issues", cascade="all, delete-orphan"),
)
},
)
mapper(LocationName, self.tables.location_name)
mapper(
Location,
self.tables.location,
properties={
"issue": relationship(
Issue,
backref=backref(
"locations",
lazy="joined",
cascade="all, delete-orphan",
),
),
"_name": relationship(LocationName),
},
)
mapper(PageSize, self.tables.page_size)
mapper(
Magazine,
self.tables.magazine,
properties={
"location": relationship(
Location, backref=backref("magazine", uselist=False)
),
"size": relationship(PageSize),
},
)
if use_unions:
page_join = polymorphic_union(
{
"m": self.tables.page.join(self.tables.magazine_page),
"c": self.tables.page.join(self.tables.magazine_page).join(
self.tables.classified_page
),
"p": self.tables.page.select(
self.tables.page.c.type == "p"
),
},
None,
"page_join",
)
page_mapper = mapper(
Page,
self.tables.page,
with_polymorphic=("*", page_join),
polymorphic_on=page_join.c.type,
polymorphic_identity="p",
)
elif use_joins:
page_join = self.tables.page.outerjoin(
self.tables.magazine_page
).outerjoin(self.tables.classified_page)
page_mapper = mapper(
Page,
self.tables.page,
with_polymorphic=("*", page_join),
polymorphic_on=self.tables.page.c.type,
polymorphic_identity="p",
)
else:
page_mapper = mapper(
Page,
self.tables.page,
polymorphic_on=self.tables.page.c.type,
polymorphic_identity="p",
)
if use_unions:
magazine_join = polymorphic_union(
{
"m": self.tables.page.join(self.tables.magazine_page),
"c": self.tables.page.join(self.tables.magazine_page).join(
self.tables.classified_page
),
},
None,
"page_join",
)
magazine_page_mapper = mapper(
MagazinePage,
self.tables.magazine_page,
with_polymorphic=("*", magazine_join),
inherits=page_mapper,
polymorphic_identity="m",
properties={
"magazine": relationship(
Magazine,
backref=backref(
"pages", order_by=magazine_join.c.page_no
),
)
},
)
elif use_joins:
magazine_join = self.tables.page.join(
self.tables.magazine_page
).outerjoin(self.tables.classified_page)
magazine_page_mapper = mapper(
MagazinePage,
self.tables.magazine_page,
with_polymorphic=("*", magazine_join),
inherits=page_mapper,
polymorphic_identity="m",
properties={
"magazine": relationship(
Magazine,
backref=backref(
"pages", order_by=self.tables.page.c.page_no
),
)
},
)
else:
magazine_page_mapper = mapper(
MagazinePage,
self.tables.magazine_page,
inherits=page_mapper,
polymorphic_identity="m",
properties={
"magazine": relationship(
Magazine,
backref=backref(
"pages", order_by=self.tables.page.c.page_no
),
)
},
)
mapper(
ClassifiedPage,
self.tables.classified_page,
inherits=magazine_page_mapper,
polymorphic_identity="c",
primary_key=[self.tables.page.c.id],
)
session = create_session()
pub = Publication(name="Test")
issue = Issue(issue=46, publication=pub)
location = Location(ref="ABC", name="London", issue=issue)
page_size = PageSize(name="A4", width=210, height=297)
magazine = Magazine(location=location, size=page_size)
page = ClassifiedPage(magazine=magazine, page_no=1)
page2 = MagazinePage(magazine=magazine, page_no=2)
page3 = ClassifiedPage(magazine=magazine, page_no=3)
session.add(pub)
session.flush()
print([x for x in session])
session.expunge_all()
session.flush()
session.expunge_all()
p = session.query(Publication).filter(Publication.name == "Test").one()
print(p.issues[0].locations[0].magazine.pages)
print([page, page2, page3])
assert repr(p.issues[0].locations[0].magazine.pages) == repr(
[page, page2, page3]
), repr(p.issues[0].locations[0].magazine.pages)
test_roundtrip = function_named(
test_roundtrip,
"test_%s"
% (not use_union and (use_joins and "joins" or "select") or "unions"),
)
setattr(MagazineTest, test_roundtrip.__name__, test_roundtrip)
def _generate_round_trip_test(
include_base, lazy_relationship, redefine_colprop, with_polymorphic
):
"""generates a round trip test.
include_base - whether or not to include the base 'person' type in
the union.
lazy_relationship - whether or not the Company relationship to
People is lazy or eager.
redefine_colprop - if we redefine the 'name' column to be
'people_name' on the base Person class
use_literal_join - primary join condition is explicitly specified
"""
def test_roundtrip(self):
if with_polymorphic == "unions":
if include_base:
person_join = polymorphic_union(
{
"engineer": people.join(engineers),
"manager": people.join(managers),
"person": people.select(people.c.type == "person"),
},
None,
"pjoin",
)
else:
person_join = polymorphic_union(
{
"engineer": people.join(engineers),
"manager": people.join(managers),
},
None,
"pjoin",
)
manager_join = people.join(managers).outerjoin(boss)
person_with_polymorphic = ["*", person_join]
manager_with_polymorphic = ["*", manager_join]
elif with_polymorphic == "joins":
person_join = (
people.outerjoin(engineers).outerjoin(managers).outerjoin(boss)
)
manager_join = people.join(managers).outerjoin(boss)
person_with_polymorphic = ["*", person_join]
manager_with_polymorphic = ["*", manager_join]
elif with_polymorphic == "auto":
person_with_polymorphic = "*"
manager_with_polymorphic = "*"
else:
person_with_polymorphic = None
manager_with_polymorphic = None
if redefine_colprop:
person_mapper = mapper(
Person,
people,
with_polymorphic=person_with_polymorphic,
polymorphic_on=people.c.type,
polymorphic_identity="person",
properties={"person_name": people.c.name},
)
else:
person_mapper = mapper(
Person,
people,
with_polymorphic=person_with_polymorphic,
polymorphic_on=people.c.type,
polymorphic_identity="person",
)
mapper(
Engineer,
engineers,
inherits=person_mapper,
polymorphic_identity="engineer",
)
mapper(
Manager,
managers,
inherits=person_mapper,
with_polymorphic=manager_with_polymorphic,
polymorphic_identity="manager",
)
mapper(Boss, boss, inherits=Manager, polymorphic_identity="boss")
mapper(
Company,
companies,
properties={
"employees": relationship(
Person,
lazy=lazy_relationship,
cascade="all, delete-orphan",
backref="company",
order_by=people.c.person_id,
)
},
)
if redefine_colprop:
person_attribute_name = "person_name"
else:
person_attribute_name = "name"
employees = [
Manager(
status="AAB",
manager_name="manager1",
**{person_attribute_name: "pointy haired boss"}
),
Engineer(
status="BBA",
engineer_name="engineer1",
primary_language="java",
**{person_attribute_name: "dilbert"}
),
]
if include_base:
employees.append(Person(**{person_attribute_name: "joesmith"}))
employees += [
Engineer(
status="CGG",
engineer_name="engineer2",
primary_language="python",
**{person_attribute_name: "wally"}
),
Manager(
status="ABA",
manager_name="manager2",
**{person_attribute_name: "jsmith"}
),
]
pointy = employees[0]
jsmith = employees[-1]
dilbert = employees[1]
session = create_session()
c = Company(name="company1")
c.employees = employees
session.add(c)
session.flush()
session.expunge_all()
eq_(session.query(Person).get(dilbert.person_id), dilbert)
session.expunge_all()
eq_(
session.query(Person)
.filter(Person.person_id == dilbert.person_id)
.one(),
dilbert,
)
session.expunge_all()
def go():
cc = session.query(Company).get(c.company_id)
eq_(cc.employees, employees)
if not lazy_relationship:
if with_polymorphic != "none":
self.assert_sql_count(testing.db, go, 1)
else:
self.assert_sql_count(testing.db, go, 5)
else:
if with_polymorphic != "none":
self.assert_sql_count(testing.db, go, 2)
else:
self.assert_sql_count(testing.db, go, 6)
# test selecting from the query, using the base
# mapped table (people) as the selection criterion.
# in the case of the polymorphic Person query,
# the "people" selectable should be adapted to be "person_join"
eq_(
session.query(Person)
.filter(getattr(Person, person_attribute_name) == "dilbert")
.first(),
dilbert,
)
assert (
session.query(Person)
.filter(getattr(Person, person_attribute_name) == "dilbert")
.first()
.person_id
)
eq_(
session.query(Engineer)
.filter(getattr(Person, person_attribute_name) == "dilbert")
.first(),
dilbert,
)
# test selecting from the query, joining against
# an alias of the base "people" table. test that
# the "palias" alias does *not* get sucked up
# into the "person_join" conversion.
palias = people.alias("palias")
dilbert = session.query(Person).get(dilbert.person_id)
is_(
dilbert,
session.query(Person)
.filter(
(palias.c.name == "dilbert")
& (palias.c.person_id == Person.person_id)
)
.first(),
)
is_(
dilbert,
session.query(Engineer)
.filter(
(palias.c.name == "dilbert")
& (palias.c.person_id == Person.person_id)
)
.first(),
)
is_(
dilbert,
session.query(Person)
.filter(
(Engineer.engineer_name == "engineer1")
& (engineers.c.person_id == people.c.person_id)
)
.first(),
)
is_(
dilbert,
session.query(Engineer).filter(
Engineer.engineer_name == "engineer1"
)[0],
)
session.flush()
session.expunge_all()
def go():
session.query(Person).filter(
getattr(Person, person_attribute_name) == "dilbert"
).first()
self.assert_sql_count(testing.db, go, 1)
session.expunge_all()
dilbert = (
session.query(Person)
.filter(getattr(Person, person_attribute_name) == "dilbert")
.first()
)
def go():
# assert that only primary table is queried for
# already-present-in-session
d = (
session.query(Person)
.filter(getattr(Person, person_attribute_name) == "dilbert")
.first()
)
self.assert_sql_count(testing.db, go, 1)
# test standalone orphans
daboss = Boss(
status="BBB",
manager_name="boss",
golf_swing="fore",
**{person_attribute_name: "daboss"}
)
session.add(daboss)
assert_raises(sa_exc.DBAPIError, session.flush)
c = session.query(Company).first()
daboss.company = c
manager_list = [e for e in c.employees if isinstance(e, Manager)]
session.flush()
session.expunge_all()
eq_(
session.query(Manager).order_by(Manager.person_id).all(),
manager_list,
)
c = session.query(Company).first()
session.delete(c)
session.flush()
eq_(select([func.count("*")]).select_from(people).scalar(), 0)
test_roundtrip = function_named(
test_roundtrip,
"test_%s%s%s_%s"
% (
(lazy_relationship and "lazy" or "eager"),
(include_base and "_inclbase" or ""),
(redefine_colprop and "_redefcol" or ""),
with_polymorphic,
),
)
setattr(RoundTripTest, test_roundtrip.__name__, test_roundtrip)
def _generate_round_trip_test(include_base, lazy_relationship,
redefine_colprop, with_polymorphic):
"""generates a round trip test.
include_base - whether or not to include the base 'person' type in
the union.
lazy_relationship - whether or not the Company relationship to
People is lazy or eager.
redefine_colprop - if we redefine the 'name' column to be
'people_name' on the base Person class
use_literal_join - primary join condition is explicitly specified
"""
def test_roundtrip(self):
if with_polymorphic == "unions":
if include_base:
person_join = polymorphic_union(
{
"engineer":
people.join(engineers),
"manager":
people.join(managers),
"person":
people.select(people.c.type == "person").subquery(),
},
None,
"pjoin",
)
else:
person_join = polymorphic_union(
{
"engineer": people.join(engineers),
"manager": people.join(managers),
},
None,
"pjoin",
)
manager_join = people.join(managers).outerjoin(boss)
person_with_polymorphic = ["*", person_join]
manager_with_polymorphic = ["*", manager_join]
elif with_polymorphic == "joins":
person_join = (people.outerjoin(engineers).outerjoin(
managers).outerjoin(boss))
manager_join = people.join(managers).outerjoin(boss)
person_with_polymorphic = ["*", person_join]
manager_with_polymorphic = ["*", manager_join]
elif with_polymorphic == "auto":
person_with_polymorphic = "*"
manager_with_polymorphic = "*"
else:
person_with_polymorphic = None
manager_with_polymorphic = None
if redefine_colprop:
person_mapper = mapper(
Person,
people,
with_polymorphic=person_with_polymorphic,
polymorphic_on=people.c.type,
polymorphic_identity="person",
properties={"person_name": people.c.name},
)
else:
person_mapper = mapper(
Person,
people,
with_polymorphic=person_with_polymorphic,
polymorphic_on=people.c.type,
polymorphic_identity="person",
)
mapper(
Engineer,
engineers,
inherits=person_mapper,
polymorphic_identity="engineer",
)
mapper(
Manager,
managers,
inherits=person_mapper,
with_polymorphic=manager_with_polymorphic,
polymorphic_identity="manager",
)
mapper(Boss, boss, inherits=Manager, polymorphic_identity="boss")
mapper(
Company,
companies,
properties={
"employees":
relationship(
Person,
lazy=lazy_relationship,
cascade="all, delete-orphan",
backref="company",
order_by=people.c.person_id,
)
},
)
if redefine_colprop:
person_attribute_name = "person_name"
else:
person_attribute_name = "name"
employees = [
Manager(status="AAB",
manager_name="manager1",
**{person_attribute_name: "pointy haired boss"}),
Engineer(status="BBA",
engineer_name="engineer1",
primary_language="java",
**{person_attribute_name: "dilbert"}),
]
if include_base:
employees.append(Person(**{person_attribute_name: "joesmith"}))
employees += [
Engineer(status="CGG",
engineer_name="engineer2",
primary_language="python",
**{person_attribute_name: "wally"}),
Manager(status="ABA",
manager_name="manager2",
**{person_attribute_name: "jsmith"}),
]
pointy = employees[0]
jsmith = employees[-1]
dilbert = employees[1]
session = create_session()
c = Company(name="company1")
c.employees = employees
session.add(c)
session.flush()
session.expunge_all()
eq_(session.query(Person).get(dilbert.person_id), dilbert)
session.expunge_all()
eq_(
session.query(Person).filter(
Person.person_id == dilbert.person_id).one(),
dilbert,
)
session.expunge_all()
def go():
cc = session.query(Company).get(c.company_id)
eq_(cc.employees, employees)
if not lazy_relationship:
if with_polymorphic != "none":
self.assert_sql_count(testing.db, go, 1)
else:
self.assert_sql_count(testing.db, go, 5)
else:
if with_polymorphic != "none":
self.assert_sql_count(testing.db, go, 2)
else:
self.assert_sql_count(testing.db, go, 6)
# test selecting from the query, using the base
# mapped table (people) as the selection criterion.
# in the case of the polymorphic Person query,
# the "people" selectable should be adapted to be "person_join"
eq_(
session.query(Person).filter(
getattr(Person, person_attribute_name) == "dilbert").first(),
dilbert,
)
assert (session.query(Person).filter(
getattr(Person, person_attribute_name) ==
"dilbert").first().person_id)
eq_(
session.query(Engineer).filter(
getattr(Person, person_attribute_name) == "dilbert").first(),
dilbert,
)
# test selecting from the query, joining against
# an alias of the base "people" table. test that
# the "palias" alias does *not* get sucked up
# into the "person_join" conversion.
palias = people.alias("palias")
dilbert = session.query(Person).get(dilbert.person_id)
is_(
dilbert,
session.query(Person).filter(
(palias.c.name == "dilbert")
& (palias.c.person_id == Person.person_id)).first(),
)
is_(
dilbert,
session.query(Engineer).filter(
(palias.c.name == "dilbert")
& (palias.c.person_id == Person.person_id)).first(),
)
is_(
dilbert,
session.query(Person).filter(
(Engineer.engineer_name == "engineer1")
& (engineers.c.person_id == people.c.person_id)).first(),
)
is_(
dilbert,
session.query(Engineer).filter(
Engineer.engineer_name == "engineer1")[0],
)
session.flush()
session.expunge_all()
def go():
session.query(Person).filter(
getattr(Person, person_attribute_name) == "dilbert").first()
self.assert_sql_count(testing.db, go, 1)
session.expunge_all()
dilbert = (session.query(Person).filter(
getattr(Person, person_attribute_name) == "dilbert").first())
def go():
# assert that only primary table is queried for
# already-present-in-session
d = (session.query(Person).filter(
getattr(Person, person_attribute_name) == "dilbert").first())
self.assert_sql_count(testing.db, go, 1)
# test standalone orphans
daboss = Boss(status="BBB",
manager_name="boss",
golf_swing="fore",
**{person_attribute_name: "daboss"})
session.add(daboss)
assert_raises(sa_exc.DBAPIError, session.flush)
c = session.query(Company).first()
daboss.company = c
manager_list = [e for e in c.employees if isinstance(e, Manager)]
session.flush()
session.expunge_all()
eq_(
session.query(Manager).order_by(Manager.person_id).all(),
manager_list,
)
c = session.query(Company).first()
session.delete(c)
session.flush()
eq_(select([func.count("*")]).select_from(people).scalar(), 0)
test_roundtrip = function_named(
test_roundtrip,
"test_%s%s%s_%s" % (
(lazy_relationship and "lazy" or "eager"),
(include_base and "_inclbase" or ""),
(redefine_colprop and "_redefcol" or ""),
with_polymorphic,
),
)
setattr(RoundTripTest, test_roundtrip.__name__, test_roundtrip)
def _generate_round_trip_test(use_unions=False, use_joins=False):
def test_roundtrip(self):
publication_mapper = mapper(Publication, publication_table)
issue_mapper = mapper(Issue, issue_table, properties = {
'publication': relationship(Publication, backref=backref('issues', cascade="all, delete-orphan")),
})
location_name_mapper = mapper(LocationName, location_name_table)
location_mapper = mapper(Location, location_table, properties = {
'issue': relationship(Issue, backref=backref('locations', lazy='joined', cascade="all, delete-orphan")),
'_name': relationship(LocationName),
})
page_size_mapper = mapper(PageSize, page_size_table)
magazine_mapper = mapper(Magazine, magazine_table, properties = {
'location': relationship(Location, backref=backref('magazine', uselist=False)),
'size': relationship(PageSize),
})
if use_unions:
page_join = polymorphic_union(
{
'm': page_table.join(magazine_page_table),
'c': page_table.join(magazine_page_table).join(classified_page_table),
'p': page_table.select(page_table.c.type=='p'),
}, None, 'page_join')
page_mapper = mapper(Page, page_table, with_polymorphic=('*', page_join), polymorphic_on=page_join.c.type, polymorphic_identity='p')
elif use_joins:
page_join = page_table.outerjoin(magazine_page_table).outerjoin(classified_page_table)
page_mapper = mapper(Page, page_table, with_polymorphic=('*', page_join), polymorphic_on=page_table.c.type, polymorphic_identity='p')
else:
page_mapper = mapper(Page, page_table, polymorphic_on=page_table.c.type, polymorphic_identity='p')
if use_unions:
magazine_join = polymorphic_union(
{
'm': page_table.join(magazine_page_table),
'c': page_table.join(magazine_page_table).join(classified_page_table),
}, None, 'page_join')
magazine_page_mapper = mapper(MagazinePage, magazine_page_table, with_polymorphic=('*', magazine_join), inherits=page_mapper, polymorphic_identity='m', properties={
'magazine': relationship(Magazine, backref=backref('pages', order_by=magazine_join.c.page_no))
})
elif use_joins:
magazine_join = page_table.join(magazine_page_table).outerjoin(classified_page_table)
magazine_page_mapper = mapper(MagazinePage, magazine_page_table, with_polymorphic=('*', magazine_join), inherits=page_mapper, polymorphic_identity='m', properties={
'magazine': relationship(Magazine, backref=backref('pages', order_by=page_table.c.page_no))
})
else:
magazine_page_mapper = mapper(MagazinePage, magazine_page_table, inherits=page_mapper, polymorphic_identity='m', properties={
'magazine': relationship(Magazine, backref=backref('pages', order_by=page_table.c.page_no))
})
classified_page_mapper = mapper(ClassifiedPage,
classified_page_table,
inherits=magazine_page_mapper,
polymorphic_identity='c',
primary_key=[page_table.c.id])
session = create_session()
pub = Publication(name='Test')
issue = Issue(issue=46,publication=pub)
location = Location(ref='ABC',name='London',issue=issue)
page_size = PageSize(name='A4',width=210,height=297)
magazine = Magazine(location=location,size=page_size)
page = ClassifiedPage(magazine=magazine,page_no=1)
page2 = MagazinePage(magazine=magazine,page_no=2)
page3 = ClassifiedPage(magazine=magazine,page_no=3)
session.add(pub)
session.flush()
print([x for x in session])
session.expunge_all()
session.flush()
session.expunge_all()
p = session.query(Publication).filter(Publication.name=="Test").one()
print(p.issues[0].locations[0].magazine.pages)
print([page, page2, page3])
assert repr(p.issues[0].locations[0].magazine.pages) == repr([page, page2, page3]), repr(p.issues[0].locations[0].magazine.pages)
test_roundtrip = function_named(
test_roundtrip, "test_%s" % (not use_union and (use_joins and "joins" or "select") or "unions"))
setattr(MagazineTest, test_roundtrip.__name__, test_roundtrip)
def _make_test(fetchtype):
def test_roundtrip(self):
class A(fixtures.ComparableEntity):
pass
class B(A):
pass
class C(B):
pass
if fetchtype == "union":
abc = a.outerjoin(b).outerjoin(c)
bc = a.join(b).outerjoin(c)
else:
abc = bc = None
mapper(
A,
a,
with_polymorphic=("*", abc),
polymorphic_on=a.c.type,
polymorphic_identity="a",
)
mapper(
B,
b,
with_polymorphic=("*", bc),
inherits=A,
polymorphic_identity="b",
)
mapper(C, c, inherits=B, polymorphic_identity="c")
a1 = A(adata="a1")
b1 = B(bdata="b1", adata="b1")
b2 = B(bdata="b2", adata="b2")
b3 = B(bdata="b3", adata="b3")
c1 = C(cdata="c1", bdata="c1", adata="c1")
c2 = C(cdata="c2", bdata="c2", adata="c2")
c3 = C(cdata="c2", bdata="c2", adata="c2")
sess = create_session()
for x in (a1, b1, b2, b3, c1, c2, c3):
sess.add(x)
sess.flush()
sess.expunge_all()
# for obj in sess.query(A).all():
# print obj
eq_(
[
A(adata="a1"),
B(bdata="b1", adata="b1"),
B(bdata="b2", adata="b2"),
B(bdata="b3", adata="b3"),
C(cdata="c1", bdata="c1", adata="c1"),
C(cdata="c2", bdata="c2", adata="c2"),
C(cdata="c2", bdata="c2", adata="c2"),
],
sess.query(A).order_by(A.id).all(),
)
eq_(
[
B(bdata="b1", adata="b1"),
B(bdata="b2", adata="b2"),
B(bdata="b3", adata="b3"),
C(cdata="c1", bdata="c1", adata="c1"),
C(cdata="c2", bdata="c2", adata="c2"),
C(cdata="c2", bdata="c2", adata="c2"),
],
sess.query(B).order_by(A.id).all(),
)
eq_(
[
C(cdata="c1", bdata="c1", adata="c1"),
C(cdata="c2", bdata="c2", adata="c2"),
C(cdata="c2", bdata="c2", adata="c2"),
],
sess.query(C).order_by(A.id).all(),
)
test_roundtrip = function_named(test_roundtrip, "test_%s" % fetchtype)
return test_roundtrip
def _generate_round_trip_test(use_unions=False, use_joins=False):
def test_roundtrip(self):
publication_mapper = mapper(Publication, self.tables.publication)
issue_mapper = mapper(
Issue,
self.tables.issue,
properties={
"publication": relationship(
Publication,
backref=backref("issues", cascade="all, delete-orphan"),
)
},
)
location_name_mapper = mapper(LocationName, self.tables.location_name)
location_mapper = mapper(
Location,
self.tables.location,
properties={
"issue": relationship(
Issue,
backref=backref(
"locations",
lazy="joined",
cascade="all, delete-orphan",
),
),
"_name": relationship(LocationName),
},
)
page_size_mapper = mapper(PageSize, self.tables.page_size)
magazine_mapper = mapper(
Magazine,
self.tables.magazine,
properties={
"location": relationship(
Location, backref=backref("magazine", uselist=False)
),
"size": relationship(PageSize),
},
)
if use_unions:
page_join = polymorphic_union(
{
"m": self.tables.page.join(self.tables.magazine_page),
"c": self.tables.page.join(self.tables.magazine_page).join(
self.tables.classified_page
),
"p": self.tables.page.select(
self.tables.page.c.type == "p"
).subquery(),
},
None,
"page_join",
)
page_mapper = mapper(
Page,
self.tables.page,
with_polymorphic=("*", page_join),
polymorphic_on=page_join.c.type,
polymorphic_identity="p",
)
elif use_joins:
page_join = self.tables.page.outerjoin(
self.tables.magazine_page
).outerjoin(self.tables.classified_page)
page_mapper = mapper(
Page,
self.tables.page,
with_polymorphic=("*", page_join),
polymorphic_on=self.tables.page.c.type,
polymorphic_identity="p",
)
else:
page_mapper = mapper(
Page,
self.tables.page,
polymorphic_on=self.tables.page.c.type,
polymorphic_identity="p",
)
if use_unions:
magazine_join = polymorphic_union(
{
"m": self.tables.page.join(self.tables.magazine_page),
"c": self.tables.page.join(self.tables.magazine_page).join(
self.tables.classified_page
),
},
None,
"page_join",
)
magazine_page_mapper = mapper(
MagazinePage,
self.tables.magazine_page,
with_polymorphic=("*", magazine_join),
inherits=page_mapper,
polymorphic_identity="m",
properties={
"magazine": relationship(
Magazine,
backref=backref(
"pages", order_by=magazine_join.c.page_no
),
)
},
)
elif use_joins:
magazine_join = self.tables.page.join(
self.tables.magazine_page
).outerjoin(self.tables.classified_page)
magazine_page_mapper = mapper(
MagazinePage,
self.tables.magazine_page,
with_polymorphic=("*", magazine_join),
inherits=page_mapper,
polymorphic_identity="m",
properties={
"magazine": relationship(
Magazine,
backref=backref(
"pages", order_by=self.tables.page.c.page_no
),
)
},
)
else:
magazine_page_mapper = mapper(
MagazinePage,
self.tables.magazine_page,
inherits=page_mapper,
polymorphic_identity="m",
properties={
"magazine": relationship(
Magazine,
backref=backref(
"pages", order_by=self.tables.page.c.page_no
),
)
},
)
classified_page_mapper = mapper(
ClassifiedPage,
self.tables.classified_page,
inherits=magazine_page_mapper,
polymorphic_identity="c",
primary_key=[self.tables.page.c.id],
)
session = create_session()
pub = Publication(name="Test")
issue = Issue(issue=46, publication=pub)
location = Location(ref="ABC", name="London", issue=issue)
page_size = PageSize(name="A4", width=210, height=297)
magazine = Magazine(location=location, size=page_size)
page = ClassifiedPage(magazine=magazine, page_no=1)
page2 = MagazinePage(magazine=magazine, page_no=2)
page3 = ClassifiedPage(magazine=magazine, page_no=3)
session.add(pub)
session.flush()
print([x for x in session])
session.expunge_all()
session.flush()
session.expunge_all()
p = session.query(Publication).filter(Publication.name == "Test").one()
print(p.issues[0].locations[0].magazine.pages)
print([page, page2, page3])
assert repr(p.issues[0].locations[0].magazine.pages) == repr(
[page, page2, page3]
), repr(p.issues[0].locations[0].magazine.pages)
test_roundtrip = function_named(
test_roundtrip,
"test_%s"
% (not use_union and (use_joins and "joins" or "select") or "unions"),
)
setattr(MagazineTest, test_roundtrip.__name__, test_roundtrip)
def _generate_round_trip_test(use_unions=False, use_joins=False):
def test_roundtrip(self):
publication_mapper = mapper(Publication, publication_table)
issue_mapper = mapper(
Issue,
issue_table,
properties={
'publication':
relationship(Publication,
backref=backref('issues',
cascade="all, delete-orphan"))
})
location_name_mapper = mapper(LocationName, location_name_table)
location_mapper = mapper(
Location,
location_table,
properties={
'issue':
relationship(Issue,
backref=backref('locations',
lazy='joined',
cascade="all, delete-orphan")),
'_name':
relationship(LocationName),
})
page_size_mapper = mapper(PageSize, page_size_table)
magazine_mapper = mapper(Magazine,
magazine_table,
properties={
'location':
relationship(Location,
backref=backref(
'magazine',
uselist=False)),
'size':
relationship(PageSize),
})
if use_unions:
page_join = polymorphic_union(
{
'm':
page_table.join(magazine_page_table),
'c':
page_table.join(magazine_page_table).join(
classified_page_table),
'p':
page_table.select(page_table.c.type == 'p'),
}, None, 'page_join')
page_mapper = mapper(Page,
page_table,
with_polymorphic=('*', page_join),
polymorphic_on=page_join.c.type,
polymorphic_identity='p')
elif use_joins:
page_join = page_table.outerjoin(magazine_page_table).outerjoin(
classified_page_table)
page_mapper = mapper(Page,
page_table,
with_polymorphic=('*', page_join),
polymorphic_on=page_table.c.type,
polymorphic_identity='p')
else:
page_mapper = mapper(Page,
page_table,
polymorphic_on=page_table.c.type,
polymorphic_identity='p')
if use_unions:
magazine_join = polymorphic_union(
{
'm':
page_table.join(magazine_page_table),
'c':
page_table.join(magazine_page_table).join(
classified_page_table),
}, None, 'page_join')
magazine_page_mapper = mapper(
MagazinePage,
magazine_page_table,
with_polymorphic=('*', magazine_join),
inherits=page_mapper,
polymorphic_identity='m',
properties={
'magazine':
relationship(
Magazine,
backref=backref('pages',
order_by=magazine_join.c.page_no))
})
elif use_joins:
magazine_join = page_table.join(magazine_page_table).outerjoin(
classified_page_table)
magazine_page_mapper = mapper(
MagazinePage,
magazine_page_table,
with_polymorphic=('*', magazine_join),
inherits=page_mapper,
polymorphic_identity='m',
properties={
'magazine':
relationship(Magazine,
backref=backref(
'pages', order_by=page_table.c.page_no))
})
else:
magazine_page_mapper = mapper(
MagazinePage,
magazine_page_table,
inherits=page_mapper,
polymorphic_identity='m',
properties={
'magazine':
relationship(Magazine,
backref=backref(
'pages', order_by=page_table.c.page_no))
})
classified_page_mapper = mapper(ClassifiedPage,
classified_page_table,
inherits=magazine_page_mapper,
polymorphic_identity='c',
primary_key=[page_table.c.id])
session = create_session()
pub = Publication(name='Test')
issue = Issue(issue=46, publication=pub)
location = Location(ref='ABC', name='London', issue=issue)
page_size = PageSize(name='A4', width=210, height=297)
magazine = Magazine(location=location, size=page_size)
page = ClassifiedPage(magazine=magazine, page_no=1)
page2 = MagazinePage(magazine=magazine, page_no=2)
page3 = ClassifiedPage(magazine=magazine, page_no=3)
session.add(pub)
session.flush()
print([x for x in session])
session.expunge_all()
session.flush()
session.expunge_all()
p = session.query(Publication).filter(Publication.name == "Test").one()
print(p.issues[0].locations[0].magazine.pages)
print([page, page2, page3])
assert repr(p.issues[0].locations[0].magazine.pages) == repr(
[page, page2,
page3]), repr(p.issues[0].locations[0].magazine.pages)
test_roundtrip = function_named(
test_roundtrip, "test_%s" %
(not use_union and (use_joins and "joins" or "select") or "unions"))
setattr(MagazineTest, test_roundtrip.__name__, test_roundtrip)
def _generate_round_trip_test(include_base, lazy_relationship,
redefine_colprop, with_polymorphic):
"""generates a round trip test.
include_base - whether or not to include the base 'person' type in
the union.
lazy_relationship - whether or not the Company relationship to
People is lazy or eager.
redefine_colprop - if we redefine the 'name' column to be
'people_name' on the base Person class
use_literal_join - primary join condition is explicitly specified
"""
def test_roundtrip(self):
if with_polymorphic == 'unions':
if include_base:
person_join = polymorphic_union(
{
'engineer':people.join(engineers),
'manager':people.join(managers),
'person':people.select(people.c.type=='person'),
}, None, 'pjoin')
else:
person_join = polymorphic_union(
{
'engineer':people.join(engineers),
'manager':people.join(managers),
}, None, 'pjoin')
manager_join = people.join(managers).outerjoin(boss)
person_with_polymorphic = ['*', person_join]
manager_with_polymorphic = ['*', manager_join]
elif with_polymorphic == 'joins':
person_join = people.outerjoin(engineers).outerjoin(managers).\
outerjoin(boss)
manager_join = people.join(managers).outerjoin(boss)
person_with_polymorphic = ['*', person_join]
manager_with_polymorphic = ['*', manager_join]
elif with_polymorphic == 'auto':
person_with_polymorphic = '*'
manager_with_polymorphic = '*'
else:
person_with_polymorphic = None
manager_with_polymorphic = None
if redefine_colprop:
person_mapper = mapper(Person, people,
with_polymorphic=person_with_polymorphic,
polymorphic_on=people.c.type,
polymorphic_identity='person',
properties= {'person_name':people.c.name})
else:
person_mapper = mapper(Person, people,
with_polymorphic=person_with_polymorphic,
polymorphic_on=people.c.type,
polymorphic_identity='person')
mapper(Engineer, engineers, inherits=person_mapper,
polymorphic_identity='engineer')
mapper(Manager, managers, inherits=person_mapper,
with_polymorphic=manager_with_polymorphic,
polymorphic_identity='manager')
mapper(Boss, boss, inherits=Manager, polymorphic_identity='boss')
mapper(Company, companies, properties={
'employees': relationship(Person, lazy=lazy_relationship,
cascade="all, delete-orphan",
backref="company", order_by=people.c.person_id
)
})
if redefine_colprop:
person_attribute_name = 'person_name'
else:
person_attribute_name = 'name'
employees = [
Manager(status='AAB', manager_name='manager1',
**{person_attribute_name:'pointy haired boss'}),
Engineer(status='BBA', engineer_name='engineer1',
primary_language='java',
**{person_attribute_name:'dilbert'}),
]
if include_base:
employees.append(Person(**{person_attribute_name:'joesmith'}))
employees += [
Engineer(status='CGG', engineer_name='engineer2',
primary_language='python',
**{person_attribute_name:'wally'}),
Manager(status='ABA', manager_name='manager2',
**{person_attribute_name:'jsmith'})
]
pointy = employees[0]
jsmith = employees[-1]
dilbert = employees[1]
session = create_session()
c = Company(name='company1')
c.employees = employees
session.add(c)
session.flush()
session.expunge_all()
eq_(session.query(Person).get(dilbert.person_id), dilbert)
session.expunge_all()
eq_(session.query(Person).filter(
Person.person_id==dilbert.person_id).one(),
dilbert)
session.expunge_all()
def go():
cc = session.query(Company).get(c.company_id)
eq_(cc.employees, employees)
if not lazy_relationship:
if with_polymorphic != 'none':
self.assert_sql_count(testing.db, go, 1)
else:
self.assert_sql_count(testing.db, go, 5)
else:
if with_polymorphic != 'none':
self.assert_sql_count(testing.db, go, 2)
else:
self.assert_sql_count(testing.db, go, 6)
# test selecting from the query, using the base
# mapped table (people) as the selection criterion.
# in the case of the polymorphic Person query,
# the "people" selectable should be adapted to be "person_join"
eq_(
session.query(Person).filter(
getattr(Person, person_attribute_name)=='dilbert'
).first(),
dilbert
)
assert session.query(Person).filter(
getattr(Person, person_attribute_name)=='dilbert'
).first().person_id
eq_(
session.query(Engineer).filter(
getattr(Person, person_attribute_name)=='dilbert'
).first(),
dilbert
)
# test selecting from the query, joining against
# an alias of the base "people" table. test that
# the "palias" alias does *not* get sucked up
# into the "person_join" conversion.
palias = people.alias("palias")
dilbert = session.query(Person).get(dilbert.person_id)
assert dilbert is session.query(Person).filter(
(palias.c.name=='dilbert') & \
(palias.c.person_id==Person.person_id)).first()
assert dilbert is session.query(Engineer).filter(
(palias.c.name=='dilbert') & \
(palias.c.person_id==Person.person_id)).first()
assert dilbert is session.query(Person).filter(
(Engineer.engineer_name=="engineer1") & \
(engineers.c.person_id==people.c.person_id)
).first()
assert dilbert is session.query(Engineer).\
filter(Engineer.engineer_name=="engineer1")[0]
session.flush()
session.expunge_all()
def go():
session.query(Person).filter(getattr(Person,
person_attribute_name)=='dilbert').first()
self.assert_sql_count(testing.db, go, 1)
session.expunge_all()
dilbert = session.query(Person).filter(getattr(Person,
person_attribute_name)=='dilbert').first()
def go():
# assert that only primary table is queried for
# already-present-in-session
d = session.query(Person).filter(getattr(Person,
person_attribute_name)=='dilbert').first()
self.assert_sql_count(testing.db, go, 1)
# test standalone orphans
daboss = Boss(status='BBB',
manager_name='boss',
golf_swing='fore',
**{person_attribute_name:'daboss'})
session.add(daboss)
assert_raises(sa_exc.DBAPIError, session.flush)
c = session.query(Company).first()
daboss.company = c
manager_list = [e for e in c.employees
if isinstance(e, Manager)]
session.flush()
session.expunge_all()
eq_(session.query(Manager).order_by(Manager.person_id).all(),
manager_list)
c = session.query(Company).first()
session.delete(c)
session.flush()
eq_(people.count().scalar(), 0)
test_roundtrip = function_named(
test_roundtrip, "test_%s%s%s_%s" % (
(lazy_relationship and "lazy" or "eager"),
(include_base and "_inclbase" or ""),
(redefine_colprop and "_redefcol" or ""),
with_polymorphic))
setattr(RoundTripTest, test_roundtrip.__name__, test_roundtrip)
def make_test(fetchtype):
def test_roundtrip(self):
class A(fixtures.ComparableEntity):pass
class B(A):pass
class C(B):pass
if fetchtype == 'union':
abc = a.outerjoin(b).outerjoin(c)
bc = a.join(b).outerjoin(c)
else:
abc = bc = None
mapper(A, a, with_polymorphic=('*', abc), polymorphic_on=a.c.type, polymorphic_identity='a')
mapper(B, b, with_polymorphic=('*', bc), inherits=A, polymorphic_identity='b')
mapper(C, c, inherits=B, polymorphic_identity='c')
a1 = A(adata='a1')
b1 = B(bdata='b1', adata='b1')
b2 = B(bdata='b2', adata='b2')
b3 = B(bdata='b3', adata='b3')
c1 = C(cdata='c1', bdata='c1', adata='c1')
c2 = C(cdata='c2', bdata='c2', adata='c2')
c3 = C(cdata='c2', bdata='c2', adata='c2')
sess = create_session()
for x in (a1, b1, b2, b3, c1, c2, c3):
sess.add(x)
sess.flush()
sess.expunge_all()
#for obj in sess.query(A).all():
# print obj
eq_(
[
A(adata='a1'),
B(bdata='b1', adata='b1'),
B(bdata='b2', adata='b2'),
B(bdata='b3', adata='b3'),
C(cdata='c1', bdata='c1', adata='c1'),
C(cdata='c2', bdata='c2', adata='c2'),
C(cdata='c2', bdata='c2', adata='c2'),
], sess.query(A).order_by(A.id).all())
eq_([
B(bdata='b1', adata='b1'),
B(bdata='b2', adata='b2'),
B(bdata='b3', adata='b3'),
C(cdata='c1', bdata='c1', adata='c1'),
C(cdata='c2', bdata='c2', adata='c2'),
C(cdata='c2', bdata='c2', adata='c2'),
], sess.query(B).order_by(A.id).all())
eq_([
C(cdata='c1', bdata='c1', adata='c1'),
C(cdata='c2', bdata='c2', adata='c2'),
C(cdata='c2', bdata='c2', adata='c2'),
], sess.query(C).order_by(A.id).all())
test_roundtrip = function_named(
test_roundtrip, 'test_%s' % fetchtype)
return test_roundtrip
page_size = PageSize(name='A4',width=210,height=297)
magazine = Magazine(location=location,size=page_size)
page = ClassifiedPage(magazine=magazine,page_no=1)
page2 = MagazinePage(magazine=magazine,page_no=2)
page3 = ClassifiedPage(magazine=magazine,page_no=3)
session.add(pub)
session.flush()
print [x for x in session]
session.expunge_all()
session.flush()
session.expunge_all()
p = session.query(Publication).filter(Publication.name=="Test").one()
print p.issues[0].locations[0].magazine.pages
print [page, page2, page3]
assert repr(p.issues[0].locations[0].magazine.pages) == repr([page, page2, page3]), repr(p.issues[0].locations[0].magazine.pages)
test_roundtrip = function_named(
test_roundtrip, "test_%s" % (not use_union and (use_joins and "joins" or "select") or "unions"))
setattr(MagazineTest, test_roundtrip.__name__, test_roundtrip)
for (use_union, use_join) in [(True, False), (False, True), (False, False)]:
_generate_round_trip_test(use_union, use_join)
