I'm trying to store old versions of (some of) the data in one of my tables.
To do so, I'm thinking about models like this (not including anything not
relevant to the case):
class EventNote(db.Model):
id = db.Column(db.Integer, primary_key=True)
latest_revision = db.relationship(
'EventNoteRevision',
lazy=False,
uselist=False,
primaryjoin=lambda: (EventNote.id == EventNoteRevision.note_id) &
EventNoteRevision.is_latest,
back_populates='note'
)
revisions = db.relationship(
'EventNoteRevision',
lazy=True,
cascade='all, delete-orphan',
primaryjoin=lambda: EventNote.id == EventNoteRevision.note_id,
order_by=lambda: EventNoteRevision.created_dt.desc(),
back_populates='note'
)
class EventNoteRevision(db.Model):
id = db.Column(db.Integer, primary_key=True)
note_id = db.Column(db.Integer, db.ForeignKey('events.notes.id'),
nullable=False, index=True)
is_latest = db.Column(db.Boolean, nullable=False, default=False)
# ...and some columns for the actual data of that revision
note = db.relationship(
'EventNote',
lazy=False,
back_populates='revisions'
)
However, it always breaks somewhere (I was trying around with some
variations in the relationship configurations)...
These are the problems I've encountered so far:
- A newly created revision assigned to `latest_revision` is flushed with a
null `note_id`
- Assigning a new revision to `latest_revision` (i.e. with another revision
already existing) results in the old one being DELETEd or its note_id being
NULLed out (neither should happen)
I could really use some help on how to do this properly. The model posted
above can be changed in any way. For example, I wouldn't mind having a
`latest_revision_id` column in `EventNote`,
but when I tried that (including `use_alter` and `post_update`) I also
ended up with tons of different errors, including some that showed up every
other time I started my application (seems like
something doesn't happen in a deterministic order during mapper
configuration).
One option to avoid all the problems could be using the revision table only
for OLD data, i.e. keeping all the latest data inside `EventNote` and only
adding a new revision when something changes.
That way I would avoid having two relationships and all the problems would
go away. I know at least one big site doing it like this (Stack Overflow),
so maybe it's not the worst option... even though
they probably had other reasons to do it like this since they aren't using
SQLAlchemy. But after having spent half the afternoon trying to get the
two-relationship solution working I'm really tempted
to do it like this... Especially since I wouldn't have to worry about
allowing only one `is_latest` revision per `note_id` (easy with a
conditional unique index, but needs extra code to mark the old ones
as not being the latest one anymore)
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
