On Sunday, 6 May 2018 07:27:15 UTC+9, Mike Bayer wrote:
> We have examples of this kind of versioning in the docs, and I just > took a look and found they needed to be modernized, so I've done that [...] > http://docs.sqlalchemy.org/en/latest/_modules/examples/versioned_rows/versioned_rows_w_versionid.html > Sorry, I completely missed this while looking for possible existing SQLAlchemy implementations! Thanks a lot for updating it. Looks great. One small thing I noticed in my implementation, is that composite key autoincrement unsurprisingly does not work well with MySQL (and I believe I also had some unexpected behaviours with sqlite). I solved this by using a declared attribute with a custom default query: @declared_attr def id(cls): return Column(Integer, primary_key=True, default=select([text('COALESCE(MAX(id), 0)+1 FROM ' + cls.__tablename__)]).as_scalar()) But this, in turn, meant that `id` appeared after `version` in the table declaration, making the composite keys be `(version, id)`, which MySQL objected to, when trying to do certain foreign key operations (and does not seem a great idea altogether for query optimisation). Best solution seemed to add: @declared_attr def version(cls): return Column(Integer, primary_key=True, default=0, autoincrement= False) I don't know if there's a simpler/cleaner way to ensure that the composite primary is created in the right order and with the right autoincrement rules, but this seemed to work on both sqlite and mySQL. you can of course write that query using query.join() directly but the > column_property() can't change the query to have a JOIN in the FROM > clause automatically. persisting the is_current_version value might > be overall easier. Yes, that's what i've been thinking and so far resisting, out of an irrational normalisation fetish. I think I will test how it fares, and potentially add a persisted value. Thanks a lot for your precious help! -- Dave -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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 https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
