[Patch] ClientsPlugin not working with Postgres 8.3 (possibly other versions) as trac backend

[scott.mead@…]

The clientsplugin is emitting some SQL that is bad when it comes to postgresql 8.3 (haven't tried with lower versions, it may be the same). I have modified the api.py and model.py files to try and make this work. The 'broken' behavior is as follows:

• Cannot upgrade

During inital schema creation, a rollback is being issue when trying to populate the 'client_events' table. This is because surrounding a string with a " (double quote) in postgres is just asking for a column. The solution was to change the " to ' (double quotes to single quotes).

API.PY Line 89-101​

• Next, the problem is that during client 'creation' (via webadmin) there was an error being thrown: "" Invalid input syntax for INTEGER

After tracing, I found that the 'self.default_rate' and 'self.currency' variables where being defaulting to ' ' (empty string) if not found. I changed this to default to 0.
MODEL.PY Line 37-38​

• The last problem was on updating the client, if you erased the '0' in the billrate box, and submitted, you would get the same error as before. I just added to checks (one for default_rate and one for currency) right before the execution of the UPDATE statement.
  MODEL.PY Line 95​

This is working on my setup now, but there are a couple of things to note:

• It was a new setup with no existing tickets
• PostgreSQL 8.3.6
• Trac being run through apache with ldap based authentication

If you have questions or want more info, please feel free to contact me.

[scott.mead@…]

Updated to support postgres 8.3

[scott.mead@…]

Updated to support postgres 8.3

[scott.mead@…]

Updated to support postgres 8.3

[scott.mead@…]

The api2.py is an exact copy of api.py. I accidentally uploaded twice, sorry for the confusion.

[Colin Guthrie]

(In [6473]) Hopefully fix some postgres issues.

Closes #5652, Refs #4805

[Colin Guthrie]

(In [6474]) Partially revert r6473 (the reports.py part was not intended here).

Refs #5652 and #4805

[Colin Guthrie]

Hi,

Sorry for the long delay.

I applied fixes to the SQL (slightly different approach to yours as I hate escaping things if I can avoid it!)

I've not applied your model.py changes as a 0 rate is not the same as "no rate" A 0 rate implies the cost to the client is 0 but "no rate" can mean that a rate is simply not defined.

I'd like to keep this separate as I don't want tickets to be created for a given client with 0's in the rate if possible.

I've made a somewhat futile attempt to pass in a null or similar to PG to see if that works. If you can test that would be great.

In the future if you can supply svn diff output rather than full files that would be much preferred as makes my life a lot easier :)

Thanks.