Re: [sqlalchemy] Versioning and Foreign Keys
Hi Michael,
Thanks for your reply... Your input is always very resourcefull, thanks !
I put together a small example which doesn't seem to function as expected:
class Sample(Base):
__metaclass__ = VersionedMeta
__tablename__ = 'sample'
__table_args__ = {'schema':'test'}
id = Column(Integer, primary_key=True)
token = Column(String(64))
box_id = Column(Integer, ForeignKey('test.box.id', onupdate='cascade',
ondelete='cascade'), nullable=False)
def __init__(self, token, box):
self.token = token
self.box = box
class Container(Base):
__metaclass__ = VersionedMeta
__tablename__ = 'container'
__table_args__ = {'schema':'test'}
id = Column(Integer, primary_key=True)
discriminator = Column('type', String(64))
token = Column(String(128), nullable=False)
description = Column(String)
__mapper_args__ = {'polymorphic_on': discriminator,
'polymorphic_identity':'container'}
def __init__(self, token, description=''):
self.token = token
self.description = description
class Box(Container):
__tablename__ = 'box'
__table_args__ = {'schema':'test'}
__mapper_args__ = {'polymorphic_identity': 'box'}
id = Column(Integer, ForeignKey('test.container.id', onupdate=cascade,
ondelete=cascade), primary_key=True)
barcode = Column(String(64))
samples = relationship('Sample', backref='box')
def __init__(self, token, description='', barcode=''):
super(Box, self).__init__(token, description)
self.barcode = barcode
Base.metadata.drop_all()
Base.metadata.create_all()
Session = scoped_session(sessionmaker(extension=VersionedListener()))
sess = Session()
b = Box('Big box', 'sample1')
sess.add(b)
s = Sample('New sample', b)
sess.add(s)
sess.commit()
sess.delete(b)
sess.commit()
This fails because of the nullable=False clause on the Sample foreign key
as SQLA seems to try to update the sample to set box_id to null...
console printout: (from the delete portion)
2011-09-20 11:41:51,021 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2011-09-20 11:41:51,022 INFO sqlalchemy.engine.base.Engine SELECT
test.container.type AS test_container_type, test.box.id AS test_box_id,
test.container.id AS test_container_id, test.container.token AS
test_container_token, test.container.description AS
test_container_description, test.container.version AS
test_container_version, test.box.barcode AS test_box_barcode
FROM test.container JOIN test.box ON test.container.id = test.box.id
WHERE test.container.id = %(param_1)s
2011-09-20 11:41:51,023 INFO sqlalchemy.engine.base.Engine {'param_1': 1}
2011-09-20 11:41:51,025 INFO sqlalchemy.engine.base.Engine SELECT
test.sample.id AS test_sample_id, test.sample.token AS test_sample_token,
test.sample.box_id AS test_sample_box_id, test.sample.version AS
test_sample_version
FROM test.sample
WHERE %(param_1)s = test.sample.box_id
2011-09-20 11:41:51,025 INFO sqlalchemy.engine.base.Engine {'param_1': 1}
2011-09-20 11:41:51,028 INFO sqlalchemy.engine.base.Engine INSERT INTO
test.container_history (id, type, token, description, version) VALUES
(%(id)s, %(type)s, %(token)s, %(description)s, %(version)s)
2011-09-20 11:41:51,028 INFO sqlalchemy.engine.base.Engine {'token': u'Big
box', 'version': 1, 'type': u'box', 'id': 1, 'description': u'sample1'}
2011-09-20 11:41:51,029 INFO sqlalchemy.engine.base.Engine INSERT INTO
test.box_history (id, barcode, version) VALUES (%(id)s, %(barcode)s,
%(version)s)
2011-09-20 11:41:51,029 INFO sqlalchemy.engine.base.Engine {'barcode': u'',
'id': 1, 'version': 1}
2011-09-20 11:41:51,031 INFO sqlalchemy.engine.base.Engine UPDATE
test.sample SET box_id=%(box_id)s WHERE test.sample.id = %(test_sample_id)s
2011-09-20 11:41:51,031 INFO sqlalchemy.engine.base.Engine {'box_id': None,
'test_sample_id': 1}
2011-09-20 11:41:51,032 INFO sqlalchemy.engine.base.Engine ROLLBACK
So it does indeed seem to visit the sample, but I must be doing something
wrong in my declaration because SQLA should be attempting to delete the
sample not update it !
Thanks
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/XAPy4uSlJAMJ.
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.
Re: [sqlalchemy] Versioning and Foreign Keys
On Sep 20, 2011, at 11:53 AM, JPLaverdure wrote:
This fails because of the nullable=False clause on the Sample foreign key
as SQLA seems to try to update the sample to set box_id to null...
console printout: (from the delete portion)
I'm not sure this has anything to do with versioning ? This is just that the
relationship box needs cascade='all, delete-orphan' on it so that Sample is
marked for deletion rather than set null when its parent Box is deleted.
2011-09-20 11:41:51,021 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2011-09-20 11:41:51,022 INFO sqlalchemy.engine.base.Engine SELECT
test.container.type AS test_container_type, test.box.id AS test_box_id,
test.container.id AS test_container_id, test.container.token AS
test_container_token, test.container.description AS
test_container_description, test.container.version AS test_container_version,
test.box.barcode AS test_box_barcode
FROM test.container JOIN test.box ON test.container.id = test.box.id
WHERE test.container.id = %(param_1)s
2011-09-20 11:41:51,023 INFO sqlalchemy.engine.base.Engine {'param_1': 1}
2011-09-20 11:41:51,025 INFO sqlalchemy.engine.base.Engine SELECT
test.sample.id AS test_sample_id, test.sample.token AS test_sample_token,
test.sample.box_id AS test_sample_box_id, test.sample.version AS
test_sample_version
FROM test.sample
WHERE %(param_1)s = test.sample.box_id
2011-09-20 11:41:51,025 INFO sqlalchemy.engine.base.Engine {'param_1': 1}
2011-09-20 11:41:51,028 INFO sqlalchemy.engine.base.Engine INSERT INTO
test.container_history (id, type, token, description, version) VALUES
(%(id)s, %(type)s, %(token)s, %(description)s, %(version)s)
2011-09-20 11:41:51,028 INFO sqlalchemy.engine.base.Engine {'token': u'Big
box', 'version': 1, 'type': u'box', 'id': 1, 'description': u'sample1'}
2011-09-20 11:41:51,029 INFO sqlalchemy.engine.base.Engine INSERT INTO
test.box_history (id, barcode, version) VALUES (%(id)s, %(barcode)s,
%(version)s)
2011-09-20 11:41:51,029 INFO sqlalchemy.engine.base.Engine {'barcode': u'',
'id': 1, 'version': 1}
2011-09-20 11:41:51,031 INFO sqlalchemy.engine.base.Engine UPDATE test.sample
SET box_id=%(box_id)s WHERE test.sample.id = %(test_sample_id)s
2011-09-20 11:41:51,031 INFO sqlalchemy.engine.base.Engine {'box_id': None,
'test_sample_id': 1}
2011-09-20 11:41:51,032 INFO sqlalchemy.engine.base.Engine ROLLBACK
So it does indeed seem to visit the sample, but I must be doing something
wrong in my declaration because SQLA should be attempting to delete the
sample not update it !
Thanks
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/XAPy4uSlJAMJ.
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.
--
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.
Re: [sqlalchemy] Versioning and Foreign Keys
Oh god... I thought the statements used when declaring the ForeignKey (ondelete, onupdate) would imply that the relationship function in the same manner. I didn't think the 2 would be decoupled so much.. Although now that you point it out, it does seem consistent. I guess when I thought the children where deleted but not saved in _history, I did not have the nullable=False statement on the foreign key and when they did not show up in the _history table, didn't think of looking back in the children's table to realise they had not been deleted. Sorry for the trouble... Seems like I owe you a beer or two now. JP -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/JOR1NVrg2bYJ. 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.
[sqlalchemy] Versioning and Foreign Keys
Hello, I'm using the versioned objects suggested in the SQLAlchemy examples: http://www.sqlalchemy.org/docs/orm/examples.html#versioned-objects One of the aspects of entity management seems a bit weird though: If entities are linked using foreign keys, (let's say in one-to-many relationship), deleting the parent will indeed delete the children (because of the on delete cascade clause) but the last state of the children will NOT be saved in the _history table of the children So I basically lose the last state of the children. ie: the parent _history is saved but none of the children are. I of course solved this by running a delete on all children prior to deleting the parent but as my model grows in complexity, I would sure love for all of this to be done automagically. in other words for versioned_meta to follow foreign keys and mark and linked entities them as modified as well. I might be doing something wrong or this might be specific to postgresql... But I don't think that's the case... What would I need to modify in versioned_meta to allow for this ? Is it even possible ? Thanks ! -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/S-Qf8WjS08IJ. 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.
Re: [sqlalchemy] Versioning and Foreign Keys
On Sep 19, 2011, at 4:09 PM, JPLaverdure wrote: Hello, I'm using the versioned objects suggested in the SQLAlchemy examples: http://www.sqlalchemy.org/docs/orm/examples.html#versioned-objects One of the aspects of entity management seems a bit weird though: If entities are linked using foreign keys, (let's say in one-to-many relationship), deleting the parent will indeed delete the children (because of the on delete cascade clause) but the last state of the children will NOT be saved in the _history table of the children So I basically lose the last state of the children. ie: the parent _history is saved but none of the children are. I of course solved this by running a delete on all children prior to deleting the parent but as my model grows in complexity, I would sure love for all of this to be done automagically. in other words for versioned_meta to follow foreign keys and mark and linked entities them as modified as well. The deletion should be hit if you delete the parent via session.delete(parent), and don't have passive_deletes=True on the relationship; it would load in the child objects and mark them deleted individually, thus making them available in the deleted list to the versioned listener. Otherwise if you're relying on the DB to emit the deletes via FK cascades or otherwise are using an aggregate DELETE statement, then you aren't going to get them in the deleted list during a flush. The core assumption of the example is that you're implementing versioning at the application level, instead of at the trigger level where behavior would be more comprehensive. -- 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.
