[Patch] Type Casts in Postgresl 8.3

[victorhg@…]

Hello,

I've been trying to use the MasterTicketsPlugin with postgresql 8.3. There are some issues with type checking (PSQGL has a strong type checking mechanism):

model.py

cursor.execute('SELECT dest FROM mastertickets WHERE source=%s ORDER BY dest', (self.tkt.id,))

should be

cursor.execute('SELECT dest FROM mastertickets WHERE source='%s' ORDER BY dest', (self.tkt.id,))

Am i right?? I'm receiveing the psql error:

2008-02-29 11:38:17 BRT HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
2008-02-29 11:38:17 BRT COMMAND:  SELECT dest FROM mastertickets WHERE source=1 ORDER BY dest

The ​TracInstall comes with the follwing alert, related with the problem described above:
Warning: PostgreSQL 8.3 uses a strict type checking mechanism. To use Trac with the 8.3 Version of PostgreSQL, you will need trac-0.11 or later.

thanks

[victorhg@…]

sorr

[anonymous]

fix

[anonymous]

fix

[apatrushev@…]

diff -r /tmp/masterticketsplugin/0.11/mastertickets/model.py mastertickets/model.py
22c22
<         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),))
26c26
<         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),))
53c53
<                     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), str(n)))
57c57
<                     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), str(n)))
62c62
<                                    (n, field))
---
>                                    (n, str(field)))

[brad-trachacks@…]

Same change model.py.diff but as unified diff.

[brad-trachacks@…]

Just ran into the same problem and was able to fix it with the model.py.diff patch. (I have no idea if that's a good solution.)

The attached model.py.diff and model.py.2.diff files are identical:

5d543b1e2db100be8d4a382cf264da21  model.py.diff
5d543b1e2db100be8d4a382cf264da21  model.py.2.diff

FWIW, I attached a unified diff version.

[khym@…]

Shouldn't that last chunk in the diff change n to str(n), rather than field to str(field)?

[Shane Caraveo]

since dest and source contain ticket id, why use a text field? wouldn't a simpler approach be to change the table to have integer fields?

[Shane Caraveo]

fix table creation to deal with strict types for postgres

[luke@…]

This patch changes the database schema to use integers instead of strings, and provides the necessary code to upgrade from the previous database schema.

[luke@…]

I've supplied a patch above that allows the database schema to be upgraded from the previous version that uses text fields instead of integers. I performed the following tests.

• New install
  1. Set up a clean Trac install with no previous mastertickets tables of records in the database
  2. Installed my patched version of mastertickets
  3. ran trac-admin /var/lib/trac-alpha upgrade --no-backup
  4. Started trac, created tickets with blocked by/blocking fields
  5. Verified UI display of the fields
  6. Verified tables and records in the database
  7. TEST OK

• Upgraded install
  1. Set up a clean Trac install with no previous mastertickets tables of records in the database
  2. Installed mastertickets with ​this patch which uses version 1 of the database schema
  3. ran trac-admin /var/lib/trac-alpha upgrade --no-backup
  4. Started trac, created tickets with blocked by/blocking fields
  5. Replaced mastertickets with my patched version
  6. ran trac-admin /var/lib/trac-alpha upgrade --no-backup
  7. Started trac, verified my old tickets, created new ones too
  8. Verified tables and records in the database
  9. TEST OK

[Shane Caraveo]

Replying to luke@zymeworks.com:

I've supplied a patch above that allows the database schema to be upgraded from the previous version that uses text fields instead of integers. I performed the following tests.

Your patch is empty.

[anonymous]

Replying to mixedpuppy:

Your patch is empty.

It's there, but you have to download it in the original format. Trac-hacks isn't parsing it for some reason, but I can't see anything wrong with the formatting. It's simply the output of svn diff. The same problem happens with the first two attachments on this ticket.

[luke@…]

This replaces my previous patch, and fixes a case where a ticket number was unsuccessfully being compared to its string representation.

[johnwilliams@…]

I'd love to use this plugin for its blocking/blocked by fields, but tickets become unusable (see server output below) when I install the plugin and upgrade the database. I think it's related to this ticket, so I tried the various patches posted above but without success. I'm running Trac 0.11, Apache 2.2.9, and Postgres 8.3.7 on Ubuntu Intrepid.

Is the issue still being looked into? Is there any other information you need from me?

Here is the output of my server when I try to view a ticket:

Trac detected an internal error:

ProgrammingError: operator does not exist: text = integer
LINE 1: SELECT dest FROM mastertickets WHERE source=3 ORDER BY dest
                                                   ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

Python Traceback

Most recent call last:
File "/usr/lib/python2.5/site-packages/trac/web/main.py", line 423, in _dispatch_request
  dispatcher.dispatch(req)
File "/usr/lib/python2.5/site-packages/trac/web/main.py", line 209, in dispatch
  self._post_process_request(req, *resp)
File "/usr/lib/python2.5/site-packages/trac/web/main.py", line 299, in _post_process_request
  resp = f.post_process_request(req, *resp)
File "build/bdist.linux-i686/egg/mastertickets/web_ui.py", line 45, in 
  post_process_requestFile "build/bdist.linux-i686/egg/mastertickets/model.py", 
  line 22, in __init__File "/usr/lib/python2.5/site-packages/trac/db/util.py", 
  line 50, in execute
  return self.cursor.execute(sql_escape_percent(sql), args)
File "/usr/lib/python2.5/site-packages/trac/db/util.py", line 50, in execute
  return self.cursor.execute(sql_escape_percent(sql), args)


System Information:

User Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.0.8) Gecko/2009032609 Firefox/3.0.8
Trac: 	0.11
Python: 	2.5.2 (r252:60911, Oct 5 2008, 19:42:18) [GCC 4.3.2]
setuptools: 	0.6c9
psycopg2: 	2.0.8
Genshi: 	0.5.1
mod_python: 	3.3.1
Pygments: 	0.10
Subversion: 	1.5.1 (r32289)
jQuery:	1.2.3

