Hello all: We are using examples/versioned_history (i.e. history_meta.py)
https://github.com/zzzeek/sqlalchemy/blob/master/examples/versioned_history/history_meta.py We noticed the following behavior: Within the same transaction (i.e. pyramid_tm), whenever a Versioned object changes and its followed by a session.query(), the event 'before_flush' is triggered and a new history object is created accompanied by a version increase. However, subsequent changes to the same versioned object followed by more session.query() keep adding new history objects with versions increased even though the transaction has not finished. Shouldn't all changes made to the same versioned object be "coalesced" into a single new history object and a single version increase if they all happen within the same transaction? It seems like as a side-effect of calling session.query() interlaced with versioned object changes would increase the version within the same transaction. Wouldn't this be considered a "questionable side-effect"? Here is the modified "PASSING" test case depicting the multiple version increase "side-effect": # examples/versioned_history/test_versioning.py # https://github.com/zzzeek/sqlalchemy/blob/master/examples/versioned_history/test_versioning.py def test_relationship(self): class SomeRelated(self.Base, ComparableEntity): __tablename__ = 'somerelated' id = Column(Integer, primary_key=True) class SomeClass(Versioned, self.Base, ComparableEntity): __tablename__ = 'sometable' id = Column(Integer, primary_key=True) name = Column(String(50)) related_id = Column(Integer, ForeignKey('somerelated.id')) related = relationship("SomeRelated", backref='classes') SomeClassHistory = SomeClass.__history_mapper__.class_ self.create_tables() sess = self.session sc = SomeClass(name='sc1') sess.add(sc) sess.commit() assert sc.version == 1 sr1 = SomeRelated() sc.related = sr1 sr2 = SomeRelated() # extra one to be used later sess.commit() assert sc.version == 2 eq_( sess.query(SomeClassHistory).filter( SomeClassHistory.version == 1).all(), [SomeClassHistory(version=1, name='sc1', related_id=None)] ) sc.related = None eq_( sess.query(SomeClassHistory).order_by( SomeClassHistory.version).all(), [ SomeClassHistory(version=1, name='sc1', related_id=None), SomeClassHistory(version=2, name='sc1', related_id=sr1.id) ] ) assert sc.version == 3 #### Modifications start here: sc.related = sr2 # Change made assert sc.version == 3 # Version has not change yet ... but it will since there is a change now history = sess.query(SomeClassHistory).order_by(SomeClassHistory.version).all() # before_flush triggered, new history object added to session for h in history: print "%r" % (h) assert sc.version == 4 # Version increased after session.query eq_( sess.query(SomeClassHistory).order_by( SomeClassHistory.version).all(), [ SomeClassHistory(version=1, name='sc1', related_id=None), SomeClassHistory(version=2, name='sc1', related_id=sr1.id), SomeClassHistory(version=3, name='sc1', related_id=None), ] ) sc.related = None # Change made assert sc.version == 4 # Version didn't increase ... yet eq_( sess.query(SomeClassHistory).order_by( SomeClassHistory.version).all(), [ SomeClassHistory(version=1, name='sc1', related_id=None), SomeClassHistory(version=2, name='sc1', related_id=sr1.id), SomeClassHistory(version=3, name='sc1', related_id=None), SomeClassHistory(version=4, name='sc1', related_id=sr2.id), ] ) assert sc.version == 5 # Version increased again although there was no net change within this transaction (sc.related = None => sc.related = sr2 => sc.related = None) # Notice there were no session.commit()'s # commit all now sess.commit() assert sc.version == 5 # version increased twice in a single transaction scr = sess.query(SomeClass).get(sc.id) assert scr.version == 5 # See final history in log print "Final history" history = sess.query(SomeClassHistory).order_by(SomeClassHistory.version).all() for h in history: print "%r" % (h) assert False, "Test case forced to fail" Here is the output log: ====================================================================== FAIL: test_relationship (examples.versioned_history.test_versioning.TestVersioning) ---------------------------------------------------------------------- Traceback (most recent call last): File "/Users/henddher/Documents/python_workspace/github_zzzeek_sqlalchemy/examples/versioned_history/test_versioning.py", line 628, in test_relationship assert False, "Test case forced to fail" AssertionError: Test case forced to fail -------------------- >> begin captured stdout << --------------------- SomeClassHistory(changed=datetime.datetime(2016, 9, 2, 17, 32, 34, 440061), id=1, name=u'sc1', related_id=None, version=1) SomeClassHistory(changed=datetime.datetime(2016, 9, 2, 17, 32, 34, 445386), id=1, name=u'sc1', related_id=1, version=2) SomeClassHistory(changed=datetime.datetime(2016, 9, 2, 17, 32, 34, 448406), id=1, name=u'sc1', related_id=None, version=3) Final history SomeClassHistory(changed=datetime.datetime(2016, 9, 2, 17, 32, 34, 440061), id=1, name=u'sc1', related_id=None, version=1) SomeClassHistory(changed=datetime.datetime(2016, 9, 2, 17, 32, 34, 445386), id=1, name=u'sc1', related_id=1, version=2) SomeClassHistory(changed=datetime.datetime(2016, 9, 2, 17, 32, 34, 448406), id=1, name=u'sc1', related_id=None, version=3) SomeClassHistory(changed=datetime.datetime(2016, 9, 2, 17, 32, 34, 451971), id=1, name=u'sc1', related_id=2, version=4) --------------------- >> end captured stdout << ---------------------- -------------------- >> begin captured logging << -------------------- sqlalchemy.engine.base.Engine: INFO: PRAGMA table_info("sometable_history") sqlalchemy.engine.base.Engine: INFO: () sqlalchemy.engine.base.Engine: INFO: PRAGMA table_info("somerelated") sqlalchemy.engine.base.Engine: INFO: () sqlalchemy.engine.base.Engine: INFO: PRAGMA table_info("sometable") sqlalchemy.engine.base.Engine: INFO: () sqlalchemy.engine.base.Engine: INFO: CREATE TABLE sometable_history ( id INTEGER NOT NULL, name VARCHAR(50), related_id INTEGER, version INTEGER NOT NULL, changed DATETIME, PRIMARY KEY (id, version) ) sqlalchemy.engine.base.Engine: INFO: () sqlalchemy.engine.base.Engine: INFO: COMMIT sqlalchemy.engine.base.Engine: INFO: CREATE TABLE somerelated ( id INTEGER NOT NULL, PRIMARY KEY (id) ) sqlalchemy.engine.base.Engine: INFO: () sqlalchemy.engine.base.Engine: INFO: COMMIT sqlalchemy.engine.base.Engine: INFO: CREATE TABLE sometable ( id INTEGER NOT NULL, name VARCHAR(50), related_id INTEGER, version INTEGER NOT NULL, PRIMARY KEY (id), FOREIGN KEY(related_id) REFERENCES somerelated (id) ) sqlalchemy.engine.base.Engine: INFO: () sqlalchemy.engine.base.Engine: INFO: COMMIT sqlalchemy.engine.base.Engine: INFO: BEGIN (implicit) sqlalchemy.engine.base.Engine: INFO: INSERT INTO sometable (name, related_id, version) VALUES (?, ?, ?) sqlalchemy.engine.base.Engine: INFO: ('sc1', None, 1) sqlalchemy.engine.base.Engine: INFO: COMMIT sqlalchemy.engine.base.Engine: INFO: BEGIN (implicit) sqlalchemy.engine.base.Engine: INFO: SELECT sometable.id AS sometable_id, sometable.name AS sometable_name, sometable.related_id AS sometable_related_id, sometable.version AS sometable_version FROM sometable WHERE sometable.id = ? sqlalchemy.engine.base.Engine: INFO: (1,) sqlalchemy.engine.base.Engine: INFO: INSERT INTO somerelated DEFAULT VALUES sqlalchemy.engine.base.Engine: INFO: () sqlalchemy.engine.base.Engine: INFO: INSERT INTO sometable_history (id, name, related_id, version, changed) VALUES (?, ?, ?, ?, ?) sqlalchemy.engine.base.Engine: INFO: (1, u'sc1', None, 1, '2016-09-02 17:32:34.440061') sqlalchemy.engine.base.Engine: INFO: UPDATE sometable SET related_id=?, version=? WHERE sometable.id = ? sqlalchemy.engine.base.Engine: INFO: (1, 2, 1) sqlalchemy.engine.base.Engine: INFO: COMMIT sqlalchemy.engine.base.Engine: INFO: BEGIN (implicit) sqlalchemy.engine.base.Engine: INFO: SELECT sometable.id AS sometable_id, sometable.name AS sometable_name, sometable.related_id AS sometable_related_id, sometable.version AS sometable_version FROM sometable WHERE sometable.id = ? sqlalchemy.engine.base.Engine: INFO: (1,) sqlalchemy.engine.base.Engine: INFO: SELECT sometable_history.id AS sometable_history_id, sometable_history.name AS sometable_history_name, sometable_history.related_id AS sometable_history_related_id, sometable_history.version AS sometable_history_version, sometable_history.changed AS sometable_history_changed FROM sometable_history WHERE sometable_history.version = ? sqlalchemy.engine.base.Engine: INFO: (1,) sqlalchemy.engine.base.Engine: INFO: SELECT somerelated.id AS somerelated_id FROM somerelated WHERE somerelated.id = ? sqlalchemy.engine.base.Engine: INFO: (1,) sqlalchemy.engine.base.Engine: INFO: INSERT INTO sometable_history (id, name, related_id, version, changed) VALUES (?, ?, ?, ?, ?) sqlalchemy.engine.base.Engine: INFO: (1, u'sc1', 1, 2, '2016-09-02 17:32:34.445386') sqlalchemy.engine.base.Engine: INFO: UPDATE sometable SET related_id=?, version=? WHERE sometable.id = ? sqlalchemy.engine.base.Engine: INFO: (None, 3, 1) sqlalchemy.engine.base.Engine: INFO: SELECT sometable_history.id AS sometable_history_id, sometable_history.name AS sometable_history_name, sometable_history.related_id AS sometable_history_related_id, sometable_history.version AS sometable_history_version, sometable_history.changed AS sometable_history_changed FROM sometable_history ORDER BY sometable_history.version sqlalchemy.engine.base.Engine: INFO: () sqlalchemy.engine.base.Engine: INFO: INSERT INTO somerelated DEFAULT VALUES sqlalchemy.engine.base.Engine: INFO: () sqlalchemy.engine.base.Engine: INFO: INSERT INTO sometable_history (id, name, related_id, version, changed) VALUES (?, ?, ?, ?, ?) sqlalchemy.engine.base.Engine: INFO: (1, u'sc1', None, 3, '2016-09-02 17:32:34.448406') sqlalchemy.engine.base.Engine: INFO: UPDATE sometable SET related_id=?, version=? WHERE sometable.id = ? sqlalchemy.engine.base.Engine: INFO: (2, 4, 1) sqlalchemy.engine.base.Engine: INFO: SELECT sometable_history.id AS sometable_history_id, sometable_history.name AS sometable_history_name, sometable_history.related_id AS sometable_history_related_id, sometable_history.version AS sometable_history_version, sometable_history.changed AS sometable_history_changed FROM sometable_history ORDER BY sometable_history.version sqlalchemy.engine.base.Engine: INFO: () sqlalchemy.engine.base.Engine: INFO: SELECT sometable_history.id AS sometable_history_id, sometable_history.name AS sometable_history_name, sometable_history.related_id AS sometable_history_related_id, sometable_history.version AS sometable_history_version, sometable_history.changed AS sometable_history_changed FROM sometable_history ORDER BY sometable_history.version sqlalchemy.engine.base.Engine: INFO: () sqlalchemy.engine.base.Engine: INFO: INSERT INTO sometable_history (id, name, related_id, version, changed) VALUES (?, ?, ?, ?, ?) sqlalchemy.engine.base.Engine: INFO: (1, u'sc1', 2, 4, '2016-09-02 17:32:34.451971') sqlalchemy.engine.base.Engine: INFO: UPDATE sometable SET related_id=?, version=? WHERE sometable.id = ? sqlalchemy.engine.base.Engine: INFO: (None, 5, 1) sqlalchemy.engine.base.Engine: INFO: SELECT sometable_history.id AS sometable_history_id, sometable_history.name AS sometable_history_name, sometable_history.related_id AS sometable_history_related_id, sometable_history.version AS sometable_history_version, sometable_history.changed AS sometable_history_changed FROM sometable_history ORDER BY sometable_history.version sqlalchemy.engine.base.Engine: INFO: () sqlalchemy.engine.base.Engine: INFO: COMMIT sqlalchemy.engine.base.Engine: INFO: BEGIN (implicit) sqlalchemy.engine.base.Engine: INFO: SELECT sometable.id AS sometable_id, sometable.name AS sometable_name, sometable.related_id AS sometable_related_id, sometable.version AS sometable_version FROM sometable WHERE sometable.id = ? sqlalchemy.engine.base.Engine: INFO: (1,) sqlalchemy.engine.base.Engine: INFO: SELECT sometable_history.id AS sometable_history_id, sometable_history.name AS sometable_history_name, sometable_history.related_id AS sometable_history_related_id, sometable_history.version AS sometable_history_version, sometable_history.changed AS sometable_history_changed FROM sometable_history ORDER BY sometable_history.version sqlalchemy.engine.base.Engine: INFO: () --------------------- >> end captured logging << --------------------- -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
