On 6/17/15 12:00 PM, Adrian wrote:
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)
Well the concept of back_populates pointing in three directions like
that is not how it was intended to be used. back_populates is intended
to point two relationships to each other mutually. I'm not sure why
these effects are happening but it probably has something to do with
that. I could dig into what's going on and I may do so, but I'm sure
whatever I see will come to the same immediate solution anyway.
If keeping this model, I would keep EventNote.revisions and
EventNoteRevision.note as the two relationships here with a traditional
back_populates between them. The "latest_revision" relationship here at
most should just be a viewonly=True. reviisions/note should be used as
the persistence channel exclusively.
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).
I'd definitely advise doing it that way, that's the supported way to do
a "favorite id" approach and is more relationally correct (e.g. not
possible to have multiple "favorites"). It seems like you read the
docs at
http://docs.sqlalchemy.org/en/rel_1_0/orm/relationship_persistence.html#rows-that-point-to-themselves-mutually-dependent-rows,
so I'd give that a revisit and feel free to share the errors from that case.
--
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.
