"priority" management is riddled with bugs

[jrf@…]

I would like to use "priority" to organize priorities of individual engineers. Unfortunately, it is frustratingly buggy. It appears not to have been tested for values other than the canical (and for us not useful) critical|major|minor|blocker.

My favorite example is below. Other examples include deleting a priority name to which tickets were associated has the bad side affect of orphaning those tickets. Even creating that name again does not un-orphan them.

Trac [/home2/mcprojects/webapps/trac]> priority add a8
Trac [/home2/mcprojects/webapps/trac]> priority add a9
Trac [/home2/mcprojects/webapps/trac]> priority add a10
Trac [/home2/mcprojects/webapps/trac]> priority list

Possible Values
---------------
a0             
a10            
a9             
a1             
a2             
a3             
a4             
a5             
a6             
a7             
a8             

Trac [/home2/mcprojects/webapps/trac]> priority remove a10
Trac [/home2/mcprojects/webapps/trac]> priority remove a9
Command failed: priority a9 does not exist.

[Matthew Good]

Can you give some more explanation of your statement that deleted priorities are "orphaning" tickets? Please give an example of the effect you're seeing on the tickets that are associated with a deleted priority.

The problem with the priority example above seems to be an issue with SQLite's "typeless" columns. It's calculating the max textually, so "9" is greater than "10", thus after the value "9" is reached all subsequent values are assigned "10". So, "a9" and "a10" both got "10" for their value, thus they both get deleted together. I can fix this one.

[Matthew Good]

Ok, this should be fixed in r3690

[anonymous]

Deleting a piority still orphans tickets. This means that every ticket that was assigned the deleted priority will now disappear from any report that contains the following clauses

FROM ticket t, enum p WHERE p.name = t.priority AND p.type = 'priority'

To reproduce in 0.10.2 simply create a ticket, give it a prio, and then delete the prio.

The problem is that ticket priorities are represented using the priority name as a foreign key into the ticket table. A more OO implementation cans solve this. That is: 1) create an (object-)id column for every priority (new dedicated table?) 2) make the prio name and order mere attributes 2) store the prio id into the ticket table

[Matthew Good]

Replying to anonymous:

Deleting a piority still orphans tickets. This means that every ticket that was assigned the deleted priority will now disappear from any report that contains the following clauses

FROM ticket t, enum p WHERE p.name = t.priority AND p.type = 'priority'

Ok. In r4357 and r4358 the reports have been updated to use a LEFT JOIN instead, so tickets with a priority not found in the "enum" table will still be displayed. This will only affect new projects, so you'll need to modify the reports on existing projects with:

FROM ticket t
LEFT JONI enum p ON p.name = t.priority and p.type = 'priority'

[sid]

Oops, that mgood's comment had a typo. You should update existing project reports with:

FROM ticket t
LEFT JOIN enum p ON p.name = t.priority and p.type = 'priority'

JOIN, not JONI :)

[gregsurbey@…]

My example if you want to get back priority color highlighting in your reports:

# http://trac.edgewall.org/wiki/TracReports#Customformattingcolumns
psql -U postgres trac
# So now I find out who has 'value'
select table_name from information_schema.columns where column_name = 'value';
\d
# I see that enum is a table in trac
select column_name from information_schema.columns where table_name = 'enum';
select * from enum;
# noticed that priority numbers were too high to activate CSS color, so I change them
update enum set value = 1 where name = 'today';
update enum set value = 2 where name = 'oneweek';
update enum set value = 3 where name = 'twoweeks';
update enum set value = 4 where name = 'onemonth';
update enum set value = 5 where name = 'whenever';
\q