Page "Proposals/BEP-0010" was changed by matevzb Diff URL: <https://issues.apache.org/bloodhound/wiki/Proposals/BEP-0010?action=diff&version=4> Revision 4 Comment: updated with results from prototype implementations Changes: -------8<------8<------8<------8<------8<------8<------8<------8<-------- Index: Proposals/BEP-0010 ========================================================================= --- Proposals/BEP-0010 (version: 3) +++ Proposals/BEP-0010 (version: 4) @@ -51,22 +51,28 @@ Old schema {{{ PRIMARY KEY: (ticket-id) -> ticket + + table field: 'id' }}} New schema {{{ PRIMARY KEY: (ticket-id) -> ticket UNIQUE KEY: (product-prefix, scoped-id) -> ticket + + table fields: 'id' renamed 'uid', new field 'id' introduced as a scoped-id }}} -Creating a new ticket would increment the ticket-id (same as before), and additionally the scoped-id. In order to avoid another database table to keep track of the scoped-id, -the scoped-id could be calculated using the SELECT MAX SQL statement. - +Creating a new ticket would increment the ticket-id (same as before), and additionally the scoped-id. In order to avoid another database table to keep track of the scoped-id, the scoped-id could be calculated using the SELECT MAX SQL statement. +Note that the existing database field 'id' is renamed to 'uid' to ease the transition to the new numbering schema from a development point of view. **URL mappings** {{{ /env/ticket/<ticket-id> -> (ticket-id) -> ticket /env/products/<product-prefix>/ticket/<scoped-id> -> (product-prefix, scoped-id) -> ticket + + additionally proposed: + /env/ticket/<product-prefix>-<scoped-id> -> (product-prefix, scoped-id) -> ticket }}} @@ -82,8 +88,19 @@ * The scoped IDs are retained from the imported product **Implications** +1. Since the scoped IDs are not autoincremented fields in the database, they have to be calculated when inserting a new ticket. The proposal was to use SELECT MAX which would take care of the calculation, e.g.: +{{{ + INSERT INTO ticket (id, ...) VALUES (IFNULL((SELECT MAX(id) FROM ticket WHERE product='%s'), 0)+1, ...) +}}} -{{{TBD}}} +This however brings up issues with regards to concurrent database requests. Specifically, Postgress will successfully perform only one of the concurrent INSERT requests, all others will fail because they will get the same value for the 'id' field as the first one. This happens regardless of whether the transactions are performed within the same or another client. Sqlite3 seems to not exhibit such behaviour, but this should be tested to greater extent. +{{{TODO - test MySQL}}} + +2. After a ticket is inserted into the database, its 'id' is retrieved from the database. This operation depends on the database backend, and in SQLite and MySQL cases, the ID is retrieved using database cursor's last row ID. Since the returned ID should be product-scoped, a different mechanism should be used (something like SELECT MAX above, but without concurrency issues). + +3. Global IDs should be used only for internal purposes and should not be exposed to users to prevent confusion. + +4. Bulk inserts to the ticket table using 'INSERT INTO ticket SELECT...' are unlikely to work {{{TODO - tests}}}. Any plugin using this method should be tested, or the SQL translator should be changed to handle auto-generating the IDs in case they are not provided by the caller. === Alternative 2: Modification of global ID @@ -105,6 +122,9 @@ **URL mappings** {{{ /env/products/<product-prefix>/ticket/<ticket-id> -> (product-prefix, ticket-id) -> ticket + + additionally proposed: + /env/ticket/<product-prefix>-<scoped-id> -> (product-prefix, scoped-id) -> ticket }}} @@ -118,9 +138,11 @@ Importing a new product into existing multiproduct environment would simply create the (product-prefix, ticket-id) pair for each imported ticket. **Implications** +1. Same issues with SELECT MAX as in Alternative 1. -{{{TBD}}} -(Potential technical/performance issues with index not being incremental, check against supported SQL databases) +2. Same issues with bulk inserts as in Alternative 1. + +3. Potential technical/performance issues with index not being incremental. {{{TODO: check against supported SQL databases}}} -------8<------8<------8<------8<------8<------8<------8<------8<--------
-- Page URL: <https://issues.apache.org/bloodhound/wiki/Proposals/BEP-0010> Apache Bloodhound <https://issues.apache.org/bloodhound/> The Apache Bloodhound issue tracker This is an automated message. Someone added your email address to be notified of changes on 'Proposals/BEP-0010' page. If it was not you, please report to .