1

related to this question: SQLAlchemy logging of changes with date and user

I'm using a modified version of the "recipe" for versioning changes automatically. I think it's able to handle some forms of relationships already (not sure, though), but I'm not able to handle the case where there's a many-to-many relationship in a separate table.

Here's a simple example that's an issue:

from history_meta import (Versioned, versioned_session)

Base = declarative_base()

user_to_group = Table('user_to_group', Base.metadata,
    Column('user_login', String(60), ForeignKey('user.login')),
    Column('group_name', String(100), ForeignKey('group.name'))
)

class User(Versioned, Base):
    __tablename__ = 'user'
    login = Column(String(60), primary_key=True, nullable=False)
    password = Column(BINARY(20), nullable=False)

class Group(Versioned, Base):
    __tablename__ = 'group'
    name = Column(String(100), primary_key=True, nullable=False)
    description = Column(String(100), nullable=True)
    users = relationship(User, secondary=user_to_group, backref='groups')

When generating the tables in the database with Base.metadata.create_all(engine) I can see that there are only 5 tables: user, group, user_to_group, user_history, and group_history There is no user_to_group_history.

The "versioning" gets added to the declarative objects through inheritance of Versioned, but there's no way (that I can see) to do something similar with the user_to_group table which isn't using the declarative format. There's also notes in the documentation saying that it's not a good idea using a table that's mapped to a class so I'm trying to avoid using a declarative object for the relationship.

Community
  • 1
  • 1
Tim Tisdall
  • 9,914
  • 3
  • 52
  • 82
  • yeah the versioned recipe would probably need more work to actually make versions for the user_to_group table. I'm not sure how a collection would be versioned, actually. You'd perhaps need to identify versions of the collection as a whole (version number for each association row?), but that implies copying the whole collection every time? not really sure, depends on what you actually need to track. – zzzeek Apr 14 '13 at 04:46
  • ^_^ I guess I was too worried about getting the recipe to work in my case to really think through whether what it did was actually what I needed in that particular case. Essentially I want to be able to recreate any system state in the past given a particular date and time. I guess I'll have to flag entries as "deleted" instead of actually deleting them (which the versioning recipe doesn't do). – Tim Tisdall Apr 15 '13 at 13:21
  • I'm still not really clear on the issue involving mapped classes as relationships. I understand the problem about it double insert/update-ing, but I don't really understand the underlying mechanics to know what to do to avoid it. Is an association proxy sufficient? And if I do the "read-only" thing, does that mean it's not possible to make changes? – Tim Tisdall Apr 15 '13 at 13:23
  • if you map the "secondary" table directly, you just would need to consider your direct mapping and the usage of "secondary" inside of the relationship() separately. If you alter the relationship() collection and flush, the rows in "secondary" will change, but that doesn't immediately alter the state of the mapped objects you might have already loaded directly from "secondary". The reverse is true as well, if you create new Secondary() objects and flush, the relationship() won't see that change until the session is expired and state is reloaded. – zzzeek Apr 16 '13 at 23:26

0 Answers0