Versioning and Foreign Keys

[JPLaverdure]

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 !

[JPLaverdure]

Oops, above references to "versioned_meta" should read "history_meta".

Sorry

[Michael Bayer]

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.

[JPLaverdure]

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

[Michael Bayer]

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 sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[JPLaverdure]

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