Re: [sqlalchemy] How to do a revision table or How to get a 1:1 and 1:n relationship between the same tables to work properly
I hadn't seen that part of the documentation - doing it that way works fine now! I ended up using a signal to update `revisions` automatically when setting `current_revision`: https://github.com/ThiefMaster/indico/blob/f300c3b9dc8d499b4d745dee74edceff53e7ffb4/indico/modules/events/notes/models/notes.py#L159-L164 Is there any better way to do this or is that the way to go? 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] How to do a revision table or How to get a 1:1 and 1:n relationship between the same tables to work properly
have you taken a look at this approach? https://bitbucket.org/zzzeek/sqlalchemy/src/4a25c10e27147917e93e6893df13b2b55673e0a7/examples/versioned_history/?at=master chers, richard. On 06/18/2015 08:44 AM, Adrian wrote: I hadn't seen that part of the documentation - doing it that way works fine now! I ended up using a signal to update `revisions` automatically when setting `current_revision`: https://github.com/ThiefMaster/indico/blob/f300c3b9dc8d499b4d745dee74edceff53e7ffb4/indico/modules/events/notes/models/notes.py#L159-L164 Is there any better way to do this or is that the way to go? 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 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 sqlalchemy+unsubscr...@googlegroups.com mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. attachment: richard.vcf
Re: [sqlalchemy] How to do a revision table or How to get a 1:1 and 1:n relationship between the same tables to work properly
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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] How to do a revision table or How to get a 1:1 and 1:n relationship between the same tables to work properly
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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.