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 > > On Mon, Jul 8, 2013 at 11:13 PM, Andrej Golcov <[email protected]> wrote: >>> I interpreted your statement above as to have global ticket ID and >>> product-specific ticket ID for a single ticket. If that's the case, >>> IMHO it is another source of confusion to have URLs /ticket/123456789 >>> (<=global) and /ticket/products/pid/ticket/12 . I mean : >>> >>> - Ticket identity is ambiguous >>> - The user would have to remember two completely unrelated IDs >> Just want to add a use case to Olemis's list: >> There are Trac plugins that provide there own UI that exposes ticket >> IDs to user (e.g. MasterTicketPlugin) and access ticket table directly >> by passing ticket API. That would be quite strange for a user to see >> one ID in Bloodhound pages and another ID in a plugin pages for the >> same ticket. >> >> Cheers, Andrej
