On Sat, May 5, 2018 at 7:49 PM, Dave von Umlaut <zedrd...@gmail.com> wrote: > > > 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.
SQLite's autoincrement won't work with a composite PK, so you do need some kind of default in that case. for MySQL, if you have a composite primary key, you can add the flag autoincrement=True to the "id" and that should render with the AUTO INCREMENT directive that will set it up as you need. > >> 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 sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
