On Tuesday 04 August 2009 03:04:00 pm Greg Troxel wrote:
> I have gotten mastertickets to work with postgresql 8.3. The problem is
> SQL like
>
> "WHERE dest = %s", self.tkt.id()
>
> which for example passes "WHERE dest = 34" which was fine in earlier
> pgsql but causes 8.3 to throw a type mismatch error because there is no
> = function that takes a strings and a number.
>
> I put the following patch in pkgsrc, and that resolves the issue. I
> suspect this is portable SQL, but I am not quite sure. I believe I
> could also use \'%s\', and I wonder if people prefer that.
>
> --- mastertickets/model.py.orig 2009-08-04 13:50:22.000000000 -0400
> +++ mastertickets/model.py
> @@ -19,11 +19,11 @@ class TicketLinks(object):
> db = db or self.env.get_db_cnx()
> cursor = db.cursor()
>
> - cursor.execute('SELECT dest FROM mastertickets WHERE source=%s
> ORDER BY dest', (self.tkt.id,)) + cursor.execute('SELECT dest FROM
> mastertickets WHERE source=text(%s) ORDER BY dest', (self.tkt.id,))
> self.blocking = set([num for num, in cursor])
> self._old_blocking = copy.copy(self.blocking)
>
> - cursor.execute('SELECT source FROM mastertickets WHERE dest=%s
> ORDER BY source', (self.tkt.id,)) + cursor.execute('SELECT source
> FROM mastertickets WHERE dest=text(%s) ORDER BY source', (self.tkt.id,))
> self.blocked_by = set([num for num, in cursor])
> self._old_blocked_by = copy.copy(self.blocked_by)
>
> @@ -54,7 +54,7 @@ class TicketLinks(object):
> update_field = lambda lst:
> lst.append(str(self.tkt.id)) elif n not in new_ids and n in old_ids:
> # Old ticket removed
> - cursor.execute('DELETE FROM mastertickets WHERE %s=%%s
> AND %s=%%s'%sourcedest, (self.tkt.id, n)) +
> cursor.execute('DELETE FROM mastertickets WHERE %s=text(%%s) AND
> %s=text(%%s)'%sourcedest, (self.tkt.id, n)) update_field = lambda lst:
> lst.remove(str(self.tkt.id))
>
> if update_field is not None:
>
>
> I'll revise the patch based on comments, and then I'd like to commit it
> to the trac-hacks repository, or have someone else commit it. What is
> the procedure for that (review/approval rules, mechanics)?
Hi, we had the same problem too a while back.
Just for the sake of having a complete choice of solutions available, attached
is a patch of what I did to fix this in our case. I really should have
submitted this earlier but by the time I'd fixed it I really needed to be
getting on with some work, and promptly forgot about it. :)
In my case I cast the variable being passed in to the string type, so when the
parameters are bound to the query they assume the correct type. The advantage
of this method is that the query itself requires no modification.
Hope this is useful.
--
Russ
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Trac
Users" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/trac-users?hl=en
-~----------~----~----~----~------~----~------~--~---
Index: mastertickets/model.py
===================================================================
--- mastertickets/model.py (revision 6257)
+++ mastertickets/model.py (working copy)
@@ -19,11 +19,11 @@
db = db or self.env.get_db_cnx()
cursor = db.cursor()
- cursor.execute('SELECT dest FROM mastertickets WHERE source=%s ORDER BY dest', (self.tkt.id,))
+ cursor.execute('SELECT dest FROM mastertickets WHERE source=%s ORDER BY dest', (str(self.tkt.id),))
self.blocking = set([num for num, in cursor])
self._old_blocking = copy.copy(self.blocking)
- cursor.execute('SELECT source FROM mastertickets WHERE dest=%s ORDER BY source', (self.tkt.id,))
+ cursor.execute('SELECT source FROM mastertickets WHERE dest=%s ORDER BY source', (str(self.tkt.id),))
self.blocked_by = set([num for num, in cursor])
self._old_blocked_by = copy.copy(self.blocked_by)
@@ -50,11 +50,11 @@
update_field = None
if n in new_ids and n not in old_ids:
# New ticket added
- cursor.execute('INSERT INTO mastertickets (%s, %s) VALUES (%%s, %%s)'%sourcedest, (self.tkt.id, n))
+ cursor.execute('INSERT INTO mastertickets (%s, %s) VALUES (%%s, %%s)'%sourcedest, (str(self.tkt.id), n))
update_field = lambda lst: lst.append(str(self.tkt.id))
elif n not in new_ids and n in old_ids:
# Old ticket removed
- cursor.execute('DELETE FROM mastertickets WHERE %s=%%s AND %s=%%s'%sourcedest, (self.tkt.id, n))
+ cursor.execute('DELETE FROM mastertickets WHERE %s=%%s AND %s=%%s'%sourcedest, (str(self.tkt.id), n))
update_field = lambda lst: lst.remove(str(self.tkt.id))
if update_field is not None:
@@ -70,7 +70,7 @@
if comment:
cursor.execute('INSERT INTO ticket_change (ticket, time, author, field, oldvalue, newvalue) VALUES (%s, %s, %s, %s, %s, %s)',
- (n, when_ts, author, 'comment', '', '(In #%s) %s'%(self.tkt.id, comment)))
+ (n, when_ts, author, 'comment', '', '(In #%s) %s'%(str(self.tkt.id), comment)))
cursor.execute('UPDATE ticket_custom SET value=%s WHERE ticket=%s AND name=%s',
