On Feb 25, 2011, at 11:59 AM, Chris Withers wrote:
> Hi All,
>
> With these models:
>
> class User(Base):
> __tablename__ = 'user'
> __table_args__ = {'mysql_engine':'InnoDB'}
> username = Column(String(50), primary_key=True)
> grants = dynamic_loader("Grant")
>
> class Grant(Base):
>
> __tablename__ = 'grant'
> __table_args__ = {'mysql_engine':'InnoDB'}
> username = Column(String(50),
> ForeignKey('user.username',ondelete='cascade'),
> primary_key=True)
> user = relation("User",cascade="all")
>
> And an autoflush=True, autocommit=False, Session backed onto a MySQL
> database, if I do:
>
> user = User(username='testname',
> password='testpassword')
> session.add(user)
> session.add(Grant(user=user)))
> assert session.query(User).count()
>
> ...then no SQL is executed until the query, and at that point, the order of
> execution of the two insert statements appears random, such that sometimes
> the grant is inserted first, resulting in an IntegrityError as the foreign
> key constraint fails.
can't reproduce. See attached. This test includes randomization of all key
data structures in the UOW which smokes out any issues in dependency sorting.
>
> What am I doing wrong here and why is the order of the SQL emitted apparently
> arbitrary?
>
> Now, if it does succeed, and I go on to do:
>
> session.delete(session.query(User).get('testname'))
> assert session.query(User).count()==0
>
> When the count is executed, I get:
>
> assert session.query(User).count()==0
> File
> "/home/chris/buildout-eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/orm/query.py",
> line 2010, in count
> should_nest = should_nest[0]
> File
> "/home/chris/buildout-eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/orm/query.py",
> line 2045, in _col_aggregate
> self.session._autoflush()
> File
> "/home/chris/buildout-eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/orm/session.py",
> line 862, in _autoflush
> self.flush()
> File
> "/home/chris/buildout-eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/orm/session.py",
> line 1388, in flush
> self._flush(objects)
> File
> "/home/chris/buildout-eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/orm/session.py",
> line 1469, in _flush
> flush_context.execute()
> File
> "/home/chris/buildout-eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/orm/unitofwork.py",
> line 302, in execute
> rec.execute(self)
> File
> "/home/chris/buildout-eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/orm/unitofwork.py",
> line 402, in execute
> self.dependency_processor.process_deletes(uow, states)
> File
> "/home/chris/buildout-eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/orm/dependency.py",
> line 488, in process_deletes
> uowcommit, False)
> File
> "/home/chris/buildout-eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/orm/dependency.py",
> line 532, in _synchronize
> sync.clear(dest, self.mapper, self.prop.synchronize_pairs)
> File
> "/home/chris/buildout-eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/orm/sync.py",
> line 41, in clear
> (r, mapperutil.state_str(dest))
> AssertionError: Dependency rule tried to blank-out primary key column
> 'grant.username' on instance '<Grant at 0x9ed946c>'
>
> ...which is confusing. Why doesn't the cascade on the Grant.user relation
> stop this happening?
>
> cheers,
>
> Chris
>
> --
> Simplistix - Content Management, Batch Processing & Python Consulting
> - http://www.simplistix.co.uk
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
import random
class RandomSet(set):
def __iter__(self):
l = list(set.__iter__(self))
random.shuffle(l)
return iter(l)
def pop(self):
index = random.randint(0, len(self) - 1)
item = list(set.__iter__(self))[index]
self.remove(item)
return item
def union(self, other):
return RandomSet(set.union(self, other))
def difference(self, other):
return RandomSet(set.difference(self, other))
def intersection(self, other):
return RandomSet(set.intersection(self, other))
def copy(self):
return RandomSet(self)
def reverse_top():
from sqlalchemy.orm import unitofwork, session, mapper, dependency
from sqlalchemy import topological
topological.set = unitofwork.set = session.set = mapper.set = dependency.set = RandomSet
reverse_top()
Base = declarative_base()
class User(Base):
__tablename__ = 'user'
__table_args__ = {'mysql_engine':'InnoDB'}
username = Column(String(50), primary_key=True)
grants = dynamic_loader("Grant")
class Grant(Base):
__tablename__ = 'grant'
__table_args__ = {'mysql_engine':'InnoDB'}
username = Column(String(50),
ForeignKey('user.username',ondelete='cascade'),
primary_key=True)
user = relation("User",cascade="all")
e = create_engine('mysql://scott:tiger@localhost/test', echo=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)
session = Session(e)
user = User(username='testname')
session.add(user)
session.add(Grant(user=user))
assert session.query(User).count()
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
