You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@bloodhound.apache.org by Andrej Golcov <an...@digiverse.si> on 2013/07/01 16:22:26 UTC
Re: [Apache Bloodhound] Proposals/BEP-0010 added
I suggest to exclude moving of tickets between products feature from
BEP-0010 and implement it as another BEP later. IMO, this adds
additional complexity to already complex problem of product-scoped
ticket numbering. We have to make sure that it will possible to
implement later (e.g. by introducing of additional history table) but
don't need to cover this in BEP-0010. I would concentrate on upgrade
and import functionality as part of feature list we want to have in
1.0 release.
I think that changing of tickets primary key to something like
id+produc_prefix (Alternative 2 in BPE-0010) is the best solution that
provides plugins backward compatibility and product-scoped numbering
as users would expect based on experience from Jira or Redmine. We
already have DB translator that emulates product scope in DB for
legacy API, so it should not be a huge change to simulate
auto-increment behaviour also.
Cheers, Andrej
Re: [Apache Bloodhound] Proposals/BEP-0010 added
Posted by Olemis Lang <ol...@gmail.com>.
On 7/1/13, Andrej Golcov <an...@digiverse.si> wrote:
[...]
>
> I think that changing of tickets primary key to something like
> id+produc_prefix (Alternative 2 in BPE-0010) is the best solution that
> provides plugins backward compatibility and product-scoped numbering
> as users would expect based on experience from Jira or Redmine. We
> already have DB translator that emulates product scope in DB for
> legacy API, so it should not be a huge change to simulate
> auto-increment behaviour also.
>
I second that , but just wanted to mention that , in case global
ticket unique ID is actually needed then it should be possible to add
it as well as a new autoinc column, though IMO should not be part of
the PK .
--
Regards,
Olemis.
Re: [Apache Bloodhound] Proposals/BEP-0010 added
Posted by Olemis Lang <ol...@gmail.com>.
On 7/24/13, Matevž Bradač <ma...@digiverse.si> wrote:
>
[...]
>
> 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.
Great !
> Please have a look at the changes, all suggestions, improvements etc. are
> more than welcome.
>
This is a really exciting improvement . I'll play with it in the next few days .
--
Regards,
Olemis.
Apache™ Bloodhound contributor
http://issues.apache.org/bloodhound
Blog ES: http://simelo-es.blogspot.com/
Blog EN: http://simelo-en.blogspot.com/
Featured article:
Re: [Apache Bloodhound] Proposals/BEP-0010 added
Posted by Matevž Bradač <ma...@digiverse.si>.
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.
Re: [Apache Bloodhound] Proposals/BEP-0010 added
Posted by Matevž Bradač <ma...@digiverse.si>.
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
Re: [Apache Bloodhound] Proposals/BEP-0010 added
Posted by Matevž Bradač <ma...@digiverse.si>.
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 <an...@digiverse.si> 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
Re: [Apache Bloodhound] Proposals/BEP-0010 added
Posted by Anze Staric <an...@gmail.com>.
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
On Mon, Jul 8, 2013 at 11:13 PM, Andrej Golcov <an...@digiverse.si> 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
Re: [Apache Bloodhound] Proposals/BEP-0010 added
Posted by Andrej Golcov <an...@digiverse.si>.
> 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
Re: [Apache Bloodhound] Proposals/BEP-0010 added
Posted by Anze Staric <an...@gmail.com>.
I prefer alternative 2 as it can be achieved with minimal (no?)
modifications to trac objects/sql translations. Ticket behaviour will
remain the same, ticket id field will still auto increment
independently of the product and /ticket/id will still only work for
tickets that reside in default product.
If import code wants to insert tickets with predefined numbers, it
will be able to (through SQL inserts, not API).
There is a caveat though. If imported ids are larger that those
already used in the table, auto increment sequences need to be
manually updated to generate numbers above the largest (in postgres
and sqlite, mysql auto increment should just work). If sequences are
not updated, new ticket ids might collide with imported ones.
Anze
On Mon, Jul 8, 2013 at 6:54 PM, Olemis Lang <ol...@gmail.com> wrote:
> On 7/8/13, Gary Martin <ga...@wandisco.com> wrote:
>> On 01/07/13 15:22, Andrej Golcov wrote:
>>> I suggest to exclude moving of tickets between products feature from
>>> BEP-0010 and implement it as another BEP later. IMO, this adds
>>> additional complexity to already complex problem of product-scoped
>>> ticket numbering. We have to make sure that it will possible to
>>> implement later (e.g. by introducing of additional history table) but
>>> don't need to cover this in BEP-0010. I would concentrate on upgrade
>>> and import functionality as part of feature list we want to have in
>>> 1.0 release.
>>>
>>> I think that changing of tickets primary key to something like
>>> id+produc_prefix (Alternative 2 in BPE-0010) is the best solution that
>>> provides plugins backward compatibility and product-scoped numbering
>>> as users would expect based on experience from Jira or Redmine. We
>>> already have DB translator that emulates product scope in DB for
>>> legacy API, so it should not be a huge change to simulate
>>> auto-increment behaviour also.
>>>
>>> Cheers, Andrej
>>
> [...]
>>
>> I am yet to see enough advantage in dropping the primary key at the
>> moment. In fact, given that I think it would probably be better to keep
>> the existing links to an upgraded trac environment working, I think it
>> is better to keep the existing primary key to support access through
>> /ticket/<ticket-id>.
>
> 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
>
> Instead I'd recommend to work towards /ticket/pid-12 URLs in global
> scope, if that's ever wanted. I'm not against having global ID for
> internal use (i.e. not exposed to users at all) but *now* I'd rather
> question how much useful it will be in practice.
>
>> While this ability could not be extended to
>> imported environments that would be the case regardless.
>>
>> I suppose I also don't see why maintaining the existing primary key is a
>> particular problem.
>
> The way I see it the problem is not exactly about maintaining the
> primary key or not (/me thinking of alternative 1 in bep:0010), but
> rather about making existing queries work by keeping as `id` the name
> of product-specific ticket ID column (be it in alt 1 or alt 2) and use
> it in ticket-resource DB relationships.
>
>> As we already have the DB translator, this should be
>> able to provide the appropriate support to deal with presenting the
>> appropriate ticket id to support legacy plugins. How is the alternative
>> an improvement upon this?
>>
>
> Alternative 3 (separate product to ticket ID mapping table) is nice to
> have but implies a big effort when it comes to keep the solution
> backwards compatible .
>
> So IMO I'd move forward by implementing alt 1 or 2 soon (Release 7 ?)
> , have some time to test it, etc ... and move forward with alt 3 later
> (Release 8 ?) to support moving tickets between products.
>
> --
> Regards,
>
> Olemis.
Re: [Apache Bloodhound] Proposals/BEP-0010 added
Posted by Olemis Lang <ol...@gmail.com>.
On 7/8/13, Gary Martin <ga...@wandisco.com> wrote:
> On 01/07/13 15:22, Andrej Golcov wrote:
>> I suggest to exclude moving of tickets between products feature from
>> BEP-0010 and implement it as another BEP later. IMO, this adds
>> additional complexity to already complex problem of product-scoped
>> ticket numbering. We have to make sure that it will possible to
>> implement later (e.g. by introducing of additional history table) but
>> don't need to cover this in BEP-0010. I would concentrate on upgrade
>> and import functionality as part of feature list we want to have in
>> 1.0 release.
>>
>> I think that changing of tickets primary key to something like
>> id+produc_prefix (Alternative 2 in BPE-0010) is the best solution that
>> provides plugins backward compatibility and product-scoped numbering
>> as users would expect based on experience from Jira or Redmine. We
>> already have DB translator that emulates product scope in DB for
>> legacy API, so it should not be a huge change to simulate
>> auto-increment behaviour also.
>>
>> Cheers, Andrej
>
[...]
>
> I am yet to see enough advantage in dropping the primary key at the
> moment. In fact, given that I think it would probably be better to keep
> the existing links to an upgraded trac environment working, I think it
> is better to keep the existing primary key to support access through
> /ticket/<ticket-id>.
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
Instead I'd recommend to work towards /ticket/pid-12 URLs in global
scope, if that's ever wanted. I'm not against having global ID for
internal use (i.e. not exposed to users at all) but *now* I'd rather
question how much useful it will be in practice.
> While this ability could not be extended to
> imported environments that would be the case regardless.
>
> I suppose I also don't see why maintaining the existing primary key is a
> particular problem.
The way I see it the problem is not exactly about maintaining the
primary key or not (/me thinking of alternative 1 in bep:0010), but
rather about making existing queries work by keeping as `id` the name
of product-specific ticket ID column (be it in alt 1 or alt 2) and use
it in ticket-resource DB relationships.
> As we already have the DB translator, this should be
> able to provide the appropriate support to deal with presenting the
> appropriate ticket id to support legacy plugins. How is the alternative
> an improvement upon this?
>
Alternative 3 (separate product to ticket ID mapping table) is nice to
have but implies a big effort when it comes to keep the solution
backwards compatible .
So IMO I'd move forward by implementing alt 1 or 2 soon (Release 7 ?)
, have some time to test it, etc ... and move forward with alt 3 later
(Release 8 ?) to support moving tickets between products.
--
Regards,
Olemis.
Re: [Apache Bloodhound] Proposals/BEP-0010 added
Posted by Gary Martin <ga...@wandisco.com>.
On 01/07/13 15:22, Andrej Golcov wrote:
> I suggest to exclude moving of tickets between products feature from
> BEP-0010 and implement it as another BEP later. IMO, this adds
> additional complexity to already complex problem of product-scoped
> ticket numbering. We have to make sure that it will possible to
> implement later (e.g. by introducing of additional history table) but
> don't need to cover this in BEP-0010. I would concentrate on upgrade
> and import functionality as part of feature list we want to have in
> 1.0 release.
>
> I think that changing of tickets primary key to something like
> id+produc_prefix (Alternative 2 in BPE-0010) is the best solution that
> provides plugins backward compatibility and product-scoped numbering
> as users would expect based on experience from Jira or Redmine. We
> already have DB translator that emulates product scope in DB for
> legacy API, so it should not be a huge change to simulate
> auto-increment behaviour also.
>
> Cheers, Andrej
Sorry if I am dragging this up when others considered this decided!
I am yet to see enough advantage in dropping the primary key at the
moment. In fact, given that I think it would probably be better to keep
the existing links to an upgraded trac environment working, I think it
is better to keep the existing primary key to support access through
/ticket/<ticket-id>. While this ability could not be extended to
imported environments that would be the case regardless.
I suppose I also don't see why maintaining the existing primary key is a
particular problem. As we already have the DB translator, this should be
able to provide the appropriate support to deal with presenting the
appropriate ticket id to support legacy plugins. How is the alternative
an improvement upon this?
Cheers,
Gary