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