[mikero]

I'm hitting similar problems and we're also massively keen to move forward with this module. Is there any more data beyond the below I can provide that would help?

Trac detected an internal error:
ProgrammingError: operator does not exist: text = integer LINE 1: SELECT dest FROM mastertickets WHERE source=415 ORDER BY des... ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
This is probably a local installation issue.

Python Traceback

Most recent call last:
File "/usr/lib/python2.5/site-packages/Trac-0.11.5-py2.5.egg/trac/web/main.py", line 444, in _dispatch_request
  dispatcher.dispatch(req)
File "/usr/lib/python2.5/site-packages/Trac-0.11.5-py2.5.egg/trac/web/main.py", line 216, in dispatch
  self._post_process_request(req, *resp)
File "/usr/lib/python2.5/site-packages/Trac-0.11.5-py2.5.egg/trac/web/main.py", line 308, in _post_process_request
  resp = f.post_process_request(req, *resp)
File "build/bdist.linux-x86_64/egg/mastertickets/web_ui.py", line 45, in post_process_requestFile "build/bdist.linux-x86_64/egg/mastertickets/model.py", line 22, in __init__File "/usr/lib/python2.5/site-packages/Trac-0.11.5-py2.5.egg/trac/db/util.py", line 59, in execute
  return self.cursor.execute(sql_escape_percent(sql), args)
File "/usr/lib/python2.5/site-packages/Trac-0.11.5-py2.5.egg/trac/db/util.py", line 59, in execute
  return self.cursor.execute(sql_escape_percent(sql), args)


System Information:

User Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10_5_7; en-us) AppleWebKit/525.28.3 (KHTML, like Gecko) Version/3.2.3 Safari/525.28.3

Trac:	0.11.5
Python:	2.5.2 (r252:60911, Jul 22 2009, 15:33:10) [GCC 4.2.4 (Ubuntu 4.2.4-1ubuntu3)]
setuptools:	0.6c8
psycopg2:	2.0.6
Genshi:	0.5.1
Pygments:	1.0
RPC:	1.0.6
jQuery:	1.2.6

[mikero]

apatrushev@…'s patch has helped - apologies for not reading the full thread first.

[anonymous]

Can a working patch be incorporated into the official version? It looks like the mastertickets-intfields-patch.3.diff patch is the most useful one. I'm using it now but it would be best if the fix was incorporated into the official version in svn.

[theyranos@…]

#6378 has been closed as a duplicate of this ticket.

[henrik.steensland@…]

Patch based on mastertickets-intfields-patch.3.diff, but actually deletes rows from masterticket table

[henrik.steensland@…]

The problem I had with mastertickets-intfields-patch.3.diff was that once you had added a blocked_by or blocking and then later attempted to remove it, it would remain in the masterticket table, even though it would be deleted from ticket_custom.

[matt.caron@…]

Replying to henrik.steensland@gmail.com:

The problem I had with mastertickets-intfields-patch.3.diff was that once you had added a blocked_by or blocking and then later attempted to remove it, it would remain in the masterticket table, even though it would be deleted from ticket_custom.

FYI - Your patch is backwards and needs to be applied with patch -R.

[matt.caron@…]

Combined patch of mastertickets-hs.patch and mastertickets-intfields-patch.3.diff

[matt.caron@…]

Added meta-patch which combines mastertickets-intfields-patch.3.diff and mastertickets-hs.patch.

Rationale:

• mastertickets-hs.patch was in the wrong order and had to be applied with patch -R
• mastertickets-hs.patch lacked the "save the old DB data" migration code which was in mastertickets-intfields-patch.3.diff - useful if you have existing data
• mastertickets-hs.patch failed to increment the DB version

[matt.caron@…]

Rev 2 of combined patck

[matt.caron@…]

Updated meta patch.

• Fixes an issue where a join would fail because it expects a string
• Fixes an issue where you couldn't delete dependencies once established.

[matt.caron@…]

Rev 2a of combined patch (fixes accidental dropping of typecast)

[matt.caron@…]

Rev 2b of combined patch (fixes accidental dropping of typecast, and malformed previous patch)

[Noah Kantrowitz]

Applied ​http://github.com/coderanger/trac-mastertickets/commit/ae03a927c629cc1df790b261202e2efea1da1805