catch_the_error.py

#!/usr/bin/python
# coding: utf-8
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import Column, ForeignKey, Integer, String, Sequence, BigInteger
from sqlalchemy.orm import backref, configure_mappers, relationship
from sqlalchemy_continuum import make_versioned, VersioningManager
from sqlalchemy_continuum.transaction import TransactionBase

app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite://"
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
app.config["SQLALCHEMY_ECHO"] = True
db = SQLAlchemy(app)


make_versioned(
    user_cls=None,
    options={
        # @note: UNCOMMENTING BELOW will make it FAIL
        # "table_name": "version_%s",
    },
)


class Parent(db.Model):
    __versioned__ = {}

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


class ChildType(db.Model):
    __versioned__ = {}

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


class Child(db.Model):
    __versioned__ = {}

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

    parent_id = Column(ForeignKey(Parent.id))
    parent = relationship(Parent, backref="children")

    type_id = Column(ForeignKey(ChildType.id))
    _type = relationship(ChildType)


configure_mappers()


def seed_db():
    child_type = ChildType(name="random name")
    db.session.add(child_type)
    db.session.commit()

    parent = Parent(name="parent name")
    db.session.add(parent)
    db.session.commit()

    child = Child(name="child name", parent=parent, _type=child_type)
    db.session.add(child)
    db.session.commit()


if __name__ == "__main__":
    """
    Here is an example on how for some reasons, it sqlalchemy_continuum fails to build the correct query to retrieve related properties of an object.

    First is created one object per type and inserted into db.
    When using the object (non-versioned), everything works as expected. The relationship navigation works as expected.
    The problem occurs when working with versioned objects. Especially when trying to access a relationship of an object.

    Running:

    db.session.query(version_class(Parent)).first().children

    you would expect to return a list of VersionChild objects, in this case a list with one element, but, the list is empty.

    And running the query below:

    db.session.query(version_class(Child)).first().parent

    Returns and error

    """

    db.create_all()

    # fill in the database with some data
    seed_db()

    # failling queries
    from sqlalchemy_continuum import version_class
    VChild = version_class(Child)
    vchild = db.session.query(VChild).first()
    # the line above produces two different queries depending on that option above:
    _sql_working = """
SELECT parent_version.id AS parent_version_id, parent_version.name AS parent_version_name, parent_version.transaction_id AS parent_version_transaction_id, parent_version.end_transaction_id AS parent_version_end_transaction_id, parent_version.operation_type AS parent_version_operation_type
FROM parent_version
WHERE parent_version.id = ? AND parent_version.transaction_id = (SELECT max(parent_version.transaction_id) AS max_1
FROM parent_version
WHERE parent_version.transaction_id <= ? AND parent_version.id = ?) AND parent_version.operation_type != ?
 LIMIT ? OFFSET ?
    """
    _sql_not_working = """
SELECT max(version_parent.transaction_id) AS max_1
FROM version_parent, parent, child
WHERE version_parent.transaction_id <= :transaction_id_1 AND parent.id = child.parent_id
    """
    print(vchild.parent)
    print(vchild.name)