On 23. Jul, 2013, at 9:31, Matevž Bradač wrote: > On 16. Jul, 2013, at 16:12, Matevž Bradač wrote: > >> On 15. Jul, 2013, at 13:32, Anze Staric wrote: >> >>> Matevz and I have started working on implementing the features >>> described in BEP_0010. These are the problems that we have encountered >>> so far: >>> >>> Both alternatives: >>> Sqlite supports auto_increment only on one field integer primary key. >>> For both variants described, we need to perform autoincrements of >>> product specific id-s manually. On postgres and mysql, auto increments >>> can be achieved, but current sql translator does not distinguish >>> between different backends. >>> >>> The current sql used to generate product specific ids is the following: >>> >>> INSERT INTO ticket (id, ...) VALUES (IFNULL((SELECT MAX(id) FROM >>> ticket WHERE product='%s'), 0)+1, ...) >>> >>> Does anyone know of a better solution? This one has problems, if two >>> concurrent transactions insert ticket. The second of them to commit >>> fails (on postgres). >>> >>> >>> Alternative 1: >>> Another problem lies in the code trac uses to fetch the ticket id >>> after the insert is done. Currently it uses db.get_last_id(cursor, >>> 'ticket') to get the last ticket id, but this causes a problem with >>> variant 1, where auto increment field is actually uid. >>> >>> >>> I am currently inclining towards backend-aware sql translator. With >>> it, we could use database capabilities for auto increment on >>> postgres/mysql and transform sql to the above version on sqlite. On >>> sqlite it should not cause problems as the database uses more rigorous >>> locking. >>> >>> >>> Anze >>> >> >> Just a quick note that the BEP has been updated with our findings so far, >> based on the prototypes created for both alternatives. >> I'm now leaning more towards Alternative 1 with Olemis' suggestions to >> rename global id to uid, since at first glance it seems cleaner from an >> implementation point of view. >> I also support Anze's proposal for fixing the backend issues, either in >> the SQL translator itself, or in the Trac's backend code. IMO, this should >> probably be a prerequisite. >> >> -- >> matevz > > Hi, > > Further update on the prototypes - the SELECT MAX issue mentioned in the BEP > seem to have been solved. For sqlite3 there was no issue found due to the way > it handles concurrent connections. For Postgres and MySQL both scoped and the > global IDs are declared as autoincremented integer fields (using the database > sequences), so the next IDs are calculated by the database engine. > If however the ID fields are specified by the caller, those values will be > used instead, and the caller must then manually reset the sequence by calling > db.update_sequence(). This will be used for upgrade and product import > purposes, where it is important to keep existing IDs intact. > > The code for alternative 1 is ready to be committed to trunk, if there are no > objections we'll commit it tomorrow. Note that recent issues with 0.6 release > have priority, so this may be postponed a bit. > > -- > matevz >
The code was committed to a new branch (bep_0010_ticket_numbering, r1506493) to avoid disruptions in case the 0.6.1 fix release is copied from trunk. Please have a look at the changes, all suggestions, improvements etc. are more than welcome.
