Page Proposals/BEP-0010 was changed by matevzb
Diff URL:
https://issues.apache.org/bloodhound/wiki/Proposals/BEP-0010?action=diffversion=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 .
