Incomplete entries in delete/update event for versioned assoc tables

[indiVar0508]

When a delete happens there are incomplete parameters for versioned table

import datetime

from sqlalchemy import Column, ForeignKey, Integer, DateTime, Table, create_engine, func, String
from sqlalchemy.orm import sessionmaker, relationship, configure_mappers, declarative_base
from sqlalchemy_history import make_versioned


make_versioned(user_cls=None)

Base = declarative_base()


book_author_table = Table(
    'book_author',
    Base.metadata,
    Column('book_id', Integer, ForeignKey('book.id'), primary_key=True, nullable=False),
    Column('author_id', Integer, ForeignKey('author.id'), primary_key=True, nullable=False),
    Column('created_date', DateTime, nullable=False, server_default=func.current_timestamp(), default=datetime.datetime.utcnow),
)


class Book(Base):
    __tablename__ = 'book'
    __versioned__ = {}

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(20), nullable=False)
    authors = relationship('Author', secondary=book_author_table, back_populates='books')


class Author(Base):
    __tablename__ = 'author'
    __versioned__ = {}

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(20), nullable=False)
    books = relationship('Book', secondary=book_author_table, back_populates='authors')

configure_mappers()
engine = create_engine('sqlite:///my_db.db', echo=True)

# Create all tables
Base.metadata.drop_all(bind=engine)
Base.metadata.create_all(bind=engine)

# Create a session
session = sessionmaker()
session.configure(bind=engine)
db = session()


lotr = Book(name='Lord of the rings')
tolkien = Author(name='JRR Tolkien', books=[lotr])
db.add(lotr)
db.add(tolkien)
db.commit()

tolkien.books = []
db.add(tolkien)
db.commit()

...
2024-02-07 15:21:52,779 INFO sqlalchemy.engine.Engine [generated in 0.00012s] (1,)
2024-02-07 15:21:52,780 INFO sqlalchemy.engine.Engine INSERT INTO "transaction" (remote_addr, issued_at) VALUES (?, ?)
2024-02-07 15:21:52,780 INFO sqlalchemy.engine.Engine [cached since 0.02407s ago] (None, '2024-02-07 09:51:52.780725')
2024-02-07 15:21:52,782 INFO sqlalchemy.engine.Engine DELETE FROM book_author WHERE book_author.book_id = ? AND book_author.author_id = ?
2024-02-07 15:21:52,782 INFO sqlalchemy.engine.Engine [generated in 0.00022s] (1, 1)
                                               **#<----- Here created date is missing --------> #**
2024-02-07 15:21:52,783 INFO sqlalchemy.engine.Engine INSERT INTO book_author_version (book_id, author_id, transaction_id, operation_type) VALUES (?, ?, ?, ?)
2024-02-07 15:21:52,783 INFO sqlalchemy.engine.Engine [generated in 0.00011s] (1, 1, 2, 2)
2024-02-07 15:21:52,783 INFO sqlalchemy.engine.Engine SELECT v.book_id, v.author_id, v.transaction_id, v2.transaction_id AS end_transaction_id 
FROM book_author_version AS v LEFT OUTER JOIN book_author_version AS v2 ON v2.transaction_id = (SELECT min(v3.transaction_id) AS min_1 
FROM book_author_version AS v3 
WHERE v3.transaction_id > v.transaction_id AND v3.book_id = v.book_id AND v3.author_id = v.author_id) ORDER BY v.transaction_id
2024-02-07 15:21:52,783 INFO sqlalchemy.engine.Engine [cached since 0.02144s ago] ()
2024-02-07 15:21:52,784 INFO sqlalchemy.engine.Engine UPDATE book_author_version SET end_transaction_id=? WHERE book_author_version.book_id = ? AND book_author_version.author_id = ? AND book_author_version.transaction_id = ?
2024-02-07 15:21:52,784 INFO sqlalchemy.engine.Engine [generated in 0.00012s] (2, 1, 1, 1)
2024-02-07 15:21:52,784 INFO sqlalchemy.engine.Engine COMMIT

[indiVar0508]

Looking further it is caused due to #97 only impacts when we delete as for update a new insert query is triggered for tables, ORM objects doesn't seem to have this issue on update

[indiVar0508]

We might need to introduce a Unset concept that could potentially address this.

[indiVar0508]

Verified that version tables store immediate value of original table in the event of edit and update.