Source code for sqlobject.tests.test_sqlbuilder_joins_instances

from sqlobject import AND, ForeignKey, SQLMultipleJoin, SQLObject, \
    SQLRelatedJoin, StringCol
from sqlobject.tests.dbtest import inserts, setupClass

# Testing for expressing join, foreign keys,
# and instance identity in SQLBuilder expressions.


[docs]class SBPerson(SQLObject): name = StringCol() addresses = SQLMultipleJoin('SBAddress', joinColumn='personID') sharedAddresses = SQLRelatedJoin('SBAddress', addRemoveName='SharedAddress')
[docs]class SBAddress(SQLObject): city = StringCol() person = ForeignKey('SBPerson') sharedPeople = SQLRelatedJoin('SBPerson')
[docs]def setup_module(mod): global ppl setupClass([SBPerson, SBAddress]) ppl = inserts(SBPerson, [('James',), ('Julia',)], 'name') adds = inserts(SBAddress, [('London', ppl[0].id), ('Chicago', ppl[1].id), ('Abu Dhabi', ppl[1].id)], 'city personID') ppl[0].addSharedAddress(adds[0]) ppl[0].addSharedAddress(adds[1]) ppl[1].addSharedAddress(adds[0])
[docs]def testJoin(): assert list(SBPerson.select( AND(SBPerson.q.id == SBAddress.q.personID, SBAddress.q.city == 'London'))) == \ list(SBAddress.selectBy(city='London').throughTo.person) assert list(SBAddress.select( AND(SBPerson.q.id == SBAddress.q.personID, SBPerson.q.name == 'Julia')).orderBy(SBAddress.q.city)) == \ list(SBPerson.selectBy(name='Julia'). throughTo.addresses.orderBy(SBAddress.q.city))
[docs]def testRelatedJoin(): assert list(SBPerson.selectBy(name='Julia').throughTo.sharedAddresses) == \ list(ppl[1].sharedAddresses)
[docs]def testInstance(): assert list(SBAddress.select( AND(SBPerson.q.id == SBAddress.q.personID, SBPerson.q.id == ppl[0].id) )) == list(ppl[0].addresses)
[docs]def testFK(): assert list(SBPerson.select( AND(SBAddress.j.person, SBAddress.q.city == 'London'))) == \ list(SBPerson.select( AND(SBPerson.q.id == SBAddress.q.personID, SBAddress.q.city == 'London')))
[docs]def testRelatedJoin2(): assert list(SBAddress.select( AND(SBAddress.j.sharedPeople, SBPerson.q.name == 'Julia'))) == \ list(SBPerson.select( SBPerson.q.name == 'Julia').throughTo.sharedAddresses)
[docs]def testJoin2(): assert list(SBAddress.select( AND(SBPerson.j.addresses, SBPerson.q.name == 'Julia')).orderBy(SBAddress.q.city)) == \ list(SBAddress.select( AND(SBPerson.q.id == SBAddress.q.personID, SBPerson.q.name == 'Julia')).orderBy(SBAddress.q.city)) == \ list(SBPerson.selectBy(name='Julia').throughTo. addresses.orderBy(SBAddress.q.city))
[docs]def testFK2(): assert list(SBAddress.select( AND(SBAddress.j.person, SBPerson.q.name == 'Julia'))) == \ list(SBAddress.select( AND(SBPerson.q.id == SBAddress.q.personID, SBPerson.q.name == 'Julia')))