#7221 NORM Opportu: trac improvement: replace multi-value 'blocking' and 'blocked by' fields with relationship

[Zarro Boogs per Child]

#7221: trac improvement: replace multi-value 'blocking' and 'blocked by' fields
with relationship
-------------------------+--------------------------------------------------
 Reporter:  ggoebel      |       Owner:  coderanger 
     Type:  enhancement  |      Status:  new        
 Priority:  normal       |   Milestone:  Opportunity
Component:  trac         |     Version:             
 Keywords:  trac         |    Verified:  0          
 Blocking:               |   Blockedby:             
-------------------------+--------------------------------------------------
 Modify 'Blocking' and 'Blocked By' ticket fields. They appear to be simple
 text fields which are used to contain 0..n ticket numbers. I.e. multi-
 value fields. Replace the multi-value fields with an underlying table
 relationship and constraints to guarantee data integrity and simplify and
 improve the performance of queries which work with blocking dependencies.

 Why is this bad?

 Text fields containing multiple values have historically been hard to work
 with in relational databases. It is hard to constrain the values by type
 or with column constraints. There is no guarantee that the data in the
 field actually be unique ticket numbers, or that values will be separated
 in a standard way. Consequently the data integrity of such values is poor.
 Furthermore, due to the limitations of most sql variants, such values are
 hard to get at using sql. Even where they are possible to get at, such
 queries are complex and inefficient.


 What would be better?

 It would be better if the concept of blocking and blocked by were defined
 by a may-have-many 'blocked by' relationship between ticket and
 ticket_block tables. If we can assume the ticket id is synonymous with
 ticket number the ticket_block table using a simple recursive hierarchy
 might look something like:

 CREATE TABLE ticket_block (
     ticket_blocked_id int,
     ticket_blocking_id int,
     CONSTRAINT pk_ticket_block
         PRIMARY KEY (
             ticket_blocked_id,
             ticket_blocking_id
         ),
     CONSTRAINT fk_ticket_block_ticket_blocked_id
         FOREIGN KEY (ticket_blocked_id)
         REFERENCES ticket (ticket_id),
     CONSTRAINT fk_ticket_block_ticket_blocking_id
         FOREIGN KEY (ticket_blocking_id)
         REFERENCES ticket (ticket_id),
 );

 In the UI for working with tickets the edit text fields for 'blocking' and
 'blocked by' should be constrained to only allow an existing ticket number
 to be entered which is:

 1) not the current ticket's number
 2) unique (not already added)
 3) prevents circular blocking dependencies

 Near the 'blocking' field (presumably beneath it) would be displayed a
 list of ticket numbers FROM ticket_block WHERE ticket_blocking_id =
 [current ticket id]. Next to each ticket would be a button which would
 allow the blocking dependency to be removed.

 Likewise for the 'blocked by' field... with the difference being that the
 list filter would be: FROM ticket_block WHERE ticket_blocked_id = [current
 ticket id]


 The Trac ticket workflow could then be modified to refuse to allow a
 ticket to be marked as completed until all the tickets it is blocked on
 have been marked completed.


 Depending on Trac's ability to be customized, you could then elect to
 allow 'Blocked Ancestors' and 'Blocking Descendants' to be displayed in
 some fashion. The former probably being more useful than the latter.

 If Trac can't represent the dependency heirarchy, it would be possible to
 create a representation using dynamic web page with direct read-only
 access to the database.

-- 
Ticket URL: <http://dev.laptop.org/ticket/7221>
One Laptop Per Child <http://laptop.org/>
OLPC bug tracking system