Modify

Opened 10 years ago

Closed 10 years ago

#1298 closed defect (fixed)

blocking "44" makes "#4" blocked

Reported by: phpxcache Owned by: Noah Kantrowitz
Priority: normal Component: MasterTicketsPlugin
Severity: major Keywords:
Cc: chris@…, paresh.solanki@…, Jeffrey Hulten Trac Release: 0.10

Description (last modified by Noah Kantrowitz)

when i fill "blocking" with "44" in #123, ticket "#4" is blocked by "#123", i'm sure this is wrong.

Index: mastertickets/util.py
===================================================================
--- mastertickets/util.py   (revision 2071)
+++ mastertickets/util.py   (working copy)
@@ -10,10 +10,15 @@
     db = env.get_db_cnx()
     cursor = db.cursor()

-    cursor.execute('SELECT ticket FROM ticket_custom WHERE name=%s AND (value LIKE %s OR value LIKE %s)',
+    cursor.execute('SELECT ticket,value FROM ticket_custom WHERE name=%s AND (value LIKE %s OR value LIKE %s)',
                    ('blocking', '%%%s,%%'%tkt, '%%%s'%tkt))
-    blocking_ids = [row[0] for row in cursor]
+    blocking_ids = []
+    for row in cursor:
+        (ticket, value) = row
+        blocks = value.split(',')
+        if tkt in blocks:
+            blocking_ids.append(ticket)
     return blocking_ids

 def linkify_ids(env, req, ids):
-    return Markup(', '.join([unicode(html.A('#%s'%i, href=req.href.ticket(i), class_='%s ticket'%Ticket(env, i)['status'])) for i in ids]))
\ No newline at end of file
+    return Markup(', '.join([unicode(html.A('#%s'%i, href=req.href.ticket(i), class_='%s ticket'%Ticket(env, i)['status'])) for i in ids]))
Index: setup.py

there is nice sql function FIND_IN_SET() but is mysql specified (not sure)

Attachments (0)

Change History (10)

comment:1 Changed 10 years ago by Jonathan S. Shapiro

I confirm this bug. I confirm that the patch appears to resolve it, though it does seem strange to need to do a filtering pass this way. I'm not a SQL user much, but surely there is a way to filter for an exact match on the ticket in the initial SELECT?

Anyway, as a workaround it seems to work.

comment:2 Changed 10 years ago by anonymous

Cc: chris@… added; anonymous removed

I'm seeing this one too.

A regexp in the SQL query to ensure the matching ticket ID was word-boundary delimited would pass, but this would require regexp in SQLITE, which appears not to be a builtin. So the filtering pass is a good workable fix.

When filterin pass is added, the SQL doesn't need the second ( LIKE OR LIKE ). I am using,

def blocked_by(env, tkt):
    if isinstance(tkt, Ticket):
        tkt = tkt.id # Allow passing a Ticket object

    db = env.get_db_cnx()
    cursor = db.cursor()

    cursor.execute('SELECT ticket, value FROM ticket_custom WHERE name=%s AND value LIKE %s',
                   ('blocking', '%%%s'%tkt))

    blocking_ids = []
    for row in cursor:
      (ticket,value) = row
      blocks = value.split(',')
      if tkt in blocks:
        blocking_ids.append(ticket)
    return blocking_ids

comment:3 Changed 10 years ago by paresh.solanki@…

Cc: paresh.solanki@… added

I also have this problem:

Tickets 50, 51 and 52 are blocking 49, but when ticket 9 is viewed, it also says it is being blocked by 50, 51 and 52.

However, this only seems to affect single digit tickets as tickets 19, 29 and 39 are not affected by this.

It's not really a fix, but a quick work around would be to create the first 10 tickets as dummies and close them off before using the system for real?

comment:4 Changed 10 years ago by anonymous

That's because '9' is a substring of '49', but '19' is not. (In SQL, %9% matches 49.) You'd also have to create dummy tickets each time your ticket count went up by a factor of ten, and 10% of your tickets would always be dummy tickets. (My maths may be wrong, it's early.)

Either of the patches above should fix the problem for you.

comment:5 Changed 10 years ago by Stefan

Isn't the real problem in storing multiple values (x,y,z) in one column (blocking)? That breaks the 1NF in table design.

comment:6 in reply to:  5 Changed 10 years ago by Noah Kantrowitz

Replying to Stefan:

Isn't the real problem in storing multiple values (x,y,z) in one column (blocking)? That breaks the 1NF in table design.

Yes, however I was hoping to avoid maintaining additional invariant data about ticket links. The next iteration of this plugin (which probably won't be until after workflow is merged to trunk) will use it's own table for data storage.

comment:7 Changed 10 years ago by Jeffrey Hulten

Cc: Jeffrey Hulten added
Description: modified (diff)

comment:8 in reply to:  2 Changed 10 years ago by anonymous

Severity: normalmajor

Replying to anonymous:

Sorry I'm not an SQL expert so I can't help further, but neither of the above patches work for me. They make the original problem go away, but break the "Blocked By:" functionality (at least in certain cases).

comment:9 Changed 10 years ago by anonymous

you tell the case, and i fix the issue

comment:10 Changed 10 years ago by Noah Kantrowitz

Description: modified (diff)
Resolution: fixed
Status: newclosed

This is now fixed in trunk.

Modify Ticket

Change Properties
Set your email in Preferences
Action
as closed The owner will remain Noah Kantrowitz.
The resolution will be deleted. Next status will be 'reopened'.

Add Comment


E-mail address and name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.