Primary keys issues on examples/versioned_history when using sqlite3

Using Python 3.4.3 and SQLAlchemy 1.1.4 on Ubuntu 14.10.

There are two unrelated bugs related to versioned_history example and its documentation.

Firstly, two tests fail because of the autoincrement parameter of the Document.id column on lines 640 and 663

            id = Column(Integer, primary_key=True, autoincrement=True)

It seems that when generating the history table, it fails because of the autoincrement argument, throwing the following exception:

SQLite does not support autoincrement for nose.proxy.CompileError: (in table 'document_history', column 'id'): SQLite does not support autoincrement for composite primary keys

It seems that deleing the autoincrement=True solves the issue. In fact, autoincrement has a particular meaning on sqlite dialect.

Secondly, I have detected a bug related to history_meta: If you add an object to the session and commit it, then you delete that object and commit, and finally you add another object and commit, using sqlite the last object you created is going to receive the same id of the deleted first object, this isn't an issue until a version of the last object has to be created: The primary key of that version will collide with the primary key of the deleted object in the history table thus raising an exception. I have prepared the following test, which can be added to the class TestVersioning on examples/versioned_history/test_versioning.py :

    def test_insert_delete_insert(self):
        ''' 
        This test checks whether the DBMS assigns the same id twice.
        sqlite does it by default, and one has to prevent this
        default behaviour by means of 'sqlite_autoincrement' set to True.
        http://docs.sqlalchemy.org/en/latest/dialects/sqlite.html
        '''
        class SomeClass(Versioned, self.Base, ComparableEntity):
            __tablename__ = 'sometable'

            id = Column(Integer, primary_key=True)
            name = Column(String(50))

        self.create_tables()
        sess = self.session
        sc = SomeClass(name='sc1')
        sess.add(sc)
        sess.commit()

        sess.delete(sc)
        sess.commit()

        sc2 = SomeClass(name='sc2')
        sess.add(sc2)
        sess.commit()

        SomeClassHistory = SomeClass.__history_mapper__.class_
        scdeleted = sess.query(SomeClassHistory).one()

        # If sc2 has the same id that deleted sc1 had,
        # it will fail when modified or deleted
        # because of the violation of the uniqueness of the primary key 
        # on sometable_history
        assert sc2.id != scdeleted.id
        # If previous assertion fails, this will also fail:
        sc2.name = 'sc2 modified'
        sess.commit()

Not sure whether this is the finest way of solving this, but one can modify the class Versioned (examples/versioned_history/history_meta.py), so that it sets the sqlite_autoincrement parameter to True on those classes that inherit from it:

class Versioned(object):
    @declared_attr
    def __mapper_cls__(cls):
        def map(cls, *arg, **kw):
            mp = mapper(cls, *arg, **kw)
            _history_mapper(mp)
            return mp
        return map

    __table_args__ = {'sqlite_autoincrement': True}

This seems to solve the issue.

Attached you can find the test file with the added test to check whether the database is giving the same id on a new instance after having deleted a previous one (test_insert_delete_insert). This file can be used to test the first issue I have talked about too (lines 677 and 701). On file history_meta you can find the proposed implementation of class Versioned that solves the issue of inserting-deleting-inserting on SQLite. This has to be thoroughly tested, as I have only check that it works with sqlite.

Comments (6)