Hi,
After some more internal discussions we tried and realized that the version
col is not really required at the child table level.
The second commit in the sample code fails because the version value on the
parent row has already moved ahead.
Please confirm if the understanding/conclusion is correct.
Regards,
Naresh
On Mon, Nov 29, 2010 at 2:21 PM, Naresh <nkhal...@vayana.in> wrote:
> Hi,
>
> We have a parent class/table (Notification/notifications) and a child
> class/table(BusinessNotification/business_notifications).
> Both of them have a version_id_col defined.
> We are facing ConcurrentModificationException errors for the model at
> the end of the mail.
> The problem is because the update state generated uses 'None' a the
> value for notifications.version column.
>
> We tried removing the version_id_col mapper arg in the child class -
> BusinessNotification and then the error went away and the update
> statement used the correct version column value to update.
>
> So, firstly we are wondering how do we fix the situation that we can
> have a version_id_col in the child class/table as well?
>
> Upon further investigations, we noticed that in the other parts of
> the application we used the following approach.
>
> parent_table = Table( ... )
> class Parent(DeclarativeBase):
> __table__ = parent_table
> __mapper_args__ = {'polymorphic_on': parent_table.c.type,
> 'version_id_col': parent_table.c.version}
>
> child1_table = Table( ... )
> class Child1(DeclarativeBase)
> __table__ = child1_table
> __mapper_args__ = {'polymorphic_identity':'c1',
> 'version_id_col':child1_table.c.version_num}
>
> With the above style of coding our models, we don't get
> ConcurrentModificationException, but any update statement to the
> parent_table does not use version column.
>
>
> Please throw some light on these behaviors.
>
> Regards,
> Naresh
>
> P.S. The model and sample client code follows.
> ------------------
>
> class Notification(DeclarativeBase):
> """This entity represents the notifications saved for sending
> later """
> __tablename__ = 'notifications'
> __table_args__ = {"mysql_engine":"InnoDB",
> "mysql_row_format":"DYNAMIC",
> "mysql_charset":"utf8"}
>
> id = Column(bigint, primary_key=True, autoincrement=False)
> entid = Column(bigint, ForeignKey('enterprises.entid',
> name='fk_notifn_entid'), nullable=False)
> notification_type = Column(types.String(30), nullable=False)
> source = Column(types.String(30), nullable=False)
> data = Column(text_type, nullable=False)
> status = Column(types.String(12), nullable=False, default='new')
> created_on = Column(types.DATETIME, nullable=False,
> default=func.current_timestamp())
> last_updated = Column(types.DATETIME, nullable=True,
> onupdate=func.current_timestamp())
> version = Column(types.SMALLINT, nullable=False, default=0)
> __mapper_args__ = {'polymorphic_on': notification_type,
> 'polymorphic_identity':'incident', 'version_id_col': version}
>
>
> class BusinessNotification(Notification):
> """This entity represents the business notifications """
> __tablename__ = 'business_notifications'
> __table_args__ = {"mysql_engine":"InnoDB",
> "mysql_row_format":"DYNAMIC",
> "mysql_charset":"utf8"}
>
> notification_id = Column(bigint, ForeignKey('notifications.id',
> name='fk_bn_notificationid'), primary_key=True, autoincrement=False)
> event_id = Column(bigint, ForeignKey('doc_events.id',
> name='fk_bn_eventid'), nullable=True)
> created_on = Column(types.DATETIME, nullable=False,
> default=func.current_timestamp())
> last_updated = Column(types.DATETIME, nullable=True,
> onupdate=func.current_timestamp())
> version_num = Column(types.SMALLINT, nullable=False, default=0)
> __mapper_args__ = {'polymorphic_identity':'event',
> 'version_id_col': version_num}
> # __mapper_args__ = {'polymorphic_identity':'event'}
>
>
> if __name__ == '__main__':
> try :
> qry = meta.Session().query(BusinessNotification).filter(and_(
>
> BusinessNotification.status=='new',
>
> BusinessNotification.created_on < now()))
> qry = qry.order_by(BusinessNotification.created_on)
> all_notifications = qry.all()
> for notification in all_notifications :
> # some business logic to trigger status update
> notification.status='in_progress'
> meta.Session().commit()
> except Exception as ex :
> log.error("Error Identifying Notification Recipients")
> log.exception(ex)
> meta.Session().rollback()
> finally :
> meta.Session.remove()
>
--
Naresh Khalasi | Vayana Services (http://www.vayana.in) | +91-9892294598
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@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 create_engine, MetaData, Column, types, func, ForeignKey, orm
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects import mysql
import datetime
bigint = mysql.BIGINT(unsigned=True)
dbengine = create_engine("mysql://testuser:test...@localhost/testdb", echo=True)
Session = orm.scoped_session(orm.sessionmaker(bind=dbengine, autoflush=True, autocommit=False, expire_on_commit=True))
Session2 = orm.scoped_session(orm.sessionmaker(bind=dbengine, autoflush=True, autocommit=False, expire_on_commit=True))
# Global metadata. If you have multiple databases with overlapping table
# names, you'll need a metadata for each database
metadata = MetaData()
metadata.bind = dbengine
class Base(object):
__table_args__ = {"mysql_engine":"InnoDB",
"mysql_row_format":"DYNAMIC",
"mysql_charset":"utf8"}
def __repr__(self):
L = []
for k in self.__class__.__table__.c.keys():
value = getattr(self, k, '')
L.append("%s=%r" % (k, value))
return '%s(%s)' % (self.__class__.__name__, ','.join(L))
# Assign the same metadata object we created earlier
DeclarativeBase = declarative_base(cls=Base, metadata=metadata)
class Parent(DeclarativeBase):
__tablename__ = 'parents'
id = Column(bigint, primary_key=True)
otype = Column(types.CHAR(10), nullable=False)
status = Column(types.CHAR(20), nullable=False)
created_on = Column(types.DATETIME, nullable=False, default=func.current_timestamp())
last_updated = Column(types.DATETIME, nullable=True, onupdate=func.current_timestamp())
version = Column(types.SMALLINT, nullable=False, default=0)
__mapper_args__ = {'polymorphic_on': otype, 'polymorphic_identity':'parent', 'version_id_col': version}
class Child1(Parent):
__tablename__ = 'c1'
cid = Column(bigint, ForeignKey('parents.id', name='fk_c1parents_id'), primary_key=True, autoincrement=False)
desc = Column(types.Unicode(100), nullable=True)
__mapper_args__ = {'polymorphic_identity':'c1'}
class Child2(Parent):
__tablename__ = 'c2'
cid = Column(bigint, ForeignKey('parents.id', name='fk_c2parents_id'), primary_key=True, autoincrement=False)
desc = Column(types.Unicode(100), nullable=True)
__mapper_args__ = {'polymorphic_identity':'c2'}
print '--------------------------------'
metadata.drop_all()
print '--------------------------------'
metadata.create_all()
print '--------------------------------'
if __name__ == '__main__':
dbsession = Session()
c11 = Child1()
c11.status = 'new'
dbsession.add(c11)
c12 = Child1()
c12.status = 'new'
dbsession.add(c12)
c21 = Child2()
c21.status = 'new'
dbsession.add(c21)
dbsession.commit()
Session.remove()
dbsession = Session()
dbs2 = Session2()
query = dbsession.query(Child1)
all_c1 = query.all()
for c1 in all_c1:
c1.desc = 'Update at %s' %(datetime.datetime.now())
query2 = dbs2.query(Child1)
all_c12 = query2.all()
for c13 in all_c12:
c13.desc = 'Update2 at %s' %(datetime.datetime.now())
print "============================= Initiating Commit 1 ==================================="
dbsession.commit()
print "============================= Completed Commit 1 ==================================="
print "============================= Initiating Commit 2 ==================================="
dbs2.commit()
print "============================= Completed Commit 1 ==================================="
