Re: [sqlalchemy] Session autoflush when using history_meta (examples/versioned_history)

Fri, 02 Sep 2016 14:24:44 -0700 


On 09/02/2016 01:42 PM, HP3 wrote:

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?



That would be a nice option, I'll gladly accept a PR that makes this an 
optional / documented / unit tested behavior of the recipe.



I wrote that code for a commercial client and it works the way they 
wanted, so at least someone wanted it that way :).  It's only an 
example, not a feature, so that people can make it do whatever they'd like.



Lots of people use the recipe as is, and who knows if they like that 
they can access the ongoing changes of an object as they are proceeding 
in a long operation.





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

    deftest_relationship(self):


        classSomeRelated(self.Base, ComparableEntity):

            __tablename__ = 'somerelated'


            id= Column(Integer, primary_key=True)


        classSomeClass(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()


        assertsc.version == 1


        sr1 = SomeRelated()

        sc.related = sr1


        sr2 = SomeRelated() # extra one to be used later

        sess.commit()


        assertsc.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)

            ]

        )


        assertsc.version == 3


        #### Modifications start here:


        sc.related = sr2 # Change made


        assertsc.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

        forh inhistory:

            print"%r"% (h)


        assertsc.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


        assertsc.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),

            ]

        )


        assertsc.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()

        assertsc.version == 5   # version increased twice in a single
transaction


        scr = sess.query(SomeClass).get(sc.id)

        assertscr.version == 5


        # See final history in log

        print"Final history"

        history =
sess.query(SomeClassHistory).order_by(SomeClassHistory.version).all()

        forh inhistory:

            print"%r"% (h)


        assertFalse, "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 [email protected]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.


--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.






















					Reply via email to