I've been thinking that I just didn't understand SQLAlchemy, but I now think I might be running into a nasty bug. I've generated two simple tests (attached) that show the problem (one passes; the other fails). Their only difference is in their schemas. I think that the difference should be irrelevant to the tests, but it's not.
I define two tables, 'people' and 'email_addresses'. Here's the schema for the test that passes:
self.people_table = Table('people', self.metadata,
Column('first_name', String, primary_key=True),
Column('last_name', String, primary_key=True))
self.email_addresses_table = Table('email_addresses', self.metadata,
Column('person_first_name', String),
Column('person_last_name', String),
Column('email_address', String, primary_key=True),
ForeignKeyConstraint(['person_first_name', 'person_last_name'],
['people.first_name', 'people.last_name' ]))
The actual test creates two people, one with an address and one without. It then moves the address from one person to the other, and verifies that it was moved. For the above schema, this works. Here's what SQLAlchemy does when the address is switched:
[2006-09-08 16:39:26,644] [engine]: BEGIN
[2006-09-08 16:39:26,682] [engine]: UPDATE email_addresses SET person_first_name=%(person_first_name)s WHERE email_addresses.email_address = %(email_addresses_email_address)s
[2006-09-08 16:39:26,684] [engine]: {'email_addresses_email_address': '[EMAIL PROTECTED]', 'person_first_name': 'jane'}
[2006-09-08 16:39:26,688] [engine]: COMMIT
Now, here's the modified email_addresses table that makes it fail:
self.email_addresses_table = Table('email_addresses', self.metadata,
Column('person_first_name', String, primary_key=True),
Column('person_last_name', String, primary_key=True),
Column('email_address', String, primary_key=True),
ForeignKeyConstraint(['person_first_name', 'person_last_name'],
[' people.first_name', 'people.last_name']))
The only difference here is that the primary key is now composed of (first_name, last_name, email_address). This doesn't really make sense semantically, but it illustrates the problem. With this schema, and the same test as before, the email address never actually gets moved from one user to the other:
[2006-09-08 16:39:26,910] [engine]: BEGIN
[2006-09-08 16:39:26,912] [engine]: COMMIT
This is all with SQLAlchemy 0.2.8. Is this expected behavior, or a known bug, or am I just lucky?
import unittest from sqlalchemy import *
class Person(object):
def __init__(self, first_name, last_name):
self.first_name, self.last_name = first_name, last_name
class Email(object):
def __init__(self, email_address):
self.email_address= email_address
class FKTestBase(unittest.TestCase):
"""
Base class for the tests: contains the setup (except the tables) and the
actual test.
"""
def setUp(self):
self.engine = create_engine('postgres://[EMAIL PROTECTED]/bitbacker')
self.metadata = BoundMetaData(self.engine)
self.people_table = Table('people', self.metadata,
Column('first_name', String, primary_key=True),
Column('last_name', String, primary_key=True))
self.define_email_table()
# Mappers
clear_mappers()
mapper(Email, self.email_addresses_table)
mapper(Person, self.people_table, properties=dict(email_addresses=relation(Email)))
# Create the tables
self.metadata.create_all()
self.session = create_session(bind_to=self.engine)
def tearDown(self):
self.metadata.drop_all()
def runTest(self):
# Create john and give him an address
john = Person('john', 'doe')
self.session.save(john)
john.email_addresses.append(Email('[EMAIL PROTECTED]'))
self.session.flush()
# Create jane with no addresses
jane = Person('jane', 'doe')
self.session.save(jane)
self.session.flush()
# Move the address from john to jane
email = john.email_addresses[0]
john.email_addresses.remove(email)
jane.email_addresses.append(email)
print
self.engine.echo = True
self.session.flush()
self.engine.echo = False
# Make sure that john has 0 addresses and jane has one
self.session.refresh(john)
self.session.refresh(jane)
self.assertEquals(len(john.email_addresses), 0)
self.assertEquals(len(jane.email_addresses), 1)
class FKTestPasses(FKTestBase):
def define_email_table(self):
self.email_addresses_table = Table('email_addresses', self.metadata,
Column('person_first_name', String),
Column('person_last_name', String),
Column('email_address', String, primary_key=True),
ForeignKeyConstraint(['person_first_name', 'person_last_name'],
['people.first_name', 'people.last_name']))
class FKTestFails(FKTestBase):
def define_email_table(self):
self.email_addresses_table = Table('email_addresses', self.metadata,
Column('person_first_name', String, primary_key=True),
Column('person_last_name', String, primary_key=True),
Column('email_address', String, primary_key=True),
ForeignKeyConstraint(['person_first_name', 'person_last_name'],
['people.first_name', 'people.last_name']))
if __name__ == '__main__':
suite = unittest.TestSuite()
suite.addTests([FKTestPasses(), FKTestFails()])
unittest.TextTestRunner(verbosity=2).run(suite)
------------------------------------------------------------------------- Using Tomcat but need to do more? Need to support web services, security? Get stuff done quickly with pre-integrated technology to make your job easier Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
_______________________________________________ Sqlalchemy-users mailing list Sqlalchemy-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
